GRASS GIS 8 Programmer's Manual  8.2.2dev(2023)-3d2c704037
copy_tab.c
Go to the documentation of this file.
1 /*!
2  \file db/dbmi_client/copy_tab.c
3 
4  \brief DBMI Library (client) - copy table
5 
6  (C) 1999-2008 by the GRASS Development Team
7 
8  This program is free software under the GNU General Public
9  License (>=v2). Read the file COPYING that comes with GRASS
10  for details.
11 
12  \author Joel Jones (CERL/UIUC), Radim Blazek
13 */
14 
15 #include <stdlib.h>
16 #include <string.h>
17 #include <grass/dbmi.h>
18 #include <grass/glocale.h>
19 #include "macros.h"
20 
21 static int cmp(const void *pa, const void *pb)
22 {
23  int *p1 = (int *)pa;
24  int *p2 = (int *)pb;
25 
26  if (*p1 < *p2)
27  return -1;
28  if (*p1 > *p2)
29  return 1;
30  return 0;
31 }
32 
33 /*!
34  \brief Copy table, used by various db_copy_table* (internal use only)
35 
36  Use either 'where' or 'select' or 'selcol'+'ivals'+'nvals' but
37  never more than one.
38 
39  Warning: driver opened as second must be closed as first, otherwise
40  it hangs, not sure why.
41 
42  \param from_dvrname name of driver from table is copied
43  \param from_dbname name of database from table is copied
44  \param from_tbl_name name of table to be copied
45  \param to_dvrname name of driver to - where table is copied to
46  \param to_dbname name of database to - where table is copied to
47  \param to_dbname name of copied table
48  \param where WHERE SQL condition (without where key word) or NULL
49  \param select full select statement
50  \param selcol name of column used to select records by values in ivals or NULL
51  \param ivals pointer to array of integer values or NULL
52  \param nvals number of values in ivals
53 
54  \return DB_OK on success
55  \return DB_FAILED on failure
56  */
57 static int copy_table(const char *from_drvname, const char *from_dbname,
58  const char *from_tblname, const char *to_drvname,
59  const char *to_dbname, const char *to_tblname,
60  const char *where, const char *select, const char *selcol,
61  int *ivals, int nvals)
62 {
63  int col, ncols, sqltype, ctype, more, selcol_found;
64  char buf[1000];
65  int *ivalues;
66  dbHandle from_handle, to_handle;
67  dbString tblname, sql;
68  dbString value_string;
69  dbString *tblnames;
70  dbTable *table, *out_table;
71  dbCursor cursor;
72  dbColumn *column;
73  dbValue *value;
74  const char *colname;
75  dbDriver *from_driver, *to_driver;
76  int count, i;
77 
78  G_debug(3, "db_copy_table():\n from driver = %s, db = %s, table = %s\n"
79  " to driver = %s, db = %s, table = %s, where = %s, select = %s",
80  from_drvname, from_dbname, from_tblname, to_drvname, to_dbname,
81  to_tblname, where, select);
82 
83  db_init_handle(&from_handle);
84  db_init_handle(&to_handle);
85  db_init_string(&tblname);
86  db_init_string(&sql);
87  db_init_string(&value_string);
88 
89  if (selcol) {
90  if (!ivals || (ivals && nvals == 0)) {
91  G_warning(_("Array of values to select from column <%s> is empty"), selcol);
92  return DB_FAILED;
93  }
94  /* Make a copy of input values and sort it */
95  if (ivals) {
96  ivalues = (int *)G_malloc(nvals * sizeof(int));
97  memcpy(ivalues, ivals, nvals * sizeof(int));
98  qsort((void *)ivalues, nvals, sizeof(int), cmp);
99  }
100  }
101  else
102  ivalues = NULL;
103 
104  /* Open input driver and database */
105  from_driver = db_start_driver(from_drvname);
106  if (from_driver == NULL) {
107  G_warning(_("Unable to start driver <%s>"), from_drvname);
108  return DB_FAILED;
109  }
110  db_set_handle(&from_handle, from_dbname, NULL);
111  if (db_open_database(from_driver, &from_handle) != DB_OK) {
112  G_warning(_("Unable to open database <%s> by driver <%s>"),
113  from_dbname, from_drvname);
115  return DB_FAILED;
116  }
117 
118  /* Open output driver and database */
119  if (strcmp(from_drvname, to_drvname) == 0
120  && strcmp(from_dbname, to_dbname) == 0) {
121  G_debug(3, "Use the same driver");
122  to_driver = from_driver;
123  }
124  else {
125  to_driver = db_start_driver(to_drvname);
126  if (to_driver == NULL) {
127  G_warning(_("Unable to start driver <%s>"), to_drvname);
129  return DB_FAILED;
130  }
131  db_set_handle(&to_handle, to_dbname, NULL);
132  if (db_open_database(to_driver, &to_handle) != DB_OK) {
133  G_warning(_("Unable to open database <%s> by driver <%s>"),
134  to_dbname, to_drvname);
136  if (from_driver != to_driver) {
138  }
139  return DB_FAILED;
140  }
141  }
142 
143  db_begin_transaction(to_driver);
144 
145  /* Because in SQLite3 an opened cursor is no more valid
146  if 'schema' is modified (create table), we have to open
147  cursor twice */
148 
149  /* test if the table exists */
150  if (db_list_tables(to_driver, &tblnames, &count, 0) != DB_OK) {
151  G_warning(_("Unable to get list tables in database <%s>"),
152  to_dbname);
154  if (from_driver != to_driver)
156 
157  return DB_FAILED;
158  }
159 
160  for (i = 0; i < count; i++) {
161  int ret;
162  char *tblname_i;
163 
164  tblname_i = NULL;
165  if (strcmp(to_drvname, "pg") == 0) {
166  char *p, *tbl;
167  dbConnection connection;
168 
169  tbl = db_get_string(&tblnames[i]);
170  db_get_connection(&connection);
171  p = strstr(tbl, ".");
172 
173  if (p) {
174  char buf[GNAME_MAX];
175 
176  sprintf(buf, "%s.%s", connection.schemaName ? connection.schemaName : "public",
177  to_tblname);
178  if (strcmp(buf, tbl) == 0)
179  tblname_i = G_store(p + 1); /* skip dot */
180  }
181  }
182  if (!tblname_i) {
183  tblname_i = G_store(db_get_string(&tblnames[i]));
184  }
185 
186  ret = DB_FAILED;
187  if (strcmp(to_tblname, tblname_i) == 0) {
188  if (G_get_overwrite()) {
189  G_warning(_("Table <%s> already exists in database and will be overwritten"),
190  to_tblname);
191  ret = db_drop_table(to_driver, &tblnames[i]);
192  }
193  else {
194  G_warning(_("Table <%s> already exists in database <%s>"),
195  to_tblname, to_dbname);
196  }
197 
198  if (ret != DB_OK) {
200  if (from_driver != to_driver)
202 
203  return DB_FAILED;
204  }
205  }
206 
207  G_free(tblname_i);
208  }
209 
210  /* Create new table */
211  /* Open cursor for data structure */
212  if (select) {
213  db_set_string(&sql, select);
214 
215  /* TODO!: cannot use this because it will not work if a query
216  * ends with 'group by' for example */
217  /*
218  tmp = strdup ( select );
219  G_tolcase ( tmp );
220 
221  if ( !strstr( tmp,"where") )
222  {
223  db_append_string ( &sql, " where 0 = 1");
224  }
225  else
226  {
227  db_append_string ( &sql, " and 0 = 1");
228  }
229 
230  free (tmp);
231  */
232  }
233  else {
234  db_set_string(&sql, "select * from ");
235  db_append_string(&sql, from_tblname);
236  db_append_string(&sql, " where 0 = 1"); /* to get no data */
237  }
238 
239  G_debug(3, "db__copy_table: %s", db_get_string(&sql));
240  if (db_open_select_cursor(from_driver, &sql, &cursor, DB_SEQUENTIAL) !=
241  DB_OK) {
242  G_warning(_("Unable to open select cursor: '%s'"),
243  db_get_string(&sql));
245  if (from_driver != to_driver) {
247  }
248  return DB_FAILED;
249  }
250  G_debug(3, "Select cursor opened");
251 
252  table = db_get_cursor_table(&cursor);
253  ncols = db_get_table_number_of_columns(table);
254  G_debug(3, "ncols = %d", ncols);
255 
256  out_table = db_alloc_table(ncols);
257  db_set_table_name(out_table, to_tblname);
258 
259  selcol_found = 0;
260  for (col = 0; col < ncols; col++) {
261  dbColumn *out_column;
262 
263  column = db_get_table_column(table, col);
264  colname = db_get_column_name(column);
265  sqltype = db_get_column_sqltype(column);
266  ctype = db_sqltype_to_Ctype(sqltype);
267 
268  G_debug(3, "%s (%s)", colname, db_sqltype_name(sqltype));
269 
270  out_column = db_get_table_column(out_table, col);
271 
272  if (selcol && G_strcasecmp(colname, selcol) == 0) {
273  if (ctype != DB_C_TYPE_INT)
274  G_fatal_error(_("Column <%s> is not integer"),
275  colname);
276  selcol_found = 1;
277  }
278 
279  db_set_column_name(out_column, db_get_column_name(column));
280  db_set_column_description(out_column,
281  db_get_column_description(column));
282  db_set_column_sqltype(out_column, db_get_column_sqltype(column));
283  db_set_column_length(out_column, db_get_column_length(column));
285  db_set_column_scale(out_column, db_get_column_scale(column));
286  }
287 
288  db_close_cursor(&cursor);
289 
290  if (selcol && !selcol_found)
291  G_fatal_error(_("Column <%s> not found"), selcol);
292 
293  if (db_create_table(to_driver, out_table) != DB_OK) {
294  G_warning(_("Unable to create table <%s>"),
295  to_tblname);
297  if (from_driver != to_driver) {
299  }
300  return DB_FAILED;
301  }
302 
303  /* Open cursor with data */
304  if (select) {
305  db_set_string(&sql, select);
306  }
307  else {
308  db_set_string(&sql, "select * from ");
309  db_append_string(&sql, from_tblname);
310  if (where) {
311  db_append_string(&sql, " where ");
312  db_append_string(&sql, where);
313  }
314  }
315 
316  G_debug(3, "db__copy_table: %s", db_get_string(&sql));
317  if (db_open_select_cursor(from_driver, &sql, &cursor, DB_SEQUENTIAL) !=
318  DB_OK) {
319  G_warning(_("Unable to open select cursor: '%s'"),
320  db_get_string(&sql));
322  if (from_driver != to_driver) {
324  }
325  return DB_FAILED;
326  }
327  G_debug(3, "Select cursor opened");
328 
329  table = db_get_cursor_table(&cursor);
330  ncols = db_get_table_number_of_columns(table);
331  G_debug(3, "ncols = %d", ncols);
332 
333  /* Copy all rows */
334  while (1) {
335  int select;
336 
337  if (db_fetch(&cursor, DB_NEXT, &more) != DB_OK) {
338  G_warning(_("Unable to fetch data from table <%s>"),
339  from_tblname);
340  db_close_cursor(&cursor);
342  if (from_driver != to_driver) {
344  }
345  return DB_FAILED;
346  }
347  if (!more)
348  break;
349 
350  sprintf(buf, "insert into %s values ( ", to_tblname);
351  db_set_string(&sql, buf);
352  select = 1;
353  for (col = 0; col < ncols; col++) {
354  column = db_get_table_column(table, col);
355  colname = db_get_column_name(column);
356  sqltype = db_get_column_sqltype(column);
357  ctype = db_sqltype_to_Ctype(sqltype);
358  value = db_get_column_value(column);
359 
360  if (selcol && G_strcasecmp(colname, selcol) == 0) {
361  if (db_test_value_isnull(value))
362  continue;
363  if (!bsearch(&(value->i), ivalues, nvals, sizeof(int), cmp)) {
364  select = 0;
365  break;
366  }
367  }
368  if (col > 0)
369  db_append_string(&sql, ", ");
370  db_convert_value_to_string(value, sqltype, &value_string);
371  switch (ctype) {
372  case DB_C_TYPE_STRING:
373  case DB_C_TYPE_DATETIME:
374  if (db_test_value_isnull(value)) {
375  db_append_string(&sql, "null");
376  }
377  else {
378  db_double_quote_string(&value_string);
379  db_append_string(&sql, "'");
380  db_append_string(&sql, db_get_string(&value_string));
381  db_append_string(&sql, "'");
382  }
383  break;
384  case DB_C_TYPE_INT:
385  case DB_C_TYPE_DOUBLE:
386  if (db_test_value_isnull(value)) {
387  db_append_string(&sql, "null");
388  }
389  else {
390  db_append_string(&sql, db_get_string(&value_string));
391  }
392  break;
393  default:
394  G_warning(_("Unknown column type (column <%s>)"),
395  colname);
396  db_close_cursor(&cursor);
398  if (from_driver != to_driver) {
400  }
401  return DB_FAILED;
402  }
403  }
404  if (!select)
405  continue;
406  db_append_string(&sql, ")");
407  G_debug(3, "db__copy_table: %s", db_get_string(&sql));
408  if (db_execute_immediate(to_driver, &sql) != DB_OK) {
409  G_warning("Unable to insert new record: '%s'",
410  db_get_string(&sql));
411  db_close_cursor(&cursor);
413  if (from_driver != to_driver) {
415  }
416  return DB_FAILED;
417  }
418  }
419  if (selcol)
420  G_free(ivalues);
421  G_debug(3, "Table copy OK");
422 
423  db_close_cursor(&cursor);
424  db_commit_transaction(to_driver);
426  if (from_driver != to_driver) {
428  }
429 
430  return DB_OK;
431 }
432 
433 /*!
434  \brief Copy a table
435 
436  \param from_drvname name of driver from table is copied
437  \param from_dbname name of database from table is copied
438  \param from_tblname name of table to be copied
439  \param to_drvname name of driver to - where table is copied to
440  \param to_dbname name of database to - where table is copied to
441  \param to_tblname name of copied table
442 
443  \return DB_OK on success
444  \return DB_FAILED on failure
445  */
446 int db_copy_table(const char *from_drvname, const char *from_dbname,
447  const char *from_tblname, const char *to_drvname,
448  const char *to_dbname, const char *to_tblname)
449 {
450  return copy_table(from_drvname, from_dbname, from_tblname,
451  to_drvname, to_dbname, to_tblname,
452  NULL, NULL, NULL, NULL, 0);
453 }
454 
455 /*!
456  \brief Copy a table (by where statement)
457 
458  \param from_drvname name of driver from table is copied
459  \param from_dbname name of database from table is copied
460  \param from_tblname name of table to be copied
461  \param to_drvname name of driver to - where table is copied to
462  \param to_dbname name of database to - where table is copied to
463  \param to_tblname name of copied table
464  \param where WHERE SQL condition (without where key word)
465 
466  \return DB_OK on success
467  \return DB_FAILED on failure
468 */
469 int db_copy_table_where(const char *from_drvname, const char *from_dbname,
470  const char *from_tblname, const char *to_drvname,
471  const char *to_dbname, const char *to_tblname,
472  const char *where)
473 {
474  return copy_table(from_drvname, from_dbname, from_tblname,
475  to_drvname, to_dbname, to_tblname,
476  where, NULL, NULL, NULL, 0);
477 }
478 
479 /*!
480  \brief Copy a table (by select statement)
481 
482  \param from_drvname name of driver from table is copied
483  \param from_dbname name of database from table is copied
484  \param from_dbname name of table to be copied
485  \param to_drvname name of driver to - where table is copied to
486  \param to_dbname name of database to - where table is copied to
487  \param to_tblname name of copied table
488  \param select full select statement
489 
490  \return DB_OK on success
491  \return DB_FAILED on failure
492 */
493 int db_copy_table_select(const char *from_drvname, const char *from_dbname,
494  const char *from_tblname, const char *to_drvname,
495  const char *to_dbname, const char *to_tblname,
496  const char *select)
497 {
498  return copy_table(from_drvname, from_dbname, from_tblname,
499  to_drvname, to_dbname, to_tblname,
500  NULL, select, NULL, NULL, 0);
501 }
502 
503 /*!
504  \brief Copy a table (by keys)
505 
506  \param from_drvname name of driver from table is copied
507  \param from_dbname name of database from table is copied
508  \param from_tblname name of table to be copied
509  \param to_drvname name of driver to - where table is copied to
510  \param to_dbname name of database to - where table is copied to
511  \param to_tblname name of copied table
512  \param selcol name of column used to select records by values in ivals or NULL
513  \param ivals pointer to array of integer values or NULL
514  \param nvals number of values in ivals
515 
516  \return DB_OK on success
517  \return DB_FAILED on failure
518 */
519 int db_copy_table_by_ints(const char *from_drvname, const char *from_dbname,
520  const char *from_tblname, const char *to_drvname,
521  const char *to_dbname, const char *to_tblname,
522  const char *selcol, int *ivals, int nvals)
523 {
524  return copy_table(from_drvname, from_dbname, from_tblname,
525  to_drvname, to_dbname, to_tblname,
526  NULL, NULL, selcol, ivals, nvals);
527 }
#define G_malloc(n)
Definition: defs/gis.h:112
int db_begin_transaction(dbDriver *)
Begin transaction.
Definition: c_execute.c:56
void void void void G_fatal_error(const char *,...) __attribute__((format(printf
int db_copy_table(const char *from_drvname, const char *from_dbname, const char *from_tblname, const char *to_drvname, const char *to_dbname, const char *to_tblname)
Copy a table.
Definition: copy_tab.c:446
dbValue * db_get_column_value(dbColumn *)
Returns column value for given column structure.
int db_create_table(dbDriver *, dbTable *)
Create table.
Definition: c_create_tab.c:27
int db_get_connection(dbConnection *)
Get default DB connection settings for the current mapset.
dbDriver * db_start_driver(const char *)
Initialize a new dbDriver for db transaction.
Definition: start.c:50
int db_list_tables(dbDriver *, dbString **, int *, int)
List available tables for given connection.
Definition: c_list_tabs.c:39
void db_set_column_sqltype(dbColumn *, int)
Define column sqltype for column.
int db_get_column_length(dbColumn *)
Get column&#39;s length.
const char * db_get_column_name(dbColumn *)
Returns column name for given column.
void db_init_string(dbString *)
Initialize dbString.
Definition: string.c:25
int db_fetch(dbCursor *, int, int *)
Fetch data from open cursor.
Definition: c_fetch.c:28
int db_open_select_cursor(dbDriver *, dbString *, dbCursor *, int)
Open select cursor.
Definition: c_openselect.c:37
char * db_get_string(const dbString *)
Get string.
Definition: string.c:140
#define DB_C_TYPE_DATETIME
Definition: dbmi.h:110
void G_free(void *)
Free allocated memory.
Definition: gis/alloc.c:149
int count
int db_set_handle(dbHandle *, const char *, const char *)
Set handle (database and schema name)
Definition: handle.c:39
#define NULL
Definition: ccmath.h:32
int db_get_column_precision(dbColumn *)
Get column precision.
int db_set_string(dbString *, const char *)
Inserts string to dbString (enlarge string)
Definition: string.c:41
int db_close_database_shutdown_driver(dbDriver *)
Close driver/database connection.
Definition: db.c:62
char * schemaName
Definition: dbmi.h:298
int db_set_column_name(dbColumn *, const char *)
Set column name.
int G_get_overwrite()
Get overwrite value.
Definition: parser.c:934
int db_get_column_sqltype(dbColumn *)
Returns column sqltype for column.
int db_copy_table_where(const char *from_drvname, const char *from_dbname, const char *from_tblname, const char *to_drvname, const char *to_dbname, const char *to_tblname, const char *where)
Copy a table (by where statement)
Definition: copy_tab.c:469
int db_test_value_isnull(dbValue *)
Check of value is null.
Definition: value.c:26
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 int G_strcasecmp(const char *, const char *)
String compare ignoring case (upper or lower)
Definition: strings.c:47
int db_copy_table_by_ints(const char *from_drvname, const char *from_dbname, const char *from_tblname, const char *to_drvname, const char *to_dbname, const char *to_tblname, const char *selcol, int *ivals, int nvals)
Copy a table (by keys)
Definition: copy_tab.c:519
int db_convert_value_to_string(dbValue *, int, dbString *)
Convert value to string.
Definition: valuefmt.c:67
#define DB_NEXT
Definition: dbmi.h:114
int db_execute_immediate(dbDriver *, dbString *)
Execute SQL statements.
Definition: c_execute.c:27
#define DB_C_TYPE_STRING
Definition: dbmi.h:107
void db_set_column_scale(dbColumn *, int)
Set column scale.
int db_sqltype_to_Ctype(int)
Get C data type based on given SQL data type.
Definition: sqlCtype.c:24
int db_copy_table_select(const char *from_drvname, const char *from_dbname, const char *from_tblname, const char *to_drvname, const char *to_dbname, const char *to_tblname, const char *select)
Copy a table (by select statement)
Definition: copy_tab.c:493
#define DB_C_TYPE_INT
Definition: dbmi.h:108
void db_init_handle(dbHandle *)
Initialize handle (i.e database/schema)
Definition: handle.c:23
int db_set_table_name(dbTable *, const char *)
Set the name of the table.
dbTable * db_alloc_table(int)
Allocate a table with a specific number of columns.
int db_set_column_description(dbColumn *, const char *)
Set column description.
int db_open_database(dbDriver *, dbHandle *)
Open database connection.
Definition: c_opendb.c:27
int db_commit_transaction(dbDriver *)
Commit transaction.
Definition: c_execute.c:82
#define DB_FAILED
Definition: dbmi.h:72
#define DB_SEQUENTIAL
Definition: dbmi.h:123
int i
Definition: dbmi.h:196
const char * db_get_column_description(dbColumn *)
Returns column description for given column.
#define DB_C_TYPE_DOUBLE
Definition: dbmi.h:109
#define GNAME_MAX
Definition: gis.h:177
void G_warning(const char *,...) __attribute__((format(printf
dbColumn * db_get_table_column(dbTable *, int)
Returns column structure for given table and column number.
void db_double_quote_string(dbString *)
Replace each &#39; is replaced by &#39;&#39;.
Definition: string.c:240
#define _(str)
Definition: glocale.h:10
int db_drop_table(dbDriver *, dbString *)
Drop table.
Definition: c_drop_tab.c:28
char * G_store(const char *)
Copy string to allocated memory.
Definition: strings.c:87
void db_set_column_precision(dbColumn *, int)
Set column precision.
dbTable * db_get_cursor_table(dbCursor *)
Get table allocated by cursor.
Definition: cursor.c:67
int db_get_table_number_of_columns(dbTable *)
Return the number of columns of the table.
int G_debug(int, const char *,...) __attribute__((format(printf
int db_get_column_scale(dbColumn *)
Get column scale.
void db_set_column_length(dbColumn *, int)
Set column&#39;s length.
int db_close_cursor(dbCursor *)
Close cursor.
Definition: c_close_cur.c:27
#define DB_OK
Definition: dbmi.h:71