diff options
Diffstat (limited to 'src/SemanticScuttle/db')
-rw-r--r-- | src/SemanticScuttle/db/QueryBuilder.php | 8 | ||||
-rw-r--r-- | src/SemanticScuttle/db/db2.php | 10 | ||||
-rw-r--r-- | src/SemanticScuttle/db/firebird.php | 10 | ||||
-rw-r--r-- | src/SemanticScuttle/db/mssql-odbc.php | 10 | ||||
-rw-r--r-- | src/SemanticScuttle/db/mssql.php | 10 | ||||
-rw-r--r-- | src/SemanticScuttle/db/mysql.php | 10 | ||||
-rw-r--r-- | src/SemanticScuttle/db/mysql4.php | 10 | ||||
-rw-r--r-- | src/SemanticScuttle/db/mysqli.php | 9 | ||||
-rw-r--r-- | src/SemanticScuttle/db/oracle.php | 10 | ||||
-rw-r--r-- | src/SemanticScuttle/db/postgres.php | 10 | ||||
-rw-r--r-- | src/SemanticScuttle/db/sqlite.php | 148 | ||||
-rw-r--r-- | src/SemanticScuttle/db/sqlite.querybuilder.php | 16 |
12 files changed, 210 insertions, 51 deletions
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..f34be28 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,16 +16,17 @@ 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; var $sql_report = ''; var $sql_time = 0; var $num_queries = 0; @@ -38,6 +39,15 @@ class sql_db var $server; /*2023-12-20 end */ + public $QueryBuilder; + + private int $transaction_level = 0; + + public function __construct() + { + $this->QueryBuilder = new SQLiteQueryBuilder(); + } + function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port, $persistency = false) { $this->persistency = $persistency; @@ -45,20 +55,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) @@ -76,19 +86,34 @@ class sql_db switch ($status) { case 'begin': - $this->transaction = true; - $result = @sqlite_query('BEGIN', $this->db_connect_id); - 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 = @sqlite_query('COMMIT', $this->db_connect_id); - 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 = @sqlite_query('ROLLBACK', $this->db_connect_id); - break; + $this->transaction_level--; + if ($this->transaction_level == 0) { + $result = $this->db_connection->exec('ROLLBACK'); + break; + } + else { + $result = true; + } default: $result = true; @@ -98,7 +123,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 +149,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 +176,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 +286,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 +315,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 +371,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 +395,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 +405,7 @@ class sql_db function sql_escape($msg) { - return @sqlite_escape_string(stripslashes($msg)); + return SQLite3::escapeString(stripslashes($msg)); } function sql_error($sql = '') @@ -369,9 +415,9 @@ 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) + while ($this->transaction_level > 0) { $this->sql_transaction('rollback'); } @@ -380,8 +426,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}) "; + } +} |