Merge branch 'sqlite-fixes' into oni

21 files changed, 327 insertions, 103 deletions

diff --git a/src/SemanticScuttle/Model/User.php b/src/SemanticScuttle/Model/User.php
index 3fbbaa0..96dddbf 100644
--- a/src/SemanticScuttle/Model/User.php
+++ b/src/SemanticScuttle/Model/User.php
@@ -83,7 +83,9 @@ class SemanticScuttle_Model_User
         if (!isset($this->privateKey)) {
             $us = SemanticScuttle_Service_Factory::get('User');
             $user = $us->getUser($this->id);
-            $this->privateKey = $user['privateKey'];
+            if ($user) {
+                $this->privateKey = $user['privateKey'];
+            }
         }
         //2023-12-20 add line to avoid 'Deprecated' warning
         if (is_null($this->privateKey)) return null;
@@ -106,7 +108,12 @@ class SemanticScuttle_Model_User
         if (!isset($this->name)) {
             $us = SemanticScuttle_Service_Factory::get('User');
             $user = $us->getUser($this->id);
-            $this->name = $user['name'];
+            if ($user) {
+                $this->name = $user['name'];
+            }
+            else {
+                $this->name = 'unknown';
+            }
         }
         return $this->name;
     }
@@ -122,7 +129,9 @@ class SemanticScuttle_Model_User
         if (!isset($this->email)) {
             $us = SemanticScuttle_Service_Factory::get('User');
             $user = $us->getUser($this->id);
-            $this->email = $user['email'];
+            if ($user) {
+                $this->email = $user['email'];
+            }
         }
         return $this->email;
     }
@@ -138,7 +147,9 @@ class SemanticScuttle_Model_User
         if(!isset($this->homepage)) {
             $us = SemanticScuttle_Service_Factory::get('User');
             $user = $us->getUser($this->id);
-            $this->homepage = $user['homepage'];
+            if ($user) {
+                $this->homepage = $user['homepage'];
+            }
         }
         return $this->homepage;
     }
@@ -154,7 +165,9 @@ class SemanticScuttle_Model_User
         if(!isset($this->content)) {
             $us = SemanticScuttle_Service_Factory::get('User');
             $user = $us->getUser($this->id);
-            $this->content = $user['uContent'];
+            if ($user) {
+                $this->content = $user['uContent'];
+            }
         }
         return $this->content;
     }
@@ -171,7 +184,9 @@ class SemanticScuttle_Model_User
         if(!isset($this->content)) {
             $us = SemanticScuttle_Service_Factory::get('User');
             $user = $us->getUser($this->id);
-            $this->datetime = $user['uDatetime'];
+            if ($user) {
+                $this->datetime = $user['uDatetime'];
+            }
         }
         return $this->datetime;
     }
diff --git a/src/SemanticScuttle/Service/AuthUser.php b/src/SemanticScuttle/Service/AuthUser.php
index 9447ee4..f0bc908 100644
--- a/src/SemanticScuttle/Service/AuthUser.php
+++ b/src/SemanticScuttle/Service/AuthUser.php
@@ -168,10 +168,12 @@ class SemanticScuttle_Service_AuthUser extends SemanticScuttle_Service_User
         //user must have changed password in external auth.
         //we need to update the local database.
         $user = $this->getUserByUsername($username);
-        $this->_updateuser(
-            $user['uId'], $this->getFieldName('password'),
-            $this->sanitisePassword($password)
-        );
+        if ($user) {
+            $this->_updateuser(
+                $user['uId'], $this->getFieldName('password'),
+                $this->sanitisePassword($password)
+            );
+        }
 
         return parent::login($username, $password, $remember);
     }
@@ -229,4 +231,4 @@ class SemanticScuttle_Service_AuthUser extends SemanticScuttle_Service_User
     }
 
 }
-?>
\ No newline at end of file
+?>
diff --git a/src/SemanticScuttle/Service/Bookmark.php b/src/SemanticScuttle/Service/Bookmark.php
index 17f91b1..7eb1174 100644
--- a/src/SemanticScuttle/Service/Bookmark.php
+++ b/src/SemanticScuttle/Service/Bookmark.php
@@ -138,7 +138,7 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService
         if ($GLOBALS['enableVoting'] && $userservice->isLoggedOn()) {
             $cuid = $userservice->getCurrentUserId();
             $vs = SemanticScuttle_Service_Factory::get('Vote');
-            $query_1 .= ', !ISNULL(V.bId) as hasVoted, V.vote as vote';
+            $query_1 .= ', ' . $this->db->QueryBuilder->isNotNull('V.bId') . ' as hasVoted, V.vote as vote';
             $query_2 .= ' LEFT JOIN ' . $vs->getTableName() . ' AS V'
                 . ' ON B.bId = V.bId'
                 . ' AND V.uId = ' . (int)$cuid;
@@ -549,7 +549,15 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService
                 '', __LINE__, __FILE__, $sql, $this->db
             );
         }
-        $this->db->sql_transaction('commit');
+
+        if (!$this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(
+                GENERAL_ERROR,
+                'Could not commit bookmark',
+                '', __LINE__, __FILE__, $sql, $this->db
+            );
+        }
 
         // Everything worked out, so return the new bookmark's bId.
         return $bId;
@@ -653,7 +661,13 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService
             );
         }
 
-        $this->db->sql_transaction('commit');
+        if (!$this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(
+                GENERAL_ERROR, 'Could not commit bookmark update',
+                '', __LINE__, __FILE__, $sql, $this->db
+            );
+        }
         // Everything worked out, so return true.
         return true;
     }
@@ -789,7 +803,7 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService
         if ($GLOBALS['enableVoting'] && $userservice->isLoggedOn()) {
             $cuid = $userservice->getCurrentUserId();
             $vs   = SemanticScuttle_Service_Factory::get('Vote');
-            $query_1 .= ', !ISNULL(V.bId) as hasVoted, V.vote as vote';
+            $query_1 .= ', ' . $this->db->QueryBuilder->isNotNull('V.bId') . ' as hasVoted, V.vote as vote';
             $query_2 .= ' LEFT JOIN ' . $vs->getTableName() . ' AS V'
                 . ' ON B.bId = V.bId'
                 . ' AND V.uId = ' . (int)$cuid;
@@ -1003,7 +1017,13 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService
             );
         }
 
-        $this->db->sql_transaction('commit');
+        if (!$this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(
+                GENERAL_ERROR, 'Could not commit deleting votes for bookmark',
+                '', __LINE__, __FILE__, $query, $this->db
+            );
+        }
 
         return true;
     }
diff --git a/src/SemanticScuttle/Service/Bookmark2Tag.php b/src/SemanticScuttle/Service/Bookmark2Tag.php
index 910da48..232fc80 100644
--- a/src/SemanticScuttle/Service/Bookmark2Tag.php
+++ b/src/SemanticScuttle/Service/Bookmark2Tag.php
@@ -208,7 +208,16 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService
                 return false;
             }
         }
-        $this->db->sql_transaction('commit');
+
+        if ($this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(
+                GENERAL_ERROR, 'Could not commit attaching tags',
+                '', __LINE__, __FILE__, $sql, $this->db
+            );
+            return false;
+        }
+
         return true;
     }
 
@@ -287,7 +296,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService
         $query = 'SELECT tag FROM ' . $this->getTableName()
             . ' WHERE bId = ' . intval($bookmarkid);
         if (!$systemTags) {
-            $query .= ' AND LEFT(tag, 7) <> "system:"';
+            $query .= ' AND ' . $this->db->QueryBuilder->left('tag', 7) . ' <> "system:"';
         }
         $query .= ' ORDER BY id ASC';
 
@@ -329,7 +338,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService
 
         $query = 'SELECT tag, bId FROM ' . $this->getTableName()
             . ' WHERE bId IN (' . implode(',', $bookmarkids) . ')'
-            . ' AND LEFT(tag, 7) <> "system:"'
+            . ' AND ' . $this->db->QueryBuilder->left('tag', 7) . ' <> "system:"'
             . ' ORDER BY id, bId ASC';
 
         if (!($dbresult = $this->db->sql_query($query))) {
@@ -367,7 +376,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService
             $conditions['B.bStatus'] = 0;
         }
 
-        $query .= ' WHERE '. $this->db->sql_build_array('SELECT', $conditions) .' AND LEFT(T.tag, 7) <> "system:" GROUP BY T.tag ORDER BY bCount DESC, tag';
+        $query .= ' WHERE '. $this->db->sql_build_array('SELECT', $conditions) .' AND ' . $this->db->QueryBuilder->left('T.tag', 7) . ' <> "system:" GROUP BY T.tag ORDER BY bCount DESC, tag';
 
         if (!($dbresult = $this->db->sql_query($query))) {
             message_die(GENERAL_ERROR, 'Could not get tags', '', __LINE__, __FILE__, $query, $this->db);
@@ -414,7 +423,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService
             $query_2 .= ', '. $this->getTableName() .' AS T'. $i;
             $query_4 .= ' AND T'. $i .'.bId = B.bId AND T'. $i .'.tag = "'. $this->db->sql_escape($tags[$i - 1]) .'" AND T0.tag <> "'. $this->db->sql_escape($tags[$i - 1]) .'"';
         }
-        $query_5 = ' AND LEFT(T0.tag, 7) <> "system:" GROUP BY T0.tag ORDER BY bCount DESC, T0.tag';
+        $query_5 = ' AND ' . $this->db->QueryBuilder->left('T0.tag', 7) . ' <> "system:" GROUP BY T0.tag ORDER BY bCount DESC, T0.tag';
         $query = $query_1 . $query_2 . $query_3 . $query_4 . $query_5;
 
         if (! ($dbresult = $this->db->sql_query_limit($query, $limit)) ){
@@ -445,7 +454,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService
             $privacy = ' AND B.bStatus = 0 ';
         }
 
-        $query = 'SELECT T.tag, COUNT(T.tag) AS bCount FROM '.$GLOBALS['tableprefix'].'bookmarks AS B LEFT JOIN '.$GLOBALS['tableprefix'].'bookmarks2tags AS T ON B.bId = T.bId WHERE B.bHash = \''. $this->db->sql_escape($hash) .'\' '. $privacy .'AND LEFT(T.tag, 7) <> "system:" GROUP BY T.tag ORDER BY bCount DESC';
+        $query = 'SELECT T.tag, COUNT(T.tag) AS bCount FROM '.$GLOBALS['tableprefix'].'bookmarks AS B LEFT JOIN '.$GLOBALS['tableprefix'].'bookmarks2tags AS T ON B.bId = T.bId WHERE B.bHash = \''. $this->db->sql_escape($hash) .'\' '. $privacy .'AND ' . $this->db->QueryBuilder->left('T.tag', 7) . ' <> "system:" GROUP BY T.tag ORDER BY bCount DESC';
 
         if (!($dbresult = $this->db->sql_query_limit($query, $limit))) {
             message_die(GENERAL_ERROR, 'Could not get related tags for this hash', '', __LINE__, __FILE__, $query, $this->db);
@@ -599,7 +608,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService
                 . '%\'';
         }
 
-        $query .= ' AND LEFT(T.tag, 7) <> "system:"'
+        $query .= ' AND ' . $this->db->QueryBuilder->left('T.tag', 7) . ' <> "system:"'
             . ' GROUP BY T.tag'
             . ' ORDER BY bCount DESC, tag';
 
diff --git a/src/SemanticScuttle/Service/Factory.php b/src/SemanticScuttle/Service/Factory.php
index b661cdb..4cc2efa 100644
--- a/src/SemanticScuttle/Service/Factory.php
+++ b/src/SemanticScuttle/Service/Factory.php
@@ -124,7 +124,7 @@ class SemanticScuttle_Service_Factory
         $db->sql_connect(
             $dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbpersist
         );
-        if (!$db->db_connect_id) {
+        if (!$db->db_connection) {
             message_die(
                 CRITICAL_ERROR,
                 'Could not connect to the database',
diff --git a/src/SemanticScuttle/Service/Tag.php b/src/SemanticScuttle/Service/Tag.php
index 17acae7..087a004 100644
--- a/src/SemanticScuttle/Service/Tag.php
+++ b/src/SemanticScuttle/Service/Tag.php
@@ -119,7 +119,13 @@ class SemanticScuttle_Service_Tag extends SemanticScuttle_DbService
             message_die(GENERAL_ERROR, 'Could not delete bookmarks', '', __LINE__, __FILE__, $query, $this->db);
             return false;
         }
-        $this->db->sql_transaction('commit');
+
+        if (!$this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(GENERAL_ERROR, 'Could not commit deleting bookmarks', '', __LINE__, __FILE__, $query, $this->db);
+            return false;
+        }
+
         return true;
     }
 
diff --git a/src/SemanticScuttle/Service/Tag2Tag.php b/src/SemanticScuttle/Service/Tag2Tag.php
index 9dddc44..d404bb5 100644
--- a/src/SemanticScuttle/Service/Tag2Tag.php
+++ b/src/SemanticScuttle/Service/Tag2Tag.php
@@ -94,7 +94,15 @@ class SemanticScuttle_Service_Tag2Tag extends SemanticScuttle_DbService
             );
             return false;
         }
-        $this->db->sql_transaction('commit');
+
+        if (!$this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(
+                GENERAL_ERROR, 'Could not commit attaching tag to tag',
+                '', __LINE__, __FILE__, $query, $this->db
+            );
+            return false;
+        }
 
         // Update stats and cache
         $this->update($tag1, $tag2, $relationType, $uId);
@@ -270,7 +278,7 @@ class SemanticScuttle_Service_Tag2Tag extends SemanticScuttle_DbService
             $tsts =SemanticScuttle_Service_Factory::get('TagStat');
             $query.= ", ".$tsts->getTableName() ." tsts";
         }
-        $query.= " WHERE tts.tag1 <> ALL";
+        $query.= " WHERE tts.tag1 not in";
         $query.= " (SELECT DISTINCT tag2 FROM `". $this->getTableName() ."`";
         $query.= " WHERE relationType = '" . $this->db->sql_escape($relationType) . "'";
         if($uId > 0) {
diff --git a/src/SemanticScuttle/Service/TagCache.php b/src/SemanticScuttle/Service/TagCache.php
index f8a28af..fb3c99a 100644
--- a/src/SemanticScuttle/Service/TagCache.php
+++ b/src/SemanticScuttle/Service/TagCache.php
@@ -106,7 +106,12 @@ class SemanticScuttle_Service_TagCache extends SemanticScuttle_DbService
             message_die(GENERAL_ERROR, 'Could not add tag cache inference', '', __LINE__, __FILE__, $query, $this->db);
             return false;
         }
-        $this->db->sql_transaction('commit');
+
+        if (!$this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(GENERAL_ERROR, 'Could not commit adding tag cache inference', '', __LINE__, __FILE__, $query, $this->db);
+            return false;
+        }
     }
 
     function removeChild($tag1, $tag2, $uId) {
@@ -220,7 +225,13 @@ class SemanticScuttle_Service_TagCache extends SemanticScuttle_DbService
                     message_die(GENERAL_ERROR, 'Could not add tag cache synonymy', '', __LINE__, __FILE__, $query, $this->db);
                     return false;
                 }
-                $this->db->sql_transaction('commit');
+
+                if (!$this->db->sql_transaction('commit')) {
+                    $this->db->sql_transaction('rollback');
+                    message_die(GENERAL_ERROR, 'Could not commit adding tag cache synonymy', '', __LINE__, __FILE__, $query, $this->db);
+                    return false;
+                }
+
                 break;
         }
     }
diff --git a/src/SemanticScuttle/Service/User.php b/src/SemanticScuttle/Service/User.php
index 68e8c11..dbbb202 100644
--- a/src/SemanticScuttle/Service/User.php
+++ b/src/SemanticScuttle/Service/User.php
@@ -103,15 +103,7 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService
                 GENERAL_ERROR, 'Could not get user',
                 '', __LINE__, __FILE__, $query, $this->db
             );
-            return [ 'uId' => 0,
-                     'username' => '',
-                     'name' => '',
-                     'email' => '',
-                     'homepage' => '',
-                     'content' => '',
-                     'datetime' => NULL,
-                     'isAdmin' => false,
-                     'privateKeey' => '' ];
+            return false;
         }
 
         $row = $this->db->sql_fetchrow($dbresult);
@@ -119,15 +111,7 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService
         if ($row) {
             return $row;
         } else {
-            return [ 'uId' => 0,
-                     'username' => '',
-                     'name' => '',
-                     'email' => '',
-                     'homepage' => '',
-                     'content' => '',
-                     'datetime' => NULL,
-                     'isAdmin' => false,
-                     'privateKeey' => '' ];
+            return false;
         }
     }
 
@@ -218,7 +202,15 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService
             );
             return false;
         }
-        $this->db->sql_transaction('commit');
+
+        if (!$this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(
+                GENERAL_ERROR, 'Could not commit user update', '',
+                __LINE__, __FILE__, $sql, $this->db
+            );
+            return false;
+        }
 
         // Everything worked out, so return true.
         return true;
@@ -411,7 +403,12 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService
     {
         if (is_numeric($user)) {
             $user = $this->getUser($user);
-            $user = $user['username'];
+            if ($user) {
+                $user = $user['username'];
+            }
+            else {
+                $user = 'unknown';
+            }
         } else if (is_array($user)) {
             $user = $user['username'];
         }
@@ -620,10 +617,6 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService
         }
 
         $arrWatch = array();
-        if ($this->db->sql_numrows($dbresult) == 0) {
-            $this->db->sql_freeresult($dbresult);
-            return $arrWatch;
-        }
         while ($row = $this->db->sql_fetchrow($dbresult)) {
             $arrWatch[] = $row['watched'];
         }
@@ -670,10 +663,6 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService
         }
 
         $arrWatch = array();
-        if ($this->db->sql_numrows($dbresult) == 0) {
-            $this->db->sql_freeresult($dbresult);
-            return $arrWatch;
-        }
         while ($row = $this->db->sql_fetchrow($dbresult)) {
             $arrWatch[] = $row[$this->getFieldName('username')];
         }
@@ -726,7 +715,12 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService
             }
         }
 
-        $this->db->sql_transaction('commit');
+        if (!$this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(GENERAL_ERROR, 'Could not commit adding user to watch list', '', __LINE__, __FILE__, $sql, $this->db);
+            return false;
+        }
+
         return true;
     }
 
@@ -770,7 +764,15 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService
             return false;
         }
         $uId = $this->db->sql_nextid($dbresult);
-        $this->db->sql_transaction('commit');
+
+        if (!$this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(
+                GENERAL_ERROR, 'Could not commit user',
+                '', __LINE__, __FILE__, $sql, $this->db
+            );
+            return false;
+        }
 
         return $uId;
     }
@@ -852,7 +854,15 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService
             );
             return false;
         }
-        $this->db->sql_transaction('commit');
+
+        if (!$this->db->sql_transaction('commit')) {
+            $this->db->sql_transaction('rollback');
+            message_die(
+                GENERAL_ERROR, 'Could not commit user update', '',
+                __LINE__, __FILE__, $sql, $this->db
+            );
+            return false;
+        }
 
         // Everything worked out, so return true.
         return true;
diff --git a/src/SemanticScuttle/db/QueryBuilder.php b/src/SemanticScuttle/db/QueryBuilder.php
new file mode 100644
index 0000000..7fe612f
--- /dev/null
+++ b/src/SemanticScuttle/db/QueryBuilder.php
@@ -0,0 +1,8 @@
+<?php
+class QueryBuilder
+{
+    public function isNotNull($query)
+    {
+        return " !ISNULL({$query}) ";
+    }
+}
diff --git a/src/SemanticScuttle/db/db2.php b/src/SemanticScuttle/db/db2.php
index 63c5bdd..0c75961 100644
--- a/src/SemanticScuttle/db/db2.php
+++ b/src/SemanticScuttle/db/db2.php
@@ -16,6 +16,8 @@ if(!defined("SQL_LAYER"))
 
 define("SQL_LAYER","db2");
 
+require_once 'QueryBuilder.php';
+
 /**
 * @package dbal_db2
 * DB2 Database Abstraction Layer
@@ -37,6 +39,14 @@ class sql_db
 	var $persistency;
 	var $dbname;
 	var $server;
+
+    public $QueryBuilder;
+
+    public function __construct()
+    {
+         $this->QueryBuilder = new QueryBuilder();
+    }
+
 	/*2023-12-20 end */
 	//
 	// Constructor
diff --git a/src/SemanticScuttle/db/firebird.php b/src/SemanticScuttle/db/firebird.php
index ab9198a..97a727b 100644
--- a/src/SemanticScuttle/db/firebird.php
+++ b/src/SemanticScuttle/db/firebird.php
@@ -16,6 +16,8 @@ if (!defined('SQL_LAYER'))
 
 define('SQL_LAYER', 'firebird');
 
+require_once 'QueryBuilder.php';
+
 /**
 * @package dbal_firebird
 * Firebird/Interbase Database Abstraction Layer
@@ -38,6 +40,14 @@ class sql_db
 	var $persistency;
 	var $dbname;
 	var $server;
+
+    public $QueryBuilder;
+
+    public function __construct()
+    {
+         $this->QueryBuilder = new QueryBuilder();
+    }
+
 	/*2023-12-20 end */
 	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
 	{
diff --git a/src/SemanticScuttle/db/mssql-odbc.php b/src/SemanticScuttle/db/mssql-odbc.php
index eb9d45b..967314b 100644
--- a/src/SemanticScuttle/db/mssql-odbc.php
+++ b/src/SemanticScuttle/db/mssql-odbc.php
@@ -16,6 +16,8 @@ if (!defined('SQL_LAYER'))
 
 define('SQL_LAYER', 'mssql-odbc');
 
+require_once 'QueryBuilder.php';
+
 /**
 * @package dbal_odbc_mssql
 * MSSQL ODBC Database Abstraction Layer for MSSQL
@@ -42,6 +44,14 @@ class sql_db
 	var $persistency;
 	var $dbname;
 	var $server;
+
+    public $QueryBuilder;
+
+    public function __construct()
+    {
+         $this->QueryBuilder = new QueryBuilder();
+    }
+
 	/*2023-12-20 end */
 	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
 	{
diff --git a/src/SemanticScuttle/db/mssql.php b/src/SemanticScuttle/db/mssql.php
index dbcfcae..3df0343 100644
--- a/src/SemanticScuttle/db/mssql.php
+++ b/src/SemanticScuttle/db/mssql.php
@@ -16,6 +16,8 @@ if (!defined('SQL_LAYER'))
 
 define('SQL_LAYER', 'mssql');
 
+require_once 'QueryBuilder.php';
+
 /**
 * @package dbal_mssql
 * MSSQL Database Abstraction Layer
@@ -36,6 +38,14 @@ class sql_db
 	var $persistency;
 	var $dbname;
 	var $server;
+
+    public $QueryBuilder;
+
+    public function __construct()
+    {
+         $this->QueryBuilder = new QueryBuilder();
+    }
+
 	/*2023-12-20 end */
 	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
 	{
diff --git a/src/SemanticScuttle/db/mysql.php b/src/SemanticScuttle/db/mysql.php
index f5f6cc2..649ad3b 100644
--- a/src/SemanticScuttle/db/mysql.php
+++ b/src/SemanticScuttle/db/mysql.php
@@ -16,6 +16,8 @@ if (!defined('SQL_LAYER'))
 
 define('SQL_LAYER', 'mysql');
 
+require_once 'QueryBuilder.php';
+
 /**
 * @package dbal_mysql
 * MySQL Database Abstraction Layer
@@ -37,6 +39,14 @@ class sql_db
 	var $dbname;
 	var $server;
 	/*2023-12-20 end */
+
+    public $QueryBuilder;
+
+    public function __construct()
+    {
+         $this->QueryBuilder = new QueryBuilder();
+    }
+
 	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
 	{
 		$this->persistency = $persistency;
diff --git a/src/SemanticScuttle/db/mysql4.php b/src/SemanticScuttle/db/mysql4.php
index 7244e51..51d757b 100644
--- a/src/SemanticScuttle/db/mysql4.php
+++ b/src/SemanticScuttle/db/mysql4.php
@@ -16,6 +16,8 @@ if (!defined('SQL_LAYER'))
 
 define('SQL_LAYER', 'mysql4');
 
+require_once 'QueryBuilder.php';
+
 /**
 * @package dbal_mysql4
 * MySQL4 Database Abstraction Layer
@@ -36,6 +38,14 @@ class sql_db
 	var $persistency;
 	var $dbname;
 	var $server;
+
+    public $QueryBuilder;
+
+    public function __construct()
+    {
+         $this->QueryBuilder = new QueryBuilder();
+    }
+
 	/*2023-12-20 end */
 	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
 	{
diff --git a/src/SemanticScuttle/db/mysqli.php b/src/SemanticScuttle/db/mysqli.php
index caaa893..5401d37 100644
--- a/src/SemanticScuttle/db/mysqli.php
+++ b/src/SemanticScuttle/db/mysqli.php
@@ -16,6 +16,8 @@ if (!defined('SQL_LAYER'))
 
 define('SQL_LAYER', 'mysqli');
 
+require_once 'QueryBuilder.php';
+
 /**
 * @package dbal_mysqli
 * MySQLi Database Abstraction Layer
@@ -43,6 +45,13 @@ class sql_db
 	var $cur_index;
 	/*2023-12-20 end block (see more below)*/
 
+    public $QueryBuilder;
+
+    public function __construct()
+    {
+         $this->QueryBuilder = new QueryBuilder();
+    }
+
 	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
 	{
 		$this->persistency = $persistency;
diff --git a/src/SemanticScuttle/db/oracle.php b/src/SemanticScuttle/db/oracle.php
index f1a45a6..3e017d4 100644
--- a/src/SemanticScuttle/db/oracle.php
+++ b/src/SemanticScuttle/db/oracle.php
@@ -16,6 +16,8 @@ if(!defined("SQL_LAYER"))
 
 define("SQL_LAYER","oracle");
 
+require_once 'QueryBuilder.php';
+
 /**
 * @package dbal_oracle
 * Oracle Database Abstraction Layer
@@ -36,6 +38,14 @@ class sql_db
 	var $dbname;
 	var $server;
 	/*2023-12-20 end */
+
+    public $QueryBuilder;
+
+    public function __construct()
+    {
+         $this->QueryBuilder = new QueryBuilder();
+    }
+
 	//
 	// Constructor
 	//
diff --git a/src/SemanticScuttle/db/postgres.php b/src/SemanticScuttle/db/postgres.php
index 4acd6a2..c9ed35c 100644
--- a/src/SemanticScuttle/db/postgres.php
+++ b/src/SemanticScuttle/db/postgres.php
@@ -16,6 +16,8 @@ if (!defined('SQL_LAYER'))
 
 define('SQL_LAYER', 'postgresql');
 
+require_once 'QueryBuilder.php';
+
 /**
 * @package dbal_postgres
 * PostgreSQL Database Abstraction Layer
@@ -37,6 +39,14 @@ class sql_db
 	var $dbname;
 	var $server;
 	/*2023-12-20 end */
+
+    public $QueryBuilder;
+
+    public function __construct()
+    {
+         $this->QueryBuilder = new QueryBuilder();
+    }
+
 	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false)
 	{
 		$this->connect_string = '';
diff --git a/src/SemanticScuttle/db/sqlite.php b/src/SemanticScuttle/db/sqlite.php
index 940e194..4531d17 100644
--- a/src/SemanticScuttle/db/sqlite.php
+++ b/src/SemanticScuttle/db/sqlite.php
@@ -1,10 +1,10 @@
 <?php
-/** 
+/**
 *
 * @package dbal_sqlite
 * @version $Id: sqlite.php,v 1.2 2005/06/10 08:52:03 devalley Exp $
-* @copyright (c) 2005 phpBB Group 
-* @license http://opensource.org/licenses/gpl-license.php GNU Public License 
+* @copyright (c) 2005 phpBB Group
+* @license http://opensource.org/licenses/gpl-license.php GNU Public License
 *
 */
 
@@ -16,13 +16,15 @@ if (!defined("SQL_LAYER"))
 
 define("SQL_LAYER","sqlite");
 
+require_once 'sqlite.querybuilder.php';
+
 /**
 * @package dbal_sqlite
 * Sqlite Database Abstraction Layer
 */
 class sql_db
 {
-	var $db_connect_id;
+    var $db_connection;
 	var $query_result;
 	var $return_on_error = false;
 	var $transaction = false;
@@ -38,6 +40,13 @@ class sql_db
 	var $server;
 	/*2023-12-20 end */
 
+    public $QueryBuilder;
+
+    public function __construct()
+    {
+         $this->QueryBuilder = new SQLiteQueryBuilder(); 
+    }
+
 	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port, $persistency = false)
 	{
 		$this->persistency = $persistency;
@@ -45,20 +54,20 @@ class sql_db
 		$this->server = $sqlserver . (($port) ? ':' . $port : '');
 		$this->dbname = $database;
 
-		$this->db_connect_id = ($this->persistency) ? @sqlite_popen($this->server, 0, $error) : @sqlite_open($this->server, 0, $error);
+		$this->db_connection = ($this->persistency) ? @sqlite_popen($this->server, 0, $error) : new SQLite3($this->server);
 
-		return ($this->db_connect_id) ? true : $error;
+		return ($this->db_connection) ? true : $error;
 	}
 
 	// Other base methods
 	function sql_close()
 	{
-		if (!$this->db_connect_id)
+		if (!$this->db_connection)
 		{
 			return false;
 		}
 
-		return @sqlite_close($this->db_connect_id);
+		return $this->db_connection->close();
 	}
 
 	function sql_return_on_error($fail = false)
@@ -77,17 +86,17 @@ class sql_db
 		{
 			case 'begin':
 				$this->transaction = true;
-				$result = @sqlite_query('BEGIN', $this->db_connect_id);
+				$result = $this->db_connection->exec('BEGIN');
 				break;
 
 			case 'commit':
 				$this->transaction = false;
-				$result = @sqlite_query('COMMIT', $this->db_connect_id);
+				$result = $this->db_connection->exec('COMMIT');
 				break;
 
 			case 'rollback':
 				$this->transaction = false;
-				$result = @sqlite_query('ROLLBACK', $this->db_connect_id);
+				$result = $this->db_connection->exec('ROLLBACK');
 				break;
 
 			default:
@@ -98,7 +107,7 @@ class sql_db
 	}
 
 	// Base query method
-	function sql_query($query = '', $expire_time = 0)
+	function sql_query($query = '', $cache_ttl = 0)
 	{
 		if ($query != '')
 		{
@@ -124,7 +133,7 @@ class sql_db
 					$curtime = $curtime[0] + $curtime[1] - $starttime;
 				}
 
-				if (!($this->query_result = @sqlite_query($query, $this->db_connect_id)))
+				if (!($this->query_result = $this->db_connection->query($query)))
 				{
 					$this->sql_error($query);
 				}
@@ -151,9 +160,9 @@ class sql_db
 					if (preg_match('#^SELECT#', $query))
 					{
 						$html_table = FALSE;
-						if ($result = @sqlite_query("EXPLAIN $query", $this->db_connect_id))
+						if ($result = $this->db_connection->query("EXPLAIN $query"))
 						{
-							while ($row = @sqlite_fetch_array($result, @sqlite_ASSOC))
+							while ($row = $result->fetchArray(SQLITE_ASSOC))
 							{
 								if (!$html_table && sizeof($row))
 								{
@@ -261,6 +270,25 @@ class sql_db
 			}
 			$query = implode(', ', $values);
 		}
+        else if ($query == 'SELECT')
+        {
+			foreach ($assoc_ary as $key => $var)
+			{
+				if (is_null($var))
+				{
+					$values[] = "$key = NULL";
+				}
+				elseif (is_string($var))
+				{
+					$values[] = "$key = '" . $this->sql_escape($var) . "'";
+				}
+				else
+				{
+					$values[] = (is_bool($var)) ? "$key = " . intval($var) : "$key = $var";
+				}
+			}
+			$query = implode(' AND ', $values);
+        }
 
 		return $query;
 	}
@@ -271,50 +299,46 @@ class sql_db
 	//         don't want this here by a middle Milestone
 	function sql_numrows($query_id = false)
 	{
-		if (!$query_id)
-		{
-			$query_id = $this->query_result;
-		}
-
-		return ($query_id) ? @sqlite_num_rows($query_id) : false;
+        // The new SQLite3 module in PHP doesn't provide a way of determining
+        // the number of rows returned before iterating through them.
+		throw new Exception('Not Implemented');
 	}
 
 	function sql_affectedrows()
 	{
-		return ($this->db_connect_id) ? @sqlite_changes($this->db_connect_id) : false;
+		return ($this->db_connection) ? $this->db_connection->changes() : false;
 	}
 
-	function sql_fetchrow($query_id = 0)
+	function sql_fetchrow($query_result = NULL)
 	{
 		global $cache;
 
-		if (!$query_id)
+		if (!$query_result)
 		{
-			$query_id = $this->query_result;
+			$query_result = $this->query_result;
 		}
 
-		if ($cache->sql_exists($query_id))
+		if (isset($cache->sql_rowset[$query_result]))
 		{
-			return $cache->sql_fetchrow($query_id);
+			return $cache->sql_fetchrow($query_result);
 		}
 
-		return ($query_id) ? @sqlite_fetch_array($query_id, @sqlite_ASSOC) : false;
+		return ($query_result) ? $query_result->fetchArray(SQLITE3_ASSOC) : false;
 	}
 
-	function sql_fetchrowset($query_id = 0)
+	function sql_fetchrowset($query_result = false)
 	{
-		if (!$query_id)
+		if (!$query_result)
 		{
-			$query_id = $this->query_result;
+			$query_result = $this->query_result;
 		}
 
-		if ($query_id)
+		if ($query_result)
 		{
-			unset($this->rowset[$query_id]);
-			unset($this->row[$query_id]);
-			while ($this->rowset[$query_id] = @sqlite_fetch_array($query_id, @sqlite_ASSOC))
+            $result = array();
+			while ($row = $query_result->fetchArray(SQLITE3_ASSOC))
 			{
-				$result[] = $this->rowset[$query_id];
+				$result[] = $row;
 			}
 			return $result;
 		}
@@ -331,9 +355,15 @@ class sql_db
 			$query_id = $this->query_result;
 		}
 
-		if ($query_id)
+        $row = [];
+
+        while ($rownum > 0) {
+            $row = $query_id->fetchArray(SQLITE3_NUM);
+        }
+
+		if ($row)
 		{
-			return ($rownum > -1) ? ((@sqlite_seek($query_id, $rownum)) ? @sqlite_column($query_id, $field) : false) : @sqlite_column($query_id, $field);
+			return $row[$field];
 		}
 	}
 
@@ -349,7 +379,7 @@ class sql_db
 
 	function sql_nextid()
 	{
-		return ($this->db_connect_id) ? @sqlite_last_insert_rowid($this->db_connect_id) : false;
+		return ($this->db_connection) ? $this->db_connection->lastInsertRowID() : false;
 	}
 
 	function sql_freeresult($query_id = false)
@@ -359,7 +389,7 @@ class sql_db
 
 	function sql_escape($msg)
 	{
-		return @sqlite_escape_string(stripslashes($msg));
+		return SQLite3::escapeString(stripslashes($msg));
 	}
 
 	function sql_error($sql = '')
@@ -369,7 +399,7 @@ class sql_db
 			$this_page = (!empty($_SERVER['PHP_SELF'])) ? $_SERVER['PHP_SELF'] : $_ENV['PHP_SELF'];
 			$this_page .= '&' . ((!empty($_SERVER['QUERY_STRING'])) ? $_SERVER['QUERY_STRING'] : $_ENV['QUERY_STRING']);
 
-			$message = '<u>SQL ERROR</u> [ ' . SQL_LAYER . ' ]<br /><br />' . @sqlite_error_string(@sqlite_last_error($this->db_connect_id)) . '<br /><br /><u>CALLING PAGE</u><br /><br />'  . htmlspecialchars($this_page) . (($sql != '') ? '<br /><br /><u>SQL</u><br /><br />' . $sql : '') . '<br />';
+			$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)
 			{
@@ -380,8 +410,8 @@ class sql_db
 		}
 
 		$result = array(
-			'message'	=> @sqlite_error_string(@sqlite_last_error($this->db_connect_id)),
-			'code'		=> @sqlite_last_error($this->db_connect_id)
+			'message'	=> $this->db_connection->lastErrorMsg(),
+			'code'		=> $this->db_connection->lastErrorCode(),
 		);
 
 		return $result;
diff --git a/src/SemanticScuttle/db/sqlite.querybuilder.php b/src/SemanticScuttle/db/sqlite.querybuilder.php
new file mode 100644
index 0000000..91cabcf
--- /dev/null
+++ b/src/SemanticScuttle/db/sqlite.querybuilder.php
@@ -0,0 +1,16 @@
+<?php
+require_once 'QueryBuilder.php';
+
+class SQLiteQueryBuilder extends QueryBuilder
+{
+    public function isNotNull($query)
+    {
+        return " IIF({$query}, true, false) ";
+    }
+
+    public function left($query, $length)
+    {
+        $endIndex = $length + 1;
+        return " SUBSTRING({$query}, 0, {$endIndex}) ";
+    }
+}