123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372 |
- <?php
- /**
- * Bibliotheca
- *
- * Copyright 2018-2023 Johannes Keßler
- *
- * This program is free software: you can redistribute it and/or modify
- * it under the terms of the GNU General Public License as published by
- * the Free Software Foundation, either version 3 of the License, or
- * (at your option) any later version.
- *
- * This program is distributed in the hope that it will be useful,
- * but WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
- * GNU General Public License for more details.
- *
- * You should have received a copy of the GNU General Public License
- * along with this program. If not, see http://www.gnu.org/licenses/gpl-3.0.
- */
- /**
- * Class ManageCollectionFields to manage fields from a existing collection
- */
- class ManageCollectionFields {
- /**
- * The database object
- *
- * @var mysqli
- */
- private mysqli $_DB;
- /**
- * The user object to query with
- *
- * @var Doomguy
- */
- private Doomguy $_User;
- /**
- * The collection we are working with
- *
- * @var string
- */
- private string $_collectionId;
- /**
- * Which db cols should not be removed
- *
- * @var array
- */
- private array $_protectedDBCols = array(
- 'id','created','modified','modificationuser','owner','group','rights'
- );
- /**
- * Store existing fields info for runtime
- *
- * @var array
- */
- private array $_cacheExistingSysFields = array();
- /**
- * Store available fields info for runtime
- *
- * @var array
- */
- private array $_cacheAvailableFields = array();
- /**
- * ManageCollections constructor
- *
- * @param mysqli $databaseConnectionObject
- * @param Doomguy $userObj
- */
- public function __construct(mysqli $databaseConnectionObject, Doomguy $userObj) {
- $this->_DB = $databaseConnectionObject;
- $this->_User = $userObj;
- }
- /**
- * The id from the collection we are working with
- *
- * @param string $id
- */
- public function setCollection(string $id): void {
- if(!empty($id)) {
- $this->_collectionId = $id;
- }
- }
- /**
- * Get available fields based on user
- *
- * @param bool $refresh
- * @return array
- * @todo No rights implemented yet. Maybe not needed. Management done by hand directly on DB
- */
- public function getAvailableFields(bool $refresh=false): array {
- if($refresh === false && !empty($this->_cacheAvailableFields)) {
- return $this->_cacheAvailableFields;
- }
- $queryStr = "SELECT `id`, `identifier`, `displayname`, `type`,
- `createstring`, `value`
- FROM `".DB_PREFIX."_sys_fields`
- ORDER BY `displayname`";
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr));
- try {
- $query = $this->_DB->query($queryStr);
- if($query !== false && $query->num_rows > 0) {
- while(($result = $query->fetch_assoc()) != false) {
- $this->_cacheAvailableFields[$result['id']] = $result;
- }
- }
- }
- catch (Exception $e) {
- Summoner::sysLog("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage());
- }
- return $this->_cacheAvailableFields;
- }
- /**
- * Simple comma separated number string
- *
- * @param string $string
- * @return bool
- */
- public function validateFieldSortString(string $string): bool {
- $ret = false;
- $_t = str_replace(",","",$string);
- if(Summoner::validate($_t, 'digit')) {
- $ret = true;
- }
- return $ret;
- }
- /**
- * Deletes relations and data from the collection!
- *
- * $fieldsSortString have to be validated already
- *
- * @param string $fieldsSortString
- * @return bool
- */
- public function updateFields(string $fieldsSortString): bool {
- $ret = false;
- $ids = array();
- $fieldsSortString = trim($fieldsSortString, ", ");
- if(strstr($fieldsSortString, ",")) {
- $ids = explode(",", $fieldsSortString);
- }
- else {
- $ids[] = $fieldsSortString;
- }
- if(!empty($ids)) {
- $_newColumns = $this->_getSQLForCollectionColumns($ids);
- $_existingFields = $this->getExistingFields();
- // use the createsting info to determine if the field needs to be remove
- // from entry table or lookup table
- $_fieldsToCheckForDelete = $_existingFields;
- $queriesDeleteEntryTable = array();
- foreach($ids as $_id) {
- if(isset($_fieldsToCheckForDelete[$_id])) {
- unset($_fieldsToCheckForDelete[$_id]);
- }
- }
- if(!empty($_fieldsToCheckForDelete)) {
- foreach($_fieldsToCheckForDelete as $k=>$v) {
- if(!empty($v['createstring'])) {
- $queriesDeleteEntryTable[] = "ALTER TABLE `".DB_PREFIX."_collection_entry_".$this->_collectionId."`
- DROP `".$v['identifier']."`";
- }
- }
- }
- $queryStrDeleteFields = "DELETE FROM `".DB_PREFIX."_collection_fields_".$this->_collectionId."`
- WHERE `fk_field_id` NOT IN (".implode(",",$ids).")";
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStrDeleteFields));
- $queryStrDeletee2l = "DELETE FROM `".DB_PREFIX."_collection_entry2lookup_".$this->_collectionId."`
- WHERE `fk_field` NOT IN (".implode(",",$ids).")";
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStrDeletee2l));
- $queryStrInsertFields = "INSERT INTO `".DB_PREFIX."_collection_fields_".$this->_collectionId."` (`fk_field_id`,`sort`) VALUES ";
- foreach ($ids as $k => $v) {
- $queryStrInsertFields .= "('".$this->_DB->real_escape_string($v)."','".$this->_DB->real_escape_string($k)."'),";
- }
- $queryStrInsertFields = trim($queryStrInsertFields, ",");
- $queryStrInsertFields .= " ON DUPLICATE KEY UPDATE `sort` = VALUES(`sort`)";
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStrInsertFields));
- if(!empty($_newColumns)) {
- $queryStrAlterEntry = array();
- foreach($_newColumns as $k=>$v) {
- $queryStrAlterEntry[] = "ALTER TABLE `".DB_PREFIX."_collection_entry_".$this->_collectionId."` ADD ".$v['createstring']."";
- }
- }
- // this is not good. mysql implicit commit is triggered with alter table.
- // needs a rewrite without alter table to fully use transactions..
- try {
- $this->_DB->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
- $this->_DB->query($queryStrDeleteFields);
- $this->_DB->query($queryStrDeletee2l);
- $this->_DB->commit();
- // mysql implicit commit
- if(!empty($queriesDeleteEntryTable)) {
- foreach($queriesDeleteEntryTable as $q) {
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::sysLog($q));
- $this->_DB->query($q);
- }
- }
- $this->_DB->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
- $this->_DB->query($queryStrInsertFields);
- $this->_DB->commit();
- // mysql implicit commit
- if(!empty($_newColumns)) {
- foreach ($queryStrAlterEntry as $q1) {
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($q1));
- $this->_DB->query($q1);
- }
- }
- $ret = true;
- }
- catch (Exception $e) {
- $this->_DB->rollback();
- Summoner::sysLog("[ERROR] update fields ".__METHOD__." mysql catch: ".$e->getMessage());
- }
- }
- return $ret;
- }
- /**
- * Get the fields for currently loaded collection.
- *
- * @param bool $refresh True to reload from DB
- * @param bool $sortAZ
- * @return array
- */
- public function getExistingFields(bool $refresh=false, bool $sortAZ=false): array {
- if($refresh === false && !empty($this->_cacheExistingSysFields)) {
- return $this->_cacheExistingSysFields;
- }
- $this->_cacheExistingSysFields = array();
- $queryStr = "SELECT `cf`.`fk_field_id` AS id,
- `sf`.`type`,
- `sf`.`displayname`,
- `sf`.`identifier`,
- `sf`.`createstring`,
- `sf`.`searchtype`
- FROM `".DB_PREFIX."_collection_fields_".$this->_collectionId."` AS cf
- LEFT JOIN `".DB_PREFIX."_sys_fields` AS sf ON `cf`.`fk_field_id` = `sf`.`id`";
- if($sortAZ === true) {
- $queryStr .= " ORDER BY `sf`.`displayname`";
- }
- else {
- $queryStr .= " ORDER BY `cf`.`sort`";
- }
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr));
- try {
- $query = $this->_DB->query($queryStr);
- if($query !== false && $query->num_rows > 0) {
- while(($result = $query->fetch_assoc()) != false) {
- $this->_cacheExistingSysFields[$result['id']] = $result;
- }
- }
- }
- catch (Exception $e) {
- Summoner::sysLog("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage());
- }
- return $this->_cacheExistingSysFields;
- }
- /**
- * return the simple search fields for loaded collection
- * Every field witch has a column in the entry table is a simple search field.
- * Name starts with entry
- *
- * @see Trite->getSimpleSearchFields()
- *
- * @return array
- */
- public function getSimpleSearchFields(): array {
- $ret = array();
- $fields = $this->getExistingFields();
- if(!empty($fields)) {
- foreach($fields as $k=>$v) {
- if(isset($v['searchtype']) && strpos($v['searchtype'],'entry') !== false) {
- $ret[$k] = $v;
- }
- }
- }
- // add systemfields
- $def['created'] = array('identifier' => 'created', 'displayname' => 'sysfield.created', 'type' => 'systemfield');
- $def['modified'] = array('identifier' => 'modified', 'displayname' => 'sysfield.modified', 'type' => 'systemfield');
- return $def + $ret;
- }
- /**
- * Get the column names from current collection entry table
- *
- * @return array
- */
- private function _getExistingCollectionColumns(): array {
- $ret = array();
- $queryStr = "SHOW COLUMNS FROM `".DB_PREFIX."_collection_entry_".$this->_collectionId."`";
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr));
- try {
- $query = $this->_DB->query($queryStr);
- if($query !== false && $query->num_rows > 0) {
- while(($result = $query->fetch_assoc()) != false) {
- if(!in_array($result['Field'], $this->_protectedDBCols, true)) {
- $ret[$result['Field']] = $result['Field'];
- }
- }
- }
- }
- catch (Exception $e) {
- Summoner::sysLog("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage());
- }
- return $ret;
- }
- /**
- * Get the required SQL information from given field ids
- * to create columns in entry table.
- *
- * @param array $columnIds sort=>fk_field_id
- * @return array
- */
- private function _getSQLForCollectionColumns(array $columnIds): array {
- $_fields = array();
- // enrich with information
- $_sysFields = $this->getAvailableFields();
- $_existingDBColumns = $this->_getExistingCollectionColumns();
- if(!empty($columnIds)) {
- foreach($columnIds as $sort=>$fieldId) {
- if(isset($_sysFields[$fieldId])) {
- $_fd = $_sysFields[$fieldId];
- if(isset($_existingDBColumns[$_fd['identifier']])) continue;
- if(empty($_fd['createstring'])) continue;
- $_fields[$fieldId] = $_fd;
- }
- }
- }
- return $_fields;
- }
- }
|