sql/civicrm_upgradedb_v1.6_v1.7_40.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 -- *
  26 -- * adding of new tables
  27 -- *
  28 -- *******************************************************/
  29
  30 -- /*******************************************************
  31 -- *
  32 -- * civicrm_event
  33 -- *
  34 -- *******************************************************/
  35 CREATE TABLE civicrm_event (
  36
  37      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Event',
  38      domain_id int unsigned NOT NULL  DEFAULT 0 COMMENT 'Event belongs to which Domain?',
  39      title varchar(255)    COMMENT 'Event Title (e.g. Fall Fundraiser Dinner)',
  40      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.',
  41      description text    COMMENT 'Full description of event. Text and html allowed. Displayed on built-in Event Information screens.',
  42      event_type_id int unsigned   DEFAULT 0 COMMENT 'Event Type ID.Implicit FK to civicrm_option_value where option_group = event_type.',
  43      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.',
  44      start_date datetime    COMMENT 'Date and time that event starts.',
  45      end_date datetime    COMMENT 'Date and time that event ends. May be NULL if no defined end date/time',
  46      is_online_registration tinyint   DEFAULT 0 COMMENT 'If true, include registration link on Event Info page.',
  47      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.',
  48      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.',
  49      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.',
  50      is_monetary tinyint   DEFAULT 1 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.',
  51      contribution_type_id int unsigned   DEFAULT 0 COMMENT 'Contribution type assigned to paid event registrations for this event. Required if is_monetary is true.',
  52      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?',
  53      is_active tinyint   DEFAULT 0 COMMENT 'Is this Event enabled or disabled/cancelled?'
  54 ,
  55     PRIMARY KEY ( id )
  56
  57 ,      INDEX FKEY_domain_id ( domain_id ) ,
  58      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
  59
  60 )  TYPE=InnoDB  ;
  61
  62
  63 -- /*******************************************************
  64 -- *
  65 -- * civicrm_event_page
  66 -- *
  67 -- *******************************************************/
  68 CREATE TABLE civicrm_event_page (
  69      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Event Page ID',
  70      event_id int unsigned NOT NULL   COMMENT 'Event which this page belongs to.',
  71      intro_text text    COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.',
  72      footer_text text    COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.',
  73      confirm_title varchar(255)   DEFAULT NULL COMMENT 'Title for Confirmation page.',
  74      confirm_text text    COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.',
  75      confirm_footer_text text    COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.',
  76      is_email_confirm tinyint   DEFAULT 1 COMMENT 'If true, confirmation is automatically emailed to contact on successful registration.',
  77      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',
  78      confirm_from_name varchar(255)    COMMENT 'FROM email name used for confirmation emails.',
  79      confirm_from_email varchar(255)    COMMENT 'FROM email address used for confirmation emails.',
  80      cc_confirm varchar(255)    COMMENT 'comma-separated list of email addresses to cc each time a confirmation is sent',
  81      bcc_confirm varchar(255)    COMMENT 'comma-separated list of email addresses to bcc each time a confirmation is sent',
  82      default_fee_id int unsigned NOT NULL   COMMENT 'FK to civicrm_custom_option.',
  83      thankyou_title varchar(255)   DEFAULT NULL COMMENT 'Title for ThankYou page.',
  84      thankyou_text text    COMMENT 'ThankYou Text.',
  85      thankyou_footer_text text    COMMENT 'Footer message.'
  86 ,
  87     PRIMARY KEY ( id )
  88
  89 )  TYPE=InnoDB  ;
  90
  91 -- /*******************************************************
  92 -- *
  93 -- * civicrm_participant
  94 -- *
  95 -- *******************************************************/
  96 CREATE TABLE civicrm_participant (
  97
  98
  99      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Participant Id',
 100      contact_id int unsigned   DEFAULT 0 COMMENT 'FK to Contact ID',
 101      event_id int unsigned   DEFAULT 0 COMMENT 'FK to Event ID',
 102      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.',
 103      role_id int unsigned   DEFAULT NULL COMMENT 'Participant role ID. Implicit FK to civicrm_option_value where option_group = participant_role.',
 104      register_date datetime    COMMENT 'When did contact register for event?',
 105      source varchar(128)    COMMENT 'Source of this event registration.',
 106      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.',
 107      is_test tinyint   DEFAULT 0
 108 ,
 109     PRIMARY KEY ( id )
 110
 111 ,      INDEX FKEY_contact_id ( contact_id ) ,
 112      FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
 113 ,      INDEX FKEY_event_id ( event_id ) ,
 114      FOREIGN KEY (event_id) REFERENCES civicrm_event(id)
 115
 116 )  TYPE=InnoDB  ;
 117
 118 -- /*******************************************************
 119 -- *
 120 -- * civicrm_participant_payment
 121 -- *
 122 -- *******************************************************/
 123 CREATE TABLE civicrm_participant_payment (
 124
 125
 126      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Participant Payment Id',
 127      participant_id int unsigned NOT NULL   COMMENT 'Participant Id (FK)',
 128      payment_entity_table varchar(128)    COMMENT 'Table being referenced for payment entity (expected usage is civicrm_contribution).',
 129      payment_entity_id int unsigned NOT NULL   COMMENT 'FK to table with payment record (e.g. civicrm_contribution.id).'
 130 ,
 131     PRIMARY KEY ( id )
 132
 133 ,      INDEX FKEY_participant_id ( participant_id ) ,
 134      FOREIGN KEY (participant_id) REFERENCES civicrm_participant(id)
 135
 136 )  TYPE=InnoDB  ;
 137
 138 -- /*******************************************************
 139 -- *
 140 -- * civicrm_currency
 141 -- *
 142 -- *******************************************************/
 143 CREATE TABLE civicrm_currency (
 144
 145
 146      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Currency Id',
 147      name varchar(64)    COMMENT 'Currency Name',
 148      symbol varchar(8)    COMMENT 'Currency Symbol'
 149 ,
 150     PRIMARY KEY ( id )
 151
 152 )  TYPE=InnoDB  ;
 153
 154 -- /*******************************************************
 155 -- *
 156 -- * Insert sample data to civicrm_currency
 157 -- *
 158 -- *******************************************************/
 159
 160   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');
 161
 162 -- /*******************************************************
 163 -- *
 164 -- * civicrm_address
 165 -- *
 166 -- *******************************************************/
 167
 168   ALTER TABLE `civicrm_address`
 169       MODIFY `geo_code_1` double NULL DEFAULT NULL COMMENT 'Latitude or UTM (Universal Transverse Mercator Grid) Northing.',
 170       MODIFY `geo_code_2` double NULL DEFAULT NULL COMMENT 'Longitude or UTM (Universal Transverse Mercator Grid) Easting.';
 171
 172 -- /*******************************************************
 173 -- *
 174 -- * civicrm_contact
 175 -- *
 176 -- *******************************************************/
 177
 178   ALTER TABLE `civicrm_contact` ADD INDEX `UI_external_identifier` (`external_identifier`);
 179
 180 -- /*******************************************************
 181 -- *
 182 -- * civicrm_contribution_page
 183 -- *
 184 -- *******************************************************/
 185   ALTER TABLE `civicrm_contribution_page`  ADD `default_amount_id` int unsigned NOT NULL  COMMENT 'FK to civicrm_custom_option.';
 186         ALTER TABLE `civicrm_contribution_page` DROP `default_amount`;
 187
 188 -- /*******************************************************
 189 -- *
 190 -- * civicrm_custom_group
 191 -- *
 192 -- *******************************************************/
 193
 194   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.).';
 195
 196 -- /*******************************************************
 197 -- *
 198 -- * civicrm_custom_value
 199 -- *
 200 -- *******************************************************/
 201
 202   ALTER TABLE `civicrm_custom_value` MODIFY float_data double NULL DEFAULT NULL COMMENT 'stores data for ext property data_type = float.';
 203
 204 -- /*******************************************************
 205 -- *
 206 -- * civicrm_location_type
 207 -- *
 208 -- *******************************************************/
 209
 210   ALTER TABLE `civicrm_location_type` MODIFY vcard_name varchar(64) NULL DEFAULT NULL COMMENT 'vCard Location Type Name.';
 211
 212 -- /*******************************************************
 213 -- *
 214 -- * civicrm_mapping
 215 -- *
 216 -- *******************************************************/
 217
 218   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.';
 219
 220 -- /*******************************************************
 221 -- *
 222 -- * civicrm_mapping_field
 223 -- *
 224 -- *******************************************************/
 225
 226   ALTER TABLE civicrm_mapping_field ADD `relationship_direction` varchar(6) NULL DEFAULT NULL COMMENT '';
 227   UPDATE `civicrm_mapping_field` set `relationship_direction` = 'a_b';
 228
 229 -- /*******************************************************
 230 -- *
 231 -- * civicrm_membership_type
 232 -- *
 233 -- *******************************************************/
 234
 235   ALTER TABLE `civicrm_membership_type` ADD `relationship_direction` varchar(6) NULL DEFAULT NULL COMMENT '';
 236   UPDATE `civicrm_membership_type` set `relationship_direction` = 'a_b';
 237
 238 -- /*******************************************************
 239 -- *
 240 -- * civicrm_note
 241 -- *
 242 -- *******************************************************/
 243   INSERT INTO `civicrm_note`
 244     (`entity_table`, `contact_id`, `entity_id`, `note`)
 245       SELECT 'civicrm_contribution', `contact_id`, `civicrm_contribution`.`id`, `civicrm_contribution`.`note`
 246      FROM civicrm_contribution;
 247
 248 -- /*******************************************************
 249 -- *
 250 -- * civicrm_contribution
 251 -- *
 252 -- *******************************************************/
 253
 254   ALTER TABLE `civicrm_contribution` ADD INDEX `received_date` (`receive_date`);
 255   ALTER TABLE `civicrm_contribution` ADD `amount_level` varchar(255) NULL DEFAULT NULL COMMENT '' AFTER source;
 256   ALTER TABLE `civicrm_contribution` DROP `note`;
 257
 258 -- /*******************************************************
 259 -- *
 260 -- * civicrm_uf_group
 261 -- *
 262 -- *******************************************************/
 263
 264   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;
 265   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;
 266
 267 -- /*******************************************************
 268 -- *
 269 -- * civicrm_uf_match
 270 -- *
 271 -- *******************************************************/
 272
 273   ALTER TABLE `civicrm_uf_match` DROP INDEX UI_uf_id;
 274   ALTER TABLE `civicrm_uf_match` ADD UNIQUE UI_uf_domain_id (uf_id, domain_id);
 275
 276 -- /*******************************************************
 277 -- *
 278 -- * CRM-1570 - migrate the old, CiviCRM 1.4 callbacks
 279 -- * (we don't support localized CiviCRM in MySQL 4.0,
 280 -- * so no localized acitivity names update here)
 281 -- *
 282 -- *******************************************************/
 283
 284   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');
 285
 286 -- /*******************************************************
 287 -- *
 288 -- * Insert Billing location type (used for billing address
 289 -- * entered in online contribution forms)
 290 -- *
 291 -- *******************************************************/
 292
 293   SELECT @domain_id := id from civicrm_domain;
 294     INSERT INTO `civicrm_location_type` ( `domain_id`, `name`, `vcard_name`, `description`, `is_reserved`, `is_active` )
 295     VALUES
 296         ( @domain_id, 'Billing', NULL, 'Billing Address location', 1, 1 );
 297
 298 -- /*******************************************************
 299 -- *
 300 -- * civicrm_option_group
 301 -- *
 302 -- *******************************************************/
 303
 304   SELECT @domain_id := id from civicrm_domain;
 305   INSERT INTO `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
 306   VALUES
 307         (@domain_id, 'participant_status', 'Participant Status', 0, 1),
 308         (@domain_id, 'participant_role', 'Participant Role', 0, 1),
 309               (@domain_id, 'event_type', 'Event Type', 0, 1);
 310
 311 -- /*******************************************************
 312 -- *
 313 -- * civicrm_option_value
 314 -- *
 315 -- *******************************************************/
 316
 317   SELECT @option_group_id_ps    := max(id) from civicrm_option_group where name = 'participant_status';
 318   SELECT @option_group_id_pRole := max(id) from civicrm_option_group where name = 'participant_role';
 319   SELECT @option_group_id_etype := max(id) from civicrm_option_group where name = 'event_type';
 320
 321   INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
 322   VALUES
 323     (@option_group_id_ps, 'Registered', 1, 'Registered', NULL, 0, NULL, 1, NULL, 0, 1, 1),
 324     (@option_group_id_ps, 'Attended',   2, 'Attended',   NULL, 0, NULL, 2, NULL, 0, 0, 1),
 325     (@option_group_id_ps, 'No-show',    3, 'No-show',    NULL, 0, NULL, 3, NULL, 0, 0, 1),
 326     (@option_group_id_ps, 'Cancelled',  4, 'Cancelled',  NULL, 0, NULL, 4, NULL, 0, 1, 1),
 327
 328     (@option_group_id_pRole, 'Attendee',  1, 'Attendee',  NULL, 0, NULL, 1, NULL, 0, 0, 1),
 329     (@option_group_id_pRole, 'Volunteer', 2, 'Volunteer', NULL, 0, NULL, 2, NULL, 0, 0, 1),
 330     (@option_group_id_pRole, 'Host',      3, 'Host',      NULL, 0, NULL, 3, NULL, 0, 0, 1),
 331     (@option_group_id_pRole, 'Speaker',   4, 'Speaker',   NULL, 0, NULL, 4, NULL, 0, 0, 1),
 332
 333     (@option_group_id_etype, 'Conference', 1, 'Conference',  NULL, 0, NULL, 1, NULL, 0, 0, 1 ),
 334     (@option_group_id_etype, 'Exhibition', 2, 'Exhibition',  NULL, 0, NULL, 2, NULL, 0, 0, 1 ),
 335     (@option_group_id_etype, 'Fundraiser', 3, 'Fundraiser',  NULL, 0, NULL, 3, NULL, 0, 0, 1 ),
 336     (@option_group_id_etype, 'Meeting',    4, 'Meeting',     NULL, 0, NULL, 4, NULL, 0, 0, 1 ),
 337     (@option_group_id_etype, 'Performance',5, 'Performance', NULL, 0, NULL, 5, NULL, 0, 0, 1 ),
 338     (@option_group_id_etype, 'Workshop',   6, 'Workshop',    NULL, 0, NULL, 6, NULL, 0, 0, 1 );