CRM/Upgrade/TwoOne/sql/misc.mysql

   1 -- /************************************************************************
   2 -- *
   3 -- * MySQL Script for civicrm database/tables - upgradation from 2.0 -> 2.1
   4 -- *
   5 -- *************************************************************************/
   6
   7 -- Please add script for all the schema / fixed-data related modifications to
   8 -- this sql script as you resolve 2.1 issues. Include the issue number which
   9 -- is the source of the change, as part of the comment.
  10
  11
  12 -- lower case all email addresses
  13
  14 UPDATE civicrm_email    SET email   = LOWER( email   );
  15 UPDATE civicrm_uf_match SET uf_name = LOWER( uf_name );
  16
  17
  18 -- Add new tinyint column to civicrm_tag
  19 ALTER TABLE `civicrm_tag`
  20     ADD `is_selectable` tinyint DEFAULT 1 AFTER `parent_id`;
  21
  22 -- Add new tinyint columns in civicrm_relationship
  23
  24 ALTER TABLE `civicrm_relationship`
  25     ADD `is_permission_a_b` tinyint DEFAULT 0 AFTER `description`;
  26 ALTER TABLE `civicrm_relationship`
  27     ADD `is_permission_b_a` tinyint DEFAULT 0 AFTER `is_permission_a_b`;
  28
  29
  30 -- Add 'My Contacts / Organizations' option in user dashboard options
  31
  32 UPDATE civicrm_preferences SET user_dashboard_options  = CONCAT(user_dashboard_options, '5\ 16\ 1'),
  33                                contact_view_options    = CONCAT(contact_view_options, '11\ 113\ 1'),
  34                                advanced_search_options = CONCAT(advanced_search_options, '12\ 113\ 115\ 1');
  35
  36
  37 -- make the dates in civicrm_contribution_recur optional
  38
  39 ALTER TABLE `civicrm_contribution_recur`
  40   MODIFY modified_date           datetime DEFAULT NULL,
  41   MODIFY cancel_date             datetime DEFAULT NULL,
  42   MODIFY end_date                datetime DEFAULT NULL,
  43   MODIFY next_sched_contribution datetime DEFAULT NULL;
  44
  45
  46 -- extend civicrm_dedupe_rule_group to the current schema
  47
  48 ALTER TABLE civicrm_dedupe_rule_group
  49   ADD level enum('Strict', 'Fuzzy'),
  50   ADD is_default tinyint;
  51
  52
  53 -- make the current dedupe rule groups into default and fuzzy ones
  54
  55 UPDATE civicrm_dedupe_rule_group SET level = 'Fuzzy', is_default = true;
  56
  57
  58 -- insert the new strict dedupe_rule_group dupe rules
  59
  60 INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, level, is_default)
  61 VALUES ('Individual', 10, 'Strict', true);
  62
  63 SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
  64 INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
  65 VALUES (@drgid, 'civicrm_email', 'email', 10);
  66
  67 INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, level, is_default)
  68 VALUES ('Organization', 10, 'Strict', true);
  69
  70 SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
  71 INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
  72 VALUES (@drgid, 'civicrm_contact', 'organization_name', 10),
  73        (@drgid, 'civicrm_email'  , 'email',             10);
  74
  75 INSERT INTO civicrm_dedupe_rule_group (contact_type, threshold, level, is_default)
  76 VALUES ('Household', 10, 'Strict', true);
  77
  78 SELECT @drgid := MAX(id) FROM civicrm_dedupe_rule_group;
  79 INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
  80 VALUES (@drgid, 'civicrm_contact', 'household_name', 10),
  81        (@drgid, 'civicrm_email'  , 'email',          10);
  82
  83
  84 -- Edited indexing of custom group table
  85
  86 ALTER TABLE `civicrm_custom_group`
  87   DROP INDEX `UI_title_domain_id` ,
  88   ADD UNIQUE `UI_title_extends` ( `title` , `extends` ),
  89   DROP INDEX `UI_name_domain_id` ,
  90   ADD UNIQUE `UI_name_extends` ( `name` , `extends` );
  91
  92 ALTER TABLE `civicrm_custom_group`
  93   MODIFY `extends` enum('Contact','Individual','Household','Organization','Location','Address','Contribution','Activity','Relationship','Group','Membership','Participant','Event','Grant','Pledge') collate utf8_unicode_ci default 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).';
  94
  95 -- change the default value of is_email_confirm to 0 from 1 in civicrm_event_page table, CRM-3109
  96
  97 ALTER TABLE `civicrm_event_page`
  98     MODIFY `is_email_confirm` tinyint(4) default '0' COMMENT 'If true, confirmation is automatically emailed to contact on successful registration.';
  99
 100
 101 -- CRM-2488
 102
 103 ALTER TABLE `civicrm_contribution_page`
 104     ADD `is_recur_interval` tinyint(4) NULL DEFAULT '0' AFTER is_recur,
 105     ADD `recur_frequency_unit` varchar(128) NULL DEFAULT NULL AFTER is_recur;
 106
 107
 108 UPDATE civicrm_country SET name = 'Moldova' WHERE id = 1143;
 109
 110
 111 -- *FIXME*
 112 -- we need to update option value table for cvOpt, ceOpt, asOpt, udOpt and adOpt. Not sure the best way to do this
 113 -- since translations are involved. The easiest option might be to delete the rows and then readd them
 114 -- CRM-2734
 115
 116
 117 -- CRM-2781
 118
 119 ALTER TABLE `civicrm_mapping` ADD `mapping_type_id` int(10) unsigned NULL DEFAULT NULL AFTER mapping_type;
 120
 121 SELECT @mapvalue := value FROM civicrm_option_value, civicrm_option_group
 122         WHERE civicrm_option_group.name = 'mapping_type' && civicrm_option_value.name = 'Export Contact';
 123 UPDATE civicrm_mapping SET mapping_type_id = @mapvalue WHERE civicrm_mapping.mapping_type = 'Export';
 124
 125 SELECT @mapvalue := value FROM civicrm_option_value, civicrm_option_group
 126         WHERE civicrm_option_group.name = 'mapping_type' && civicrm_option_value.name = 'Export Contribution';
 127 UPDATE civicrm_mapping SET mapping_type_id = @mapvalue WHERE civicrm_mapping.mapping_type = 'Export Contributions';
 128
 129 SELECT @mapvalue := value FROM civicrm_option_value, civicrm_option_group
 130         WHERE civicrm_option_group.name = 'mapping_type' && civicrm_option_value.name = 'Import Activity';
 131 UPDATE civicrm_mapping SET mapping_type_id = @mapvalue WHERE civicrm_mapping.mapping_type = 'Import Activity';
 132
 133 SELECT @mapvalue := value FROM civicrm_option_value, civicrm_option_group
 134         WHERE civicrm_option_group.name = 'mapping_type' && civicrm_option_value.name = 'Import Contact';
 135 UPDATE civicrm_mapping SET mapping_type_id = @mapvalue WHERE civicrm_mapping.mapping_type = 'Import';
 136
 137 SELECT @mapvalue := value FROM civicrm_option_value, civicrm_option_group
 138         WHERE civicrm_option_group.name = 'mapping_type' && civicrm_option_value.name = 'Import Contribution';
 139 UPDATE civicrm_mapping SET mapping_type_id = @mapvalue WHERE civicrm_mapping.mapping_type = 'Import Contributions';
 140
 141 SELECT @mapvalue := value FROM civicrm_option_value, civicrm_option_group
 142         WHERE civicrm_option_group.name = 'mapping_type' && civicrm_option_value.name = 'Import Membership';
 143 UPDATE civicrm_mapping SET mapping_type_id = @mapvalue WHERE civicrm_mapping.mapping_type = 'Import Memberships';
 144
 145 SELECT @mapvalue := value FROM civicrm_option_value, civicrm_option_group
 146         WHERE civicrm_option_group.name = 'mapping_type' && civicrm_option_value.name = 'Import Participant';
 147 UPDATE civicrm_mapping SET mapping_type_id = @mapvalue WHERE civicrm_mapping.mapping_type = 'Import Participants';
 148
 149 SELECT @mapvalue := value FROM civicrm_option_value, civicrm_option_group
 150         WHERE civicrm_option_group.name = 'mapping_type' && civicrm_option_value.name = 'Search Builder';
 151 UPDATE civicrm_mapping SET mapping_type_id = @mapvalue WHERE civicrm_mapping.mapping_type = 'Search Builder';
 152
 153 ALTER TABLE `civicrm_mapping` DROP `mapping_type`;
 154
 155 -- CRM-2964
 156
 157 ALTER TABLE `civicrm_contribution_page`
 158     ADD `for_organization` text NULL DEFAULT NULL AFTER thankyou_footer,
 159     ADD `is_for_organization` tinyint(4) NULL DEFAULT '0' AFTER thankyou_footer;
 160
 161
 162 -- add the language column to civicrm_uf_match
 163
 164 ALTER TABLE civicrm_uf_match ADD language VARCHAR(5) COMMENT 'UI language preferred by the given user/contact';
 165
 166
 167 -- CRM-3217
 168
 169 ALTER TABLE `civicrm_custom_field` MODIFY `label` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Text for form field label (also friendly name for administering this custom property).';
 170
 171 ALTER TABLE `civicrm_price_set`
 172    MODIFY `title` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Displayed title for Price Set.',
 173    MODIFY `name` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Variable name/programmatic handle for this set of price fields.';
 174
 175 ALTER TABLE `civicrm_price_field`
 176    MODIFY `label` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Text for form field label (also friendly name for administering this field).',
 177    MODIFY `name` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Variable name/programmatic handle for this field.';
 178
 179
 180 -- Modify civicrm_acl_cache
 181
 182 ALTER TABLE civicrm_acl_cache
 183     DROP FOREIGN KEY FK_civicrm_acl_cache_contact_id,
 184     DROP FOREIGN KEY FK_civicrm_acl_cache_acl_id;
 185
 186 ALTER TABLE civicrm_acl_cache
 187     ADD CONSTRAINT FK_civicrm_acl_cache_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE,
 188     ADD CONSTRAINT FK_civicrm_acl_cache_acl_id FOREIGN KEY (acl_id) REFERENCES civicrm_acl(id) ON DELETE CASCADE;
 189
 190
 191 -- CRM-3261
 192
 193 UPDATE civicrm_relationship_type SET is_reserved = 1 WHERE id IN (4,6,7);
 194
 195
 196 -- CRM-3281
 197
 198 UPDATE civicrm_state_province SET name = "Frederiksberg"   WHERE id = 2261;
 199 UPDATE civicrm_state_province SET name = "Copenhagen City" WHERE id = 2262;
 200 UPDATE civicrm_state_province SET name = "Vestsjælland"    WHERE id = 2266;
 201 UPDATE civicrm_state_province SET name = "Fyn"             WHERE id = 2269;
 202 UPDATE civicrm_state_province SET name = "South Jutland"   WHERE id = 2270;
 203 UPDATE civicrm_state_province SET name = "Ringkjøbing"     WHERE id = 2273;
 204 UPDATE civicrm_state_province SET name = "Århus"           WHERE id = 2274;
 205 UPDATE civicrm_state_province SET name = "North Jutland"   WHERE id = 2276;
 206
 207 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES
 208 (5196, 1016, "13", "Al Manāmah (Al ‘Āşimah)"),
 209 (5197, 1016, "14", "Al Janūbīyah"),
 210 (5199, 1016, "16", "Al Wusţá"),
 211 (5200, 1016, "17", "Ash Shamālīyah");
 212
 213
 214 -- CRM-3435
 215
 216 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES
 217 (5201, 1165, "_A", "Jenin"),
 218 (5202, 1165, "_B", "Tubas"),
 219 (5203, 1165, "_C", "Tulkarm"),
 220 (5204, 1165, "_D", "Nablus"),
 221 (5205, 1165, "_E", "Qalqilya"),
 222 (5206, 1165, "_F", "Salfit"),
 223 (5207, 1165, "_G", "Ramallah and Al-Bireh"),
 224 (5208, 1165, "_H", "Jericho"),
 225 (5209, 1165, "_I", "Jerusalem"),
 226 (5210, 1165, "_J", "Bethlehem"),
 227 (5211, 1165, "_K", "Hebron"),
 228 (5212, 1165, "_L", "North Gaza"),
 229 (5213, 1165, "_M", "Gaza"),
 230 (5214, 1165, "_N", "Deir el-Balah"),
 231 (5215, 1165, "_O", "Khan Yunis"),
 232 (5216, 1165, "_P", "Rafah");
 233
 234
 235 -- CRM-3441
 236
 237 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES
 238 (10000, 1107, "CI", "Carbonia-Iglesias"),
 239 (10001, 1107, "OT", "Olbia-Tempio"),
 240 (10002, 1107, "VS", "Medio Campidano"),
 241 (10003, 1107, "OG", "Ogliastra");
 242
 243 UPDATE civicrm_state_province SET name = "Forlì-Cesena" WHERE id = 3206;
 244 UPDATE civicrm_state_province SET abbreviation = "FC"   WHERE id = 3206;
 245 UPDATE civicrm_state_province SET abbreviation = "MC"   WHERE id = 3221;
 246 UPDATE civicrm_state_province SET abbreviation = "PU"   WHERE id = 3237;
 247
 248
 249 -- CRM-3609
 250
 251 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (5217, 1020, "BRU", "Brussels");
 252
 253
 254 -- Following wasn't done by 2.0 upgrade script, so adding here (also added to 2.0 script)
 255
 256 ALTER TABLE `civicrm_loc_block`
 257 DROP FOREIGN KEY `FK_civicrm_loc_block_email_id`,
 258 DROP FOREIGN KEY `FK_civicrm_loc_block_email_2_id`,
 259 DROP FOREIGN KEY `FK_civicrm_loc_block_phone_id`,
 260 DROP FOREIGN KEY  `FK_civicrm_loc_block_phone_2_id`,
 261 DROP FOREIGN KEY `FK_civicrm_loc_block_im_id`,
 262 DROP FOREIGN KEY  `FK_civicrm_loc_block_im_2_id`;
 263
 264 ALTER TABLE `civicrm_loc_block`
 265 ADD CONSTRAINT `FK_civicrm_loc_block_email_id`   FOREIGN KEY (`email_id`)   REFERENCES `civicrm_email` (`id`) ON DELETE SET NULL,
 266 ADD CONSTRAINT `FK_civicrm_loc_block_email_2_id` FOREIGN KEY (`email_2_id`) REFERENCES `civicrm_email` (`id`) ON DELETE SET NULL,
 267 ADD CONSTRAINT `FK_civicrm_loc_block_phone_id`   FOREIGN KEY (`phone_id`)   REFERENCES `civicrm_phone` (`id`) ON DELETE SET NULL,
 268 ADD CONSTRAINT `FK_civicrm_loc_block_phone_2_id` FOREIGN KEY (`phone_2_id`) REFERENCES `civicrm_phone` (`id`) ON DELETE SET NULL,
 269 ADD CONSTRAINT `FK_civicrm_loc_block_im_id`      FOREIGN KEY (`im_id`)      REFERENCES `civicrm_im` (`id`)    ON DELETE SET NULL,
 270 ADD CONSTRAINT `FK_civicrm_loc_block_im_2_id`    FOREIGN KEY (`im_2_id`)    REFERENCES `civicrm_im` (`id`)    ON DELETE SET NULL;
 271
 272
 273
 274 -- add the new civicrm_domain.locales column
 275
 276 ALTER TABLE civicrm_domain ADD locales text COMMENT 'list of locales supported by the current db state (NULL for single-lang install)';
 277
 278
 279
 280 -- new tables added to 2.0
 281
 282 CREATE TABLE `civicrm_cache` (
 283   `id` int(10) unsigned NOT NULL auto_increment,
 284   `group_name` varchar(32) collate utf8_unicode_ci NOT NULL COMMENT 'group name for cache element, useful in cleaning cache elements',
 285   `path` varchar(64) collate utf8_unicode_ci default NULL COMMENT 'Unique path name for cache element',
 286   `data` text collate utf8_unicode_ci COMMENT 'data associated with this path',
 287   `component_id` int(10) unsigned default NULL COMMENT 'Component that this menu item belongs to',
 288   `created_date` datetime default NULL COMMENT 'When was the cache item created',
 289   `expired_date` datetime default NULL COMMENT 'When should cache item expire',
 290   PRIMARY KEY  (`id`),
 291   UNIQUE `UI_group_path` (`group_name`,`path`)
 292 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 293
 294 ALTER TABLE `civicrm_cache`
 295   ADD CONSTRAINT `FK_civicrm_cache_component_id` FOREIGN KEY (`component_id`) REFERENCES `civicrm_component` (`id`);
 296
 297
 298 CREATE TABLE `civicrm_group_contact_cache` (
 299   `id` int(10) unsigned NOT NULL auto_increment COMMENT 'primary key',
 300   `group_id` int(10) unsigned NOT NULL COMMENT 'FK to civicrm_group',
 301   `contact_id` int(10) unsigned NOT NULL COMMENT 'FK to civicrm_contact',
 302   PRIMARY KEY  (`id`),
 303   UNIQUE `UI_contact_group` (`contact_id`,`group_id`)
 304 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 305
 306 ALTER TABLE `civicrm_group_contact_cache`
 307   ADD CONSTRAINT `FK_civicrm_group_contact_cache_group_id` FOREIGN KEY (`group_id`) REFERENCES `civicrm_group` (`id`) ON DELETE CASCADE,
 308   ADD CONSTRAINT `FK_civicrm_group_contact_cache_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
 309
 310 -- create discount table
 311 CREATE TABLE `civicrm_discount` (
 312     `id` int(10) unsigned NOT NULL COMMENT 'primary key' auto_increment,
 313     `entity_table` varchar(64) collate utf8_unicode_ci default NULL COMMENT 'physical tablename for entity being joined to discount, e.g. civicrm_event',
 314     `entity_id` int(10) unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
 315     `option_group_id` int(10) unsigned NOT NULL  COMMENT 'FK to civicrm_option_group',
 316     `start_date` date default NULL COMMENT 'Date when discount starts.',
 317     `end_date` date default NULL COMMENT 'Date when discount ends.',
 318     PRIMARY KEY (id),
 319     INDEX index_entity (entity_table, entity_id),
 320     INDEX index_entity_option_id (entity_table, entity_id, option_group_id),
 321     CONSTRAINT `FK_civicrm_discount_option_group_id` FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group` (`id`) ON DELETE CASCADE
 322    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
 323
 324
 325 -- menu table
 326
 327 CREATE TABLE `civicrm_menu` (
 328   id int unsigned NOT NULL AUTO_INCREMENT  ,
 329   path varchar(255)    COMMENT 'Path Name',
 330   path_arguments text    COMMENT 'Arguments to pass to the url',
 331   title varchar(255)    COMMENT 'Menu Title',
 332   access_callback varchar(255)    COMMENT 'Function to call to check access permissions',
 333   access_arguments text    COMMENT 'Arguments to pass to access callback',
 334   page_callback varchar(255)    COMMENT 'function to call for this url',
 335   page_arguments text    COMMENT 'Arguments to pass to page callback',
 336   breadcrumb text    COMMENT 'Breadcrumb for the path.',
 337   return_url varchar(255)    COMMENT 'Url where a page should redirected to, if next url not known.',
 338   return_url_args varchar(255)    COMMENT 'Arguments to pass to return_url',
 339   component_id int unsigned    COMMENT 'Component that this menu item belongs to',
 340   is_active tinyint    COMMENT 'Is this menu item active?',
 341   is_public tinyint    COMMENT 'Is this menu accessible to the public?',
 342   is_exposed tinyint    COMMENT 'Is this menu exposed to the navigation system?',
 343   is_ssl tinyint    COMMENT 'Should this menu be exposed via SSL if enabled?',
 344   weight int NOT NULL  DEFAULT 1 COMMENT 'Ordering of the menu items in various blocks.',
 345   type int NOT NULL  DEFAULT 1 COMMENT 'Drupal menu type.',
 346   page_type int NOT NULL  DEFAULT 1 COMMENT 'CiviCRM menu type.',
 347   PRIMARY KEY  (`id`),
 348   UNIQUE `UI_path` (`path`)
 349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 350
 351 ALTER TABLE `civicrm_menu`
 352   ADD CONSTRAINT `FK_civicrm_menu_component_id` FOREIGN KEY (`component_id`) REFERENCES `civicrm_component` (`id`);
 353
 354 -- pledge component insert
 355
 356 INSERT INTO civicrm_component (name, namespace) VALUES ('CiviPledge', 'CRM_Pledge' );
 357
 358 -- pledge tables
 359
 360 CREATE TABLE `civicrm_pledge` (
 361   `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Pledge ID',
 362   `contact_id` int(10) unsigned NOT NULL COMMENT 'Foreign key to civicrm_contact.id .',
 363   `contribution_type_id` int(10) unsigned default NULL COMMENT 'FK to Contribution Type. This is propagated to contribution record when pledge payments are made.',
 364   `contribution_page_id` int(10) unsigned default NULL COMMENT 'The Contribution Page which triggered this contribution',
 365   `amount` decimal(20,2) NOT NULL COMMENT 'Total pledged amount.',
 366   `frequency_unit` enum('day','week','month','year') collate utf8_unicode_ci default 'month' COMMENT 'Time units for recurrence of pledge payments.',
 367   `frequency_interval` int(10) unsigned NOT NULL default '1' COMMENT 'Number of time units for recurrence of pledge payments.',
 368   `frequency_day` int(10) unsigned NOT NULL default '3' COMMENT 'Day in the period when the pledge payment is due e.g. 1st of month, 15th etc. Use this to set the scheduled dates for pledge payments.',
 369   `installments` int(10) unsigned default '1' COMMENT 'Total number of payments to be made.',
 370   `start_date` datetime NOT NULL COMMENT 'The date the first scheduled pledge occurs.',
 371   `create_date` datetime NOT NULL COMMENT 'When this pledge record was created.',
 372   `acknowledge_date` datetime default NULL COMMENT 'When a pledge acknowledgement message was sent to the contributor.',
 373   `modified_date` datetime default NULL COMMENT 'Last updated date for this pledge record.',
 374   `cancel_date` datetime default NULL COMMENT 'Date this pledge was cancelled by contributor.',
 375   `end_date` datetime default NULL COMMENT 'Date this pledge finished successfully (total pledge payments equal to or greater than pledged amount).',
 376   `honor_contact_id` int(10) unsigned default NULL COMMENT 'FK to contact ID. Used when pledge is made in honor of another contact. This is propagated to contribution records when pledge payments are made.',
 377   `honor_type_id` int(10) unsigned default NULL COMMENT 'Implicit FK to civicrm_option_value.',
 378   `max_reminders` int(10) unsigned default '1' COMMENT 'The maximum number of payment reminders to send for any given payment.',
 379   `initial_reminder_day` int(10) unsigned default '5' COMMENT 'Send initial reminder this many days prior to the payment due date.',
 380   `additional_reminder_day` int(10) unsigned default '5' COMMENT 'Send additional reminder this many days after last one sent, up to maximum number of reminders.',
 381   `status_id` int(10) unsigned default NULL COMMENT 'Implicit foreign key to civicrm_option_values in the contribution_status option group.',
 382   `is_test` tinyint(4) default '0',
 383   PRIMARY KEY  (`id`),
 384   INDEX `index_status` (`status_id`),
 385   CONSTRAINT `FK_civicrm_pledge_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
 386   CONSTRAINT `FK_civicrm_pledge_contribution_type_id` FOREIGN KEY (`contribution_type_id`) REFERENCES `civicrm_contribution_type` (`id`) ON DELETE SET NULL,
 387   CONSTRAINT `FK_civicrm_pledge_contribution_page_id` FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page` (`id`) ON DELETE SET NULL,
 388   CONSTRAINT `FK_civicrm_pledge_honor_contact_id` FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL
 389 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 390
 391 CREATE TABLE `civicrm_pledge_block` (
 392   `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Pledge ID',
 393   `entity_table` varchar(64) collate utf8_unicode_ci default NULL COMMENT 'physical tablename for entity being joined to pledge, e.g. civicrm_contact',
 394   `entity_id` int(10) unsigned NOT NULL COMMENT 'FK to entity table specified in entity_table column.',
 395   `pledge_frequency_unit` varchar(128) collate utf8_unicode_ci default NULL COMMENT 'Delimited list of supported frequency units',
 396   `is_pledge_interval` tinyint(4) default '0' COMMENT 'Is frequency interval exposed on the contribution form.',
 397   `max_reminders` int(10) unsigned default '1' COMMENT 'The maximum number of payment reminders to send for any given payment.',
 398   `initial_reminder_day` int(10) unsigned default '5' COMMENT 'Send initial reminder this many days prior to the payment due date.',
 399   `additional_reminder_day` int(10) unsigned default '5' COMMENT 'Send additional reminder this many days after last one sent, up to maximum number of reminders.',
 400   PRIMARY KEY  (`id`),
 401   INDEX `index_entity` (`entity_table`,`entity_id`)
 402 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 403
 404 CREATE TABLE `civicrm_pledge_payment` (
 405   `id` int(10) unsigned NOT NULL auto_increment,
 406   `pledge_id` int(10) unsigned NOT NULL COMMENT 'FK to Pledge table',
 407   `contribution_id` int(10) unsigned default NULL COMMENT 'FK to contribution table.',
 408   `scheduled_amount` decimal(20,2) NOT NULL COMMENT 'Pledged amount for this payment (the actual contribution amount might be different).',
 409   `scheduled_date` datetime NOT NULL COMMENT 'The date the pledge payment is supposed to happen.',
 410   `reminder_date` datetime default NULL COMMENT 'The date that the most recent payment reminder was sent.',
 411   `reminder_count` int(10) unsigned default '0' COMMENT 'The number of payment reminders sent.',
 412   `status_id` int(10) unsigned default NULL,
 413   PRIMARY KEY  (`id`),
 414   UNIQUE `UI_contribution_pledge` (`contribution_id`, `pledge_id`),
 415   INDEX `index_status` (`status_id`),
 416   CONSTRAINT `FK_civicrm_pledge_payment_pledge_id` FOREIGN KEY (`pledge_id`) REFERENCES `civicrm_pledge` (`id`) ON DELETE CASCADE,
 417   CONSTRAINT `FK_civicrm_pledge_payment_contribution_id` FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution` (`id`) ON DELETE CASCADE
 418 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 419
 420 CREATE TABLE `civicrm_case_contact` (
 421     id int(10) unsigned NOT NULL auto_increment,
 422     `case_id` int(10) unsigned NOT NULL,
 423     `contact_id` int(10) unsigned NOT NULL,
 424     PRIMARY KEY (id),
 425     CONSTRAINT `FK_civicrm_case_contact_case_id` FOREIGN KEY (`case_id`) REFERENCES `civicrm_case` (`id`) ON DELETE CASCADE,
 426     CONSTRAINT `FK_civicrm_case_contact_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
 427     UNIQUE `UI_case_contact_id` (`case_id`, `contact_id`)
 428 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 429
 430 INSERT INTO civicrm_case_contact (case_id, contact_id)
 431 SELECT ca.id, ca.contact_id
 432 FROM   civicrm_case ca
 433 ON DUPLICATE KEY UPDATE case_id=ca.id;
 434
 435
 436 -- fixes noticed via db diff
 437
 438 ALTER TABLE `civicrm_contact`
 439     ADD `employer_id` int(10) unsigned NULL DEFAULT NULL AFTER user_unique_id,
 440     ADD INDEX `index_contact_type` (`contact_type`),
 441     ADD INDEX `index_contact_sub_type` (`contact_sub_type`),
 442     ADD INDEX `index_sort_name` (`sort_name`),
 443     ADD INDEX `index_hash` (`hash`),
 444     DROP INDEX index_contact_type_domain,
 445     DROP INDEX index_contact_sub_type_domain,
 446     DROP INDEX index_sort_name_domain,
 447     DROP INDEX index_hash_domain;
 448
 449
 450 UPDATE civicrm_contact cc1
 451 LEFT JOIN civicrm_contact cc2 ON cc1.mail_to_household_id=cc2.id
 452 SET cc1.mail_to_household_id=NULL
 453 WHERE cc2.id IS NULL;
 454
 455 ALTER TABLE `civicrm_contact`
 456   ADD CONSTRAINT `FK_civicrm_contact_mail_to_household_id` FOREIGN KEY (`mail_to_household_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL,
 457   ADD CONSTRAINT `FK_civicrm_contact_employer_id` FOREIGN KEY (`employer_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL;
 458
 459
 460 ALTER TABLE `civicrm_contribution_recur`
 461     ADD UNIQUE `UI_contrib_trxn_id` (`trxn_id`),
 462     ADD UNIQUE `UI_contrib_invoice_id` (`invoice_id`);
 463
 464
 465 ALTER TABLE `civicrm_contribution_type`
 466     ADD UNIQUE `UI_name` (name);
 467
 468 ALTER TABLE `civicrm_custom_field`
 469     ADD `is_view` tinyint(4) NULL DEFAULT NULL AFTER is_active,
 470     MODIFY `html_type` enum('Text','TextArea','Select','Multi-Select','Radio','CheckBox','Select Date','Select State/Province','Select Country','Multi-Select Country','Multi-Select State/Province','File','Link','RichTextEditor') NOT NULL DEFAULT 'Text';
 471
 472 ALTER TABLE `civicrm_dedupe_rule_group`
 473     ADD name varchar(64) NULL DEFAULT NULL AFTER is_default;
 474
 475 DROP TABLE `civicrm_dupe_match`;
 476
 477 ALTER TABLE `civicrm_event`
 478     DROP `receipt_text`;
 479
 480 ALTER TABLE `civicrm_event_page`
 481     ADD `is_multiple_registrations` tinyint(4) NULL DEFAULT '0' AFTER pay_later_receipt,
 482     ADD `default_discount_id` int(10) unsigned NULL DEFAULT NULL AFTER default_fee_id;
 483
 484 ALTER TABLE `civicrm_financial_trxn`
 485     ADD UNIQUE `UI_ft_trxn_id` (`trxn_id`);
 486
 487 ALTER TABLE `civicrm_grant`
 488     MODIFY `amount_requested` decimal(20,2) NULL DEFAULT NULL,
 489     MODIFY `amount_granted` decimal(20,2) NULL DEFAULT NULL;
 490
 491 ALTER TABLE `civicrm_group`
 492     ADD `cache_date` datetime NULL DEFAULT NULL AFTER group_type,
 493     ADD parents text NULL DEFAULT NULL AFTER cache_date,
 494     ADD children text NULL DEFAULT NULL AFTER parents,
 495     MODIFY description text NULL DEFAULT NULL,
 496     ADD UNIQUE `UI_title` (title),
 497     ADD UNIQUE `UI_name` (name);
 498
 499 ALTER TABLE `civicrm_location_type`
 500     ADD UNIQUE `UI_name` (name);
 501
 502 ALTER TABLE `civicrm_mailing`
 503     MODIFY `body_text` longtext NULL DEFAULT NULL,
 504     MODIFY `body_html` longtext NULL DEFAULT NULL;
 505
 506 ALTER TABLE `civicrm_membership_type`
 507     ALTER `fixed_period_start_day`  DROP DEFAULT,
 508     ALTER `fixed_period_rollover_day`  DROP DEFAULT;
 509
 510 ALTER TABLE `civicrm_note`
 511     MODIFY `contact_id` int(10) unsigned NULL DEFAULT NULL;
 512
 513 ALTER TABLE `civicrm_participant`
 514     CHANGE `event_level` `fee_level` varchar(255) NULL DEFAULT NULL,
 515     ADD `is_pay_later` tinyint(4) NULL DEFAULT '0' AFTER is_test,
 516     ADD `fee_amount` decimal(20,2) NULL DEFAULT NULL AFTER is_pay_later,
 517     ADD `registered_by_id` int(10) unsigned NULL DEFAULT NULL AFTER fee_amount,
 518     ADD `discount_id` int(10) unsigned NULL DEFAULT NULL AFTER registered_by_id;
 519
 520 ALTER TABLE `civicrm_participant`
 521     ADD CONSTRAINT `FK_civicrm_participant_registered_by_id` FOREIGN KEY (`registered_by_id`) REFERENCES `civicrm_participant` (`id`) ON DELETE SET NULL,
 522     ADD CONSTRAINT `FK_civicrm_participant_discount_id` FOREIGN KEY (`discount_id`) REFERENCES `civicrm_discount` (`id`) ON DELETE SET NULL;
 523
 524 ALTER TABLE `civicrm_preferences`
 525     ADD `editor_id` int(10) unsigned NULL DEFAULT NULL AFTER address_standardization_url;
 526
 527 ALTER TABLE `civicrm_price_field`
 528     MODIFY name varchar(255) NOT NULL DEFAULT '',
 529     MODIFY label varchar(255) NOT NULL DEFAULT '';
 530
 531 ALTER TABLE `civicrm_price_set`
 532     MODIFY name varchar(255) NOT NULL DEFAULT '',
 533     MODIFY title varchar(255) NOT NULL DEFAULT '';
 534
 535 ALTER TABLE `civicrm_relationship`
 536     MODIFY `is_permission_a_b` tinyint(4) NULL DEFAULT '0',
 537     MODIFY `is_permission_b_a` tinyint(4) NULL DEFAULT '0';
 538
 539 ALTER TABLE `civicrm_relationship_type`
 540     ADD UNIQUE `UI_name_a_b` (`name_a_b`),
 541     ADD UNIQUE `UI_name_b_a` (`name_b_a`);
 542
 543 ALTER TABLE `civicrm_tag`
 544     ADD UNIQUE `UI_name` (name);
 545
 546 ALTER TABLE `civicrm_uf_group`
 547     ADD `group_type` varchar(255) NULL DEFAULT NULL AFTER is_active,
 548     DROP `form_type`;
 549
 550 ALTER TABLE `civicrm_uf_match`
 551     ADD UNIQUE `UI_uf_name` (`uf_name`);
 552
 553 ALTER TABLE `civicrm_contribution`
 554     ADD `is_pay_later` tinyint(4) NULL DEFAULT '0' AFTER is_test,
 555     ADD UNIQUE `UI_contrib_trxn_id` (`trxn_id`),
 556     ADD UNIQUE `UI_contrib_invoice_id` (`invoice_id`);
 557
 558 ALTER TABLE `civicrm_membership`
 559     ADD `is_pay_later` tinyint(4) NULL DEFAULT '0' AFTER is_test;
 560
 561 ALTER TABLE `civicrm_membership_status`
 562     ADD `is_reserved` tinyint(4) NULL DEFAULT '0' AFTER is_active;
 563
 564 ALTER TABLE `civicrm_payment_processor_type`
 565     ADD UNIQUE `UI_name` (name);
 566
 567 UPDATE civicrm_dedupe_rule SET rule_table='civicrm_contact' WHERE rule_table IN ('civicrm_individual', 'civicrm_household', 'civicrm_organization');
 568
 569 ALTER TABLE `civicrm_case`
 570     DROP FOREIGN KEY `FK_civicrm_case_contact_id`;
 571 ALTER TABLE `civicrm_case`
 572     DROP `contact_id`;
 573
 574 ALTER TABLE `civicrm_case_activity`
 575     DROP FOREIGN KEY `FK_civicrm_case_activity_case_id`;
 576 ALTER TABLE `civicrm_case_activity`
 577     ADD CONSTRAINT `FK_civicrm_case_activity_case_id` FOREIGN KEY (`case_id`) REFERENCES `civicrm_case` (`id`) ON DELETE CASCADE;
 578
 579
 580 -- current employer data upgrade
 581
 582 SELECT @relTypeId := id FROM civicrm_relationship_type WHERE name_a_b='Employee of';
 583
 584 UPDATE civicrm_relationship cr
 585 INNER JOIN civicrm_contact cci ON (cr.contact_id_a=cci.id)
 586 INNER JOIN civicrm_contact cco ON (cr.contact_id_b=cco.id)
 587 SET   cci.employer_id = cr.contact_id_b,
 588       cci.organization_name = cco.organization_name
 589 WHERE cr.relationship_type_id=@relTypeId AND cr.is_active=1 AND IF(cr.end_date IS NULL, 1, (DATEDIFF(CURDATE( ), cr.end_date) <= 0));
 590
 591 -- table civicrm_mailing_event_forward, adding on delete set null
 592
 593 ALTER TABLE `civicrm_mailing_event_forward`
 594     MODIFY dest_queue_id   int(10) unsigned DEFAULT NULL COMMENT 'FK to EventQueue for destination',
 595     DROP FOREIGN KEY `FK_civicrm_mailing_event_forward_dest_queue_id`;
 596 ALTER TABLE `civicrm_mailing_event_forward`
 597     ADD CONSTRAINT `FK_civicrm_mailing_event_forward_dest_queue_id` FOREIGN KEY (`dest_queue_id`) REFERENCES `civicrm_mailing_event_queue` (`id`) ON DELETE SET NULL;
 598
 599 -- table civicrm_payment_processor_type, insert ebay record
 600
 601 INSERT INTO `civicrm_payment_processor_type`
 602  (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 )
 603 VALUES
 604 ('eWAY', 'eWAY (Single Currency)',NULL,1,0,'Customer ID',NULL,NULL,NULL,'Payment_eWAY','https://www.eway.com.au/gateway_cvn/xmlpayment.asp',NULL,NULL,NULL,'https://www.eway.com.au/gateway_cvn/xmltest/testpage.asp',NULL,NULL,NULL,1,0);
 605
 606 UPDATE `civicrm_payment_processor_type`
 607 SET is_recur = NULL WHERE title = 'Authorize.Net - AIM';
 608
 609 --address name field in civicrm_address table
 610
 611 ALTER TABLE `civicrm_address`
 612  ADD `name` varchar(255) collate utf8_unicode_ci DEFAULT NULL ;
 613
 614 -- fix for CRM-3469
 615 ALTER TABLE `civicrm_line_item`
 616   DROP FOREIGN KEY FK_civicrm_line_item_price_field_id;
 617
 618 ALTER TABLE `civicrm_line_item`
 619   ADD CONSTRAINT `FK_civicrm_line_item_price_field_id` FOREIGN KEY (`price_field_id`) REFERENCES `civicrm_price_field` (`id`) ON DELETE CASCADE;
 620