QPSQL: Add support for forward-only queries

With this change, it is possible to significantly reduce memory
consumption of applications that fetch large result sets from databases.
The implementation is based on the new functionality called "single-row
mode" that was introduced in PostgreSQL version 9.2:

https://www.postgresql.org/docs/9.2/static/libpq-async.html

It also uses asynchronous commands PQsendQuery(), PQgetResult():

https://www.postgresql.org/docs/9.2/static/libpq-single-row-mode.html

[ChangeLog][QtSql][QPSQL] Added support for forward-only queries (requires
libpq version 9.2 or later)

[Important Behavior Changes] The QPSQL driver now supports forward-only
queries. To use this feature, you must build QPSQL plugin with PostreSQL
client library version 9.2 or later. See the Qt SQL documentation for
more information about QPSQL limitations of forward-only queries
(sql-driver.html).

[Important Behavior Changes] If you build the QPSQL plugin with PostgreSQL
version 9.2 or later, then you must distribute your application with
libpq version 9.2 or later. Otherwise, the QPSQL plugin will fail to load.

Task-number: QTBUG-63714
Change-Id: I15db8c8fd664f2a1f719329f5d113511fa69010c
Reviewed-by: Andy Shaw <andy.shaw@qt.io>
Reviewed-by: Edward Welbourne <edward.welbourne@qt.io>

3 files changed, 83 insertions, 0 deletions

diff --git a/src/sql/doc/snippets/code/doc_src_sql-driver.cpp b/src/sql/doc/snippets/code/doc_src_sql-driver.cpp
index 7983386642..e7dfbd90d5 100644
--- a/src/sql/doc/snippets/code/doc_src_sql-driver.cpp
+++ b/src/sql/doc/snippets/code/doc_src_sql-driver.cpp
@@ -95,3 +95,35 @@ QSqlDatabase: available drivers: QMYSQL
 //! [34]
 column.contains(QRegularExpression("pattern"));
 //! [34]
+
+
+//! [36]
+QSqlQuery query(db);
+query.setForwardOnly(true);
+query.exec("SELECT * FROM table");
+while (query.next()) {
+    // Handle changes in every iteration of the loop
+    QVariant v = query.result()->handle();
+    if (qstrcmp(v.typeName(), "PGresult*") == 0) {
+        PGresult *handle = *static_cast<PGresult **>(v.data());
+        if (handle != 0) {
+            // Do something...
+        }
+    }
+}
+//! [36]
+
+
+//! [37]
+int value;
+QSqlQuery query1(db);
+query1.setForwardOnly(true);
+query1.exec("select * FROM table1");
+while (query1.next()) {
+    value = query1.value(0).toInt();
+    if (value == 1) {
+        QSqlQuery query2(db);
+        query2.exec("update table2 set col=2");  // WRONG: This will discard all results of
+    }                                            // query1, and cause the loop to quit
+}
+//! [37]
diff --git a/src/sql/doc/snippets/code/doc_src_sql-driver.qdoc b/src/sql/doc/snippets/code/doc_src_sql-driver.qdoc
index 04ea30915d..d127bdf8a5 100644
--- a/src/sql/doc/snippets/code/doc_src_sql-driver.qdoc
+++ b/src/sql/doc/snippets/code/doc_src_sql-driver.qdoc
@@ -225,3 +225,15 @@ cd $QTDIR/qtbase/src/plugins/sqldrivers
 qmake -- OCI_INCDIR=/usr/include/oracle/10.1.0.3/client OCI_LIBDIR=/usr/lib/oracle/10.1.0.3/client/lib "OCI_LIBS=-Wl,-rpath,/usr/lib/oracle/10.1.0.3/client/lib -lclntsh -lnnz10"
 make sub-oci
 //! [33]
+
+
+//! [35]
+QSqlDatabase: QPSQL driver not loaded
+QSqlDatabase: available drivers: QSQLITE QMYSQL QMYSQL3 QODBC QODBC3 QPSQL QPSQL7
+Could not create database object
+//! [35]
+
+
+//! [38]
+QPSQLDriver::getResult: Query results lost - probably discarded on executing another SQL query.
+//! [38]
diff --git a/src/sql/doc/src/sql-driver.qdoc b/src/sql/doc/src/sql-driver.qdoc
index 025cd43ef7..70205d5552 100644
--- a/src/sql/doc/src/sql-driver.qdoc
+++ b/src/sql/doc/src/sql-driver.qdoc
@@ -380,6 +380,45 @@
     Binary Large Objects are supported through the \c BYTEA field type in
     PostgreSQL server versions >= 7.1.
 
+    \section3 QPSQL Forward-only query support
+
+    To use forward-only queries, you must build the QPSQL plugin with
+    PostreSQL client library version 9.2 or later. If the plugin is
+    built with an older version, then forward-only mode will not be
+    available - calling QSqlQuery::setForwardOnly() with \c true will
+    have no effect.
+
+    \warning If you build the QPSQL plugin with PostgreSQL version 9.2 or later,
+    then you must distribute your application with libpq version 9.2 or later.
+    Otherwise, loading the QPSQL plugin will fail with the following message:
+
+    \snippet code/doc_src_sql-driver.qdoc 35
+
+    While navigating the results in forward-only mode, the handle of
+    QSqlResult may change. Applications that use the low-level handle of
+    SQL result must get a new handle after each call to any of QSqlResult
+    fetch functions. Example:
+
+    \snippet code/doc_src_sql-driver.cpp 36
+
+    While reading the results of a forward-only query with PostgreSQL,
+    the database connection cannot be used to execute other queries.
+    This is a limitation of libpq library. Example:
+
+    \snippet code/doc_src_sql-driver.cpp 37
+
+    This problem will not occur if query1 and query2 use different
+    database connections, or if we execute query2 after the while loop.
+
+    \note Some methods of QSqlDatabase like tables(), primaryIndex()
+    implicity execute SQL queries, so these also cannot be used while
+    navigating the results of forward-only query.
+
+    \note QPSQL will print the following warning if it detects a loss of
+    query results:
+
+    \snippet code/doc_src_sql-driver.qdoc 38
+
     \section3 How to Build the QPSQL Plugin on Unix and \macos
 
     You need the PostgreSQL client library and headers installed.