| 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 | |