[sqlite] Question about foreign key

[Igor Korot]

Hi, ALL,
According to http://www.sqlite.org/foreignkeys.html the FOREIGN KEY
support is disabled by default.
In order to enable it I need to compile SQLITE with 2 defines undefined.

I downloaded the file sqlite-amalgamation-3071400.zip, unpacked it,
added .c and .h file to my project
and inspected them.

SQLITE_OMIT_FOREIGN_KEY can not be found in both .h files and I don't
see the #define of this constant
anywhere in the .c file.

Is foreign key documentation outdated?

Also one minor question: do I need both .h files or just one will suffice?

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

Re: [sqlite] transaction commit is successful despite I/O error

[Ryan Johnson]

On 19/10/2012 4:40 PM, Efim Dyadkin wrote:


Hi Richard,

You are right about the purpose of unlink but it is out of context. 
There are a transaction in progress and hot journal on disk. If 
journal can't be deleted by the end of transaction, the transaction 
can't be considered to be successfully finished. But current 
implementation of Sqlite considers this transaction as successfully 
committed!


The problem with file system going down during a transaction is a real 
problem that occurred to our customers. For them it was absolutely a 
disaster because they lost data they had successfully saved.


I guess unixDelete must propagate ENOENT error when it runs in the 
context of transaction. For applications it will be like "transaction 
failed due to IO error".


For a test I tried to remove "errno != ENOENT" unconditionally from 
unixDelete but immediately encountered a problem. When I tried to 
execute any command on an empty database e.g. "create table a (a1 
text)" I got I/O error. This is because Sqlite tried to remove 
non-existsing "--wal" file


I think you're on the right track: pagerOpenWalIfPresent() attempts to 
delete a file that may not exist (the comments say so!), so it should 
have been expecting the occasional ENOENT.


Ryan

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

Re: [sqlite] transaction commit is successful despite I/O error

[Dan Kennedy]

On 10/20/2012 09:14 PM, Simon Slavin wrote:


On 19 Oct 2012, at 9:40pm, Efim Dyadkin
wrote:


You are right about the purpose of unlink but it is out of context.
There are a transaction in progress and hot journal on disk. If
journal can't be deleted by the end of transaction, the transaction
can't be considered to be successfully finished.


This is not correct.  SQLite does not close the journal file at the
end of every transaction unless you have only a single connection to
the database and the journal mode set to DELETE, and that is not
common these days because creating and deleting files is so slow.
The times you should see a journal file deleted is when all
connections to the database have been closed: you've done a _close()
for every _open().


In WAL mode, the WAL file is held open by a connection until it
is closed. And only deleted after the number of connections to the
database drops to zero (the last connection usually unlinks it as
part of the sqlite3_close() call).

If you're running with journal_mode=DELETE (the default), the journal
file is always closed and unlinked at the end of each write
transaction.

If using journal_mode=TRUNCATE or journal_mode=PERSIST on unix, then
the journal file is always closed at the end of a write transaction.
However, on win32 (or any other system where it is impossible to
delete a file while the current or some other process has it open),
the journal file file descriptor may be held open between transactions.



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

Re: [sqlite] transaction commit is successful despite I/O error

[Simon Slavin]

On 19 Oct 2012, at 9:40pm, Efim Dyadkin  wrote:

> You are right about the purpose of unlink but it is out of context. There are 
> a transaction in progress and hot journal on disk. If journal can't be 
> deleted by the end of transaction, the transaction can't be considered to be 
> successfully finished.

This is not correct.  SQLite does not close the journal file at the end of 
every transaction unless you have only a single connection to the database and 
the journal mode set to DELETE, and that is not common these days because 
creating and deleting files is so slow.  The times you should see a journal 
file deleted is when all connections to the database have been closed: you've 
done a _close() for every _open().



You can ignore things like journal files, which file data is stored in, and 
where in the file it's stored.  SQLite should be creating and deleting its own 
files as it sees fit, and if you create, edit or delete any of them yourself 
then you can consider your database to be potentially wrong or corrupt.  Don't 
mess with any SQLite files.

>  But current implementation of Sqlite considers this transaction as 
> successfully committed!

This is how to know that a transaction is finished: you execute the SQL command 
"COMMIT" and the function call returns with the code SQLITE_OK rather than an 
error.  If this has happened, the transaction is successfully committed.  
Whether you happen to notice files on disk being created, resized, deleted or 
deletable is useful for monitoring whether database connections are open, but 
not to individual transactions.

> The problem with file system going down during a transaction is a real 
> problem that occurred to our customers. For them it was absolutely a disaster 
> because they lost data they had successfully saved.

If the file system goes down /during/ a transaction, your application did not 
save data.  Your application's user interface should not be indicating to the 
user that data is saved during a transaction.  It should do that only once the 
"COMMIT" is reported successful.

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

Re: [sqlite] transaction commit is successful despite I/O error

[Efim Dyadkin]

Hi Richard,

You are right about the purpose of unlink but it is out of context. There are a 
transaction in progress and hot journal on disk. If journal can't be deleted by 
the end of transaction, the transaction can't be considered to be successfully 
finished. But current implementation of Sqlite considers this transaction as 
successfully committed!

The problem with file system going down during a transaction is a real problem 
that occurred to our customers. For them it was absolutely a disaster because 
they lost data they had successfully saved.

I guess unixDelete must propagate ENOENT error when it runs in the context of 
transaction. For applications it will be like "transaction failed due to IO 
error".

For a test I tried to remove "errno != ENOENT" unconditionally from unixDelete 
but immediately encountered a problem. When I tried to execute any command on 
an empty database e.g. "create table a (a1 text)" I got I/O error. This is 
because Sqlite tried to remove non-existsing "-wal" file with this stack trace:

unixDelete,  FP=7fff44f133e0
sqlite3OsDelete, FP=7fff44f13420
pagerOpenWalIfPresent,   FP=7fff44f13460
sqlite3PagerSharedLock,  FP=7fff44f13520
lockBtree,   FP=7fff44f135d0
sqlite3BtreeBeginTrans,  FP=7fff44f13650
sqlite3InitOne,  FP=7fff44f13770
sqlite3Init, FP=7fff44f137b0
sqlite3ReadSchema,   FP=7fff44f137e0
sqlite3StartTable,   FP=7fff44f13980
yy_reduce,   FP=7fff44f140f0
sqlite3Parser,   FP=7fff44f14170
sqlite3RunParser,FP=7fff44f14220
sqlite3Prepare,  FP=7fff44f14300
sqlite3LockAndPrepare,   FP=7fff44f143a0
sqlite3_prepare, FP=7fff44f14410
sqlite3_exec,FP=7fff44f14520

Thanks,
Efim Dyadkin

 This e-mail, including any attached files, may contain confidential and 
privileged information for the sole use of the intended recipient. Any review, 
use, distribution, or disclosure by others is strictly prohibited. If you are 
not the intended recipient (or authorized to receive information for the 
intended recipient), please contact the sender by reply e-mail and delete all 
copies of this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users