If the php sqlite bindings are incomplete and don't support the backup functions, write a small program in C that uses them to copy a database, and execute that from the php code?
On Mon, Mar 18, 2019, 8:24 AM Jonathan Moules <jonathan-li...@lightpear.com> wrote: > Hi Simon, > > Thanks for your thoughts. Sorry, I should have been clearer: I have no > way of knowing if there are other open connections to the file - there > may be as it's a web-application. So I'll assume there are connections. > > At this point I'm starting to think that the best option is to create a > new database with the requisite structure and copy the data across via > an ATTACH (there are only two tables and one will almost always be empty > at this point). > > Any other thoughts welcome though! > Cheers, > Jonathan > > On 2019-03-18 13:37, Simon Slavin wrote: > > On 18 Mar 2019, at 1:10pm, Jonathan Moules <jonathan-li...@lightpear.com> > wrote: > > > >> I was wondering if there was a good way of backing up an SQLite > database if you do *not* have access to the SQLite command line tool (which > I know has .backup - https://stackoverflow.com/a/25684912). [snip] > >> I've considered simply running "PRAGMA wal_checkpointer;" and then > copying the file immediately after that, but that still seems prone to > error. > > Ideally, rather than force a WAL checkpoint, close the file, make the > copy, then open it again. This does not take significantly more time, and > it ensures that you will copy the right thing no matter what caching and > optimization your tools are trying to do. > > > > In more general terms ... > > > > Are you trying to backup while the database is being modified using > SQLite function calls ? > > > > If not, then the data is just a single file. Assuming all programs > using SQLite calls closed their connections properly, just copy the file > using any file copy commands, or file copy primatives in your favourite > programming language. In PHP I'd use the built-in copy command: > > > > <https://secure.php.net/manual/en/function.copy.php> > > > > There may be a journal file there and you can copy that too, but just > the database file is enough for a backup for emergency purposes. > > > > If you're trying to copy a file while connections still have it open > then you should use SQLite API calls to do it. The obvious ones are in the > SQLite Online Backup API, which is the set of calls underlying the > '.backup' command you mentioned. You can find documentation for this here: > > > > <https://www.sqlite.org/backup.html> > > > > Unfortunately I don't think the PHP sqlite3 tools give access to this > API. > > > > Hope that helps. Don't hesitate to get back to us if we can help. > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
