Tue, 02 Dec 2008 21:11:27 +0000
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
1 | 1 | #include "dbd_postgresql.h" |
2 | ||
2 | 3 | #define MAX_PLACEHOLDERS 9999 |
1 | 4 | #define MAX_PLACEHOLDER_SIZE (1+4) /* $\d{4} */ |
5 | ||
6 | static lua_push_type_t postgresql_to_lua_push(unsigned int postgresql_type) { | |
7 | lua_push_type_t lua_type; | |
8 | ||
9 | switch(postgresql_type) { | |
10 | case INT2OID: | |
11 | case INT4OID: | |
12 | lua_type = LUA_PUSH_INTEGER; | |
13 | break; | |
14 | ||
15 | case FLOAT4OID: | |
16 | case FLOAT8OID: | |
17 | lua_type = LUA_PUSH_NUMBER; | |
18 | break; | |
19 | ||
20 | case BOOLOID: | |
21 | lua_type = LUA_PUSH_BOOLEAN; | |
22 | break; | |
23 | ||
24 | default: | |
25 | lua_type = LUA_PUSH_STRING; | |
26 | } | |
27 | ||
28 | return lua_type; | |
29 | } | |
30 | ||
2 | 31 | /* |
32 | * replace '?' placeholders with $\d+ placeholders | |
33 | * to be compatible with PSQL API | |
34 | */ | |
1 | 35 | static char *replace_placeholders(lua_State *L, const char *sql) { |
36 | size_t len = strlen(sql); | |
37 | int num_placeholders = 0; | |
38 | int extra_space = 0; | |
39 | int i; | |
40 | char *newsql; | |
41 | int newpos = 1; | |
42 | int ph_num = 1; | |
43 | int in_quote = 0; | |
44 | ||
2 | 45 | /* |
46 | * dumb count of all '?' | |
47 | * this will match more placeholders than necessesary | |
48 | * but it's safer to allocate more placeholders at the | |
49 | * cost of a few bytes than risk a buffer overflow | |
50 | */ | |
1 | 51 | for (i = 1; i < len; i++) { |
52 | if (sql[i] == '?') { | |
53 | num_placeholders++; | |
54 | } | |
55 | } | |
56 | ||
2 | 57 | /* |
58 | * this is MAX_PLACEHOLDER_SIZE-1 because the '?' is | |
59 | * replaced with '$' | |
60 | */ | |
1 | 61 | extra_space = num_placeholders * (MAX_PLACEHOLDER_SIZE-1); |
62 | ||
2 | 63 | /* |
64 | * allocate a new string for the converted SQL statement | |
65 | */ | |
1 | 66 | newsql = malloc(sizeof(char) * (len+extra_space+1)); |
67 | memset(newsql, 0, sizeof(char) * (len+extra_space+1)); | |
68 | ||
2 | 69 | /* |
70 | * copy first char. In valid SQL this cannot be a placeholder | |
71 | */ | |
1 | 72 | newsql[0] = sql[0]; |
2 | 73 | |
74 | /* | |
75 | * only replace '?' not in a single quoted string | |
76 | */ | |
1 | 77 | for (i = 1; i < len; i++) { |
2 | 78 | /* |
79 | * don't change the quote flag if the ''' is preceded | |
80 | * bt a '\' to account for escaping | |
81 | */ | |
1 | 82 | if (sql[i] == '\'' && sql[i-1] != '\\') { |
83 | in_quote = !in_quote; | |
84 | } | |
85 | ||
86 | if (sql[i] == '?' && !in_quote) { | |
87 | size_t n; | |
88 | ||
89 | if (ph_num > MAX_PLACEHOLDERS) { | |
90 | luaL_error(L, "Sorry, you are using more than %d placeholders. Use ${num} format instead", MAX_PLACEHOLDERS); | |
91 | } | |
92 | ||
93 | n = snprintf(&newsql[newpos], MAX_PLACEHOLDER_SIZE, "$%u", ph_num++); | |
94 | ||
95 | newpos += n; | |
96 | } else { | |
97 | newsql[newpos] = sql[i]; | |
98 | newpos++; | |
99 | } | |
100 | } | |
101 | ||
2 | 102 | /* |
103 | * terminate string on the last position | |
104 | */ | |
1 | 105 | newsql[newpos] = '\0'; |
106 | ||
107 | /* fprintf(stderr, "[%s]\n", newsql); */ | |
108 | return newsql; | |
109 | } | |
110 | ||
2 | 111 | /* |
112 | * success = statement:close() | |
113 | */ | |
1 | 114 | static int statement_close(lua_State *L) { |
115 | statement_t *statement = (statement_t *)luaL_checkudata(L, 1, DBD_POSTGRESQL_STATEMENT); | |
116 | ||
117 | if (statement->result) { | |
118 | PQclear(statement->result); | |
119 | statement->result = NULL; | |
120 | } | |
121 | ||
122 | return 0; | |
123 | } | |
124 | ||
2 | 125 | /* |
126 | * success = statement:execute(...) | |
127 | */ | |
1 | 128 | static int statement_execute(lua_State *L) { |
129 | int n = lua_gettop(L); | |
130 | statement_t *statement = (statement_t *)luaL_checkudata(L, 1, DBD_POSTGRESQL_STATEMENT); | |
131 | int num_bind_params = n - 1; | |
132 | ExecStatusType status; | |
133 | int p; | |
10 | 134 | const char *errstr = NULL; |
1 | 135 | |
9 | 136 | const char **params; |
1 | 137 | PGresult *result = NULL; |
138 | ||
139 | statement->tuple = 0; | |
140 | ||
141 | params = malloc(num_bind_params * sizeof(params)); | |
11
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
142 | memset(params, 0, num_bind_params * sizeof(params)); |
1 | 143 | |
2 | 144 | /* |
145 | * convert and copy parameters into a string array | |
146 | */ | |
1 | 147 | for (p = 2; p <= n; p++) { |
148 | int i = p - 2; | |
10 | 149 | int type = lua_type(L, p); |
150 | char err[64]; | |
1 | 151 | |
10 | 152 | switch(type) { |
153 | case LUA_TNIL: | |
1 | 154 | params[i] = NULL; |
10 | 155 | break; |
156 | case LUA_TBOOLEAN: | |
157 | /* | |
158 | * boolean values in postgresql can either be | |
159 | * t/f or 1/0. Pass integer values rather than | |
160 | * strings to maintain semantic compatibility | |
161 | * with other DBD drivers that pass booleans | |
162 | * as integers. | |
163 | */ | |
164 | params[i] = lua_toboolean(L, p) ? "1" : "0"; | |
165 | break; | |
166 | case LUA_TNUMBER: | |
167 | case LUA_TSTRING: | |
168 | params[i] = lua_tostring(L, p); | |
169 | break; | |
170 | default: | |
171 | snprintf(err, sizeof(err)-1, DBI_ERR_BINDING_TYPE_ERR, lua_typename(L, type)); | |
172 | errstr = err; | |
173 | goto cleanup; | |
1 | 174 | } |
175 | } | |
176 | ||
177 | result = PQexecPrepared( | |
178 | statement->postgresql, | |
179 | statement->name, | |
180 | num_bind_params, | |
181 | (const char **)params, | |
182 | NULL, | |
183 | NULL, | |
184 | 0 | |
185 | ); | |
186 | ||
10 | 187 | cleanup: |
1 | 188 | free(params); |
189 | ||
10 | 190 | if (errstr) { |
191 | lua_pushboolean(L, 0); | |
192 | lua_pushfstring(L, DBI_ERR_BINDING_PARAMS, errstr); | |
193 | return 2; | |
194 | } | |
195 | ||
1 | 196 | if (!result) { |
197 | lua_pushboolean(L, 0); | |
4 | 198 | lua_pushfstring(L, DBI_ERR_ALLOC_RESULT, PQerrorMessage(statement->postgresql)); |
3 | 199 | return 2; |
1 | 200 | } |
201 | ||
202 | status = PQresultStatus(result); | |
203 | if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK) { | |
204 | lua_pushboolean(L, 0); | |
4 | 205 | lua_pushfstring(L, DBI_ERR_BINDING_EXEC, PQresultErrorMessage(result)); |
3 | 206 | return 2; |
1 | 207 | } |
208 | ||
209 | statement->result = result; | |
210 | ||
211 | lua_pushboolean(L, 1); | |
212 | return 1; | |
213 | } | |
214 | ||
2 | 215 | /* |
216 | * must be called after an execute | |
217 | */ | |
11
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
218 | static int statement_fetch_impl(lua_State *L, statement_t *statement, int named_columns) { |
1 | 219 | int tuple = statement->tuple++; |
220 | int i; | |
221 | int num_columns; | |
12
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
222 | int d = 1; |
1 | 223 | |
3 | 224 | if (!statement->result) { |
4 | 225 | luaL_error(L, DBI_ERR_FETCH_INVALID); |
3 | 226 | return 0; |
227 | } | |
228 | ||
1 | 229 | if (PQresultStatus(statement->result) != PGRES_TUPLES_OK) { |
230 | lua_pushnil(L); | |
231 | return 1; | |
232 | } | |
233 | ||
234 | if (tuple >= PQntuples(statement->result)) { | |
235 | lua_pushnil(L); /* no more results */ | |
236 | return 1; | |
237 | } | |
238 | ||
239 | num_columns = PQnfields(statement->result); | |
240 | lua_newtable(L); | |
241 | for (i = 0; i < num_columns; i++) { | |
242 | const char *name = PQfname(statement->result, i); | |
243 | ||
244 | if (PQgetisnull(statement->result, tuple, i)) { | |
245 | if (named_columns) { | |
246 | LUA_PUSH_ATTRIB_NIL(name); | |
247 | } else { | |
248 | LUA_PUSH_ARRAY_NIL(d); | |
249 | } | |
250 | } else { | |
251 | const char *value = PQgetvalue(statement->result, tuple, i); | |
252 | lua_push_type_t lua_push = postgresql_to_lua_push(PQftype(statement->result, i)); | |
253 | ||
2 | 254 | /* |
255 | * data is returned as strings from PSQL | |
9 | 256 | * convert them here into Lua types |
2 | 257 | */ |
258 | ||
1 | 259 | if (lua_push == LUA_PUSH_NIL) { |
260 | if (named_columns) { | |
261 | LUA_PUSH_ATTRIB_NIL(name); | |
262 | } else { | |
263 | LUA_PUSH_ARRAY_NIL(d); | |
264 | } | |
265 | } else if (lua_push == LUA_PUSH_INTEGER) { | |
266 | int val = atoi(value); | |
267 | ||
268 | if (named_columns) { | |
269 | LUA_PUSH_ATTRIB_INT(name, val); | |
270 | } else { | |
271 | LUA_PUSH_ARRAY_INT(d, val); | |
272 | } | |
273 | } else if (lua_push == LUA_PUSH_NUMBER) { | |
274 | double val = strtod(value, NULL); | |
275 | ||
276 | if (named_columns) { | |
277 | LUA_PUSH_ATTRIB_FLOAT(name, val); | |
278 | } else { | |
279 | LUA_PUSH_ARRAY_FLOAT(d, val); | |
280 | } | |
281 | } else if (lua_push == LUA_PUSH_STRING) { | |
282 | if (named_columns) { | |
283 | LUA_PUSH_ATTRIB_STRING(name, value); | |
284 | } else { | |
285 | LUA_PUSH_ARRAY_STRING(d, value); | |
286 | } | |
287 | } else if (lua_push == LUA_PUSH_BOOLEAN) { | |
2 | 288 | /* |
289 | * booleans are returned as a string | |
290 | * either 't' or 'f' | |
291 | */ | |
1 | 292 | int val = value[0] == 't' ? 1 : 0; |
293 | ||
294 | if (named_columns) { | |
295 | LUA_PUSH_ATTRIB_BOOL(name, val); | |
296 | } else { | |
297 | LUA_PUSH_ARRAY_BOOL(d, val); | |
298 | } | |
299 | } else { | |
4 | 300 | luaL_error(L, DBI_ERR_UNKNOWN_PUSH); |
1 | 301 | } |
302 | } | |
303 | } | |
304 | ||
305 | return 1; | |
306 | } | |
307 | ||
11
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
308 | |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
309 | static int next_iterator(lua_State *L) { |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
310 | statement_t *statement = (statement_t *)luaL_checkudata(L, lua_upvalueindex(1), DBD_POSTGRESQL_STATEMENT); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
311 | 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
|
312 | |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
313 | return statement_fetch_impl(L, statement, named_columns); |
1 | 314 | } |
315 | ||
2 | 316 | /* |
12
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
317 | * table = statement:fetch(named_indexes) |
2 | 318 | */ |
12
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
319 | static int statement_fetch(lua_State *L) { |
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
320 | statement_t *statement = (statement_t *)luaL_checkudata(L, 1, DBD_POSTGRESQL_STATEMENT); |
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
321 | int named_columns = lua_toboolean(L, 2); |
11
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
322 | |
12
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
323 | return statement_fetch_impl(L, statement, named_columns); |
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
324 | } |
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
325 | |
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
326 | /* |
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
327 | * iterfunc = statement:rows(named_indexes) |
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
328 | */ |
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
329 | static int statement_rows(lua_State *L) { |
11
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
330 | if (lua_gettop(L) == 1) { |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
331 | lua_pushvalue(L, 1); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
332 | lua_pushboolean(L, 0); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
333 | } else { |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
334 | lua_pushvalue(L, 1); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
335 | lua_pushboolean(L, lua_toboolean(L, 2)); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
336 | } |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
337 | |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
338 | lua_pushcclosure(L, next_iterator, 2); |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
339 | return 1; |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
340 | } |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
341 | |
b3e05e361f46
Bugfix: PSQL array returns were not being indexed properly.
nrich@ii.net
parents:
10
diff
changeset
|
342 | /* |
2 | 343 | * __gc |
344 | */ | |
1 | 345 | static int statement_gc(lua_State *L) { |
346 | /* always free the handle */ | |
347 | statement_close(L); | |
348 | ||
349 | return 0; | |
350 | } | |
351 | ||
352 | int dbd_postgresql_statement_create(lua_State *L, connection_t *conn, const char *sql_query) { | |
353 | statement_t *statement = NULL; | |
354 | ExecStatusType status; | |
355 | PGresult *result = NULL; | |
356 | char *new_sql; | |
357 | char name[IDLEN]; | |
358 | ||
2 | 359 | /* |
360 | * convert SQL string into a PSQL API compatible SQL statement | |
361 | */ | |
1 | 362 | new_sql = replace_placeholders(L, sql_query); |
363 | ||
364 | snprintf(name, IDLEN, "%017u", ++conn->statement_id); | |
365 | ||
366 | result = PQprepare(conn->postgresql, name, new_sql, 0, NULL); | |
2 | 367 | |
368 | /* | |
369 | * free converted statement after use | |
370 | */ | |
1 | 371 | free(new_sql); |
372 | ||
373 | if (!result) { | |
3 | 374 | lua_pushnil(L); |
4 | 375 | lua_pushfstring(L, DBI_ERR_ALLOC_STATEMENT, PQerrorMessage(statement->postgresql)); |
3 | 376 | return 2; |
1 | 377 | } |
378 | ||
379 | status = PQresultStatus(result); | |
380 | if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK) { | |
381 | const char *err_string = PQresultErrorMessage(result); | |
382 | PQclear(result); | |
3 | 383 | |
384 | lua_pushnil(L); | |
4 | 385 | lua_pushfstring(L, DBI_ERR_PREP_STATEMENT, err_string); |
3 | 386 | return 2; |
1 | 387 | } |
388 | ||
389 | PQclear(result); | |
390 | ||
391 | statement = (statement_t *)lua_newuserdata(L, sizeof(statement_t)); | |
392 | statement->postgresql = conn->postgresql; | |
393 | statement->result = NULL; | |
394 | statement->tuple = 0; | |
395 | strncpy(statement->name, name, IDLEN-1); | |
396 | statement->name[IDLEN-1] = '\0'; | |
397 | ||
398 | luaL_getmetatable(L, DBD_POSTGRESQL_STATEMENT); | |
399 | lua_setmetatable(L, -2); | |
400 | ||
401 | return 1; | |
402 | } | |
403 | ||
404 | int dbd_postgresql_statement(lua_State *L) { | |
2 | 405 | static const luaL_Reg statement_methods[] = { |
406 | {"close", statement_close}, | |
407 | {"execute", statement_execute}, | |
408 | {"fetch", statement_fetch}, | |
12
014ba3ab3903
Renamed statement:fetch() to statement:rows(), and statement:row() to statement:fetch(). The API reads better this way.
nrich@ii.net
parents:
11
diff
changeset
|
409 | {"rows", statement_rows}, |
2 | 410 | {NULL, NULL} |
411 | }; | |
412 | ||
413 | static const luaL_Reg statement_class_methods[] = { | |
414 | {NULL, NULL} | |
415 | }; | |
416 | ||
1 | 417 | luaL_newmetatable(L, DBD_POSTGRESQL_STATEMENT); |
418 | luaL_register(L, 0, statement_methods); | |
419 | lua_pushvalue(L,-1); | |
420 | lua_setfield(L, -2, "__index"); | |
421 | ||
422 | lua_pushcfunction(L, statement_gc); | |
423 | lua_setfield(L, -2, "__gc"); | |
424 | ||
425 | luaL_register(L, DBD_POSTGRESQL_STATEMENT, statement_class_methods); | |
426 | ||
427 | return 1; | |
428 | } |