D 2014-08-28T16:02:55.176
L CloneTable
P 4a796e8e2b7c8b6893758a8f4af9fd8ec43a0251
U sandro
W 14886
<h2>quick how-to guide: using CloneTable()</h2>
Starting since version <b>4.2.1</b> a new <b>CloneTable()</b> SQL function is now available; this new function is specifically intended to simplify copying data between different Tables and (may be) between different DB-files.
Several advanced option intended to support flexible behaviors are supported, so reading this quick tutorial is strongly suggested in order to clarify any possible doubt about <b>CloneTable()</b>.
<h4>downloading the sample DB</h4>
In this tutorial we'll use a sample DB available for <a href="http://www.gaia-gis.it/gaia-sins/clone_table/clone_origin.zip">download</a>
<h4>exploring the sample DB</h4>
As you can easily check the sample DB contains the following Tables:<br><br>
<table width="100%" cellspacing="2" cellpadding="6" border="1" bgcolor="#f0fff0">
<tr><th bgcolor="#ffdda0">Table</th><th bgcolor="#ffdda0">Create Statement</th><th bgcolor="#ffdda0">Geometry Column</th><th bgcolor="#ffdda0">Index</th><th bgcolor="#ffdda0">Trigger</th></tr>
<tr><td><b>regions</b></td>
<td><verbatim>
CREATE TABLE regions (
macro_region TEXT NOT NULL,
nuts_1 TEXT NOT NULL,
nuts_2 TEXT NOT NULL PRIMARY KEY,
region TEXT NOT NULL,
pop_2011 INTEGER NOT NULL,
pop_m_2011 INTEGER NOT NULL,
pop_f_2011 INTEGER NOT NULL
);
</verbatim></td><td><i>none</i></td><td><i>none</i></td><td><i>none</i></td></tr>
<tr><td><b>prov_itc</b></td>
<td><verbatim>
CREATE TABLE prov_itc (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nuts_2 TEXT NOT NULL,
nuts_3 TEXT NOT NULL,
province TEXT NOT NULL,
code TEXT NOT NULL,
pop_2011 INTEGER DEFAULT 0,
pop_m_2011 INTEGER DEFAULT 0,
pop_f_2011 INTEGER DEFAULT 0
);
</verbatim></td><td><b>geom</b><br><br>srid=<b>32632</b><br><b>MULTIPOLYGON</b><br><br><i>no Spatial Index</i></td>
<td><i>none</i></td><td><i>none</i></td></tr>
<tr><td><b>prov_itf</b></td><td colspan="4" rowspan="4" align="center"><i>same as <b>prov_itc</b></td></tr>
<tr><td><b>prov_itg</b></td></tr>
<tr><td><b>prov_ith</b></td></tr>
<tr><td><b>prov_iti</b></td></tr>
<tr><td><b>provinces</b></td>
<td><verbatim>
CREATE TABLE provinces (
nuts_2 TEXT NOT NULL,
nuts_3 TEXT NOT NULL PRIMARY KEY,
province TEXT NOT NULL,
code TEXT NOT NULL,
pop_2011 INTEGER NOT NULL,
pop_m_2011 INTEGER NOT NULL,
pop_f_2011 INTEGER NOT NULL,
CONSTRAINT fk_prov_reg
FOREIGN KEY (nuts_2)
REFERENCES regions (nuts_2));
</verbatim></td><td><b>geom</b><br><br>srid=<b>32632</b><br><b>MULTIPOLYGON</b><br><br><b>Spatial Index</b></td>
<td><verbatim>
CREATE INDEX idx_prov_code
ON provinces (code);
CREATE INDEX idx_prov_name
ON provinces (name);
</verbatim></td><td><verbatim>
CREATE TRIGGER ins_prov
AFTER INSERT ON provinces
BEGIN
UPDATE regions
SET pop_2011 = pop_2011 + NEW.pop_2011,
pop_m_2011 = pop_m_2011 + NEW.pop_m_2011,
pop_f_2011 = pop_f_2011 + NEW.pop_f_2011
WHERE nuts_2 = NEW.nuts_2;
END;
</verbatim></td></tr>
</table><br>
The sample DB is fully based on the 2011 Italian Census kindly released by <a href="http://www.istat.it/it/archivio/104317">ISTAT</a> under <a href="https://creativecommons.org/licenses/by/3.0/">CC-BY license</a>.<br><br>
Just few keypoints worth to be highlighted:
<ul>
<li>Both Regions and Provinces are coded accordingly to <a href="http://en.wikipedia.org/wiki/Nomenclature_of_Territorial_Units_for_Statistics">NUTS</a><br>
<b>NUTS level-2</b> and <b>NUTS level-3</b> codes correspond to a relational hierarchy reflected by the <b>Primary</b> / <b>Foreign Key</b> relationships joining the <b>regions</b> and <b>provinces</b> Tables.</li>
<li>All Italian Provinces are contained within the tables <b>prov_itc</b>, <b>prov_itf</b>, <b>prov_itg</b>, <b>prov_ith</b> and <b>prov_iti</b>.<br>
Each single Table directly corresponds to a <b>NUTS level-1</b> code.</li>
<li>Table <b>provinces</b> (whole Italy) is instead completely empty (<i>we'll see later the intended scope of this Table</i>):
<ul>
<li>this table is supported by two ordinary Indices.</li>
<li>there is a Trigger firing each time that a new row will be inserted into this Table.<br>
The intended scope of this Trigger is to immediately update the Population 2011 values stored into the <b>regions</b> Table.</li>
</ul></li>
</ul><br>
<hr>
<h3>Tutorial step #1 (<i>basic level</i>)</h3>
We'll use the <b>sqlite3</b> CLI front-end tool in the following examples, but you could eventually use either <b>spatialite</b> or <b>spatialite_gui</b>.
<verbatim>
$ sqlite3 test1.sqlite
SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
sqlite> SELECT load_extension('mod_spatialite');
sqlite>
</verbatim>
we'll start by creating a new DB named <b>test1.sqlite</b> and by loading the <b>mod_spatialite</b> dynamic extension.<br>
<verbatim>
sqlite> SELECT InitSpatialMetadata(1);
1
sqlite>
</verbatim>
now we'll properly initialize the new DB by creating any required matatable.
<verbatim>
sqlite> ATTACH DATABASE "./clone_origin.sqlite" AS origin;
sqlite>
</verbatim>
and finally we'll attach the <b>clone_origin.sqlite</b> sample DB: we are now ready for copying data between the two DB-files.
<verbatim>
sqlite> SELECT CloneTable('origin', 'regions', 'regions', 1);
1
sqlite>
</verbatim>
<ul>
<li>the first argument <b>'origin'</b> identifies the <b><i>DB-prefix</i></b> of the input Table.<br>
If the input Table is located on the primary DB you simply have to specify the <b>'main'</b> conventional DB-prefix.</li>
<li>the second argument <b>'regions'</b> identifies the <b><i>input Table</i></b>.</li>
<li>the third argument <b>'regions'</b> identifies the <b><i>output Table</i></b>.<br>
<u>Please note</u>: the output Table is always intended to be located on the <b>main</b> DB.</li>
<li>the fourth argument <b>1</b> simply declares that the whole <u>create / copy</u> (<i>aka</i> <u>cloning</u>) operation has to be atomically confined within a monolithic SQL Transaction.</li>
</ul>
<verbatim>
sqlite> SELECT * FROM regions;
</verbatim>
as you can easily check a new <b>regions</b> table has been created (exactly corresponding to input definitions), and all rows have been copied.
<verbatim>
sqlite> SELECT CloneTable('origin', 'prov_itc', 'provinces', 1,
'::resequence::', '::ignore::pop_2011', '::ignore::pop_m_2011', '::ignore::pop_f_2011');
1
sqlite>
</verbatim>
more or less this is the same as above: but in this case further <b>options</b> gave been specified.<br>
The <b>CloneTable()</b> SQL functions accepts a maximum of 10 options all presenting the following canonical form: <b>::option_name::</b><i>column_name</i>.<br>
The first option corresponds to argument #5 and so on: badly formatted or meaningless / not applicable directives will be silently discarded without any further consequence.
<verbatim>
sqlite> SELECT * FROM regions;
</verbatim>
as you can directly check:
<ul>
<li>the <b>::resequence::</b> option has caused that Primary Key values have been resequenced starting from 1 and avoiding any void interval.<br>
<u>Please note</u>: it simply works for Primary Keys based on a single column of the <b>INTEGER AUTOINCREMENT</b> type and will be ignored on any other case.</li>
<li>each <b>::ignore::</b><i>column</i> option instead has caused that <b>pop_2011</b>, <b>pop_m_2011</b> and <b>pop_f_2011</b> columns have been completely ignored.</li>
</ul>
<verbatim>
sqlite> SELECT CloneTable('origin', 'prov_ith', 'provinces', 1,
'::append::', '::ignore::pop_2011', '::ignore::pop_m_2011', '::ignore::pop_f_2011');
1
sqlite> SELECT CloneTable('origin', 'prov_iti', 'provinces', 1,
'::append::', '::ignore::pop_2011', '::ignore::pop_m_2011', '::ignore::pop_f_2011');
1
sqlite> SELECT CloneTable('origin', 'prov_itf', 'provinces', 1,
'::append::', '::ignore::pop_2011', '::ignore::pop_m_2011', '::ignore::pop_f_2011');
1
sqlite>
</verbatim>
more or less as before; we'll simply read data from <b>prov_ith</b>, <b>prov_iti</b> and <b>prov_itf</b> input Tables still continuing to use the <b>provinces</b> Table as the output target.<br>
Anyway in this case we've specified the <b>::append::</b> option; this is because <b>CloneTable()</b> has the capability to append data into an already existing output table, but strictly requires this explicit authorization.<br>
<u>Please note</u>: <b>::append::</b> always implies <b>::resequence::</b>, so there is no need to specify this second option.
<verbatim>sqlite> SELECT CloneTable('origin', 'prov_itg', 'provinces', 1, '::append::');
1
sqlite>
</verbatim>
in this last call to <b>CloneTable()</b> we've intentionally suppressed all the <b>::ignore::</b> options.
<verbatim>
sqlite> SELECT * FROM regions;
</verbatim>
as you can directly check <b>CloneTable()</b> has detected that the output Table lacked some of the required columns, and all them have been automatically created on the fly before copying any data.
<verbatim>
sqlite> DETACH DATABASE origin;
sqlite> .quit
$
</verbatim>
finished ... this first tutorial stops here.
<br><br><hr>
<h3>Tutorial step #2 (<i>advanced level</i>)</h3>
This second tutorial is very similar to the previous one, but is intended to explore more complex and sophisticated SQL features.
<verbatim>
$ sqlite3 test2.sqlite
SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
sqlite> SELECT load_extension('mod_spatialite');
sqlite> SELECT InitSpatialMetadata(1);
1
sqlite> ATTACH DATABASE "./clone_origin.sqlite" AS origin;
sqlite>
</verbatim>
Exactly as in the previous tutorial we'll start by creating and initializing a new empty DB <b>test2.sqlite</b> (output destination) then attaching <b>clone_origin.sqlite</b> (input origin).
<verbatim>
sqlite> SELECT CloneTable('origin', 'regions', 'regions', 1);
1
sqlite>
</verbatim>
first we'll clone the <b>regions</b> Table.
<verbatim>sqlite> SELECT CloneTable('origin', 'provinces', 'provinces', 1,
'::with-foreign-keys::', '::with-triggers::');
sqlite>
</verbatim>
then we'll clone the <b>provinces</b> Table. As you surely remember this Table is completely empty (it contains no rows); but it's intended scope is the one to act as kind of <i><b>template</b></i>, so to ensure that a new Table presenting exactly the same identical layout will be created on the destination side.
<ul>
<li>the <b>::with-foreign-keys::</b> option specifies that any Foreign Key defined in the origin will be recreated on the destination.<br>
By default <b>CloneTable()</b> never attempts to recreate Foreign Keys, because they completely relies upon other Tables that could be eventually defined or not. Anyway you can explicitly request for their duplication whenever you think this is an useful and legitimate action.</li>
<li>the same consideration apply to Triggers; <b>CloneTable()</b> will usually ignore any Trigger definition, but you can anyway explicit request for their duplication by specifying the <b>::with-triggers::</b> option.</li>
<li><u>Please note</u>: any Index (or Spatial Index) defined by the origin will always be replicated into the destination because this doesn't involves any interaction with different Tables.</li>
</ul>
<verbatim>sqlite> SELECT CloneTable('origin', 'prov_itc', 'provinces', 1, '::append::');
1
<verbatim>sqlite> SELECT CloneTable('origin', 'prov_ith', 'provinces', 1, '::append::');
1
<verbatim>sqlite> SELECT CloneTable('origin', 'prov_iti', 'provinces', 1, '::append::');
1
<verbatim>sqlite> SELECT CloneTable('origin', 'prov_itf', 'provinces', 1, '::append::');
1
<verbatim>sqlite> SELECT CloneTable('origin', 'prov_itg', 'provinces', 1, '::append::');
1
sqlite>
</verbatim>
this final series of <b>CloneTable()</b> calls will effectively populate the <b>provinces</b> Table; passing the <b>::append::</b> option is a mandatory requisite, because the destination Table was been created in a previous step.
<verbatim>
sqlite> SELECT * FROM regions;
</verbatim>
final check: all 2011 Population values are now correctly reported for each Region. The Trigger did is work as expected.
<verbatim>
sqlite> DETACH DATABASE origin;
sqlite> .quit
$
</verbatim>
finished ... close and quit.
<br><br><hr>
<h3>options supported by CloneTable()</h3>
<table width="100%" border="1" cellspacing="2" cellpadding="8" bgcolor="#fffff0">
<tr><th bgcolor="#d0d0d0">option name</b></th><th bgcolor="#d0d0d0" width="40%">action</th><th bgcolor="#d0d0d0" width="40%">notes</th></tr>
<tr><td><b>::with-foreign-keys::</b></td><td>while creating the destination Table all Foreign Keys defined by the origin will be duplicated.<br>
(<i>this may eventually cause subsequent failures if the referenced Table doesn't effectively exist</i>).</td><td></td></tr>
<tr><td><b>::with-triggers::</b></td><td>while creating the destination Table all Triggers defined by the origin will be duplicated.<br>
(<i>this may eventually cause subsequent failures if the Trigger's action references some not existing Table or Column</i>).</td><td></td></tr>
<tr><td><b>::append::</b></td><td>assumes that the destination Table has been already created in a previous step.<br>
Additional Columns may be automatically added if required.</td>
<td>Mismatching column declarations or SQL constraints may lead to an eventual fail.<br>
e.g. this will usually happen when an ordinary column is already defined and creating a Geometry of the same identical name is strictly required.</td></tr>
<tr><td><b>::resequence::</b></td><td>resets from beginning the sequence of values used by a Primary Key of the <b>INTEGER AUTOINCREMENT</b> type.<br>
Any other different type of Primary Key lacking the <b>AUTOINCREMENT</b> clause will be unaffected.</td>
<td>potentially harmful option if some further Foreign Key insists on that Primary Key</td></tr>
<tr><td><b>::ignore::</b><i>column_name</i></td><td>Shadows a column present in the origin Table as if it was not existing.</td>
<td>this option will be always ignored for any column directly referenced by a Primary or Foreign Key or even by an Index.</td><tr>
<tr><td><b>::cast2multi::</b><i>geometry_name</i></td><td>Forces a Geometry to be promoted to its <b>MULTI-type</b> equivalent.</td>
<td>See the documentation about the <b>CastToMulti()</b> SQL function for more details.</td></tr>
</table><br>
<hr>
<h3>useful hint: circumventing the <u>ALTER TABLE DROP COLUMN</u> issue</h3>
SQLite notoriously doesn't support the <b>ALTER TABLE DROP COLUMN</b> statement; <b>CloneTable()</b> can be easily used in order to circumvent this limitation.
<verbatim>
SELECT CloneTable('main', 'old_table', 'new_table', 1, '::ignore::foo');
DROP TABLE old_table;
</verbatim>
Z 3d6f2e2703340946553938ec2d6b18a4