trivsql: A trivial SQL engine for your application
Introduction
trivsql is a simple SQL engine, implemented on the tdb database engine. The intention of trivsql is that it is embedded into your application, allowing the use of familiar SQL constructs for accessing data stored within your application.
trivsql is licensed under the terms of the GNU GPL, as is the tdb engine upon which it depends.
About the author
Michael has been working in the image processing field for several years, including a couple of years managing and developing large image databases for an Australian government department. He currently works for TOWER Software, who manufacture a world leading EDMS and Records Management package named TRIM. Michael is also the developer of Panda, an open source PDF generation API, as well as being the maintainer of the comp.text.pdf USENET frequently asked questions document.
Michael has a website at http://www.stillhq.com.
An introduction to tdb
tdb is a (name, value) pair database similar to gdbm. It was originally implemented for the Samba project, as a way of avoiding having to implement a large number of in memory linked lists for storing usage and access information. The code for tdb is available from the Samba CVS server.
To quote from the Samba source code: This is a simple database API.
It was inspired by the realisation that
in Samba we have several ad-hoc bits of code that essentially
implement small databases for sharing structures between parts of
Samba. As I was about to add another I realised that a generic
database module was called for to replace all the ad-hoc bits.
I based the interface on gdbm. I couldn't use gdbm as we need to be
able to have multiple writers to the databases at one time.
To make use of a tdb database at the most trivial level, you open the database with a tdb_open function call, you can then add pairs to the database using the tdb_store function, and retrieve pairs from the database with the tdb_fetch function. The database is closed when you are finished with the via the tdb_close function.
There are also functions which allow you to do slightly more interesting operations, such as enumerating all of the keys defined in the database.
Why trivsql?
The functionality offered by tdb is satisfactory for simple databases where all you want to store is small nuggets of data. For instance, when you want to store that the user Daniel is logged on, then a piece of code such as:
TDB_CONTEXT db;
TDB_DATA dbkey, dbdata;
// Open the database
document->db = tdb_open ("foo.tdb", 0,
TDB_CLEAR_IF_FIRST, O_RDWR | O_CREAT | O_TRUNC,
0600);
// Store the value
dbkey.dptr = "Daniel";
dbkey.dsize = strlen (key) + 1;
dbdata.dptr = "LoggedOn";
dbdata.dsize = strlen (value) + 1;
if (tdb_store (db, dbkey, dbdata, TDB_REPLACE) != 0)
{
fprintf(stderr, "Could not store value");
}
And to retrieve the value:
TDB_CONTEXT db;
TDB_DATA dbkey, dbdata;
// Open the database
document->db = tdb_open ("foo.tdb", 0,
TDB_CLEAR_IF_FIRST, O_RDWR | O_CREAT | O_TRUNC,
0600);
// Fetch the value
dbkey.dptr = "Daniel";
dbkey.dsize = strlen (key) + 1;
dbdata = tdb_fetch (document->db, dbkey);
This doesn't work so well when the data you want to store is more complex. For example, let's imagine that we want to store some more information about Daniel -- for instance how many bytes he has transfered this session and a list of his favourite users. I can think of four ways that we can store this information in a tdb database:
We can define a structure that contains these elements, and then store the pointer to this structure in the tdb. The problem with this is that it doesn't persist across restarts of the program, because the pointer will no longer be valid.
We can use a series of interestingly named keys, each storing a piece of information. For example:
daniel-bytestransfered
daniel-friends
et cetera
The problem with this is that it produces horribly verbose code, because the code to read a single key is repeated all over the place.
We can push all the information into one string, and then store that in the tdb, but this means that we have to handle parsing the information again when you need it back. Since writing trivsql the Samba team has dealt with exactly this problem in this manner. To ease the implementation, Dr Andrew Tridgell has written genstruct, which takes c structures (including pointers) and serializes them into strings. genstruct is available from http://www.samba.org.
You can use trivsql, which looks after all the ugliness for you, and allows you to perform select and insert statements in a manner familiar to many programmers.
Why SQL?
SQL was selected as a logical layer over tdb because there are many developers in the world which are already familiar with it. It is also relatively simple to parse and process the commands.
Parsing SQL
The SQL parser for trivsql is implemented in flex and bison, the listings for which are included at the end of this article. These files define a simple grammar for the supported SQL commands.
The following SQL commands are currently supported:
CREATE: used to create a table, for example CREATE TABLE newtable (cola, colb, colc);. Inside the brackets is a comma separated list of column names. Note that trivsql doesn't currently support datatypes for the columns of tables, so it is up to the user to make sure that they parse the string values in each column correctly -- this will change in a later version of trivsql.
INSERT: for example INSERT INTO newtable (cola, colc) VALUES ('123', 'banana');. Here we are inserting the given values into the table newtable as specified in the column list. trivsql supports NULL values, so the list of columns here does not need to include every column in the table.
SELECT: this is the most compilicated of the SQL commands supported by trivsql. An example is SELECT * FROM newtable WHERE cola = '123';. You are not required to have a conditional applied to the select statement -- so SELECT * FROM newtable will return all the rows in the table. If the select has returned a column value which has a NULL value (i.e. data has never been inserted there), then you'll recieve a (char *) NULL. The available conditions are discussed below.
ALTER: ALTER newtable ADD COLUMN cold; allows you to add columns to a table. At the moment you can only add columns to a table with the ALTER command.
UPDATE: allows commands of the form UPDATE newtable SET two = 'g' WHERE two = 'f';. This is useful for changing existing values in a row without having to remove the entire row and then readd it.
Available conditional statements
The following selectors are currently available:
Equality: for instance 'foo' = 7
Like: although the % syntax is not implemented
And / or: so that you can have more than one criteria (including using brackets to apply precidence)
Execution strategy for conditional statements
The current state of play
As shown by the list of SQL commands supported, trivsql is by no means a complete SQL implementation at this time. It is however quite useful, and stable. Because it is open source, it is also possible for users to add support for their own SQL commands if they are needed.
trivsql is still being actively developed -- future releases will include greater functionality, so users should keep track of what has recently been released by occassionally visiting http://www.stillhq.com
Interface to trivsql
...
An example usage
trivsql is really easy to use. The code snippet below is from the trivsql distribution. It simply reads commands from standard input, and executes them with trivsql. If the command returned a recordset (which is how all data is returned to the caller), then the recordset is displayed.
#include <stdio.h>
#include "trivsql.h"
int main(int argc, char *argv[]){
trivsql_state *ourState;
trivsql_recordset *rs;
char cmd[1000];
if(argc != 2){
fprintf(stderr, "Please specify a db file\n");
exit(42);
}
ourState = trivsql_opendb(argv[1]);
if(trivsql_initok(ourState) != TRIVSQL_TRUE){
fprintf(stderr, "Database open failed\n");
exit(42);
}
while(fgets(cmd, 1000, stdin) != NULL){
rs = trivsql_execute(ourState, cmd);
if(rs != NULL) trivsql_displayrs(rs);
else printf("NULL recordset\n");
trivsql_xfree(rs);
}
}
Code: /home/mikal/opensource/trivsql/example.c
Input
The following input:
CREATE TABLE newtable (cola, colb, colc);
SELECT * FROM newtable;
INSERT INTO newtable (cola, colc) VALUES ('123', 'banana');
SELECT * FROM newtable;
SELECT * FROM newtable WHERE cola = '123';
Code: /home/mikal/opensource/trivsql/example.sql
Produces the following output:
Genstruct as an alternative
Given that the Samba team chose to use genstruct to solve this same problem, it is worthwhile to briefly discuss their solution.
What is genstruct?
genstruct is very similar in some respects to the perl Data::Dumper. It is therefore useful to provide a brief introduction to that functionality before moving onto genstruct.
Perl's Data::Dumper in a nutshell
Perl's Data::Dumper is quite simple to use:
Which produces...
$VAR1 = [
'foo',
'bar'
];
$VAR2 = {
'walter' => 'wibble',
'carl' => 'chicken',
'bob' => 'banana'
};
$VAR3 = 42;
Genstruct
genstruct is a perl program which is run at compile time. It parses the c header files for the program you want to use genstruct with, using tags that you have to embed into the header file. For example, the sample which comes with genstruct is as follows:
GENSTRUCT enum fruit {APPLE, ORANGE=2, PEAR,
RASBERRY, PEACH};
GENSTRUCT
struct test2
{
int x1;
char *foo;
char fstring[20]; _NULLTERM
int dlen;
char *dfoo; _LEN(dlen)
enum fruit fvalue;
struct test2 *next;
};
GENSTRUCT struct test1 {
char foo[100];
char *foo2[20];
int xlen;
int *iarray; _LEN(xlen);
unsigned slen;
char **strings; _LEN(slen);
char *s2[5];
double d1, d2, d3;
struct test2 *test2;
int alen;
struct test2 *test2_array; _LEN(alen);
struct test2 *test2_fixed[2];
int plen;
struct test2 **test2_parray; _LEN(plen)
};
Code: ../short-genstruct/article.sgml
In this example you can see that structures which should have genstruct enabled have the GENSTRUCT attribute associated with them, you are therefore not required to have all of the data structures in your code exportable. GENSTRUCT is merely an empty #define, which the genstruct header file parser can search for.
To create a string representation of a data structure, simply:
char *s;
struct test1 t;
// ... we need to populate t with data here ...
s = gen_dump(pinfo_test1, (char *) &t, 0);
In this code, we define a structure, fill it with data, and then use the gen_dump function to create a string representation of that structure. The arguments to gen_dump are:
char *gen_dump(const struct parse_struct *pinfo,
const char *data,
unsigned indent);
const struct parse_struct *pinfo: is generated at compile time by genstruct, and is located in the output to that command.
const char *data: is the data to dump to the string representation (simply cast your structure to a char * before passing it.
unsigned indent: is the starting indent for ease of recursive calling. Set it to zero.
Returns: a string representation of the structure.
The most interesting thing here is the pinfo structure which is the first argument to this gen_dump function. The pinfo_test1 in this example looks like:
static const struct parse_struct pinfo_test1[] = {
{"foo", 0, sizeof(char), offsetof(struct test1, foo), 100, NULL,
0, gen_dump_char, gen_parse_char},
{"foo2", 1, sizeof(char), offsetof(struct test1, foo2), 20, NULL,
0, gen_dump_char, gen_parse_char},
{"xlen", 0, sizeof(int), offsetof(struct test1, xlen), 0, NULL,
0, gen_dump_int, gen_parse_int},
{"iarray", 1, sizeof(int), offsetof(struct test1, iarray), 0,
"xlen", 0, gen_dump_int, gen_parse_int},
// ... and so on ...
{NULL, 0, 0, 0, 0, NULL, 0, NULL, NULL}};
This table might seem a bit daunting at first, but readers need to remember that they're not expected to be able to read, generate, or use these tables. They are created solely for the use of genstruct.
The output of the gen_dump function call will be something like:
foo = {hello foo}
foo2 = 1:{foo2 \7d you}, 2:{foo2 you 2}
xlen = 6
iarray = 0:9, 1:4, 2:3, 3:9, 4:7
slen = 3
strings = 0:{test string 48}, 1:{test string 69}, 2:{test string 36}
s2 = 2:{t2 string 0}, 3:{t2 string 74}
d1 = 3.5
d3 = -7
test2 = {
x1 = 4
foo = {hello \7b there}
fstring = {blah 1}
dlen = 12
dfoo = {q\a9\08z\faO\ca\e3\1d\b2M\88}
fvalue = APPLE
next = {
x1 = 5
foo = {hello \7b there}
fstring = {blah 1}
dlen = 28
dfoo = {\e8\8f\dc\1c\0e\c7)'\ea\da\07e\ca\042\ce\078?\b0@\ba\ab\90\84\8e\ad6}
fvalue = APPLE
next = {
x1 = 6
foo = {hello \7b there}
fstring = {blah 1}
dlen = 27
dfoo = {5r\c3\c4O\e0\d2\16 \f9\01\e3\01f\ad\05\98\7b^L\d0\bb\bd\11uh\a1\f9}
fvalue = APPLE
next = {
x1 = 7
foo = {hello \7b there}
fstring = {blah 1}
dlen = 14
dfoo = {N/\d1\83\a2\94G\f1t\1a\07\7d\13\08}
fvalue = APPLE
}
}
}
}
This string representation can then be stored for later use.
The other side of the equation
The only reason you would use a package such as Data::Dumper or genstruct is so that you can read the information back in later. This is done with the eval function in perl. genstruct's equivalent is gen_parse, which takes your string representation and recreates the data structures as stored. The arguments to gen_parse are:
int gen_parse(const struct parse_struct *pinfo,
char *data,
const char *str0);
const struct parse_struct *pinfo: is the same parse structure that was used in the gen_dump call.
char *data: is a pointer to the location that the structure should be created at. This memory should already have been allocated (for the main structure).
const char *str0: the string representation to use.
Returns: non zero if there was an error.
A sample usage is:
char *s;
struct test1 t1, t2;
// ... we need to populate t1 with data here ...
s = gen_dump(pinfo_test1, (char *) &t1, 0);
memset(&t2, 0, sizeof(t2));
if(gen_parse(pinfo_test1, (char *) &t2, s) != 0){
printf("Parse failed!\n");
exit(1);
}
Genstruct pros and cons
Advantages of the Samba solution to this problem are:
Fewer database accesses
MORE?
However, the disadvantages of this solution are:
Requiring pre processing of source code with a perl script
The computational effort expended in data serialization
MORE?
Conclusion
This paper has described trivsql, a SQL engine which can be embedded into your code. This code is still a work in progress, and updates on it's development can be found at http://www.stillhq.com
Code listings
All the code included here is available for download from http://www.stillhq.com.
SQL lexer
The lexer for my SQL implementation is:
%{
#include "parser.h"
#undef YY_INPUT
#define YY_INPUT(b, r, ms) (r = trivsql_gettext(b, ms))
%}
%%
[Ss][Ee][Ll][Ee][Cc][Tt] { return SELECT; }
[Cc][Rr][Ee][Aa][Tt][Ee] { return CREATE; }
[Ii][Nn][Ss][Ee][Rr][Tt] { return INSERT; }
[Vv][Aa][Ll][Uu][Ee][Ss] { return VALUES; }
[Ii][Nn][Tt][Oo] { return INTO; }
[Tt][Aa][Bb][Ll][Ee] { return TABLE; }
[Ff][Rr][Oo][Mm] { return FROM; }
[Ww][Hh][Ee][Rr][Ee] { return WHERE; }
[Ll][Ii][Kk][Ee] { return LIKE; }
[Aa][Ll][Tt][Ee][Rr] { return ALTER; }
[Aa][Dd][Dd] { return ADD; }
[Cc][Oo][Ll][Uu][Mm][Nn] { return COLUMN; }
[Uu][Pp][Dd][Aa][Tt][Ee] { return UPDATE; }
[Ss][Ee][Tt] { return SET; }
[Aa][Nn][Dd] { return AND; }
[Oo][Rr] { return OR; }
[a-zA-Z0-9%\-]+ { yylval = trivsql_xsnprintf("%s", yytext); return STRING; }
[ \t\r\n] { }
. { return yytext[0]; }
%%
Code: /home/mikal/opensource/trivsql/lexer.l
I am not completely happy with this implementation. My main objection is the way I have implemented case insensitivity, although I am not aware of a better method.
SQL grammar
The grammar for my SQL implementation is:
%{
#include <stdio.h>
#include "trivsql.h"
#define YYERROR_VERBOSE 1
trivsql_state *gState;
%}
%token CREATE TABLE
%token INSERT VALUES INTO
%token SELECT FROM STRING WHERE LIKE
%token ALTER ADD COLUMN
%token UPDATE SET
%token AND OR
%%
sql : create sql | insert sql | sel sql | alt sql | upd sql
|
;
create : CREATE TABLE STRING '(' colvalspec ')' ';'
{ gState->rs = trivsql_makers((char *) $3); trivsql_docreate((char *) $3, (char *) $5); }
;
insert : INSERT INTO STRING '(' colvalspec ')' VALUES '(' colvalspec ')' ';'
{ gState->rs = trivsql_makers((char *) $3); trivsql_checktable((char *) $3, gState->rs); if(gState->rs->errno == TRIVSQL_FALSE){trivsql_doinsert((char *) $3, (char *) $5, (char *) $9);}}
;
sel : SELECT cvsaster FROM STRING { gState->rs = trivsql_makers((char *) $4); trivsql_xfree(gState->table); gState->table = trivsql_xsnprintf("%s", $4); } wsel ';'
{ trivsql_checktable((char *) $4, gState->rs); if(gState->rs->errno == TRIVSQL_FALSE){trivsql_doselect((char *) $4, (char *) $2);}}
;
alt : ALTER STRING ADD COLUMN STRING ';'
{ gState->rs = trivsql_makers((char *) $2); trivsql_checktable((char *) $2, gState->rs); if(gState->rs->errno == TRIVSQL_FALSE){trivsql_doalter((char *) $2, (char *) $5);}}
;
upd : UPDATE STRING SET STRING '=' str { gState->rs = trivsql_makers((char *) $2); trivsql_xfree(gState->table); gState->table = trivsql_xsnprintf("%s", $2); } wsel ';'
{trivsql_checktable((char *) $2, gState->rs); if(gState->rs->errno == TRIVSQL_FALSE){trivsql_doselect((char *) $2, (char *) $4); trivsql_updaters(gState, gState->rs, (char *) $4, (char *) $6);}}
;
cvsaster : colvalspec { $$ = trivsql_xsnprintf("%s", (char *) $1); }
| '*' { $$ = trivsql_xsnprintf("*"); }
;
colvalspec : str ',' colvalspec { $$ = trivsql_xsnprintf("%s;%s", (char *) $1, (char *) $3); }
| str { $$ = trivsql_xsnprintf("%s", (char *) $1); }
;
wsel : WHERE selector { gState->seltree = $2; }
|
;
selector : str '=' str { $$ = trivsql_makesel(trivsql_selequal, $1, $3); }
| str LIKE str { $$ = trivsql_makesel(trivsql_sellike, $1, $3); }
| selector AND selector { $$ = trivsql_makeslr(trivsql_seland, $1, $3); }
| selector OR selector { $$ = trivsql_makeslr(trivsql_seland, $1, $3); }
| '(' selector ')' { $$ = $2; }
| { $$ = NULL; }
;
str : STRING { $$ = $1; }
| '\'' STRING '\'' { $$ = $2; }
;
%%
int yyerror(char *s){
printf("\nsql parsing error: %s\n", s);
exit(42);
}
Code: /home/mikal/opensource/trivsql/parser.y
Automake Makefile.am
The following file is used to automatically generate a Makefile, in conjunction with autoconf.
lib_LTLIBRARIES = libtrivsql.la
noinst_PROGRAMS = sample
libtrivsql_la_SOURCES = parser.y lexer.l tdb.c spinlock.c selectors.c interface.c internal.c
include_HEADERS = trivsql.h trivsql_tdb.h trivsql_spinlock.h
sample_SOURCES = example.c
sample_LDADD = -ltrivsql -L.libs
YFLAGS = -d
Code: /home/mikal/opensource/trivsql/Makefile.am
Autoconf configure.in
The following file is used to automatically generate a configure script, which will build a Makefile appropriate to the build host.
dnl Process this file with autoconf to produce a configure script.
AC_INIT(lexer.l)
AC_CONFIG_AUX_DIR(config)
AC_REVISION
AM_INIT_AUTOMAKE(trivsql, 0.4.1)
AM_MAINTAINER_MODE
dnl Libtool
AC_LIBTOOL_WIN32_DLL
AC_LIBTOOL_DLOPEN
dnl Checks for programs.
AC_PROG_CC
AM_PROG_LEX
AC_PROG_YACC
AC_PROG_INSTALL
AC_PROG_RANLIB
AM_PROG_LIBTOOL
dnl Checks for libraries.
dnl The syntax is library name, function, action if found, action if not found
dnl We just use the default if found action which adds -l<lib> to the LIBS var
dnl and #defined HAVE_LIB<lib>
dnl -lm:
AC_CHECK_LIB(m, atan)
dnl -lfl:
AC_CHECK_LIB(fl, yywrap)
dnl Checks for header files.
dnl STDC checks for stdlib.h stdarg.h string.h and float.h
AC_HEADER_STDC
dnl Checks for typedefs, structures, and compiler characteristics.
dnl Checks for library functions.
AC_OUTPUT(Makefile)
Code: /home/mikal/opensource/trivsql/configure.in
C source code
The following files implement trivsql and tdb...
#include "trivsql_tdb.h"
#include "trivsql_spinlock.h"
#ifndef TRIVSQL_HEADER
#define TRIVSQL_HEADER
#ifdef __cplusplus
extern "C"
{
#endif
#define TRIVSQL_FALSE 0
#define TRIVSQL_TRUE 1
#define TRIVSQL_NOSUCHTABLE 2
#define TRIVSQL_BADVALUES 3
#define TRIVSQL_MEMORYERROR 4
#define TRIVSQL_TDBNULLKEY 5
#define TRIVSQL_TDBNULLDATA 6
#define TRIVSQL_TDBSTOREERROR 7
#define TRIVSQL_NOSUCHCOLUMN 8
#define TRIVSQL_NOROWSTOUPDATE 9
#define TRIVSQL_BADSELCOLARG 10
#define TRIVSQL_DBOPENFAIL 11
#define SELTRUE 1
#define SELFALSE 0
typedef int (*trivsql_selectorfunc) (char *arg1, char *arg2);
typedef struct trivsql_internal_seltreenode
{
char *selArgOne;
int selColOne;
char *selArgTwo;
int selColTwo;
trivsql_selectorfunc selector;
struct trivsql_internal_seltreenode *left, *right;
} trivsql_seltreenode;
typedef struct trivsql_internal_col
{
char *val;
char *key;
struct trivsql_internal_col *next;
} trivsql_col;
typedef struct trivsql_internal_row
{
trivsql_col *cols;
struct trivsql_internal_row *next, *prev;
} trivsql_row;
typedef struct trivsql_internal_rs
{
int numCols;
int numRows;
trivsql_row *rows;
trivsql_row *currentRow;
char *tname;
char *cols;
int errno;
char *errstring;
} trivsql_recordset;
typedef struct trivsql_internal_state
{
TDB_CONTEXT *db;
trivsql_recordset *rs;
trivsql_seltreenode *seltree;
char *table;
} trivsql_state;
// Internal functions
trivsql_state *trivsql_init(char *);
void trivsql_docreate(char *, char *);
void trivsql_doinsert(char *, char *, char *);
void trivsql_doselect(char *, char *);
void trivsql_doalter(char *, char *);
int *trivsql_parsecols(char *, char *, int *);
int trivsql_findcol(char *, char *, char *);
void trivsql_addrow(trivsql_recordset *, char *, int, int *);
char *trivsql_getallcolumns(char *);
trivsql_recordset *trivsql_makers();
trivsql_seltreenode *trivsql_makest();
trivsql_seltreenode* trivsql_makesel(trivsql_selectorfunc, char *, char *);
trivsql_seltreenode* trivsql_makeslr(trivsql_selectorfunc,
trivsql_seltreenode *,
trivsql_seltreenode *);
int trivsql_executeselector(trivsql_seltreenode *, int);
void trivsql_checktable(char *, trivsql_recordset *);
void *trivsql_xmalloc(size_t);
void trivsql_dbwrite(trivsql_state *, char *, char *);
char *trivsql_dbread(trivsql_state *, char *);
char *trivsql_xsnprintf(char *, ...);
void trivsql_xfree(void *);
void *trivsql_xrealloc(void *, size_t);
int trivsql_min(int, int);
int trivsql_initok(trivsql_state *);
// Selectors
int trivsql_selequal(char *, char *);
int trivsql_sellike(char *, char *);
int trivsql_selor(int, int);
int trivsql_seland(int, int);
// Interface methods
trivsql_state *trivsql_opendb(char *);
trivsql_recordset *trivsql_execute(trivsql_state *, char *);
int trivsql_gettext(char *, int);
void trivsql_displayrs(trivsql_recordset *);
void trivsql_rsmovefirst(trivsql_recordset *);
void trivsql_rsmovenext(trivsql_recordset *);
int trivsql_rseof(trivsql_recordset *);
int trivsql_rsbof(trivsql_recordset *);
char *trivsql_rsfield(trivsql_recordset *, int);
void trivsql_updaters(trivsql_state *, trivsql_recordset *, char *, char *);
void trivsql_rsupdatefield(trivsql_state *, trivsql_recordset *,
int, char *newval);
#ifdef __cplusplus
}
#endif
#endif
Code: /home/mikal/opensource/trivsql/trivsql.h
#include "trivsql.h"
char *gTrivData = NULL;
int gTrivInset;
extern trivsql_state *gState;
// Interface to the SQL database
trivsql_state *trivsql_opendb(char *path){
return trivsql_init(path);
}
trivsql_recordset *trivsql_execute(trivsql_state *state, char *sql){
trivsql_xfree(gTrivData);
gTrivData = trivsql_xsnprintf("%s", sql);
gTrivInset = 0;
trivsql_xfree(state->rs);
state->rs = NULL;
gState = state;
yyparse();
return gState->rs;
}
int trivsql_gettext(char *buffer, int maxlen){
int size;
// Determine the maximum size to return
size = trivsql_min(maxlen, strlen(gTrivData) - gTrivInset);
if(size > 0){
memcpy(buffer, gTrivData + gTrivInset, size);
gTrivInset += size;
}
return size;
}
void trivsql_displayrs(trivsql_recordset *rs){
int i, col;
char *t, *u, *c, *localCols;
// Was there an error?
switch(rs->errno){
case TRIVSQL_FALSE:
printf("This statement produced no results.\n");
return;
case TRIVSQL_TRUE:
break;
default:
printf("There was an error processing this statement (%d).\n",
rs->errno);
if(rs->errstring != NULL)
printf("trivsql engine reported: %s\n", rs->errstring);
return;
}
// Print the header line
printf("\n=");
for(i = 0; i < rs->numCols; i++){
printf("===============");
}
printf("\n|");
// Print out the column names
localCols = trivsql_xsnprintf("%s", rs->cols);
c = strtok(localCols, ";");
while(c != NULL){
printf(" %-12s |", c);
c = strtok(NULL, ";");
}
printf("\n=");
for(i = 0; i < rs->numCols; i++){
printf("===============");
}
printf("\n");
// Print out the values we have found
trivsql_rsmovefirst(rs);
while(trivsql_rseof(rs) != TRIVSQL_TRUE){
for(i = 0; i < rs->numCols; i++)
printf("| %-12s ", trivsql_rsfield(rs, i));
printf("|\n-");
for(i = 0; i < rs->numCols; i++){
printf("---------------");
}
printf("\n");
trivsql_rsmovenext(rs);
}
printf("\n");
printf("Select returned %d rows of %d columns\n",
rs->numRows, rs->numCols);
}
void trivsql_rsmovefirst(trivsql_recordset *rs){
rs->currentRow = rs->rows;
}
void trivsql_rsmovenext(trivsql_recordset *rs){
if(rs->currentRow->next != NULL)
rs->currentRow = rs->currentRow->next;
}
int trivsql_rseof(trivsql_recordset *rs){
if(rs->errno != TRIVSQL_TRUE) return TRIVSQL_TRUE;
return rs->currentRow->next == NULL ? TRIVSQL_TRUE : TRIVSQL_FALSE;
}
int trivsql_rsbof(trivsql_recordset *rs){
return rs->currentRow == rs->rows ? TRIVSQL_TRUE : TRIVSQL_FALSE;
}
char *trivsql_rsfield(trivsql_recordset *rs, int colnum){
int count;
trivsql_col *theCol;
count = 0;
theCol = rs->currentRow->cols;
while((theCol->next != NULL) && (count < colnum)){
theCol = theCol->next;
count++;
}
return theCol->val;
}
void trivsql_updaters(trivsql_state *state, trivsql_recordset *rs,
char *col, char *newval){
// Was there an error?
switch(rs->errno){
case TRIVSQL_FALSE:
gState->rs->errno = TRIVSQL_NOROWSTOUPDATE;
return;
case TRIVSQL_TRUE:
break;
default:
return;
}
// For the moment we assume there is only one column
trivsql_rsmovefirst(rs);
while(trivsql_rseof(rs) != TRIVSQL_TRUE){
trivsql_rsupdatefield(state, rs, 0, newval);
trivsql_rsmovenext(rs);
}
}
// NOTE: The existing recordset is not updated -- you need to reselect
void trivsql_rsupdatefield(trivsql_state *state, trivsql_recordset *rs,
int colnum, char *newval){
int count;
trivsql_col *theCol;
count = 0;
theCol = rs->currentRow->cols;
while((theCol->next != NULL) && (count < colnum)){
theCol = theCol->next;
count++;
}
trivsql_dbwrite(state, theCol->key, newval);
}
Code: /home/mikal/opensource/trivsql/interface.c
#include "trivsql.h"
#include <stdarg.h>
extern trivsql_state *gState;
const char *trivsql_version = VERSION;
trivsql_state *trivsql_init(char *filename){
trivsql_state *state;
state = (trivsql_state *) trivsql_xmalloc(sizeof(trivsql_state));
state->db = tdb_open(filename, 0, 0, O_RDWR | O_CREAT, 0600);
state->rs = NULL;
state->seltree = NULL;
state->table = NULL;
// We write the version of trivsql we used into a tag in the tdb for
// debugging
if(state->db != NULL)
trivsql_dbwrite(state, "trivsql_lastversion", trivsql_version);
return state;
}
void trivsql_docreate(char *tname, char *cols)
{
char *t;
char *u;
int colCount = 0;
t = trivsql_xsnprintf("trivsql_%s_numrows", tname);
trivsql_dbwrite(gState, t, "0");
trivsql_xfree(t);
u = strtok(cols, ";");
while(u != NULL){
t = trivsql_xsnprintf("trivsql_%s_col%d", tname, colCount);
trivsql_dbwrite(gState, t, u);
trivsql_xfree(t);
colCount++;
u = strtok(NULL, ";");
}
t = trivsql_xsnprintf("trivsql_%s_numcols", tname);
u = trivsql_xsnprintf("%d", colCount);
trivsql_dbwrite(gState, t, u);
trivsql_xfree(t);
trivsql_xfree(u);
}
void trivsql_doinsert(char *tname, char *cols, char *vals){
char *t, *u, *c;
int rowCount, i, col, numCols;
int *colNumbers;
if((rowCount = trivsql_getrowcount(tname)) == -1){
return;
}
// Get ready for columns
if((colNumbers = trivsql_parsecols(tname, cols, &numCols)) == NULL){
return;
}
// How we have the right number of values?
col = 1;
for(i = 0; i < strlen(vals); i++)
if(vals[i] == ';')
col++;
if(col != numCols){
gState->rs->errno = TRIVSQL_BADVALUES;
return;
}
// Save each column value
c = strtok(vals, ";");
col = 0;
while(c != NULL){
t = trivsql_xsnprintf("trivsql_%s_col%drow%d", tname, colNumbers[col], rowCount);
trivsql_dbwrite(gState, t, c);
trivsql_xfree(t);
c = strtok(NULL, ";");
col++;
}
// And we should keep count of how many of the rows are in the table
t = trivsql_xsnprintf("trivsql_%s_numrows", tname);
u = trivsql_xsnprintf("%d", rowCount + 1);
trivsql_dbwrite(gState, t, u);
trivsql_xfree(t);
trivsql_xfree(u);
}
void trivsql_doselect(char *tname, char *cols){
int *colNumbers;
int row, rowCount, numCols;
char *t, *u, *localCols;
// If the columns list is '*', substitute a list of all the columns
if(strcmp(cols, "*") == 0)
localCols = trivsql_getallcolumns(tname);
else
localCols = cols;
// Get ready for columns
if((colNumbers = trivsql_parsecols(tname, localCols, &numCols)) == NULL){
return;
}
// Populate recordset
gState->rs->numCols = numCols;
gState->rs->cols = trivsql_xsnprintf("%s", localCols);
gState->rs->errno = TRIVSQL_TRUE;
// Decide what rows on the table match the select condition
if((rowCount = trivsql_getrowcount(tname)) == -1){
return;
}
for(row = 0; row < rowCount; row++){
if(trivsql_executeselector(gState->seltree, row) == SELTRUE)
trivsql_addrow(gState->rs, tname, row, colNumbers);
}
}
void trivsql_doalter(char *tname, char *cols)
{
char *t;
char *u;
int colCount = 0;
t = trivsql_xsnprintf("trivsql_%s_numcols", tname);
u = trivsql_dbread(gState, t);
if(u == NULL) colCount = 0;
else colCount = atoi(u);
trivsql_xfree(t);
trivsql_xfree(u);
// Add the column
t = trivsql_xsnprintf("trivsql_%s_col%d", tname, colCount);
trivsql_dbwrite(gState, t, cols);
trivsql_xfree(t);
colCount++;
t = trivsql_xsnprintf("trivsql_%s_numcols", tname);
u = trivsql_xsnprintf("%d", colCount);
trivsql_dbwrite(gState, t, u);
trivsql_xfree(t);
trivsql_xfree(u);
}
void *
trivsql_xmalloc (size_t size)
{
void *buffer;
if ((buffer = malloc (size)) == NULL)
{
// todo_mikal: improve this
fprintf(stderr, "trivsql memory allocation error");
exit(42);
}
return buffer;
}
void
trivsql_dbwrite (trivsql_state *state, char *key, char *value)
{
TDB_DATA dbkey, dbdata;
if (key == NULL){
gState->rs->errno = TRIVSQL_TDBNULLKEY;
return;
}
if (value == NULL){
gState->rs->errno = TRIVSQL_TDBNULLDATA;
return;
}
// We need to build the structures for the TDB call
dbkey.dptr = key;
dbkey.dsize = strlen (key) + 1;
dbdata.dptr = value;
dbdata.dsize = strlen (value) + 1;
if (tdb_store (state->db, dbkey, dbdata, TDB_REPLACE) != 0)
{
gState->rs->errno = TRIVSQL_TDBSTOREERROR;
return;
}
}
char *
trivsql_dbread (trivsql_state *state, char *key)
{
TDB_DATA dbkey, dbdata;
if (key == NULL){
gState->rs->errno = TRIVSQL_TDBNULLKEY;
return NULL;
}
// We need to build the structures for the TDB call
dbkey.dptr = key;
dbkey.dsize = strlen (key) + 1;
dbdata = tdb_fetch (state->db, dbkey);
return dbdata.dptr;
}
char *
trivsql_xsnprintf (char *format, ...)
{
char *output = NULL;
int size, result;
va_list ap;
/* We start with the size of the format string as a guess */
size = strlen (format);
va_start (ap, format);
while (1)
{
output = (char *) trivsql_xrealloc (output, size);
result = vsnprintf (output, size, format, ap);
if (result == -1)
{
/* Up to glibc 2.0.6 and Microsoft's implementation */
size += 100;
}
else
{
/* Check if we are done */
if (result < size)
break;
/* Glibc from now on */
size = result + 1;
}
}
va_end (ap);
return output;
}
void
trivsql_xfree (void *memory)
{
if (memory != NULL)
free(memory);
}
void *
trivsql_xrealloc (void *memory, size_t size)
{
void *buffer;
if ((buffer = realloc (memory, size)) == NULL)
{
fprintf(stderr, "Realloc of memory failed");
exit(42);
}
return buffer;
}
int *trivsql_parsecols(char *tname, char *cols, int *numCols){
int i, col;
int *colNumbers = NULL;
char *t, *u, *coltmp, *c;
// How many columns do we have?
*numCols = 1;
for(i = 0; i < strlen(cols); i++)
if(cols[i] == ';')
(*numCols)++;
coltmp = trivsql_xsnprintf("%s", cols);
colNumbers = trivsql_xmalloc(sizeof(int) * (*numCols));
// Determine that the named columns exist
col = 0;
c = strtok(coltmp, ";");
while(c != NULL){
i = 0;
while(1){
t = trivsql_xsnprintf("trivsql_%s_col%d", tname, i);
u = trivsql_dbread(gState, t);
if(u == NULL){
trivsql_xfree(t);
// todo_mikal: this make break if used during a selection...
gState->rs->errno = TRIVSQL_NOSUCHCOLUMN;
gState->rs->errstring = trivsql_xsnprintf("The column \"%s\" does not exist in the table \"%s\" (search inset is %d).", c, tname, i);
return NULL;
}
else if(strcmp(u, c) == 0){
trivsql_xfree(t);
trivsql_xfree(u);
break;
}
trivsql_xfree(t);
trivsql_xfree(u);
i++;
}
colNumbers[col] = i;
c = strtok(NULL, ";");
col++;
}
return colNumbers;
}
int trivsql_findcol(char *tname, char *cols, char *col){
char *t, *u, *coltmp, *c;
int colNum;
coltmp = trivsql_xsnprintf("%s", cols);
colNum = 0;
// Determine that the named columns exist
c = strtok(coltmp, ";");
while(c != NULL){
if(strcmp(c, col) == 0)
return colNum;
c = strtok(NULL, ";");
colNum++;
}
return -1;
}
int trivsql_getrowcount(char *tname){
char *t, *u;
int rowCount;
// Determine if the table exists, and if so how many rows it has
t = trivsql_xsnprintf("trivsql_%s_numrows", tname);
u = trivsql_dbread(gState, t);
if(u == NULL){
gState->rs->errno = TRIVSQL_NOSUCHTABLE;
gState->rs->errstring = trivsql_xsnprintf("Could not determine the row count for the table \"%s\" because the table does not exist", tname);
return -1;
}
rowCount = atoi(u);
trivsql_xfree(u);
trivsql_xfree(t);
return rowCount;
}
void trivsql_addrow(trivsql_recordset *rs, char *tname, int row, int *cols){
char *t;
int colCount;
trivsql_row *theRow;
trivsql_col *theCol;
// Make space for the new row
rs->numRows++;
theRow = rs->rows;
while(theRow->next != NULL)
theRow = theRow->next;
theRow->next = trivsql_xmalloc(sizeof(trivsql_row));
theRow->next->next = NULL;
theRow->cols = trivsql_xmalloc(sizeof(trivsql_col));
theRow->cols->next = NULL;
theCol = theRow->cols;
// Get the row
for(colCount = 0; colCount < rs->numCols; colCount++){
t = trivsql_xsnprintf("trivsql_%s_col%drow%d", tname, cols[colCount], row);
theCol->val = trivsql_dbread(gState, t);
theCol->key = t;
theCol->next = trivsql_xmalloc(sizeof(trivsql_col));
theCol->next->next = NULL;
theCol = theCol->next;
}
}
char *trivsql_getallcolumns(char *tname)
{
char *t, *u, *retVal, *retVal2;
int i, maxCols;
t = trivsql_xsnprintf("trivsql_%s_numcols", tname);
u = trivsql_dbread(gState, t);
if(u == NULL) maxCols = 0;
else maxCols = atoi(u);
trivsql_xfree(t);
trivsql_xfree(u);
retVal = trivsql_xsnprintf("");
for(i = 0; i < maxCols; i++){
t = trivsql_xsnprintf("trivsql_%s_col%d", tname, i);
u = trivsql_dbread(gState, t);
if(strcmp(retVal, "") != 0)
retVal2 = trivsql_xsnprintf("%s;%s", retVal, u);
else
retVal2 = trivsql_xsnprintf("%s", u);
trivsql_xfree(t);
trivsql_xfree(u);
trivsql_xfree(retVal);
retVal = retVal2;
}
return retVal;
}
int trivsql_min(int a, int b){
if(a > b) return b;
return a;
}
void trivsql_checktable(char *tname, trivsql_recordset *rs){
char *t, *u;
t = trivsql_xsnprintf("trivsql_%s_numrows", tname);
u = trivsql_dbread(gState, t);
trivsql_xfree(t);
if(u == NULL){
(*rs).errno = TRIVSQL_NOSUCHTABLE;
(*rs).errstring = trivsql_xsnprintf("Existance check for the table \"%s\" determined that the table does not exist.", tname);
}
}
trivsql_recordset* trivsql_makers(char *tname){
trivsql_recordset *rrs;
// Build the recordset
rrs = trivsql_xmalloc(sizeof(trivsql_recordset));
rrs->rows = trivsql_xmalloc(sizeof(trivsql_row));
rrs->rows->next = NULL;
rrs->rows->cols = NULL;
rrs->numCols = 0;
rrs->numRows = 0;
rrs->tname = trivsql_xsnprintf("%s", tname);
rrs->currentRow = rrs->rows;
rrs->errno = TRIVSQL_FALSE;
rrs->errstring = NULL;
rrs->cols = NULL;
if(gState->db == NULL){
rrs->errno = TRIVSQL_DBOPENFAIL;
}
return rrs;
}
trivsql_seltreenode* trivsql_makest(){
trivsql_seltreenode *rst;
// Build the recordset
rst = trivsql_xmalloc(sizeof(trivsql_seltreenode));
rst->selArgOne = NULL;
rst->selColOne = -1;
rst->selArgTwo = NULL;
rst->selColTwo = -1;
rst->selector = NULL;
rst->left = NULL;
rst->right = NULL;
return rst;
}
trivsql_seltreenode* trivsql_makesel(trivsql_selectorfunc func, char *a1,
char *a2){
int *colNumbers, numCols;
trivsql_seltreenode *tst = trivsql_makest();
tst->selector = func;
if((colNumbers = trivsql_parsecols(gState->table, a1, &numCols)) == NULL){
tst->selArgOne = a1;
gState->rs->errno = TRIVSQL_FALSE;
}
else{
tst->selColOne = colNumbers[0];
}
trivsql_xfree(colNumbers);
if((colNumbers = trivsql_parsecols(gState->table, a2, &numCols)) == NULL){
tst->selArgTwo = a2;
gState->rs->errno = TRIVSQL_FALSE;
}
else{
tst->selColTwo = colNumbers[0];
}
trivsql_xfree(colNumbers);
return tst;
}
trivsql_seltreenode* trivsql_makeslr(trivsql_selectorfunc func,
trivsql_seltreenode *left,
trivsql_seltreenode *right){
trivsql_seltreenode* tst = trivsql_makest();
tst->selector = func;
tst->left = left;
tst->right = right;
}
int trivsql_executeselector(trivsql_seltreenode* node, int row){
char *a1, *a2, *t;
if(node == NULL)
return SELTRUE;
if(((node->selArgOne != NULL) || (node->selColOne != -1)) &&
((node->selArgTwo != NULL) || (node->selColTwo != -1))){
if(node->selColOne == -1)
a1 = node->selArgOne;
else{
t = trivsql_xsnprintf("trivsql_%s_col%drow%d", gState->table,
node->selColOne, row);
a1 = trivsql_dbread(gState, t);
trivsql_xfree(t);
if(a1 == NULL)
return SELFALSE;
}
if(node->selColTwo == -1)
a2 = node->selArgTwo;
else{
t = trivsql_xsnprintf("trivsql_%s_col%drow%d", gState->table,
node->selColTwo, row);
a2 = trivsql_dbread(gState, t);
trivsql_xfree(t);
if(a2 == NULL)
return SELFALSE;
}
return (node->selector)(a1, a2);
}
return (node->selector)(trivsql_executeselector(node->left, row),
trivsql_executeselector(node->right, row));
}
int trivsql_initok(trivsql_state *state){
if(state->db == NULL)
return TRIVSQL_FALSE;
return TRIVSQL_TRUE;
}
Code: /home/mikal/opensource/trivsql/internal.c
#include "trivsql.h"
#include <string.h>
int trivsql_selequal(char *arg1, char *arg2){
if(strcmp(arg1, arg2) == 0) return SELTRUE;
return SELFALSE;
}
// todo: implement %'s
int trivsql_sellike(char *arg1, char *arg2){
int front = 0, back = 0, ret;
char *sel, *str;
sel = strdup(arg2);
if(arg2[0] == '%'){
front = 1;
sel++;
}
if(arg2[strlen(arg2) - 1] == '%'){
back = 1;
sel[strlen(sel) - 1] = '\0';
}
printf("%d %d (%s)\n", front, back, sel);
if((str = strstr(arg1, sel)) != NULL){
if((front == 1) && (str == arg1))
ret = SELFALSE;
else
ret = SELTRUE;
}
else ret = SELFALSE;
//trivsql_xfree(sel);
return ret;
}
int trivsql_selor(int left, int right){
if(left == SELTRUE) return SELTRUE;
if(right == SELTRUE) return SELTRUE;
return SELFALSE;
}
int trivsql_seland(int left, int right){
if(left == SELFALSE) return SELFALSE;
if(right == SELFALSE) return SELFALSE;
return SELTRUE;
}
Code: /home/mikal/opensource/trivsql/selectors.c
#include <stdio.h>
#include "trivsql.h"
int main(int argc, char *argv[]){
trivsql_state *ourState;
trivsql_recordset *rs;
char cmd[1000];
if(argc != 2){
fprintf(stderr, "Please specify a db file\n");
exit(42);
}
ourState = trivsql_opendb(argv[1]);
if(trivsql_initok(ourState) != TRIVSQL_TRUE){
fprintf(stderr, "Database open failed\n");
exit(42);
}
while(fgets(cmd, 1000, stdin) != NULL){
rs = trivsql_execute(ourState, cmd);
if(rs != NULL) trivsql_displayrs(rs);
else printf("NULL recordset\n");
trivsql_xfree(rs);
}
}
Code: /home/mikal/opensource/trivsql/example.c
I haven't included the TDB code, as it is not strictly part of trivsql, and may be downloaded from http://www.samba.org