projects / civicrm-core.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
Merge pull request #4054 from eileenmcnaughton/CRM-15237
[civicrm-core.git] / CRM / Upgrade / Incremental / sql / 2.2.alpha1.mysql.tpl
CommitLineData
6a488035
TO		 1-- /************************************************************************
2-- *
3-- * MySQL Script for civicrm database/tables - upgradation from 2.1 -> 2.2
4-- *
5-- *************************************************************************/
6
10824d34 7-- Please add script for all the schema / fixed-data related modifications to
8-- this sql script as you resolve 2.2 issues. Include the issue number which
6a488035
TO		 9-- is the source of the change, as part of the comment.
10
11-- fix version column first
10824d34 12ALTER TABLE `civicrm_domain`
6a488035
TO		 13 MODIFY version varchar(32) COMMENT 'The civicrm version this instance is running';
14
10824d34 15-- make the register_by_id cascade in civicrm_participant
6a488035
TO		 16
17ALTER TABLE `civicrm_participant`
18 DROP FOREIGN KEY `FK_civicrm_participant_registered_by_id`;
19ALTER TABLE `civicrm_participant`
20 ADD CONSTRAINT `FK_civicrm_participant_registered_by_id` FOREIGN KEY (`registered_by_id`) REFERENCES `civicrm_participant` (`id`) ON DELETE CASCADE;
21
22
23-- merge civicrm_event_page to civicrm_event
24
25ALTER TABLE `civicrm_event`
26 {if $multilingual}
27 {foreach from=$locales item=locale}
28 ADD `intro_text_{$locale}` text collate utf8_unicode_ci,
29 ADD `footer_text_{$locale}` text collate utf8_unicode_ci,
30 ADD `confirm_title_{$locale}` varchar(255) collate utf8_unicode_ci default NULL,
31 ADD `confirm_text_{$locale}` text collate utf8_unicode_ci,
32 ADD `confirm_footer_text_{$locale}` text collate utf8_unicode_ci,
33 ADD `confirm_email_text_{$locale}` text collate utf8_unicode_ci,
34 ADD `confirm_from_name_{$locale}` varchar(255) collate utf8_unicode_ci default NULL,
35 ADD `thankyou_title_{$locale}` varchar(255) collate utf8_unicode_ci default NULL,
36 ADD `thankyou_text_{$locale}` text collate utf8_unicode_ci,
37 ADD `thankyou_footer_text_{$locale}` text collate utf8_unicode_ci,
38 ADD `pay_later_text_{$locale}` text collate utf8_unicode_ci,
39 ADD `pay_later_receipt_{$locale}` text collate utf8_unicode_ci,
40 {/foreach}
41 {else}
42 ADD `intro_text` text collate utf8_unicode_ci COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.',
43 ADD `footer_text` text collate utf8_unicode_ci COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.',
44 ADD `confirm_title` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'Title for Confirmation page.',
45 ADD `confirm_text` text collate utf8_unicode_ci COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.',
46 ADD `confirm_footer_text` text collate utf8_unicode_ci COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.',
47 ADD `confirm_email_text` text collate utf8_unicode_ci COMMENT 'text to include above standard event info on confirmation email. emails are text-only, so do not allow html for now',
48 ADD `confirm_from_name` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'FROM email name used for confirmation emails.',
49 ADD `thankyou_title` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'Title for ThankYou page.',
50 ADD `thankyou_text` text collate utf8_unicode_ci COMMENT 'ThankYou Text.',
51 ADD `thankyou_footer_text` text collate utf8_unicode_ci COMMENT 'Footer message.',
52 ADD `pay_later_text` text collate utf8_unicode_ci COMMENT 'The text displayed to the user in the main form',
53 ADD `pay_later_receipt` text collate utf8_unicode_ci COMMENT 'The receipt sent to the user instead of the normal receipt text',
54 {/if}
55 ADD `is_email_confirm` tinyint(4) default '0' COMMENT 'If true, confirmation is automatically emailed to contact on successful registration.',
56 ADD `confirm_from_email` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'FROM email address used for confirmation emails.',
57 ADD `cc_confirm` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'comma-separated list of email addresses to cc each time a confirmation is sent',
58 ADD `bcc_confirm` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'comma-separated list of email addresses to bcc each time a confirmation is sent',
59 ADD `default_fee_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_option_value.',
60 ADD `default_discount_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_option_value.',
61 ADD `is_pay_later` tinyint(4) default '0' COMMENT 'if true - allows the user to send payment directly to the org later',
62 ADD `is_multiple_registrations` tinyint(4) default '0' COMMENT 'if true - allows the user to register multiple participants for event',
63 ALTER `max_participants` DROP DEFAULT;
64
65UPDATE civicrm_event ce
66LEFT JOIN civicrm_event_page cp ON ce.id=cp.event_id
67SET
68 {if $multilingual}
69 {foreach from=$locales item=locale}
70 ce.intro_text_{$locale} = cp.intro_text_{$locale},
71 ce.footer_text_{$locale} = cp.footer_text_{$locale},
10824d34 72 ce.confirm_title_{$locale} = cp.confirm_title_{$locale},
6a488035
TO		 73 ce.confirm_text_{$locale} = cp.confirm_text_{$locale},
74 ce.confirm_footer_text_{$locale} = cp.confirm_footer_text_{$locale},
75 ce.confirm_email_text_{$locale} = cp.confirm_email_text_{$locale},
76 ce.confirm_from_name_{$locale} = cp.confirm_from_name_{$locale},
77 ce.thankyou_title_{$locale} = cp.thankyou_title_{$locale},
78 ce.thankyou_text_{$locale} = cp.thankyou_text_{$locale},
79 ce.thankyou_footer_text_{$locale} = cp.thankyou_footer_text_{$locale},
80 ce.pay_later_text_{$locale} = cp.pay_later_text_{$locale},
81 ce.pay_later_receipt_{$locale} = cp.pay_later_receipt_{$locale},
82 {/foreach}
83 {else}
84 ce.intro_text = cp.intro_text,
85 ce.footer_text = cp.footer_text,
10824d34 86 ce.confirm_title = cp.confirm_title,
6a488035
TO		 87 ce.confirm_text = cp.confirm_text,
88 ce.confirm_footer_text = cp.confirm_footer_text,
89 ce.confirm_email_text = cp.confirm_email_text,
90 ce.confirm_from_name = cp.confirm_from_name,
91 ce.thankyou_title = cp.thankyou_title,
92 ce.thankyou_text = cp.thankyou_text,
93 ce.thankyou_footer_text = cp.thankyou_footer_text,
94 ce.pay_later_text = cp.pay_later_text,
95 ce.pay_later_receipt = cp.pay_later_receipt,
96 {/if}
97 ce.is_email_confirm = cp.is_email_confirm,
98 ce.confirm_from_email = cp.confirm_from_email,
99 ce.cc_confirm = cp.cc_confirm,
100 ce.bcc_confirm = cp.bcc_confirm,
101 ce.default_fee_id = cp.default_fee_id,
102 ce.default_discount_id = cp.default_discount_id,
103 ce.is_pay_later = cp.is_pay_later,
104 ce.is_multiple_registrations = cp.is_multiple_registrations;
105
106-- CRM-3391
10824d34 107-- Update table name in civicrm_option_group (Fee Level)
6a488035
TO		 108-- cleanup for unused option group
109
10824d34 110DELETE og.* FROM civicrm_option_group og
111 LEFT JOIN civicrm_event_page ep ON ep.id = SUBSTRING_INDEX( SUBSTRING( og.name, 27 ) , '.discount', 1)
6a488035
TO		 112 WHERE og.name LIKE 'civicrm_event_page.amount%' AND ep.id IS NULL;
113
114{foreach from =$eventFees item=ogid}
115
116 SELECT @option_group_id := id ,@event_page_id := SUBSTRING_INDEX( SUBSTRING( name, 27 ) , '.discount', 1 )
117 FROM civicrm_option_group WHERE `name` LIKE 'civicrm_event_page.amount%' LIMIT 1;
118
119 SELECT @event_id := ep.event_id FROM civicrm_event_page ep WHERE ep.id = @event_page_id;
120
121 UPDATE `civicrm_option_group`
10824d34 122 SET `name` = REPLACE( name, CONCAT_WS('.', 'civicrm_event_page.amount', @event_page_id ), CONCAT_WS('.', 'civicrm_event.amount', @event_id ) )
6a488035
TO		 123 WHERE `id` = @option_group_id;
124{/foreach}
125
10824d34 126-- Update table entity_table and entity_id civicrm_tell_friend
6a488035
TO		 127UPDATE civicrm_tell_friend tf
128 SET tf.`entity_table` = 'civicrm_event',
129 tf.`entity_id` = (SELECT e.`event_id` FROM civicrm_event_page e WHERE e.`id` = tf.`entity_id`)
130 WHERE tf.`entity_table`='civicrm_event_page' ;
131
132--CRM-4256
10824d34 133-- Update table name in civicrm_price_set_entity (Price Set)
6a488035
TO		 134UPDATE civicrm_price_set_entity pse
135 SET pse.`entity_table`='civicrm_event',
136 pse.`entity_id` = (SELECT e.`event_id` FROM civicrm_event_page e WHERE e.`id` = pse.`entity_id`)
137 WHERE pse.`entity_table`='civicrm_event_page';
138
139-- /*******************************************************
140-- *
141-- * Drop civicrm_event_page table
142-- *
143-- *******************************************************/
144
145DROP TABLE civicrm_event_page;
146
147-- CRM-3546
148{if $multilingual}
149 INSERT INTO civicrm_option_group (name, {foreach from=$locales item=locale}description_{$locale},{/foreach} is_reserved, is_active) VALUES ('visibility', {foreach from=$locales item=locale}'Visibility',{/foreach} 0, 1);
150
151{else}
152 INSERT INTO civicrm_option_group (name, description, is_reserved, is_active) VALUES ('visibility', 'Visibility', 0, 1);
153{/if}
154
155SELECT @option_group_id_vis := max(id) from civicrm_option_group where name = 'visibility';
156
157{if $multilingual}
158 INSERT INTO civicrm_option_value
159 (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight) VALUES
160 (@option_group_id_vis, {foreach from=$locales item=locale}'Public',{/foreach} 1, 'public', 1),
161 (@option_group_id_vis, {foreach from=$locales item=locale}'Admin',{/foreach} 2, 'admin', 2);
162{else}
163 INSERT INTO civicrm_option_value
164 (option_group_id, label, value, name, weight) VALUES
165 (@option_group_id_vis, 'Public', 1, 'public', 1),
166 (@option_group_id_vis, 'Admin', 2, 'admin', 2);
167{/if}
168
169ALTER TABLE civicrm_option_value
170 ADD visibility_id int unsigned default NULL;
171
172
173-- * A Personal Campaign Page Block stores admin configurable status options and rules
174
175CREATE TABLE civicrm_pcp_block (
176 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'PCP block Id',
177 entity_table varchar(64) ,
178 entity_id int unsigned NOT NULL COMMENT 'FK to civicrm_contribution_page.id',
179 supporter_profile_id int unsigned DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
180 is_approval_needed tinyint DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
181 is_tellfriend_enabled tinyint DEFAULT NULL COMMENT 'Does Personal Campaign Page allow using tell a friend?',
182 tellfriend_limit int unsigned DEFAULT NULL COMMENT 'Maximum recipient fields allowed in tell a friend',
183 link_text varchar(255) DEFAULT NULL COMMENT 'Link text for PCP.',
184 is_active tinyint DEFAULT 1 COMMENT 'Is Personal Campaign Page Block enabled/active?',
185 PRIMARY KEY ( id ),
10824d34 186 CONSTRAINT FK_civicrm_pcp_block_entity_id FOREIGN KEY (entity_id) REFERENCES civicrm_contribution_page(id)
6a488035
TO		 187) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
188
189-- * civicrm_pcp
190
191CREATE TABLE civicrm_pcp (
192 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Personal Campaign Page ID',
193 contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID',
194 status_id int unsigned NOT NULL ,
195 title varchar(255) DEFAULT NULL ,
196 intro_text text DEFAULT NULL ,
197 page_text text DEFAULT NULL ,
198 donate_link_text varchar(255) DEFAULT NULL ,
199 contribution_page_id int unsigned NOT NULL COMMENT 'The Contribution Page which triggered this pcp',
200 is_thermometer int unsigned DEFAULT 0 ,
201 is_honor_roll int unsigned DEFAULT 0 ,
202 goal_amount decimal(20,2) COMMENT 'Goal amount of this Personal Campaign Page.',
203 referer varchar(255) DEFAULT NULL ,
204 is_active tinyint DEFAULT 0 COMMENT 'Is Personal Campaign Page enabled/active?',
10824d34 205 PRIMARY KEY ( id ),
206 CONSTRAINT FK_civicrm_pcp_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE,
207 CONSTRAINT FK_civicrm_pcp_contribution_page_id FOREIGN KEY (contribution_page_id) REFERENCES civicrm_contribution_page(id)
6a488035
TO		 208) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
209
210-- * civicrm_contribution_soft
211
212CREATE TABLE civicrm_contribution_soft (
213 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Soft Contribution ID',
214 contribution_id int unsigned NOT NULL COMMENT 'FK to contribution table.',
215 contact_id int unsigned NOT NULL COMMENT 'FK to Contact ID',
216 amount decimal(20,2) NOT NULL COMMENT 'Amount of this soft contribution.',
217 pcp_id int unsigned DEFAULT NULL ,
218 pcp_display_in_roll tinyint DEFAULT 0 ,
219 pcp_roll_nickname varchar(255) DEFAULT NULL ,
220 pcp_personal_note varchar(255) DEFAULT NULL ,
221 PRIMARY KEY ( id ) ,
10824d34 222 INDEX index_id( pcp_id ) ,
223 CONSTRAINT FK_civicrm_contribution_soft_contribution_id FOREIGN KEY (contribution_id) REFERENCES civicrm_contribution(id) ON DELETE CASCADE,
224 CONSTRAINT FK_civicrm_contribution_soft_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE
6a488035
TO		 225) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
226
227
228-- fixed for CRM-2105 Greeting Type
229{if $multilingual}
230 INSERT INTO civicrm_option_group (name, {foreach from=$locales item=locale}description_{$locale},{/foreach} is_reserved, is_active) VALUES ('greeting_type', {foreach from=$locales item=locale}'Greeting Type',{/foreach} 0, 1);
231{else}
232 INSERT INTO civicrm_option_group (name, description, is_reserved, is_active ) VALUES ('greeting_type', 'Greeting Type', 0, 1 );
233{/if}
234
235SELECT @option_group_id_gr := max(id) from civicrm_option_group where name = 'greeting_type';
236
237{if $multilingual}
238 INSERT INTO civicrm_option_value
239 (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, is_default, weight, is_reserved) VALUES
240 (@option_group_id_gr, {foreach from=$locales item=locale}'Dear [first]',{/foreach} 1, 'Dear [first]', 1, 1, 0),
241 (@option_group_id_gr, {foreach from=$locales item=locale}'Dear [prefix] [first] [last]',{/foreach} 2, 'Dear [prefix] [first] [last]', 0, 2, 0),
242 (@option_group_id_gr, {foreach from=$locales item=locale}'Dear [prefix] [last]',{/foreach} 3, 'Dear [prefix] [last]', 0, 3, 0),
243 (@option_group_id_gr, {foreach from=$locales item=locale}'Customized',{/foreach} 4, 'Customized', 0, 4, 1);
244{else}
245 INSERT INTO civicrm_option_value
246 (option_group_id, label, value, name, is_default, weight, is_reserved) VALUES
247 (@option_group_id_gr, 'Dear [first]', 1, 'Dear [first]', 1, 1, 0),
248 (@option_group_id_gr, 'Dear [prefix] [first] [last]', 2, 'Dear [prefix] [first] [last]', 0, 2, 0),
249 (@option_group_id_gr, 'Dear [prefix] [last]', 3, 'Dear [prefix] [last]', 0, 3, 0),
250 (@option_group_id_gr, 'Customized', 4, 'Customized', 0, 4, 1);
251{/if}
252
253ALTER TABLE `civicrm_contact` ADD `greeting_type_id` int(10) unsigned NULL DEFAULT NULL AFTER greeting_type;
254
10824d34 255SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group
256 WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Dear [first]';
6a488035
TO		 257UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Informal';
258
10824d34 259SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group
260 WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Dear [prefix] [first] [last]';
6a488035
TO		 261UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Formal';
262
10824d34 263SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group
264 WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Dear [prefix] [last]';
6a488035
TO		 265UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Honorific';
266
10824d34 267SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group
268 WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Customized';
6a488035
TO		 269UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Custom';
270
271
272ALTER TABLE `civicrm_contact` DROP `greeting_type`;
273
274
275-- Add 'Address Edit option IM & Open ID
276-- CRM- 3419
277
278SELECT @option_group_id_ao := max(id) from civicrm_option_group where name = 'address_options';
279
280{if $multilingual}
281 INSERT INTO civicrm_option_value
282 (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight, is_reserved) VALUES
283 (@option_group_id_ao, {foreach from=$locales item=locale}'Instant Messenger',{/foreach} 13, 'im', 13, 1),
284 (@option_group_id_ao, {foreach from=$locales item=locale}'OpenID',{/foreach} 14, 'openid', 14, 1);
285{else}
286 INSERT INTO civicrm_option_value
287 (option_group_id, label, value, name, weight, is_reserved) VALUES
288 (@option_group_id_ao, 'Instant Messenger', 13, 'im', 13, 1),
289 (@option_group_id_ao, 'OpenID', 14, 'openid', 14, 1);
290{/if}
291
292UPDATE civicrm_preferences SET address_options = CONCAT(address_options, '13\ 114\ 1');
293
294
295-- * Fix for CRM-3248
296{if $multilingual}
297 INSERT INTO civicrm_option_group (name, {foreach from=$locales item=locale}description_{$locale},{/foreach} is_reserved, is_active ) VALUES ('phone_type', {foreach from=$locales item=locale}'Phone Type',{/foreach} 0, 1);
298{else}
299 INSERT INTO civicrm_option_group (name, description, is_reserved, is_active) VALUES ('phone_type', 'Phone Type', 0, 1);
300{/if}
301
302SELECT @option_group_id_pt := max(id) from civicrm_option_group where name = 'phone_type';
303
304{if $multilingual}
305 INSERT INTO civicrm_option_value
306 (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight) VALUES
307 (@option_group_id_pt, {foreach from=$locales item=locale}'Phone',{/foreach} 1, 'Phone', 1),
308 (@option_group_id_pt, {foreach from=$locales item=locale}'Mobile',{/foreach} 2, 'Mobile', 2),
309 (@option_group_id_pt, {foreach from=$locales item=locale}'Fax',{/foreach} 3, 'Fax', 3),
310 (@option_group_id_pt, {foreach from=$locales item=locale}'Pager',{/foreach} 4, 'Pager', 4),
311 (@option_group_id_pt, {foreach from=$locales item=locale}'Voicemail',{/foreach} 5, 'Voicemail', 5);
312{else}
313 INSERT INTO civicrm_option_value
314 (option_group_id, label, value, name, weight) VALUES
315 (@option_group_id_pt, 'Phone' , 1, 'Phone', 1),
316 (@option_group_id_pt, 'Mobile', 2, 'Mobile', 2),
317 (@option_group_id_pt, 'Fax' , 3, 'Fax', 3),
318 (@option_group_id_pt, 'Pager' , 4, 'Pager', 4),
319 (@option_group_id_pt, 'Voicemail', 5, 'Voicemail', 5);
320{/if}
321
322ALTER TABLE `civicrm_phone` ADD `phone_type_id` int(10) unsigned NULL DEFAULT NULL AFTER phone_type;
323ALTER TABLE `civicrm_mapping_field` ADD `phone_type_id` int(10) unsigned NULL DEFAULT NULL AFTER phone_type;
324ALTER TABLE `civicrm_uf_field` ADD `phone_type_id` int(10) unsigned NULL DEFAULT NULL AFTER phone_type;
325
10824d34 326SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group
327 WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Phone';
6a488035
TO		 328UPDATE civicrm_phone SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type = 'Phone';
329UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Phone';
330UPDATE civicrm_uf_field SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type = 'Phone';
331
10824d34 332SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group
333 WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Mobile';
6a488035
TO		 334UPDATE civicrm_phone SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type = 'Mobile';
335UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Mobile';
336UPDATE civicrm_uf_field SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type = 'Mobile';
337
10824d34 338SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group
339 WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Fax';
6a488035
TO		 340UPDATE civicrm_phone SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type = 'Fax';
341UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Fax';
342UPDATE civicrm_uf_field SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type = 'Fax';
343
10824d34 344SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group
345 WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Pager';
6a488035
TO		 346UPDATE civicrm_phone SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type = 'Pager';
347UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Pager';
348UPDATE civicrm_uf_field SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type = 'Pager';
349
350ALTER TABLE `civicrm_phone` DROP `phone_type`;
351ALTER TABLE `civicrm_mapping_field` DROP `phone_type`;
352ALTER TABLE `civicrm_uf_field` DROP `phone_type`;
353
10824d34 354-- custom Group table
6a488035
TO		 355ALTER TABLE civicrm_custom_group
356 ADD min_multiple int unsigned DEFAULT 0 COMMENT 'minimum number of multiple records (typically 0?)',
357 ADD max_multiple int unsigned DEFAULT 0 COMMENT 'maximum number of multiple records, if 0 - no max';
358
359ALTER TABLE civicrm_custom_field
360 ADD text_length int unsigned COMMENT 'field length if alphanumeric' AFTER options_per_line;
361
10824d34 362-- need to add update statement for site preference options to enable
6a488035
TO		 363-- preferences for contact_type / groups / tags, CRM-2794
364
365SELECT @option_group_id_aso := max(id) from civicrm_option_group where name = 'advanced_search_options';
366
367{if $multilingual}
368 INSERT INTO civicrm_option_value
369 (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight) VALUES
370 (@option_group_id_aso, {foreach from=$locales item=locale}'Contact Type',{/foreach} 16, 'contactType', 16),
371 (@option_group_id_aso, {foreach from=$locales item=locale}'Groups',{/foreach} 17, 'groups', 17),
372 (@option_group_id_aso, {foreach from=$locales item=locale}'Tags',{/foreach} 18, 'tags', 18);
373{else}
374 INSERT INTO civicrm_option_value
375 (option_group_id, label, value, name, weight) VALUES
376 (@option_group_id_aso, 'Contact Type', 16, 'contactType', 16),
377 (@option_group_id_aso, 'Groups', 17, 'groups', 17),
378 (@option_group_id_aso, 'Tags', 18, 'tags', 18);
379{/if}
380
381
382UPDATE civicrm_preferences SET advanced_search_options = CONCAT(advanced_search_options, '16\ 117\ 118\ 1');
383
384ALTER TABLE civicrm_preferences
385 ADD mailing_backend text COMMENT 'Smtp Backend configuration.';
386
387
388-- /**
389-- * add new mailing fields
390-- * CRM-3599 (created_id, scheduled_id)
391-- * CRM-3598 (search_id, search_args)
392-- **/
393
394ALTER TABLE civicrm_mailing
395 ADD override_verp tinyint DEFAULT 0 AFTER msg_template_id,
396 ADD created_id int unsigned NULL DEFAULT NULL AFTER override_verp,
397 ADD scheduled_id int unsigned NULL DEFAULT NULL AFTER created_id,
10824d34 398 ADD is_archived tinyint DEFAULT 0 COMMENT 'Is this mailing archived?',
6a488035
TO		 399 ADD CONSTRAINT FK_civicrm_mailing_created_id FOREIGN KEY (created_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE,
400 ADD CONSTRAINT FK_civicrm_mailing_scheduled_id FOREIGN KEY (scheduled_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE;
401
402ALTER TABLE civicrm_mailing_group
403 ADD search_id int AFTER entity_id,
404 ADD search_args text AFTER search_id,
405 MODIFY `group_type` enum('Include','Exclude','Base') NULL DEFAULT NULL;
406
407
408-- CRM-3609 (used IGNORE as 2.1 post beta5 should have this already)
10824d34 409
6a488035
TO		 410INSERT IGNORE INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (5217, 1020, "BRU", "Brussels");
411
412-- ======== CiviCase Related Upgrade ==========
413-- Insert the CiviCase Component
414INSERT INTO `civicrm_component` ( `name`, `namespace`) VALUES ( 'CiviCase','CRM_Case' );
415SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
416
417-- CRM-3667 case mapping
418SELECT @option_group_id_mt := max(id) from civicrm_option_group where name = 'mapping_type';
419
420{if $multilingual}
421 INSERT INTO civicrm_option_value
422 (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight, is_reserved) VALUES
423 (@option_group_id_mt, {foreach from=$locales item=locale}'Export Case',{/foreach} 12, 'Export Case', 12, 1);
424{else}
425 INSERT INTO civicrm_option_value
426 (option_group_id, label, value, name, weight, is_reserved) VALUES
427 (@option_group_id_mt, 'Export Case', 12, 'Export Case', 12, 1);
428{/if}
429
430-- * Case Status - Set names for Open and Closed and add Urgent status
431SELECT @csgId := max(id) FROM civicrm_option_group WHERE name = 'case_status';
432
433{if $multilingual}
434 {foreach from=$locales item=locale}
435 UPDATE civicrm_option_value SET name = 'Open' WHERE option_group_id = @csgId AND label_{$locale} = 'Ongoing';
436 UPDATE civicrm_option_value SET name = 'Closed' WHERE option_group_id = @csgId AND label_{$locale} = 'Resolved';
437 {/foreach}
438 INSERT INTO civicrm_option_value
439 (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight, is_reserved) VALUES
440 (@csgId, {foreach from=$locales item=locale}'Urgent',{/foreach} 3, 'Urgent', 3, 1);
441{else}
442 UPDATE civicrm_option_value SET name = 'Open' WHERE option_group_id = @csgId AND label = 'Ongoing';
443 UPDATE civicrm_option_value SET name = 'Closed' WHERE option_group_id = @csgId AND label = 'Resolved';
444 INSERT INTO civicrm_option_value
445 (option_group_id, label, value, name, weight, is_reserved) VALUES
446 (@csgId, 'Urgent', 3, 'Urgent', 3, 1);
447{/if}
448
449-- Relationship Types for cases
450INSERT INTO civicrm_relationship_type( name_a_b, name_b_a, description, contact_type_a, contact_type_b, is_reserved )
451 VALUES( 'Case Coordinator is', 'Case Coordinator', 'Case Coordinator', 'Individual', 'Individual', 0 );
452
453INSERT INTO civicrm_relationship_type( name_a_b, name_b_a, description, contact_type_a, contact_type_b, is_reserved )
454 VALUES( 'Supervised by', 'Supervisor', 'Immediate workplace supervisor', 'Individual', 'Individual', 0 );
455
456-- Activity Types for cases
457SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type';
458SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id = @option_group_id_activity_type;
459
460{if $multilingual}
461 INSERT INTO civicrm_option_value
462 (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, weight, component_id) VALUES
463 (@option_group_id_activity_type, {foreach from=$locales item=locale}'Open Case',{/foreach} (SELECT @max_val := @max_val+1), 'Open Case', (SELECT @max_val := @max_val+1), @caseCompId),
464 (@option_group_id_activity_type, {foreach from=$locales item=locale}'Follow up',{/foreach} (SELECT @max_val := @max_val+1), 'Follow up', (SELECT @max_val := @max_val+1), @caseCompId),
10824d34 465 (@option_group_id_activity_type, {foreach from=$locales item=locale}'Change Case Type',{/foreach} (SELECT @max_val := @max_val+1), 'Change Case Type', (SELECT @max_val := @max_val+1), @caseCompId),
466 (@option_group_id_activity_type, {foreach from=$locales item=locale}'Change Case Status',{/foreach} (SELECT @max_val := @max_val+1), 'Change Case Status', (SELECT @max_val := @max_val+1), @caseCompId),
6a488035
TO		 467 (@option_group_id_activity_type, {foreach from=$locales item=locale}'Close Case',{/foreach} (SELECT @max_val := @max_val+1), 'Close Case', (SELECT @max_val := @max_val+1), @caseCompId);
468{else}
469 INSERT INTO civicrm_option_value
470 (option_group_id, label, value, name, weight, component_id) VALUES
471 (@option_group_id_activity_type, 'Open Case', (SELECT @max_val := @max_val+1), 'Open Case', (SELECT @max_val := @max_val+1), @caseCompId),
472 (@option_group_id_activity_type, 'Follow up', (SELECT @max_val := @max_val+1), 'Follow up', (SELECT @max_val := @max_val+1), @caseCompId),
473 (@option_group_id_activity_type, 'Change Case Type', (SELECT @max_val := @max_val+1), 'Change Case Type', (SELECT @max_val := @max_val+1), @caseCompId),
10824d34 474 (@option_group_id_activity_type, 'Change Case Status', (SELECT @max_val := @max_val+1), 'Change Case Status', (SELECT @max_val := @max_val+1), @caseCompId),
6a488035
TO		 475 (@option_group_id_activity_type, 'Close Case', (SELECT @max_val := @max_val+1), 'Close Case', (SELECT @max_val := @max_val+1), @caseCompId);
476{/if}
477
478-- Encounter Medium Option Values for Case Activities
479{if $multilingual}
480 INSERT INTO civicrm_option_group
10824d34 481 (name, {foreach from=$locales item=locale}label_{$locale}, description_{$locale},{/foreach} is_reserved, is_active)
6a488035
TO		 482 VALUES
483 ('encounter_medium', {foreach from=$locales item=locale}'Encounter Medium', 'Encounter medium for case activities (e.g. In Person, By Phone, etc.)',{/foreach} 1, 1 );
484 SELECT @option_group_id_medium := max(id) FROM civicrm_option_group WHERE name = 'encounter_medium';
485 INSERT INTO civicrm_option_value
486 (option_group_id, {foreach from=$locales item=locale}label_{$locale},{/foreach} value, name, is_default, weight, is_reserved) VALUES
487 (@option_group_id_medium, {foreach from=$locales item=locale}'In Person',{/foreach} 1, 'in_person', 0, 1, 1),
488 (@option_group_id_medium, {foreach from=$locales item=locale}'Phone',{/foreach} 2, 'phone', 1, 2, 1),
489 (@option_group_id_medium, {foreach from=$locales item=locale}'Email',{/foreach} 3, 'email', 0, 3, 1),
490 (@option_group_id_medium, {foreach from=$locales item=locale}'Fax',{/foreach} 4, 'fax', 0, 4, 1),
491 (@option_group_id_medium, {foreach from=$locales item=locale}'Letter Mail',{/foreach} 5, 'letter_mail', 0, 5, 1);
492{else}
493 INSERT INTO civicrm_option_group
10824d34 494 (name, label, description, is_reserved, is_active )
6a488035
TO		 495 VALUES
496 ('encounter_medium', 'Encounter Medium', 'Encounter medium for case activities (e.g. In Person, By Phone, etc.)', 1, 1);
497 SELECT @option_group_id_medium := max(id) FROM civicrm_option_group WHERE name = 'encounter_medium';
498 INSERT INTO civicrm_option_value
499 (option_group_id, label, value, name, is_default, weight, is_reserved) VALUES
500 (@option_group_id_medium, 'In Person', 1, 'in_person', 0, 1, 1),
501 (@option_group_id_medium, 'Phone', 2, 'phone', 1, 2, 1),
502 (@option_group_id_medium, 'Email', 3, 'email', 0, 3, 1),
503 (@option_group_id_medium, 'Fax', 4, 'fax', 0, 4, 1),
504 (@option_group_id_medium, 'Letter Mail', 5, 'letter_mail', 0, 5, 1);
505{/if}
506
10824d34 507-- CRM-3573
6a488035
TO		 508-- added column case_id in civicrm_relationship table.
509-- added columns medium, is_auto, relationship_id fileds in civicrm_activity.
510-- added value 'Case' in civicrm_custom_group.
511
512ALTER TABLE `civicrm_relationship`
513 ADD `case_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_case' AFTER is_permission_b_a,
514 ADD CONSTRAINT FK_civicrm_case_id FOREIGN KEY (case_id) REFERENCES civicrm_case(id) ON DELETE CASCADE;
515
516ALTER TABLE `civicrm_case`
517 ADD `is_deleted` tinyint DEFAULT 0;
518
519ALTER TABLE `civicrm_activity`
520 ADD `medium_id` int(10) unsigned default NULL COMMENT 'Activity Medium, Implicit FK to civicrm_option_value where option_group = activity_medium.',
521 ADD `is_auto` tinyint(4) default '0' COMMENT 'if true - activity is auto populated while case review',
522 ADD `relationship_id` int(10) unsigned default NULL COMMENT 'FK to Relationship ID',
523 ADD `is_current_revision` tinyint DEFAULT 1 ,
524 ADD `original_id` int unsigned COMMENT 'Activity ID of the first activity record in versioning chain.',
525 ADD `is_deleted` tinyint DEFAULT 0,
10824d34 526 ADD CONSTRAINT FK_civicrm_activity_original_id FOREIGN KEY (original_id) REFERENCES civicrm_activity(id) ON DELETE CASCADE,
6a488035
TO		 527 ADD CONSTRAINT FK_civicrm_relationship_id FOREIGN KEY (relationship_id) REFERENCES civicrm_relationship(id) ON DELETE SET NULL;
528
529ALTER TABLE `civicrm_custom_group`
530 MODIFY `extends` enum('Contact','Individual','Household','Organization','Location','Address','Contribution','Activity','Relationship','Group','Membership','Participant','Event','Grant','Pledge','Case') collate utf8_unicode_ci default 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).';
531
532-- schema change CRM-3337
533ALTER TABLE `civicrm_custom_group` CHANGE `extends_entity_column_name` `extends_entity_column_id` INT( 10 ) UNSIGNED NULL DEFAULT NULL COMMENT 'reference to option value';
534
535-- added new column 'skipBreadcrumb' to civicrm_menu. CRM-2699.
536
537ALTER TABLE `civicrm_menu`
538 ADD `skipBreadcrumb` tinyint(4) COMMENT 'skip this url being exposed to breadcrumb';
539
10824d34 540-- CRM-3709
6a488035
TO		 541CREATE INDEX index_option_group_id_name ON civicrm_option_value( `option_group_id` , `name` );
542
543-- fix constraint
544ALTER TABLE `civicrm_group_nesting`
545 DROP FOREIGN KEY `FK_civicrm_group_nesting_child_group_id`;
546ALTER TABLE `civicrm_group_nesting`
547 ADD CONSTRAINT `FK_civicrm_group_nesting_child_group_id` FOREIGN KEY (`child_group_id`) REFERENCES `civicrm_group` (`id`) ON DELETE CASCADE;
548
549ALTER TABLE `civicrm_group`
550 ADD `is_hidden` tinyint DEFAULT 0 COMMENT 'Is this group hidden?';
551
552-- New field for CRM-3494 (billing name and address for contributions)
553ALTER TABLE `civicrm_contribution`
554 ADD `address_id` int(10) unsigned default NULL COMMENT 'Conditional foreign key to civicrm_address.id. We insert an address record for each contribution when we have associated billing name and address data.',
555 ADD CONSTRAINT FK_civicrm_contribution_address_id FOREIGN KEY (address_id) REFERENCES civicrm_address(id) ON DELETE SET NULL;
556
557-- Removing solicitor_id as per CRM-3917
10824d34 558INSERT INTO civicrm_contribution_soft (contribution_id, contact_id, amount)
6a488035
TO		 559SELECT id, solicitor_id, total_amount FROM civicrm_contribution
560WHERE solicitor_id IS NOT NULL;
561
10824d34 562ALTER TABLE `civicrm_contribution`
6a488035
TO		 563-- Added check_number as per CRM-3923
564 ADD `check_number` varchar(255) collate utf8_unicode_ci default NULL,
565 DROP FOREIGN KEY `FK_civicrm_contribution_solicitor_id`;
566
567DROP INDEX FK_civicrm_contribution_solicitor_id ON civicrm_contribution;
568ALTER TABLE civicrm_contribution DROP solicitor_id;
569
570-- Make sure is_billing flag is true for all address records where location type is Billing
571-- Using hard-coded location_type_id since we don't have a non-translatable name for location types
572UPDATE `civicrm_address`
573SET `is_billing` = 1
574WHERE `location_type_id` = 5;
575
576-- civicrm_note constraint fix
10824d34 577ALTER TABLE civicrm_note
6a488035
TO		 578 DROP FOREIGN KEY `FK_civicrm_note_contact_id`;
579ALTER TABLE `civicrm_note`
580 ADD CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL;
581
582-- CRM-3860
583ALTER TABLE civicrm_contact
584 ADD api_key varchar(32);
585CREATE INDEX index_api_key ON civicrm_contact(api_key);
586
587-- CRM-3863
588SELECT @piOpt := id from civicrm_option_group where name = 'payment_instrument';
589UPDATE civicrm_option_value SET is_reserved = 1 WHERE option_group_id = @piOpt AND name = 'Credit Card';
590
591-- CRM-3851: migrate civicrm_domain.email_domain and .email_return_path to civicrm_mail_settings
592SELECT email_domain, email_return_path FROM civicrm_domain LIMIT 1 INTO @domain, @return_path;
593
594
595CREATE TABLE `civicrm_mail_settings` (
596 `id` int(10) unsigned NOT NULL auto_increment COMMENT 'primary key',
597 `name` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'name of this group of settings',
598 `is_default` tinyint(4) default NULL COMMENT 'whether this is the default set of settings for this domain',
599 `domain` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'email address domain (the part after @)',
600 `localpart` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'optional local part (like civimail+ for addresses like civimail+s.1.2@example.com)',
601 `return_path` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'contents of the Return-Path header',
602 `protocol` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'name of the protocol to use for polling (like IMAP, POP3 or Maildir)',
603 `server` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'server to use when polling',
604 `port` int(10) unsigned default NULL COMMENT 'port to use when polling',
605 `username` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'username to use when polling',
606 `password` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'password to use when polling',
607 `is_ssl` tinyint(4) default NULL COMMENT 'whether to use SSL or not',
608 `source` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'folder to poll from when using IMAP, path to poll from when using Maildir, etc.',
609 PRIMARY KEY (`id`)
610) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
611
612INSERT INTO civicrm_mail_settings (name, is_default, domain, return_path) VALUES ('default', true, @domain, @return_path);
613ALTER TABLE civicrm_domain DROP email_domain;
614ALTER TABLE civicrm_domain DROP email_return_path;
615
616-- CRM-4258, needs to be done before we add a unique index
617delete t1 from civicrm_entity_tag t1 inner join civicrm_entity_tag t2 where t1.tag_id = t2.tag_id AND t1.contact_id = t2.contact_id AND t1.id > t2.id;
618
619-- CRM-3696
620ALTER TABLE civicrm_entity_tag
621 ADD UNIQUE UI_contact_id_tag_id ( contact_id, tag_id );
622
623
624SELECT @option_group_id_ps := id from civicrm_option_group where name = 'participant_status';
625UPDATE civicrm_option_value
626 SET visibility_id = CASE name
627 WHEN 'Registered' THEN 1
628 ELSE 2
629 END
630 WHERE option_group_id = @option_group_id_ps;
631
632-- CRM-3487
633ALTER TABLE civicrm_uf_group DROP collapse_display;
634
635
636-- FIXED FOR CRM-3772
637-- upgrading civicrm_country table for region id by taking reference of civicrm_worldregion
638
639-- Europe and Central Asia
640
641SELECT @region_id := max(id) from civicrm_worldregion where name = "Europe and Central Asia";
642
643UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "AL","AD","AQ","AT","AZ","BY","BE","BA","BV","BG","HR","CY","CZ","DK","EE","FO","FI","FR","TF","DE","GI","GR","VA","HU","IS","IE","IT","KZ","KG","LV","LI","LT","LU","MK","MT","MD","MC","NL","NO","PL","PT","RO","RU","SM","SK","SI","ES","SJ","SE","CH","TR","TM","UA","GB","UZ","CS","AX","RS","ME" ) AND `civicrm_country`.`region_id` IS null;
644
645-- America South, Central, North and Carribean
646
647SELECT @region_id := max(id) from civicrm_worldregion where name = "America South, Central, North and Carribean";
648
649UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "AS","AI","AG","AR","AW","BZ","BM","BO","BR","VG","CA","CL","CO","CR","CU","DM","DO","EC","SV","FK","GF","GL","GD","GP","GT","GY","HT","HN","JM","MQ","MX","MS","AN","NI","MP","PY","PE","PR","KN","LC","PM","VC","GS","SR","BS","TT","TC","UM","US","UY","VE","VI" ) AND `civicrm_country`.`region_id` IS null;
650
651-- Middle East and North Africa
652
653SELECT @region_id := max(id) from civicrm_worldregion where name = "Middle East and North Africa";
654
655UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "DZ","BH","EG","IR","IQ","IL","JO","KW","LB","LY","MA","OM","PS","QA","SA","SY","TN","AE","EH","YE" ) AND `civicrm_country`.`region_id` IS null;
656
657-- Asia-Pacific
658
659SELECT @region_id := max(id) from civicrm_worldregion where name = "Asia-Pacific";
660
661UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "AF","AM","AU","BD","BB","BT","IO","BN","MM","KH","CN","CX","CC","CK","TL","FJ","PF","GE","GU","HM","HK","IN","ID","JP","KI","KP","KR","LA","MO","MY","MV","MH","FM","MN","NR","NP","NC","NZ","NU","NF","PK","PW","PG","PH","PN","WS","SG","SB","LK","TW","TJ","TH","TK","TO","TV","VU","VN","WF" ) AND `civicrm_country`.`region_id` IS null;
662
663-- Africa West, East, Central and Southern
664
665SELECT @region_id := max(id) from civicrm_worldregion where name = "Africa West, East, Central and Southern";
666
667UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "AO","BJ","BW","BF","BI","CM","CV","KY","CF","TD","KM","CD","CG","CI","DJ","GQ","ER","ET","GA","GH","GN","GW","KE","LS","LR","MG","MW","ML","MR","MU","YT","MZ","NA","NE","NG","PA","RW","RE","SH","SN","SC","SL","SO","ZA","SD","SZ","ST","TZ","GM","TG","UG","ZM","ZW" ) AND `civicrm_country`.`region_id` IS null;
668
669-- unassigned
670
671SELECT @region_id := max(id) from civicrm_worldregion where name = "unassigned";
672
673UPDATE `civicrm_country` SET `region_id` = @region_id WHERE `civicrm_country`.`iso_code` IN( "JE","GG","IM" ) AND `civicrm_country`.`region_id` IS null;
674
675
676-- update minute increment to 1 in activitydatetime in preferences_date
677UPDATE `civicrm_preferences_date` SET `minute_increment` = 1 WHERE `name` = 'activityDatetime';
678
679-- CRM-3730
680ALTER TABLE civicrm_price_field ALTER active_on SET DEFAULT NULL;
681ALTER TABLE civicrm_price_field ALTER expire_on SET DEFAULT NULL;
682
683-- ******************************************************
684-- END OF THE UPGRADE