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