select.c

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

Generated on Wed Aug 23 17:48:32 2006 for GRASS by  doxygen 1.4.7