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.
Thanks, Kenichi On Wed, 06 May 2009 16:09:42 +1000, Toby Corkindale <[email protected]> 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 <[email protected]> > > 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 >[email protected] >http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite _______________________________________________ DBD-SQLite mailing list [email protected] http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
