From aab9126d3e24935ae87bc50b5abf6e3b8064241b Mon Sep 17 00:00:00 2001 From: Banana Date: Mon, 4 Jan 2021 11:39:32 +0100 Subject: [PATCH] simplified tag overview and moved to the trite class --- documentation/setup/bibliotheca.sql | 4 +- webclient/lib/managecollections.class.php | 2 + webclient/lib/mancubus.class.php | 144 ------------------ webclient/lib/trite.class.php | 45 ++++++ .../view/default/manageentry/manageentry.html | 2 +- .../view/default/managetags/managetags.html | 15 ++ webclient/view/default/tags/tags.php | 7 +- 7 files changed, 70 insertions(+), 149 deletions(-) diff --git a/documentation/setup/bibliotheca.sql b/documentation/setup/bibliotheca.sql index 750e442..6bd188b 100644 --- a/documentation/setup/bibliotheca.sql +++ b/documentation/setup/bibliotheca.sql @@ -141,13 +141,13 @@ INSERT INTO `#REPLACEME#_sys_fields` (`id`, `identifier`, `displayname`, `type`, (6, 'category', 'Category', 'lookupmultiple', 'tag', NULL, NULL, 'string 64', '2019-09-01 19:11:18', 0, 1, 1, 'rw-r--r--'), (7, 'publisher', 'Publisher', 'lookupmultiple', 'tag', NULL, NULL, 'string 64', '2019-09-01 19:17:51', 0, 1, 1, 'rw-r--r--'), (8, 'developer', 'Developer', 'lookupmultiple', 'tag', NULL, NULL, 'string 64', '2019-09-01 19:17:51', 0, 1, 1, 'rw-r--r--'), -(9, 'platform', 'Platform', 'selection', 'tag', '`platform` varchar(32) NULL DEFAULT NULL', 'PC,Xbox,Playstation,Nintendo,Nintendo Switch', 'One of PC,Xbox,Playstation,Nintendo,Nintendo Switch', '2019-09-01 19:18:33', 0, 1, 1, 'rw-r--r--'), +(9, 'platform', 'Platform', 'selection', 'entrySingleText', '`platform` varchar(32) NULL DEFAULT NULL', 'PC,Xbox,Playstation,Nintendo,Nintendo Switch', 'One of PC,Xbox,Playstation,Nintendo,Nintendo Switch', '2019-09-01 19:18:33', 0, 1, 1, 'rw-r--r--'), (10, 'storage', 'Storage', 'lookupmultiple', 'tag', NULL, NULL, 'string 64', '2019-09-01 19:18:33', 0, 1, 1, 'rw-r--r--'), (13, 'rating', 'Rating', 'selection', 'entrySingleText', '`rating` varchar(16) NULL DEFAULT NULL', '0/10,2/10,3/10,4/10,5/10,6/10,7/10,8/10,9/10,10/10', 'One of 0/10,2/10,3/10,4/10,5/10,6/10,7/10,8/10,9/10,10/10', '2019-09-01 19:25:35', 0, 1, 1, 'rw-r--r--'), (14, 'year', 'Year', 'number', 'entrySingleNum', '`year` int(10) NULL, ADD INDEX (`year`)', NULL, 'int 10', '2019-09-01 19:30:11', 0, 1, 1, 'rw-r--r--'), (15, 'coverimage', 'Cover image', 'upload', NULL, NULL, NULL, 'One file in $_FILES[uploads] of post', '2019-09-01 19:48:44', 0, 1, 1, 'rw-r--r--'), (16, 'attachment', 'Attachments', 'upload_multiple', NULL, NULL, NULL, 'Multiple in $_FILES[uploads] of post', '2019-09-01 19:48:44', 0, 1, 1, 'rw-r--r--'), -(17, 'os', 'Operating system and version', 'selection', 'tag', '`os` varchar(32) NULL DEFAULT NULL', 'Windows 10,Windows 95, Windows 99,Windows XP, Windows ME, Windows 2000,Windows Vista,Windows 8', 'One of Windows 10,Windows 95, Windows 99,Windows XP, Windows ME, Windows 2000,Windows Vista,Windows 8', '2019-09-01 19:55:13', 0, 1, 1, 'rw-r--r--'), +(17, 'os', 'Operating system and version', 'selection', 'entrySingleNum', '`os` varchar(32) NULL DEFAULT NULL', 'Windows 10,Windows 95, Windows 99,Windows XP, Windows ME, Windows 2000,Windows Vista,Windows 8', 'One of Windows 10,Windows 95, Windows 99,Windows XP, Windows ME, Windows 2000,Windows Vista,Windows 8', '2019-09-01 19:55:13', 0, 1, 1, 'rw-r--r--'), (18, 'actors', 'Actors', 'lookupmultiple', 'tag', NULL, NULL, 'string 64', '2020-07-26 07:12:48', NULL, 1, 1, 'rw-r--r--'), (19, 'countries', 'Countries', 'lookupmultiple', 'tag', NULL, NULL, 'string 64', '2020-07-26 07:16:08', NULL, 1, 1, 'rw-r--r--'), (20, 'directors', 'Directors', 'lookupmultiple', 'tag', NULL, NULL, 'string 64', '2020-07-26 07:17:59', NULL, 1, 1, 'rw-r--r--'), diff --git a/webclient/lib/managecollections.class.php b/webclient/lib/managecollections.class.php index 410900f..91d0928 100644 --- a/webclient/lib/managecollections.class.php +++ b/webclient/lib/managecollections.class.php @@ -221,6 +221,8 @@ class ManageCollections { `fk_field` int NOT NULL, `fk_entry` int NOT NULL, `value` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + KEY `fk_entry` (`fk_entry`), + KEY `fk_field` (`fk_field`), FULLTEXT KEY `value` (`value`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"; if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryEntry2lookup,true)); diff --git a/webclient/lib/mancubus.class.php b/webclient/lib/mancubus.class.php index c2b7a10..67eac8b 100644 --- a/webclient/lib/mancubus.class.php +++ b/webclient/lib/mancubus.class.php @@ -400,46 +400,6 @@ class Mancubus { return $ret; } - /** - * Get tags for loaded collection. Provide search term to use match against db search - * - * @todo Replace with trite class - * - * @param mixed $search Search term - * @return array - */ - public function getTags($search=false) { - $ret = array(); - - if(!empty($this->_collectionId)) { - $queryStr = "SELECT `cf`.`fk_field_id` AS id, `sf`.`type`, `sf`.`displayname`, `sf`.`identifier` - FROM `".DB_PREFIX."_collection_fields_".$this->_DB->real_escape_string($this->_collectionId)."` AS cf - LEFT JOIN `".DB_PREFIX."_sys_fields` AS sf ON `cf`.`fk_field_id` = `sf`.`id` - WHERE `sf`.`searchtype` = 'tag' - ORDER BY `sf`.`displayname`"; - if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); - try { - $query = $this->_DB->query($queryStr); - if($query !== false && $query->num_rows > 0) { - while(($result = $query->fetch_assoc()) != false) { - $ret[$result['id']] = $result; - $ret[$result['id']]['entries'] = array(); - - $_mn = '_loadTagDistinct_'.$result['type']; - if(method_exists($this, $_mn)) { - $ret[$result['id']]['entries'] = $this->$_mn($result,$search); - } - } - } - } - catch (Exception $e) { - error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); - } - } - - return $ret; - } - /** * Return the storage info for loaded collection * Used by API @@ -627,110 +587,6 @@ class Mancubus { return $ret; } - /** - * Load the selection as tag. - * Search is a 1:1 match - * - * @param array $fieldData The sys field data - * @param mixed $search Searchterm - * @return array - */ - private function _loadTagDistinct_selection($fieldData,$search=false) { - return $this->_loadColAsTagFromEntryTable($fieldData['identifier'],$search); - } - - /** - * Load the data for lookupmultiple field. Provide field id and optional searchterm. - * Uses currently loaded collection - * - * @param array $fieldData The field data to use - * @param mixed $search Searchterm to run a match against DB search - * @return array - */ - private function _loadTagDistinct_lookupmultiple($fieldData,$search=false) { - $ret = array(); - - if(!empty($fieldData) && !empty($this->_collectionId)) { - $queryStr = "SELECT DISTINCT(`value`) - FROM `".DB_PREFIX."_collection_entry2lookup_".$this->_DB->real_escape_string($this->_collectionId)."` - WHERE `fk_field` = '".$this->_DB->real_escape_string($fieldData['id'])."'"; - if(!empty($search)) { - $queryStr .= " AND MATCH (`value`) AGAINST ('" . $this->_DB->real_escape_string($search) . "' IN BOOLEAN MODE)"; - } - - if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); - try { - $query = $this->_DB->query($queryStr); - if ($query !== false && $query->num_rows > 0) { - while (($result = $query->fetch_assoc()) != false) { - $ret[] = $result['value']; - } - } - } - catch (Exception $e) { - error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); - - } - } - - return $ret; - } - - /** - * Load the data from lookupmultiple field. In this case $fieldata is overwritten - * and year is used. - * - * @param array $fieldData - * @param bool $search - * @return array - */ - private function _loadTagDistinct_year($fieldData,$search=false) { - return $this->_loadColAsTagFromEntryTable("year",$search); - } - - /** - * Get the distinct data from a col and optional search term - * - * @param string $colname - * @param string $search - * @return array - */ - private function _loadColAsTagFromEntryTable($colname,$search) { - $ret = array(); - - if(!empty($colname) && !empty($this->_collectionId)) { - if(!empty($search)) { - $queryStr = "SELECT `".$this->_DB->real_escape_string($colname)."` - FROM `".DB_PREFIX."_collection_entry_".$this->_DB->real_escape_string($this->_collectionId)."`"; - $queryStr .= " WHERE `".$colname."` = '".$this->_DB->real_escape_string($search)."'"; - } - else { - $queryStr = "SELECT DISTINCT(`".$this->_DB->real_escape_string($colname)."`) - FROM `".DB_PREFIX."_collection_entry_".$this->_DB->real_escape_string($this->_collectionId)."`"; - } - - $queryStr .= " ORDER BY `".$this->_DB->real_escape_string($colname)."` DESC"; - - if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); - try { - $query = $this->_DB->query($queryStr); - if($query !== false && $query->num_rows > 0) { - while(($result = $query->fetch_assoc()) != false) { - if(!empty($result[$colname])) { - $ret[] = $result[$colname]; - } - } - } - } - catch (Exception $e) { - error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); - } - } - - - return $ret; - } - /** * set some defaults by init of the class * diff --git a/webclient/lib/trite.class.php b/webclient/lib/trite.class.php index 31f3046..a61af9b 100644 --- a/webclient/lib/trite.class.php +++ b/webclient/lib/trite.class.php @@ -210,6 +210,51 @@ class Trite { return $ret; } + /** + * Get the tag fields (searchtype = tag) and their values. + * Possible optimization can be done here: Do not load everything at once, but per field + * Needs also change in frontend to separate those calls + * + * @param string $search String value to search value against + * @return array + */ + public function getTags($search='') { + $ret = array(); + + $queryStr = "SELECT `cf`.`fk_field_id` AS id, + `sf`.`type`, + `sf`.`displayname`, + `sf`.`identifier`, + `e2l`.`value` + FROM `".DB_PREFIX."_collection_fields_".$this->_DB->real_escape_string($this->_id)."` AS cf + LEFT JOIN `".DB_PREFIX."_sys_fields` AS sf ON `cf`.`fk_field_id` = `sf`.`id` + LEFT JOIN `".DB_PREFIX."_collection_entry2lookup_".$this->_DB->real_escape_string($this->_id)."` AS e2l ON `e2l`.`fk_field` = `sf`.`id` + WHERE `sf`.`searchtype` = 'tag'"; + if(!empty($search)) { + $queryStr .= " AND MATCH (`e2l`.`value`) AGAINST ('".$this->_DB->real_escape_string($search)."' IN BOOLEAN MODE)"; + } + else { + $queryStr .= " ORDER BY `sf`.`displayname`, `e2l`.`value`"; + } + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); + $query = $this->_DB->query($queryStr); + try { + if($query !== false && $query->num_rows > 0) { + while(($result = $query->fetch_assoc()) != false) { + $ret[$result['id']]['id'] = $result['id']; + $ret[$result['id']]['displayname'] = $result['displayname']; + $ret[$result['id']]['identifier'] = $result['identifier']; + $ret[$result['id']]['type'] = $result['type']; + $ret[$result['id']]['entries'][$result['value']] = $result['value']; + } + } + } catch (Exception $e) { + error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); + } + + return $ret; + } + /** * set some defaults by init of the class * diff --git a/webclient/view/default/manageentry/manageentry.html b/webclient/view/default/manageentry/manageentry.html index 8fef73c..f57e59d 100644 --- a/webclient/view/default/manageentry/manageentry.html +++ b/webclient/view/default/manageentry/manageentry.html @@ -6,7 +6,7 @@ if(!empty($TemplateData['editFields'])) { ?>

Update an entry in:

- View entry + View entry

Add an entry to:

diff --git a/webclient/view/default/managetags/managetags.html b/webclient/view/default/managetags/managetags.html index e69de29..a97a961 100644 --- a/webclient/view/default/managetags/managetags.html +++ b/webclient/view/default/managetags/managetags.html @@ -0,0 +1,15 @@ + + + +

Collection overview

+
+
+
+ $v) { ?> +
+
+ +
+
+
+ diff --git a/webclient/view/default/tags/tags.php b/webclient/view/default/tags/tags.php index 7db18b1..d8df8b3 100644 --- a/webclient/view/default/tags/tags.php +++ b/webclient/view/default/tags/tags.php @@ -2,7 +2,7 @@ /** * Bibliotheca webclient * - * Copyright 2018-2020 Johannes Keßler + * Copyright 2018-2021 Johannes Keßler * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. @@ -49,10 +49,13 @@ if(!empty($_collection)) { if(!empty($TemplateData['loadedCollection'])) { $TemplateData['searchAction'] = 'index.php?p=tags&collection='.$Trite->param('id'); $Mancubus->setCollection($Trite->param('id')); - $TemplateData['tags'] = $Mancubus->getTags($_search); + //$TemplateData['tags'] = $Mancubus->getTags($_search); + $TemplateData['tags'] = $Trite->getTags($_search); if(!empty($_search)) { $TemplateData['search'] = $_search; } + + $Trite->getTags(); } else { $TemplateData['message']['content'] = "Can not load given collection."; -- 2.39.5