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