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 -- /*******************************************************
26 -- * Clean up the exisiting tables
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;
35 -- /*******************************************************
37 -- * Ensure we're working with InnoDBs for foreign keys' sake
39 -- *******************************************************/
40 ALTER TABLE civicrm_domain ENGINE=InnoDB;
41 ALTER TABLE civicrm_location_type ENGINE=InnoDB;
42 ALTER TABLE civicrm_relationship_type ENGINE=InnoDB;
44 -- /*******************************************************
48 -- * Store field mappings in import or export for reuse
50 -- *******************************************************/
51 CREATE TABLE civicrm_mapping (
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.'
62 , UNIQUE INDEX UI_name(
67 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
69 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
72 -- /*******************************************************
74 -- * civicrm_individual_prefix
76 -- *******************************************************/
77 CREATE TABLE civicrm_individual_prefix (
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?'
88 , UNIQUE INDEX UI_name_domain_id(
94 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
96 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
98 -- /*******************************************************
100 -- * civicrm_individual_suffix
102 -- *******************************************************/
103 CREATE TABLE civicrm_individual_suffix (
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?'
114 , UNIQUE INDEX UI_name_domain_id(
120 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
122 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
125 -- /*******************************************************
127 -- * civicrm_mapping_field
129 -- * Individual field mappings for Mapping
131 -- *******************************************************/
132 CREATE TABLE civicrm_mapping_field (
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'
148 FOREIGN KEY (mapping_id) REFERENCES civicrm_mapping(id)
150 FOREIGN KEY (location_type_id) REFERENCES civicrm_location_type(id)
152 FOREIGN KEY (relationship_type_id) REFERENCES civicrm_relationship_type(id)
154 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
157 -- /*******************************************************
161 -- *******************************************************/
162 CREATE TABLE civicrm_gender (
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?'
173 , UNIQUE INDEX UI_name_domain_id(
179 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
181 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
184 -- /*******************************************************
186 -- * Modify the civicrm_uf_group Table Structure
188 -- *******************************************************/
190 ALTER TABLE civicrm_uf_group ADD help_pre text COMMENT 'Description and/or help text to display before fields in form.';
192 ALTER TABLE civicrm_uf_group ADD help_post text COMMENT 'Description and/or help text to display after fields in form.';
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.';
199 -- /*******************************************************
201 -- * Modify the civicrm_uf_field Table Structure
203 -- *******************************************************/
205 ALTER TABLE civicrm_uf_field ADD in_selector tinyint DEFAULT 1 COMMENT 'Is this field included as a column in the selector table?';
209 -- /*******************************************************
211 -- * Modify the civicrm_individual Table Structure
213 -- *******************************************************/
216 ALTER TABLE civicrm_individual ADD prefix_id int unsigned COMMENT 'Prefix or Title for name (Ms, Mr...). FK to prefix ID';
218 ALTER TABLE civicrm_individual ADD suffix_id int unsigned COMMENT 'Suffix for name (Jr, Sr...). FK to suffix ID';
220 ALTER TABLE civicrm_individual ADD gender_id int unsigned COMMENT 'FK to gender ID';
223 ALTER TABLE civicrm_individual ADD FOREIGN KEY (prefix_id) REFERENCES civicrm_individual_prefix(id);
225 ALTER TABLE civicrm_individual ADD FOREIGN KEY (suffix_id) REFERENCES civicrm_individual_suffix(id);
227 ALTER TABLE civicrm_individual ADD FOREIGN KEY (gender_id) REFERENCES civicrm_gender(id);
230 -- /*******************************************************
232 -- * Modify the civicrm_custom_field Table Structure
234 -- *******************************************************/
236 ALTER TABLE civicrm_custom_field ADD options_per_line int unsigned DEFAULT 0 COMMENT 'number of options per line for checkbox and radio';
238 -- /*******************************************************
240 -- * Modify the civicrm_group Table Structure
242 -- *******************************************************/
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.';
247 -- /*******************************************************
249 -- * Insert data in to civicrm_individual_prefix civicrm_individual_suffix and civicrm_gender
251 -- *******************************************************/
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);
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);
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);
268 -- /*******************************************************
270 -- * adding data for perefix, suffix and gender in the individual table
272 -- *******************************************************/
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';
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';
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';
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;
293 -- /*******************************************************
295 -- * also update custom_field and group with the default values
297 -- *******************************************************/
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';
302 UPDATE civicrm_group SET visibility = 'User and User Admin Only' WHERE 1;
304 -- /*******************************************************
306 -- * Drop old columns
308 -- *******************************************************/
310 ALTER TABLE civicrm_uf_field DROP listings_title;
313 ALTER TABLE civicrm_individual DROP prefix;
315 ALTER TABLE civicrm_individual DROP suffix;
317 ALTER TABLE civicrm_individual DROP gender;
321 -- /*******************************************************
325 -- *******************************************************/
327 DROP TABLE IF EXISTS civicrm_import_mapping_field;
329 DROP TABLE IF EXISTS civicrm_import_mapping;