Committed the sqlite extension and documentation.

6 files changed, 485 insertions, 2 deletions

diff --git a/ChangeLog b/ChangeLog
index f5adabf..b9fb532 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -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
diff --git a/Makefile b/Makefile
index a31da4b..2216b1d 100644
--- a/Makefile
+++ b/Makefile
@@ -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
diff --git a/README b/README
index 77f41a3..ee5a42c 100644
--- a/README
+++ b/README
@@ -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
 --------------------------------------------------------------------------------
diff --git a/TODO b/TODO
index 429c04b..5002943 100644
--- a/TODO
+++ b/TODO
@@ -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;
+}