SELECT DateTime('now'),'export_ogc_comuni.sql [begin]';
SELECT DateTime('now'),'sample: rm export_ogc_comuni.db ; spatialite export_ogc_comuni.db < export_ogc_comuni.sql';
---
SELECT DateTime('now'),'The sample Database tuscany-admin.sqlite can be found at:';
SELECT DateTime('now'),'-> https://www.gaia-gis.it/gaia-sins/toposamples/tuscany-admin.7z';
---
SELECT DateTime('now'),'ATTACHing tuscany-admin.sqlite.';
ATTACH DATABASE 'tuscany-admin.sqlite' AS db_import;
--- PRAGMA ddb_import.recursive_triggers = on;
---
SELECT DateTime('now'),'CREATE TABLE export_ogc_comuni';
CREATE TABLE export_ogc_comuni
(
 com_id INTEGER,
 codcom TEXT,
 codpro TEXT,
 codcatasto TEXT,
 nome TEXT,
 geom_type TEXT,
 geom_srid  INTEGER,
 ogc_geom  BLOB
);
---
SELECT DateTime('now'),'INSERTing geom: convert Spatialite-Geometry to OGC-Geometry.';
SELECT DateTime('now'),'- storing the Geometry: Type and SRID for later sanity checks';
INSERT INTO export_ogc_comuni 
 SELECT
  com_id,
  codcom,
  codpro,
  codcatasto,
  nome, 
  -- MS Sql-Server R2: 'geom.STGeometryType()'
  ST_GeometryType(geom) AS geom_type,
  -- MS Sql-Server R2: 'geom.STSrid()'
  ST_SRID(geom) AS geom_srid,
  -- MS Sql-Server R2: 'geom.STAsBinary()'
  ST_AsBinary(geom) AS ogc_geom
 FROM
  db_import.comuni;
---
SELECT DateTime('now'),'DETACH DATABASE ''tuscany-admin.sqlite'' AS db_import';
DETACH DATABASE db_import;
---
SELECT DateTime('now'),'CREATE TABLE with INSERT Statements';
SELECT DateTime('now'),'- for Postgres: use "||com_id:text||" instead of "||com_id||"';
SELECT DateTime('now'),'- for MS Sql-Server R2: use "+com_id+" instead of "||com_id||"'; 
SELECT DateTime('now'),'- for Postgres: use "encode(ogc_geom,''hex'')" instead of "hex(ogc_geom)"';
SELECT DateTime('now'),'- for MS Sql-Server R2: use "convert(varchar(max), @ogc_geom, 2)" instead of "hex(ogc_geom)"';
CREATE TABLE sql_insert_ogc_comuni AS
 SELECT
 'INSERT INTO  import_ogc_comuni VALUES('
  ||com_id||',"'
  ||codcom||'","'
  ||codpro||'","'
  ||codcatasto||'","'
  ||nome||'","'
  ||geom_type||'",'
  ||geom_srid||',"'
  ||hex(ogc_geom)||'");' AS insert_sql
 FROM
  export_ogc_comuni;
---
SELECT DateTime('now'),'sql_insert_ogc_comuni contains'||(SELECT count(insert_sql) FROM sql_insert_ogc_comuni)||' records.';
SELECT DateTime('now'),'- Export the column "insert_sql" to a text-file, with something like: "SELECT insert_sql FROM sql_insert_ogc_comuni"';
SELECT DateTime('now'),'--> sqlite3 export_ogc_comuni.db < select.comuni_imported.sql > insert.comuni_imported.sql';
---
SELECT DateTime('now'),'export_ogc_comuni.sql [finished] [Habe fertig!]';
---
