CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl

   1 {* file to handle db changes in 4.5.alpha1 during upgrade *}
   2 {include file='../CRM/Upgrade/4.5.alpha1.msg_template/civicrm_msg_template.tpl'}
   3
   4 ALTER TABLE `civicrm_contact`
   5   ADD COLUMN `formal_title` varchar(64) COMMENT 'Formal (academic or similar) title in front of name. (Prof., Dr. etc.)' AFTER `suffix_id`;
   6
   7 ALTER TABLE `civicrm_contact`
   8   ADD COLUMN `communication_style_id` int(10) unsigned COMMENT 'Communication style (e.g. formal vs. familiar) to use with this contact. FK to communication styles in civicrm_option_value.' AFTER `formal_title`,
   9   ADD INDEX `index_communication_style_id` (`communication_style_id`);
  10
  11 INSERT INTO
  12   `civicrm_option_group` (`name`, {localize field='title'}`title`{/localize}, `is_reserved`, `is_active`)
  13 VALUES
  14   ('communication_style', {localize}'{ts escape="sql"}Communication Style{/ts}'{/localize}, 1, 1);
  15
  16 SELECT @option_group_id_communication_style := max(id) from civicrm_option_group where name = 'communication_style';
  17
  18 INSERT INTO
  19   `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`)
  20 VALUES
  21   (@option_group_id_communication_style, {localize}'{ts escape="sql"}Formal{/ts}'{/localize},   1, 'formal'  , NULL, 0, 1, 1, 0, 0, 1, NULL, NULL),
  22   (@option_group_id_communication_style, {localize}'{ts escape="sql"}Familiar{/ts}'{/localize}, 2, 'familiar', NULL, 0, 0, 2, 0, 0, 1, NULL, NULL);
  23
  24 -- Insert menu item at Administer > Communications, above the various Greeting Formats
  25
  26 SELECT @parent_id := `id` FROM `civicrm_navigation` WHERE `name` = 'Communications' AND `domain_id` = {$domainID};
  27 SELECT @add_weight := MIN(`weight`) FROM `civicrm_navigation` WHERE `name` IN('Email Greeting Formats', 'Postal Greeting Formats', 'Addressee Formats') AND `parent_id` = @parent_id;
  28
  29 UPDATE `civicrm_navigation`
  30 SET `weight` = `weight`+1
  31 WHERE `parent_id` = @parent_id
  32 AND `weight` >= @add_weight;
  33
  34 INSERT INTO `civicrm_navigation`
  35   ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
  36 VALUES
  37   ( {$domainID}, 'civicrm/admin/options/communication_style&group=communication_style&reset=1', '{ts escape="sql" skip="true"}Communication Style Options{/ts}', 'Communication Style Options', 'administer CiviCRM', '', @parent_id, '1', NULL, @add_weight );
  38
  39 -- CRM-9988 Change world region of Panama country to America South, Central, North and Caribbean
  40 UPDATE `civicrm_country` SET `region_id` = 2 WHERE `id` = 1166;
  41
  42 SELECT @option_group_id_contact_edit_options := max(id) from civicrm_option_group where name = 'contact_edit_options';
  43
  44 INSERT INTO
  45   `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`)
  46 VALUES
  47   (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Prefix{/ts}'{/localize}      , 12, 'Prefix'      , NULL, 2, NULL, 12, 0, 0, 1, NULL, NULL),
  48   (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Formal Title{/ts}'{/localize}, 13, 'Formal Title', NULL, 2, NULL, 13, 0, 0, 1, NULL, NULL),
  49   (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}First Name{/ts}'{/localize}  , 14, 'First Name'  , NULL, 2, NULL, 14, 0, 0, 1, NULL, NULL),
  50   (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Middle Name{/ts}'{/localize} , 15, 'Middle Name' , NULL, 2, NULL, 15, 0, 0, 1, NULL, NULL),
  51   (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Last Name{/ts}'{/localize}   , 16, 'Last Name'   , NULL, 2, NULL, 16, 0, 0, 1, NULL, NULL),
  52   (@option_group_id_contact_edit_options, {localize}'{ts escape="sql"}Suffix{/ts}'{/localize}      , 17, 'Suffix'      , NULL, 2, NULL, 17, 0, 0, 1, NULL, NULL);
  53
  54 -- CRM-13857
  55 ALTER TABLE civicrm_group
  56   ADD COLUMN `modified_id` INT(10) unsigned DEFAULT NULL COMMENT 'FK to contact table, modifier of the group.',
  57   ADD CONSTRAINT `FK_civicrm_group_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL;
  58
  59 -- CRM-13913
  60 ALTER TABLE civicrm_word_replacement
  61   ALTER COLUMN `is_active` SET DEFAULT 1;
  62
  63 --CRM-13833 Implement Soft Credit Type for Contribution
  64 INSERT INTO civicrm_option_group
  65       (name, {localize field='title'}title{/localize}, is_reserved, is_active) VALUES ('soft_credit_type', {localize}'{ts escape="sql"}Soft Credit Types{/ts}'{/localize}, 1, 1);
  66
  67 SELECT @option_group_id_soft_credit_type := max(id) from civicrm_option_group where name = 'soft_credit_type';
  68
  69 INSERT INTO `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `weight`, `is_default`, `is_active`, `is_reserved`)
  70  VALUES
  71   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}In Honor of{/ts}'{/localize}, 1, 'in_honor_of', 1, 0, 1, 1),
  72   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}In Memory of{/ts}'{/localize}, 2, 'in_memory_of', 2, 0, 1, 1),
  73   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}Solicited{/ts}'{/localize}, 3, 'solicited', 3, 0, 1, 1),
  74   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}Household{/ts}'{/localize}, 4, 'household', 4, 0, 1, 0),
  75   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}Workplace Giving{/ts}'{/localize}, 5, 'workplace', 5, 0, 1, 0),
  76   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}Foundation Affiliate{/ts}'{/localize}, 6, 'foundation_affiliate', 6, 0, 1, 0),
  77   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}3rd-party Service{/ts}'{/localize}, 7, '3rd-party_service', 7, 0, 1, 0),
  78   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}Donor-advised Fund{/ts}'{/localize}, 8, 'donor-advised_fund', 8, 0, 1, 0),
  79   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}Matched Gift{/ts}'{/localize}, 9, 'matched_gift', 9, 0, 1, 0),
  80   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}Personal Campaign Page{/ts}'{/localize}, 10, 'pcp', 10, 0, 1, 1),
  81   (@option_group_id_soft_credit_type   , {localize}'{ts escape="sql"}Gift{/ts}'{/localize}, 11, 'gift', 11, 0, 1, 1);
  82
  83 ALTER TABLE `civicrm_contribution_soft`
  84   ADD COLUMN `soft_credit_type_id`  int(10) unsigned COMMENT 'Soft Credit Type ID.Implicit FK to civicrm_option_value where option_group = soft_credit_type.';
  85
  86 SELECT @sct_pcp_id := value from civicrm_option_value where name = 'pcp' and option_group_id = @option_group_id_soft_credit_type;
  87
  88 UPDATE `civicrm_contribution_soft`
  89 SET soft_credit_type_id = @sct_pcp_id
  90 WHERE pcp_id IS NOT NULL;
  91
  92 --CRM-13734 make basic Case Activity Types reserved
  93 SELECT @option_group_id_activity_type := id from civicrm_option_group where name = 'activity_type';
  94 SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
  95
  96 UPDATE `civicrm_option_value`
  97 SET is_reserved = 1
  98 WHERE is_reserved = 0 AND option_group_id = @option_group_id_activity_type AND component_id = @caseCompId;
  99
 100 -- CRM-13912
 101 ALTER TABLE civicrm_action_schedule
 102 ADD COLUMN `mode` varchar(128) COLLATE utf8_unicode_ci DEFAULT 'Email' COMMENT 'Send the message as email or sms or both.';
 103
 104 INSERT INTO
 105 civicrm_option_group (name, {localize field='title'}title{/localize}, is_reserved, is_active)
 106 VALUES
 107 ('msg_mode', {localize}'{ts escape="sql"}Message Mode{/ts}'{/localize}, 1, 1);
 108
 109 SELECT @option_group_id_msg_mode := max(id) from civicrm_option_group where name = 'msg_mode';
 110
 111 INSERT INTO
 112 civicrm_option_value (option_group_id, {localize field='label'}`label`{/localize}, value, name, is_default, weight, is_reserved, is_active)
 113 VALUES
 114 (@option_group_id_msg_mode, {localize}'{ts escape="sql"}Email{/ts}'{/localize}, 'Email', 'Email', 1, 1, 1, 1),
 115 (@option_group_id_msg_mode, {localize}'{ts escape="sql"}SMS{/ts}'{/localize},'SMS', 'SMS', 0, 2, 1, 1),
 116 (@option_group_id_msg_mode, {localize}'{ts escape="sql"}User Preference{/ts}'{/localize}, 'User_Preference', 'User Preference', 0, 3, 1, 1);
 117
 118 ALTER TABLE civicrm_action_schedule ADD sms_provider_id int(10) unsigned NULL COMMENT 'FK to civicrm_sms_provider id ';
 119 ALTER TABLE civicrm_action_schedule ADD CONSTRAINT FK_civicrm_action_schedule_sms_provider_id FOREIGN KEY (`sms_provider_id`) REFERENCES `civicrm_sms_provider` (`id`) ON DELETE SET NULL;
 120
 121 --CRM-13981 migrate 'In Honor of' to Soft Credits
 122 INSERT INTO `civicrm_uf_group`
 123      (`name`, `group_type`, {localize field='title'}`title`{/localize}, `is_cms_user`, `is_reserved`)
 124 VALUES
 125    ('honoree_individual', 'Individual, Contact', {localize}'{ts escape="sql"}Honoree Individual{/ts}'{/localize}, 0, 1);
 126
 127 SELECT @uf_group_id_honoree_individual := id from civicrm_uf_group where name = 'honoree_individual';
 128
 129 INSERT INTO `civicrm_uf_field`
 130       (`uf_group_id`, `field_name`, `is_required`, `is_reserved`, `weight`, `visibility`, `in_selector`, `is_searchable`, `location_type_id`, {localize field='label'}`label`{/localize}, field_type)
 131 VALUES
 132       (@uf_group_id_honoree_individual, 'prefix_id',  0, 1, 1, 'User and User Admin Only', 0, 1, NULL, {localize}'{ts escape="sql"}Individual Prefix{/ts}'{/localize}, 'Individual'),
 133       (@uf_group_id_honoree_individual, 'first_name', 0, 1, 2, 'User and User Admin Only', 0, 1, NULL, {localize}'{ts escape="sql"}First Name{/ts}'{/localize},        'Individual'),
 134       (@uf_group_id_honoree_individual, 'last_name',  0, 1, 3, 'User and User Admin Only', 0, 1, NULL, {localize}'{ts escape="sql"}Last Name{/ts}'{/localize},         'Individual'),
 135       (@uf_group_id_honoree_individual, 'email',      0, 1, 4, 'User and User Admin Only', 0, 1, 1,    {localize}'{ts escape="sql"}Email Address{/ts}'{/localize},     'Individual');
 136
 137 ALTER TABLE `civicrm_uf_join`
 138   ADD COLUMN `module_data` longtext COMMENT 'Json serialized array of data used by the ufjoin.module';
 139
 140 {if $multilingual}
 141   {foreach from=$locales item=loc}
 142      ALTER TABLE civicrm_contribution_page DROP honor_block_title_{$loc};
 143      ALTER TABLE civicrm_contribution_page DROP honor_block_text_{$loc};
 144   {/foreach}
 145 {else}
 146      ALTER TABLE civicrm_contribution_page DROP honor_block_title;
 147      ALTER TABLE civicrm_contribution_page DROP honor_block_text;
 148 {/if}
 149 ALTER TABLE civicrm_contribution_page DROP honor_block_is_active;
 150
 151 ALTER TABLE civicrm_contribution DROP FOREIGN KEY `FK_civicrm_contribution_honor_contact_id`;
 152 ALTER TABLE civicrm_contribution DROP honor_contact_id;
 153 ALTER TABLE civicrm_contribution DROP honor_type_id;
 154
 155 ALTER TABLE civicrm_pledge DROP FOREIGN KEY `FK_civicrm_pledge_honor_contact_id`;
 156 ALTER TABLE civicrm_pledge DROP honor_contact_id;
 157 ALTER TABLE civicrm_pledge DROP honor_type_id;
 158
 159 -- CRM-13964 and CRM-13965
 160 SELECT @option_group_id_cs   := max(id) from civicrm_option_group where name = 'contribution_status';
 161 SELECT @option_val_id_cs_wt  := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs;
 162 SELECT @option_val_id_cs_val := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs;
 163
 164 INSERT INTO
 165    `civicrm_option_value` (`option_group_id`, {localize field='label'}label{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`)
 166 VALUES
 167   (@option_group_id_cs, {localize}'{ts escape="sql"}Partially paid{/ts}'{/localize}, @option_val_id_cs_val+1, 'Partially paid', NULL, 0, NULL, @option_val_id_cs_wt+1, 0, 1, 1, NULL, NULL),
 168   (@option_group_id_cs, {localize}'{ts escape="sql"}Pending refund{/ts}'{/localize}, @option_val_id_cs_val+2, 'Pending refund', NULL, 0, NULL, @option_val_id_cs_wt+2, 0, 1, 1, NULL, NULL);
 169
 170 -- participant status adding
 171 SELECT @participant_status_wt  := max(id) from civicrm_participant_status_type;
 172
 173 INSERT INTO civicrm_participant_status_type (name,  {localize field='label'}label{/localize}, class, is_reserved, is_active, is_counted, weight, visibility_id)
 174 VALUES
 175   ('Partially paid', {localize}'{ts escape="sql"}Partially paid{/ts}'{/localize}, 'Positive', 1, 1, 1, @participant_status_wt+1, 2),
 176   ('Pending refund', {localize}'{ts escape="sql"}Pending refund{/ts}'{/localize}, 'Positive', 1, 1, 1, @participant_status_wt+2, 2);
 177
 178 -- new activity types required for partial payments
 179 SELECT @option_group_id_act     := max(id) from civicrm_option_group where name = 'activity_type';
 180 SELECT @option_group_id_act_wt  := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
 181 SELECT @option_group_id_act_val := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
 182 SELECT @contributeCompId := max(id) FROM civicrm_component where name = 'CiviContribute';
 183
 184 INSERT INTO
 185    `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, {localize field='description'}`description`{/localize}, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`)
 186 VALUES
 187    (@option_group_id_act, {localize}'{ts escape="sql"}Payment{/ts}'{/localize}, @option_group_id_act_val+1, 'Payment', NULL, 1, NULL, @option_group_id_act_wt+1, {localize}'{ts escape="sql"}Additional payment recorded for event or membership fee.{/ts}'{/localize}, 0, 1, 1, @contributeCompId, NULL),
 188    (@option_group_id_act, {localize}'{ts escape="sql"}Refund{/ts}'{/localize}, @option_group_id_act_val+2, 'Refund', NULL, 1, NULL, @option_group_id_act_wt+2, {localize}'{ts escape="sql"}Refund recorded for event or membership fee.{/ts}'{/localize}, 0, 1, 1, @contributeCompId, NULL),
 189    (@option_group_id_act, {localize}'{ts escape="sql"}Change Registration{/ts}'{/localize}, @option_group_id_act_val+3, 'Change Registration', NULL, 1, NULL, @option_group_id_act_wt+3, {localize}'{ts escape="sql"}Changes to an existing event registration.{/ts}'{/localize}, 0, 1, 1, @eventCompId, NULL);
 190
 191 -- CRM-13970
 192 UPDATE civicrm_navigation set url = 'civicrm/admin/options/from_email_address&reset=1' WHERE url LIKE 'civicrm/admin/options/from_email%';
 193 UPDATE civicrm_navigation set url = CONCAT(SUBSTRING_INDEX(url, '&', 1), '&reset=1') WHERE url LIKE 'civicrm/admin/options/%';
 194
 195 -- CRM-14181
 196 ALTER TABLE  civicrm_acl CHANGE  operation  operation VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT  'What operation does this ACL entry control?';
 197 ALTER TABLE  civicrm_campaign_group CHANGE  group_type  group_type VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Type of Group.';
 198 ALTER TABLE  `civicrm_acl_contact_cache` CHANGE  `operation`  `operation` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT  'What operation does this user have permission on?';
 199 ALTER TABLE  `civicrm_price_field` CHANGE  `html_type`  `html_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
 200 ALTER TABLE  `civicrm_pledge` CHANGE  `frequency_unit`  `frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'month' COMMENT  'Time units for recurrence of pledge payments.';
 201 ALTER TABLE  `civicrm_membership_type` CHANGE  `duration_unit`  `duration_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Unit in which membership period is expressed.';
 202 ALTER TABLE  `civicrm_membership_type` CHANGE  `period_type`  `period_type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Rolling membership period starts on signup date. Fixed membership periods start on fixed_period_start_day.';
 203 ALTER TABLE  `civicrm_membership_status` CHANGE  `start_event`  `start_event` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Event when this status starts.';
 204 ALTER TABLE  `civicrm_membership_status` CHANGE  `start_event_adjust_unit`  `start_event_adjust_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Unit used for adjusting from start_event.';
 205 ALTER TABLE  `civicrm_membership_status` CHANGE  `end_event`  `end_event` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Event after which this status ends.';
 206 ALTER TABLE  `civicrm_membership_status` CHANGE  `end_event_adjust_unit`  `end_event_adjust_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Unit used for adjusting from the ending event.';
 207 ALTER TABLE  `civicrm_mailing_job` CHANGE  `status`  `status` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'The state of this job';
 208 ALTER TABLE  `civicrm_mailing_group` CHANGE  `group_type`  `group_type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Are the members of the group included or excluded?.';
 209 ALTER TABLE  `civicrm_mailing` CHANGE  `visibility`  `visibility` VARCHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'User and User Admin Only' COMMENT  'In what context(s) is the mailing contents visible (online viewing)';
 210 ALTER TABLE  `civicrm_mailing_component` CHANGE  `component_type`  `component_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Type of Component.';
 211 ALTER TABLE  `civicrm_mailing_bounce_type` CHANGE  `name`  `name` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT  'Type of bounce';
 212 ALTER TABLE  `civicrm_participant_status_type` CHANGE  `class`  `class` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'the general group of status type this one belongs to';
 213 ALTER TABLE  `civicrm_dedupe_rule_group` CHANGE  `contact_type`  `contact_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'The type of contacts this group applies to';
 214 ALTER TABLE  `civicrm_dedupe_rule_group` CHANGE  `used`  `used` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'Whether the rule should be used for cases where usage is Unsupervised, Supervised OR General(programatically)';
 215 ALTER TABLE  `civicrm_word_replacement` CHANGE  `match_type`  `match_type` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'wildcardMatch';
 216 ALTER TABLE  `civicrm_uf_field` CHANGE  `visibility`  `visibility` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'User and User Admin Only' COMMENT  'In what context(s) is this field visible.';
 217 ALTER TABLE  `civicrm_mapping_field` CHANGE  `operator`  `operator` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'SQL WHERE operator for search-builder mapping fields (search criteria).';
 218 ALTER TABLE  `civicrm_job` CHANGE  `run_frequency`  `run_frequency` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'Daily' COMMENT  'Scheduled job run frequency.';
 219 ALTER TABLE  `civicrm_extension` CHANGE  `type`  `type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
 220 ALTER TABLE  `civicrm_custom_group` CHANGE  `style`  `style` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Visual relationship between this form and its parent.';
 221 ALTER TABLE  `civicrm_custom_field` CHANGE  `data_type`  `data_type` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT  'Controls location of data storage in extended_data table.';
 222 ALTER TABLE  `civicrm_custom_field` CHANGE  `html_type`  `html_type` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT  'HTML types plus several built-in extended types.';
 223 ALTER TABLE  `civicrm_action_schedule` CHANGE  `start_action_unit`  `start_action_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Time units for reminder.';
 224 ALTER TABLE  `civicrm_action_schedule` CHANGE  `repetition_frequency_unit`  `repetition_frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Time units for repetition of reminder.';
 225 ALTER TABLE  `civicrm_action_schedule` CHANGE  `end_frequency_unit`  `end_frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'Time units till repetition of reminder.';
 226 ALTER TABLE  `civicrm_product` CHANGE  `period_type`  `period_type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'rolling' COMMENT 'Rolling means we set start/end based on current day, fixed means we set start/end for current year or month(e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006) ';
 227 ALTER TABLE  `civicrm_product` CHANGE  `duration_unit`  `duration_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'year';
 228 ALTER TABLE  `civicrm_product` CHANGE  `frequency_unit`  `frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'month' COMMENT 'Frequency unit and interval allow option to store actual delivery frequency for a subscription or service.';
 229 ALTER TABLE  `civicrm_contribution_recur` CHANGE  `frequency_unit`  `frequency_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'month' COMMENT  'Time units for recurrence of payment.';
 230 ALTER TABLE  `civicrm_subscription_history` CHANGE  `method`  `method` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'How the (un)subscription was triggered';
 231 ALTER TABLE  `civicrm_subscription_history` CHANGE  `status`  `status` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'The state of the contact within the group';
 232 ALTER TABLE  `civicrm_relationship_type` CHANGE  `contact_type_a`  `contact_type_a` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'If defined, contact_a in a relationship of this type must be a specific contact_type.';
 233 ALTER TABLE  `civicrm_relationship_type` CHANGE  `contact_type_b`  `contact_type_b` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'If defined, contact_b in a relationship of this type must be a specific contact_type.';
 234 ALTER TABLE  `civicrm_group_contact` CHANGE  `status`  `status` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT  'status of contact relative to membership in group';
 235 ALTER TABLE  `civicrm_group` CHANGE  `visibility`  `visibility` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'User and User Admin Only' COMMENT  'In what context(s) is this field visible.';
 236 ALTER TABLE  `civicrm_contact` CHANGE  `preferred_mail_format`  `preferred_mail_format` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'Both' COMMENT  'What is the preferred mode of sending an email.';
 237
 238 -- CRM-14183
 239 INSERT INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1157, "PL", "Plateau");
 240 UPDATE civicrm_state_province SET name = "Abuja Federal Capital Territory" WHERE name = "Abuja Capital Territory";
 241
 242 -- CRM-13992
 243 ALTER TABLE `civicrm_custom_field`
 244   ADD COLUMN `in_selector` tinyint(4) DEFAULT '0' COMMENT 'Should the multi-record custom field values be displayed in tab table listing';
 245 UPDATE civicrm_custom_field cf
 246   LEFT JOIN civicrm_custom_group cg
 247     ON cf.custom_group_id = cg.id
 248   SET cf.in_selector = 1
 249   WHERE cg.is_multiple = 1 AND cf.html_type != 'TextArea';
 250 ALTER TABLE `civicrm_custom_group`
 251  CHANGE COLUMN `style` `style` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Visual relationship between this form and its parent.';
 252
 253 -- Add "developer" help menu
 254 SELECT @parent_id := `id` FROM `civicrm_navigation` WHERE `name` = 'Help' AND `domain_id` = {$domainID};
 255
 256 INSERT INTO civicrm_navigation
 257 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 258 VALUES
 259 ( {$domainID}, NULL, '{ts escape="sql" skip="true"}Developer{/ts}', 'Developer', 'administer CiviCRM', '', @parent_id, '1', NULL, 5 );
 260
 261 SET @devellastID:=LAST_INSERT_ID();
 262 INSERT INTO civicrm_navigation
 263 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 264 VALUES
 265 ( {$domainID}, 'civicrm/api/explorer', '{ts escape="sql" skip="true"}API Explorer{/ts}','API Explorer', 'administer CiviCRM', '', @devellastID, '1', NULL, 1 ),
 266 ( {$domainID}, 'http://wiki.civicrm.org/confluence/display/CRMDOC/Develop', '{ts escape="sql" skip="true"}Developer Docs{/ts}', 'Developer Docs', 'administer CiviCRM', '', @devellastID, '1', NULL, 3 );
 267
 268 -- CRM-14435
 269 ALTER TABLE `civicrm_mail_settings`
 270   ADD CONSTRAINT `FK_civicrm_mail_settings_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) ON DELETE CASCADE;
 271
 272 -- CRM-14436
 273 ALTER TABLE `civicrm_mailing`
 274   ADD COLUMN `hash` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Key for validating requests related to this mailing.',
 275   ADD INDEX `index_hash` (`hash`);
 276
 277 -- CRM-14300
 278 UPDATE `civicrm_event` SET is_template = 0 WHERE is_template IS NULL;
 279 ALTER TABLE `civicrm_event`
 280   CHANGE is_template is_template tinyint(4) DEFAULT '0' COMMENT 'whether the event has template';
 281
 282 -- CRM-14493
 283 INSERT INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1085, "61", "Pieria");
 284
 285 -- CRM-14445
 286 ALTER TABLE `civicrm_option_group`
 287         ADD COLUMN `is_locked` int(1) DEFAULT 0 COMMENT 'A lock to remove the ability to add new options via the UI';
 288
 289 UPDATE `civicrm_option_group` SET is_locked = 1 WHERE name IN ('contribution_status','activity_contacts','advanced_search_options','auto_renew_options','contact_autocomplete_options','batch_status','batch_type','batch_mode','contact_edit_options','contact_reference_options','contact_smart_group_display','contact_view_options','financial_item_status','mapping_type','pcp_status','user_dashboard_options','tag_used_for');
 290
 291
 292 -- CRM-14522
 293 ALTER TABLE `civicrm_msg_template` DROP FOREIGN KEY `FK_civicrm_msg_template_pdf_format_id`;
 294
 295 -- CRM-14449
 296 CREATE TABLE IF NOT EXISTS `civicrm_system_log` (
 297 `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: ID.',
 298 `message` VARCHAR(128) NOT NULL COMMENT 'Standardized message',
 299 `context` LONGTEXT NULL COMMENT 'JSON encoded data',
 300 `level` VARCHAR(9)  NOT NULL DEFAULT 'info' COMMENT 'error level per PSR3',
 301 `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of when event occurred.',
 302 `contact_id` INT(11) NULL DEFAULT NULL COMMENT 'Optional Contact ID that created the log. Not an FK as we keep this regardless',
 303  hostname VARCHAR(128) NOT NULL COMMENT 'Optional Name of logging host',
 304 PRIMARY KEY (`id`),
 305 INDEX `message` (`message`),
 306 INDEX `contact_id` (`contact_id`),
 307 INDEX `level` (`level`)
 308 )
 309 COMMENT='Table that contains logs of all system events.'
 310 COLLATE='utf8_general_ci';
 311
 312 -- CRM-14473 civicrm_case_type table creation and migration
 313 CREATE TABLE IF NOT EXISTS `civicrm_case_type` (
 314   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Autoincremented type id',
 315   `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Machine name for Case Type',
 316   {localize field='title'}title varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Natural language name for Case Type'{/localize},
 317   {localize field='description'}description varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Description of the Case Type'{/localize},
 318   `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this entry active?',
 319   `is_reserved` tinyint(4) DEFAULT NULL COMMENT 'Is this case type a predefined system type?',
 320   `weight` int(11) NOT NULL DEFAULT '1' COMMENT 'Ordering of the case types',
 321   PRIMARY KEY (`id`),
 322   UNIQUE KEY `case_type_name` (`name`)
 323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
 324
 325 SELECT @option_group_id_case_type := max(id) from civicrm_option_group where name = 'case_type';
 326
 327 INSERT IGNORE INTO civicrm_case_type
 328   (name, {localize field='title'}title{/localize}, {localize field='description'}description{/localize}, is_active, is_reserved, weight)
 329   SELECT
 330     name,
 331     {localize field='label'}label{/localize},
 332     {localize field='description'}description{/localize},
 333     is_active,
 334     is_reserved,
 335     weight
 336   FROM civicrm_option_value
 337   WHERE
 338     option_group_id = @option_group_id_case_type;
 339
 340 -- Replace the pseudo-fk to ov.value with a reference to civicrm_case_type.id
 341 UPDATE civicrm_case
 342   SET case_type_id = (
 343     SELECT civicrm_case_type.id FROM civicrm_case_type
 344     JOIN civicrm_option_value
 345       ON civicrm_option_value.name = civicrm_case_type.name
 346     WHERE
 347       civicrm_option_value.option_group_id = @option_group_id_case_type
 348       AND civicrm_option_value.value = replace(civicrm_case.case_type_id, 0x01, '')
 349   );
 350
 351 ALTER TABLE civicrm_case
 352   MODIFY case_type_id int(10) unsigned COLLATE utf8_unicode_ci NULL COMMENT 'FK to civicrm_case_type.id',
 353   ADD CONSTRAINT FK_civicrm_case_case_type_id FOREIGN KEY (case_type_id) REFERENCES civicrm_case_type (id) ON DELETE SET NULL;
 354
 355 DELETE FROM civicrm_option_value WHERE option_group_id = @option_group_id_case_type;
 356
 357 DELETE FROM civicrm_option_group WHERE id = @option_group_id_case_type;
 358
 359 -- CRM-14611
 360 {if $multilingual}
 361   {foreach from=$locales item=locale}
 362       ALTER TABLE civicrm_survey ADD title_{$locale} varchar(255);
 363       UPDATE civicrm_survey SET title_{$locale} = title;
 364
 365       ALTER TABLE civicrm_survey ADD instructions_{$locale} TEXT;
 366       UPDATE civicrm_survey SET instructions_{$locale} = instructions;
 367   {/foreach}
 368
 369   ALTER TABLE civicrm_survey DROP title;
 370   ALTER TABLE civicrm_survey DROP instructions;
 371 {/if}
 372
 373 -- CRM-11182 -- Make event confirmation page optional
 374  ALTER TABLE civicrm_event
 375    ADD COLUMN is_confirm_enabled tinyint(4) DEFAULT '1';
 376
 377  UPDATE civicrm_event
 378    SET is_confirm_enabled = 1
 379    WHERE is_monetary = 1;
 380
 381  UPDATE civicrm_event
 382    SET is_confirm_enabled = 0
 383    WHERE is_monetary = 0;
 384
 385 -- CRM-11182
 386 ALTER TABLE civicrm_event
 387   ADD COLUMN dedupe_rule_group_id int(10) unsigned DEFAULT NULL COMMENT 'Rule to use when matching registrations for this event',
 388   ADD CONSTRAINT `FK_civicrm_event_dedupe_rule_group_id` FOREIGN KEY (`dedupe_rule_group_id`) REFERENCES `civicrm_dedupe_rule_group` (`id`);
 389
 390 -- CRM-9288
 391 SELECT @option_web_id := id  FROM civicrm_option_group WHERE name = 'website_type';
 392
 393 SELECT @website_default := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and is_default = 1;
 394
 395 SELECT @website_work := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and name= 'Work';
 396
 397 UPDATE civicrm_option_value
 398 SET is_default = 1 WHERE option_group_id = @option_web_id and value = IFNULL(@website_default , @website_work);
 399
 400 SELECT @website_default := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and is_default = 1;
 401
 402 ALTER TABLE civicrm_uf_field
 403   ADD COLUMN `website_type_id` int(10) unsigned DEFAULT NULL COMMENT 'Website Type Id, if required' AFTER phone_type_id,
 404   ADD INDEX `IX_website_type_id` (`website_type_id`);
 405
 406 UPDATE civicrm_uf_field
 407 SET website_type_id = @website_default,
 408 field_name = 'url' WHERE field_name LIKE 'url%';
 409
 410 SELECT @website_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_web_id;
 411 SELECT @website_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_web_id;
 412
 413 INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}label{/localize}, name, value, weight)
 414 SELECT @option_web_id, {localize}website{/localize}, website, (@website_value := @website_value + 1), (@website_weight := @website_weight + 1) FROM (
 415 SELECT 'Google+' AS website
 416     UNION ALL
 417 SELECT 'Instagram' AS website
 418     UNION ALL
 419 SELECT 'LinkedIn' AS website
 420     UNION ALL
 421 SELECT 'Pinterest' AS website
 422     UNION ALL
 423 SELECT 'Tumblr' AS website
 424     UNION ALL
 425 SELECT 'SnapChat' AS website
 426     UNION ALL
 427 SELECT 'Vine' AS website
 428 ) AS temp
 429 LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.website)
 430 AND option_group_id = @option_web_id
 431 WHERE co.id IS NULL;
 432
 433 -- CRM-14627 civicrm navigation inconsistent
 434 UPDATE civicrm_navigation
 435 SET civicrm_navigation.url = CONCAT(SUBSTRING(url FROM 1 FOR LOCATE('&', url) - 1), '?', SUBSTRING(url FROM LOCATE('&', url) + 1))
 436 WHERE civicrm_navigation.url LIKE "%&%" AND civicrm_navigation.url NOT LIKE "%?%";
 437
 438 -- CRM-14639
 439
 440 SELECT @option_grant_status := id  FROM civicrm_option_group WHERE name = 'grant_status';
 441 UPDATE civicrm_option_value
 442 SET
 443 {if !$multilingual}
 444   label =
 445     CASE
 446       WHEN lower(name) = 'granted'
 447       THEN 'Paid'
 448       WHEN lower(name) = 'approved'
 449       THEN 'Eligible'
 450       WHEN lower(name) = 'rejected'
 451       THEN 'Ineligible'
 452       ELSE 'Submitted'
 453     END,
 454 {else}
 455   {foreach from=$locales item=locale}
 456     label_{$locale} =
 457       CASE
 458         WHEN lower(name) = 'granted'
 459         THEN 'Paid'
 460         WHEN lower(name) = 'approved'
 461         THEN 'Eligible'
 462         WHEN lower(name) = 'rejected'
 463         THEN 'Ineligible'
 464         ELSE 'Submitted'
 465       END,
 466   {/foreach}
 467 {/if}
 468 name =
 469 CASE
 470   WHEN lower(name) = 'granted'
 471    THEN 'Paid'
 472   WHEN lower(name) = 'approved'
 473    THEN 'Eligible'
 474   WHEN lower(name) = 'rejected'
 475    THEN 'Ineligible'
 476   ELSE 'Submitted'
 477 END
 478 WHERE option_group_id = @option_grant_status and LOWER(name) IN ('granted', 'pending', 'approved', 'rejected');
 479
 480 SELECT @grant_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_grant_status;
 481 SELECT @grant_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_grant_status;
 482
 483 INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}label{/localize}, name, value, weight)
 484 SELECT @option_grant_status, {localize}grantstatus{/localize}, grantstatus, @grant_value := @grant_value + 1, @grant_weight := @grant_weight + 1 FROM (
 485 SELECT 'Submitted' AS grantstatus
 486     UNION ALL
 487 SELECT 'Approved for Payment' AS grantstatus
 488     UNION ALL
 489 SELECT 'Eligible' AS grantstatus
 490     UNION ALL
 491 SELECT 'Awaiting Information' AS grantstatus
 492     UNION ALL
 493 SELECT 'Withdrawn' AS grantstatus
 494 ) AS temp
 495 LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.grantstatus)
 496 AND option_group_id = @option_grant_status
 497 WHERE co.id IS NULL;
 498
 499 -- Fix trailing single quote in grant status label
 500 {if !$multilingual}
 501   UPDATE civicrm_option_value v
 502         INNER JOIN civicrm_option_group g
 503         ON v.option_group_id = g.id AND g.name = 'grant_status'
 504         SET label = 'Awaiting Information'
 505         WHERE v.label = 'Awaiting Information\'' and v.name = 'Awaiting Information';
 506 {else}
 507   UPDATE civicrm_option_value v
 508     INNER JOIN civicrm_option_group g
 509     ON v.option_group_id = g.id AND g.name = 'grant_status'
 510     SET
 511     {foreach from=$locales item=locale}
 512       v.label_{$locale} = CASE
 513         WHEN v.label_{$locale} = 'Awaiting Information\'' THEN 'Awaiting Information'
 514         ELSE v.label_{$locale}
 515       END,
 516     {/foreach}
 517     v.name = v.name
 518     WHERE v.name = 'Awaiting Information';
 519 {/if}