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");
163 -- /*******************************************************
165 -- * update database (CRM-1275)
167 -- *******************************************************/
169 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
171 -- /*******************************************************
173 -- * Clean up the exisiting tables
175 -- *******************************************************/
177 DROP TABLE IF EXISTS civicrm_accept_credit_card;
178 DROP TABLE IF EXISTS civicrm_activity_type;
179 DROP TABLE IF EXISTS civicrm_acl_group;
180 DROP TABLE IF EXISTS civicrm_gender;
181 DROP TABLE IF EXISTS civicrm_individual_prefix;
182 DROP TABLE IF EXISTS civicrm_individual_suffix;
183 DROP TABLE IF EXISTS civicrm_im_provider;
184 DROP TABLE IF EXISTS civicrm_mobile_provider;
185 DROP TABLE IF EXISTS civicrm_payment_instrument;
186 DROP TABLE IF EXISTS civicrm_acl_group_join;
188 -- /*******************************************************
190 -- * civicrm_acl_cache
192 -- * Cache for acls and contacts
194 -- *******************************************************/
195 CREATE TABLE civicrm_acl_cache (
197 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID',
198 contact_id int unsigned COMMENT 'Foreign Key to Contact',
199 acl_id int unsigned NOT NULL COMMENT 'Foreign Key to ACL',
200 modified_date date COMMENT 'When was this cache entry last modified'
204 INDEX index_acl_id( acl_id),
205 INDEX contact_id (contact_id)
207 FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
209 FOREIGN KEY (acl_id) REFERENCES civicrm_acl(id)
211 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
213 -- /*******************************************************
215 -- * civicrm_acl_entity_role
217 -- * Join table for Contacts and Groups to ACL Roles
219 -- *******************************************************/
220 CREATE TABLE civicrm_acl_entity_role (
221 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique table ID',
222 domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact',
223 acl_role_id int unsigned NOT NULL COMMENT 'Foreign Key to ACL Role (which is an option value pair and hence an implicit FK)',
224 entity_table varchar(64) NOT NULL COMMENT 'Table of the object joined to the ACL Role (Contact or Group)',
225 entity_id int unsigned NOT NULL COMMENT 'ID of the group/contact object being joined',
226 is_active tinyint COMMENT 'Is this property active?'
233 , INDEX index_entity(
241 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
243 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
245 -- /*******************************************************
247 -- * civicrm_msg_template
249 -- * Users will need a way to save and retrieve templates with tokens for use in recurring email communication tasks
251 -- *******************************************************/
252 CREATE TABLE civicrm_msg_template (
254 id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Message Template ID',
255 domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contact',
256 msg_title varchar(255) COMMENT 'Descriptive title of message',
257 msg_subject varchar(255) COMMENT 'Subject for email message.',
258 msg_text text COMMENT 'Text formatted message',
259 msg_html text COMMENT 'HTML formatted message',
260 is_active tinyint DEFAULT 1
266 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
268 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
270 -- /*******************************************************
272 -- * Modify the civicrm_acl Table Structure
273 -- *******************************************************/
275 ALTER TABLE `civicrm_acl` ADD domain_id int(10) unsigned NOT NULL COMMENT 'Which Domain owns this contact';
276 ALTER TABLE `civicrm_acl` ADD FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`);
277 ALTER TABLE `civicrm_acl` ADD INDEX `domain_id` (`domain_id`);
279 ALTER TABLE `civicrm_acl` ADD name varchar(64) COMMENT 'ACL Name.';
281 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?';
282 ALTER TABLE `civicrm_acl` ADD is_active tinyint(4) COMMENT 'Is this property active?' AFTER acl_id;
284 ALTER TABLE `civicrm_acl` ADD INDEX index_acl_id (acl_id);
286 -- /*******************************************************
288 -- * civicrm_activity
290 -- * Join table for activities
292 -- *******************************************************/
294 ALTER TABLE `civicrm_activity` DROP FOREIGN KEY civicrm_activity_ibfk_2;
295 ALTER TABLE `civicrm_activity` ADD INDEX UI_activity_type_id (`activity_type_id`);
297 -- /*******************************************************
299 -- * civicrm_activity_history
301 -- * Record history for an entity in the crm module
303 -- *******************************************************/
305 ALTER TABLE civicrm_activity_history ADD INDEX index_activity (activity_id);
307 -- /*******************************************************
311 -- * 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.
313 -- *******************************************************/
315 ALTER TABLE civicrm_contact ADD INDEX index_contact_type_domain (contact_type, domain_id);
316 ALTER TABLE civicrm_contact ADD INDEX index_contact_sub_type_domain (contact_sub_type, domain_id);
318 ALTER TABLE civicrm_contact ADD INDEX index_preferred_communication_method (preferred_communication_method);
320 UPDATE civicrm_contact
321 SET `preferred_communication_method` = CONCAT(char(1),`preferred_communication_method`,char(1))
322 WHERE civicrm_contact.preferred_communication_method IS NOT NULL;
324 -- /*******************************************************
326 -- * civicrm_contribution
328 -- *******************************************************/
330 ALTER TABLE `civicrm_contribution` DROP FOREIGN KEY `civicrm_contribution_ibfk_6`;
331 ALTER TABLE `civicrm_contribution` DROP FOREIGN KEY `civicrm_contribution_ibfk_7`;
333 ALTER TABLE `civicrm_contribution` DROP `recur_contribution_id`;
335 ALTER TABLE `civicrm_contribution` ADD `honor_contact_id` int unsigned COMMENT 'FK to contact ID';
336 ALTER TABLE `civicrm_contribution` ADD FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact` (`id`);
338 ALTER TABLE `civicrm_contribution` ADD contribution_recur_id int unsigned COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each contribution made in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.' AFTER note;
339 ALTER TABLE `civicrm_contribution` ADD FOREIGN KEY (`contribution_recur_id`) REFERENCES civicrm_contribution_recur(`id`);
341 ALTER TABLE `civicrm_contribution` ADD is_test tinyint DEFAULT 0;
342 ALTER TABLE `civicrm_contribution` ADD `contribution_status_id` int unsigned NULL DEFAULT '1';
343 ALTER TABLE `civicrm_contribution` ADD INDEX honor_contact_id (`honor_contact_id`);
344 ALTER TABLE `civicrm_contribution` ADD INDEX UI_contrib_payment_instrument_id (payment_instrument_id);
345 ALTER TABLE `civicrm_contribution` ADD INDEX index_contribution_status (contribution_status_id);
347 -- /*******************************************************
349 -- * civicrm_contribution_page
351 -- * A Contribution object store meta information about a single customized contribution page
353 -- *******************************************************/
355 ALTER TABLE `civicrm_contribution_page` ADD honor_block_is_active tinyint COMMENT 'Should this contribution have the honor block enabled?';
356 ALTER TABLE `civicrm_contribution_page` ADD honor_block_title varchar(255) COMMENT 'Title for honor block.';
357 ALTER TABLE `civicrm_contribution_page` ADD honor_block_text text COMMENT 'text for honor block.';
358 ALTER TABLE `civicrm_contribution_page` ADD is_monetary tinyint NULL DEFAULT '1' COMMENT 'if true - allows real-time monetary transactions otherwise non-monetary transactions';
359 ALTER TABLE `civicrm_contribution_page` ADD is_recur tinyint NULL DEFAULT '0' COMMENT 'if true - allows recurring contributions, valid only for PayPal_Standard' AFTER is_monetary;
361 -- /*******************************************************
363 -- * civicrm_contribution_recur
365 -- *******************************************************/
366 ALTER TABLE civicrm_contribution_recur DROP is_active;
368 ALTER TABLE civicrm_contribution_recur ADD domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this contribution class.' AFTER id;
369 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';
370 ALTER TABLE civicrm_contribution_recur ADD invoice_id varchar(255) COMMENT 'unique invoice id, system generated or passed in' AFTER trxn_id;
371 ALTER TABLE civicrm_contribution_recur ADD contribution_status_id int unsigned NULL DEFAULT '1' AFTER invoice_id;
372 ALTER TABLE civicrm_contribution_recur ADD end_date datetime NOT NULL COMMENT 'Date this recurring contribution finished successfully' AFTER cancel_date;
373 ALTER TABLE civicrm_contribution_recur ADD is_test tinyint NULL DEFAULT '0'AFTER contribution_status_id ;
375 ALTER TABLE civicrm_contribution_recur MODIFY start_date datetime NOT NULL COMMENT 'The date the first scheduled recurring contribution occurs.';
376 ALTER TABLE civicrm_contribution_recur MODIFY create_date datetime NOT NULL COMMENT 'When this recurring contribution record was created.';
377 ALTER TABLE civicrm_contribution_recur MODIFY modified_date datetime NOT NULL COMMENT 'Last updated date for this record. mostly the last time a payment was received';
378 ALTER TABLE civicrm_contribution_recur MODIFY cancel_date datetime NOT NULL COMMENT 'Date this recurring contribution was cancelled by contributor- if we can get access to it';
379 ALTER TABLE civicrm_contribution_recur MODIFY next_sched_contribution datetime NOT NULL COMMENT 'At Groundspring this was used by the cron job which triggered payments. If we\'re not doing that but we know about payments, it might still be useful to store for display to org andor contributors.';
380 ALTER TABLE civicrm_contribution_recur MODIFY failure_retry_date datetime COMMENT 'At Groundspring we set a business rule to retry failed payments every 7 days - and stored the next scheduled attempt date there.';
382 ALTER TABLE civicrm_contribution_recur ADD INDEX index_contribution_status( contribution_status_id );
384 ALTER TABLE civicrm_contribution_recur ADD FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id);
386 -- /*******************************************************
388 -- * civicrm_custom_group
390 -- * All extended (custom) properties are associated with a group. These are logical sets of related data.
392 -- *******************************************************/
394 ALTER TABLE `civicrm_custom_group` ADD extends_entity_column_name varchar(64) COMMENT 'linking custom group for dynamic object';
395 ALTER TABLE `civicrm_custom_group` ADD extends_entity_column_value varchar(64) COMMENT 'linking custom group for dynamic object';
396 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.).';
398 UPDATE civicrm_custom_group
399 SET extends_entity_column_value=NULL
400 WHERE extends='Activity';
402 UPDATE civicrm_custom_group
403 SET extends='Activity', extends_entity_column_value='2'
404 WHERE extends='Phonecall';
406 UPDATE civicrm_custom_group
407 SET extends='Activity', extends_entity_column_value='1'
408 WHERE extends='Meeting';
410 UPDATE civicrm_custom_group
411 SET extends_entity_column_value=NULL
412 WHERE extends='Contact';
414 UPDATE civicrm_custom_group
415 SET extends_entity_column_value=NULL
416 WHERE extends='Relationship';
418 -- /*******************************************************
420 -- * civicrm_custom_value
422 -- * Data store for each extended properties.
424 -- *******************************************************/
426 ALTER TABLE civicrm_custom_value ADD INDEX index_int_field (int_data, custom_field_id);
427 ALTER TABLE civicrm_custom_value ADD INDEX index_float_field (float_data, custom_field_id);
428 ALTER TABLE civicrm_custom_value ADD INDEX index_decimal_field (decimal_data, custom_field_id);
429 ALTER TABLE civicrm_custom_value ADD INDEX index_char_field (char_data, custom_field_id);
430 ALTER TABLE civicrm_custom_value ADD INDEX index_date_field (date_data, custom_field_id);
432 -- /*******************************************************
436 -- * Top-level hierarchy to support multi-org/domain installations. Define domains for multi-org installs, else all contacts belong to one domain.
438 -- *******************************************************/
440 ALTER TABLE civicrm_domain ADD config_backend text COMMENT 'Backend configuration.';
441 ALTER TABLE civicrm_domain ADD config_frontend text COMMENT 'Frontend configuration.';
443 -- /*******************************************************
445 -- * civicrm_entity_tag
447 -- * Tag entities (Contacts, Groups, Actions) to categories.
449 -- *******************************************************/
450 ALTER TABLE `civicrm_entity_tag` ALTER COLUMN `entity_id` DROP DEFAULT;
451 ALTER TABLE `civicrm_entity_tag` ALTER COLUMN `tag_id` DROP DEFAULT;
453 -- /*******************************************************
457 -- * 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.
459 -- *******************************************************/
461 ALTER TABLE `civicrm_file` DROP FOREIGN KEY `civicrm_file_ibfk_1`;
462 ALTER TABLE `civicrm_file` DROP `file_type_id`;
463 ALTER TABLE `civicrm_file` ADD file_type_id int unsigned COMMENT 'Type of file (e.g. Transcript, Income Tax Return, etc). FK to civicrm_option_value.';
465 -- /*******************************************************
469 -- * Provide grouping of related contacts
471 -- *******************************************************/
473 ALTER TABLE civicrm_group ADD where_clause text COMMENT 'the sql where clause if a saved search acl';
474 ALTER TABLE civicrm_group ADD select_tables text COMMENT 'the tables to be included in a select data';
475 ALTER TABLE civicrm_group ADD where_tables text COMMENT 'the tables to be included in the count statement';
477 -- /*******************************************************
479 -- * civicrm_group_contact
481 -- * Join table sets membership for 'static' groups. Also used to store 'opt-out' entries for 'query' type groups (status = 'OUT')
483 -- *******************************************************/
485 ALTER TABLE civicrm_group_contact DROP FOREIGN KEY civicrm_group_contact_ibfk_2;
487 -- /*******************************************************
489 -- * civicrm_household
491 -- * Define household specific properties
493 -- *******************************************************/
495 ALTER TABLE civicrm_household DROP FOREIGN KEY civicrm_household_ibfk_1;
497 -- /*******************************************************
501 -- * IM information for a specific location.
503 -- *******************************************************/
505 ALTER TABLE civicrm_im DROP FOREIGN KEY civicrm_im_ibfk_2;
506 ALTER TABLE civicrm_im ADD INDEX UI_provider_id (provider_id);
508 -- /*******************************************************
510 -- * civicrm_individual
512 -- * Define contact-individual specific properties. Extends civicrm_contact.
514 -- *******************************************************/
516 ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_1;
517 ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_2;
518 ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_3;
519 ALTER TABLE civicrm_individual DROP FOREIGN KEY civicrm_individual_ibfk_4;
520 ALTER TABLE civicrm_individual ADD INDEX UI_prefix (prefix_id);
521 ALTER TABLE civicrm_individual ADD INDEX UI_suffix (suffix_id);
522 ALTER TABLE civicrm_individual ADD INDEX UI_gender (gender_id);
524 -- /*******************************************************
528 -- * Store field mappings in import or export for reuse
530 -- *******************************************************/
532 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.';
534 -- /*******************************************************
536 -- * civicrm_membership
538 -- * Contact Membership records.
540 -- *******************************************************/
542 ALTER TABLE `civicrm_membership` ADD reminder_date date COMMENT 'When should a reminder be sent.' AFTER is_override;
544 -- /*******************************************************
546 -- * civicrm_membership_log
548 -- * Logs actions which affect a Membership record (signup, status override, renewal, etc.)
550 -- *******************************************************/
552 ALTER TABLE civicrm_membership_log ADD renewal_reminder_date date COMMENT 'The day we sent a renewal reminder' AFTER modified_date;
554 -- /*******************************************************
556 -- * civicrm_membership_type
558 -- * 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.
560 -- *******************************************************/
562 ALTER TABLE civicrm_membership_type ADD renewal_msg_id int unsigned COMMENT 'FK to civicrm_msg_template.id' AFTER weight;
563 ALTER TABLE civicrm_membership_type ADD renewal_reminder_day int COMMENT 'Number of days prior to expiration to send renewal reminder' AFTER renewal_msg_id;
565 ALTER TABLE civicrm_membership_type ADD FOREIGN KEY (renewal_msg_id) REFERENCES civicrm_msg_template(id);
567 -- /*******************************************************
569 -- * civicrm_organization
571 -- * Define organization specific properties
573 -- *******************************************************/
575 ALTER TABLE civicrm_organization DROP FOREIGN KEY civicrm_organization_ibfk_1;
577 -- /*******************************************************
581 -- * Phone information for a specific location.
583 -- *******************************************************/
585 ALTER TABLE `civicrm_phone` DROP FOREIGN KEY civicrm_phone_ibfk_2;
587 ALTER TABLE `civicrm_phone` ADD INDEX UI_mobile_provider_id (mobile_provider_id);
589 -- /*******************************************************
593 -- * Instantiate projects, programs, campaigns, etc.
595 -- *******************************************************/
597 ALTER TABLE civicrm_project DROP FOREIGN KEY civicrm_project_ibfk_2;
598 ALTER TABLE civicrm_project DROP `status_id`;
599 ALTER TABLE civicrm_project ADD status_id int unsigned COMMENT 'Configurable status value (e.g. Planned, Active, Closed...). FK to civicrm_option_value.';
601 -- /*******************************************************
603 -- * civicrm_saved_search
605 -- * Users can save their complex SQL queries and use them later.
607 -- *******************************************************/
609 ALTER TABLE `civicrm_saved_search` ADD where_clause text DEFAULT NULL COMMENT 'the sql where clause if a saved search acl';
611 ALTER TABLE `civicrm_saved_search` ADD select_tables text DEFAULT NULL COMMENT 'the tables to be included in a select data';
613 ALTER TABLE `civicrm_saved_search` ADD where_tables text DEFAULT NULL COMMENT 'the tables to be included in the count statement';
615 -- /*******************************************************
619 -- * To-do items. Can be assigned to self or other entities.
621 -- *******************************************************/
623 ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_2;
624 ALTER TABLE `civicrm_task` DROP `task_type_id`;
625 ALTER TABLE `civicrm_task` ADD task_type_id int unsigned COMMENT 'Configurable task type values (e.g. App Submit, App Review...). FK to civicrm_option_value.';
627 ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_3;
628 ALTER TABLE `civicrm_task` DROP `priority_id`;
629 ALTER TABLE `civicrm_task` ADD priority_id int unsigned COMMENT 'Configurable priority value (e.g. Critical, High, Medium...). FK to civicrm_option_value.';
631 -- /*******************************************************
633 -- * civicrm_task_status
635 -- * 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.
637 -- *******************************************************/
639 ALTER TABLE `civicrm_task_status` DROP FOREIGN KEY civicrm_task_status_ibfk_2;
640 ALTER TABLE `civicrm_task_status` DROP `status_id`;
641 ALTER TABLE `civicrm_task_status` ADD status_id int unsigned COMMENT 'Configurable status value (e.g. Not Started, In Progress, Completed, Deferred...). FK to civicrm_option_value.';
643 -- /*******************************************************
645 -- * civicrm_uf_group
647 -- * 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.).
649 -- *******************************************************/
651 ALTER TABLE `civicrm_uf_group` ADD is_edit_link tinyint NULL DEFAULT '0' COMMENT 'Should edit link display in profile selector';
653 -- /*******************************************************
655 -- * Add UNIQUE indexes to maintain data integrity
657 -- *******************************************************/
659 CREATE UNIQUE INDEX UI_contact_group ON civicrm_group_contact(contact_id,group_id);
660 CREATE UNIQUE INDEX UI_contact ON civicrm_individual(contact_id);
661 CREATE UNIQUE INDEX UI_contact ON civicrm_household(contact_id);
662 CREATE UNIQUE INDEX UI_contact ON civicrm_organization(contact_id);
663 CREATE UNIQUE INDEX UI_location ON civicrm_address(location_id);
664 CREATE UNIQUE INDEX UI_contact ON civicrm_uf_match(contact_id);
665 DROP INDEX UI_uf_id ON civicrm_uf_match;
666 CREATE UNIQUE INDEX UI_uf_id ON civicrm_uf_match(uf_id);
667 CREATE UNIQUE INDEX UI_contrib_trxn_id_domain_id ON civicrm_contribution_recur(trxn_id,domain_id);
668 CREATE UNIQUE INDEX UI_contrib_invoice_id_domain_id ON civicrm_contribution_recur(invoice_id,domain_id);