addons/SQLite/source/IoSQLite.c

   1 /*#io
   2 SQLite ioDoc(
   3                    docCopyright("Steve Dekorte", 2004)
   4                    docLicense("BSD revised")
   5                    docCategory("Databases")
   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                    */
  21
  22 #include "IoSQLite.h"
  23 #include "IoState.h"
  24 #include "IoNumber.h"
  25 #include "IoList.h"
  26 #include "IoMap.h"
  27
  28 typedef int (ResultRowCallback)(void *, int , char **, char **);
  29
  30 #define DATA(self) ((IoSQLiteData *)IoObject_dataPointer(self))
  31
  32 /*
  33  static int IoSQLite_resultRow(void *context, int argc, char **argv, char **azColName);
  34  static int IoSQLite_busyHandler(void *context, const char *s, int n);
  35  */
  36
  37 IoTag *IoSQLite_newTag(void *state)
  38 {
  39         IoTag *tag = IoTag_newWithName_("SQLite");
  40         IoTag_state_(tag, state);
  41         IoTag_cloneFunc_(tag, (IoTagCloneFunc *)IoSQLite_rawClone);
  42         IoTag_freeFunc_(tag, (IoTagFreeFunc *)IoSQLite_free);
  43         IoTag_markFunc_(tag, (IoTagMarkFunc *)IoSQLite_mark);
  44         return tag;
  45 }
  46
  47 IoSQLite *IoSQLite_proto(void *state)
  48 {
  49         IoObject *self = IoObject_new(state);
  50         IoObject_tag_(self, IoSQLite_newTag(state));
  51
  52         IoObject_setDataPointer_(self, calloc(1, sizeof(IoSQLiteData)));
  53         DATA(self)->path = IOSYMBOL(".");
  54         IoSQLite_error_(self, "");
  55
  56         IoState_registerProtoWithFunc_(state, self, IoSQLite_proto);
  57
  58         {
  59                 IoMethodTable methodTable[] = {
  60                 {"setPath", IoSQLite_setPath},
  61                 {"path", IoSQLite_path},
  62                 {"open", IoSQLite_open},
  63                 {"close", IoSQLite_close},
  64                 {"exec", IoSQLite_exec},
  65                 {"error", IoSQLite_errorMessage},
  66                 {"version", IoSQLite_version},
  67                 {"setTimeoutSeconds", IoSQLite_setTimeoutSeconds},
  68                 {"timeoutSeconds", IoSQLite_timeoutSeconds},
  69                 {"rowsChangedCount", IoSQLite_changes},
  70                 {"lastInsertRowId", IoSQLite_lastInsertRowId},
  71                 {"tableNames", IoSQLite_tableNames},
  72                 {"viewNames", IoSQLite_viewNames},
  73                 {"columnNamesOfTable", IoSQLite_columnNamesOfTable},
  74                 {"debugOn", IoSQLite_debugOn},
  75                 {"debugOff", IoSQLite_debugOff},
  76                 {"isOpen", IoSQLite_isOpen},
  77                 {"escapeString", IoSQLite_escapeString},
  78                 {NULL, NULL},
  79                 };
  80                 IoObject_addMethodTable_(self, methodTable);
  81         }
  82         return self;
  83 }
  84
  85 IoSQLite *IoSQLite_rawClone(IoSQLite *proto)
  86 {
  87         IoObject *self = IoObject_rawClonePrimitive(proto);
  88         IoObject_setDataPointer_(self, cpalloc(IoObject_dataPointer(proto), sizeof(IoSQLiteData)));
  89         DATA(self)->error = NULL;
  90         IoSQLite_error_(self, "");
  91         return self;
  92 }
  93
  94 /* ----------------------------------------------------------- */
  95
  96 IoSQLite *IoSQLite_new(void *state)
  97 {
  98         IoObject *proto = IoState_protoWithInitFunction_(state, IoSQLite_proto);
  99         return IOCLONE(proto);
 100 }
 101
 102 IoSQLite *IoSQLite_newWithPath_(void *state, IoSymbol *path)
 103 {
 104         IoSQLite *self = IoSQLite_new(state);
 105         DATA(self)->path = IOREF(path);
 106         return self;
 107 }
 108
 109 void IoSQLite_free(IoSQLite *self)
 110 {
 111         if (DATA(self)->db) sqlite_close(DATA(self)->db);
 112         if (DATA(self)->error) free(DATA(self)->error);
 113         free(IoObject_dataPointer(self));
 114 }
 115
 116 void IoSQLite_mark(IoSQLite *self)
 117 {
 118         IoObject_shouldMark((IoObject *)DATA(self)->path);
 119
 120         if (DATA(self)->results)
 121         {
 122                 IoObject_shouldMark((IoObject *)DATA(self)->results);
 123         }
 124 }
 125
 126 void IoSQLite_error_(IoSQLite *self, char *error)
 127 {
 128         DATA(self)->error = strcpy((char *)realloc(DATA(self)->error, strlen(error)+1), error);
 129
 130         if (strlen(DATA(self)->error) && DATA(self)->debugOn)
 131         {
 132                 IoState_print_(IOSTATE, "*** IoSQLite error '%s' ***\n", DATA(self)->error);
 133         }
 134 }
 135
 136 char *IoSQLite_error(IoSQLite *self)
 137 {
 138         return DATA(self)->error;
 139 }
 140
 141 /* ----------------------------------------------------------- */
 142
 143 static int IoSQLite_busyHandler(void *context, const char *s, int n)
 144 {
 145         IoSQLite *self = context;
 146         IoState_yield(IOSTATE);
 147         return 1;
 148 }
 149
 150 IoObject *IoSQLite_path(IoSQLite *self, IoObject *locals, IoMessage *m)
 151 {
 152         /*#io
 153         docSlot("path",
 154                    "Returns the path to the database file. ")
 155         */
 156
 157         return DATA(self)->path;
 158 }
 159
 160 IoObject *IoSQLite_setPath(IoSQLite *self, IoObject *locals, IoMessage *m)
 161 {
 162         /*#io
 163         docSlot("setPath",
 164                    "Sets the path to the database file. Returns self. ")
 165         */
 166
 167         DATA(self)->path = IOREF(IoMessage_locals_symbolArgAt_(m, locals, 0));
 168         return self;
 169 }
 170
 171 IoObject *IoSQLite_timeoutSeconds(IoSQLite *self, IoObject *locals, IoMessage *m)
 172 {
 173         /*#io
 174         docSlot("timeoutSeconds",
 175                    "Returns the number of seconds to wait before timing out an open call. If the number is 0, an open call will never timeout. ")
 176         */
 177         return IONUMBER(DATA(self)->timeoutSeconds);
 178 }
 179
 180 IoObject *IoSQLite_setTimeoutSeconds(IoSQLite *self, IoObject *locals, IoMessage *m)
 181 {
 182         /*#io
 183         docSlot("setTimeoutSeconds(aNumber)",
 184                    "Sets the open timeout to aNumber. If aNumber is 0, an open call will never timeout. Returns self. ")
 185         */
 186
 187         IoNumber *num = IoMessage_locals_numberArgAt_(m, locals, 0);
 188         IOASSERT(IoNumber_asDouble(num) >= 0, "SQLite timeout must be a positive number");
 189         DATA(self)->timeoutSeconds = IoNumber_asDouble(num);
 190         return self;
 191 }
 192
 193 IoObject *IoSQLite_open(IoSQLite *self, IoObject *locals, IoMessage *m)
 194 {
 195         /*#io
 196         docSlot("open(optionalPathString)",
 197                    """Opens the database.Returns self on success or nil upon failure.
 198
 199 If the databse is locked, "yield" will be called until it is accessable or timeoutSeconds has expired.
 200 """)
 201         */
 202
 203         char *zErrMsg;
 204
 205         if (DATA(self)->debugOn)
 206         {
 207                 IoState_print_(IOSTATE, "IoSQLite opening '%s'\n", CSTRING(DATA(self)->path));
 208         }
 209
 210         DATA(self)->db = sqlite_open(CSTRING(DATA(self)->path), 0, &zErrMsg);
 211
 212         if (!DATA(self)->db)
 213         {
 214                 IoSQLite_error_(self, zErrMsg);
 215         }
 216         else
 217         {
 218                 IoSQLite_error_(self, "");
 219         }
 220
 221         sqlite_busy_handler(DATA(self)->db, IoSQLite_busyHandler, self);
 222         sqlite_busy_timeout(DATA(self)->db, DATA(self)->timeoutSeconds*1000);
 223         return self;
 224 }
 225
 226 IoObject *IoSQLite_isOpen(IoSQLite *self, IoObject *locals, IoMessage *m)
 227 {
 228         /*#io
 229         docSlot("isOpen", "Returns true if the database is open, false otherwise.")
 230         */
 231
 232         return IOBOOL(self, DATA(self)->db != NULL);
 233 }
 234
 235 IoObject *IoSQLite_close(IoSQLite *self, IoObject *locals, IoMessage *m)
 236 {
 237         /*#io
 238         docSlot("close",
 239                    "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. ")
 240         */
 241
 242         if (DATA(self)->db)
 243         {
 244                 sqlite_close(DATA(self)->db);
 245                 DATA(self)->db = NULL;
 246         }
 247
 248         return self;
 249 }
 250
 251 static int IoSQLite_singleItemResultRow(void *context, int argc, char **argv, char **azColName)
 252 {
 253         IoSQLite *self = context;
 254         int i = 0;
 255         IoSymbol *value;
 256
 257         if (argv[i])
 258         {
 259                 value = IOSYMBOL(argv[i]);
 260         }
 261         else
 262         {
 263                 value = IOSYMBOL((char *)"NULL");
 264         }
 265
 266         IoList_rawAppend_(DATA(self)->results, value);
 267
 268         return 0;
 269 }
 270
 271 static int IoSQLite_columnNamesResultRow(void *context, int argc, char **argv, char **azColName)
 272 {
 273         IoSQLite *self = context;
 274         int i = 0;
 275
 276         for (i= 0; i < argc; i ++)
 277         {
 278                 if (!strcmp(azColName[i], "name"))
 279                 {
 280                         IoList_rawAppend_(DATA(self)->results, IOSYMBOL(argv[i]));
 281                         break;
 282                 }
 283         }
 284
 285         return 0;
 286 }
 287
 288 static int IoSQLite_resultRow(void *context, int argc, char **argv, char **azColName)
 289 {
 290         IoSQLite *self = context;
 291         IoState_pushRetainPool(IOSTATE);
 292
 293         {
 294                 IoMap *map = IoMap_new(IOSTATE);
 295                 PHash *hash = IoMap_rawHash(map);
 296                 int i;
 297                 IoSymbol *key, *value;
 298
 299                 for(i = 0; i < argc; i ++)
 300                 {
 301                         key = IOSYMBOL(azColName[i]);
 302
 303                         if (argv[i])
 304                         {
 305                                 value = IOSYMBOL(argv[i]);
 306                         }
 307                         else
 308                         {
 309                                 value = IOSYMBOL((char *)"NULL");
 310                         }
 311
 312                         PHash_at_put_(hash, key, value);
 313                         /*printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL"); */
 314                 }
 315
 316                 IoList_rawAppend_(DATA(self)->results, map);
 317         }
 318
 319         IoState_popRetainPool(IOSTATE);
 320
 321         return 0;
 322 }
 323
 324 IoObject *IoSQLite_execWithCallback(IoSQLite *self,
 325                                                          IoObject *locals,
 326                                                          IoMessage *m,
 327                                                          IoSymbol *s,
 328                                                          ResultRowCallback *callback)
 329 {
 330         IoList *results;
 331
 332         if (!DATA(self)->db)
 333         {
 334                 IoSQLite_open(self, locals, m);
 335
 336                 if (!DATA(self)->db)
 337                 {
 338                         return IONIL(self);
 339                 }
 340         }
 341
 342         DATA(self)->results = IOREF(IoList_new(IOSTATE));
 343
 344         if (DATA(self)->debugOn)
 345         {
 346                 IoState_print_(IOSTATE, "*** %s ***\n", CSTRING(s));
 347         }
 348
 349         {
 350                 char *zErrMsg;
 351                 int rc = sqlite_exec(DATA(self)->db, CSTRING(s), callback, self, &zErrMsg);
 352
 353                 if (rc != SQLITE_OK)
 354                 {
 355                         IoSQLite_error_(self, zErrMsg);
 356                         IoState_error_(IOSTATE, m, zErrMsg);
 357                 }
 358                 else
 359                 {
 360                         IoSQLite_error_(self, "");
 361                 }
 362         }
 363
 364         results = DATA(self)->results;
 365         DATA(self)->results = NULL;
 366         return results;
 367 }
 368
 369 IoObject *IoSQLite_exec(IoSQLite *self, IoObject *locals, IoMessage *m)
 370 {
 371         /*#io
 372         docSlot("exec(aString)",
 373                    "Opens the database if it is not already open and executes
 374 aString as an sql command. Results a List of Map objects or Nil if
 375 there was an error. Each map holds the contents of a row.
 376 The key/value pairs of the maps being column name/column value
 377 pairs for a row. ")
 378
 379         */
 380
 381         IoSymbol *s = IoMessage_locals_seqArgAt_(m, locals, 0);
 382         return IoSQLite_execWithCallback(self, locals, m, s, IoSQLite_resultRow);
 383 }
 384
 385 IoObject *IoSQLite_errorMessage(IoSQLite *self, IoObject *locals, IoMessage *m)
 386 {
 387         /*#io
 388         docSlot("error",
 389                    "Results a string containing the current error. If there is no error, Nil is returned. ")
 390         */
 391
 392         if (strlen(DATA(self)->error)==0)
 393         {
 394                 return IONIL(self);
 395         }
 396
 397         return IOSYMBOL(DATA(self)->error);
 398 }
 399
 400 IoObject *IoSQLite_version(IoSQLite *self, IoObject *locals, IoMessage *m)
 401 {
 402         /*#io
 403         docSlot("version",
 404                    "Results a string the version of SQLite being used. ")
 405         */
 406
 407         return IOSYMBOL(SQLITE_VERSION);
 408 }
 409
 410 IoObject *IoSQLite_changes(IoSQLite *self, IoObject *locals, IoMessage *m)
 411 {
 412         /*#io
 413         docSlot("changes",
 414                    "Returns the number of rows that were changed by the most  recent SQL statement. Or Nil if the database is closed.")
 415         */
 416
 417         if (!DATA(self)->db)
 418         {
 419                 return IONUMBER(0);
 420         }
 421
 422         return IONUMBER(sqlite_changes(DATA(self)->db));
 423 }
 424
 425 IoObject *IoSQLite_lastInsertRowId(IoSQLite *self, IoObject *locals, IoMessage *m)
 426 {
 427         /*#io
 428         docSlot("lastInsertRowId",
 429                    "Returns the number with the row id of the last row inserted. ")
 430         */
 431
 432         if (!DATA(self)->db)
 433         {
 434                 return IONIL(self);
 435         }
 436
 437         return IONUMBER(sqlite_last_insert_rowid(DATA(self)->db));
 438 }
 439
 440 IoObject *IoSQLite_tableNames(IoSQLite *self, IoObject *locals, IoMessage *m)
 441 {
 442         /*#io
 443         docSlot("tableNames",
 444                    "Returns a list containing the names of all tables in the database.")
 445         */
 446
 447         IoSymbol *s = IOSYMBOL("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name");
 448         return IoSQLite_execWithCallback(self, locals, m, s, IoSQLite_singleItemResultRow);
 449 }
 450
 451 IoObject *IoSQLite_viewNames(IoSQLite *self, IoObject *locals, IoMessage *m)
 452 {
 453         /*#io
 454         docSlot("viewNames",
 455                    "Returns a list containing the names of all
 456 views in the database.")
 457         */
 458
 459         IoSymbol *s = IOSYMBOL("SELECT name FROM sqlite_master WHERE type='view' ORDER BY name");
 460         return IoSQLite_execWithCallback(self, locals, m, s, IoSQLite_singleItemResultRow);
 461 }
 462
 463 IoObject *IoSQLite_columnNamesOfTable(IoSQLite *self, IoObject *locals, IoMessage *m)
 464 {
 465         /*#io
 466         docSlot("columnNamesOfTable(tableName)",
 467                    "Returns a list containing the names of all columns in the specified table.")
 468         */
 469
 470         IoSymbol *tableName = IoMessage_locals_symbolArgAt_(m, locals, 0);
 471         IoSymbol *s = IoSeq_newSymbolWithFormat_(IOSTATE, "PRAGMA TABLE_INFO(%s)", CSTRING(tableName));
 472         return IoSQLite_execWithCallback(self, locals, m, s, IoSQLite_columnNamesResultRow);
 473 }
 474
 475 IoObject *IoSQLite_debugOn(IoSQLite *self, IoObject *locals, IoMessage *m)
 476 {
 477         /*#io
 478         docSlot("debugOn",
 479                    "Turns on debugging.")
 480         */
 481
 482         DATA(self)->debugOn = 1;
 483         return self;
 484 }
 485
 486 IoObject *IoSQLite_debugOff(IoSQLite *self, IoObject *locals, IoMessage *m)
 487 {
 488         /*#io
 489         docSlot("debugOff",
 490                    "Turns off debugging.")
 491         */
 492
 493         DATA(self)->debugOn = 0;
 494         return self;
 495 }
 496
 497 IoObject *IoSQLite_escapeString(IoSQLite *self, IoObject *locals, IoMessage *m)
 498 {
 499         /*#io
 500         docSlot("escapeString(aString)",
 501                    "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.")
 502         */
 503
 504         IoSymbol *s = IoMessage_locals_seqArgAt_(m, locals, 0);
 505         char *newString = sqlite_mprintf("%q", CSTRING(s));
 506         UArray *ba = UArray_newWithCString_(newString);
 507         sqlite_freemem(newString);
 508         return IoState_symbolWithUArray_copy_(IOSTATE, ba, 0);
 509 }