If the original author bothered to set TEMP_STORE to "always memory", I would take that as a strong indication that they are using TEMP tables for performance reasons.
Off the top of my head I would probably do: CREATE TABLE log (id integer primary key, timestamp integer, data_item_1 float, ...); CREATE TEMP TABLE log_buffer (id integer primary key, timestamp integer, data_item_1 float, ...); Insert 256 rows into log_buffer with id 0..255. Keep track of the current id in software. Logging executes the prepared statement: UPDATE log_buffer SET ... WHERE id = :CURR; and increments the rowid If the current rowid hits 128 (or 256, where it wraps to 0 without extra action if you use an uint8_t): BEGIN; INSERT INTO log (SELECT * FROM log_buffer WHERE id < 128); UPDATE log_buffer SET ... WHERE id <128); COMMIT; This would copy the buffered records to persistent storage while significantly reduding the IO load to flash. The copy over is fast and thus reduces the risk of corruption. Any data in the TEMP table would be lost during a hard power fail. Too bad. A copy over interrupted by the hard power fail would be rolled back on recovery. Hard luck. But the database would be quite resistant to corruption -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Ted Goldblatt Gesendet: Dienstag, 12. März 2019 22:42 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie On Tue, Mar 12, 2019 at 12:17 PM Simon Slavin <slav...@bigfraud.org> wrote: > Other posters have taken care of very important aspects of your > circumstances, but I wanted to mention one I didn't see anyone mention. > Settings. > > If you compile SQLite without changing compilation settings, and use > it without changing defaults, SQLite is extremely good at avoiding > corruption, and at recovering after corruption. This includes > corruption due to power-loss at any stage while changes are being made to the > database. > > However, settings can be made which improve SQLite for some specific > uses. They make it faster. Or use less memory. Or use less > filespace while working. Unfortunately some of them all sacrifice > harness against corruption. > > These settings can be made at three (or more ? not sure) different places: > > 1) Compilation settings when the SQLite API is compiled > 2) Extra parameters passed when the database is opened > 3) PRAGMA settings made at any time while the database is open > > To assess how 'hard' your use of SQLite is against corruption, you > would have to track down whether any of the above three have been done. > > 1) May or may not be easy. Do you know how SQLite is included in your > project ? Is it part of a library downloaded from somewhere or did > your programmer compile it themself ? If the former, you can assume > that whoever prepared the library didn't mess with default settings. > If the latter, can you track down the compilation settings they used ? > SQLite is built from (the combined) source as part of the project build. I don't see signs in the project options that any special (SQLite) settings are used. However, the interface is done through the CppSQLite3 interface layer. (There have been some local changes to CppSQLite3, but they appear to be limited to adding error logging.) There is also a local header file (that is, one written as part of the project) with what appear to be SQLite compilation options (SQLITE_OMIT_xxx, SQLITE_DEFAULT_CACHE_SIZE, SQLITE_THREADSAFE, etc. One of these is SQLITE_TEMP_STORE, which is set to "Always use memory" which strikes me a suspicious relative to a power fail problem. However, I cannot find anything that seems to reference most of these, and specifically not the TEMP_STORE define. And this file is only included by a project specific DB interface file that invokes CppSQLite3 methods but not by sqlite3.c, so I'm not sure of the point. > > 2) Do you have the source code for your project ? Can you find all > places where a database is opened ? If it uses the SQLite API > directly you can just search for "sqlite3_open". See whether you can > spot whether anything except file name & path are passed. > I have full sources. The DB opens are directly by CppSQLite3 (which uses sqlite_open_v2()), and all of those calls have OPEN_READONLY or OPEN_READWRITE as the 3rd param and 0 as the 4th. The CppSQLite3 methods themselves take only a filename. 3) Do you have the source code for your project ? Can you do a global > search for "PRAGMA" ? Only a few of the PRAGMAs reduce integrity. > Most of them are fine. But you can look them up and see for yourself. > It doesn't appear that any PRAGMAs appear outside the CppSQLite3_16.cpp and sqlite3.h files. > > The whole of the above is merely me being picky. Millions of SQLite > users just leave all settings at their defaults. But it seems to be > the sort of thing you're asking about. > It is. I am guessing that the problem isn't in SQLite, per se, but in the local config or usage. However, clues about where to look are always welcome... _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
