1 /*
2 ** LuaSQL, MySQL driver
3 ** Authors: Eduardo Quintao
4 ** See Copyright Notice in license.html
5 ** $Id: ls_mysql.c,v 1.31 2009/02/07 23:16:23 tomas Exp $
6 */
7
8 #include <assert.h>
9 #include <stdio.h>
10 #include <stdlib.h>
11 #include <string.h>
12 #include <ctype.h>
13
14 #ifdef WIN32
15 #include <winsock2.h>
16 #define NO_CLIENT_LONG_LONG
17 #endif
18
19 #include "mysql.h"
20 #include "errmsg.h"
21
22 #include "lua.h"
23 #include "lauxlib.h"
24
25 #include "luasql.h"
26
27 #define LUASQL_ENVIRONMENT_MYSQL "MySQL environment"
28 #define LUASQL_CONNECTION_MYSQL "MySQL connection"
29 #define LUASQL_CURSOR_MYSQL "MySQL cursor"
30
31 /* For compat with old version 4.0 */
32 #if (MYSQL_VERSION_ID < 40100)
33 #define MYSQL_TYPE_VAR_STRING FIELD_TYPE_VAR_STRING
34 #define MYSQL_TYPE_STRING FIELD_TYPE_STRING
35 #define MYSQL_TYPE_DECIMAL FIELD_TYPE_DECIMAL
36 #define MYSQL_TYPE_SHORT FIELD_TYPE_SHORT
37 #define MYSQL_TYPE_LONG FIELD_TYPE_LONG
38 #define MYSQL_TYPE_FLOAT FIELD_TYPE_FLOAT
39 #define MYSQL_TYPE_DOUBLE FIELD_TYPE_DOUBLE
40 #define MYSQL_TYPE_LONGLONG FIELD_TYPE_LONGLONG
41 #define MYSQL_TYPE_INT24 FIELD_TYPE_INT24
42 #define MYSQL_TYPE_YEAR FIELD_TYPE_YEAR
43 #define MYSQL_TYPE_TINY FIELD_TYPE_TINY
44 #define MYSQL_TYPE_TINY_BLOB FIELD_TYPE_TINY_BLOB
45 #define MYSQL_TYPE_MEDIUM_BLOB FIELD_TYPE_MEDIUM_BLOB
46 #define MYSQL_TYPE_LONG_BLOB FIELD_TYPE_LONG_BLOB
47 #define MYSQL_TYPE_BLOB FIELD_TYPE_BLOB
48 #define MYSQL_TYPE_DATE FIELD_TYPE_DATE
49 #define MYSQL_TYPE_NEWDATE FIELD_TYPE_NEWDATE
50 #define MYSQL_TYPE_DATETIME FIELD_TYPE_DATETIME
51 #define MYSQL_TYPE_TIME FIELD_TYPE_TIME
52 #define MYSQL_TYPE_TIMESTAMP FIELD_TYPE_TIMESTAMP
53 #define MYSQL_TYPE_ENUM FIELD_TYPE_ENUM
54 #define MYSQL_TYPE_SET FIELD_TYPE_SET
55 #define MYSQL_TYPE_NULL FIELD_TYPE_NULL
56
57 #define mysql_commit(_) ((void)_)
58 #define mysql_rollback(_) ((void)_)
59 #define mysql_autocommit(_,__) ((void)_)
60
61 #endif
62
63 typedef struct {
64 short closed;
65 } env_data;
66
67 typedef struct {
68 short closed;
69 int env; /* reference to environment */
70 MYSQL *my_conn;
71 } conn_data;
72
73 typedef struct {
74 short closed;
75 int conn; /* reference to connection */
76 int numcols; /* number of columns */
77 int colnames, coltypes; /* reference to column information tables */
78 MYSQL_RES *my_res;
79 } cur_data;
80
81 LUASQL_API int luaopen_luasql_mysql (lua_State *L);
82
83
84 /*
85 ** Check for valid environment.
86 */
getenvironment(lua_State * L)87 static env_data *getenvironment (lua_State *L) {
88 env_data *env = (env_data *)luaL_checkudata (L, 1, LUASQL_ENVIRONMENT_MYSQL);
89 luaL_argcheck (L, env != NULL, 1, "environment expected");
90 luaL_argcheck (L, !env->closed, 1, "environment is closed");
91 return env;
92 }
93
94
95 /*
96 ** Check for valid connection.
97 */
getconnection(lua_State * L)98 static conn_data *getconnection (lua_State *L) {
99 conn_data *conn = (conn_data *)luaL_checkudata (L, 1, LUASQL_CONNECTION_MYSQL);
100 luaL_argcheck (L, conn != NULL, 1, "connection expected");
101 luaL_argcheck (L, !conn->closed, 1, "connection is closed");
102 return conn;
103 }
104
105
106 /*
107 ** Check for valid cursor.
108 */
getcursor(lua_State * L)109 static cur_data *getcursor (lua_State *L) {
110 cur_data *cur = (cur_data *)luaL_checkudata (L, 1, LUASQL_CURSOR_MYSQL);
111 luaL_argcheck (L, cur != NULL, 1, "cursor expected");
112 luaL_argcheck (L, !cur->closed, 1, "cursor is closed");
113 return cur;
114 }
115
116
117 /*
118 ** Push the value of #i field of #tuple row.
119 */
pushvalue(lua_State * L,void * row,long int len)120 static void pushvalue (lua_State *L, void *row, long int len) {
121 if (row == NULL)
122 lua_pushnil (L);
123 else
124 lua_pushlstring (L, row, len);
125 }
126
127
128 /*
129 ** Get the internal database type of the given column.
130 */
getcolumntype(enum enum_field_types type)131 static char *getcolumntype (enum enum_field_types type) {
132
133 switch (type) {
134 case MYSQL_TYPE_VAR_STRING: case MYSQL_TYPE_STRING:
135 return "string";
136 case MYSQL_TYPE_DECIMAL: case MYSQL_TYPE_SHORT: case MYSQL_TYPE_LONG:
137 case MYSQL_TYPE_FLOAT: case MYSQL_TYPE_DOUBLE: case MYSQL_TYPE_LONGLONG:
138 case MYSQL_TYPE_INT24: case MYSQL_TYPE_YEAR: case MYSQL_TYPE_TINY:
139 return "number";
140 case MYSQL_TYPE_TINY_BLOB: case MYSQL_TYPE_MEDIUM_BLOB:
141 case MYSQL_TYPE_LONG_BLOB: case MYSQL_TYPE_BLOB:
142 return "binary";
143 case MYSQL_TYPE_DATE: case MYSQL_TYPE_NEWDATE:
144 return "date";
145 case MYSQL_TYPE_DATETIME:
146 return "datetime";
147 case MYSQL_TYPE_TIME:
148 return "time";
149 case MYSQL_TYPE_TIMESTAMP:
150 return "timestamp";
151 case MYSQL_TYPE_ENUM: case MYSQL_TYPE_SET:
152 return "set";
153 case MYSQL_TYPE_NULL:
154 return "null";
155 default:
156 return "undefined";
157 }
158 }
159
160
161 /*
162 ** Creates the lists of fields names and fields types.
163 */
create_colinfo(lua_State * L,cur_data * cur)164 static void create_colinfo (lua_State *L, cur_data *cur) {
165 MYSQL_FIELD *fields;
166 char typename[50];
167 int i;
168 fields = mysql_fetch_fields(cur->my_res);
169 lua_newtable (L); /* names */
170 lua_newtable (L); /* types */
171 for (i = 1; i <= cur->numcols; i++) {
172 lua_pushstring (L, fields[i-1].name);
173 lua_rawseti (L, -3, i);
174 sprintf (typename, "%.20s(%ld)", getcolumntype (fields[i-1].type), fields[i-1].length);
175 lua_pushstring(L, typename);
176 lua_rawseti (L, -2, i);
177 }
178 /* Stores the references in the cursor structure */
179 cur->coltypes = luaL_ref (L, LUA_REGISTRYINDEX);
180 cur->colnames = luaL_ref (L, LUA_REGISTRYINDEX);
181 }
182
183
184 /*
185 ** Closes the cursos and nullify all structure fields.
186 */
cur_nullify(lua_State * L,cur_data * cur)187 static void cur_nullify (lua_State *L, cur_data *cur) {
188 /* Nullify structure fields. */
189 cur->closed = 1;
190 mysql_free_result(cur->my_res);
191 luaL_unref (L, LUA_REGISTRYINDEX, cur->conn);
192 luaL_unref (L, LUA_REGISTRYINDEX, cur->colnames);
193 luaL_unref (L, LUA_REGISTRYINDEX, cur->coltypes);
194 }
195
196
197 /*
198 ** Get another row of the given cursor.
199 */
cur_fetch(lua_State * L)200 static int cur_fetch (lua_State *L) {
201 cur_data *cur = getcursor (L);
202 MYSQL_RES *res = cur->my_res;
203 unsigned long *lengths;
204 MYSQL_ROW row = mysql_fetch_row(res);
205 if (row == NULL) {
206 cur_nullify (L, cur);
207 lua_pushnil(L); /* no more results */
208 return 1;
209 }
210 lengths = mysql_fetch_lengths(res);
211
212 if (lua_istable (L, 2)) {
213 const char *opts = luaL_optstring (L, 3, "n");
214 if (strchr (opts, 'n') != NULL) {
215 /* Copy values to numerical indices */
216 int i;
217 for (i = 0; i < cur->numcols; i++) {
218 pushvalue (L, row[i], lengths[i]);
219 lua_rawseti (L, 2, i+1);
220 }
221 }
222 if (strchr (opts, 'a') != NULL) {
223 int i;
224 /* Check if colnames exists */
225 if (cur->colnames == LUA_NOREF)
226 create_colinfo(L, cur);
227 lua_rawgeti (L, LUA_REGISTRYINDEX, cur->colnames);/* Push colnames*/
228
229 /* Copy values to alphanumerical indices */
230 for (i = 0; i < cur->numcols; i++) {
231 lua_rawgeti(L, -1, i+1); /* push the field name */
232
233 /* Actually push the value */
234 pushvalue (L, row[i], lengths[i]);
235 lua_rawset (L, 2);
236 }
237 /* lua_pop(L, 1); Pops colnames table. Not needed */
238 }
239 lua_pushvalue(L, 2);
240 return 1; /* return table */
241 }
242 else {
243 int i;
244 luaL_checkstack (L, cur->numcols, LUASQL_PREFIX"too many columns");
245 for (i = 0; i < cur->numcols; i++)
246 pushvalue (L, row[i], lengths[i]);
247 return cur->numcols; /* return #numcols values */
248 }
249 }
250
251
252 /*
253 ** Cursor object collector function
254 */
cur_gc(lua_State * L)255 static int cur_gc (lua_State *L) {
256 cur_data *cur = (cur_data *)luaL_checkudata (L, 1, LUASQL_CURSOR_MYSQL);
257 if (cur != NULL && !(cur->closed))
258 cur_nullify (L, cur);
259 return 0;
260 }
261
262
263 /*
264 ** Close the cursor on top of the stack.
265 ** Return 1
266 */
cur_close(lua_State * L)267 static int cur_close (lua_State *L) {
268 cur_data *cur = (cur_data *)luaL_checkudata (L, 1, LUASQL_CURSOR_MYSQL);
269 luaL_argcheck (L, cur != NULL, 1, LUASQL_PREFIX"cursor expected");
270 if (cur->closed) {
271 lua_pushboolean (L, 0);
272 return 1;
273 }
274 cur_nullify (L, cur);
275 lua_pushboolean (L, 1);
276 return 1;
277 }
278
279
280 /*
281 ** Pushes a column information table on top of the stack.
282 ** If the table isn't built yet, call the creator function and stores
283 ** a reference to it on the cursor structure.
284 */
_pushtable(lua_State * L,cur_data * cur,size_t off)285 static void _pushtable (lua_State *L, cur_data *cur, size_t off) {
286 int *ref = (int *)((char *)cur + off);
287
288 /* If colnames or coltypes do not exist, create both. */
289 if (*ref == LUA_NOREF)
290 create_colinfo(L, cur);
291
292 /* Pushes the right table (colnames or coltypes) */
293 lua_rawgeti (L, LUA_REGISTRYINDEX, *ref);
294 }
295 #define pushtable(L,c,m) (_pushtable(L,c,offsetof(cur_data,m)))
296
297
298 /*
299 ** Return the list of field names.
300 */
cur_getcolnames(lua_State * L)301 static int cur_getcolnames (lua_State *L) {
302 pushtable (L, getcursor(L), colnames);
303 return 1;
304 }
305
306
307 /*
308 ** Return the list of field types.
309 */
cur_getcoltypes(lua_State * L)310 static int cur_getcoltypes (lua_State *L) {
311 pushtable (L, getcursor(L), coltypes);
312 return 1;
313 }
314
315
316 /*
317 ** Push the number of rows.
318 */
cur_numrows(lua_State * L)319 static int cur_numrows (lua_State *L) {
320 lua_pushinteger (L, (lua_Number)mysql_num_rows (getcursor(L)->my_res));
321 return 1;
322 }
323
324
325 /*
326 ** Create a new Cursor object and push it on top of the stack.
327 */
create_cursor(lua_State * L,int conn,MYSQL_RES * result,int cols)328 static int create_cursor (lua_State *L, int conn, MYSQL_RES *result, int cols) {
329 cur_data *cur = (cur_data *)lua_newuserdata(L, sizeof(cur_data));
330 luasql_setmeta (L, LUASQL_CURSOR_MYSQL);
331
332 /* fill in structure */
333 cur->closed = 0;
334 cur->conn = LUA_NOREF;
335 cur->numcols = cols;
336 cur->colnames = LUA_NOREF;
337 cur->coltypes = LUA_NOREF;
338 cur->my_res = result;
339 lua_pushvalue (L, conn);
340 cur->conn = luaL_ref (L, LUA_REGISTRYINDEX);
341
342 return 1;
343 }
344
345
conn_gc(lua_State * L)346 static int conn_gc (lua_State *L) {
347 conn_data *conn=(conn_data *)luaL_checkudata(L, 1, LUASQL_CONNECTION_MYSQL);
348 if (conn != NULL && !(conn->closed)) {
349 /* Nullify structure fields. */
350 conn->closed = 1;
351 luaL_unref (L, LUA_REGISTRYINDEX, conn->env);
352 mysql_close (conn->my_conn);
353 }
354 return 0;
355 }
356
357
358 /*
359 ** Close a Connection object.
360 */
conn_close(lua_State * L)361 static int conn_close (lua_State *L) {
362 conn_data *conn=(conn_data *)luaL_checkudata(L, 1, LUASQL_CONNECTION_MYSQL);
363 luaL_argcheck (L, conn != NULL, 1, LUASQL_PREFIX"connection expected");
364 if (conn->closed) {
365 lua_pushboolean (L, 0);
366 return 1;
367 }
368 conn_gc (L);
369 lua_pushboolean (L, 1);
370 return 1;
371 }
372
373 /*
374 ** Ping connection.
375 */
conn_ping(lua_State * L)376 static int conn_ping (lua_State *L) {
377 conn_data *conn=(conn_data *)luaL_checkudata(L, 1, LUASQL_CONNECTION_MYSQL);
378 luaL_argcheck (L, conn != NULL, 1, LUASQL_PREFIX"connection expected");
379 if (conn->closed) {
380 lua_pushboolean (L, 0);
381 return 1;
382 }
383 if (mysql_ping (conn->my_conn) == 0) {
384 lua_pushboolean (L, 1);
385 return 1;
386 } else if (mysql_errno (conn->my_conn) == CR_SERVER_GONE_ERROR) {
387 lua_pushboolean (L, 0);
388 return 1;
389 }
390 luaL_error(L, mysql_error(conn->my_conn));
391 return 0;
392 }
393
394
escape_string(lua_State * L)395 static int escape_string (lua_State *L) {
396 size_t size, new_size;
397 conn_data *conn = getconnection (L);
398 const char *from = luaL_checklstring(L, 2, &size);
399 char *to;
400 to = (char*)malloc(sizeof(char) * (2 * size + 1));
401 if(to) {
402 new_size = mysql_real_escape_string(conn->my_conn, to, from, size);
403 lua_pushlstring(L, to, new_size);
404 free(to);
405 return 1;
406 }
407 luaL_error(L, "could not allocate escaped string");
408 return 0;
409 }
410
411 /*
412 ** Execute an SQL statement.
413 ** Return a Cursor object if the statement is a query, otherwise
414 ** return the number of tuples affected by the statement.
415 */
conn_execute(lua_State * L)416 static int conn_execute (lua_State *L) {
417 conn_data *conn = getconnection (L);
418 size_t st_len;
419 const char *statement = luaL_checklstring (L, 2, &st_len);
420 if (mysql_real_query(conn->my_conn, statement, st_len))
421 /* error executing query */
422 return luasql_failmsg(L, "error executing query. MySQL: ", mysql_error(conn->my_conn));
423 else
424 {
425 MYSQL_RES *res = mysql_store_result(conn->my_conn);
426 unsigned int num_cols = mysql_field_count(conn->my_conn);
427
428 if (res) { /* tuples returned */
429 return create_cursor (L, 1, res, num_cols);
430 }
431 else { /* mysql_use_result() returned nothing; should it have? */
432 if(num_cols == 0) { /* no tuples returned */
433 /* query does not return data (it was not a SELECT) */
434 lua_pushinteger(L, mysql_affected_rows(conn->my_conn));
435 return 1;
436 }
437 else /* mysql_use_result() should have returned data */
438 return luasql_failmsg(L, "error retrieving result. MySQL: ", mysql_error(conn->my_conn));
439 }
440 }
441 }
442
443
444 /*
445 ** Commit the current transaction.
446 */
conn_commit(lua_State * L)447 static int conn_commit (lua_State *L) {
448 conn_data *conn = getconnection (L);
449 lua_pushboolean(L, !mysql_commit(conn->my_conn));
450 return 1;
451 }
452
453
454 /*
455 ** Rollback the current transaction.
456 */
conn_rollback(lua_State * L)457 static int conn_rollback (lua_State *L) {
458 conn_data *conn = getconnection (L);
459 lua_pushboolean(L, !mysql_rollback(conn->my_conn));
460 return 1;
461 }
462
463
464 /*
465 ** Set "auto commit" property of the connection. Modes ON/OFF
466 */
conn_setautocommit(lua_State * L)467 static int conn_setautocommit (lua_State *L) {
468 conn_data *conn = getconnection (L);
469 if (lua_toboolean (L, 2)) {
470 mysql_autocommit(conn->my_conn, 1); /* Set it ON */
471 }
472 else {
473 mysql_autocommit(conn->my_conn, 0);
474 }
475 lua_pushboolean(L, 1);
476 return 1;
477 }
478
479
480 /*
481 ** Get Last auto-increment id generated
482 */
conn_getlastautoid(lua_State * L)483 static int conn_getlastautoid (lua_State *L) {
484 conn_data *conn = getconnection(L);
485 lua_pushinteger(L, mysql_insert_id(conn->my_conn));
486 return 1;
487 }
488
489 /*
490 ** Create a new Connection object and push it on top of the stack.
491 */
create_connection(lua_State * L,int env,MYSQL * const my_conn)492 static int create_connection (lua_State *L, int env, MYSQL *const my_conn) {
493 conn_data *conn = (conn_data *)lua_newuserdata(L, sizeof(conn_data));
494 luasql_setmeta (L, LUASQL_CONNECTION_MYSQL);
495
496 /* fill in structure */
497 conn->closed = 0;
498 conn->env = LUA_NOREF;
499 conn->my_conn = my_conn;
500 lua_pushvalue (L, env);
501 conn->env = luaL_ref (L, LUA_REGISTRYINDEX);
502 return 1;
503 }
504
505
506 /*
507 ** Connects to a data source.
508 ** param: one string for each connection parameter, said
509 ** datasource, username, password, host and port.
510 */
env_connect(lua_State * L)511 static int env_connect (lua_State *L) {
512 const char *sourcename = luaL_checkstring(L, 2);
513 const char *username = luaL_optstring(L, 3, NULL);
514 const char *password = luaL_optstring(L, 4, NULL);
515 const char *host = luaL_optstring(L, 5, NULL);
516 const int port = luaL_optinteger(L, 6, 0);
517 const char *unix_socket = luaL_optstring(L, 7, NULL);
518 const long client_flag = (long)luaL_optinteger(L, 8, 0);
519 MYSQL *conn;
520 getenvironment(L); /* validade environment */
521
522 /* Try to init the connection object. */
523 conn = mysql_init(NULL);
524 if (conn == NULL)
525 return luasql_faildirect(L, "error connecting: Out of memory.");
526
527 if (!mysql_real_connect(conn, host, username, password,
528 sourcename, port, unix_socket, client_flag))
529 {
530 char error_msg[100];
531 strncpy (error_msg, mysql_error(conn), 99);
532 mysql_close (conn); /* Close conn if connect failed */
533 return luasql_failmsg (L, "error connecting to database. MySQL: ", error_msg);
534 }
535 return create_connection(L, 1, conn);
536 }
537
538
539 /*
540 **
541 */
env_gc(lua_State * L)542 static int env_gc (lua_State *L) {
543 env_data *env= (env_data *)luaL_checkudata (L, 1, LUASQL_ENVIRONMENT_MYSQL); if (env != NULL && !(env->closed))
544 env->closed = 1;
545 return 0;
546 }
547
548
549 /*
550 ** Close environment object.
551 */
env_close(lua_State * L)552 static int env_close (lua_State *L) {
553 env_data *env= (env_data *)luaL_checkudata (L, 1, LUASQL_ENVIRONMENT_MYSQL);
554 luaL_argcheck (L, env != NULL, 1, LUASQL_PREFIX"environment expected");
555 if (env->closed) {
556 lua_pushboolean (L, 0);
557 return 1;
558 }
559 mysql_library_end();
560 env->closed = 1;
561 lua_pushboolean (L, 1);
562 return 1;
563 }
564
565
566 /*
567 ** Create metatables for each class of object.
568 */
create_metatables(lua_State * L)569 static void create_metatables (lua_State *L) {
570 struct luaL_Reg environment_methods[] = {
571 {"__gc", env_gc},
572 {"close", env_close},
573 {"connect", env_connect},
574 {NULL, NULL},
575 };
576 struct luaL_Reg connection_methods[] = {
577 {"__gc", conn_gc},
578 {"close", conn_close},
579 {"ping", conn_ping},
580 {"escape", escape_string},
581 {"execute", conn_execute},
582 {"commit", conn_commit},
583 {"rollback", conn_rollback},
584 {"setautocommit", conn_setautocommit},
585 {"getlastautoid", conn_getlastautoid},
586 {NULL, NULL},
587 };
588 struct luaL_Reg cursor_methods[] = {
589 {"__gc", cur_gc},
590 {"close", cur_close},
591 {"getcolnames", cur_getcolnames},
592 {"getcoltypes", cur_getcoltypes},
593 {"fetch", cur_fetch},
594 {"numrows", cur_numrows},
595 {NULL, NULL},
596 };
597 luasql_createmeta (L, LUASQL_ENVIRONMENT_MYSQL, environment_methods);
598 luasql_createmeta (L, LUASQL_CONNECTION_MYSQL, connection_methods);
599 luasql_createmeta (L, LUASQL_CURSOR_MYSQL, cursor_methods);
600 lua_pop (L, 3);
601 }
602
603
604 /*
605 ** Creates an Environment and returns it.
606 */
create_environment(lua_State * L)607 static int create_environment (lua_State *L) {
608 env_data *env = (env_data *)lua_newuserdata(L, sizeof(env_data));
609 luasql_setmeta (L, LUASQL_ENVIRONMENT_MYSQL);
610
611 /* fill in structure */
612 env->closed = 0;
613 return 1;
614 }
615
616
617 /*
618 ** Creates the metatables for the objects and registers the
619 ** driver open method.
620 */
luaopen_luasql_mysql(lua_State * L)621 LUASQL_API int luaopen_luasql_mysql (lua_State *L) {
622 struct luaL_Reg driver[] = {
623 {"mysql", create_environment},
624 {NULL, NULL},
625 };
626 create_metatables (L);
627 lua_newtable(L);
628 luaL_setfuncs(L, driver, 0);
629 luasql_set_info (L);
630 lua_pushliteral (L, "_CLIENTVERSION");
631 #ifdef MARIADB_CLIENT_VERSION_STR
632 lua_pushliteral (L, MARIADB_CLIENT_VERSION_STR);
633 #else
634 lua_pushliteral (L, MYSQL_SERVER_VERSION);
635 #endif
636 /*lua_pushliteral (L, MYSQL_SERVER_VERSION);*/
637 lua_settable (L, -3);
638 return 1;
639 }
640