From fc9e7e517117432196a452183e9648efc81e33a7 Mon Sep 17 00:00:00 2001
From: Adam Roses Wight <awight@wikimedia.org>
Date: Tue, 29 Apr 2014 14:42:04 -0700
Subject: [PATCH] Schema and migration to make Case Type a first-class table

CRM-14473
---
 .../Incremental/sql/4.5.alpha1.mysql.tpl      | 45 ++++++++++++
 tests/phpunit/CiviTest/truncate.xml           |  2 +
 xml/schema/Case/Case.xml                      | 15 ++--
 xml/schema/Case/CaseType.xml                  | 69 +++++++++++++++++++
 xml/schema/Case/files.xml                     |  1 +
 5 files changed, 126 insertions(+), 6 deletions(-)
 create mode 100644 xml/schema/Case/CaseType.xml

diff --git a/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl
index 1cbf053425..9fdfddd8ad 100644
--- a/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl
+++ b/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl
@@ -309,3 +309,48 @@ INDEX `level` (`level`)
 COMMENT='Table that contains logs of all system events.'
 COLLATE='utf8_general_ci';
 
+-- CRM-14473 civicrm_case_type table creation and migration
+CREATE TABLE IF NOT EXISTS `civicrm_case_type` (
+  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Autoincremented type id',
+  `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Machine name for Case Type',
+  `title` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Natural language name for Case Type',
+  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Description of the Case Type',
+  `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this entry active?',
+  `is_reserved` tinyint(4) DEFAULT NULL COMMENT 'Is this case type a predefined system type?',
+  `weight` int(11) NOT NULL DEFAULT '1' COMMENT 'Ordering of the case types',
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `case_type_name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
+
+SELECT @option_group_id_case_type := max(id) from civicrm_option_group where name = 'case_type';
+
+INSERT IGNORE INTO civicrm_case_type
+  (name, title, description, is_active, is_reserved, weight)
+  SELECT
+    name,
+    label,
+    description,
+    is_active,
+    is_reserved,
+    weight
+  FROM civicrm_option_value
+  WHERE
+    option_group_id = @option_group_id_case_type;
+
+-- Replace the pseudo-fk to ov.value with a reference to civicrm_case_type.id
+UPDATE civicrm_case
+  SET case_type_id = (
+    SELECT civicrm_case_type.id FROM civicrm_case_type
+    JOIN civicrm_option_value
+      ON civicrm_option_value.name = civicrm_case_type.name
+    WHERE
+      civicrm_option_value.option_group_id = @option_group_id_case_type
+      AND civicrm_option_value.value = replace(civicrm_case.case_type_id, 0x01, '')
+  );
+
+ALTER TABLE civicrm_case
+  MODIFY case_type_id int(10) unsigned COLLATE utf8_unicode_ci NOT NULL COMMENT 'FK to civicrm_case_type.name';
+
+DELETE FROM civicrm_option_value WHERE option_group_id = @option_group_id_case_type;
+
+DELETE FROM civicrm_option_group WHERE id = @option_group_id_case_type;
diff --git a/tests/phpunit/CiviTest/truncate.xml b/tests/phpunit/CiviTest/truncate.xml
index e1656a1460..db94538844 100644
--- a/tests/phpunit/CiviTest/truncate.xml
+++ b/tests/phpunit/CiviTest/truncate.xml
@@ -93,6 +93,8 @@
 
   <civicrm_case_contact />
 
+  <civicrm_case_type />
+
   <civicrm_grant />
 
   <civicrm_tell_friend />
diff --git a/xml/schema/Case/Case.xml b/xml/schema/Case/Case.xml
index 8c669e604c..4d741a71e9 100644
--- a/xml/schema/Case/Case.xml
+++ b/xml/schema/Case/Case.xml
@@ -39,16 +39,12 @@
   </foreignKey>
   <field>
        <name>case_type_id</name>
-       <type>varchar</type>
-       <length>128</length>
+       <type>int unsigned</type>
        <required>true</required>
        <import>true</import>
        <export>false</export>
        <title>Case Type</title>
-       <comment>Multivalued pseudo-fk to option_value table where option_group_name = case_type.</comment>
-       <pseudoconstant>
-         <optionGroupName>case_type</optionGroupName>
-       </pseudoconstant>
+       <comment>FK to civicrm_case_type.id</comment>
        <html>
          <type>Select</type>
        </html>
@@ -59,6 +55,13 @@
        <fieldName>case_type_id</fieldName>
        <add>2.0</add>
   </index>
+  <foreignKey>
+       <name>case_type</name>
+       <table>civicrm_case_type</table>
+       <key>id</key>
+       <add>4.5</add>
+       <onDelete>CASCADE</onDelete>
+  </foreignKey>
   <field>
        <name>casetag1_id</name>
        <type>varchar</type>
diff --git a/xml/schema/Case/CaseType.xml b/xml/schema/Case/CaseType.xml
new file mode 100644
index 0000000000..c9db0220ab
--- /dev/null
+++ b/xml/schema/Case/CaseType.xml
@@ -0,0 +1,69 @@
+<?xml version="1.0" encoding="utf-8" ?>
+<table>
+  <base>CRM/Case</base>
+  <class>CaseType</class>
+  <name>civicrm_case_type</name>
+  <comment>Case type definition</comment>
+  <add>4.5</add>
+  <log>true</log>
+  <field>
+    <name>id</name>
+    <type>int unsigned</type>
+    <required>true</required>
+    <comment>Autoincremented type id</comment>
+    <add>4.5</add>
+  </field>
+  <primaryKey>
+    <name>id</name>
+    <autoincrement>true</autoincrement>
+  </primaryKey>
+  <field>
+    <name>name</name>
+    <type>varchar</type>
+    <length>64</length>
+    <required>true</required>
+    <comment>Machine name for Case Type</comment>
+    <add>4.5</add>
+  </field>
+  <index>
+    <name>case_type_name</name>
+    <fieldName>name</fieldName>
+    <unique>true</unique>
+    <add>4.5</add>
+  </index>
+  <field>
+    <name>title</name>
+    <type>varchar</type>
+    <length>64</length>
+    <required>true</required>
+    <comment>Natural language name for Case Type</comment>
+    <add>4.5</add>
+  </field>
+  <field>
+    <name>description</name>
+    <type>varchar</type>
+    <length>255</length>
+    <comment>Description of the Case Type</comment>
+    <add>4.5</add>
+  </field>
+  <field>
+    <name>is_active</name>
+    <type>boolean</type>
+    <comment>Is this entry active?</comment>
+    <add>4.5</add>
+  </field>
+  <field>
+    <name>is_reserved</name>
+    <type>boolean</type>
+    <comment>Is this case type a predefined system type?</comment>
+    <add>4.5</add>
+  </field>
+  <field>
+    <name>weight</name>
+    <type>int</type>
+    <required>true</required>
+    <default>1</default>
+    <comment>Ordering of the case types</comment>
+    <add>4.5</add>
+  </field>
+</table>
diff --git a/xml/schema/Case/files.xml b/xml/schema/Case/files.xml
index 8d2ec97241..ab66c82031 100644
--- a/xml/schema/Case/files.xml
+++ b/xml/schema/Case/files.xml
@@ -4,4 +4,5 @@
 <xi:include href="Case.xml"                 parse="xml" />
 <xi:include href="CaseActivity.xml"         parse="xml" />
 <xi:include href="CaseContact.xml"          parse="xml" />
+<xi:include href="CaseType.xml"             parse="xml" />
 </tables>
-- 
2.25.1