1 /*!
2 \file lib/db/dbmi_base/table.c
3
4 \brief DBMI Library (base) - table management
5
6 (C) 1999-2009, 2011 by the GRASS Development Team
7
8 This program is free software under the GNU General Public License
9 (>=v2). Read the file COPYING that comes with GRASS for details.
10
11 \author Joel Jones (CERL/UIUC), Radim Blazek
12 \author Doxygenized by Martin Landa <landa.martin gmail.com> (2011)
13 */
14
15 #include <stdlib.h>
16 #include <string.h>
17 #include <grass/gis.h>
18 #include <grass/dbmi.h>
19
20 /*!
21 \brief Allocate a table with a specific number of columns
22
23 \param ncols number of columns which should be allocated
24
25 \return allocated dbTable
26 \return NULL in case of an error
27 */
db_alloc_table(int ncols)28 dbTable *db_alloc_table(int ncols)
29 {
30 dbTable *table;
31 int i;
32
33 table = (dbTable *) db_malloc(sizeof(dbTable));
34 if (table == NULL)
35 return (table = NULL);
36
37 db_init_table(table);
38
39 table->columns = (dbColumn *) db_calloc(sizeof(dbColumn), ncols);
40 if (table->columns == NULL) {
41 db_free(table);
42 return (table = NULL);
43 }
44 table->numColumns = ncols;
45 for (i = 0; i < ncols; i++)
46 db_init_column(&table->columns[i]);
47
48 return table;
49 }
50
51 /*!
52 \brief Initialize the table to zero
53
54 \param table pointer to dbTable
55 */
db_init_table(dbTable * table)56 void db_init_table(dbTable * table)
57 {
58 db_zero((void *)table, sizeof(dbTable));
59 db_init_string(&table->tableName);
60 db_init_string(&table->description);
61 }
62
63 /*!
64 \brief Free the table
65
66 \param table pointer to dbTable
67 */
db_free_table(dbTable * table)68 void db_free_table(dbTable * table)
69 {
70 int i;
71
72 db_free_string(&table->tableName);
73 db_free_string(&table->description);
74 for (i = 0; i < table->numColumns; i++)
75 db_free_column(&table->columns[i]);
76 if (table->columns)
77 db_free(table->columns);
78 db_free(table);
79 }
80
81 /*!
82 \brief Set the name of the table
83
84 \param table pointer to dbTable
85 \param name The name of the table
86
87 \return DB_OK on success
88 */
db_set_table_name(dbTable * table,const char * name)89 int db_set_table_name(dbTable * table, const char *name)
90 {
91 return db_set_string(&table->tableName, name);
92 }
93
94 /*!
95 \brief Get the name of the table
96
97 \param table pointer to dbTable
98
99 \return name of the table
100 */
db_get_table_name(dbTable * table)101 const char *db_get_table_name(dbTable * table)
102 {
103 return db_get_string(&table->tableName);
104 }
105
106 /*!
107 \brief Set the description of the table
108
109 \param table pointer to dbTable
110 \param name description of the table
111
112 \return DB_OK
113 */
db_set_table_description(dbTable * table,const char * description)114 int db_set_table_description(dbTable * table, const char *description)
115 {
116 return db_set_string(&table->description, description);
117 }
118
119 /*!
120 \brief Get the description of the table
121
122 \param table pointer to dbTable
123
124 \return description of the table
125 */
db_get_table_description(dbTable * table)126 const char *db_get_table_description(dbTable * table)
127 {
128 return db_get_string(&table->description);
129 }
130
131 /*!
132 \brief Return the number of columns of the table
133
134 \param table pointer to dbTable
135
136 \return number of columns
137 */
db_get_table_number_of_columns(dbTable * table)138 int db_get_table_number_of_columns(dbTable * table)
139 {
140 return table->numColumns;
141 }
142
set_all_column_privs(dbTable * table,void (* set_column_priv)())143 static void set_all_column_privs(dbTable * table, void (*set_column_priv) ())
144 {
145 int col, ncols;
146 dbColumn *column;
147
148 ncols = db_get_table_number_of_columns(table);
149 for (col = 0; col < ncols; col++) {
150 column = db_get_table_column(table, col);
151 set_column_priv(column);
152 }
153 }
154
get_all_column_privs(dbTable * table,int (* get_column_priv)())155 static int get_all_column_privs(dbTable * table, int (*get_column_priv) ())
156 {
157 int priv, col, ncols;
158 dbColumn *column;
159
160 ncols = db_get_table_number_of_columns(table);
161 for (col = 0; col < ncols; col++) {
162 column = db_get_table_column(table, col);
163 priv = get_column_priv(column);
164 if (priv != DB_GRANTED)
165 return priv;
166 }
167 return DB_GRANTED;
168 }
169
170 /*!
171 \brief Grant selection privileges for all columns
172
173 \param table pointer to dbTable
174 */
db_set_table_select_priv_granted(dbTable * table)175 void db_set_table_select_priv_granted(dbTable * table)
176 {
177 set_all_column_privs(table, db_set_column_select_priv_granted);
178 }
179
180 /*!
181 \brief Set selection privileges not granted for all columns
182
183 \param table pointer to dbTable
184 */
db_set_table_select_priv_not_granted(dbTable * table)185 void db_set_table_select_priv_not_granted(dbTable * table)
186 {
187 set_all_column_privs(table, db_set_column_select_priv_not_granted);
188 }
189
190 /*!
191 \brief Get table select privileges
192
193 \param table pointer to dbTable
194
195 \return privilages
196 */
db_get_table_select_priv(dbTable * table)197 int db_get_table_select_priv(dbTable * table)
198 {
199 return get_all_column_privs(table, db_get_column_select_priv);
200 }
201
202 /*!
203 \brief Grant update privileges for all columns
204
205 \param table pointer to dbTable
206 */
db_set_table_update_priv_granted(dbTable * table)207 void db_set_table_update_priv_granted(dbTable * table)
208 {
209 set_all_column_privs(table, db_set_column_update_priv_granted);
210 }
211
212 /*!
213 \brief Set update privileges not granted for all columns
214
215 \param table pointer to dbTable
216 */
db_set_table_update_priv_not_granted(dbTable * table)217 void db_set_table_update_priv_not_granted(dbTable * table)
218 {
219 set_all_column_privs(table, db_set_column_update_priv_not_granted);
220 }
221
222 /*!
223 \brief Get table update privileges
224
225 \param table pointer to dbTable
226
227 \return privilages
228 */
db_get_table_update_priv(dbTable * table)229 int db_get_table_update_priv(dbTable * table)
230 {
231 return get_all_column_privs(table, db_get_column_update_priv);
232 }
233
234 /*!
235 \brief Grant insert privileges for table
236
237 \param table pointer to dbTable
238 */
db_set_table_insert_priv_granted(dbTable * table)239 void db_set_table_insert_priv_granted(dbTable * table)
240 {
241 table->priv_insert = DB_GRANTED;
242 }
243
244 /*!
245 \brief Set insert privileges not granted for table
246
247 \param table pointer to dbTable
248 */
db_set_table_insert_priv_not_granted(dbTable * table)249 void db_set_table_insert_priv_not_granted(dbTable * table)
250 {
251 table->priv_insert = DB_NOT_GRANTED;
252 }
253
254 /*!
255 \brief Get table insert privileges
256
257 \param table pointer to dbTable
258
259 \return prilileges
260 */
db_get_table_insert_priv(dbTable * table)261 int db_get_table_insert_priv(dbTable * table)
262 {
263 return table->priv_insert;
264 }
265
266 /*!
267 \brief Grant delete privileges for table
268
269 \param table pointer to dbTable
270 */
db_set_table_delete_priv_granted(dbTable * table)271 void db_set_table_delete_priv_granted(dbTable * table)
272 {
273 table->priv_delete = DB_GRANTED;
274 }
275
276 /*!
277 \brief Set delete privileges not granted for table
278
279 \param table pointer to dbTable
280 */
db_set_table_delete_priv_not_granted(dbTable * table)281 void db_set_table_delete_priv_not_granted(dbTable * table)
282 {
283 table->priv_delete = DB_NOT_GRANTED;
284 }
285
286 /*!
287 \brief Get table delete privileges
288
289 \param table pointer to dbTable
290
291 \return privileges
292 */
db_get_table_delete_priv(dbTable * table)293 int db_get_table_delete_priv(dbTable * table)
294 {
295 return table->priv_delete;
296 }
297
298 /*!
299 \brief Returns column structure for given table and column number
300
301 \param table pointer to dbTable
302 \param idx column index (starting with '0')
303
304 \return pointer to dbColumn
305 \return NULL if not found
306 */
db_get_table_column(dbTable * table,int idx)307 dbColumn *db_get_table_column(dbTable * table, int idx)
308 {
309 if (idx < 0 || idx >= table->numColumns)
310 return ((dbColumn *) NULL);
311 return &table->columns[idx];
312 }
313
314 /*!
315 \brief Returns column structure for given table and column name
316
317 \param table pointer to dbTable
318 \param name the name of the column
319
320 \return pointer to dbColumn
321 \return NULL if not found
322 */
db_get_table_column_by_name(dbTable * table,const char * name)323 dbColumn *db_get_table_column_by_name(dbTable * table, const char* name)
324 {
325 dbColumn *c = NULL;
326 int i, columns = table->numColumns;
327
328 for(i = 0; i < columns; i++ ) {
329 c = db_get_table_column(table, i);
330
331 if(c == NULL)
332 return c;
333
334 if(strcmp(name, db_get_string(&c->columnName)) == 0)
335 break;
336
337 c = NULL;
338 }
339
340 return c;
341 }
342
343 /*!
344 \brief Set a specific column for given table and column number
345
346 \param table Pointer to dbTable
347 \param idx Column index (starting with '0'). The index must be in range.
348 \param column Pointer to a dbColumn to insert.
349 A copy of the column stored, so the original column can be deleted.
350
351 \return DB_OK on success
352 \return DB_FAILURE on error
353 */
db_set_table_column(dbTable * table,int idx,dbColumn * column)354 int db_set_table_column(dbTable * table, int idx, dbColumn *column)
355 {
356 if (idx < 0 || idx >= table->numColumns)
357 return DB_FAILED;
358 db_copy_column(&table->columns[idx], column);
359 return DB_OK;
360 }
361
362 /*!
363 \brief Append a specific column to given table
364
365 \param table Pointer to dbTable
366 \param column Pointer to a dbColumn to append.
367 A copy of the column is stored, so the original column can be deleted.
368
369 \return DB_OK on success
370 \return DB_FAILURE on error
371 */
db_append_table_column(dbTable * table,dbColumn * column)372 int db_append_table_column(dbTable * table, dbColumn *column)
373 {
374 table->columns = (dbColumn*)db_realloc((void*)table->columns, sizeof(dbColumn)*(table->numColumns + 1));
375 if(table->columns == NULL)
376 return DB_FAILED;
377 db_copy_column(&table->columns[table->numColumns], column);
378 table->numColumns++;
379 return DB_OK;
380 }
381
382 /*!
383 \brief Make a new exact copy of an existing table
384
385 New memory is allocated for the clone, the columns-content will be copied too.
386
387 \param src Pointer to dbTable
388
389 \return A new alloacted clone of the given table on success
390 \return NULL on error
391 */
db_clone_table(dbTable * src)392 dbTable *db_clone_table(dbTable *src)
393 {
394 int i, n = db_get_table_number_of_columns(src);
395 dbTable *new = db_alloc_table(n);
396 if(new == NULL)
397 return (new = NULL);
398
399 db_copy_string(&new->description, &src->description);
400 db_copy_string(&new->tableName, &src->tableName);
401
402 /* Deep copy the columns */
403 for(i = 0; i < n; i++)
404 {
405 db_copy_column(&new->columns[i], &src->columns[i]);
406 }
407
408 new->numColumns = n;
409 new->priv_delete = src->priv_delete;
410 new->priv_insert = src->priv_insert;
411
412 return new;
413 }
414
415 /*!
416 \brief Create SQL CREATE sring from table definition
417
418 \param table pointer to dbTable
419 \param sql dbString to store the SQL CREATE string
420
421 \return DB_OK on success
422 \return DB_FAILED on error
423 */
db_table_to_sql(dbTable * table,dbString * sql)424 int db_table_to_sql(dbTable * table, dbString * sql)
425 {
426 int col, ncols;
427 dbColumn *column;
428 const char *colname;
429 int sqltype, ctype;
430 char buf[500];
431
432 db_set_string(sql, "create table ");
433 db_append_string(sql, db_get_table_name(table));
434 db_append_string(sql, " ( ");
435
436 ncols = db_get_table_number_of_columns(table);
437
438 for (col = 0; col < ncols; col++) {
439 column = db_get_table_column(table, col);
440 colname = db_get_column_name(column);
441 sqltype = db_get_column_sqltype(column);
442
443 ctype = db_sqltype_to_Ctype(sqltype);
444 G_debug(3, "%s (%s)", colname, db_sqltype_name(sqltype));
445
446 if (col > 0)
447 db_append_string(sql, ", ");
448 db_append_string(sql, colname);
449 db_append_string(sql, " ");
450 /* Note: I found on Web:
451 * These are the ANSI data types: BIT, CHARACTER, DATE, DECIMAL, DOUBLE PRECISION, FLOAT,
452 * INTEGER, INTERVAL, NUMERIC, REAL, SMALLINT, TIMESTAMP, TIME, VARBIT, VARCHAR, CHAR
453 * ...
454 * Thus, the only data types you can use with the assurance that they will
455 * work everywhere are as follows:
456 * DOUBLE PRECISION, FLOAT, INTEGER, NUMERIC, REAL, SMALLINT, VARCHAR, CHAR */
457 switch (sqltype) {
458 case DB_SQL_TYPE_CHARACTER:
459 sprintf(buf, "varchar(%d)", db_get_column_length(column));
460 db_append_string(sql, buf);
461 break;
462 case DB_SQL_TYPE_TEXT:
463 G_warning("Type TEXT converted to 'VARCHAR(250)'");
464 db_append_string(sql, "varchar(250)");
465 break;
466 case DB_SQL_TYPE_SMALLINT:
467 case DB_SQL_TYPE_INTEGER:
468 db_append_string(sql, "integer");
469 break;
470 case DB_SQL_TYPE_REAL:
471 case DB_SQL_TYPE_DOUBLE_PRECISION:
472 case DB_SQL_TYPE_DECIMAL:
473 case DB_SQL_TYPE_NUMERIC:
474 case DB_SQL_TYPE_INTERVAL:
475 db_append_string(sql, "double precision");
476 break;
477 case DB_SQL_TYPE_DATE:
478 db_append_string(sql, "date");
479 break;
480 case DB_SQL_TYPE_TIME:
481 db_append_string(sql, "time");
482 break;
483 case DB_SQL_TYPE_TIMESTAMP:
484 db_append_string(sql, "datetime");
485 break;
486 default:
487 G_warning("Unknown column type (%s)", colname);
488 return DB_FAILED;
489 }
490 }
491 db_append_string(sql, " )");
492 G_debug(3, "sql statement: %s", db_get_string(sql));
493
494 return DB_OK;
495 }
496