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 > <[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 _______________________________________________ DBD-SQLite mailing list [email protected] http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
