Removed 'asset_id' column from database tables; Added column 'hash' t…

…o questions table
minitek · Dec 19, 2022 · 5e3dc6b · 5e3dc6b
1 parent 5709aab
commit 5e3dc6b
Show file tree

Hide file tree

Showing 2 changed files with 107 additions and 4 deletions.
diff --git a/admin/sql/install.mysql.utf8.sql b/admin/sql/install.mysql.utf8.sql
@@ -13,7 +13,6 @@ CREATE TABLE IF NOT EXISTS `#__minitek_faqbook_attachments` (
 
 CREATE TABLE IF NOT EXISTS `#__minitek_faqbook_question_types` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-  `asset_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'FK to the #__assets table.',
   `title` varchar(255) NOT NULL DEFAULT '',
   `alias` varchar(255) NOT NULL DEFAULT '',
   `color` varchar(10) DEFAULT '#5bc0de',
@@ -26,7 +25,6 @@ CREATE TABLE IF NOT EXISTS `#__minitek_faqbook_question_types` (
 
 CREATE TABLE IF NOT EXISTS `#__minitek_faqbook_customfields` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-  `asset_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'FK to the #__assets table.',
   `title` varchar(255) NOT NULL DEFAULT '',
   `alias` varchar(255) NOT NULL DEFAULT '',
   `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
@@ -75,7 +73,6 @@ CREATE TABLE IF NOT EXISTS `#__minitek_faqbook_answers` (
 
 CREATE TABLE IF NOT EXISTS `#__minitek_faqbook_email_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `asset_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'FK to the #__assets table.',
  `template_key` varchar(255) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `subject` varchar(500) NOT NULL DEFAULT '',
@@ -205,7 +202,6 @@ CREATE TABLE IF NOT EXISTS `#__minitek_faqbook_votes` (
 
 CREATE TABLE IF NOT EXISTS `#__minitek_faqbook_answer_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `asset_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'FK to the #__assets table.',
  `title` varchar(255) NOT NULL DEFAULT '',
  `content` mediumtext NOT NULL,
  `state` tinyint(3) NOT NULL DEFAULT '0',

diff --git a/install.faqbookpro.php b/install.faqbookpro.php
@@ -67,6 +67,113 @@ function preflight($type, $parent)
 			if (isset($this->installed_version) && $this->installed_version && version_compare($this->installed_version, '4.1.2', '<')) {
 				self::update412($parent);
 			}
+
+			// Run update script if old version is older than 4.3.0
+			if (isset($this->installed_version) && $this->installed_version && version_compare($this->installed_version, '4.3.0', '<')) {
+				self::update430($parent);
+			}
+		}
+	}
+
+	/*
+	 * $parent is the class calling this method.
+	 * update runs if old version is < 4.3.0
+	 */
+	function update430($parent)
+	{
+		$db = Factory::getDbo();
+
+		#__minitek_faqbook_answer_templates
+		$answer_templates_columns = $db->getTableColumns('#__minitek_faqbook_answer_templates');
+
+		// Delete column 'asset_id'
+		if (isset($answer_templates_columns['asset_id']))
+		{
+			$query = $db->getQuery(true);
+			$query = " ALTER TABLE `#__minitek_faqbook_answer_templates` ";
+			$query .= " DROP COLUMN `asset_id` ";
+			$db->setQuery($query);
+
+			if (!$result = $db->execute())
+			{
+				throw new GenericDataException('Error 4.3.0-1: Could not delete column asset_id.', 500);
+
+				return false;
+			}
+		}
+
+		#__minitek_faqbook_customfields
+		$customfields_columns = $db->getTableColumns('#__minitek_faqbook_customfields');
+
+		// Delete column 'asset_id'
+		if (isset($customfields_columns['asset_id']))
+		{
+			$query = $db->getQuery(true);
+			$query = " ALTER TABLE `#__minitek_faqbook_customfields` ";
+			$query .= " DROP COLUMN `asset_id` ";
+			$db->setQuery($query);
+
+			if (!$result = $db->execute())
+			{
+				throw new GenericDataException('Error 4.3.0-2: Could not delete column asset_id.', 500);
+
+				return false;
+			}
+		}
+
+		#__minitek_faqbook_email_templates
+		$email_templates_columns = $db->getTableColumns('#__minitek_faqbook_email_templates');
+
+		// Delete column 'asset_id'
+		if (isset($email_templates_columns['asset_id']))
+		{
+			$query = $db->getQuery(true);
+			$query = " ALTER TABLE `#__minitek_faqbook_email_templates` ";
+			$query .= " DROP COLUMN `asset_id` ";
+			$db->setQuery($query);
+
+			if (!$result = $db->execute())
+			{
+				throw new GenericDataException('Error 4.3.0-3: Could not delete column asset_id.', 500);
+
+				return false;
+			}
+		}
+
+		#__minitek_faqbook_question_types
+		$question_types_columns = $db->getTableColumns('#__minitek_faqbook_question_types');
+
+		// Delete column 'asset_id'
+		if (isset($question_types_columns['asset_id']))
+		{
+			$query = $db->getQuery(true);
+			$query = " ALTER TABLE `#__minitek_faqbook_question_types` ";
+			$query .= " DROP COLUMN `asset_id` ";
+			$db->setQuery($query);
+
+			if (!$result = $db->execute())
+			{
+				throw new GenericDataException('Error 4.3.0-4: Could not delete column asset_id.', 500);
+
+				return false;
+			}
+		}
+
+		#__minitek_faqbook_questions
+		$questions_columns = $db->getTableColumns('#__minitek_faqbook_questions');
+
+		// Add column 'hash'
+		if (!isset($questions_columns['hash'])) {
+			$query = $db->getQuery(true);
+			$query = " ALTER TABLE `#__minitek_faqbook_questions` ";
+			$query .= " ADD COLUMN `hash` varchar(500) NOT NULL DEFAULT '' ";
+			$db->setQuery($query);
+			$result = $db->execute();
+
+			if (!$result) {
+				throw new GenericDataException('Error 4.3.0-5: Could not update __minitek_faqbook_questions table.', 500);
+				return false;
+			}
 		}
 	}