[Python-checkins] bpo-27645: Add support for native backup facility of SQLite (GH-4238)
Berker Peksag
webhook-mailer at python.org
Sat Mar 10 17:08:35 EST 2018
https://github.com/python/cpython/commit/d7aed4102d2a40c74553240c7f03585624d27aea
commit: d7aed4102d2a40c74553240c7f03585624d27aea
branch: master
author: Emanuele Gaifas <lelegaifax at gmail.com>
committer: Berker Peksag <berker.peksag at gmail.com>
date: 2018-03-11T01:08:31+03:00
summary:
bpo-27645: Add support for native backup facility of SQLite (GH-4238)
files:
A Lib/sqlite3/test/backup.py
A Misc/NEWS.d/next/Library/2017-10-05-20-41-48.bpo-27645.1Y_Wag.rst
M Doc/library/sqlite3.rst
M Doc/whatsnew/3.7.rst
M Lib/test/test_sqlite.py
M Modules/_sqlite/connection.c
M Modules/_sqlite/module.c
diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
index e7676a9f3a50..d7eaea638f82 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 76e1f7b36b0b..fc5f5ab77095 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 000000000000..784702fb46e4
--- /dev/null
+++ b/Lib/sqlite3/test/backup.py
@@ -0,0 +1,162 @@
+import sqlite3 as sqlite
+import unittest
+
+
+ at 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 adfcd9994575..9564da35193f 100644
--- a/Lib/test/test_sqlite.py
+++ b/Lib/test/test_sqlite.py
@@ -7,7 +7,7 @@
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 000000000000..c4b7185614a5
--- /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 3e83fb662bba..14b6a2774e78 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 @@ pysqlite_connection_iterdump(pysqlite_Connection* self, PyObject* args)
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 879c66692bc9..6befa073dcf0 100644
--- a/Modules/_sqlite/module.c
+++ b/Modules/_sqlite/module.c
@@ -322,6 +322,9 @@ static const IntConstantPair _int_constants[] = {
#endif
#if SQLITE_VERSION_NUMBER >= 3008003
{"SQLITE_RECURSIVE", SQLITE_RECURSIVE},
+#endif
+#if SQLITE_VERSION_NUMBER >= 3006011
+ {"SQLITE_DONE", SQLITE_DONE},
#endif
{(char*)NULL, 0}
};
More information about the Python-checkins
mailing list