SpatiaLite: CloneTable

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 DEFAULT 0, pop_m_2011 INTEGER DEFAULT 0, pop_f_2011 INTEGER DEFAULT 0 );	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 inserted 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 specify 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.

sqlite> SELECT CloneTable('origin', 'prov_itc', 'provinces', 1,
          '::resequence::', '::ignore::pop_2011', '::ignore::pop_m_2011', '::ignore::pop_f_2011');
1
sqlite>

more or less this is the same as above: but in this case further options gave been specified.
The CloneTable() SQL functions accepts a maximum of 10 options all presenting the following canonical form: ::option_name::column_name.
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.

sqlite> SELECT * FROM regions;

as you can directly check:

the ::resequence:: option has caused that Primary Key values have been resequenced starting from 1 and avoiding any void interval.
Please note: it simply works for Primary Keys based on a single column of the INTEGER AUTOINCREMENT type and will be ignored on any other case.
each ::ignore::column option instead has caused that pop_2011, pop_m_2011 and pop_f_2011 columns have been completely ignored.

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>

more or less as before; we'll simply read data from prov_ith, prov_iti and prov_itf input Tables still continuing to use the provinces Table as the output target.
Anyway in this case we've specified the ::append:: option; this is because CloneTable() has the capability to append data into an already existing output table, but strictly requires this explicit authorization.
Please note: ::append:: always implies ::resequence::, so there is no need to specify this second option.

sqlite> SELECT CloneTable('origin', 'prov_itg', 'provinces', 1, '::append::');
1
sqlite>

in this last call to CloneTable() we've intentionally suppressed all the ::ignore:: options.

sqlite> SELECT * FROM regions;

as you can directly check CloneTable() 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.

sqlite> DETACH DATABASE origin;
sqlite> .quit
$

finished ... this first tutorial stops here.

Tutorial step #2 (advanced level)

This second tutorial is very similar to the previous one, but is intended to explore more complex and sophisticated SQL features.

$ 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>

Exactly as in the previous tutorial we'll start by creating and initializing a new empty DB test2.sqlite (output destination) then attaching clone_origin.sqlite (input origin).

sqlite> SELECT CloneTable('origin', 'regions', 'regions', 1);
1
sqlite>

first we'll clone the regions Table.

sqlite> SELECT CloneTable('origin', 'provinces', 'provinces', 1,
                    '::with-foreign-keys::', '::with-triggers::');
sqlite>

then we'll clone the provinces 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 template, so to ensure that a new Table presenting exactly the same identical layout will be created on the destination side.

the ::with-foreign-keys:: option specifies that any Foreign Key defined in the origin will be recreated on the destination.
By default CloneTable() 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.
the same consideration apply to Triggers; CloneTable() will usually ignore any Trigger definition, but you can anyway explicit request for their duplication by specifying the ::with-triggers:: option.
Please note: 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.

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>

this final series of CloneTable() calls will effectively populate the provinces Table; passing the ::append:: option is a mandatory requisite, because the destination Table was been created in a previous step.

sqlite> SELECT * FROM regions;

final check: all 2011 Population values are now correctly reported for each Region. The Trigger did is work as expected.

sqlite> DETACH DATABASE origin;
sqlite> .quit
$

finished ... close and quit.

options supported by CloneTable()

option name	action	notes
::with-foreign-keys::	while creating the destination Table all Foreign Keys defined by the origin will be duplicated. (this may eventually cause subsequent failures if the referenced Table doesn't effectively exist).
::with-triggers::	while creating the destination Table all Triggers defined by the origin will be duplicated. (this may eventually cause subsequent failures if the Trigger's action references some not existing Table or Column).
::append::	assumes that the destination Table has been already created in a previous step. Additional Columns may be automatically added if required.	Mismatching column declarations or SQL constraints may lead to an eventual fail. 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.
::resequence::	resets from beginning the sequence of values used by a Primary Key of the INTEGER AUTOINCREMENT type. Any other different type of Primary Key lacking the AUTOINCREMENT clause will be unaffected.	potentially harmful option if some further Foreign Key insists on that Primary Key
::ignore::column_name	Shadows a column present in the origin Table as if it was not existing.	this option will be always ignored for any column directly referenced by a Primary or Foreign Key or even by an Index.
::cast2multi::geometry_column	Forces a Geometry to be promoted to its MULTI-type equivalent.	See the documentation about the CastToMulti() SQL function for more details.

useful hint: circumventing the ALTER TABLE DROP COLUMN issue

SQLite notoriously doesn't support the ALTER TABLE DROP COLUMN statement; CloneTable() can be easily used in order to circumvent this limitation.

SELECT CloneTable('main', 'old_table', 'new_table', 1, '::ignore::foo');
DROP TABLE old_table;