Hi All,
Short question:
What's the best way (or your way) of facilitating an updatable view (a
view that will accept insert, delete and update, propagating changes
back to the underlying tables), especially for columns that are joined
to satisfy normalization?
Long version of question:
Properly normalizing a database means (in part) moving to their own
table any columns that contain mostly nulls. I can create a view to
join that information for the user to see. I can use "instead of"
triggers on the view to redirect insert, delete and update events on
the views to instead change the underlying tables. What's the best way
to do this? I have developed a method, but wonder if there's a better,
standard or popular approach.
Detailed example:
Let's say I have a table of refunds I receive from orders I make to
suppliers, such as when they can't source any more stock and cancel
the order. Each refund will have a primary key ID, the [Order ID] of
the original Order, the Date the refund occurred, and the Reason for
the refund.
Some refunds will also have note of the Paying method (an integer
numeration for Credit Card, Account, Direct Deposit, Cash, or PayPal
etc) if the paying method differs from the default (how I paid them
when ordering). Since most Refunds will be Paying via the default
method, only a few need to be recorded.
Similarly, the refund is normally the full amount of the original
order (ie the default). In some cases, it may only be a partial refund
(eg not all items in that order cancelled) or more than the original
amount. For those few refunds where the amount paid differs from the
default, I need to record the amount. The default amounts don't need
to be recorded.
So, I need an [Orders Refunds] table containing all the fields that
are always used. I need a separate [Orders Refunds Paying] table for
recording the ID and Paying method of only those refunds that are not
the default. I need a separate [Orders Refunds Amount] table to record
the amount if it is not the default value (ie full refund).
The tables look like this:
create table [Orders Refunds]
(
ID integer primary key
, [Order ID] --> Orders.ID
, Date date -- date stored as a real
, Reason text collate nocase
)
;
create table [Orders Refunds Paying] -- only insert in here if not
same as when purchased
(
ID integer primary key --> [Orders Refunds].ID
, Paying integer --> Paying.ID
)
;
create table [Orders Refunds Amount] -- only insert if Amount not same
as full purchase
(
ID integer primary key --> [Orders Refunds].ID
, Amount real -- money amount in dollars and decimal
cents
)
;
Incidentally, the Paying table is just a lookup list and looks like
this:
CREATE TABLE Paying
(
ID integer primary key
, Label text unique
)
;
Of course, it's often useful or simplest (eg when a human is looking
at the refunds) to be able to see all the columns joined together,
converting the Date into a readable YYYY-MM-DD string and just showing
null (ie blank) for missing (default) Paying and Amount values. I can
easily create a view to join the three tables together, like this:
create view [Orders Refunds Joined]
as
select
[Orders Refunds].ID as ID
, [Order ID]
, date(Date, 'localtime') as Date
, Reason
, Paying.Label as Paying
, Amount
from [Orders Refunds]
left join [Orders Refunds Paying] on [Orders Refunds].ID = [Orders
Refunds Paying].ID
left join [Orders Refunds Amount] on [Orders Refunds].ID = [Orders
Refunds Amount].ID
left join Paying on [Orders Refunds Paying].Paying = Paying.ID
;
Note above that I'm also converting the stored Date format (real
absolute date) into a more readable Date string adjusted for localtime
(eg '2008-02-06').
Now, if the user inserts a row into the [Orders Refunds Joined] view,
I want the database to redirect to instead insert in the three joined
tables, where needed. It will always insert into [Orders Refunds], but
will only insert into [Orders Refunds Paying] if there's an entry in
the Paying column (ie not null), and only insert into [Orders Refunds
Amount] if an Amount is given (ie not null). The new row in each of
the three tables should use the same ID, so they ar related. To do so,
I created this trigger:
create trigger [Insert Orders Refunds Joined]
instead of insert
on [Orders Refunds Joined]
for each row
begin
insert into [Orders Refunds]
(
ID
, [Order ID]
, Date
, Reason
)
select
new.ID
, new.[Order ID]
, julianday( new.Date, 'utc' ) -- convert date from string in
localtime to real
, new.Reason
;
insert into [Orders Refunds Paying]
(
ID
, Paying
)
select
last_insert_rowid()
, ( select ID from Paying where Label = new.Paying )
where new.Paying not null
;
insert into [Orders Refunds Amount]
(
ID
, Amount
)
select
last_insert_rowid()
, new.Amount
where new.Amount not null
;
end
;
Similarly, if the user deletes from the [Orders Refunds Joined] view,
I want to instead delete the related rows from [Orders Refunds], which
should in turn propagate deletes from [Orders Refunds Paying] and
[Orders Refunds Amount]. So my delete triggers are:
create trigger [Delete Orders Refunds Joined]
instead of delete
on [Orders Refunds Joined]
for each row
begin
delete from [Orders Refunds]
where old.ID = [Orders Refunds].ID
;
end
;
create trigger [Delete Orders Refunds]
before delete
on [Orders Refunds]
begin
delete from [Orders Refunds Paying]
where old.ID = [Orders Refunds Paying].ID
;
delete from [Orders Refunds Amount]
where old.ID = [Orders Refunds Amount].ID
;
end
;
So that takes care of insert and delete on the view. Lastly I need to
take care of any updates on the view, enacting changes instead on the
underlying joined tables. In the case of the fields in the [Orders
Refunds] table, that's fairly easy. I just have to create a trigger
for each field ([Order ID], Date, Reason) to update the corresponding
table field instead of the view field. The only tricky bit is
reversing the date string (in localtime) back to a real (in utc). So
the triggers are:
create trigger [Update Orders Refunds Joined Order ID]
instead of update of [Order ID]
on [Orders Refunds Joined]
for each row
begin
update [Orders Refunds]
set [Order ID] = new.[Order ID]
where [Orders Refunds].ID = new.ID
;
end
;
create trigger [Update Orders Refunds Joined Date]
instead of update of Date
on [Orders Refunds Joined]
for each row
begin
update [Orders Refunds]
set Date = julianday( new.Date, 'utc' )
where [Orders Refunds].ID = new.ID
;
end
;
create trigger [Update Orders Refunds Joined Reason]
instead of update of Reason
on [Orders Refunds Joined]
for each row
begin
update [Orders Refunds]
set Reason = new.Reason
where [Orders Refunds].ID = new.ID
;
end
;
Now for updating the Paying and Amount fields. Again, instead of the
view, I want to update the corresponding joined table for each. Paying
has to convert a text label to the related integer value. Because the
[Orders Refunds Paying] and [Orders Refunds Amount] tables are
designed to only contain a row if their value is not null, I have to:
1. Insert a row if it doesn't already exist for that ID, or
2. Delete the row if the value has changed to null, or
3. Simply update the value if the row exists and the new value is not
null.
So I have one trigger for updates to Paying, a second trigger for
updates to Amount, each of which performs either an insert, delete or
update:
create trigger [Update Orders Refunds Joined Paying]
instead of update of Paying
on [Orders Refunds Joined]
for each row
begin
-- insert if row doesn't exist and new value not null
insert into [Orders Refunds Paying]
(
ID
, Paying
)
select
new.ID
, (select ID from Paying where Label = new.Paying)
where new.Paying not null
and new.ID not in ( select ID from [Orders Refunds Paying] )
;
-- delete if row exists and new value is null
delete from [Orders Refunds Paying]
where new.Paying is null
and [Orders Refunds Paying].ID = new.ID
;
-- update if row exists and new value not null
update [Orders Refunds Paying]
set Paying = (select ID from Paying where Label = new.Paying)
where new.Paying not null
and [Orders Refunds Paying].ID = new.ID
;
end
;
create trigger [Update Orders Refunds Joined Amount]
instead of update of Amount
on [Orders Refunds Joined]
for each row
begin
-- insert if row doesn't exist and new value not null
insert into [Orders Refunds Amount]
(
ID
, Amount
)
select
new.ID
, new.Amount
where new.Amount not null
and new.ID not in ( select ID from [Orders Refunds Amount] )
;
-- delete if row exists and new value is null
delete from [Orders Refunds Amount]
where new.Amount is null
and [Orders Refunds Amount].ID = new.ID
;
-- update if row exists and new value not null
update [Orders Refunds Amount]
set Amount = new.Amount
where new.Amount not null
and [Orders Refunds Amount].ID = new.ID
;
end
;
So, is this the best way to tackle the objective of having updatable
views? Or is there a better way? Are there any bugs in my approach?
Any feedback appreciated.
Thanks,
Tom
BareFeet
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
