GRASS Programmer's Manual  6.5.svn(2014)-r66266
 All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Macros Pages
db/dbmi_client/select.c
Go to the documentation of this file.
1 
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 
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 
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 
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 
417 void db_CatValArray_sort(dbCatValArray * arr)
418 {
419  qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal), cmpcat);
420 }
421 
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 
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 
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 
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 }
dbColumn * db_get_table_column(dbTable *table, int n)
returns column structure for given table and column number
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_close_cursor(dbCursor *cursor)
Close cursor.
Definition: c_close_cur.c:27
void G_free(void *buf)
Free allocated memory.
Definition: gis/alloc.c:142
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.
float b
Definition: named_colr.c:8
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 count
int G_asprintf(char **out, const char *fmt,...)
Definition: asprintf.c:116
int db_CatValArray_alloc(dbCatValArray *arr, int n)
Definition: value.c:372
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_CatValArray_get_value_double(dbCatValArray *arr, int key, double *val)
Find value (double) by key.
int db_append_string(dbString *x, const char *s)
Definition: string.c:193
int db_sqltype_to_Ctype(int sqltype)
Definition: sqlCtype.c:9
int db_fetch(dbCursor *cursor, int position, int *more)
Fetch data.
Definition: c_fetch.c:28
int G_zero(void *buf, int i)
Zero out a buffer, buf, of length i.
Definition: gis/zero.c:29
dbTable * db_get_cursor_table(dbCursor *cursor)
Definition: cursor.c:55
double db_get_value_double(dbValue *value)
Definition: value.c:32
int db_get_column_sqltype(dbColumn *column)
returns column sqltype for column (the function db_sqltype_name() returns sqltype description) ...
void * malloc(YYSIZE_T)
dbValue * db_get_column_value(dbColumn *column)
returns column value for given column structure
int db_get_num_rows(dbCursor *cursor)
Get number of selected rows.
Definition: c_rows.c:26
int
Definition: g3dcolor.c:48
char * value
Definition: env.c:30
char buf[GNAME_MAX+sizeof(G3D_DIRECTORY)+2]
Definition: g3drange.c:62
return NULL
Definition: dbfopen.c:1394
Definition: driver.h:25
G_warning("category support for [%s] in mapset [%s] %s", name, mapset, type)
char * db_get_string(dbString *x)
Definition: string.c:131
int G_debug(int level, const char *msg,...)
Print debugging message.
Definition: gis/debug.c:51
int db_CatValArray_sort_by_value(dbCatValArray *arr)
Sort key/value array by value.
int db_set_string(dbString *x, const char *s)
Definition: string.c:33
int db_get_value_int(dbValue *value)
Definition: value.c:21
CELL cat
Definition: g3dcats.c:90
void db_free_string(dbString *x)
Definition: string.c:142
int db_CatValArray_get_value(dbCatValArray *arr, int key, dbCatVal **cv)
Find value by key.
const char * db_get_value_string(dbValue *value)
Definition: value.c:70
int db_open_select_cursor(dbDriver *driver, dbString *select, dbCursor *cursor, int mode)
Open select cursor.
Definition: c_openselect.c:29
void db_init_string(dbString *x)
Definition: string.c:11
void db_copy_value(dbValue *dst, dbValue *src)
Definition: value.c:313