select.c

Go to the documentation of this file.
00001 #include <stdlib.h>
00002 #include <string.h>
00003 #include <grass/gis.h>
00004 #include <grass/dbmi.h>
00005 
00006 static int cmp ( const void *pa, const void *pb)
00007 {
00008     int *p1 = (int *) pa;    
00009     int *p2 = (int *) pb;
00010 
00011     if( *p1 < *p2 ) return -1;
00012     if( *p1 > *p2 ) return 1;
00013     return 0;
00014 }
00015 
00016 static int cmpcat ( const void *pa, const void *pb)
00017 {
00018     dbCatVal *p1 = (dbCatVal *) pa;    
00019     dbCatVal *p2 = (dbCatVal *) pb;
00020 
00021     if( p1->cat < p2->cat ) return -1;
00022     if( p1->cat > p2->cat ) return 1;
00023     return 0;
00024 }
00025 
00026 static int cmpcatkey ( const void *pa, const void *pb)
00027 {
00028     int *p1 = (int *) pa;    
00029     dbCatVal *p2 = (dbCatVal *) pb;
00030 
00031     if( *p1 < p2->cat ) return -1;
00032     if( *p1 > p2->cat ) return 1;
00033     return 0;
00034 }
00035 
00042 /* selet array of ordered integers
00043  *
00044  * return: number of selected values
00045  *         -1 on error
00046  */
00047 
00048 int db_select_int (dbDriver *driver, char *tab, char *col, char *where, int **pval)
00049 {
00050     int type, more, alloc, count;
00051     int *val;
00052     char buf[1024], *sval;
00053     dbString stmt;
00054     dbCursor cursor;
00055     dbColumn *column;
00056     dbValue *value;
00057     dbTable *table;
00058     
00059     G_debug (3, "db_select_int()" );
00060     
00061     /* allocate */
00062     alloc = 1000;
00063     val = (int *) G_malloc ( alloc * sizeof(int));
00064 
00065     if ( where == NULL || strlen(where) == 0 )
00066         G_snprintf(buf,1023, "SELECT %s FROM %s", col, tab);
00067     else
00068         G_snprintf(buf,1023, "SELECT %s FROM %s WHERE %s", col, tab, where);
00069 
00070     G_debug (3, "  SQL: %s", buf );
00071     
00072     db_init_string ( &stmt);
00073     db_append_string ( &stmt, buf);
00074 
00075     if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
00076             return (-1);
00077 
00078     table = db_get_cursor_table (&cursor);
00079     column = db_get_table_column(table, 0); /* first column */
00080     value  = db_get_column_value(column);
00081     type = db_get_column_sqltype(column);
00082     type = db_sqltype_to_Ctype(type);
00083 
00084     /* fetch the data */
00085     count = 0;
00086     while(1)
00087       {
00088         if(db_fetch (&cursor, DB_NEXT, &more) != DB_OK)
00089             return (-1);
00090 
00091         if (!more) break;  
00092                                                 
00093         if ( count == alloc )
00094           {
00095             alloc += 1000;                
00096             val = (int *) G_realloc ( val, alloc * sizeof(int));
00097           }
00098         
00099         switch ( type )
00100           {
00101             case ( DB_C_TYPE_INT ):
00102                 val[count] = db_get_value_int(value);
00103                 break;
00104             case ( DB_C_TYPE_STRING ):
00105                 sval = db_get_value_string(value);
00106                 val[count] = atoi(sval);
00107                 break;
00108             case ( DB_C_TYPE_DOUBLE ):
00109                 val[count] = (int) db_get_value_double(value);
00110                 break;
00111             default:
00112                 return (-1);
00113           }
00114         count++;
00115     }
00116 
00117     db_close_cursor(&cursor);
00118     db_free_string ( &stmt );
00119 
00120     qsort( (void *)val, count, sizeof(int), cmp);
00121 
00122     *pval = val; 
00123 
00124     return (count);
00125 }
00126 
00133 /* selet one (first) value from table/column for key/id
00134  *
00135  * return: number of selected values
00136  *         -1 on error
00137  */
00138 int db_select_value (dbDriver *driver, 
00139         char *tab, /* table name */
00140         char *key, /* key column name (integer) */
00141         int  id,   /* identifier in key column */
00142         char *col, /* the name of column to select the value from */
00143         dbValue *val) /* pointer to existing dbValue to store within */ 
00144 {
00145     int  more, count;
00146     char buf[1024];
00147     dbString stmt;
00148     dbCursor cursor;
00149     dbColumn *column;
00150     dbValue *value;
00151     dbTable *table;
00152     
00153     sprintf( buf, "SELECT %s FROM %s WHERE %s = %d\n", col, tab, key, id);
00154     db_init_string ( &stmt);
00155     db_append_string ( &stmt, buf);
00156 
00157     if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
00158         return (-1);
00159 
00160     table = db_get_cursor_table (&cursor);
00161     column = db_get_table_column(table, 0); /* first column */
00162     value  = db_get_column_value(column);
00163 
00164     /* fetch the data */
00165     count = 0;
00166     while(1) {
00167         if(db_fetch (&cursor, DB_NEXT, &more) != DB_OK)
00168             return (-1);
00169 
00170         if (!more) break;  
00171         if ( count == 0 ) db_copy_value ( val, value );
00172         count++;
00173     }
00174     db_close_cursor(&cursor);
00175     db_free_string ( &stmt );
00176 
00177     return (count);
00178 }
00179 
00186 /* selet pairs key/value to array, values are sorted by key
00187  *
00188  * return: number of selected values
00189  *         -1 on error
00190  */
00191 
00192 int db_select_CatValArray ( dbDriver *driver, char *tab, char *key, char *col, char *where, 
00193                          dbCatValArray *cvarr )
00194 {
00195     int  i, type, more, nrows;
00196     char buf[1024];
00197     dbString stmt;
00198     dbCursor cursor;
00199     dbColumn *column;
00200     dbValue *value;
00201     dbTable *table;
00202     
00203     G_debug (3, "db_select_db_select_CatValArray ()" );
00204     
00205     db_init_string ( &stmt);
00206     
00207     sprintf( buf, "SELECT %s, %s FROM %s", key, col, tab);
00208     db_set_string ( &stmt, buf);
00209 
00210     if ( where != NULL && strlen(where) > 0 ) {
00211         db_append_string ( &stmt, " WHERE ");
00212         db_append_string ( &stmt, where );
00213     }
00214 
00215     G_debug (3, "  SQL: %s", db_get_string ( &stmt ) );
00216     
00217     if (db_open_select_cursor(driver, &stmt, &cursor, DB_SEQUENTIAL) != DB_OK)
00218             return (-1);
00219 
00220     nrows = db_get_num_rows ( &cursor );
00221     G_debug (3, "  %d rows selected", nrows );
00222     if ( nrows < 0 ) G_fatal_error ( "Cannot select rows from database");
00223         
00224     db_CatValArray_alloc( cvarr, nrows );
00225 
00226     table = db_get_cursor_table (&cursor);
00227 
00228     /* Check if key column is integer */
00229     column = db_get_table_column(table, 0); 
00230     type = db_sqltype_to_Ctype( db_get_column_sqltype(column) );
00231     G_debug (3, "  key type = %d", type );
00232 
00233     if ( type != DB_C_TYPE_INT ) {
00234         G_fatal_error ( "Key column type is not integer" );
00235     }   
00236 
00237     column = db_get_table_column(table, 1); 
00238     type = db_sqltype_to_Ctype( db_get_column_sqltype(column) );
00239     G_debug (3, "  col type = %d", type );
00240 
00241     /*
00242     if ( type != DB_C_TYPE_INT && type != DB_C_TYPE_DOUBLE ) {
00243         G_fatal_error ( "Column type not supported by db_select_to_array()" );
00244     }
00245     */  
00246 
00247     cvarr->ctype = type;
00248 
00249     /* fetch the data */
00250     for ( i = 0; i < nrows; i++ ) {
00251         if(db_fetch (&cursor, DB_NEXT, &more) != DB_OK)
00252             return (-1);
00253 
00254         column = db_get_table_column(table, 0); /* first column */
00255         value  = db_get_column_value(column);
00256         cvarr->value[i].cat = db_get_value_int(value);
00257 
00258         column = db_get_table_column(table, 1);
00259         value  = db_get_column_value(column);
00260         cvarr->value[i].isNull = value->isNull;
00261         switch ( type ) {
00262             case ( DB_C_TYPE_INT ):
00263                 if ( value->isNull )
00264                     cvarr->value[i].val.i = 0;
00265                 else
00266                     cvarr->value[i].val.i = db_get_value_int(value);
00267                 break;
00268 
00269             case ( DB_C_TYPE_DOUBLE ):
00270                 if ( value->isNull )
00271                     cvarr->value[i].val.d = 0.0;
00272                 else
00273                     cvarr->value[i].val.d = db_get_value_double(value);
00274                 break;
00275 
00276             case ( DB_C_TYPE_STRING ):
00277                 cvarr->value[i].val.s = (dbString *)malloc(sizeof(dbString));
00278                 db_init_string ( cvarr->value[i].val.s );
00279                 
00280                 if ( !(value->isNull) )
00281                     db_set_string ( cvarr->value[i].val.s, db_get_value_string(value) );
00282                 break;
00283 
00284             case ( DB_C_TYPE_DATETIME ):
00285                 cvarr->value[i].val.t = (dbDateTime *) calloc(1, sizeof(dbDateTime));
00286                 
00287                 if ( !(value->isNull) )
00288                     memcpy ( cvarr->value[i].val.t, &(value->t), sizeof(dbDateTime) );
00289                 break;
00290 
00291             default:
00292                 return (-1);
00293         }
00294     }
00295     cvarr->n_values = nrows;
00296 
00297     db_close_cursor(&cursor);
00298     db_free_string ( &stmt );
00299 
00300     db_CatValArray_sort ( cvarr );
00301 
00302     return (nrows);
00303 }
00304 
00305 /* Sort dbCatValArray by category */
00306 void
00307 db_CatValArray_sort ( dbCatValArray *arr )
00308 {
00309     qsort( (void *) arr->value, arr->n_values, sizeof(dbCatVal), cmpcat);
00310 } 
00311 
00312 /* find value by key
00313 *  returns: DB_FAILED, DB_OK
00314 */
00315 int
00316 db_CatValArray_get_value ( dbCatValArray *arr, int key, dbCatVal **cv )
00317 {
00318     dbCatVal *catval;
00319     
00320     catval = bsearch ( (void *) &key, arr->value, arr->n_values, sizeof ( dbCatVal ), cmpcat );
00321     if ( catval == NULL ) { return DB_FAILED; }
00322 
00323     *cv = catval;
00324     
00325     return DB_OK;
00326 }
00327 
00328 /* find value by key
00329 *  returns: DB_FAILED, DB_OK
00330 */
00331 int
00332 db_CatValArray_get_value_int ( dbCatValArray *arr, int key, int *val )
00333 {
00334     dbCatVal *catval;
00335     
00336     catval = bsearch ( (void *) &key, arr->value, arr->n_values, sizeof ( dbCatVal ), cmpcat );
00337     if ( catval == NULL ) { return DB_FAILED; }
00338 
00339     *val = catval->val.i;
00340     
00341     return DB_OK;
00342 }
00343 
00344 /* find value by key
00345 *  returns: 0 not found, 1 OK
00346 */
00347 int
00348 db_CatValArray_get_value_double ( dbCatValArray *arr, int key, double *val )
00349 {
00350     dbCatVal *catval;
00351 
00352     G_debug (3, "db_CatValArray_get_value_double(), key = %d", key );
00353     
00354     catval = bsearch ( (void *) &key, arr->value, arr->n_values, sizeof ( dbCatVal ), cmpcatkey );
00355     if ( catval == NULL ) { return DB_FAILED; }
00356 
00357     *val = catval->val.d;
00358     
00359     return DB_OK;
00360 }
00361 

Generated on Sun Apr 6 17:31:38 2008 for GRASS by  doxygen 1.5.5