3
table_cloner.c -- Cloning a Table
5
version 4.3, 2015 June 29
7
Author: Sandro Furieri a.furieri@lqt.it
9
------------------------------------------------------------------------------
11
Version: MPL 1.1/GPL 2.0/LGPL 2.1
13
The contents of this file are subject to the Mozilla Public License Version
14
1.1 (the "License"); you may not use this file except in compliance with
15
the License. You may obtain a copy of the License at
16
http://www.mozilla.org/MPL/
18
Software distributed under the License is distributed on an "AS IS" basis,
19
WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
20
for the specific language governing rights and limitations under the
23
The Original Code is the SpatiaLite library
25
The Initial Developer of the Original Code is Alessandro Furieri
27
Portions created by the Initial Developer are Copyright (C) 2008-2015
28
the Initial Developer. All Rights Reserved.
32
Alternatively, the contents of this file may be used under the terms of
33
either the GNU General Public License Version 2 or later (the "GPL"), or
34
the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
35
in which case the provisions of the GPL or the LGPL are applicable instead
36
of those above. If you wish to allow use of your version of this file only
37
under the terms of either the GPL or the LGPL, and not to allow others to
38
use your version of this file under the terms of the MPL, indicate your
39
decision by deleting the provisions above and replace them with the notice
40
and other provisions required by the GPL or the LGPL. If you do not delete
41
the provisions above, a recipient may use your version of this file under
42
the terms of any one of the MPL, the GPL or the LGPL.
50
#if defined(_WIN32) && !defined(__MINGW32__)
51
#include "config-msvc.h"
56
#include <spatialite/sqlite.h>
57
#include <spatialite/debug.h>
59
#include <spatialite.h>
60
#include <spatialite_private.h>
61
#include <spatialite/gaiaaux.h>
64
#define strcasecmp _stricmp
65
#define strncasecmp _strnicmp
66
#endif /* not WIN32 */
70
/* a Geometry column object */
81
/* a Trigger object */
85
struct aux_trigger *next;
90
/* a Foreign Key Columns object */
93
struct aux_fk_columns *next;
96
struct aux_foreign_key
98
/* a Foreign Key object */
105
struct aux_fk_columns *first;
106
struct aux_fk_columns *last;
107
struct aux_foreign_key *next;
110
struct aux_index_column
112
/* an Index Column object */
114
struct aux_index_column *next;
119
/* a Table Index object */
122
struct aux_index_column *first;
123
struct aux_index_column *last;
124
struct aux_index *next;
129
/* a Table Column object */
137
struct aux_geometry *geometry;
139
int already_existing;
141
struct aux_column *next;
146
/* Primary Key Columns */
147
struct aux_column *column;
148
struct aux_pk_column *next;
153
/* the main Cloner object */
158
struct aux_column *first_col;
159
struct aux_column *last_col;
160
struct aux_pk_column *first_pk;
161
struct aux_pk_column *last_pk;
162
struct aux_index *first_idx;
163
struct aux_index *last_idx;
164
struct aux_foreign_key *first_fk;
165
struct aux_foreign_key *last_fk;
166
struct aux_trigger *first_trigger;
167
struct aux_trigger *last_trigger;
168
struct aux_pk_column **sorted_pks;
175
int already_existing;
179
create_column (sqlite3 * sqlite, const char *table, struct aux_column *column)
181
/* creating a further ordinary Column */
183
char *err_msg = NULL;
188
xtable = gaiaDoubleQuotedSql (table);
189
xcolumn = gaiaDoubleQuotedSql (column->name);
192
if (column->deflt != NULL)
193
sql = sqlite3_mprintf ("ALTER TABLE main.\"%s\" "
194
"ADD COLUMN \"%s\" %s NOT NULL DEFAULT %s",
195
xtable, xcolumn, column->type,
198
sql = sqlite3_mprintf ("ALTER TABLE main.\"%s\" "
199
"ADD COLUMN \"%s\" %s NOT NULL", xtable,
200
xcolumn, column->type);
204
if (column->deflt != NULL)
205
sql = sqlite3_mprintf ("ALTER TABLE main.\"%s\" "
206
"ADD COLUMN \"%s\" %s DEFAULT %s", xtable,
207
xcolumn, column->type, column->deflt);
209
sql = sqlite3_mprintf ("ALTER TABLE main.\"%s\" "
210
"ADD COLUMN \"%s\" %s", xtable, xcolumn,
215
ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
216
if (ret != SQLITE_OK)
218
spatialite_e ("ALTER TABLE ADD COLUMN error: %s\n", err_msg);
219
sqlite3_free (err_msg);
226
create_geometry (sqlite3 * sqlite, const char *table, struct aux_column *column)
228
/* creating a further Geometry Column */
230
char *err_msg = NULL;
234
const char *type = "GEOMETRY";
235
const char *dims = "XY";
236
int gtype = column->geometry->type;
238
if (column->geometry->cast2multi)
240
/* promoting to the corresponding MultiType */
241
switch (column->geometry->type)
349
type = "MULTILINESTRING";
353
type = "MULTILINESTRING";
357
type = "MULTILINESTRING";
361
type = "MULTILINESTRING";
365
type = "MULTIPOLYGON";
369
type = "MULTIPOLYGON";
373
type = "MULTIPOLYGON";
377
type = "MULTIPOLYGON";
381
type = "GEOMETRYCOLLECTION";
385
type = "GEOMETRYCOLLECTION";
389
type = "GEOMETRYCOLLECTION";
393
type = "GEOMETRYCOLLECTION";
414
xtable = gaiaDoubleQuotedSql (table);
415
xcolumn = gaiaDoubleQuotedSql (column->name);
418
sql = sqlite3_mprintf ("SELECT AddGeometryColumn(Lower(%Q), "
419
"Lower(%Q), %d, %Q, %Q, 1)", xtable, xcolumn,
420
column->geometry->srid, type, dims);
424
sql = sqlite3_mprintf ("SELECT AddGeometryColumn(Lower(%Q), "
425
"Lower(%Q), %d, %Q, %Q)", xtable, xcolumn,
426
column->geometry->srid, type, dims);
430
ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
431
if (ret != SQLITE_OK)
433
spatialite_e ("ADD GEOMETRY COLUMN error: %s\n", err_msg);
434
sqlite3_free (err_msg);
438
if (column->geometry->spatial_index)
440
/* creating the corresponding Spatial Index */
441
xtable = gaiaDoubleQuotedSql (table);
442
xcolumn = gaiaDoubleQuotedSql (column->name);
443
sql = sqlite3_mprintf ("SELECT CreateSpatialIndex("
444
"Lower(%Q), Lower(%Q))", xtable, xcolumn);
447
ret = sqlite3_exec (sqlite, sql, NULL, NULL, &err_msg);
448
if (ret != SQLITE_OK)
450
spatialite_e ("CREATE SPATIAL INDEX error: %s\n", err_msg);
451
sqlite3_free (err_msg);
459
upgrade_output_table (struct aux_cloner *cloner)
461
/* creating any new Column required by APPEND */
462
struct aux_column *column = cloner->first_col;
463
while (column != NULL)
467
/* skipping columns marked to be ignored */
468
column = column->next;
471
if (column->already_existing == 0)
473
if (column->geometry != NULL)
475
/* creating a Geometry */
477
(cloner->sqlite, cloner->out_table, column))
480
("CloneTable: unable to ADD Geometry COLUMN \"%s\" on Table \"%s\"\n",
481
column->name, cloner->out_table);
487
/* creating an ordinary Column */
489
(cloner->sqlite, cloner->out_table, column))
492
("CloneTable: unable to ADD COLUMN \"%s\" on Table \"%s\"\n",
493
column->name, cloner->out_table);
498
column = column->next;
504
sort_pk_columns (struct aux_cloner *cloner)
506
/* sorting the PK columns (if required) */
507
struct aux_pk_column *ppk;
510
if (cloner->pk_count <= 1)
513
malloc (sizeof (struct aux_pk_column *) * cloner->pk_count);
515
ppk = cloner->first_pk;
518
/* copying pointers to PK cols */
519
*(cloner->sorted_pks + cnt++) = ppk;
527
for (cnt = 1; cnt < cloner->pk_count; cnt++)
529
struct aux_pk_column *ppk1 = *(cloner->sorted_pks + cnt - 1);
530
ppk = *(cloner->sorted_pks + cnt);
531
if (ppk1->column->pk > ppk->column->pk)
534
*(cloner->sorted_pks + cnt - 1) = ppk;
535
*(cloner->sorted_pks + cnt) = ppk1;
543
adjust_ignore (struct aux_cloner *cloner)
545
/* adjusting Ignore columns */
546
struct aux_column *column = cloner->first_col;
547
while (column != NULL)
553
if (column->fk && cloner->with_fks)
558
column = column->next;
563
get_pk_column (struct aux_cloner *cloner, int index)
565
/* returning a PK column name (by sorted index) */
566
struct aux_pk_column *ppk;
567
if (cloner->sorted_pks == NULL)
569
if (index < 0 || index >= cloner->pk_count)
571
ppk = *(cloner->sorted_pks + index);
572
return ppk->column->name;
576
mark_existing_trigger (struct aux_cloner *cloner, const char *name)
578
/* marking an existing Trigger */
579
struct aux_trigger *trigger = cloner->first_trigger;
580
while (trigger != NULL)
582
if (strcasecmp (trigger->name, name) == 0)
584
trigger->already_existing = 1;
587
trigger = trigger->next;
592
check_existing_triggers (struct aux_cloner *cloner)
594
/* exploring the output table - already existing Triggers */
603
sql = sqlite3_mprintf ("SELECT name FROM main.sqlite_master "
604
"WHERE type = 'trigger' AND Lower(tbl_name) = Lower(%Q)",
607
sqlite3_get_table (cloner->sqlite, sql, &results, &rows, &columns,
610
if (ret != SQLITE_OK)
616
for (i = 1; i <= rows; i++)
618
name = results[(i * columns) + 0];
619
mark_existing_trigger (cloner, name);
622
sqlite3_free_table (results);
626
create_output_table (struct aux_cloner *cloner)
628
/* creating the output Table */
629
char *err_msg = NULL;
631
struct aux_column *column;
632
struct aux_foreign_key *fk;
633
struct aux_fk_columns *fk_col;
634
struct aux_index *index;
635
struct aux_index_column *idx_column;
646
sort_pk_columns (cloner);
647
adjust_ignore (cloner);
648
xtable = gaiaDoubleQuotedSql (cloner->out_table);
649
sql = sqlite3_mprintf ("CREATE TABLE main.\"%s\"", xtable);
653
column = cloner->first_col;
654
while (column != NULL)
658
/* IGNORE requested */
659
column = column->next;
662
if (column->geometry != NULL)
664
/* skipping any Geometry column */
665
column = column->next;
670
sql = sqlite3_mprintf ("%s (\n", prev_sql);
674
sql = sqlite3_mprintf ("%s,\n", prev_sql);
675
sqlite3_free (prev_sql);
677
xcolumn = gaiaDoubleQuotedSql (column->name);
678
if (cloner->pk_count == 1 && column->pk)
680
/* immediately declaring a single column Primary Key */
681
if (cloner->autoincrement)
685
if (column->deflt != NULL)
688
("%s\t\"%s\" %s NOT NULL PRIMARY KEY AUTOINCREMENT DEFAULT %s",
689
prev_sql, xcolumn, column->type,
694
("%s\t\"%s\" %s NOT NULL PRIMARY KEY AUTOINCREMENT",
695
prev_sql, xcolumn, column->type);
699
if (column->deflt != NULL)
702
("%s\t\"%s\" %s PRIMARY KEY AUTOINCREMENT DEFAULT %s",
703
prev_sql, xcolumn, column->type,
708
("%s\t\"%s\" %s PRIMARY KEY AUTOINCREMENT",
709
prev_sql, xcolumn, column->type);
716
if (column->deflt != NULL)
719
("%s\t\"%s\" %s NOT NULL PRIMARY KEY DEFAULT %s",
720
prev_sql, xcolumn, column->type,
725
("%s\t\"%s\" %s NOT NULL PRIMARY KEY",
726
prev_sql, xcolumn, column->type);
730
if (column->deflt != NULL)
733
("%s\t\"%s\" %s PRIMARY KEY DEFAULT %s",
734
prev_sql, xcolumn, column->type,
739
("%s\t\"%s\" %s PRIMARY KEY", prev_sql,
740
xcolumn, column->type);
744
sqlite3_free (prev_sql);
746
column = column->next;
751
if (column->deflt != NULL)
752
sql = sqlite3_mprintf ("%s\t\"%s\" %s NOT NULL DEFAULT %s",
753
prev_sql, xcolumn, column->type,
756
sql = sqlite3_mprintf ("%s\t\"%s\" %s NOT NULL",
757
prev_sql, xcolumn, column->type);
761
if (column->deflt != NULL)
762
sql = sqlite3_mprintf ("%s\t\"%s\" %s DEFAULT %s",
763
prev_sql, xcolumn, column->type,
766
sql = sqlite3_mprintf ("%s\t\"%s\" %s",
767
prev_sql, xcolumn, column->type);
770
sqlite3_free (prev_sql);
772
column = column->next;
775
if (cloner->pk_count > 1)
777
/* declaring a PRIMARY KEY CONSTRAINT */
778
sql = sqlite3_mprintf ("%s,\n", prev_sql);
779
sqlite3_free (prev_sql);
781
constraint = sqlite3_mprintf ("pk_%s", cloner->out_table);
782
xconstraint = gaiaDoubleQuotedSql (constraint);
783
sqlite3_free (constraint);
785
sqlite3_mprintf ("%s\tCONSTRAINT \"%s\" PRIMARY KEY (", prev_sql,
788
sqlite3_free (prev_sql);
790
for (i = 0; i < cloner->pk_count; i++)
792
xconstraint = gaiaDoubleQuotedSql (get_pk_column (cloner, i));
794
sql = sqlite3_mprintf ("%s%s", prev_sql, xconstraint);
796
sql = sqlite3_mprintf ("%s, %s", prev_sql, xconstraint);
798
sqlite3_free (prev_sql);
801
sql = sqlite3_mprintf ("%s)", prev_sql);
802
sqlite3_free (prev_sql);
806
if (cloner->with_fks)
808
/* cloning all Foreign Key definitions */
809
fk = cloner->first_fk;
813
/* declaring all FOREIGN KEY CONSTRAINTs */
814
sql = sqlite3_mprintf ("%s,\n", prev_sql);
815
sqlite3_free (prev_sql);
818
sqlite3_mprintf ("fk_%s_%d", cloner->out_table, fk_no++);
819
xconstraint = gaiaDoubleQuotedSql (constraint);
820
sqlite3_free (constraint);
822
sqlite3_mprintf ("%s\tCONSTRAINT \"%s\" FOREIGN KEY (",
823
prev_sql, xconstraint);
825
sqlite3_free (prev_sql);
828
while (fk_col != NULL)
830
xconstraint = gaiaDoubleQuotedSql (fk_col->from);
831
if (fk_col == fk->first)
832
sql = sqlite3_mprintf ("%s%s", prev_sql, xconstraint);
835
sqlite3_mprintf ("%s, %s", prev_sql, xconstraint);
837
sqlite3_free (prev_sql);
839
fk_col = fk_col->next;
841
xtable = gaiaDoubleQuotedSql (fk->references);
843
sqlite3_mprintf ("%s) REFERENCES \"%s\" (", prev_sql,
846
sqlite3_free (prev_sql);
849
while (fk_col != NULL)
851
xconstraint = gaiaDoubleQuotedSql (fk_col->to);
852
if (fk_col == fk->first)
853
sql = sqlite3_mprintf ("%s%s", prev_sql, xconstraint);
856
sqlite3_mprintf ("%s, %s", prev_sql, xconstraint);
858
sqlite3_free (prev_sql);
860
fk_col = fk_col->next;
862
sql = sqlite3_mprintf ("%s)", prev_sql);
863
sqlite3_free (prev_sql);
868
sql = sqlite3_mprintf ("%s\n)", prev_sql);
869
sqlite3_free (prev_sql);
871
ret = sqlite3_exec (cloner->sqlite, sql, NULL, NULL, &err_msg);
873
if (ret != SQLITE_OK)
875
spatialite_e ("CREATE TABLE error: %s\n", err_msg);
876
sqlite3_free (err_msg);
880
column = cloner->first_col;
881
while (column != NULL)
883
if (column->geometry != NULL && !(column->ignore))
885
/* adding a Geometry Column */
887
(cloner->sqlite, cloner->out_table, column))
890
column = column->next;
893
index = cloner->first_idx;
895
while (index != NULL)
897
/* creating an Index */
899
sqlite3_mprintf ("idx_%s_%d", cloner->out_table, fk_no++);
900
xconstraint = gaiaDoubleQuotedSql (constraint);
901
xtable = gaiaDoubleQuotedSql (cloner->out_table);
904
sqlite3_mprintf ("CREATE UNIQUE INDEX main.\"%s\" ON \"%s\"",
905
xconstraint, xtable);
908
sqlite3_mprintf ("CREATE INDEX main.\"%s\" ON \"%s\"",
909
xconstraint, xtable);
914
idx_column = index->first;
916
while (idx_column != NULL)
918
xcolumn = gaiaDoubleQuotedSql (idx_column->name);
921
sql = sqlite3_mprintf ("%s (\"%s\"", prev_sql, xcolumn);
925
sql = sqlite3_mprintf ("%s, \"%s\"", prev_sql, xcolumn);
927
sqlite3_free (prev_sql);
929
idx_column = idx_column->next;
931
sql = sqlite3_mprintf ("%s)\n", prev_sql);
932
sqlite3_free (prev_sql);
934
ret = sqlite3_exec (cloner->sqlite, sql, NULL, NULL, &err_msg);
936
if (ret != SQLITE_OK)
938
spatialite_e ("CREATE INDEX error: %s\n", err_msg);
939
sqlite3_free (err_msg);
945
if (cloner->with_triggers)
947
struct aux_trigger *trigger;
948
check_existing_triggers (cloner);
949
trigger = cloner->first_trigger;
950
while (trigger != NULL)
952
if (trigger->already_existing)
954
/* skipping already defined triggers */
955
trigger = trigger->next;
958
/* adding a trigger */
960
sqlite3_exec (cloner->sqlite, trigger->sql, NULL, NULL,
962
if (ret != SQLITE_OK)
964
spatialite_e ("CREATE TRIGGER error: %s\n", err_msg);
965
sqlite3_free (err_msg);
968
trigger = trigger->next;
975
copy_rows (struct aux_cloner *cloner)
977
/* copying all rows from the origin into the destination Table */
978
sqlite3_stmt *stmt_in = NULL;
979
sqlite3_stmt *stmt_out = NULL;
981
struct aux_column *column;
989
/* composing the SELECT statement */
990
sql = sqlite3_mprintf ("SELECT ");
992
column = cloner->first_col;
993
while (column != NULL)
997
/* skipping columns to be IGNORED */
998
column = column->next;
1001
xcolumn = gaiaDoubleQuotedSql (column->name);
1004
sql = sqlite3_mprintf ("%s\"%s\"", prev_sql, xcolumn);
1008
sql = sqlite3_mprintf ("%s, \"%s\"", prev_sql, xcolumn);
1010
sqlite3_free (prev_sql);
1012
column = column->next;
1014
xdb_prefix = gaiaDoubleQuotedSql (cloner->db_prefix);
1015
xtable = gaiaDoubleQuotedSql (cloner->in_table);
1017
sqlite3_mprintf ("%s FROM \"%s\".\"%s\"", prev_sql, xdb_prefix, xtable);
1020
/* compiling the SELECT FROM statement */
1022
sqlite3_prepare_v2 (cloner->sqlite, sql, strlen (sql), &stmt_in, NULL);
1024
if (ret != SQLITE_OK)
1026
spatialite_e ("SELECT FROM: \"%s\"\n",
1027
sqlite3_errmsg (cloner->sqlite));
1031
/* composing the INSERT INTO statement */
1032
xtable = gaiaDoubleQuotedSql (cloner->out_table);
1033
sql = sqlite3_mprintf ("INSERT INTO \"%s\" (", xtable);
1037
column = cloner->first_col;
1038
while (column != NULL)
1042
/* skipping columns to be IGNORED */
1043
column = column->next;
1046
xcolumn = gaiaDoubleQuotedSql (column->name);
1049
sql = sqlite3_mprintf ("%s\"%s\"", prev_sql, xcolumn);
1053
sql = sqlite3_mprintf ("%s, \"%s\"", prev_sql, xcolumn);
1055
sqlite3_free (prev_sql);
1057
column = column->next;
1059
sql = sqlite3_mprintf ("%s) VALUES (", prev_sql);
1060
sqlite3_free (prev_sql);
1063
column = cloner->first_col;
1064
while (column != NULL)
1068
/* skipping columns to be IGNORED */
1069
column = column->next;
1072
if (column->geometry != NULL)
1074
/* Geometry column */
1075
if (column->geometry->cast2multi)
1077
/* casting to MultiType */
1078
const char *expr = "CastToMulti(?)";
1081
sql = sqlite3_mprintf ("%s%s", prev_sql, expr);
1085
sql = sqlite3_mprintf ("%s, %s", prev_sql, expr);
1086
sqlite3_free (prev_sql);
1088
column = column->next;
1094
sql = sqlite3_mprintf ("%s?", prev_sql);
1098
sql = sqlite3_mprintf ("%s, ?", prev_sql);
1099
sqlite3_free (prev_sql);
1101
column = column->next;
1103
sql = sqlite3_mprintf ("%s)", prev_sql);
1104
sqlite3_free (prev_sql);
1105
/* compiling the INSERT INTO statement */
1107
sqlite3_prepare_v2 (cloner->sqlite, sql, strlen (sql), &stmt_out, NULL);
1109
if (ret != SQLITE_OK)
1111
spatialite_e ("INSERT INTO: \"%s\"\n",
1112
sqlite3_errmsg (cloner->sqlite));
1118
/* scrolling the result set rows */
1119
ret = sqlite3_step (stmt_in);
1120
if (ret == SQLITE_DONE)
1121
break; /* end of result set */
1122
if (ret == SQLITE_ROW)
1124
/* copying values between input and output tables */
1126
sqlite3_reset (stmt_out);
1127
sqlite3_clear_bindings (stmt_out);
1128
column = cloner->first_col;
1129
while (column != NULL)
1133
/* skipping columns to be IGNORED */
1134
column = column->next;
1137
if (cloner->resequence && cloner->pk_count == 1
1138
&& cloner->autoincrement && column->pk)
1140
/* resequencing an AUTOINCREMENT PK */
1141
sqlite3_bind_null (stmt_out, pos + 1);
1143
column = column->next;
1146
if (sqlite3_column_type (stmt_in, pos) == SQLITE_INTEGER)
1147
sqlite3_bind_int64 (stmt_out, pos + 1,
1148
sqlite3_column_int64 (stmt_in,
1150
else if (sqlite3_column_type (stmt_in, pos) ==
1152
sqlite3_bind_double (stmt_out, pos + 1,
1153
sqlite3_column_double (stmt_in,
1155
else if (sqlite3_column_type (stmt_in, pos) ==
1157
sqlite3_bind_text (stmt_out, pos + 1,
1159
sqlite3_column_text (stmt_in, pos),
1160
sqlite3_column_bytes (stmt_in,
1163
else if (sqlite3_column_type (stmt_in, pos) ==
1165
sqlite3_bind_blob (stmt_out, pos + 1,
1166
sqlite3_column_blob (stmt_in, pos),
1167
sqlite3_column_bytes (stmt_in,
1171
sqlite3_bind_null (stmt_out, pos + 1);
1173
column = column->next;
1175
/* inserting into the output table */
1176
ret = sqlite3_step (stmt_out);
1177
if (ret == SQLITE_DONE || ret == SQLITE_ROW)
1181
spatialite_e ("OUTPUT step error: <%s>\n",
1182
sqlite3_errmsg (cloner->sqlite));
1188
spatialite_e ("INPUT step error: <%s>\n",
1189
sqlite3_errmsg (cloner->sqlite));
1193
sqlite3_finalize (stmt_in);
1194
sqlite3_finalize (stmt_out);
1198
if (stmt_in != NULL)
1199
sqlite3_finalize (stmt_in);
1200
if (stmt_out != NULL)
1201
sqlite3_finalize (stmt_out);
1206
add_geometry (struct aux_cloner *cloner, const char *name, int type, int dims,
1207
int srid, int spatial_index)
1209
/* adding a Geometry Column definition */
1210
struct aux_column *pc = cloner->first_col;
1213
if (strcasecmp (pc->name, name) == 0)
1215
struct aux_geometry *geom =
1216
malloc (sizeof (struct aux_geometry));
1220
geom->spatial_index = spatial_index;
1221
geom->cast2multi = 0;
1222
geom->already_existing = 0;
1223
if (pc->geometry != NULL)
1224
free (pc->geometry);
1225
pc->geometry = geom;
1233
mark_existing_geometry (struct aux_cloner *cloner, const char *name, int type,
1236
/* marking an existing Geometry Column definition */
1237
struct aux_column *pc = cloner->first_col;
1240
if (strcasecmp (pc->name, name) == 0)
1242
if (pc->geometry == NULL)
1244
/* gosh, it's not a Geometry */
1245
pc->mismatching = 1;
1248
if (pc->geometry->type == type && pc->geometry->dims == dims
1249
&& pc->geometry->srid == srid)
1251
/* matching arguments: confirmed */
1252
pc->geometry->already_existing = 1;
1255
/* different arguments: invalid */
1256
pc->mismatching = 1;
1264
add_trigger (struct aux_cloner *cloner, const char *name, const char *sql)
1266
/* adding a Trigger definition */
1268
struct aux_trigger *trigger = malloc (sizeof (struct aux_trigger));
1269
len = strlen (name);
1270
trigger->name = malloc (len + 1);
1271
strcpy (trigger->name, name);
1273
trigger->sql = malloc (len + 1);
1274
strcpy (trigger->sql, sql);
1275
trigger->already_existing = 0;
1276
trigger->next = NULL;
1277
/* updating the linked list */
1278
if (cloner->first_trigger == NULL)
1279
cloner->first_trigger = trigger;
1280
if (cloner->last_trigger != NULL)
1281
cloner->last_trigger->next = trigger;
1282
cloner->last_trigger = trigger;
1286
add_fk_columns (struct aux_foreign_key *fk, struct aux_column *first_col,
1287
const char *from, const char *to)
1289
/* adding Columns correspondencies into a Foreign Key definition */
1291
struct aux_column *column;
1292
struct aux_fk_columns *col = malloc (sizeof (struct aux_fk_columns));
1293
len = strlen (from);
1294
col->from = malloc (len + 1);
1295
strcpy (col->from, from);
1297
col->to = malloc (len + 1);
1298
strcpy (col->to, to);
1300
/* updating the linked list */
1301
if (fk->first == NULL)
1303
if (fk->last != NULL)
1304
fk->last->next = col;
1306
/* marking the column as a Foreign Key */
1308
while (column != NULL)
1310
if (strcasecmp (column->name, from) == 0)
1315
column = column->next;
1320
add_foreign_key (struct aux_cloner *cloner, int id, const char *references,
1321
const char *from, const char *to, const char *on_update,
1322
const char *on_delete, const char *match)
1324
/* adding a Foreign Key definition */
1326
struct aux_foreign_key *fk;
1327
if (cloner->last_fk != NULL)
1329
if (cloner->last_fk->id == id)
1331
/* continuing with the latest FK */
1332
add_fk_columns (cloner->last_fk, cloner->first_col, from, to);
1336
fk = malloc (sizeof (struct aux_foreign_key));
1339
len = strlen (references);
1340
fk->references = malloc (len + 1);
1341
strcpy (fk->references, references);
1342
fk->on_update = NULL;
1343
fk->on_delete = NULL;
1345
if (on_update != NULL)
1347
len = strlen (on_update);
1348
fk->on_update = malloc (len + 1);
1349
strcpy (fk->on_update, on_update);
1351
if (on_delete != NULL)
1353
len = strlen (on_delete);
1354
fk->on_delete = malloc (len + 1);
1355
strcpy (fk->on_delete, on_delete);
1359
len = strlen (match);
1360
fk->match = malloc (len + 1);
1361
strcpy (fk->match, match);
1366
add_fk_columns (fk, cloner->first_col, from, to);
1367
/* updating the linked list */
1368
if (cloner->first_fk == NULL)
1369
cloner->first_fk = fk;
1370
if (cloner->last_fk != NULL)
1371
cloner->last_fk->next = fk;
1372
cloner->last_fk = fk;
1376
add_index_column (struct aux_index *index, struct aux_column *first_col,
1379
/* adding a Column into an Index definition */
1381
struct aux_column *col;
1382
struct aux_index_column *column = malloc (sizeof (struct aux_index_column));
1383
len = strlen (name);
1384
column->name = malloc (len + 1);
1385
strcpy (column->name, name);
1386
column->next = NULL;
1387
/* updating the linked list */
1388
if (index->first == NULL)
1389
index->first = column;
1390
if (index->last != NULL)
1391
index->last->next = column;
1392
index->last = column;
1393
/* marking the column as a Foreign Key */
1397
if (strcasecmp (col->name, name) == 0)
1406
static struct aux_index *
1407
add_index (struct aux_cloner *cloner, const char *name, int unique)
1409
/* adding an Index definition */
1411
struct aux_index *index = malloc (sizeof (struct aux_index));
1412
len = strlen (name);
1413
index->name = malloc (len + 1);
1414
strcpy (index->name, name);
1415
index->unique = unique;
1416
index->first = NULL;
1419
/* updating the linked list */
1420
if (cloner->first_idx == NULL)
1421
cloner->first_idx = index;
1422
if (cloner->last_idx != NULL)
1423
cloner->last_idx->next = index;
1424
cloner->last_idx = index;
1429
add_column (struct aux_cloner *cloner, const char *name, const char *type,
1430
int notnull, const char *deflt, int pk)
1432
/* adding a Column definition */
1434
struct aux_column *column = malloc (sizeof (struct aux_column));
1435
len = strlen (name);
1436
column->name = malloc (len + 1);
1437
strcpy (column->name, name);
1438
len = strlen (type);
1439
column->type = malloc (len + 1);
1440
strcpy (column->type, type);
1441
column->notnull = notnull;
1443
column->deflt = NULL;
1446
len = strlen (deflt);
1447
column->deflt = malloc (len + 1);
1448
strcpy (column->deflt, deflt);
1453
column->geometry = NULL;
1455
column->already_existing = 0;
1456
column->mismatching = 0;
1457
column->next = NULL;
1458
/* updating the linked list */
1459
if (cloner->first_col == NULL)
1460
cloner->first_col = column;
1461
if (cloner->last_col != NULL)
1462
cloner->last_col->next = column;
1463
cloner->last_col = column;
1466
struct aux_pk_column *ppk = malloc (sizeof (struct aux_pk_column));
1467
ppk->column = column;
1469
/* updating the linked list */
1470
if (cloner->first_pk == NULL)
1471
cloner->first_pk = ppk;
1472
if (cloner->last_pk != NULL)
1473
cloner->last_pk->next = ppk;
1474
cloner->last_pk = ppk;
1475
cloner->pk_count += 1;
1480
mark_existing_column (struct aux_cloner *cloner, const char *name)
1482
/* marking an existing Column */
1483
struct aux_column *column = cloner->first_col;
1484
while (column != NULL)
1486
if (strcasecmp (column->name, name) == 0)
1488
column->already_existing = 1;
1491
column = column->next;
1496
check_input_table_columns (struct aux_cloner *cloner)
1498
/* exploring the input table - Columns */
1513
xprefix = gaiaDoubleQuotedSql (cloner->db_prefix);
1514
xtable = gaiaDoubleQuotedSql (cloner->in_table);
1515
sql = sqlite3_mprintf ("PRAGMA \"%s\".table_info(\"%s\")", xprefix, xtable);
1519
sqlite3_get_table (cloner->sqlite, sql, &results, &rows, &columns,
1522
if (ret != SQLITE_OK)
1528
for (i = 1; i <= rows; i++)
1530
name = results[(i * columns) + 1];
1531
type = results[(i * columns) + 2];
1532
notnull = atoi (results[(i * columns) + 3]);
1533
deflt = results[(i * columns) + 4];
1534
pk = atoi (results[(i * columns) + 5]);
1535
add_column (cloner, name, type, notnull, deflt, pk);
1538
sqlite3_free_table (results);
1540
if (cloner->first_col == NULL)
1543
("CloneTable: input table \"%s\".\"%s\" does not exist\n",
1544
cloner->db_prefix, cloner->in_table);
1554
check_input_table_autoincrement (struct aux_cloner *cloner)
1556
/* exploring the input table AUTOINCREMENT property */
1565
if (cloner->pk_count != 1)
1568
xprefix = gaiaDoubleQuotedSql (cloner->db_prefix);
1569
sql = sqlite3_mprintf ("SELECT Count(*) FROM \"%s\".sqlite_sequence "
1570
"WHERE Lower(name) = Lower(%Q)",
1571
xprefix, cloner->in_table);
1574
sqlite3_get_table (cloner->sqlite, sql, &results, &rows, &columns,
1577
if (ret != SQLITE_OK)
1583
for (i = 1; i <= rows; i++)
1585
if (atoi (results[(i * columns) + 0]) > 0)
1586
cloner->autoincrement = 1;
1589
sqlite3_free_table (results);
1593
check_output_table_columns (struct aux_cloner *cloner)
1595
/* exploring the output table - Columns */
1605
xtable = gaiaDoubleQuotedSql (cloner->out_table);
1606
sql = sqlite3_mprintf ("PRAGMA main.table_info(\"%s\")", xtable);
1609
sqlite3_get_table (cloner->sqlite, sql, &results, &rows, &columns,
1612
if (ret != SQLITE_OK)
1618
for (i = 1; i <= rows; i++)
1620
name = results[(i * columns) + 1];
1621
mark_existing_column (cloner, name);
1624
sqlite3_free_table (results);
1628
expand_index (struct aux_cloner *cloner, struct aux_index *index)
1630
/* expanding an Index definitions */
1641
xprefix = gaiaDoubleQuotedSql (cloner->db_prefix);
1642
xindex = gaiaDoubleQuotedSql (index->name);
1643
sql = sqlite3_mprintf ("PRAGMA \"%s\".index_info(\"%s\")", xprefix, xindex);
1647
sqlite3_get_table (cloner->sqlite, sql, &results, &rows, &columns,
1650
if (ret != SQLITE_OK)
1656
for (i = 1; i <= rows; i++)
1658
name = results[(i * columns) + 2];
1659
add_index_column (index, cloner->first_col, name);
1662
sqlite3_free_table (results);
1666
check_input_table_index_defs (struct aux_cloner *cloner)
1668
/* exploring the input table - Index definitions */
1679
struct aux_index *idx;
1681
xprefix = gaiaDoubleQuotedSql (cloner->db_prefix);
1682
xtable = gaiaDoubleQuotedSql (cloner->in_table);
1683
sql = sqlite3_mprintf ("PRAGMA \"%s\".index_list(\"%s\")", xprefix, xtable);
1687
sqlite3_get_table (cloner->sqlite, sql, &results, &rows, &columns,
1690
if (ret != SQLITE_OK)
1696
for (i = 1; i <= rows; i++)
1698
name = results[(i * columns) + 1];
1699
if (strncasecmp (name, "sqlite_autoindex_", 17) == 0)
1701
/* ignoring any AUTOINDEX defined by SQLite */
1704
unique = atoi (results[(i * columns) + 2]);
1705
idx = add_index (cloner, name, unique);
1706
expand_index (cloner, idx);
1709
sqlite3_free_table (results);
1713
check_input_table_foreign_keys (struct aux_cloner *cloner)
1715
/* exploring the input table - Foreign Keys */
1723
const char *references;
1726
const char *on_update;
1727
const char *on_delete;
1732
xprefix = gaiaDoubleQuotedSql (cloner->db_prefix);
1733
xtable = gaiaDoubleQuotedSql (cloner->in_table);
1735
sqlite3_mprintf ("PRAGMA \"%s\".foreign_key_list(\"%s\")", xprefix,
1740
sqlite3_get_table (cloner->sqlite, sql, &results, &rows, &columns,
1743
if (ret != SQLITE_OK)
1749
for (i = 1; i <= rows; i++)
1751
id = atoi (results[(i * columns) + 0]);
1752
references = results[(i * columns) + 2];
1753
from = results[(i * columns) + 3];
1754
to = results[(i * columns) + 4];
1755
on_update = results[(i * columns) + 5];
1756
on_delete = results[(i * columns) + 6];
1757
match = results[(i * columns) + 7];
1758
if (strcasecmp (on_update, "NO ACTION") == 0)
1760
if (strcasecmp (on_delete, "NO ACTION") == 0)
1762
if (strcasecmp (match, "NONE") == 0)
1764
add_foreign_key (cloner, id, references, from, to, on_update,
1768
sqlite3_free_table (results);
1772
check_input_table_triggers (struct aux_cloner *cloner)
1774
/* exploring the input table - Triggers */
1785
xprefix = gaiaDoubleQuotedSql (cloner->db_prefix);
1786
sql = sqlite3_mprintf ("SELECT name, sql FROM \"%s\".sqlite_master "
1787
"WHERE type = 'trigger' AND Lower(tbl_name) = Lower(%Q)",
1788
xprefix, cloner->in_table);
1791
sqlite3_get_table (cloner->sqlite, sql, &results, &rows, &columns,
1794
if (ret != SQLITE_OK)
1800
for (i = 1; i <= rows; i++)
1802
name = results[(i * columns) + 0];
1803
sqlx = results[(i * columns) + 1];
1804
add_trigger (cloner, name, sqlx);
1807
sqlite3_free_table (results);
1811
check_input_table_geometries (struct aux_cloner *cloner)
1813
/* exploring the input table - Geometries */
1827
xprefix = gaiaDoubleQuotedSql (cloner->db_prefix);
1828
sql = sqlite3_mprintf ("SELECT f_geometry_column, geometry_type, "
1829
"coord_dimension, srid, spatial_index_enabled "
1830
"FROM \"%s\".geometry_columns "
1831
"WHERE Lower(f_table_name) = Lower(%Q)",
1832
xprefix, cloner->in_table);
1835
sqlite3_get_table (cloner->sqlite, sql, &results, &rows, &columns,
1838
if (ret != SQLITE_OK)
1844
for (i = 1; i <= rows; i++)
1846
name = results[(i * columns) + 0];
1847
type = atoi (results[(i * columns) + 1]);
1848
dims = atoi (results[(i * columns) + 2]);
1849
srid = atoi (results[(i * columns) + 3]);
1850
spatial_index = atoi (results[(i * columns) + 4]);
1851
add_geometry (cloner, name, type, dims, srid, spatial_index);
1854
sqlite3_free_table (results);
1858
check_output_table_geometries (struct aux_cloner *cloner)
1860
/* exploring the output table - Geometries */
1872
sql = sqlite3_mprintf ("SELECT f_geometry_column, geometry_type, "
1873
"coord_dimension, srid, spatial_index_enabled "
1874
"FROM main.geometry_columns "
1875
"WHERE Lower(f_table_name) = Lower(%Q)",
1878
sqlite3_get_table (cloner->sqlite, sql, &results, &rows, &columns,
1881
if (ret != SQLITE_OK)
1887
for (i = 1; i <= rows; i++)
1889
name = results[(i * columns) + 0];
1890
type = atoi (results[(i * columns) + 1]);
1891
dims = atoi (results[(i * columns) + 2]);
1892
srid = atoi (results[(i * columns) + 3]);
1893
mark_existing_geometry (cloner, name, type, dims, srid);
1896
sqlite3_free_table (results);
1900
free_trigger (struct aux_trigger *trigger)
1902
/* memory cleanup - destroying a Trigger object */
1903
if (trigger == NULL)
1905
if (trigger->name != NULL)
1906
free (trigger->name);
1907
if (trigger->sql != NULL)
1908
free (trigger->sql);
1913
free_fk_columns (struct aux_fk_columns *col)
1915
/* memory cleanup - destroying a Foreign Key Columns object */
1918
if (col->from != NULL)
1920
if (col->to != NULL)
1926
free_foreign_key (struct aux_foreign_key *fk)
1928
/* memory cleanup - destroying a Foreign Key object */
1929
struct aux_fk_columns *pc;
1930
struct aux_fk_columns *pcn;
1933
if (fk->name != NULL)
1935
if (fk->references != NULL)
1936
free (fk->references);
1937
if (fk->on_update != NULL)
1938
free (fk->on_update);
1939
if (fk->on_delete != NULL)
1940
free (fk->on_delete);
1941
if (fk->match != NULL)
1947
free_fk_columns (pc);
1954
free_index_column (struct aux_index_column *column)
1956
/* memory cleanup - destroying an Index Column object */
1959
if (column->name != NULL)
1960
free (column->name);
1965
free_index (struct aux_index *index)
1967
/* memory cleanup - destroying an Index object */
1968
struct aux_index_column *pc;
1969
struct aux_index_column *pcn;
1972
if (index->name != NULL)
1978
free_index_column (pc);
1985
free_column (struct aux_column *column)
1987
/* memory cleanup - destroying a Column object */
1990
if (column->name != NULL)
1991
free (column->name);
1992
if (column->type != NULL)
1993
free (column->type);
1994
if (column->deflt != NULL)
1995
free (column->deflt);
1996
if (column->geometry != NULL)
1997
free (column->geometry);
2002
free_cloner (struct aux_cloner *cloner)
2004
/* memory cleanup - destroying a Cloner object */
2005
struct aux_column *pc;
2006
struct aux_column *pcn;
2007
struct aux_pk_column *ppk;
2008
struct aux_pk_column *ppkn;
2009
struct aux_index *pi;
2010
struct aux_index *pin;
2011
struct aux_foreign_key *pfk;
2012
struct aux_foreign_key *pfkn;
2013
struct aux_trigger *ptrg;
2014
struct aux_trigger *ptrgn;
2017
if (cloner->db_prefix != NULL)
2018
free (cloner->db_prefix);
2019
if (cloner->in_table != NULL)
2020
free (cloner->in_table);
2021
if (cloner->out_table != NULL)
2022
free (cloner->out_table);
2023
pc = cloner->first_col;
2030
ppk = cloner->first_pk;
2037
pi = cloner->first_idx;
2044
pfk = cloner->first_fk;
2048
free_foreign_key (pfk);
2051
ptrg = cloner->first_trigger;
2052
while (ptrg != NULL)
2055
free_trigger (ptrg);
2058
if (cloner->sorted_pks != NULL)
2059
free (cloner->sorted_pks);
2064
already_existing_table (sqlite3 * sqlite, const char *table)
2066
/* testing if the target Table is already defined */
2075
sql = sqlite3_mprintf ("SELECT Count(*) FROM main.sqlite_master "
2076
"WHERE type = 'table' AND Lower(name) = Lower(%Q)",
2078
ret = sqlite3_get_table (sqlite, sql, &results, &rows, &columns, NULL);
2080
if (ret != SQLITE_OK)
2086
for (i = 1; i <= rows; i++)
2087
count = atoi (results[(i * columns) + 0]);
2089
sqlite3_free_table (results);
2094
SPATIALITE_PRIVATE const void *
2095
gaiaAuxClonerCreate (const void *sqlite, const char *db_prefix,
2096
const char *in_table, const char *out_table)
2098
/* creating a Cloner object */
2100
struct aux_cloner *cloner;
2101
if (checkSpatialMetaData ((sqlite3 *) sqlite) < 3)
2103
spatialite_e ("CloneTable: obsolete DB-layout (< 4.0.0)\n");
2107
cloner = malloc (sizeof (struct aux_cloner));
2110
cloner->sqlite = (sqlite3 *) sqlite;
2111
cloner->db_prefix = NULL;
2112
cloner->in_table = NULL;
2113
cloner->out_table = NULL;
2114
len = strlen (db_prefix);
2115
cloner->db_prefix = malloc (len + 1);
2116
strcpy (cloner->db_prefix, db_prefix);
2117
len = strlen (in_table);
2118
cloner->in_table = malloc (len + 1);
2119
strcpy (cloner->in_table, in_table);
2120
len = strlen (out_table);
2121
cloner->out_table = malloc (len + 1);
2122
strcpy (cloner->out_table, out_table);
2123
cloner->first_col = NULL;
2124
cloner->last_col = NULL;
2125
cloner->first_pk = NULL;
2126
cloner->last_pk = NULL;
2127
cloner->first_idx = NULL;
2128
cloner->last_idx = NULL;
2129
cloner->first_fk = NULL;
2130
cloner->last_fk = NULL;
2131
cloner->first_trigger = NULL;
2132
cloner->last_trigger = NULL;
2133
cloner->pk_count = 0;
2134
cloner->sorted_pks = NULL;
2135
cloner->autoincrement = 0;
2136
cloner->resequence = 0;
2137
cloner->with_fks = 0;
2138
cloner->with_triggers = 0;
2140
cloner->already_existing = 0;
2142
/* exploring the input table - Columns */
2143
if (!check_input_table_columns (cloner))
2145
/* exploring PRIMARY KEY AUTOINCREMENT */
2146
check_input_table_autoincrement (cloner);
2147
/* exploring the input table - Index definitions */
2148
check_input_table_index_defs (cloner);
2149
/* exploring the input table - Foreign Key definitions */
2150
check_input_table_foreign_keys (cloner);
2151
/* exploring the input table - Trigger definitions */
2152
check_input_table_triggers (cloner);
2153
/* exploring the input table - Geometry definitions */
2154
check_input_table_geometries (cloner);
2156
if (already_existing_table (cloner->sqlite, out_table))
2157
cloner->already_existing = 1;
2160
free_cloner (cloner);
2164
SPATIALITE_PRIVATE void
2165
gaiaAuxClonerDestroy (const void *handle)
2167
/* destroying a Cloner object */
2168
struct aux_cloner *cloner = (struct aux_cloner *) handle;
2171
free_cloner (cloner);
2175
ignore_column (struct aux_cloner *cloner, const char *column)
2177
/* marking a Column to be ignored */
2178
struct aux_column *pc = cloner->first_col;
2181
if (strcasecmp (pc->name, column) == 0)
2191
check_append (struct aux_cloner *cloner)
2193
/* cheching for APPEND validity */
2195
struct aux_column *column = cloner->first_col;
2196
while (column != NULL)
2198
if (column->mismatching)
2200
column = column->next;
2208
cast2multi_column (struct aux_cloner *cloner, const char *column)
2210
/* marking a Geometry Column to be casted to MultiType */
2211
struct aux_column *pc = cloner->first_col;
2214
if (strcasecmp (pc->name, column) == 0 && pc->geometry != NULL)
2216
pc->geometry->cast2multi = 1;
2223
SPATIALITE_PRIVATE void
2224
gaiaAuxClonerAddOption (const void *handle, const char *option)
2226
/* parsing an Option */
2227
struct aux_cloner *cloner = (struct aux_cloner *) handle;
2230
if (strncasecmp (option, "::ignore::", 10) == 0)
2231
ignore_column (cloner, option + 10);
2232
if (strncasecmp (option, "::cast2multi::", 14) == 0)
2233
cast2multi_column (cloner, option + 14);
2234
if (strncasecmp (option, "::resequence::", 14) == 0)
2235
cloner->resequence = 1;
2236
if (strncasecmp (option, "::with-foreign-keys::", 21) == 0)
2237
cloner->with_fks = 1;
2238
if (strncasecmp (option, "::with-triggers::", 17) == 0)
2239
cloner->with_triggers = 1;
2240
if (strncasecmp (option, "::append::", 10) == 0)
2243
cloner->resequence = 1;
2248
SPATIALITE_PRIVATE int
2249
gaiaAuxClonerCheckValidTarget (const void *handle)
2251
/* checking the Target Table for validity */
2252
struct aux_cloner *cloner = (struct aux_cloner *) handle;
2256
if (cloner->already_existing)
2260
/* exploring the output table - Columns */
2261
check_output_table_columns (cloner);
2262
/* exploring the output table - Geometries */
2263
check_output_table_geometries (cloner);
2264
/* checking for validity */
2265
if (!check_append (cloner))
2268
("CloneTable: output table \"%s\" can't support APPEND\n",
2276
("CloneTable: output table \"%s\" already exists and APPEND is not enabled\n",
2284
SPATIALITE_PRIVATE int
2285
gaiaAuxClonerExecute (const void *handle)
2287
/* executing the actual work */
2288
struct aux_cloner *cloner = (struct aux_cloner *) handle;
2291
if (cloner->already_existing)
2293
/* creating any further column if required */
2294
if (!upgrade_output_table (cloner))
2297
("CloneTable: unable to updgrade the output table \"%s\"\n",
2304
/* creating the output table */
2305
if (!create_output_table (cloner))
2308
("CloneTable: unable to create the output table \"%s\"\n",
2313
if (!copy_rows (cloner))
2315
spatialite_e ("CloneTable: unable to copy Table rows\n");