[DBD-SQLite] [PATCH] Add online backup support

[Tim Bunce]

Great, but please don't use the old func() interface.  It's *much*
better for the driver to call install_method() when it loads.
Then applications can invoke the methods directly, without having
to use func(). As a bonus, RaiseError etc work with install_method()
but not with func().

Either way, driver-private methods should begin with the driver's
prefix, 'sqlite_' in this case.

Tim.


On Fri, May 01, 2009 at 04:24:06PM +1000, Toby Corkindale wrote:
> The attached patch adds support for SQLite's Online Backup API via 
> DBI->func() calls.
>
> In this patch I chose to implement all the work in a single DBI func call, 
> rather than exposing all the backup_*() functions individually.
>
> I have an intention to improve the system to use the _step() method, and to 
> have more resilience for database-locked conditions, but that won't change 
> the API calls I defined.
>
> I suck at naming functions, so I won't be hurt if you want to call them 
> something better :)
>
> Cheers,
> 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>
> 
>  SQLite.xs         |   19 ++++++++++++++++++
>  dbdimp.c          |   56 +++++++++++++++++++++++++++++++++++++++++++++++++++++
>  dbdimp.h          |    2 +
>  lib/DBD/SQLite.pm |   12 +++++++++++
>  4 files changed, 89 insertions(+), 0 deletions(-)
> 
> diff --git a/SQLite.xs b/SQLite.xs
> index fda00aa..ea2e87d 100644
> --- a/SQLite.xs
> +++ b/SQLite.xs
> @@ -92,6 +92,25 @@ busy_timeout(dbh, timeout=0)
>    OUTPUT:
>      RETVAL
>  
> +int
> +backup_from_file(dbh, filename)
> +  SV *dbh
> +  char *filename
> +  CODE:
> +    RETVAL = dbd_backup_from_file(aTHX_ dbh, filename);
> +  OUTPUT:
> +    RETVAL
> +
> +int
> +backup_to_file(dbh, filename)
> +  SV *dbh
> +  char *filename
> +  CODE:
> +    RETVAL = dbd_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 0281e6e..ad03a10 100644
> --- a/dbdimp.c
> +++ b/dbdimp.c
> @@ -151,6 +151,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
> +dbd_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
> +dbd_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..d8b8634 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 dbd_backup_from_file(pTHX_ SV *dbh, char *filename);
> +int dbd_backup_to_file(pTHX_ SV *dbh, char *filename);
>  
>  #ifdef SvUTF8_on
>  
> diff --git a/lib/DBD/SQLite.pm b/lib/DBD/SQLite.pm
> index c03a699..0ff61e6 100644
> --- a/lib/DBD/SQLite.pm
> +++ b/lib/DBD/SQLite.pm
> @@ -534,6 +534,18 @@ Set the current busy timeout. The timeout is in milliseconds.
>  This method will register a new function which will be useable in an SQL
>  query. The method's parameters are:
>  
> +=head2 $dbh->func( $filename, 'backup_from_file' )
> +
> +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->func( $filename, 'backup_to_file' )
> +
> +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.
> +
>  =over
>  
>  =item $name

> _______________________________________________
> DBD-SQLite mailing list
> DBD-SQLite at lists.scsys.co.uk
> http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite