crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/sql/civicrm_upgradedb_v1.2_v1.3_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 -- *
  27 -- * adding of new tables
  28 -- *
  29 -- *******************************************************/
  30
  31
  32 -- /*******************************************************
  33 -- *
  34 -- * civicrm_accept_credit_card
  35 -- *
  36 -- *******************************************************/
  37 CREATE TABLE civicrm_accept_credit_card (
  38
  39
  40      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT ' Accept Credit Card ID',
  41      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this credit card.',
  42      name varchar(64)    COMMENT ' Credit Card Type as defined by the payment processor.',
  43      title varchar(64)    COMMENT 'Descriptive Credit Card Name.',
  44      is_reserved tinyint    COMMENT 'Is this a predefined system object?',
  45      is_active tinyint    COMMENT 'Is this property active?'
  46 ,
  47     PRIMARY KEY ( id )
  48
  49
  50 ,
  51      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
  52
  53 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
  54
  55
  56
  57
  58 -- /*******************************************************
  59 -- *
  60 -- * civicrm_contribtion_type
  61 -- *
  62 -- *******************************************************/
  63
  64 CREATE TABLE civicrm_contribution_type (
  65
  66
  67      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution Type ID',
  68      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this contribution type.',
  69      name varchar(64)    COMMENT 'Contribution Type Name.',
  70      accounting_code varchar(64)    COMMENT 'Optional value for mapping contributions to accounting system codes for each type/category of contribution.',
  71      description varchar(255)    COMMENT 'Contribution Type Description.',
  72      is_deductible tinyint   DEFAULT 1 COMMENT 'Is this contribution type tax-deductible? If true, contributions of this type may be fully OR partially deductible - non-deductible amount is stored in the Contribution record.',
  73      is_reserved tinyint    COMMENT 'Is this a predefined system object?',
  74      is_active tinyint    COMMENT 'Is this property active?'
  75 ,
  76     PRIMARY KEY ( id )
  77
  78     ,     UNIQUE INDEX UI_name_domain_id(
  79         name
  80       , domain_id
  81   )
  82
  83 ,
  84      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
  85
  86 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
  87
  88 -- /*******************************************************
  89 -- *
  90 -- * civicrm_payment_instrument
  91 -- *
  92 -- *
  93 -- *
  94 -- *******************************************************/
  95
  96 CREATE TABLE civicrm_payment_instrument (
  97
  98
  99      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT ' Payment Instrument ID',
 100      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this  payment instrument.',
 101      name varchar(64)    COMMENT ' Payment Instrument Name.',
 102      description varchar(255)    COMMENT ' Payment Instrument Description.',
 103      is_reserved tinyint    COMMENT 'Is this a predefined system object?',
 104      is_active tinyint    COMMENT 'Is this property active?'
 105 ,
 106     PRIMARY KEY ( id )
 107
 108     ,     UNIQUE INDEX UI_name_domain_id(
 109         name
 110       , domain_id
 111   )
 112
 113 ,
 114      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 115
 116 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 117
 118
 119 -- /*******************************************************
 120 -- *
 121 -- * civicrm_contribution
 122 -- *
 123 -- *
 124 -- *
 125 -- *******************************************************/
 126
 127 CREATE TABLE civicrm_contribution (
 128
 129      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution ID',
 130      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this contribution class.',
 131      contact_id int unsigned NOT NULL   COMMENT 'FK to Contact ID',
 132      contribution_type_id int unsigned    COMMENT 'FK to Contribution Type',
 133      payment_instrument_id int unsigned    COMMENT 'FK to Payment Instrument',
 134      receive_date datetime NOT NULL   COMMENT 'when was gift received',
 135      non_deductible_amount decimal(20,2)   DEFAULT 0 COMMENT 'Portion of total amount which is NOT tax deductible. Equal to total_amount for non-deductible contribution types.',
 136      total_amount decimal(20,2) NOT NULL   COMMENT 'Total amount of this contribution. Use market value for non-monetary gifts.',
 137      fee_amount decimal(20,2) COMMENT 'actual processor fee if known - may be 0.',
 138      net_amount decimal(20,2) COMMENT 'actual funds transfer amount; total less fees; if processor does not report actual fee during transaction, this is set to total_amount.',
 139      trxn_id varchar(255)    COMMENT 'unique transaction id; may be processor id, bank id + trans id, or account number + check number... depending on payment_method',
 140      invoice_id varchar(255)    COMMENT 'unique invoice id, system generated or passed in',
 141      currency varchar(64) NOT NULL   COMMENT '3 character string, value derived from payment processor config setting.',
 142      cancel_date datetime    COMMENT 'when was gift cancelled',
 143      cancel_reason text    ,
 144      receipt_date datetime    COMMENT 'when (if) receipt was sent; populated automatically for online donations w/ automatic receipting',
 145      thankyou_date datetime    COMMENT 'when (if) was donor thanked',
 146      source varchar(255)    COMMENT 'Origin of this Contribution.'
 147 ,
 148     PRIMARY KEY ( id )
 149
 150     ,     UNIQUE INDEX UI_contrib_trxn_id_domain_id(
 151         trxn_id
 152       , domain_id
 153   )
 154     ,     UNIQUE INDEX UI_contrib_invoice_id_domain_id(
 155         invoice_id
 156       , domain_id
 157   )
 158
 159
 160 ,
 161      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 162 ,
 163      FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
 164 ,
 165      FOREIGN KEY (contribution_type_id) REFERENCES civicrm_contribution_type(id)
 166 ,
 167      FOREIGN KEY (payment_instrument_id) REFERENCES civicrm_payment_instrument(id)
 168
 169 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 170
 171
 172
 173 -- /*******************************************************
 174 -- *
 175 -- * civicrm_contribution_page
 176 -- *
 177 -- *******************************************************/
 178    CREATE TABLE civicrm_contribution_page (
 179
 180
 181      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution Id',
 182      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this page',
 183      title varchar(255)    COMMENT 'Contribution Page title. For top of page display',
 184      intro_text text    COMMENT 'Text and html allowed. Displayed below title.',
 185      contribution_type_id int unsigned NOT NULL   COMMENT 'default Contribution type assigned to contributions submitted via this page, e.g. Contribution, Campaign Contribution',
 186      is_credit_card_only tinyint   DEFAULT 0 COMMENT 'if true - processing logic must reject transaction at confirmation stage if pay method != credit card',
 187      is_allow_other_amount tinyint   DEFAULT 0 COMMENT 'if true, page will include an input text field where user can enter their own amount',
 188      default_amount decimal(20,2)    COMMENT 'the default amount allowed.',
 189      min_amount decimal(20,2)    COMMENT 'if other amounts allowed, user can configure minimum allowed.',
 190      max_amount decimal(20,2)    COMMENT 'if other amounts allowed, user can configure maximum allowed.',
 191      thankyou_title varchar(255)    COMMENT 'Title for Thank-you page (header title tag, and display at the top of the page).',
 192      thankyou_text text    COMMENT 'text and html allowed; displayed above result on success page',
 193      thankyou_footer text    COMMENT 'Text and html allowed; displayed at the bottom of the success page. Common usage is to include link(s) to other pages such as tell-a-friend, etc.',
 194      is_email_receipt tinyint   DEFAULT 1 COMMENT 'if true, receipt is automatically emailed to contact on success',
 195      receipt_from_name varchar(255)    COMMENT 'FROM email name used for receipts generated by contributions to this contribution page.',
 196      receipt_from_email varchar(255)    COMMENT 'FROM email address used for receipts generated by contributions to this contribution page.',
 197      cc_receipt varchar(255)    COMMENT 'comma-separated list of email addresses to cc each time a receipt is sent',
 198      bcc_receipt varchar(255)    COMMENT 'comma-separated list of email addresses to bcc each time a receipt is sent',
 199      receipt_text text    COMMENT 'text to include above standard receipt info on receipt email; emails are text-only, so do not allow html for now',
 200      is_active tinyint    COMMENT 'Is this property active?'
 201 ,
 202     PRIMARY KEY ( id )
 203
 204
 205 ,
 206      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 207 ,
 208      FOREIGN KEY (contribution_type_id) REFERENCES civicrm_contribution_type(id)
 209
 210 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 211
 212
 213
 214
 215
 216 -- /*******************************************************
 217 -- *
 218 -- * civicrm_dupe_match
 219 -- *
 220 -- *
 221 -- *
 222 -- *******************************************************/
 223   CREATE TABLE civicrm_dupe_match (
 224
 225
 226      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique DupeMatch ID',
 227      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this contact',
 228      entity_table varchar(64) NOT NULL   COMMENT 'Name Of Entity Table',
 229      rule varchar(255) NOT NULL   COMMENT 'String that can Contains valid civicrm core or custom field name,parenthesis,,AND,OR '
 230 ,
 231     PRIMARY KEY ( id )
 232
 233
 234 ,
 235      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 236
 237 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 238
 239
 240
 241 -- /*******************************************************
 242 -- *
 243 -- * civicrm_financial_trxn
 244 -- *
 245 -- *
 246 -- *
 247 -- *******************************************************/
 248
 249   CREATE TABLE civicrm_financial_trxn (
 250
 251
 252      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Gift ID',
 253      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this gift class.',
 254      entity_table varchar(64)    COMMENT 'physical tablename for entity being extended by this data, e.g. civicrm_contact',
 255      entity_id int unsigned NOT NULL   COMMENT 'FK to record in the entity table specified by entity_table column.',
 256      trxn_date datetime NOT NULL   ,
 257      trxn_type enum('Debit', 'Credit') NOT NULL   ,
 258      total_amount decimal(20,2) NOT NULL   COMMENT 'amount of transaction',
 259      fee_amount decimal(20,2) COMMENT 'actual processor fee if known - may be 0.',
 260      net_amount decimal(20,2) COMMENT 'actual funds transfer amount; total less fees; if processor does not report actual fee during transaction, this is set to total_amount.',
 261      currency varchar(64) NOT NULL   COMMENT '3 character string, value derived from payment processor config setting.',
 262      payment_processor varchar(64) NOT NULL   COMMENT 'derived from Processor setting in civicrm.settings.php.',
 263      trxn_id varchar(255) NOT NULL   COMMENT 'unique processor transaction id, bank id + trans id,... depending on payment_method',
 264      trxn_result_code varchar(255)    COMMENT 'processor result code'
 265 ,
 266     PRIMARY KEY ( id )
 267
 268     ,     INDEX index_entity(
 269         entity_table
 270       , entity_id
 271   )
 272   ,     UNIQUE INDEX UI_ft_trxn_id_domain_id(
 273         trxn_id
 274       , domain_id
 275   )
 276
 277 ,
 278      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 279
 280 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 281
 282
 283 -- /*******************************************************
 284 -- *
 285 -- * civicrm_module_profile
 286 -- *
 287 -- *
 288 -- *
 289 -- *******************************************************/
 290
 291   CREATE TABLE civicrm_module_profile (
 292
 293
 294      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique ID',
 295      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this module profile entry.',
 296      module varchar(255)    COMMENT 'Module Name.',
 297      entity_table varchar(64)    COMMENT 'physical tablename for entity being extended by this data, e.g. civicrm_contact',
 298      entity_id int unsigned NOT NULL   COMMENT 'FK to record in the entity table specified by entity_table column.',
 299      uf_group_id int unsigned NOT NULL   COMMENT 'Which form does this field belong to.',
 300      weight int NOT NULL  DEFAULT 1 COMMENT 'each internal or external module uses this to order multiple profiles associated with an entity_id'
 301 ,
 302     PRIMARY KEY ( id )
 303
 304     ,     INDEX index_entity(
 305         entity_table
 306       , entity_id
 307   )
 308
 309 ,
 310      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 311 ,
 312      FOREIGN KEY (uf_group_id) REFERENCES civicrm_uf_group(id)
 313
 314 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 315
 316
 317 -- /*******************************************************
 318 -- *
 319 -- * civicrm_uf_join
 320 -- *
 321 -- *
 322 -- *
 323 -- *******************************************************/
 324
 325     CREATE TABLE civicrm_uf_join (
 326
 327
 328      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
 329      is_active tinyint   DEFAULT 1 COMMENT 'Is this join currently active?',
 330      module varchar(64) NOT NULL   COMMENT 'Module which owns this uf_join instance, e.g. User Registration, CiviDonate, etc.',
 331      entity_table varchar(64)    COMMENT 'Name of table where item being referenced is stored. Modules which only need a single collection of uf_join instances may choose not to populate entity_table and entity_id.',
 332      entity_id int unsigned    COMMENT 'Foreign key to the referenced item.',
 333      weight int NOT NULL  DEFAULT 1 COMMENT 'Controls display order when multiple user framework groups are setup for concurrent display.',
 334      uf_group_id int unsigned NOT NULL   COMMENT 'Which form does this field belong to.'
 335 ,
 336     PRIMARY KEY ( id )
 337
 338     ,     INDEX index_entity(
 339         entity_table
 340       , entity_id
 341   )
 342
 343 ,
 344      FOREIGN KEY (uf_group_id) REFERENCES civicrm_uf_group(id)
 345
 346 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 347
 348
 349
 350
 351 -- /*******************************************************
 352 -- *
 353 -- * Insert Default Data in newly created tables
 354 -- *
 355 -- *******************************************************/
 356
 357    INSERT INTO `civicrm_contribution_type` VALUES (1, 1, 'Donation', NULL, NULL, 1, 0, 1);
 358    INSERT INTO `civicrm_contribution_type` VALUES (2, 1, 'Member Dues', NULL, NULL, 1, 0, 1);
 359    INSERT INTO `civicrm_contribution_type` VALUES (3, 1, 'Campaign Contribution', NULL, NULL, 0, 0, 1);
 360
 361    INSERT INTO `civicrm_payment_instrument` VALUES (1, 1, 'Credit Card', NULL, 1, 1);
 362    INSERT INTO `civicrm_payment_instrument` VALUES (2, 1, 'Debit Card', NULL, 1, 1);
 363    INSERT INTO `civicrm_payment_instrument` VALUES (3, 1, 'Cash', NULL, 1, 1);
 364    INSERT INTO `civicrm_payment_instrument` VALUES (4, 1, 'Check', NULL, 1, 1);
 365    INSERT INTO `civicrm_payment_instrument` VALUES (5, 1, 'EFT', NULL, 1, 1);
 366
 367    INSERT INTO `civicrm_dupe_match` VALUES (1, 1, 'contact_individual', 'first_name AND last_name AND email');
 368
 369
 370
 371 -- /*******************************************************
 372 -- *
 373 -- * Modify the civicrm_uf_group  Table Structure
 374 -- *
 375 -- *******************************************************/
 376
 377     ALTER TABLE `civicrm_uf_group` DROP `weight` ;
 378
 379
 380 -- /*******************************************************
 381 -- *
 382 -- * Modify the civicrm_uf_field  Table Structure
 383 -- *
 384 -- *******************************************************/
 385
 386    ALTER TABLE `civicrm_uf_field` DROP `is_registration` ,
 387                                   DROP `is_match` ;
 388
 389    ALTER TABLE `civicrm_uf_field` ADD `location_type_id` INT UNSIGNED COMMENT 'Location type of this mapping, if required';
 390    ALTER TABLE `civicrm_uf_field` ADD FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type` (`id`);
 391
 392    ALTER TABLE `civicrm_uf_field` ADD `phone_type` VARCHAR( 64 )   COMMENT 'Phone type, if required';
 393
 394
 395
 396 -- /*******************************************************
 397 -- *
 398 -- * Modify the  civicrm_custom_option Table Structure
 399 -- *
 400 -- *******************************************************/
 401
 402     ALTER TABLE `civicrm_custom_option` ADD `entity_table` VARCHAR( 64 )  COMMENT 'Name of table where item being referenced is stored.' ;
 403     UPDATE `civicrm_custom_option` SET `entity_table` = 'civicrm_custom_field' ;
 404
 405     ALTER TABLE `civicrm_custom_option` ADD `entity_id`  INT( 10 ) UNSIGNED DEFAULT '0' NOT NULL;
 406     UPDATE `civicrm_custom_option` SET entity_id = custom_field_id;
 407
 408     ALTER TABLE `civicrm_custom_option` DROP KEY `UI_label_custom_field_id`;
 409
 410     ALTER TABLE `civicrm_custom_option` DROP FOREIGN KEY `civicrm_custom_option_ibfk_1`;
 411
 412     ALTER TABLE `civicrm_custom_option` DROP `custom_field_id`;
 413
 414     ALTER TABLE `civicrm_custom_option` ADD INDEX `index_entity` ( `entity_table`, `entity_id` ) ;
 415
 416
 417
 418 -- /*******************************************************
 419 -- *
 420 -- * Insert Default data in to civicrm_accept_credit_card
 421 -- *
 422 -- *******************************************************/
 423
 424   INSERT INTO `civicrm_accept_credit_card` VALUES (1,1,'Visa','Visa',0,1),
 425                     (2,1,'MasterCard','Master Card',0,1),
 426               (3,1,'Amex','American Express',0,1),
 427               (4,1,'Discover','Discover',0,1);
 428
 429
 430 -- /*******************************************************
 431 -- *
 432 -- * Modify the  civicrm_custom_group Table Structure
 433 -- *
 434 -- *******************************************************/
 435
 436   ALTER TABLE `civicrm_custom_group` CHANGE `extends` `extends` ENUM( 'Contact', 'Individual', 'Household', 'Organization', 'Location', 'Address', 'Contribution', 'Activity', 'Phonecall', 'Meeting', 'Group' ) DEFAULT 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).';
 437
 438
 439 -- /*******************************************************
 440 -- *
 441 -- * Modify the  civicrm_custom_field Table Structure
 442 -- *
 443 -- *******************************************************/
 444
 445    ALTER TABLE `civicrm_custom_field` CHANGE `html_type` `html_type` ENUM( 'Text', 'TextArea', 'Select', 'Multi-Select', 'Radio', 'CheckBox', 'Select Date', 'Select State/Province', 'Select Country' ) DEFAULT NULL COMMENT 'HTML types plus several built-in extended types.';
 446
 447
 448
 449 -- /*******************************************************
 450 -- *
 451 -- * Drop Old Tables
 452 -- *
 453 -- *******************************************************/
 454
 455    DROP TABLE IF EXISTS civicrm_donation_page;
 456
 457