From 5f5568f7ef58c45a83bc3a80a4b522dfed238e53 Mon Sep 17 00:00:00 2001 From: Banana Date: Tue, 5 Jan 2021 12:45:15 +0100 Subject: [PATCH] manage collection fields will also remove the data from the collection since the field is not available anymore in this collection --- TODO | 2 - .../lib/managecollectionfields.class.php | 87 +++++++++++++------ .../managecollectionfields.html | 2 +- 3 files changed, 63 insertions(+), 28 deletions(-) diff --git a/TODO b/TODO index e6d856d..6788105 100644 --- a/TODO +++ b/TODO @@ -1,5 +1,3 @@ -* Collection fields: final remove from entry table - Add waring about it! * rights example setup and also check if it matches the menu and actions + admin + Management diff --git a/webclient/lib/managecollectionfields.class.php b/webclient/lib/managecollectionfields.class.php index 072aa09..f4dadc7 100644 --- a/webclient/lib/managecollectionfields.class.php +++ b/webclient/lib/managecollectionfields.class.php @@ -137,9 +137,9 @@ class ManageCollectionFields { } /** - * $fieldsSortString have to be validated already + * Deletes relations and data from the collection! * - * @todo remove non existing ones from table + * $fieldsSortString have to be validated already * * @param string $fieldsSortString * @return bool @@ -158,38 +158,74 @@ class ManageCollectionFields { 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']."`"; + } + } + } - $queryStr1 = "DELETE FROM `".DB_PREFIX."_collection_fields_".$this->_collectionId."` + $queryStrDeleteFields = "DELETE FROM `".DB_PREFIX."_collection_fields_".$this->_collectionId."` WHERE `fk_field_id` NOT IN (".implode(",",$ids).")"; + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStrDeleteFields,true)); + + $queryStrDeletee2l = "DELETE FROM `".DB_PREFIX."_collection_entry2lookup_".$this->_collectionId."` + WHERE `fk_field` NOT IN (".implode(",",$ids).")"; + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStrDeletee2l,true)); + + $queryStrInsertFields = "INSERT INTO `".DB_PREFIX."_collection_fields_".$this->_collectionId."` (`fk_field_id`,`sort`) VALUES "; + foreach ($ids as $k => $v) { + $queryStrInsertFields .= "($v,$k),"; + } + $queryStrInsertFields = trim($queryStrInsertFields, ","); + $queryStrInsertFields .= " ON DUPLICATE KEY UPDATE `sort` = VALUES(`sort`)"; + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStrInsertFields,true)); + + if(!empty($_newColumns)) { + $queryStrAlterEntry = "ALTER TABLE `".DB_PREFIX."_collection_entry_".$this->_collectionId."`"; + foreach($_newColumns as $k=>$v) { + $queryStrAlterEntry .= " ADD ".$v['createstring'].","; + } + $queryStrAlterEntry = trim($queryStrAlterEntry, ","); + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStrAlterEntry,true)); + } + try { $this->_DB->begin_transaction(MYSQLI_TRANS_START_READ_WRITE); - $q1 = $this->_DB->query($queryStr1); - if($q1 !== false) { - // https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html - $queryStr = "INSERT INTO `".DB_PREFIX."_collection_fields_".$this->_collectionId."` (`fk_field_id`,`sort`) VALUES "; - foreach ($ids as $k => $v) { - $queryStr .= "($v,$k),"; + if($this->_DB->query($queryStrDeleteFields) !== false && $this->_DB->query($queryStrDeletee2l) !== false) { + + $_check = true; + if(!empty($queriesDeleteEntryTable)) { + foreach($queriesDeleteEntryTable as $q) { + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($q,true)); + if($this->_DB->query($q) == false) { + $_check = false; + break; + } + } } - $queryStr = trim($queryStr, ","); - $queryStr .= " ON DUPLICATE KEY UPDATE `sort` = VALUES(`sort`)"; - if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); - $q2 = $this->_DB->query($queryStr); - if($q2 !== false) { - $_newColumns = $this->_getSQLForCollectionColumns($ids); + if($this->_DB->query($queryStrInsertFields) !== false && $_check === true) { $alterQuery = false; if(!empty($_newColumns)) { - $alterString = "ALTER TABLE `".DB_PREFIX."_collection_entry_".$this->_collectionId."`"; - foreach($_newColumns as $k=>$v) { - $alterString .= " ADD ".$v['createstring'].","; - } - $alterString = trim($alterString, ","); - - if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($alterQuery,true)); - $alterQuery = $this->_DB->query($alterString); + $alterQuery = $this->_DB->query($queryStrAlterEntry); } - if(!empty($_newColumns) && $alterQuery == false) { + if(!empty($_newColumns) && $alterQuery === false) { throw new Exception("Failed to insert alter the table."); } } @@ -223,7 +259,8 @@ class ManageCollectionFields { return $this->_cacheExistingSysFields; } - $queryStr = "SELECT `cf`.`fk_field_id` AS id, `sf`.`type`, `sf`.`displayname`, `sf`.`identifier` + $queryStr = "SELECT `cf`.`fk_field_id` AS id, `sf`.`type`, `sf`.`displayname`, `sf`.`identifier`, + `sf`.`createstring` FROM `".DB_PREFIX."_collection_fields_".$this->_collectionId."` AS cf LEFT JOIN `".DB_PREFIX."_sys_fields` AS sf ON `cf`.`fk_field_id` = `sf`.`id` ORDER BY `cf`.`sort`"; diff --git a/webclient/view/default/managecollectionfields/managecollectionfields.html b/webclient/view/default/managecollectionfields/managecollectionfields.html index fce9fff..8b4be13 100644 --- a/webclient/view/default/managecollectionfields/managecollectionfields.html +++ b/webclient/view/default/managecollectionfields/managecollectionfields.html @@ -5,7 +5,7 @@

Add or remove a field

Just use drag and drop below to add, remove or order your fields.
- Removing a field will remove + Removing a field will remove the stored data from the collection.
Make sure at least the title, cover image and description fields are available. -- 2.39.5