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 | -- * | |
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 TYPE=InnoDB; | |
41 | ALTER TABLE civicrm_location_type TYPE=InnoDB; | |
42 | ALTER TABLE civicrm_relationship_type TYPE=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 | , INDEX FKEY_domain_id ( domain_id ) , | |
67 | FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) | |
68 | ||
69 | ) TYPE=InnoDB ; | |
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 | , INDEX FKEY_domain_id ( domain_id ) , | |
94 | FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) | |
95 | ||
96 | ) TYPE=InnoDB ; | |
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 | , INDEX FKEY_domain_id ( domain_id ) , | |
120 | FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) | |
121 | ||
122 | ) TYPE=InnoDB ; | |
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 | , 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) | |
153 | ||
154 | ) TYPE=InnoDB ; | |
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 | , INDEX FKEY_domain_id ( domain_id ) , | |
179 | FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id) | |
180 | ||
181 | ) TYPE=InnoDB ; | |
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 INDEX (prefix_id); | |
224 | ALTER TABLE civicrm_individual ADD FOREIGN KEY (prefix_id) REFERENCES civicrm_individual_prefix(id); | |
225 | ||
226 | ALTER TABLE civicrm_individual ADD INDEX (suffix_id); | |
227 | ALTER TABLE civicrm_individual ADD FOREIGN KEY (suffix_id) REFERENCES civicrm_individual_suffix(id); | |
228 | ||
229 | ALTER TABLE civicrm_individual ADD INDEX (gender_id); | |
230 | ALTER TABLE civicrm_individual ADD FOREIGN KEY (gender_id) REFERENCES civicrm_gender(id); | |
231 | ||
232 | ||
233 | -- /******************************************************* | |
234 | -- * | |
235 | -- * Modify the civicrm_custom_field Table Structure | |
236 | -- * | |
237 | -- *******************************************************/ | |
238 | ||
239 | ALTER TABLE civicrm_custom_field ADD options_per_line int unsigned DEFAULT 0 COMMENT 'number of options per line for checkbox and radio'; | |
240 | ||
241 | -- /******************************************************* | |
242 | -- * | |
243 | -- * Modify the civicrm_group Table Structure | |
244 | -- * | |
245 | -- *******************************************************/ | |
246 | ||
247 | ||
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.'; | |
249 | ||
250 | -- /******************************************************* | |
251 | -- * | |
252 | -- * Insert data in to civicrm_individual_prefix civicrm_individual_suffix and civicrm_gender | |
253 | -- * | |
254 | -- *******************************************************/ | |
255 | ||
256 | ||
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); | |
261 | ||
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); | |
265 | ||
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); | |
269 | ||
270 | ||
271 | -- /******************************************************* | |
272 | -- * | |
273 | -- * adding data for perefix, suffix and gender in the individual table | |
274 | -- * | |
275 | -- *******************************************************/ | |
276 | ||
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'; | |
281 | ||
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'; | |
285 | ||
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'; | |
289 | ||
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; | |
295 | ||
296 | -- /******************************************************* | |
297 | -- * | |
298 | -- * also update custom_field and group with the default values | |
299 | -- * | |
300 | -- *******************************************************/ | |
301 | ||
302 | ||
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'; | |
305 | ||
306 | UPDATE civicrm_group SET visibility = 'User and User Admin Only' WHERE 1; | |
307 | ||
308 | -- /******************************************************* | |
309 | -- * | |
310 | -- * Drop old columns | |
311 | -- * | |
312 | -- *******************************************************/ | |
313 | ||
314 | ALTER TABLE civicrm_uf_field DROP listings_title; | |
315 | ||
316 | ||
317 | ALTER TABLE civicrm_individual DROP prefix; | |
318 | ||
319 | ALTER TABLE civicrm_individual DROP suffix; | |
320 | ||
321 | ALTER TABLE civicrm_individual DROP gender; | |
322 | ||
323 | ||
324 | ||
325 | -- /******************************************************* | |
326 | -- * | |
327 | -- * Drop Old Tables | |
328 | -- * | |
329 | -- *******************************************************/ | |
330 | ||
331 | DROP TABLE IF EXISTS civicrm_import_mapping_field; | |
332 | ||
333 | DROP TABLE IF EXISTS civicrm_import_mapping; |