Tue, 02 Dec 2008 08:10:54 +0000
Bugfix: PSQL array returns were not being indexed properly.
Changes:
* statement:fetch() returns an iterator
* statement:row() added to replace old statement:fetch() behaviour
* statement:fetch() and statement:row() now take an optional boolean value to either return an array(false, default) or a hashmap (true)
1 | 1 | #include "dbd_sqlite3.h" |
2 | ||
2 | 3 | /* |
4 | * Converts SQLite types to Lua types | |
5 | */ | |
1 | 6 | static lua_push_type_t sqlite_to_lua_push(unsigned int sqlite_type) { |
7 | lua_push_type_t lua_type; | |
8 | ||
9 | switch(sqlite_type) { | |
10 | case SQLITE_NULL: | |
11 | lua_type = LUA_PUSH_NIL; | |
12 | break; | |
13 | ||
14 | case SQLITE_INTEGER: | |
15 | lua_type = LUA_PUSH_INTEGER; | |
16 | break; | |
17 | ||
18 | case SQLITE_FLOAT: | |
19 | lua_type = LUA_PUSH_NUMBER; | |
20 | break; | |
21 | ||
22 | default: | |
23 | lua_type = LUA_PUSH_STRING; | |
24 | } | |
25 | ||
26 | return lua_type; | |
27 | } | |
28 | ||
2 | 29 | /* |
30 | * runs sqlite3_step on a statement handle | |
31 | */ | |
1 | 32 | static int step(statement_t *statement) { |
33 | int res = sqlite3_step(statement->stmt); | |
34 | ||
35 | if (res == SQLITE_DONE) { | |
36 | statement->more_data = 0; | |
37 | return 1; | |
38 | } else if (res == SQLITE_ROW) { | |
39 | statement->more_data = 1; | |
40 | return 1; | |
41 | } | |
42 | ||
43 | return 0; | |
44 | } | |
45 | ||
2 | 46 | /* |
47 | * success = statement:close() | |
48 | */ | |
3 | 49 | int statement_close(lua_State *L) { |
1 | 50 | statement_t *statement = (statement_t *)luaL_checkudata(L, 1, DBD_SQLITE_STATEMENT); |
51 | int ok = 0; | |
52 | ||
53 | if (statement->stmt) { | |
54 | if (sqlite3_finalize(statement->stmt) == SQLITE_OK) { | |
55 | ok = 1; | |
56 | } | |
3 | 57 | |
58 | statement->stmt = NULL; | |
1 | 59 | } |
60 | ||
61 | lua_pushboolean(L, ok); | |
62 | return 1; | |
63 | } | |
64 | ||
2 | 65 | /* |
3 | 66 | * success,err = statement:execute(...) |
2 | 67 | */ |
3 | 68 | int statement_execute(lua_State *L) { |
1 | 69 | int n = lua_gettop(L); |
70 | statement_t *statement = (statement_t *)luaL_checkudata(L, 1, DBD_SQLITE_STATEMENT); | |
71 | int p; | |
10 | 72 | int errflag = 0; |
73 | const char *errstr = NULL; | |
74 | int expected_params; | |
75 | int num_bind_params = n - 1; | |
3 | 76 | |
77 | if (!statement->stmt) { | |
78 | lua_pushboolean(L, 0); | |
4 | 79 | lua_pushstring(L, DBI_ERR_EXECUTE_INVALID); |
3 | 80 | return 2; |
81 | } | |
1 | 82 | |
2 | 83 | /* |
84 | * reset the handle before binding params | |
85 | * this wil be a NOP if the handle has not | |
86 | * been executed | |
87 | */ | |
1 | 88 | if (sqlite3_reset(statement->stmt) != SQLITE_OK) { |
89 | lua_pushboolean(L, 0); | |
4 | 90 | lua_pushfstring(L, DBI_ERR_EXECUTE_FAILED, sqlite3_errmsg(statement->sqlite)); |
3 | 91 | return 2; |
1 | 92 | } |
93 | ||
10 | 94 | expected_params = sqlite3_bind_parameter_count(statement->stmt); |
95 | if (expected_params != num_bind_params) { | |
96 | /* | |
97 | * sqlite3_reset does not handle this condition, | |
98 | * and the client library will fill unset params | |
99 | * with NULLs | |
100 | */ | |
101 | lua_pushboolean(L, 0); | |
102 | lua_pushfstring(L, DBI_ERR_PARAM_MISCOUNT, expected_params, num_bind_params); | |
103 | return 2; | |
104 | } | |
105 | ||
1 | 106 | for (p = 2; p <= n; p++) { |
107 | int i = p - 1; | |
9 | 108 | int type = lua_type(L, p); |
10 | 109 | char err[64]; |
9 | 110 | |
111 | switch(type) { | |
112 | case LUA_TNIL: | |
10 | 113 | errflag = sqlite3_bind_null(statement->stmt, i) != SQLITE_OK; |
9 | 114 | break; |
115 | case LUA_TNUMBER: | |
10 | 116 | errflag = sqlite3_bind_double(statement->stmt, i, lua_tonumber(L, p)) != SQLITE_OK; |
9 | 117 | break; |
118 | case LUA_TSTRING: | |
10 | 119 | errflag = sqlite3_bind_text(statement->stmt, i, lua_tostring(L, p), -1, SQLITE_STATIC) != SQLITE_OK; |
9 | 120 | break; |
121 | case LUA_TBOOLEAN: | |
10 | 122 | errflag = sqlite3_bind_int(statement->stmt, i, lua_toboolean(L, p)) != SQLITE_OK; |
9 | 123 | break; |
124 | default: | |
125 | /* | |
126 | * Unknown/unsupported value type | |
127 | */ | |
10 | 128 | errflag = 1; |
129 | snprintf(err, sizeof(err)-1, DBI_ERR_BINDING_TYPE_ERR, lua_typename(L, type)); | |
130 | errstr = err; | |
1 | 131 | } |
3 | 132 | |
10 | 133 | if (errflag) |
3 | 134 | break; |
1 | 135 | } |
136 | ||
10 | 137 | if (errflag) { |
3 | 138 | lua_pushboolean(L, 0); |
10 | 139 | if (errstr) |
140 | lua_pushfstring(L, DBI_ERR_BINDING_PARAMS, errstr); | |
141 | else | |
142 | lua_pushfstring(L, DBI_ERR_BINDING_PARAMS, sqlite3_errmsg(statement->sqlite)); | |
143 | ||
3 | 144 | return 2; |
145 | } | |
146 | ||
4 | 147 | if (!step(statement)) { |
148 | lua_pushboolean(L, 0); | |
149 | lua_pushfstring(L, DBI_ERR_EXECUTE_FAILED, sqlite3_errmsg(statement->sqlite)); | |
5 | 150 | return 2; |
4 | 151 | } |
152 | ||
3 | 153 | lua_pushboolean(L, 1); |
1 | 154 | return 1; |
155 | } | |
156 | ||
2 | 157 | /* |
158 | * must be called after an execute | |
159 | */ | |
11
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
160 | static int statement_fetch_impl(lua_State *L, statement_t *statement, int named_columns) { |
1 | 161 | int num_columns; |
162 | ||
3 | 163 | if (!statement->stmt) { |
4 | 164 | luaL_error(L, DBI_ERR_FETCH_INVALID); |
3 | 165 | return 0; |
166 | } | |
167 | ||
1 | 168 | if (!statement->more_data) { |
2 | 169 | /* |
170 | * Result set is empty, or not result set returned | |
171 | */ | |
172 | ||
1 | 173 | lua_pushnil(L); |
174 | return 1; | |
175 | } | |
176 | ||
177 | num_columns = sqlite3_column_count(statement->stmt); | |
178 | ||
179 | if (num_columns) { | |
180 | int i; | |
181 | int d = 1; | |
182 | ||
183 | lua_newtable(L); | |
184 | ||
185 | for (i = 0; i < num_columns; i++) { | |
186 | lua_push_type_t lua_push = sqlite_to_lua_push(sqlite3_column_type(statement->stmt, i)); | |
187 | const char *name = sqlite3_column_name(statement->stmt, i); | |
188 | ||
189 | if (lua_push == LUA_PUSH_NIL) { | |
190 | if (named_columns) { | |
191 | LUA_PUSH_ATTRIB_NIL(name); | |
192 | } else { | |
193 | LUA_PUSH_ARRAY_NIL(d); | |
194 | } | |
195 | } else if (lua_push == LUA_PUSH_INTEGER) { | |
196 | int val = sqlite3_column_int(statement->stmt, i); | |
197 | ||
198 | if (named_columns) { | |
199 | LUA_PUSH_ATTRIB_INT(name, val); | |
200 | } else { | |
201 | LUA_PUSH_ARRAY_INT(d, val); | |
202 | } | |
203 | } else if (lua_push == LUA_PUSH_NUMBER) { | |
204 | double val = sqlite3_column_double(statement->stmt, i); | |
205 | ||
206 | if (named_columns) { | |
207 | LUA_PUSH_ATTRIB_FLOAT(name, val); | |
208 | } else { | |
209 | LUA_PUSH_ARRAY_FLOAT(d, val); | |
210 | } | |
211 | } else if (lua_push == LUA_PUSH_STRING) { | |
212 | const char *val = (const char *)sqlite3_column_text(statement->stmt, i); | |
213 | ||
214 | if (named_columns) { | |
215 | LUA_PUSH_ATTRIB_STRING(name, val); | |
216 | } else { | |
217 | LUA_PUSH_ARRAY_STRING(d, val); | |
218 | } | |
219 | } else if (lua_push == LUA_PUSH_BOOLEAN) { | |
220 | int val = sqlite3_column_int(statement->stmt, i); | |
221 | ||
222 | if (named_columns) { | |
223 | LUA_PUSH_ATTRIB_BOOL(name, val); | |
224 | } else { | |
225 | LUA_PUSH_ARRAY_BOOL(d, val); | |
226 | } | |
227 | } else { | |
4 | 228 | luaL_error(L, DBI_ERR_UNKNOWN_PUSH); |
1 | 229 | } |
230 | } | |
2 | 231 | } else { |
232 | /* | |
233 | * no columns returned by statement? | |
234 | */ | |
235 | lua_pushnil(L); | |
1 | 236 | } |
237 | ||
238 | if (step(statement) == 0) { | |
239 | if (sqlite3_reset(statement->stmt) != SQLITE_OK) { | |
2 | 240 | /* |
241 | * reset needs to be called to retrieve the 'real' error message | |
242 | */ | |
4 | 243 | luaL_error(L, DBI_ERR_FETCH_FAILED, sqlite3_errmsg(statement->sqlite)); |
1 | 244 | } |
245 | } | |
246 | ||
247 | return 1; | |
248 | } | |
249 | ||
11
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
250 | static int next_iterator(lua_State *L) { |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
251 | statement_t *statement = (statement_t *)luaL_checkudata(L, lua_upvalueindex(1), DBD_SQLITE_STATEMENT); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
252 | int named_columns = lua_toboolean(L, lua_upvalueindex(2)); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
253 | |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
254 | return statement_fetch_impl(L, statement, named_columns); |
1 | 255 | } |
256 | ||
2 | 257 | /* |
11
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
258 | * iterfunc = statement:fetch(named_indexes) |
2 | 259 | */ |
11
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
260 | |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
261 | static int statement_fetch(lua_State *L) { |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
262 | if (lua_gettop(L) == 1) { |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
263 | lua_pushvalue(L, 1); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
264 | lua_pushboolean(L, 0); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
265 | } else { |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
266 | lua_pushvalue(L, 1); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
267 | lua_pushboolean(L, lua_toboolean(L, 2)); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
268 | } |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
269 | |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
270 | lua_pushcclosure(L, next_iterator, 2); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
271 | return 1; |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
272 | } |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
273 | |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
274 | /* |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
275 | * table = statement:row(named_indexes) |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
276 | */ |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
277 | static int statement_row(lua_State *L) { |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
278 | statement_t *statement = (statement_t *)luaL_checkudata(L, 1, DBD_SQLITE_STATEMENT); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
279 | int named_columns = lua_toboolean(L, 2); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
280 | |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
281 | return statement_fetch_impl(L, statement, named_columns); |
1 | 282 | } |
283 | ||
2 | 284 | /* |
285 | * __gc | |
286 | */ | |
1 | 287 | static int statement_gc(lua_State *L) { |
288 | /* always free the handle */ | |
289 | statement_close(L); | |
290 | ||
291 | return 0; | |
292 | } | |
293 | ||
294 | int dbd_sqlite3_statement_create(lua_State *L, connection_t *conn, const char *sql_query) { | |
295 | statement_t *statement = NULL; | |
296 | ||
297 | statement = (statement_t *)lua_newuserdata(L, sizeof(statement_t)); | |
298 | statement->sqlite = conn->sqlite; | |
299 | statement->stmt = NULL; | |
300 | statement->more_data = 0; | |
301 | ||
302 | if (sqlite3_prepare_v2(statement->sqlite, sql_query, strlen(sql_query), &statement->stmt, NULL) != SQLITE_OK) { | |
303 | lua_pushnil(L); | |
4 | 304 | lua_pushfstring(L, DBI_ERR_PREP_STATEMENT, sqlite3_errmsg(statement->sqlite)); |
3 | 305 | return 2; |
1 | 306 | } |
307 | ||
308 | luaL_getmetatable(L, DBD_SQLITE_STATEMENT); | |
309 | lua_setmetatable(L, -2); | |
310 | return 1; | |
311 | } | |
312 | ||
313 | int dbd_sqlite3_statement(lua_State *L) { | |
2 | 314 | static const luaL_Reg statement_methods[] = { |
315 | {"close", statement_close}, | |
316 | {"execute", statement_execute}, | |
317 | {"fetch", statement_fetch}, | |
11
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
318 | {"row", statement_row}, |
2 | 319 | {NULL, NULL} |
320 | }; | |
321 | ||
322 | static const luaL_Reg statement_class_methods[] = { | |
323 | {NULL, NULL} | |
324 | }; | |
325 | ||
1 | 326 | luaL_newmetatable(L, DBD_SQLITE_STATEMENT); |
327 | luaL_register(L, 0, statement_methods); | |
328 | lua_pushvalue(L,-1); | |
329 | lua_setfield(L, -2, "__index"); | |
330 | ||
331 | lua_pushcfunction(L, statement_gc); | |
332 | lua_setfield(L, -2, "__gc"); | |
333 | ||
334 | luaL_register(L, DBD_SQLITE_STATEMENT, statement_class_methods); | |
335 | ||
336 | return 1; | |
337 | } |