D 2014-08-28T11:05:01.319 L CloneTable P 0d3082e342c8d167c2615c5d03b8ffc6753a187f U sandro W 6358

quick how-to guide: using CloneTable()

Starting since version 4.2.1 a new CloneTable() 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 CloneTable().

downloading the sample DB

In this tutorial we'll use a sample DB available for download

exploring the sample DB

As you can easily check the sample DB contains the following Tables:

Table	Create Statement	Geometry Column	Index	Trigger
regions	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 );	none	none	none
prov_itc	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);	geom srid=32632 MULTIPOLYGON no Spatial Index	none	none
prov_itf	same as prov_itc
prov_itg
prov_ith
prov_iti
provinces	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));	geom srid=32632 MULTIPOLYGON Spatial Index	CREATE INDEX idx_prov_code ON provinces (code); CREATE INDEX idx_prov_name ON provinces (name);	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;

The sample DB is fully based on the 2011 Italian Census kindly released by ISTAT under CC-BY license.

Just few keypoints worth to be highlighted:

Both Regions and Provinces are coded accordingly to NUTS
NUTS level-2 and NUTS level-3 codes correspond to a relational hierarchy reflected by the Primary / Foreign Key relationships joining the regions and provinces Tables.
All Italian Provinces are contained within the tables prov_itc, prov_itf, prov_itg, prov_ith and prov_iti.
Each single Table directly corresponds to a NUTS level-1 code.
Table provinces (whole Italy) is instead completely empty (we'll see later the intended scope of this Table):
- this table is supported by two ordinary Indices.
- there is a Trigger firing each time that a new row will be interted into this Table.
  The intended scope of this Trigger is to immediately update the Population 2011 values stored into the regions Table.

Tutorial step #1 (basic level)

We'll use the sqlite3 CLI front-end tool in the following examples, but you could eventually use either spatialite or spatialite_gui. $ 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> we'll start by creating a new DB named test1.sqlite and by loading the mod_spatialite dynamic extension.
sqlite> SELECT InitSpatialMetadata(1); 1 sqlite> now we'll properly initialize the new DB by creating any required matatable. sqlite> ATTACH DATABASE "./clone_origin.sqlite" AS origin; sqlite> and finally we'll attach the clone_origin.sqlite sample DB: we are now ready for copying data between the two DB-files. sqlite> SELECT CloneTable('origin', 'regions', 'regions', 1); 1 sqlite>

the first argument 'origin' identifies the DB-prefix of the input Table.
If the input Table is located on the primary DB you simply have to specificy the 'main' conventional DB-prefix.
the second argument 'regions' identifies the input Table.
the third argument 'regions' identifies the output Table.
Please note: the output Table is always intended to be located on the main DB.
the fourth argument 1 simply declares that the whole create / copy (aka cloning) operation has to be atomically confined within a monolithic SQL Transaction.

sqlite> SELECT * FROM regions; as you can easily check a new regions table has been created (exactly corresponding to input definitions), and all rows have been copied.

Tutorial step #2 (advanced level)

Z e9dd269df6366bc8f2df0f82133a92b9