GRASS GIS 8 Programmer's Manual  8.2.2dev(2023)-3d2c704037
db/dbmi_client/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 
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 
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 
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 
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 
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 
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 */
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  */
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  */
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);
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),
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  */
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  */
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 */
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 }
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)
#define G_malloc(n)
Definition: defs/gis.h:112
int isNull
Definition: dbmi.h:269
dbValue * db_get_column_value(dbColumn *)
Returns column value for given column structure.
void db_CatValArray_sort(dbCatValArray *arr)
Sort key/value array by key.
int db_CatValArray_get_value_int(dbCatValArray *arr, int key, int *val)
Find value (integer) by key.
struct _db_date_time dbDateTime
int n_values
Definition: dbmi.h:286
void db_copy_value(dbValue *, dbValue *)
Copy value.
Definition: value.c:340
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.
void db_init_string(dbString *)
Initialize dbString.
Definition: string.c:25
int db_fetch(dbCursor *, int, int *)
Fetch data from open cursor.
Definition: c_fetch.c:28
int db_open_select_cursor(dbDriver *, dbString *, dbCursor *, int)
Open select cursor.
Definition: c_openselect.c:37
int ctype
Definition: dbmi.h:288
char * db_get_string(const dbString *)
Get string.
Definition: string.c:140
#define DB_C_TYPE_DATETIME
Definition: dbmi.h:110
void G_free(void *)
Free allocated memory.
Definition: gis/alloc.c:149
int db_get_value_int(dbValue *)
Get integer value.
Definition: value.c:38
int count
#define NULL
Definition: ccmath.h:32
dbCatVal * value
Definition: dbmi.h:289
int db_CatValArray_alloc(dbCatValArray *, int)
Allocate dbCatValArray.
Definition: value.c:401
int db_set_string(dbString *, const char *)
Inserts string to dbString (enlarge string)
Definition: string.c:41
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.
dbDateTime * t
Definition: dbmi.h:279
int db_CatValArray_get_value_double(dbCatValArray *arr, int key, double *val)
Find value (double) by key.
void * malloc(YYSIZE_T)
int db_get_column_sqltype(dbColumn *)
Returns column sqltype for column.
int db_append_string(dbString *, const char *)
Append string to dbString.
Definition: string.c:205
double b
Definition: r_raster.c:39
int cat
Definition: dbmi.h:268
Definition: dbmi.h:266
#define DB_NEXT
Definition: dbmi.h:114
#define DB_C_TYPE_STRING
Definition: dbmi.h:107
int db_get_num_rows(dbCursor *)
Get number of selected rows.
Definition: c_rows.c:26
int db_sqltype_to_Ctype(int)
Get C data type based on given SQL data type.
Definition: sqlCtype.c:24
int i
Definition: dbmi.h:272
#define DB_C_TYPE_INT
Definition: dbmi.h:108
dbDateTime t
Definition: dbmi.h:199
dbString * s
Definition: dbmi.h:278
const char * db_get_value_string(dbValue *)
Get string value.
Definition: value.c:92
Definition: driver.h:22
double db_get_value_double(dbValue *)
Get double precision value.
Definition: value.c:50
#define DB_FAILED
Definition: dbmi.h:72
#define DB_SEQUENTIAL
Definition: dbmi.h:123
void G_zero(void *, int)
Zero out a buffer, buf, of length i.
Definition: gis/zero.c:23
#define DB_C_TYPE_DOUBLE
Definition: dbmi.h:109
void G_warning(const char *,...) __attribute__((format(printf
int db_CatValArray_sort_by_value(dbCatValArray *arr)
Sort key/value array by value.
dbColumn * db_get_table_column(dbTable *, int)
Returns column structure for given table and column number.
union dbCatVal::@1 val
char isNull
Definition: dbmi.h:195
#define G_realloc(p, n)
Definition: defs/gis.h:114
#define _(str)
Definition: glocale.h:10
int G_asprintf(char **, const char *,...) __attribute__((format(printf
dbTable * db_get_cursor_table(dbCursor *)
Get table allocated by cursor.
Definition: cursor.c:67
int db_CatValArray_get_value(dbCatValArray *arr, int key, dbCatVal **cv)
Find value by key.
int G_debug(int, const char *,...) __attribute__((format(printf
double d
Definition: dbmi.h:273
void db_free_string(dbString *)
Free allocated space for dbString.
Definition: string.c:150
int db_close_cursor(dbCursor *)
Close cursor.
Definition: c_close_cur.c:27
#define DB_OK
Definition: dbmi.h:71