2 -- modify type fee_level of civicrm_participant and amount_level of civicrm_contribution
4 ALTER TABLE civicrm_participant MODIFY column fee_level text collate utf8_unicode_ci default NULL COMMENT 'Populate with the label (text) associated with a fee level for paid events with multiple levels. Note that we store the label value and not the key';
6 ALTER TABLE civicrm_contribution MODIFY column amount_level text collate utf8_unicode_ci default NULL;
8 -- subtype upgrade TODOs:
9 -- make changes for CRM-4970
11 -- modify contact_type column definition
12 ALTER TABLE `civicrm_contact` MODIFY column contact_type varchar(64) collate utf8_unicode_ci DEFAULT NULL COMMENT 'Type of Contact';
14 -- add table definiton and data for civicrm_contact_type table
15 CREATE TABLE civicrm_contact_type (
16 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contact Type ID',
17 name varchar(64) COMMENT 'Internal name of Contact Type (or Subtype).',
18 {localize field='label'}label varchar(64) COMMENT ' localized Name of Contact Type.'{/localize},
19 {localize field='description'}description text COMMENT ' localized Optional verbose description of the type.'{/localize},
20 image_URL varchar(255) COMMENT 'URL of image if any.',
21 parent_id int unsigned COMMENT 'Optional FK to parent contact type.',
22 is_active tinyint COMMENT 'Is this entry active?',
23 is_reserved tinyint COMMENT 'Is this contact type a predefined system type',
25 UNIQUE INDEX contact_type(name),
26 CONSTRAINT FK_civicrm_contact_type_parent_id FOREIGN KEY (parent_id) REFERENCES civicrm_contact_type(id)
27 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
29 INSERT INTO civicrm_contact_type
30 ( id, name,{localize field='label'}`label`{/localize},image_URL, parent_id, is_active,is_reserved )
32 (1, 'Individual', {localize}'Individual'{/localize}, NULL, NULL, 1,1),
33 (2, 'Household', {localize}'Household'{/localize}, NULL, NULL, 1,1),
34 (3, 'Organization', {localize}'Organization'{/localize},NULL, NULL, 1,1);
38 -- added menu for contact types in navigation
39 SELECT @domain_id := min(id) FROM civicrm_domain;
40 SELECT @nav_ol := id FROM civicrm_navigation WHERE name = 'Option Lists';
41 SELECT @nav_ol_wt := max(weight) from civicrm_navigation WHERE parent_id = @nav_ol;
42 INSERT INTO `civicrm_navigation`
43 ( domain_id, url, label, name,permission, permission_operator, parent_id, is_active, has_separator, weight )
45 ( @domain_id,'civicrm/admin/options/subtype&reset=1', 'Contact Types', 'Contact Types', 'administer CiviCRM', '', @nav_ol, '1', NULL, @nav_ol_wt+1 );
47 -- make changes for CRM-5100
48 ALTER TABLE `civicrm_relationship_type`
49 ADD `contact_sub_type_a` varchar(64) collate utf8_unicode_ci DEFAULT NULL AFTER `contact_type_b`,
50 ADD `contact_sub_type_b` varchar(64) collate utf8_unicode_ci DEFAULT NULL AFTER `contact_sub_type_a`;
52 -- Upgrade FCKEditor to CKEditor CRM-5226
54 {foreach from=$locales item=locale}
55 UPDATE civicrm_option_value SET label_{$locale} ='CKEditor' WHERE label_{$locale} = 'FCKEditor';
58 UPDATE civicrm_option_value SET label ='CKEditor' WHERE label = 'FCKEditor';
61 -- Added Autocomplete search options in civicrm_preferences 'Admin Search Settings' form
63 ALTER TABLE `civicrm_preferences`
64 ADD `contact_autocomplete_options` VARCHAR( 255 ) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'What Autocomplete has to return';
66 -- Added default value checked for sort_name and email
67 UPDATE `civicrm_preferences` SET `contact_autocomplete_options` = '
\ 11
\ 12
\ 1' WHERE `civicrm_preferences`.`id` =1 LIMIT 1;
69 -- Insert values for option group
71 `civicrm_option_group` (`name`, {localize field='description'}`description`{/localize}, `is_reserved`, `is_active`)
73 ('contact_autocomplete_options', {localize}'Autocomplete Contact Search'{/localize} , 0, 1);
75 SELECT @option_group_id_acsOpt := max(id) from civicrm_option_group where name = 'contact_autocomplete_options';
77 INSERT INTO `civicrm_option_value`
78 (`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`)
80 (@option_group_id_acsOpt, {localize}'Email Address'{/localize} , 2, 'email', NULL, 0, NULL, 2, 0, 0, 1, NULL, NULL),
81 (@option_group_id_acsOpt, {localize}'Phone'{/localize} , 3, 'phone', NULL, 0, NULL, 3, 0, 0, 1, NULL, NULL),
82 (@option_group_id_acsOpt, {localize}'Street Address'{/localize} , 4, 'street_address', NULL, 4, NULL, 0, 0, 0, 1, NULL, NULL),
83 (@option_group_id_acsOpt, {localize}'City'{/localize} , 5, 'city', NULL, 0, NULL, 5, 0, 0, 1, NULL, NULL),
84 (@option_group_id_acsOpt, {localize}'State/Province'{/localize} , 6, 'state_province', NULL, 6, NULL, 0, 0, 0, 1, NULL, NULL),
85 (@option_group_id_acsOpt, {localize}'Country'{/localize} , 7, 'country', NULL, 0, NULL, 7, 0, 0, 1, NULL, NULL);
88 ALTER TABLE `civicrm_price_set`
89 ADD `extends` VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL COMMENT 'What components are using this price set?';
91 UPDATE `civicrm_price_set` SET extends = (select id from civicrm_component where name = 'CiviEvent');
94 ALTER TABLE `civicrm_acl`
95 MODIFY `operation` enum('All', 'View', 'Edit', 'Create', 'Delete', 'Grant', 'Revoke', 'Search') COLLATE utf8_unicode_ci NOT NULL COMMENT 'What operation does this ACL entry control?';
98 UPDATE civicrm_state_province SET name = 'Haifa' where id = 3115;
101 UPDATE civicrm_state_province SET name = 'Jerusalem' where id = 3117;
104 /* Set references to obsolete UK counties to NULL */
105 UPDATE `civicrm_address` SET `state_province_id` = NULL WHERE `state_province_id` IN
106 ('2596', '2599', '2600', '2601', '2602', '2603', '2604', '2605', '2607', '2608', '2609', '2610', '2611',
107 '2613', '2614', '2615', '2616', '2617', '2618', '2619', '2621', '2623', '2624', '2625', '2627', '2628',
108 '2629', '2630', '2631', '2632', '2633', '2636', '2637', '2638', '2640', '2641', '2642', '2644', '2645',
109 '2646', '2650', '2653', '2656', '2658', '2667', '2672', '2673', '2676', '2677', '2679', '2680', '2681',
110 '2683', '2684', '2685', '2686', '2690', '2691', '2693', '2695', '2696', '2697', '2698', '2700', '2701',
111 '2701', '2702', '2703', '2704', '2706', '2707', '2708', '2710', '2711', '2713', '2714', '2716', '2717',
112 '2719', '2720', '2721', '2722', '2724', '2725', '2727', '2728', '2729', '2730', '2731', '2732', '2733',
113 '2736', '2737', '2739', '2740', '2741', '2745', '2751', '2753', '2754', '2755', '2756', '2758', '2759',
114 '2760', '2762', '2763', '2764', '2765', '2767', '2768', '2769', '2771', '2772', '2775', '2776', '2781',
115 '2782', '2783', '2784', '2787', '2788', '2789', '2790', '2792', '2794', '2795', '2796', '2797', '2798',
116 '2799', '2800', '2801', '2802', '2803', '2805', '2806', '2807', '2808', '2809', '2810', '2816', '2817',
117 '2819', '2820', '2821', '2822', '2824', '2825', '9987', '9995', '9996', '9997', '2812', '2718', '2715' );
119 /* Delete obsolete UK counties */
120 DELETE FROM `civicrm_state_province` WHERE `id` IN
121 ('2596', '2599', '2600', '2601', '2602', '2603', '2604', '2605', '2607', '2608', '2609', '2610', '2611',
122 '2613', '2614', '2615', '2616', '2617', '2618', '2619', '2621', '2623', '2624', '2625', '2627', '2628',
123 '2629', '2630', '2631', '2632', '2633', '2636', '2637', '2638', '2640', '2641', '2642', '2644', '2645',
124 '2646', '2650', '2653', '2656', '2658', '2667', '2672', '2673', '2676', '2677', '2679', '2680', '2681',
125 '2683', '2684', '2685', '2686', '2690', '2691', '2693', '2695', '2696', '2697', '2698', '2700', '2701',
126 '2701', '2702', '2703', '2704', '2706', '2707', '2708', '2710', '2711', '2713', '2714', '2716', '2717',
127 '2719', '2720', '2721', '2722', '2724', '2725', '2727', '2728', '2729', '2730', '2731', '2732', '2733',
128 '2736', '2737', '2739', '2740', '2741', '2745', '2751', '2753', '2754', '2755', '2756', '2758', '2759',
129 '2760', '2762', '2763', '2764', '2765', '2767', '2768', '2769', '2771', '2772', '2775', '2776', '2781',
130 '2782', '2783', '2784', '2787', '2788', '2789', '2790', '2792', '2794', '2795', '2796', '2797', '2798',
131 '2799', '2800', '2801', '2802', '2803', '2805', '2806', '2807', '2808', '2809', '2810', '2816', '2817',
132 '2819', '2820', '2821', '2822', '2824', '2825', '9987', '9995', '9996', '9997', '2812', '2718', '2715' );
134 /* Update the names of several existing UK counties */
135 UPDATE `civicrm_state_province` SET `name`='Gwent' WHERE `id`='2612';
136 UPDATE `civicrm_state_province` SET `name`='Bristol, City of' WHERE `id`='2620';
137 UPDATE `civicrm_state_province` SET `name`='Co Londonderry' WHERE `id`='2648';
138 UPDATE `civicrm_state_province` SET `name`='Na h-Eileanan Siar' WHERE `id`='2666';
139 UPDATE `civicrm_state_province` SET `name`='Glasgow City' WHERE `id`='2674';
140 UPDATE `civicrm_state_province` SET `name`='Mid Glamorgan' WHERE `id`='2804';
141 UPDATE `civicrm_state_province` SET `name`='Greater London' WHERE `id`='9999';
142 UPDATE `civicrm_state_province` SET `name`='County Durham' WHERE `id`='2657';
144 /* Create additional UK counties */
145 INSERT INTO `civicrm_state_province`
146 (id, `name`, `abbreviation`, `country_id`)
148 (10013, 'Clwyd', 'CLD', 1226),
149 (10014, 'Dyfed', 'DFD', 1226),
150 (10015, 'South Glamorgan', 'GNS', 1226);
153 SELECT @domain_id := min(id) FROM civicrm_domain;
154 SELECT @nav_contrbutionID := id FROM civicrm_navigation WHERE name = 'Contributions';
155 SELECT @nav_contribution_wt := max(weight) from civicrm_navigation WHERE parent_id = @nav_contrbutionID;
157 UPDATE civicrm_navigation
158 SET has_separator = 1
159 WHERE civicrm_navigation.parent_id= @nav_contrbutionID AND civicrm_navigation.weight = @nav_contribution_wt;
161 INSERT INTO civicrm_navigation
162 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
164 ( @domain_id, 'civicrm/admin/price&reset=1&action=add', '{ts escape="sql"}New Price Set{/ts}', 'New Price Set', 'access CiviContribute,administer CiviCRM', 'AND', @nav_contrbutionID, '1', NULL, @nav_contribution_wt+1 ),
165 ( @domain_id, 'civicrm/admin/price&reset=1', '{ts escape="sql"}Manage Price Sets{/ts}', 'Manage Price Sets', 'access CiviContribute,administer CiviCRM', 'AND', @nav_contrbutionID, '1', NULL, @nav_contribution_wt+2 );
168 SELECT @nav_contrbutionID_admin := id FROM civicrm_navigation WHERE name = 'CiviContribute';
169 SELECT @nav_contribution_wt_admin := max(weight) from civicrm_navigation WHERE parent_id = @nav_contrbutionID_admin;
171 UPDATE civicrm_navigation
172 SET has_separator = 1
173 WHERE civicrm_navigation.parent_id= @nav_contrbutionID_admin AND civicrm_navigation.weight = @nav_contribution_wt_admin;
175 INSERT INTO civicrm_navigation
176 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
178 ( @domain_id, 'civicrm/admin/price&reset=1&action=add', '{ts escape="sql"}New Price Set{/ts}', 'New Price Set', 'access CiviContribute,administer CiviCRM', 'AND', @nav_contrbutionID_admin, '1', NULL, @nav_contribution_wt_admin+1 ),
179 ( @domain_id, 'civicrm/admin/price&reset=1', '{ts escape="sql"}Manage Price Sets{/ts}', 'Manage Price Sets', 'access CiviContribute,administer CiviCRM', 'AND', @nav_contrbutionID_admin, '1', NULL, @nav_contribution_wt_admin+2 );
182 SELECT @nav_customize_admin := id FROM civicrm_navigation WHERE name = 'Customize';
183 SELECT @nav_customize_wt_admin := max(weight) from civicrm_navigation WHERE parent_id = @nav_customize_admin;
184 INSERT INTO civicrm_navigation
185 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
187 ( @domain_id, 'civicrm/admin/price&reset=1', '{ts escape="sql"}Price Sets{/ts}', 'Price Sets', 'administer CiviCRM', '', @nav_customize_admin, '1', NULL, @nav_customize_wt_admin+1 );
189 UPDATE civicrm_navigation
190 SET url = 'civicrm/admin/price&reset=1'
191 WHERE civicrm_navigation.url = 'civicrm/event/price&reset=1';
193 CREATE TABLE civicrm_acl_contact_cache (
194 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
195 user_id int unsigned COMMENT 'FK to civicrm_contact (could be null for anon user)',
196 contact_id int unsigned NOT NULL COMMENT 'FK to civicrm_contact',
197 operation enum('All', 'View', 'Edit', 'Create', 'Delete', 'Grant', 'Revoke') NOT NULL COMMENT 'What operation does this user have permission on?'
200 UNIQUE INDEX UI_user_contact_operation(user_id, contact_id, operation ) ,
201 CONSTRAINT FK_civicrm_acl_contact_cache_user_id FOREIGN KEY (user_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE,
202 CONSTRAINT FK_civicrm_acl_contact_cache_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE
203 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
206 INSERT INTO civicrm_payment_processor_type
207 ( name, title, description, is_active, is_default, user_name_label, password_label, signature_label, subject_label, class_name, url_site_default, url_api_default, url_recur_default, url_button_default, url_site_test_default, url_api_test_default, url_recur_test_default, url_button_test_default, billing_mode, is_recur, payment_type)
209 ( 'Realex', 'Realex Payment', NULL, 1, 0, 'Merchant ID', 'Password', NULL, 'Account', 'Payment_Realex', 'https://epage.payandshop.com/epage.cgi', NULL, NULL, NULL, 'https://epage.payandshop.com/epage-remote.cgi', NULL, NULL, NULL, 1, 0, 1);
212 UPDATE civicrm_payment_processor_type
213 SET url_recur_default = 'https://www.paypal.com/',
214 url_recur_test_default = 'https://www.sandbox.paypal.com/',
216 WHERE name = 'PayPal';
218 UPDATE civicrm_payment_processor
220 url_recur = 'https://www.paypal.com/'
221 WHERE payment_processor_type = 'PayPal' AND is_test = 0;
223 UPDATE civicrm_payment_processor
225 url_recur = 'https://www.sandbox.paypal.com/'
226 WHERE payment_processor_type = 'PayPal' AND is_test = 1;
229 -- migrate the contribution id's to participant id's in lineitem table
230 UPDATE civicrm_line_item AS li
231 LEFT JOIN civicrm_participant_payment AS pp ON (pp.contribution_id = li.entity_id)
232 SET li.entity_id = pp.participant_id,
233 li.entity_table = 'civicrm_participant'
234 WHERE pp.contribution_id = li.entity_id AND
235 li.entity_table = 'civicrm_contribution';
238 -- copy name to value, since we want to use value instead of name.
239 UPDATE civicrm_option_value as vals
240 LEFT JOIN civicrm_option_group as groups ON ( groups.id = vals.option_group_id )
241 INNER JOIN civicrm_price_field fields ON ( groups.name = CONCAT( 'civicrm_price_field.amount.', fields.id ) )
242 SET vals.value=vals.name;
245 ALTER TABLE civicrm_mail_settings
246 ADD `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this match entry for' AFTER id;
248 UPDATE civicrm_mail_settings SET domain_id = @domain_id;
250 -- Date migration CRM-4312
251 ALTER TABLE civicrm_custom_field
252 CHANGE date_parts date_format VARCHAR( 64 ) NULL DEFAULT NULL COMMENT 'date format for custom date',
253 ADD time_format int unsigned COMMENT 'time format for custom date' AFTER date_format;
255 ALTER TABLE `civicrm_preferences_date`
256 CHANGE `format` `date_format` VARCHAR( 64 ) NULL DEFAULT NULL COMMENT 'The date type',
257 ADD `time_format` VARCHAR( 64 ) NULL DEFAULT NULL COMMENT 'store time format' AFTER `date_format`;
258 ALTER TABLE `civicrm_preferences_date`
259 DROP `minute_increment`;
261 DELETE FROM civicrm_preferences_date WHERE name IN ('datetime','duration','fixed','manual' );
263 UPDATE civicrm_preferences_date
265 WHERE name IN ( 'activityDate', 'activityDatetime', 'birth', 'custom', 'mailing', 'relative' );
267 UPDATE civicrm_preferences_date SET name = 'searchDate' WHERE name = 'relative';
268 UPDATE civicrm_preferences_date SET name = 'activityDateTime' WHERE name = 'activityDatetime';
269 UPDATE civicrm_preferences_date SET time_format = 1 WHERE name = 'activityDatetime';
271 ALTER TABLE civicrm_country
272 ADD is_province_abbreviated tinyint(4) default '0' COMMENT 'Should state/province be displayed as abbreviation for contacts from this country?';
274 -- message templates, CRM-3507
275 ALTER TABLE civicrm_msg_template
276 CHANGE msg_subject msg_subject TEXT NULL COMMENT 'Subject for email message.',
277 ADD workflow_id int(10) unsigned default NULL COMMENT 'a pseudo-FK to civicrm_option_value AFTER is_active',
278 ADD is_default tinyint(4) default '1' COMMENT 'is this the default message template for the workflow referenced by workflow_id?' AFTER workflow_id,
279 ADD is_reserved tinyint(4) default NULL COMMENT 'is this the reserved message template which we ship for the workflow referenced by workflow_id?' AFTER is_default;
281 {include file='../CRM/Upgrade/3.1.alpha1.msg_template/civicrm_msg_template.tpl'}