Daniel wrote:
> Is it safe do set PRAGMA synchronous = OFF when a
> transaction-safe file system is used?
>
> We are working on WinCE with TFAT (see below) - but this might
> be a general question.
> TFAT:
> ... By making file operations
> transaction-safe, TFAT stabilizes the file system and
> ensures that the file system is not corrupted when an interruption occurs.
One thing you'd need to ensure is that data is safe as well as the
filesystem.
It seems that TransactData provides this:
http://msdn.microsoft.com/en-us/library/aa915463.aspx
"By default, only modifications to a directory and the FAT are
backed up during a transaction using TFAT. To back up
modifications to the data of a file, you must set the TransactData
registry key to 1."
For database consistency, the other thing you'd need to ensure is
that file system transactions are committed in the same order as
they are scheduled. ForceWriteThrough or FILE_FLAG_WRITE_THROUGH
would be sufficient to ensure this.
"If you set this value and also set the ForceWriteThrough value to
1, all successful write operations are committed."
I don't know whether write through is necessary with TFAT but it
sounds like it may be:
http://msdn.microsoft.com/en-us/library/aa916300.aspx
"All databases and memory-mapped files must commit every WriteFile
operation to the FAT atomically and set the
FILE_FLAG_WRITE_THROUGH value to TRUE."
Donald wrote:
> A transaction in the database would usually involve multiple disk
> writes, and I don't see how the filesystem would know what constitutes
> an sqlite transaction.
SQLite would still need to keep a journal.
IIUC, synchronous = OFF, doesn't disable the journal, but merely
disables the syncing of the journal.
It looks to me like TFAT can be safe wrt keeping a consistent
database with synchronous = OFF, but, if that requires turning on
write through, then there is no gain as that would make every
write synchronous, and the journal will still be the same size.
Daniel wrote:
> We have a 14MB SQLite database on a 16MB flash disk. The journal
> file gets to big on some queries, which results in a SQLITE_FULL
> error.
>
> Any other ideas to make data storage secure?
I can't think how to implement transactions without a journal.
You don't have much room to play with here.
All I can suggest is reducing the size of the transactions (or
recording them more efficiently in the journal).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
