addons/SQLite3/source/IoSQLite3.c

   1 /*#io
   2 SQLite3 ioDoc(
   3                     docCopyright("Steve Dekorte", 2004)
   4                     docLicense("BSD revised")
   5                     docObject("SQLite3")
   6                     docDescription("""SQLite provides a embedded simple and fast (2x faster than PostgreSQL or MySQL) SQL database. See http://www.hwaci.com/sw/sqlite/ for details. It's SQL command set is described at http://www.hwaci.com/sw/sqlite/lang.html. SQLite was written by Dr. Richard Hipp who offers consulting services for custom modifications and support of SQLite. Example:
   7 <pre>
   8 db := SQLite clone
   9 db setPath("myDatabase.sqlite")
  10 db open
  11 db exec("CREATE TABLE Dbm (key, value)")
  12 db exec("CREATE INDEX DbmIndex ON Dbm (key)")
  13 db exec("INSERT INTO Dbm ('key', 'value') VALUES ('a', '123')")
  14 db exec("INSERT INTO Dbm ('key', 'value') VALUES ('a', 'efg')")
  15 rows := db exec("SELECT key, value FROM Dbm WHERE key='a'")
  16 db exec("DELETE FROM Dbm WHERE key='a'")
  17 rows := db exec("SELECT key, value FROM Dbm WHERE key='a'")
  18 db close
  19 </pre>""")
  20                     docCategory("Databases")
  21                     */
  22
  23 #include "IoSQLite3.h"
  24 #include "IoState.h"
  25 #include "IoNumber.h"
  26 #include "IoList.h"
  27 #include "IoMap.h"
  28
  29 typedef int (ResultRowCallback)(void *, int , char **, char **);
  30
  31 #define DATA(self) ((IoSQLite3Data *)IoObject_dataPointer(self))
  32
  33 /*
  34  static int IoSQLite3_resultRow(void *context, int argc, char **argv, char **azColName);
  35  static int IoSQLite3_busyHandler(void *context, const char *s, int n);
  36  */
  37
  38 IoTag *IoSQLite3_newTag(void *state)
  39 {
  40         IoTag *tag = IoTag_newWithName_("SQLite3");
  41         IoTag_state_(tag, state);
  42         IoTag_cloneFunc_(tag, (IoTagCloneFunc *)IoSQLite3_rawClone);
  43         IoTag_freeFunc_(tag, (IoTagFreeFunc *)IoSQLite3_free);
  44         IoTag_markFunc_(tag, (IoTagMarkFunc *)IoSQLite3_mark);
  45         return tag;
  46 }
  47
  48 IoSQLite3 *IoSQLite3_proto(void *state)
  49 {
  50         IoObject *self = IoObject_new(state);
  51         IoObject_tag_(self, IoSQLite3_newTag(state));
  52
  53         IoObject_setDataPointer_(self, calloc(1, sizeof(IoSQLite3Data)));
  54         DATA(self)->path = IOSYMBOL(".");
  55
  56         IoState_registerProtoWithFunc_(state, self, IoSQLite3_proto);
  57
  58         {
  59                 IoMethodTable methodTable[] = {
  60                 {"setPath", IoSQLite3_setPath},
  61                 {"path", IoSQLite3_path},
  62                 {"open", IoSQLite3_open},
  63                 {"close", IoSQLite3_close},
  64                 {"exec", IoSQLite3_exec},
  65                 {"error", IoSQLite3_errorMessage},
  66                 {"version", IoSQLite3_version},
  67                 {"setTimeoutSeconds", IoSQLite3_setTimeoutSeconds},
  68                 {"timeoutSeconds", IoSQLite3_timeoutSeconds},
  69                 {"rowsChangedCount", IoSQLite3_changes},
  70                 {"lastInsertRowId", IoSQLite3_lastInsertRowId},
  71                 {"tableNames", IoSQLite3_tableNames},
  72                 {"viewNames", IoSQLite3_viewNames},
  73
  74                 {"columnNamesOfTable", IoSQLite3_columnNamesOfTable},
  75                 {"debugOn", IoSQLite3_debugOn},
  76                 {"debugOff", IoSQLite3_debugOff},
  77                 {"isOpen", IoSQLite3_isOpen},
  78                 {"escapeString", IoSQLite3_escapeString},
  79                 {NULL, NULL},
  80                 };
  81                 IoObject_addMethodTable_(self, methodTable);
  82         }
  83         return self;
  84 }
  85
  86 IoSQLite3 *IoSQLite3_rawClone(IoSQLite3 *proto)
  87 {
  88         IoObject *self = IoObject_rawClonePrimitive(proto);
  89         IoObject_setDataPointer_(self, cpalloc(IoObject_dataPointer(proto), sizeof(IoSQLite3Data)));
  90         return self;
  91 }
  92
  93 /* ----------------------------------------------------------- */
  94
  95 IoSQLite3 *IoSQLite3_new(void *state)
  96 {
  97         IoObject *proto = IoState_protoWithInitFunction_(state, IoSQLite3_proto);
  98         return IOCLONE(proto);
  99 }
 100
 101 IoSQLite3 *IoSQLite3_newWithPath_(void *state, IoSymbol *path)
 102 {
 103         IoSQLite3 *self = IoSQLite3_new(state);
 104         DATA(self)->path = IOREF(path);
 105         return self;
 106 }
 107
 108 void IoSQLite3_free(IoSQLite3 *self)
 109 {
 110         if (DATA(self)->db) sqlite3_close(DATA(self)->db);
 111         free(IoObject_dataPointer(self));
 112 }
 113
 114 void IoSQLite3_mark(IoSQLite3 *self)
 115 {
 116         IoObject_shouldMark((IoObject *)DATA(self)->path);
 117
 118         if (DATA(self)->results)
 119         {
 120                 IoObject_shouldMark((IoObject *)DATA(self)->results);
 121         }
 122 }
 123
 124 void IoSQLite3_showError(IoSQLite3 *self)
 125 {
 126         int status = sqlite3_errcode(DATA(self)->db);
 127
 128         if (status != SQLITE_OK)
 129         {
 130                 const char *error = IoSQLite3_error(self);
 131
 132                 if (DATA(self)->debugOn)
 133                 {
 134                         IoState_print_(IOSTATE, "*** IoSQLite3 error '%s' ***\n", error);
 135                 }
 136         }
 137 }
 138
 139 const char *IoSQLite3_error(IoSQLite3 *self)
 140 {
 141         return sqlite3_errmsg(DATA(self)->db);
 142 }
 143
 144 /* ----------------------------------------------------------- */
 145
 146 static int IoSQLite3_busyHandler(void *context, int n)
 147 {
 148         IoSQLite3 *self = context;
 149         IoState_yield(IOSTATE);
 150         return 1;
 151 }
 152
 153 IoObject *IoSQLite3_path(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 154 {
 155         /*#io
 156         docSlot("path",
 157                    "Returns the path to the database file. ")
 158         */
 159
 160         return DATA(self)->path;
 161 }
 162
 163 IoObject *IoSQLite3_setPath(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 164 {
 165         /*#io
 166         docSlot("setPath",
 167                    "Sets the path to the database file. Returns self. ")
 168         */
 169
 170         DATA(self)->path = IOREF(IoMessage_locals_symbolArgAt_(m, locals, 0));
 171         return self;
 172 }
 173
 174 IoObject *IoSQLite3_timeoutSeconds(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 175 {
 176         /*#io
 177         docSlot("timeoutSeconds",
 178                    "Returns the number of seconds to wait before timing out an
 179 open call. If the number is 0, an open call will never timeout. ")
 180         */
 181         return IONUMBER(DATA(self)->timeoutSeconds);
 182 }
 183
 184 IoObject *IoSQLite3_setTimeoutSeconds(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 185 {
 186         /*#io
 187         docSlot("setTimeoutSeconds(aNumber)",
 188                    "Sets the open timeout to aNumber. If aNumber is 0, an open
 189 call will never timeout. Returns self. ")
 190         */
 191
 192         IoNumber *num = IoMessage_locals_numberArgAt_(m, locals, 0);
 193         IOASSERT(IoNumber_asDouble(num) >= 0, "SQLite timeout must be a positive number");
 194         DATA(self)->timeoutSeconds = IoNumber_asDouble(num);
 195         return self;
 196 }
 197
 198 IoObject *IoSQLite3_open(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 199 {
 200         /*#io
 201         docSlot("open(optionalPathString)",
 202                    """Opens the database. If there is an optionalPathString argument,
 203 the path is set to it's value before opening the database. If path is "" or ":memory:"
 204 a database will be created in-memory, otherwise the file specified by path is opened.
 205 Returns self or Nil upon failure.
 206
 207 If the databse is locked, "yield" will be called until it is accessable or
 208 timeoutSeconds has expired. """)
 209         */
 210
 211         if (IoMessage_argCount(m) > 0)
 212         {
 213                 DATA(self)->path = IOREF(IoMessage_locals_symbolArgAt_(m, locals, 0));
 214         }
 215
 216         sqlite3_open(CSTRING(DATA(self)->path), &(DATA(self)->db));
 217
 218         IoSQLite3_showError(self);
 219
 220         sqlite3_busy_handler(DATA(self)->db, IoSQLite3_busyHandler, self);
 221         sqlite3_busy_timeout(DATA(self)->db, DATA(self)->timeoutSeconds * 1000);
 222         return self;
 223 }
 224
 225 IoObject *IoSQLite3_isOpen(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 226 {
 227         /*#io
 228         docSlot("isOpen", "Returns true if the database is open, false otherwise.")
 229         */
 230
 231         return IOBOOL(self, DATA(self)->db != NULL);
 232 }
 233
 234 IoObject *IoSQLite3_close(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 235 {
 236         /*#io
 237         docSlot("close",
 238                    "Closes the database if it is open. Returns self. If the database is open when the open is garbage collected, it will be automatically closed. ")
 239         */
 240
 241         if (DATA(self)->db)
 242         {
 243                 sqlite3_close(DATA(self)->db);
 244                 DATA(self)->db = NULL;
 245         }
 246
 247         return self;
 248 }
 249
 250 static int IoSQLite3_singleItemResultRow(void *context, int argc, char **argv, char **azColName)
 251 {
 252         IoSQLite3 *self = context;
 253         int i = 0;
 254         IoSymbol *value;
 255
 256         if (argv[i])
 257         {
 258                 value = IOSYMBOL(argv[i]);
 259         }
 260         else
 261         {
 262                 value = IOSYMBOL((char *)"NULL");
 263         }
 264
 265         IoList_rawAppend_(DATA(self)->results, value);
 266         return 0;
 267 }
 268
 269 static int IoSQLite3_columnNamesResultRow(void *context, int argc, char **argv, char **azColName)
 270 {
 271         IoSQLite3 *self = context;
 272         int i = 0;
 273
 274         for (i= 0; i < argc; i ++)
 275         {
 276                 if (!strcmp(azColName[i], "name"))
 277                 {
 278                         IoList_rawAppend_(DATA(self)->results, IOSYMBOL(argv[i]));
 279                         break;
 280                 }
 281         }
 282         return 0;
 283 }
 284
 285 static int IoSQLite3_resultRow(void *context, int argc, char **argv, char **azColName)
 286 {
 287         IoSQLite3 *self = context;
 288         IoState_pushRetainPool(IOSTATE);
 289
 290         {
 291                 IoMap *map = IoMap_new(IOSTATE);
 292                 PHash *hash = IoMap_rawHash(map);
 293                 int i;
 294                 IoSymbol *key, *value;
 295
 296                 for(i = 0; i < argc; i ++)
 297                 {
 298                         key = IOSYMBOL(azColName[i]);
 299
 300                         if (argv[i])
 301                         {
 302                                 value = IOSYMBOL(argv[i]);
 303                         }
 304                         else
 305                         {
 306                                 value = IOSYMBOL((char *)"NULL");
 307                         }
 308
 309                         PHash_at_put_(hash, key, value);
 310                         /*printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); */
 311                 }
 312
 313                 IoList_rawAppend_(DATA(self)->results, map);
 314         }
 315
 316         IoState_popRetainPool(IOSTATE);
 317
 318         return 0;
 319 }
 320
 321 IoObject *IoSQLite3_execWithCallback(IoSQLite3 *self,
 322                                                           IoObject *locals, IoMessage *m, IoSymbol *s, ResultRowCallback *callback)
 323 {
 324         IoList *results;
 325
 326         if (!DATA(self)->db)
 327         {
 328                 IoSQLite3_open(self, locals, m);
 329
 330                 if (!DATA(self)->db)
 331                 {
 332                         return IONIL(self);
 333                 }
 334         }
 335
 336         DATA(self)->results = IOREF(IoList_new(IOSTATE));
 337
 338         if (DATA(self)->debugOn)
 339         {
 340                 IoState_print_(IOSTATE, "*** %s ***\n", CSTRING(s));
 341         }
 342
 343         {
 344                 char *zErrMsg;
 345                 sqlite3_exec(DATA(self)->db, CSTRING(s), callback, self, &zErrMsg);
 346
 347                 IoSQLite3_showError(self);
 348         }
 349
 350         results = DATA(self)->results;
 351         DATA(self)->results = NULL;
 352         return results;
 353 }
 354
 355 IoObject *IoSQLite3_exec(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 356 {
 357         /*#io
 358         docSlot("exec(aString)",
 359                    "Opens the database if it is not already open and executes
 360 aString as an sql command. Results a List of Map objects or Nil if
 361 there was an error. Each map holds the contents of a row.
 362 The key/value pairs of the maps being column name/column value
 363 pairs for a row. ")
 364
 365         */
 366
 367         IoSymbol *s = IoMessage_locals_seqArgAt_(m, locals, 0);
 368         return IoSQLite3_execWithCallback(self, locals, m, s, IoSQLite3_resultRow);
 369 }
 370
 371 IoObject *IoSQLite3_errorMessage(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 372 {
 373         /*#io
 374         docSlot("error",
 375                    "Results a string containing the current error. If there is no error, Nil is returned. ")
 376         */
 377
 378         if (sqlite3_errcode(DATA(self)->db) == SQLITE_OK)
 379         {
 380                 return IONIL(self);
 381         }
 382
 383         return IOSYMBOL(IoSQLite3_error(self));
 384 }
 385
 386 IoObject *IoSQLite3_version(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 387 {
 388         /*#io
 389         docSlot("version",
 390                    "Results a string the version of SQLite being used. ")
 391         */
 392
 393         return IOSYMBOL(SQLITE_VERSION);
 394 }
 395
 396 IoObject *IoSQLite3_changes(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 397 {
 398         /*#io
 399         docSlot("changes",
 400                    "Returns the number of rows that were changed by the most
 401 recent SQL statement. Or Nil if the database is closed.")
 402         */
 403
 404         if (!DATA(self)->db)
 405         {
 406                 return IONUMBER(0);
 407         }
 408
 409         return IONUMBER(sqlite3_changes(DATA(self)->db));
 410 }
 411
 412 IoObject *IoSQLite3_lastInsertRowId(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 413 {
 414         /*#io
 415         docSlot("lastInsertRowId",
 416                    "Returns the number with the row id of the last row inserted. ")
 417         */
 418
 419         if (!DATA(self)->db)
 420         {
 421                 return IONIL(self);
 422         }
 423
 424         return IONUMBER(sqlite3_last_insert_rowid(DATA(self)->db));
 425 }
 426
 427 IoObject *IoSQLite3_tableNames(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 428 {
 429         /*#io
 430         docSlot("tableNames",
 431                    "Returns a list containing the names of all tables in the database.")
 432         */
 433
 434         IoSymbol *s = IOSYMBOL("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name");
 435         return IoSQLite3_execWithCallback(self, locals, m, s, IoSQLite3_singleItemResultRow);
 436 }
 437
 438 IoObject *IoSQLite3_viewNames(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 439 {
 440         /*#io
 441         docSlot("viewNames",
 442                    "Returns a list containing the names of all
 443 views in the database.")
 444         */
 445
 446         IoSymbol *s = IOSYMBOL("SELECT name FROM sqlite_master WHERE type='view' ORDER BY name");
 447         return IoSQLite3_execWithCallback(self, locals, m, s, IoSQLite3_singleItemResultRow);
 448 }
 449
 450 IoObject *IoSQLite3_columnNamesOfTable(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 451 {
 452         /*#io
 453         docSlot("columnNamesOfTable(tableName)",
 454                    "Returns a list containing the names of all columns in the specified table.")
 455         */
 456
 457         IoSymbol *tableName = IoMessage_locals_symbolArgAt_(m, locals, 0);
 458         IoSymbol *s = IoSeq_newSymbolWithFormat_(IOSTATE, "PRAGMA TABLE_INFO(%s)", CSTRING(tableName));
 459         return IoSQLite3_execWithCallback(self, locals, m, s, IoSQLite3_columnNamesResultRow);
 460 }
 461
 462 IoObject *IoSQLite3_debugOn(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 463 {
 464         /*#io
 465         docSlot("debugOn",
 466                    "Turns on debugging.")
 467         */
 468
 469         DATA(self)->debugOn = 1;
 470         return self;
 471 }
 472
 473 IoObject *IoSQLite3_debugOff(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 474 {
 475         /*#io
 476         docSlot("debugOff",
 477                    "Turns off debugging.")
 478         */
 479
 480         DATA(self)->debugOn = 0;
 481         return self;
 482 }
 483
 484 IoObject *IoSQLite3_escapeString(IoSQLite3 *self, IoObject *locals, IoMessage *m)
 485 {
 486         /*#io
 487         docSlot("escapeString(aString)",
 488                    "Returns a translated version of aString by making two copies of every single-quote (') character. This has the effect of escaping the end-of-string meaning of single-quote within a string literal.")
 489         */
 490
 491         IoSymbol *s = IoMessage_locals_seqArgAt_(m, locals, 0);
 492         char *newString = sqlite3_mprintf("%q", CSTRING(s));
 493         UArray *ba = UArray_newWithCString_(newString);
 494         sqlite3_free(newString);
 495         return IoState_symbolWithUArray_copy_(IOSTATE, ba, 0);
 496 }