GRASS GIS 7 Programmer's Manual  7.9.dev(2021)-e5379bbd7
dbmi_base/table.c
Go to the documentation of this file.
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  */
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 */
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 */
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 */
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 */
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  */
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 */
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 */
139 {
140  return table->numColumns;
141 }
142 
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 
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 */
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 */
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 privileges
196 */
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 */
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 */
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 privileges
228 */
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 */
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  */
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 */
262 {
263  return table->priv_insert;
264 }
265 
266 /*!
267  \brief Grant delete privileges for table
268 
269  \param table pointer to dbTable
270  */
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 */
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 */
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 */
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 */
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 */
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 */
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 */
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 */
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) {
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;
467  case DB_SQL_TYPE_INTEGER:
468  db_append_string(sql, "integer");
469  break;
470  case DB_SQL_TYPE_REAL:
472  case DB_SQL_TYPE_DECIMAL:
473  case DB_SQL_TYPE_NUMERIC:
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;
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 }
void * db_realloc(void *, int)
Reallocate memory.
int db_table_to_sql(dbTable *table, dbString *sql)
Create SQL CREATE sring from table definition.
#define DB_SQL_TYPE_INTEGER
Definition: dbmi.h:83
#define DB_SQL_TYPE_INTERVAL
Definition: dbmi.h:91
#define DB_SQL_TYPE_TIMESTAMP
Definition: dbmi.h:90
dbTable * db_alloc_table(int ncols)
Allocate a table with a specific number of columns.
int db_get_column_length(dbColumn *)
Get column&#39;s length.
const char * db_get_column_name(dbColumn *)
Returns column name for given column.
int db_set_table_name(dbTable *table, const char *name)
Set the name of the table.
void db_init_string(dbString *)
Initialize dbString.
Definition: string.c:25
int db_get_table_select_priv(dbTable *table)
Get table select privileges.
#define DB_GRANTED
Definition: dbmi.h:128
void db_set_table_update_priv_not_granted(dbTable *table)
Set update privileges not granted for all columns.
void db_zero(void *, int)
Zero allocated space.
void db_set_table_update_priv_granted(dbTable *table)
Grant update privileges for all columns.
dbColumn * db_get_table_column_by_name(dbTable *table, const char *name)
Returns column structure for given table and column name.
dbString columnName
Definition: dbmi.h:204
int db_get_column_select_priv(dbColumn *)
Get select privileges.
char * db_get_string(const dbString *)
Get string.
Definition: string.c:140
dbColumn * columns
Definition: dbmi.h:225
int db_copy_string(dbString *, const dbString *)
Copy dbString.
Definition: string.c:230
#define DB_SQL_TYPE_REAL
Definition: dbmi.h:84
int priv_delete
Definition: dbmi.h:227
void db_free(void *)
Free allocated memory.
#define DB_SQL_TYPE_CHARACTER
Definition: dbmi.h:81
dbString description
Definition: dbmi.h:223
dbString tableName
Definition: dbmi.h:222
#define NULL
Definition: ccmath.h:32
int db_set_string(dbString *, const char *)
Inserts string to dbString (enlarge string)
Definition: string.c:41
dbTable * db_clone_table(dbTable *src)
Make a new exact copy of an existing table.
int db_get_column_update_priv(dbColumn *)
Get update privileges.
int columns
Definition: calc.c:12
int numColumns
Definition: dbmi.h:224
#define DB_SQL_TYPE_DATE
Definition: dbmi.h:88
int db_get_table_update_priv(dbTable *table)
Get table update privileges.
void db_set_table_insert_priv_granted(dbTable *table)
Grant insert privileges for table.
int db_get_column_sqltype(dbColumn *)
Returns column sqltype for column.
const char * db_sqltype_name(int)
Get SQL data type description.
Definition: sqltype.c:25
int db_append_string(dbString *, const char *)
Append string to dbString.
Definition: string.c:205
int db_append_table_column(dbTable *table, dbColumn *column)
Append a specific column to given table.
const char * db_get_table_description(dbTable *table)
Get the description of the table.
int db_get_table_number_of_columns(dbTable *table)
Return the number of columns of the table.
void db_init_column(dbColumn *)
Initialize dbColumn.
#define DB_SQL_TYPE_TEXT
Definition: dbmi.h:92
int db_get_table_delete_priv(dbTable *table)
Get table delete privileges.
int db_sqltype_to_Ctype(int)
Get C data type based on given SQL data type.
Definition: sqlCtype.c:24
int db_set_table_column(dbTable *table, int idx, dbColumn *column)
Set a specific column for given table and column number.
#define DB_SQL_TYPE_NUMERIC
Definition: dbmi.h:87
void * db_calloc(int, int)
Allocate memory.
void db_set_column_select_priv_granted(dbColumn *)
Set select privileges to be granted.
#define DB_SQL_TYPE_DOUBLE_PRECISION
Definition: dbmi.h:85
void db_set_table_insert_priv_not_granted(dbTable *table)
Set insert privileges not granted for table.
int db_get_table_insert_priv(dbTable *table)
Get table insert privileges.
void db_init_table(dbTable *table)
Initialize the table to zero.
void db_set_table_select_priv_granted(dbTable *table)
Grant selection privileges for all columns.
void db_free_table(dbTable *table)
Free the table.
#define DB_FAILED
Definition: dbmi.h:72
void db_set_column_update_priv_granted(dbColumn *)
Set update privileges to be granted.
void db_set_table_delete_priv_granted(dbTable *table)
Grant delete privileges for table.
void G_warning(const char *,...) __attribute__((format(printf
#define DB_NOT_GRANTED
Definition: dbmi.h:129
#define DB_SQL_TYPE_TIME
Definition: dbmi.h:89
int db_set_table_description(dbTable *table, const char *description)
Set the description of the table.
#define DB_SQL_TYPE_SMALLINT
Definition: dbmi.h:82
void db_set_column_update_priv_not_granted(dbColumn *)
Unset update privileges.
void db_set_table_delete_priv_not_granted(dbTable *table)
Set delete privileges not granted for table.
void db_set_table_select_priv_not_granted(dbTable *table)
Set selection privileges not granted for all columns.
void * db_malloc(int)
Allocate memory.
#define DB_SQL_TYPE_DECIMAL
Definition: dbmi.h:86
int priv_insert
Definition: dbmi.h:226
const char * name
Definition: named_colr.c:7
struct _db_column dbColumn
const char * db_get_table_name(dbTable *table)
Get the name of the table.
dbColumn * db_get_table_column(dbTable *table, int idx)
Returns column structure for given table and column number.
void db_set_column_select_priv_not_granted(dbColumn *)
Unset select privileges.
dbColumn * db_copy_column(dbColumn *, dbColumn *)
Copy a db column from source to destination.
int G_debug(int, const char *,...) __attribute__((format(printf
void db_free_string(dbString *)
Free allocated space for dbString.
Definition: string.c:150
void db_free_column(dbColumn *)
Frees column structure.
#define DB_OK
Definition: dbmi.h:71