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 TYPE=InnoDB;
41 ALTER TABLE civicrm_location_type TYPE=InnoDB;
42 ALTER TABLE civicrm_relationship_type TYPE=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(
66 , INDEX FKEY_domain_id ( domain_id ) ,
67 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
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(
93 , INDEX FKEY_domain_id ( domain_id ) ,
94 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
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(
119 , INDEX FKEY_domain_id ( domain_id ) ,
120 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
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'
147 , INDEX FKEY_mapping_id ( mapping_id ) ,
148 FOREIGN KEY (mapping_id) REFERENCES civicrm_mapping(id)
149 , INDEX FKEY_location_type_id ( location_type_id ) ,
150 FOREIGN KEY (location_type_id) REFERENCES civicrm_location_type(id)
151 , INDEX FKEY_relationship_type_id ( relationship_type_id ) ,
152 FOREIGN KEY (relationship_type_id) REFERENCES civicrm_relationship_type(id)
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(
178 , INDEX FKEY_domain_id ( domain_id ) ,
179 FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
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 INDEX (prefix_id);
224 ALTER TABLE civicrm_individual ADD FOREIGN KEY (prefix_id) REFERENCES civicrm_individual_prefix(id);
226 ALTER TABLE civicrm_individual ADD INDEX (suffix_id);
227 ALTER TABLE civicrm_individual ADD FOREIGN KEY (suffix_id) REFERENCES civicrm_individual_suffix(id);
229 ALTER TABLE civicrm_individual ADD INDEX (gender_id);
230 ALTER TABLE civicrm_individual ADD FOREIGN KEY (gender_id) REFERENCES civicrm_gender(id);
233 -- /*******************************************************
235 -- * Modify the civicrm_custom_field Table Structure
237 -- *******************************************************/
239 ALTER TABLE civicrm_custom_field ADD options_per_line int unsigned DEFAULT 0 COMMENT 'number of options per line for checkbox and radio';
241 -- /*******************************************************
243 -- * Modify the civicrm_group Table Structure
245 -- *******************************************************/
248 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.';
250 -- /*******************************************************
252 -- * Insert data in to civicrm_individual_prefix civicrm_individual_suffix and civicrm_gender
254 -- *******************************************************/
257 INSERT INTO civicrm_individual_prefix (domain_id, name, weight, is_active) VALUES ( 1, 'Mrs', 1, 1);
258 INSERT INTO civicrm_individual_prefix (domain_id, name, weight, is_active) VALUES ( 1, 'Ms', 2, 1);
259 INSERT INTO civicrm_individual_prefix (domain_id, name, weight, is_active) VALUES ( 1, 'Mr', 3, 1);
260 INSERT INTO civicrm_individual_prefix (domain_id, name, weight, is_active) VALUES ( 1, 'Dr', 4, 1);
262 INSERT INTO civicrm_individual_suffix (domain_id, name, weight, is_active) VALUES ( 1, 'Jr', 1, 1);
263 INSERT INTO civicrm_individual_suffix (domain_id, name, weight, is_active) VALUES ( 1, 'Sr', 2, 1);
264 INSERT INTO civicrm_individual_suffix (domain_id, name, weight, is_active) VALUES ( 1, 'II', 3, 1);
266 INSERT INTO civicrm_gender (domain_id, name, weight, is_active) VALUES ( 1, 'Female', 1, 1);
267 INSERT INTO civicrm_gender (domain_id, name, weight, is_active) VALUES ( 1, 'Male', 2, 1);
268 INSERT INTO civicrm_gender (domain_id, name, weight, is_active) VALUES ( 1, 'Transgender', 3, 1);
271 -- /*******************************************************
273 -- * adding data for perefix, suffix and gender in the individual table
275 -- *******************************************************/
277 UPDATE civicrm_individual SET prefix_id=1 WHERE prefix='Mrs';
278 UPDATE civicrm_individual SET prefix_id=2 WHERE prefix='Ms';
279 UPDATE civicrm_individual SET prefix_id=3 WHERE prefix='Mr';
280 UPDATE civicrm_individual SET prefix_id=4 WHERE prefix='Dr';
282 UPDATE civicrm_individual SET suffix_id=1 WHERE suffix='Jr';
283 UPDATE civicrm_individual SET suffix_id=2 WHERE suffix='Sr';
284 UPDATE civicrm_individual SET suffix_id=3 WHERE suffix='II';
286 UPDATE civicrm_individual SET gender_id=1 WHERE gender='Female';
287 UPDATE civicrm_individual SET gender_id=2 WHERE gender='Male';
288 UPDATE civicrm_individual SET gender_id=3 WHERE gender='Transgender';
290 -- Also reset all the is_reserved fields for the generic types
291 UPDATE civicrm_relationship_type SET is_reserved=0;
292 UPDATE civicrm_location_type SET is_reserved=0;
293 UPDATE civicrm_im_provider SET is_reserved=0;
294 UPDATE civicrm_mobile_provider SET is_reserved=0;
296 -- /*******************************************************
298 -- * also update custom_field and group with the default values
300 -- *******************************************************/
303 UPDATE civicrm_custom_field SET options_per_line = 0;
304 UPDATE civicrm_custom_field SET options_per_line = 1 WHERE html_type = 'Radio' OR html_type = 'CheckBox';
306 UPDATE civicrm_group SET visibility = 'User and User Admin Only' WHERE 1;
308 -- /*******************************************************
310 -- * Drop old columns
312 -- *******************************************************/
314 ALTER TABLE civicrm_uf_field DROP listings_title;
317 ALTER TABLE civicrm_individual DROP prefix;
319 ALTER TABLE civicrm_individual DROP suffix;
321 ALTER TABLE civicrm_individual DROP gender;
325 -- /*******************************************************
329 -- *******************************************************/
331 DROP TABLE IF EXISTS civicrm_import_mapping_field;
333 DROP TABLE IF EXISTS civicrm_import_mapping;