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
00043
00044
00045
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
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);
00080 value = db_get_column_value(column);
00081 type = db_get_column_sqltype(column);
00082 type = db_sqltype_to_Ctype(type);
00083
00084
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
00134
00135
00136
00137
00138 int db_select_value (dbDriver *driver,
00139 char *tab,
00140 char *key,
00141 int id,
00142 char *col,
00143 dbValue *val)
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);
00162 value = db_get_column_value(column);
00163
00164
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
00187
00188
00189
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
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
00243
00244
00245
00246
00247 cvarr->ctype = type;
00248
00249
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);
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
00306 void
00307 db_CatValArray_sort ( dbCatValArray *arr )
00308 {
00309 qsort( (void *) arr->value, arr->n_values, sizeof(dbCatVal), cmpcat);
00310 }
00311
00312
00313
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
00329
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
00345
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