SpatiaLite: writable-view

Updatable Views

Notoriously any SQLite's own VIEW is a read-only object; you can query a VIEW exactly as you can can query a TABLE, i.e. by invoking some appropriate SELECT statement. But any INSERT, UPDATE or DELETE statement affecting a VIEW will inexorably fail, because all these are forbidden operations for VIEW targets.

Anyway the ISO SQL standard supports a smart trick effectively allowing to circumvent this limitation; and after all SQLite genuinely is an ISO SQL comformat DBMS ...
Let's now go to explore how updatable views (aka writable views) are implemented in SQLite and SpatiaLite.

Generating a sample dataset

We'll start creating (and populating) two tables:

the headquarter table
and the store table
each store surely has a corresponding headquarter; the same headquarter may eventually control many different stores
so a PRIMARY - FOREIGN KEY relationship will be defined, joining headquarters and stores
each store will be georeferenced, i.e. will be represented by a corresponding POINT geometry

Just few SQL commands useful to initialize our sample dataset:

CREATE TABLE headquarter (
    hq_id INTEGER NOT NULL PRIMARY KEY,
    hq_name TEXT NOT NULL,
    hq_manager TEXT NOT NULL,
    hq_ip_addr TEXT NOT NULL);

INSERT INTO headquarter (hq_id, hq_name, hq_manager, hq_ip_addr)
    VALUES (1, 'Rome', 'Mario Rossi', '257.257.257.1');
INSERT INTO headquarter (hq_id, hq_name, hq_manager, hq_ip_addr)
    VALUES (2, 'London', 'John Smith', '258.258.258.23');
INSERT INTO headquarter (hq_id, hq_name, hq_manager, hq_ip_addr)
    VALUES (3, 'Paris', 'Paul Dupont', '34.17.258.259');

CREATE TABLE store (
    st_id INTEGER NOT NULL PRIMARY KEY,
    st_name TEXT NOT NULL,
    st_contact TEXT NOT NULL,
    st_ip_addr TEXT NOT NULL,
    hq_id INTEGER NOT NULL,
    CONSTRAINT fk_store_hq FOREIGN KEY (hq_id) REFERENCES headquarter (hq_id));
SELECT AddGeometryColumn('store', 'geom', 4326, 'POINT', 'XY');

INSERT INTO store (st_id, st_name, st_contact, st_ip_addr, hq_id, geom)
    VALUES (1, 'ABC Srl', 'Paolo Bianchi', '258.1.1.23', 1, MakePoint(11.1, 42.2, 4326));
INSERT INTO store (st_id, st_name, st_contact, st_ip_addr, hq_id, geom)
    VALUES (2, 'Chez Marcel', 'Marcel Dubois', '260.1.23.257', 3, MakePoint(2.1, 48.5, 4326));
INSERT INTO store (st_id, st_name, st_contact, st_ip_addr, hq_id, geom)
    VALUES (3, 'ACME Plc', 'Janet White', '251.23.258.257', 2, MakePoint(-0.1, 51.2, 4326));

Creating an Updatable View

We'll simply use the Query composer dialog available on the spatialite_gui tool.
First we'll select all required columns form both tables, and we'll set the relational JOIN connecting the two tables.

And finally we'll then create a Spatial View.

Please note: we have checked the RW Enabled Main Table option, so the View being created will be an Updatable VIEW.

Please note well: there is yet another supported option, i.e. RW Enabled Table #2 ... we'll examine this too, but in a following step.
For now we'll simply test the simpler case.

Testing the Updatable View - step #1

Just a very trivial SELECT * FROM store_view; there is nothing interesting to be noted here.
Anyway you can easily notice that this VIEW is supported by three Triggers; and are exactly these Triggers who magically transform a read-only View into an Updatable View. Let's go to verify by actually executing few SQL statements.

INSERT INTO store_view (st_id, st_name, st_contact, st_ip_addr, hq_id, geom)
VALUES (4, 'da Marino', 'Marino Neri', '258.1.1.57', 1, MakePoint(12.3, 44.1, 4326));

INSERT INTO store_view (st_id, st_name, st_contact, st_ip_addr, hq_id, geom)
VALUES (5, 'F.lli Verdi', 'Giuseppe Verdi', '258.1.1.198', 1, MakePoint(11.3, 43.2, 4326));

UPDATE store_view SET st_contact = 'Paulette Laloux'
WHERE st_id = 2;

DELETE FROM store_view
WHERE st_id = 1;

Now we'll execute yet another SELECT * FROM store_view in order to check ... WOW it really works !
As you can easily verify, all changes requested by the previous SQL statements are now permanently saved into the Database; even if performed on behalf of a View.

It's now time to get a quick glance at few technical details supporting Updatable Views: as we've yet anticipated, all the magic is in the Triggers defined for this View:

*vw_ins_store_view*	CREATE TRIGGER "vw_ins_store_view" INSTEAD OF INSERT ON "store_view" BEGIN 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	this Trigger intercepts any INSERT statement affecting the View. As you can see by reading the SQL code, what actually happens simply is a redirection. The INSERT statement effectively being executed targets the appropriate Table and not the View itself.
*vw_upd_store_view*	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 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	this Trigger intercepts any UPDATE statement affecting the View. Then redirecting the actual UPDATE effectively being executed.
*vw_del_store_view*	CREATE TRIGGER "vw_del_store_view" INSTEAD OF DELETE ON "store_view" BEGIN DELETE FROM "store" WHERE ROWID = OLD.ROWID; END	this Trigger intercepts any DELETE statement affecting the View. Then redirecting the actual DELETE effectively being executed.

Testing the Updatable View - step #2

As you surely remember, I'd announced in the previous step that an Updatable View can even support the RW Enabled Table #2 option.
store_view2 is a second View exactly defined as store_view, but this time enabling the RW Enabled Table #2 option as well. Let's go to to check the difference by actually executing few SQL statements.

INSERT INTO store_view2 (st_id, st_name, st_contact, st_ip_addr, hq_id, hq_name, hq_manager, hq_ip_addr, geom)
VALUES (6, 'SmartDevices Ltd', 'George Hunter', '258.258.3.198', 4, 'Dublin', 'Brendan O''Hara', '258.258.72.1', MakePoint(-6.3, 43.2, 4326));

UPDATE store_view2 SET hq_manager = 'Luisa Fabbri'
WHERE hq_id = 1;

DELETE FROM store_view2
WHERE st_id = 4;

As you can easily notice, this time our changes have affected indifferently both tables.

Please note well: this configuration isn't at all a wise and stable one. Never forget: beyond the scenes we actually have two distinct tables, and a Primary - Foreign Key constraint defined between them. In many cases your changes may actually cause severe damages or absolutely unexpected crazy side effects, if not carefully handled.
Remember: this one surely is a powerful and may be interesting tool; but it's not at all hazard free. Think well twice about this.

Here are the Triggers defined for this View:

*vw_ins_store_view2*	CREATE TRIGGER "vw_ins_store_view2" INSTEAD OF INSERT ON "store_view2" BEGIN 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"); 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	this Trigger intercepts any INSERT statement affecting the View. This time two distinct INSERTs are performed, one for each table. All the column values are dispatched as appropriate.
*vw_upd_store_view2*	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 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"; 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	this Trigger intercepts any UPDATE statement affecting the View. Then splitting in two distinct UPDATEs, one for each table.
*vw_del_store_view2*	CREATE TRIGGER "vw_del_store_view2" INSTEAD OF DELETE ON "store_view2" BEGIN DELETE FROM "store" WHERE ROWID = OLD.ROWID; END	this Trigger intercepts any DELETE statement affecting the View. Please note: this time a single DELETE will be redirected, so to skip many relational integrity violations.

Further readings:
about SQLite Triggers
about SQLite read-only Views and updatable Views

back