grass  7.8.6
About: GRASS (Geographic Resources Analysis Support System) is a raster- and vector-based GIS, image processing system, graphics production system and spatial modeling system.
  Fossies Dox: grass-7.8.6.tar.gz  ("unofficial" and yet experimental doxygen-generated source code documentation)  

select.c
Go to the documentation of this file.
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
21static 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
33static 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
45static 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
57static 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
70static 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
83static 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*/
103int 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);
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 */
204int 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 */
268int 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);
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);
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*/
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 */
431{
432 switch (arr->ctype) {
433 case (DB_C_TYPE_INT):
434 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
436 break;
437 case (DB_C_TYPE_DOUBLE):
438 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
440 break;
441 case (DB_C_TYPE_STRING):
442 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
444 break;
445 case (DB_C_TYPE_DATETIME): /* is cmpvaluestring right here ? */
446 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
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 */
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 */
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*/
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}
int G_asprintf(char **out, const char *fmt,...)
Definition: asprintf.c:70
int db_close_cursor(dbCursor *cursor)
Close cursor.
Definition: c_close_cur.c:27
int db_fetch(dbCursor *cursor, int position, int *more)
Fetch data from open cursor.
Definition: c_fetch.c:28
int db_open_select_cursor(dbDriver *driver, dbString *select, dbCursor *cursor, int mode)
Open select cursor.
Definition: c_openselect.c:37
int db_get_num_rows(dbCursor *cursor)
Get number of selected rows.
Definition: c_rows.c:26
#define NULL
Definition: ccmath.h:32
dbTable * db_get_cursor_table(dbCursor *cursor)
Get table allocated by cursor.
Definition: cursor.c:67
static int cmpvalueint(const void *pa, const void *pb)
Definition: select.c:57
static int cmpcat(const void *pa, const void *pb)
Definition: select.c:33
static int cmpvaluedouble(const void *pa, const void *pb)
Definition: select.c:70
int db_CatValArray_get_value_double(dbCatValArray *arr, int key, double *val)
Find value (double) by key.
Definition: select.c:518
static int cmp(const void *pa, const void *pb)
Definition: select.c:21
static int cmpvaluestring(const void *pa, const void *pb)
Definition: select.c:83
static int cmpcatkey(const void *pa, const void *pb)
Definition: select.c:45
int db_select_CatValArray(dbDriver *driver, const char *tab, const char *key, const char *col, const char *where, dbCatValArray *cvarr)
Select pairs key/value to array, values are sorted by key (must be integer)
Definition: select.c:268
int db_select_int(dbDriver *driver, const char *tab, const char *col, const char *where, int **pval)
Select array of ordered integers from table/column.
Definition: select.c:103
int db_select_value(dbDriver *driver, const char *tab, const char *key, int id, const char *col, dbValue *val)
Select one (first) value from table/column for key/id.
Definition: select.c:204
int db_CatValArray_get_value_int(dbCatValArray *arr, int key, int *val)
Find value (integer) by key.
Definition: select.c:492
void db_CatValArray_sort(dbCatValArray *arr)
Sort key/value array by key.
Definition: select.c:417
int db_CatValArray_sort_by_value(dbCatValArray *arr)
Sort key/value array by value.
Definition: select.c:430
int db_CatValArray_get_value(dbCatValArray *arr, int key, dbCatVal **cv)
Find value by key.
Definition: select.c:466
struct _db_date_time dbDateTime
#define DB_C_TYPE_INT
Definition: dbmi.h:108
#define DB_SEQUENTIAL
Definition: dbmi.h:123
#define DB_FAILED
Definition: dbmi.h:72
#define DB_C_TYPE_STRING
Definition: dbmi.h:107
#define DB_C_TYPE_DOUBLE
Definition: dbmi.h:109
#define DB_OK
Definition: dbmi.h:71
#define DB_C_TYPE_DATETIME
Definition: dbmi.h:110
#define DB_NEXT
Definition: dbmi.h:114
dbValue * db_get_column_value(dbColumn *column)
Returns column value for given column structure.
Definition: column.c:27
int db_get_column_sqltype(dbColumn *column)
Returns column sqltype for column.
Definition: column.c:142
dbColumn * db_get_table_column(dbTable *table, int idx)
Returns column structure for given table and column number.
Definition: table.c:307
int G_debug(int level, const char *msg,...)
Print debugging message.
Definition: debug.c:65
static int type
Definition: fpxdr.c:101
void G_free(void *buf)
Free allocated memory.
Definition: alloc.c:149
static struct table table
void G_warning(const char *msg,...)
Print a warning message to stderr.
Definition: error.c:204
void G_zero(void *buf, int i)
Zero out a buffer, buf, of length i.
Definition: zero.c:23
#define _(str)
Definition: glocale.h:13
int count
static int ncols
Definition: raster.c:29
double b
Definition: r_raster.c:39
static int more(const char **)
Definition: scan.c:477
int db_sqltype_to_Ctype(int sqltype)
Get C data type based on given SQL data type.
Definition: sqlCtype.c:24
void db_init_string(dbString *x)
Initialize dbString.
Definition: string.c:25
int db_append_string(dbString *x, const char *s)
Append string to dbString.
Definition: string.c:205
char * db_get_string(const dbString *x)
Get string.
Definition: string.c:140
void db_free_string(dbString *x)
Free allocated space for dbString.
Definition: string.c:150
int db_set_string(dbString *x, const char *s)
Inserts string to dbString (enlarge string)
Definition: string.c:41
dbDateTime t
Definition: dbmi.h:199
char isNull
Definition: dbmi.h:195
int ctype
Definition: dbmi.h:288
int n_values
Definition: dbmi.h:286
dbCatVal * value
Definition: dbmi.h:289
Definition: dbmi.h:267
dbDateTime * t
Definition: dbmi.h:279
union dbCatVal::@1 val
int isNull
Definition: dbmi.h:269
int cat
Definition: dbmi.h:268
int i
Definition: dbmi.h:272
dbString * s
Definition: dbmi.h:278
double d
Definition: dbmi.h:273
Definition: driver.h:23
Definition: datum.c:28
static char key[100]
Definition: read.c:24
static unsigned int a
Definition: unfl.c:8
int db_get_value_int(dbValue *value)
Get integer value.
Definition: value.c:38
int db_CatValArray_alloc(dbCatValArray *arr, int n)
Allocate dbCatValArray.
Definition: value.c:401
void db_copy_value(dbValue *dst, dbValue *src)
Copy value.
Definition: value.c:340
const char * db_get_value_string(dbValue *value)
Get string value.
Definition: value.c:92
double db_get_value_double(dbValue *value)
Get double precision value.
Definition: value.c:50