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