crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/sql/civicrm_upgradedb_v1.5_v1.6_41.mysql

   1 -- +--------------------------------------------------------------------+
   2 -- | CiviCRM version 4.6                                                |
   3 -- +--------------------------------------------------------------------+
   4 -- | Copyright CiviCRM LLC (c) 2004-2015                                |
   5 -- +--------------------------------------------------------------------+
   6 -- | This file is a part of CiviCRM.                                    |
   7 -- |                                                                    |
   8 -- | CiviCRM is free software; you can copy, modify, and distribute it  |
   9 -- | under the terms of the GNU Affero General Public License           |
  10 -- | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  11 -- |                                                                    |
  12 -- | CiviCRM is distributed in the hope that it will be useful, but     |
  13 -- | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  14 -- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  15 -- | See the GNU Affero General Public License for more details.        |
  16 -- |                                                                    |
  17 -- | You should have received a copy of the GNU Affero General Public   |
  18 -- | License and the CiviCRM Licensing Exception along                  |
  19 -- | with this program; if not, contact CiviCRM LLC                     |
  20 -- | at info[AT]civicrm[DOT]org. If you have questions about the        |
  21 -- | GNU Affero General Public License or the licensing of CiviCRM,     |
  22 -- | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  23 -- +--------------------------------------------------------------------+
  24 -- /*******************************************************
  25 -- All domain-keyed values handled by this included file
  26 -- Handles all domain-keyed data. Included in civicrm_data.tpl for base initialization (@domain_id = 1).
  27 -- *******************************************************/
  28
  29     set @domain_id = 1;
  30
  31 -- /*******************************************************
  32 -- *
  33 -- * update the option group and option value
  34 -- *
  35 -- *******************************************************/
  36
  37 INSERT INTO
  38    `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
  39 VALUES
  40    (@domain_id, 'activity_type'                 , 'Activity Type'                      , 0, 1),
  41    (@domain_id, 'gender'                        , 'Gender'                             , 0, 1),
  42    (@domain_id, 'instant_messenger_service'     , 'Instant Messenger (IM) screen-names', 0, 1),
  43    (@domain_id, 'mobile_provider'               , 'Mobile Phone Providers'             , 0, 1),
  44    (@domain_id, 'individual_prefix'             , 'Individual contact prefixes.'       , 0, 1),
  45    (@domain_id, 'individual_suffix'             , 'Individual contact suffixes.'       , 0, 1),
  46    (@domain_id, 'acl_role'                      , 'ACL Role.'                          , 0, 1),
  47    (@domain_id, 'accept_creditcard'             , 'Accept Credit Card'                 , 0, 1),
  48    (@domain_id, 'payment_instrument'            , 'Payment Instrument'                 , 0, 1),
  49    (@domain_id, 'contribution_status'           , 'Contribution Status'               , 0, 1);
  50
  51 SELECT @option_group_id_act            := max(id) from civicrm_option_group where name = 'activity_type';
  52 SELECT @option_group_id_gender         := max(id) from civicrm_option_group where name = 'gender';
  53 SELECT @option_group_id_IMProvider     := max(id) from civicrm_option_group where name = 'instant_messenger_service';
  54 SELECT @option_group_id_mobileProvider := max(id) from civicrm_option_group where name = 'mobile_provider';
  55 SELECT @option_group_id_prefix         := max(id) from civicrm_option_group where name = 'individual_prefix';
  56 SELECT @option_group_id_suffix         := max(id) from civicrm_option_group where name = 'individual_suffix';
  57 SELECT @option_group_id_aclRole        := max(id) from civicrm_option_group where name = 'acl_role';
  58 SELECT @option_group_id_acc            := max(id) from civicrm_option_group where name = 'accept_creditcard';
  59 SELECT @option_group_id_pi             := max(id) from civicrm_option_group where name = 'payment_instrument';
  60 SELECT @option_group_id_cs             := max(id) from civicrm_option_group where name = 'contribution_status';
  61
  62 INSERT INTO
  63    `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
  64 VALUES
  65    (@option_group_id_act, 'Meeting', 1, 'Meeting',NULL, 0, NULL, 1, 'Schedule a meeting', 0, 0, 1),
  66    (@option_group_id_act, 'Phone Call', 2, 'Phone Call', NULL,  0, NULL, 2, 'Schedule a Phone Call', 0, 0, 1),
  67    (@option_group_id_act, 'Email', 3, 'Email', NULL, 0, NULL, 3, 'Email Sent', 0, 0, 1),
  68    (@option_group_id_act, 'SMS', 4, 'SMS', NULL, 0, NULL, 4, 'SMS', 0, 0, 1),
  69    (@option_group_id_act, 'Event', 5,'Event', NULL, 0, NULL, 5, 'Event', 0, 0, 1),
  70
  71    (@option_group_id_gender, 'Female',      1, 'Female',      NULL, 0, NULL, 1, NULL, 0, 0, 1),
  72    (@option_group_id_gender, 'Male',        2, 'Male',        NULL, 0, NULL, 2, NULL, 0, 0, 1),
  73    (@option_group_id_gender, 'Transgender', 3, 'Transgender', NULL, 0, NULL, 3, NULL, 0, 0, 1),
  74
  75    (@option_group_id_IMProvider, 'Yahoo', 1, 'Yahoo', NULL, 0, NULL, 1, NULL, 0, 0, 1),
  76    (@option_group_id_IMProvider, 'MSN',   2, 'Msn',   NULL, 0, NULL, 2, NULL, 0, 0, 1),
  77    (@option_group_id_IMProvider, 'AIM',   3, 'Aim',   NULL, 0, NULL, 3, NULL, 0, 0, 1),
  78    (@option_group_id_IMProvider, 'GTalk', 4, 'Gtalk', NULL, 0, NULL, 4, NULL, 0, 0, 1),
  79    (@option_group_id_IMProvider, 'Jabber',5, 'Jabber',NULL, 0, NULL, 5, NULL, 0, 0, 1),
  80    (@option_group_id_IMProvider, 'Skype', 6, 'Skype', NULL, 0, NULL, 6, NULL, 0, 0, 1),
  81
  82    (@option_group_id_mobileProvider, 'Sprint'  , 1, 'Sprint'  , NULL, 0, NULL, 1, NULL, 0, 0, 1),
  83    (@option_group_id_mobileProvider, 'Verizon' , 2, 'Verizon' , NULL, 0, NULL, 2, NULL, 0, 0, 1),
  84    (@option_group_id_mobileProvider, 'Cingular', 3, 'Cingular', NULL, 0, NULL, 3, NULL, 0, 0, 1),
  85
  86    (@option_group_id_prefix, 'Mrs', 1, 'Mrs', NULL, 0, NULL, 1, NULL, 0, 0, 1),
  87    (@option_group_id_prefix, 'Ms',  2, 'Ms', NULL, 0, NULL, 2, NULL, 0, 0, 1),
  88    (@option_group_id_prefix, 'Mr',  3, 'Mr', NULL, 0, NULL, 3, NULL, 0, 0, 1),
  89    (@option_group_id_prefix, 'Dr',  4, 'Dr', NULL, 0, NULL, 4, NULL, 0, 0, 1),
  90
  91    (@option_group_id_suffix, 'Jr',  1, 'Jr', NULL, 0, NULL, 1, NULL, 0, 0, 1),
  92    (@option_group_id_suffix, 'Sr',  2, 'Sr', NULL, 0, NULL, 2, NULL, 0, 0, 1),
  93    (@option_group_id_suffix, 'II',  3, 'II', NULL, 0, NULL, 3, NULL, 0, 0, 1),
  94    (@option_group_id_suffix, 'III', 4, 'III', NULL, 0, NULL, 4, NULL, 0, 0, 1),
  95    (@option_group_id_suffix, 'IV',  5, 'IV',  NULL, 0, NULL, 5, NULL, 0, 0, 1),
  96    (@option_group_id_suffix, 'V',   6, 'V',   NULL, 0, NULL, 6, NULL, 0, 0, 1),
  97    (@option_group_id_suffix, 'VI',  7, 'VI',  NULL, 0, NULL, 7, NULL, 0, 0, 1),
  98    (@option_group_id_suffix, 'VII', 8, 'VII', NULL, 0, NULL, 8, NULL, 0, 0, 1),
  99
 100    (@option_group_id_aclRole, 'Administrator',  1, 'Admin', NULL, 0, NULL, 1, NULL, 0, 0, 1),
 101    (@option_group_id_aclRole, 'Authenticated',  2, 'Auth' , NULL, 0, NULL, 2, NULL, 0, 0, 1),
 102
 103    (@option_group_id_acc, 'Visa',  1, 'Visa', NULL, 0, NULL, 1, NULL, 0, 0, 1),
 104    (@option_group_id_acc, 'MasterCard',  2, 'MasterCard', NULL, 0, NULL, 2, NULL, 0, 0, 1),
 105    (@option_group_id_acc, 'American Express',  3, 'American Express', NULL, 0, NULL, 3, NULL, 0, 0, 1),
 106    (@option_group_id_acc, 'Discover',  4, 'Discover', NULL, 0, NULL, 4, NULL, 0, 0, 1),
 107
 108   (@option_group_id_pi, 'Credit Card',  1, 'Credit Card', NULL, 0, NULL, 1, NULL, 0, 0, 1),
 109   (@option_group_id_pi, 'Debit Card',  2, 'Debit Card', NULL, 0, NULL, 2, NULL, 0, 0, 1),
 110   (@option_group_id_pi, 'Cash',  3, 'Cash', NULL, 0, NULL, 3, NULL, 0, 0, 1),
 111   (@option_group_id_pi, 'Check',  4, 'Check', NULL, 0, NULL, 4, NULL, 0, 0, 1),
 112   (@option_group_id_pi, 'EFT',  5, 'EFT', NULL, 0, NULL, 5, NULL, 0, 0, 1),
 113
 114   (@option_group_id_cs, 'Completed', 1, 'Completed', NULL, 0, NULL, 1, NULL, 0, 0, 1),
 115   (@option_group_id_cs, 'Pending'  , 2, 'Pending'  , NULL, 0, NULL, 2, NULL, 0, 0, 1),
 116   (@option_group_id_cs, 'Cancelled', 3, 'Cancelled', NULL, 0, NULL, 3, NULL, 0, 0, 1);
 117
 118 -- /*******************************************************
 119 -- *
 120 -- * add the Netherlands state of Limburg (CRM-1228)
 121 -- *
 122 -- *******************************************************/
 123
 124    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (5176, 1152, "LI", "Limburg");
 125
 126 -- /*******************************************************
 127 -- *
 128 -- * update the province list (CRM-1271)
 129 -- *
 130 -- *******************************************************/
 131
 132    UPDATE civicrm_state_province SET name="Co Antrim"          WHERE id=2595;
 133    UPDATE civicrm_state_province SET name="Co Armagh"          WHERE id=2598;
 134    UPDATE civicrm_state_province SET name="Bristol"            WHERE id=2620;
 135    UPDATE civicrm_state_province SET name="Co Down"            WHERE id=2652;
 136    UPDATE civicrm_state_province SET name="Co Durham"          WHERE id=2657;
 137    UPDATE civicrm_state_province SET name="Co Fermanagh"       WHERE id=2670;
 138    UPDATE civicrm_state_province SET name="Glasgow"            WHERE id=2674;
 139    UPDATE civicrm_state_province SET name="Herefordshire"      WHERE id=2687;
 140    UPDATE civicrm_state_province SET name="Kingston upon Hull" WHERE id=2700;
 141    UPDATE civicrm_state_province SET name="North Ayrshire"     WHERE id=2735;
 142    UPDATE civicrm_state_province SET name="Cleveland"          WHERE id=2760;
 143    UPDATE civicrm_state_province SET name="Rhondda Cynon Taff" WHERE id=2762;
 144    UPDATE civicrm_state_province SET name="Scottish Borders"   WHERE id=2770;
 145    UPDATE civicrm_state_province SET name="Vale of Glamorgan"  WHERE id=2804;
 146
 147    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9000, 1196, "NW", "North West" );
 148    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9986, 1226, "xTYW", "Tyne and Wear");
 149    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9987, 1226, "xIOM", "Isle of Man");
 150    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9988, 1226, "xGMA", "Greater Manchester");
 151    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9989, 1226, "xTYR", "Co Tyrone");
 152    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9990, 1226, "xWYO", "West Yorkshire");
 153    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9991, 1226, "xSYO", "South Yorkshire");
 154    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9992, 1226, "xMSD", "Merseyside");
 155    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9993, 1226, "xBRK", "Berkshire");
 156    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9994, 1226, "xWMD", "West Midlands");
 157    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9995, 1226, "xIVN", "Inverness");
 158    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9996, 1226, "xAVN", "Avon");
 159    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9997, 1226, "xMDX", "Middlesex");
 160    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9998, 1226, "xWGM", "West Glamorgan");
 161    INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9999, 1226, "xLON", "London");
 162
 163 -- /*******************************************************
 164 -- *
 165 -- * update database (CRM-1275)
 166 -- *
 167 -- *******************************************************/
 168
 169 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 170
 171 -- /*******************************************************
 172 -- *
 173 -- * Clean up the exisiting tables
 174 -- *
 175 -- *******************************************************/
 176
 177 DROP TABLE IF EXISTS civicrm_accept_credit_card;
 178 DROP TABLE IF EXISTS civicrm_activity_type;
 179 DROP TABLE IF EXISTS civicrm_acl_group;
 180 DROP TABLE IF EXISTS civicrm_gender;
 181 DROP TABLE IF EXISTS civicrm_individual_prefix;
 182 DROP TABLE IF EXISTS civicrm_individual_suffix;
 183 DROP TABLE IF EXISTS civicrm_im_provider;
 184 DROP TABLE IF EXISTS civicrm_mobile_provider;
 185 DROP TABLE IF EXISTS civicrm_payment_instrument;
 186 DROP TABLE IF EXISTS civicrm_acl_group_join;
 187
 188 -- /*******************************************************
 189 -- *
 190 -- * civicrm_acl_cache
 191 -- *
 192 -- * Cache for acls and contacts
 193 -- *
 194 -- *******************************************************/
 195 CREATE TABLE civicrm_acl_cache (
 196
 197      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
 198      contact_id int unsigned    COMMENT 'Foreign Key to Contact',
 199      acl_id int unsigned NOT NULL   COMMENT 'Foreign Key to ACL',
 200      modified_date date    COMMENT 'When was this cache entry last modified'
 201 ,
 202     PRIMARY KEY ( id )
 203 ,
 204      INDEX index_acl_id( acl_id),
 205      INDEX contact_id (contact_id)
 206 ,
 207      FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
 208 ,
 209      FOREIGN KEY (acl_id) REFERENCES civicrm_acl(id)
 210
 211 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 212
 213 -- /*******************************************************
 214 -- *
 215 -- * civicrm_acl_entity_role
 216 -- *
 217 -- * Join table for Contacts and Groups to ACL Roles
 218 -- *
 219 -- *******************************************************/
 220 CREATE TABLE civicrm_acl_entity_role (
 221      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
 222      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this contact',
 223      acl_role_id int unsigned NOT NULL   COMMENT 'Foreign Key to ACL Role (which is an option value pair and hence an implicit FK)',
 224      entity_table varchar(64) NOT NULL   COMMENT 'Table of the object joined to the ACL Role (Contact or Group)',
 225      entity_id int unsigned NOT NULL   COMMENT 'ID of the group/contact object being joined',
 226      is_active tinyint    COMMENT 'Is this property active?'
 227 ,
 228     PRIMARY KEY ( id )
 229
 230     ,     INDEX index_role(
 231         acl_role_id
 232   )
 233   ,     INDEX index_entity(
 234         entity_table
 235       , entity_id
 236   )
 237   ,     INDEX domain_id (
 238   domain_id
 239   )
 240   ,
 241      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 242
 243 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 244
 245 -- /*******************************************************
 246 -- *
 247 -- * civicrm_msg_template
 248 -- *
 249 -- * Users will need a way to save and retrieve templates with tokens for use in recurring email communication tasks
 250 -- *
 251 -- *******************************************************/
 252 CREATE TABLE civicrm_msg_template (
 253
 254      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Message Template ID',
 255      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this contact',
 256      msg_title varchar(255)    COMMENT 'Descriptive title of message',
 257      msg_subject varchar(255)    COMMENT 'Subject for email message.',
 258      msg_text text    COMMENT 'Text formatted message',
 259      msg_html text    COMMENT 'HTML formatted message',
 260      is_active tinyint   DEFAULT 1
 261 ,
 262     PRIMARY KEY ( id )
 263
 264
 265 ,
 266      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 267
 268 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 269
 270 -- /*******************************************************
 271 -- *
 272 -- * Modify the civicrm_acl  Table Structure
 273 -- *******************************************************/
 274
 275   ALTER TABLE `civicrm_acl` ADD domain_id int(10) unsigned NOT NULL   COMMENT 'Which Domain owns this contact';
 276   ALTER TABLE `civicrm_acl` ADD FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`);
 277   ALTER TABLE `civicrm_acl` ADD INDEX `domain_id` (`domain_id`);
 278
 279   ALTER TABLE `civicrm_acl` ADD name varchar(64) COMMENT 'ACL Name.';
 280
 281   ALTER TABLE `civicrm_acl` CHANGE `operation` `operation` ENUM('All', 'View', 'Edit', 'Create', 'Delete', 'Grant', 'Revoke') NOT NULL   COMMENT 'What operation does this ACL entry control?';
 282   ALTER TABLE `civicrm_acl` ADD is_active tinyint(4) COMMENT 'Is this property active?' AFTER acl_id;
 283
 284   ALTER TABLE `civicrm_acl` ADD INDEX index_acl_id (acl_id);
 285
 286 -- /*******************************************************
 287 -- *
 288 -- * civicrm_activity
 289 -- *
 290 -- * Join table for activities
 291 -- *
 292 -- *******************************************************/
 293
 294   ALTER TABLE `civicrm_activity` DROP FOREIGN KEY civicrm_activity_ibfk_2;
 295   ALTER TABLE `civicrm_activity` ADD INDEX UI_activity_type_id (`activity_type_id`);
 296
 297 -- /*******************************************************
 298 -- *
 299 -- * civicrm_activity_history
 300 -- *
 301 -- * Record history for an entity in the crm module
 302 -- *
 303 -- *******************************************************/
 304
 305   ALTER TABLE civicrm_activity_history ADD INDEX index_activity (activity_id);
 306
 307 -- /*******************************************************
 308 -- *
 309 -- * civicrm_contact
 310 -- *
 311 -- * Three types of contacts are defined: Individual, Organization and Household. Contact objects are defined by a civicrm_contact record plus a related civicrm_contact_type record.
 312 -- *
 313 -- *******************************************************/
 314
 315   ALTER TABLE civicrm_contact ADD INDEX index_contact_type_domain (contact_type, domain_id);
 316   ALTER TABLE civicrm_contact ADD INDEX index_contact_sub_type_domain (contact_sub_type, domain_id);
 317
 318   ALTER TABLE civicrm_contact ADD INDEX index_preferred_communication_method (preferred_communication_method);
 319
 320   UPDATE civicrm_contact
 321   SET `preferred_communication_method` = CONCAT(char(1),`preferred_communication_method`,char(1))
 322   WHERE civicrm_contact.preferred_communication_method IS NOT NULL;
 323
 324 -- /*******************************************************
 325 -- *
 326 -- * civicrm_contribution
 327 -- *
 328 -- *******************************************************/
 329
 330   ALTER TABLE `civicrm_contribution` DROP FOREIGN KEY `civicrm_contribution_ibfk_6`;
 331   ALTER TABLE `civicrm_contribution` DROP FOREIGN KEY `civicrm_contribution_ibfk_7`;
 332
 333   ALTER TABLE `civicrm_contribution` DROP `recur_contribution_id`;
 334
 335   ALTER TABLE `civicrm_contribution` ADD `honor_contact_id` int unsigned COMMENT 'FK to contact ID';
 336   ALTER TABLE `civicrm_contribution` ADD FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact` (`id`);
 337
 338   ALTER TABLE `civicrm_contribution` ADD contribution_recur_id int unsigned COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each contribution made in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.' AFTER note;
 339   ALTER TABLE `civicrm_contribution` ADD FOREIGN KEY (`contribution_recur_id`) REFERENCES civicrm_contribution_recur(`id`);
 340
 341   ALTER TABLE `civicrm_contribution` ADD is_test tinyint DEFAULT 0;
 342   ALTER TABLE `civicrm_contribution` ADD `contribution_status_id` int unsigned NULL DEFAULT '1';
 343     ALTER TABLE `civicrm_contribution` ADD INDEX honor_contact_id (`honor_contact_id`);
 344   ALTER TABLE `civicrm_contribution` ADD INDEX UI_contrib_payment_instrument_id (payment_instrument_id);
 345   ALTER TABLE `civicrm_contribution` ADD INDEX index_contribution_status (contribution_status_id);
 346
 347 -- /*******************************************************
 348 -- *
 349 -- * civicrm_contribution_page
 350 -- *
 351 -- * A Contribution object store meta information about a single customized contribution page
 352 -- *
 353 -- *******************************************************/
 354
 355   ALTER TABLE `civicrm_contribution_page` ADD honor_block_is_active tinyint COMMENT 'Should this contribution have the honor  block enabled?';
 356   ALTER TABLE `civicrm_contribution_page` ADD honor_block_title varchar(255) COMMENT 'Title for honor block.';
 357   ALTER TABLE `civicrm_contribution_page` ADD honor_block_text text COMMENT 'text for honor block.';
 358   ALTER TABLE `civicrm_contribution_page` ADD is_monetary tinyint NULL DEFAULT '1' COMMENT 'if true - allows real-time monetary transactions otherwise non-monetary transactions';
 359   ALTER TABLE `civicrm_contribution_page` ADD is_recur tinyint NULL DEFAULT '0' COMMENT 'if true - allows recurring contributions, valid only for PayPal_Standard' AFTER is_monetary;
 360
 361 -- /*******************************************************
 362 -- *
 363 -- * civicrm_contribution_recur
 364 -- *
 365 -- *******************************************************/
 366   ALTER TABLE civicrm_contribution_recur DROP is_active;
 367
 368   ALTER TABLE civicrm_contribution_recur ADD domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contribution class.' AFTER id;
 369       ALTER TABLE civicrm_contribution_recur ADD trxn_id varchar(255) COMMENT 'unique transaction id. may be processor id, bank id + trans id, or account number + check number... depending on payment_method';
 370       ALTER TABLE civicrm_contribution_recur ADD invoice_id varchar(255) COMMENT 'unique invoice id, system generated or passed in' AFTER trxn_id;
 371       ALTER TABLE civicrm_contribution_recur ADD contribution_status_id int unsigned NULL DEFAULT '1' AFTER invoice_id;
 372   ALTER TABLE civicrm_contribution_recur ADD end_date datetime NOT NULL COMMENT 'Date this recurring contribution finished successfully' AFTER cancel_date;
 373   ALTER TABLE civicrm_contribution_recur ADD is_test tinyint NULL DEFAULT '0'AFTER contribution_status_id  ;
 374
 375   ALTER TABLE civicrm_contribution_recur MODIFY start_date datetime NOT NULL COMMENT 'The date the first scheduled recurring contribution occurs.';
 376       ALTER TABLE civicrm_contribution_recur MODIFY create_date datetime NOT NULL COMMENT 'When this recurring contribution record was created.';
 377       ALTER TABLE civicrm_contribution_recur MODIFY modified_date datetime NOT NULL COMMENT 'Last updated date for this record. mostly the last time a payment was received';
 378       ALTER TABLE civicrm_contribution_recur MODIFY cancel_date datetime NOT NULL COMMENT 'Date this recurring contribution was cancelled by contributor- if we can get access to it';
 379   ALTER TABLE civicrm_contribution_recur MODIFY next_sched_contribution datetime NOT NULL COMMENT 'At Groundspring this was used by the cron job which triggered payments. If we\'re not doing that but we know about payments, it might still be useful to store for display to org andor contributors.';
 380       ALTER TABLE civicrm_contribution_recur MODIFY failure_retry_date datetime COMMENT 'At Groundspring we set a business rule to retry failed payments every 7 days - and stored the next scheduled attempt date there.';
 381
 382   ALTER TABLE civicrm_contribution_recur ADD INDEX index_contribution_status( contribution_status_id );
 383
 384   ALTER TABLE civicrm_contribution_recur ADD FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id);
 385
 386 -- /*******************************************************
 387 -- *
 388 -- * civicrm_custom_group
 389 -- *
 390 -- * All extended (custom) properties are associated with a group. These are logical sets of related data.
 391 -- *
 392 -- *******************************************************/
 393
 394   ALTER TABLE `civicrm_custom_group` ADD extends_entity_column_name varchar(64) COMMENT 'linking custom group for dynamic object';
 395   ALTER TABLE `civicrm_custom_group` ADD extends_entity_column_value varchar(64) COMMENT 'linking custom group for dynamic object';
 396   ALTER TABLE `civicrm_custom_group` MODIFY extends enum('Contact','Individual','Household','Organization','Location','Address','Contribution','Activity','Relationship','Phonecall','Meeting','Group','Membership') NULL DEFAULT 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).';
 397
 398    UPDATE civicrm_custom_group
 399    SET extends_entity_column_value=NULL
 400    WHERE extends='Activity';
 401
 402   UPDATE civicrm_custom_group
 403    SET extends='Activity', extends_entity_column_value='2'
 404    WHERE extends='Phonecall';
 405
 406   UPDATE civicrm_custom_group
 407    SET extends='Activity', extends_entity_column_value='1'
 408    WHERE extends='Meeting';
 409
 410   UPDATE civicrm_custom_group
 411    SET extends_entity_column_value=NULL
 412    WHERE extends='Contact';
 413
 414   UPDATE civicrm_custom_group
 415    SET extends_entity_column_value=NULL
 416    WHERE extends='Relationship';
 417
 418 -- /*******************************************************
 419 -- *
 420 -- * civicrm_custom_value
 421 -- *
 422 -- * Data store for each extended properties.
 423 -- *
 424 -- *******************************************************/
 425
 426   ALTER TABLE civicrm_custom_value ADD INDEX index_int_field (int_data, custom_field_id);
 427       ALTER TABLE civicrm_custom_value ADD INDEX index_float_field (float_data, custom_field_id);
 428       ALTER TABLE civicrm_custom_value ADD INDEX index_decimal_field (decimal_data, custom_field_id);
 429       ALTER TABLE civicrm_custom_value ADD INDEX index_char_field (char_data, custom_field_id);
 430       ALTER TABLE civicrm_custom_value ADD INDEX index_date_field (date_data, custom_field_id);
 431
 432 -- /*******************************************************
 433 -- *
 434 -- * civicrm_domain
 435 -- *
 436 -- * Top-level hierarchy to support multi-org/domain installations. Define domains for multi-org installs, else all contacts belong to one domain.
 437 -- *
 438 -- *******************************************************/
 439
 440   ALTER TABLE civicrm_domain ADD config_backend text COMMENT 'Backend configuration.';
 441       ALTER TABLE civicrm_domain ADD config_frontend text COMMENT 'Frontend configuration.';
 442
 443 -- /*******************************************************
 444 -- *
 445 -- * civicrm_entity_tag
 446 -- *
 447 -- * Tag entities (Contacts, Groups, Actions) to categories.
 448 -- *
 449 -- *******************************************************/
 450   ALTER TABLE `civicrm_entity_tag` ALTER COLUMN `entity_id` DROP DEFAULT;
 451   ALTER TABLE `civicrm_entity_tag` ALTER COLUMN `tag_id` DROP DEFAULT;
 452
 453 -- /*******************************************************
 454 -- *
 455 -- * civicrm_file
 456 -- *
 457 -- * Data store for uploaded (attached) files (pointer to file on disk OR blob). Maybe be joined to entities via custom_value.file_id or entity_file table.
 458 -- *
 459 -- *******************************************************/
 460
 461   ALTER TABLE `civicrm_file` DROP FOREIGN KEY `civicrm_file_ibfk_1`;
 462   ALTER TABLE `civicrm_file` DROP `file_type_id`;
 463   ALTER TABLE `civicrm_file` ADD file_type_id int unsigned    COMMENT 'Type of file (e.g. Transcript, Income Tax Return, etc). FK to civicrm_option_value.';
 464
 465 -- /*******************************************************
 466 -- *
 467 -- * civicrm_group
 468 -- *
 469 -- * Provide grouping of related contacts
 470 -- *
 471 -- *******************************************************/
 472
 473   ALTER TABLE civicrm_group ADD where_clause text COMMENT 'the sql where clause if a saved search acl';
 474       ALTER TABLE civicrm_group ADD select_tables text COMMENT 'the tables to be included in a select data';
 475       ALTER TABLE civicrm_group ADD where_tables text COMMENT 'the tables to be included in the count statement';
 476
 477 -- /*******************************************************
 478 -- *
 479 -- * civicrm_group_contact
 480 -- *
 481 -- * Join table sets membership for 'static' groups. Also used to store 'opt-out' entries for 'query' type groups (status = 'OUT')
 482 -- *
 483 -- *******************************************************/
 484
 485   ALTER TABLE civicrm_group_contact DROP FOREIGN KEY civicrm_group_contact_ibfk_2;
 486
 487 -- /*******************************************************
 488 -- *
 489 -- * civicrm_household
 490 -- *
 491 -- * Define household specific properties
 492 -- *
 493 -- *******************************************************/
 494
 495   ALTER TABLE civicrm_household DROP FOREIGN KEY civicrm_household_ibfk_1;
 496
 497 -- /*******************************************************
 498 -- *
 499 -- * civicrm_im
 500 -- *
 501 -- * IM information for a specific location.
 502 -- *
 503 -- *******************************************************/
 504
 505       ALTER TABLE civicrm_im DROP FOREIGN KEY civicrm_im_ibfk_2;
 506   ALTER TABLE civicrm_im ADD INDEX UI_provider_id (provider_id);
 507
 508 -- /*******************************************************
 509 -- *
 510 -- * civicrm_individual
 511 -- *
 512 -- * Define contact-individual specific properties. Extends civicrm_contact.
 513 -- *
 514 -- *******************************************************/
 515
 516   ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_1;
 517   ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_2;
 518   ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_3;
 519   ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_4;
 520   ALTER TABLE civicrm_individual ADD INDEX UI_prefix (prefix_id);
 521       ALTER TABLE civicrm_individual ADD INDEX UI_suffix (suffix_id);
 522       ALTER TABLE civicrm_individual ADD INDEX UI_gender (gender_id);
 523
 524 -- /*******************************************************
 525 -- *
 526 -- * civicrm_mapping
 527 -- *
 528 -- * Store field mappings in import or export for reuse
 529 -- *
 530 -- *******************************************************/
 531
 532   ALTER TABLE `civicrm_mapping` MODIFY mapping_type enum('Export','Import','Export Contributions','Import Contributions','Import Activity History','Search Builder','Import Memberships') COMMENT 'Type of Mapping.';
 533
 534 -- /*******************************************************
 535 -- *
 536 -- * civicrm_membership
 537 -- *
 538 -- * Contact Membership records.
 539 -- *
 540 -- *******************************************************/
 541
 542   ALTER TABLE `civicrm_membership` ADD reminder_date date COMMENT 'When should a reminder be sent.' AFTER is_override;
 543
 544 -- /*******************************************************
 545 -- *
 546 -- * civicrm_membership_log
 547 -- *
 548 -- * Logs actions which affect a Membership record (signup, status override, renewal, etc.)
 549 -- *
 550 -- *******************************************************/
 551
 552   ALTER TABLE civicrm_membership_log ADD renewal_reminder_date date COMMENT 'The day we sent a renewal reminder' AFTER modified_date;
 553
 554 -- /*******************************************************
 555 -- *
 556 -- * civicrm_membership_type
 557 -- *
 558 -- * Sites can configure multiple types of memberships. They encode the owner organization, fee, and the rules needed to set start and end (expire) dates when a member signs up for that type.
 559 -- *
 560 -- *******************************************************/
 561
 562   ALTER TABLE civicrm_membership_type ADD renewal_msg_id int unsigned COMMENT 'FK to civicrm_msg_template.id' AFTER weight;
 563       ALTER TABLE civicrm_membership_type ADD renewal_reminder_day int COMMENT 'Number of days prior to expiration to send renewal reminder' AFTER renewal_msg_id;
 564
 565   ALTER TABLE civicrm_membership_type ADD FOREIGN KEY (renewal_msg_id) REFERENCES civicrm_msg_template(id);
 566
 567 -- /*******************************************************
 568 -- *
 569 -- * civicrm_organization
 570 -- *
 571 -- * Define organization specific properties
 572 -- *
 573 -- *******************************************************/
 574
 575   ALTER TABLE civicrm_organization DROP FOREIGN KEY civicrm_organization_ibfk_1;
 576
 577 -- /*******************************************************
 578 -- *
 579 -- * civicrm_phone
 580 -- *
 581 -- * Phone information for a specific location.
 582 -- *
 583 -- *******************************************************/
 584
 585   ALTER TABLE `civicrm_phone` DROP FOREIGN KEY civicrm_phone_ibfk_2;
 586
 587       ALTER TABLE `civicrm_phone` ADD INDEX UI_mobile_provider_id (mobile_provider_id);
 588
 589 -- /*******************************************************
 590 -- *
 591 -- * civicrm_project
 592 -- *
 593 -- * Instantiate projects, programs, campaigns, etc.
 594 -- *
 595 -- *******************************************************/
 596
 597   ALTER TABLE civicrm_project DROP FOREIGN KEY civicrm_project_ibfk_2;
 598   ALTER TABLE civicrm_project DROP `status_id`;
 599   ALTER TABLE civicrm_project ADD status_id int unsigned    COMMENT 'Configurable status value (e.g. Planned, Active, Closed...). FK to civicrm_option_value.';
 600
 601 -- /*******************************************************
 602 -- *
 603 -- * civicrm_saved_search
 604 -- *
 605 -- * Users can save their complex SQL queries and use them later.
 606 -- *
 607 -- *******************************************************/
 608
 609   ALTER TABLE `civicrm_saved_search` ADD where_clause text DEFAULT NULL COMMENT 'the sql where clause if a saved search acl';
 610
 611   ALTER TABLE `civicrm_saved_search` ADD select_tables text DEFAULT NULL COMMENT 'the tables to be included in a select data';
 612
 613   ALTER TABLE `civicrm_saved_search` ADD where_tables text DEFAULT NULL COMMENT 'the tables to be included in the count statement';
 614
 615 -- /*******************************************************
 616 -- *
 617 -- * civicrm_task
 618 -- *
 619 -- * To-do items. Can be assigned to self or other entities.
 620 -- *
 621 -- *******************************************************/
 622
 623   ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_2;
 624   ALTER TABLE `civicrm_task` DROP `task_type_id`;
 625   ALTER TABLE `civicrm_task` ADD task_type_id int unsigned    COMMENT 'Configurable task type values (e.g. App Submit, App Review...). FK to civicrm_option_value.';
 626
 627   ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_3;
 628   ALTER TABLE `civicrm_task` DROP `priority_id`;
 629   ALTER TABLE `civicrm_task` ADD priority_id int unsigned    COMMENT 'Configurable priority value (e.g. Critical, High, Medium...). FK to civicrm_option_value.';
 630
 631 -- /*******************************************************
 632 -- *
 633 -- * civicrm_task_status
 634 -- *
 635 -- * Tracks the responsible entity, optional target entity and status of a task. Tasks can have multiple task_status entries if assigned to multiple responsible entities and-or there are multiple targets.
 636 -- *
 637 -- *******************************************************/
 638
 639   ALTER TABLE `civicrm_task_status` DROP FOREIGN KEY civicrm_task_status_ibfk_2;
 640   ALTER TABLE `civicrm_task_status` DROP `status_id`;
 641   ALTER TABLE `civicrm_task_status` ADD status_id int unsigned    COMMENT 'Configurable status value (e.g. Not Started, In Progress, Completed, Deferred...). FK to civicrm_option_value.';
 642
 643 -- /*******************************************************
 644 -- *
 645 -- * civicrm_uf_group
 646 -- *
 647 -- * User framework groups. Each group represents a form which encompasses a set of fields defined in civicrm_uf_fields table. Initially will be used for CiviCRM Profile form(s). Subsequently we anticipate using this to define other public facing forms (e.g. online donation solicitation forms, mailing list preferences, etc.).
 648 -- *
 649 -- *******************************************************/
 650
 651   ALTER TABLE `civicrm_uf_group` ADD is_edit_link tinyint NULL DEFAULT '0' COMMENT 'Should edit link display in profile selector';
 652
 653 -- /*******************************************************
 654 -- *
 655 -- * Add UNIQUE indexes to maintain data integrity
 656 -- *
 657 -- *******************************************************/
 658
 659     CREATE UNIQUE INDEX UI_contact_group ON civicrm_group_contact(contact_id,group_id);
 660     CREATE UNIQUE INDEX UI_contact ON civicrm_individual(contact_id);
 661     CREATE UNIQUE INDEX UI_contact ON civicrm_household(contact_id);
 662     CREATE UNIQUE INDEX UI_contact ON civicrm_organization(contact_id);
 663     CREATE UNIQUE INDEX UI_location ON civicrm_address(location_id);
 664     CREATE UNIQUE INDEX UI_contact ON civicrm_uf_match(contact_id);
 665     DROP INDEX UI_uf_id ON civicrm_uf_match;
 666     CREATE UNIQUE INDEX UI_uf_id ON civicrm_uf_match(uf_id);
 667     CREATE UNIQUE INDEX UI_contrib_trxn_id_domain_id ON civicrm_contribution_recur(trxn_id,domain_id);
 668     CREATE UNIQUE INDEX UI_contrib_invoice_id_domain_id ON civicrm_contribution_recur(invoice_id,domain_id);