create read/write spatial view from sql?

[Richard Males]

Using the spatialite gui, I can successfully create a spatial view with read/write capabilities from two tables, using a geometry column from one of the tables.    On the view tab, I select 'create spatial view' and check both the read write boxes for the main and table #2..  I can then use this view in QGIS to add features to both of the underlying tables, which is my goal.

My question is:

What, if any, are the additional steps that are taken in the background through the GUI to create the spatial view and make it read/write and is it possible to do this entirely though an SQL script?  

I would like to be able to do this repeatedly for a number of paired master-detail tables (with the geometry always in the master table, attributes in the detail tables) without having to use the GUI for each case.

Thanks.

Dick

[a.fu...@lqt.it]

Hi Dick,

all VIEWs in any SQLite DB are always read-only objects; you
can freely query a VIEW exactly as if it was an ordinary table
by issuing an appropriate SELECT statement, but any attempt to
execute an INSERT, UPDATE or DELETE statement targeting some
VIEW will inexorably fail.

anyway there is a rather trivial SQL trick we can adopt so to
circumvent such a limitation.
we just have to install few appropriate TRIGGERs intended to
replace any forbidden action targeting the View with another
one legitimately targeting the underlying "mother table" itself.

just an elementary simple example:
------------------------------------------------------

CREATE TABLE mother (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL);

INSERT INTO mother VALUES (NULL, 'apple');
INSERT INTO mother VALUES (NULL, 'lemon');
INSERT INTO mother VALUES (NULL, 'orange');
INSERT INTO mother VALUES (NULL, 'pineapple');
INSERT INTO mother VALUES (NULL, 'cherry');
INSERT INTO mother VALUES (NULL, 'peach');
INSERT INTO mother VALUES (NULL, 'pear');

CREATE VIEW child AS
SELECT id AS pk_uid, name AS fruit
FROM mother;

CREATE TRIGGER child_delete INSTEAD OF DELETE ON child
BEGIN
DELETE FROM mother WHERE id = OLD.pk_uid;
END;

DELETE FROM child WHERE fruit LIKE '%apple';

SELECT * FROM child;
----------------------
2 lemon
3 orange
5 cherry
6 peach
7 pear

quick explanation:
==================
a) the trigger will intercept any DELETE targeting
the VIEW; this explicit action will never be executed
and consequently no exception will be raised.
b) what will be effectively executed is the alternative
action specified within the BEGIN / END block
c) in this specific case: "for every row found in the
resultset DELETE a corresponding row from the
mother table presenting the same PK value"

you could eventually read a more exhaustive explanation
from this Wiki page:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=writable-view

bye Sandro

[mj10777]

On Wednesday, 5 August 2015 23:23:13 UTC+2, Richard Males wrote:

Using the spatialite gui, I can successfully create a spatial view with read/write capabilities from two tables, using a geometry column from one of the tables.    On the view tab, I select 'create spatial view' and check both the read write boxes for the main and table #2..  I can then use this view in QGIS to add features to both of the underlying tables, which is my goal.

It should be noted, that gdal does NOT support writable-views

- as default, QGIS will use the GDAL-OGR driver for Vector Layers

There is a 13 months old ticket for this at:

https://trac.osgeo.org/gdal/ticket/5582

during which 3 different versions had been worked on:

1) pre gdal 2.* version

2) a gdal 2.* verion based on the idea that

-- a base vector class would be created to deal with common tasks (avoiding repetition of the same code)

--- a 'SpatialTable', 'SpatialView' and a 'RasterLite2' class would use this base class for common tasks

--- with would determine how the Sqlite3 container as being used

---> with a main function dealing with the determination

----> as apposed to the present version where it is scattered everywhere inside the class

---> rejecting MbTiles, GeoPackages (which have other classes) and fossil (which is not supported)

---> accepting 'SpatialTable', 'SpatialView', 'RasterLite1' and 'RasterLite2'

----> filtering out the administration tables (which also have geometries)

The base class was called 'OGRSQLiteVectorLayer'.

In the end, 98% of the work was done in the base class

- because dealing with a writable 'SpatialView' was basically the same as a 'SpatialTable'

- for 'RasterLite1' and 'RasterLite2': the administration tables are stored for use with the Raster-Drivers

This version (which I still work with in QGIS) was not accepted.

3)

- A skimmed down version 'OGRSQLiteVectorLayer' was desired

-- with would (basically) only determine how the Sqlite3 container as being used

- a 'SpatialTable' doing what it does now

- a 'SpatialView' doing what it does now, plus the logic reading the TRIGGERS for writable VIEWS

-- ignoring the fact that the code needed for this would have to be repeated again

---> defeating the goal of optimizing the code

- a 'RasterLite2'  class would the have to repeat every thing in 'SpatialTable' again

--> thus 3 times (plus any future use, repeating everything again)

I have received no reply to this conclusion, given 4 months ago

- so the chances seem slim that the writable View logic (which has existed since Spatialite V 2.4) will be offered soon

Also the chances for support of 'RasterLite2', are (from my side) slim

- since the administration tasks are extensive

-- with the (in the mean time) added support for RL2-Vector-layers and Styling - even more so

The '2)' version of 'OGRSQLiteVectorLayer' was designed to deal with this

- but in its present form, this would be a horror without end

Mark

[Richard Males]

Mark/Sandro:

Thank you for your quick responses.  

I am a little unclear on some things in your replies:

1) Using QGIS 2.8 Wien, I most definitely was able to attach the spatialite database with the created spatial view and then add a feature, which did propagate back to the two tables in the database from which the view was created.   This was exactly the behavior I was looking for.  I just did this again moments ago to verify the behavior, it worked as desired.  Are you telling me that this is really unsupported behavior at present?

2) Is the creation of triggers something that happens automatically when using the spatialite GUI to create the read/write view?   If so, is there any way to see what is going on 'behind the scenes' when I create the view from the GUI?

3) I am particularly interested in the creation of a spatial view.   The example given does not show what other SQL is executed from the GUI to get the view treated as a spatial view, for example by populating views_geometry_columns

4) I see that there is no way, in the GUI, to add a row to a view when the grid is opened via the query view.   Is there SQL that will allow me to add a row, as I can through QGIS, or do I need to insert into the master table first, then the detail table second, via SQL?

Thanks.

Dick

[a.fu...@lqt.it]

Hi Dick,

On Thu, 6 Aug 2015 11:12:05 -0700 (PDT), Richard Males wrote:
> 2) Is the creation of triggers something that happens automatically
> when using the spatialite GUI to create the read/write view? 
>

yes: when you select the "Read/Write View" option then the
QueryViewComposer wizard will automatically create any required
Trigger immediately after creating the View itself.

> If so, is there any way to see what is going on 'behind the scenes'
> when
> I create the view from the GUI?
>

there are at least two different ways allowing to inspect in full
detail a View and all its Triggers:

1 - using the visual interface
please see the attached picture

2 - by querying SQLite's metadata:

SELECT name, sql
FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'my_view';

> 3) I am particularly interested in the creation of a spatial view.  
> The example given does not show what other SQL is executed from the
> GUI to get the view treated as a spatial view, for example by
> populating views_geometry_columns
>

you can find more informations on the Cookbook:

http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/sp-view.html

(at page bottom: Hand-writing your own Spatial VIEW)

> 4) I see that there is no way, in the GUI, to add a row to a view
> when
> the grid is opened via the query view.   Is there SQL that will
> allow me to add a row, as I can through QGIS, or do I need to insert
> into the master table first, then the detail table second, via SQL?
>

View is a View is View is a View (as Gertrud Stein said)
... and will always be considered as a read-only object
by spatialite_gui

only the Devil (or an human possessing very good SQL skills) could be
able to guess in advance if your specific target View simply is an
ordinary read-only view as any other or if it's an extravagant
"special effect" read-write view.
it's not the kind of decision a blind automatic tool can safely adopt,
so the baseline assumption will always be that all Views are read-only.

you can anyway perform a direct INSERT into the View by executing an
appropriate SQL statement, something like:

INSERT INTO my_view VALUES ( a, b, c, d );

--------------------
the situation is completely different in QGIS, because the data
provider is always expected to check first "views_geometry_columns";
and when the "read_only" column is set to FALSE a View effectively
supporting unconstrained operations can be legitimately assumed.

bye Sandro

[mj10777]

On Thursday, 6 August 2015 20:12:05 UTC+2, Richard Males wrote:

Mark/Sandro:

Thank you for your quick responses.  

I am a little unclear on some things in your replies:

1) Using QGIS 2.8 Wien, I most definitely was able to attach the spatialite database with the created spatial view and then add a feature, which did propagate back to the two tables in the database from which the view was created.   This was exactly the behavior I was looking for.  I just did this again moments ago to verify the behavior, it worked as desired.  Are you telling me that this is really unsupported behavior at present?

If QGIS is using gdal-org for this, yes it very strange.

- gdal is used when:

-- 'Add Vector layer' or you drag a Database into QGIS and drop it

--- after loading, right Mouse on the geometry in the Tree: and the 'Toggle Editing' should be OFF for ALL Views

---> QgsOgrProvider::capabilities() is called when loading for 'SequentialWrite' [adding], 'DeleteFeature' [delete] and 'RandomWrite' [update]

----> this called the gdal-ogr function: TestCapability() [present 2.0 version]

-----> OGRSQLiteViewLayer will always return FALSE for these values

-----> OGRSQLiteTableLayer will always check for any errors and in most cases return TRUE for all 3

- for the planned 'OGRSQLiteVectorLayer' class

-- in function 'EstablishFeatureDefn', when it a View

--- it will check for existing TRIGGERS

SELECT 1 FROM sqlite_master WHERE ((type = 'trigger' AND tbl_name='%s') AND (sql LIKE '%%INSTEAD OF INSERT%%'))
SELECT 1 FROM sqlite_master WHERE ((type = 'trigger' AND tbl_name='%s') AND (sql LIKE '%%INSTEAD OF UPDATE%%'))
SELECT 1 FROM sqlite_master WHERE ((type = 'trigger' AND tbl_name='%s') AND (sql LIKE '%%INSTEAD OF DELETE%%'))

storing the result.

TestCapability() will then return this result.

In QGIS, if one of the 3 are true

- then 'Toggle Editing' will be activated

-- depending on the result of ADD,UPDATE or DELETE 

--- the corresponding buttons will be activated

---

With 'Add Spatialite layer'

- this may work, but I cannot say for sure - since I rarely use it

-- this seems to use: QgsSpatiaLiteProvider

--- there, for views, the existence of TRIGGERS is queried (QgsSpatiaLiteProvider::hasTriggers())

---> if a least 1 is found, 'readonly' is set to false

---> thus allowing you to INSERT, DELETE, UPDATE even if the corresponding TRIGGER is missing

- this may be outdated, since I am using the source code of 2014-02-22 (v 2.1.0)

-- after the 2014-02-22 version

-- when updating the screen (moving in any way) a terrible flickering happens, which I find hard for the eyes

2) Is the creation of triggers something that happens automatically when using the spatialite GUI to create the read/write view?   If so, is there any way to see what is going on 'behind the scenes' when I create the view from the GUI?

For spatialite_gui: yes, it builds the needed 'CREATE TRIGGER' sql-commands

- it uses a naming convention for the created triggers:

--  vw_upd_* for 'INSTEAD OF UPDATE'

--  vw_ins_* for 'INSTEAD OF INSERT'

--  vw_del_* for 'INSTEAD OF DELETE'

My scripts are also based on these conventions, but are not binding

so in spatialite_gui you will see these in the treeview inside the view after the field definitions

- 'Show trigger' will show you the sql used to create the trigger

I have attached a sql-script that

- created a gcp table

-- which is intended to collect known points for geo-referencing

-- there are different types

--- begin of street, end of street, street intersection, bridges etc

-- there valid_since, valid_until: to show only points that existed at a specific time

--- bridges that were moved to a new position at a specific date, so when geo-referencing a map of a specific date you can show only those points that were then valid

-- gcp_order 

--- tcp, 1st, 2nd, 3rd (see https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Ground+Control+Points)

For the different types there are views

- 'vw_ins_gcp_points_street_end'

-- these define points that are the end of the street

--- if the street is extended, the valid_until will have a end-date for the old point and for the new a valid_since

--- on an INSERT the gcp_type is set to the correct value (here 102) [the TRIGGER will set this value during an insert]

--- there are 2 fields showing the position in clear text (map_x,map_y)

---- on INSERT or UPDATE these a (re-)set with ST_X(NEW.gcp_point) and ST_Y(NEW.gcp_point) [the TRIGGER keeps these values up to date]

--- use of 'CASE WHEN / THEN/ELSE/END' for setting of default values

---- for views based on DATES, this can be different than the TABLE default values

So there is a lot that a TRIGGER can do, so the study of the sqlite TRIGGER syntax is strongly advised

https://www.sqlite.org/lang_createtrigger.html

The possible complexity of a TRIGGER makes it impossible for a database-driver to determine if the TRIGGER will work correctly 

- so a database-driver can only determine that the database claims to have a TRIGGER that will work

3) I am particularly interested in the creation of a spatial view.   The example given does not show what other SQL is executed from the GUI to get the view treated as a spatial view, for example by populating views_geometry_columns

The 'create.gcp_master.sql'

- should show all of the needed steps 

-- in a terminal where the sql-script is stored execute the following command

--> spatialite gcp_master.db < create.gcp_master.sql

- This assumes that you are using 'Soldner Berlin' srid=3068

-- change '3068' to '4326' to support Wsg84 or whatever projection you desire

4) I see that there is no way, in the GUI, to add a row to a view when the grid is opened via the query view.   Is there SQL that will allow me to add a row, as I can through QGIS, or do I need to insert into the master table first, then the detail table second, via SQL?

For some unknown reason, sub-queries even on a normal table (i.e. WHERE name LIKE 'Some%' ) 

- no changes can be made

Also for views with an valid UPDATE-TRIGGER (and read_only=0)

- no changes can be made

Sandro has given reasons for views, which I do not agree with

- if the TRIGGER does not work correctly, then it the fault of the Database-Designer

But since sub-queries on normal tables can also not be changed

- may be the true cause of not being able to edit a writable view with a valid TRIGGER and read_only=0

-- a view being a sub-query on  one/multiple normal table(s)

--

Note: 

- while working out a INSERT sample to a view

-- and of course testing if it would work as expected

--> I found 2 different errors in the created TRIGGERS

---> 1 which failed during an INSERT (not during the creation)

----> 2 new fields I had created, were not listed in the fields list.but included in the INSERT Statement

---> 1 that produced rubbish during the successful INSERT

----> the VALUES order did not correspond to the field ORDER

------> gcp_type in position 7 in the field list but position 2 in the VALUE list

which proves again that : TRIGGERS are TRICKY

--

As Sandro has stated, INSERT command can be executed directly into a view:

INSERT INTO gcp_points_bridge

 (name,notes,text,belongs_to_01,belongs_to_02,valid_since,valid_until,gcp_point,order_selected) 

 VALUES

 (

  'Promeranzenbrücke, Kleine',

  'Süden, Lustgarten, Schloß',

  '1658-1824 Von neue Packhof (Orangerie-Haus, Promeranzen Haus) über Communikationsgraben zur Lustgarten',

  'Bezirk 18 - Schloß',

  'Alt-Cölln',

  '1658-01-01',

  '1823-12-31',

  GeomFromEWKT('SRID=3068;POINT(24671.85217766343 21397.99982336495)'),

  1 -- precise point

 );

This is one of the 4 INSERTS that the script does.

- 2 in bridges, 2 in know points

-- 1 is a 2nd order point, 3 a 1st order point (which also have views)

After UpdateLayerStatistics()

- vector_layers_statistics show proper (i.e non-NULL) values

-- for the VIEWs: bridges, known, gcp_order_01 and 02

-- for the gcp_master TABLE which store the points

So building a test script to fill all the views

- checking the results

-- is the best way to insure that the TRIGGERS work correctly

Hope this helps.

Mark

Thanks.

Dick

[a.fu...@lqt.it]

short summary for wise and prudent men:

1. writable views implemented via Triggers are like an acrobat walking
on a flimsy wire suspended over the deepest abyss.
there are no safety margins at all, and even the slightest error
will cause immediate death.
2. anyway an exceptionally able (and lucky) acrobat could sometimes
survive unharmed.

bye Sandro

[Richard Males]

Thank you both for your comprehensive answers, exactly what I was looking for, and to Sandro for his vivid acrobat imagery.

Dick

[Richard Males]

One additional thing has come up, not addressed in your previous answers.  This relates to read-only spatial views.  I create a view directly with the sql below.   It appears in the gui, but does not have the symbol that indicates geometry.   In addition, there is no corresponding entry in the views_geometry_columns table.   However, once I manually enter the required information into the views_geometry_columns table, it then becomes visible as a spatial view (not writeable, in this case, but that is fine for my purposes), and then available in QGIS when I connect to and open the spatialite layer.   So I assume that this is an additional step that is carried out behind the scenes when creating a spatial view directly from the gui, and I will need to implement the additional sql to load the views_geometry_columns table when creating the spatial view directly from sql scripts.   Just wanting to check if there is anything else that needs to be done on the spatialite end.

Thanks.

Dick

CREATE VIEW vAssetsAllStatistics3 AS
SELECT "a"."ROWID" AS ROWID, "a"."AssetExternalReference" AS AssetExternalReference,
    "a"."AssetRepresentativePointGeometry" AS AssetRepresentativePointGeometry,
    "b"."ID1" AS ID1, "b"."ID2" AS ID2, "b"."ID3" AS ID3,
    "b"."StatisticsTypeId" AS StatisticsTypeId, "b"."StatisticsTypeName" AS StatisticsTypeName,
    "b"."StatisticsUnitId" AS StatisticsUnitId, "b"."StatisticsUnitName" AS StatisticsUnitName,
    "b"."N" AS N, "b"."MeanValue" AS MeanValue,
    "b"."StandardDeviation" AS StandardDeviation,
    "b"."Skew" AS Skew, "b"."Kurtosis" AS Kurtosis,
    "b"."MinimumValue" AS MinimumValue, "b"."LowerQuantile" AS LowerQuantile,
    "b"."MedianValue" AS MedianValue, "b"."UpperQuantile" AS UpperQuantile,
    "b"."MaximumValue" AS MaximumValue, "b"."CDF0" AS CDF0,
    "b"."CDF05" AS CDF05, "b"."CDF10" AS CDF10,
    "b"."CDF15" AS CDF15, "b"."CDF20" AS CDF20,
    "b"."CDF25" AS CDF25, "b"."CDF30" AS CDF30,
    "b"."CDF35" AS CDF35, "b"."CDF40" AS CDF40,
    "b"."CDF45" AS CDF45, "b"."CDF50" AS CDF50,
    "b"."CDF55" AS CDF55, "b"."CDF60" AS CDF60,
    "b"."CDF65" AS CDF65, "b"."CDF70" AS CDF70,
    "b"."CDF75" AS CDF75, "b"."CDF80" AS CDF80,
    "b"."CDF85" AS CDF85, "b"."CDF90" AS CDF90,
    "b"."CDF95" AS CDF95, "b"."CDF100" AS CDF100
FROM "Assets" AS a
JOIN "Statistics" AS b ON ("a"."AssetExternalReference" = "b"."ID3")

[mj10777]

Probably this (1=read_only=true):

INSERT INTO views_geometry_columns

 (view_name,view_geometry,view_rowid,f_table_name,f_geometry_column,read_only)

 VALUES ('vAssetsAllStatistics3','AssetRepresentativePointGeometry','ROWID','Assets','AssetRepresentativePointGeometry',1);