1
2 /*****************************************************************************
3 *
4 * MODULE: DBF driver
5 *
6 * AUTHOR(S): Radim Blazek, Daniel Calvelo
7 *
8 * PURPOSE: Simple driver for reading and writing dbf files
9 *
10 * COPYRIGHT: (C) 2000,2005 by the GRASS Development Team
11 *
12 * This program is free software under the GNU General Public
13 * License (>=v2). Read the file COPYING that comes with GRASS
14 * for details.
15 *
16 * DBF API: http://shapelib.maptools.org/dbf_api.html
17 *****************************************************************************/
18 #include <stdlib.h>
19 #include <stdio.h>
20 #include <string.h>
21 #include <unistd.h>
22 #include <grass/dbmi.h>
23 #include <grass/shapefil.h>
24 #include <grass/gis.h>
25 #include <grass/glocale.h>
26 #include "globals.h"
27 #include "proto.h"
28
29 /* Results of eval_node */
30 #define NODE_FALSE 0
31 #define NODE_TRUE 1
32 #define NODE_VALUE 2
33 #define NODE_NULL 3
34 #define NODE_ERROR 4
35
36 int yyparse(void);
37 void get_col_def(SQLPSTMT * st, int col, int *type, int *width,
38 int *decimals);
39 int sel(SQLPSTMT * st, int tab, int **set);
40 void eval_val(int tab, int row, int col, SQLPVALUE * inval,
41 SQLPVALUE * result);
42 int set_val(int tab, int row, int col, SQLPVALUE * val);
43 double eval_node(SQLPNODE *, int, int, SQLPVALUE *);
44 int eval_node_type(SQLPNODE *, int);
45
execute(char * sql,cursor * c)46 int execute(char *sql, cursor * c)
47 {
48 int i, j, tab, ret;
49 SQLPSTMT *st;
50 ROW *dbrows;
51 VALUE *dbval;
52 int row, nrows;
53 int *cols = NULL, ncols, col;
54 int *selset;
55 int dtype, stype;
56 int width, decimals;
57 char *tmpsql, name[500];
58 SQLPVALUE *calctmp; /* store for calculated values in UPDATE, if any */
59
60 /* parse sql statement */
61 /* I don't know why, but if the statement ends by string in quotes 'xxx' and is not
62 * followed by space or '\n' it is not parsed properly -> */
63 tmpsql = (char *)G_malloc(strlen(sql) + 2);
64 sprintf(tmpsql, "%s ", sql);
65 st = sqpInitStmt();
66 st->stmt = tmpsql;
67 sqpInitParser(st);
68
69 if (yyparse() != 0) {
70 G_free(tmpsql);
71 db_d_append_error("%s (%s) %s\n%s\n",
72 _("SQL parser error"),
73 st->errmsg,
74 _("in statement:"),
75 sql);
76 sqpFreeStmt(st);
77 return DB_FAILED;
78 }
79 G_free(tmpsql);
80
81 G_debug(3, "SQL statement parsed successfully: %s", sql);
82
83 /* sqpPrintStmt(st); *//* debug output only */
84
85 /* find table */
86 tab = find_table(st->table);
87 if (tab < 0 && st->command != SQLP_CREATE) {
88 db_d_append_error(_("Table '%s' doesn't exist."), st->table);
89 return DB_FAILED;
90 }
91
92 /* For DROP we have to call load_table_head() because it reads permissions */
93 if ((st->command != SQLP_CREATE)) {
94 ret = load_table_head(tab);
95 if (ret == DB_FAILED) {
96 db_d_append_error(_("Unable to load table head."));
97 return DB_FAILED;
98 }
99 }
100
101 if ((st->command == SQLP_DROP) || (st->command == SQLP_DELETE) ||
102 (st->command == SQLP_INSERT) || (st->command == SQLP_UPDATE) ||
103 (st->command == SQLP_ADD_COLUMN) || (st->command == SQLP_DROP_COLUMN)
104 ) {
105 if (db.tables[tab].write == FALSE) {
106 db_d_append_error(_("Unable to modify table, "
107 "don't have write permission for DBF file."));
108 return DB_FAILED;
109 }
110 }
111
112 /* find columns */
113 ncols = st->nCol;
114 if (st->command == SQLP_INSERT || st->command == SQLP_SELECT
115 || st->command == SQLP_UPDATE || st->command == SQLP_DROP_COLUMN) {
116 if (ncols > 0) { /* columns were specified */
117 cols = (int *)G_malloc(ncols * sizeof(int));
118 for (i = 0; i < ncols; i++) {
119 cols[i] = find_column(tab, st->Col[i].s);
120 if (cols[i] == -1) {
121 db_d_append_error(_("Column '%s' not found"), st->Col[i].s);
122 return DB_FAILED;
123 }
124 }
125 }
126 else { /* all columns */
127
128 ncols = db.tables[tab].ncols;
129 cols = (int *)G_malloc(ncols * sizeof(int));
130 for (i = 0; i < ncols; i++)
131 cols[i] = i;
132 }
133 }
134
135 /* check column types */
136 if (st->command == SQLP_INSERT || st->command == SQLP_UPDATE) {
137 for (i = 0; i < st->nVal; i++) {
138 col = cols[i];
139 if (st->Val[i].type != SQLP_NULL && st->Val[i].type != SQLP_EXPR) {
140 dtype = db.tables[tab].cols[col].type;
141 stype = st->Val[i].type;
142 if ((dtype == DBF_INT && stype != SQLP_I)
143 || (dtype == DBF_DOUBLE && stype == SQLP_S)
144 || (dtype == DBF_CHAR && stype != SQLP_S)) {
145 db_d_append_error(_("Incompatible value type."));
146 return DB_FAILED;
147 }
148 }
149 }
150 }
151
152 /* do command */
153 G_debug(3, "Doing SQL command <%d> on DBF table... (see include/sqlp.h)",
154 st->command);
155 switch (st->command) {
156 case (SQLP_ADD_COLUMN):
157 load_table(tab);
158 get_col_def(st, 0, &dtype, &width, &decimals);
159 ret = add_column(tab, dtype, st->Col[0].s, width, decimals);
160 if (ret == DB_FAILED) {
161 db_d_append_error(_("Unable to add column."));
162 return DB_FAILED;
163 }
164 /* Add column to each row */
165 for (i = 0; i < db.tables[tab].nrows; i++) {
166 db.tables[tab].rows[i].values =
167 (VALUE *) G_realloc(db.tables[tab].rows[i].values,
168 db.tables[tab].ncols * sizeof(VALUE));
169
170 dbval =
171 &(db.tables[tab].rows[i].values[db.tables[tab].ncols - 1]);
172 dbval->i = 0;
173 dbval->d = 0.0;
174 dbval->c = NULL;
175 dbval->is_null = 1;
176 }
177 db.tables[tab].updated = TRUE;
178 break;
179
180 case (SQLP_DROP_COLUMN):
181 load_table(tab);
182 if (drop_column(tab, st->Col[0].s) != DB_OK) {
183 db_d_append_error(_("Unable to delete column."));
184 return DB_FAILED;
185 }
186 db.tables[tab].updated = TRUE;
187 break;
188
189 case (SQLP_CREATE):
190 if (tab >= 0) {
191 db_d_append_error(_("Table %s already exists"), st->table);
192
193 return DB_FAILED;
194 }
195 sprintf(name, "%s.dbf", st->table);
196 add_table(st->table, name);
197
198 tab = find_table(st->table);
199 db.tables[tab].read = TRUE;
200 db.tables[tab].write = TRUE;
201
202 for (i = 0; i < ncols; i++) {
203 get_col_def(st, i, &dtype, &width, &decimals);
204 ret = add_column(tab, dtype, st->Col[i].s, width, decimals);
205 if (ret == DB_FAILED) {
206 db_d_append_error(_("Unable to create table."));
207 db.tables[tab].alive = FALSE;
208 return DB_FAILED;
209 }
210 }
211 db.tables[tab].described = TRUE;
212 db.tables[tab].loaded = TRUE;
213 db.tables[tab].updated = TRUE;
214 break;
215
216 case (SQLP_DROP):
217 unlink(db.tables[tab].file);
218 db.tables[tab].alive = FALSE;
219 break;
220
221 case (SQLP_INSERT):
222 load_table(tab);
223
224 /* add row */
225 if (db.tables[tab].nrows == db.tables[tab].arows) {
226 db.tables[tab].arows += 1000;
227 db.tables[tab].rows =
228 (ROW *) G_realloc(db.tables[tab].rows,
229 db.tables[tab].arows * sizeof(ROW));
230 }
231 dbrows = db.tables[tab].rows;
232 row = db.tables[tab].nrows;
233 dbrows[row].values =
234 (VALUE *) G_calloc(db.tables[tab].ncols, sizeof(VALUE));
235 dbrows[row].alive = TRUE;
236
237 /* set to null */
238 for (i = 0; i < db.tables[tab].ncols; i++) {
239 VALUE *dbval;
240
241 dbval = &(dbrows[row].values[i]);
242 dbval->is_null = 1;
243 }
244
245 /* set values */
246 for (i = 0; i < st->nVal; i++) {
247 col = cols[i];
248 set_val(tab, row, col, &(st->Val[i]));
249 }
250
251 db.tables[tab].nrows++;
252 db.tables[tab].updated = TRUE;
253 break;
254
255 case (SQLP_SELECT):
256 G_debug(2, "SELECT");
257 c->st = st;
258 c->table = tab;
259 c->cols = cols;
260 c->ncols = ncols;
261 c->nrows = sel(st, tab, &(c->set));
262 if (c->nrows < 0) {
263 db_d_append_error(_("Error in selecting rows"));
264 return DB_FAILED;
265 }
266 c->cur = -1;
267
268 break;
269
270 case (SQLP_UPDATE):
271 nrows = sel(st, tab, &selset);
272 if (nrows < 0) {
273 db_d_append_error(_("Error in selecting rows"));
274 return DB_FAILED;
275 }
276 dbrows = db.tables[tab].rows;
277
278 /* update rows */
279 for (i = 0; i < nrows; i++) {
280 SQLPVALUE *temp_p;
281
282 calctmp = (SQLPVALUE *) G_malloc((st->nVal) * sizeof(SQLPVALUE));
283 row = selset[i];
284 for (j = 0; j < st->nVal; j++) {
285 col = cols[j];
286 eval_val(tab, row, col, &(st->Val[j]), &(calctmp[j]));
287 }
288 temp_p = st->Val;
289 st->Val = calctmp;
290 for (j = 0; j < st->nVal; j++) {
291 col = cols[j];
292 set_val(tab, row, col, &(st->Val[j]));
293 db.tables[tab].updated = TRUE;
294 }
295 st->Val = temp_p;
296 G_free(calctmp);
297 }
298 break;
299
300 case (SQLP_DELETE):
301 nrows = sel(st, tab, &selset);
302 if (nrows < 0) {
303 db_d_append_error(_("Error in selecting rows"));
304 return DB_FAILED;
305 }
306 dbrows = db.tables[tab].rows;
307
308 /* delete rows */
309 for (i = 0; i < nrows; i++) {
310 row = selset[i];
311 dbrows[row].alive = FALSE;
312 db.tables[tab].updated = TRUE;
313 }
314 break;
315
316 }
317 if (st->command != SQLP_SELECT) { /* because statement is released with cursor */
318 sqpFreeStmt(st);
319 if (cols)
320 G_free(cols);
321 }
322
323 return DB_OK;
324 }
325
326 /* for given parser result and column index finds dbf column definition */
get_col_def(SQLPSTMT * st,int col,int * type,int * width,int * decimals)327 void get_col_def(SQLPSTMT * st, int col, int *type, int *width, int *decimals)
328 {
329 switch (st->ColType[col]) {
330 case (SQLP_INTEGER):
331 *type = DBF_INT;
332 *width = 11;
333 *decimals = 0;
334 break;
335 case (SQLP_VARCHAR):
336 *type = DBF_CHAR;
337 *width = st->ColWidth[col];
338 *decimals = 0;
339 break;
340 case (SQLP_DATE): /* DATE treated as string unless SHAPELIB/DBFLIB supports date type */
341 *type = DBF_CHAR;
342 *width = 10; /* 2004-01-23 = 10 chars */
343 *decimals = 0;
344 break;
345 case (SQLP_DOUBLE):
346 *type = DBF_DOUBLE;
347 *width = 20;
348 *decimals = 6;
349 break;
350 }
351 }
352
eval_val(int tab,int row,int col,SQLPVALUE * inval,SQLPVALUE * val)353 void eval_val(int tab, int row, int col, SQLPVALUE * inval, SQLPVALUE * val)
354 {
355
356 double retval;
357
358 /* XXX */
359 if (inval->type == SQLP_EXPR) {
360
361 retval = eval_node(inval->expr, tab, row, val);
362 if (retval == NODE_NULL) {
363 val->type = SQLP_NULL;
364 }
365 else if (retval == NODE_TRUE) {
366 val->i = 1;
367 val->d = 1.0;
368 val->s = "TRUE";
369 }
370 else if (retval == NODE_FALSE) {
371 val->i = 0;
372 val->d = 0.0;
373 val->s = NULL;
374 }
375 else if (retval == NODE_VALUE) {
376 /* Ok, got a value, propagate it to the proper type */
377 if (val->type == SQLP_I) {
378 val->d = (double)val->i;
379 val->s = (char *)G_malloc(32 * sizeof(char));
380 sprintf(val->s, "%d", val->i);
381 }
382 else if (val->type == SQLP_D) {
383 val->i = (int)val->d;
384 val->s = (char *)G_malloc(32 * sizeof(char));
385 sprintf(val->s, "%g", val->d);
386 }
387 else if (val->type == SQLP_S) {
388 val->i = atoi(val->s);
389 val->d = atof(val->s);
390 }
391 else {
392 G_fatal_error
393 ("This should not happen: wrong return type in parsing.");
394 }
395 }
396 else if (retval == NODE_ERROR) {
397 G_fatal_error
398 ("This should not happen: got a wrong expression structure after parsing.");
399 }
400 else {
401 G_fatal_error
402 ("Unknown return value calling eval_node from eval_val");
403 }
404 }
405 else {
406 /*
407 * TODO: maybe use this function to perform type "conversion",
408 * i.e. setting all of s,i,d to the same "value",as is done with
409 * the result of eval_node above.
410 */
411 val = inval;
412 }
413 }
414
set_val(int tab,int row,int col,SQLPVALUE * val)415 int set_val(int tab, int row, int col, SQLPVALUE * val)
416 {
417 VALUE *dbval;
418
419 dbval = &(db.tables[tab].rows[row].values[col]);
420 /* For debugging purposes; see FIXME below
421 fprintf(stderr, "In set_val : ");
422 fprintf(stderr, val->type==SQLP_EXPR?"sqlp_expr":
423 val->type==SQLP_NULL?"sqlp_null":
424 val->type==SQLP_I?"sqlp_i":
425 val->type==SQLP_D?"sqlp_d":
426 val->type==SQLP_S?"sqlp_s":
427 "other"); //DCA
428 fprintf(stderr,"%d\n",val->type);
429 fflush(stderr);
430 */
431 if (val->type == SQLP_EXPR) {
432 eval_val(tab, row, col, val, val);
433 }
434
435 /* FIXME: SQLP_NULL is not always properly detected.
436 * This workaround works, since type should be some of these
437 * after passing through eval_val; otherwise it is NULL
438 */
439 if (!(val->type == SQLP_I || val->type == SQLP_D || val->type == SQLP_S)) {
440 dbval->is_null = 1;
441 dbval->c = NULL;
442 dbval->i = 0;
443 dbval->d = 0.0;
444 }
445 else {
446 dbval->is_null = 0;
447 switch (db.tables[tab].cols[col].type) {
448 case DBF_INT:
449 dbval->i = val->i;
450 break;
451 case DBF_CHAR:
452 save_string(dbval, val->s);
453 break;
454 case DBF_DOUBLE:
455 if (val->type == SQLP_I)
456 dbval->d = val->i;
457 else if (val->type == SQLP_D)
458 dbval->d = val->d;
459 else if (val->type == SQLP_S) {
460 char *tailptr;
461 double dval = strtod(val->s, &tailptr);
462
463 if (!(*tailptr)) {
464 dbval->d = dval;
465 }
466 }
467 break;
468 }
469 }
470 return (1);
471 }
472
473 /* Comparison of 2 rows */
474 static int cur_cmp_table;
475 static int cur_cmp_ocol;
cmp_row_asc(const void * pa,const void * pb)476 static int cmp_row_asc(const void *pa, const void *pb)
477 {
478 int *row1 = (int *)pa;
479 int *row2 = (int *)pb;
480 char *c1, *c2;
481 int i1, i2;
482 double d1, d2;
483 TABLE *tbl;
484
485 tbl = &(db.tables[cur_cmp_table]);
486
487 if (tbl->rows[*row1].values[cur_cmp_ocol].is_null) {
488 if (tbl->rows[*row2].values[cur_cmp_ocol].is_null) {
489 return 0;
490 }
491 else {
492 return 1;
493 }
494 }
495 else {
496 if (tbl->rows[*row2].values[cur_cmp_ocol].is_null) {
497 return -1;
498 }
499 else {
500 switch (tbl->cols[cur_cmp_ocol].type) {
501 case DBF_CHAR:
502 c1 = tbl->rows[*row1].values[cur_cmp_ocol].c;
503 c2 = tbl->rows[*row2].values[cur_cmp_ocol].c;
504 return (strcmp(c1, c2));
505 break;
506 case DBF_INT:
507 i1 = tbl->rows[*row1].values[cur_cmp_ocol].i;
508 i2 = tbl->rows[*row2].values[cur_cmp_ocol].i;
509 if (i1 < i2)
510 return -1;
511 if (i1 > i2)
512 return 1;
513 return 0;
514 break;
515 case DBF_DOUBLE:
516 d1 = tbl->rows[*row1].values[cur_cmp_ocol].d;
517 d2 = tbl->rows[*row2].values[cur_cmp_ocol].d;
518 if (d1 < d2)
519 return -1;
520 if (d1 > d2)
521 return 1;
522 return 0;
523 break;
524 }
525 return 0;
526 }
527 }
528 }
529
cmp_row_desc(const void * pa,const void * pb)530 static int cmp_row_desc(const void *pa, const void *pb)
531 {
532
533 return -cmp_row_asc(pa, pb);
534
535 }
536
537 /* Select records, sets 'selset' to new array of items and returns
538 * number of items or -1 for error */
sel(SQLPSTMT * st,int tab,int ** selset)539 int sel(SQLPSTMT * st, int tab, int **selset)
540 {
541 int i, ret, condition;
542 int *set; /* pointer to array of indexes to rows */
543 int aset, nset;
544
545 G_debug(2, "sel(): tab = %d", tab);
546
547 *selset = NULL;
548 nset = 0;
549
550 ret = load_table(tab);
551 if (ret == DB_FAILED) {
552 db_d_append_error(_("Cannot load table."));
553 return -1;
554 }
555
556 aset = 1;
557 set = (int *)G_malloc(aset * sizeof(int));
558
559 if (st->upperNodeptr) {
560 int node_type;
561
562 /* First eval node type */
563 node_type = eval_node_type(st->upperNodeptr, tab);
564 G_debug(4, "node result type = %d", node_type);
565
566 if (node_type == -1) {
567 db_d_append_error(_("Incompatible types in WHERE condition."));
568 return -1;
569 }
570 else if (node_type == SQLP_S || node_type == SQLP_I ||
571 node_type == SQLP_D) {
572 db_d_append_error(_("Result of WHERE condition is not of type BOOL."));
573 return -1;
574 }
575 else if (node_type == SQLP_NULL) {
576 /* Conditions has undefined result -> nothing selected */
577 return 0;
578 }
579 else if (node_type == SQLP_BOOL) {
580 for (i = 0; i < db.tables[tab].nrows; i++) {
581 SQLPVALUE value;
582
583 G_debug(4, "row %d", i);
584 condition = eval_node(st->upperNodeptr, tab, i, &value);
585 G_debug(4, "condition = %d", condition);
586
587 if (condition == NODE_ERROR) { /* e.g. division by 0 */
588 db_d_append_error(_("Error in evaluation of WHERE condition."));
589 return -1;
590 }
591 else if (condition == NODE_TRUE) { /* true */
592 if (nset == aset) {
593 aset += 1000;
594 set = (int *)G_realloc(set, aset * sizeof(int));
595 }
596 set[nset] = i;
597 nset++;
598 }
599 else if (condition != NODE_FALSE && condition != NODE_NULL) { /* Should not happen */
600 db_d_append_error(_("Unknown result (%d) of WHERE evaluation"),
601 condition);
602 return -1;
603 }
604 }
605 }
606 else { /* Should not happen */
607 db_d_append_error(_("Unknown WHERE condition type (bug in DBF driver)."));
608 return -1;
609 }
610 }
611 else { /* Select all */
612 aset = db.tables[tab].nrows;
613 set = (int *)G_realloc(set, aset * sizeof(int));
614 for (i = 0; i < db.tables[tab].nrows; i++) {
615 set[i] = i;
616 }
617 nset = db.tables[tab].nrows;
618 }
619
620 /* Order */
621 if (st->command == SQLP_SELECT && st->orderCol) {
622 G_debug(3, "Order selection by %s", st->orderCol);
623
624 /* Find order col */
625 cur_cmp_ocol = -1;
626 for (i = 0; i < db.tables[tab].ncols; i++) {
627 if (strcmp(db.tables[tab].cols[i].name, st->orderCol) == 0) {
628 cur_cmp_ocol = i;
629 break;
630 }
631 }
632 if (cur_cmp_ocol < 0) {
633 db_d_append_error(_("Unable to find order column '%s'"), st->orderCol);
634 return -1;
635 }
636
637 cur_cmp_table = tab;
638 if (st->orderDir == SORT_DESC) {
639 qsort(set, nset, sizeof(int), cmp_row_desc);
640 }
641 else {
642 qsort(set, nset, sizeof(int), cmp_row_asc);
643 }
644
645
646 }
647
648 *selset = set;
649 return nset;
650 }
651
652 /* Evaluate node recursively.
653 *
654 * Returns:
655 * NODE_NULL result/value is unknown
656 * NODE_TRUE
657 * NODE_FALSE
658 * NODE_VALUE result is a value stored in 'value'
659 * (if value is not NULL otherwise NODE_NULL is returned and value is not set)
660 * NODE_ERROR e.g. division by 0
661 *
662 * If results is NODE_VALUE, the 'value' is set, if value is type SQLP_S the string is not duplicated
663 * and only pointer is set -> do not free value->s
664 */
eval_node(SQLPNODE * nptr,int tab,int row,SQLPVALUE * value)665 double eval_node(SQLPNODE * nptr, int tab, int row, SQLPVALUE * value)
666 {
667 int left, right;
668 SQLPVALUE left_value, right_value;
669 int ccol;
670 COLUMN *col;
671 VALUE *val;
672 double left_dval, right_dval, dval;
673 char *rightbuf;
674
675 /* Note: node types were previously checked by eval_node_type */
676
677 G_debug(4, "eval_node node_type = %d", nptr->node_type);
678
679 switch (nptr->node_type) {
680 case SQLP_NODE_VALUE:
681 if (nptr->value.type == SQLP_NULL)
682 return NODE_NULL;
683
684 value->type = nptr->value.type;
685 value->s = nptr->value.s;
686 value->i = nptr->value.i;
687 value->d = nptr->value.d;
688 return NODE_VALUE;
689 break;
690
691 case SQLP_NODE_COLUMN:
692 ccol = find_column(tab, nptr->column_name);
693 col = &(db.tables[tab].cols[ccol]);
694 val = &(db.tables[tab].rows[row].values[ccol]);
695
696 if (val->is_null)
697 return NODE_NULL;
698
699 switch (col->type) {
700 case DBF_CHAR:
701 value->s = val->c;
702 value->type = SQLP_S;
703 break;
704 case DBF_INT:
705 value->i = val->i;
706 value->type = SQLP_I;
707 break;
708 case DBF_DOUBLE:
709 value->d = val->d;
710 value->type = SQLP_D;
711 break;
712 }
713 return NODE_VALUE;
714 break;
715
716 case SQLP_NODE_EXPRESSION:
717 /* Note: Some expressions (e.g. NOT) have only one side */
718 if (nptr->left) {
719 left = eval_node(nptr->left, tab, row, &left_value);
720 G_debug(4, " left = %d", left);
721
722 if (left == NODE_ERROR)
723 return NODE_ERROR;
724
725 if (left != NODE_NULL) {
726 if (left_value.type == SQLP_I)
727 left_dval = left_value.i;
728 else
729 left_dval = left_value.d;
730
731 G_debug(4, " left_dval = %f", left_dval);
732 }
733 }
734
735 if (nptr->right) {
736 right = eval_node(nptr->right, tab, row, &right_value);
737 G_debug(4, " right = %d", right);
738
739 if (right == NODE_ERROR)
740 return NODE_ERROR;
741
742 if (right != NODE_NULL) {
743 if (right_value.type == SQLP_I)
744 right_dval = right_value.i;
745 else
746 right_dval = right_value.d;
747
748 G_debug(4, " right_dval = %f", right_dval);
749 }
750 }
751
752 G_debug(4, " operator = %d", nptr->oper);
753
754 switch (nptr->oper) {
755 /* Arithmetical */
756 case SQLP_ADD:
757 case SQLP_SUBTR:
758 case SQLP_MLTP:
759 case SQLP_DIV:
760 if (left == NODE_NULL || right == NODE_NULL)
761 return NODE_NULL;
762
763 switch (nptr->oper) {
764 case SQLP_ADD:
765 dval = left_dval + right_dval;
766 break;
767 case SQLP_SUBTR:
768 dval = left_dval - right_dval;
769 break;
770 case SQLP_MLTP:
771 dval = left_dval * right_dval;
772 break;
773 case SQLP_DIV:
774 if (right_dval != 0.0) {
775 dval = left_dval / right_dval;
776 }
777 else {
778 db_d_append_error(_("Division by zero"));
779 return NODE_ERROR;
780 }
781 break;
782 }
783
784 if (left_value.type == SQLP_I && right_value.type == SQLP_I &&
785 (nptr->oper == SQLP_ADD || nptr->oper == SQLP_SUBTR ||
786 nptr->oper == SQLP_MLTP)) {
787 value->type = SQLP_I;
788 value->i = (int)dval;
789 }
790 else {
791 value->type = SQLP_D;
792 value->d = dval;
793 }
794 return NODE_VALUE;
795
796 break;
797
798 /* Comparison */
799 /* Operators valid for all type */
800 case SQLP_EQ:
801 if (left == NODE_NULL || right == NODE_NULL) {
802 return NODE_NULL;
803 }
804 else if (left_value.type == SQLP_S) { /* we checked before if right is also string */
805 if (left_value.s && right_value.s &&
806 strcmp(left_value.s, right_value.s) == 0)
807 return NODE_TRUE;
808 else
809 return NODE_FALSE;
810 }
811 else { /* numbers */
812 if (left_dval == right_dval)
813 return NODE_TRUE;
814 else
815 return NODE_FALSE;
816 }
817 break;
818
819 case SQLP_NE:
820 if (left == NODE_NULL || right == NODE_NULL) {
821 return NODE_NULL;
822 }
823 else if (left_value.type == SQLP_S) { /* we checked before if right is also string */
824 if (left_value.s && right_value.s &&
825 strcmp(left_value.s, right_value.s) != 0)
826 return NODE_TRUE;
827 else
828 return NODE_FALSE;
829 }
830 else { /* numbers */
831 if (left_dval != right_dval)
832 return NODE_TRUE;
833 else
834 return NODE_FALSE;
835 }
836
837 /* Operators valid for numbers */
838 case SQLP_LT:
839 if (left == NODE_NULL || right == NODE_NULL) {
840 return NODE_NULL;
841 }
842 else {
843 if (left_dval < right_dval)
844 return NODE_TRUE;
845 else
846 return NODE_FALSE;
847 }
848
849 case SQLP_LE:
850 if (left == NODE_NULL || right == NODE_NULL) {
851 return NODE_NULL;
852 }
853 else {
854 if (left_dval <= right_dval)
855 return NODE_TRUE;
856 else
857 return NODE_FALSE;
858 }
859
860 case SQLP_GT:
861 if (left == NODE_NULL || right == NODE_NULL) {
862 return NODE_NULL;
863 }
864 else {
865 if (left_dval > right_dval)
866 return NODE_TRUE;
867 else
868 return NODE_FALSE;
869 }
870
871 case SQLP_GE:
872 if (left == NODE_NULL || right == NODE_NULL) {
873 return NODE_NULL;
874 }
875 else {
876 if (left_dval >= right_dval)
877 return NODE_TRUE;
878 else
879 return NODE_FALSE;
880 }
881
882 /* Operator valid for string */
883 case SQLP_MTCH:
884 if (left == NODE_NULL || right == NODE_NULL) {
885 return NODE_NULL;
886 }
887 else {
888 /* hack to get '%substring' and 'substring%' working */
889 rightbuf = G_str_replace(right_value.s, "%", "");
890 G_chop(rightbuf);
891 if (left_value.s && right_value.s &&
892 strstr(left_value.s, rightbuf) != NULL) {
893 G_free(rightbuf);
894 return NODE_TRUE;
895 }
896 else {
897 G_free(rightbuf);
898 return NODE_FALSE;
899 }
900 }
901
902 case SQLP_ISNULL:
903 return right == NODE_NULL ? NODE_TRUE : NODE_FALSE;
904
905 case SQLP_NOTNULL:
906 return right != NODE_NULL ? NODE_TRUE : NODE_FALSE;
907
908 /* Logical */
909 case SQLP_AND:
910 if (left == NODE_NULL || right == NODE_NULL) {
911 return NODE_NULL;
912 }
913 else if (left == NODE_TRUE && right == NODE_TRUE) {
914 return NODE_TRUE;
915 }
916 else if (left == NODE_VALUE || right == NODE_VALUE) { /* Should not happen */
917 db_d_append_error(_("Value operand for AND"));
918 return NODE_ERROR;
919 }
920 else {
921 return NODE_FALSE;
922 }
923 case SQLP_OR:
924 if (left == NODE_NULL && right == NODE_NULL) {
925 return NODE_NULL;
926 }
927 else if (left == NODE_TRUE || right == NODE_TRUE) {
928 return NODE_TRUE;
929 }
930 else if (left == NODE_VALUE || right == NODE_VALUE) { /* Should not happen */
931 db_d_append_error(_("Value operand for OR"));
932 return NODE_ERROR;
933 }
934 else {
935 return NODE_FALSE;
936 }
937 case SQLP_NOT:
938 /* sub node stored on the right side */
939 if (right == NODE_NULL) {
940 return NODE_NULL;
941 }
942 else if (right == NODE_TRUE) {
943 return NODE_FALSE;
944 }
945 else if (right == NODE_VALUE) { /* Should not happen */
946 db_d_append_error(_("Value operand for NOT"));
947 return NODE_ERROR;
948 }
949 else {
950 return NODE_TRUE;
951 }
952
953 default:
954 db_d_append_error(_("Unknown operator %d"), nptr->oper);
955 return NODE_FALSE;
956 }
957 }
958
959 return NODE_ERROR; /* Not reached */
960 }
961
962 /* Recursively get value/expression type.
963 * Returns: node type (SQLP_S, SQLP_I, SQLP_D, SQLP_NULL, SQLP_BOOL)
964 * -1 on error (if types in expression are not compatible)
965 *
966 * Rules:
967 * Values (in SQL Statement):
968 * SQLP_S -> SQLP_S
969 * SQLP_I -> SQLP_I
970 * SQLP_D -> SQLP_D
971 * SQLP_NULL -> SQLP_NULL
972 * Columns (in dbf table):
973 * DBF_CHAR -> SQLP_S
974 * DBF_INT -> SQLP_I
975 * DBF_DOUBLE -> SQLP_D
976 * Arithetical Expressions :
977 * side1 side2 exp
978 * SQLP_S ALL ALL -> error
979 * SQLP_NULL SQLP_I ALL -> SQLP_NULL
980 * SQLP_NULL SQLP_D ALL -> SQLP_NULL
981 * SQLP_I SQLP_I +,-,* -> SQLP_I
982 * SQLP_I SQLP_I / -> SQLP_D
983 * SQLP_I SQLP_D ALL -> SQLP_D
984 * SQLP_D SQLP_D ALL -> SQLP_D
985 * Comparisons :
986 * side1 side2 exp
987 * SQLP_S SQLP_S =,<>,~ -> SQLP_BOOL
988 * SQLP_S SQLP_S <,<=,>,>= -> error
989 * SQLP_S SQLP_I ALL -> error
990 * SQLP_S SQLP_D ALL -> error
991 * SQLP_I SQLP_I =,<>,<,<=,>,>= -> SQLP_BOOL
992 * SQLP_D SQLP_D =,<>,<,<=,>,>= -> SQLP_BOOL
993 * SQLP_I SQLP_D =,<>,<,<=,>,>= -> SQLP_BOOL
994 * SQLP_I ALL ~ -> error
995 * SQLP_D ALL ~ -> error
996 * SQLP_NULL ALL ALL -> SQLP_NULL
997 * Logical expressions
998 * In general, if we know that the result is NULL regardless actual values it returns SQLP_NULL
999 * so that tests for individual rows are not performed, otherwise SQLP_BOOL
1000 * SQLP_BOOL SQLP_BOOL AND -> SQLP_BOOL
1001 * SQLP_BOOL SQLP_NULL AND -> SQLP_NULL
1002 * SQLP_NULL SQLP_NULL AND -> SQLP_NULL
1003 * SQLP_BOOL SQLP_BOOL OR -> SQLP_BOOL
1004 * SQLP_BOOL SQLP_NULL OR -> SQLP_BOOL
1005 * SQLP_NULL SQLP_NULL OR -> SQLP_NULL
1006 * SQLP_BOOL - NOT -> SQLP_BOOL
1007 * SQLP_NULL - NOT -> SQLP_NULL
1008 */
eval_node_type(SQLPNODE * nptr,int tab)1009 int eval_node_type(SQLPNODE * nptr, int tab)
1010 {
1011 int left, right;
1012 int ccol;
1013 COLUMN *col = NULL;
1014
1015 switch (nptr->node_type) {
1016 case SQLP_NODE_VALUE:
1017 return nptr->value.type;
1018 break;
1019
1020 case SQLP_NODE_COLUMN:
1021 ccol = find_column(tab, nptr->column_name);
1022 if (ccol == -1) {
1023 db_d_append_error(_("Column '%s' not found"), nptr->column_name);
1024 return -1;
1025 }
1026 col = &(db.tables[tab].cols[ccol]);
1027 switch (col->type) {
1028 case DBF_CHAR:
1029 return (SQLP_S);
1030 break;
1031 case DBF_INT:
1032 return (SQLP_I);
1033 break;
1034 case DBF_DOUBLE:
1035 return (SQLP_D);
1036 break;
1037 }
1038 break;
1039
1040 case SQLP_NODE_EXPRESSION:
1041 /* Note: Some expressions (e.g. NOT) have only one side */
1042 if (nptr->left) {
1043 left = eval_node_type(nptr->left, tab);
1044 if (left == -1)
1045 return -1;
1046 }
1047
1048 if (nptr->right) {
1049 right = eval_node_type(nptr->right, tab);
1050 if (right == -1)
1051 return -1;
1052 }
1053
1054 switch (nptr->oper) {
1055 /* Arithmetical */
1056 case SQLP_ADD:
1057 case SQLP_SUBTR:
1058 case SQLP_MLTP:
1059 case SQLP_DIV:
1060 if (left == SQLP_S || right == SQLP_S) {
1061 db_d_append_error(_("Arithmetical operation with strings is not allowed"));
1062 return -1;
1063 }
1064 else if (left == SQLP_NULL || right == SQLP_NULL) {
1065 return SQLP_NULL;
1066 }
1067 else if (left == SQLP_I && right == SQLP_I &&
1068 (nptr->oper == SQLP_ADD || nptr->oper == SQLP_SUBTR ||
1069 nptr->oper == SQLP_MLTP)) {
1070 return SQLP_I;
1071 }
1072 else {
1073 return SQLP_D;
1074 }
1075 break;
1076
1077 /* Comparison */
1078 /* Operators valid for all type */
1079 case SQLP_EQ:
1080 case SQLP_NE:
1081 if ((left == SQLP_S && (right == SQLP_I || right == SQLP_D)) ||
1082 (right == SQLP_S && (left == SQLP_I || left == SQLP_D))) {
1083 db_d_append_error(_("Comparison between string and number is not allowed"));
1084 return -1;
1085 }
1086 else if (left == SQLP_NULL || right == SQLP_NULL) {
1087 return SQLP_NULL;
1088 }
1089 else {
1090 return SQLP_BOOL;
1091 }
1092 /* Operators valid for numbers */
1093 case SQLP_LT:
1094 case SQLP_LE:
1095 case SQLP_GT:
1096 case SQLP_GE:
1097 if (left == SQLP_S || right == SQLP_S) {
1098 db_d_append_error(_("Comparison '%s' between strings not allowed"),
1099 sqpOperatorName(nptr->oper));
1100 return -1;
1101 }
1102 else if (left == SQLP_NULL || right == SQLP_NULL) {
1103 return SQLP_NULL;
1104 }
1105 else {
1106 return SQLP_BOOL;
1107 }
1108 /* Operator valid for string */
1109 case SQLP_MTCH:
1110 if (left == SQLP_I || left == SQLP_D || right == SQLP_I ||
1111 right == SQLP_D) {
1112 db_d_append_error(_("Match (~) between numbers not allowed"));
1113 return -1;
1114 }
1115 else if (left == SQLP_NULL || right == SQLP_NULL) {
1116 return SQLP_NULL;
1117 }
1118 else {
1119 return SQLP_BOOL;
1120 }
1121
1122 case SQLP_ISNULL:
1123 case SQLP_NOTNULL:
1124 return SQLP_BOOL;
1125
1126 /* Logical */
1127 case SQLP_AND:
1128 if (left == SQLP_NULL || right == SQLP_NULL) {
1129 return SQLP_NULL;
1130 }
1131 else {
1132 return SQLP_BOOL;
1133 }
1134 case SQLP_OR:
1135 if (left == SQLP_NULL && right == SQLP_NULL) {
1136 return SQLP_NULL;
1137 }
1138 else {
1139 return SQLP_BOOL;
1140 }
1141 case SQLP_NOT:
1142 /* sub node stored on the right side */
1143 if (right == SQLP_NULL) {
1144 return SQLP_NULL;
1145 }
1146 else {
1147 return SQLP_BOOL;
1148 }
1149
1150 default:
1151 db_d_append_error(_("Unknown operator %d"), nptr->oper);
1152 return -1;
1153 }
1154 }
1155
1156 return -1; /* Not reached */
1157 }
1158