1 -- +--------------------------------------------------------------------+
2 -- | CiviCRM version 3.2 |
3 -- +--------------------------------------------------------------------+
4 -- | Copyright CiviCRM LLC (c) 2004-2010 |
5 -- +--------------------------------------------------------------------+
6 -- | This file is a part of CiviCRM. |
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. |
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. |
17 -- | You should have received a copy of the GNU Affero General Public |
18 -- | License and the CiviCRM Licensing Exception along |
19 -- | with this program; if not, contact CiviCRM LLC |
20 -- | at info[AT]civicrm[DOT]org. If you have questions about the |
21 -- | GNU Affero General Public License or the licensing of CiviCRM, |
22 -- | see the CiviCRM license FAQ at http://civicrm.org/licensing |
23 -- +--------------------------------------------------------------------+
24 -- /*******************************************************
25 -- All domain-keyed values handled by this included file
26 -- Handles all domain-keyed data. Included in civicrm_data.tpl for base initialization (@domain_id = 1).
27 -- *******************************************************/
31 -- /*******************************************************
33 -- * update the option group and option value
35 -- *******************************************************/
38 `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
40 (@domain_id, 'activity_type' , 'Activity Type' , 0, 1),
41 (@domain_id, 'gender' , 'Gender' , 0, 1),
42 (@domain_id, 'instant_messenger_service' , 'Instant Messenger (IM) screen-names', 0, 1),
43 (@domain_id, 'mobile_provider' , 'Mobile Phone Providers' , 0, 1),
44 (@domain_id, 'individual_prefix' , 'Individual contact prefixes.' , 0, 1),
45 (@domain_id, 'individual_suffix' , 'Individual contact suffixes.' , 0, 1),
46 (@domain_id, 'acl_role' , 'ACL Role.' , 0, 1),
47 (@domain_id, 'accept_creditcard' , 'Accept Credit Card' , 0, 1),
48 (@domain_id, 'payment_instrument' , 'Payment Instrument' , 0, 1),
49 (@domain_id, 'contribution_status' , 'Contribution Status' , 0, 1);
51 SELECT @option_group_id_act := max(id) from civicrm_option_group where name = 'activity_type';
52 SELECT @option_group_id_gender := max(id) from civicrm_option_group where name = 'gender';
53 SELECT @option_group_id_IMProvider := max(id) from civicrm_option_group where name = 'instant_messenger_service';
54 SELECT @option_group_id_mobileProvider := max(id) from civicrm_option_group where name = 'mobile_provider';
55 SELECT @option_group_id_prefix := max(id) from civicrm_option_group where name = 'individual_prefix';
56 SELECT @option_group_id_suffix := max(id) from civicrm_option_group where name = 'individual_suffix';
57 SELECT @option_group_id_aclRole := max(id) from civicrm_option_group where name = 'acl_role';
58 SELECT @option_group_id_acc := max(id) from civicrm_option_group where name = 'accept_creditcard';
59 SELECT @option_group_id_pi := max(id) from civicrm_option_group where name = 'payment_instrument';
60 SELECT @option_group_id_cs := max(id) from civicrm_option_group where name = 'contribution_status';
63 `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
65 (@option_group_id_act, 'Meeting', 1, 'Meeting',NULL, 0, NULL, 1, 'Schedule a meeting', 0, 0, 1),
66 (@option_group_id_act, 'Phone Call', 2, 'Phone Call', NULL, 0, NULL, 2, 'Schedule a Phone Call', 0, 0, 1),
67 (@option_group_id_act, 'Email', 3, 'Email', NULL, 0, NULL, 3, 'Email Sent', 0, 0, 1),
68 (@option_group_id_act, 'SMS', 4, 'SMS', NULL, 0, NULL, 4, 'SMS', 0, 0, 1),
69 (@option_group_id_act, 'Event', 5,'Event', NULL, 0, NULL, 5, 'Event', 0, 0, 1),
71 (@option_group_id_gender, 'Female', 1, 'Female', NULL, 0, NULL, 1, NULL, 0, 0, 1),
72 (@option_group_id_gender, 'Male', 2, 'Male', NULL, 0, NULL, 2, NULL, 0, 0, 1),
73 (@option_group_id_gender, 'Transgender', 3, 'Transgender', NULL, 0, NULL, 3, NULL, 0, 0, 1),
75 (@option_group_id_IMProvider, 'Yahoo', 1, 'Yahoo', NULL, 0, NULL, 1, NULL, 0, 0, 1),
76 (@option_group_id_IMProvider, 'MSN', 2, 'Msn', NULL, 0, NULL, 2, NULL, 0, 0, 1),
77 (@option_group_id_IMProvider, 'AIM', 3, 'Aim', NULL, 0, NULL, 3, NULL, 0, 0, 1),
78 (@option_group_id_IMProvider, 'GTalk', 4, 'Gtalk', NULL, 0, NULL, 4, NULL, 0, 0, 1),
79 (@option_group_id_IMProvider, 'Jabber',5, 'Jabber',NULL, 0, NULL, 5, NULL, 0, 0, 1),
80 (@option_group_id_IMProvider, 'Skype', 6, 'Skype', NULL, 0, NULL, 6, NULL, 0, 0, 1),
82 (@option_group_id_mobileProvider, 'Sprint' , 1, 'Sprint' , NULL, 0, NULL, 1, NULL, 0, 0, 1),
83 (@option_group_id_mobileProvider, 'Verizon' , 2, 'Verizon' , NULL, 0, NULL, 2, NULL, 0, 0, 1),
84 (@option_group_id_mobileProvider, 'Cingular', 3, 'Cingular', NULL, 0, NULL, 3, NULL, 0, 0, 1),
86 (@option_group_id_prefix, 'Mrs', 1, 'Mrs', NULL, 0, NULL, 1, NULL, 0, 0, 1),
87 (@option_group_id_prefix, 'Ms', 2, 'Ms', NULL, 0, NULL, 2, NULL, 0, 0, 1),
88 (@option_group_id_prefix, 'Mr', 3, 'Mr', NULL, 0, NULL, 3, NULL, 0, 0, 1),
89 (@option_group_id_prefix, 'Dr', 4, 'Dr', NULL, 0, NULL, 4, NULL, 0, 0, 1),
91 (@option_group_id_suffix, 'Jr', 1, 'Jr', NULL, 0, NULL, 1, NULL, 0, 0, 1),
92 (@option_group_id_suffix, 'Sr', 2, 'Sr', NULL, 0, NULL, 2, NULL, 0, 0, 1),
93 (@option_group_id_suffix, 'II', 3, 'II', NULL, 0, NULL, 3, NULL, 0, 0, 1),
94 (@option_group_id_suffix, 'III', 4, 'III', NULL, 0, NULL, 4, NULL, 0, 0, 1),
95 (@option_group_id_suffix, 'IV', 5, 'IV', NULL, 0, NULL, 5, NULL, 0, 0, 1),
96 (@option_group_id_suffix, 'V', 6, 'V', NULL, 0, NULL, 6, NULL, 0, 0, 1),
97 (@option_group_id_suffix, 'VI', 7, 'VI', NULL, 0, NULL, 7, NULL, 0, 0, 1),
98 (@option_group_id_suffix, 'VII', 8, 'VII', NULL, 0, NULL, 8, NULL, 0, 0, 1),
100 (@option_group_id_aclRole, 'Administrator', 1, 'Admin', NULL, 0, NULL, 1, NULL, 0, 0, 1),
101 (@option_group_id_aclRole, 'Authenticated', 2, 'Auth' , NULL, 0, NULL, 2, NULL, 0, 0, 1),
103 (@option_group_id_acc, 'Visa', 1, 'Visa', NULL, 0, NULL, 1, NULL, 0, 0, 1),
104 (@option_group_id_acc, 'MasterCard', 2, 'MasterCard', NULL, 0, NULL, 2, NULL, 0, 0, 1),
105 (@option_group_id_acc, 'American Express', 3, 'American Express', NULL, 0, NULL, 3, NULL, 0, 0, 1),
106 (@option_group_id_acc, 'Discover', 4, 'Discover', NULL, 0, NULL, 4, NULL, 0, 0, 1),
108 (@option_group_id_pi, 'Credit Card', 1, 'Credit Card', NULL, 0, NULL, 1, NULL, 0, 0, 1),
109 (@option_group_id_pi, 'Debit Card', 2, 'Debit Card', NULL, 0, NULL, 2, NULL, 0, 0, 1),
110 (@option_group_id_pi, 'Cash', 3, 'Cash', NULL, 0, NULL, 3, NULL, 0, 0, 1),
111 (@option_group_id_pi, 'Check', 4, 'Check', NULL, 0, NULL, 4, NULL, 0, 0, 1),
112 (@option_group_id_pi, 'EFT', 5, 'EFT', NULL, 0, NULL, 5, NULL, 0, 0, 1),
114 (@option_group_id_cs, 'Completed', 1, 'Completed', NULL, 0, NULL, 1, NULL, 0, 0, 1),
115 (@option_group_id_cs, 'Pending' , 2, 'Pending' , NULL, 0, NULL, 2, NULL, 0, 0, 1),
116 (@option_group_id_cs, 'Cancelled', 3, 'Cancelled', NULL, 0, NULL, 3, NULL, 0, 0, 1);
118 -- /*******************************************************
120 -- * add the Netherlands state of Limburg (CRM-1228)
122 -- *******************************************************/
124 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (5176, 1152, "LI", "Limburg");
126 -- /*******************************************************
128 -- * update the province list (CRM-1271)
130 -- *******************************************************/
132 UPDATE civicrm_state_province SET name="Co Antrim" WHERE id=2595;
133 UPDATE civicrm_state_province SET name="Co Armagh" WHERE id=2598;
134 UPDATE civicrm_state_province SET name="Bristol" WHERE id=2620;
135 UPDATE civicrm_state_province SET name="Co Down" WHERE id=2652;
136 UPDATE civicrm_state_province SET name="Co Durham" WHERE id=2657;
137 UPDATE civicrm_state_province SET name="Co Fermanagh" WHERE id=2670;
138 UPDATE civicrm_state_province SET name="Glasgow" WHERE id=2674;
139 UPDATE civicrm_state_province SET name="Herefordshire" WHERE id=2687;
140 UPDATE civicrm_state_province SET name="Kingston upon Hull" WHERE id=2700;
141 UPDATE civicrm_state_province SET name="North Ayrshire" WHERE id=2735;
142 UPDATE civicrm_state_province SET name="Cleveland" WHERE id=2760;
143 UPDATE civicrm_state_province SET name="Rhondda Cynon Taff" WHERE id=2762;
144 UPDATE civicrm_state_province SET name="Scottish Borders" WHERE id=2770;
145 UPDATE civicrm_state_province SET name="Vale of Glamorgan" WHERE id=2804;
147 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9000, 1196, "NW", "North West" );
148 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9986, 1226, "xTYW", "Tyne and Wear");
149 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9987, 1226, "xIOM", "Isle of Man");
150 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9988, 1226, "xGMA", "Greater Manchester");
151 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9989, 1226, "xTYR", "Co Tyrone");
152 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9990, 1226, "xWYO", "West Yorkshire");
153 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9991, 1226, "xSYO", "South Yorkshire");
154 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9992, 1226, "xMSD", "Merseyside");
155 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9993, 1226, "xBRK", "Berkshire");
156 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9994, 1226, "xWMD", "West Midlands");
157 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9995, 1226, "xIVN", "Inverness");
158 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9996, 1226, "xAVN", "Avon");
159 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9997, 1226, "xMDX", "Middlesex");
160 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9998, 1226, "xWGM", "West Glamorgan");
161 INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (9999, 1226, "xLON", "London");
164 -- /*******************************************************
166 -- * update database (CRM-1275)
168 -- *******************************************************/
170 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
172 -- /*******************************************************
174 -- * Clean up the exisiting tables
176 -- *******************************************************/
178 DROP TABLE IF EXISTS civicrm_accept_credit_card;
179 DROP TABLE IF EXISTS civicrm_activity_type;
180 DROP TABLE IF EXISTS civicrm_acl_group;
181 DROP TABLE IF EXISTS civicrm_acl_group_join;
182 DROP TABLE IF EXISTS civicrm_gender;
183 DROP TABLE IF EXISTS civicrm_individual_prefix;
184 DROP TABLE IF EXISTS civicrm_individual_suffix;
185 DROP TABLE IF EXISTS civicrm_im_provider;
186 DROP TABLE IF EXISTS civicrm_mobile_provider;
187 DROP TABLE IF EXISTS civicrm_payment_instrument;
190 -- /*******************************************************
192 -- * civicrm_acl_cache
194 -- * Cache for acls and contacts
196 -- *******************************************************/
197 CREATE TABLE civicrm_acl_cache (
200 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID',
201 contact_id int unsigned COMMENT 'Foreign Key to Contact',
202 acl_id int unsigned NOT NULL COMMENT 'Foreign Key to ACL',
203 modified_date date COMMENT 'When was this cache entry last modified'
208 , INDEX FKEY_contact_id ( contact_id ) ,
209 FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
210 , INDEX FKEY_acl_id ( acl_id ) ,
211 FOREIGN KEY (acl_id) REFERENCES civicrm_acl(id)
212 , INDEX index_acl_id( acl_id)
216 -- /*******************************************************
218 -- * civicrm_acl_entity_role
220 -- * Join table for Contacts and Groups to ACL Roles
222 -- *******************************************************/
223 CREATE TABLE civicrm_acl_entity_role (
226 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID',
227 domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact',
228 acl_role_id int unsigned NOT NULL COMMENT 'Foreign Key to ACL Role (which is an option value pair and hence an implicit FK)',
229 entity_table varchar(64) NOT NULL COMMENT 'Table of the object joined to the ACL Role (Contact or Group)',
230 entity_id int unsigned NOT NULL COMMENT 'ID of the group/contact object being joined',
231 is_active tinyint COMMENT 'Is this property active?'
238 , INDEX index_entity(
243 , INDEX FKEY_domain_id ( domain_id ) ,
244 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
249 -- /*******************************************************
251 -- * civicrm_msg_template
253 -- * Users will need a way to save and retrieve templates with tokens for use in recurring email communication tasks
255 -- *******************************************************/
256 CREATE TABLE civicrm_msg_template (
259 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Message Template ID',
260 domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact',
261 msg_title varchar(255) COMMENT 'Descriptive title of message',
262 msg_subject varchar(255) COMMENT 'Subject for email message.',
263 msg_text text COMMENT 'Text formatted message',
264 msg_html text COMMENT 'HTML formatted message',
265 is_active tinyint DEFAULT 1
270 , INDEX FKEY_domain_id ( domain_id ) ,
271 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
275 -- /*******************************************************
277 -- * Modify the civicrm_acl Table Structure
278 -- *******************************************************/
280 ALTER TABLE `civicrm_acl` ADD domain_id int unsigned NOT NULL DEFAULT '0' COMMENT 'Which Domain owns this contact';
281 ALTER TABLE `civicrm_acl` ADD INDEX FKEY_domain_id (domain_id);
282 ALTER TABLE `civicrm_acl` ADD FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id);
284 ALTER TABLE `civicrm_acl` ADD name varchar(64) COMMENT 'ACL Name.';
286 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?';
288 ALTER TABLE `civicrm_acl` ADD is_active tinyint(4) COMMENT 'Is this property active?';
290 ALTER TABLE `civicrm_acl` ADD INDEX index_acl_id (acl_id);
292 -- /*******************************************************
294 -- * civicrm_activity
296 -- * Join table for activities
298 -- *******************************************************/
300 ALTER TABLE `civicrm_activity` ADD INDEX UI_activity_type_id (`activity_type_id`);
301 ALTER TABLE `civicrm_activity` DROP FOREIGN KEY `civicrm_activity_ibfk_2`;
302 ALTER TABLE `civicrm_activity` DROP INDEX `FKEY_activity_type_id`;
304 -- /*******************************************************
306 -- * civicrm_activity_history
308 -- * Record history for an entity in the crm module
310 -- *******************************************************/
312 ALTER TABLE `civicrm_activity_history` ADD INDEX index_activity (`activity_id`);
314 -- /*******************************************************
318 -- * 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.
320 -- *******************************************************/
322 ALTER TABLE civicrm_contact ADD INDEX index_contact_type_domain (contact_type, domain_id);
323 ALTER TABLE civicrm_contact ADD INDEX index_contact_sub_type_domain (contact_sub_type, domain_id);
324 ALTER TABLE civicrm_contact ADD INDEX index_preferred_communication_method (preferred_communication_method);
326 UPDATE civicrm_contact
327 SET `preferred_communication_method` = CONCAT(char(1),`preferred_communication_method`,char(1))
328 WHERE civicrm_contact.preferred_communication_method IS NOT NULL;
330 -- /*******************************************************
332 -- * civicrm_contribution
334 -- *******************************************************/
336 ALTER TABLE `civicrm_contribution` ADD `contribution_status_id` int unsigned DEFAULT '1';
337 ALTER TABLE `civicrm_contribution` DROP FOREIGN KEY `civicrm_contribution_ibfk_6`;
338 ALTER TABLE `civicrm_contribution` DROP FOREIGN KEY `civicrm_contribution_ibfk_7`;
339 ALTER TABLE `civicrm_contribution` DROP INDEX `FKEY_payment_instrument_id`;
340 ALTER TABLE `civicrm_contribution` DROP INDEX `FKEY_recur_contribution_id`;
342 ALTER TABLE `civicrm_contribution` DROP recur_contribution_id;
344 ALTER TABLE `civicrm_contribution` ADD is_test tinyint NULL DEFAULT 0;
345 ALTER TABLE `civicrm_contribution` ADD `honor_contact_id` int unsigned COMMENT 'FK to contact ID';
346 ALTER TABLE `civicrm_contribution` ADD contribution_recur_id int unsigned AFTER note;
347 ALTER TABLE `civicrm_contribution` ADD INDEX FKEY_contribution_recur_id (contribution_recur_id);
348 ALTER TABLE `civicrm_contribution` ADD CONSTRAINT FOREIGN KEY (`contribution_recur_id`) REFERENCES civicrm_contribution_recur (`id`);
350 ALTER TABLE `civicrm_contribution` ADD INDEX `UI_contrib_payment_instrument_id`(payment_instrument_id);
351 ALTER TABLE `civicrm_contribution` ADD INDEX `index_contribution_status` (contribution_status_id);
352 ALTER TABLE `civicrm_contribution` ADD INDEX FKEY_honor_contact_id (honor_contact_id);
353 ALTER TABLE `civicrm_contribution` ADD CONSTRAINT FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact` (`id`);
355 UPDATE civicrm_contribution SET contribution_status_id=3 WHERE cancel_date IS NOT NULL;
357 -- /*******************************************************
359 -- * civicrm_contribution_page
361 -- * A Contribution object store meta information about a single customized contribution page
363 -- *******************************************************/
365 ALTER TABLE `civicrm_contribution_page` ADD honor_block_is_active tinyint COMMENT 'Should this contribution have the honor block enabled?';
366 ALTER TABLE `civicrm_contribution_page` ADD honor_block_title varchar(255) COMMENT 'Title for honor block.';
367 ALTER TABLE `civicrm_contribution_page` ADD honor_block_text text COMMENT 'text for honor block.';
368 ALTER TABLE `civicrm_contribution_page` ADD is_monetary tinyint DEFAULT '1' COMMENT 'if true - allows real-time monetary transactions otherwise non-monetary transactions.';
369 ALTER TABLE `civicrm_contribution_page` ADD is_recur tinyint NULL DEFAULT '0' AFTER is_monetary;
372 -- /*******************************************************
374 -- * civicrm_contribution_recur
376 -- *******************************************************/
378 ALTER TABLE `civicrm_contribution_recur` ADD domain_id int unsigned NOT NULL DEFAULT '0' COMMENT 'Which Domain owns this contact';
379 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';
380 ALTER TABLE `civicrm_contribution_recur` ADD invoice_id varchar(255) COMMENT 'unique invoice id, system generated or passed in';
381 ALTER TABLE `civicrm_contribution_recur` ADD contribution_status_id int unsigned NULL DEFAULT '1' AFTER invoice_id;
382 ALTER TABLE `civicrm_contribution_recur` ADD end_date datetime NOT NULL AFTER cancel_date;
383 ALTER TABLE `civicrm_contribution_recur` ADD is_test tinyint NULL DEFAULT '0' AFTER contribution_status_id;
385 ALTER TABLE `civicrm_contribution_recur` MODIFY start_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
386 ALTER TABLE `civicrm_contribution_recur` MODIFY create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
387 ALTER TABLE `civicrm_contribution_recur` MODIFY modified_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
388 ALTER TABLE `civicrm_contribution_recur` MODIFY cancel_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
389 ALTER TABLE `civicrm_contribution_recur` MODIFY next_sched_contribution datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
390 ALTER TABLE `civicrm_contribution_recur` MODIFY failure_retry_date datetime NULL DEFAULT NULL;
392 ALTER TABLE `civicrm_contribution_recur` DROP is_active;
394 ALTER TABLE `civicrm_contribution_recur` ADD INDEX FKEY_domain_id (domain_id);
395 ALTER TABLE `civicrm_contribution_recur` ADD INDEX index_contribution_status (contribution_status_id);
396 ALTER TABLE `civicrm_contribution_recur` ADD FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id);
398 -- /*******************************************************
400 -- * civicrm_custom_group
402 -- * All extended (custom) properties are associated with a group. These are logical sets of related data.
404 -- *******************************************************/
406 ALTER TABLE `civicrm_custom_group` ADD extends_entity_column_name varchar(64) COMMENT 'linking custom group for dynamic object';
407 ALTER TABLE `civicrm_custom_group` ADD extends_entity_column_value varchar(64) COMMENT 'linking custom group for dynamic object';
409 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.).';
411 UPDATE civicrm_custom_group
412 SET extends_entity_column_value=NULL
413 WHERE extends='Activity';
415 UPDATE civicrm_custom_group
416 SET extends='Activity', extends_entity_column_value='2'
417 WHERE extends='Phonecall';
419 UPDATE civicrm_custom_group
420 SET extends='Activity', extends_entity_column_value='1'
421 WHERE extends='Meeting';
423 UPDATE civicrm_custom_group
424 SET extends_entity_column_value=NULL
425 WHERE extends='Contact';
427 UPDATE civicrm_custom_group
428 SET extends_entity_column_value=NULL
429 WHERE extends='Relationship';
431 -- /*******************************************************
433 -- * civicrm_custom_value
435 -- * Data store for each extended properties.
437 -- *******************************************************/
439 ALTER TABLE civicrm_custom_value ADD INDEX index_int_field (int_data, custom_field_id);
440 ALTER TABLE civicrm_custom_value ADD INDEX index_float_field (float_data, custom_field_id);
441 ALTER TABLE civicrm_custom_value ADD INDEX index_decimal_field (decimal_data, custom_field_id);
442 ALTER TABLE civicrm_custom_value ADD INDEX index_char_field (char_data, custom_field_id);
443 ALTER TABLE civicrm_custom_value ADD INDEX index_date_field (date_data, custom_field_id);
445 -- /*******************************************************
449 -- * Top-level hierarchy to support multi-org/domain installations. Define domains for multi-org installs, else all contacts belong to one domain.
451 -- *******************************************************/
453 ALTER TABLE civicrm_domain ADD config_backend text COMMENT 'Backend configuration.';
454 ALTER TABLE civicrm_domain ADD config_frontend text COMMENT 'Frontend configuration.';
456 -- /*******************************************************
458 -- * civicrm_entity_tag
460 -- * Tag entities (Contacts, Groups, Actions) to categories.
462 -- *******************************************************/
463 ALTER TABLE `civicrm_entity_tag` ALTER COLUMN `entity_id` DROP DEFAULT;
464 ALTER TABLE `civicrm_entity_tag` ALTER COLUMN `tag_id` DROP DEFAULT;
466 -- /*******************************************************
470 -- * 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.
472 -- *******************************************************/
474 ALTER TABLE `civicrm_file` DROP FOREIGN KEY `civicrm_file_ibfk_1`;
475 ALTER TABLE `civicrm_file` DROP INDEX `FKEY_file_type_id`;
477 -- /*******************************************************
481 -- * Provide grouping of related contacts
483 -- *******************************************************/
485 ALTER TABLE civicrm_group ADD where_clause text NULL DEFAULT NULL AFTER visibility;
486 ALTER TABLE civicrm_group ADD select_tables text NULL DEFAULT NULL AFTER where_clause;
487 ALTER TABLE civicrm_group ADD where_tables text NULL DEFAULT NULL AFTER select_tables;
489 -- /*******************************************************
493 -- * IM information for a specific location.
495 -- *******************************************************/
497 ALTER TABLE `civicrm_im` DROP FOREIGN KEY civicrm_im_ibfk_2;
498 ALTER TABLE `civicrm_im` DROP INDEX FKEY_provider_id;
499 ALTER TABLE `civicrm_im` ADD INDEX UI_provider_id (provider_id);
502 -- /*******************************************************
504 -- * civicrm_individual
506 -- * Define contact-individual specific properties. Extends civicrm_contact.
508 -- *******************************************************/
510 ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_2;
511 ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_3;
512 ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_4;
514 ALTER TABLE civicrm_individual DROP INDEX FKEY_prefix_id;
515 ALTER TABLE civicrm_individual DROP INDEX FKEY_suffix_id;
516 ALTER TABLE civicrm_individual DROP INDEX FKEY_gender_id;
518 ALTER TABLE civicrm_individual ADD INDEX UI_prefix (prefix_id);
519 ALTER TABLE civicrm_individual ADD INDEX UI_suffix (suffix_id);
520 ALTER TABLE civicrm_individual ADD INDEX UI_gender (gender_id);
522 -- /*******************************************************
526 -- * Store field mappings in import or export for reuse
528 -- *******************************************************/
530 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.';
532 -- /*******************************************************
534 -- * civicrm_membership
536 -- * Contact Membership records.
538 -- *******************************************************/
540 ALTER TABLE civicrm_membership ADD reminder_date date AFTER is_override;
542 -- /*******************************************************
544 -- * civicrm_membership_log
546 -- * Logs actions which affect a Membership record (signup, status override, renewal, etc.)
548 -- *******************************************************/
550 ALTER TABLE civicrm_membership_log ADD renewal_reminder_date date AFTER modified_date;
552 -- /*******************************************************
554 -- * civicrm_membership_type
556 -- * 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.
558 -- *******************************************************/
560 ALTER TABLE civicrm_membership_type ADD renewal_msg_id int unsigned AFTER weight;
561 ALTER TABLE civicrm_membership_type ADD renewal_reminder_day int AFTER renewal_msg_id;
563 ALTER TABLE civicrm_membership_type ADD INDEX FKEY_renewal_msg_id ( renewal_msg_id );
564 ALTER TABLE civicrm_membership_type ADD FOREIGN KEY (renewal_msg_id) REFERENCES civicrm_msg_template(id);
566 -- /*******************************************************
570 -- * Phone information for a specific location.
572 -- *******************************************************/
574 ALTER TABLE `civicrm_phone` DROP FOREIGN KEY civicrm_phone_ibfk_2;
575 ALTER TABLE `civicrm_phone` DROP INDEX FKEY_mobile_provider_id;
577 ALTER TABLE `civicrm_phone` ADD INDEX UI_mobile_provider_id (mobile_provider_id);
579 -- /*******************************************************
583 -- * Instantiate projects, programs, campaigns, etc.
585 -- *******************************************************/
587 ALTER TABLE civicrm_project DROP FOREIGN KEY civicrm_project_ibfk_2;
588 ALTER TABLE civicrm_project DROP INDEX FKEY_status_id;
590 -- /*******************************************************
592 -- * civicrm_saved_search
594 -- * Users can save their complex SQL queries and use them later.
596 -- *******************************************************/
598 ALTER TABLE `civicrm_saved_search` ADD where_clause text DEFAULT NULL COMMENT 'the sql where clause if a saved search acl';
600 ALTER TABLE `civicrm_saved_search` ADD select_tables text DEFAULT NULL COMMENT 'the tables to be included in a select data';
602 ALTER TABLE `civicrm_saved_search` ADD where_tables text DEFAULT NULL COMMENT 'the tables to be included in the count statement';
604 -- /*******************************************************/
606 -- /*******************************************************
610 -- * To-do items. Can be assigned to self or other entities.
612 -- *******************************************************/
614 ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_2;
615 ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_3;
617 ALTER TABLE `civicrm_task` DROP INDEX FKEY_task_type_id;
618 ALTER TABLE `civicrm_task` DROP INDEX FKEY_priority_id;
620 -- /*******************************************************
622 -- * civicrm_task_status
624 -- * 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.
626 -- *******************************************************/
628 ALTER TABLE `civicrm_task_status` DROP FOREIGN KEY civicrm_task_status_ibfk_2;
629 ALTER TABLE `civicrm_task_status` DROP INDEX FKEY_status_id;
631 -- /*******************************************************
633 -- * civicrm_uf_group
635 -- * 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.).
637 -- *******************************************************/
639 ALTER TABLE `civicrm_uf_group` ADD is_edit_link tinyint NULL DEFAULT '0' COMMENT 'Should edit link display in profile selector';
641 -- /*******************************************************
643 -- * Add UNIQUE indexes to maintain data integrity
645 -- *******************************************************/
647 CREATE UNIQUE INDEX UI_contact_group ON civicrm_group_contact(contact_id,group_id);
648 CREATE UNIQUE INDEX UI_contact ON civicrm_individual(contact_id);
649 CREATE UNIQUE INDEX UI_contact ON civicrm_household(contact_id);
650 CREATE UNIQUE INDEX UI_contact ON civicrm_organization(contact_id);
652 CREATE UNIQUE INDEX UI_location ON civicrm_address(location_id);
654 CREATE UNIQUE INDEX UI_contact ON civicrm_uf_match(contact_id);
655 DROP INDEX UI_uf_id ON civicrm_uf_match;
656 CREATE UNIQUE INDEX UI_uf_id ON civicrm_uf_match(uf_id);
657 CREATE UNIQUE INDEX UI_contrib_trxn_id_domain_id ON civicrm_contribution_recur(trxn_id, domain_id);
658 CREATE UNIQUE INDEX UI_contrib_invoice_id_domain_id ON civicrm_contribution_recur(invoice_id, domain_id);