crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/sql/civicrm_upgradedb_v1.1_v1.2_40.mysql

   1 -- +--------------------------------------------------------------------+
   2 -- | CiviCRM version 4.6                                                |
   3 -- +--------------------------------------------------------------------+
   4 -- | Copyright CiviCRM LLC (c) 2004-2015                                |
   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;