Merge pull request #6368 from colemanw/gender

[civicrm-core.git] / sql / civicrm_upgradedb_v1.6_v1.7_41.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        |
-- +--------------------------------------------------------------------+
-- /*******************************************************
-- *
-- * adding of new tables
-- *
-- *******************************************************/

-- /*******************************************************
-- *
-- * civicrm_event
-- *
-- *******************************************************/
CREATE TABLE civicrm_event (
     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Event',
     domain_id int unsigned NOT NULL  DEFAULT 0 COMMENT 'Event belongs to which Domain?',
     title varchar(255)    COMMENT 'Event Title (e.g. Fall Fundraiser Dinner)',
     summary text    COMMENT 'Brief summary of event. Text and html allowed. Displayed on Event Registration form and can be used on other CMS pages which need an event summary.',
     description text    COMMENT 'Full description of event. Text and html allowed. Displayed on built-in Event Information screens.',
     event_type_id int unsigned   DEFAULT 0 COMMENT 'Event Type ID.Implicit FK to civicrm_option_value where option_group = event_type.',
     is_public tinyint   DEFAULT 1 COMMENT 'Public events will be included in the iCal feeds. Access to private event information may be limited using ACLs.',
     start_date datetime    COMMENT 'Date and time that event starts.',
     end_date datetime    COMMENT 'Date and time that event ends. May be NULL if no defined end date/time',
     is_online_registration tinyint   DEFAULT 0 COMMENT 'If true, include registration link on Event Info page.',
     registration_link_text varchar(255)    COMMENT 'Text for link to Event Registration form which is displayed on Event Information screen when is_online_registration is true.',
     max_participants int unsigned   DEFAULT 0 COMMENT 'Maximum number of registered participants to allow. After max is reached, a custom Event Full message is displayed. If NULL, allow unlimited number of participants.',
     event_full_text text    COMMENT 'Message to display on Event Information page and INSTEAD OF Event Registration form if maximum participants are signed up. Can include email address/info about getting on a waiting list, etc. Text and html allowed.',
     is_monetary tinyint   DEFAULT 0 COMMENT 'Is this a PAID event? If true, one or more fee amounts must be set and a Payment Processor must be configured for Online Event Registration.',
     contribution_type_id int unsigned   DEFAULT 0 COMMENT 'Contribution type assigned to paid event registrations for this event. Required if is_monetary is true.',
     is_map tinyint   DEFAULT 0 COMMENT 'Include a map block on the Event Information page when geocode info is available and a mapping provider has been specified?',
     is_active tinyint   DEFAULT 0 COMMENT 'Is this Event enabled or disabled/cancelled?'
,
    PRIMARY KEY ( id )
,
     CONSTRAINT FK_civicrm_event_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)

)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_event_page
-- *
-- *******************************************************/
CREATE TABLE civicrm_event_page (


     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Event Page ID',
     event_id int unsigned NOT NULL   COMMENT 'Event which this page belongs to.',
     intro_text text    COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.',
     footer_text text    COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.',
     confirm_title varchar(255)   DEFAULT NULL COMMENT 'Title for Confirmation page.',
     confirm_text text    COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.',
     confirm_footer_text text    COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.',
     is_email_confirm tinyint   DEFAULT 1 COMMENT 'If true, confirmation is automatically emailed to contact on successful registration.',
     confirm_email_text text    COMMENT 'text to include above standard event info on confirmation email. emails are text-only, so do not allow html for now',
     confirm_from_name varchar(255)    COMMENT 'FROM email name used for confirmation emails.',
     confirm_from_email varchar(255)    COMMENT 'FROM email address used for confirmation emails.',
     cc_confirm varchar(255)    COMMENT 'comma-separated list of email addresses to cc each time a confirmation is sent',
     bcc_confirm varchar(255)    COMMENT 'comma-separated list of email addresses to bcc each time a confirmation is sent',
     default_fee_id int unsigned NOT NULL   COMMENT 'FK to civicrm_custom_option.',
     thankyou_title varchar(255)   DEFAULT NULL COMMENT 'Title for ThankYou page.',
     thankyou_text text    COMMENT 'ThankYou Text.',
     thankyou_footer_text text    COMMENT 'Footer message.'
,
    PRIMARY KEY ( id )

)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_participant
-- *
-- *******************************************************/
CREATE TABLE civicrm_participant (


     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Participant Id',
     contact_id int unsigned   DEFAULT 0 COMMENT 'FK to Contact ID',
     event_id int unsigned   DEFAULT 0 COMMENT 'FK to Event ID',
     status_id int unsigned   DEFAULT 1 COMMENT 'Participant status ID. Implicit FK to civicrm_option_value where option_group = participant_status. Default of 1 should map to status = Registered.',
     role_id int unsigned   DEFAULT NULL COMMENT 'Participant role ID. Implicit FK to civicrm_option_value where option_group = participant_role.',
     register_date datetime    COMMENT 'When did contact register for event?',
     source varchar(128)    COMMENT 'Source of this event registration.',
     event_level varchar(255)    COMMENT 'Populate with the label (text) associated with a fee level for paid events with multiple levels. Note that we store the label value rather than an FK as the label stored in custom_option may change, but we dont want that to change the label in place at time of this registration.',
     is_test tinyint   DEFAULT 0
,
    PRIMARY KEY ( id )
,
     CONSTRAINT FK_civicrm_participant_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
,
     CONSTRAINT FK_civicrm_participant_event_id FOREIGN KEY (event_id) REFERENCES civicrm_event(id)

)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_participant_payment
-- *
-- *******************************************************/
CREATE TABLE civicrm_participant_payment (


     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Participant Payment Id',
     participant_id int unsigned NOT NULL   COMMENT 'Participant Id (FK)',
     payment_entity_table varchar(128)    COMMENT 'Table being referenced for payment entity (expected usage is civicrm_contribution).',
     payment_entity_id int unsigned NOT NULL   COMMENT 'FK to table with payment record (e.g. civicrm_contribution.id).'
,
    PRIMARY KEY ( id )
,
     CONSTRAINT FK_civicrm_participant_payment_participant_id FOREIGN KEY (participant_id) REFERENCES civicrm_participant(id)

)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_currency
-- *
-- *******************************************************/
CREATE TABLE civicrm_currency (


     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Currency Id',
     name varchar(64)    COMMENT 'Currency Name',
     symbol varchar(8)    COMMENT 'Currency Symbol'
,
    PRIMARY KEY ( id )
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * Insert sample data to
-- *
-- *******************************************************/
  INSERT INTO `civicrm_currency` VALUES (1,'AUD','$'),(2,'CAD','$'),(3,'EUR','€'),(4,'GBP','£'),(5,'ILS','₪'),(6,'INR','₨'),(7,'JPY','¥'),(8,'KRW','₩'),(9,'LAK','₭'),(10,'MNT','₮'),(11,'NGN','₦'),(12,'PLN','zł'),(13,'THB','฿'),(14,'USD','$'),(15,'VND','₫'),(16,'ZAR','R');


-- /*******************************************************
-- *
-- * civicrm_address
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_address`
      MODIFY `geo_code_1` double NULL DEFAULT NULL COMMENT 'Latitude or UTM (Universal Transverse Mercator Grid) Northing.',
      MODIFY `geo_code_2` double NULL DEFAULT NULL COMMENT 'Longitude or UTM (Universal Transverse Mercator Grid) Easting.';

-- /*******************************************************
-- *
-- * civicrm_contact
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_contact` ADD INDEX `UI_external_identifier` (`external_identifier`);

-- /*******************************************************
-- *
-- * civicrm_contribution_page
-- *
-- *******************************************************/
  ALTER TABLE `civicrm_contribution_page`  ADD `default_amount_id` int unsigned NOT NULL  COMMENT 'FK to civicrm_custom_option.';
        ALTER TABLE `civicrm_contribution_page` DROP `default_amount`;

-- /*******************************************************
-- *
-- * civicrm_custom_group
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_custom_group` MODIFY extends enum('Contact','Individual','Household','Organization','Location','Address','Contribution','Activity','Relationship','Phonecall','Meeting','Group','Membership','Participant','Event') NULL DEFAULT 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).';


-- /*******************************************************
-- *
-- * civicrm_custom_value
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_custom_value` MODIFY float_data double NULL DEFAULT NULL COMMENT 'stores data for ext property data_type = float.';

-- /*******************************************************
-- *
-- * civicrm_location_type
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_location_type` MODIFY vcard_name varchar(64) NULL DEFAULT NULL COMMENT 'vCard Location Type Name.';

-- /*******************************************************
-- *
-- * civicrm_mapping
-- *
-- *******************************************************/

  ALTER TABLE civicrm_mapping MODIFY mapping_type enum('Export','Import','Export Contributions','Import Contributions','Import Activity History','Search Builder','Import Memberships','Import Participants') NULL DEFAULT NULL COMMENT 'Type of Mapping.';

-- /*******************************************************
-- *
-- * civicrm_membership
-- *
-- *******************************************************/
  ALTER TABLE civicrm_membership ADD owner_membership_id int(10) UNSIGNED NULL DEFAULT NULL COMMENT 'Optional FK to Parent Membership.';
  ALTER TABLE civicrm_membership ADD INDEX index_owner_membership_id( owner_membership_id );

-- /*******************************************************
-- *
-- * civicrm_membership_type
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_membership_type` ADD `relationship_direction` varchar(6) NULL DEFAULT NULL COMMENT '';
  UPDATE `civicrm_membership_type` SET `relationship_direction`='a_b';


-- /*******************************************************
-- *
-- * civicrm_mapping_field
-- *
-- *******************************************************/

  ALTER TABLE civicrm_mapping_field ADD `relationship_direction` varchar(6) NULL DEFAULT NULL COMMENT '';
  UPDATE `civicrm_mapping_field` SET `relationship_direction`='a_b';


-- /*******************************************************
-- *
-- * civicrm_note
-- *
-- *******************************************************/
  INSERT INTO `civicrm_note`
    (`entity_table`, `contact_id`, `entity_id`, `note`)
      SELECT 'civicrm_contribution', `contact_id`, `civicrm_contribution`.`id`, `civicrm_contribution`.`note`
     FROM civicrm_contribution;

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

  ALTER TABLE `civicrm_contribution` ADD INDEX `received_date` (`receive_date`);
  ALTER TABLE `civicrm_contribution` ADD `amount_level` varchar(255) NULL DEFAULT NULL COMMENT '' AFTER source;
  ALTER TABLE `civicrm_contribution` DROP `note`;

-- /*******************************************************
-- *
-- * civicrm_uf_group
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_uf_group` ADD `is_uf_link` tinyint(4) NULL DEFAULT '0' COMMENT 'Should we display a link to the website profile in profile selector' AFTER is_edit_link;
  ALTER TABLE `civicrm_uf_group` ADD `is_update_dupe` tinyint(4) NULL DEFAULT '0' COMMENT 'Should we update the contact record if we find a duplicate' AFTER is_uf_link;

-- /*******************************************************
-- *
-- * civicrm_uf_match
-- *
-- *******************************************************/

  ALTER TABLE `civicrm_uf_match` DROP INDEX `UI_uf_id`;
  ALTER TABLE `civicrm_uf_match` ADD UNIQUE `UI_uf_domain_id`(`uf_id`, `domain_id`);

-- /*******************************************************
-- *
-- * CRM-1570 - migrate the old, CiviCRM 1.4 callbacks
-- * and fix (un-translate) localized activity names
-- *
-- *******************************************************/

  UPDATE civicrm_activity_history SET callback = 'CRM_Activity_BAO_Activity::showActivityDetails' WHERE callback IN ('CRM_Activity_Form_Meeting::showMeetingDetails', 'CRM_Activity_Form_OtherActivity::showOtherActivityDetails', 'CRM_Activity_Form_Phonecall::showCallDetails');

  UPDATE civicrm_activity_history SET activity_type = 'Meeting'    WHERE activity_type IN ('Treffen', 'Cita', 'Tapaaminen', 'Reunion', 'Riunione', 'Bijeenkomst', 'Spotkanie');
  UPDATE civicrm_activity_history SET activity_type = 'Meeting'    WHERE activity_type LIKE 'R%union';
  UPDATE civicrm_activity_history SET activity_type = 'Meeting'    WHERE activity_type LIKE 'Tal%lkoz%';
  UPDATE civicrm_activity_history SET activity_type = 'Meeting'    WHERE activity_type LIKE 'Reuni%o';
  UPDATE civicrm_activity_history SET activity_type = 'Meeting'    WHERE activity_type LIKE '%nt%lnire';
  UPDATE civicrm_activity_history SET activity_type = 'Meeting'    WHERE activity_type LIKE 'M%te(n)';

  UPDATE civicrm_activity_history SET activity_type = 'Phone Call' WHERE activity_type IN ('Telefonanruf', 'Puhelu', 'Appel', 'Telefonata', 'Telefoonoproep', 'Telefonsamtale', 'Rozmowa', 'Telefonema', 'Apel telefon', 'Telefonkontakt');
  UPDATE civicrm_activity_history SET activity_type = 'Phone Call' WHERE activity_type LIKE 'Llamada telef%nica';
  UPDATE civicrm_activity_history SET activity_type = 'Phone Call' WHERE activity_type LIKE 'Appel t%l%phonique';
  UPDATE civicrm_activity_history SET activity_type = 'Phone Call' WHERE activity_type LIKE 'Telefonh%v%s';

  UPDATE civicrm_activity_history SET activity_type = 'Email'      WHERE activity_type IN ('Courriel', 'Epost', 'E-post');
  UPDATE civicrm_activity_history SET activity_type = 'Email'      WHERE activity_type LIKE 'S%hk%posti';

  UPDATE civicrm_activity_history SET activity_type = 'Event'      WHERE activity_type IN ('Evenement', 'Wydarzenie', 'Evento');


-- /*******************************************************
-- *
-- * Insert Billing location type (used for billing address
-- * entered in online contribution forms)
-- *
-- *******************************************************/

  SELECT @domain_id := id from civicrm_domain;
    INSERT INTO `civicrm_location_type` ( `domain_id`, `name`, `vcard_name`, `description`, `is_reserved`, `is_active` )
    VALUES
        ( @domain_id, 'Billing', NULL, 'Billing Address location', 1, 1 );

-- /*******************************************************
-- *
-- * civicrm_option_group
-- *
-- *******************************************************/

  SELECT @domain_id := id from civicrm_domain;
  INSERT INTO `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
  VALUES
        (@domain_id, 'participant_status', 'Participant Status', 0, 1),
        (@domain_id, 'participant_role', 'Participant Role', 0, 1),
          (@domain_id, 'event_type', 'Event Type', 0, 1);


-- /*******************************************************
-- *
-- * civicrm_option_value
-- *
-- *******************************************************/

  SELECT @option_group_id_ps    := max(id) from civicrm_option_group where name = 'participant_status';
  SELECT @option_group_id_pRole := max(id) from civicrm_option_group where name = 'participant_role';
  SELECT @option_group_id_etype := max(id) from civicrm_option_group where name = 'event_type';

  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_ps, 'Registered', 1, 'Registered', NULL, 0, NULL, 1, NULL, 0, 1, 1),
    (@option_group_id_ps, 'Attended',   2, 'Attended',   NULL, 0, NULL, 2, NULL, 0, 0, 1),
    (@option_group_id_ps, 'No-show',    3, 'No-show',    NULL, 0, NULL, 3, NULL, 0, 0, 1),
    (@option_group_id_ps, 'Cancelled',  4, 'Cancelled',  NULL, 0, NULL, 4, NULL, 0, 1, 1),

    (@option_group_id_pRole, 'Attendee',  1, 'Attendee',  NULL, 0, NULL, 1, NULL, 0, 0, 1),
    (@option_group_id_pRole, 'Volunteer', 2, 'Volunteer', NULL, 0, NULL, 2, NULL, 0, 0, 1),
    (@option_group_id_pRole, 'Host',      3, 'Host',      NULL, 0, NULL, 3, NULL, 0, 0, 1),
    (@option_group_id_pRole, 'Speaker',   4, 'Speaker',   NULL, 0, NULL, 4, NULL, 0, 0, 1),

    (@option_group_id_etype, 'Conference', 1, 'Conference',  NULL, 0, NULL, 1, NULL, 0, 0, 1 ),
    (@option_group_id_etype, 'Exhibition', 2, 'Exhibition',  NULL, 0, NULL, 2, NULL, 0, 0, 1 ),
    (@option_group_id_etype, 'Fundraiser', 3, 'Fundraiser',  NULL, 0, NULL, 3, NULL, 0, 0, 1 ),
    (@option_group_id_etype, 'Meeting',    4, 'Meeting',     NULL, 0, NULL, 4, NULL, 0, 0, 1 ),
    (@option_group_id_etype, 'Performance',5, 'Performance', NULL, 0, NULL, 5, NULL, 0, 0, 1 ),
    (@option_group_id_etype, 'Workshop',   6, 'Workshop',    NULL, 0, NULL, 6, NULL, 0, 0, 1 );