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     /* allocate */
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);     /* first column */
00137     value = db_get_column_value(column);
00138     type = db_get_column_sqltype(column);
00139     type = db_sqltype_to_Ctype(type);
00140 
00141     /* fetch the data */
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);     /* first column */
00215     value = db_get_column_value(column);
00216 
00217     /* fetch the data */
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     /* Check if key column is integer */
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        if ( type != DB_C_TYPE_INT && type != DB_C_TYPE_DOUBLE ) {
00300        G_fatal_error ( "Column type not supported by db_select_to_array()" );
00301        }
00302      */
00303 
00304     cvarr->ctype = type;
00305 
00306     /* fetch the data */
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); /* first column */
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):  /* is cmpvaluestring right here ? */
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 }

Generated on Thu Jul 16 13:20:15 2009 for GRASS Programmer's Manual by  doxygen 1.5.6