GRASS Programmer's Manual  6.4.2(2012)
 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[1024];
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_snprintf(buf, 1023, "SELECT %s FROM %s", col, tab);
129  else
130  G_snprintf(buf, 1023, "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_append_string(&stmt, buf);
136 
137  if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
138  return (-1);
139 
140  table = db_get_cursor_table(&cursor);
141  column = db_get_table_column(table, 0); /* first column */
142  value = db_get_column_value(column);
143  type = db_get_column_sqltype(column);
144  type = db_sqltype_to_Ctype(type);
145 
146  /* fetch the data */
147  count = 0;
148  while (1) {
149  if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
150  return (-1);
151 
152  if (!more)
153  break;
154 
155  if (count == alloc) {
156  alloc += 1000;
157  val = (int *)G_realloc(val, alloc * sizeof(int));
158  }
159 
160  switch (type) {
161  case (DB_C_TYPE_INT):
162  val[count] = db_get_value_int(value);
163  break;
164  case (DB_C_TYPE_STRING):
165  sval = db_get_value_string(value);
166  val[count] = atoi(sval);
167  break;
168  case (DB_C_TYPE_DOUBLE):
169  val[count] = (int)db_get_value_double(value);
170  break;
171  default:
172  return (-1);
173  }
174  count++;
175  }
176 
177  db_close_cursor(&cursor);
178  db_free_string(&stmt);
179 
180  qsort((void *)val, count, sizeof(int), cmp);
181 
182  *pval = val;
183 
184  return (count);
185 }
186 
200 int db_select_value(dbDriver * driver, const char *tab, const char *key,
201  int id, const char *col, dbValue * val)
202 {
203  int more, count;
204  char buf[1024];
205  dbString stmt;
206  dbCursor cursor;
207  dbColumn *column;
208  dbValue *value;
209  dbTable *table;
210 
211  if (key == NULL || strlen(key) == 0) {
212  G_warning(_("Missing key column name"));
213  return -1;
214  }
215 
216  if (col == NULL || strlen(col) == 0) {
217  G_warning(_("Missing column name"));
218  return -1;
219  }
220 
221  G_zero(val, sizeof(dbValue));
222  sprintf(buf, "SELECT %s FROM %s WHERE %s = %d\n", col, tab, key, id);
223  db_init_string(&stmt);
224  db_append_string(&stmt, buf);
225 
226  if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
227  return (-1);
228 
229  table = db_get_cursor_table(&cursor);
230  column = db_get_table_column(table, 0); /* first column */
231  value = db_get_column_value(column);
232 
233  /* fetch the data */
234  count = 0;
235  while (1) {
236  if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
237  return (-1);
238 
239  if (!more)
240  break;
241  if (count == 0)
242  db_copy_value(val, value);
243  count++;
244  }
245  db_close_cursor(&cursor);
246  db_free_string(&stmt);
247 
248  return (count);
249 }
250 
263 int db_select_CatValArray(dbDriver * driver, const char *tab, const char *key,
264  const char *col, const char *where,
265  dbCatValArray * cvarr)
266 {
267  int i, type, more, nrows;
268  char buf[1024];
269  dbString stmt;
270  dbCursor cursor;
271  dbColumn *column;
272  dbValue *value;
273  dbTable *table;
274 
275  G_debug(3, "db_select_CatValArray ()");
276 
277  if (key == NULL || strlen(key) == 0) {
278  G_warning(_("Missing key column name"));
279  return -1;
280  }
281 
282  if (col == NULL || strlen(col) == 0) {
283  G_warning(_("Missing column name"));
284  return -1;
285  }
286 
287  db_init_string(&stmt);
288 
289  sprintf(buf, "SELECT %s, %s FROM %s", key, col, tab);
290  db_set_string(&stmt, buf);
291 
292  if (where != NULL && strlen(where) > 0) {
293  db_append_string(&stmt, " WHERE ");
294  db_append_string(&stmt, where);
295  }
296 
297  G_debug(3, " SQL: %s", db_get_string(&stmt));
298 
299  if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
300  return (-1);
301 
302  nrows = db_get_num_rows(&cursor);
303  G_debug(3, " %d rows selected", nrows);
304  if (nrows < 0)
305  G_fatal_error(_("Unable select records from table <%s>"), tab);
306 
307  db_CatValArray_alloc(cvarr, nrows);
308 
309  table = db_get_cursor_table(&cursor);
310 
311  /* Check if key column is integer */
312  column = db_get_table_column(table, 0);
314  G_debug(3, " key type = %d", type);
315 
316  if (type != DB_C_TYPE_INT) {
317  G_fatal_error("Key column type is not integer");
318  }
319 
320  column = db_get_table_column(table, 1);
322  G_debug(3, " col type = %d", type);
323 
324  /*
325  if ( type != DB_C_TYPE_INT && type != DB_C_TYPE_DOUBLE ) {
326  G_fatal_error ( "Column type not supported by db_select_to_array()" );
327  }
328  */
329 
330  cvarr->ctype = type;
331 
332  /* fetch the data */
333  for (i = 0; i < nrows; i++) {
334  if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
335  return (-1);
336 
337  column = db_get_table_column(table, 0); /* first column */
338  value = db_get_column_value(column);
339  cvarr->value[i].cat = db_get_value_int(value);
340 
341  column = db_get_table_column(table, 1);
342  value = db_get_column_value(column);
343  cvarr->value[i].isNull = value->isNull;
344  switch (type) {
345  case (DB_C_TYPE_INT):
346  if (value->isNull)
347  cvarr->value[i].val.i = 0;
348  else
349  cvarr->value[i].val.i = db_get_value_int(value);
350  break;
351 
352  case (DB_C_TYPE_DOUBLE):
353  if (value->isNull)
354  cvarr->value[i].val.d = 0.0;
355  else
356  cvarr->value[i].val.d = db_get_value_double(value);
357  break;
358 
359  case (DB_C_TYPE_STRING):
360  cvarr->value[i].val.s = (dbString *) malloc(sizeof(dbString));
361  db_init_string(cvarr->value[i].val.s);
362 
363  if (!(value->isNull))
364  db_set_string(cvarr->value[i].val.s,
365  db_get_value_string(value));
366  break;
367 
368  case (DB_C_TYPE_DATETIME):
369  cvarr->value[i].val.t =
370  (dbDateTime *) calloc(1, sizeof(dbDateTime));
371 
372  if (!(value->isNull))
373  memcpy(cvarr->value[i].val.t, &(value->t),
374  sizeof(dbDateTime));
375  break;
376 
377  default:
378  return (-1);
379  }
380  }
381  cvarr->n_values = nrows;
382 
383  db_close_cursor(&cursor);
384  db_free_string(&stmt);
385 
386  db_CatValArray_sort(cvarr);
387 
388  return (nrows);
389 }
390 
395 void db_CatValArray_sort(dbCatValArray * arr)
396 {
397  qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal), cmpcat);
398 }
399 
408 int db_CatValArray_sort_by_value(dbCatValArray * arr)
409 {
410  switch (arr->ctype) {
411  case (DB_C_TYPE_INT):
412  qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
413  cmpvalueint);
414  break;
415  case (DB_C_TYPE_DOUBLE):
416  qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
417  cmpvaluedouble);
418  break;
419  case (DB_C_TYPE_STRING):
420  qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
421  cmpvaluestring);
422  break;
423  case (DB_C_TYPE_DATETIME): /* is cmpvaluestring right here ? */
424  qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
425  cmpvaluestring);
426  break;
427  default:
428  return (DB_FAILED);
429  }
430 
431  return (DB_OK);
432 }
433 
444 int db_CatValArray_get_value(dbCatValArray * arr, int key, dbCatVal ** cv)
445 {
446  dbCatVal *catval;
447 
448  catval =
449  bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
450  cmpcat);
451  if (catval == NULL) {
452  return DB_FAILED;
453  }
454 
455  *cv = catval;
456 
457  return DB_OK;
458 }
459 
470 int db_CatValArray_get_value_int(dbCatValArray * arr, int key, int *val)
471 {
472  dbCatVal *catval;
473 
474  catval =
475  bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
476  cmpcat);
477  if (catval == NULL) {
478  return DB_FAILED;
479  }
480 
481  *val = catval->val.i;
482 
483  return DB_OK;
484 }
485 
496 int db_CatValArray_get_value_double(dbCatValArray * arr, int key, double *val)
497 {
498  dbCatVal *catval;
499 
500  G_debug(3, "db_CatValArray_get_value_double(), key = %d", key);
501 
502  catval =
503  bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
504  cmpcatkey);
505  if (catval == NULL) {
506  return DB_FAILED;
507  }
508 
509  *val = catval->val.d;
510 
511  return DB_OK;
512 }