diff options
Diffstat (limited to 'src')
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}) "; + } +} |