Re: [sqlite] Backing up a SQLite database without the CLI

[Bohwaz/Fossil]

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:



Unfortunately I don't think the PHP sqlite3 tools give access to this 
API.


Not yet, but I did a patch and there is a pending pull request for that: 
https://github.com/php/php-src/pull/3617


It does work, I'm using it, and you could too if you are willing to 
compile your own PHP extension.


Hopefully it will land in PHP 7.4 :)

Cheers.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backing up a SQLite database without the CLI

[Scott Perry]

`ATTACH` and iterating over all tables with `INSERT INTO SELECT` is how `VACUUM 
INTO` is implemented (see src/vacuum.c).

A less complicated way to back up the database might be to run `BEGIN` followed 
by `PRAGMA user_version` to acquire a read lock, after which you can safely 
copy the database (and wal, if appropriate) files directly.

Scott

On Mar 18, 2019, at 08:21, Jonathan Moules  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  
>> 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:
>> 
>> 
>> 
>> 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:
>> 
>> 
>> 
>> 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

Re: [sqlite] Backing up a SQLite database without the CLI

[Simon Slavin]

On 18 Mar 2019, at 3:21pm, Jonathan Moules  wrote:

> 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).

That could work well.  Create the new database and set

PRAGMA journal_mode = OFF

then close it, attach it to your main database and use the 

INSERT INTO table SELECT ...

syntax.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backing up a SQLite database without the CLI

[Shawn Wagner]

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 
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 
> 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:
> >
> > 
> >
> > 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:
> >
> > 
> >
> > 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

Re: [sqlite] Backing up a SQLite database without the CLI

[Jonathan Moules]

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  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:



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:



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

Re: [sqlite] Backing up a SQLite database without the CLI

[Simon Slavin]

On 18 Mar 2019, at 1:10pm, Jonathan Moules  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:



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:



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] Backing up a SQLite database without the CLI

[Jonathan Moules]

Hi List,
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).


The new VACUUM INTO (https://www.sqlite.org/lang_vacuum.html#vacuuminto) 
is not an option either because I'm using this via PHP on a remote host 
I have no control over, and its version of SQLite will definitely be 
older than 3.27.0.


I've considered simply running "PRAGMA wal_checkpointer;" and then 
copying the file immediately after that, but that still seems prone to 
error.


Any suggestions?

Thanks,
Jonathan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users