Re: [sqlite] Updatable views

[Fred Williams]

I agree very much with your suggestion.  Although all those vendor
specific "extensions" generally make the designers and coders lives
easier, the wheels tend to come off in onerous ways when a heavily
extended project migration is attempted.

Even when using a given vendor's product I have, based on past
experiences, made every effort to avoid the "gray" areas (IBM manuals
highlight extensions with gray background) of a particular vendor's
product.  In the long run it will serve a project well no matter the
project's size or level of complexity.

I have single user PC based projects that I have, over their life,
migrated to more than one vendors database. (Do I know how to pick a
loser?)  And I have participated in projects where a migration from the
likes of Oracle to SQL Server (no matter how ill advised) have been
made.

Therefore I strongly adhere to the SQL Standard whenever possible.  This
tends to cause less future work in the long run.  Although that approach
does not eliminate all conversion efforts, because most DB vendors can't
even get the SQL Standard right :-(

Fred

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Dennis Cote
> Sent: Tuesday, February 12, 2008 9:41 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Updatable views
>
>
> John Stanton wrote:
> > That ia a nice idea.  To have a pragma which specied the
> dialect.  There
> > could be "strict" or "ansi" and "mysql", "oracle",
> "sqlserver" etc etc.
> >   It would give tighter control over hard to track annoying
> minor syntax
> > errors.
> >
>
> I don't think we need anything that involved. Just a simple
> standard SQL
> mode and a the current SQLite mode with all its extensions in
> place. The
> second is needed as the default case for backward compatibility. The
> first would let those who care move to standard SQL syntax.
>
> I would further suggest that SQLite could change its behavior
> when the
> next major version release happens, and make the standard mode the
> default, since it is allowed to break backwards compatibility
> at that time.
>
> Users migrating from other databases generally have to make some
> modifications to their schema and SQL code since none of
> these systems
> are fully standard compliant, and most have some extensions
> that are not
> supported elsewhere. It would be relatively easy to make the changes
> needed to use standard SQL quoting when migrating to SQLite
> at that point.
>
> All these database products are continually moving to better
> support for
> the SQL standard. This would simply be another step along that road.
> Once the database is converted to use standard quoting in SQLite it
> would be portable to any of the other databases since they
> all support
> standard quoting. Similarly, databases created in SQLite
> using standard
> quoting would be more easily portable to any of these other databases
> if the need arises.
>
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[Dennis Cote]

John Stanton wrote:
> That ia a nice idea.  To have a pragma which specied the dialect.  There 
> could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. 
>   It would give tighter control over hard to track annoying minor syntax 
> errors.
> 

I don't think we need anything that involved. Just a simple standard SQL 
mode and a the current SQLite mode with all its extensions in place. The 
second is needed as the default case for backward compatibility. The 
first would let those who care move to standard SQL syntax.

I would further suggest that SQLite could change its behavior when the 
next major version release happens, and make the standard mode the 
default, since it is allowed to break backwards compatibility at that time.

Users migrating from other databases generally have to make some 
modifications to their schema and SQL code since none of these systems 
are fully standard compliant, and most have some extensions that are not 
supported elsewhere. It would be relatively easy to make the changes 
needed to use standard SQL quoting when migrating to SQLite at that point.

All these database products are continually moving to better support for 
the SQL standard. This would simply be another step along that road. 
Once the database is converted to use standard quoting in SQLite it 
would be portable to any of the other databases since they all support 
standard quoting. Similarly, databases created in SQLite using standard 
quoting would be more easily portable to any of these other databases 
if the need arises.

Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[Dennis Cote]

BareFeet wrote:
> 
> I thought I saw syntax of some other SQL engines that  
> permit if/then or case/when type branching within a trigger, but I may  
> be mistaken.
> 

A case/when expression is just that an expression. It can appear any 
where an expression can be used. It can contain multiple sub-select 
statements, but it can't be used to select between insert, update, and 
delete statements.

Some dialects of SQL support SQL procedures which could be used to do 
such things, but SQLite does not.


> 
> Yes, although now I'm keen to see fixed the apparent huge overhead of  
> the inefficiency that Steve raised about using updatable views ;-)
> 

Likewise, I think this is a very good optimization idea that should be 
implemented as soon as possible. I see he has created a ticket to get it 
on the radar at SQLite HQ.

Dennis Cote

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[John Stanton]

[EMAIL PROTECTED] wrote:
> John Stanton <[EMAIL PROTECTED]> wrote:
>> That ia a nice idea.  To have a pragma which specied the dialect.  There 
>> could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. 
>>   It would give tighter control over hard to track annoying minor syntax 
>> errors.
>>
> 
> And, it would multiple exponentially the number of test cases
> we have to write and maintain in order to adequately test the
> parser ;-)
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
A practical limitation.  Perhaps a simpler to administer approach would 
be an auxiliary program like lint to decouple the maintenance of the 
dialects from the ongoing enhancement of Sqlite proper.

> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[BareFeet]

Hi Dennis,

> I don't think there is any way in SQL to eliminate the redundant  
> lookups.

OK, thanks. I thought I saw syntax of some other SQL engines that  
permit if/then or case/when type branching within a trigger, but I may  
be mistaken.

>>  where new.Amount not null
>>  and new.ID not in ( select ID from [Orders Refunds Amount] )

> The only thing I see that could be done differently is to change the
> where clause in the insert case to use a correlated subquery.

>   select
> new.ID
>   , new.Amount
>   where new.Amount not null
>   and not exists
>   ( select ID from [Orders Refunds Amount]
>   where ID = new.ID)
>   ;
>
> This exists clause will exit the subquery as soon as it finds a  
> matching ID. It will also be able to use the index on the ID to do  
> an O(logN) lookup to find or eliminate the new.ID value.

OK, thanks. I didn't realize that "not exists" in this context would  
be faster than my original "not in" syntax. I thought they'd both exit  
when the first match is found.

> Because all three cases use the same index, all the pages needed  
> should be in the page cache after the first scan, so they should not  
> require any additional I/O, and hence should complete very quickly.

Ah yes, good point :-)

> While SQL does require doing some redundant work, it isn't really  
> that much extra, and I suspect that you would be hard pressed to  
> measure the extra overhead.

Yes, although now I'm keen to see fixed the apparent huge overhead of  
the inefficiency that Steve raised about using updatable views ;-)

Thanks for all your input. I'll hone my updatable views strategy and  
apply it to more cases to find any tangent situations.

Tom
BareFeet

  --
5000 computer accessories delivered anywhere in Australia:
http://www.tandb.com.au/forsale/?sig
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[P Kishor]

On 2/11/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> John Stanton <[EMAIL PROTECTED]> wrote:
> > That ia a nice idea.  To have a pragma which specied the dialect.  There
> > could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc.
> >   It would give tighter control over hard to track annoying minor syntax
> > errors.
> >
>
> And, it would multiple exponentially the number of test cases
> we have to write and maintain in order to adequately test the
> parser ;-)
>


One of the dangers of supporting "other standards" is that it becomes
hard to wean folks off of them when you do decide to go "pure."

Microsoft is experiencing a similar issue with IE. IE6 buggered up the
standards support royally, but enough people around the world used it
and made websites that were "compliant" with it that when MS made IE7
which hewed to the standards much better, all those websites broke.
So, they are now proposing a new quirks mode in IE8. A nice collection
of links debating both sides is at


Keep SQLite simple, and keep it compliant with whatever the single
ANSI SQL standard may be there out there. Users of other databases
that adhere to the same standards will feel at home. Others will
adapt.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[drh]

John Stanton <[EMAIL PROTECTED]> wrote:
> That ia a nice idea.  To have a pragma which specied the dialect.  There 
> could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. 
>   It would give tighter control over hard to track annoying minor syntax 
> errors.
> 

And, it would multiple exponentially the number of test cases
we have to write and maintain in order to adequately test the
parser ;-)

--
D. Richard Hipp <[EMAIL PROTECTED]>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[John Stanton]

That ia a nice idea.  To have a pragma which specied the dialect.  There 
could be "strict" or "ansi" and "mysql", "oracle", "sqlserver" etc etc. 
  It would give tighter control over hard to track annoying minor syntax 
errors.

Dennis Cote wrote:
> BareFeet wrote:
>> No, I have no MS legacy or habits. I use the  
>> square brackets for identifiers because I find that using double  
>> quotes doesn't catch errors. If I say select "column name that does  
>> not exist" I get a string back. But if I use square brackets SQLite  
>> gives me an error that the column doesn't exist, which is far more  
>> useful, especially when embedded in some function.
>>
> 
> Tom,
> 
> Yes I'm aware of that issue. It's an unfortunate side effect of SQLite's 
> mySQL compatibility extension that supports double quotes for string 
> literals. It's sad that because of this extension, you need to use the 
> MS compatible extension for identifiers so that you get useful error 
> messages.
> 
> It would be nice if SQLite added a pragma that could be set to disable 
> the non-standard extensions. It would have to default off for backwards 
> compatibility of course. This would allow users to use only the SQL 
> standard quoting rules, and still get proper error messages if they make 
> a typographical error.
> 
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[Dennis Cote]

BareFeet wrote:
> Hi All (especially Dennis I guess ;-) )
> 
> Focusing on the "instead of update" trigger in my example (copied  
> below), you'll notice that the trigger scans through the table [Orders  
> Refunds Amount] three times to find the matching ID, once for each of  
> insert, delete, update, whose where tests contain the following  
> (partial):
> 
> insert: where new.ID not in ( select ID from [Orders Refunds Amount] )
> delete: where [Orders Refunds Amount].ID = new.ID
> update: where [Orders Refunds Amount].ID = new.ID
> 
> When you look at the complete where clauses in each, you'll realize  
> that, by design, only one of the where clauses is ever true. So it  
> seems inefficient for the trigger to test three times. Is there a more  
> efficient method?
> 
> Quoting the relevant section of my original post:
> 
>> 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 ... which performs either an insert, delete or  
>> update:
> 
>> 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
>> ;
> 

Tom,

I don't think there is any way in SQL to eliminate the redundant lookups.

The only thing I see that could be done differently is to change the 
where clause in the insert case to use a correlated subquery.

insert into [Orders Refunds Amount]
(
  ID
, Amount
)
select
  new.ID
, new.Amount
where new.Amount not null
and not exists
( select ID from [Orders Refunds Amount]
where ID = new.ID)
;

This exists clause will exit the subquery as soon as it finds a matching 
ID. It will also be able to use the index on the ID to do an O(logN) 
lookup to find or eliminate the new.ID value.

The other cases already do O(logN) searches in the index to find the row 
to delete or update, and only need to do a very fast isnull check on 
that one row.

Because all three cases use the same index, all the pages needed should 
be in the page cache after the first scan, so they should not require 
any additional I/O, and hence should complete very quickly.

While SQL does require doing some redundant work, it isn't really that 
much extra, and I suspect that you would be hard pressed to measure the 
extra overhead.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[BareFeet]

Hi All (especially Dennis I guess ;-) )

Focusing on the "instead of update" trigger in my example (copied  
below), you'll notice that the trigger scans through the table [Orders  
Refunds Amount] three times to find the matching ID, once for each of  
insert, delete, update, whose where tests contain the following  
(partial):

insert: where new.ID not in ( select ID from [Orders Refunds Amount] )
delete: where [Orders Refunds Amount].ID = new.ID
update: where [Orders Refunds Amount].ID = new.ID

When you look at the complete where clauses in each, you'll realize  
that, by design, only one of the where clauses is ever true. So it  
seems inefficient for the trigger to test three times. Is there a more  
efficient method?

Quoting the relevant section of my original post:

> 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 ... which performs either an insert, delete or  
> update:

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

Thanks,
Tom
BareFeet

--
Widest range of Macs & accessories in Australia:
http://www.tandb.com.au/forsale/?sig

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[Dennis Cote]

BareFeet wrote:
> 
>> Since you can't use a create table statement in a trigger,
> 
> Yes, I thought of doing that and realized the limitation of triggers  
> not allowing create temporary table. Is this restriction part of  
> standard SQL, or just SQLite? Is it likely to change? It seems a  
> common need.
> 

Tom,

Standard SQL doesn't allow any SQL-Schema statements (basically any 
create or drop statements) in a trigger body. I doubt this will ever change.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[BareFeet]

Hi Stephen,

> I should note that there's a gross inefficiency when using triggers  
> to handle updates or deletes against views; SQLite does the  
> equivalent of this:
>
> For UPDATE  ...  WHERE ,  SQLite copies the entire  
> source view into the temp table.
>
> SELECT * INTO  FROM 
>
> Then, it iterates over , looking for rows that match  
> , and *then* runs the trigger on them.  This means that  
> if your source view is large, this will run slowly.

That's horrendous, and surprisingly (and exponentially) inefficient  
for such otherwise efficient software.

> I submitted a patch a long while ago to optimize this by turning the  
> initial temp-table population into "SELECT * INTO  FROM  
>  WHERE ", which worked much faster

Great thinking.

> but I don't think anything came of it.

I'm hoping it will now that Dennis has suggested mechanisms ;-) Thanks  
for your efforts.

Thanks,
Tom
BareFeet

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[Dennis Cote]

BareFeet wrote:
> 
> No, I have no MS legacy or habits. I use the  
> square brackets for identifiers because I find that using double  
> quotes doesn't catch errors. If I say select "column name that does  
> not exist" I get a string back. But if I use square brackets SQLite  
> gives me an error that the column doesn't exist, which is far more  
> useful, especially when embedded in some function.
> 

Tom,

Yes I'm aware of that issue. It's an unfortunate side effect of SQLite's 
mySQL compatibility extension that supports double quotes for string 
literals. It's sad that because of this extension, you need to use the 
MS compatible extension for identifiers so that you get useful error 
messages.

It would be nice if SQLite added a pragma that could be set to disable 
the non-standard extensions. It would have to default off for backwards 
compatibility of course. This would allow users to use only the SQL 
standard quoting rules, and still get proper error messages if they make 
a typographical error.

Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[BareFeet]

Hi Dennis,

Thanks for your reply. I really appreciate the feedback.

> This is a very nice set of triggers to handle the base tables of a  
> view
> I believe this is the best way to handle this.

Thanks, it's good to at least know that I'm heading the right way.

I believe this is the best way to handle this.

> (except for all the non-standard MS style quoting of identifiers  
> using square brackets :-)).

Wash your mouth ;-) No, I have no MS legacy or habits. I use the  
square brackets for identifiers because I find that using double  
quotes doesn't catch errors. If I say select "column name that does  
not exist" I get a string back. But if I use square brackets SQLite  
gives me an error that the column doesn't exist, which is far more  
useful, especially when embedded in some function.

> The only problem I see is with your use of the last_insert_rowid()  
> function in the instead of insert trigger.

Well spotted.

> If your new row has non-default values for both the Paying and  
> Amount fields, you will have a problem. After the insert into "Order  
> Refunds Paying" the value returned by last_insert_rowid() changes to  
> the rowid of the newly inserted row in that table.

I actually tested that scenario (and others) and it seems to work. It  
works in this case because all the joined tables have the same rowid  
for related records. So each insert in the subsequent tables is using  
the same rowid, so last_insert_rowid() gives the same result after  
each insert.

> You need to call last_insert_rowid() once and save the result for  
> use in both the subsequent inserts.

Yes. I will have to do that if the joined tables don't have the same  
rowid for related records (which is not the case in this example).

> Since you can't use a create table statement in a trigger,

Yes, I thought of doing that and realized the limitation of triggers  
not allowing create temporary table. Is this restriction part of  
standard SQL, or just SQLite? Is it likely to change? It seems a  
common need.

> you have to create a table to use for this purpose, which you can  
> update and query in the trigger. This could be a temporary table  
> that is created and initialized when the database is opened.

Oh I see, yes, I guess I could prefix the initiating insert in my  
program with a create temporary table, but since that would be outside  
of the SQL, my database would lose portability. That is to say, if I  
use my database outside of my program, such as via the command line,  
then inserting into the view will fail with some error like "table  
'New ID' doesn't exist".

In order to facilitate portability (ie my database will function in  
any environment, not just when running in my program), I guess I'll  
have to create a permanent (ie not temporary) table to track the  
last_row_id of each table.

>
Thanks,
Tom

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[Dennis Cote]

Stephen Oberholtzer wrote:
> I should note that there's a gross inefficiency when using triggers to
> handle updates or deletes against views; SQLite does the equivalent of
> this:
> 
> For UPDATE  ...  WHERE ,  SQLite copies the entire
> source view into the temp table.
> 
> SELECT * INTO  FROM 
> 
> Then, it iterates over , looking for rows that match
> , and *then* runs the trigger on them.  This means that if
> your source view is large, this will run slowly.
> 
> I submitted a patch a long while ago to optimize this by turning the
> initial temp-table population into "SELECT * INTO  FROM
>  WHERE ", which worked much faster, but I don't think
> anything came of it.
> 
> (my original msg to this list:
> http://readlist.com/lists/sqlite.org/sqlite-users/2/11029.html )
> 
> 
> 

Stephen,

This does seem like a good idea.

The SQLite mailing list doesn't pass files attached to submissions, so 
no one saw your patch. I would suggest creating at ticket at 
http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew and posting 
your message, or a link to it, along with your patch.

I suspect the patch itself will probably have to be modified, since 
SQLite recently underwent significant changes to its code generation 
routines.

As with all patches, it will be reviewed and accepted much faster if it 
passes the test suite.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[Stephen Oberholtzer]

I should note that there's a gross inefficiency when using triggers to
handle updates or deletes against views; SQLite does the equivalent of
this:

For UPDATE  ...  WHERE ,  SQLite copies the entire
source view into the temp table.

SELECT * INTO  FROM 

Then, it iterates over , looking for rows that match
, and *then* runs the trigger on them.  This means that if
your source view is large, this will run slowly.

I submitted a patch a long while ago to optimize this by turning the
initial temp-table population into "SELECT * INTO  FROM
 WHERE ", which worked much faster, but I don't think
anything came of it.

(my original msg to this list:
http://readlist.com/lists/sqlite.org/sqlite-users/2/11029.html )



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updatable views

[Dennis Cote]

BareFeet wrote:
> 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
> ;
> 
> 
> 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.
> 

Tom,

This is a very nice set of triggers to handle the base tables of a view 
(except for all the non-standard MS style quoting of identifiers using 
square brackets :-)). I believe this is the best way to handle this.

The only problem I see is with your use of the last_insert_rowid() 
function in the instead of insert trigger. If your new row has 
non-default values for both the Paying and Amount fields, you will have 
a problem.

After the insert into "Order Refunds Paying" the value returned by 
last_insert_rowid() changes to the rowid of the newly inserted row in 
that table. When you call it again to insert into "Orders Refunds 
Amount" you will get the wrong value (i.e. it is no longer the value of 
the ID column in the newly inserted row in "Orders Refunds" table).

You need to call last_insert_rowid() once and save the result for use in 
both the subsequent inserts. Since you can't use a create table 
statement in a trigger, you have to create a table to use for this 
purpose, which you can update and query in the trigger. This could be a 
temporary table that is created and initialized when the database is 
opened. Then you update and query that table to get the ids used to link 
the joined tables to the new record.

create temp table "New Id" (
"table name"text primary key,
id  integer
);
insert into "New Id" values ('Orders Refunds', null);

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
;
update "New Id"
set id = last_insert_rowid()
where "table name" = 'Orders Refunds';
insert into [Orders Refunds Paying]
(
  ID
, Paying
)
select
  ( select id from "New Id" where "table name" = 'Orders 
Refunds' )
, ( select ID from Paying where Label = new.Paying )
where new.Paying not null
;
insert into [Orders Refunds Amount]
(
  ID
, Amount
)
select
  ( select id from "New Id" where "table name" = 'Orders 
Refunds' )
, new.Amount
where new.Amount not null
;
end
;


HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Updatable views

[BareFeet]

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

[sqlite] Updatable views

[T]

Hi All,

I'm using triggers to make my views updatable (ie the user can edit  
the values view because SQLite will instead change the related source  
table value). Which method is best to facilitate this?:


1. Use the "instead of update on ViewName" syntax. Trigger on the  
update of the view as a whole (ie any column triggers the one update).


or:

2. Use the "instead of update of ColumnName on ViewName" syntax in a  
trigger for each column. Trigger on the update of each column/field  
individually.


It seems to me that triggering on the view as a whole would  
unnecessarily update multiple values/columns when only one is changed.  
On the other hand, adding a trigger for each column seems overkill.  
Which is the best way?


Below is the complete SQL of a simplified example, where I have an  
Orders table and a Products table, (which lists the Products in each  
order). I have an "Orders Calc" view which shows fields from the  
Orders table, along with a sum() calculation of the Products in that  
Order.


The result of the SQL by both methods is the same:

Testing method 1: Create a trigger for the view as a whole:
10001|Apple|22.0|1020.0|Cheque|2007-07-21|R1234|2007-07-21
10001|Apple|22.0|1020.0|Cheque|2007-07-22|R1234|2007-07-21
SQL error near line 85: You cannot change the Total, since it is  
calculated.


Testing method 2: Create a trigger for the view per column:
10001|Apple|22.0|1020.0|Cheque|2007-07-21|R1234|2007-07-21
10001|Apple|22.0|1020.0|Cheque|2007-07-23|R1234|2007-07-21
SQL error near line 154: You cannot change the Total, since it is  
calculated.


Any personal approaches or revelations welcome :-)

Thanks,
Tom

/*
Create the test tables and view.
*/

create table Orders
(
"Order ID" integer primary key autoincrement,
Supplier text,  -- name of supplier
Delivery real,  -- delivery cost in dollars
	"Paid Method" text,	-- method of payment, such as deposit, credit  
card, cash

"Paid Date" date, -- date that payment was sent
Receipt text,   -- payment receipt
Ordered date-- date that the order was sent
);

create table if not exists Products
(
"Order ID" integer,   -- Orders foreign key
Code text,  -- Product Code
Description text,   -- Product Description
Buy real,   -- Buy price I pay when ordering this item
Quantity integer-- Quantity of this product in this order
);

create view "Orders Calc"
as
select
Orders."Order ID" as "Order ID",
Supplier,
Delivery,
sum( Quantity * Buy ) + Delivery
as Total,
"Paid Method",
"Paid Date",
Receipt,
Ordered
from Orders
left join Products
on Orders."Order ID" = Products."Order ID"
group by Orders."Order ID";

/*
Insert Test data
*/

begin;
insert into Orders values ( 10001, 'Apple', 22.0, 'Cheque',  
'2007-07-21', 'R1234', '2007-07-21');

insert into Products values ( 10001, 'IPH8GB', 'iPhone 8GB', 499.0, 2 );
commit;

/*
Method 1: Create a trigger for the view as a whole
*/

create trigger "Update Orders Calc"
instead of update on "Orders Calc"
for each row
begin
update Orders
set
Supplier = new.Supplier,
Delivery = new.Delivery,
"Paid Method" = new."Paid Method",
"Paid Date" = new."Paid Date",
Receipt = new.Receipt,
Ordered = new.Ordered
where "Order ID" = new."Order ID";
select
case
when old.Total != new.Total
			then raise( abort, 'You cannot change the Total, since it is  
calculated.')

end;
end;

/*
Test method 1
*/

begin;
select 'Testing method 1: Create a trigger for the view as a whole:';
select * from "Orders Calc";
update "Orders Calc"
set "Paid Date" = '2007-07-22' where "Order ID" = 10001;
select * from "Orders Calc";
update "Orders Calc"
set Total = 300.0 where "Order ID" = 10001;
rollback;

/*
Method 2: Create a trigger for the view per column
*/

drop trigger if exists "Update Orders Calc";

create trigger "Update Orders Calc Supplier"
instead of update of Supplier on "Orders Calc"
for each row
begin
	update Orders set Supplier = new.Supplier where "Order ID" =  
new."Order ID";

end;

create trigger "Update Orders Calc Delivery"
instead of update of Delivery on "Orders Calc"
for each row
begin
	update Orders set Delivery = new.Delivery where "Order ID" =  
new."Order ID";

end;

create trigger "Update Orders Calc Paid Method"
instead of update of "Paid Method" on "Orders Calc"
for each row
begin
	update Orders set "Paid Method" = new."Paid Method" where "Order ID"