1 /*!
2 * \file db/dbmi_client/select.c
3 *
4 * \brief DBMI Library (client) - select records from table
5 *
6 * (C) 1999-2008 by the GRASS Development Team
7 *
8 * This program is free software under the GNU General Public
9 * License (>=v2). Read the file COPYING that comes with GRASS
10 * for details.
11 *
12 * \author Joel Jones (CERL/UIUC), Radim Blazek
13 */
14
15 #include <stdlib.h>
16 #include <string.h>
17 #include <grass/gis.h>
18 #include <grass/dbmi.h>
19 #include <grass/glocale.h>
20
cmp(const void * pa,const void * pb)21 static int cmp(const void *pa, const void *pb)
22 {
23 int *p1 = (int *)pa;
24 int *p2 = (int *)pb;
25
26 if (*p1 < *p2)
27 return -1;
28 if (*p1 > *p2)
29 return 1;
30 return 0;
31 }
32
cmpcat(const void * pa,const void * pb)33 static int cmpcat(const void *pa, const void *pb)
34 {
35 dbCatVal *p1 = (dbCatVal *) pa;
36 dbCatVal *p2 = (dbCatVal *) pb;
37
38 if (p1->cat < p2->cat)
39 return -1;
40 if (p1->cat > p2->cat)
41 return 1;
42 return 0;
43 }
44
cmpcatkey(const void * pa,const void * pb)45 static int cmpcatkey(const void *pa, const void *pb)
46 {
47 int *p1 = (int *)pa;
48 dbCatVal *p2 = (dbCatVal *) pb;
49
50 if (*p1 < p2->cat)
51 return -1;
52 if (*p1 > p2->cat)
53 return 1;
54 return 0;
55 }
56
cmpvalueint(const void * pa,const void * pb)57 static int cmpvalueint(const void *pa, const void *pb)
58 {
59 dbCatVal *p1 = (dbCatVal *) pa;
60 dbCatVal *p2 = (dbCatVal *) pb;
61
62 if (p1->val.i < p2->val.i)
63 return -1;
64 if (p1->val.i > p2->val.i)
65 return 1;
66
67 return 0;
68 }
69
cmpvaluedouble(const void * pa,const void * pb)70 static int cmpvaluedouble(const void *pa, const void *pb)
71 {
72 dbCatVal *p1 = (dbCatVal *) pa;
73 dbCatVal *p2 = (dbCatVal *) pb;
74
75 if (p1->val.d < p2->val.d)
76 return -1;
77 if (p1->val.d > p2->val.d)
78 return 1;
79
80 return 0;
81 }
82
cmpvaluestring(const void * pa,const void * pb)83 static int cmpvaluestring(const void *pa, const void *pb)
84 {
85 dbCatVal *const *a = pa;
86 dbCatVal *const *b = pb;
87
88 return strcmp((const char *)a, (const char *)b);
89 }
90
91 /*!
92 \brief Select array of ordered integers from table/column
93
94 \param driver DB driver
95 \param tab table name
96 \param col column name
97 \param where where statement
98 \param[out] pval array of ordered integer values
99
100 \return number of selected values
101 \return -1 on error
102 */
db_select_int(dbDriver * driver,const char * tab,const char * col,const char * where,int ** pval)103 int db_select_int(dbDriver * driver, const char *tab, const char *col,
104 const char *where, int **pval)
105 {
106 int type, more, alloc, count;
107 int *val;
108 char *buf = NULL;
109 const char *sval;
110 dbString stmt;
111 dbCursor cursor;
112 dbColumn *column;
113 dbValue *value;
114 dbTable *table;
115
116 G_debug(3, "db_select_int()");
117
118 if (col == NULL || strlen(col) == 0) {
119 G_warning(_("Missing column name"));
120 return -1;
121 }
122
123 /* allocate */
124 alloc = 1000;
125 val = (int *)G_malloc(alloc * sizeof(int));
126
127 if (where == NULL || strlen(where) == 0)
128 G_asprintf(&buf, "SELECT %s FROM %s", col, tab);
129 else
130 G_asprintf(&buf, "SELECT %s FROM %s WHERE %s", col, tab, where);
131
132 G_debug(3, " SQL: %s", buf);
133
134 db_init_string(&stmt);
135 db_set_string(&stmt, buf);
136 G_free(buf);
137
138 if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
139 return (-1);
140
141 table = db_get_cursor_table(&cursor);
142 column = db_get_table_column(table, 0); /* first column */
143 if (column == NULL) {
144 return -1;
145 }
146 value = db_get_column_value(column);
147 type = db_get_column_sqltype(column);
148 type = db_sqltype_to_Ctype(type);
149
150 /* fetch the data */
151 count = 0;
152 while (1) {
153 if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
154 return (-1);
155
156 if (!more)
157 break;
158
159 if (count == alloc) {
160 alloc += 1000;
161 val = (int *)G_realloc(val, alloc * sizeof(int));
162 }
163
164 switch (type) {
165 case (DB_C_TYPE_INT):
166 val[count] = db_get_value_int(value);
167 break;
168 case (DB_C_TYPE_STRING):
169 sval = db_get_value_string(value);
170 val[count] = atoi(sval);
171 break;
172 case (DB_C_TYPE_DOUBLE):
173 val[count] = (int)db_get_value_double(value);
174 break;
175 default:
176 return (-1);
177 }
178 count++;
179 }
180
181 db_close_cursor(&cursor);
182 db_free_string(&stmt);
183
184 qsort((void *)val, count, sizeof(int), cmp);
185
186 *pval = val;
187
188 return (count);
189 }
190
191 /*!
192 \brief Select one (first) value from table/column for key/id
193
194 \param driver DB driver
195 \param tab table name
196 \param key key column name
197 \param id identifier in key column
198 \param col name of column to select the value from
199 \param[out] val dbValue to store within
200
201 \return number of selected values
202 \return -1 on error
203 */
db_select_value(dbDriver * driver,const char * tab,const char * key,int id,const char * col,dbValue * val)204 int db_select_value(dbDriver * driver, const char *tab, const char *key,
205 int id, const char *col, dbValue * val)
206 {
207 int more, count;
208 char *buf = NULL;
209 dbString stmt;
210 dbCursor cursor;
211 dbColumn *column;
212 dbValue *value;
213 dbTable *table;
214
215 if (key == NULL || strlen(key) == 0) {
216 G_warning(_("Missing key column name"));
217 return -1;
218 }
219
220 if (col == NULL || strlen(col) == 0) {
221 G_warning(_("Missing column name"));
222 return -1;
223 }
224
225 G_zero(val, sizeof(dbValue));
226 G_asprintf(&buf, "SELECT %s FROM %s WHERE %s = %d", col, tab, key, id);
227 db_init_string(&stmt);
228 db_set_string(&stmt, buf);
229 G_free(buf);
230
231 if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
232 return (-1);
233
234 table = db_get_cursor_table(&cursor);
235 column = db_get_table_column(table, 0); /* first column */
236 value = db_get_column_value(column);
237
238 /* fetch the data */
239 count = 0;
240 while (1) {
241 if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
242 return (-1);
243
244 if (!more)
245 break;
246 if (count == 0)
247 db_copy_value(val, value);
248 count++;
249 }
250 db_close_cursor(&cursor);
251 db_free_string(&stmt);
252
253 return (count);
254 }
255
256 /*!
257 \brief Select pairs key/value to array, values are sorted by key (must be integer)
258
259 \param driver DB driver
260 \param tab table name
261 \param key key column name
262 \param col value column name
263 \param[out] cvarr dbCatValArray to store within
264
265 \return number of selected values
266 \return -1 on error
267 */
db_select_CatValArray(dbDriver * driver,const char * tab,const char * key,const char * col,const char * where,dbCatValArray * cvarr)268 int db_select_CatValArray(dbDriver * driver, const char *tab, const char *key,
269 const char *col, const char *where,
270 dbCatValArray * cvarr)
271 {
272 int i, type, more, nrows, ncols;
273 char *buf = NULL;
274 dbString stmt;
275 dbCursor cursor;
276 dbColumn *column;
277 dbValue *value;
278 dbTable *table;
279
280 G_debug(3, "db_select_CatValArray ()");
281
282 if (key == NULL || strlen(key) == 0) {
283 G_warning(_("Missing key column name"));
284 return -1;
285 }
286
287 if (col == NULL || strlen(col) == 0) {
288 G_warning(_("Missing column name"));
289 return -1;
290 }
291 db_init_string(&stmt);
292
293 if (strcmp(key, col) == 0) {
294 ncols = 1;
295 G_asprintf(&buf, "SELECT %s FROM %s", key, tab);
296 }
297 else {
298 ncols = 2;
299 G_asprintf(&buf, "SELECT %s, %s FROM %s", key, col, tab);
300 }
301 db_set_string(&stmt, buf);
302 G_free(buf);
303
304 if (where != NULL && strlen(where) > 0) {
305 db_append_string(&stmt, " WHERE ");
306 db_append_string(&stmt, where);
307 }
308
309 G_debug(3, " SQL: %s", db_get_string(&stmt));
310
311 if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
312 return (-1);
313
314 nrows = db_get_num_rows(&cursor);
315 G_debug(3, " %d rows selected", nrows);
316 if (nrows < 0) {
317 G_warning(_("Unable select records from table <%s>"), tab);
318 db_close_cursor(&cursor);
319 db_free_string(&stmt);
320 return -1;
321 }
322
323 db_CatValArray_alloc(cvarr, nrows);
324
325 table = db_get_cursor_table(&cursor);
326
327 /* Check if key column is integer */
328 column = db_get_table_column(table, 0);
329 type = db_sqltype_to_Ctype(db_get_column_sqltype(column));
330 G_debug(3, " key type = %d", type);
331
332 if (type != DB_C_TYPE_INT) {
333 G_warning(_("Key column type is not integer"));
334 db_close_cursor(&cursor);
335 db_free_string(&stmt);
336 return -1;
337 }
338
339 if (ncols == 2) {
340 column = db_get_table_column(table, 1);
341 type = db_sqltype_to_Ctype(db_get_column_sqltype(column));
342 G_debug(3, " col type = %d", type);
343
344 /*
345 if ( type != DB_C_TYPE_INT && type != DB_C_TYPE_DOUBLE ) {
346 G_fatal_error ( "Column type not supported by db_select_to_array()" );
347 }
348 */
349 }
350 cvarr->ctype = type;
351
352 /* fetch the data */
353 for (i = 0; i < nrows; i++) {
354 if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
355 return (-1);
356
357 column = db_get_table_column(table, 0); /* first column */
358 value = db_get_column_value(column);
359 cvarr->value[i].cat = db_get_value_int(value);
360
361 if (ncols == 2) {
362 column = db_get_table_column(table, 1);
363 value = db_get_column_value(column);
364 }
365 cvarr->value[i].isNull = value->isNull;
366 switch (type) {
367 case (DB_C_TYPE_INT):
368 if (value->isNull)
369 cvarr->value[i].val.i = 0;
370 else
371 cvarr->value[i].val.i = db_get_value_int(value);
372 break;
373
374 case (DB_C_TYPE_DOUBLE):
375 if (value->isNull)
376 cvarr->value[i].val.d = 0.0;
377 else
378 cvarr->value[i].val.d = db_get_value_double(value);
379 break;
380
381 case (DB_C_TYPE_STRING):
382 cvarr->value[i].val.s = (dbString *) malloc(sizeof(dbString));
383 db_init_string(cvarr->value[i].val.s);
384
385 if (!(value->isNull))
386 db_set_string(cvarr->value[i].val.s,
387 db_get_value_string(value));
388 break;
389
390 case (DB_C_TYPE_DATETIME):
391 cvarr->value[i].val.t =
392 (dbDateTime *) calloc(1, sizeof(dbDateTime));
393
394 if (!(value->isNull))
395 memcpy(cvarr->value[i].val.t, &(value->t),
396 sizeof(dbDateTime));
397 break;
398
399 default:
400 return (-1);
401 }
402 }
403 cvarr->n_values = nrows;
404
405 db_close_cursor(&cursor);
406 db_free_string(&stmt);
407
408 db_CatValArray_sort(cvarr);
409
410 return nrows;
411 }
412
413 /*!
414 \brief Sort key/value array by key
415 \param[in,out] arr dbCatValArray (key/value array)
416 */
db_CatValArray_sort(dbCatValArray * arr)417 void db_CatValArray_sort(dbCatValArray * arr)
418 {
419 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal), cmpcat);
420 }
421
422 /*!
423 \brief Sort key/value array by value
424
425 \param[in,out] arr dbCatValArray (key/value array)
426
427 \return DB_OK on success
428 \return DB_FAILED on error
429 */
db_CatValArray_sort_by_value(dbCatValArray * arr)430 int db_CatValArray_sort_by_value(dbCatValArray * arr)
431 {
432 switch (arr->ctype) {
433 case (DB_C_TYPE_INT):
434 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
435 cmpvalueint);
436 break;
437 case (DB_C_TYPE_DOUBLE):
438 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
439 cmpvaluedouble);
440 break;
441 case (DB_C_TYPE_STRING):
442 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
443 cmpvaluestring);
444 break;
445 case (DB_C_TYPE_DATETIME): /* is cmpvaluestring right here ? */
446 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
447 cmpvaluestring);
448 break;
449 default:
450 return (DB_FAILED);
451 }
452
453 return (DB_OK);
454 }
455
456 /*!
457 \brief Find value by key
458
459 \param arr dbCatValArray (key/value array)
460 \param key key value
461 \param[out] cv dbCatVal structure (key/value) to store within
462
463 \return DB_OK on success
464 \return DB_FAILED on error
465 */
db_CatValArray_get_value(dbCatValArray * arr,int key,dbCatVal ** cv)466 int db_CatValArray_get_value(dbCatValArray * arr, int key, dbCatVal ** cv)
467 {
468 dbCatVal *catval;
469
470 catval =
471 bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
472 cmpcat);
473 if (catval == NULL) {
474 return DB_FAILED;
475 }
476
477 *cv = catval;
478
479 return DB_OK;
480 }
481
482 /*!
483 \brief Find value (integer) by key
484
485 \param arr dbCatValArray (key/value array)
486 \param key key value
487 \param[out] val found value (integer)
488
489 \return DB_OK on success
490 \return DB_FAILED on error
491 */
db_CatValArray_get_value_int(dbCatValArray * arr,int key,int * val)492 int db_CatValArray_get_value_int(dbCatValArray * arr, int key, int *val)
493 {
494 dbCatVal *catval;
495
496 catval =
497 bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
498 cmpcat);
499 if (catval == NULL) {
500 return DB_FAILED;
501 }
502
503 *val = catval->val.i;
504
505 return DB_OK;
506 }
507
508 /*!
509 \brief Find value (double) by key
510
511 \param arr dbCatValArray (key/value array)
512 \param key key value
513 \param[out] val found value (double)
514
515 \return DB_OK on success
516 \return DB_FAILED on error
517 */
db_CatValArray_get_value_double(dbCatValArray * arr,int key,double * val)518 int db_CatValArray_get_value_double(dbCatValArray * arr, int key, double *val)
519 {
520 dbCatVal *catval;
521
522 G_debug(3, "db_CatValArray_get_value_double(), key = %d", key);
523
524 catval =
525 bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
526 cmpcatkey);
527 if (catval == NULL) {
528 return DB_FAILED;
529 }
530
531 *val = catval->val.d;
532
533 return DB_OK;
534 }
535