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