CRM/Upgrade/Incremental/sql/2.2.alpha1.mysql.tpl

   1 -- /************************************************************************
   2 -- *
   3 -- * MySQL Script for civicrm database/tables - upgradation from 2.1 -> 2.2
   4 -- *
   5 -- *************************************************************************/
   6
   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
   9 -- is the source of the change, as part of the comment.
  10
  11 -- fix version column first
  12 ALTER TABLE `civicrm_domain`
  13   MODIFY version varchar(32) COMMENT 'The civicrm version this instance is running';
  14
  15 -- make the register_by_id cascade in civicrm_participant
  16
  17 ALTER TABLE `civicrm_participant`
  18    DROP FOREIGN KEY `FK_civicrm_participant_registered_by_id`;
  19 ALTER 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
  25 ALTER 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
  65 UPDATE civicrm_event ce
  66 LEFT JOIN civicrm_event_page cp ON ce.id=cp.event_id
  67 SET
  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},
  72       ce.confirm_title_{$locale} = cp.confirm_title_{$locale},
  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,
  86     ce.confirm_title = cp.confirm_title,
  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
 107 -- Update table name in civicrm_option_group (Fee Level)
 108 -- cleanup for unused option group
 109
 110 DELETE og.* FROM civicrm_option_group og
 111  LEFT JOIN civicrm_event_page ep ON ep.id = SUBSTRING_INDEX( SUBSTRING( og.name, 27 ) , '.discount', 1)
 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`
 122     SET `name` = REPLACE( name, CONCAT_WS('.', 'civicrm_event_page.amount', @event_page_id ), CONCAT_WS('.', 'civicrm_event.amount', @event_id ) )
 123     WHERE `id` = @option_group_id;
 124 {/foreach}
 125
 126 -- Update table entity_table and entity_id civicrm_tell_friend
 127 UPDATE 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
 133 -- Update table name in civicrm_price_set_entity (Price Set)
 134 UPDATE 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
 145 DROP 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
 155 SELECT @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
 169 ALTER 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
 175 CREATE 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 ),
 186      CONSTRAINT FK_civicrm_pcp_block_entity_id FOREIGN KEY (entity_id) REFERENCES civicrm_contribution_page(id)
 187 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 188
 189 -- * civicrm_pcp
 190
 191 CREATE 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?',
 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)
 208 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 209
 210 -- * civicrm_contribution_soft
 211
 212 CREATE 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 ) ,
 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
 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
 235 SELECT @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
 253 ALTER TABLE `civicrm_contact` ADD `greeting_type_id` int(10) unsigned NULL DEFAULT NULL AFTER greeting_type;
 254
 255 SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group
 256   WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Dear [first]';
 257 UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Informal';
 258
 259 SELECT @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]';
 261 UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Formal';
 262
 263 SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group
 264   WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Dear [prefix] [last]';
 265 UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Honorific';
 266
 267 SELECT @greetingvalue := value FROM civicrm_option_value, civicrm_option_group
 268   WHERE civicrm_option_group.name = 'greeting_type' && civicrm_option_value.name = 'Customized';
 269 UPDATE civicrm_contact SET greeting_type_id = @greetingvalue WHERE civicrm_contact.greeting_type = 'Custom';
 270
 271
 272 ALTER TABLE `civicrm_contact` DROP `greeting_type`;
 273
 274
 275 -- Add 'Address Edit option IM  & Open ID
 276 -- CRM- 3419
 277
 278 SELECT @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
 292 UPDATE 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
 302 SELECT @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
 322 ALTER TABLE `civicrm_phone`         ADD `phone_type_id` int(10) unsigned NULL DEFAULT NULL AFTER phone_type;
 323 ALTER TABLE `civicrm_mapping_field` ADD `phone_type_id` int(10) unsigned NULL DEFAULT NULL AFTER phone_type;
 324 ALTER TABLE `civicrm_uf_field`      ADD `phone_type_id` int(10) unsigned NULL DEFAULT NULL AFTER phone_type;
 325
 326 SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group
 327   WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Phone';
 328 UPDATE civicrm_phone         SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type         = 'Phone';
 329 UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Phone';
 330 UPDATE civicrm_uf_field      SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type      = 'Phone';
 331
 332 SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group
 333   WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Mobile';
 334 UPDATE civicrm_phone         SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type         = 'Mobile';
 335 UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Mobile';
 336 UPDATE civicrm_uf_field      SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type      = 'Mobile';
 337
 338 SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group
 339   WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Fax';
 340 UPDATE civicrm_phone         SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type         = 'Fax';
 341 UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Fax';
 342 UPDATE civicrm_uf_field      SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type      = 'Fax';
 343
 344 SELECT @phonevalue := value FROM civicrm_option_value, civicrm_option_group
 345   WHERE civicrm_option_group.name = 'phone_type' && civicrm_option_value.name = 'Pager';
 346 UPDATE civicrm_phone         SET phone_type_id = @phonevalue WHERE civicrm_phone.phone_type         = 'Pager';
 347 UPDATE civicrm_mapping_field SET phone_type_id = @phonevalue WHERE civicrm_mapping_field.phone_type = 'Pager';
 348 UPDATE civicrm_uf_field      SET phone_type_id = @phonevalue WHERE civicrm_uf_field.phone_type      = 'Pager';
 349
 350 ALTER TABLE `civicrm_phone`         DROP `phone_type`;
 351 ALTER TABLE `civicrm_mapping_field` DROP `phone_type`;
 352 ALTER TABLE `civicrm_uf_field`      DROP `phone_type`;
 353
 354 -- custom Group table
 355 ALTER 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
 359 ALTER TABLE civicrm_custom_field
 360   ADD text_length int unsigned    COMMENT 'field length if alphanumeric' AFTER options_per_line;
 361
 362 -- need to add update statement for site preference options to enable
 363 -- preferences for contact_type / groups / tags, CRM-2794
 364
 365 SELECT @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
 382 UPDATE civicrm_preferences SET advanced_search_options = CONCAT(advanced_search_options, '16\ 117\ 118\ 1');
 383
 384 ALTER 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
 394 ALTER 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,
 398   ADD is_archived tinyint   DEFAULT 0 COMMENT 'Is this mailing archived?',
 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
 402 ALTER 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)
 409
 410 INSERT 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
 414 INSERT INTO `civicrm_component` ( `name`, `namespace`) VALUES ( 'CiviCase','CRM_Case' );
 415 SELECT @caseCompId := id FROM `civicrm_component` where `name` like 'CiviCase';
 416
 417 -- CRM-3667 case mapping
 418 SELECT @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
 431 SELECT @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
 450 INSERT 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
 453 INSERT 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
 457 SELECT @option_group_id_activity_type        := max(id) from civicrm_option_group where name = 'activity_type';
 458 SELECT @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),
 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),
 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),
 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),
 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
 481     (name, {foreach from=$locales item=locale}label_{$locale}, description_{$locale},{/foreach} is_reserved, is_active)
 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
 494     (name, label, description, is_reserved, is_active )
 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
 507 -- CRM-3573
 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
 512 ALTER 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
 516 ALTER TABLE `civicrm_case`
 517  ADD `is_deleted` tinyint   DEFAULT 0;
 518
 519 ALTER 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,
 526   ADD CONSTRAINT FK_civicrm_activity_original_id FOREIGN KEY (original_id) REFERENCES civicrm_activity(id) ON DELETE CASCADE,
 527   ADD CONSTRAINT FK_civicrm_relationship_id FOREIGN KEY (relationship_id) REFERENCES civicrm_relationship(id) ON DELETE SET NULL;
 528
 529 ALTER 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
 533 ALTER 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
 537 ALTER TABLE `civicrm_menu`
 538   ADD `skipBreadcrumb` tinyint(4) COMMENT 'skip this url being exposed to breadcrumb';
 539
 540 -- CRM-3709
 541 CREATE INDEX index_option_group_id_name ON civicrm_option_value( `option_group_id` , `name` );
 542
 543 -- fix constraint
 544 ALTER TABLE `civicrm_group_nesting`
 545     DROP FOREIGN KEY `FK_civicrm_group_nesting_child_group_id`;
 546 ALTER 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
 549 ALTER 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)
 553 ALTER 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
 558 INSERT INTO civicrm_contribution_soft (contribution_id, contact_id, amount)
 559 SELECT id, solicitor_id, total_amount FROM civicrm_contribution
 560 WHERE solicitor_id IS NOT NULL;
 561
 562 ALTER TABLE `civicrm_contribution`
 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
 567 DROP INDEX FK_civicrm_contribution_solicitor_id ON civicrm_contribution;
 568 ALTER 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
 572 UPDATE `civicrm_address`
 573 SET `is_billing` = 1
 574 WHERE `location_type_id` = 5;
 575
 576 -- civicrm_note constraint fix
 577 ALTER TABLE civicrm_note
 578     DROP FOREIGN KEY `FK_civicrm_note_contact_id`;
 579 ALTER 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
 583 ALTER TABLE civicrm_contact
 584     ADD api_key varchar(32);
 585 CREATE INDEX index_api_key ON civicrm_contact(api_key);
 586
 587 -- CRM-3863
 588 SELECT @piOpt := id from civicrm_option_group where name = 'payment_instrument';
 589 UPDATE 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
 592 SELECT email_domain, email_return_path FROM civicrm_domain LIMIT 1 INTO @domain, @return_path;
 593
 594
 595 CREATE 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
 612 INSERT INTO civicrm_mail_settings (name, is_default, domain, return_path) VALUES ('default', true, @domain, @return_path);
 613 ALTER TABLE civicrm_domain DROP email_domain;
 614 ALTER TABLE civicrm_domain DROP email_return_path;
 615
 616 -- CRM-4258, needs to be done before we add a unique index
 617 delete 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
 620 ALTER TABLE civicrm_entity_tag
 621   ADD UNIQUE UI_contact_id_tag_id ( contact_id, tag_id );
 622
 623
 624 SELECT @option_group_id_ps := id from civicrm_option_group where name = 'participant_status';
 625 UPDATE 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
 633 ALTER 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
 641 SELECT @region_id   := max(id) from civicrm_worldregion where name = "Europe and Central Asia";
 642
 643 UPDATE `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
 647 SELECT @region_id   := max(id) from civicrm_worldregion where name = "America South, Central, North and Carribean";
 648
 649 UPDATE `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
 653 SELECT @region_id   := max(id) from civicrm_worldregion where name = "Middle East and North Africa";
 654
 655 UPDATE `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
 659 SELECT @region_id   := max(id) from civicrm_worldregion where name = "Asia-Pacific";
 660
 661 UPDATE `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
 665 SELECT @region_id   := max(id) from civicrm_worldregion where name = "Africa West, East, Central and Southern";
 666
 667 UPDATE `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
 671 SELECT @region_id   := max(id) from civicrm_worldregion where name = "unassigned";
 672
 673 UPDATE `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
 677 UPDATE `civicrm_preferences_date` SET `minute_increment` = 1 WHERE `name`  = 'activityDatetime';
 678
 679 -- CRM-3730
 680 ALTER TABLE civicrm_price_field ALTER active_on SET DEFAULT NULL;
 681 ALTER TABLE civicrm_price_field ALTER expire_on SET DEFAULT NULL;
 682
 683 -- ******************************************************
 684 -- END OF THE UPGRADE