[DBD-SQLite] [PATCH] Add online backup support (take 2)
Kenichi Ishigaki
kishigaki at gmail.com
Wed May 6 08:01:56 GMT 2009
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 <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
More information about the DBD-SQLite
mailing list