-- rm import_ogc_comuni.db ; spatialite import_ogc_comuni.db < create.comuni_imported.sql
SELECT DateTime('now'),'import_ogc_comuni.sql [begin] ';
SELECT DateTime('now'),'CREATE TABLE import_ogc_comuni';
BEGIN;
CREATE TABLE import_ogc_comuni
(
 com_id INTEGER,
 codcom TEXT,
 codpro TEXT,
 codcatasto TEXT,
 nome TEXT,
 geom_type TEXT,
 geom_srid INTEGER,
 hex_ogc_geom  TEXT
);
---
SELECT DateTime('now'),'INSERTing INTO TABLE import_ogc_comuni';
.read insert.comuni_imported.sql UTF-8
---
SELECT DateTime('now'),'UPDATING INTEGERs with CASTing TO INTEGER (just in case it is considere a TEXT field)';
UPDATE import_ogc_comuni SET
 com_id=CAST(com_id AS INTEGER),
 geom_srid=CAST(geom_srid AS INTEGER);
---
SELECT DateTime('now'),'ADD COLUMN of 2 BLOBS(which the importing scripts know nothing about)';
ALTER TABLE import_ogc_comuni ADD COLUMN ogc_geom BLOB;
ALTER TABLE import_ogc_comuni ADD COLUMN spatialite_geom  BLOB;
---
SELECT DateTime('now'),'UPDATE ogc_geom: convert Hex to binary OGC-Geometry ';
UPDATE import_ogc_comuni SET ogc_geom=CastToBlob(hex_ogc_geom,1);
---
SELECT DateTime('now'),'UPDATE spatialite_geom: convert OGC-Geometry to Spatialite-Geometry';
UPDATE import_ogc_comuni SET spatialite_geom=ST_GeomFromWKB(ogc_geom,geom_srid);
---
SELECT
 "Sanity-check: all values MUST be the same:" AS text,
 count(geom_srid) AS count_records,
 (SELECT count(spatialite_geom) FROM import_ogc_comuni AS test_type WHERE ST_GeometryType(test_type.spatialite_geom)=import_ogc_comuni.geom_type) AS count_type,
 (SELECT count(spatialite_geom) FROM import_ogc_comuni AS test_srid WHERE ST_SRID(test_srid.spatialite_geom)=import_ogc_comuni.geom_srid) AS count_srid
FROM 
 import_ogc_comuni; 
---
SELECT DateTime('now'),'CREATE TABLE comuni_imported (final TABLE)';
CREATE TABLE comuni_imported 
(
 com_id INTEGER NOT NULL PRIMARY KEY,
 codcom TEXT NOT NULL,
 codpro TEXT NOT NULL,
 codcatasto TEXT NOT NULL,
 nome TEXT NOT NULL
);
---
SELECT AddGeometryColumn('comuni_imported','geom',3003,'MULTIPOLYGON','XY');
SELECT CreateSpatialIndex('comuni_imported','geom');
---
SELECT DateTime('now'),'INSERTing imported data into comuni_imported';
INSERT INTO comuni_imported 
 SELECT
  com_id,
  codcom,
  codpro,
  codcatasto,
  nome,
  spatialite_geom AS geom
 FROM
  import_ogc_comuni;
---
SELECT DateTime('now'),'UpdateLayerStatistics: comuni_imported.geom';
SELECT UpdateLayerStatistics('omuni_imported','geom');
---
-- SE_UpdateVectorCoverageExtent is only needed, when RasterLite2 is being used
-- SELECT SE_UpdateVectorCoverageExtent(1);
---
SELECT DateTime('now'),'DROPing TABLE import_ogc_comuni (comment out if there are errors)';
-- DROP TABLE IF EXISTS sql_insert_ogc_comuni;
---
COMMIT;
---
SELECT DateTime('now'),'VACUUM';
VACUUM;
SELECT DateTime('now'),'import_ogc_comuni.sql [finished] [Habe fertig!] ';
---
