select.c

Go to the documentation of this file.
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 }