1 /*
2 ** LuaSQL, SQLite driver
3 ** Author: Tiago Dionizio, Eduardo Quintao
4 ** See Copyright Notice in license.html
5
6 ** $Id: ls_sqlite3.c,v 1.15 2009/02/07 23:16:23 tomas Exp $
7 */
8
9 #include <stdio.h>
10 #include <stdlib.h>
11 #include <string.h>
12 #include <ctype.h>
13
14 #include "sqlite3.h"
15
16 #include "lua.h"
17 #include "lauxlib.h"
18
19
20 #include "luasql.h"
21
22 #define LUASQL_ENVIRONMENT_SQLITE "SQLite3 environment"
23 #define LUASQL_CONNECTION_SQLITE "SQLite3 connection"
24 #define LUASQL_CURSOR_SQLITE "SQLite3 cursor"
25
26 typedef struct
27 {
28 short closed;
29 } env_data;
30
31
32 typedef struct
33 {
34 short closed;
35 int env; /* reference to environment */
36 short auto_commit; /* 0 for manual commit */
37 unsigned int cur_counter;
38 sqlite3 *sql_conn;
39 } conn_data;
40
41
42 typedef struct
43 {
44 short closed;
45 int conn; /* reference to connection */
46 int numcols; /* number of columns */
47 int colnames, coltypes; /* reference to column information tables */
48 conn_data *conn_data; /* reference to connection for cursor */
49 sqlite3_stmt *sql_vm;
50 } cur_data;
51
52 LUASQL_API int luaopen_luasql_sqlite3(lua_State *L);
53
54
55 /*
56 ** Check for valid environment.
57 */
getenvironment(lua_State * L)58 static env_data *getenvironment(lua_State *L) {
59 env_data *env = (env_data *)luaL_checkudata(L, 1, LUASQL_ENVIRONMENT_SQLITE);
60 luaL_argcheck(L, env != NULL, 1, LUASQL_PREFIX"environment expected");
61 luaL_argcheck(L, !env->closed, 1, LUASQL_PREFIX"environment is closed");
62 return env;
63 }
64
65
66 /*
67 ** Check for valid connection.
68 */
getconnection(lua_State * L)69 static conn_data *getconnection(lua_State *L) {
70 conn_data *conn = (conn_data *)luaL_checkudata (L, 1, LUASQL_CONNECTION_SQLITE);
71 luaL_argcheck(L, conn != NULL, 1, LUASQL_PREFIX"connection expected");
72 luaL_argcheck(L, !conn->closed, 1, LUASQL_PREFIX"connection is closed");
73 return conn;
74 }
75
76
77 /*
78 ** Check for valid cursor.
79 */
getcursor(lua_State * L)80 static cur_data *getcursor(lua_State *L) {
81 cur_data *cur = (cur_data *)luaL_checkudata (L, 1, LUASQL_CURSOR_SQLITE);
82 luaL_argcheck(L, cur != NULL, 1, LUASQL_PREFIX"cursor expected");
83 luaL_argcheck(L, !cur->closed, 1, LUASQL_PREFIX"cursor is closed");
84 return cur;
85 }
86
87 /*
88 ** Closes the cursor and nullify all structure fields.
89 */
cur_nullify(lua_State * L,cur_data * cur)90 static void cur_nullify(lua_State *L, cur_data *cur)
91 {
92 conn_data *conn;
93
94 /* Nullify structure fields. */
95 cur->closed = 1;
96 cur->sql_vm = NULL;
97 /* Decrement cursor counter on connection object */
98 lua_rawgeti (L, LUA_REGISTRYINDEX, cur->conn);
99 conn = lua_touserdata (L, -1);
100 conn->cur_counter--;
101
102 luaL_unref(L, LUA_REGISTRYINDEX, cur->conn);
103 luaL_unref(L, LUA_REGISTRYINDEX, cur->colnames);
104 luaL_unref(L, LUA_REGISTRYINDEX, cur->coltypes);
105 }
106
107
108 /*
109 ** Finalizes the vm
110 ** Return nil + errmsg or nil in case of sucess
111 */
finalize(lua_State * L,cur_data * cur)112 static int finalize(lua_State *L, cur_data *cur) {
113 const char *errmsg;
114 if (sqlite3_finalize(cur->sql_vm) != SQLITE_OK)
115 {
116 errmsg = sqlite3_errmsg(cur->conn_data->sql_conn);
117 cur_nullify(L, cur);
118 return luasql_faildirect(L, errmsg);
119 }
120 cur_nullify(L, cur);
121 lua_pushnil(L);
122 return 1;
123 }
124
125
push_column(lua_State * L,sqlite3_stmt * vm,int column)126 static void push_column(lua_State *L, sqlite3_stmt *vm, int column) {
127 switch (sqlite3_column_type(vm, column)) {
128 case SQLITE_INTEGER:
129 #if LUA_VERSION_NUM >= 503
130 lua_pushinteger(L, sqlite3_column_int64(vm, column));
131 #else
132 // Preserves precision of integers up to 2^53.
133 lua_pushnumber(L, sqlite3_column_int64(vm, column));
134 #endif
135 break;
136 case SQLITE_FLOAT:
137 lua_pushnumber(L, sqlite3_column_double(vm, column));
138 break;
139 case SQLITE_TEXT:
140 lua_pushlstring(L, (const char *)sqlite3_column_text(vm, column),
141 (size_t)sqlite3_column_bytes(vm, column));
142 break;
143 case SQLITE_BLOB:
144 lua_pushlstring(L, sqlite3_column_blob(vm, column),
145 (size_t)sqlite3_column_bytes(vm, column));
146 break;
147 case SQLITE_NULL:
148 lua_pushnil(L);
149 break;
150 default:
151 luaL_error(L, LUASQL_PREFIX"Unrecognized column type");
152 break;
153 }
154 }
155
156
157 /*
158 ** Get another row of the given cursor.
159 */
cur_fetch(lua_State * L)160 static int cur_fetch (lua_State *L) {
161 cur_data *cur = getcursor(L);
162 sqlite3_stmt *vm = cur->sql_vm;
163 int res;
164
165 if (vm == NULL)
166 return 0;
167
168 res = sqlite3_step(vm);
169
170 /* no more results? */
171 if (res == SQLITE_DONE)
172 return finalize(L, cur);
173
174 if (res != SQLITE_ROW)
175 return finalize(L, cur);
176
177 if (lua_istable (L, 2))
178 {
179 int i;
180 const char *opts = luaL_optstring(L, 3, "n");
181
182 if (strchr(opts, 'n') != NULL)
183 {
184 /* Copy values to numerical indices */
185 for (i = 0; i < cur->numcols;)
186 {
187 push_column(L, vm, i);
188 lua_rawseti(L, 2, ++i);
189 }
190 }
191 if (strchr(opts, 'a') != NULL)
192 {
193 /* Copy values to alphanumerical indices */
194 lua_rawgeti(L, LUA_REGISTRYINDEX, cur->colnames);
195
196 for (i = 0; i < cur->numcols; i++)
197 {
198 lua_rawgeti(L, -1, i+1);
199 push_column(L, vm, i);
200 lua_rawset (L, 2);
201 }
202 }
203 lua_pushvalue(L, 2);
204 return 1; /* return table */
205 }
206 else
207 {
208 int i;
209 luaL_checkstack (L, cur->numcols, LUASQL_PREFIX"too many columns");
210 for (i = 0; i < cur->numcols; ++i)
211 push_column(L, vm, i);
212 return cur->numcols; /* return #numcols values */
213 }
214 }
215
216
217 /*
218 ** Cursor object collector function
219 */
cur_gc(lua_State * L)220 static int cur_gc(lua_State *L)
221 {
222 cur_data *cur = (cur_data *)luaL_checkudata(L, 1, LUASQL_CURSOR_SQLITE);
223 if (cur != NULL && !(cur->closed))
224 {
225 sqlite3_finalize(cur->sql_vm);
226 cur_nullify(L, cur);
227 }
228 return 0;
229 }
230
231
232 /*
233 ** Close the cursor on top of the stack.
234 ** Return 1
235 */
cur_close(lua_State * L)236 static int cur_close(lua_State *L)
237 {
238 cur_data *cur = (cur_data *)luaL_checkudata(L, 1, LUASQL_CURSOR_SQLITE);
239 luaL_argcheck(L, cur != NULL, 1, LUASQL_PREFIX"cursor expected");
240 if (cur->closed) {
241 lua_pushboolean(L, 0);
242 return 1;
243 }
244 sqlite3_finalize(cur->sql_vm);
245 cur_nullify(L, cur);
246 lua_pushboolean(L, 1);
247 return 1;
248 }
249
250
251 /*
252 ** Return the list of field names.
253 */
cur_getcolnames(lua_State * L)254 static int cur_getcolnames(lua_State *L)
255 {
256 cur_data *cur = getcursor(L);
257 lua_rawgeti(L, LUA_REGISTRYINDEX, cur->colnames);
258 return 1;
259 }
260
261
262 /*
263 ** Return the list of field types.
264 */
cur_getcoltypes(lua_State * L)265 static int cur_getcoltypes(lua_State *L)
266 {
267 cur_data *cur = getcursor(L);
268 lua_rawgeti(L, LUA_REGISTRYINDEX, cur->coltypes);
269 return 1;
270 }
271
272
273 /*
274 ** Create a new Cursor object and push it on top of the stack.
275 */
276 /* static int create_cursor(lua_State *L, int conn, sqlite3_stmt *sql_vm,
277 int numcols, const char **row, const char **col_info)*/
create_cursor(lua_State * L,int o,conn_data * conn,sqlite3_stmt * sql_vm,int numcols)278 static int create_cursor(lua_State *L, int o, conn_data *conn,
279 sqlite3_stmt *sql_vm, int numcols)
280 {
281 int i;
282 cur_data *cur = (cur_data*)lua_newuserdata(L, sizeof(cur_data));
283 luasql_setmeta (L, LUASQL_CURSOR_SQLITE);
284
285 /* increment cursor count for the connection creating this cursor */
286 conn->cur_counter++;
287
288 /* fill in structure */
289 cur->closed = 0;
290 cur->conn = LUA_NOREF;
291 cur->numcols = numcols;
292 cur->colnames = LUA_NOREF;
293 cur->coltypes = LUA_NOREF;
294 cur->sql_vm = sql_vm;
295 cur->conn_data = conn;
296
297 lua_pushvalue(L, o);
298 cur->conn = luaL_ref(L, LUA_REGISTRYINDEX);
299
300 /* create table with column names */
301 lua_newtable(L);
302 for (i = 0; i < numcols;)
303 {
304 lua_pushstring(L, sqlite3_column_name(sql_vm, i));
305 lua_rawseti(L, -2, ++i);
306 }
307 cur->colnames = luaL_ref(L, LUA_REGISTRYINDEX);
308
309 /* create table with column types */
310 lua_newtable(L);
311 for (i = 0; i < numcols;)
312 {
313 lua_pushstring(L, sqlite3_column_decltype(sql_vm, i));
314 lua_rawseti(L, -2, ++i);
315 }
316 cur->coltypes = luaL_ref(L, LUA_REGISTRYINDEX);
317
318 return 1;
319 }
320
321
322 /*
323 ** Connection object collector function
324 */
conn_gc(lua_State * L)325 static int conn_gc(lua_State *L)
326 {
327 conn_data *conn = (conn_data *)luaL_checkudata(L, 1, LUASQL_CONNECTION_SQLITE);
328 if (conn != NULL && !(conn->closed))
329 {
330 if (conn->cur_counter > 0)
331 return luaL_error (L, LUASQL_PREFIX"there are open cursors");
332
333 /* Nullify structure fields. */
334 conn->closed = 1;
335 luaL_unref(L, LUA_REGISTRYINDEX, conn->env);
336 sqlite3_close(conn->sql_conn);
337 }
338 return 0;
339 }
340
341
342 /*
343 ** Close a Connection object.
344 */
conn_close(lua_State * L)345 static int conn_close(lua_State *L)
346 {
347 conn_data *conn = (conn_data *)luaL_checkudata(L, 1, LUASQL_CONNECTION_SQLITE);
348 luaL_argcheck (L, conn != NULL, 1, LUASQL_PREFIX"connection expected");
349 if (conn->closed)
350 {
351 lua_pushboolean(L, 0);
352 return 1;
353 }
354 conn_gc(L);
355 lua_pushboolean(L, 1);
356 return 1;
357 }
358
conn_escape(lua_State * L)359 static int conn_escape(lua_State *L)
360 {
361 const char *from = luaL_checklstring (L, 2, 0);
362 char *escaped = sqlite3_mprintf("%q", from);
363 if (escaped == NULL)
364 {
365 lua_pushnil(L);
366 }
367 else
368 {
369 lua_pushstring(L, escaped);
370 sqlite3_free(escaped);
371 }
372 return 1;
373 }
374
375 /*
376 ** Execute an SQL statement.
377 ** Return a Cursor object if the statement is a query, otherwise
378 ** return the number of tuples affected by the statement.
379 */
conn_execute(lua_State * L)380 static int conn_execute(lua_State *L)
381 {
382 conn_data *conn = getconnection(L);
383 const char *statement = luaL_checkstring(L, 2);
384 int res;
385 sqlite3_stmt *vm;
386 const char *errmsg;
387 int numcols;
388 const char *tail;
389
390 #if SQLITE_VERSION_NUMBER > 3006013
391 res = sqlite3_prepare_v2(conn->sql_conn, statement, -1, &vm, &tail);
392 #else
393 res = sqlite3_prepare(conn->sql_conn, statement, -1, &vm, &tail);
394 #endif
395 if (res != SQLITE_OK)
396 {
397 errmsg = sqlite3_errmsg(conn->sql_conn);
398 return luasql_faildirect(L, errmsg);
399 }
400
401 /* process first result to retrive query information and type */
402 res = sqlite3_step(vm);
403 numcols = sqlite3_column_count(vm);
404
405 /* real query? if empty, must have numcols!=0 */
406 if ((res == SQLITE_ROW) || ((res == SQLITE_DONE) && numcols))
407 {
408 sqlite3_reset(vm);
409 return create_cursor(L, 1, conn, vm, numcols);
410 }
411
412 if (res == SQLITE_DONE) /* and numcols==0, INSERT,UPDATE,DELETE statement */
413 {
414 sqlite3_finalize(vm);
415 /* return number of columns changed */
416 lua_pushnumber(L, sqlite3_changes(conn->sql_conn));
417 return 1;
418 }
419
420 /* error */
421 errmsg = sqlite3_errmsg(conn->sql_conn);
422 sqlite3_finalize(vm);
423 return luasql_faildirect(L, errmsg);
424 }
425
426
427 /*
428 ** Commit the current transaction.
429 */
conn_commit(lua_State * L)430 static int conn_commit(lua_State *L)
431 {
432 char *errmsg;
433 conn_data *conn = getconnection(L);
434 int res;
435 const char *sql = "COMMIT";
436
437 if (conn->auto_commit == 0) sql = "COMMIT;BEGIN";
438
439 res = sqlite3_exec(conn->sql_conn, sql, NULL, NULL, &errmsg);
440
441 if (res != SQLITE_OK)
442 {
443 lua_pushnil(L);
444 lua_pushliteral(L, LUASQL_PREFIX);
445 lua_pushstring(L, errmsg);
446 sqlite3_free(errmsg);
447 lua_concat(L, 2);
448 return 2;
449 }
450 lua_pushboolean(L, 1);
451 return 1;
452 }
453
454
455 /*
456 ** Rollback the current transaction.
457 */
conn_rollback(lua_State * L)458 static int conn_rollback(lua_State *L)
459 {
460 char *errmsg;
461 conn_data *conn = getconnection(L);
462 int res;
463 const char *sql = "ROLLBACK";
464
465 if (conn->auto_commit == 0) sql = "ROLLBACK;BEGIN";
466
467 res = sqlite3_exec(conn->sql_conn, sql, NULL, NULL, &errmsg);
468 if (res != SQLITE_OK)
469 {
470 lua_pushnil(L);
471 lua_pushliteral(L, LUASQL_PREFIX);
472 lua_pushstring(L, errmsg);
473 sqlite3_free(errmsg);
474 lua_concat(L, 2);
475 return 2;
476 }
477 lua_pushboolean(L, 1);
478 return 1;
479 }
480
conn_getlastautoid(lua_State * L)481 static int conn_getlastautoid(lua_State *L)
482 {
483 conn_data *conn = getconnection(L);
484 lua_pushnumber(L, sqlite3_last_insert_rowid(conn->sql_conn));
485 return 1;
486 }
487
488
489 /*
490 ** Set "auto commit" property of the connection.
491 ** If 'true', then rollback current transaction.
492 ** If 'false', then start a new transaction.
493 */
conn_setautocommit(lua_State * L)494 static int conn_setautocommit(lua_State *L)
495 {
496 conn_data *conn = getconnection(L);
497 if (lua_toboolean(L, 2))
498 {
499 conn->auto_commit = 1;
500 /* undo active transaction - ignore errors */
501 (void) sqlite3_exec(conn->sql_conn, "ROLLBACK", NULL, NULL, NULL);
502 }
503 else
504 {
505 char *errmsg;
506 int res;
507 conn->auto_commit = 0;
508 res = sqlite3_exec(conn->sql_conn, "BEGIN", NULL, NULL, &errmsg);
509 if (res != SQLITE_OK)
510 {
511 lua_pushliteral(L, LUASQL_PREFIX);
512 lua_pushstring(L, errmsg);
513 sqlite3_free(errmsg);
514 lua_concat(L, 2);
515 lua_error(L);
516 }
517 }
518 lua_pushboolean(L, 1);
519 return 1;
520 }
521
522
523 /*
524 ** Create a new Connection object and push it on top of the stack.
525 */
create_connection(lua_State * L,int env,sqlite3 * sql_conn)526 static int create_connection(lua_State *L, int env, sqlite3 *sql_conn)
527 {
528 conn_data *conn = (conn_data*)lua_newuserdata(L, sizeof(conn_data));
529 luasql_setmeta(L, LUASQL_CONNECTION_SQLITE);
530
531 /* fill in structure */
532 conn->closed = 0;
533 conn->env = LUA_NOREF;
534 conn->auto_commit = 1;
535 conn->sql_conn = sql_conn;
536 conn->cur_counter = 0;
537 lua_pushvalue (L, env);
538 conn->env = luaL_ref (L, LUA_REGISTRYINDEX);
539 return 1;
540 }
541
542
543 /*
544 ** Connects to a data source.
545 */
env_connect(lua_State * L)546 static int env_connect(lua_State *L)
547 {
548 const char *sourcename;
549 sqlite3 *conn;
550 const char *errmsg;
551 int res;
552 getenvironment(L); /* validate environment */
553
554 sourcename = luaL_checkstring(L, 2);
555
556 #if SQLITE_VERSION_NUMBER > 3006013
557 if (strstr(sourcename, ":memory:")) /* TODO: rework this and get/add param 'flag' for sqlite3_open_v2 - see TODO below */
558 {
559 res = sqlite3_open_v2(sourcename, &conn, SQLITE_OPEN_READWRITE | SQLITE_OPEN_MEMORY, NULL);
560 }
561 else
562 {
563 res = sqlite3_open_v2(sourcename, &conn, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
564 }
565 #else
566 res = sqlite3_open(sourcename, &conn);
567 #endif
568 if (res != SQLITE_OK)
569 {
570 errmsg = sqlite3_errmsg(conn);
571 luasql_faildirect(L, errmsg);
572 sqlite3_close(conn);
573 return 2;
574 }
575
576 if (lua_isnumber(L, 3)) {
577 sqlite3_busy_timeout(conn, lua_tonumber(L,3)); /* TODO: remove this */
578 }
579
580 return create_connection(L, 1, conn);
581 }
582
583
584 /*
585 ** Environment object collector function.
586 */
env_gc(lua_State * L)587 static int env_gc (lua_State *L)
588 {
589 env_data *env = (env_data *)luaL_checkudata(L, 1, LUASQL_ENVIRONMENT_SQLITE);
590 if (env != NULL && !(env->closed))
591 env->closed = 1;
592 return 0;
593 }
594
595
596 /*
597 ** Close environment object.
598 */
env_close(lua_State * L)599 static int env_close (lua_State *L)
600 {
601 env_data *env = (env_data *)luaL_checkudata(L, 1, LUASQL_ENVIRONMENT_SQLITE);
602 luaL_argcheck(L, env != NULL, 1, LUASQL_PREFIX"environment expected");
603 if (env->closed) {
604 lua_pushboolean(L, 0);
605 return 1;
606 }
607 env_gc(L);
608 lua_pushboolean(L, 1);
609 return 1;
610 }
611
612
613 /*
614 ** Sets the timeout for a lock in the connection.
615 static int opts_settimeout (lua_State *L)
616 {
617 conn_data *conn = getconnection(L);
618 int milisseconds = luaL_checknumber(L, 2);
619 lua_pushnumber(L, sqlite3_busy_timeout(conn->sql_conn, milisseconds));
620 return 1;
621 }
622 */
623
624 /*
625 ** Create metatables for each class of object.
626 */
create_metatables(lua_State * L)627 static void create_metatables (lua_State *L)
628 {
629 struct luaL_Reg environment_methods[] = {
630 {"__gc", env_gc},
631 {"close", env_close},
632 {"connect", env_connect},
633 {NULL, NULL},
634 };
635 struct luaL_Reg connection_methods[] = {
636 {"__gc", conn_gc},
637 {"close", conn_close},
638 {"escape", conn_escape},
639 {"execute", conn_execute},
640 {"commit", conn_commit},
641 {"rollback", conn_rollback},
642 {"setautocommit", conn_setautocommit},
643 {"getlastautoid", conn_getlastautoid},
644 {NULL, NULL},
645 };
646 struct luaL_Reg cursor_methods[] = {
647 {"__gc", cur_gc},
648 {"close", cur_close},
649 {"getcolnames", cur_getcolnames},
650 {"getcoltypes", cur_getcoltypes},
651 {"fetch", cur_fetch},
652 {NULL, NULL},
653 };
654 luasql_createmeta(L, LUASQL_ENVIRONMENT_SQLITE, environment_methods);
655 luasql_createmeta(L, LUASQL_CONNECTION_SQLITE, connection_methods);
656 luasql_createmeta(L, LUASQL_CURSOR_SQLITE, cursor_methods);
657 lua_pop (L, 3);
658 }
659
660 /*
661 ** Creates an Environment and returns it.
662 */
create_environment(lua_State * L)663 static int create_environment (lua_State *L)
664 {
665 env_data *env = (env_data *)lua_newuserdata(L, sizeof(env_data));
666 luasql_setmeta(L, LUASQL_ENVIRONMENT_SQLITE);
667
668 /* fill in structure */
669 env->closed = 0;
670 return 1;
671 }
672
673
674 /*
675 ** Creates the metatables for the objects and registers the
676 ** driver open method.
677 */
luaopen_luasql_sqlite3(lua_State * L)678 LUASQL_API int luaopen_luasql_sqlite3(lua_State *L)
679 {
680 struct luaL_Reg driver[] = {
681 {"sqlite3", create_environment},
682 {NULL, NULL},
683 };
684 create_metatables (L);
685 lua_newtable (L);
686 luaL_setfuncs (L, driver, 0);
687 luasql_set_info (L);
688 lua_pushliteral (L, "_CLIENTVERSION");
689 lua_pushliteral (L, SQLITE_VERSION);
690 lua_settable (L, -3);
691 return 1;
692 }
693