1 /* MDB Tools - A library for reading MS Access database file
2 * Copyright (C) 2000 Brian Bruns
3 *
4 * This library is free software; you can redistribute it and/or
5 * modify it under the terms of the GNU Library General Public
6 * License as published by the Free Software Foundation; either
7 * version 2 of the License, or (at your option) any later version.
8 *
9 * This library is distributed in the hope that it will be useful,
10 * but WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12 * Library General Public License for more details.
13 *
14 * You should have received a copy of the GNU Library General Public
15 * License along with this library; if not, write to the Free Software
16 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
17 */
18
19 #include <stdarg.h>
20 #include "mdbsql.h"
21
22 #ifdef HAVE_STRPTIME
23 #include <time.h>
24 #include <stdio.h>
25 #endif
26
27 #include <locale.h>
28
29 /** \addtogroup mdbsql
30 * @{
31 */
32
33 /* Prevent warnings from -Wmissing-prototypes. */
34 #ifdef YYPARSE_PARAM
35 #if defined __STDC__ || defined __cplusplus
36 int yyparse (void *YYPARSE_PARAM);
37 #else
38 int yyparse ();
39 #endif
40 #else /* ! YYPARSE_PARAM */
41 #if defined __STDC__ || defined __cplusplus
42 int yyparse (void);
43 #else
44 int yyparse ();
45 #endif
46 #endif /* ! YYPARSE_PARAM */
47
48 static MdbSargNode * mdb_sql_alloc_node(void);
49
50 void
mdb_sql_error(MdbSQL * sql,const char * fmt,...)51 mdb_sql_error(MdbSQL* sql, const char* fmt, ...)
52 {
53 va_list ap;
54
55 va_start(ap, fmt);
56 vsnprintf(sql->error_msg, sizeof(sql->error_msg), fmt, ap);
57 va_end(ap);
58
59 fprintf(stderr, "%s\n", sql->error_msg);
60 }
61
mdb_sql_init()62 MdbSQL *mdb_sql_init()
63 {
64 MdbSQL *sql = g_malloc0(sizeof(MdbSQL));
65 sql->columns = g_ptr_array_new();
66 sql->tables = g_ptr_array_new();
67 sql->bound_values = g_ptr_array_new();
68 sql->sarg_tree = NULL;
69 sql->sarg_stack = NULL;
70 sql->max_rows = -1;
71 sql->limit = -1;
72 sql->limit_percent = 0;
73
74 return sql;
75 }
76
77 #ifndef _
78 #define _(x) x
79 #endif
80
81 /**
82 *
83 * @param sql: MdbSQL object to execute the query on.
84 * @param querystr: SQL query string to execute.
85 *
86 * Parses \p querystr and executes it within the given \p sql object.
87 *
88 * @return the updated MDB SQL object, or NULL on error
89 **/
90 MdbSQL*
mdb_sql_run_query(MdbSQL * sql,const gchar * querystr)91 mdb_sql_run_query (MdbSQL* sql, const gchar* querystr) {
92 g_return_val_if_fail (sql, NULL);
93 g_return_val_if_fail (querystr, NULL);
94
95 sql->error_msg[0]='\0';
96
97 if (parse_sql (sql, querystr)) {
98 /* end unsafe */
99 mdb_sql_error (sql, _("Could not parse '%s' command"), querystr);
100 mdb_sql_reset (sql);
101 return NULL;
102 }
103
104 if (sql->cur_table == NULL) {
105 /* Invalid column name? (should get caught by mdb_sql_select,
106 * but it appeared to happen anyway with 0.5) */
107 mdb_sql_error (sql, _("Got no result for '%s' command"), querystr);
108 return NULL;
109 }
110
111 if (mdb_sql_bind_all(sql) == -1) {
112 mdb_sql_error (sql, _("Failed to bind columns for '%s' command"), querystr);
113 return NULL;
114 }
115
116 return sql;
117 }
118
mdb_sql_set_maxrow(MdbSQL * sql,int maxrow)119 void mdb_sql_set_maxrow(MdbSQL *sql, int maxrow)
120 {
121 sql->max_rows = maxrow;
122 }
123
mdb_sql_free_columns(GPtrArray * columns)124 static void mdb_sql_free_columns(GPtrArray *columns)
125 {
126 int i;
127 if (!columns) return;
128 for (i=0; i<columns->len; i++) {
129 MdbSQLColumn *c = (MdbSQLColumn *)g_ptr_array_index(columns, i);
130 g_free(c->name);
131 g_free(c);
132 }
133 g_ptr_array_free(columns, TRUE);
134 }
mdb_sql_free_tables(GPtrArray * tables)135 static void mdb_sql_free_tables(GPtrArray *tables)
136 {
137 int i;
138 if (!tables) return;
139 for (i=0; i<tables->len; i++) {
140 MdbSQLTable *t = (MdbSQLTable *)g_ptr_array_index(tables, i);
141 g_free(t->name);
142 g_free(t);
143 }
144 g_ptr_array_free(tables, TRUE);
145 }
146
147 /* This gives us a nice, uniform place to keep up with memory that needs to be freed */
mdb_sql_free(MdbSQL * sql)148 static void mdb_sql_free(MdbSQL *sql)
149 {
150 /* Free MdbTableDef structures */
151 if (sql->cur_table) {
152 mdb_index_scan_free(sql->cur_table);
153 mdb_free_tabledef(sql->cur_table);
154 sql->cur_table = NULL;
155 }
156
157 /* Free MdbSQLColumns and MdbSQLTables */
158 mdb_sql_free_columns(sql->columns);
159 mdb_sql_free_tables(sql->tables);
160
161 /* Free sargs */
162 if (sql->sarg_tree) {
163 mdb_sql_free_tree(sql->sarg_tree);
164 sql->sarg_tree = NULL;
165 }
166
167 g_list_free(sql->sarg_stack);
168 sql->sarg_stack = NULL;
169
170 /* Free bindings */
171 mdb_sql_unbind_all(sql);
172 g_ptr_array_free(sql->bound_values, TRUE);
173 }
174
175 void
mdb_sql_close(MdbSQL * sql)176 mdb_sql_close(MdbSQL *sql)
177 {
178 if (sql->mdb) {
179 mdb_close(sql->mdb);
180 sql->mdb = NULL;
181 } else {
182 mdb_sql_error(sql, "Not connected.");
183 }
184 }
185
mdb_sql_open(MdbSQL * sql,char * db_name)186 MdbHandle *mdb_sql_open(MdbSQL *sql, char *db_name)
187 {
188 sql->mdb = mdb_open(db_name, MDB_NOFLAGS);
189 if ((!sql->mdb) && (!strstr(db_name, ".mdb"))) {
190 char *tmpstr = g_strconcat(db_name, ".mdb", NULL);
191 sql->mdb = mdb_open(tmpstr, MDB_NOFLAGS);
192 g_free(tmpstr);
193 }
194 if (!sql->mdb) {
195 mdb_sql_error(sql, "Unable to locate database %s", db_name);
196 }
197
198 return sql->mdb;
199 }
200 static MdbSargNode *
mdb_sql_alloc_node()201 mdb_sql_alloc_node()
202 {
203 return (MdbSargNode *) g_malloc0(sizeof(MdbSargNode));
204 }
205 void
mdb_sql_free_tree(MdbSargNode * tree)206 mdb_sql_free_tree(MdbSargNode *tree)
207 {
208
209 if (tree->left) mdb_sql_free_tree(tree->left);
210 if (tree->right) mdb_sql_free_tree(tree->right);
211 if (tree->parent) g_free(tree->parent);
212 g_free(tree);
213 }
214 void
mdb_sql_push_node(MdbSQL * sql,MdbSargNode * node)215 mdb_sql_push_node(MdbSQL *sql, MdbSargNode *node)
216 {
217 sql->sarg_stack = g_list_append(sql->sarg_stack, node);
218 /*
219 * Tree builds from bottom to top, so we should be left with
220 * the correct tree root when done
221 */
222 sql->sarg_tree = node;
223 }
224 MdbSargNode *
mdb_sql_pop_node(MdbSQL * sql)225 mdb_sql_pop_node(MdbSQL *sql)
226 {
227 GList *glist;
228 MdbSargNode *node;
229
230 glist = g_list_last(sql->sarg_stack);
231 if (!glist) return NULL;
232
233 node = glist->data;
234 #if 0
235 if (node->op==MDB_EQUAL)
236 printf("popping %d\n", node->value.i);
237 else
238 printf("popping %s\n", node->op == MDB_OR ? "OR" : "AND");
239 #endif
240 sql->sarg_stack = g_list_remove(sql->sarg_stack, node);
241 return node;
242 }
243
244 void
mdb_sql_add_not(MdbSQL * sql)245 mdb_sql_add_not(MdbSQL *sql)
246 {
247 MdbSargNode *node, *left;
248
249 left = mdb_sql_pop_node(sql);
250 if (!left) {
251 mdb_sql_error(sql, "parse error near 'NOT'");
252 mdb_sql_reset(sql);
253 return;
254 }
255 node = mdb_sql_alloc_node();
256 node->op = MDB_NOT;
257 node->left = left;
258 mdb_sql_push_node(sql, node);
259 }
260 void
mdb_sql_add_or(MdbSQL * sql)261 mdb_sql_add_or(MdbSQL *sql)
262 {
263 MdbSargNode *node, *left, *right;
264
265 left = mdb_sql_pop_node(sql);
266 right = mdb_sql_pop_node(sql);
267 if (!left || !right) {
268 mdb_sql_error(sql, "parse error near 'OR'");
269 mdb_sql_reset(sql);
270 return;
271 }
272 node = mdb_sql_alloc_node();
273 node->op = MDB_OR;
274 node->left = left;
275 node->right = right;
276 mdb_sql_push_node(sql, node);
277 }
278 void
mdb_sql_add_and(MdbSQL * sql)279 mdb_sql_add_and(MdbSQL *sql)
280 {
281 MdbSargNode *node, *left, *right;
282
283 left = mdb_sql_pop_node(sql);
284 right = mdb_sql_pop_node(sql);
285 if (!left || !right) {
286 mdb_sql_error(sql, "parse error near 'AND'");
287 mdb_sql_reset(sql);
288 return;
289 }
290 node = mdb_sql_alloc_node();
291 node->op = MDB_AND;
292 node->left = left;
293 node->right = right;
294 mdb_sql_push_node(sql, node);
295 }
296 void
mdb_sql_dump_node(MdbSargNode * node,int level)297 mdb_sql_dump_node(MdbSargNode *node, int level)
298 {
299 int i;
300 int mylevel = level+1;
301
302 if (!level)
303 printf("root ");
304 for (i=0;i<mylevel;i++) printf("--->");
305 switch (node->op) {
306 case MDB_OR:
307 printf(" or\n");
308 break;
309 case MDB_AND:
310 printf(" and\n");
311 break;
312 case MDB_NOT:
313 printf(" not\n");
314 break;
315 case MDB_LT:
316 printf(" < %d\n", node->value.i);
317 break;
318 case MDB_GT:
319 printf(" > %d\n", node->value.i);
320 break;
321 case MDB_LIKE:
322 printf(" like %s\n", node->value.s);
323 break;
324 case MDB_ILIKE:
325 printf(" ilike %s\n", node->value.s);
326 break;
327 case MDB_EQUAL:
328 printf(" = %d\n", node->value.i);
329 break;
330 }
331 if (node->left) {
332 printf("left ");
333 mdb_sql_dump_node(node->left, mylevel);
334 }
335 if (node->right) {
336 printf("right ");
337 mdb_sql_dump_node(node->right, mylevel);
338 }
339 }
340
341 /* Parses date format specifier into JET date representation (double) */
342 char *
mdb_sql_strptime(MdbSQL * sql,char * data,char * format)343 mdb_sql_strptime(MdbSQL *sql, char *data, char *format)
344 {
345 #ifndef HAVE_STRPTIME
346 mdb_sql_error(sql, "Your system doesn't support strptime.");
347 mdb_sql_reset(sql);
348 return NULL;
349 #else
350 char *p, *pszDate;
351 struct tm tm={0};
352 double date=0;
353
354 if (data[0]!='\'' || *(p=&data[strlen(data)-1])!='\'') {
355 mdb_sql_error(sql, "First parameter of strptime (data) must be a string.");
356 mdb_sql_reset(sql);
357 return NULL;
358 }
359 *p=0; ++data;
360 if (format[0]!='\'' || *(p=&format[strlen(format)-1])!='\'') {
361 mdb_sql_error(sql, "Second parameter of strptime (format) must be a string.");
362 mdb_sql_reset(sql);
363 return NULL;
364 }
365 *p=0; ++format;
366 if (!strptime(data, format, &tm)) {
367 mdb_sql_error(sql, "strptime('%s','%s') failed.", data, format);
368 mdb_sql_reset(sql);
369 return NULL;
370 }
371 mdb_tm_to_date(&tm, &date);
372 /* It seems that when just using a time offset without date in strptime,
373 * we always get 1 as decimal part, een though it should be 0 for time */
374 if (date < 2 && date > 1) date--;
375 if ((pszDate=malloc(16))) {
376 char cLocale=localeconv()->decimal_point[0], *p;
377 snprintf(pszDate, 16, "%lf", date);
378 if (cLocale!='.') for (p=pszDate; *p; p++) if (*p==cLocale) *p='.';
379 }
380 return pszDate;
381 #endif
382 }
383
384 /* evaluate a expression involving 2 constants and add answer to the stack */
385 int
mdb_sql_eval_expr(MdbSQL * sql,char * const1,int op,char * const2)386 mdb_sql_eval_expr(MdbSQL *sql, char *const1, int op, char *const2)
387 {
388 long val1, val2, value, compar;
389 unsigned char illop = 0;
390 MdbSargNode *node;
391
392 if (const1[0]=='\'' && const2[0]=='\'') {
393 value = strcoll(const1, const2);
394 switch (op) {
395 case MDB_EQUAL: compar = (value ? 0 : 1); break;
396 case MDB_GT: compar = (value > 0); break;
397 case MDB_GTEQ: compar = (value >= 0); break;
398 case MDB_LT: compar = (value < 0); break;
399 case MDB_LTEQ: compar = (value <= 0); break;
400 case MDB_LIKE: compar = mdb_like_cmp(const1,const2); break;
401 case MDB_ILIKE: compar = mdb_ilike_cmp(const1,const2); break;
402 case MDB_NEQ: compar = (value ? 1 : 0); break;
403 default: illop = 1;
404 }
405 } else if (const1[0]!='\'' && const2[0]!='\'') {
406 val1 = atol(const1);
407 val2 = atol(const2);
408 switch (op) {
409 case MDB_EQUAL: compar = (val1 == val2); break;
410 case MDB_GT: compar = (val1 > val2); break;
411 case MDB_GTEQ: compar = (val1 >= val2); break;
412 case MDB_LT: compar = (val1 < val2); break;
413 case MDB_LTEQ: compar = (val1 <= val2); break;
414 case MDB_NEQ: compar = (val1 != val2); break;
415 default: illop = 1;
416 }
417 } else {
418 mdb_sql_error(sql, "Comparison of strings and numbers not allowed.");
419 /* the column and table names are no good now */
420 mdb_sql_reset(sql);
421 return 1;
422 }
423 if (illop) {
424 mdb_sql_error(sql, "Illegal operator used for comparison of literals.");
425 /* the column and table names are no good now */
426 mdb_sql_reset(sql);
427 return 1;
428 }
429 node = mdb_sql_alloc_node();
430 node->op = MDB_EQUAL;
431 node->col = NULL;
432 node->value.i = (compar ? 1 : 0);
433 mdb_sql_push_node(sql, node);
434 return 0;
435 }
436 int
mdb_sql_add_sarg(MdbSQL * sql,char * col_name,int op,char * constant)437 mdb_sql_add_sarg(MdbSQL *sql, char *col_name, int op, char *constant)
438 {
439 char *p;
440 MdbSargNode *node;
441
442 node = mdb_sql_alloc_node();
443 node->op = op;
444 /* stash the column name until we finish with the grammar */
445 node->parent = (void *) g_strdup(col_name);
446
447 if (!constant) {
448 /* XXX - do we need to check operator? */
449 mdb_sql_push_node(sql, node);
450 return 0;
451 }
452 /* FIX ME -- we should probably just be storing the ascii value until the
453 ** column definition can be checked for validity
454 */
455 if (constant[0]=='\'') {
456 snprintf(node->value.s, sizeof(node->value.s), "%.*s", (int)strlen(constant) - 2, &constant[1]);
457 node->val_type = MDB_TEXT;
458 } else if ((p=strchr(constant, '.'))) {
459 *p=localeconv()->decimal_point[0];
460 node->value.d = strtod(constant, NULL);
461 node->val_type = MDB_DOUBLE;
462 } else {
463 node->value.i = atoi(constant);
464 node->val_type = MDB_INT;
465 }
466
467 mdb_sql_push_node(sql, node);
468
469 return 0;
470 }
471 void
mdb_sql_all_columns(MdbSQL * sql)472 mdb_sql_all_columns(MdbSQL *sql)
473 {
474 sql->all_columns=1;
475 }
476 void
mdb_sql_sel_count(MdbSQL * sql)477 mdb_sql_sel_count(MdbSQL *sql)
478 {
479 sql->sel_count=1;
480 }
481
mdb_sql_add_column(MdbSQL * sql,char * column_name)482 int mdb_sql_add_column(MdbSQL *sql, char *column_name)
483 {
484 MdbSQLColumn *c = g_malloc0(sizeof(MdbSQLColumn));
485 c->name = g_strdup(column_name);
486 g_ptr_array_add(sql->columns, c);
487 sql->num_columns++;
488 return 0;
489 }
mdb_sql_add_limit(MdbSQL * sql,char * limit,int percent)490 int mdb_sql_add_limit(MdbSQL *sql, char *limit, int percent)
491 {
492 sql->limit = atoi(limit);
493 sql->limit_percent = percent;
494
495 if (sql->limit_percent && (sql->limit < 0 || sql->limit > 100)) {
496 return 1;
497 }
498
499 return 0;
500 }
501
mdb_sql_get_limit(MdbSQL * sql)502 int mdb_sql_get_limit(MdbSQL *sql)
503 {
504 return sql->limit;
505 }
506
mdb_sql_add_function1(MdbSQL * sql,char * func_name,char * arg1)507 int mdb_sql_add_function1(MdbSQL *sql, char *func_name, char *arg1)
508 {
509 fprintf(stderr, "calling function %s with %s", func_name, arg1);
510 return 0;
511 }
mdb_sql_add_table(MdbSQL * sql,char * table_name)512 int mdb_sql_add_table(MdbSQL *sql, char *table_name)
513 {
514 MdbSQLTable *t = g_malloc0(sizeof(MdbSQLTable));
515 t->name = g_strdup(table_name);
516 t->alias = NULL;
517 g_ptr_array_add(sql->tables, t);
518 sql->num_tables++;
519 return 0;
520 }
mdb_sql_dump(MdbSQL * sql)521 void mdb_sql_dump(MdbSQL *sql)
522 {
523 unsigned int i;
524 MdbSQLColumn *c;
525 MdbSQLTable *t;
526
527 for (i=0;i<sql->num_columns;i++) {
528 c = g_ptr_array_index(sql->columns,i);
529 printf("column = %s\n",c->name);
530 }
531 for (i=0;i<sql->num_tables;i++) {
532 t = g_ptr_array_index(sql->tables,i);
533 printf("table = %s\n",t->name);
534 }
535 }
mdb_sql_exit(MdbSQL * sql)536 void mdb_sql_exit(MdbSQL *sql)
537 {
538 mdb_sql_free(sql);
539 if (sql->mdb)
540 mdb_close(sql->mdb);
541
542 /* Cleanup the SQL engine object */
543 g_free(sql);
544 }
mdb_sql_reset(MdbSQL * sql)545 void mdb_sql_reset(MdbSQL *sql)
546 {
547 mdb_sql_free(sql);
548
549 /* Reset columns */
550 sql->num_columns = 0;
551 sql->columns = g_ptr_array_new();
552
553 /* Reset MdbSQL tables */
554 sql->num_tables = 0;
555 sql->tables = g_ptr_array_new();
556
557 /* Reset bindings */
558 sql->bound_values = g_ptr_array_new();
559
560 sql->all_columns = 0;
561 sql->sel_count = 0;
562 sql->max_rows = -1;
563 sql->row_count = 0;
564 sql->limit = -1;
565 }
print_break(int sz,int first)566 static void print_break(int sz, int first)
567 {
568 int i;
569 if (first) {
570 fprintf(stdout,"+");
571 }
572 for (i=0;i<sz;i++) {
573 fprintf(stdout,"-");
574 }
575 fprintf(stdout,"+");
576 }
print_value(char * v,int sz,int first)577 static void print_value(char *v, int sz, int first)
578 {
579 int i;
580 int vlen;
581
582 if (first) {
583 fprintf(stdout,"|");
584 }
585 vlen = strlen(v);
586 for (i=0;i<sz;i++) {
587 fprintf(stdout,"%c",i >= vlen ? ' ' : v[i]);
588 }
589 fprintf(stdout,"|");
590 }
mdb_sql_listtables(MdbSQL * sql)591 void mdb_sql_listtables(MdbSQL *sql)
592 {
593 unsigned int i;
594 MdbCatalogEntry *entry;
595 MdbHandle *mdb = sql->mdb;
596 MdbField fields[1];
597 unsigned char row_buffer[MDB_PGSIZE];
598 int row_size;
599 MdbTableDef *ttable;
600 gchar tmpstr[100];
601 int tmpsiz;
602
603 if (!mdb) {
604 mdb_sql_error(sql, "You must connect to a database first");
605 return;
606 }
607 mdb_read_catalog (mdb, MDB_TABLE);
608
609 ttable = mdb_create_temp_table(mdb, "#listtables");
610 mdb_sql_add_temp_col(sql, ttable, 0, "Tables", MDB_TEXT, 30, 0);
611
612 /* add all user tables in catalog to list */
613 for (i=0; i < mdb->num_catalog; i++) {
614 entry = g_ptr_array_index (mdb->catalog, i);
615 if (mdb_is_user_table(entry)) {
616 //col = g_ptr_array_index(table->columns,0);
617 tmpsiz = mdb_ascii2unicode(mdb, entry->object_name, 0, tmpstr, sizeof(tmpstr));
618 mdb_fill_temp_field(&fields[0],tmpstr, tmpsiz, 0,0,0,0);
619 row_size = mdb_pack_row(ttable, row_buffer, 1, fields);
620 mdb_add_row_to_pg(ttable,row_buffer, row_size);
621 ttable->num_rows++;
622 }
623 }
624 sql->cur_table = ttable;
625
626 }
627 int
mdb_sql_add_temp_col(MdbSQL * sql,MdbTableDef * ttable,int col_num,char * name,int col_type,int col_size,int is_fixed)628 mdb_sql_add_temp_col(MdbSQL *sql, MdbTableDef *ttable, int col_num, char *name, int col_type, int col_size, int is_fixed)
629 {
630 MdbColumn tcol;
631 MdbSQLColumn *sqlcol;
632
633 mdb_fill_temp_col(&tcol, name, col_size, col_type, is_fixed);
634 mdb_temp_table_add_col(ttable, &tcol);
635 mdb_sql_add_column(sql, name);
636 sqlcol = g_ptr_array_index(sql->columns,col_num);
637 sqlcol->disp_size = mdb_col_disp_size(&tcol);
638
639 return 0;
640 }
mdb_sql_describe_table(MdbSQL * sql)641 void mdb_sql_describe_table(MdbSQL *sql)
642 {
643 MdbTableDef *ttable, *table = NULL;
644 MdbSQLTable *sql_tab;
645 MdbHandle *mdb = sql->mdb;
646 MdbColumn *col;
647 unsigned int i;
648 MdbField fields[3];
649 char tmpstr[256];
650 unsigned char row_buffer[MDB_PGSIZE];
651 int row_size;
652 gchar col_name[100], col_type[100], col_size[100];
653 int tmpsiz;
654
655 if (!mdb) {
656 mdb_sql_error(sql, "You must connect to a database first");
657 return;
658 }
659
660 sql_tab = g_ptr_array_index(sql->tables,0);
661
662 table = mdb_read_table_by_name(mdb, sql_tab->name, MDB_TABLE);
663 if (!table) {
664 mdb_sql_error(sql, "%s is not a table in this database", sql_tab->name);
665 /* the column and table names are no good now */
666 mdb_sql_reset(sql);
667 return;
668 }
669
670 if (!mdb_read_columns(table)) {
671 mdb_sql_error(sql, "Could not read columns of table %s", sql_tab->name);
672 /* the column and table names are no good now */
673 mdb_sql_reset(sql);
674 return;
675 }
676
677 ttable = mdb_create_temp_table(mdb, "#describe");
678
679 mdb_sql_add_temp_col(sql, ttable, 0, "Column Name", MDB_TEXT, 30, 0);
680 mdb_sql_add_temp_col(sql, ttable, 1, "Type", MDB_TEXT, 20, 0);
681 mdb_sql_add_temp_col(sql, ttable, 2, "Size", MDB_TEXT, 10, 0);
682
683 for (i=0;i<table->num_cols;i++) {
684
685 col = g_ptr_array_index(table->columns,i);
686 tmpsiz = mdb_ascii2unicode(mdb, col->name, 0, col_name, sizeof(col_name));
687 mdb_fill_temp_field(&fields[0],col_name, tmpsiz, 0,0,0,0);
688
689 snprintf(tmpstr, sizeof(tmpstr), "%s", mdb_get_colbacktype_string(col));
690 tmpsiz = mdb_ascii2unicode(mdb, tmpstr, 0, col_type, sizeof(col_type));
691 mdb_fill_temp_field(&fields[1],col_type, tmpsiz, 0,0,0,1);
692
693 snprintf(tmpstr, sizeof(tmpstr), "%d", col->col_size);
694 tmpsiz = mdb_ascii2unicode(mdb, tmpstr, 0, col_size, sizeof(col_size));
695 mdb_fill_temp_field(&fields[2],col_size, tmpsiz, 0,0,0,2);
696
697 row_size = mdb_pack_row(ttable, row_buffer, 3, fields);
698 mdb_add_row_to_pg(ttable,row_buffer, row_size);
699 ttable->num_rows++;
700 }
701
702 /* the column and table names are no good now */
703 //mdb_sql_reset(sql);
704 sql->cur_table = ttable;
705 }
706
mdb_sql_find_colbyname(MdbTableDef * table,char * name)707 MdbColumn *mdb_sql_find_colbyname(MdbTableDef *table, char *name)
708 {
709 unsigned int i;
710 MdbColumn *col;
711
712 for (i=0;i<table->num_cols;i++) {
713 col=g_ptr_array_index(table->columns,i);
714 if (!g_ascii_strcasecmp(col->name, name)) return col;
715 }
716 return NULL;
717 }
718
mdb_sql_find_sargcol(MdbSargNode * node,gpointer data)719 int mdb_sql_find_sargcol(MdbSargNode *node, gpointer data)
720 {
721 MdbTableDef *table = data;
722 MdbColumn *col;
723
724 if (!mdb_is_relational_op(node->op)) return 0;
725 if (!node->parent) return 0;
726
727 if ((col = mdb_sql_find_colbyname(table, (char *)node->parent))) {
728 node->col = col;
729 /* Do conversion to required target value type.
730 * Plain integers are UNIX timestamps for backwards compatibility of parser
731 */
732 if (col->col_type == MDB_DATETIME && node->val_type == MDB_INT) {
733 struct tm *tmp;
734 #ifdef HAVE_GMTIME_R
735 struct tm tm;
736 tmp = gmtime_r((time_t*)&node->value.i, &tm);
737 #else // regular gmtime on Windows uses thread-local storage
738 tmp = gmtime((time_t*)&node->value.i);
739 #endif
740 mdb_tm_to_date(tmp, &node->value.d);
741 node->val_type = MDB_DOUBLE;
742 }
743 }
744 return 0;
745 }
746
747 void
mdb_sql_select(MdbSQL * sql)748 mdb_sql_select(MdbSQL *sql)
749 {
750 unsigned int i,j;
751 MdbHandle *mdb = sql->mdb;
752 MdbTableDef *table = NULL;
753 MdbSQLTable *sql_tab;
754 MdbColumn *col;
755 MdbSQLColumn *sqlcol;
756 int found = 0;
757
758 if (!mdb) {
759 mdb_sql_error(sql, "You must connect to a database first");
760 return;
761 }
762
763 if (!sql->num_tables) return;
764 sql_tab = g_ptr_array_index(sql->tables,0);
765
766 table = mdb_read_table_by_name(mdb, sql_tab->name, MDB_TABLE);
767 if (!table) {
768 mdb_sql_error(sql, "%s is not a table in this database", sql_tab->name);
769 /* the column and table names are no good now */
770 mdb_sql_reset(sql);
771 return;
772 }
773 if (!mdb_read_columns(table)) {
774 mdb_sql_error(sql, "Could not read columns of table %s", sql_tab->name);
775 /* the column and table names are no good now */
776 mdb_sql_reset(sql);
777 return;
778 }
779
780 if (sql->sel_count && !sql->sarg_tree) {
781 MdbTableDef *ttable = mdb_create_temp_table(mdb, "#count");
782 char tmpstr[32];
783 gchar row_cnt[32];
784 unsigned char row_buffer[MDB_PGSIZE];
785 MdbField fields[1];
786 int row_size, tmpsiz;
787
788 mdb_sql_add_temp_col(sql, ttable, 0, "count", MDB_TEXT, 30, 0);
789 snprintf(tmpstr, sizeof(tmpstr), "%d", table->num_rows);
790 tmpsiz = mdb_ascii2unicode(mdb, tmpstr, 0, row_cnt, sizeof(row_cnt));
791 mdb_fill_temp_field(&fields[0],row_cnt, tmpsiz, 0,0,0,0);
792 row_size = mdb_pack_row(ttable, row_buffer, 1, fields);
793 mdb_add_row_to_pg(ttable,row_buffer, row_size);
794 ttable->num_rows++;
795 sql->cur_table = ttable;
796 mdb_free_tabledef(table);
797 return;
798 }
799
800 mdb_read_indices(table);
801 mdb_rewind_table(table);
802
803 if (sql->all_columns) {
804 for (i=0;i<table->num_cols;i++) {
805 col = g_ptr_array_index(table->columns,i);
806 mdb_sql_add_column(sql, col->name);
807 }
808 }
809 /* verify all specified columns exist in this table */
810 for (i=0;i<sql->num_columns;i++) {
811 sqlcol = g_ptr_array_index(sql->columns,i);
812 found=0;
813 for (j=0;j<table->num_cols;j++) {
814 col=g_ptr_array_index(table->columns,j);
815 if (!g_ascii_strcasecmp(sqlcol->name, col->name)) {
816 sqlcol->disp_size = mdb_col_disp_size(col);
817 found=1;
818 break;
819 }
820 }
821 if (!found) {
822 mdb_sql_error(sql, "Column %s not found",sqlcol->name);
823 mdb_index_scan_free(table);
824 mdb_free_tabledef(table);
825 mdb_sql_reset(sql);
826 return;
827 }
828 }
829
830 /*
831 * resolve column names to MdbColumn structs
832 */
833 if (sql->sarg_tree) {
834 mdb_sql_walk_tree(sql->sarg_tree, mdb_sql_find_sargcol, table);
835 mdb_sql_walk_tree(sql->sarg_tree, mdb_find_indexable_sargs, NULL);
836 }
837 /*
838 * move the sarg_tree.
839 * XXX - this won't work when we implement joins
840 */
841 table->sarg_tree = sql->sarg_tree;
842 sql->sarg_tree = NULL;
843
844 sql->cur_table = table;
845 mdb_index_scan_init(mdb, table);
846
847 /* We know how many rows there are, so convert limit percentage
848 * to an row count */
849 if (sql->limit != -1 && sql->limit_percent) {
850 sql->limit = (int)((double)table->num_rows / 100 * sql->limit);
851 sql->limit_percent = 0;
852 }
853 }
854
855 int
mdb_sql_bind_column(MdbSQL * sql,int colnum,void * varaddr,int * len_ptr)856 mdb_sql_bind_column(MdbSQL *sql, int colnum, void *varaddr, int *len_ptr)
857 {
858 MdbSQLColumn *sqlcol;
859
860 if (colnum <= 0 || colnum > sql->num_columns)
861 return -1;
862
863 /* sql columns are traditionally 1 based, so decrement colnum */
864 sqlcol = g_ptr_array_index(sql->columns,colnum - 1);
865 return mdb_bind_column_by_name(sql->cur_table, sqlcol->name, varaddr, len_ptr);
866 }
867
868 int
mdb_sql_bind_all(MdbSQL * sql)869 mdb_sql_bind_all(MdbSQL *sql)
870 {
871 unsigned int i;
872 void *bound_value;
873
874 for (i=0;i<sql->num_columns;i++) {
875 bound_value = g_malloc0(sql->mdb->bind_size);
876 g_ptr_array_add(sql->bound_values, bound_value);
877 if (mdb_sql_bind_column(sql, i+1, bound_value, NULL) == -1) {
878 mdb_sql_unbind_all(sql);
879 return -1;
880 }
881 }
882 return sql->num_columns;
883 }
884
mdb_sql_unbind_all(MdbSQL * sql)885 void mdb_sql_unbind_all(MdbSQL *sql)
886 {
887 unsigned int i;
888
889 for (i=0;i<sql->bound_values->len;i++) {
890 g_free(g_ptr_array_index(sql->bound_values, i));
891 }
892 }
893
894 /*
895 * mdb_sql_fetch_row is now just a wrapper around mdb_fetch_row.
896 * It is left here only for backward compatibility.
897 */
898 int
mdb_sql_fetch_row(MdbSQL * sql,MdbTableDef * table)899 mdb_sql_fetch_row(MdbSQL *sql, MdbTableDef *table)
900 {
901 int rc = mdb_fetch_row(table);
902 if (rc) {
903 if (sql->limit >= 0 && sql->row_count + 1 > sql->limit) {
904 return 0;
905 }
906 sql->row_count++;
907 }
908 return rc;
909 }
910
911 void
mdb_sql_dump_results(MdbSQL * sql)912 mdb_sql_dump_results(MdbSQL *sql)
913 {
914 unsigned int j;
915 MdbSQLColumn *sqlcol;
916
917 /* print header */
918 for (j=0;j<sql->num_columns;j++) {
919 sqlcol = g_ptr_array_index(sql->columns,j);
920 print_break(sqlcol->disp_size, !j);
921 }
922 fprintf(stdout,"\n");
923 for (j=0;j<sql->num_columns;j++) {
924 sqlcol = g_ptr_array_index(sql->columns,j);
925 print_value(sqlcol->name,sqlcol->disp_size,!j);
926 }
927 fprintf(stdout,"\n");
928 for (j=0;j<sql->num_columns;j++) {
929 sqlcol = g_ptr_array_index(sql->columns,j);
930 print_break(sqlcol->disp_size, !j);
931 }
932 fprintf(stdout,"\n");
933
934 /* print each row */
935 while(mdb_fetch_row(sql->cur_table)) {
936 for (j=0;j<sql->num_columns;j++) {
937 sqlcol = g_ptr_array_index(sql->columns,j);
938 print_value(g_ptr_array_index(sql->bound_values, j),sqlcol->disp_size,!j);
939 }
940 fprintf(stdout,"\n");
941 }
942
943 /* footer */
944 for (j=0;j<sql->num_columns;j++) {
945 sqlcol = g_ptr_array_index(sql->columns,j);
946 print_break(sqlcol->disp_size, !j);
947 }
948
949 fprintf(stdout,"\n");
950
951 /* the column and table names are no good now */
952 mdb_sql_reset(sql);
953 }
954 /** @}*/
955