| 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 | -- * |
| 26 | -- * Clean up the exisiting tables |
| 27 | -- * |
| 28 | -- *******************************************************/ |
| 29 | DROP TABLE IF EXISTS civicrm_mapping_field; |
| 30 | DROP TABLE IF EXISTS civicrm_mapping; |
| 31 | DROP TABLE IF EXISTS civicrm_individual_suffix; |
| 32 | DROP TABLE IF EXISTS civicrm_individual_prefix; |
| 33 | DROP TABLE IF EXISTS civicrm_gender; |
| 34 | |
| 35 | -- /******************************************************* |
| 36 | -- * |
| 37 | -- * Ensure we're working with InnoDBs for foreign keys' sake |
| 38 | -- * |
| 39 | -- *******************************************************/ |
| 40 | ALTER TABLE civicrm_domain ENGINE=InnoDB; |
| 41 | ALTER TABLE civicrm_location_type ENGINE=InnoDB; |
| 42 | ALTER TABLE civicrm_relationship_type ENGINE=InnoDB; |
| 43 | |
| 44 | -- /******************************************************* |
| 45 | -- * |
| 46 | -- * civicrm_mapping |
| 47 | -- * |
| 48 | -- * Store field mappings in import or export for reuse |
| 49 | -- * |
| 50 | -- *******************************************************/ |
| 51 | CREATE TABLE civicrm_mapping ( |
| 52 | |
| 53 | |
| 54 | id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Mapping ID', |
| 55 | domain_id int unsigned NOT NULL COMMENT 'Domain to which this mapping belongs', |
| 56 | name varchar(64) COMMENT 'Name of Mapping', |
| 57 | description varchar(255) COMMENT 'Description of Mapping.', |
| 58 | mapping_type enum('Export', 'Import') COMMENT 'Type of Mapping.' |
| 59 | , |
| 60 | PRIMARY KEY ( id ) |
| 61 | |
| 62 | , UNIQUE INDEX UI_name( |
| 63 | name |
| 64 | ) |
| 65 | |
| 66 | , |
| 67 | FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) |
| 68 | |
| 69 | ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; |
| 70 | |
| 71 | |
| 72 | -- /******************************************************* |
| 73 | -- * |
| 74 | -- * civicrm_individual_prefix |
| 75 | -- * |
| 76 | -- *******************************************************/ |
| 77 | CREATE TABLE civicrm_individual_prefix ( |
| 78 | |
| 79 | |
| 80 | id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Individual Prefix ID', |
| 81 | domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this individual prefix.', |
| 82 | name varchar(64) COMMENT 'Individual Prefix Name.', |
| 83 | weight int NOT NULL DEFAULT 1 COMMENT 'Controls Individual Prefix order in the select box.', |
| 84 | is_active tinyint COMMENT 'Is this property active?' |
| 85 | , |
| 86 | PRIMARY KEY ( id ) |
| 87 | |
| 88 | , UNIQUE INDEX UI_name_domain_id( |
| 89 | name |
| 90 | , domain_id |
| 91 | ) |
| 92 | |
| 93 | , |
| 94 | FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) |
| 95 | |
| 96 | ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; |
| 97 | |
| 98 | -- /******************************************************* |
| 99 | -- * |
| 100 | -- * civicrm_individual_suffix |
| 101 | -- * |
| 102 | -- *******************************************************/ |
| 103 | CREATE TABLE civicrm_individual_suffix ( |
| 104 | |
| 105 | |
| 106 | id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Individual Suffix ID', |
| 107 | domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this individual suffix.', |
| 108 | name varchar(64) COMMENT 'Individual Suffix Name.', |
| 109 | weight int NOT NULL DEFAULT 1 COMMENT 'Controls Individual Suffix order in the select box.', |
| 110 | is_active tinyint COMMENT 'Is this property active?' |
| 111 | , |
| 112 | PRIMARY KEY ( id ) |
| 113 | |
| 114 | , UNIQUE INDEX UI_name_domain_id( |
| 115 | name |
| 116 | , domain_id |
| 117 | ) |
| 118 | |
| 119 | , |
| 120 | FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) |
| 121 | |
| 122 | ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; |
| 123 | |
| 124 | |
| 125 | -- /******************************************************* |
| 126 | -- * |
| 127 | -- * civicrm_mapping_field |
| 128 | -- * |
| 129 | -- * Individual field mappings for Mapping |
| 130 | -- * |
| 131 | -- *******************************************************/ |
| 132 | CREATE TABLE civicrm_mapping_field ( |
| 133 | |
| 134 | |
| 135 | id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Mapping Field ID', |
| 136 | mapping_id int unsigned NOT NULL COMMENT 'Mapping to which this field belongs', |
| 137 | name varchar(64) COMMENT 'Mapping field key', |
| 138 | contact_type varchar(64) COMMENT 'Contact Type in mapping', |
| 139 | column_number int unsigned NOT NULL COMMENT 'Column number for mapping set', |
| 140 | location_type_id int unsigned COMMENT 'Location type of this mapping, if required', |
| 141 | phone_type varchar(64) COMMENT 'Phone type, if required', |
| 142 | relationship_type_id int unsigned COMMENT 'Relationship type, if required' |
| 143 | , |
| 144 | PRIMARY KEY ( id ) |
| 145 | |
| 146 | |
| 147 | , |
| 148 | FOREIGN KEY (mapping_id) REFERENCES civicrm_mapping(id) |
| 149 | , |
| 150 | FOREIGN KEY (location_type_id) REFERENCES civicrm_location_type(id) |
| 151 | , |
| 152 | FOREIGN KEY (relationship_type_id) REFERENCES civicrm_relationship_type(id) |
| 153 | |
| 154 | ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; |
| 155 | |
| 156 | |
| 157 | -- /******************************************************* |
| 158 | -- * |
| 159 | -- * civicrm_gender |
| 160 | -- * |
| 161 | -- *******************************************************/ |
| 162 | CREATE TABLE civicrm_gender ( |
| 163 | |
| 164 | |
| 165 | id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Gender ID', |
| 166 | domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this gender.', |
| 167 | name varchar(64) COMMENT 'Gender Name.', |
| 168 | weight int NOT NULL DEFAULT 1 COMMENT 'Controls Gender order in the select box.', |
| 169 | is_active tinyint COMMENT 'Is this property active?' |
| 170 | , |
| 171 | PRIMARY KEY ( id ) |
| 172 | |
| 173 | , UNIQUE INDEX UI_name_domain_id( |
| 174 | name |
| 175 | , domain_id |
| 176 | ) |
| 177 | |
| 178 | , |
| 179 | FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) |
| 180 | |
| 181 | ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; |
| 182 | |
| 183 | |
| 184 | -- /******************************************************* |
| 185 | -- * |
| 186 | -- * Modify the civicrm_uf_group Table Structure |
| 187 | -- * |
| 188 | -- *******************************************************/ |
| 189 | |
| 190 | ALTER TABLE civicrm_uf_group ADD help_pre text COMMENT 'Description and/or help text to display before fields in form.'; |
| 191 | |
| 192 | ALTER TABLE civicrm_uf_group ADD help_post text COMMENT 'Description and/or help text to display after fields in form.'; |
| 193 | |
| 194 | ALTER TABLE civicrm_uf_group ADD weight int NOT NULL DEFAULT 1 COMMENT 'Controls display order when multiple user framework groups are setup for concurrent display.'; |
| 195 | |
| 196 | |
| 197 | |
| 198 | |
| 199 | -- /******************************************************* |
| 200 | -- * |
| 201 | -- * Modify the civicrm_uf_field Table Structure |
| 202 | -- * |
| 203 | -- *******************************************************/ |
| 204 | |
| 205 | ALTER TABLE civicrm_uf_field ADD in_selector tinyint DEFAULT 1 COMMENT 'Is this field included as a column in the selector table?'; |
| 206 | |
| 207 | |
| 208 | |
| 209 | -- /******************************************************* |
| 210 | -- * |
| 211 | -- * Modify the civicrm_individual Table Structure |
| 212 | -- * |
| 213 | -- *******************************************************/ |
| 214 | |
| 215 | |
| 216 | ALTER TABLE civicrm_individual ADD prefix_id int unsigned COMMENT 'Prefix or Title for name (Ms, Mr...). FK to prefix ID'; |
| 217 | |
| 218 | ALTER TABLE civicrm_individual ADD suffix_id int unsigned COMMENT 'Suffix for name (Jr, Sr...). FK to suffix ID'; |
| 219 | |
| 220 | ALTER TABLE civicrm_individual ADD gender_id int unsigned COMMENT 'FK to gender ID'; |
| 221 | |
| 222 | |
| 223 | ALTER TABLE civicrm_individual ADD FOREIGN KEY (prefix_id) REFERENCES civicrm_individual_prefix(id); |
| 224 | |
| 225 | ALTER TABLE civicrm_individual ADD FOREIGN KEY (suffix_id) REFERENCES civicrm_individual_suffix(id); |
| 226 | |
| 227 | ALTER TABLE civicrm_individual ADD FOREIGN KEY (gender_id) REFERENCES civicrm_gender(id); |
| 228 | |
| 229 | |
| 230 | -- /******************************************************* |
| 231 | -- * |
| 232 | -- * Modify the civicrm_custom_field Table Structure |
| 233 | -- * |
| 234 | -- *******************************************************/ |
| 235 | |
| 236 | ALTER TABLE civicrm_custom_field ADD options_per_line int unsigned DEFAULT 0 COMMENT 'number of options per line for checkbox and radio'; |
| 237 | |
| 238 | -- /******************************************************* |
| 239 | -- * |
| 240 | -- * Modify the civicrm_group Table Structure |
| 241 | -- * |
| 242 | -- *******************************************************/ |
| 243 | |
| 244 | |
| 245 | ALTER TABLE civicrm_group ADD visibility enum('User and User Admin Only', 'Public User Pages', 'Public User Pages and Listings') DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.'; |
| 246 | |
| 247 | -- /******************************************************* |
| 248 | -- * |
| 249 | -- * Insert data in to civicrm_individual_prefix civicrm_individual_suffix and civicrm_gender |
| 250 | -- * |
| 251 | -- *******************************************************/ |
| 252 | |
| 253 | |
| 254 | INSERT INTO civicrm_individual_prefix (domain_id, name, weight, is_active) VALUES ( 1, 'Mrs', 1, 1); |
| 255 | INSERT INTO civicrm_individual_prefix (domain_id, name, weight, is_active) VALUES ( 1, 'Ms', 2, 1); |
| 256 | INSERT INTO civicrm_individual_prefix (domain_id, name, weight, is_active) VALUES ( 1, 'Mr', 3, 1); |
| 257 | INSERT INTO civicrm_individual_prefix (domain_id, name, weight, is_active) VALUES ( 1, 'Dr', 4, 1); |
| 258 | |
| 259 | INSERT INTO civicrm_individual_suffix (domain_id, name, weight, is_active) VALUES ( 1, 'Jr', 1, 1); |
| 260 | INSERT INTO civicrm_individual_suffix (domain_id, name, weight, is_active) VALUES ( 1, 'Sr', 2, 1); |
| 261 | INSERT INTO civicrm_individual_suffix (domain_id, name, weight, is_active) VALUES ( 1, 'II', 3, 1); |
| 262 | |
| 263 | INSERT INTO civicrm_gender (domain_id, name, weight, is_active) VALUES ( 1, 'Female', 1, 1); |
| 264 | INSERT INTO civicrm_gender (domain_id, name, weight, is_active) VALUES ( 1, 'Male', 2, 1); |
| 265 | INSERT INTO civicrm_gender (domain_id, name, weight, is_active) VALUES ( 1, 'Transgender', 3, 1); |
| 266 | |
| 267 | |
| 268 | -- /******************************************************* |
| 269 | -- * |
| 270 | -- * adding data for perefix, suffix and gender in the individual table |
| 271 | -- * |
| 272 | -- *******************************************************/ |
| 273 | |
| 274 | UPDATE civicrm_individual SET prefix_id=1 WHERE prefix='Mrs'; |
| 275 | UPDATE civicrm_individual SET prefix_id=2 WHERE prefix='Ms'; |
| 276 | UPDATE civicrm_individual SET prefix_id=3 WHERE prefix='Mr'; |
| 277 | UPDATE civicrm_individual SET prefix_id=4 WHERE prefix='Dr'; |
| 278 | |
| 279 | UPDATE civicrm_individual SET suffix_id=1 WHERE suffix='Jr'; |
| 280 | UPDATE civicrm_individual SET suffix_id=2 WHERE suffix='Sr'; |
| 281 | UPDATE civicrm_individual SET suffix_id=3 WHERE suffix='II'; |
| 282 | |
| 283 | UPDATE civicrm_individual SET gender_id=1 WHERE gender='Female'; |
| 284 | UPDATE civicrm_individual SET gender_id=2 WHERE gender='Male'; |
| 285 | UPDATE civicrm_individual SET gender_id=3 WHERE gender='Transgender'; |
| 286 | |
| 287 | -- Also reset all the is_reserved fields for the generic types |
| 288 | UPDATE civicrm_relationship_type SET is_reserved=0; |
| 289 | UPDATE civicrm_location_type SET is_reserved=0; |
| 290 | UPDATE civicrm_im_provider SET is_reserved=0; |
| 291 | UPDATE civicrm_mobile_provider SET is_reserved=0; |
| 292 | |
| 293 | -- /******************************************************* |
| 294 | -- * |
| 295 | -- * also update custom_field and group with the default values |
| 296 | -- * |
| 297 | -- *******************************************************/ |
| 298 | |
| 299 | UPDATE civicrm_custom_field SET options_per_line = 0; |
| 300 | UPDATE civicrm_custom_field SET options_per_line = 1 WHERE html_type = 'Radio' OR html_type = 'CheckBox'; |
| 301 | |
| 302 | UPDATE civicrm_group SET visibility = 'User and User Admin Only' WHERE 1; |
| 303 | |
| 304 | -- /******************************************************* |
| 305 | -- * |
| 306 | -- * Drop old columns |
| 307 | -- * |
| 308 | -- *******************************************************/ |
| 309 | |
| 310 | ALTER TABLE civicrm_uf_field DROP listings_title; |
| 311 | |
| 312 | |
| 313 | ALTER TABLE civicrm_individual DROP prefix; |
| 314 | |
| 315 | ALTER TABLE civicrm_individual DROP suffix; |
| 316 | |
| 317 | ALTER TABLE civicrm_individual DROP gender; |
| 318 | |
| 319 | |
| 320 | |
| 321 | -- /******************************************************* |
| 322 | -- * |
| 323 | -- * Drop Old Tables |
| 324 | -- * |
| 325 | -- *******************************************************/ |
| 326 | |
| 327 | DROP TABLE IF EXISTS civicrm_import_mapping_field; |
| 328 | |
| 329 | DROP TABLE IF EXISTS civicrm_import_mapping; |