writable-view
Not logged in

back

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:
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

writable-view-1.png
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.

writable-view-2.png
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

writable-view-3.png
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;

writable-view-4.png
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;

writable-view-5.png
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