From b76f3afb27702c990275708c2ea0d432f35c6df1 Mon Sep 17 00:00:00 2001
From: Tom Willemse
Date: Tue, 11 Jun 2024 14:45:50 -0700
Subject: Support reentrant calls to ‘sql_db->sql_transaction’ for SQLite

SQLite transactions don't appear to be reentrant. So calling ‘BEGIN’ and then
again ‘BEGIN’ is an error if there is no ‘COMMIT’ or ‘ROLLBACK’ in between.

In order to pretend like this works (it doesn't) I keep track of the level of
the number of transactions that have been created and only call the actual
‘BEGIN’ on the first call and ‘COMMIT’ and ‘ROLLBACK’ on the final one.

This does mean that if any part of the code forgets to do a commit or rollback
for any reason it'll maintain the transaction indefinitely.

I want to change it to explicitly fail if this is attempted, but MySQL/MariaDB
doesn't seem to have any problem with calling ‘BEGIN’ and ‘COMMIT’/‘ROLLBACK’ as
many times as one wants.
---
 src/SemanticScuttle/db/sqlite.php | 38 +++++++++++++++++++++++++++-----------
 1 file changed, 27 insertions(+), 11 deletions(-)

diff --git a/src/SemanticScuttle/db/sqlite.php b/src/SemanticScuttle/db/sqlite.php
index 4531d17..f34be28 100644
--- a/src/SemanticScuttle/db/sqlite.php
+++ b/src/SemanticScuttle/db/sqlite.php
@@ -27,7 +27,6 @@ class sql_db
     var $db_connection;
 	var $query_result;
 	var $return_on_error = false;
-	var $transaction = false;
 	var $sql_report = '';
 	var $sql_time = 0;
 	var $num_queries = 0;
@@ -42,6 +41,8 @@ class sql_db
 
     public $QueryBuilder;
 
+    private int $transaction_level = 0;
+
     public function __construct()
     {
          $this->QueryBuilder = new SQLiteQueryBuilder(); 
@@ -85,19 +86,34 @@ class sql_db
 		switch ($status)
 		{
 			case 'begin':
-				$this->transaction = true;
-				$result = $this->db_connection->exec('BEGIN');
-				break;
+				$this->transaction_level++;
+                if ($this->transaction_level == 1) {
+                    $result = $this->db_connection->exec('BEGIN');
+                    break;
+                }
+                else {
+                    $result = true;
+                }
 
 			case 'commit':
-				$this->transaction = false;
-				$result = $this->db_connection->exec('COMMIT');
-				break;
+				$this->transaction_level--;
+                if ($this->transaction_level == 0) {
+                    $result = $this->db_connection->exec('COMMIT');
+                    break;
+                }
+                else {
+                    $result = true;
+                }
 
 			case 'rollback':
-				$this->transaction = false;
-				$result = $this->db_connection->exec('ROLLBACK');
-				break;
+				$this->transaction_level--;
+                if ($this->transaction_level == 0) {
+                    $result = $this->db_connection->exec('ROLLBACK');
+                    break;
+                }
+                else {
+                    $result = true;
+                }
 
 			default:
 				$result = true;
@@ -401,7 +417,7 @@ class sql_db
 
 			$message = '<u>SQL ERROR</u> [ ' . SQL_LAYER . ' ]<br /><br />' . $this->db_connection->lastErrorMsg() . '<br /><br /><u>CALLING PAGE</u><br /><br />'  . htmlspecialchars($this_page) . (($sql != '') ? '<br /><br /><u>SQL</u><br /><br />' . $sql : '') . '<br />';
 
-			if ($this->transaction)
+			while ($this->transaction_level > 0)
 			{
 				$this->sql_transaction('rollback');
 			}
-- 
cgit v1.2.3-54-g00ecf