00001
00015 #include <stdlib.h>
00016 #include <string.h>
00017 #include <grass/gis.h>
00018 #include <grass/dbmi.h>
00019 #include <grass/glocale.h>
00020
00021 static int cmp(const void *pa, const void *pb)
00022 {
00023 int *p1 = (int *)pa;
00024 int *p2 = (int *)pb;
00025
00026 if (*p1 < *p2)
00027 return -1;
00028 if (*p1 > *p2)
00029 return 1;
00030 return 0;
00031 }
00032
00033 static int cmpcat(const void *pa, const void *pb)
00034 {
00035 dbCatVal *p1 = (dbCatVal *) pa;
00036 dbCatVal *p2 = (dbCatVal *) pb;
00037
00038 if (p1->cat < p2->cat)
00039 return -1;
00040 if (p1->cat > p2->cat)
00041 return 1;
00042 return 0;
00043 }
00044
00045 static int cmpcatkey(const void *pa, const void *pb)
00046 {
00047 int *p1 = (int *)pa;
00048 dbCatVal *p2 = (dbCatVal *) pb;
00049
00050 if (*p1 < p2->cat)
00051 return -1;
00052 if (*p1 > p2->cat)
00053 return 1;
00054 return 0;
00055 }
00056
00057 static int cmpvalueint(const void *pa, const void *pb)
00058 {
00059 dbCatVal *p1 = (dbCatVal *) pa;
00060 dbCatVal *p2 = (dbCatVal *) pb;
00061
00062 if (p1->val.i < p2->val.i)
00063 return -1;
00064 if (p1->val.i > p2->val.i)
00065 return 1;
00066
00067 return 0;
00068 }
00069
00070 static int cmpvaluedouble(const void *pa, const void *pb)
00071 {
00072 dbCatVal *p1 = (dbCatVal *) pa;
00073 dbCatVal *p2 = (dbCatVal *) pb;
00074
00075 if (p1->val.d < p2->val.d)
00076 return -1;
00077 if (p1->val.d > p2->val.d)
00078 return 1;
00079
00080 return 0;
00081 }
00082
00083 static int cmpvaluestring(const void *pa, const void *pb)
00084 {
00085 dbCatVal *const *a = pa;
00086 dbCatVal *const *b = pb;
00087
00088 return strcmp((const char *)a, (const char *)b);
00089 }
00090
00103 int db_select_int(dbDriver * driver, const char *tab, const char *col,
00104 const char *where, int **pval)
00105 {
00106 int type, more, alloc, count;
00107 int *val;
00108 char buf[1024];
00109 const char *sval;
00110 dbString stmt;
00111 dbCursor cursor;
00112 dbColumn *column;
00113 dbValue *value;
00114 dbTable *table;
00115
00116 G_debug(3, "db_select_int()");
00117
00118
00119 alloc = 1000;
00120 val = (int *)G_malloc(alloc * sizeof(int));
00121
00122 if (where == NULL || strlen(where) == 0)
00123 G_snprintf(buf, 1023, "SELECT %s FROM %s", col, tab);
00124 else
00125 G_snprintf(buf, 1023, "SELECT %s FROM %s WHERE %s", col, tab, where);
00126
00127 G_debug(3, " SQL: %s", buf);
00128
00129 db_init_string(&stmt);
00130 db_append_string(&stmt, buf);
00131
00132 if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
00133 return (-1);
00134
00135 table = db_get_cursor_table(&cursor);
00136 column = db_get_table_column(table, 0);
00137 value = db_get_column_value(column);
00138 type = db_get_column_sqltype(column);
00139 type = db_sqltype_to_Ctype(type);
00140
00141
00142 count = 0;
00143 while (1) {
00144 if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
00145 return (-1);
00146
00147 if (!more)
00148 break;
00149
00150 if (count == alloc) {
00151 alloc += 1000;
00152 val = (int *)G_realloc(val, alloc * sizeof(int));
00153 }
00154
00155 switch (type) {
00156 case (DB_C_TYPE_INT):
00157 val[count] = db_get_value_int(value);
00158 break;
00159 case (DB_C_TYPE_STRING):
00160 sval = db_get_value_string(value);
00161 val[count] = atoi(sval);
00162 break;
00163 case (DB_C_TYPE_DOUBLE):
00164 val[count] = (int)db_get_value_double(value);
00165 break;
00166 default:
00167 return (-1);
00168 }
00169 count++;
00170 }
00171
00172 db_close_cursor(&cursor);
00173 db_free_string(&stmt);
00174
00175 qsort((void *)val, count, sizeof(int), cmp);
00176
00177 *pval = val;
00178
00179 return (count);
00180 }
00181
00195 int db_select_value(dbDriver * driver, const char *tab, const char *key,
00196 int id, const char *col, dbValue * val)
00197 {
00198 int more, count;
00199 char buf[1024];
00200 dbString stmt;
00201 dbCursor cursor;
00202 dbColumn *column;
00203 dbValue *value;
00204 dbTable *table;
00205
00206 sprintf(buf, "SELECT %s FROM %s WHERE %s = %d\n", col, tab, key, id);
00207 db_init_string(&stmt);
00208 db_append_string(&stmt, buf);
00209
00210 if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
00211 return (-1);
00212
00213 table = db_get_cursor_table(&cursor);
00214 column = db_get_table_column(table, 0);
00215 value = db_get_column_value(column);
00216
00217
00218 count = 0;
00219 while (1) {
00220 if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
00221 return (-1);
00222
00223 if (!more)
00224 break;
00225 if (count == 0)
00226 db_copy_value(val, value);
00227 count++;
00228 }
00229 db_close_cursor(&cursor);
00230 db_free_string(&stmt);
00231
00232 return (count);
00233 }
00234
00247 int db_select_CatValArray(dbDriver * driver, const char *tab, const char *key,
00248 const char *col, const char *where,
00249 dbCatValArray * cvarr)
00250 {
00251 int i, type, more, nrows;
00252 char buf[1024];
00253 dbString stmt;
00254 dbCursor cursor;
00255 dbColumn *column;
00256 dbValue *value;
00257 dbTable *table;
00258
00259 G_debug(3, "db_select_db_select_CatValArray ()");
00260
00261 db_init_string(&stmt);
00262
00263 sprintf(buf, "SELECT %s, %s FROM %s", key, col, tab);
00264 db_set_string(&stmt, buf);
00265
00266 if (where != NULL && strlen(where) > 0) {
00267 db_append_string(&stmt, " WHERE ");
00268 db_append_string(&stmt, where);
00269 }
00270
00271 G_debug(3, " SQL: %s", db_get_string(&stmt));
00272
00273 if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
00274 return (-1);
00275
00276 nrows = db_get_num_rows(&cursor);
00277 G_debug(3, " %d rows selected", nrows);
00278 if (nrows < 0)
00279 G_fatal_error(_("Unable select records from table <%s>"), tab);
00280
00281 db_CatValArray_alloc(cvarr, nrows);
00282
00283 table = db_get_cursor_table(&cursor);
00284
00285
00286 column = db_get_table_column(table, 0);
00287 type = db_sqltype_to_Ctype(db_get_column_sqltype(column));
00288 G_debug(3, " key type = %d", type);
00289
00290 if (type != DB_C_TYPE_INT) {
00291 G_fatal_error("Key column type is not integer");
00292 }
00293
00294 column = db_get_table_column(table, 1);
00295 type = db_sqltype_to_Ctype(db_get_column_sqltype(column));
00296 G_debug(3, " col type = %d", type);
00297
00298
00299
00300
00301
00302
00303
00304 cvarr->ctype = type;
00305
00306
00307 for (i = 0; i < nrows; i++) {
00308 if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
00309 return (-1);
00310
00311 column = db_get_table_column(table, 0);
00312 value = db_get_column_value(column);
00313 cvarr->value[i].cat = db_get_value_int(value);
00314
00315 column = db_get_table_column(table, 1);
00316 value = db_get_column_value(column);
00317 cvarr->value[i].isNull = value->isNull;
00318 switch (type) {
00319 case (DB_C_TYPE_INT):
00320 if (value->isNull)
00321 cvarr->value[i].val.i = 0;
00322 else
00323 cvarr->value[i].val.i = db_get_value_int(value);
00324 break;
00325
00326 case (DB_C_TYPE_DOUBLE):
00327 if (value->isNull)
00328 cvarr->value[i].val.d = 0.0;
00329 else
00330 cvarr->value[i].val.d = db_get_value_double(value);
00331 break;
00332
00333 case (DB_C_TYPE_STRING):
00334 cvarr->value[i].val.s = (dbString *) malloc(sizeof(dbString));
00335 db_init_string(cvarr->value[i].val.s);
00336
00337 if (!(value->isNull))
00338 db_set_string(cvarr->value[i].val.s,
00339 db_get_value_string(value));
00340 break;
00341
00342 case (DB_C_TYPE_DATETIME):
00343 cvarr->value[i].val.t =
00344 (dbDateTime *) calloc(1, sizeof(dbDateTime));
00345
00346 if (!(value->isNull))
00347 memcpy(cvarr->value[i].val.t, &(value->t),
00348 sizeof(dbDateTime));
00349 break;
00350
00351 default:
00352 return (-1);
00353 }
00354 }
00355 cvarr->n_values = nrows;
00356
00357 db_close_cursor(&cursor);
00358 db_free_string(&stmt);
00359
00360 db_CatValArray_sort(cvarr);
00361
00362 return (nrows);
00363 }
00364
00369 void db_CatValArray_sort(dbCatValArray * arr)
00370 {
00371 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal), cmpcat);
00372 }
00373
00382 int db_CatValArray_sort_by_value(dbCatValArray * arr)
00383 {
00384 switch (arr->ctype) {
00385 case (DB_C_TYPE_INT):
00386 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
00387 cmpvalueint);
00388 break;
00389 case (DB_C_TYPE_DOUBLE):
00390 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
00391 cmpvaluedouble);
00392 break;
00393 case (DB_C_TYPE_STRING):
00394 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
00395 cmpvaluestring);
00396 break;
00397 case (DB_C_TYPE_DATETIME):
00398 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
00399 cmpvaluestring);
00400 break;
00401 default:
00402 return (DB_FAILED);
00403 }
00404
00405 return (DB_OK);
00406 }
00407
00418 int db_CatValArray_get_value(dbCatValArray * arr, int key, dbCatVal ** cv)
00419 {
00420 dbCatVal *catval;
00421
00422 catval =
00423 bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
00424 cmpcat);
00425 if (catval == NULL) {
00426 return DB_FAILED;
00427 }
00428
00429 *cv = catval;
00430
00431 return DB_OK;
00432 }
00433
00444 int db_CatValArray_get_value_int(dbCatValArray * arr, int key, int *val)
00445 {
00446 dbCatVal *catval;
00447
00448 catval =
00449 bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
00450 cmpcat);
00451 if (catval == NULL) {
00452 return DB_FAILED;
00453 }
00454
00455 *val = catval->val.i;
00456
00457 return DB_OK;
00458 }
00459
00470 int db_CatValArray_get_value_double(dbCatValArray * arr, int key, double *val)
00471 {
00472 dbCatVal *catval;
00473
00474 G_debug(3, "db_CatValArray_get_value_double(), key = %d", key);
00475
00476 catval =
00477 bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
00478 cmpcatkey);
00479 if (catval == NULL) {
00480 return DB_FAILED;
00481 }
00482
00483 *val = catval->val.d;
00484
00485 return DB_OK;
00486 }