From 4f0689ca560e0b7fe8d989e2787520f009ebba02 Mon Sep 17 00:00:00 2001 From: Tom Willemse Date: Mon, 10 Jun 2024 00:35:34 -0700 Subject: Add initial sqlite tables file --- data/tables.sqlite.sql | 203 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 203 insertions(+) create mode 100644 data/tables.sqlite.sql diff --git a/data/tables.sqlite.sql b/data/tables.sqlite.sql new file mode 100644 index 0000000..6a22ad1 --- /dev/null +++ b/data/tables.sqlite.sql @@ -0,0 +1,203 @@ +-- Semantic Scuttle - Tables creation SQL script +-- ! Dont forget to change table names according to $tableprefix defined in config.php ! + +-- +-- Table structure for table `sc_bookmarks` +-- + +CREATE TABLE IF NOT EXISTS `sc_bookmarks` ( + `bId` integer NOT NULL, + `uId` int(11) NOT NULL default '0', + `bIp` varchar(40) default NULL, + `bStatus` tinyint(1) NOT NULL default '0', + `bDatetime` datetime NOT NULL default '0000-00-00 00:00:00', + `bModified` datetime NOT NULL default '0000-00-00 00:00:00', + `bTitle` varchar(255) NOT NULL default '', + `bAddress` varchar(1500) NOT NULL, + `bDescription` text default NULL, + `bPrivateNote` text default NULL, + `bHash` varchar(32) NOT NULL default '', + `bVotes` int(11) NOT NULL default '0', + `bVoting` int(11) NOT NULL default '0', + `bShort` varchar(16) default NULL, + PRIMARY KEY (`bId`), + CONSTRAINT `sc_bookmarks_usd` UNIQUE (`uId`,`bStatus`,`bDatetime`), + CONSTRAINT `sc_bookmarks_hui` UNIQUE (`bHash`,`uId`,`bId`), + CONSTRAINT `sc_bookmarks_du` UNIQUE (`bDatetime`,`uId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_tags` +-- + +CREATE TABLE IF NOT EXISTS `sc_tags` ( + `tId` integer NOT NULL, + `tag` varchar(100) NOT NULL default '', + `uId` int(11) NOT NULL default '0', + `tDescription` text default NULL, + PRIMARY KEY (`tId`), + CONSTRAINT `sc_tags_tag_uId` UNIQUE (`tag`, `uId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_bookmarks2tags` +-- + +CREATE TABLE IF NOT EXISTS `sc_bookmarks2tags` ( + `id` integer NOT NULL, + `bId` int(11) NOT NULL default '0', + `tag` varchar(100) NOT NULL default '', + PRIMARY KEY (`id`), + UNIQUE (`tag`,`bId`), + CONSTRAINT `sc_bookmarks2tags_bId` UNIQUE (`bId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_users` +-- + +CREATE TABLE IF NOT EXISTS `sc_users` ( + `uId` integer NOT NULL, + `username` varchar(25) NOT NULL default '', + `password` varchar(40) NOT NULL default '', + `uDatetime` datetime NOT NULL default '0000-00-00 00:00:00', + `uModified` datetime NOT NULL default '0000-00-00 00:00:00', + `name` varchar(50) default NULL, + `email` varchar(50) NOT NULL default '', + `homepage` varchar(255) default NULL, + `uContent` text, + `privateKey` varchar(33) default NULL, + PRIMARY KEY (`uId`), + CONSTRAINT `privateKey` UNIQUE (`privateKey`) +); + +-- -------------------------------------------------------- + +CREATE TABLE IF NOT EXISTS `sc_users_sslclientcerts` ( + `id` INTEGER NOT NULL , + `uId` INT NOT NULL , + `sslSerial` VARCHAR( 32 ) NOT NULL , + `sslClientIssuerDn` VARCHAR( 1024 ) NOT NULL , + `sslName` VARCHAR( 64 ) NOT NULL , + `sslEmail` VARCHAR( 64 ) NOT NULL , + PRIMARY KEY ( `id` ) +); + +-- +-- Table structure for table `sc_watched` +-- + +CREATE TABLE IF NOT EXISTS `sc_watched` ( + `wId` integer NOT NULL, + `uId` int(11) NOT NULL default '0', + `watched` int(11) NOT NULL default '0', + PRIMARY KEY (`wId`), + CONSTRAINT `sc_watched_uId` UNIQUE (`uId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_tags2tags` +-- + +CREATE TABLE IF NOT EXISTS `sc_tags2tags` ( + `ttId` integer NOT NULL, + `tag1` varchar(100) NOT NULL default '', + `tag2` varchar(100) NOT NULL default '', + `relationType` varchar(32) NOT NULL default '', + `uId` int(11) NOT NULL default '0', + PRIMARY KEY (`ttId`), + CONSTRAINT `sc_tags2tags_tag1_tag2_uId` UNIQUE (`tag1`,`tag2`,`relationType`,`uId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_tagsstats` +-- + +CREATE TABLE IF NOT EXISTS `sc_tagsstats` ( + `tstId` integer NOT NULL, + `tag1` varchar(100) NOT NULL default '', + `relationType` varchar(32) NOT NULL default '', + `uId` int(11) NOT NULL default '0', + `nb` int(11) NOT NULL default '0', + `depth` int(11) NOT NULL default '0', + `nbupdate` int(11) NOT NULL default '0', + PRIMARY KEY (`tstId`), + CONSTRAINT `sc_tagsstats_tag1_type_uId` UNIQUE (`tag1`,`relationType`,`uId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_tagscache` +-- + +CREATE TABLE IF NOT EXISTS `sc_tagscache` ( + `tcId` integer NOT NULL, + `tag1` varchar(100) NOT NULL default '', + `tag2` varchar(100) NOT NULL default '', + `relationType` varchar(32) NOT NULL default '', + `uId` int(11) NOT NULL default '0', + PRIMARY KEY (`tcId`), + CONSTRAINT `sc_tagscache_tag1_tag2_type_uId` UNIQUE (`tag1`,`tag2`,`relationType`,`uId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_commondescription` +-- + +CREATE TABLE IF NOT EXISTS `sc_commondescription` ( + `cdId` integer NOT NULL, + `uId` int(11) NOT NULL default '0', + `tag` varchar(100) NOT NULL default '', + `bHash` varchar(32) NOT NULL default '', + `cdTitle` varchar(255) NOT NULL default '', + `cdDescription` text default NULL, + `cdDatetime` datetime NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (`cdId`), + CONSTRAINT `sc_commondescription_tag_datetime` UNIQUE (`tag`,`CDDATETIME`), + CONSTRAINT `sc_commondescription_bookmark_datetime` UNIQUE (`bHash`,`cdDatetime`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_searchhistory` +-- + +CREATE TABLE IF NOT EXISTS `sc_searchhistory` ( + `shId` integer NOT NULL, + `shTerms` varchar(255) NOT NULL default '', + `shRange` varchar(32) NOT NULL default '', + `shDatetime` datetime NOT NULL default '0000-00-00 00:00:00', + `shNbResults` int(6) NOT NULL default '0', + `uId` int(11) NOT NULL default '0', + PRIMARY KEY (`shId`) +); + + +CREATE TABLE IF NOT EXISTS `sc_votes` ( + `bId` INT NOT NULL , + `uId` INT NOT NULL , + `vote` INT( 2 ) NOT NULL , + CONSTRAINT `bid_2` UNIQUE (`bId`,`uId`), + CONSTRAINT `bid` UNIQUE (`bId`), + CONSTRAINT `uid` UNIQUE (`uId`) +); + + +CREATE TABLE IF NOT EXISTS `sc_version` ( + `schema_version` int(11) NOT NULL +); +INSERT INTO `sc_version` (`schema_version`) VALUES ('6'); -- cgit v1.2.3-54-g00ecf From 53b174a3236d89e1528ea663584f6bbca3c24c53 Mon Sep 17 00:00:00 2001 From: Tom Willemse Date: Mon, 10 Jun 2024 11:04:41 -0700 Subject: Revert "Fix issue accessing an array index on a boolean" This reverts commit e1bfad3df7b41cbc8537222f211edaf9b3444edf. --- src/SemanticScuttle/Service/User.php | 20 ++------------------ 1 file changed, 2 insertions(+), 18 deletions(-) diff --git a/src/SemanticScuttle/Service/User.php b/src/SemanticScuttle/Service/User.php index 68e8c11..917c7c9 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; } } -- cgit v1.2.3-54-g00ecf From b5ac1fa2a33ae046fa785d9938dcd0d5274b7ce1 Mon Sep 17 00:00:00 2001 From: Tom Willemse Date: Mon, 10 Jun 2024 21:09:15 -0700 Subject: Make sure to check users for being false --- .../default/bookmarkcommondescriptionedit.tpl.php | 9 ++++++-- data/templates/default/bookmarks.tpl.php | 2 +- data/templates/default/sidebar.block.menu.php | 2 +- .../default/tagcommondescriptionedit.tpl.php | 9 ++++++-- .../minimal/bookmarkcommondescriptionedit.tpl.php | 9 ++++++-- data/templates/minimal/bookmarks.tpl.php | 2 +- data/templates/minimal/sidebar.block.menu.php | 2 +- .../minimal/tagcommondescriptionedit.tpl.php | 9 ++++++-- .../bookmarkcommondescriptionedit.tpl.php | 9 ++++++-- data/templates/sscuttlizr/bookmarks.tpl.php | 2 +- data/templates/sscuttlizr/sidebar.block.menu.php | 2 +- .../sscuttlizr/tagcommondescriptionedit.tpl.php | 9 ++++++-- src/SemanticScuttle/Model/User.php | 27 +++++++++++++++++----- src/SemanticScuttle/Service/AuthUser.php | 12 ++++++---- src/SemanticScuttle/Service/User.php | 7 +++++- www/profile.php | 5 ++-- 16 files changed, 85 insertions(+), 32 deletions(-) diff --git a/data/templates/default/bookmarkcommondescriptionedit.tpl.php b/data/templates/default/bookmarkcommondescriptionedit.tpl.php index 807c58b..306086e 100644 --- a/data/templates/default/bookmarkcommondescriptionedit.tpl.php +++ b/data/templates/default/bookmarkcommondescriptionedit.tpl.php @@ -30,8 +30,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '' - . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + if ($lastUser) { + echo '' + . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + } + else { + echo 'Unknown user'; + } } ?> diff --git a/data/templates/default/bookmarks.tpl.php b/data/templates/default/bookmarks.tpl.php index 2a3d169..0f88a60 100644 --- a/data/templates/default/bookmarks.tpl.php +++ b/data/templates/default/bookmarks.tpl.php @@ -86,7 +86,7 @@ if ($userservice->isLoggedOn()) { /* personal tag description */ if($currenttag!= '' && $user!='') { $userObject = $userservice->getUserByUsername($user); - if($tagservice->getDescription($currenttag, $userObject['uId'])) { ?> + if($userObject && $tagservice->getDescription($currenttag, $userObject['uId'])) { ?>

getDescription($currenttag, $userObject['uId']); diff --git a/data/templates/default/sidebar.block.menu.php b/data/templates/default/sidebar.block.menu.php index 94a9fa2..dfb2c25 100644 --- a/data/templates/default/sidebar.block.menu.php +++ b/data/templates/default/sidebar.block.menu.php @@ -63,7 +63,7 @@ if (sizeof($menuTags) > 0 || ($userid != 0 && $userid === $logged_on_userid)) { getUser($userid); ?> -0): ?> +0): ?>

→

diff --git a/data/templates/default/tagcommondescriptionedit.tpl.php b/data/templates/default/tagcommondescriptionedit.tpl.php index 207cfd2..f52d5b0 100644 --- a/data/templates/default/tagcommondescriptionedit.tpl.php +++ b/data/templates/default/tagcommondescriptionedit.tpl.php @@ -20,8 +20,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '' - . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + if ($lastUser) { + echo '' + . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + } + else { + echo 'Unknown user'; + } } ?> diff --git a/data/templates/minimal/bookmarkcommondescriptionedit.tpl.php b/data/templates/minimal/bookmarkcommondescriptionedit.tpl.php index b1114d7..4629d72 100644 --- a/data/templates/minimal/bookmarkcommondescriptionedit.tpl.php +++ b/data/templates/minimal/bookmarkcommondescriptionedit.tpl.php @@ -33,8 +33,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '' - . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + if ($lastUser) { + echo '' + . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + } + else { + echo 'Unknown user'; + } } ?> diff --git a/data/templates/minimal/bookmarks.tpl.php b/data/templates/minimal/bookmarks.tpl.php index 1510d44..86749bc 100644 --- a/data/templates/minimal/bookmarks.tpl.php +++ b/data/templates/minimal/bookmarks.tpl.php @@ -87,7 +87,7 @@ if ($userservice->isLoggedOn()) { /* personal tag description */ if($currenttag!= '' && $user!='') { $userObject = $userservice->getUserByUsername($user); - if($tagservice->getDescription($currenttag, $userObject['uId'])) { ?> + if($userObject && $tagservice->getDescription($currenttag, $userObject['uId'])) { ?>

getDescription($currenttag, $userObject['uId']); diff --git a/data/templates/minimal/sidebar.block.menu.php b/data/templates/minimal/sidebar.block.menu.php index 94a9fa2..dfb2c25 100644 --- a/data/templates/minimal/sidebar.block.menu.php +++ b/data/templates/minimal/sidebar.block.menu.php @@ -63,7 +63,7 @@ if (sizeof($menuTags) > 0 || ($userid != 0 && $userid === $logged_on_userid)) { getUser($userid); ?> -0): ?> +0): ?>

→

diff --git a/data/templates/minimal/tagcommondescriptionedit.tpl.php b/data/templates/minimal/tagcommondescriptionedit.tpl.php index e46c184..bde2ff8 100644 --- a/data/templates/minimal/tagcommondescriptionedit.tpl.php +++ b/data/templates/minimal/tagcommondescriptionedit.tpl.php @@ -24,8 +24,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '' - . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + if ($lastUser) { + echo '' + . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + } + else { + echo 'Unknown user'; + } } ?> diff --git a/data/templates/sscuttlizr/bookmarkcommondescriptionedit.tpl.php b/data/templates/sscuttlizr/bookmarkcommondescriptionedit.tpl.php index b1114d7..4629d72 100644 --- a/data/templates/sscuttlizr/bookmarkcommondescriptionedit.tpl.php +++ b/data/templates/sscuttlizr/bookmarkcommondescriptionedit.tpl.php @@ -33,8 +33,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '' - . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + if ($lastUser) { + echo '' + . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + } + else { + echo 'Unknown user'; + } } ?> diff --git a/data/templates/sscuttlizr/bookmarks.tpl.php b/data/templates/sscuttlizr/bookmarks.tpl.php index 2df30db..c87a337 100644 --- a/data/templates/sscuttlizr/bookmarks.tpl.php +++ b/data/templates/sscuttlizr/bookmarks.tpl.php @@ -87,7 +87,7 @@ if ($userservice->isLoggedOn()) { /* personal tag description */ if($currenttag!= '' && $user!='') { $userObject = $userservice->getUserByUsername($user); - if($tagservice->getDescription($currenttag, $userObject['uId'])) { ?> + if($userObject && $tagservice->getDescription($currenttag, $userObject['uId'])) { ?>

getDescription($currenttag, $userObject['uId']); diff --git a/data/templates/sscuttlizr/sidebar.block.menu.php b/data/templates/sscuttlizr/sidebar.block.menu.php index 94a9fa2..dfb2c25 100644 --- a/data/templates/sscuttlizr/sidebar.block.menu.php +++ b/data/templates/sscuttlizr/sidebar.block.menu.php @@ -63,7 +63,7 @@ if (sizeof($menuTags) > 0 || ($userid != 0 && $userid === $logged_on_userid)) { getUser($userid); ?> -0): ?> +0): ?>

→

diff --git a/data/templates/sscuttlizr/tagcommondescriptionedit.tpl.php b/data/templates/sscuttlizr/tagcommondescriptionedit.tpl.php index e46c184..bde2ff8 100644 --- a/data/templates/sscuttlizr/tagcommondescriptionedit.tpl.php +++ b/data/templates/sscuttlizr/tagcommondescriptionedit.tpl.php @@ -24,8 +24,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '' - . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + if ($lastUser) { + echo '' + . SemanticScuttle_Model_UserArray::getName($lastUser) . ''; + } + else { + echo 'Unknown user'; + } } ?> 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/User.php b/src/SemanticScuttle/Service/User.php index 917c7c9..675c4fb 100644 --- a/src/SemanticScuttle/Service/User.php +++ b/src/SemanticScuttle/Service/User.php @@ -395,7 +395,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']; } diff --git a/www/profile.php b/www/profile.php index 6a4222e..88e58e5 100644 --- a/www/profile.php +++ b/www/profile.php @@ -130,10 +130,11 @@ if (POST_SUBMITTED!='' && $currentUser->getId() == $userid) { } } $userinfo = $userservice->getObjectUserByUsername($user); - $tplVars['privateKey'] = $userinfo->getPrivateKey(true); - if ($userservice->isPrivateKeyValid($userinfo->getPrivateKey())) { + if ($userinfo && $userservice->isPrivateKeyValid($userinfo->getPrivateKey())) { + $tplVars['privateKey'] = $userinfo->getPrivateKey(true); $tplVars['privateKeyIsEnabled'] = 'checked="checked"'; } else { + $tplVars['privateKey'] = null; $tplVars['privateKeyIsEnabled'] = ''; } } -- cgit v1.2.3-54-g00ecf From c6101ba37422dcfcf16131d9dc41692e0725aa81 Mon Sep 17 00:00:00 2001 From: Tom Willemse Date: Mon, 10 Jun 2024 23:35:51 -0700 Subject: Fix initial database import for sqlite My understanding of the ‘KEY’ keyword in the ‘tables.sql’ was incomplete. I didn't realize that it made indexes. --- data/tables.sqlite.sql | 26 +++++++++++++++----------- 1 file changed, 15 insertions(+), 11 deletions(-) diff --git a/data/tables.sqlite.sql b/data/tables.sqlite.sql index 6a22ad1..b7265cc 100644 --- a/data/tables.sqlite.sql +++ b/data/tables.sqlite.sql @@ -20,12 +20,13 @@ CREATE TABLE IF NOT EXISTS `sc_bookmarks` ( `bVotes` int(11) NOT NULL default '0', `bVoting` int(11) NOT NULL default '0', `bShort` varchar(16) default NULL, - PRIMARY KEY (`bId`), - CONSTRAINT `sc_bookmarks_usd` UNIQUE (`uId`,`bStatus`,`bDatetime`), - CONSTRAINT `sc_bookmarks_hui` UNIQUE (`bHash`,`uId`,`bId`), - CONSTRAINT `sc_bookmarks_du` UNIQUE (`bDatetime`,`uId`) + PRIMARY KEY (`bId`) ); +CREATE INDEX IF NOT EXISTS `sc_bookmarks_usd` ON `sc_bookmarks` (`uId`, `bStatus`, `bDatetime`); +CREATE INDEX IF NOT EXISTS `sc_bookmarks_hui` ON `sc_bookmarks` (`bHash`, `uId`, `bId`); +CREATE INDEX IF NOT EXISTS `sc_bookmarks_du` ON `sc_bookmarks` (`bDatetime`, `uId`); + -- -------------------------------------------------------- -- @@ -52,10 +53,11 @@ CREATE TABLE IF NOT EXISTS `sc_bookmarks2tags` ( `bId` int(11) NOT NULL default '0', `tag` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), - UNIQUE (`tag`,`bId`), - CONSTRAINT `sc_bookmarks2tags_bId` UNIQUE (`bId`) + CONSTRAINT `sc_bookmarks2tags_tag_bId` UNIQUE (`tag`,`bId`) ); +CREATE INDEX IF NOT EXISTS `sc_bookmarks2tags_bId` ON `sc_bookmarks2tags` (`bId`); + -- -------------------------------------------------------- -- @@ -97,10 +99,11 @@ CREATE TABLE IF NOT EXISTS `sc_watched` ( `wId` integer NOT NULL, `uId` int(11) NOT NULL default '0', `watched` int(11) NOT NULL default '0', - PRIMARY KEY (`wId`), - CONSTRAINT `sc_watched_uId` UNIQUE (`uId`) + PRIMARY KEY (`wId`) ); +CREATE INDEX IF NOT EXISTS `sc_watched_uId` ON `sc_watched` (`uId`); + -- -------------------------------------------------------- -- @@ -191,11 +194,12 @@ CREATE TABLE IF NOT EXISTS `sc_votes` ( `bId` INT NOT NULL , `uId` INT NOT NULL , `vote` INT( 2 ) NOT NULL , - CONSTRAINT `bid_2` UNIQUE (`bId`,`uId`), - CONSTRAINT `bid` UNIQUE (`bId`), - CONSTRAINT `uid` UNIQUE (`uId`) + CONSTRAINT `bid_2` UNIQUE (`bId`,`uId`) ); +CREATE INDEX IF NOT EXISTS `bId` ON `sc_votes` (`bId`); +CREATE INDEX IF NOT EXISTS `uId` ON `sc_votes` (`uId`); + CREATE TABLE IF NOT EXISTS `sc_version` ( `schema_version` int(11) NOT NULL -- cgit v1.2.3-54-g00ecf 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/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 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 @@ +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 @@ 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; 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 @@ +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; -- cgit v1.2.3-54-g00ecf From dec3464bffa14245b2cea076f2aac3a55283230d Mon Sep 17 00:00:00 2001 From: Tom Willemse Date: Tue, 11 Jun 2024 00:00:38 -0700 Subject: Use ‘exec’ instead of ‘query’ for transaction commands in sqlite The ‘query’ method will try and return a ‘SQLite3Result’ object, which will be meaningless because it won't contain any fetched data. The ‘exec’ method instead returns a boolean indicating the success or failure of the query. --- src/SemanticScuttle/db/sqlite.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/src/SemanticScuttle/db/sqlite.php b/src/SemanticScuttle/db/sqlite.php index 49de59c..4531d17 100644 --- a/src/SemanticScuttle/db/sqlite.php +++ b/src/SemanticScuttle/db/sqlite.php @@ -86,17 +86,17 @@ class sql_db { case 'begin': $this->transaction = true; - $result = $this->db_connection->query('BEGIN'); + $result = $this->db_connection->exec('BEGIN'); break; case 'commit': $this->transaction = false; - $result = $this->db_connection->query('COMMIT'); + $result = $this->db_connection->exec('COMMIT'); break; case 'rollback': $this->transaction = false; - $result = $this->db_connection->query('ROLLBACK'); + $result = $this->db_connection->exec('ROLLBACK'); break; default: -- cgit v1.2.3-54-g00ecf From 85cca718e308779a56dbf302140ce9088fc0727a Mon Sep 17 00:00:00 2001 From: Tom Willemse Date: Tue, 11 Jun 2024 00:11:58 -0700 Subject: Throw an error when changes can't be committed to the database I was testing and another process had the database open, so it couldn't commit changes. This wasn't apparent from the UI because it would just silently assume a commit went fine. --- src/SemanticScuttle/Service/Bookmark.php | 16 ++++++++++-- src/SemanticScuttle/Service/Bookmark2Tag.php | 11 ++++++++- src/SemanticScuttle/Service/Tag.php | 8 +++++- src/SemanticScuttle/Service/Tag2Tag.php | 10 +++++++- src/SemanticScuttle/Service/TagCache.php | 15 +++++++++-- src/SemanticScuttle/Service/User.php | 37 +++++++++++++++++++++++++--- 6 files changed, 86 insertions(+), 11 deletions(-) diff --git a/src/SemanticScuttle/Service/Bookmark.php b/src/SemanticScuttle/Service/Bookmark.php index f617059..7eb1174 100644 --- a/src/SemanticScuttle/Service/Bookmark.php +++ b/src/SemanticScuttle/Service/Bookmark.php @@ -661,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; } @@ -1011,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 2b95bd8..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; } 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 9a5d0f2..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); 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 d527031..dbbb202 100644 --- a/src/SemanticScuttle/Service/User.php +++ b/src/SemanticScuttle/Service/User.php @@ -202,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; @@ -707,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; } @@ -751,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; } @@ -833,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; -- cgit v1.2.3-54-g00ecf