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 if (col == NULL || strlen(col) == 0) {
00119 G_warning(_("Missing column name"));
00120 return -1;
00121 }
00122
00123
00124 alloc = 1000;
00125 val = (int *)G_malloc(alloc * sizeof(int));
00126
00127 if (where == NULL || strlen(where) == 0)
00128 G_snprintf(buf, 1023, "SELECT %s FROM %s", col, tab);
00129 else
00130 G_snprintf(buf, 1023, "SELECT %s FROM %s WHERE %s", col, tab, where);
00131
00132 G_debug(3, " SQL: %s", buf);
00133
00134 db_init_string(&stmt);
00135 db_append_string(&stmt, buf);
00136
00137 if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
00138 return (-1);
00139
00140 table = db_get_cursor_table(&cursor);
00141 column = db_get_table_column(table, 0);
00142 value = db_get_column_value(column);
00143 type = db_get_column_sqltype(column);
00144 type = db_sqltype_to_Ctype(type);
00145
00146
00147 count = 0;
00148 while (1) {
00149 if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
00150 return (-1);
00151
00152 if (!more)
00153 break;
00154
00155 if (count == alloc) {
00156 alloc += 1000;
00157 val = (int *)G_realloc(val, alloc * sizeof(int));
00158 }
00159
00160 switch (type) {
00161 case (DB_C_TYPE_INT):
00162 val[count] = db_get_value_int(value);
00163 break;
00164 case (DB_C_TYPE_STRING):
00165 sval = db_get_value_string(value);
00166 val[count] = atoi(sval);
00167 break;
00168 case (DB_C_TYPE_DOUBLE):
00169 val[count] = (int)db_get_value_double(value);
00170 break;
00171 default:
00172 return (-1);
00173 }
00174 count++;
00175 }
00176
00177 db_close_cursor(&cursor);
00178 db_free_string(&stmt);
00179
00180 qsort((void *)val, count, sizeof(int), cmp);
00181
00182 *pval = val;
00183
00184 return (count);
00185 }
00186
00200 int db_select_value(dbDriver * driver, const char *tab, const char *key,
00201 int id, const char *col, dbValue * val)
00202 {
00203 int more, count;
00204 char buf[1024];
00205 dbString stmt;
00206 dbCursor cursor;
00207 dbColumn *column;
00208 dbValue *value;
00209 dbTable *table;
00210
00211 if (key == NULL || strlen(key) == 0) {
00212 G_warning(_("Missing key column name"));
00213 return -1;
00214 }
00215
00216 if (col == NULL || strlen(col) == 0) {
00217 G_warning(_("Missing column name"));
00218 return -1;
00219 }
00220
00221 G_zero(val, sizeof(dbValue));
00222 sprintf(buf, "SELECT %s FROM %s WHERE %s = %d\n", col, tab, key, id);
00223 db_init_string(&stmt);
00224 db_append_string(&stmt, buf);
00225
00226 if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
00227 return (-1);
00228
00229 table = db_get_cursor_table(&cursor);
00230 column = db_get_table_column(table, 0);
00231 value = db_get_column_value(column);
00232
00233
00234 count = 0;
00235 while (1) {
00236 if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
00237 return (-1);
00238
00239 if (!more)
00240 break;
00241 if (count == 0)
00242 db_copy_value(val, value);
00243 count++;
00244 }
00245 db_close_cursor(&cursor);
00246 db_free_string(&stmt);
00247
00248 return (count);
00249 }
00250
00263 int db_select_CatValArray(dbDriver * driver, const char *tab, const char *key,
00264 const char *col, const char *where,
00265 dbCatValArray * cvarr)
00266 {
00267 int i, type, more, nrows;
00268 char buf[1024];
00269 dbString stmt;
00270 dbCursor cursor;
00271 dbColumn *column;
00272 dbValue *value;
00273 dbTable *table;
00274
00275 G_debug(3, "db_select_CatValArray ()");
00276
00277 if (key == NULL || strlen(key) == 0) {
00278 G_warning(_("Missing key column name"));
00279 return -1;
00280 }
00281
00282 if (col == NULL || strlen(col) == 0) {
00283 G_warning(_("Missing column name"));
00284 return -1;
00285 }
00286
00287 db_init_string(&stmt);
00288
00289 sprintf(buf, "SELECT %s, %s FROM %s", key, col, tab);
00290 db_set_string(&stmt, buf);
00291
00292 if (where != NULL && strlen(where) > 0) {
00293 db_append_string(&stmt, " WHERE ");
00294 db_append_string(&stmt, where);
00295 }
00296
00297 G_debug(3, " SQL: %s", db_get_string(&stmt));
00298
00299 if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
00300 return (-1);
00301
00302 nrows = db_get_num_rows(&cursor);
00303 G_debug(3, " %d rows selected", nrows);
00304 if (nrows < 0)
00305 G_fatal_error(_("Unable select records from table <%s>"), tab);
00306
00307 db_CatValArray_alloc(cvarr, nrows);
00308
00309 table = db_get_cursor_table(&cursor);
00310
00311
00312 column = db_get_table_column(table, 0);
00313 type = db_sqltype_to_Ctype(db_get_column_sqltype(column));
00314 G_debug(3, " key type = %d", type);
00315
00316 if (type != DB_C_TYPE_INT) {
00317 G_fatal_error("Key column type is not integer");
00318 }
00319
00320 column = db_get_table_column(table, 1);
00321 type = db_sqltype_to_Ctype(db_get_column_sqltype(column));
00322 G_debug(3, " col type = %d", type);
00323
00324
00325
00326
00327
00328
00329
00330 cvarr->ctype = type;
00331
00332
00333 for (i = 0; i < nrows; i++) {
00334 if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK)
00335 return (-1);
00336
00337 column = db_get_table_column(table, 0);
00338 value = db_get_column_value(column);
00339 cvarr->value[i].cat = db_get_value_int(value);
00340
00341 column = db_get_table_column(table, 1);
00342 value = db_get_column_value(column);
00343 cvarr->value[i].isNull = value->isNull;
00344 switch (type) {
00345 case (DB_C_TYPE_INT):
00346 if (value->isNull)
00347 cvarr->value[i].val.i = 0;
00348 else
00349 cvarr->value[i].val.i = db_get_value_int(value);
00350 break;
00351
00352 case (DB_C_TYPE_DOUBLE):
00353 if (value->isNull)
00354 cvarr->value[i].val.d = 0.0;
00355 else
00356 cvarr->value[i].val.d = db_get_value_double(value);
00357 break;
00358
00359 case (DB_C_TYPE_STRING):
00360 cvarr->value[i].val.s = (dbString *) malloc(sizeof(dbString));
00361 db_init_string(cvarr->value[i].val.s);
00362
00363 if (!(value->isNull))
00364 db_set_string(cvarr->value[i].val.s,
00365 db_get_value_string(value));
00366 break;
00367
00368 case (DB_C_TYPE_DATETIME):
00369 cvarr->value[i].val.t =
00370 (dbDateTime *) calloc(1, sizeof(dbDateTime));
00371
00372 if (!(value->isNull))
00373 memcpy(cvarr->value[i].val.t, &(value->t),
00374 sizeof(dbDateTime));
00375 break;
00376
00377 default:
00378 return (-1);
00379 }
00380 }
00381 cvarr->n_values = nrows;
00382
00383 db_close_cursor(&cursor);
00384 db_free_string(&stmt);
00385
00386 db_CatValArray_sort(cvarr);
00387
00388 return (nrows);
00389 }
00390
00395 void db_CatValArray_sort(dbCatValArray * arr)
00396 {
00397 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal), cmpcat);
00398 }
00399
00408 int db_CatValArray_sort_by_value(dbCatValArray * arr)
00409 {
00410 switch (arr->ctype) {
00411 case (DB_C_TYPE_INT):
00412 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
00413 cmpvalueint);
00414 break;
00415 case (DB_C_TYPE_DOUBLE):
00416 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
00417 cmpvaluedouble);
00418 break;
00419 case (DB_C_TYPE_STRING):
00420 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
00421 cmpvaluestring);
00422 break;
00423 case (DB_C_TYPE_DATETIME):
00424 qsort((void *)arr->value, arr->n_values, sizeof(dbCatVal),
00425 cmpvaluestring);
00426 break;
00427 default:
00428 return (DB_FAILED);
00429 }
00430
00431 return (DB_OK);
00432 }
00433
00444 int db_CatValArray_get_value(dbCatValArray * arr, int key, dbCatVal ** cv)
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 *cv = catval;
00456
00457 return DB_OK;
00458 }
00459
00470 int db_CatValArray_get_value_int(dbCatValArray * arr, int key, int *val)
00471 {
00472 dbCatVal *catval;
00473
00474 catval =
00475 bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
00476 cmpcat);
00477 if (catval == NULL) {
00478 return DB_FAILED;
00479 }
00480
00481 *val = catval->val.i;
00482
00483 return DB_OK;
00484 }
00485
00496 int db_CatValArray_get_value_double(dbCatValArray * arr, int key, double *val)
00497 {
00498 dbCatVal *catval;
00499
00500 G_debug(3, "db_CatValArray_get_value_double(), key = %d", key);
00501
00502 catval =
00503 bsearch((void *)&key, arr->value, arr->n_values, sizeof(dbCatVal),
00504 cmpcatkey);
00505 if (catval == NULL) {
00506 return DB_FAILED;
00507 }
00508
00509 *val = catval->val.d;
00510
00511 return DB_OK;
00512 }