Merge pull request #6644 from totten/4.6-sql-where

[civicrm-core.git] / sql / civicrm_upgradedb_v1.5_v1.6_40.mysql

-- +--------------------------------------------------------------------+
-- | CiviCRM version 4.6                                                |
-- +--------------------------------------------------------------------+
-- | Copyright CiviCRM LLC (c) 2004-2015                                |
-- +--------------------------------------------------------------------+
-- | This file is a part of CiviCRM.                                    |
-- |                                                                    |
-- | CiviCRM is free software; you can copy, modify, and distribute it  |
-- | under the terms of the GNU Affero General Public License           |
-- | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
-- |                                                                    |
-- | CiviCRM is distributed in the hope that it will be useful, but     |
-- | WITHOUT ANY WARRANTY; without even the implied warranty of         |
-- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
-- | See the GNU Affero General Public License for more details.        |
-- |                                                                    |
-- | You should have received a copy of the GNU Affero General Public   |
-- | License and the CiviCRM Licensing Exception along                  |
-- | with this program; if not, contact CiviCRM LLC                     |
-- | at info[AT]civicrm[DOT]org. If you have questions about the        |
-- | GNU Affero General Public License or the licensing of CiviCRM,     |
-- | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
-- +--------------------------------------------------------------------+
-- /*******************************************************
-- All domain-keyed values handled by this included file
-- Handles all domain-keyed data. Included in civicrm_data.tpl for base initialization (@domain_id = 1).
-- *******************************************************/

    set @domain_id = 1;

-- /*******************************************************
-- *
-- * update the option group and option value
-- *
-- *******************************************************/

INSERT INTO
   `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
VALUES
   (@domain_id, 'activity_type'                 , 'Activity Type'                      , 0, 1),
   (@domain_id, 'gender'                        , 'Gender'                             , 0, 1),
   (@domain_id, 'instant_messenger_service'     , 'Instant Messenger (IM) screen-names', 0, 1),
   (@domain_id, 'mobile_provider'               , 'Mobile Phone Providers'             , 0, 1),
   (@domain_id, 'individual_prefix'             , 'Individual contact prefixes.'       , 0, 1),
   (@domain_id, 'individual_suffix'             , 'Individual contact suffixes.'       , 0, 1),
   (@domain_id, 'acl_role'                      , 'ACL Role.'                          , 0, 1),
   (@domain_id, 'accept_creditcard'             , 'Accept Credit Card'                 , 0, 1),
   (@domain_id, 'payment_instrument'            , 'Payment Instrument'                 , 0, 1),
   (@domain_id, 'contribution_status'           , 'Contribution Status'               , 0, 1);

SELECT @option_group_id_act            := max(id) from civicrm_option_group where name = 'activity_type';
SELECT @option_group_id_gender         := max(id) from civicrm_option_group where name = 'gender';
SELECT @option_group_id_IMProvider     := max(id) from civicrm_option_group where name = 'instant_messenger_service';
SELECT @option_group_id_mobileProvider := max(id) from civicrm_option_group where name = 'mobile_provider';
SELECT @option_group_id_prefix         := max(id) from civicrm_option_group where name = 'individual_prefix';
SELECT @option_group_id_suffix         := max(id) from civicrm_option_group where name = 'individual_suffix';
SELECT @option_group_id_aclRole        := max(id) from civicrm_option_group where name = 'acl_role';
SELECT @option_group_id_acc            := max(id) from civicrm_option_group where name = 'accept_creditcard';
SELECT @option_group_id_pi             := max(id) from civicrm_option_group where name = 'payment_instrument';
SELECT @option_group_id_cs             := max(id) from civicrm_option_group where name = 'contribution_status';

INSERT INTO
   `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
VALUES
   (@option_group_id_act, 'Meeting', 1, 'Meeting',NULL, 0, NULL, 1, 'Schedule a meeting', 0, 0, 1),
   (@option_group_id_act, 'Phone Call', 2, 'Phone Call', NULL,  0, NULL, 2, 'Schedule a Phone Call', 0, 0, 1),
   (@option_group_id_act, 'Email', 3, 'Email', NULL, 0, NULL, 3, 'Email Sent', 0, 0, 1),
   (@option_group_id_act, 'SMS', 4, 'SMS', NULL, 0, NULL, 4, 'SMS', 0, 0, 1),
   (@option_group_id_act, 'Event', 5,'Event', NULL, 0, NULL, 5, 'Event', 0, 0, 1),

   (@option_group_id_gender, 'Female',      1, 'Female',      NULL, 0, NULL, 1, NULL, 0, 0, 1),
   (@option_group_id_gender, 'Male',        2, 'Male',        NULL, 0, NULL, 2, NULL, 0, 0, 1),
   (@option_group_id_gender, 'Transgender', 3, 'Transgender', NULL, 0, NULL, 3, NULL, 0, 0, 1),

   (@option_group_id_IMProvider, 'Yahoo', 1, 'Yahoo', NULL, 0, NULL, 1, NULL, 0, 0, 1),
   (@option_group_id_IMProvider, 'MSN',   2, 'Msn',   NULL, 0, NULL, 2, NULL, 0, 0, 1),
   (@option_group_id_IMProvider, 'AIM',   3, 'Aim',   NULL, 0, NULL, 3, NULL, 0, 0, 1),
   (@option_group_id_IMProvider, 'GTalk', 4, 'Gtalk', NULL, 0, NULL, 4, NULL, 0, 0, 1),
   (@option_group_id_IMProvider, 'Jabber',5, 'Jabber',NULL, 0, NULL, 5, NULL, 0, 0, 1),
   (@option_group_id_IMProvider, 'Skype', 6, 'Skype', NULL, 0, NULL, 6, NULL, 0, 0, 1),

   (@option_group_id_mobileProvider, 'Sprint'  , 1, 'Sprint'  , NULL, 0, NULL, 1, NULL, 0, 0, 1),
   (@option_group_id_mobileProvider, 'Verizon' , 2, 'Verizon' , NULL, 0, NULL, 2, NULL, 0, 0, 1),
   (@option_group_id_mobileProvider, 'Cingular', 3, 'Cingular', NULL, 0, NULL, 3, NULL, 0, 0, 1),

   (@option_group_id_prefix, 'Mrs', 1, 'Mrs', NULL, 0, NULL, 1, NULL, 0, 0, 1),
   (@option_group_id_prefix, 'Ms',  2, 'Ms', NULL, 0, NULL, 2, NULL, 0, 0, 1),
   (@option_group_id_prefix, 'Mr',  3, 'Mr', NULL, 0, NULL, 3, NULL, 0, 0, 1),
   (@option_group_id_prefix, 'Dr',  4, 'Dr', NULL, 0, NULL, 4, NULL, 0, 0, 1),

   (@option_group_id_suffix, 'Jr',  1, 'Jr', NULL, 0, NULL, 1, NULL, 0, 0, 1),
   (@option_group_id_suffix, 'Sr',  2, 'Sr', NULL, 0, NULL, 2, NULL, 0, 0, 1),
   (@option_group_id_suffix, 'II',  3, 'II', NULL, 0, NULL, 3, NULL, 0, 0, 1),
   (@option_group_id_suffix, 'III', 4, 'III', NULL, 0, NULL, 4, NULL, 0, 0, 1),
   (@option_group_id_suffix, 'IV',  5, 'IV',  NULL, 0, NULL, 5, NULL, 0, 0, 1),
   (@option_group_id_suffix, 'V',   6, 'V',   NULL, 0, NULL, 6, NULL, 0, 0, 1),
   (@option_group_id_suffix, 'VI',  7, 'VI',  NULL, 0, NULL, 7, NULL, 0, 0, 1),
   (@option_group_id_suffix, 'VII', 8, 'VII', NULL, 0, NULL, 8, NULL, 0, 0, 1),

   (@option_group_id_aclRole, 'Administrator',  1, 'Admin', NULL, 0, NULL, 1, NULL, 0, 0, 1),
   (@option_group_id_aclRole, 'Authenticated',  2, 'Auth' , NULL, 0, NULL, 2, NULL, 0, 0, 1),

   (@option_group_id_acc, 'Visa',  1, 'Visa', NULL, 0, NULL, 1, NULL, 0, 0, 1),
   (@option_group_id_acc, 'MasterCard',  2, 'MasterCard', NULL, 0, NULL, 2, NULL, 0, 0, 1),
   (@option_group_id_acc, 'American Express',  3, 'American Express', NULL, 0, NULL, 3, NULL, 0, 0, 1),
   (@option_group_id_acc, 'Discover',  4, 'Discover', NULL, 0, NULL, 4, NULL, 0, 0, 1),

  (@option_group_id_pi, 'Credit Card',  1, 'Credit Card', NULL, 0, NULL, 1, NULL, 0, 0, 1),
  (@option_group_id_pi, 'Debit Card',  2, 'Debit Card', NULL, 0, NULL, 2, NULL, 0, 0, 1),
  (@option_group_id_pi, 'Cash',  3, 'Cash', NULL, 0, NULL, 3, NULL, 0, 0, 1),
  (@option_group_id_pi, 'Check',  4, 'Check', NULL, 0, NULL, 4, NULL, 0, 0, 1),
  (@option_group_id_pi, 'EFT',  5, 'EFT', NULL, 0, NULL, 5, NULL, 0, 0, 1),

  (@option_group_id_cs, 'Completed', 1, 'Completed', NULL, 0, NULL, 1, NULL, 0, 0, 1),
  (@option_group_id_cs, 'Pending'  , 2, 'Pending'  , NULL, 0, NULL, 2, NULL, 0, 0, 1),
  (@option_group_id_cs, 'Cancelled', 3, 'Cancelled', NULL, 0, NULL, 3, NULL, 0, 0, 1);

-- /*******************************************************
-- *
-- * add the Netherlands state of Limburg (CRM-1228)
-- *
-- *******************************************************/

   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (5176, 1152, "LI", "Limburg");

-- /*******************************************************
-- *
-- * update the province list (CRM-1271)
-- *
-- *******************************************************/

   UPDATE civicrm_state_province SET name="Co Antrim"          WHERE id=2595;
   UPDATE civicrm_state_province SET name="Co Armagh"          WHERE id=2598;
   UPDATE civicrm_state_province SET name="Bristol"            WHERE id=2620;
   UPDATE civicrm_state_province SET name="Co Down"            WHERE id=2652;
   UPDATE civicrm_state_province SET name="Co Durham"          WHERE id=2657;
   UPDATE civicrm_state_province SET name="Co Fermanagh"       WHERE id=2670;
   UPDATE civicrm_state_province SET name="Glasgow"            WHERE id=2674;
   UPDATE civicrm_state_province SET name="Herefordshire"      WHERE id=2687;
   UPDATE civicrm_state_province SET name="Kingston upon Hull" WHERE id=2700;
   UPDATE civicrm_state_province SET name="North Ayrshire"     WHERE id=2735;
   UPDATE civicrm_state_province SET name="Cleveland"          WHERE id=2760;
   UPDATE civicrm_state_province SET name="Rhondda Cynon Taff" WHERE id=2762;
   UPDATE civicrm_state_province SET name="Scottish Borders"   WHERE id=2770;
   UPDATE civicrm_state_province SET name="Vale of Glamorgan"  WHERE id=2804;

   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9000, 1196, "NW", "North West" );
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9986, 1226, "xTYW", "Tyne and Wear");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9987, 1226, "xIOM", "Isle of Man");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9988, 1226, "xGMA", "Greater Manchester");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9989, 1226, "xTYR", "Co Tyrone");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9990, 1226, "xWYO", "West Yorkshire");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9991, 1226, "xSYO", "South Yorkshire");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9992, 1226, "xMSD", "Merseyside");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9993, 1226, "xBRK", "Berkshire");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9994, 1226, "xWMD", "West Midlands");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9995, 1226, "xIVN", "Inverness");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9996, 1226, "xAVN", "Avon");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9997, 1226, "xMDX", "Middlesex");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9998, 1226, "xWGM", "West Glamorgan");
   INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9999, 1226, "xLON", "London");


-- /*******************************************************
-- *
-- * update database (CRM-1275)
-- *
-- *******************************************************/

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

-- /*******************************************************
-- *
-- * Clean up the exisiting tables
-- *
-- *******************************************************/

DROP TABLE IF EXISTS civicrm_accept_credit_card;
DROP TABLE IF EXISTS civicrm_activity_type;
DROP TABLE IF EXISTS civicrm_acl_group;
DROP TABLE IF EXISTS civicrm_acl_group_join;
DROP TABLE IF EXISTS civicrm_gender;
DROP TABLE IF EXISTS civicrm_individual_prefix;
DROP TABLE IF EXISTS civicrm_individual_suffix;
DROP TABLE IF EXISTS civicrm_im_provider;
DROP TABLE IF EXISTS civicrm_mobile_provider;
DROP TABLE IF EXISTS civicrm_payment_instrument;


-- /*******************************************************
-- *
-- * civicrm_acl_cache
-- *
-- * Cache for acls and contacts
-- *
-- *******************************************************/
CREATE TABLE civicrm_acl_cache (


     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
     contact_id int unsigned    COMMENT 'Foreign Key to Contact',
     acl_id int unsigned NOT NULL   COMMENT 'Foreign Key to ACL',
     modified_date date    COMMENT 'When was this cache entry last modified'
,
    PRIMARY KEY ( id )


,      INDEX FKEY_contact_id ( contact_id ) ,
     FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
,      INDEX FKEY_acl_id ( acl_id ) ,
     FOREIGN KEY (acl_id) REFERENCES civicrm_acl(id)
,    INDEX index_acl_id( acl_id)

)  TYPE=InnoDB;

-- /*******************************************************
-- *
-- * civicrm_acl_entity_role
-- *
-- * Join table for Contacts and Groups to ACL Roles
-- *
-- *******************************************************/
CREATE TABLE civicrm_acl_entity_role (


     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
     domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this contact',
     acl_role_id int unsigned NOT NULL   COMMENT 'Foreign Key to ACL Role (which is an option value pair and hence an implicit FK)',
     entity_table varchar(64) NOT NULL   COMMENT 'Table of the object joined to the ACL Role (Contact or Group)',
     entity_id int unsigned NOT NULL   COMMENT 'ID of the group/contact object being joined',
     is_active tinyint    COMMENT 'Is this property active?'
,
    PRIMARY KEY ( id )

    ,     INDEX index_role(
        acl_role_id
  )
  ,     INDEX index_entity(
        entity_table
      , entity_id
  )

,      INDEX FKEY_domain_id ( domain_id ) ,
     FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)

)  TYPE=InnoDB  ;


-- /*******************************************************
-- *
-- * civicrm_msg_template
-- *
-- * Users will need a way to save and retrieve templates with tokens for use in recurring email communication tasks
-- *
-- *******************************************************/
CREATE TABLE civicrm_msg_template (


     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Message Template ID',
     domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this contact',
     msg_title varchar(255)    COMMENT 'Descriptive title of message',
     msg_subject varchar(255)    COMMENT 'Subject for email message.',
     msg_text text    COMMENT 'Text formatted message',
     msg_html text    COMMENT 'HTML formatted message',
     is_active tinyint   DEFAULT 1
,
    PRIMARY KEY ( id )


,      INDEX FKEY_domain_id ( domain_id ) ,
     FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)

)  TYPE=InnoDB  ;

-- /*******************************************************
-- *
-- * Modify the civicrm_acl  Table Structure
-- *******************************************************/

  ALTER TABLE `civicrm_acl` ADD domain_id int unsigned NOT NULL DEFAULT '0' COMMENT 'Which Domain owns this contact';
  ALTER TABLE `civicrm_acl` ADD INDEX FKEY_domain_id (domain_id);
  ALTER TABLE `civicrm_acl` ADD FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id);

  ALTER TABLE `civicrm_acl` ADD name varchar(64) COMMENT 'ACL Name.';

  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?';

  ALTER TABLE `civicrm_acl` ADD is_active tinyint(4) COMMENT 'Is this property active?';

  ALTER TABLE `civicrm_acl` ADD INDEX index_acl_id (acl_id);

-- /*******************************************************
-- *
-- * civicrm_activity
-- *
-- * Join table for activities
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_activity` ADD INDEX UI_activity_type_id (`activity_type_id`);
  ALTER TABLE `civicrm_activity` DROP FOREIGN KEY `civicrm_activity_ibfk_2`;
  ALTER TABLE `civicrm_activity` DROP INDEX `FKEY_activity_type_id`;

-- /*******************************************************
-- *
-- * civicrm_activity_history
-- *
-- * Record history for an entity in the crm module
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_activity_history` ADD INDEX index_activity (`activity_id`);

-- /*******************************************************
-- *
-- * civicrm_contact
-- *
-- * 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.
-- *
-- *******************************************************/

  ALTER TABLE civicrm_contact ADD INDEX index_contact_type_domain (contact_type, domain_id);
  ALTER TABLE civicrm_contact ADD INDEX index_contact_sub_type_domain (contact_sub_type, domain_id);
  ALTER TABLE civicrm_contact ADD INDEX index_preferred_communication_method (preferred_communication_method);

  UPDATE civicrm_contact
  SET `preferred_communication_method` = CONCAT(char(1),`preferred_communication_method`,char(1))
  WHERE civicrm_contact.preferred_communication_method IS NOT NULL;

-- /*******************************************************
-- *
-- * civicrm_contribution
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_contribution` ADD  `contribution_status_id` int unsigned   DEFAULT '1';
  ALTER TABLE `civicrm_contribution` DROP FOREIGN KEY `civicrm_contribution_ibfk_6`;
  ALTER TABLE `civicrm_contribution` DROP FOREIGN KEY `civicrm_contribution_ibfk_7`;
  ALTER TABLE `civicrm_contribution` DROP INDEX `FKEY_payment_instrument_id`;
  ALTER TABLE `civicrm_contribution` DROP INDEX `FKEY_recur_contribution_id`;

  ALTER TABLE `civicrm_contribution` DROP recur_contribution_id;

  ALTER TABLE `civicrm_contribution` ADD is_test tinyint NULL DEFAULT 0;
  ALTER TABLE `civicrm_contribution` ADD `honor_contact_id` int unsigned COMMENT 'FK to contact ID';
  ALTER TABLE `civicrm_contribution` ADD contribution_recur_id int unsigned AFTER note;
  ALTER TABLE `civicrm_contribution` ADD INDEX FKEY_contribution_recur_id (contribution_recur_id);
  ALTER TABLE `civicrm_contribution` ADD CONSTRAINT FOREIGN KEY (`contribution_recur_id`) REFERENCES civicrm_contribution_recur (`id`);

  ALTER TABLE `civicrm_contribution` ADD INDEX `UI_contrib_payment_instrument_id`(payment_instrument_id);
  ALTER TABLE `civicrm_contribution` ADD INDEX `index_contribution_status` (contribution_status_id);
  ALTER TABLE `civicrm_contribution` ADD INDEX FKEY_honor_contact_id (honor_contact_id);
  ALTER TABLE `civicrm_contribution` ADD CONSTRAINT FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact` (`id`);

  UPDATE civicrm_contribution SET contribution_status_id=3          WHERE cancel_date IS NOT NULL;

-- /*******************************************************
-- *
-- * civicrm_contribution_page
-- *
-- * A Contribution object store meta information about a single customized contribution page
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_contribution_page` ADD honor_block_is_active tinyint COMMENT 'Should this contribution have the honor  block enabled?';
  ALTER TABLE `civicrm_contribution_page` ADD honor_block_title varchar(255) COMMENT 'Title for honor block.';
  ALTER TABLE `civicrm_contribution_page` ADD honor_block_text text COMMENT 'text for honor block.';
  ALTER TABLE `civicrm_contribution_page` ADD is_monetary tinyint DEFAULT '1' COMMENT 'if true - allows real-time monetary transactions otherwise non-monetary transactions.';
  ALTER TABLE `civicrm_contribution_page` ADD is_recur tinyint NULL DEFAULT '0' AFTER is_monetary;


-- /*******************************************************
-- *
-- * civicrm_contribution_recur
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_contribution_recur` ADD domain_id int unsigned NOT NULL DEFAULT '0' COMMENT 'Which Domain owns this contact';
  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';
  ALTER TABLE `civicrm_contribution_recur` ADD  invoice_id varchar(255) COMMENT 'unique invoice id, system generated or passed in';
  ALTER TABLE `civicrm_contribution_recur` ADD contribution_status_id int unsigned NULL DEFAULT '1' AFTER invoice_id;
  ALTER TABLE `civicrm_contribution_recur` ADD end_date datetime NOT NULL AFTER cancel_date;
  ALTER TABLE `civicrm_contribution_recur` ADD is_test tinyint NULL DEFAULT '0' AFTER contribution_status_id;

  ALTER TABLE `civicrm_contribution_recur` MODIFY start_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
      ALTER TABLE `civicrm_contribution_recur` MODIFY create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
      ALTER TABLE `civicrm_contribution_recur` MODIFY modified_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
      ALTER TABLE `civicrm_contribution_recur` MODIFY cancel_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
      ALTER TABLE `civicrm_contribution_recur` MODIFY next_sched_contribution datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
      ALTER TABLE `civicrm_contribution_recur` MODIFY failure_retry_date datetime NULL DEFAULT NULL;

  ALTER TABLE `civicrm_contribution_recur` DROP is_active;

  ALTER TABLE `civicrm_contribution_recur` ADD INDEX FKEY_domain_id (domain_id);
  ALTER TABLE `civicrm_contribution_recur` ADD INDEX index_contribution_status (contribution_status_id);
  ALTER TABLE `civicrm_contribution_recur` ADD FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id);

-- /*******************************************************
-- *
-- * civicrm_custom_group
-- *
-- * All extended (custom) properties are associated with a group. These are logical sets of related data.
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_custom_group` ADD extends_entity_column_name varchar(64) COMMENT 'linking custom group for dynamic object';
  ALTER TABLE `civicrm_custom_group` ADD extends_entity_column_value varchar(64) COMMENT 'linking custom group for dynamic object';

  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.).';

   UPDATE civicrm_custom_group
   SET extends_entity_column_value=NULL
   WHERE extends='Activity';

  UPDATE civicrm_custom_group
   SET extends='Activity', extends_entity_column_value='2'
   WHERE extends='Phonecall';

  UPDATE civicrm_custom_group
   SET extends='Activity', extends_entity_column_value='1'
   WHERE extends='Meeting';

  UPDATE civicrm_custom_group
   SET extends_entity_column_value=NULL
   WHERE extends='Contact';

  UPDATE civicrm_custom_group
   SET extends_entity_column_value=NULL
   WHERE extends='Relationship';

-- /*******************************************************
-- *
-- * civicrm_custom_value
-- *
-- * Data store for each extended properties.
-- *
-- *******************************************************/

  ALTER TABLE civicrm_custom_value ADD INDEX index_int_field (int_data, custom_field_id);
      ALTER TABLE civicrm_custom_value ADD INDEX index_float_field (float_data, custom_field_id);
      ALTER TABLE civicrm_custom_value ADD INDEX index_decimal_field (decimal_data, custom_field_id);
      ALTER TABLE civicrm_custom_value ADD INDEX index_char_field (char_data, custom_field_id);
      ALTER TABLE civicrm_custom_value ADD INDEX index_date_field (date_data, custom_field_id);

-- /*******************************************************
-- *
-- * civicrm_domain
-- *
-- * Top-level hierarchy to support multi-org/domain installations. Define domains for multi-org installs, else all contacts belong to one domain.
-- *
-- *******************************************************/

  ALTER TABLE civicrm_domain ADD config_backend text COMMENT 'Backend configuration.';
      ALTER TABLE civicrm_domain ADD config_frontend text COMMENT 'Frontend configuration.';

-- /*******************************************************
-- *
-- * civicrm_entity_tag
-- *
-- * Tag entities (Contacts, Groups, Actions) to categories.
-- *
-- *******************************************************/
  ALTER TABLE `civicrm_entity_tag` ALTER COLUMN `entity_id` DROP DEFAULT;
  ALTER TABLE `civicrm_entity_tag` ALTER COLUMN `tag_id` DROP DEFAULT;

-- /*******************************************************
-- *
-- * civicrm_file
-- *
-- * 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.
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_file` DROP FOREIGN KEY `civicrm_file_ibfk_1`;
  ALTER TABLE `civicrm_file` DROP INDEX `FKEY_file_type_id`;

-- /*******************************************************
-- *
-- * civicrm_group
-- *
-- * Provide grouping of related contacts
-- *
-- *******************************************************/

  ALTER TABLE civicrm_group ADD where_clause text NULL DEFAULT NULL AFTER visibility;
      ALTER TABLE civicrm_group ADD select_tables text NULL DEFAULT NULL AFTER where_clause;
      ALTER TABLE civicrm_group ADD where_tables text NULL DEFAULT NULL AFTER select_tables;

-- /*******************************************************
-- *
-- * civicrm_im
-- *
-- * IM information for a specific location.
-- *
-- *******************************************************/

      ALTER TABLE `civicrm_im` DROP FOREIGN KEY civicrm_im_ibfk_2;
  ALTER TABLE `civicrm_im` DROP INDEX FKEY_provider_id;
  ALTER TABLE `civicrm_im` ADD INDEX UI_provider_id (provider_id);


-- /*******************************************************
-- *
-- * civicrm_individual
-- *
-- * Define contact-individual specific properties. Extends civicrm_contact.
-- *
-- *******************************************************/

  ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_2;
  ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_3;
  ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_4;

  ALTER TABLE civicrm_individual DROP INDEX FKEY_prefix_id;
      ALTER TABLE civicrm_individual DROP INDEX FKEY_suffix_id;
      ALTER TABLE civicrm_individual DROP INDEX FKEY_gender_id;

  ALTER TABLE civicrm_individual ADD INDEX UI_prefix (prefix_id);
      ALTER TABLE civicrm_individual ADD INDEX UI_suffix (suffix_id);
      ALTER TABLE civicrm_individual ADD INDEX UI_gender (gender_id);

-- /*******************************************************
-- *
-- * civicrm_mapping
-- *
-- * Store field mappings in import or export for reuse
-- *
-- *******************************************************/

  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.';

-- /*******************************************************
-- *
-- * civicrm_membership
-- *
-- * Contact Membership records.
-- *
-- *******************************************************/

  ALTER TABLE civicrm_membership ADD reminder_date date AFTER is_override;

-- /*******************************************************
-- *
-- * civicrm_membership_log
-- *
-- * Logs actions which affect a Membership record (signup, status override, renewal, etc.)
-- *
-- *******************************************************/

  ALTER TABLE civicrm_membership_log ADD renewal_reminder_date date AFTER modified_date;

-- /*******************************************************
-- *
-- * civicrm_membership_type
-- *
-- * 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.
-- *
-- *******************************************************/

  ALTER TABLE civicrm_membership_type ADD renewal_msg_id int unsigned AFTER weight;
      ALTER TABLE civicrm_membership_type ADD renewal_reminder_day int AFTER renewal_msg_id;

  ALTER TABLE civicrm_membership_type ADD INDEX FKEY_renewal_msg_id ( renewal_msg_id );
  ALTER TABLE civicrm_membership_type ADD FOREIGN KEY (renewal_msg_id) REFERENCES civicrm_msg_template(id);

-- /*******************************************************
-- *
-- * civicrm_phone
-- *
-- * Phone information for a specific location.
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_phone` DROP FOREIGN KEY civicrm_phone_ibfk_2;
  ALTER TABLE `civicrm_phone` DROP INDEX FKEY_mobile_provider_id;

  ALTER TABLE `civicrm_phone` ADD INDEX UI_mobile_provider_id (mobile_provider_id);

-- /*******************************************************
-- *
-- * civicrm_project
-- *
-- * Instantiate projects, programs, campaigns, etc.
-- *
-- *******************************************************/

  ALTER TABLE civicrm_project DROP FOREIGN KEY civicrm_project_ibfk_2;
  ALTER TABLE civicrm_project DROP INDEX FKEY_status_id;

-- /*******************************************************
-- *
-- * civicrm_saved_search
-- *
-- * Users can save their complex SQL queries and use them later.
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_saved_search` ADD where_clause text DEFAULT NULL COMMENT 'the sql where clause if a saved search acl';

  ALTER TABLE `civicrm_saved_search` ADD select_tables text DEFAULT NULL COMMENT 'the tables to be included in a select data';

  ALTER TABLE `civicrm_saved_search` ADD where_tables text DEFAULT NULL COMMENT 'the tables to be included in the count statement';

-- /*******************************************************/

-- /*******************************************************
-- *
-- * civicrm_task
-- *
-- * To-do items. Can be assigned to self or other entities.
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_2;
  ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_3;

  ALTER TABLE `civicrm_task` DROP INDEX FKEY_task_type_id;
  ALTER TABLE `civicrm_task` DROP INDEX FKEY_priority_id;

-- /*******************************************************
-- *
-- * civicrm_task_status
-- *
-- * 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.
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_task_status` DROP FOREIGN KEY civicrm_task_status_ibfk_2;
  ALTER TABLE `civicrm_task_status` DROP INDEX FKEY_status_id;

-- /*******************************************************
-- *
-- * civicrm_uf_group
-- *
-- * 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.).
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_uf_group` ADD is_edit_link tinyint NULL DEFAULT '0' COMMENT 'Should edit link display in profile selector';

-- /*******************************************************
-- *
-- * Add UNIQUE indexes to maintain data integrity
-- *
-- *******************************************************/

    CREATE UNIQUE INDEX UI_contact_group ON civicrm_group_contact(contact_id,group_id);
    CREATE UNIQUE INDEX UI_contact ON civicrm_individual(contact_id);
    CREATE UNIQUE INDEX UI_contact ON civicrm_household(contact_id);
    CREATE UNIQUE INDEX UI_contact ON civicrm_organization(contact_id);

    CREATE UNIQUE INDEX UI_location ON civicrm_address(location_id);

    CREATE UNIQUE INDEX UI_contact ON civicrm_uf_match(contact_id);
    DROP INDEX UI_uf_id ON civicrm_uf_match;
    CREATE UNIQUE INDEX UI_uf_id ON civicrm_uf_match(uf_id);
    CREATE UNIQUE INDEX UI_contrib_trxn_id_domain_id ON civicrm_contribution_recur(trxn_id, domain_id);
    CREATE UNIQUE INDEX UI_contrib_invoice_id_domain_id ON civicrm_contribution_recur(invoice_id, domain_id);