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 "Co
