> Le 15 févr. 2017 à 17:41, Olivier Mascia <o...@integral.be> a écrit : > >> Le 15 févr. 2017 à 16:04, Olivier Mascia <o...@integral.be> a écrit : >> >> Dear all, >> >> https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep makes it >> clear that connections (other than the one used for the backup feature) >> which writes in between calls to sqlite3_backup_step() will force the next >> sqlite3_backup_step() to start again the whole copy processing. >> >> What about databases set for journal_mode=WAL? >> Is backup_step() also restarting the whole procedure after any write commit >> happen? >> Or only if some checkpointing occurs? >> >> Said differently, is the whole scheme of sqlite3_backup_* API meant to build >> a copy of the database, including whatever would be in the WAL journal, or >> is the goal to get a copy of what is seen by the implicit read transaction >> of the connection doing the backup? > > Answering my own post, now that I have prepared and experimented with a test > program: backup_step() indeed restarts its work for *any* write occurring > during the backup, even though the test db is set for journal_mode=WAL and no > checkpointing occurs (verified) at the end of the commit of any of those test > writes. > > Has anyone ever wanted the sqlite3_backup_* API to be able to proceed > differently, when journal_mode=WAL? > Something along the lines of starting a transaction (deferred) on _init, > _step() copying the database as seen by this transaction, and _finish() > releasing that read-transaction? > > It wouldn’t copy changes occurring after the backup/copy has started, of > course, but would copy a complete stable snapshot of the database, without > impacting (excepting the backup I/O) neither the readers or being > cancelled/restarted by the occasional writer? Passive checkpointing as done > by default by Sqlite3 wouldn’t even impact the process. > > I for sure have a nice use case for this. :)
A good approach, which seems to run fine according to my additional tests is to drive the backup by a single call to sqlite3_backup_step() with a negative integer parameter. This process all the database pages in one iteration, as described by the documentation. Proceeding this way, I solve my primary need (using a journal_mode WAL database): making a backup of the database while it is possibly queried and updated by other connections. Without any possibility for the backup to run endless due to restarts occurring too often. I’ll use that scheme for now and it essentially kills the above proposal. The only downside is that I loose the capability to monitor (or inform users if needed) of the backup progress. Some alternative sqlite3_backup_step() version could get a callback function (and a count of pages) and would call the callback function every such pages processed and one last time when complete. There might even be provision to allow the callback function to request premature termination of the copy, if needed. int backup_callback(sqlite3_backup*, void* user_data); Return value SQLITE_OK or SQLITE_ABORT (or any other convention) would allow the callback to request interruption or continuation. Remaining and total pages could be obtained through the existing sqlite3_backup_remaining and sqlite3_backup_pagecount functions or they could be passed as parameters to the callback function as the callback probably has few interest if not to report or log on the progress of the task. -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia, http://integral.software _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
