Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Artifact ID: | 4a796e8e2b7c8b6893758a8f4af9fd8ec43a0251 |
|---|---|
| Page Name: | CloneTable |
| Date: | 2014-08-28 11:05:01 |
| Original User: | sandro |
| Parent: | 0d3082e342c8d167c2615c5d03b8ffc6753a187f (diff) |
| Next | b004a56dd683f09a2e949d0e781acc8eafee0fa6 |
Content
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 downloadexploring 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.