From 32b2ecb83e47aeea8ed6faaeeeeea2a57a135be7 Mon Sep 17 00:00:00 2001 From: Banana Date: Mon, 11 Sep 2023 15:39:37 +0200 Subject: [PATCH] query updates and update notes --- ChangeLog | 1 + documentation/update.txt | 11 ++++- webroot/lib/management.class.php | 83 ++++++++++++++++++-------------- webroot/lib/summoner.class.php | 2 +- webroot/lib/tag.class.php | 14 +++--- webroot/view/home.inc.php | 8 +-- 6 files changed, 68 insertions(+), 51 deletions(-) diff --git a/ChangeLog b/ChangeLog index 73e6edd..66c2ed2 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,6 +1,7 @@ version 2.x - Dragon Chapel () + Added LOGFILE and QUERY_DEBUG const to config.php. See update.txt for more details + + Improved DB queries. + Add top 5 tag or category relation stats in the tag and category overview + Update license to GNU GENERAL PUBLIC LICENSE Version 3, 29 June 2007 + Updated error logging and try/catch for mysql diff --git a/documentation/update.txt b/documentation/update.txt index 9fed812..f01058f 100644 --- a/documentation/update.txt +++ b/documentation/update.txt @@ -2,10 +2,17 @@ If you are updating from a previous version make sure every update info from the version your are updating from is done. ## version 2. - Dragon Chapel -+ Added query debug setting to config file. ++ Add query debug setting to config file. See config.default.php for QUERY_DEBUG constant. Add it to your local config.php -+ Added log file path constant to config file. ++ Add log file path constant to config file. See config.default.php for LOGFILE constant. Add it to your local config.php ++ Update your tables with the following SQL statements. Replace #REPLACE_ME# with your current table prefix. +ALTER TABLE `#REPLACE_ME#_link` ADD INDEX (`created`); +ALTER TABLE `#REPLACE_ME#_link` ADD INDEX (`status`); +ALTER TABLE `#REPLACE_ME#_categoryrelation` ADD INDEX (`categoryid`); +ALTER TABLE `#REPLACE_ME#_tagrelation` ADD UNIQUE `tagid` (`linkid`, `tagid`); +ALTER TABLE `#REPLACE_ME#_tagrelation` ADD INDEX (`linkid`); +ALTER TABLE `#REPLACE_ME#_category` ADD INDEX (`name`); ## version 2.8.1 - Deathwind + Added debug setting into config. See config.default.php for DEBUG constant. diff --git a/webroot/lib/management.class.php b/webroot/lib/management.class.php index 7ab6a35..bdc05a3 100644 --- a/webroot/lib/management.class.php +++ b/webroot/lib/management.class.php @@ -50,6 +50,10 @@ class Management { */ private int $_queryStatus = self::LINK_QUERY_STATUS; + /** + * @var array Store already loaded categories to avoid unneeded queries + */ + private array $_categories; /** * Management constructor. @@ -92,19 +96,22 @@ class Management { * optional limit * optional stats * - * @param int $limit + * @param string $limit * @param bool $stats * @return array */ - public function categories(int $limit=0, bool $stats=false): array { + public function categories(string $limit="0", bool $stats=false): array { $ret = array(); $statsInfo = array(); + if(!empty($this->_categories)) return $this->_categories; + if($stats === true) { $queryStr = "SELECT COUNT(*) AS amount, cr.categoryid AS categoryId - FROM `".DB_PREFIX."_categoryrelation` AS cr, `".DB_PREFIX."_link` AS t + FROM `".DB_PREFIX."_categoryrelation` AS cr, + `".DB_PREFIX."_link` AS t WHERE cr.linkid = t.id"; $queryStr .= " AND ".$this->_decideLinkTypeForQuery(); $queryStr .= " GROUP BY categoryid"; @@ -148,6 +155,7 @@ class Management { Summoner::sysLog("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); } + $this->_categories = $ret; return $ret; } @@ -156,11 +164,11 @@ class Management { * optional limit * optional stats * - * @param int $limit + * @param string $limit * @param bool $stats * @return array */ - public function tags(int $limit=0, bool $stats=false): array { + public function tags(string $limit="0", bool $stats=false): array { $ret = array(); $statsInfo = array(); @@ -217,10 +225,10 @@ class Management { /** * return the latest added links * - * @param int $limit + * @param string $limit * @return array */ - public function latestLinks(int $limit=5): array { + public function latestLinks(string $limit="5"): array { $ret = array(); $queryStr = "SELECT `title`, `link` FROM `".DB_PREFIX."_link` AS t"; @@ -249,18 +257,18 @@ class Management { * Slow but does the trick for now. If there is way more entries * re-think this solution * - * @param int $limit + * @param String $limit * @return array */ - public function randomLink(int $limit=1): array { + public function randomLink(string $limit="1"): array { $ret = array(); + $amount = $this->linkAmount(); + $offset = rand(0, $amount-1); + $queryStr = "SELECT `title`, `link`, `hash` FROM `".DB_PREFIX."_link` AS t"; $queryStr .= " WHERE ".$this->_decideLinkTypeForQuery(); - $queryStr .= " ORDER BY RAND()"; - if(!empty($limit)) { - $queryStr .= " LIMIT $limit"; - } + $queryStr .= " LIMIT $offset, $limit"; if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr)); @@ -279,17 +287,17 @@ class Management { /** * Get a random category * - * @param int $limit + * @param string $limit * @return array */ - public function randomCategory(int $limit=1): array { + public function randomCategory(string $limit="1"): array { $ret = array(); + $amount = $this->categoryAmount(); + $offset = rand(0, $amount-1); + $queryStr = "SELECT `id`, `name` FROM `".DB_PREFIX."_category`"; - $queryStr .= " ORDER BY RAND()"; - if(!empty($limit)) { - $queryStr .= " LIMIT $limit"; - } + $queryStr .= " LIMIT $offset, $limit"; if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr)); @@ -308,17 +316,17 @@ class Management { /** * Get a random tag * - * @param int $limit + * @param string $limit * @return array */ - public function randomTag(int $limit=1): array { + public function randomTag(string $limit="1"): array { $ret = array(); + $amount = $this->tagAmount(); + $offset = rand(0, $amount-1); + $queryStr = "SELECT `id`, `name` FROM `".DB_PREFIX."_tag`"; - $queryStr .= " ORDER BY RAND()"; - if(!empty($limit)) { - $queryStr .= " LIMIT $limit"; - } + $queryStr .= " LIMIT $offset, $limit"; if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr)); @@ -585,8 +593,8 @@ class Management { FROM `".DB_PREFIX."_combined` AS t"; $queryStr .= " WHERE ".$this->_decideLinkTypeForQuery(); $queryStr .= " AND t.categoryId = '" . $this->DB->real_escape_string($categoryid) . "' - ORDER BY t.created DESC - LIMIT 1"; + ORDER BY t.created DESC + LIMIT 1"; if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr)); @@ -612,7 +620,8 @@ class Management { $ret = array(); if(!empty($url)) { - $queryStr = "SELECT * FROM `".DB_PREFIX."_link` AS t"; + $queryStr = "SELECT `id`, `link`, `title`, `hash` + FROM `".DB_PREFIX."_link` AS t"; $queryStr .= " WHERE ".$this->_decideLinkTypeForQuery(); $queryStr .= " AND t.link = '".$this->DB->real_escape_string($url)."'"; @@ -641,7 +650,7 @@ class Management { $ret = array(); if(!empty($searchStr)) { - $queryStr = "SELECT *, + $queryStr = "SELECT `id`, `link`, `title`, `hash`, MATCH (`search`) AGAINST ('".$this->DB->real_escape_string($searchStr)."' IN BOOLEAN MODE) AS score FROM `".DB_PREFIX."_link` AS t WHERE MATCH (`search`) AGAINST ('".$this->DB->real_escape_string($searchStr)."' IN BOOLEAN MODE)"; @@ -666,9 +675,9 @@ class Management { /** * amount of links in the DB. Status 1 and 2 only * - * @return int + * @return string */ - public function linkAmount(): int { + public function linkAmount(): string { $ret = 0; $queryStr = "SELECT COUNT(*) AS amount @@ -694,9 +703,9 @@ class Management { /** * amount of tags * - * @return int + * @return string */ - public function tagAmount(): int { + public function tagAmount(): string { $ret = 0; $queryStr = "SELECT COUNT(*) AS amount FROM `".DB_PREFIX."_tag`"; @@ -719,9 +728,9 @@ class Management { /** * amount of categories * - * @return int + * @return string */ - public function categoryAmount(): int { + public function categoryAmount(): string { $ret = 0; $queryStr = "SELECT COUNT(*) AS amount FROM `".DB_PREFIX."_category`"; @@ -744,9 +753,9 @@ class Management { /** * Amount of links need moderation * - * @return int + * @return string */ - public function moderationAmount(): int { + public function moderationAmount(): string { $ret = 0; $queryStr = "SELECT COUNT(*) AS amount FROM `".DB_PREFIX."_link`"; diff --git a/webroot/lib/summoner.class.php b/webroot/lib/summoner.class.php index 0673fba..0822837 100644 --- a/webroot/lib/summoner.class.php +++ b/webroot/lib/summoner.class.php @@ -568,7 +568,7 @@ class Summoner { * @param string $input The string to be made more safe * @return string */ - static function cleanForLog($input): string { + static function cleanForLog(string $input): string { $input = var_export($input, true); $input = preg_replace( "/[\t\n\r]/", " ", $input); return addcslashes($input, "\000..\037\177..\377\\"); diff --git a/webroot/lib/tag.class.php b/webroot/lib/tag.class.php index d6a131f..c8b35c9 100644 --- a/webroot/lib/tag.class.php +++ b/webroot/lib/tag.class.php @@ -67,7 +67,7 @@ class Tag { * @param bool $doNotCreate * @return int 0=fail, 1=existing, 2=new, 3=newNotCreated */ - public function initbystring(string $string, $doNotCreate=false): int { + public function initbystring(string $string, bool $doNotCreate=false): int { $ret = 0; $this->_id = false; if(!empty($string)) { @@ -100,7 +100,7 @@ class Tag { } } else { - $ret=3; + $ret = 3; } } } catch (Exception $e) { @@ -113,10 +113,10 @@ class Tag { /** * by given DB table id load all the info we need * - * @param int $id - * @return int + * @param string $id + * @return string */ - public function initbyid(int $id): int { + public function initbyid(string $id): string { $this->_id = 0; if(!empty($id)) { @@ -159,10 +159,10 @@ class Tag { /** * set the relation to the given link to the loaded tag * - * @param int $linkid + * @param string $linkid * @return void */ - public function setRelation(int $linkid): void { + public function setRelation(string $linkid): void { if(!empty($linkid) && !empty($this->_id)) { $queryStr = "INSERT IGNORE INTO `".DB_PREFIX."_tagrelation` SET `linkid` = '".$this->DB->real_escape_string($linkid)."', diff --git a/webroot/view/home.inc.php b/webroot/view/home.inc.php index 6b4bded..c91c8c4 100644 --- a/webroot/view/home.inc.php +++ b/webroot/view/home.inc.php @@ -129,8 +129,6 @@ if(isset($_POST['data']) && !empty($_POST['data']) && isset($_POST['addnewone']) if($isUrl === true && !empty($formData['title'])) { $hash = md5($formData['url']); - - $DB->begin_transaction(MYSQLI_TRANS_START_READ_WRITE); $linkObj = new Link($DB); @@ -186,7 +184,9 @@ if(isset($_POST['data']) && !empty($_POST['data']) && isset($_POST['addnewone']) } } -$existingCategories = $Management->categories(); -$existingTags = $Management->tags(); +if($showAddForm === true) { + $existingCategories = $Management->categories(); + $existingTags = $Management->tags(); +} $latestLinks = $Management->latestLinks(20); $orderedCategories = $Management->categoriesByDateAdded(); -- 2.39.5