From f0f5e41fc5ad416b2bec03fd6705465d2f163eea Mon Sep 17 00:00:00 2001 From: Banana Date: Tue, 10 Aug 2021 11:02:36 +0200 Subject: [PATCH] stats and some other code cleanup --- CHANGELOG | 3 +- TODO | 1 - documentation/requirements.txt | 3 +- documentation/setup/bibliotheca.sql | 4 +- documentation/setup/install.txt | 3 + sources/updatecheck.txt | 7 +- upgrade/from-version-1.2.txt | 1 + webclient/index.php | 2 + webclient/lib/doomguy.class.php | 2 +- webclient/lib/managecollections.class.php | 35 +------- webclient/lib/summoner.class.php | 30 +++++++ webclient/lib/trite.class.php | 88 ++++++++++++++++++--- webclient/setup/bibliotheca.sql.default | 4 +- webclient/setup/index.php | 11 ++- webclient/view/default/sysinfo/sysinfo.html | 32 ++++++++ webclient/view/default/sysinfo/sysinfo.php | 51 ++++++++++++ webclient/view/default/ui/css/style.css | 4 + 17 files changed, 225 insertions(+), 56 deletions(-) create mode 100644 webclient/view/default/sysinfo/sysinfo.html create mode 100644 webclient/view/default/sysinfo/sysinfo.php diff --git a/CHANGELOG b/CHANGELOG index 2acd30c..41abb56 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -1,7 +1,8 @@ 1.x - Rrajigar Mine * Fixed install sql file. * New field: artists - lookupmultiple field - * Added setup file. See setup/install.txt for more details + * Added setup process. See setup/install.txt for more details + * Stats info page 1.2 - NyLeve's Falls 20210717 * Updated requirements information diff --git a/TODO b/TODO index 42d468e..fc3c759 100644 --- a/TODO +++ b/TODO @@ -1,5 +1,4 @@ * Automatic upgrades of DB -* stats overview page. amount of entries. file / cache and db storage. Version info and where to find it. * Definition of fields in "card view" * User and groupmanagement: Check where a user or group is used! * Better error handling and display while adding / update and delete diff --git a/documentation/requirements.txt b/documentation/requirements.txt index c39ad59..eff02e7 100644 --- a/documentation/requirements.txt +++ b/documentation/requirements.txt @@ -1,5 +1,6 @@ * A Webserver (tested on Apache 2.4) * PHP 7+ with mysqli, mbstring, curl * MySQL 8+; MariaDB 10.2/MySQL 8 +-- User with full rights on the Database. Including lock tables * At least 20MB webserver storage WITHOUT images -* At least 100MB DB storage. +* At least 20MB DB storage. diff --git a/documentation/setup/bibliotheca.sql b/documentation/setup/bibliotheca.sql index fe12c78..fa1241d 100644 --- a/documentation/setup/bibliotheca.sql +++ b/documentation/setup/bibliotheca.sql @@ -98,7 +98,7 @@ CREATE TABLE `#REPLACEME#_menu` ( `position` int NOT NULL DEFAULT '0', `category` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -107,7 +107,7 @@ CREATE TABLE `#REPLACEME#_menu` ( LOCK TABLES `#REPLACEME#_menu` WRITE; /*!40000 ALTER TABLE `#REPLACEME#_menu` DISABLE KEYS */; -INSERT INTO `#REPLACEME#_menu` VALUES (1,'Dashboard','','home',1,1,'rw-r--r--',0,'show'),(2,'Collections','collections','database',1,1,'rw-r--r--',1,'show'),(3,'Tags','tags','tag',1,1,'rw-r--r--',2,'show'),(4,'Add','manageentry','plus-circle',1,2,'rw-rw----',0,'manage'),(6,'Tags','managetags','tag',1,2,'rw-rw----',2,'manage'),(7,'Collections','managecolletions','database',1,2,'rw-rw----',3,'manage'),(8,'Users','manageusers','users',1,1,'rw-------',4,'manage'),(9,'Login','auth','',1,1,'rw-r--r--',0,''),(10,'Collection fields','managecollectionfields','',1,2,'rw-rw----',0,''),(11,'Entry','entry','',1,1,'rw-r--r--',0,''),(12,'Search','search','',1,1,'rw-r--r--',0,''),(14,'Tool','tool','',1,2,'rw-rw----',0,''),(15,'Advanced search','advancedsearch','',1,1,'rw-r--r--',0,''),(16,'Profile','profile','user',1,2,'rw-rw----',6,'manage'),(17,'Groups','managegroups','users',1,1,'rw-------',5,'manage'),(18,'Bulkedit','bulkedit','',1,2,'rw-rw----',0,''); +INSERT INTO `#REPLACEME#_menu` VALUES (1,'Dashboard','','home',1,1,'rw-r--r--',0,'show'),(2,'Collections','collections','database',1,1,'rw-r--r--',1,'show'),(3,'Tags','tags','tag',1,1,'rw-r--r--',2,'show'),(4,'Add','manageentry','plus-circle',1,2,'rw-rw----',0,'manage'),(6,'Tags','managetags','tag',1,2,'rw-rw----',2,'manage'),(7,'Collections','managecolletions','database',1,2,'rw-rw----',3,'manage'),(8,'Users','manageusers','users',1,1,'rw-------',4,'manage'),(9,'Login','auth','',1,1,'rw-r--r--',0,''),(10,'Collection fields','managecollectionfields','',1,2,'rw-rw----',0,''),(11,'Entry','entry','',1,1,'rw-r--r--',0,''),(12,'Search','search','',1,1,'rw-r--r--',0,''),(14,'Tool','tool','',1,2,'rw-rw----',0,''),(15,'Advanced search','advancedsearch','',1,1,'rw-r--r--',0,''),(16,'Profile','profile','user',1,2,'rw-rw----',6,'manage'),(17,'Groups','managegroups','users',1,1,'rw-------',5,'manage'),(18,'Bulkedit','bulkedit','',1,2,'rw-rw----',0,''),(19,'System Information','sysinfo','info',1,1,'rw-------',3,'show'); /*!40000 ALTER TABLE `#REPLACEME#_menu` ENABLE KEYS */; UNLOCK TABLES; diff --git a/documentation/setup/install.txt b/documentation/setup/install.txt index 1bfa447..e1be19b 100644 --- a/documentation/setup/install.txt +++ b/documentation/setup/install.txt @@ -26,3 +26,6 @@ Default admin user pw: test Login with default admin account and change the password! Create your own user. Create your first collection. + +To re run the setup: +Upload the setup folder again. It deletes itself after a successfull setup diff --git a/sources/updatecheck.txt b/sources/updatecheck.txt index 02964ff..ee3e62a 100644 --- a/sources/updatecheck.txt +++ b/sources/updatecheck.txt @@ -2,10 +2,13 @@ setup - sql file mit mysqldump - config files in setup.php - options in setup.php +- sql file in setup documentation version, readme and changelog -upgrade file +upgrade file with correct placeholders -correct placeholders +correct placeholders in sql file for manual setup + +correct version info in VERSION and index.php diff --git a/upgrade/from-version-1.2.txt b/upgrade/from-version-1.2.txt index c35efeb..6f6c0c5 100644 --- a/upgrade/from-version-1.2.txt +++ b/upgrade/from-version-1.2.txt @@ -1,3 +1,4 @@ # DB changes. Run each line against your bibliotheca DB. # Replace #REPLACEME# with your table prefix. Default is bib INSERT INTO `#REPLACEME#_sys_fields` (`id`, `identifier`, `displayname`, `type`, `searchtype`, `createstring`, `inputValidation`, `value`, `apiinfo`, `created`, `modified`, `modificationuser`, `owner`, `group`, `rights`) VALUES (NULL, 'artists', 'Artists', 'lookupmultiple', 'tag', NULL, 'allowSpace', NULL, 'string 64', NOW(), NOW(), NULL, '1', '1', 'rw-r--r--'); +INSERT INTO `#REPLACEME#_menu` (`id`, `text`, `action`, `icon`, `owner`, `group`, `rights`, `position`, `category`) VALUES (NULL, 'System Information', 'sysinfo', 'info', '1', '1', 'rw-------', '3', 'show'); diff --git a/webclient/index.php b/webclient/index.php index d379624..65be066 100644 --- a/webclient/index.php +++ b/webclient/index.php @@ -18,6 +18,8 @@ require_once './config/config.php'; +define('BIB_VERSION','1.x - Rrajigar Mine ()'); + mb_http_output('UTF-8'); mb_internal_encoding('UTF-8'); ini_set('error_reporting',-1); // E_ALL & E_STRICT diff --git a/webclient/lib/doomguy.class.php b/webclient/lib/doomguy.class.php index 3058afd..3943281 100644 --- a/webclient/lib/doomguy.class.php +++ b/webclient/lib/doomguy.class.php @@ -251,7 +251,7 @@ class Doomguy { * @param bool $tableName * @return string */ - public function getSQLRightsString($mode = "read", $tableName=false) { + public function getSQLRightsString($mode = "read", $tableName=false): string { $str = ''; $prefix = ''; diff --git a/webclient/lib/managecollections.class.php b/webclient/lib/managecollections.class.php index 324153d..023c238 100644 --- a/webclient/lib/managecollections.class.php +++ b/webclient/lib/managecollections.class.php @@ -40,47 +40,17 @@ class ManageCollections { * @param mysqli $databaseConnectionObject * @param Doomguy $userObj */ - public function __construct($databaseConnectionObject, $userObj) { + public function __construct(mysqli $databaseConnectionObject, Doomguy $userObj) { $this->_DB = $databaseConnectionObject; $this->_User = $userObj; } - /** - * Load collection info from table. Checks user rights - * - * @param string $id - * @param string $rightsMode - * @return array - */ - public function getCollection($id,$rightsMode="read") { - $ret = array(); - - if (Summoner::validate($id, 'digit')) { - $queryStr = "SELECT `c`.`id`, `c`.`name`, `c`.`description`, `c`.`created` - FROM `".DB_PREFIX."_collection` AS c - WHERE ".$this->_User->getSQLRightsString($rightsMode, "c")." - AND `c`.`id` = '".$this->_DB->real_escape_string($id)."'"; - 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) { - $ret = $query->fetch_assoc(); - } - } - catch (Exception $e) { - error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); - } - } - - return $ret; - } - /** * Get all available collections for display based on current user * * @return array */ - public function getCollections() { + public function getCollections(): array{ $ret = array(); $queryStr = "SELECT `c`.`id`, `c`.`name`, `c`.`description`, `c`.`created`, @@ -94,7 +64,6 @@ class ManageCollections { 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; diff --git a/webclient/lib/summoner.class.php b/webclient/lib/summoner.class.php index ea684db..b3aed8c 100644 --- a/webclient/lib/summoner.class.php +++ b/webclient/lib/summoner.class.php @@ -684,4 +684,34 @@ class Summoner { return $ret; } + + /** + * Size of the folder and the data within in bytes + * + * @param string $dir + * @return int + */ + static function folderSize(string $dir): int { + $size = 0; + + foreach (glob(rtrim($dir, '/').'/*', GLOB_NOSORT) as $each) { + $size += is_file($each) ? filesize($each) : self::folderSize($each); + } + + return $size; + } + + /** + * Given bytes to human format with unit + * + * @param int $bytes + * @return string + */ + static function bytesToHuman(int $bytes): string { + $units = ['B', 'KB', 'MB', 'GB', 'TB', 'PB']; + for ($i = 0; $bytes > 1024; $i++) { + $bytes /= 1024; + } + return round($bytes, 2) . ' ' . $units[$i]; + } } diff --git a/webclient/lib/trite.class.php b/webclient/lib/trite.class.php index 9538730..dc65c38 100644 --- a/webclient/lib/trite.class.php +++ b/webclient/lib/trite.class.php @@ -104,7 +104,7 @@ class Trite { } /** - * Get information to display for current collection + * Get information to display for given collection * based on current user and given rights * * @param string $id The collection ID to load @@ -145,10 +145,10 @@ class Trite { * get the value of the specified param from the collection data array * * @param string $param - * @return bool|mixed + * @return string */ - public function param($param) { - $ret = false; + public function param(string $param): string { + $ret = ''; $param = trim($param); @@ -163,9 +163,10 @@ class Trite { * Get all available collections for display based on current user * and read mode * + * @param string $rightsMode * @return array */ - public function getCollections($rightsMode="read") { + public function getCollections($rightsMode="read"): array { $ret = array(); $queryStr = "SELECT `c`.`id`, `c`.`name`, `c`.`description` @@ -264,7 +265,7 @@ class Trite { * @param string $search String value to search value against * @return array */ - public function getTags($search='') { + public function getTags($search=''): array { $ret = array(); $queryStr = "SELECT `cf`.`fk_field_id` AS id, @@ -311,7 +312,7 @@ class Trite { * * @return array */ - public function getAvailableTools() { + public function getAvailableTools(): array { $ret = array(); $queryStr = "SELECT `t`.`id`, `t`.`name`, `t`.`description`, `t`.`action`, `t`.`target` @@ -334,6 +335,64 @@ class Trite { return $ret; } + /** + * Some statistics about the current collection. + * Entries, tags, storage + * Adds a stats array to _collectionData + * + * @return array + */ + public function getStats(): array { + if(empty($this->_id)) return array(); + + $this->_collectionData['stats'] = array(); + + $queryStr = "SELECT COUNT(*) AS entries FROM `".DB_PREFIX."_collection_entry_".$this->_id."`"; + 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) { + $result = $query->fetch_assoc(); + $this->_collectionData['stats']['entriesCount'] = $result['entries']; + } + } + catch (Exception $e) { + error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); + } + + $tags = $this->getTags(); + $tagsCount = 0; + foreach ($tags as $k=>$v) { + $tagsCount += count($v['entries']); + } + $this->_collectionData['stats']['tagsCount'] = $tagsCount; + + $tableSize = 0; // in MB + $queryStr = "SELECT (DATA_LENGTH + INDEX_LENGTH) AS `size` + FROM information_schema.TABLES + WHERE TABLE_SCHEMA = 'bibliotheca' + AND TABLE_NAME LIKE 'bib_collection_%_".$this->_id."' + ORDER BY (DATA_LENGTH + INDEX_LENGTH) 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) { + $tableSize += $result['size']; + } + } + } + catch (Exception $e) { + error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); + } + $this->_collectionData['stats']['tableSize'] = Summoner::bytesToHuman($tableSize); + + $this->_collectionData['stats']['storageSize'] = Summoner::bytesToHuman(Summoner::folderSize(PATH_STORAGE.'/'.$this->_id)); + + + return $this->_collectionData; + } + /** * set some defaults by init of the class * @@ -351,15 +410,20 @@ class Trite { /** * Make a key=>value array of a comma seperated string and use the value as key * - * @param array $data + * @param string $data * @return array */ - private function _loadAdvancedSearchTableFields($data) { + private function _loadAdvancedSearchTableFields(string $data): array { $ret = array(); - $_t = explode(',',$data); - foreach($_t as $e) { - $ret[$e] = $e; + if(!strstr($data, ',')) { + $ret[$data] = $data; + } + else { + $_t = explode(',',$data); + foreach($_t as $e) { + $ret[$e] = $e; + } } return $ret; diff --git a/webclient/setup/bibliotheca.sql.default b/webclient/setup/bibliotheca.sql.default index fe12c78..fa1241d 100644 --- a/webclient/setup/bibliotheca.sql.default +++ b/webclient/setup/bibliotheca.sql.default @@ -98,7 +98,7 @@ CREATE TABLE `#REPLACEME#_menu` ( `position` int NOT NULL DEFAULT '0', `category` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -107,7 +107,7 @@ CREATE TABLE `#REPLACEME#_menu` ( LOCK TABLES `#REPLACEME#_menu` WRITE; /*!40000 ALTER TABLE `#REPLACEME#_menu` DISABLE KEYS */; -INSERT INTO `#REPLACEME#_menu` VALUES (1,'Dashboard','','home',1,1,'rw-r--r--',0,'show'),(2,'Collections','collections','database',1,1,'rw-r--r--',1,'show'),(3,'Tags','tags','tag',1,1,'rw-r--r--',2,'show'),(4,'Add','manageentry','plus-circle',1,2,'rw-rw----',0,'manage'),(6,'Tags','managetags','tag',1,2,'rw-rw----',2,'manage'),(7,'Collections','managecolletions','database',1,2,'rw-rw----',3,'manage'),(8,'Users','manageusers','users',1,1,'rw-------',4,'manage'),(9,'Login','auth','',1,1,'rw-r--r--',0,''),(10,'Collection fields','managecollectionfields','',1,2,'rw-rw----',0,''),(11,'Entry','entry','',1,1,'rw-r--r--',0,''),(12,'Search','search','',1,1,'rw-r--r--',0,''),(14,'Tool','tool','',1,2,'rw-rw----',0,''),(15,'Advanced search','advancedsearch','',1,1,'rw-r--r--',0,''),(16,'Profile','profile','user',1,2,'rw-rw----',6,'manage'),(17,'Groups','managegroups','users',1,1,'rw-------',5,'manage'),(18,'Bulkedit','bulkedit','',1,2,'rw-rw----',0,''); +INSERT INTO `#REPLACEME#_menu` VALUES (1,'Dashboard','','home',1,1,'rw-r--r--',0,'show'),(2,'Collections','collections','database',1,1,'rw-r--r--',1,'show'),(3,'Tags','tags','tag',1,1,'rw-r--r--',2,'show'),(4,'Add','manageentry','plus-circle',1,2,'rw-rw----',0,'manage'),(6,'Tags','managetags','tag',1,2,'rw-rw----',2,'manage'),(7,'Collections','managecolletions','database',1,2,'rw-rw----',3,'manage'),(8,'Users','manageusers','users',1,1,'rw-------',4,'manage'),(9,'Login','auth','',1,1,'rw-r--r--',0,''),(10,'Collection fields','managecollectionfields','',1,2,'rw-rw----',0,''),(11,'Entry','entry','',1,1,'rw-r--r--',0,''),(12,'Search','search','',1,1,'rw-r--r--',0,''),(14,'Tool','tool','',1,2,'rw-rw----',0,''),(15,'Advanced search','advancedsearch','',1,1,'rw-r--r--',0,''),(16,'Profile','profile','user',1,2,'rw-rw----',6,'manage'),(17,'Groups','managegroups','users',1,1,'rw-------',5,'manage'),(18,'Bulkedit','bulkedit','',1,2,'rw-rw----',0,''),(19,'System Information','sysinfo','info',1,1,'rw-------',3,'show'); /*!40000 ALTER TABLE `#REPLACEME#_menu` ENABLE KEYS */; UNLOCK TABLES; diff --git a/webclient/setup/index.php b/webclient/setup/index.php index 51d19c2..ae360d8 100644 --- a/webclient/setup/index.php +++ b/webclient/setup/index.php @@ -16,6 +16,13 @@ * limitations under the License. */ +/** + * A plain and simple setup for bibliotheca + * It creates the config file based on the .default file. + * It creates the default database tables. + * It self deletes after a setup is complete to reduce some sercuirty risks + */ + mb_http_output('UTF-8'); mb_internal_encoding('UTF-8'); ini_set('error_reporting',-1); // E_ALL & E_STRICT @@ -41,6 +48,9 @@ if(!is_dir('../storage') || !is_writeable('../storage')) { if(!is_dir('../systemout') || !is_writeable('../systemout')) { die('Missing correct write permissions for ../systemout dir'); } +if(!is_writeable(getcwd())) { + die('Missing correct write/delete permissions for the setup folder'); +} $configStep = 'ready'; $configFile = '../config/config.php'; @@ -164,7 +174,6 @@ switch($configStep) { $DB->query("SET collation_connection = 'utf8mb4_unicode_ci'"); } - if(!empty($_conCheck)) { try { $query = $DB->query("SELECT * FROM information_schema.tables WHERE table_schema = '".DB_NAME."' AND table_name = '".DB_PREFIX."_sys_fields' LIMIT 1"); diff --git a/webclient/view/default/sysinfo/sysinfo.html b/webclient/view/default/sysinfo/sysinfo.html new file mode 100644 index 0000000..873b338 --- /dev/null +++ b/webclient/view/default/sysinfo/sysinfo.html @@ -0,0 +1,32 @@ +

System information

+
+
+
+ $v) { ?> +
+

+
+ +

+

+ Created:
+ Entries:
+ Tags:
+ DB Usage:
+ Disk Usage: +

+
+ +
+
+
+
    +
  • Bibliotheca:
    • +
  • Apache (depends on ServerTokens setting):
    • +
  • PHP:
    • +
  • MySQL server:
    • +
  • MySQL data usage: MB
    • +
  • Storage usage:
    • +
+
+
diff --git a/webclient/view/default/sysinfo/sysinfo.php b/webclient/view/default/sysinfo/sysinfo.php new file mode 100644 index 0000000..6b479bd --- /dev/null +++ b/webclient/view/default/sysinfo/sysinfo.php @@ -0,0 +1,51 @@ +query($queryStr); + if($query !== false && $query->num_rows > 0) { + while(($result = $query->fetch_assoc()) != false) { + $overallTableSize += $result['size']; + } + } +} +catch (Exception $e) { + error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); +} +$TemplateData['overallTableSize'] = Summoner::bytesToHuman($overallTableSize); + +$TemplateData['overallStorageSize'] = Summoner::bytesToHuman(Summoner::folderSize(PATH_STORAGE)); + +require_once 'lib/trite.class.php'; +$Trite = new Trite($DB,$Doomguy); +$TemplateData['existingCollections'] = $Trite->getCollections("write"); +foreach($TemplateData['existingCollections'] as $k=>$v) { + $Trite->load($k); + $TemplateData['existingCollections'][$k] = $Trite->getStats(); +} diff --git a/webclient/view/default/ui/css/style.css b/webclient/view/default/ui/css/style.css index 4ac5c05..94b3083 100644 --- a/webclient/view/default/ui/css/style.css +++ b/webclient/view/default/ui/css/style.css @@ -11,3 +11,7 @@ .input-multiple-template { margin: 1px; } + +.uk-navbar-dropdown { + width: auto; +} -- 2.39.5