Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Nicolas Williams]

On Tue, Jul 07, 2009 at 12:17:36AM +0100, Simon Slavin wrote:
> He didn't make up the rule.  Nor did I.  It's part of the standard for  
> mailing lists and usenet:
> 
> 

This is way off-topic now, but, to be fair, RFC1855 is not a standard,
it is an Informational status RFC -- it provides information.

That said, netiquette is a very good thing :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query by Day

[John Machin]

On 7/07/2009 10:13 AM, Rick Ratchford wrote:
> Hi Simon. 
> 
> Ah. So what I need to do then is to make the return of strftime of type INT.
> 
> Since I'm creating a recordset from an existing table (rather than creating
> a table itself), then I don't have the option to set the affinity of my
> newly created column Day to INT.
> 
> Can CAST(strftime('%d', Date), INTEGER) be used in this context, or is there
> another way?

It can be used, but not with a very productive outcome.

Consider getting answers faster by (a) trying things out yourself, e.g. 
type this at the sqlite3 command-line program:

select CAST(strftime('%d', '2009-06-30'), INTEGER);

and (b) looking at the docs; in this case 
http://www.sqlite.org/lang_expr.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] there is problem when getting data by sqlite3's c apis in signal-handling func

[liubin liu]

there is problem when getting data by sqlite3's c apis in signal-handling
func.


_


#include   // for printf()
#include  // for signal()
#include  // for alarm()
#include  // for system()
#include // for sqlite3_***



sqlite3 *db = NULL;


int sqlite3_helper_create_db (void);


int sqlite3_helper_insert_data (void);


// psf - sqlite3_prepare_v2()+sqlite3_step()+sqlite3_finalize()
int sqlite3_helper_get_data_psf_from_tb1 ( int id );

void timer_handler()
{
printf ( "# IN timer_handler()\n" );


int ret = -1;
int i = 9;

char *query_format2 = "SELECT * FROM ts2 WHERE id=%d;";
char *query_string = NULL;


sqlite3_stmt *p_stmt = NULL;

printf ( "# IN timer_handler(), ago   sqlite3_mprintf()\n" );
query_string = sqlite3_mprintf ( query_format2, i%500 );

printf ( "# IN timer_handler(), ago   sqlite3_prepare_v2()\n" );
ret = sqlite3_prepare_v2 ( db, query_string, -1, &p_stmt, NULL );
printf ( "# IN timer_handler(), ago   sqlite3_step()\n" );
ret = sqlite3_step ( p_stmt );
printf ( "# IN timer_handler(), ago   printf()\n" );
if ( SQLITE_ROW == ret )
printf ( "# IN timer_handler(), id: %d, length: %d\n", 
sqlite3_column_int( p_stmt, 0 ), sqlite3_column_int( p_stmt, 1 ) );
printf ( "# IN timer_handler(), ago   sqlite3_free_table()\n" );
sqlite3_finalize ( p_stmt );


alarm(1);
}

int inittimer()
{
signal ( SIGALRM, timer_handler );
alarm(1);
return 0;
}


int main ( void )
{
int ret = -1;
int i = 0;


// open db file
ret = sqlite3_open ( "testsignal.db", &db );

// create tables: ts1 and ts2
sqlite3_helper_create_db ();

// insert datas into the tables(ts1&ts2)
sqlite3_helper_insert_data ();

// init the timer
ret = inittimer();

// get datas from table ts1
for ( i=0; ; i++ )
ret = sqlite3_helper_get_data_psf_from_tb1 ( i );

// close db file
ret = sqlite3_close ( db );

// get the num of records
system ( "sqlite3 testsignal.db \"SELECT COUNT(*) FROM ts1;\"" );

// delete db file
//system ( "rm testsignal.db -rf" );

return 0;
}


int sqlite3_helper_create_db (void)
{
int ret = -1;
char *cr_tb1 = "CREATE TABLE ts1 (id INTEGER PRIMARY KEY, length
INTEGER, data CHAR(50));";
char *cr_tb2 = "CREATE TABLE ts2 (id INTEGER PRIMARY KEY, length
INTEGER, data CHAR(50));";

ret = sqlite3_exec ( db, cr_tb1, NULL, NULL, NULL );
//printf ( "ret: %d\n", ret );
ret = sqlite3_exec ( db, cr_tb2, NULL, NULL, NULL );
//printf ( "ret: %d\n", ret );
if ( SQLITE_OK == ret )
printf ( "# IN main(), create tables, DONE~\n" );


return 0;
}


int sqlite3_helper_insert_data (void)
{
int ret = -1;
int i = 0;

sqlite3_stmt *p_stmt = NULL;


char *sql_format1 = "INSERT OR REPLACE INTO ts1 (id, length, data)
VALUES (%d, %d, %Q);";
char *sql_format2 = "INSERT OR REPLACE INTO ts2 (id, length, data)
VALUES (%d, %d, %Q);";
char *sql = NULL;


ret = sqlite3_exec ( db, "BEGIN", NULL, NULL, NULL );
for ( i=0; i<500; i++ )
{
sql = sqlite3_mprintf ( sql_format1, i, i%10,
"datadatadatadatadatadatadatadatadatadatadata" );
ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
ret = sqlite3_step ( p_stmt );
sqlite3_free ( sql );
ret = sqlite3_finalize ( p_stmt );

sql = sqlite3_mprintf ( sql_format2, i, i%10,
"datadatadatadatadatadatadatadatadatadatadata" );
ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
ret = sqlite3_step ( p_stmt );
sqlite3_free ( sql );
ret = sqlite3_finalize ( p_stmt );
}
ret = sqlite3_exec ( db, "COMMIT", NULL, NULL, NULL );
if ( SQLITE_OK == ret )
printf ( "# IN main(), insert datas, DONE~\n" );

return 0;
}


int sqlite3_helper_get_data_psf_from_tb1 ( int id )
{
int ret = -1;

sqlite3_stmt *p_stmt = NULL;


char *query_format1 = "SELECT * FROM ts1 WHERE id=%d;";
char *query_string = NULL;

query_string = sqlite3_mprintf ( query_format1, id%500 );
ret = sqlite3_prepare_v2 ( db, query_string, -1, &p_stmt, NULL );
ret = sqlite3_step ( p_stmt );
if ( SQLITE_ROW == ret )
printf ( "# IN main(), id: %d, length: %d\n", sqlite3_column_int(
p_stmt, 0 ), sqlite3_column_int( p_stmt, 1 ) );
sqlite3_finalize ( p_stmt );

return 0;
}


-- 
View this message in context: 
http://www.nabble.com/there-is-problem-when-getting-data-by-sqlite3%27s-c-apis-in-signal-handling-func-tp24365955p24365955.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Query by Day

[Rick Ratchford]

Hi Simon. 

Ah. So what I need to do then is to make the return of strftime of type INT.

Since I'm creating a recordset from an existing table (rather than creating
a table itself), then I don't have the option to set the affinity of my
newly created column Day to INT.

Can CAST(strftime('%d', Date), INTEGER) be used in this context, or is there
another way?

Thanks!


Rick
 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies
Sent: Monday, July 06, 2009 4:44 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query by Day

2009/7/6 Rick Ratchford :
> Greetings!
>

Hi Rick,

> I'm having trouble with what I thought would be a simple SQL query.
>
>    SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as 
> Tops,
> IsSwingBtm1 as Btms " & _
>                "FROM TmpTable WHERE Day = 11"
>
> I'm trying to create a recordset where the only records returned are 
> those that fall on the 11th of the month.
>
> When I run this without the 'WHERE Day = 11", it returns all the 
> records in
> 3 columns, Day, IsSwingTop1 and IsSwingBtm1. Under the Day column, I 
> can see all the days as 01, 02...31.

That's good...

>
> I'm not sure where I'm making my logic error.

...and you think that there is a problem because?

It would really help if you explained what leads you to believe that there
is a logic error.

>
> Thanks.
>
> Rick
>

If you use typeof() around "strftime('%d',date)", it shows type TEXT.
You are comparing this with an INTEGER; never true.

Regards,
Simon
___
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] Replying to posts (was: plans for completion of INSERT ORREPLACE INTO?)

[Dennis Volodomanov]

> > He didn't make up the rule.  Nor did I.  It's part of the standard
> > for mailing lists and usenet:
> >
> > 

To be fair, there's no such thing as a "standard" in this matter - the
very first paragraph of that document says so.

Each mailing list has its own guidelines which are sometimes strictly
enforced and sometimes not...

   Dennis


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

[sqlite] Replying to posts (was: plans for completion of INSERT OR REPLACE INTO?)

[BareFeet]

> He didn't make up the rule.  Nor did I.  It's part of the standard  
> for mailing lists and usenet:
>
> 
>
> "- If you are sending a reply to a message or a posting be sure you  
> summarize the original at the top of the message, or include just  
> enough text of the original to give a context.

Amen!! :-)

Tom
BareFeet


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

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Simon Slavin]

On 6 Jul 2009, at 11:59pm, James Gregurich wrote:

> How's this...
>
> you have a pretty low threshold for "obnoxious." Frankly, I lack the
> desire and energy needed to keep up with the list of rules people make
> up. read the email or don't.

He didn't make up the rule.  Nor did I.  It's part of the standard for  
mailing lists and usenet:



Section 3.1.1 (in part)


"- If you are sending a reply to a message or a posting be sure you  
summarize the original at the top of the message, or include just  
enough text of the original to give a context. This will make
sure readers understand when they start to read your response. Since  
NetNews, especially, is proliferated by distributing the postings from  
one host to another, it is possible to see a response to a message  
before seeing the original. Giving context helps everyone. But do not  
include the entire original!"

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

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[James Gregurich]

On Jul 6, 2009, at 3:14 PM, Nicolas Williams wrote:

> On Mon, Jul 06, 2009 at 02:49:07PM -0700, James Gregurich wrote:
>> 1) Why on earth would you want to scroll all the way to the bottom of
>> a long email to get the response simply for the sake of "We read
>> English top to bottom."
>
> Any quoted context must be read before the reply or else is not
> necessary and should not have been quoted.  Thus top posting (as
> the practice is known) is obnoxious because it causes the reader to
> read the reply, read the quoted text, and then re-read the reply to
> understand it in its proper context, and if the quoted text was not
> necessary in order to understand your reply (as in this particular
> case), then it just wastes bandwidth and storage.
>
>> 2) This is going to be a challenge for me because I'm not writing a
>> fixed DB with a known schema.  I'm writing a higher-level data store
>> wrapper using SQLITE as the backing. The schema is configured at
>> runtime.
>
> I agree.  ISTM that SQLite3 ought to call either call DELETE triggers
> for all rows deleted by INSERT OR REPLACE, or UPDATE triggers for the
> row that had the same PRIMARY KEY and DELETE triggers for any other
> deleted rows.
>
> (To me INSERT OR REPLACE can equally be seen as deleting all  
> conflicting
> rows, then inserting a replacement row, or else as updating the row  
> that
> conflicts with the new row's PRIMARY KEY and deleting rows that  
> conflict
> on other unique indexes.  If the INSERT OR REPLACE has many input rows
> then this gets murkier in that which rows are updated or deleted may
> become non-deterministic, I think, so deleting all affected rows seems
> like the better solution.)
>
>> 3)  I found the following comment on the mysql dev site (relevant
>> since "replace into" is inspired by mysql). They apparently fire the
>> triggers as expected. It seems to me that your comments  
>> overcomplicate
>> the requirements. If you are going to delete a row, call the delete
>> triggersthat is all that is needed as best I can tell. However, I
>> do admit that I am not well educated on SQL db engines. I'm figuring
>> all this out as I go along. Feel free to tell me how absurdly wrong I
>> am.
>
> INSERT OR REPLACE is not a feature of the standard, so anything  
> goes...
>
> Nico
> -- 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



How's this...

you have a pretty low threshold for "obnoxious." Frankly, I lack the  
desire and energy needed to keep up with the list of rules people make  
up. read the email or don't. it doesn't matter to me. I don't make  
rules designed to get other people to adhere to my personal  
preferencesand it annoys me when I get lectured by others who  
expect me to follow some unwritten code of conduct just to suit their  
personal tastes. The adjective I'd use to counter "obnoxious" is  
"uptight."





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

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Nicolas Williams]

On Mon, Jul 06, 2009 at 02:49:07PM -0700, James Gregurich wrote:
> 1) Why on earth would you want to scroll all the way to the bottom of  
> a long email to get the response simply for the sake of "We read  
> English top to bottom."

Any quoted context must be read before the reply or else is not
necessary and should not have been quoted.  Thus top posting (as
the practice is known) is obnoxious because it causes the reader to
read the reply, read the quoted text, and then re-read the reply to
understand it in its proper context, and if the quoted text was not
necessary in order to understand your reply (as in this particular
case), then it just wastes bandwidth and storage.

> 2) This is going to be a challenge for me because I'm not writing a  
> fixed DB with a known schema.  I'm writing a higher-level data store  
> wrapper using SQLITE as the backing. The schema is configured at  
> runtime.

I agree.  ISTM that SQLite3 ought to call either call DELETE triggers
for all rows deleted by INSERT OR REPLACE, or UPDATE triggers for the
row that had the same PRIMARY KEY and DELETE triggers for any other
deleted rows.

(To me INSERT OR REPLACE can equally be seen as deleting all conflicting
rows, then inserting a replacement row, or else as updating the row that
conflicts with the new row's PRIMARY KEY and deleting rows that conflict
on other unique indexes.  If the INSERT OR REPLACE has many input rows
then this gets murkier in that which rows are updated or deleted may
become non-deterministic, I think, so deleting all affected rows seems
like the better solution.)

> 3)  I found the following comment on the mysql dev site (relevant  
> since "replace into" is inspired by mysql). They apparently fire the  
> triggers as expected. It seems to me that your comments overcomplicate  
> the requirements. If you are going to delete a row, call the delete  
> triggersthat is all that is needed as best I can tell. However, I  
> do admit that I am not well educated on SQL db engines. I'm figuring  
> all this out as I go along. Feel free to tell me how absurdly wrong I  
> am.

INSERT OR REPLACE is not a feature of the standard, so anything goes...

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

Re: [sqlite] referential integrity and INSERT OR REPLACE

[James Gregurich]

based on the test I just ran, it reports the first one encountered only.

On Jul 6, 2009, at 2:53 PM, Nicolas Williams wrote:

> On Sat, Jul 04, 2009 at 10:24:50AM +0200, Kees Nuyt wrote:
>> On Fri, 03 Jul 2009 14:38:43 -0700, James Gregurich
>>  wrote:
>>
>>>
>>> nuts. that makes INSERT OR REPLACE worthless if you have tables
>>> dependent on one another.
>>>
>>>
>>> Is there any way to manually get a list of records for which there
>>> would be a conflict if a given record was inserted?
>>
>> BEGIN;
>> INSERT the row, and observe any UNIQUE constraint failures.
>>
>> If there isn't one, the INSERT succeeded and you're done.
>
> Does SQLite3 report all those failuers in one attempt?  Or must one  
> loop
> to make this a robust conflict detection algorithm?
> ___
> 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] referential integrity and INSERT OR REPLACE

[Nicolas Williams]

On Sat, Jul 04, 2009 at 10:24:50AM +0200, Kees Nuyt wrote:
> On Fri, 03 Jul 2009 14:38:43 -0700, James Gregurich
>  wrote:
> 
> >
> >nuts. that makes INSERT OR REPLACE worthless if you have tables  
> >dependent on one another.
> >
> >
> >Is there any way to manually get a list of records for which there  
> >would be a conflict if a given record was inserted?
> 
> BEGIN;
> INSERT the row, and observe any UNIQUE constraint failures.
> 
> If there isn't one, the INSERT succeeded and you're done.

Does SQLite3 report all those failuers in one attempt?  Or must one loop
to make this a robust conflict detection algorithm?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[James Gregurich]

1) Why on earth would you want to scroll all the way to the bottom of  
a long email to get the response simply for the sake of "We read  
English top to bottom."

2) This is going to be a challenge for me because I'm not writing a  
fixed DB with a known schema.  I'm writing a higher-level data store  
wrapper using SQLITE as the backing. The schema is configured at  
runtime.

3)  I found the following comment on the mysql dev site (relevant  
since "replace into" is inspired by mysql). They apparently fire the  
triggers as expected. It seems to me that your comments overcomplicate  
the requirements. If you are going to delete a row, call the delete  
triggersthat is all that is needed as best I can tell. However, I  
do admit that I am not well educated on SQL db engines. I'm figuring  
all this out as I go along. Feel free to tell me how absurdly wrong I  
am.


http://dev.mysql.com/doc/refman/5.0/en/replace.html

> MySQL uses the following algorithm for REPLACE (and LOAD DATA ...  
> REPLACE):
>
> Try to insert the new row into the table
>
> While the insertion fails because a duplicate-key error occurs for a  
> primary key or unique index:
>
> Delete from the table the conflicting row that has the duplicate key  
> value
>
> Try again to insert the new row into the table
>


and in the comments...


> If you are using REPLACE INTO... triggers are fired in this order  
> (if delete of duplcate key is used):
> - before insert
> - before delete
> - after delete
> - after insert





On Jul 6, 2009, at 1:15 PM, Simon Slavin wrote:

> Please quote previous text above your response to it.  We read English
> top to bottom.
>
> On 6 Jul 2009, at 8:22pm, James Gregurich wrote:
>
>> On Jul 6, 2009, at 3:53 AM, Simon Slavin wrote:
>>
>>> It should not call DELETE triggers since it never deletes.  It  
>>> should
>>> call either INSERT triggers or UPDATE triggers depending on which  
>>> one
>>> it decides to do.
>>>
>>> In the meantime, you can do the same thing yourself: instead of
>>> calling INSERT OR REPLACE, test to see which one would happen then
>>> call either INSERT or UPDATE.  The triggers on both of those
>>> combinations will work correctly.
>
>> so you are suggesting that I put an INSERT in a C loop checking for a
>> constraint violation failure.
>
> You have pointed out an error I made.  I was thinking that REPLACE
> meant that only one existing row could be replaced.  This is wrong:
> the new row can replace any number of existing rows.  Thank you for
> spotting my error.  The documentation points it out, not quite as
> clearly, in the REPLACE section of
>
> http://www.sqlite.org/lang_conflict.html
>
> So any proper trigger structure would have to call a combination of
> all three types of triggers: INSERT, DELETE and UPDATE.  I can't think
> of a good way to manage this properly.  And that may be why INSERT OR
> REPLACE itself doesn't use triggers correctly: it's too complicated to
> work out which of the existing rows is being REPLACED.  And you can't
> call DELETE triggers instead, because they're intended to stop
> something being deleted, and it might be okay to DELETE this thing if
> you're immediately going to INSERT another row that satisfies the
> requirement.
>
> I can only suggest that you handle the constraints testing yourself,
> in your code.  You will know, from the design of your database,
> whether your new row should be considered a replacement for an
> existing one.  You're going to have to replace your INSERT OR REPLACE
> with your own check for other constraints, and then deciding in your
> own code what commands to execute.  I don't think it will be possible
> to write code to do this properly for an arbitrary database.
>
>>>  Or do the INSERT, allow it to fail if
>>> it will, then do the UPDATE.
>
> This previous suggestion of mine won't work at all, given that there's
> no way to tell which of the existing records you think you're  
> updating.
>
> I conclude that INSERT OR REPLACE isn't compatible with triggers, or
> that triggers have to support FOR EACH TRANSACTION as well as ROW and
> STATEMENT, or that INSERT OR REPLACE has to treat a primary key
> conflict differently to some other kind of conflict.  There are
> drawbacks to all three of these.  I can't think of a way to do it that
> suits SQLite's small/neat/obvious design criteria.
>
> Simon.
> ___
> 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] Query by Day

[Simon Davies]

2009/7/6 Rick Ratchford :
> Greetings!
>

Hi Rick,

> I'm having trouble with what I thought would be a simple SQL query.
>
>    SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as Tops,
> IsSwingBtm1 as Btms " & _
>                "FROM TmpTable WHERE Day = 11"
>
> I'm trying to create a recordset where the only records returned are those
> that fall on the 11th of the month.
>
> When I run this without the 'WHERE Day = 11", it returns all the records in
> 3 columns, Day, IsSwingTop1 and IsSwingBtm1. Under the Day column, I can see
> all the days as 01, 02...31.

That's good...

>
> I'm not sure where I'm making my logic error.

...and you think that there is a problem because?

It would really help if you explained what leads you to believe that
there is a logic error.

>
> Thanks.
>
> Rick
>

If you use typeof() around "strftime('%d',date)", it shows type TEXT.
You are comparing this with an INTEGER; never true.

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

[sqlite] Query by Day

[Rick Ratchford]

Greetings!
 
I'm having trouble with what I thought would be a simple SQL query.
 
SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as Tops,
IsSwingBtm1 as Btms " & _
"FROM TmpTable WHERE Day = 11"

I'm trying to create a recordset where the only records returned are those
that fall on the 11th of the month.
 
When I run this without the 'WHERE Day = 11", it returns all the records in
3 columns, Day, IsSwingTop1 and IsSwingBtm1. Under the Day column, I can see
all the days as 01, 02...31.
 
I'm not sure where I'm making my logic error.
 
Thanks.
 
Rick
 
 
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Simon Slavin]

Please quote previous text above your response to it.  We read English  
top to bottom.

On 6 Jul 2009, at 8:22pm, James Gregurich wrote:

> On Jul 6, 2009, at 3:53 AM, Simon Slavin wrote:
>
>> It should not call DELETE triggers since it never deletes.  It should
>> call either INSERT triggers or UPDATE triggers depending on which one
>> it decides to do.
>>
>> In the meantime, you can do the same thing yourself: instead of
>> calling INSERT OR REPLACE, test to see which one would happen then
>> call either INSERT or UPDATE.  The triggers on both of those
>> combinations will work correctly.

> so you are suggesting that I put an INSERT in a C loop checking for a
> constraint violation failure.

You have pointed out an error I made.  I was thinking that REPLACE  
meant that only one existing row could be replaced.  This is wrong:  
the new row can replace any number of existing rows.  Thank you for  
spotting my error.  The documentation points it out, not quite as  
clearly, in the REPLACE section of

http://www.sqlite.org/lang_conflict.html

So any proper trigger structure would have to call a combination of  
all three types of triggers: INSERT, DELETE and UPDATE.  I can't think  
of a good way to manage this properly.  And that may be why INSERT OR  
REPLACE itself doesn't use triggers correctly: it's too complicated to  
work out which of the existing rows is being REPLACED.  And you can't  
call DELETE triggers instead, because they're intended to stop  
something being deleted, and it might be okay to DELETE this thing if  
you're immediately going to INSERT another row that satisfies the  
requirement.

I can only suggest that you handle the constraints testing yourself,  
in your code.  You will know, from the design of your database,  
whether your new row should be considered a replacement for an  
existing one.  You're going to have to replace your INSERT OR REPLACE  
with your own check for other constraints, and then deciding in your  
own code what commands to execute.  I don't think it will be possible  
to write code to do this properly for an arbitrary database.

>>   Or do the INSERT, allow it to fail if
>> it will, then do the UPDATE.

This previous suggestion of mine won't work at all, given that there's  
no way to tell which of the existing records you think you're updating.

I conclude that INSERT OR REPLACE isn't compatible with triggers, or  
that triggers have to support FOR EACH TRANSACTION as well as ROW and  
STATEMENT, or that INSERT OR REPLACE has to treat a primary key  
conflict differently to some other kind of conflict.  There are  
drawbacks to all three of these.  I can't think of a way to do it that  
suits SQLite's small/neat/obvious design criteria.

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

Re: [sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT Works?

[Jay A. Kreibich]

On Mon, Jul 06, 2009 at 01:22:52PM -0400, Andy Froncioni scratched on the wall:
> 
> On 6-Jul-09, at 1:20 PM, D. Richard Hipp wrote:
> > At http://www.sqlite.org/compile.html under section 1.6, it says you
> > cannot use SQLITE_OMIT_ compile-time options with the amalgamation.
> > The documentation never says you cannot use
> > SQLITE_ENABLE_UPDATE_DELETE_LIMIT with the amalgamation, but it
> > should, since SQLITE_ENABLE_ is really just SQLITE_OMIT_ with the
> > default behavior inverted, and so SQLITE_ENABLE_ will not work with
> > the amalgamation for exactly the same reason that SQLITE_OMIT_ will  
> > not.
> 
> See?  That's how I read this, too.   Oh, well... I've wasted more time
> on stupider things than this, so it's no use cying over spilled  
> milk.  :-)

  This, at least, seems pretty clear:


  http://sqlite.org/compile.html#enable_update_delete_limit

   SQLITE_ENABLE_UPDATE_DELETE_LIMIT

 This option enables an optional ORDER BY and LIMIT clause on
 UPDATE and DELETE statements.

 If this option is defined, then it must also be defined when
 using the 'lemon' tool to generate a parse.c file. Because of
 this, this option may only be used when the library is built
 from source, not from the amalgamation or from the collection
 of pre-packaged C files provided for non-Unix like platforms
 on the website.



  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[James Gregurich]

so you are suggesting that I put an INSERT in a C loop checking for a  
constraint violation failure. if I get one, I use errmsg to get the  
"column  is not unique" message and extract . Then, I issue a  
DELETE to clear out rows that match the value of . is that correct?





On Jul 6, 2009, at 3:53 AM, Simon Slavin wrote:

> (Sorry, hit 'Send' before I meant to.)
>
> On 6 Jul 2009, at 6:34am, James Gregurich wrote:
>
>> a question for the sqlite developers.
>>
>> The inability of "INSERT OR REPLACE" to maintain referential  
>> integrity
>> leaves me with no mechanism to implement a feature in my project that
>> I was intending to provide.  Are there any plans to add in the
>> functionality for "INSERT OR REPLACE" to call delete triggers so that
>> referential integrity can be maintained?
>
> It should not call DELETE triggers since it never deletes.  It should
> call either INSERT triggers or UPDATE triggers depending on which one
> it decides to do.
>
> In the meantime, you can do the same thing yourself: instead of
> calling INSERT OR REPLACE, test to see which one would happen then
> call either INSERT or UPDATE.  Or do the INSERT, allow it to fail if
> it will, then do the UPDATE.  The triggers on both of those
> combinations will work correctly.
>
> Simon.
> ___
> 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] SQLITE_ENABLE_UPDATE_DELETE_LIMIT Works?

[Dan]

On Jul 7, 2009, at 1:56 AM, Andy Froncioni wrote:

>> On Jul 6, 2009, at 11:08 PM, Andy Froncioni wrote:
>>
>>> I know I'm probably doing something wrong, but I can't seem to get
>>> a simple UPDATE...LIMIT query working.
>>>
>>> sqlite> UPDATE customers SET lock=1 WHERE lock!=1 LIMIT 10;
>>> SQL error: near "LIMIT": syntax error
>>>
>>>
>>> I have compiled sqlite3 with the following configure options:
>>>
>>> % CFLAGS="-Os -DSQLITE_ENABLE_RTREE=1 -
>>> DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1" ./configure
>>>
>>> And, yes, I'm sure I'm executing the freshly-compiled sqlite3, and
>>> not
>>> the system-installed version.  :-)
>>
>> You will need to build from the source tarball (the one with the
>> unsupported configure script), not the amalgamation package for
>> this to work.
>
> Do you know if it's possible to convert an amalgamation package so  
> that
> it supports compile options?   I'm trying to get the perl module
> DBD:SQLite
> to compile and include RTREE and UPDATE_DELETE_LIMIT.
>
> Any ideas?   I tried putting a
>
> #define SQLITE_ENABLE_UPDATE_DELETE_LIMIT 1
>
> into sqlite3.h, but that didn't work.  :(

The makefiles in the full source tarball support a target "sqlite3.c".
Build this target with SQLITE_UPDATE_DELETE_LIMIT defined. Then use
the resulting sqlite3.c file in the perl DBD build.

You will need to specify SQLITE_UPDATE_DELETE_LIMIT when compiling
the special sqlite3.c as part of the DBD build as well.

Dan.




> Thanks,
>
> Andy
>
> Andy Froncioni
> ---
> My other signature is a pun.
> ---
> a.fronci...@sympatico.ca
> (514) 829-0255
>
>
>
>
> ___
> 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] SQLITE_ENABLE_UPDATE_DELETE_LIMIT Works?

[Andy Froncioni]

> On Jul 6, 2009, at 11:08 PM, Andy Froncioni wrote:
>
>> I know I'm probably doing something wrong, but I can't seem to get
>> a simple UPDATE...LIMIT query working.
>>
>> sqlite> UPDATE customers SET lock=1 WHERE lock!=1 LIMIT 10;
>> SQL error: near "LIMIT": syntax error
>>
>>
>> I have compiled sqlite3 with the following configure options:
>>
>> % CFLAGS="-Os -DSQLITE_ENABLE_RTREE=1 -
>> DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1" ./configure
>>
>> And, yes, I'm sure I'm executing the freshly-compiled sqlite3, and  
>> not
>> the system-installed version.  :-)
>
> You will need to build from the source tarball (the one with the
> unsupported configure script), not the amalgamation package for
> this to work.

Do you know if it's possible to convert an amalgamation package so that
it supports compile options?   I'm trying to get the perl module  
DBD:SQLite
to compile and include RTREE and UPDATE_DELETE_LIMIT.

Any ideas?   I tried putting a

#define SQLITE_ENABLE_UPDATE_DELETE_LIMIT 1

into sqlite3.h, but that didn't work.  :(

Thanks,

Andy

Andy Froncioni
---
My other signature is a pun.
---
a.fronci...@sympatico.ca
(514) 829-0255




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

Re: [sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT Works?

[Andy Froncioni]

On 6-Jul-09, at 1:20 PM, D. Richard Hipp wrote:
> At http://www.sqlite.org/compile.html under section 1.6, it says you
> cannot use SQLITE_OMIT_ compile-time options with the amalgamation.
> The documentation never says you cannot use
> SQLITE_ENABLE_UPDATE_DELETE_LIMIT with the amalgamation, but it
> should, since SQLITE_ENABLE_ is really just SQLITE_OMIT_ with the
> default behavior inverted, and so SQLITE_ENABLE_ will not work with
> the amalgamation for exactly the same reason that SQLITE_OMIT_ will  
> not.

See?  That's how I read this, too.   Oh, well... I've wasted more time
on stupider things than this, so it's no use cying over spilled  
milk.  :-)

Thanks,

Andy


Andy Froncioni
---
My other signature is a pun.
---
a.fronci...@sympatico.ca
(514) 829-0255




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

Re: [sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT Works?

[D. Richard Hipp]

On Jul 6, 2009, at 1:03 PM, Andy Froncioni wrote:
>>
>> You will need to build from the source tarball (the one with the
>> unsupported configure script), not the amalgamation package for
>> this to work.
>
> Thanks a bunch.  I'll try that...
>
> But I don't remember reading that the tarball I used was not suitable
> for these
> compile options.  Where does it say this?
>


At http://www.sqlite.org/compile.html under section 1.6, it says you  
cannot use SQLITE_OMIT_ compile-time options with the amalgamation.   
The documentation never says you cannot use  
SQLITE_ENABLE_UPDATE_DELETE_LIMIT with the amalgamation, but it  
should, since SQLITE_ENABLE_ is really just SQLITE_OMIT_ with the  
default behavior inverted, and so SQLITE_ENABLE_ will not work with  
the amalgamation for exactly the same reason that SQLITE_OMIT_ will not.


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT Works?

[Andy Froncioni]

On 6-Jul-09, at 12:18 PM, Dan wrote:

>
> On Jul 6, 2009, at 11:08 PM, Andy Froncioni wrote:
>
>> I know I'm probably doing something wrong, but I can't seem to get
>> a simple UPDATE...LIMIT query working.
>>
>> sqlite> UPDATE customers SET lock=1 WHERE lock!=1 LIMIT 10;
>> SQL error: near "LIMIT": syntax error
>>
>>
>> I have compiled sqlite3 with the following configure options:
>>
>> % CFLAGS="-Os -DSQLITE_ENABLE_RTREE=1 -
>> DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1" ./configure
>>
>> And, yes, I'm sure I'm executing the freshly-compiled sqlite3, and  
>> not
>> the system-installed version.  :-)
>
> You will need to build from the source tarball (the one with the
> unsupported configure script), not the amalgamation package for
> this to work.

Thanks a bunch.  I'll try that...

But I don't remember reading that the tarball I used was not suitable  
for these
compile options.  Where does it say this?

Cheers,

Andy

Andy Froncioni
---
My other signature is a pun.
---
a.fronci...@sympatico.ca
(514) 829-0255




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

Re: [sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT Works?

[Dan]

On Jul 6, 2009, at 11:08 PM, Andy Froncioni wrote:

> I know I'm probably doing something wrong, but I can't seem to get
> a simple UPDATE...LIMIT query working.
>
> sqlite> UPDATE customers SET lock=1 WHERE lock!=1 LIMIT 10;
> SQL error: near "LIMIT": syntax error
>
>
> I have compiled sqlite3 with the following configure options:
>
> % CFLAGS="-Os -DSQLITE_ENABLE_RTREE=1 -
> DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1" ./configure
>
> And, yes, I'm sure I'm executing the freshly-compiled sqlite3, and not
> the system-installed version.  :-)

You will need to build from the source tarball (the one with the
unsupported configure script), not the amalgamation package for
this to work.

Dan.


> Can anyone help me?
>
> Thanks,
>
> Andy
>
>
>
>
> ___
> 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

[sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT Works?

[Andy Froncioni]

I know I'm probably doing something wrong, but I can't seem to get
a simple UPDATE...LIMIT query working.

sqlite> UPDATE customers SET lock=1 WHERE lock!=1 LIMIT 10;
SQL error: near "LIMIT": syntax error


I have compiled sqlite3 with the following configure options:

% CFLAGS="-Os -DSQLITE_ENABLE_RTREE=1 - 
DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1" ./configure

And, yes, I'm sure I'm executing the freshly-compiled sqlite3, and not  
the system-installed version.  :-)


Can anyone help me?

Thanks,

Andy




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

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Simon Slavin]

(Sorry, hit 'Send' before I meant to.)

On 6 Jul 2009, at 6:34am, James Gregurich wrote:

> a question for the sqlite developers.
>
> The inability of "INSERT OR REPLACE" to maintain referential integrity
> leaves me with no mechanism to implement a feature in my project that
> I was intending to provide.  Are there any plans to add in the
> functionality for "INSERT OR REPLACE" to call delete triggers so that
> referential integrity can be maintained?

It should not call DELETE triggers since it never deletes.  It should  
call either INSERT triggers or UPDATE triggers depending on which one  
it decides to do.

In the meantime, you can do the same thing yourself: instead of  
calling INSERT OR REPLACE, test to see which one would happen then  
call either INSERT or UPDATE.  Or do the INSERT, allow it to fail if  
it will, then do the UPDATE.  The triggers on both of those  
combinations will work correctly.

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

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Simon Slavin]

On 6 Jul 2009, at 6:34am, James Gregurich wrote:

> The inability of "INSERT OR REPLACE" to maintain referential integrity
> leaves me with no mechanism to implement a feature in my project that
> I was intending to provide.  Are there any plans to add in the
> functionality for "INSERT OR REPLACE" to call delete triggers so that
> referential integrity can be maintained?

It should not call DELETE triggers.  It should call either INSERT or  
UPDATE triggers depending on which one it decides to do.

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