Re: [sqlite] Deleting the sqlite journal file?

[Richard Hipp]

On Fri, Jan 21, 2011 at 9:18 PM, Artur Reilin  wrote:

> > We've thought about that before, but never could come up with a
> reasonable
> > suggestion.
> >
>
> Something like an hash key in every db and also in a journal, if it is
> created, the same key as well. So if the db hash key and journal hash key
> doesn't match, the journal will not be used.
>

So what if your system crashes (due to a power failure, for example) just as
it is writing the page that contains the hash or token in the database file,
such that the hash or token (along with the rest of that page) is corrupted?
 Then when power is restored, instead of repairing the damage using the
journal, SQLite would discard the journal and leave the database corrupted?

An how would you write the hash or token into the database file in the first
place?  You have to create a journal file before you make any changes to the
database, to provide for recovery following a crash.  But if you can't
create the journal without first writing the hash/token, then you are
deadlocked.




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



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

Re: [sqlite] Deleting the sqlite journal file?

[Artur Reilin]

> We've thought about that before, but never could come up with a reasonable
> suggestion.
>

Something like an hash key in every db and also in a journal, if it is
created, the same key as well. So if the db hash key and journal hash key
doesn't match, the journal will not be used.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Deleting the sqlite journal file?

[James Berry]

Richard,

On Jan 21, 2011, at 5:41 PM, Richard Hipp wrote:

> On Fri, Jan 21, 2011 at 8:27 PM, James Berry  wrote:
> 
>>   (1) Is there any API I can/should use to predictably get the name of
>> the journal file so that I can delete it, without "knowing" what is should
>> be?
>> 
> 
> The name of the journal is always the name of the original database file
> with either "-journal" or "-wal" appended.  Delete those two files when you
> delete the original database and you are always safe.  To change the journal
> filenames in any way would result in an incompatible file format, since it
> would mean that newer versions of SQLite would not be able to recover from
> crashes on older versions of SQLite.  We work really, really hard to avoid
> incompatibilities, so you may safely assume that the journal filenames will
> remain unchanged.
> 
> 
>> 
>>   (2) Are there any changes that can/should be made to sqlite3 so that
>> it can identify the bogus journal in this scenario and discard it?
>> 
> 
> We've thought about that before, but never could come up with a reasonable
> suggestion.
> 
> 
>> 
>>   (3) Should there be a flag to open_v2, or something, that tells it
>> to discard any extant journal?
>> 
> 
> Having that option on open_v2() would just confuse people into actually
> using it, which means they would more often end up deleting hot journal
> files that they really needed for crash recovery.  Such a flag would result
> in far more problems that it solves, I believe.

I see your point here. What if there were a separate api that I could hand a 
file to,
prior to calling open, that would cleanse the file. Though I can look for 
-journal
and -wal today, I don't know to look for -wbl and -swirl tomorrow.

Granted, if you add sqlite_cleanse(dbPath) you might have as many people
calling that as you would have using a new flag to open_v2.

Thanks for a piece of software I marvel at (nearly every day ;)

James




> 
> 
>> 
>> Thanks,
>> 
>> James
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Deleting the sqlite journal file?

[Scott Hess]

On Fri, Jan 21, 2011 at 5:33 PM, Pavel Ivanov  wrote:
>>        (2) Are there any changes that can/should be made
>> to sqlite3 so that it can identify the bogus journal in this
>> scenario and discard it?
>
> Define the word "bogus". How should SQLite understand that the journal
> and the database are unrelated?

I've wondered in the past if a db cookie couldn't be added, which has
to match the cookie in the primary database.  Even better if it could
change over commits so that one could tell if the journal mis-matches
because the database file was restored from backup or something.
Unfortunately, I never was able to work my way around legitimate
mis-matches (where the page the db's cookie is derived from went
awry).

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

Re: [sqlite] Deleting the sqlite journal file?

[Richard Hipp]

On Fri, Jan 21, 2011 at 8:27 PM, James Berry  wrote:

> I ran into an interesting problem today having to do with a left-over
> journal file.
>
> When I first initialize my app, my general strategy is this:
>
>- Delete src.db, tmp.db, dst.db
>- Copy a static copy of my database (src.db) to a well-known
> temporary place (tmp.db).
>- Make some changes to the database (add some indexes, etc)
>- If everything has succeeded, copy tmp.db -> dst.db
>
> The theory (was) that if there are any crashes during the initialization
> process, etc, that dst.db will never be valid and initialization will start
> from ground zero on the next launch.
>
> This fails, for the simply reason that if a crash occurs, a single file is
> left-over at the next startup: dst.db-journal. And when the journal is
> there, in conjunction with a fresh copy of the src.db, sqlite fails in
> various manners, as might (or might not) be expected.
>
> That background out of the way, three questions:
>
>(1) Is there any API I can/should use to predictably get the name of
> the journal file so that I can delete it, without "knowing" what is should
> be?
>

The name of the journal is always the name of the original database file
with either "-journal" or "-wal" appended.  Delete those two files when you
delete the original database and you are always safe.  To change the journal
filenames in any way would result in an incompatible file format, since it
would mean that newer versions of SQLite would not be able to recover from
crashes on older versions of SQLite.  We work really, really hard to avoid
incompatibilities, so you may safely assume that the journal filenames will
remain unchanged.


>
>(2) Are there any changes that can/should be made to sqlite3 so that
> it can identify the bogus journal in this scenario and discard it?
>

We've thought about that before, but never could come up with a reasonable
suggestion.


>
>(3) Should there be a flag to open_v2, or something, that tells it
> to discard any extant journal?
>

Having that option on open_v2() would just confuse people into actually
using it, which means they would more often end up deleting hot journal
files that they really needed for crash recovery.  Such a flag would result
in far more problems that it solves, I believe.


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



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

Re: [sqlite] Deleting the sqlite journal file?

[Simon Slavin]

On 22 Jan 2011, at 1:27am, James Berry wrote:

>   - Delete src.db, tmp.db, dst.db
>   - Copy a static copy of my database (src.db) to a well-known temporary 
> place (tmp.db).
>   - Make some changes to the database (add some indexes, etc)
>   - If everything has succeeded, copy tmp.db -> dst.db

Between steps 3 and 4 I hope you close the connection to the database.

Apart from that, I suggest you add some logic to look for the journal file 
before step 4 and generate an error message if one still exists.  Apart from 
that I see no problem with your procedure.

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

Re: [sqlite] Deleting the sqlite journal file?

[Pavel Ivanov]

>        (1) Is there any API I can/should use to predictably get the name of 
> the journal file so that I can delete it, without "knowing" what is should be?

Suffix -journal is hardcoded and won't change in the future to keep
compatibility (journals left by any previous version should be read by
next version).

>        (2) Are there any changes that can/should be made to sqlite3 so that 
> it can identify the bogus journal in this scenario and discard it?

Define the word "bogus". How should SQLite understand that the journal
and the database are unrelated?

>        (3) Should there be a flag to open_v2, or something, that tells it to 
> discard any extant journal?

That would defeat the purpose of creating journal. You'd better use
'pragma journal_mode = off' for that.


Pavel

On Fri, Jan 21, 2011 at 8:27 PM, James Berry  wrote:
> I ran into an interesting problem today having to do with a left-over journal 
> file.
>
> When I first initialize my app, my general strategy is this:
>
>        - Delete src.db, tmp.db, dst.db
>        - Copy a static copy of my database (src.db) to a well-known temporary 
> place (tmp.db).
>        - Make some changes to the database (add some indexes, etc)
>        - If everything has succeeded, copy tmp.db -> dst.db
>
> The theory (was) that if there are any crashes during the initialization 
> process, etc, that dst.db will never be valid and initialization will start 
> from ground zero on the next launch.
>
> This fails, for the simply reason that if a crash occurs, a single file is 
> left-over at the next startup: dst.db-journal. And when the journal is there, 
> in conjunction with a fresh copy of the src.db, sqlite fails in various 
> manners, as might (or might not) be expected.
>
> That background out of the way, three questions:
>
>        (1) Is there any API I can/should use to predictably get the name of 
> the journal file so that I can delete it, without "knowing" what is should be?
>
>        (2) Are there any changes that can/should be made to sqlite3 so that 
> it can identify the bogus journal in this scenario and discard it?
>
>        (3) Should there be a flag to open_v2, or something, that tells it to 
> discard any extant journal?
>
> Thanks,
>
> James
> ___
> 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] Deleting the sqlite journal file?

[James Berry]

I ran into an interesting problem today having to do with a left-over journal 
file.

When I first initialize my app, my general strategy is this:

- Delete src.db, tmp.db, dst.db
- Copy a static copy of my database (src.db) to a well-known temporary 
place (tmp.db).
- Make some changes to the database (add some indexes, etc)
- If everything has succeeded, copy tmp.db -> dst.db

The theory (was) that if there are any crashes during the initialization 
process, etc, that dst.db will never be valid and initialization will start 
from ground zero on the next launch.

This fails, for the simply reason that if a crash occurs, a single file is 
left-over at the next startup: dst.db-journal. And when the journal is there, 
in conjunction with a fresh copy of the src.db, sqlite fails in various 
manners, as might (or might not) be expected.

That background out of the way, three questions:

(1) Is there any API I can/should use to predictably get the name of 
the journal file so that I can delete it, without "knowing" what is should be?

(2) Are there any changes that can/should be made to sqlite3 so that it 
can identify the bogus journal in this scenario and discard it?

(3) Should there be a flag to open_v2, or something, that tells it to 
discard any extant journal?

Thanks,

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

Re: [sqlite] Problem adding SQLite.Interop.060.DLL to windows mobile project

[naresh pasnur]

Hi All,

I'm aware of that thread and discussions and my application works fine on
the mobile device.
I'm having issue while creating the cab file of my project.
Can someone please explain me how the below note can be done ?

(Note that when you create an installation CAB for production deployment,
the CAB can be configured to install the Interop DLL automatically. It is
only Visual Studio's deployment feature which will not do so automatically.)



Thanks and sorry if I'm posting this in a wrong forum,
Naresh


On Fri, Jan 21, 2011 at 5:50 PM, Dave Watkinson  wrote:

> http://www.google.com/
>
> gave me
>
> http://sqlite.phxsoftware.com/forums/p/1569/8573.aspx
>  
> which contains
>
> "This issue has been covered several times on the forum. The upshot is that
> Visual Studio will not deploy the Interop DLL automatically, so you will
> have to copy it to your device or emulator manually during development.
> Copy
> it to the same device folder to which System.Data.SQLite.dll is deployed."
>
>
> HTH
>
>
>
>
> On Fri, Jan 21, 2011 at 5:38 PM, Simon Slavin 
> wrote:
>
> >
> > On 21 Jan 2011, at 9:49pm, naresh pasnur wrote:
> >
> > > I'm developing a  windows mobile application using Visual Studio 2008.
> > > I'm able to add  "System.Data.SQLite.dll" to my project's reference.
> > > However, when I'm trying to add  "SQLite.Interop.060.DLL" to my
> project's
> > > reference, it's throwing this error
> > > *"A reference to SQLite.Interop.060.DLL cannot be added"
> > > *
> >
> > While you are free to post details of your problem here, it doesn't
> really
> > have anything to do with SQLite.  The SQLite team did not supply that
> DLL,
> > it was made up by someone else.  Someone with good understanding of
> VS2008
> > and DLLs is much better placed to solve your problem.  So you will
> probably
> > do better posting your problem in a VS2008 forum than here.
> >
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem adding SQLite.Interop.060.DLL to windows mobile project

[Dave Watkinson]

http://www.google.com/

gave me

http://sqlite.phxsoftware.com/forums/p/1569/8573.aspx
 
which contains

"This issue has been covered several times on the forum. The upshot is that
Visual Studio will not deploy the Interop DLL automatically, so you will
have to copy it to your device or emulator manually during development. Copy
it to the same device folder to which System.Data.SQLite.dll is deployed."


HTH




On Fri, Jan 21, 2011 at 5:38 PM, Simon Slavin  wrote:

>
> On 21 Jan 2011, at 9:49pm, naresh pasnur wrote:
>
> > I'm developing a  windows mobile application using Visual Studio 2008.
> > I'm able to add  "System.Data.SQLite.dll" to my project's reference.
> > However, when I'm trying to add  "SQLite.Interop.060.DLL" to my project's
> > reference, it's throwing this error
> > *"A reference to SQLite.Interop.060.DLL cannot be added"
> > *
>
> While you are free to post details of your problem here, it doesn't really
> have anything to do with SQLite.  The SQLite team did not supply that DLL,
> it was made up by someone else.  Someone with good understanding of VS2008
> and DLLs is much better placed to solve your problem.  So you will probably
> do better posting your problem in a VS2008 forum than here.
>
> 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] Problem adding SQLite.Interop.060.DLL to windows mobile project

[Simon Slavin]

On 21 Jan 2011, at 9:49pm, naresh pasnur wrote:

> I'm developing a  windows mobile application using Visual Studio 2008.
> I'm able to add  "System.Data.SQLite.dll" to my project's reference.
> However, when I'm trying to add  "SQLite.Interop.060.DLL" to my project's
> reference, it's throwing this error
> *"A reference to SQLite.Interop.060.DLL cannot be added"
> *

While you are free to post details of your problem here, it doesn't really have 
anything to do with SQLite.  The SQLite team did not supply that DLL, it was 
made up by someone else.  Someone with good understanding of VS2008 and DLLs is 
much better placed to solve your problem.  So you will probably do better 
posting your problem in a VS2008 forum than here.

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

Re: [sqlite] How can we put check constraints on a column

[Jay A. Kreibich]

On Fri, Jan 21, 2011 at 10:21:02AM +0530, Sunil Bhardwaj scratched on the wall:

> How can we put check constraints on a column in sqlite, while
> creating a table.

  A CHECK constraint can be defined as a column constraint or as a table
  constraint.  In SQLite, all CHECK constraints act as table-level
  constrains, in that all types of CHECK constraints can reference any
  column in the row.  Both columns and tables allow more than one
  check constraint.  On any INSERT or UPDATE, all the constraints
  are evaluated.  If any of them evaluate to zero (false), a violation
  is thrown.

  At the column level:

CREATE TABLE (
   ...,
   colName colType ... CHECK ( expr ) ...,
   ...
);

  And at the table level:

CREATE TABLE (
   ...,
   colName colType ...,
   CHECK( expr ),
   ...
);

> We want to restrict values for a column in a range say '1 to 10'.

  Lots of ways:


  CREATE TABLE tbl ( 
  i INTEGER CHECK ( i BETWEEN 1 AND 10 )
  );

  CREATE TABLE tbl (
  i INTEGER CHECK ( i >= 1 AND i <= 10 )
  );

  CREATE TABLE tbl (
  i INTEGER CHECK ( i >= 1 ) CHECK ( i <= 10 )
  );

  CREATE TABLE tbl (
  i INTEGER,
  CHECK ( i BETWEEN 1 AND 10 )
  );

  CREATE TABLE tbl (
  i INTEGER,
  CHECK ( i >= 1 AND i <= 10 )
  );

  CREATE TABLE tbl (
  i INTEGER,
  CHECK ( i >= 1 ),
  CHECK ( i <= 10 )
  );




  In all cases, you should get this:

  INSERT INTO tbl ( i ) VALUES ( 0 )=> Constraint violation
  INSERT INTO tbl ( i ) VALUES ( 1 )=> OK
  INSERT INTO tbl ( i ) VALUES ( 10 )   => OK
  INSERT INTO tbl ( i ) VALUES ( 11 )   => Constraint violation
  INSERT INTO tbl ( i ) VALUES ( NULL ) => OK

  Note that last one... If the check constraint evals to NULL, the
  operation is still allowed.

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem adding SQLite.Interop.060.DLL to windows mobile project

[naresh pasnur]

Hi,

I'm developing a  windows mobile application using Visual Studio 2008.
I'm able to add  "System.Data.SQLite.dll" to my project's reference.
However, when I'm trying to add  "SQLite.Interop.060.DLL" to my project's
reference, it's throwing this error
*"A reference to SQLite.Interop.060.DLL cannot be added"
*
When I build cab file of my project and tried to run the application, I'm
getting this error
*Can't find PInvoke DLL 'SQLite.Interop.DLL' *

I tried adding SQLite.Interop.060.DLL to the project as content and in its
properties I set it to "Content" and "Copy always"
But I'm still getting the error.

What I noticed to the file type of SQLite.Interop.060.DLL is not showing as
"Assembly Type" in the cab builder project?
If I somehow able to add the project reference, the issue will be fixed.

Can anyone please help me with this ?

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

Re: [sqlite] Deferred foreign key constraint bug?

[Steve Campbell]

In that case, we get the desired behavior.  Both calls to sqlite3_step return 
SQLITE_DONE because it is a deferred constraint.  When you execute the 
"COMMIT;", it then returns SQLITE_CONSTRAINT because of the constraint 
violation.

To summarize, to reproduce this problem, the statements must not be enclosed in 
a BEGIN-COMMIT block.

To be very explicit, here is the code that I used to test this:

sqlite3* pDatabase;
sqlite3_open( ":memory:", &pDatabase );

sqlite3_exec( pDatabase, "PRAGMA foreign_keys =on;", NULL, NULL, NULL );
sqlite3_exec( pDatabase, "CREATE TABLE t1(a PRIMARY KEY, b);", NULL, NULL, NULL 
);
sqlite3_exec( pDatabase, "CREATE TABLE t2(c REFERENCES t1(a) DEFERRABLE 
INITIALLY DEFERRED, d);", NULL, NULL, NULL );
sqlite3_exec( pDatabase, "INSERT INTO t1 VALUES(1, 2);", NULL, NULL, NULL );

// Does not violate constraint
sqlite3_exec( pDatabase, "INSERT INTO t2 VALUES(1, 3);", NULL, NULL, NULL );

sqlite3_exec( pDatabase, "BEGIN;", NULL, NULL, NULL );

sqlite3_stmt* pStatement1;
sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(2, 4);", -1, 
&pStatement1, NULL );

// Returns SQLITE_DONE because constraint is deferred
sqlite3_step( pStatement1 );

sqlite3_stmt* pStatement2;
sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(3, 6);", -1, 
&pStatement2, NULL);

// Returns SQLITE_DONE because constraint is deferred 
sqlite3_step( pStatement2 );

// Returns SQLITE_CONSTRAINT because both statements violate the constraint
sqlite3_exec( pDatabase, "COMMIT;", NULL, NULL, NULL );

sqlite3_finalize( pStatement2 );
sqlite3_finalize( pStatement1 );
sqlite3_close( pDatabase );

Steve


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jan
Sent: Friday, January 21, 2011 2:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Deferred foreign key constraint bug?

What happens with a transaction around your statements?

Am 21.01.2011 19:38, schrieb Steve Campbell:
> A lingering statement handle can prevent a deferred foreign key constraint 
> from being enforced.  Here is an example:
>
> sqlite3_open( ":memory:",&pDatabase );
> sqlite3_exec( pDatabase, "PRAGMA foreign_keys =on;", NULL, NULL, NULL );
> sqlite3_exec( pDatabase, "CREATE TABLE t1(a PRIMARY KEY, b);", NULL, NULL, 
> NULL );
> sqlite3_exec( pDatabase, "CREATE TABLE t2(c REFERENCES t1(a) DEFERRABLE 
> INITIALLY DEFERRED, d);", NULL, NULL, NULL );
> sqlite3_exec( pDatabase, "INSERT INTO t1 VALUES(1, 2);", NULL, NULL, NULL );
>
> // Does not violate constraint; will return SQLITE_OK
> sqlite3_exec( pDatabase, "INSERT INTO t2 VALUES(1, 3);", NULL, NULL, NULL )
>
> sqlite3_stmt* pStatement1;
> sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(2, 4);", 
> -1,&pStatement1, NULL);
>
> // Does violate constraint
> // Will return SQLITE_CONSTRAINT
> sqlite3_step( pStatement1 );
>
> sqlite3_stmt* pStatement2;
> sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(3, 6);", 
> -1,&pStatement2, NULL);
>
> // Note that pStatement1 has not been finalized yet
> // Does violate constraint
> // Should return SQLITE_CONSTRAINT, but it will return SQLITE_OK instead; 
> this is the malfunction
> sqlite3_step( pStatement2 );
>
> sqlite3_finalize( pStatement2 );
> sqlite3_finalize( pStatement1 );
> sqlite3_close( pDatabase );
>
> The last call to sqlite3_step should return SQLITE_CONSTRAINT, but it will 
> return SQLITE_OK instead.
>
> If you change the code to finalize statement1 first, then the last call to 
> sqlite3_step will return SQLITE_CONSTRAINT.  Also, if you declare the foreign 
> key constraint to be immediate instead of deferred, the last call to 
> sqlite3_step will return SQLITE_CONSTRAINT.
>
> To summarize, we can prevent foreign keys from being properly enforced if we 
> declare the constraints to be deferred and we leave lingering statement 
> handles open.
>
> Is this a bug?
>
> Much appreciated,
> Steve Campbell
>
> -
> This transmission (including any attachments) may contain confidential 
> information, privileged material (including material protected by the 
> solicitor-client or other applicable privileges), or constitute non-public 
> information. Any use of this information by anyone other than the intended 
> recipient is prohibited. If you have received this transmission in error, 
> please immediately reply to the sender and delete this information from your 
> system. Use, dissemination, distribution, or reproduction of this 
> transmission by unintended recipients is not authorized and may be unlawful.
> ___
> 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] Deferred foreign key constraint bug?

[Jan]

What happens with a transaction around your statements?

Am 21.01.2011 19:38, schrieb Steve Campbell:
> A lingering statement handle can prevent a deferred foreign key constraint 
> from being enforced.  Here is an example:
>
> sqlite3_open( ":memory:",&pDatabase );
> sqlite3_exec( pDatabase, "PRAGMA foreign_keys =on;", NULL, NULL, NULL );
> sqlite3_exec( pDatabase, "CREATE TABLE t1(a PRIMARY KEY, b);", NULL, NULL, 
> NULL );
> sqlite3_exec( pDatabase, "CREATE TABLE t2(c REFERENCES t1(a) DEFERRABLE 
> INITIALLY DEFERRED, d);", NULL, NULL, NULL );
> sqlite3_exec( pDatabase, "INSERT INTO t1 VALUES(1, 2);", NULL, NULL, NULL );
>
> // Does not violate constraint; will return SQLITE_OK
> sqlite3_exec( pDatabase, "INSERT INTO t2 VALUES(1, 3);", NULL, NULL, NULL )
>
> sqlite3_stmt* pStatement1;
> sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(2, 4);", 
> -1,&pStatement1, NULL);
>
> // Does violate constraint
> // Will return SQLITE_CONSTRAINT
> sqlite3_step( pStatement1 );
>
> sqlite3_stmt* pStatement2;
> sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(3, 6);", 
> -1,&pStatement2, NULL);
>
> // Note that pStatement1 has not been finalized yet
> // Does violate constraint
> // Should return SQLITE_CONSTRAINT, but it will return SQLITE_OK instead; 
> this is the malfunction
> sqlite3_step( pStatement2 );
>
> sqlite3_finalize( pStatement2 );
> sqlite3_finalize( pStatement1 );
> sqlite3_close( pDatabase );
>
> The last call to sqlite3_step should return SQLITE_CONSTRAINT, but it will 
> return SQLITE_OK instead.
>
> If you change the code to finalize statement1 first, then the last call to 
> sqlite3_step will return SQLITE_CONSTRAINT.  Also, if you declare the foreign 
> key constraint to be immediate instead of deferred, the last call to 
> sqlite3_step will return SQLITE_CONSTRAINT.
>
> To summarize, we can prevent foreign keys from being properly enforced if we 
> declare the constraints to be deferred and we leave lingering statement 
> handles open.
>
> Is this a bug?
>
> Much appreciated,
> Steve Campbell
>
> -
> This transmission (including any attachments) may contain confidential 
> information, privileged material (including material protected by the 
> solicitor-client or other applicable privileges), or constitute non-public 
> information. Any use of this information by anyone other than the intended 
> recipient is prohibited. If you have received this transmission in error, 
> please immediately reply to the sender and delete this information from your 
> system. Use, dissemination, distribution, or reproduction of this 
> transmission by unintended recipients is not authorized and may be unlawful.
> ___
> 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] Deferred foreign key constraint bug?

[Steve Campbell]

A lingering statement handle can prevent a deferred foreign key constraint from 
being enforced.  Here is an example:

sqlite3_open( ":memory:", &pDatabase );
sqlite3_exec( pDatabase, "PRAGMA foreign_keys =on;", NULL, NULL, NULL );
sqlite3_exec( pDatabase, "CREATE TABLE t1(a PRIMARY KEY, b);", NULL, NULL, NULL 
);
sqlite3_exec( pDatabase, "CREATE TABLE t2(c REFERENCES t1(a) DEFERRABLE 
INITIALLY DEFERRED, d);", NULL, NULL, NULL );
sqlite3_exec( pDatabase, "INSERT INTO t1 VALUES(1, 2);", NULL, NULL, NULL );

// Does not violate constraint; will return SQLITE_OK
sqlite3_exec( pDatabase, "INSERT INTO t2 VALUES(1, 3);", NULL, NULL, NULL )

sqlite3_stmt* pStatement1;
sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(2, 4);", -1, 
&pStatement1, NULL);

// Does violate constraint
// Will return SQLITE_CONSTRAINT
sqlite3_step( pStatement1 );

sqlite3_stmt* pStatement2;
sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(3, 6);", -1, 
&pStatement2, NULL);

// Note that pStatement1 has not been finalized yet
// Does violate constraint
// Should return SQLITE_CONSTRAINT, but it will return SQLITE_OK instead; this 
is the malfunction
sqlite3_step( pStatement2 );

sqlite3_finalize( pStatement2 );
sqlite3_finalize( pStatement1 );
sqlite3_close( pDatabase );

The last call to sqlite3_step should return SQLITE_CONSTRAINT, but it will 
return SQLITE_OK instead.

If you change the code to finalize statement1 first, then the last call to 
sqlite3_step will return SQLITE_CONSTRAINT.  Also, if you declare the foreign 
key constraint to be immediate instead of deferred, the last call to 
sqlite3_step will return SQLITE_CONSTRAINT.

To summarize, we can prevent foreign keys from being properly enforced if we 
declare the constraints to be deferred and we leave lingering statement handles 
open.

Is this a bug?

Much appreciated,
Steve Campbell

-
This transmission (including any attachments) may contain confidential 
information, privileged material (including material protected by the 
solicitor-client or other applicable privileges), or constitute non-public 
information. Any use of this information by anyone other than the intended 
recipient is prohibited. If you have received this transmission in error, 
please immediately reply to the sender and delete this information from your 
system. Use, dissemination, distribution, or reproduction of this transmission 
by unintended recipients is not authorized and may be unlawful.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Patch to allow DISTINCT in multi-argument aggregatefunctions

[Stephen Oberholtzer]

On Fri, Jan 21, 2011 at 5:51 AM, smart sl  wrote:
> Good news. I've asked a few days earlier that how could I use DISTINCT in
> group_concat with seperator specified meanwhile. It's lovely.

I know -- reading that thread is what inspired the patch.


-- 
-- 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] Joinery

[Ian Hardingham]

Works perfectly, many thanks Martin.

Ian

On 21/01/2011 11:49, Martin.Engelschalk wrote:
> select a.ID
>   from a
>   join b on b.aID = a.ID
>  where b.user = 'MyUser' 

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

Re: [sqlite] Joinery

[Martin.Engelschalk]

Hi,

select a.ID
   from a
   join b on b.aID = a.ID
  where b.user = 'MyUser'

The join is an inner join, so that only rowa of table a are selected 
where a rocord in b exists. This seems to be what you want.

Martin

Am 21.01.2011 12:33, schrieb Ian Hardingham:
> Hey guys.
>
> This is just an utterly simple question I know, but I still haven't got
> my head around it.  I have two tables:
>
> Table A
> int ID
>
> Table B
> int user
> int aID
>
> I need a query which selects all elements of Table A which are "owned"
> by a specific user, ie for which there is an entry with user, id in Table B.
>
> Thanks,
> Ian
> ___
> 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] Joinery

[Ian Hardingham]

Hey guys.

This is just an utterly simple question I know, but I still haven't got 
my head around it.  I have two tables:

Table A
int ID

Table B
int user
int aID

I need a query which selects all elements of Table A which are "owned" 
by a specific user, ie for which there is an entry with user, id in Table B.

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

Re: [sqlite] Patch to allow DISTINCT in multi-argument aggregatefunctions

[smart sl]

Good news. I've asked a few days earlier that how could I use DISTINCT in
group_concat with seperator specified meanwhile. It's lovely.

2011/1/21 Andy Gibbs 

> On Thursday, January 20, 2011 8:27 PM, Stephen Oberholtzer wrote:
>
>
> > Here's a quick diff.  If people (in particular, DRH) think it's worth
> > it, I'll write up the test cases and submit a more formal patch.
>
> Great patch!
>
> I'd be interested in a more formal patch with test cases, if you have the
> time.  There may be others on this list who would also be interested --
> there are a number of us that maintain "alternative" versions of sqlite...
> Of course, if you can get it into mainline sqlite, even better!
>
> 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

Re: [sqlite] Patch to allow DISTINCT in multi-argument aggregatefunctions

[Andy Gibbs]

On Thursday, January 20, 2011 8:27 PM, Stephen Oberholtzer wrote:


> Here's a quick diff.  If people (in particular, DRH) think it's worth
> it, I'll write up the test cases and submit a more formal patch.

Great patch!

I'd be interested in a more formal patch with test cases, if you have the 
time.  There may be others on this list who would also be interested --  
there are a number of us that maintain "alternative" versions of sqlite... 
Of course, if you can get it into mainline sqlite, even better!

Thanks!

Andy

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