[DBD-SQLite] [PATCH] Add online backup support (take 2)

Tim Bunce Tim.Bunce at pobox.com
Wed May 6 09:47:59 GMT 2009


On Wed, May 06, 2009 at 05:01:56PM +0900, Kenichi Ishigaki wrote:
> Hi Toby,
> 
> applied your patch with slight modification (changed function
> names in dbdimp.c/h and, added a test for pre-1.608 DBI, etc).
> As for the modification of your DBI, DBI 1.608 with sqlite_
> prefix is out on the 5th, May (Tim++). So upgrade your DBI
> and everything will be fine.

But not for people who can't upgrade their DBI, for whatever reason.
There should be an "if ($DBI::VERSION >= 1.608)" around the
install_method() calls and a note in the docs.

Either that, or make DBI 1.608 a pre-requisite - but that would be a bit
extreem.

Tim.

> Thanks,
> Kenichi
> 
> On Wed, 06 May 2009 16:09:42 +1000, Toby Corkindale <toby.corkindale at strategicdata.com.au> wrote:
> 
> >Please find attached my revised patch for adding online backup support
> >to DBD::SQLite.
> >
> >This time it includes a unit test, and uses the sqlite_ prefix for
> >functions, and uses the install_method() system from DBI to provide
> >those functions.
> >
> >I note that I needed to patch my local DBI.pm to include:
> >
> >     sqlite_  => { class => 'DBD::SQLite' }
> >
> >So I'm not sure what effect that will have on backward compatibility
> >with current stable versions of DBI?
> >
> >-Toby
> 
> >Patch to add support for SQLite's Online Backup functions.
> >
> >I intend to improve this to use the "stepping" online backup method at some
> >point, which is friendlier for concurrent database users. However the public
> >API should remain the same, and I figure people might like to play with the
> >system for now.
> >
> >Signed off by Toby Corkindale <TJC at cpan.org>
> >
> > Changes              |    1 +
> > SQLite.xs            |   19 ++++++++++++++++
> > dbdimp.c             |   56 +++++++++++++++++++++++++++++++++++++++++++++++++
> > dbdimp.h             |    2 +
> > lib/DBD/SQLite.pm    |   16 ++++++++++++++
> > t/35_online_backup.t |   57 ++++++++++++++++++++++++++++++++++++++++++++++++++
> > 6 files changed, 151 insertions(+), 0 deletions(-)
> >
> >diff --git a/Changes b/Changes
> >index 4998374..2224f90 100644
> >--- a/Changes
> >+++ b/Changes
> >@@ -9,6 +9,7 @@ Changes for Perl extension DBD-SQLite
> >     - Added some explanation and workarounds for a SQL that
> >       compares a return value of a function with a numeric bind
> >       value (ISHIGAKI)
> >+    - Added access to Online Backup functionality. (TJC)
> >
> > 1.25 Thu 23 Apr 2009
> >     - Amalgamation conversion turned out to be quicker than expected.
> >diff --git a/SQLite.xs b/SQLite.xs
> >index fda00aa..8e9d0a6 100644
> >--- a/SQLite.xs
> >+++ b/SQLite.xs
> >@@ -92,6 +92,25 @@ busy_timeout(dbh, timeout=0)
> >   OUTPUT:
> >     RETVAL
> >
> >+int
> >+sqlite_backup_from_file(dbh, filename)
> >+  SV *dbh
> >+  char *filename
> >+  CODE:
> >+    RETVAL = _sqlite_backup_from_file(aTHX_ dbh, filename);
> >+  OUTPUT:
> >+    RETVAL
> >+
> >+int
> >+sqlite_backup_to_file(dbh, filename)
> >+  SV *dbh
> >+  char *filename
> >+  CODE:
> >+    RETVAL = _sqlite_backup_to_file(aTHX_ dbh, filename);
> >+  OUTPUT:
> >+    RETVAL
> >+
> >+
> > MODULE = DBD::SQLite          PACKAGE = DBD::SQLite::st
> >
> > PROTOTYPES: DISABLE
> >diff --git a/dbdimp.c b/dbdimp.c
> >index 89a531e..cce3e30 100644
> >--- a/dbdimp.c
> >+++ b/dbdimp.c
> >@@ -139,6 +139,62 @@ dbd_set_sqlite3_busy_timeout (pTHX_ SV *dbh, int timeout )
> >   return imp_dbh->timeout;
> > }
> >
> >+/* Accesses the SQLite Online Backup API, and fills the currently loaded
> >+ * database from the passed filename.
> >+ * Usual usage of this would be when you're operating on the :memory:
> >+ * special database connection and want to copy it in from a real db.
> >+ */
> >+int
> >+_sqlite_backup_from_file(pTHX_ SV *dbh, char *filename)
> >+{
> >+    int rc;
> >+    sqlite3 *pFrom;
> >+    sqlite3_backup *pBackup;
> >+
> >+    D_imp_dbh(dbh);
> >+
> >+    rc = sqlite3_open(filename, &pFrom);
> >+    if (rc==SQLITE_OK) {
> >+
> >+        pBackup = sqlite3_backup_init(imp_dbh->db, "main", pFrom, "main");
> >+        if (pBackup) {
> >+            (void)sqlite3_backup_step(pBackup, -1);
> >+            (void)sqlite3_backup_finish(pBackup);
> >+        }
> >+        rc = sqlite3_errcode(imp_dbh->db);
> >+        (void)sqlite3_close(pFrom);
> >+    }
> >+    return rc;
> >+}
> >+
> >+/* Accesses the SQLite Online Backup API, and copies the currently loaded
> >+ * database into the passed filename.
> >+ * Usual usage of this would be when you're operating on the :memory:
> >+ * special database connection, and want to back it up to an on-disk file.
> >+ */
> >+int
> >+_sqlite_backup_to_file(pTHX_ SV *dbh, char *filename)
> >+{
> >+    int rc;
> >+    sqlite3 *pTo;
> >+    sqlite3_backup *pBackup;
> >+
> >+    D_imp_dbh(dbh);
> >+
> >+    rc = sqlite3_open(filename, &pTo);
> >+    if (rc==SQLITE_OK) {
> >+
> >+        pBackup = sqlite3_backup_init(pTo, "main", imp_dbh->db, "main");
> >+        if (pBackup) {
> >+            (void)sqlite3_backup_step(pBackup, -1);
> >+            (void)sqlite3_backup_finish(pBackup);
> >+        }
> >+        rc = sqlite3_errcode(pTo);
> >+        (void)sqlite3_close(pTo);
> >+    }
> >+    return rc;
> >+}
> >+
> > int
> > sqlite_db_disconnect (SV *dbh, imp_dbh_t *imp_dbh)
> > {
> >diff --git a/dbdimp.h b/dbdimp.h
> >index 78f7c5e..dea1bcd 100644
> >--- a/dbdimp.h
> >+++ b/dbdimp.h
> >@@ -79,6 +79,8 @@ void sqlite3_db_progress_handler(pTHX_ SV *dbh, int n_opcodes, SV *handler);
> > void sqlite_st_reset(pTHX_ SV *sth );
> > int sqlite_bind_col( SV *sth, imp_sth_t *imp_sth, SV *col, SV *ref, IV sql_type, SV *attribs );
> > int dbd_set_sqlite3_busy_timeout (pTHX_ SV *dbh, int timeout );
> >+int _sqlite_backup_from_file(pTHX_ SV *dbh, char *filename);
> >+int _sqlite_backup_to_file(pTHX_ SV *dbh, char *filename);
> >
> > #ifdef SvUTF8_on
> >
> >diff --git a/lib/DBD/SQLite.pm b/lib/DBD/SQLite.pm
> >index 3e6219a..4adb72d 100644
> >--- a/lib/DBD/SQLite.pm
> >+++ b/lib/DBD/SQLite.pm
> >@@ -98,6 +98,10 @@ sub connect {
> >         $attr->{Warn} = 0;
> >     }
> >
> >+    # Install online backup private methods:
> >+    DBD::SQLite::db->install_method('sqlite_backup_from_file', {});
> >+    DBD::SQLite::db->install_method('sqlite_backup_to_file', {});
> >+
> >     return $dbh;
> > }
> >
> >@@ -723,6 +727,18 @@ progress handler.
> >
> > =back
> >
> >+=head2 $dbh->sqlite_backup_from_file( $filename )
> >+
> >+This method accesses the SQLite Online Backup API, and will take a backup of
> >+the named database file, copying it to, and overwriting, your current database
> >+connection. This can be particularly handy if your current connection is to the
> >+special :memory: database, and you wish to populate it from an existing DB.
> >+
> >+=head2 $dbh->sqlite_backup_to_file( $filename )
> >+
> >+This method accesses the SQLite Online Backup API, and will take a backup of
> >+the currently connected database, and write it out to the named file.
> >+
> > =head1 BLOBS
> >
> > As of version 1.11, blobs should "just work" in SQLite as text columns.
> >diff --git a/t/35_online_backup.t b/t/35_online_backup.t
> >new file mode 100644
> >index 0000000..37dea7b
> >--- /dev/null
> >+++ b/t/35_online_backup.t
> >@@ -0,0 +1,57 @@
> >+#!/usr/bin/perl
> >+use strict;
> >+use warnings;
> >+
> >+use Test::More tests => 4;
> >+use t::lib::Test;
> >+use DBI;
> >+
> >+# Connect to the test db and add some stuff:
> >+my $foo = connect_ok( RaiseError => 1 );
> >+$foo->do(
> >+    'CREATE TABLE online_backup_test( id INTEGER PRIMARY KEY, foo INTEGER )'
> >+);
> >+$foo->do("INSERT INTO online_backup_test (foo) VALUES ($$)");
> >+
> >+# That should be in the "foo" database on disk now, so disconnect and try to
> >+# back it up:
> >+
> >+$foo->disconnect;
> >+
> >+my $dbh = DBI->connect(
> >+    'dbi:SQLite:dbname=:memory:',
> >+    undef, undef,
> >+    { RaiseError => 1 }
> >+);
> >+
> >+$dbh->sqlite_backup_from_file('foo');
> >+
> >+{
> >+    my ($count) = $dbh->selectrow_array(
> >+        "SELECT count(foo) FROM online_backup_test WHERE foo=$$"
> >+    );
> >+    is($count, 1, "Found our process ID in backed-up table");
> >+}
> >+
> >+# Add more data then attempt to copy it back to file:
> >+$dbh->do(
> >+    'CREATE TABLE online_backup_test2 ( id INTEGER PRIMARY KEY, foo INTEGER )'
> >+);
> >+$dbh->do("INSERT INTO online_backup_test2 (foo) VALUES ($$)");
> >+
> >+# backup to file (foo):
> >+$dbh->sqlite_backup_to_file('foo');
> >+
> >+$dbh->disconnect;
> >+
> >+# Reconnect to foo db and check data made it over:
> >+{
> >+    my $foo = connect_ok( RaiseError => 1 );
> >+
> >+    my ($count) = $foo->selectrow_array(
> >+        "SELECT count(foo) FROM online_backup_test2 WHERE foo=$$"
> >+    );
> >+    is($count, 1, "Found our process ID in table back on disk");
> >+
> >+    $foo->disconnect;
> >+}
> 
> >_______________________________________________
> >DBD-SQLite mailing list
> >DBD-SQLite at lists.scsys.co.uk
> >http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
> 
> 
> 
> _______________________________________________
> DBD-SQLite mailing list
> DBD-SQLite at lists.scsys.co.uk
> http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite



More information about the DBD-SQLite mailing list