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 INSERT INTO civicrm_contribution_soft(contribution_id, contact_id, amount, currency, soft_credit_type_id)
  87 SELECT id, honor_contact_id, total_amount, currency, honor_type_id
  88 FROM civicrm_contribution
  89 WHERE honor_contact_id IS NOT NULL;
  90
  91 SELECT @sct_pcp_id := value from civicrm_option_value where name = 'pcp' and option_group_id = @option_group_id_soft_credit_type;
  92
  93 UPDATE `civicrm_contribution_soft`
  94 SET soft_credit_type_id = @sct_pcp_id
  95 WHERE pcp_id IS NOT NULL;
  96
  97 --CRM-13734 make basic Case Activity Types reserved
  98 SELECT @option_group_id_activity_type := id from civicrm_option_group where name = 'activity_type';
  99 SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
 100
 101 UPDATE `civicrm_option_value`
 102 SET is_reserved = 1
 103 WHERE is_reserved = 0 AND option_group_id = @option_group_id_activity_type AND component_id = @caseCompId;
 104
 105 -- CRM-13912
 106 ALTER TABLE civicrm_action_schedule
 107 ADD COLUMN `mode` varchar(128) COLLATE utf8_unicode_ci DEFAULT 'Email' COMMENT 'Send the message as email or sms or both.';
 108
 109 INSERT INTO
 110 civicrm_option_group (name, {localize field='title'}title{/localize}, is_reserved, is_active)
 111 VALUES
 112 ('msg_mode', {localize}'{ts escape="sql"}Message Mode{/ts}'{/localize}, 1, 1);
 113
 114 SELECT @option_group_id_msg_mode := max(id) from civicrm_option_group where name = 'msg_mode';
 115
 116 INSERT INTO
 117 civicrm_option_value (option_group_id, {localize field='label'}`label`{/localize}, value, name, is_default, weight, is_reserved, is_active)
 118 VALUES
 119 (@option_group_id_msg_mode, {localize}'{ts escape="sql"}Email{/ts}'{/localize}, 'Email', 'Email', 1, 1, 1, 1),
 120 (@option_group_id_msg_mode, {localize}'{ts escape="sql"}SMS{/ts}'{/localize},'SMS', 'SMS', 0, 2, 1, 1),
 121 (@option_group_id_msg_mode, {localize}'{ts escape="sql"}User Preference{/ts}'{/localize}, 'User_Preference', 'User Preference', 0, 3, 1, 1);
 122
 123 ALTER TABLE civicrm_action_schedule ADD sms_provider_id int(10) unsigned NULL COMMENT 'FK to civicrm_sms_provider id ';
 124 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;
 125
 126 --CRM-13981 migrate 'In Honor of' to Soft Credits
 127 INSERT INTO `civicrm_uf_group`
 128      (`name`, `group_type`, {localize field='title'}`title`{/localize}, `is_cms_user`, `is_reserved`)
 129 VALUES
 130    ('honoree_individual', 'Individual, Contact', {localize}'{ts escape="sql"}Honoree Individual{/ts}'{/localize}, 0, 1);
 131
 132 SELECT @uf_group_id_honoree_individual := id from civicrm_uf_group where name = 'honoree_individual';
 133
 134 INSERT INTO `civicrm_uf_field`
 135       (`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)
 136 VALUES
 137       (@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'),
 138       (@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'),
 139       (@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'),
 140       (@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');
 141
 142 UPDATE civicrm_uf_join SET uf_group_id = @uf_group_id_honoree_individual WHERE module = 'soft_credit';
 143
 144 {if $multilingual}
 145   {foreach from=$locales item=loc}
 146      ALTER TABLE civicrm_contribution_page DROP honor_block_title_{$loc};
 147      ALTER TABLE civicrm_contribution_page DROP honor_block_text_{$loc};
 148   {/foreach}
 149 {else}
 150      ALTER TABLE civicrm_contribution_page DROP honor_block_title;
 151      ALTER TABLE civicrm_contribution_page DROP honor_block_text;
 152 {/if}
 153 ALTER TABLE civicrm_contribution_page DROP honor_block_is_active;
 154
 155 ALTER TABLE civicrm_contribution DROP FOREIGN KEY `FK_civicrm_contribution_honor_contact_id`;
 156 ALTER TABLE civicrm_contribution DROP honor_contact_id;
 157 ALTER TABLE civicrm_contribution DROP honor_type_id;
 158
 159 ALTER TABLE civicrm_pledge DROP FOREIGN KEY `FK_civicrm_pledge_honor_contact_id`;
 160 ALTER TABLE civicrm_pledge DROP honor_contact_id;
 161 ALTER TABLE civicrm_pledge DROP honor_type_id;
 162
 163 -- CRM-13964 and CRM-13965
 164 SELECT @option_group_id_cs   := max(id) from civicrm_option_group where name = 'contribution_status';
 165 SELECT @option_val_id_cs_wt  := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs;
 166 SELECT @option_val_id_cs_val := MAX(value) FROM civicrm_option_value WHERE option_group_id = @option_group_id_cs;
 167
 168 INSERT INTO
 169    `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`)
 170 VALUES
 171   (@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),
 172   (@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);
 173
 174 -- participant status adding
 175 SELECT @participant_status_wt  := max(id) from civicrm_participant_status_type;
 176
 177 INSERT INTO civicrm_participant_status_type (name,  {localize field='label'}label{/localize}, class, is_reserved, is_active, is_counted, weight, visibility_id)
 178 VALUES
 179   ('Partially paid', {localize}'{ts escape="sql"}Partially paid{/ts}'{/localize}, 'Positive', 1, 1, 1, @participant_status_wt+1, 2),
 180   ('Pending refund', {localize}'{ts escape="sql"}Pending refund{/ts}'{/localize}, 'Positive', 1, 1, 1, @participant_status_wt+2, 2);
 181
 182 -- new activity types required for partial payments
 183 SELECT @option_group_id_act     := max(id) from civicrm_option_group where name = 'activity_type';
 184 SELECT @option_group_id_act_wt  := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
 185 SELECT @option_group_id_act_val := MAX(ROUND(value)) FROM civicrm_option_value WHERE option_group_id = @option_group_id_act;
 186 SELECT @contributeCompId := max(id) FROM civicrm_component where name = 'CiviContribute';
 187
 188 INSERT INTO
 189    `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`)
 190 VALUES
 191    (@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),
 192    (@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),
 193    (@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);
 194
 195 -- CRM-13970
 196 UPDATE civicrm_navigation set url = 'civicrm/admin/options/from_email_address&reset=1' WHERE url LIKE 'civicrm/admin/options/from_email%';
 197 UPDATE civicrm_navigation set url = CONCAT(SUBSTRING_INDEX(url, '&', 1), '&reset=1') WHERE url LIKE 'civicrm/admin/options/%';
 198
 199 -- CRM-14181
 200 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?';
 201 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.';
 202 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?';
 203 ALTER TABLE  `civicrm_price_field` CHANGE  `html_type`  `html_type` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
 204 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.';
 205 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.';
 206 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.';
 207 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.';
 208 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.';
 209 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.';
 210 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.';
 211 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';
 212 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?.';
 213 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)';
 214 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.';
 215 ALTER TABLE  `civicrm_mailing_bounce_type` CHANGE  `name`  `name` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT  'Type of bounce';
 216 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';
 217 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';
 218 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)';
 219 ALTER TABLE  `civicrm_word_replacement` CHANGE  `match_type`  `match_type` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'wildcardMatch';
 220 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.';
 221 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).';
 222 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.';
 223 ALTER TABLE  `civicrm_extension` CHANGE  `type`  `type` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
 224 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.';
 225 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.';
 226 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.';
 227 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.';
 228 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.';
 229 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.';
 230 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) ';
 231 ALTER TABLE  `civicrm_product` CHANGE  `duration_unit`  `duration_unit` VARCHAR( 8 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT  'year';
 232 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.';
 233 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.';
 234 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';
 235 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';
 236 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.';
 237 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.';
 238 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';
 239 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.';
 240 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.';
 241
 242 -- CRM-14183
 243 INSERT IGNORE INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1157, "PL", "Plateau");
 244 UPDATE civicrm_state_province SET name = "Abuja Federal Capital Territory" WHERE name = "Abuja Capital Territory";
 245
 246 -- CRM-13992
 247 ALTER TABLE `civicrm_custom_field`
 248   ADD COLUMN `in_selector` tinyint(4) DEFAULT '0' COMMENT 'Should the multi-record custom field values be displayed in tab table listing';
 249 UPDATE civicrm_custom_field cf
 250   LEFT JOIN civicrm_custom_group cg
 251     ON cf.custom_group_id = cg.id
 252   SET cf.in_selector = 1
 253   WHERE cg.is_multiple = 1 AND cf.html_type != 'TextArea';
 254 ALTER TABLE `civicrm_custom_group`
 255  CHANGE COLUMN `style` `style` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Visual relationship between this form and its parent.';
 256
 257 -- Add "developer" help menu
 258 SELECT @parent_id := `id` FROM `civicrm_navigation` WHERE `name` = 'Help' AND `domain_id` = {$domainID};
 259
 260 INSERT INTO civicrm_navigation
 261 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 262 VALUES
 263 ( {$domainID}, NULL, '{ts escape="sql" skip="true"}Developer{/ts}', 'Developer', 'administer CiviCRM', '', @parent_id, '1', NULL, 5 );
 264
 265 SET @devellastID:=LAST_INSERT_ID();
 266 INSERT INTO civicrm_navigation
 267 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 268 VALUES
 269 ( {$domainID}, 'civicrm/api/explorer', '{ts escape="sql" skip="true"}API Explorer{/ts}','API Explorer', 'administer CiviCRM', '', @devellastID, '1', NULL, 1 ),
 270 ( {$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 );
 271
 272 -- CRM-14435
 273 ALTER TABLE `civicrm_mail_settings`
 274   ADD CONSTRAINT `FK_civicrm_mail_settings_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) ON DELETE CASCADE;
 275
 276 -- CRM-14436
 277 ALTER TABLE `civicrm_mailing`
 278   ADD COLUMN `hash` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Key for validating requests related to this mailing.',
 279   ADD INDEX `index_hash` (`hash`);
 280
 281 -- CRM-14300
 282 UPDATE `civicrm_event` SET is_template = 0 WHERE is_template IS NULL;
 283 ALTER TABLE `civicrm_event`
 284   CHANGE is_template is_template tinyint(4) DEFAULT '0' COMMENT 'whether the event has template';
 285
 286 -- CRM-14493
 287 INSERT IGNORE INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1085, "61", "Pieria");
 288
 289 -- CRM-14445
 290 ALTER TABLE `civicrm_option_group`
 291     ADD COLUMN `is_locked` int(1) DEFAULT 0 COMMENT 'A lock to remove the ability to add new options via the UI';
 292
 293 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');
 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   `definition` blob    COMMENT 'xml definition of case type',
 322   PRIMARY KEY (`id`),
 323   UNIQUE KEY `case_type_name` (`name`)
 324 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
 325
 326 SELECT @option_group_id_case_type := max(id) from civicrm_option_group where name = 'case_type';
 327
 328 INSERT IGNORE INTO civicrm_case_type
 329   (id, name, {localize field='title'}title{/localize}, {localize field='description'}description{/localize}, is_active, is_reserved, weight)
 330   SELECT
 331     value,
 332     name,
 333     {localize field='label'}label{/localize},
 334     {localize field='description'}description{/localize},
 335     is_active,
 336     is_reserved,
 337     weight
 338   FROM civicrm_option_value
 339   WHERE
 340     option_group_id = @option_group_id_case_type;
 341
 342 -- Remove the special character, earlier used as a separator and reference to civicrm_case_type.id
 343 UPDATE civicrm_case SET case_type_id = replace(case_type_id, 0x01, '');
 344
 345 ALTER TABLE civicrm_case
 346   MODIFY case_type_id int(10) unsigned COLLATE utf8_unicode_ci NULL COMMENT 'FK to civicrm_case_type.id',
 347   ADD CONSTRAINT FK_civicrm_case_case_type_id FOREIGN KEY (case_type_id) REFERENCES civicrm_case_type (id) ON DELETE SET NULL;
 348
 349 -- CRM-15343 set the auto increment civicrm_case_type.id start point to max id to avoid conflict in future insertion
 350 SELECT @max_case_type_id := max(id) from civicrm_case_type;
 351 SET @query  = CONCAT("ALTER TABLE civicrm_case_type AUTO_INCREMENT = ", IFNULL(@max_case_type_id,1));
 352 PREPARE alter_case_type_auto_inc FROM @query;
 353 EXECUTE alter_case_type_auto_inc;
 354 DEALLOCATE PREPARE alter_case_type_auto_inc;
 355
 356 DELETE FROM civicrm_option_value WHERE option_group_id = @option_group_id_case_type;
 357
 358 DELETE FROM civicrm_option_group WHERE id = @option_group_id_case_type;
 359
 360 -- CRM-14611
 361 {if $multilingual}
 362   {foreach from=$locales item=locale}
 363       ALTER TABLE civicrm_survey ADD title_{$locale} varchar(255);
 364       UPDATE civicrm_survey SET title_{$locale} = title;
 365
 366       ALTER TABLE civicrm_survey ADD instructions_{$locale} TEXT;
 367       UPDATE civicrm_survey SET instructions_{$locale} = instructions;
 368   {/foreach}
 369
 370   ALTER TABLE civicrm_survey DROP title;
 371   ALTER TABLE civicrm_survey DROP instructions;
 372 {/if}
 373
 374 -- CRM-11182 -- Make event confirmation page optional
 375  ALTER TABLE civicrm_event
 376    ADD COLUMN is_confirm_enabled tinyint(4) DEFAULT '1';
 377
 378  UPDATE civicrm_event
 379    SET is_confirm_enabled = 1
 380    WHERE is_monetary = 1;
 381
 382  UPDATE civicrm_event
 383    SET is_confirm_enabled = 0
 384    WHERE is_monetary = 0;
 385
 386 -- CRM-11182
 387 ALTER TABLE civicrm_event
 388   ADD COLUMN dedupe_rule_group_id int(10) unsigned DEFAULT NULL COMMENT 'Rule to use when matching registrations for this event',
 389   ADD CONSTRAINT `FK_civicrm_event_dedupe_rule_group_id` FOREIGN KEY (`dedupe_rule_group_id`) REFERENCES `civicrm_dedupe_rule_group` (`id`);
 390
 391 -- CRM-9288
 392 SELECT @option_web_id := id  FROM civicrm_option_group WHERE name = 'website_type';
 393
 394 SELECT @website_default := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and is_default = 1;
 395
 396 SELECT @website_work := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and name= 'Work';
 397
 398 UPDATE civicrm_option_value
 399 SET is_default = 1 WHERE option_group_id = @option_web_id and value = IFNULL(@website_default , @website_work);
 400
 401 SELECT @website_default := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and is_default = 1;
 402
 403 ALTER TABLE civicrm_uf_field
 404   ADD COLUMN `website_type_id` int(10) unsigned DEFAULT NULL COMMENT 'Website Type Id, if required' AFTER phone_type_id,
 405   ADD INDEX `IX_website_type_id` (`website_type_id`);
 406
 407 UPDATE civicrm_uf_field
 408 SET website_type_id = @website_default,
 409 field_name = 'url' WHERE field_name LIKE 'url%';
 410
 411 SELECT @website_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_web_id;
 412 SELECT @website_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_web_id;
 413
 414 INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}label{/localize}, name, value, weight)
 415 SELECT @option_web_id, {localize}website{/localize}, website, (@website_value := @website_value + 1), (@website_weight := @website_weight + 1) FROM (
 416 SELECT 'Google+' AS website
 417     UNION ALL
 418 SELECT 'Instagram' AS website
 419     UNION ALL
 420 SELECT 'LinkedIn' AS website
 421     UNION ALL
 422 SELECT 'Pinterest' AS website
 423     UNION ALL
 424 SELECT 'Tumblr' AS website
 425     UNION ALL
 426 SELECT 'SnapChat' AS website
 427     UNION ALL
 428 SELECT 'Vine' AS website
 429 ) AS temp
 430 LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.website)
 431 AND option_group_id = @option_web_id
 432 WHERE co.id IS NULL;
 433
 434 -- CRM-14627 civicrm navigation inconsistent
 435 UPDATE civicrm_navigation
 436 SET civicrm_navigation.url = CONCAT(SUBSTRING(url FROM 1 FOR LOCATE('&', url) - 1), '?', SUBSTRING(url FROM LOCATE('&', url) + 1))
 437 WHERE civicrm_navigation.url LIKE "%&%" AND civicrm_navigation.url NOT LIKE "%?%";
 438
 439 -- CRM-14478 Add a "cleanup" policy for managed entities
 440 ALTER TABLE `civicrm_managed`
 441 ADD COLUMN `cleanup` varchar(32) COMMENT 'Policy on when to cleanup entity (always, never, unused)';
 442
 443 -- CRM-14639
 444
 445 SELECT @option_grant_status := id  FROM civicrm_option_group WHERE name = 'grant_status';
 446 UPDATE civicrm_option_value
 447 SET
 448 {if !$multilingual}
 449   label =
 450     CASE
 451       WHEN lower(name) = 'granted'
 452       THEN 'Paid'
 453       WHEN lower(name) = 'approved'
 454       THEN 'Eligible'
 455       WHEN lower(name) = 'rejected'
 456       THEN 'Ineligible'
 457       ELSE 'Submitted'
 458     END,
 459 {else}
 460   {foreach from=$locales item=locale}
 461     label_{$locale} =
 462       CASE
 463         WHEN lower(name) = 'granted'
 464           THEN 'Paid'
 465           WHEN lower(name) = 'approved'
 466           THEN 'Eligible'
 467           WHEN lower(name) = 'rejected'
 468           THEN 'Ineligible'
 469           ELSE 'Submitted'
 470       END,
 471   {/foreach}
 472 {/if}
 473 name =
 474 CASE
 475   WHEN lower(name) = 'granted'
 476    THEN 'Paid'
 477   WHEN lower(name) = 'approved'
 478    THEN 'Eligible'
 479   WHEN lower(name) = 'rejected'
 480    THEN 'Ineligible'
 481   ELSE 'Submitted'
 482 END
 483 WHERE option_group_id = @option_grant_status and LOWER(name) IN ('granted', 'pending', 'approved', 'rejected');
 484
 485 SELECT @grant_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_grant_status;
 486 SELECT @grant_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_grant_status;
 487
 488 INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}label{/localize}, name, value, weight)
 489 SELECT @option_grant_status, {localize}grantstatus{/localize}, grantstatus, @grant_value := @grant_value + 1, @grant_weight := @grant_weight + 1 FROM (
 490 SELECT 'Submitted' AS grantstatus
 491     UNION ALL
 492 SELECT 'Approved for Payment' AS grantstatus
 493     UNION ALL
 494 SELECT 'Eligible' AS grantstatus
 495     UNION ALL
 496 SELECT 'Awaiting Information' AS grantstatus
 497     UNION ALL
 498 SELECT 'Withdrawn' AS grantstatus
 499 ) AS temp
 500 LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.grantstatus)
 501 AND option_group_id = @option_grant_status
 502 WHERE co.id IS NULL;
 503
 504 -- Fix trailing single quote in grant status label
 505 {if !$multilingual}
 506   UPDATE civicrm_option_value v
 507     INNER JOIN civicrm_option_group g
 508     ON v.option_group_id = g.id AND g.name = 'grant_status'
 509     SET label = 'Awaiting Information'
 510     WHERE v.label = 'Awaiting Information\'' and v.name = 'Awaiting Information';
 511 {else}
 512   UPDATE civicrm_option_value v
 513     INNER JOIN civicrm_option_group g
 514     ON v.option_group_id = g.id AND g.name = 'grant_status'
 515     SET
 516     {foreach from=$locales item=locale}
 517       v.label_{$locale} = CASE
 518         WHEN v.label_{$locale} = 'Awaiting Information\'' THEN 'Awaiting Information'
 519         ELSE v.label_{$locale}
 520       END,
 521     {/foreach}
 522     v.name = v.name
 523     WHERE v.name = 'Awaiting Information';
 524 {/if}
 525
 526 -- CRM-14197 Add contribution_id to civicrm_line_item
 527
 528 ALTER TABLE civicrm_line_item ADD contribution_id INT(10) unsigned COMMENT 'Contribution ID' NULL AFTER entity_id;
 529
 530 -- FK to civicrm_contribution
 531
 532 ALTER TABLE civicrm_line_item
 533 ADD CONSTRAINT `FK_civicrm_contribution_id` FOREIGN KEY (`contribution_id`) REFERENCES civicrm_contribution (`id`) ON DELETE SET NULL;
 534
 535 ALTER TABLE `civicrm_line_item`
 536 DROP INDEX `UI_line_item_value`,
 537 ADD UNIQUE INDEX `UI_line_item_value` (`entity_table`, `entity_id`, `contribution_id`, `price_field_value_id`, `price_field_id`);
 538
 539 -- update case type menu
 540 UPDATE civicrm_navigation set url = 'civicrm/a/#/caseType' WHERE url LIKE 'civicrm/admin/options/case_type%';