From d7aed4102d2a40c74553240c7f03585624d27aea Mon Sep 17 00:00:00 2001 From: Emanuele Gaifas Date: Sat, 10 Mar 2018 23:08:31 +0100 Subject: bpo-27645: Add support for native backup facility of SQLite (GH-4238) --- Doc/library/sqlite3.rst | 50 +++++++ Doc/whatsnew/3.7.rst | 9 ++ Lib/sqlite3/test/backup.py | 162 +++++++++++++++++++++ Lib/test/test_sqlite.py | 5 +- .../2017-10-05-20-41-48.bpo-27645.1Y_Wag.rst | 3 + Modules/_sqlite/connection.c | 139 ++++++++++++++++++ Modules/_sqlite/module.c | 3 + 7 files changed, 369 insertions(+), 2 deletions(-) create mode 100644 Lib/sqlite3/test/backup.py create mode 100644 Misc/NEWS.d/next/Library/2017-10-05-20-41-48.bpo-27645.1Y_Wag.rst diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst index e7676a9..d7eaea6 100644 --- a/Doc/library/sqlite3.rst +++ b/Doc/library/sqlite3.rst @@ -532,6 +532,56 @@ Connection Objects f.write('%s\n' % line) + .. method:: backup(target, *, pages=0, progress=None, name="main", sleep=0.250) + + This method makes a backup of a SQLite database even while it's being accessed + by other clients, or concurrently by the same connection. The copy will be + written into the mandatory argument *target*, that must be another + :class:`Connection` instance. + + By default, or when *pages* is either ``0`` or a negative integer, the entire + database is copied in a single step; otherwise the method performs a loop + copying up to *pages* pages at a time. + + If *progress* is specified, it must either be ``None`` or a callable object that + will be executed at each iteration with three integer arguments, respectively + the *status* of the last iteration, the *remaining* number of pages still to be + copied and the *total* number of pages. + + The *name* argument specifies the database name that will be copied: it must be + a string containing either ``"main"``, the default, to indicate the main + database, ``"temp"`` to indicate the temporary database or the name specified + after the ``AS`` keyword in an ``ATTACH DATABASE`` statement for an attached + database. + + The *sleep* argument specifies the number of seconds to sleep by between + successive attempts to backup remaining pages, can be specified either as an + integer or a floating point value. + + Example 1, copy an existing database into another:: + + import sqlite3 + + def progress(status, remaining, total): + print(f'Copied {total-remaining} of {total} pages...') + + con = sqlite3.connect('existing_db.db') + with sqlite3.connect('backup.db') as bck: + con.backup(bck, pages=1, progress=progress) + + Example 2, copy an existing database into a transient copy:: + + import sqlite3 + + source = sqlite3.connect('existing_db.db') + dest = sqlite3.connect(':memory:') + source.backup(dest) + + Availability: SQLite 3.6.11 or higher + + .. versionadded:: 3.7 + + .. _sqlite3-cursor-objects: Cursor Objects diff --git a/Doc/whatsnew/3.7.rst b/Doc/whatsnew/3.7.rst index 76e1f7b..fc5f5ab 100644 --- a/Doc/whatsnew/3.7.rst +++ b/Doc/whatsnew/3.7.rst @@ -630,6 +630,15 @@ can be set within the scope of a group. ``'^$'`` or ``(?=-)`` that matches an empty string. (Contributed by Serhiy Storchaka in :issue:`25054`.) + +sqlite3 +------- + +:class:`sqlite3.Connection` now exposes a :class:`~sqlite3.Connection.backup` +method, if the underlying SQLite library is at version 3.6.11 or higher. +(Contributed by Lele Gaifax in :issue:`27645`.) + + ssl --- diff --git a/Lib/sqlite3/test/backup.py b/Lib/sqlite3/test/backup.py new file mode 100644 index 0000000..784702f --- /dev/null +++ b/Lib/sqlite3/test/backup.py @@ -0,0 +1,162 @@ +import sqlite3 as sqlite +import unittest + + +@unittest.skipIf(sqlite.sqlite_version_info < (3, 6, 11), "Backup API not supported") +class BackupTests(unittest.TestCase): + def setUp(self): + cx = self.cx = sqlite.connect(":memory:") + cx.execute('CREATE TABLE foo (key INTEGER)') + cx.executemany('INSERT INTO foo (key) VALUES (?)', [(3,), (4,)]) + cx.commit() + + def tearDown(self): + self.cx.close() + + def verify_backup(self, bckcx): + result = bckcx.execute("SELECT key FROM foo ORDER BY key").fetchall() + self.assertEqual(result[0][0], 3) + self.assertEqual(result[1][0], 4) + + def test_bad_target_none(self): + with self.assertRaises(TypeError): + self.cx.backup(None) + + def test_bad_target_filename(self): + with self.assertRaises(TypeError): + self.cx.backup('some_file_name.db') + + def test_bad_target_same_connection(self): + with self.assertRaises(ValueError): + self.cx.backup(self.cx) + + def test_bad_target_closed_connection(self): + bck = sqlite.connect(':memory:') + bck.close() + with self.assertRaises(sqlite.ProgrammingError): + self.cx.backup(bck) + + def test_bad_target_in_transaction(self): + bck = sqlite.connect(':memory:') + bck.execute('CREATE TABLE bar (key INTEGER)') + bck.executemany('INSERT INTO bar (key) VALUES (?)', [(3,), (4,)]) + with self.assertRaises(sqlite.OperationalError) as cm: + self.cx.backup(bck) + if sqlite.sqlite_version_info < (3, 8, 7): + self.assertEqual(str(cm.exception), 'target is in transaction') + + def test_keyword_only_args(self): + with self.assertRaises(TypeError): + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, 1) + + def test_simple(self): + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck) + self.verify_backup(bck) + + def test_progress(self): + journal = [] + + def progress(status, remaining, total): + journal.append(status) + + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, pages=1, progress=progress) + self.verify_backup(bck) + + self.assertEqual(len(journal), 2) + self.assertEqual(journal[0], sqlite.SQLITE_OK) + self.assertEqual(journal[1], sqlite.SQLITE_DONE) + + def test_progress_all_pages_at_once_1(self): + journal = [] + + def progress(status, remaining, total): + journal.append(remaining) + + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, progress=progress) + self.verify_backup(bck) + + self.assertEqual(len(journal), 1) + self.assertEqual(journal[0], 0) + + def test_progress_all_pages_at_once_2(self): + journal = [] + + def progress(status, remaining, total): + journal.append(remaining) + + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, pages=-1, progress=progress) + self.verify_backup(bck) + + self.assertEqual(len(journal), 1) + self.assertEqual(journal[0], 0) + + def test_non_callable_progress(self): + with self.assertRaises(TypeError) as cm: + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, pages=1, progress='bar') + self.assertEqual(str(cm.exception), 'progress argument must be a callable') + + def test_modifying_progress(self): + journal = [] + + def progress(status, remaining, total): + if not journal: + self.cx.execute('INSERT INTO foo (key) VALUES (?)', (remaining+1000,)) + self.cx.commit() + journal.append(remaining) + + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, pages=1, progress=progress) + self.verify_backup(bck) + + result = bck.execute("SELECT key FROM foo" + " WHERE key >= 1000" + " ORDER BY key").fetchall() + self.assertEqual(result[0][0], 1001) + + self.assertEqual(len(journal), 3) + self.assertEqual(journal[0], 1) + self.assertEqual(journal[1], 1) + self.assertEqual(journal[2], 0) + + def test_failing_progress(self): + def progress(status, remaining, total): + raise SystemError('nearly out of space') + + with self.assertRaises(SystemError) as err: + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, progress=progress) + self.assertEqual(str(err.exception), 'nearly out of space') + + def test_database_source_name(self): + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, name='main') + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, name='temp') + with self.assertRaises(sqlite.OperationalError) as cm: + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, name='non-existing') + self.assertIn( + str(cm.exception), + ['SQL logic error', 'SQL logic error or missing database'] + ) + + self.cx.execute("ATTACH DATABASE ':memory:' AS attached_db") + self.cx.execute('CREATE TABLE attached_db.foo (key INTEGER)') + self.cx.executemany('INSERT INTO attached_db.foo (key) VALUES (?)', [(3,), (4,)]) + self.cx.commit() + with sqlite.connect(':memory:') as bck: + self.cx.backup(bck, name='attached_db') + self.verify_backup(bck) + + +def suite(): + return unittest.makeSuite(BackupTests) + +if __name__ == "__main__": + unittest.main() diff --git a/Lib/test/test_sqlite.py b/Lib/test/test_sqlite.py index adfcd99..9564da3 100644 --- a/Lib/test/test_sqlite.py +++ b/Lib/test/test_sqlite.py @@ -7,7 +7,7 @@ import unittest import sqlite3 from sqlite3.test import (dbapi, types, userfunctions, factory, transactions, hooks, regression, - dump) + dump, backup) def load_tests(*args): if test.support.verbose: @@ -18,7 +18,8 @@ def load_tests(*args): userfunctions.suite(), factory.suite(), transactions.suite(), hooks.suite(), regression.suite(), - dump.suite()]) + dump.suite(), + backup.suite()]) if __name__ == "__main__": unittest.main() diff --git a/Misc/NEWS.d/next/Library/2017-10-05-20-41-48.bpo-27645.1Y_Wag.rst b/Misc/NEWS.d/next/Library/2017-10-05-20-41-48.bpo-27645.1Y_Wag.rst new file mode 100644 index 0000000..c4b7185 --- /dev/null +++ b/Misc/NEWS.d/next/Library/2017-10-05-20-41-48.bpo-27645.1Y_Wag.rst @@ -0,0 +1,3 @@ +:class:`sqlite3.Connection` now exposes a :class:`~sqlite3.Connection.backup` +method, if the underlying SQLite library is at version 3.6.11 +or higher. Patch by Lele Gaifax. diff --git a/Modules/_sqlite/connection.c b/Modules/_sqlite/connection.c index 3e83fb6..14b6a27 100644 --- a/Modules/_sqlite/connection.c +++ b/Modules/_sqlite/connection.c @@ -41,6 +41,10 @@ #endif #endif +#if SQLITE_VERSION_NUMBER >= 3006011 +#define HAVE_BACKUP_API +#endif + _Py_IDENTIFIER(cursor); static const char * const begin_statements[] = { @@ -1447,6 +1451,137 @@ finally: return retval; } +#ifdef HAVE_BACKUP_API +static PyObject * +pysqlite_connection_backup(pysqlite_Connection *self, PyObject *args, PyObject *kwds) +{ + PyObject *target = NULL; + int pages = -1; + PyObject *progress = Py_None; + const char *name = "main"; + int rc; + int callback_error = 0; + double sleep_secs = 0.250; + sqlite3 *bck_conn; + sqlite3_backup *bck_handle; + static char *keywords[] = {"target", "pages", "progress", "name", "sleep", NULL}; + + if (!PyArg_ParseTupleAndKeywords(args, kwds, "O!|$iOsd:backup", keywords, + &pysqlite_ConnectionType, &target, + &pages, &progress, &name, &sleep_secs)) { + return NULL; + } + + if (!pysqlite_check_connection((pysqlite_Connection *)target)) { + return NULL; + } + + if ((pysqlite_Connection *)target == self) { + PyErr_SetString(PyExc_ValueError, "target cannot be the same connection instance"); + return NULL; + } + +#if SQLITE_VERSION_NUMBER < 3008007 + /* Since 3.8.7 this is already done, per commit + https://www.sqlite.org/src/info/169b5505498c0a7e */ + if (!sqlite3_get_autocommit(((pysqlite_Connection *)target)->db)) { + PyErr_SetString(pysqlite_OperationalError, "target is in transaction"); + return NULL; + } +#endif + + if (progress != Py_None && !PyCallable_Check(progress)) { + PyErr_SetString(PyExc_TypeError, "progress argument must be a callable"); + return NULL; + } + + if (pages == 0) { + pages = -1; + } + + bck_conn = ((pysqlite_Connection *)target)->db; + + Py_BEGIN_ALLOW_THREADS + bck_handle = sqlite3_backup_init(bck_conn, "main", self->db, name); + Py_END_ALLOW_THREADS + + if (bck_handle) { + do { + Py_BEGIN_ALLOW_THREADS + rc = sqlite3_backup_step(bck_handle, pages); + Py_END_ALLOW_THREADS + + if (progress != Py_None) { + PyObject *res; + + res = PyObject_CallFunction(progress, "iii", rc, + sqlite3_backup_remaining(bck_handle), + sqlite3_backup_pagecount(bck_handle)); + if (res == NULL) { + /* User's callback raised an error: interrupt the loop and + propagate it. */ + callback_error = 1; + rc = -1; + } else { + Py_DECREF(res); + } + } + + /* Sleep for a while if there are still further pages to copy and + the engine could not make any progress */ + if (rc == SQLITE_BUSY || rc == SQLITE_LOCKED) { + Py_BEGIN_ALLOW_THREADS + sqlite3_sleep(sleep_secs * 1000.0); + Py_END_ALLOW_THREADS + } + } while (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED); + + Py_BEGIN_ALLOW_THREADS + rc = sqlite3_backup_finish(bck_handle); + Py_END_ALLOW_THREADS + } else { + rc = _pysqlite_seterror(bck_conn, NULL); + } + + if (!callback_error && rc != SQLITE_OK) { + /* We cannot use _pysqlite_seterror() here because the backup APIs do + not set the error status on the connection object, but rather on + the backup handle. */ + if (rc == SQLITE_NOMEM) { + (void)PyErr_NoMemory(); + } else { +#if SQLITE_VERSION_NUMBER > 3007015 + PyErr_SetString(pysqlite_OperationalError, sqlite3_errstr(rc)); +#else + switch (rc) { + case SQLITE_READONLY: + PyErr_SetString(pysqlite_OperationalError, + "attempt to write a readonly database"); + break; + case SQLITE_BUSY: + PyErr_SetString(pysqlite_OperationalError, "database is locked"); + break; + case SQLITE_LOCKED: + PyErr_SetString(pysqlite_OperationalError, + "database table is locked"); + break; + default: + PyErr_Format(pysqlite_OperationalError, + "unrecognized error code: %d", rc); + break; + } +#endif + } + } + + if (!callback_error && rc == SQLITE_OK) { + Py_RETURN_NONE; + } else { + return NULL; + } +} +#endif + static PyObject * pysqlite_connection_create_collation(pysqlite_Connection* self, PyObject* args) { @@ -1619,6 +1754,10 @@ static PyMethodDef connection_methods[] = { PyDoc_STR("Abort any pending database operation. Non-standard.")}, {"iterdump", (PyCFunction)pysqlite_connection_iterdump, METH_NOARGS, PyDoc_STR("Returns iterator to the dump of the database in an SQL text format. Non-standard.")}, + #ifdef HAVE_BACKUP_API + {"backup", (PyCFunction)pysqlite_connection_backup, METH_VARARGS | METH_KEYWORDS, + PyDoc_STR("Makes a backup of the database. Non-standard.")}, + #endif {"__enter__", (PyCFunction)pysqlite_connection_enter, METH_NOARGS, PyDoc_STR("For context manager. Non-standard.")}, {"__exit__", (PyCFunction)pysqlite_connection_exit, METH_VARARGS, diff --git a/Modules/_sqlite/module.c b/Modules/_sqlite/module.c index 879c666..6befa07 100644 --- a/Modules/_sqlite/module.c +++ b/Modules/_sqlite/module.c @@ -323,6 +323,9 @@ static const IntConstantPair _int_constants[] = { #if SQLITE_VERSION_NUMBER >= 3008003 {"SQLITE_RECURSIVE", SQLITE_RECURSIVE}, #endif +#if SQLITE_VERSION_NUMBER >= 3006011 + {"SQLITE_DONE", SQLITE_DONE}, +#endif {(char*)NULL, 0} }; -- cgit v0.12