GRASS Programmer's Manual
6.4.2(2012)
|
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 /* allocate */ 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); /* first column */ 00142 value = db_get_column_value(column); 00143 type = db_get_column_sqltype(column); 00144 type = db_sqltype_to_Ctype(type); 00145 00146 /* fetch the data */ 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); /* first column */ 00231 value = db_get_column_value(column); 00232 00233 /* fetch the data */ 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 /* Check if key column is integer */ 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 if ( type != DB_C_TYPE_INT && type != DB_C_TYPE_DOUBLE ) { 00326 G_fatal_error ( "Column type not supported by db_select_to_array()" ); 00327 } 00328 */ 00329 00330 cvarr->ctype = type; 00331 00332 /* fetch the data */ 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); /* first column */ 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): /* is cmpvaluestring right here ? */ 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 }