D 2014-08-28T11:05:01.319
L CloneTable
P 0d3082e342c8d167c2615c5d03b8ffc6753a187f
U sandro
W 6358
<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 NOT NULL,
pop_m_2011 INTEGER NOT NULL,
pop_f_2011 INTEGER NOT NULL);
</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 interted 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 specificy 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.
<h3>Tutorial step #2 (<i>advanced level</i>)</h3>
Z e9dd269df6366bc8f2df0f82133a92b9