crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/sql/civicrm_upgradedb_v1.7_v1.8_41.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 -- * Select DomainID
  27 -- *
  28 -- *******************************************************/
  29   SELECT @domain_id := id from civicrm_domain;
  30
  31 -- /*******************************************************
  32 -- *
  33 -- * civicrm_dedupe_rule_group
  34 -- *
  35 -- * Dedupe rule groups
  36 -- *
  37 -- *******************************************************/
  38 CREATE TABLE civicrm_dedupe_rule_group (
  39
  40      id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique dedupe rule group id',
  41      domain_id int unsigned NOT NULL COMMENT 'The id of the domain this rule group belongs to',
  42      contact_type enum('Individual', 'Organization', 'Household')    COMMENT 'The type of contacts this group applies to',
  43      threshold int NOT NULL COMMENT 'The weight threshold the sum of the rule weights has to cross to consider two contacts the same'
  44
  45 ,    PRIMARY KEY ( id )
  46
  47 ,    CONSTRAINT FK_civicrm_dedupe_rule_group_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
  48
  49 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
  50
  51 -- /*******************************************************
  52 -- *
  53 -- * civicrm_dedupe_rule
  54 -- *
  55 -- * Dedupe rules for use by rule groups
  56 -- *
  57 -- *******************************************************/
  58 CREATE TABLE civicrm_dedupe_rule (
  59
  60      id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique dedupe rule id',
  61      dedupe_rule_group_id int unsigned NOT NULL COMMENT 'The id of the rule group this rule belongs to',
  62      rule_table varchar(64) NOT NULL COMMENT 'The name of the table this rule is about',
  63      rule_field varchar(64) NOT NULL COMMENT 'The name of the field of the table referenced in rule_table',
  64      rule_length int unsigned COMMENT 'The lenght of the matching substring',
  65      rule_weight int NOT NULL COMMENT 'The weight of the rule'
  66
  67 ,    PRIMARY KEY ( id )
  68
  69 ,    CONSTRAINT FK_civicrm_dedupe_rule_dedupe_rule_group_id FOREIGN KEY (dedupe_rule_group_id) REFERENCES civicrm_dedupe_rule_group(id)
  70
  71 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
  72
  73 -- /*******************************************************
  74 -- *
  75 -- * civicrm_participant
  76 -- *
  77 -- *******************************************************/
  78   ALTER TABLE civicrm_participant
  79       ADD INDEX index_status_id (status_id),
  80       ADD INDEX index_role_id (role_id);
  81
  82 -- /*******************************************************
  83 -- *
  84 -- * civicrm_payment_processor
  85 -- *
  86 -- *******************************************************/
  87 CREATE TABLE civicrm_payment_processor (
  88
  89      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Payment Processor ID',
  90      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this payment processor.',
  91      name varchar(64) COMMENT 'Payment Processor Name.',
  92      description varchar(255) COMMENT 'Payment Processor Description.',
  93      payment_processor_type varchar(255) COMMENT 'Payment Processor Type.',
  94      is_active tinyint COMMENT 'Is this processor active?',
  95      is_default tinyint COMMENT 'Is this processor the default?',
  96      is_test tinyint COMMENT 'Is this processor for a test site?',
  97      user_name varchar(255),
  98      password varchar(255),
  99      signature varchar(255),
 100      url_site varchar(255),
 101      url_recur varchar(255),
 102      url_button varchar(255),
 103      subject varchar(255),
 104      class_name varchar(255),
 105      billing_mode int unsigned NOT NULL COMMENT 'Billing Mode',
 106      is_recur tinyint COMMENT 'Can process recurring contributions'
 107
 108 ,    PRIMARY KEY ( id )
 109
 110 ,    UNIQUE INDEX UI_name_test( name, is_test )
 111
 112 ,    CONSTRAINT FK_civicrm_payment_processor_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 113
 114 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 115
 116 -- /*******************************************************
 117 -- *
 118 -- * civicrm_payment_processor_type
 119 -- *
 120 -- *******************************************************/
 121 CREATE TABLE civicrm_payment_processor_type (
 122
 123      id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Payment Processor Type ID',
 124      domain_id int unsigned NOT NULL COMMENT 'Which Domain owns this payment processor.',
 125      name varchar(64) COMMENT 'Payment Processor Name.',
 126      title varchar(64) COMMENT 'Payment Processor Name.',
 127      description varchar(255) COMMENT 'Payment Processor Description.',
 128      is_active tinyint COMMENT 'Is this processor active?',
 129      is_default tinyint COMMENT 'Is this processor the default?',
 130      user_name_label varchar(255),
 131      password_label varchar(255),
 132      signature_label varchar(255),
 133      subject_label varchar(255),
 134      class_name varchar(255),
 135      url_site_default varchar(255),
 136      url_recur_default varchar(255),
 137      url_button_default varchar(255),
 138      url_site_test_default varchar(255),
 139      url_recur_test_default varchar(255),
 140      url_button_test_default varchar(255),
 141      billing_mode int unsigned NOT NULL COMMENT 'Billing Mode',
 142      is_recur tinyint COMMENT 'Can process recurring contributions'
 143 ,
 144     PRIMARY KEY ( id )
 145
 146 ,   UNIQUE INDEX UI_name( name )
 147
 148 ,   CONSTRAINT FK_civicrm_payment_processor_type_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 149
 150 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 151
 152
 153 -- /*******************************************************
 154 -- *
 155 -- * civicrm_preferences
 156 -- *
 157 -- * Define preferences for the site and users
 158 -- *
 159 -- *******************************************************/
 160 CREATE TABLE civicrm_preferences (
 161
 162
 163      id int unsigned NOT NULL AUTO_INCREMENT  ,
 164      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this contact',
 165      contact_id int unsigned    COMMENT 'FK to Contact ID',
 166      is_domain tinyint    COMMENT 'Is this the record for the domain setting?',
 167      location_count int unsigned    COMMENT 'Number of locations to be displayed on edit page?',
 168      contact_view_options varchar(128)    COMMENT 'What tabs are displayed in the contact summary',
 169      contact_edit_options varchar(128)    COMMENT 'What tabs are displayed in the contact edit',
 170      advanced_search_options varchar(128)    COMMENT 'What tabs are displayed in the advanced search screen',
 171      user_dashboard_options varchar(128)    COMMENT 'What tabs are displayed in the contact edit',
 172      address_options varchar(128)    COMMENT 'What fields are displayed from the address table',
 173      address_format text    COMMENT 'Format to display the address',
 174      mailing_format text    COMMENT 'Format to display a mailing label',
 175      individual_name_format text    COMMENT 'Format to display a individual name',
 176      address_standardization_provider varchar(64)    COMMENT 'object name of provider for address standarization',
 177      address_standardization_userid varchar(64)    COMMENT 'user id for provider login',
 178      address_standardization_url varchar(255)    COMMENT 'url of address standardization service'
 179 ,
 180     PRIMARY KEY ( id )
 181
 182     ,     INDEX index_contact_view_options(
 183         contact_view_options
 184   )
 185   ,     INDEX index_contact_edit_options(
 186         contact_edit_options
 187   )
 188   ,     INDEX index_advanced_search_options(
 189         advanced_search_options
 190   )
 191   ,     INDEX index_user_dashboard_options(
 192         user_dashboard_options
 193   )
 194   ,     INDEX index_address_options(
 195         address_options
 196   )
 197
 198 ,
 199      CONSTRAINT FK_civicrm_preferences_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 200 ,
 201      CONSTRAINT FK_civicrm_preferences_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
 202
 203 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 204
 205
 206 -- /*******************************************************
 207 -- *
 208 -- * civicrm_price_set
 209 -- *
 210 -- *******************************************************/
 211 CREATE TABLE civicrm_price_set (
 212
 213      id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Price Set',
 214      domain_id int unsigned NOT NULL COMMENT 'Which domain owns this price set',
 215      name varchar(64) NOT NULL  COMMENT 'Variable name/programmatic handle for this group',
 216      title varchar(64) NOT NULL COMMENT 'Friendly name',
 217      is_active tinyint DEFAULT 1 COMMENT 'Is this price set active',
 218      help_pre text COMMENT 'Description and/or help text to display before fields in form.',
 219      help_post text COMMENT 'Description and/or help text to display after fields in form.',
 220      javascript varchar(64) COMMENT 'Optional Javascript script function(s) included on the form with this price_set. Can be used for conditional'
 221
 222 ,    PRIMARY KEY ( id )
 223
 224 ,    UNIQUE INDEX UI_name( name )
 225
 226 ,    UNIQUE INDEX UI_title( title )
 227
 228 ,    CONSTRAINT FK_civicrm_price_set_domain_id FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 229
 230 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 231
 232 -- /*******************************************************
 233 -- *
 234 -- * civicrm_price_field
 235 -- *
 236 -- *******************************************************/
 237 CREATE TABLE civicrm_price_field (
 238
 239      id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Price Field',
 240      price_set_id int unsigned NOT NULL COMMENT 'FK to civicrm_price_set',
 241      name varchar(64) NOT NULL COMMENT 'Variable name/programmatic handle for this field',
 242      label varchar(64) NOT NULL COMMENT 'Text for form field label (also friendly name for administering this field)',
 243      html_type enum('Text', 'Select', 'Radio', 'CheckBox') NOT NULL   ,
 244      is_enter_qty tinyint DEFAULT 0 COMMENT 'Enter a quantity for this field?',
 245      help_pre text COMMENT 'Description and/or help text to display before this field.',
 246      help_post text COMMENT 'Description and/or help text to display after this field.',
 247      weight int DEFAULT 1 COMMENT 'Order in which the fields should appear',
 248      is_display_amounts tinyint DEFAULT 1 COMMENT 'Should the price be displayed next to the label for each option?',
 249      options_per_line int unsigned DEFAULT 1 COMMENT 'number of options per line for checkbox and radio',
 250      is_active tinyint DEFAULT 1 COMMENT 'Is this price field active',
 251      is_required tinyint DEFAULT 1 COMMENT 'Is this price field required (value must be > 1)',
 252      active_on datetime DEFAULT 0 COMMENT 'If non-zero, do not show this field before the date specified',
 253      expire_on datetime DEFAULT 0 COMMENT 'If non-zero, do not show this field after the date specified',
 254      javascript varchar(255) COMMENT 'Optional scripting attributes for field'
 255
 256 ,    PRIMARY KEY ( id )
 257
 258 ,    INDEX index_name( name )
 259
 260 ,    CONSTRAINT FK_civicrm_price_field_price_set_id FOREIGN KEY (price_set_id) REFERENCES civicrm_price_set(id)
 261
 262 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 263
 264
 265 -- /*******************************************************
 266 -- *
 267 -- * civicrm_price_set_entity
 268 -- *
 269 -- *******************************************************/
 270 CREATE TABLE civicrm_price_set_entity (
 271
 272      id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Price Set Entity',
 273      entity_table varchar(64) NOT NULL COMMENT 'Table which uses this price set',
 274      entity_id int unsigned NOT NULL COMMENT 'Item in table',
 275      price_set_id int unsigned NOT NULL COMMENT 'price set being used'
 276
 277 ,    PRIMARY KEY ( id )
 278
 279 ,    UNIQUE INDEX UI_entity( entity_table, entity_id )
 280
 281 ,    CONSTRAINT FK_civicrm_price_set_entity_price_set_id FOREIGN KEY (price_set_id) REFERENCES civicrm_price_set(id)
 282
 283 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 284
 285
 286 -- /*******************************************************
 287 -- *
 288 -- * civicrm_line_item
 289 -- *
 290 -- *******************************************************/
 291 CREATE TABLE civicrm_line_item (
 292
 293      id int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Line Item',
 294      entity_table varchar(64) NOT NULL COMMENT 'table which has the transaction',
 295      entity_id int unsigned NOT NULL COMMENT 'entry in table',
 296      price_field_id int unsigned NOT NULL COMMENT 'FK to price_field',
 297      custom_option_id int unsigned NOT NULL COMMENT 'FK to custom_option',
 298      label varchar(255) NOT NULL COMMENT 'descriptive label for item - from custom_option.label',
 299      qty int unsigned NOT NULL COMMENT 'How many items ordered',
 300      unit_price decimal(20,2) NOT NULL COMMENT 'price of each item',
 301      line_total decimal(20,2) NOT NULL COMMENT 'qty * unit_price'
 302
 303 ,    PRIMARY KEY ( id )
 304
 305 ,    CONSTRAINT FK_civicrm_line_item_price_field_id FOREIGN KEY (price_field_id) REFERENCES civicrm_price_field(id)
 306
 307 ,    CONSTRAINT FK_civicrm_line_item_custom_option_id FOREIGN KEY (custom_option_id) REFERENCES civicrm_custom_option(id)
 308
 309 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 310
 311
 312 -- /*******************************************************
 313 -- *
 314 -- * Modifying Tables
 315 -- *
 316 -- *******************************************************/
 317
 318 -- /*******************************************************
 319 -- *
 320 -- * civicrm_activity_history
 321 -- *
 322 -- *******************************************************/
 323   ALTER TABLE civicrm_activity_history
 324       ADD is_test tinyint(4) NULL DEFAULT '0';
 325
 326
 327 -- /*******************************************************
 328 -- *
 329 -- * civicrm_contribution_page
 330 -- *
 331 -- *******************************************************/
 332   ALTER TABLE civicrm_contribution_page
 333       ADD `start_date` datetime NULL DEFAULT NULL,
 334       ADD `end_date` datetime NULL DEFAULT NULL,
 335       ADD payment_processor_id int(10) unsigned NULL DEFAULT NULL,
 336       ADD CONSTRAINT FK_civicrm_contribution_page_payment_processor_id FOREIGN KEY (payment_processor_id) REFERENCES civicrm_payment_processor(id);
 337
 338
 339 -- /*******************************************************
 340 -- *
 341 -- * civicrm_custom_field
 342 -- *
 343 -- *******************************************************/
 344   ALTER TABLE civicrm_custom_field
 345       MODIFY data_type enum('String','Int','Float','Money','Memo','Date','Boolean','StateProvince','Country','File','Link') NULL DEFAULT NULL,
 346           MODIFY html_type enum('Text','TextArea','Select','Multi-Select','Radio','CheckBox','Select Date','Select State/Province','Select Country','File','Link') NULL DEFAULT NULL;
 347
 348
 349 -- /*******************************************************
 350 -- *
 351 -- * civicrm_event
 352 -- *
 353 -- *******************************************************/
 354   ALTER TABLE civicrm_event
 355       ADD registration_start_date datetime NULL DEFAULT NULL,
 356       ADD registration_end_date datetime NULL DEFAULT NULL,
 357       ADD fee_label varchar(255) NULL DEFAULT NULL,
 358       ADD is_show_location tinyint(4) NULL DEFAULT '1',
 359       ADD payment_processor_id int(10) unsigned NULL DEFAULT NULL AFTER contribution_type_id,
 360       ADD CONSTRAINT FK_civicrm_event_payment_processor_id FOREIGN KEY (payment_processor_id) REFERENCES civicrm_payment_processor(id),
 361           ADD INDEX index_event_type_id (event_type_id);
 362
 363
 364 -- /*******************************************************
 365 -- *
 366 -- * civicrm_household
 367 -- *
 368 -- *******************************************************/
 369   ALTER TABLE civicrm_household
 370       ADD INDEX index_household_name (household_name);
 371
 372 -- /*******************************************************
 373 -- *
 374 -- * civicrm_individual
 375 -- *
 376 -- *******************************************************/
 377   ALTER TABLE civicrm_individual
 378       ADD INDEX index_first_name (first_name),
 379       ADD INDEX index_last_name (last_name);
 380
 381 -- /*******************************************************
 382 -- *
 383 -- * civicrm_line_item
 384 -- *
 385 -- *******************************************************/
 386   ALTER TABLE civicrm_line_item
 387       ADD INDEX index_entity (entity_table, entity_id);
 388
 389
 390
 391 -- /*******************************************************
 392 -- *
 393 -- * civicrm_location
 394 -- *
 395 -- *******************************************************/
 396   ALTER TABLE civicrm_location
 397       MODIFY location_type_id int(10) unsigned NULL DEFAULT NULL;
 398
 399
 400 -- /*******************************************************
 401 -- *
 402 -- * civicrm_membership
 403 -- *
 404 -- *******************************************************/
 405   ALTER TABLE civicrm_membership
 406       ADD is_test tinyint(4) NULL DEFAULT '0';
 407
 408
 409 -- /*******************************************************
 410 -- *
 411 -- * civicrm_membership_type
 412 -- *
 413 -- *******************************************************/
 414   ALTER TABLE civicrm_membership_type ALTER fixed_period_start_day    DROP DEFAULT;
 415   ALTER TABLE civicrm_membership_type ALTER fixed_period_rollover_day DROP DEFAULT;
 416
 417
 418 -- /*******************************************************
 419 -- *
 420 -- * civicrm_option_group
 421 -- *
 422 -- *******************************************************/
 423   ALTER TABLE civicrm_option_group
 424           ADD label varchar(255) NULL DEFAULT NULL;
 425
 426 -- /*******************************************************
 427 -- *
 428 -- * civicrm_option_value
 429 -- *
 430 -- *******************************************************/
 431   ALTER TABLE civicrm_option_value
 432              MODIFY filter int(10) unsigned DEFAULT NULL;
 433
 434 -- /*******************************************************
 435 -- *
 436 -- * civicrm_organization
 437 -- *
 438 -- *******************************************************/
 439   ALTER TABLE civicrm_organization
 440       ADD INDEX index_organization_name (organization_name);
 441
 442
 443
 444 -- /*******************************************************
 445 -- *
 446 -- * Insert data into civicrm_payment_processor_type
 447 -- *
 448 -- *******************************************************/
 449
 450   INSERT INTO civicrm_payment_processor_type
 451       (domain_id, name, title, description, is_active, is_default, user_name_label, password_label, signature_label, subject_label, class_name, url_site_default, url_recur_default, url_button_default, url_site_test_default, url_recur_test_default, url_button_test_default, billing_mode, is_recur )
 452   VALUES
 453       (@domain_id,'Dummy','Dummy Payment Processor',NULL,1,1,'User Name',NULL,NULL,NULL,'Payment_Dummy',NULL,NULL,NULL,NULL,NULL,NULL,1,NULL),
 454       (@domain_id,'PayPal_Standard','PayPal - Website Payments Standard',NULL,1,0,'Merchant Account Email',NULL,NULL,NULL,'Payment_PayPalImpl','https://www.paypal.com/','https://www.paypal.com/',NULL,'https://www.sandbox.paypal.com/','https://www.sandbox.paypal.com/',NULL,4,1),
 455       (@domain_id,'PayPal','PayPal - Website Payments Pro',NULL,1,0,'User Name','Password','Signature',NULL,'Payment_PayPalImpl','https://www.paypal.com/',NULL,'https://www.paypal.com/en_US/i/btn/btn_xpressCheckout.gif','https://www.sandbox.paypal.com/',NULL,'https://www.paypal.com/en_US/i/btn/btn_xpressCheckout.gif',3,NULL),
 456       (@domain_id,'PayPal_Express','PayPal - Express',NULL,1,0,'User Name','Password','Signature',NULL,'Payment_PayPalImpl','https://www.paypal.com/',NULL,'https://www.paypal.com/en_US/i/btn/btn_xpressCheckout.gif','https://www.sandbox.paypal.com/',NULL,'https://www.paypal.com/en_US/i/btn/btn_xpressCheckout.gif',3,NULL),
 457       (@domain_id,'Google_Checkout','Google Checkout',NULL,1,0,'Merchant ID','Key',NULL,NULL,'Payment_Google','https://checkout.google.com/',NULL,'http://checkout.google.com/buttons/checkout.gif','https://sandbox.google.com/checkout',NULL,'http://sandbox.google.com/checkout/buttons/checkout.gif',4,NULL),
 458       (@domain_id,'Moneris','Moneris',NULL,1,0,'User Name','Password','Store ID',NULL,'Payment_Moneris','https://www3.moneris.com/',NULL,NULL,'https://esqa.moneris.com/',NULL,NULL,1,1),
 459       (@domain_id,'AuthNet_AIM','Authorize.Net - AIM',NULL,1,0,'API Login','Payment Key','MD5 Hash',NULL,'Payment_AuthorizeNet','https://secure.authorize.net/gateway/transact.dll','https://api.authorize.net/xml/v1/request.api',NULL,'https://secure.authorize.net/gateway/transact.dll','https://apitest.authorize.net/xml/v1/request.api',NULL,1,1);
 460
 461
 462 -- /*******************************************************
 463 -- *
 464 -- * civicrm_uf_group
 465 -- *
 466 -- *******************************************************/
 467   ALTER TABLE civicrm_uf_group
 468         ADD is_cms_user tinyint(4) NULL DEFAULT '0',
 469       ADD notify varchar(255) NULL DEFAULT NULL COMMENT 'If you want member(s) of your organization to receive a notification email whenever this Profile form is used to enter or update contact information, enter one or more email addresses here separated by a comma';
 470
 471
 472 -- /*******************************************************
 473 -- *
 474 -- * civicrm_option_group and civicrm_option_value
 475 -- *
 476 -- *******************************************************/
 477
 478   INSERT INTO
 479     civicrm_option_group (domain_id, name, description, is_reserved, is_active)
 480   VALUES
 481     (@domain_id, 'contact_view_options', 'Contact View Options', 0, 1),
 482     (@domain_id, 'contact_edit_options', 'Contact Edit Options', 0, 1),
 483      (@domain_id, 'advanced_search_options', 'Advanced Search Options', 0, 1),
 484     (@domain_id, 'user_dashboard_options', 'User Dashboard Options', 0, 1),
 485     (@domain_id, 'address_options', 'Addressing Options', 0, 1);
 486
 487   SELECT @option_group_id_cvOpt := max(id) from civicrm_option_group where name = 'contact_view_options';
 488   SELECT @option_group_id_ceOpt := max(id) from civicrm_option_group where name = 'contact_edit_options';
 489   SELECT @option_group_id_asOpt := max(id) from civicrm_option_group where name = 'advanced_search_options';
 490   SELECT @option_group_id_udOpt := max(id) from civicrm_option_group where name = 'user_dashboard_options';
 491   SELECT @option_group_id_adOpt := max(id) from civicrm_option_group where name = 'address_options';
 492
 493   INSERT INTO
 494      civicrm_option_value (option_group_id, label, value, name, grouping, filter, is_default, weight, description, is_optgroup, is_reserved, is_active)
 495   VALUES
 496     (@option_group_id_cvOpt, 'Activities'   ,   1, NULL, NULL, 0, NULL,  1, NULL, 0, 0, 1 ),
 497     (@option_group_id_cvOpt, 'Relationships',   2, NULL, NULL, 0, NULL,  2, NULL, 0, 0, 1 ),
 498     (@option_group_id_cvOpt, 'Groups'       ,   3, NULL, NULL, 0, NULL,  3, NULL, 0, 0, 1 ),
 499     (@option_group_id_cvOpt, 'Notes'        ,   4, NULL, NULL, 0, NULL,  4, NULL, 0, 0, 1 ),
 500     (@option_group_id_cvOpt, 'Tags'         ,   5, NULL, NULL, 0, NULL,  5, NULL, 0, 0, 1 ),
 501     (@option_group_id_cvOpt, 'Change Log'   ,   6, NULL, NULL, 0, NULL,  6, NULL, 0, 0, 1 ),
 502     (@option_group_id_cvOpt, 'Contributions',   7, NULL, NULL, 0, NULL,  7, NULL, 0, 0, 1 ),
 503     (@option_group_id_cvOpt, 'Memberships'  ,   8, NULL, NULL, 0, NULL,  8, NULL, 0, 0, 1 ),
 504     (@option_group_id_cvOpt, 'Events'       ,   9, NULL, NULL, 0, NULL,  9, NULL, 0, 0, 1 ),
 505
 506     (@option_group_id_ceOpt, 'Communication Preferences',   1, NULL, NULL, 0, NULL, 1, NULL, 0, 0, 1 ),
 507     (@option_group_id_ceOpt, 'Demographics'             ,   2, NULL, NULL, 0, NULL, 2, NULL, 0, 0, 1 ),
 508     (@option_group_id_ceOpt, 'Tags and Groups'          ,   3, NULL, NULL, 0, NULL, 3, NULL, 0, 0, 1 ),
 509     (@option_group_id_ceOpt, 'Notes'                    ,   4, NULL, NULL, 0, NULL, 4, NULL, 0, 0, 1 ),
 510
 511     (@option_group_id_asOpt, 'Address Fields'      ,   1, NULL, NULL, 0, NULL,  1, NULL, 0, 0, 1 ),
 512     (@option_group_id_asOpt, 'Custom Fields'       ,   2, NULL, NULL, 0, NULL,  2, NULL, 0, 0, 1 ),
 513     (@option_group_id_asOpt, 'Activity History'    ,   3, NULL, NULL, 0, NULL,  3, NULL, 0, 0, 1 ),
 514     (@option_group_id_asOpt, 'Scheduled Activities',   4, NULL, NULL, 0, NULL,  4, NULL, 0, 0, 1 ),
 515     (@option_group_id_asOpt, 'Relationships'       ,   5, NULL, NULL, 0, NULL,  5, NULL, 0, 0, 1 ),
 516     (@option_group_id_asOpt, 'Notes'               ,   6, NULL, NULL, 0, NULL,  6, NULL, 0, 0, 1 ),
 517     (@option_group_id_asOpt, 'Change Log'          ,   7, NULL, NULL, 0, NULL,  7, NULL, 0, 0, 1 ),
 518     (@option_group_id_asOpt, 'Contributions'       ,   8, NULL, NULL, 0, NULL,  8, NULL, 0, 0, 1 ),
 519     (@option_group_id_asOpt, 'Memberships'         ,   9, NULL, NULL, 0, NULL,  9, NULL, 0, 0, 1 ),
 520     (@option_group_id_asOpt, 'Events'              ,  10, NULL, NULL, 0, NULL, 10, NULL, 0, 0, 1 ),
 521
 522     (@option_group_id_udOpt, 'Groups'       , 1, NULL, NULL, 0, NULL, 1, NULL, 0, 0, 1 ),
 523     (@option_group_id_udOpt, 'Contributions', 2, NULL, NULL, 0, NULL, 2, NULL, 0, 0, 1 ),
 524     (@option_group_id_udOpt, 'Memberships'  , 3, NULL, NULL, 0, NULL, 3, NULL, 0, 0, 1 ),
 525     (@option_group_id_udOpt, 'Events'       , 4, NULL, NULL, 0, NULL, 4, NULL, 0, 0, 1 ),
 526
 527     (@option_group_id_adOpt, 'Street Address'   ,  1, NULL, NULL, 0, NULL,  1, NULL, 0, 0, 1 ),
 528     (@option_group_id_adOpt, 'Addt\'l Address 1' , 2, NULL, NULL, 0, NULL,  2, NULL, 0, 0, 1 ),
 529     (@option_group_id_adOpt, 'Addt\'l Address 2' , 3, NULL, NULL, 0, NULL,  3, NULL, 0, 0, 1 ),
 530     (@option_group_id_adOpt, 'City'             ,  4, NULL, NULL, 0, NULL,  4, NULL, 0, 0, 1 ),
 531     (@option_group_id_adOpt, 'Zip / Postal Code',  5, NULL, NULL, 0, NULL,  5, NULL, 0, 0, 1 ),
 532     (@option_group_id_adOpt, 'Postal Code Suffix', 6, NULL, NULL, 0, NULL,  6, NULL, 0, 0, 1 ),
 533     (@option_group_id_adOpt, 'County'           ,  7, NULL, NULL, 0, NULL,  7, NULL, 0, 0, 1 ),
 534     (@option_group_id_adOpt, 'State / Province' ,  8, NULL, NULL, 0, NULL,  8, NULL, 0, 0, 1 ),
 535     (@option_group_id_adOpt, 'Country'          ,  9, NULL, NULL, 0, NULL,  9, NULL, 0, 0, 1 ),
 536     (@option_group_id_adOpt, 'Latitude'         , 10, NULL, NULL, 0, NULL, 10, NULL, 0, 0, 1 ),
 537     (@option_group_id_adOpt, 'Longitude'        , 11, NULL, NULL, 0, NULL, 11, NULL, 0, 0, 1 );
 538
 539
 540 -- /*******************************************************
 541 -- *
 542 -- * civicrm_preferences
 543 -- *
 544 -- *******************************************************/
 545         INSERT INTO
 546           civicrm_preferences(domain_id, contact_id, is_domain, location_count, contact_view_options, contact_edit_options, advanced_search_options, user_dashboard_options, address_options, address_format, mailing_format, individual_name_format, address_standardization_provider, address_standardization_userid, address_standardization_url )
 547         VALUES
 548           (@domain_id,NULL,1,1,'\ 11\ 12\ 13\ 14\ 15\ 16\ 17\ 18\ 19\ 1','\ 11\ 12\ 13\ 14\ 1','\ 11\ 12\ 13\ 14\ 15\ 16\ 17\ 18\ 19\ 110\ 1','\ 11\ 12\ 13\ 14\ 1','\ 11\ 12\ 13\ 14\ 15\ 16\ 18\ 19\ 110\ 111\ 1','{street_address}\n{supplemental_address_1}\n{supplemental_address_2}\n{city}{, }{state_province}{ }{postal_code}\n{country}','{street_address}\n{supplemental_address_1}\n{supplemental_address_2}\n{city}{, }{state_province}{ }{postal_code}\n{country}','{individual_prefix}{ } {first_name}{ }{middle_name}{ }{last_name}{ }{individual_suffix}',NULL,NULL,NULL);
 549
 550 -- /*******************************************************
 551 -- *
 552 -- * CRM-2064
 553 -- *
 554 -- *******************************************************/
 555 UPDATE civicrm_location_type SET name = 'Billing' WHERE name IN ('Facturering', 'Facturation', 'Faturamento');
 556
 557
 558 -- /*******************************************************
 559 -- *
 560 -- * dupe rule defaults
 561 -- *
 562 -- *******************************************************/
 563
 564 INSERT INTO civicrm_dedupe_rule_group (domain_id, contact_type, threshold) VALUES (@domain_id, 'Individual', 20);
 565
 566 SELECT @dedupe_rule_group_id := MAX(id) FROM civicrm_dedupe_rule_group;
 567
 568 INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
 569  VALUES
 570  (@dedupe_rule_group_id, 'civicrm_individual', 'first_name', 5),
 571  (@dedupe_rule_group_id, 'civicrm_individual', 'last_name',  7),
 572  (@dedupe_rule_group_id, 'civicrm_email',      'email',     10);
 573
 574 INSERT INTO civicrm_dedupe_rule_group (domain_id, contact_type, threshold) VALUES (@domain_id, 'Organization', 10);
 575
 576 SELECT @dedupe_rule_group_id := MAX(id) FROM civicrm_dedupe_rule_group;
 577
 578 INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
 579 VALUES
 580   (@dedupe_rule_group_id, 'civicrm_organization', 'organization_name', 5),
 581   (@dedupe_rule_group_id, 'civicrm_email',        'email',             5);
 582
 583 INSERT INTO civicrm_dedupe_rule_group (domain_id, contact_type, threshold) VALUES (@domain_id, 'Household', 10);
 584
 585 SELECT @dedupe_rule_group_id := MAX(id) FROM civicrm_dedupe_rule_group;
 586
 587 INSERT INTO civicrm_dedupe_rule (dedupe_rule_group_id, rule_table, rule_field, rule_weight)
 588 VALUES
 589   (@dedupe_rule_group_id, 'civicrm_household', 'household_name', 5),
 590   (@dedupe_rule_group_id, 'civicrm_email',     'email',          5);
 591
 592 -- /*******************************************************
 593 -- *
 594 -- * CRM-2125
 595 -- *
 596 -- *******************************************************/
 597 INSERT IGNORE INTO civicrm_state_province (id,country_id, abbreviation, name) VALUES
 598 (5177, 1098, "CW", "Central and Western"),
 599 (5178, 1098, "EA", "Eastern"),
 600 (5179, 1098, "SO", "Southern"),
 601 (5180, 1098, "WC", "Wan Chai"),
 602 (5181, 1098, "KC", "Kowloon City"),
 603 (5182, 1098, "KU", "Kwun Tong"),
 604 (5183, 1098, "SS", "Sham Shui Po"),
 605 (5184, 1098, "WT", "Wong Tai Sin"),
 606 (5185, 1098, "YT", "Yau Tsim Mong"),
 607 (5186, 1098, "IS", "Islands"),
 608 (5187, 1098, "KI", "Kwai Tsing"),
 609 (5188, 1098, "NO", "North"),
 610 (5189, 1098, "SK", "Sai Kung"),
 611 (5190, 1098, "ST", "Sha Tin"),
 612 (5191, 1098, "TP", "Tai Po"),
 613 (5192, 1098, "TW", "Tsuen Wan"),
 614 (5193, 1098, "TM", "Tuen Mun"),
 615 (5194, 1098, "YL", "Yuen Long");
 616
 617 -- /*******************************************************
 618 -- *
 619 -- * CRM-2130
 620 -- *
 621 -- *******************************************************/
 622 INSERT IGNORE INTO civicrm_country (id, name, iso_code) VALUES ("1242", "Serbia", "RS");
 623 INSERT IGNORE INTO civicrm_country (id, name, iso_code) VALUES ("1243", "Montenegro", "ME");
 624
 625 -- /*******************************************************
 626 -- *
 627 -- * CRM-2131
 628 -- *
 629 -- *******************************************************/
 630 INSERT IGNORE INTO civicrm_country (id, name, iso_code) VALUES ("1244", "Jersey", "JE");
 631 INSERT IGNORE INTO civicrm_country (id, name, iso_code) VALUES ("1245", "Guernsey", "GG");
 632 INSERT IGNORE INTO civicrm_country (id, name, iso_code) VALUES ("1246", "Isle of Man", "IM");