Commits

gh  committed 255f9af

Closes: #182

Implemented set_progress_handler for progress callbacks from SQLite. This is
particularly useful to update GUIs during long-running queries.

Thanks to exarkun for the original patch.

  • Participants
  • Parent commits db76f39

Comments (0)

Files changed (5)

File doc/code/progress.py

+from pysqlite2 import dbapi2 as sqlite
+
+def progress():
+    print "Query still executing. Please wait ..."
+
+con = sqlite.connect(":memory:")
+con.execute("create table test(x)")
+
+# Let's create some data
+con.executemany("insert into test(x) values (?)", [(x,) for x in xrange(300)])
+
+# A progress handler, executed every 10 million opcodes
+con.set_progress_handler(progress, 10000000)
+
+# A particularly long-running query
+killer_stament = """
+    select count(*) from (
+        select t1.x from test t1, test t2, test t3
+    )
+    """
+
+con.execute(killer_stament)
+print "-" * 50
+
+# Clear the progress handler
+con.set_progress_handler(None, 0)
+
+con.execute(killer_stament)
+

File doc/usage-guide.txt

 | (c) 2004-2005 David Rushby
 | (c) 2005-2007 Gerhard H�ring
 
-Last updated for pysqlite 2.3.5
+Last updated for pysqlite 2.4.0
 
 Table Of Contents
 =================
 |   `3.4 Checking for complete statements`_
 |   `3.5 Enabling SQLite's shared cache`_
 |   `3.6 Setting an authorizer callback`_
+|   `3.7 Setting a progress handler`_
 | `4. SQLite and Python types`_
 |   `4.1 Introduction`_
 |   `4.2 Using adapters to store additional Python types in SQLite databases`_
    :language: Python
    :source-file: code/authorizer.py
 
+3.7 Setting a progress handler
+------------------------------
+
+If you want to get called by SQLite during long-running operations, you can set
+a progress handler. An example use for this is to keep a GUI updated during a
+long-running query. 
+
+  .. code-block:: Python
+
+    def set_progress_handler(self, handler, n)
+
+The progress handler will be called every n SQLite virtual machine opcodes. If
+handler returns a nonzero value, the query is aborted with an OperationalError.
+
+Here's an example that demonstrates the usage of this function:
+
+  .. code-block::
+   :language: Python
+   :source-file: code/progress.py
 
 4. SQLite and Python types
 ==========================

File pysqlite2/test/hooks.py

             if not e.args[0].startswith("no such collation sequence"):
                 self.fail("wrong OperationalError raised")
 
+class ProgressTests(unittest.TestCase):
+    def CheckProgressHandlerUsed(self):
+        """
+        Test that the progress handler is invoked once it is set.
+        """
+        con = sqlite.connect(":memory:")
+        progress_calls = []
+        def progress():
+            progress_calls.append(None)
+            return 0
+        con.set_progress_handler(progress, 1)
+        con.execute("""
+            create table foo(a, b)
+            """)
+        self.failUnless(progress_calls)
+
+
+    def CheckOpcodeCount(self):
+        """
+        Test that the opcode argument is respected.
+        """
+        con = sqlite.connect(":memory:")
+        progress_calls = []
+        def progress():
+            progress_calls.append(None)
+            return 0
+        con.set_progress_handler(progress, 1)
+        curs = con.cursor()
+        curs.execute("""
+            create table foo (a, b)
+            """)
+        first_count = len(progress_calls)
+        progress_calls = []
+        con.set_progress_handler(progress, 2)
+        curs.execute("""
+            create table bar (a, b)
+            """)
+        second_count = len(progress_calls)
+        self.failUnless(first_count > second_count)
+
+    def CheckCancelOperation(self):
+        """
+        Test that returning a non-zero value stops the operation in progress.
+        """
+        con = sqlite.connect(":memory:")
+        progress_calls = []
+        def progress():
+            progress_calls.append(None)
+            return 1
+        con.set_progress_handler(progress, 1)
+        curs = con.cursor()
+        self.assertRaises(
+            sqlite.OperationalError,
+            curs.execute,
+            "create table bar (a, b)")
+
+    def CheckClearHandler(self):
+        """
+        Test that setting the progress handler to None clears the previously set handler.
+        """
+        con = sqlite.connect(":memory:")
+        action = 0
+        def progress():
+            action = 1
+            return 0
+        con.set_progress_handler(progress, 1)
+        con.set_progress_handler(None, 1)
+        con.execute("select 1 union select 2 union select 3").fetchall()
+        self.failUnlessEqual(action, 0, "progress handler was not cleared")
+
 def suite():
     collation_suite = unittest.makeSuite(CollationTests, "Check")
-    return unittest.TestSuite((collation_suite,))
+    progress_suite = unittest.makeSuite(ProgressTests, "Check")
+    return unittest.TestSuite((collation_suite, progress_suite))
 
 def test():
     runner = unittest.TextTestRunner()

File src/connection.c

     return rc;
 }
 
+static int _progress_handler(void* user_arg)
+{
+    int rc;
+    PyObject *ret;
+    PyGILState_STATE gilstate;
+
+    gilstate = PyGILState_Ensure();
+    ret = PyObject_CallFunction((PyObject*)user_arg, "");
+
+    if (!ret) {
+        if (_enable_callback_tracebacks) {
+            PyErr_Print();
+        } else {
+            PyErr_Clear();
+        }
+
+        /* abort query if error occured */
+        rc = 1; 
+    } else {
+        rc = (int)PyObject_IsTrue(ret);
+    }
+
+    Py_DECREF(ret);
+    PyGILState_Release(gilstate);
+    return rc;
+}
+
 PyObject* pysqlite_connection_set_authorizer(pysqlite_Connection* self, PyObject* args, PyObject* kwargs)
 {
     PyObject* authorizer_cb;
     }
 }
 
+PyObject* pysqlite_connection_set_progress_handler(pysqlite_Connection* self, PyObject* args, PyObject* kwargs)
+{
+    PyObject* progress_handler;
+    int n;
+
+    static char *kwlist[] = { "progress_handler", "n", NULL };
+
+    if (!PyArg_ParseTupleAndKeywords(args, kwargs, "Oi:set_progress_handler",
+                                      kwlist, &progress_handler, &n)) {
+        return NULL;
+    }
+
+    if (progress_handler == Py_None) {
+        /* None clears the progress handler previously set */
+        sqlite3_progress_handler(self->db, 0, 0, (void*)0);
+    } else {
+        sqlite3_progress_handler(self->db, n, _progress_handler, progress_handler);
+        PyDict_SetItem(self->function_pinboard, progress_handler, Py_None);
+    }
+
+    Py_INCREF(Py_None);
+    return Py_None;
+}
+
 int pysqlite_check_thread(pysqlite_Connection* self)
 {
     if (self->check_same_thread) {
         PyDoc_STR("Creates a new aggregate. Non-standard.")},
     {"set_authorizer", (PyCFunction)pysqlite_connection_set_authorizer, METH_VARARGS|METH_KEYWORDS,
         PyDoc_STR("Sets authorizer callback. Non-standard.")},
+    {"set_progress_handler", (PyCFunction)pysqlite_connection_set_progress_handler, METH_VARARGS|METH_KEYWORDS,
+        PyDoc_STR("Sets progress handler callback. Non-standard.")},
     {"execute", (PyCFunction)pysqlite_connection_execute, METH_VARARGS,
         PyDoc_STR("Executes a SQL statement. Non-standard.")},
     {"executemany", (PyCFunction)pysqlite_connection_executemany, METH_VARARGS,

File src/module.h

 #define PYSQLITE_MODULE_H
 #include "Python.h"
 
-#define PYSQLITE_VERSION "2.3.5"
+#define PYSQLITE_VERSION "2.4.0"
 
 extern PyObject* pysqlite_Error;
 extern PyObject* pysqlite_Warning;