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'}
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`;
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`);
12 `civicrm_option_group` (`name`, {localize field='title'}`title`{/localize}, `is_reserved`, `is_active`)
14 ('communication_style', {localize}'{ts escape="sql"}Communication Style{/ts}'{/localize}, 1, 1);
16 SELECT @option_group_id_communication_style := max(id) from civicrm_option_group where name = 'communication_style';
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`)
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);
24 -- Insert menu item at Administer > Communications, above the various Greeting Formats
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;
29 UPDATE `civicrm_navigation`
30 SET `weight` = `weight`+1
31 WHERE `parent_id` = @parent_id
32 AND `weight` >= @add_weight;
34 INSERT INTO `civicrm_navigation`
35 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
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 );
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;
42 SELECT @option_group_id_contact_edit_options := max(id) from civicrm_option_group where name = 'contact_edit_options';
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`)
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);
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;
60 ALTER TABLE civicrm_word_replacement
61 ALTER COLUMN `is_active` SET DEFAULT 1;
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);
67 SELECT @option_group_id_soft_credit_type := max(id) from civicrm_option_group where name = 'soft_credit_type';
69 INSERT INTO `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `weight`, `is_default`, `is_active`, `is_reserved`)
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);
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.';
86 SELECT @sct_pcp_id := value from civicrm_option_value where name = 'pcp' and option_group_id = @option_group_id_soft_credit_type;
88 UPDATE `civicrm_contribution_soft`
89 SET soft_credit_type_id = @sct_pcp_id
90 WHERE pcp_id IS NOT NULL;
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';
96 UPDATE `civicrm_option_value`
98 WHERE is_reserved = 0 AND option_group_id = @option_group_id_activity_type AND component_id = @caseCompId;
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.';
105 civicrm_option_group (name, {localize field='title'}title{/localize}, is_reserved, is_active)
107 ('msg_mode', {localize}'{ts escape="sql"}Message Mode{/ts}'{/localize}, 1, 1);
109 SELECT @option_group_id_msg_mode := max(id) from civicrm_option_group where name = 'msg_mode';
112 civicrm_option_value (option_group_id, {localize field='label'}`label`{/localize}, value, name, is_default, weight, is_reserved, is_active)
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);
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;
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`)
125 ('honoree_individual', 'Individual, Contact', {localize}'{ts escape="sql"}Honoree Individual{/ts}'{/localize}, 0, 1);
127 SELECT @uf_group_id_honoree_individual := id from civicrm_uf_group where name = 'honoree_individual';
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)
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');
137 ALTER TABLE `civicrm_uf_join`
138 ADD COLUMN `module_data` longtext COMMENT 'Json serialized array of data used by the ufjoin.module';
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};
146 ALTER TABLE civicrm_contribution_page DROP honor_block_title;
147 ALTER TABLE civicrm_contribution_page DROP honor_block_text;
149 ALTER TABLE civicrm_contribution_page DROP honor_block_is_active;
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;
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;
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;
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`)
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);
170 -- participant status adding
171 SELECT @participant_status_wt := max(id) from civicrm_participant_status_type;
173 INSERT INTO civicrm_participant_status_type (name, {localize field='label'}label{/localize}, class, is_reserved, is_active, is_counted, weight, visibility_id)
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);
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';
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`)
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);
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/%';
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.';
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";
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.';
253 -- Add "developer" help menu
254 SELECT @parent_id := `id` FROM `civicrm_navigation` WHERE `name` = 'Help' AND `domain_id` = {$domainID};
256 INSERT INTO civicrm_navigation
257 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
259 ( {$domainID}, NULL, '{ts escape="sql" skip="true"}Developer{/ts}', 'Developer', 'administer CiviCRM', '', @parent_id, '1', NULL, 5 );
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 )
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 );
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;
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`);
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';
283 INSERT INTO civicrm_state_province (country_id, abbreviation, name) VALUES (1085, "61", "Pieria");
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';
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');
292 CREATE TABLE IF NOT EXISTS `civicrm_system_log` (
293 `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: ID.',
294 `message` VARCHAR(128) NOT NULL COMMENT 'Standardized message',
295 `context` LONGTEXT NULL COMMENT 'JSON encoded data',
296 `level` VARCHAR(9) NOT NULL DEFAULT 'info' COMMENT 'error level per PSR3',
297 `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of when event occurred.',
298 `contact_id` INT(11) NULL DEFAULT NULL COMMENT 'Optional Contact ID that created the log. Not an FK as we keep this regardless',
299 hostname VARCHAR(128) NOT NULL COMMENT 'Optional Name of logging host',
301 INDEX `message` (`message`),
302 INDEX `contact_id` (`contact_id`),
303 INDEX `level` (`level`)
305 COMMENT='Table that contains logs of all system events.'
306 COLLATE='utf8_general_ci';
308 -- CRM-14473 civicrm_case_type table creation and migration
309 CREATE TABLE IF NOT EXISTS `civicrm_case_type` (
310 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Autoincremented type id',
311 `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Machine name for Case Type',
312 {localize field='title'}title varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Natural language name for Case Type'{/localize},
313 {localize field='description'}description varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Description of the Case Type'{/localize},
314 `is_active` tinyint(4) DEFAULT NULL COMMENT 'Is this entry active?',
315 `is_reserved` tinyint(4) DEFAULT NULL COMMENT 'Is this case type a predefined system type?',
316 `weight` int(11) NOT NULL DEFAULT '1' COMMENT 'Ordering of the case types',
317 `xml_definition` blob COMMENT 'xml definition of case type',
319 UNIQUE KEY `case_type_name` (`name`)
320 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
322 SELECT @option_group_id_case_type := max(id) from civicrm_option_group where name = 'case_type';
324 INSERT IGNORE INTO civicrm_case_type
325 (name, {localize field='title'}title{/localize}, {localize field='description'}description{/localize}, is_active, is_reserved, weight)
328 {localize field='label'}label{/localize},
329 {localize field='description'}description{/localize},
333 FROM civicrm_option_value
335 option_group_id = @option_group_id_case_type;
337 -- Replace the pseudo-fk to ov.value with a reference to civicrm_case_type.id
340 SELECT civicrm_case_type.id FROM civicrm_case_type
341 JOIN civicrm_option_value
342 ON civicrm_option_value.name = civicrm_case_type.name
344 civicrm_option_value.option_group_id = @option_group_id_case_type
345 AND civicrm_option_value.value = replace(civicrm_case.case_type_id, 0x01, '')
348 ALTER TABLE civicrm_case
349 MODIFY case_type_id int(10) unsigned COLLATE utf8_unicode_ci NULL COMMENT 'FK to civicrm_case_type.id',
350 ADD CONSTRAINT FK_civicrm_case_case_type_id FOREIGN KEY (case_type_id) REFERENCES civicrm_case_type (id) ON DELETE SET NULL;
352 DELETE FROM civicrm_option_value WHERE option_group_id = @option_group_id_case_type;
354 DELETE FROM civicrm_option_group WHERE id = @option_group_id_case_type;
358 {foreach from=$locales item=locale}
359 ALTER TABLE civicrm_survey ADD title_{$locale} varchar(255);
360 UPDATE civicrm_survey SET title_{$locale} = title;
362 ALTER TABLE civicrm_survey ADD instructions_{$locale} TEXT;
363 UPDATE civicrm_survey SET instructions_{$locale} = instructions;
366 ALTER TABLE civicrm_survey DROP title;
367 ALTER TABLE civicrm_survey DROP instructions;
370 -- CRM-11182 -- Make event confirmation page optional
371 ALTER TABLE civicrm_event
372 ADD COLUMN is_confirm_enabled tinyint(4) DEFAULT '1';
375 SET is_confirm_enabled = 1
376 WHERE is_monetary = 1;
379 SET is_confirm_enabled = 0
380 WHERE is_monetary = 0;
383 ALTER TABLE civicrm_event
384 ADD COLUMN dedupe_rule_group_id int(10) unsigned DEFAULT NULL COMMENT 'Rule to use when matching registrations for this event',
385 ADD CONSTRAINT `FK_civicrm_event_dedupe_rule_group_id` FOREIGN KEY (`dedupe_rule_group_id`) REFERENCES `civicrm_dedupe_rule_group` (`id`);
388 SELECT @option_web_id := id FROM civicrm_option_group WHERE name = 'website_type';
390 SELECT @website_default := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and is_default = 1;
392 SELECT @website_work := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and name= 'Work';
394 UPDATE civicrm_option_value
395 SET is_default = 1 WHERE option_group_id = @option_web_id and value = IFNULL(@website_default , @website_work);
397 SELECT @website_default := value FROM civicrm_option_value WHERE option_group_id = @option_web_id and is_default = 1;
399 ALTER TABLE civicrm_uf_field
400 ADD COLUMN `website_type_id` int(10) unsigned DEFAULT NULL COMMENT 'Website Type Id, if required' AFTER phone_type_id,
401 ADD INDEX `IX_website_type_id` (`website_type_id`);
403 UPDATE civicrm_uf_field
404 SET website_type_id = @website_default,
405 field_name = 'url' WHERE field_name LIKE 'url%';
407 SELECT @website_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_web_id;
408 SELECT @website_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_web_id;
410 INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}label{/localize}, name, value, weight)
411 SELECT @option_web_id, {localize}website{/localize}, website, (@website_value := @website_value + 1), (@website_weight := @website_weight + 1) FROM (
412 SELECT 'Google+' AS website
414 SELECT 'Instagram' AS website
416 SELECT 'LinkedIn' AS website
418 SELECT 'Pinterest' AS website
420 SELECT 'Tumblr' AS website
422 SELECT 'SnapChat' AS website
424 SELECT 'Vine' AS website
426 LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.website)
427 AND option_group_id = @option_web_id
430 -- CRM-14627 civicrm navigation inconsistent
431 UPDATE civicrm_navigation
432 SET civicrm_navigation.url = CONCAT(SUBSTRING(url FROM 1 FOR LOCATE('&', url) - 1), '?', SUBSTRING(url FROM LOCATE('&', url) + 1))
433 WHERE civicrm_navigation.url LIKE "%&%" AND civicrm_navigation.url NOT LIKE "%?%";
437 SELECT @option_grant_status := id FROM civicrm_option_group WHERE name = 'grant_status';
438 UPDATE civicrm_option_value
443 WHEN lower(name) = 'granted'
445 WHEN lower(name) = 'approved'
447 WHEN lower(name) = 'rejected'
452 {foreach from=$locales item=locale}
455 WHEN lower(name) = 'granted'
457 WHEN lower(name) = 'approved'
459 WHEN lower(name) = 'rejected'
467 WHEN lower(name) = 'granted'
469 WHEN lower(name) = 'approved'
471 WHEN lower(name) = 'rejected'
475 WHERE option_group_id = @option_grant_status and LOWER(name) IN ('granted', 'pending', 'approved', 'rejected');
477 SELECT @grant_value := max(cast(value as UNSIGNED)) FROM civicrm_option_value WHERE option_group_id = @option_grant_status;
478 SELECT @grant_weight := max(weight) FROM civicrm_option_value WHERE option_group_id = @option_grant_status;
480 INSERT INTO civicrm_option_value(option_group_id, {localize field='label'}label{/localize}, name, value, weight)
481 SELECT @option_grant_status, {localize}grantstatus{/localize}, grantstatus, @grant_value := @grant_value + 1, @grant_weight := @grant_weight + 1 FROM (
482 SELECT 'Submitted' AS grantstatus
484 SELECT 'Approved for Payment' AS grantstatus
486 SELECT 'Eligible' AS grantstatus
488 SELECT 'Awaiting Information' AS grantstatus
490 SELECT 'Withdrawn' AS grantstatus
492 LEFT JOIN civicrm_option_value co ON LOWER(co.name) = LOWER(temp.grantstatus)
493 AND option_group_id = @option_grant_status
496 -- Fix trailing single quote in grant status label
498 UPDATE civicrm_option_value v
499 INNER JOIN civicrm_option_group g
500 ON v.option_group_id = g.id AND g.name = 'grant_status'
501 SET label = 'Awaiting Information'
502 WHERE v.label = 'Awaiting Information\'' and v.name = 'Awaiting Information';
504 UPDATE civicrm_option_value v
505 INNER JOIN civicrm_option_group g
506 ON v.option_group_id = g.id AND g.name = 'grant_status'
508 {foreach from=$locales item=locale}
509 v.label_{$locale} = CASE
510 WHEN v.label_{$locale} = 'Awaiting Information\'' THEN 'Awaiting Information'
511 ELSE v.label_{$locale}
515 WHERE v.name = 'Awaiting Information';