GRASS GIS 8 Programmer's Manual  8.5.0dev(2024)-bea8435a9e
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, int id,
205  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
258  integer)
259 
260  \param driver DB driver
261  \param tab table name
262  \param key key column name
263  \param col value column name
264  \param[out] cvarr dbCatValArray to store within
265 
266  \return number of selected values
267  \return -1 on error
268  */
269 int db_select_CatValArray(dbDriver *driver, const char *tab, const char *key,
270  const char *col, const char *where,
271  dbCatValArray *cvarr)
272 {
273  int i, type, more, nrows, ncols;
274  char *buf = NULL;
275  dbString stmt;
276  dbCursor cursor;
277  dbColumn *column;
278  dbValue *value;
279  dbTable *table;
280 
281  G_debug(3, "db_select_CatValArray ()");
282 
283  if (key == NULL || strlen(key) == 0) {
284  G_warning(_("Missing key column name"));
285  return -1;
286  }
287 
288  if (col == NULL || strlen(col) == 0) {
289  G_warning(_("Missing column name"));
290  return -1;
291  }
292  db_init_string(&stmt);
293 
294  if (strcmp(key, col) == 0) {
295  ncols = 1;
296  G_asprintf(&buf, "SELECT %s FROM %s", key, tab);
297  }
298  else {
299  ncols = 2;
300  G_asprintf(&buf, "SELECT %s, %s FROM %s", key, col, tab);
301  }
302  db_set_string(&stmt, buf);
303  G_free(buf);
304 
305  if (where != NULL && strlen(where) > 0) {
306  db_append_string(&stmt, " WHERE ");
307  db_append_string(&stmt, where);
308  }
309 
310  G_debug(3, " SQL: %s", db_get_string(&stmt));
311 
312  if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
313  return (-1);
314 
315  nrows = db_get_num_rows(&cursor);
316  G_debug(3, " %d rows selected", nrows);
317  if (nrows < 0) {
318  G_warning(_("Unable select records from table <%s>"), tab);
319  db_close_cursor(&cursor);
320  db_free_string(&stmt);
321  return -1;
322  }
323 
324  db_CatValArray_alloc(cvarr, nrows);
325 
326  table = db_get_cursor_table(&cursor);
327 
328  /* Check if key column is integer */
329  column = db_get_table_column(table, 0);
331  G_debug(3, " key type = %d", type);
332 
333  if (type != DB_C_TYPE_INT) {
334  G_warning(_("Key column type is not integer"));
335  db_close_cursor(&cursor);
336  db_free_string(&stmt);
337  return -1;
338  }
339 
340  if (ncols == 2) {
341  column = db_get_table_column(table, 1);
343  G_debug(3, " col type = %d", type);
344 
345  /*
346  if ( type != DB_C_TYPE_INT && type != DB_C_TYPE_DOUBLE ) {
347  G_fatal_error ( "Column type not supported by db_select_to_array()"
348  );
349  }
350  */
351  }
352  cvarr->ctype = type;
353 
354  /* fetch the data */
355  for (i = 0; i < nrows; i++) {
356  if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
357  return (-1);
358 
359  column = db_get_table_column(table, 0); /* first column */
360  value = db_get_column_value(column);
361  cvarr->value[i].cat = db_get_value_int(value);
362 
363  if (ncols == 2) {
364  column = db_get_table_column(table, 1);
365  value = db_get_column_value(column);
366  }
367  cvarr->value[i].isNull = value->isNull;
368  switch (type) {
369  case (DB_C_TYPE_INT):
370  if (value->isNull)
371  cvarr->value[i].val.i = 0;
372  else
373  cvarr->value[i].val.i = db_get_value_int(value);
374  break;
375 
376  case (DB_C_TYPE_DOUBLE):
377  if (value->isNull)
378  cvarr->value[i].val.d = 0.0;
379  else
380  cvarr->value[i].val.d = db_get_value_double(value);
381  break;
382 
383  case (DB_C_TYPE_STRING):
384  cvarr->value[i].val.s = (dbString *)malloc(sizeof(dbString));
385  db_init_string(cvarr->value[i].val.s);
386 
387  if (!(value->isNull))
388  db_set_string(cvarr->value[i].val.s,
389  db_get_value_string(value));
390  break;
391 
392  case (DB_C_TYPE_DATETIME):
393  cvarr->value[i].val.t = (dbDateTime *)calloc(1, sizeof(dbDateTime));
394 
395  if (!(value->isNull))
396  memcpy(cvarr->value[i].val.t, &(value->t), 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), cmpvalueint);
435  break;
436  case (DB_C_TYPE_DOUBLE):
437  qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
438  cmpvaluedouble);
439  break;
440  case (DB_C_TYPE_STRING):
441  qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
442  cmpvaluestring);
443  break;
444  case (DB_C_TYPE_DATETIME): /* is cmpvaluestring right here ? */
445  qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
446  cmpvaluestring);
447  break;
448  default:
449  return (DB_FAILED);
450  }
451 
452  return (DB_OK);
453 }
454 
455 /*!
456  \brief Find value by key
457 
458  \param arr dbCatValArray (key/value array)
459  \param key key value
460  \param[out] cv dbCatVal structure (key/value) to store within
461 
462  \return DB_OK on success
463  \return DB_FAILED on error
464  */
466 {
467  dbCatVal *catval;
468 
469  catval = bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
470  cmpcat);
471  if (catval == NULL) {
472  return DB_FAILED;
473  }
474 
475  *cv = catval;
476 
477  return DB_OK;
478 }
479 
480 /*!
481  \brief Find value (integer) by key
482 
483  \param arr dbCatValArray (key/value array)
484  \param key key value
485  \param[out] val found value (integer)
486 
487  \return DB_OK on success
488  \return DB_FAILED on error
489  */
490 int db_CatValArray_get_value_int(dbCatValArray *arr, int key, int *val)
491 {
492  dbCatVal *catval;
493 
494  catval = bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
495  cmpcat);
496  if (catval == NULL) {
497  return DB_FAILED;
498  }
499 
500  *val = catval->val.i;
501 
502  return DB_OK;
503 }
504 
505 /*!
506  \brief Find value (double) by key
507 
508  \param arr dbCatValArray (key/value array)
509  \param key key value
510  \param[out] val found value (double)
511 
512  \return DB_OK on success
513  \return DB_FAILED on error
514  */
515 int db_CatValArray_get_value_double(dbCatValArray *arr, int key, double *val)
516 {
517  dbCatVal *catval;
518 
519  G_debug(3, "db_CatValArray_get_value_double(), key = %d", key);
520 
521  catval = bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
522  cmpcatkey);
523  if (catval == NULL) {
524  return DB_FAILED;
525  }
526 
527  *val = catval->val.d;
528 
529  return DB_OK;
530 }
#define NULL
Definition: ccmath.h:32
int db_CatValArray_get_value_double(dbCatValArray *arr, int key, double *val)
Find value (double) by key.
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)
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.
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.
int db_CatValArray_get_value_int(dbCatValArray *arr, int key, int *val)
Find value (integer) by key.
void db_CatValArray_sort(dbCatValArray *arr)
Sort key/value array by key.
int db_CatValArray_sort_by_value(dbCatValArray *arr)
Sort key/value array by value.
int db_CatValArray_get_value(dbCatValArray *arr, int key, dbCatVal **cv)
Find value by key.
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
void db_copy_value(dbValue *, dbValue *)
Copy value.
Definition: value.c:340
const char * db_get_value_string(dbValue *)
Get string value.
Definition: value.c:92
dbValue * db_get_column_value(dbColumn *)
Returns column value for given column structure.
dbColumn * db_get_table_column(dbTable *, int)
Returns column structure for given table and column number.
int db_CatValArray_alloc(dbCatValArray *, int)
Allocate dbCatValArray.
Definition: value.c:401
double db_get_value_double(dbValue *)
Get double precision value.
Definition: value.c:50
int db_sqltype_to_Ctype(int)
Get C data type based on given SQL data type.
Definition: sqlCtype.c:24
int db_get_column_sqltype(dbColumn *)
Returns column sqltype for column.
int db_get_num_rows(dbCursor *)
Get number of selected rows.
Definition: c_rows.c:26
dbTable * db_get_cursor_table(dbCursor *)
Get table allocated by cursor.
Definition: cursor.c:67
void db_free_string(dbString *)
Free allocated space for dbString.
Definition: string.c:150
int db_set_string(dbString *, const char *)
Inserts string to dbString (enlarge string)
Definition: string.c:41
int db_get_value_int(dbValue *)
Get integer value.
Definition: value.c:38
void db_init_string(dbString *)
Initialize dbString.
Definition: string.c:25
int db_close_cursor(dbCursor *)
Close cursor.
Definition: c_close_cur.c:27
int db_open_select_cursor(dbDriver *, dbString *, dbCursor *, int)
Open select cursor.
Definition: c_openselect.c:37
int db_append_string(dbString *, const char *)
Append string to dbString.
Definition: string.c:205
char * db_get_string(const dbString *)
Get string.
Definition: string.c:140
int db_fetch(dbCursor *, int, int *)
Fetch data from open cursor.
Definition: c_fetch.c:28
void G_zero(void *, int)
Zero out a buffer, buf, of length i.
Definition: gis/zero.c:23
void G_free(void *)
Free allocated memory.
Definition: gis/alloc.c:150
#define G_realloc(p, n)
Definition: defs/gis.h:96
void G_warning(const char *,...) __attribute__((format(printf
#define G_malloc(n)
Definition: defs/gis.h:94
int G_asprintf(char **, const char *,...) __attribute__((format(printf
int G_debug(int, const char *,...) __attribute__((format(printf
#define _(str)
Definition: glocale.h:10
int count
double b
Definition: r_raster.c:39
void * malloc(YYSIZE_T)
dbDateTime t
Definition: dbmi.h:192
char isNull
Definition: dbmi.h:188
int ctype
Definition: dbmi.h:272
int n_values
Definition: dbmi.h:270
dbCatVal * value
Definition: dbmi.h:273
Definition: dbmi.h:253
dbDateTime * t
Definition: dbmi.h:264
union dbCatVal::@1 val
int isNull
Definition: dbmi.h:255
int cat
Definition: dbmi.h:254
int i
Definition: dbmi.h:257
dbString * s
Definition: dbmi.h:263
double d
Definition: dbmi.h:258
Definition: driver.h:21