diff options
-rw-r--r-- | ChangeLog | 4 | ||||
-rw-r--r-- | Makefile | 4 | ||||
-rw-r--r-- | README | 40 | ||||
-rw-r--r-- | TODO | 1 | ||||
-rw-r--r-- | doc/Sqlite-Extension.txt | 155 | ||||
-rw-r--r-- | jim-sqlite.c | 283 |
6 files changed, 485 insertions, 2 deletions
@@ -1,3 +1,7 @@ +2005-04-02 12:14 antirez + + * ChangeLog, README, doc/AIO-Extension.txt: minor docs update + 2005-04-02 12:08 antirez * ChangeLog, Makefile, jim-stdlib-1.0.tcl, jim.c: Added a very @@ -74,6 +74,9 @@ jim-aio-1.0.so: jim-aio.xo jim-posix-1.0.so: jim-posix.xo $(LD) -G -z text -o $@ $< $(LIBS) -lc +jim-sqlite-1.0.so: jim-sqlite.xo + $(LD) -G -z text -o $@ $< $(LIBS) -lc -lsqlite + jim-sdl.xo: jim-sdl.c $(CC) `sdl-config --cflags` -I. $(CFLAGS) $(DEFS) -fPIC -c $< -o $@ @@ -85,6 +88,7 @@ jim: $(JIM_OBJECTS) $(CC) $(LDFLAGS) -o jim $(JIM_OBJECTS) $(LIBS) posix: jim-posix-1.0.so +sqlite: jim-sqlite-1.0.so aio: jim-aio-1.0.so aio-dll: jim-aio-1.0.dll sdl: jim-sdl-1.0.so @@ -143,13 +143,15 @@ This should compile Jim almost everywhere there is a decent ANSI-C compiler. EXTENSIONS -------------------------------------------------------------------------------- -JIM-POSIX +POSIX +===== This is the start of a library that should export to Jim useful bits of the POSIX API. For now there are just a few utility functions, but it's an example on how to write a simple library for Jim. -JIM-WIN32 +WIN32 +===== This is the start of a library that should export to Jim useful bits of the WIN32 API. Currently there is just one function that is used to call windows @@ -160,6 +162,40 @@ applications. For example run jim and try the extension with: You should see a notepad application running. +ANSI-I/O, SQLITE +================ + +There is documentation under the "doc" dictory about the "ANSI I/O" +and "SQLITE" extensions. + +SDL +=== + +The SDL extension is currently undocumented (work in progress), but +there is enought to start to play. That's an example script: + + package require sdl + + set xres 800 + set yres 800 + set s [sdl.screen $xres $yres] + + set i 0 + while 1 { + set x1 [rand $xres] + set y1 [rand $yres] + set x2 [rand $xres] + set y2 [rand $yres] + set rad [rand 40] + set r [rand 256] + set g [rand 256] + set b [rand 256] + $s fcircle $x1 $y1 $rad $r $g $b 200 + incr i + if {$i > 2000} {$s flip} + if {$i == 3000} exit + } + -------------------------------------------------------------------------------- HOW TO EMBED JIM INTO APPLICATIONS / HOW TO WRITE EXTENSIONS FOR JIM -------------------------------------------------------------------------------- @@ -69,6 +69,7 @@ IMPLEMENTATION ISSUES - *AssocData() function should allow to specify a delProc C function like in the Tcl API. When the interpreter is destroied all the delProc functions should be called to free the memory before to free the interpreter. +- Convert dicts from lists directly without to pass from the string repr. ERROR MESSAGES diff --git a/doc/Sqlite-Extension.txt b/doc/Sqlite-Extension.txt new file mode 100644 index 0000000..ceb3437 --- /dev/null +++ b/doc/Sqlite-Extension.txt @@ -0,0 +1,155 @@ +Jim Sqlite extension documentation. +Copyright 2005 Salvatore Sanfilippo <antirez@invece.org> + +$Id: Sqlite-Extension.txt,v 1.1 2005/04/02 21:35:33 antirez Exp $ + +Overview +~~~~~~~~ + +The Sqlite extension makes possible to work with sqlite (http://www.sqlite.org) +databases from Jim. SQLite is a small C library that implements a +self-contained, embeddable, zero-configuration SQL database engine. This +means it is perfect for embedded systems, and for stand-alone applications +that need the power of SQL without to use an external server like Mysql. + +Basic usage +~~~~~~~~~~~ + +The Sqlite extension exports an Object Based interface for databases. In order +to open a database use: + + set f [sqlite.open dbname] + +The [sqlite.open] command returns a db handle, that is a command name that +can be used to perform operations on the database. A real example: + + . set db [sqlite.open test.db] + sqlite.handle0 + . $db query "SELECT * from tbl1" + {one hello! two 10} {one goodbye two 20} + +In the second line the handle is used as a command name, followed +by the 'method' or 'subcommand' ("query" in the example), and the arguments. + +The query method +~~~~~~~~~~~~~~~~ + +The query method has the following signature: + + $db query SqlQuery ?args? + +The sql query may contain occurrences of "%s" that are substituted +in the actual query with the following arguments, quoted in order +to make sure that the query is correct even if this arguments contain +"'" characters. So for example it is possible to write: + + . $db query "SELECT * from tbl1 WHERE one='%s'" hello! + {one hello! two 10} + +Instead of hello! it is possible to use a string with embedded "'": + + . $db query "SELECT * from tbl1 WHERE one='%s'" a'b + (no matches - the empty list is returned) + +This does not work instead using the Tcl variable expansion in the string: + + . $db query "SELECT * from tbl1 WHERE one='$foo'" + Runtime error, file "?", line 1: + near "b": syntax error + +In order to obtain an actual '%' character in the query, there is just +to use two, like in "foo %% bar". This is the same as the [format] argument. + +Specification of query results +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +In one of the above examples, the following query was used: + + . $db query "SELECT * from tbl1" + {one hello! two 10} {one goodbye two 20} + +As you can see the result of a query is a list of lists. Every +element of the list represents a row, as a list of key/value pairs, +so actually every row is a Jim dictionary. + +The following example and generated output show how to take advantage +of this representation: + + . set res [$db query "SELECT * from tbl1"] + {one hello! two 10} {one goodbye two 20} + . foreach row $res {puts "One: $row(one), Two: $row(two)"} + One: hello!, Two: 10 + One: goodbye, Two: 20 + +To access every row sequentially is very simple, and field of a row +can be accessed using the $row(field) syntax. + +The close method +~~~~~~~~~~~~~~~~ + +In order to close the db, use the 'close' method that will have as side effect +to close the db and to remove the command associated with the db. +Just use: + + $db close + +Handling NULL values +~~~~~~~~~~~~~~~~~~~~ + +In the SQL language there is a special value NULL that is not the empty +string, so how to represent it in a typeless language like Tcl? +For default this extension will use the empty string, but it is possible +to specify a different string for the NULL value. + +In the above example there were two rows in the 'tbl1' table. Now +we can add usign the "sqlite" command line client another one with +a NULL value: + + sqlite> INSERT INTO tbl1 VALUES(NULL,30); + sqlite> .exit + +That's what the sqlite extension will return for default: + + . $db query "SELECT * from tbl1" + {one hello! two 10} {one goodbye two 20} {one {} two 30} + +As you can see in the last row, the NULL is represented as {}, that's +the empty string. Using the -null option of the 'query' command we +can change this default, and tell the sqlite extension to represent +the NULL value as a different string: + + . $db query -null <<NULL>> "SELECT * from tbl1" + {one hello! two 10} {one goodbye two 20} {one <<NULL>> two 30} + +This way if the emtpy string has some semantical value for your +dataset you can change it. + +Finding the ID of the last inserted row +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +This is as simple as: + + . $db lastid + 10 + +Number of rows changed by the most recent query +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +This is also very simple, there is just to use the 'changes' method +without arugments. + + . $db changes + 5 + +Note that if you drop an entire table the number of changes will +be reported as zero, because of details of the sqlite implementation. + +That's all, +Enjoy! +Salvatore Sanfilippo + +p.s. this extension is just the work of some hour thanks to the cool +clean C API that sqlite exports. Thanks to the author of sqlite for this +great work. + + diff --git a/jim-sqlite.c b/jim-sqlite.c new file mode 100644 index 0000000..77586ef --- /dev/null +++ b/jim-sqlite.c @@ -0,0 +1,283 @@ +/* Jim - Sqlite bindings + * Copyright 2005 Salvatore Sanfilippo <antirez@invece.org> + * + * $Id: jim-sqlite.c,v 1.1 2005/04/02 21:35:33 antirez Exp $ + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * A copy of the license is also included in the source distribution + * of Jim, as a TXT file name called LICENSE. + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +#include <stdio.h> +#include <string.h> +#include <sqlite.h> + +#define JIM_EXTENSION +#include "jim.h" + +#define SQLITE_CMD_LEN 128 + +typedef struct JimSqliteHandle { + sqlite *db; +} JimSqliteHandle; + +static void JimSqliteDelProc(Jim_Interp *interp, void *privData) +{ + JimSqliteHandle *sh = privData; + JIM_NOTUSED(interp); + + sqlite_close(sh->db); + Jim_Free(sh); +} + +static char *JimSqliteQuoteString(const char *str, int len, int *newLenPtr) +{ + int i, newLen, c = 0; + const char *s; + char *d, *buf; + + for (i = 0; i < len; i++) + if (str[i] == '\'') + c++; + newLen = len+c; + s = str; + d = buf = Jim_Alloc(newLen); + while (len--) { + if (*s == '\'') + *d++ = '\''; + *d++ = *s++; + } + *newLenPtr = newLen; + return buf; +} + +static Jim_Obj *JimSqliteFormatQuery(Jim_Interp *interp, Jim_Obj *fmtObjPtr, + int objc, Jim_Obj *const *objv) +{ + const char *fmt; + int fmtLen; + Jim_Obj *resObjPtr; + + fmt = Jim_GetString(fmtObjPtr, &fmtLen); + resObjPtr = Jim_NewStringObj(interp, "", 0); + while (fmtLen) { + const char *p = fmt; + char spec[2]; + + while (*fmt != '%' && fmtLen) { + fmt++; fmtLen--; + } + Jim_AppendString(interp, resObjPtr, p, fmt-p); + if (fmtLen == 0) + break; + fmt++; fmtLen--; /* skip '%' */ + if (*fmt != '%') { + if (objc == 0) { + Jim_FreeNewObj(interp, resObjPtr); + Jim_SetResultString(interp, + "not enough arguments for all format specifiers", -1); + return NULL; + } else { + objc--; + } + } + switch(*fmt) { + case 's': + { + const char *str; + char *quoted; + int len, newLen; + + str = Jim_GetString(objv[0], &len); + quoted = JimSqliteQuoteString(str, len, &newLen); + Jim_AppendString(interp, resObjPtr, quoted, newLen); + Jim_Free(quoted); + } + objv++; + break; + case '%': + Jim_AppendString(interp, resObjPtr, "%" , 1); + break; + default: + spec[1] = *fmt; spec[2] = '\0'; + Jim_FreeNewObj(interp, resObjPtr); + Jim_SetResult(interp, Jim_NewEmptyStringObj(interp)); + Jim_AppendStrings(interp, Jim_GetResult(interp), + "bad field specifier \"", spec, "\", ", + "only %%s and %%%% are validd", NULL); + return NULL; + } + fmt++; + fmtLen--; + } + return resObjPtr; +} + +/* Calls to [sqlite.open] create commands that are implemented by this + * C command. */ +static int JimSqliteHandlerCommand(Jim_Interp *interp, int argc, + Jim_Obj *const *argv) +{ + JimSqliteHandle *sh = Jim_CmdPrivData(interp); + int option; + const char *options[] = { + "close", "query", "lastid", "changes", NULL + }; + enum {OPT_CLOSE, OPT_QUERY, OPT_LASTID, OPT_CHANGES}; + + if (argc < 2) { + Jim_WrongNumArgs(interp, 1, argv, "method ?args ...?"); + return JIM_ERR; + } + if (Jim_GetEnum(interp, argv[1], options, &option, "Sqlite method", + JIM_ERRMSG) != JIM_OK) + return JIM_ERR; + /* CLOSE */ + if (option == OPT_CLOSE) { + if (argc != 2) { + Jim_WrongNumArgs(interp, 2, argv, ""); + return JIM_ERR; + } + Jim_DeleteCommand(interp, Jim_GetString(argv[0], NULL)); + return JIM_OK; + } else if (option == OPT_QUERY) { + /* QUERY */ + Jim_Obj *objPtr, *rowsListPtr; + sqlite_vm *vm; + char *errMsg; + const char *query, *tail, **values, **names; + int columns, rows; + char *nullstr; + + if (argc >= 4 && Jim_CompareStringImmediate(interp, argv[2], "-null")) { + nullstr = Jim_StrDup(Jim_GetString(argv[3], NULL)); + argv += 2; + argc -= 2; + } else { + nullstr = Jim_StrDup(""); + } + if (argc < 3) { + Jim_WrongNumArgs(interp, 2, argv, "query ?args?"); + Jim_Free(nullstr); + return JIM_ERR; + } + objPtr = JimSqliteFormatQuery(interp, argv[2], argc-3, argv+3); + if (objPtr == NULL) { + Jim_Free(nullstr); + return JIM_ERR; + } + query = Jim_GetString(objPtr, NULL); + Jim_IncrRefCount(objPtr); + /* Compile the query into VM code */ + if (sqlite_compile(sh->db, query, &tail, &vm, &errMsg) != SQLITE_OK) { + Jim_DecrRefCount(interp, objPtr); + Jim_SetResultString(interp, errMsg, -1); + sqlite_freemem(errMsg); + Jim_Free(nullstr); + return JIM_ERR; + } + Jim_DecrRefCount(interp, objPtr); /* query no longer needed. */ + /* Build a list of rows (that are lists in turn) */ + rowsListPtr = Jim_NewListObj(interp, NULL, 0); + Jim_IncrRefCount(rowsListPtr); + rows = 0; + while (sqlite_step(vm, &columns, &values, &names) == SQLITE_ROW) { + int i; + + objPtr = Jim_NewListObj(interp, NULL, 0); + for (i = 0; i < columns; i++) { + Jim_ListAppendElement(interp, objPtr, + Jim_NewStringObj(interp, names[i], -1)); + Jim_ListAppendElement(interp, objPtr, + Jim_NewStringObj(interp, + values[i] != NULL ? values[i] : nullstr, -1)); + } + Jim_ListAppendElement(interp, rowsListPtr, objPtr); + rows++; + } + /* Finalize */ + Jim_Free(nullstr); + if (sqlite_finalize(vm, &errMsg) != SQLITE_OK) { + Jim_DecrRefCount(interp, rowsListPtr); + Jim_SetResultString(interp, errMsg, -1); + sqlite_freemem(errMsg); + return JIM_ERR; + } + Jim_SetResult(interp, rowsListPtr); + Jim_DecrRefCount(interp, rowsListPtr); + } else if (option == OPT_LASTID) { + if (argc != 2) { + Jim_WrongNumArgs(interp, 2, argv, ""); + return JIM_ERR; + } + Jim_SetResult(interp, Jim_NewIntObj(interp, + sqlite_last_insert_rowid(sh->db))); + return JIM_OK; + } else if (option == OPT_CHANGES) { + if (argc != 2) { + Jim_WrongNumArgs(interp, 2, argv, ""); + return JIM_ERR; + } + Jim_SetResult(interp, Jim_NewIntObj(interp, + sqlite_changes(sh->db))); + return JIM_OK; + } + return JIM_OK; +} + +static int JimSqliteOpenCommand(Jim_Interp *interp, int argc, + Jim_Obj *const *argv) +{ + sqlite *db; + JimSqliteHandle *sh; + char buf[SQLITE_CMD_LEN], *errMsg; + Jim_Obj *objPtr; + long dbId; + + if (argc != 2) { + Jim_WrongNumArgs(interp, 1, argv, "dbname"); + return JIM_ERR; + } + db = sqlite_open(Jim_GetString(argv[1], NULL), 0, &errMsg); + if (db == NULL) { + Jim_SetResultString(interp, errMsg, -1); + sqlite_freemem(errMsg); + return JIM_ERR; + } + /* Get the next file id */ + if (Jim_EvalGlobal(interp, + "if {[catch {incr sqlite.dbId}]} {set sqlite.dbId 0}") != JIM_OK) + return JIM_ERR; + objPtr = Jim_GetVariableStr(interp, "sqlite.dbId", JIM_ERRMSG); + if (objPtr == NULL) return JIM_ERR; + if (Jim_GetLong(interp, objPtr, &dbId) != JIM_OK) return JIM_ERR; + + /* Create the file command */ + sh = Jim_Alloc(sizeof(*sh)); + sh->db = db; + sprintf(buf, "sqlite.handle%ld", dbId); + Jim_CreateCommand(interp, buf, JimSqliteHandlerCommand, sh, + JimSqliteDelProc); + Jim_SetResultString(interp, buf, -1); + return JIM_OK; +} + +int Jim_OnLoad(Jim_Interp *interp) +{ + Jim_InitExtension(interp); + if (Jim_PackageProvide(interp, "sqlite", "1.0", JIM_ERRMSG) != JIM_OK) + return JIM_ERR; + Jim_CreateCommand(interp, "sqlite.open", JimSqliteOpenCommand, NULL, NULL); + return JIM_OK; +} |