-- spatialite gdal_views.db < gdal_views.sql
SELECT DateTime('now'),'gdal_views.sql [begin] - Testing SpatialViews INSERT,UPDATE and DELETE Functionality.';
SELECT DateTime('now'),'CREATINGing positions TABLE';
CREATE TABLE positions
(
 id_admin INTEGER  PRIMARY KEY AUTOINCREMENT,
 name TEXT,  notes TEXT,
 valid_since DATE DEFAULT '0001-01-01',
 valid_until DATE DEFAULT '3000-01-01'
);
---
SELECT DateTime('now'),'CREATINGing wsg84_center GEOMETRY';
SELECT AddGeometryColumn('positions','wsg84_center',4326,'POINT','XY');
SELECT CreateSpatialIndex('positions','wsg84_center');
---
INSERT INTO positions (name,notes,valid_since,valid_until, wsg84_center) VALUES ('Brandenburger Tor','Pariser Platz','1791-08-06','3000-01-01',GeomFromText('POINT(13.3777045509583 52.51627039900002)',4326));
---
SELECT DateTime('now'),'CREATINGing positions_1900 VIEW with INSERT TRIGGER only';
CREATE VIEW IF NOT EXISTS positions_1900 AS
 SELECT * FROM positions WHERE ('1900-01-01' BETWEEN valid_since AND valid_until);
INSERT INTO views_geometry_columns
 (view_name,view_geometry,view_rowid,f_table_name,f_geometry_column,read_only)
 VALUES ('positions_1900','wsg84_center','id_admin','positions','wsg84_center',0);
 ---
CREATE TRIGGER vw_ins_positions_1900
 INSTEAD OF INSERT ON positions_1900
BEGIN
 INSERT OR REPLACE INTO positions
  (id_admin,name,notes,valid_since,valid_until,wsg84_center)
 VALUES(NEW.id_admin,NEW.name,NEW.notes,
  NEW.valid_since,NEW.valid_until,NEW.wsg84_center);
END;
---
SELECT DateTime('now'),'INSERTing Siegessäule - Königs Platz into positions_1900 VIEW  - TRIGGER INSERT TEST';
INSERT INTO positions_1900 (name,notes,valid_since,valid_until, wsg84_center) VALUES ('Siegessäule','Königs Platz','1873-09-02','1938-12-31',GeomFromText('POINT(13.37217053204385 52.51855407940274)',4326));
SELECT UpdateLayerStatistics('positions_1900','wsg84_center');
---
CREATE VIEW IF NOT EXISTS positions_1950 AS
 SELECT * FROM positions WHERE ('1950-01-01' BETWEEN valid_since AND valid_until);
INSERT INTO views_geometry_columns
 (view_name,view_geometry,view_rowid,f_table_name,f_geometry_column,read_only)
 VALUES ('positions_1950','wsg84_center','id_admin','positions','wsg84_center',0);
---
SELECT DateTime('now'),'CREATINGing positions_1950 VIEW with INSERT,UPDATE and DELETE TRIGGERS';
CREATE TRIGGER vw_ins_positions_1950
 INSTEAD OF INSERT ON positions_1950
BEGIN
 INSERT OR REPLACE INTO positions
  (id_admin,name,notes,valid_since,valid_until,wsg84_center)
 VALUES(NEW.id_admin,NEW.name,NEW.notes,
  NEW.valid_since,NEW.valid_until,NEW.wsg84_center);
END;
---
CREATE TRIGGER vw_upd_positions_1950
 INSTEAD OF UPDATE OF 
  name,notes,valid_since,valid_until,wsg84_center
 ON positions_1950
BEGIN
 UPDATE positions
 SET 
  name = NEW.name, 
  notes = NEW.notes,
  valid_since = NEW.valid_since, 
  valid_until = NEW.valid_until, 
  wsg84_center = NEW.wsg84_center
  -- the primary key known to the view ḿust be used
 WHERE id_admin = OLD.id_admin;
END;
---
CREATE TRIGGER vw_del_positions_1950
 INSTEAD OF DELETE ON 
 positions_1950
BEGIN
 DELETE FROM positions WHERE id_admin = OLD.id_admin;
END;
--
SELECT DateTime('now'),'INSERTing Siegessäule - Große Stern into positions_1950 VIEW  - TRIGGER INSERT TEST';
INSERT INTO positions_1950 (name,notes,valid_since,valid_until, wsg84_center) VALUES ('Siegessäule','Große Stern','1939-01-01','3000-01-01',GeomFromText('POINT(13.350082397827691 52.514520477567764)',4326));
--
SELECT DateTime('now'),'UpdateLayerStatistics: wsg84_center';
SELECT UpdateLayerStatistics('positions_1950','wsg84_center');
SELECT UpdateLayerStatistics('positions','wsg84_center');
--
SELECT DateTime('now'),'Statistics: of Table,Views: should be [3,2,2 - with 3 unique name-notes and positions]';
SELECT DateTime('now'),'count_positions('||(SELECT count(*) FROM positions)||') ; count_positions_1900('||(SELECT count(*) FROM positions_1900)||') ; count_positions_1950('||(SELECT count(*) FROM positions_1950)||')' AS result_count;
SELECT DateTime('now'),'DISTINCT name and notes('||count(DISTINCT name||notes)||') positions('||count(DISTINCT wsg84_center)||') from positions'  AS distinct_results  FROM positions;
SELECT DateTime('now'),'SELECT: of positions_1900';
SELECT DateTime('now'),'SELECT: of positions_1900';
SELECT '1900',name,notes,valid_since,valid_until, AsEWKT(wsg84_center)  FROM positions_1900;
SELECT DateTime('now'),'SELECT: of positions_1950';
SELECT '1950',name,notes,valid_since,valid_until, AsEWKT(wsg84_center)  FROM positions_1950;
--
SELECT DateTime('now'),'Replacing Siegessäule position of 1939 with Original Position of 1873 - TRIGGER UPDATE TEST';
BEGIN;
--
UPDATE positions_1950 
 SET wsg84_center=
 (
  SELECT wsg84_center FROM positions_1900 WHERE (name = 'Siegessäule')
 ),
 notes=
 (
  SELECT notes FROM positions_1900 WHERE (name = 'Siegessäule')
 )
WHERE (name = 'Siegessäule');
--
SELECT DateTime('now'),'Statistics: of Table,Views after UPDATE:  should be [3,2,2 - with 2 unique name-notes and positions]';
SELECT DateTime('now'),'count_positions('||(SELECT count(*) FROM positions)||') ; count_positions_1900('||(SELECT count(*) FROM positions_1900)||') ; count_positions_1950('||(SELECT count(*) FROM positions_1950)||')' AS result_count;
SELECT DateTime('now'),'DISTINCT name and notes('||count(DISTINCT name||notes)||') positions('||count(DISTINCT wsg84_center)||') from positions'  AS distinct_results  FROM positions;
SELECT DateTime('now'),'SELECT: of positions_1900';
SELECT '1900',name,notes,valid_since,valid_until, AsEWKT(wsg84_center)  FROM positions_1900;
SELECT DateTime('now'),'SELECT: of positions_1950';
SELECT '1950',name,notes,valid_since,valid_until, AsEWKT(wsg84_center)  FROM positions_1950;
--
SELECT DateTime('now'),'DELETE Siegessäule from positions_1950 - TRIGGER DELETE TEST';
DELETE FROM positions_1950  WHERE (name = 'Siegessäule');
--
SELECT DateTime('now'),'Statistics: of Table,Views after DELETE: should be [2,2,1 - with 2 unique name-notes and positions]';
SELECT DateTime('now'),'count_positions('||(SELECT count(*) FROM positions)||') ; count_positions_1900('||(SELECT count(*) FROM positions_1900)||') ; count_positions_1950('||(SELECT count(*) FROM positions_1950)||')' AS result_count;
SELECT DateTime('now'),'DISTINCT name and notes('||count(DISTINCT name||notes)||') positions('||count(DISTINCT wsg84_center)||') from positions'  AS distinct_results  FROM positions;
SELECT DateTime('now'),'SELECT: of positions_1900';
SELECT '1900',name,notes,valid_since,valid_until, AsEWKT(wsg84_center)  FROM positions_1900;
SELECT DateTime('now'),'SELECT: of positions_1950';
SELECT '1950',name,notes,valid_since,valid_until, AsEWKT(wsg84_center)  FROM positions_1950;
--
SELECT DateTime('now'),'Rollback of changes';
ROLLBACK;
--
SELECT DateTime('now'),'Statistics: of Table,Views after ROLLBACK: should be [3,2,2 - with 3 unique name-notes and positions]';
SELECT DateTime('now'),'count_positions('||(SELECT count(*) FROM positions)||') ; count_positions_1900('||(SELECT count(*) FROM positions_1900)||') ; count_positions_1950('||(SELECT count(*) FROM positions_1950)||')' AS result_count;
SELECT DateTime('now'),'DISTINCT name and notes('||count(DISTINCT name||notes)||') positions('||count(DISTINCT wsg84_center)||') from positions'  AS distinct_results  FROM positions;
SELECT DateTime('now'),'SELECT: of positions_1900';
SELECT '1900',name,notes,valid_since,valid_until, AsEWKT(wsg84_center)  FROM positions_1900;
SELECT DateTime('now'),'SELECT: of positions_1950';
SELECT '1950',name,notes,valid_since,valid_until, AsEWKT(wsg84_center)  FROM positions_1950;
--
SELECT DateTime('now'),'gdal_views.sql [finished] [Habe fertig!]';
--
