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