Hi Marco,
I know we've been discussing this directly, but since you've also mentioned it
here on this list, I thought a response here could also facilitate greater
discussion.
An updatable view is a view that will accept insert, delete and update
commands, propagating changes back to the underlying tables. I believe that
some SQL database engines (not SQLite) facilitate updatable views
automatically, if the view uses just one table or is a simple join of tables,
showing only some of their columns (no calculations). However, any view
(including all views in SQLite) can be explicitly designed to be updatable by
using "instead of" triggers.
Here's a very simple single table example:
create table "Person"
( ID integer primary key not null
, "Name First" text collate nocase
, "Name Last" text collate nocase
, unique ("Name First", "Name Last")
)
;
create view "Person Sorted"
as
select
ID
, "Name First"
, "Name Last"
from "Person"
order by "Name Last", "Name First"
;
If I want to make the "Person Sorted" view updatable, I need to consider which
of update, insert and delete I want to facilitate, and create an "instead of"
trigger for each:
create trigger "Person Sorted delete"
instead of delete
on "Person Sorted"
begin
delete from "Person" where ID = old.ID
;
end
;
create trigger "Person Sorted insert"
instead of insert
on "Person Sorted"
begin
insert into "Person" (ID, "Name First", "Name Last")
select new.ID, new."Name First", new."Name Last"
;
end
;
create trigger "Person Sorted update"
instead of update
on "Person Sorted"
begin
update "Person"
set ID = new.ID, "Name First" = new."Name First", "Name Last" =
new."Name Last"
where ID = old.ID
;
end
;
So, now, if the user opens the view "Person Sorted", they can insert, delete or
update directly in that view, and the triggers will propagate those changes to
the underlying table. It appears to behave just like a table.
For instance, they can do any of the following:
insert into "Person Sorted" ("Name First", "Name Last") values ('Tom',
'Brodhurst-Hill')
;
delete from "Person Sorted" where ID = 4
;
delete from "Person Sorted" where "Name Last" = 'Brodhurst-Hill'
;
update "Person Sorted" set "Name First" = 'Thomas' where "Name First" = 'Tom'
and "Name Last" = 'Brodhurst-Hill'
;
Of course, the above example shows a view that is basically a mirror of a
single table, so isn't very useful. It was just for illustration. Let's look at
something more complex. Let's add the following tables so we can associate with
each person a company, a job title and many (or one or none) email addresses.
We can specify whether each email address is for work, home or another purpose:
create table "Company"
( ID integer primary key not null
, Name text collate nocase not null unique
)
;
create table "Person Company"
( ID integer primary key not null references "Person" (ID) on delete
cascade on update cascade
, "Company" integer not null references "Company" (ID) on delete cascade
on update cascade
)
;
create table "Job Title"
( ID integer primary key not null
, Name text collate nocase not null unique
)
;
create table "Person Job Title"
( ID integer primary key not null references "Person" (ID) on delete
cascade on update cascade
, "Job Title" integer not null references "Job Title" (ID) on delete
cascade on update cascade
)
;
create table "Purpose"
( ID integer primary key not null
, Name text collate nocase not null unique
)
;
insert into "Purpose" (Name) select 'Home' union select 'Work' union select
'School'
;
create table "Person Email"
( ID integer primary key not null
, "Person" integer not null references "Person" (ID) on delete cascade on
update cascade
, "Email" text collate nocase not null
, "Purpose" integer references "Purpose" (ID) on delete restrict on
update cascade
, unique ("Person", "Purpose")
)
;
The above structure is a fairly well normalised store of the data. In this
example, I've restricted the database to allowing just one email address for
each purpose. So each person can have only one work email address.
So, imagine the human resources department has populated these tables with all
the people in our company. They want to distribute a list of all employees,
only showing their name, company and work email address, something like this:
create view "Person Work"
as
select
"Person".ID as ID
, "Name First"
, "Name Last"
, "Company"."Name" as "Company"
, "Job Title"."Name" as "Job Title"
, ( select "Email" from "Person Email" join "Purpose" on
"Purpose".ID = "Purpose"
where "Person" = "Person".ID and "Purpose".Name = 'Work'
) as "Email"
from "Person"
left join "Person Company" on "Person Company".ID = "Person".ID
left join "Company" on "Company".ID = "Company"
left join "Person Job Title" on "Person Job Title".ID = "Person".ID
left join "Job Title" on "Job Title".ID = "Job Title"
order by "Name Last", "Name First"
;
That will display a list like:
ID
Name First
Name Last
Company
Job Title
Email
55
Tom
Brodhurst-Hill
BareFeetWare
iOS Developer
[email protected]
etc
All pretty typical so far. Now let's take the next step by making the view
updatable.
It would be great if the people manager could enter changes to the "Person
Work" list directly, rather than having to find each detail in the constituent
tables and manually cross referencing IDs. We can facilitate updating in the
view directly by adding "instead of" triggers:
create trigger "Person Work delete"
instead of delete
on "Person Work"
begin
delete from "Person" where ID = old.ID -- note that cascades in the
related tables will automatically delete there too
;
end
;
create trigger "Person Work insert"
instead of insert
on "Person Work"
begin
insert into "Person" (ID, "Name First", "Name Last")
select new.ID, new."Name First", new."Name Last"
;
-- Company:
insert into "Company" ("Name")
select new."Company"
where new."Company" not in (select Name from "Company")
and new."Company" not null
;
insert into "Person Company" ("ID", "Company")
select
(select ID from "Person" where "Name First" = new."Name First"
and "Name Last" = new."Name Last")
, (select ID from "Company" where Name = new."Company")
where new."Company" not null
;
-- Job Title:
insert into "Job Title" ("Name")
select new."Job Title"
where new."Job Title" not in (select Name from "Job Title")
and new."Job Title" not null
;
insert into "Person Job Title" ("ID", "Job Title")
select
(select ID from "Person" where "Name First" = new."Name First"
and "Name Last" = new."Name Last")
, (select ID from "Job Title" where Name = new."Job Title")
where new."Job Title" not null
;
-- Email:
insert into "Person Email" ("Person", "Email", "Purpose")
select
(select ID from "Person" where "Name First" = new."Name First"
and "Name Last" = new."Name Last")
, new."Email"
, (select ID from "Purpose" where Name = 'Work')
where new."Email" not null
;
end
;
create trigger "Person Work update"
instead of update
on "Person Work"
begin
update "Person"
set ID = new.ID, "Name First" = new."Name First", "Name Last" =
new."Name Last"
where ID = old.ID
;
-- Company:
insert into "Company" ("Name")
select new."Company"
where new."Company" not in (select Name from "Company")
and new."Company" not null
;
insert into "Person Company" ("ID", "Company")
select
new.ID
, (select ID from "Company" where Name = new."Company")
where new."Company" not null and old."Company" is null -- ie Company
was blank but new value entered
;
delete from "Person Company"
where ID = old.ID
and new."Company" is null and old."Company" not null -- ie
Existing Company value deleted
;
update "Person Company"
set "Company" = (select ID from "Company" where Name = new."Company")
where ID = new.ID
and new."Company" not null and old."Company" is null -- ie
Existing Company value changed
;
-- Job Title:
insert into "Job Title" ("Name")
select new."Job Title"
where new."Job Title" not in (select Name from "Job Title")
and new."Job Title" not null
;
insert into "Person Job Title" ("ID", "Job Title")
select
new.ID
, (select ID from "Job Title" where Name = new."Job Title")
where new."Job Title" not null and old."Job Title" is null -- ie Job
Title was blank but new value entered
;
delete from "Person Job Title"
where ID = old.ID
and new."Job Title" is null and old."Job Title" not null -- ie
Existing Job Title value deleted
;
update "Person Job Title"
set "Job Title" = (select ID from "Job Title" where Name = new."Job
Title")
where ID = new.ID
and new."Job Title" not null and old."Job Title" is null -- ie
Existing Job Title value changed
;
-- Email:
update "Person Email"
set "Email" = new."Email"
where "Person" = new.ID and "Purpose" = (select ID from "Purpose" where
Name = 'Work')
and new."Email" not null and old."Email" not null -- ie
changing an existing email address
;
delete from "Person Email"
where "Person" = new.ID and "Purpose" = (select ID from "Purpose" where
Name = 'Work')
and new."Email" is null and old."Email" not null -- ie deleting
an existing email address
;
insert into "Person Email" ("Person", "Email", "Purpose")
select
new.ID
, new."Email"
, (select ID from "Purpose" where Name = 'Work')
where new."Email" not null and old."Email" is null -- adding a email
address that didn't exist
;
end
;
The above triggers make the "Person Work" view updatable. The user can update,
insert or delete from the list, the same way as they would for a table.
For instance, I can tell SQLite (eg via the command line) to do things like:
insert into "Person Work" ("Name First", "Name Last", "Company", "Email")
values ('Marco', 'Bambini', 'SQL Labs', '[email protected]')
;
delete from "Person Work" where ID = 78
;
update "Person Work" set "Email" = '[email protected]' where ID = 55
;
update "Person Work" set "Job Title" = 'CEO' where "Name First" = 'Tom' and
"Company" = 'BareFeetWare'
;
Other views may perform calculations on table data (such as converting a
julianday real value into a human friendly text representation in the local
time zone). So the "instead of" triggers of an updatable view can be configured
to do the reverse (so the user can edit the human readable local version to
effectively update the cryptic julianday value stored in the table).
Updatable views make it possible to create properly normalised database tables,
while still presenting neat logical and human readable views to users, in which
they can edit values directly and easily.
If you're embedding SQLite in your own app, you can achieve similar
functionality by instead writing all of the cross referencing in application
code, but that's a lot of work and moves a lot of logic away from the database
(where it belongs). So, I would argue strongly that updatable views are
preferable to application code cross referencing.
If you're using a database manager program (such as SQLite Manager or Navicat),
you need to find one that facilitates updatable views. I have listed this
feature as "Entry in views (with "instead of" triggers)" in my comparison
matrix at:
http://www.barefeetware.com/sqlite/compare/?ml
It's way out of date so I should update it soon. Fittingly, that whole matrix
is actually an updatable view.
Contrary to what's listed there, Navicat does a great job of supporting
updatable views.
For the developer of an SQLite admin program, (such as you, Marco), you need to
come up with an SQL command for each GUI instruction from the user. When they
update or delete a row in a table, you need to do include a "where" clause to
identify the correct row. For a table, you're probably using the primary key of
that table, such as:
where ID = 55
For an updatable view, you need to also build the where clause, but there is no
primary key for a view. So you need to come up with a way to identify a unique
column of data. I know of a few ways, listed from best to worst:
1. Parse the "instead of" triggers and deduce the unique column(s).
2. Search through the data in the view and deduce which column or column
combination is unique to each row.
3. Add your own identifier column to each row of the view's output so you can
use it when updating.
4. Ask the user to identify a unique column (or combination of columns) in the
view.
5. Expect a particular column name in the view to be unique for each row, such
as "ID" or "rowid".
I used method 1 in my iPad/iPhone SQLite editing app. It's a pretty
sophisticated approach and works really well.
I hope this helps,
Tom
Tom Brodhurst-Hill
BareFeetWare
--
iPhone/iPad/iPod and Mac software development, specialising in databases
[email protected]
--
Follow us on Twitter: http://twitter.com/barefeetware/
Like us on Facebook: http://www.facebook.com/BareFeetWare
----
On 09/11/2012, at 11:37 PM, Marco Bambini <[email protected]> wrote:
> From the official documentation:
> You cannot DELETE, INSERT, or UPDATE a view. Views are read-only in SQLite.
> However, in many cases you can use an INSTEAD OF trigger on the view to
> accomplish the same thing.
>
> Can someone clarifies this point?
> Seems like it is possible to write into a view but I do not understand what
> if the correct way to perform the operation.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
