FAQ
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 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@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@lists.scsys.co.uk
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 5 | next ›
Discussion Overview
groupdbd-sqlite @
postedMay 6, '09 at 6:09a
activeMay 7, '09 at 10:17a
posts5
users3
websiteshadowcat.co.uk

People

Translate

site design / logo © 2021 Grokbase