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 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'src/SemanticScuttle/Service/Bookmark.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; -- cgit v1.2.3-54-g00ecf From 6e7fc03dd92f87477ac807519c48d0149b83c476 Mon Sep 17 00:00:00 2001 From: Tom Willemse Date: Tue, 11 Jun 2024 00:00:19 -0700 Subject: Throw an error if a bookmark can't be committed to the database --- src/SemanticScuttle/Service/Bookmark.php | 10 +++++++++- 1 file changed, 9 insertions(+), 1 deletion(-) (limited to 'src/SemanticScuttle/Service/Bookmark.php') diff --git a/src/SemanticScuttle/Service/Bookmark.php b/src/SemanticScuttle/Service/Bookmark.php index e157fc4..f617059 100644 --- a/src/SemanticScuttle/Service/Bookmark.php +++ b/src/SemanticScuttle/Service/Bookmark.php @@ -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; -- 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(-) (limited to 'src/SemanticScuttle/Service/Bookmark.php') 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