Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | -- +--------------------------------------------------------------------+ |
2 | -- | CiviCRM version 3.2 | | |
3 | -- +--------------------------------------------------------------------+ | |
4 | -- | Copyright CiviCRM LLC (c) 2004-2010 | | |
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 | -- 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 | -- *******************************************************/ | |
28 | ||
29 | set @domain_id = 1; | |
30 | ||
31 | -- /******************************************************* | |
32 | -- * | |
33 | -- * update the option group and option value | |
34 | -- * | |
35 | -- *******************************************************/ | |
36 | ||
37 | INSERT INTO | |
38 | `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`) | |
39 | VALUES | |
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); | |
50 | ||
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'; | |
61 | ||
62 | INSERT INTO | |
63 | `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`) | |
64 | VALUES | |
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), | |
70 | ||
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), | |
74 | ||
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), | |
81 | ||
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), | |
85 | ||
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), | |
90 | ||
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), | |
99 | ||
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), | |
102 | ||
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), | |
107 | ||
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), | |
113 | ||
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); | |
117 | ||
118 | -- /******************************************************* | |
119 | -- * | |
120 | -- * add the Netherlands state of Limburg (CRM-1228) | |
121 | -- * | |
122 | -- *******************************************************/ | |
123 | ||
124 | INSERT INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES (5176, 1152, "LI", "Limburg"); | |
125 | ||
126 | -- /******************************************************* | |
127 | -- * | |
128 | -- * update the province list (CRM-1271) | |
129 | -- * | |
130 | -- *******************************************************/ | |
131 | ||
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; | |
146 | ||
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"); | |
162 | ||
163 | ||
164 | -- /******************************************************* | |
165 | -- * | |
166 | -- * update database (CRM-1275) | |
167 | -- * | |
168 | -- *******************************************************/ | |
169 | ||
170 | /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; | |
171 | ||
172 | -- /******************************************************* | |
173 | -- * | |
174 | -- * Clean up the exisiting tables | |
175 | -- * | |
176 | -- *******************************************************/ | |
177 | ||
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; | |
188 | ||
189 | ||
190 | -- /******************************************************* | |
191 | -- * | |
192 | -- * civicrm_acl_cache | |
193 | -- * | |
194 | -- * Cache for acls and contacts | |
195 | -- * | |
196 | -- *******************************************************/ | |
197 | CREATE TABLE civicrm_acl_cache ( | |
198 | ||
199 | ||
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' | |
204 | , | |
205 | PRIMARY KEY ( id ) | |
206 | ||
207 | ||
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) | |
213 | ||
214 | ) TYPE=InnoDB; | |
215 | ||
216 | -- /******************************************************* | |
217 | -- * | |
218 | -- * civicrm_acl_entity_role | |
219 | -- * | |
220 | -- * Join table for Contacts and Groups to ACL Roles | |
221 | -- * | |
222 | -- *******************************************************/ | |
223 | CREATE TABLE civicrm_acl_entity_role ( | |
224 | ||
225 | ||
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?' | |
232 | , | |
233 | PRIMARY KEY ( id ) | |
234 | ||
235 | , INDEX index_role( | |
236 | acl_role_id | |
237 | ) | |
238 | , INDEX index_entity( | |
239 | entity_table | |
240 | , entity_id | |
241 | ) | |
242 | ||
243 | , INDEX FKEY_domain_id ( domain_id ) , | |
244 | FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) | |
245 | ||
246 | ) TYPE=InnoDB ; | |
247 | ||
248 | ||
249 | -- /******************************************************* | |
250 | -- * | |
251 | -- * civicrm_msg_template | |
252 | -- * | |
253 | -- * Users will need a way to save and retrieve templates with tokens for use in recurring email communication tasks | |
254 | -- * | |
255 | -- *******************************************************/ | |
256 | CREATE TABLE civicrm_msg_template ( | |
257 | ||
258 | ||
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 | |
266 | , | |
267 | PRIMARY KEY ( id ) | |
268 | ||
269 | ||
270 | , INDEX FKEY_domain_id ( domain_id ) , | |
271 | FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) | |
272 | ||
273 | ) TYPE=InnoDB ; | |
274 | ||
275 | -- /******************************************************* | |
276 | -- * | |
277 | -- * Modify the civicrm_acl Table Structure | |
278 | -- *******************************************************/ | |
279 | ||
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); | |
283 | ||
284 | ALTER TABLE `civicrm_acl` ADD name varchar(64) COMMENT 'ACL Name.'; | |
285 | ||
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?'; | |
287 | ||
288 | ALTER TABLE `civicrm_acl` ADD is_active tinyint(4) COMMENT 'Is this property active?'; | |
289 | ||
290 | ALTER TABLE `civicrm_acl` ADD INDEX index_acl_id (acl_id); | |
291 | ||
292 | -- /******************************************************* | |
293 | -- * | |
294 | -- * civicrm_activity | |
295 | -- * | |
296 | -- * Join table for activities | |
297 | -- * | |
298 | -- *******************************************************/ | |
299 | ||
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`; | |
303 | ||
304 | -- /******************************************************* | |
305 | -- * | |
306 | -- * civicrm_activity_history | |
307 | -- * | |
308 | -- * Record history for an entity in the crm module | |
309 | -- * | |
310 | -- *******************************************************/ | |
311 | ||
312 | ALTER TABLE `civicrm_activity_history` ADD INDEX index_activity (`activity_id`); | |
313 | ||
314 | -- /******************************************************* | |
315 | -- * | |
316 | -- * civicrm_contact | |
317 | -- * | |
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. | |
319 | -- * | |
320 | -- *******************************************************/ | |
321 | ||
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); | |
325 | ||
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; | |
329 | ||
330 | -- /******************************************************* | |
331 | -- * | |
332 | -- * civicrm_contribution | |
333 | -- * | |
334 | -- *******************************************************/ | |
335 | ||
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`; | |
341 | ||
342 | ALTER TABLE `civicrm_contribution` DROP recur_contribution_id; | |
343 | ||
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`); | |
349 | ||
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`); | |
354 | ||
355 | UPDATE civicrm_contribution SET contribution_status_id=3 WHERE cancel_date IS NOT NULL; | |
356 | ||
357 | -- /******************************************************* | |
358 | -- * | |
359 | -- * civicrm_contribution_page | |
360 | -- * | |
361 | -- * A Contribution object store meta information about a single customized contribution page | |
362 | -- * | |
363 | -- *******************************************************/ | |
364 | ||
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; | |
370 | ||
371 | ||
372 | -- /******************************************************* | |
373 | -- * | |
374 | -- * civicrm_contribution_recur | |
375 | -- * | |
376 | -- *******************************************************/ | |
377 | ||
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; | |
384 | ||
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; | |
391 | ||
392 | ALTER TABLE `civicrm_contribution_recur` DROP is_active; | |
393 | ||
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); | |
397 | ||
398 | -- /******************************************************* | |
399 | -- * | |
400 | -- * civicrm_custom_group | |
401 | -- * | |
402 | -- * All extended (custom) properties are associated with a group. These are logical sets of related data. | |
403 | -- * | |
404 | -- *******************************************************/ | |
405 | ||
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'; | |
408 | ||
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.).'; | |
410 | ||
411 | UPDATE civicrm_custom_group | |
412 | SET extends_entity_column_value=NULL | |
413 | WHERE extends='Activity'; | |
414 | ||
415 | UPDATE civicrm_custom_group | |
416 | SET extends='Activity', extends_entity_column_value='2' | |
417 | WHERE extends='Phonecall'; | |
418 | ||
419 | UPDATE civicrm_custom_group | |
420 | SET extends='Activity', extends_entity_column_value='1' | |
421 | WHERE extends='Meeting'; | |
422 | ||
423 | UPDATE civicrm_custom_group | |
424 | SET extends_entity_column_value=NULL | |
425 | WHERE extends='Contact'; | |
426 | ||
427 | UPDATE civicrm_custom_group | |
428 | SET extends_entity_column_value=NULL | |
429 | WHERE extends='Relationship'; | |
430 | ||
431 | -- /******************************************************* | |
432 | -- * | |
433 | -- * civicrm_custom_value | |
434 | -- * | |
435 | -- * Data store for each extended properties. | |
436 | -- * | |
437 | -- *******************************************************/ | |
438 | ||
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); | |
444 | ||
445 | -- /******************************************************* | |
446 | -- * | |
447 | -- * civicrm_domain | |
448 | -- * | |
449 | -- * Top-level hierarchy to support multi-org/domain installations. Define domains for multi-org installs, else all contacts belong to one domain. | |
450 | -- * | |
451 | -- *******************************************************/ | |
452 | ||
453 | ALTER TABLE civicrm_domain ADD config_backend text COMMENT 'Backend configuration.'; | |
454 | ALTER TABLE civicrm_domain ADD config_frontend text COMMENT 'Frontend configuration.'; | |
455 | ||
456 | -- /******************************************************* | |
457 | -- * | |
458 | -- * civicrm_entity_tag | |
459 | -- * | |
460 | -- * Tag entities (Contacts, Groups, Actions) to categories. | |
461 | -- * | |
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; | |
465 | ||
466 | -- /******************************************************* | |
467 | -- * | |
468 | -- * civicrm_file | |
469 | -- * | |
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. | |
471 | -- * | |
472 | -- *******************************************************/ | |
473 | ||
474 | ALTER TABLE `civicrm_file` DROP FOREIGN KEY `civicrm_file_ibfk_1`; | |
475 | ALTER TABLE `civicrm_file` DROP INDEX `FKEY_file_type_id`; | |
476 | ||
477 | -- /******************************************************* | |
478 | -- * | |
479 | -- * civicrm_group | |
480 | -- * | |
481 | -- * Provide grouping of related contacts | |
482 | -- * | |
483 | -- *******************************************************/ | |
484 | ||
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; | |
488 | ||
489 | -- /******************************************************* | |
490 | -- * | |
491 | -- * civicrm_im | |
492 | -- * | |
493 | -- * IM information for a specific location. | |
494 | -- * | |
495 | -- *******************************************************/ | |
496 | ||
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); | |
500 | ||
501 | ||
502 | -- /******************************************************* | |
503 | -- * | |
504 | -- * civicrm_individual | |
505 | -- * | |
506 | -- * Define contact-individual specific properties. Extends civicrm_contact. | |
507 | -- * | |
508 | -- *******************************************************/ | |
509 | ||
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; | |
513 | ||
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; | |
517 | ||
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); | |
521 | ||
522 | -- /******************************************************* | |
523 | -- * | |
524 | -- * civicrm_mapping | |
525 | -- * | |
526 | -- * Store field mappings in import or export for reuse | |
527 | -- * | |
528 | -- *******************************************************/ | |
529 | ||
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.'; | |
531 | ||
532 | -- /******************************************************* | |
533 | -- * | |
534 | -- * civicrm_membership | |
535 | -- * | |
536 | -- * Contact Membership records. | |
537 | -- * | |
538 | -- *******************************************************/ | |
539 | ||
540 | ALTER TABLE civicrm_membership ADD reminder_date date AFTER is_override; | |
541 | ||
542 | -- /******************************************************* | |
543 | -- * | |
544 | -- * civicrm_membership_log | |
545 | -- * | |
546 | -- * Logs actions which affect a Membership record (signup, status override, renewal, etc.) | |
547 | -- * | |
548 | -- *******************************************************/ | |
549 | ||
550 | ALTER TABLE civicrm_membership_log ADD renewal_reminder_date date AFTER modified_date; | |
551 | ||
552 | -- /******************************************************* | |
553 | -- * | |
554 | -- * civicrm_membership_type | |
555 | -- * | |
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. | |
557 | -- * | |
558 | -- *******************************************************/ | |
559 | ||
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; | |
562 | ||
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); | |
565 | ||
566 | -- /******************************************************* | |
567 | -- * | |
568 | -- * civicrm_phone | |
569 | -- * | |
570 | -- * Phone information for a specific location. | |
571 | -- * | |
572 | -- *******************************************************/ | |
573 | ||
574 | ALTER TABLE `civicrm_phone` DROP FOREIGN KEY civicrm_phone_ibfk_2; | |
575 | ALTER TABLE `civicrm_phone` DROP INDEX FKEY_mobile_provider_id; | |
576 | ||
577 | ALTER TABLE `civicrm_phone` ADD INDEX UI_mobile_provider_id (mobile_provider_id); | |
578 | ||
579 | -- /******************************************************* | |
580 | -- * | |
581 | -- * civicrm_project | |
582 | -- * | |
583 | -- * Instantiate projects, programs, campaigns, etc. | |
584 | -- * | |
585 | -- *******************************************************/ | |
586 | ||
587 | ALTER TABLE civicrm_project DROP FOREIGN KEY civicrm_project_ibfk_2; | |
588 | ALTER TABLE civicrm_project DROP INDEX FKEY_status_id; | |
589 | ||
590 | -- /******************************************************* | |
591 | -- * | |
592 | -- * civicrm_saved_search | |
593 | -- * | |
594 | -- * Users can save their complex SQL queries and use them later. | |
595 | -- * | |
596 | -- *******************************************************/ | |
597 | ||
598 | ALTER TABLE `civicrm_saved_search` ADD where_clause text DEFAULT NULL COMMENT 'the sql where clause if a saved search acl'; | |
599 | ||
600 | ALTER TABLE `civicrm_saved_search` ADD select_tables text DEFAULT NULL COMMENT 'the tables to be included in a select data'; | |
601 | ||
602 | ALTER TABLE `civicrm_saved_search` ADD where_tables text DEFAULT NULL COMMENT 'the tables to be included in the count statement'; | |
603 | ||
604 | -- /*******************************************************/ | |
605 | ||
606 | -- /******************************************************* | |
607 | -- * | |
608 | -- * civicrm_task | |
609 | -- * | |
610 | -- * To-do items. Can be assigned to self or other entities. | |
611 | -- * | |
612 | -- *******************************************************/ | |
613 | ||
614 | ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_2; | |
615 | ALTER TABLE `civicrm_task` DROP FOREIGN KEY civicrm_task_ibfk_3; | |
616 | ||
617 | ALTER TABLE `civicrm_task` DROP INDEX FKEY_task_type_id; | |
618 | ALTER TABLE `civicrm_task` DROP INDEX FKEY_priority_id; | |
619 | ||
620 | -- /******************************************************* | |
621 | -- * | |
622 | -- * civicrm_task_status | |
623 | -- * | |
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. | |
625 | -- * | |
626 | -- *******************************************************/ | |
627 | ||
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; | |
630 | ||
631 | -- /******************************************************* | |
632 | -- * | |
633 | -- * civicrm_uf_group | |
634 | -- * | |
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.). | |
636 | -- * | |
637 | -- *******************************************************/ | |
638 | ||
639 | ALTER TABLE `civicrm_uf_group` ADD is_edit_link tinyint NULL DEFAULT '0' COMMENT 'Should edit link display in profile selector'; | |
640 | ||
641 | -- /******************************************************* | |
642 | -- * | |
643 | -- * Add UNIQUE indexes to maintain data integrity | |
644 | -- * | |
645 | -- *******************************************************/ | |
646 | ||
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); | |
651 | ||
652 | CREATE UNIQUE INDEX UI_location ON civicrm_address(location_id); | |
653 | ||
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); | |
659 |