From 57f5e273d521cb2bf1032d8124ad52082723f1dc Mon Sep 17 00:00:00 2001
From: chriskl <chriskl>
Date: Mon, 1 Aug 2005 03:47:46 +0000
Subject: [PATCH] When viewing paginated queries, use read only transaction
 mode if available.  This avoid problems with SELECT queries that have write
 side-effects.

---
 HISTORY                         |  2 ++
 classes/database/Postgres.php   | 15 ++++++++++++++-
 classes/database/Postgres74.php |  4 ++--
 3 files changed, 18 insertions(+), 3 deletions(-)

diff --git a/HISTORY b/HISTORY
index a93ac83a..6db8889d 100644
--- a/HISTORY
+++ b/HISTORY
@@ -24,6 +24,8 @@ Features
 * Avoid getting and setting encoding queries if possible
 * Avoid version query in PHP 5 / PostgreSQL 7.4+
 * Avoid query for superuser status in PHP 5 / PostgreSQL 7.4+
+* Put PostgreSQL 7.4+ in read only mode for pagination of results
+  to avoid executing selects that have write side effects.
 * Allow re-using username and password for all servers - saves re-entering
   username and password for every server in a pool.
   
diff --git a/classes/database/Postgres.php b/classes/database/Postgres.php
index b4244db9..f54d83d5 100755
--- a/classes/database/Postgres.php
+++ b/classes/database/Postgres.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: Postgres.php,v 1.267 2005/07/31 09:15:06 chriskl Exp $
+ * $Id: Postgres.php,v 1.268 2005/08/01 03:47:46 chriskl Exp $
  */
 
 // @@@ THOUGHT: What about inherits? ie. use of ONLY???
@@ -3919,6 +3919,7 @@ class Postgres extends ADODB_base {
 	 * @return -2 counting error
 	 * @return -3 page or page_size invalid
 	 * @return -4 unknown type
+	 * @return -5 failed setting transaction read only
 	 */
 	function browseQuery($type, $table, $query, $sortkey, $sortdir, $page, $page_size, &$max_pages) {
 		// Check that we're not going to divide by zero
@@ -3950,6 +3951,17 @@ class Postgres extends ADODB_base {
 		$status = $this->beginTransaction();
 		if ($status != 0) return -1;
 		
+		// If backend supports read only queries, then specify read only mode
+		// to avoid side effects from repeating queries that do writes.
+		if ($this->hasReadOnlyQueries()) {
+			$status = $this->execute("SET TRANSACTION READ ONLY");
+			if ($status != 0) {
+				$this->rollbackTransaction();
+				return -5;
+			}
+		}
+
+		
 		// Count the number of rows
 		$total = $this->browseQueryCount($query, $count);
 		if ($total < 0) {
@@ -4474,6 +4486,7 @@ class Postgres extends ADODB_base {
 	function hasNamedParams() { return false; }
 	function hasUserAndDbVariables() { return false; }
 	function hasCompositeTypes() { return false; }
+	function hasReadOnlyQueries() { return false; }
 
 }
 
diff --git a/classes/database/Postgres74.php b/classes/database/Postgres74.php
index 2978937d..148cc3ac 100644
--- a/classes/database/Postgres74.php
+++ b/classes/database/Postgres74.php
@@ -4,7 +4,7 @@
  * A class that implements the DB interface for Postgres
  * Note: This class uses ADODB and returns RecordSets.
  *
- * $Id: Postgres74.php,v 1.47 2005/07/31 09:15:07 chriskl Exp $
+ * $Id: Postgres74.php,v 1.48 2005/08/01 03:47:46 chriskl Exp $
  */
 
 include_once('./classes/database/Postgres73.php');
@@ -550,7 +550,7 @@ class Postgres74 extends Postgres73 {
 	function hasDomainConstraints() { return true; }
 	function hasUserRename() { return true; }
 	function hasRecluster() { return true; }
-	
+	function hasReadOnlyQueries() { return true; }	
 }
 
 ?>
-- 
2.39.5