They both cause a new copy-of-the-source-db file, but...
On 2017/06/15 6:07 PM, Keith Medcalf wrote:
On Thursday, 15 June, 2017 09:40, David Raymond <[email protected]>
wrote:
In the same train of thought: What's the difference between .backup and
.clone in the CLI, and would either of those do the equivalent?
.backup ?DB? FILE Backup DB (default "main") to FILE
.clone NEWDB Clone data into NEWDB from the existing database
.backup makes a backup of the database file using the backup API call,
effectively copying all the pages (without looking at them to much) from main
to the new file.
Indeed - It's also worth noting that the backup is able to be used
within a live database situation. i.e. it doesn't hold an exclusive
lock. It will transfer pages to the backed-up DB file while you can
still use the DB as normal, and, if there are any writes to the source
DB while being backed up, the backup API will realise this and start
over. Once it completes, you have the most recent state of the DB.
The BACKUP is an API functionality available to any process linking
against the SQLite source code.
.clone is (AFAICT) a construct of the CLI and not an API interface, it
simply transfers data via a custom CLI process using SQL API functions
as Keith explained below.
Basically, these are two very different means to a similar end and many
situations can be fathomed where one will be superior to the other and
vice versa.
.clone "copies" the data to a new database file by effectively doing the
following:
open the newfile
begin exclusive on newfile
for each sqlstatement in main.sqlite_master
if type=='table'
execute retrieved sql in newfile
prepare select * from main.table
prepare insert or replace into newfile.table
step select from main.table
while (SQLITE_ROW)
bind values from selected row to prepared insert
step insert statement
step select statement
for each sqlstatement in main.sqlite_master
if type != 'table'
execute retrieved sql in newfile
commit newfile
close newfile
So, backup will be faster. clone may permit data to be recovered from
corrupted databases. Sort of like how dump/load works, only without the
overhead of writing a dump file and reloading it, or generating SQL statements.
In most cases similar to:
sqlite3 main.db .dump | sqlite3 newfile.db
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
