Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4

I'd like to use reader-writer lock in the client code, but at this point I'm not sure if I can determine at which point SQLite is not writing. I mean, INSERT / UPDATE are most likely need a writer lock, but I don't know if SELECT is guaranteed to be read-only in its internal operation within

[sqlite] Performance degradation after 3.3.17 -> 3.5.4 upgrade

Hello everyone. I've discover performance degradation due to update 3.3.17 -> 3.5.4. This SQL query work very slowly: DELETE FROM population_stamp WHERE town_id IN ( SELECT DISTINCT town_id FROM population_stamp EXCEPT SELECT id FROM town );

[sqlite] Re: Performance degradation after 3.3.17 -> 3.5.4 upgrade

On Jan 20, 2008 3:32 PM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote: > Hello everyone. > I've discover performance degradation due to update 3.3.17 -> 3.5.4. > This SQL query work very slowly: > > DELETE FROM > population_stamp > WHERE > town_id IN ( > SELECT

RE: [sqlite] SQLite character comparisons

Hello All, Not trying to be antagonistic, but I'm curious to know how many of you agree with Darren's sentiments on this issue. To restate briefly, ANSI SQL-92 specifies that when comparing two character fields, trailing spaces should be ignored. Correct me if I'm wrong Darren, but you feel

Re: [sqlite] SQLite character comparisons

On Jan 20, 2008 10:19 AM, Fowler, Jeff <[EMAIL PROTECTED]> wrote: > > Not trying to be antagonistic, but I'm curious to know how many of you agree > with Darren's sentiments on this issue. Sqlite is DRH's project. Voting doesn't come into the picture anywhere. If you convince him to fix it then

Re: [sqlite] SQLite character comparisons

On Sunday, January 20, 2008 Fowler, Jeff wrote: > briefly, ANSI SQL-92 specifies that when comparing two character > fields, trailing spaces should be ignored. From SQL-92 (draft July 1992) section 4.6 > When values of unequal length are compared, if the collating > sequence for the comparison

Re: [sqlite] SQLite character comparisons

I would suggest that Sqlite is correct having regard to its universal variable length feature and single TEXT type. Compatibility would be achieved by not storing redundant trailing spaces if a string field is required. More efficient storage would be a bonus. Fowler, Jeff wrote: Hello

Re: [sqlite] Performance degradation after 3.3.17 -> 3.5.4 upgrade

On Jan 20, 2008 11:32 PM, <[EMAIL PROTECTED]> wrote: > "Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > > Hello everyone. > > I've discover performance degradation due to update 3.3.17 -> 3.5.4. > > This SQL query work very slowly: > > > > DELETE FROM > > population_stamp > >

Re: [sqlite] SQLite character comparisons

"Fowler, Jeff" <[EMAIL PROTECTED]> wrote: > I'm curious to know how many of you agree with Darren's > sentiments on this issue Changing the behavior of SQLite to ignore trailing spaces is not an option for SQLite version 3, since to do so would result in a incompatible file format All

Re: [sqlite] Performance degradation after 3.3.17 -> 3.5.4 upgrade

"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote: > Hello everyone. > I've discover performance degradation due to update 3.3.17 -> 3.5.4. > This SQL query work very slowly: > > DELETE FROM > population_stamp > WHERE > town_id IN ( > SELECT DISTINCT town_id FROM

Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4

SELECT is read only. Think of an Sqlite lock as a lock on the journal file. The critical action of an Sqlite insert or update is to transfer the contents of the journal file to disk and verify that the write to disk has completed (the ACID feature). Apart from that it is read only.

RE: [sqlite] SQLite character comparisons

At 11:19 AM -0500 1/20/08, Fowler, Jeff wrote: To restate briefly, ANSI SQL-92 specifies that when comparing two character fields, trailing spaces should be ignored. Correct me if I'm wrong Darren, but you feel this is a bad decision, and in fact SQLite's implementation of character comparison

Re: [sqlite] sql optimization question

Jay I've used a trigger to do this with good success. You'll need one trigger per table to cause the delete to cascade through the tree. Or if you know that you will always delete from the top level tree"parent" then just one trigger would probably suffice. Ken Jay

RE: [sqlite] SQLite character comparisons

I don't recall asking for a vote on the subject or asking anyone to agree with me, but thank you for your input anyway Jay. And I do appreciate Dr H. for adding the "COLLATE RTRIM" declaration! Regards, - Jeff From: Jay Sprenkle [mailto:[EMAIL PROTECTED]

Re: [sqlite] sql optimization question

On Sun, 20 Jan 2008 19:16:03 -0800 (PST), Ken wrote > Jay Sprenkle <[EMAIL PROTECTED]> wrote: >> I'm deleting a tree of data stored in sqlite and was looking for the >> most efficient way to do it. > > You'll need one trigger per table to cause the delete to cascade > through the tree. Watch out.

[sqlite] How do I add primary key on existing table?

Hi, I was trying to add a primary key to existing table but could not. "alter table custsales add constraint pk_custsales primary key (id, type)" Is this supported? Is there a different syntax to do this? best regards, Radzi.

Re: [sqlite] How do I add primary key on existing table?

On 21-Jan-2008, at 3:12 PM, Dan wrote: On Jan 21, 2008, at 1:24 PM, Mohd Radzi Ibrahim wrote: Hi, I was trying to add a primary key to existing table but could not. "alter table custsales add constraint pk_custsales primary key (id, type)" Is this supported? Is there a different syntax

Re: [sqlite] How do I add primary key on existing table?

On Jan 21, 2008, at 1:24 PM, Mohd Radzi Ibrahim wrote: Hi, I was trying to add a primary key to existing table but could not. "alter table custsales add constraint pk_custsales primary key (id, type)" Is this supported? Is there a different syntax to do this? Using ALTER TABLE to add a