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 ’ I use ‘NOT IN ’. - 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/db/sqlite.php | 116 ++++++++++++++++++++++++-------------- 1 file changed, 73 insertions(+), 43 deletions(-) (limited to 'src/SemanticScuttle/db/sqlite.php') 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 @@ 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 = 'SQL ERROR [ ' . SQL_LAYER . ' ]

' . @sqlite_error_string(@sqlite_last_error($this->db_connect_id)) . '

CALLING PAGE

' . htmlspecialchars($this_page) . (($sql != '') ? '

SQL

' . $sql : '') . '
'; + $message = 'SQL ERROR [ ' . SQL_LAYER . ' ]

' . $this->db_connection->lastErrorMsg() . '

CALLING PAGE

' . htmlspecialchars($this_page) . (($sql != '') ? '

SQL

' . $sql : '') . '
'; 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; -- cgit v1.2.3-54-g00ecf