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
