Updatable_Views

How to create a writable SpatialView from a sql-script

--> 'List of Sql-Commands'

--> 'List of Themes'

--> 'List of Database-Designing'

--> 'List of Geopaparazzi Specific Themes'

--> 'List of SpatialView Themes'

<-- 'SpatialView-Introduction'

---

This is a sql script based on the sample shown at : spatialite - writable-view

• the full script is in the repository at
  • 'Updatable_Views.sql'

---

• there are 3 main points that should be remembered
  • when using a SpatialView (or for that matter also a SpatialTable) with geopaparazzi
    • a SpatialIndex must be created for the geometry of the table it was created in
      • this is not done automatically in spatialite_gui

SELECT DateTime('now'),'calling CreateSpatialIndex: needed for geopaparazzi';
SELECT CreateSpatialIndex('store','geom');

• an entry in the views_geometry_columns for the view must be created
  • spatialite_gui does this for you

SELECT DateTime('now'),'inserting view: to views_geometry_columns';
INSERT INTO views_geometry_columns
 (view_name,view_geometry,view_rowid,f_table_name,f_geometry_column,read_only)
 VALUES ('store_view','geom','rowid','store','geom',0);

• all fields MUST be LOWERCASE (please don't ask me why)

  • 'Geometry' must be 'geometry'

• read_only is a mandatory parameter

  • 0 = read only ; 1 = writable
    • never assume, that by only setting 1 that the view will be writable
      • without the triggers added, it will remain read only

• triggers based on RW Enabled Main Table

SELECT DateTime('now'),'creating triggers for writable view: store_view';
-- insert
CREATE TRIGGER vw_ins_store_view
 INSTEAD OF INSERT ON store_view
BEGIN
 -- the only table
 INSERT OR REPLACE INTO store
  (st_id,st_name,st_contact,st_ip_addr,hq_id,geom)
  VALUES(NEW.st_id,NEW.st_name,NEW.st_contact,NEW.st_ip_addr,NEW.hq_id,NEW.geom);
END;
-- update
CREATE TRIGGER vw_upd_store_view
 INSTEAD OF UPDATE OF
 st_id,st_name,st_contact,st_ip_addr,hq_id,geom ON store_view
BEGIN
 -- the only table
 UPDATE store SET
  st_id = NEW.st_id,
  st_name = NEW.st_name,
  st_contact = NEW.st_contact,
  st_ip_addr = NEW.st_ip_addr,
  hq_id = NEW.hq_id,
  geom = NEW.geom
 WHERE ROWID = OLD.ROWID;
END;
-- delete
CREATE TRIGGER vw_del_store_view
 INSTEAD OF DELETE ON
 store_view
BEGIN
 -- the only table
 DELETE FROM store WHERE ROWID = OLD.ROWID;
END;

• triggers based on RW Enabled Table #2

SELECT DateTime('now'),'creating triggers for writable view: store_view2';
-- insert
CREATE TRIGGER vw_ins_store_view2
 INSTEAD OF INSERT ON
 store_view2
BEGIN
 -- first table
 INSERT OR REPLACE INTO headquarter
  (hq_id,hq_name,hq_manager,hq_ip_addr)
  VALUES(NEW.hq_id,NEW.hq_name,NEW.hq_manager,NEW.hq_ip_addr);
 -- second table
 INSERT INTO store
  (st_id,st_name,st_contact,st_ip_addr,hq_id,geom)
  VALUES(NEW.st_id,NEW.st_name, NEW.st_contact,NEW.st_ip_addr,NEW.hq_id,NEW.geom);
END;
-- update
CREATE TRIGGER vw_upd_store_view2
 INSTEAD OF UPDATE OF
 st_id,st_name,st_contact,st_ip_addr,hq_id,geom,hq_name,hq_manager,hq_ip_addr ON store_view2
BEGIN
 -- first table
 UPDATE headquarter SET
  hq_name = NEW.hq_name,
  hq_manager = NEW.hq_manager,
  hq_ip_addr = NEW.hq_ip_addr
 WHERE hq_id = NEW.hq_id;
 -- second table
 UPDATE store SET
  st_id = NEW.st_id,
  st_name = NEW.st_name,
  st_contact = NEW.st_contact,
  st_ip_addr = NEW.st_ip_addr,
  hq_id = NEW.hq_id,geom = NEW.geom
 WHERE ROWID = OLD.ROWID;
END;
-- delete
CREATE TRIGGER vw_del_store_view2
 INSTEAD OF DELETE ON store_view2
BEGIN
 DELETE FROM store WHERE ROWID = OLD.ROWID;
END;

---

Never having written a trigger before, I looked closely at the syntax

• it seems very straight forward
  • the INSERT / REPLACE for the creation or replacement logic after CREATE TRIGGER
  • BEGIN for the start of the sql-statement to be used
    • a valid INSERT, UPDATE and DELETE commands
      • with NEW. and OLD. being used as needed
  • END for the end of the sql-statement

---

• a nice feature addition for spatialite would be:
  • when an INSERT in the table views_geometry_columns is being done and
    • read_only = 1
      • that the 3 triggers would be created automatically
  • this would simplify sql-script creations

---

• here is an image showing show_view in QGIS
  • the red stars are the POINTS from show_view
    • the left Menu shows the Properties Menus for show_view
      • the Toggle Editing Menu does not show
        • it looks as if QGIS cannot recognize that the views are writable
  • the green area (Top/Right) are my Ortsteil Geometries
    • the right Menu shows the Properties Menus for Ortsteile
      • the Toggle Editing Menu can be seen

---

2014-04-24: Mark Johnson, Berlin Germany

---