From eb81d7b851f51ab5919be78493737ef2abf49aab Mon Sep 17 00:00:00 2001
From: Tom Willemse
Date: Mon, 10 Jun 2024 23:38:09 -0700
Subject: Fix using SQLite as a database engine

- The ‘sqlite_*’ functions don't exist anymore in PHP 8. They have been replaced
  with several ‘SQLite3*’ classes.

- There are some differences between MySQL and SQLite queries that showed up
  while doing this work. These differences have been pushed into a QueryBuilder
  class so that the other database engines don't have to be modified. This is
  done in an ad-hoc basis for now, just to get things working.

- SQLite doesn't support the ‘!’ negation operator used as ‘!ISNULL(...)’,
  instead I use ‘IIF(...)’.

- SQLite doesn't support a ‘LEFT(...)’ function, instead I use ‘SUBSTRING(...,
  0, ...)’.

- The SQLite3 module doesn't provide a connection identifier, instead you use an
  object that represents the connection.

- SQLite doesn't support the ‘ALL’ keyword (or at least doesn't support it in
  the same way MySQL does). Instead of ‘<> ALL <subquery>’ I use ‘NOT IN
  <subquery>’.

- The ‘SQLite3*’ classes don't provide any way to determine how many rows have
  been returned without iterating through all of them, so any place that tries
  to figure out the rows beforehand just doesn't anymore.

- All the database engine classes require a ‘QueryBuilder’ instance. The sqlite
  one uses a specialized one. I can't test most of these database engines, so
  I'm focusing on SQLite and MySQL/MariaDB for now.
---
 src/SemanticScuttle/Service/Bookmark.php       |   4 +-
 src/SemanticScuttle/Service/Bookmark2Tag.php   |  12 +--
 src/SemanticScuttle/Service/Factory.php        |   2 +-
 src/SemanticScuttle/Service/Tag2Tag.php        |   2 +-
 src/SemanticScuttle/Service/User.php           |   8 --
 src/SemanticScuttle/db/QueryBuilder.php        |   8 ++
 src/SemanticScuttle/db/db2.php                 |  10 +++
 src/SemanticScuttle/db/firebird.php            |  10 +++
 src/SemanticScuttle/db/mssql-odbc.php          |  10 +++
 src/SemanticScuttle/db/mssql.php               |  10 +++
 src/SemanticScuttle/db/mysql.php               |  10 +++
 src/SemanticScuttle/db/mysql4.php              |  10 +++
 src/SemanticScuttle/db/mysqli.php              |   9 ++
 src/SemanticScuttle/db/oracle.php              |  10 +++
 src/SemanticScuttle/db/postgres.php            |  10 +++
 src/SemanticScuttle/db/sqlite.php              | 116 ++++++++++++++++---------
 src/SemanticScuttle/db/sqlite.querybuilder.php |  16 ++++
 17 files changed, 196 insertions(+), 61 deletions(-)
 create mode 100644 src/SemanticScuttle/db/QueryBuilder.php
 create mode 100644 src/SemanticScuttle/db/sqlite.querybuilder.php

(limited to 'src')

diff --git a/src/SemanticScuttle/Service/Bookmark.php b/src/SemanticScuttle/Service/Bookmark.php
index 17f91b1..e157fc4 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;
@@ -789,7 +789,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;
diff --git a/src/SemanticScuttle/Service/Bookmark2Tag.php b/src/SemanticScuttle/Service/Bookmark2Tag.php
index 910da48..2b95bd8 100644
--- a/src/SemanticScuttle/Service/Bookmark2Tag.php
+++ b/src/SemanticScuttle/Service/Bookmark2Tag.php
@@ -287,7 +287,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 +329,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 +367,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 +414,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 +445,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 +599,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/Tag2Tag.php b/src/SemanticScuttle/Service/Tag2Tag.php
index 9dddc44..9a5d0f2 100644
--- a/src/SemanticScuttle/Service/Tag2Tag.php
+++ b/src/SemanticScuttle/Service/Tag2Tag.php
@@ -270,7 +270,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/User.php b/src/SemanticScuttle/Service/User.php
index 675c4fb..d527031 100644
--- a/src/SemanticScuttle/Service/User.php
+++ b/src/SemanticScuttle/Service/User.php
@@ -609,10 +609,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'];
         }
@@ -659,10 +655,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')];
         }
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..49de59c 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->query('BEGIN');
 				break;
 
 			case 'commit':
 				$this->transaction = false;
-				$result = @sqlite_query('COMMIT', $this->db_connect_id);
+				$result = $this->db_connection->query('COMMIT');
 				break;
 
 			case 'rollback':
 				$this->transaction = false;
-				$result = @sqlite_query('ROLLBACK', $this->db_connect_id);
+				$result = $this->db_connection->query('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}) ";
+    }
+}
-- 
cgit v1.2.3-54-g00ecf