crm.fsf.org/20131203/files/sites/all/modules-new/civicrm/sql/civicrm_upgradedb_v1.4_v1.5_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 -- *
  27 -- * civicrm_membership_type
  28 -- *
  29 -- * Sites can configure multiple types of memberships. They encode the owner organization, fee, and the rules needed to set start and end (expire) dates when a member signs up for that type.
  30 -- *
  31 -- *******************************************************/
  32 CREATE TABLE civicrm_membership_type (
  33
  34
  35      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Membership Id',
  36      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this contact',
  37      name varchar(128)    COMMENT 'Name of Membership Type',
  38      description varchar(255)    COMMENT 'Description of Membership Type',
  39      member_of_contact_id int unsigned NOT NULL   COMMENT 'Owner organization for this membership type. FK to Contact ID',
  40      contribution_type_id int unsigned NOT NULL   COMMENT 'If membership is paid by a contribution - what contribution type should be used. FK to Contribution Type ID',
  41      minimum_fee decimal(20,2)   DEFAULT 0 COMMENT 'Minimum fee for this membership (0 for free/complimentary memberships).',
  42      duration_unit enum('day', 'month', 'year', 'lifetime')    COMMENT 'Unit in which membership period is expressed.',
  43      duration_interval int    COMMENT 'Number of duration units in membership period (e.g. 1 year, 12 months).',
  44      period_type enum('rolling', 'fixed')    COMMENT 'Rolling membership period starts on signup date. Fixed membership periods start on fixed_period_start_day.',
  45      fixed_period_start_day int   DEFAULT 0101 COMMENT 'For fixed period memberships, month and day (mmdd) on which subscription/membership will start. Period start is back-dated unless after rollover day.',
  46      fixed_period_rollover_day int   DEFAULT 1231 COMMENT 'For fixed period memberships, signups after this day (mmdd) rollover to next period.',
  47      relationship_type_id int unsigned    COMMENT 'FK to Relationship Type ID',
  48      visibility varchar(64)    ,
  49      weight int    ,
  50      is_active tinyint   DEFAULT 1 COMMENT 'Is this membership_type enabled'
  51 ,
  52     PRIMARY KEY ( id )
  53
  54
  55 ,      INDEX FKEY_domain_id ( domain_id ) ,
  56      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
  57 ,      INDEX FKEY_member_of_contact_id ( member_of_contact_id ) ,
  58      FOREIGN KEY (member_of_contact_id) REFERENCES civicrm_contact(id)
  59 ,      INDEX FKEY_contribution_type_id ( contribution_type_id ) ,
  60      FOREIGN KEY (contribution_type_id) REFERENCES civicrm_contribution_type(id)
  61 ,      INDEX FKEY_relationship_type_id ( relationship_type_id ) ,
  62      FOREIGN KEY (relationship_type_id) REFERENCES civicrm_relationship_type(id)
  63
  64 )  TYPE=InnoDB  ;
  65
  66 -- /*******************************************************
  67 -- *
  68 -- * civicrm_membership_status
  69 -- *
  70 -- * Membership Status stores admin configurable rules for assigning status to memberships.
  71 -- *
  72 -- *******************************************************/
  73 CREATE TABLE civicrm_membership_status (
  74
  75
  76      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Membership Id',
  77      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this contact',
  78      name varchar(128)    COMMENT 'Name for Membership Status',
  79      start_event enum('start_date', 'end_date', 'join_date')    COMMENT 'Event when this status starts.',
  80      start_event_adjust_unit enum('day', 'month', 'year')    COMMENT 'Unit used for adjusting from start_event.',
  81      start_event_adjust_interval int    COMMENT 'Status range begins this many units from start_event.',
  82      end_event enum('start_date', 'end_date', 'join_date')    COMMENT 'Event after which this status ends.',
  83      end_event_adjust_unit enum('day', 'month', 'year')    COMMENT 'Unit used for adjusting from the ending event.',
  84      end_event_adjust_interval int    COMMENT 'Status range ends this many units from end_event.',
  85      is_current_member tinyint    COMMENT 'Does this status aggregate to current members (e.g. New, Renewed, Grace might all be TRUE... while Unrenewed, Lapsed, Inactive would be FALSE).',
  86      is_admin tinyint    COMMENT 'Is this status for admin/manual assignment only.',
  87      weight int    ,
  88      is_default tinyint    COMMENT 'Assign this status to a membership record if no other status match is found.',
  89      is_active tinyint   DEFAULT 1 COMMENT 'Is this membership_status enabled.'
  90 ,
  91     PRIMARY KEY ( id )
  92
  93
  94 ,      INDEX FKEY_domain_id ( domain_id ) ,
  95      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
  96
  97 )  TYPE=InnoDB  ;
  98
  99 -- /*******************************************************
 100 -- *
 101 -- * civicrm_membership
 102 -- *
 103 -- * Contact Membership records.
 104 -- *
 105 -- *******************************************************/
 106 CREATE TABLE civicrm_membership (
 107
 108
 109      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Membership Id',
 110      contact_id int unsigned NOT NULL   COMMENT 'FK to Contact ID',
 111      membership_type_id int unsigned NOT NULL   COMMENT 'FK to Membership Type',
 112      join_date date    COMMENT 'Beginning of initial membership period (member since...).',
 113      start_date date    COMMENT 'Beginning of current uninterrupted membership period.',
 114      end_date date    COMMENT 'Current membership period expire date.',
 115      source varchar(128)    ,
 116      status_id int unsigned NOT NULL   COMMENT 'FK to Membership Status',
 117      is_override tinyint    COMMENT 'Admin users may set a manual status which overrides the calculated status. When this flag is true, automated status update scripts should NOT modify status for the record.'
 118 ,
 119     PRIMARY KEY ( id )
 120
 121
 122 ,      INDEX FKEY_contact_id ( contact_id ) ,
 123      FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
 124 ,      INDEX FKEY_membership_type_id ( membership_type_id ) ,
 125      FOREIGN KEY (membership_type_id) REFERENCES civicrm_membership_type(id)
 126 ,      INDEX FKEY_status_id ( status_id ) ,
 127      FOREIGN KEY (status_id) REFERENCES civicrm_membership_status(id)
 128
 129 )  TYPE=InnoDB  ;
 130
 131
 132 -- /*******************************************************
 133 -- *
 134 -- * civicrm_membership_block
 135 -- *
 136 -- * A Membership Block stores admin configurable status options and rules
 137 -- *
 138 -- *******************************************************/
 139 CREATE TABLE civicrm_membership_block (
 140
 141
 142      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Membership Id',
 143      entity_table varchar(64)    COMMENT 'Name for Membership Status',
 144      entity_id int unsigned NOT NULL   COMMENT 'FK to civicrm_contribution_page.id',
 145      membership_types varchar(255)    COMMENT 'Membership types to be exposed by this block',
 146      membership_type_default int unsigned    COMMENT 'Optional foreign key to membership_type',
 147      display_min_fee tinyint   DEFAULT 1 COMMENT 'Display minimum membership fee',
 148      is_separate_payment tinyint   DEFAULT 1 COMMENT 'Should membership transactions be processed separately',
 149      new_title varchar(255)    COMMENT 'Title to display at top of block',
 150      new_text text    COMMENT 'Text to display below title',
 151      renewal_title varchar(255)    COMMENT 'Title for renewal',
 152      renewal_text text    COMMENT 'Text to display for member renewal',
 153      is_required tinyint   DEFAULT 0 COMMENT 'Is membership sign up optional',
 154      is_active tinyint   DEFAULT 1 COMMENT 'Is this membership_block enabled'
 155 ,
 156     PRIMARY KEY ( id )
 157
 158
 159 ,      INDEX FKEY_entity_id ( entity_id ) ,
 160      FOREIGN KEY (entity_id) REFERENCES civicrm_contribution_page(id)
 161 ,      INDEX FKEY_membership_type_default ( membership_type_default ) ,
 162      FOREIGN KEY (membership_type_default) REFERENCES civicrm_membership_type(id)
 163
 164 )  TYPE=InnoDB  ;
 165
 166 -- /*******************************************************
 167 -- *
 168 -- * civicrm_membership_log
 169 -- *
 170 -- * Logs actions which affect a Membership record (signup, status override, renewal, etc.)
 171 -- *
 172 -- *******************************************************/
 173 CREATE TABLE civicrm_membership_log (
 174
 175
 176      id int unsigned NOT NULL AUTO_INCREMENT  ,
 177      membership_id int unsigned NOT NULL   COMMENT 'FK to Membership table',
 178      status_id int unsigned NOT NULL   COMMENT 'New status assigned to membership by this action. FK to Membership Status',
 179      start_date date    COMMENT 'New membership period start date',
 180      end_date date    COMMENT 'New membership period expiration date.',
 181      modified_id int unsigned    COMMENT 'FK to Contact ID of person under whose credentials this data modification was made.',
 182      modified_date date    COMMENT 'Date this membership modification action was logged.'
 183 ,
 184     PRIMARY KEY ( id )
 185
 186
 187 ,      INDEX FKEY_membership_id ( membership_id ) ,
 188      FOREIGN KEY (membership_id) REFERENCES civicrm_membership(id)
 189 ,      INDEX FKEY_status_id ( status_id ) ,
 190      FOREIGN KEY (status_id) REFERENCES civicrm_membership_status(id)
 191 ,      INDEX FKEY_modified_id ( modified_id ) ,
 192      FOREIGN KEY (modified_id) REFERENCES civicrm_contact(id)
 193
 194 )  TYPE=InnoDB  ;
 195
 196 -- /*******************************************************
 197 -- *
 198 -- * civicrm_membership_payment
 199 -- *
 200 -- * Membership Payment
 201 -- *
 202 -- *******************************************************/
 203 CREATE TABLE civicrm_membership_payment (
 204
 205
 206      id int unsigned NOT NULL AUTO_INCREMENT  ,
 207      membership_id int unsigned NOT NULL   COMMENT 'FK to Membership table',
 208      payment_entity_table varchar(128)    COMMENT 'Table being referenced for payment entity.',
 209      payment_entity_id int unsigned    COMMENT 'FK to table with payment record (expected usage is civicrm_contribution).'
 210 ,
 211     PRIMARY KEY ( id )
 212
 213     ,     INDEX index_payment_entity(
 214         payment_entity_table
 215       , payment_entity_id
 216   )
 217
 218 ,      INDEX FKEY_membership_id ( membership_id ) ,
 219      FOREIGN KEY (membership_id) REFERENCES civicrm_membership(id)
 220
 221 )  TYPE=InnoDB  ;
 222
 223 -- /*******************************************************
 224 -- *
 225 -- * civicrm_option_group
 226 -- *
 227 -- *******************************************************/
 228 CREATE TABLE civicrm_option_group (
 229
 230
 231      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Option Group ID',
 232      domain_id int unsigned NOT NULL   COMMENT 'Which domain owns this group of options.',
 233      name varchar(64) NOT NULL   COMMENT 'Option group name. Used as selection key by class properties which lookup options in civicrm_option_value.',
 234      description varchar(255)    COMMENT 'Option group description.',
 235      is_reserved tinyint    COMMENT 'Is this a predefined system option group (i.e. it can not be deleted)?',
 236      is_active tinyint    COMMENT 'Is this option group active?'
 237 ,
 238     PRIMARY KEY ( id )
 239
 240     ,     UNIQUE INDEX option_group_name_domain_id(
 241         name
 242       , domain_id
 243   )
 244
 245 ,      INDEX FKEY_domain_id ( domain_id ) ,
 246      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 247
 248 )  TYPE=InnoDB  ;
 249
 250 -- /*******************************************************
 251 -- *
 252 -- * civicrm_option_value
 253 -- *
 254 -- *******************************************************/
 255 CREATE TABLE civicrm_option_value (
 256
 257
 258      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Option ID',
 259      option_group_id int unsigned NOT NULL   COMMENT 'Group which this option belongs to.',
 260      label varchar(64) NOT NULL   COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.',
 261      value int unsigned NOT NULL   COMMENT 'The actual value stored (as a foreign key) in the data record. Functions which need lookup option_value.title should use civicrm_option_value.option_group_id plus civicrm_option_value.value as the key.',
 262      name varchar(64)    COMMENT 'May be used to store an option string that is different from the display title. One use case is when a non-translated value needs to be set / sent to another application (e.g. for Credit Card type).',
 263      grouping varchar(64)    COMMENT 'Use to sort and/or set display properties for sub-set(s) of options within an option group. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners.',
 264      filter int unsigned NOT NULL   COMMENT 'Bitwise logic can be used to create subsets of options within an option_group for different uses.',
 265      is_default tinyint   DEFAULT 0 COMMENT 'Is this the default option for the group?',
 266      weight int unsigned NOT NULL   COMMENT 'Controls display sort order.',
 267      description varchar(255)    COMMENT 'Optional description.',
 268      is_optgroup tinyint   DEFAULT 0 COMMENT 'Is this row simply a display header? Expected usage is to render these as OPTGROUP tags within a SELECT field list of options?',
 269      is_reserved tinyint   DEFAULT 0 COMMENT 'Is this a predefined system object?',
 270      is_active tinyint   DEFAULT 1 COMMENT 'Is this option active?'
 271 ,
 272     PRIMARY KEY ( id )
 273
 274     ,     INDEX index_option_group_id_value(
 275         value
 276       , option_group_id
 277   )
 278
 279 ,      INDEX FKEY_option_group_id ( option_group_id ) ,
 280      FOREIGN KEY (option_group_id) REFERENCES civicrm_option_group(id)
 281
 282 )  TYPE=InnoDB  ;
 283
 284 -- /*******************************************************
 285 -- *
 286 -- * civicrm_project
 287 -- *
 288 -- * Instantiate projects, programs, campaigns, etc.
 289 -- *
 290 -- *******************************************************/
 291 CREATE TABLE civicrm_project (
 292
 293
 294      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Project ID',
 295      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this record.',
 296      title varchar(64)    COMMENT 'Project name.',
 297      description text    COMMENT 'Optional verbose description of the project. May be used for display - HTML allowed.',
 298      logo varchar(255)    COMMENT 'Full or relative URL to optional uploaded logo image for project.',
 299      owner_entity_table varchar(64) NOT NULL   COMMENT 'Name of table where project owner being referenced is stored (e.g. civicrm_contact or civicrm_group).',
 300      owner_entity_id int unsigned NOT NULL   COMMENT 'Foreign key to project owner (contact, group, etc.).',
 301      start_date datetime    COMMENT 'Project start date.',
 302      end_date datetime    COMMENT 'Project end date.',
 303      is_active tinyint    COMMENT 'Is this record active? For Projects: can tasks be created for it, does it appear on project listings, etc.',
 304      status_id int unsigned    COMMENT 'Configurable status value (e.g. Planned, Active, Closed...). FK to civicrm_option_value.'
 305 ,
 306     PRIMARY KEY ( id )
 307
 308     ,     UNIQUE INDEX UI_project_owner(
 309         id
 310       , owner_entity_table
 311       , owner_entity_id
 312   )
 313
 314 ,      INDEX FKEY_domain_id ( domain_id ) ,
 315      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 316 ,      INDEX FKEY_status_id ( status_id ) ,
 317      FOREIGN KEY (status_id) REFERENCES civicrm_option_value(value)
 318
 319 )  TYPE=InnoDB  ;
 320
 321 -- /*******************************************************
 322 -- *
 323 -- * civicrm_task
 324 -- *
 325 -- * To-do items. Can be assigned to self or other entities.
 326 -- *
 327 -- *******************************************************/
 328 CREATE TABLE civicrm_task (
 329
 330
 331      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Task ID',
 332      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this record.',
 333      title varchar(64)    COMMENT 'Task name.',
 334      description varchar(255)    COMMENT 'Optional verbose description of the Task. May be used for display - HTML allowed.',
 335      task_type_id int unsigned    COMMENT 'Configurable task type values (e.g. App Submit, App Review...). FK to civicrm_option_value.',
 336      owner_entity_table varchar(64) NOT NULL   COMMENT 'Name of table where Task owner being referenced is stored (e.g. civicrm_contact or civicrm_group).',
 337      owner_entity_id int unsigned NOT NULL   COMMENT 'Foreign key to Task owner (contact, group, etc.).',
 338      parent_entity_table varchar(64)    COMMENT 'Name of table where optional Task parent is stored (e.g. civicrm_project, or civicrm_task for sub-tasks).',
 339      parent_entity_id int unsigned    COMMENT 'Optional foreign key to Task Parent (project, another task, etc.).',
 340      due_date datetime    COMMENT 'Task due date.',
 341      priority_id int unsigned    COMMENT 'Configurable priority value (e.g. Critical, High, Medium...). FK to civicrm_option_value.',
 342      task_class varchar(255)    COMMENT 'Optional key to a process class related to this task (e.g. CRM_Quest_PreApp).',
 343      is_active tinyint    COMMENT 'Is this record active? For tasks: can it be assigned, does it appear on open task listings, etc.'
 344 ,
 345     PRIMARY KEY ( id )
 346
 347     ,     UNIQUE INDEX UI_task_parent(
 348         id
 349       , parent_entity_table
 350       , parent_entity_id
 351   )
 352   ,     UNIQUE INDEX UI_task_owner(
 353         id
 354       , owner_entity_table
 355       , owner_entity_id
 356   )
 357
 358 ,      INDEX FKEY_domain_id ( domain_id ) ,
 359      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 360 ,      INDEX FKEY_task_type_id ( task_type_id ) ,
 361      FOREIGN KEY (task_type_id) REFERENCES civicrm_option_value(value)
 362 ,      INDEX FKEY_priority_id ( priority_id ) ,
 363      FOREIGN KEY (priority_id) REFERENCES civicrm_option_value(id)
 364
 365 )  TYPE=InnoDB  ;
 366
 367 -- /*******************************************************
 368 -- *
 369 -- * civicrm_task_status
 370 -- *
 371 -- * Tracks the responsible entity, optional target entity and status of a task. Tasks can have multiple task_status entries if assigned to multiple responsible entities and-or there are multiple targets.
 372 -- *
 373 -- *******************************************************/
 374 CREATE TABLE civicrm_task_status (
 375
 376
 377      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Task ID',
 378      task_id int unsigned NOT NULL   COMMENT 'Status is for which task.',
 379      responsible_entity_table varchar(64) NOT NULL   COMMENT 'Entity responsible for this task_status instance (table where entity is stored e.g. civicrm_contact or civicrm_group).',
 380      responsible_entity_id int unsigned NOT NULL   COMMENT 'Foreign key to responsible entity (contact, group, etc.).',
 381      target_entity_table varchar(64) NOT NULL   COMMENT 'Optional target entity for this task_status instance, i.e. review this membership application-prospect member contact record is target (table where entity is stored e.g. civicrm_contact or civicrm_group).',
 382      target_entity_id int unsigned NOT NULL   COMMENT 'Foreign key to target entity (contact, group, etc.).',
 383      status_detail text    COMMENT 'Encoded array of status details used for programmatic progress reporting and tracking.',
 384      status_id int unsigned    COMMENT 'Configurable status value (e.g. Not Started, In Progress, Completed, Deferred...). FK to civicrm_option_value.',
 385      create_date datetime    COMMENT 'Date this record was created (date work on task started).',
 386      modified_date datetime    COMMENT 'Date-time of last update to this task_status record.'
 387 ,
 388     PRIMARY KEY ( id )
 389
 390     ,     INDEX index_task_status_responsible(
 391         task_id
 392       , responsible_entity_table
 393       , responsible_entity_id
 394   )
 395   ,     INDEX index_task_status_target(
 396         task_id
 397       , target_entity_table
 398       , target_entity_id
 399   )
 400
 401 ,      INDEX FKEY_task_id ( task_id ) ,
 402      FOREIGN KEY (task_id) REFERENCES civicrm_task(id)
 403 ,      INDEX FKEY_status_id ( status_id ) ,
 404      FOREIGN KEY (status_id) REFERENCES civicrm_option_value(value)
 405
 406 )  TYPE=InnoDB  ;
 407
 408
 409 -- /*******************************************************
 410 -- *
 411 -- * civicrm_log
 412 -- *
 413 -- * Log can be linked to any object in the application.
 414 -- *
 415 -- *******************************************************/
 416 CREATE TABLE civicrm_log (
 417
 418
 419      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Log ID',
 420      entity_table varchar(64) NOT NULL   COMMENT 'Name of table where item being referenced is stored.',
 421      entity_id int unsigned NOT NULL   COMMENT 'Foreign key to the referenced item.',
 422      data text    COMMENT 'Updates does to this object if any.',
 423      modified_id int unsigned    COMMENT 'FK to Contact ID of person under whose credentials this data modification was made.',
 424      modified_date datetime    COMMENT 'When was the referenced entity created or modified or deleted.'
 425 ,
 426     PRIMARY KEY ( id )
 427
 428     ,     INDEX index_entity(
 429         entity_table
 430       , entity_id
 431   )
 432
 433 ,      INDEX FKEY_modified_id ( modified_id ) ,
 434      FOREIGN KEY (modified_id) REFERENCES civicrm_contact(id)
 435
 436 )  TYPE=InnoDB  ;
 437
 438
 439 -- /*******************************************************
 440 -- *
 441 -- * civicrm_file
 442 -- *
 443 -- * Data store for uploaded (attached) files (pointer to file on disk OR blob). Maybe be joined to entities via custom_value.file_id or entity_file table.
 444 -- *
 445 -- *******************************************************/
 446 CREATE TABLE civicrm_file (
 447
 448
 449      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique ID',
 450      file_type_id int unsigned    COMMENT 'Type of file (e.g. Transcript, Income Tax Return, etc). FK to civicrm_option_value.',
 451      mime_type varchar(255)    COMMENT 'mime type of the document',
 452      uri varchar(255)    COMMENT 'uri of the file on disk',
 453      document mediumblob    COMMENT 'contents of the document',
 454      description varchar(255)    COMMENT 'Additional descriptive text regarding this attachment (optional).',
 455      upload_date datetime    COMMENT 'Date and time that this attachment was uploaded or written to server.'
 456 ,
 457     PRIMARY KEY ( id )
 458
 459
 460 ,      INDEX FKEY_file_type_id ( file_type_id ) ,
 461      FOREIGN KEY (file_type_id) REFERENCES civicrm_option_value(value)
 462
 463 )  TYPE=InnoDB  ;
 464
 465 -- /*******************************************************
 466 -- *
 467 -- * civicrm_entity_file
 468 -- *
 469 -- * Attaches (joins)  uploaded files (images, documents, etc.) to entities (Contacts, Groups, Actions).
 470 -- *
 471 -- *******************************************************/
 472 CREATE TABLE civicrm_entity_file (
 473
 474
 475      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
 476      entity_table varchar(64)    COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
 477      entity_id int unsigned NOT NULL   COMMENT 'FK to entity table specified in entity_table column.',
 478      file_id int unsigned NOT NULL   COMMENT 'FK to civicrm_file'
 479 ,
 480     PRIMARY KEY ( id )
 481
 482     ,     INDEX index_entity(
 483         entity_table
 484       , entity_id
 485   )
 486   ,     INDEX index_entity_file_id(
 487         entity_table
 488       , entity_id
 489       , file_id
 490   )
 491
 492 ,      INDEX FKEY_file_id ( file_id ) ,
 493      FOREIGN KEY (file_id) REFERENCES civicrm_file(id)
 494
 495 )  TYPE=InnoDB  ;
 496
 497
 498
 499 -- /*******************************************************
 500 -- *
 501 -- * Modify the civicrm_individual  Table Structure
 502 -- *******************************************************/
 503
 504    ALTER TABLE `civicrm_individual` ADD deceased_date date COMMENT 'Date of deceased';
 505
 506 -- /*******************************************************
 507 -- *
 508 -- * Modify the civicrm_contact  Table Structure
 509 -- *******************************************************/
 510    ALTER TABLE `civicrm_contact` DROP INDEX `index_sort_name`;
 511     ALTER TABLE `civicrm_contact` DROP `hash`;
 512
 513    ALTER TABLE `civicrm_contact` ADD hash varchar(32) DEFAULT NULL COMMENT 'Key for validating requests related to this contact.';
 514    ALTER TABLE `civicrm_contact` ADD contact_sub_type varchar(64) DEFAULT NULL COMMENT 'May be used to over-ride contact view and edit templates.';
 515
 516    ALTER TABLE `civicrm_contact` MODIFY preferred_communication_method varchar(255) DEFAULT NULL COMMENT 'What is the preferred mode of communication.';
 517
 518    ALTER TABLE `civicrm_contact` ADD INDEX index_sort_name_domain(sort_name, domain_id, hash);
 519
 520    ALTER TABLE `civicrm_contact` ADD INDEX index_hash_domain(hash, domain_id);
 521
 522 -- /*******************************************************
 523 -- *
 524 -- * fix preferred communication data
 525 -- *******************************************************/
 526   SELECT @domain_id := id from civicrm_domain;
 527     INSERT INTO
 528     `civicrm_option_group` (`domain_id`, `name`, `description`, `is_reserved`, `is_active`)
 529     VALUES
 530     (@domain_id, 'preferred_communication_method', 'Preferred Communication Method'     , 0, 1);
 531     SELECT @option_group_id_pcm := max(id) from civicrm_option_group where name = 'preferred_communication_method';
 532
 533     INSERT INTO
 534    `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`)
 535     VALUES
 536     (@option_group_id_pcm, 'Phone', 1, NULL, NULL, 0, NULL, 1, NULL, 0, 0, 1),
 537     (@option_group_id_pcm, 'Email', 2, NULL, NULL, 0, NULL, 2, NULL, 0, 0, 1),
 538     (@option_group_id_pcm, 'Postal Mail', 3, NULL, NULL, 0, NULL, 3, NULL, 0, 0, 1),
 539     (@option_group_id_pcm, 'SMS', 4, NULL, NULL, 0, NULL, 4, NULL, 0, 0, 1),
 540     (@option_group_id_pcm, 'Fax', 5, NULL, NULL, 0, NULL, 5, NULL, 0, 0, 1);
 541
 542       UPDATE civicrm_contact SET preferred_communication_method = REPLACE( preferred_communication_method, 'Phone', 1);
 543       UPDATE civicrm_contact SET preferred_communication_method = REPLACE( preferred_communication_method, 'Email', 2);
 544       UPDATE civicrm_contact SET preferred_communication_method = REPLACE( preferred_communication_method, 'Postal Mail', 3);
 545       UPDATE civicrm_contact SET preferred_communication_method = REPLACE( preferred_communication_method, 'SMS', 4);
 546       UPDATE civicrm_contact SET preferred_communication_method = REPLACE( preferred_communication_method, 'Fax', 5);
 547
 548 -- /*******************************************************
 549 -- *
 550 -- * Modify the civicrm_contribution_page  Table Structure
 551 -- *******************************************************/
 552   ALTER TABLE `civicrm_contribution_page` ADD amount_block_is_active tinyint unsigned DEFAULT 1 COMMENT 'Is this property active?';
 553   ALTER TABLE `civicrm_contribution_page` ADD goal_amount  decimal(20,2) COMMENT 'The target goal for this page, allows people to build a goal meter';
 554   ALTER TABLE `civicrm_contribution_page` ADD is_thermometer int(4) unsigned DEFAULT 1 COMMENT 'Should this contribution have the thermometer block enabled?';
 555   ALTER TABLE `civicrm_contribution_page` ADD thermometer_title varchar(255) DEFAULT NULL COMMENT 'Title for contribution page thermometer block.';
 556
 557 -- /*******************************************************
 558 -- *
 559 -- * Modify the civicrm_contribution  Table Structure
 560 -- *******************************************************/
 561         ALTER TABLE `civicrm_contribution` ADD `contribution_page_id` int(10) unsigned DEFAULT NULL  COMMENT 'The Contribution Page which triggered this contribution';
 562         ALTER TABLE `civicrm_contribution` ADD INDEX (`contribution_page_id`);
 563         ALTER TABLE `civicrm_contribution` ADD FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page`(`id`);
 564
 565 -- /*******************************************************
 566 -- *
 567 -- * Modify the civicrm_custom_field  Table Structure
 568 -- *******************************************************/
 569
 570   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','File') DEFAULT NULL COMMENT 'HTML types plus several built-in extended types.';
 571
 572   ALTER TABLE `civicrm_custom_field` CHANGE `data_type` `data_type` ENUM('String', 'Int', 'Float', 'Money', 'Memo', 'Date', 'Boolean', 'StateProvince', 'Country', 'File') DEFAULT NULL COMMENT 'Controls location of data storage in extended_data table.';
 573
 574
 575 -- /*******************************************************
 576 -- *
 577 -- * Modify the  civicrm_custom_group Table Structure
 578 -- *
 579 -- *******************************************************/
 580
 581     ALTER TABLE `civicrm_custom_group` CHANGE `extends` `extends` ENUM( 'Contact', 'Individual', 'Household', 'Organization', 'Location', 'Address', 'Contribution', 'Activity', 'Phonecall', 'Meeting', 'Group','Relationship') DEFAULT 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).';
 582
 583 -- /*******************************************************
 584 -- *
 585 -- * Modify the  civicrm_custom_value Table Structure
 586 -- *
 587 -- *******************************************************/
 588
 589   ALTER TABLE `civicrm_custom_value` ADD file_id int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_file';
 590   ALTER TABLE `civicrm_custom_value` ADD INDEX (`file_id`);
 591   ALTER TABLE `civicrm_custom_value` ADD FOREIGN KEY (`file_id`) REFERENCES `civicrm_file`(`id`);
 592
 593
 594 -- /*******************************************************
 595 -- *
 596 -- * Modify the  civicrm_email Table Structure
 597 -- *
 598 -- *******************************************************/
 599
 600   ALTER TABLE `civicrm_email` ADD INDEX `UI_email` (`email`);
 601
 602 -- /*******************************************************
 603 -- *
 604 -- * Modify the  civicrm_mapping Table Structure
 605 -- *
 606 -- *******************************************************/
 607
 608
 609   ALTER TABLE `civicrm_mapping` CHANGE `mapping_type` `mapping_type` ENUM('Export', 'Import', 'Export Contributions', 'Import Contributions', 'Import Activity History', 'Search Builder') default NULL COMMENT 'Type of Mapping.';
 610
 611
 612 -- /*******************************************************
 613 -- *
 614 -- * Modify the  civicrm_mapping_field Table Structure
 615 -- *
 616 -- *******************************************************/
 617
 618   ALTER TABLE `civicrm_mapping_field` ADD grouping int(10) unsigned DEFAULT 1 COMMENT 'Used to group mapping_field records into related sets (e.g. for criteria sets in search builder mappings).';
 619
 620   ALTER TABLE `civicrm_mapping_field` ADD operator ENUM('=','!=','>','<','>=','<=','IN','NOT IN','LIKE','NOT LIKE') DEFAULT NULL COMMENT 'SQL WHERE operator for search-builder mapping fields (search criteria).';
 621
 622   ALTER TABLE `civicrm_mapping_field` ADD value varchar(255) DEFAULT NULL COMMENT 'SQL WHERE value for search-builder mapping fields.';
 623
 624 -- /*******************************************************
 625 -- *
 626 -- * Modify the civicrm_uf_group Table Structure
 627 -- *
 628 -- *******************************************************/
 629
 630   ALTER TABLE `civicrm_uf_group` ADD add_to_group_id int unsigned DEFAULT NULL COMMENT 'foreign key to civicrm_group_id';
 631   ALTER TABLE `civicrm_uf_group` ADD INDEX (`add_to_group_id`);
 632   ALTER TABLE `civicrm_uf_group` ADD FOREIGN KEY (`add_to_group_id`) REFERENCES `civicrm_group` (`id`);
 633
 634   ALTER TABLE `civicrm_uf_group` ADD add_captcha tinyint DEFAULT 0 COMMENT 'Should a CAPTCHA widget be included this Profile form.';
 635
 636   ALTER TABLE `civicrm_uf_group` ADD cancel_URL varchar(255) DEFAULT NULL COMMENT 'Redirect to URL when Cancle button clik .';
 637   ALTER TABLE `civicrm_uf_group` ADD is_map tinyint DEFAULT 0 COMMENT 'Do we want to map results from this profile.';
 638   ALTER TABLE `civicrm_uf_group` ADD collapse_display int(10) unsigned DEFAULT 0 COMMENT 'Will this group be in collapsed or expanded mode on initial display ?';
 639
 640 -- /*******************************************************
 641 -- *
 642 -- * Modify the civicrm_uf_match  Table Structure
 643 -- *
 644 -- *******************************************************/
 645
 646    ALTER TABLE `civicrm_uf_match` ADD INDEX `UI_uf_id` (`uf_id`);
 647
 648 -- /*******************************************************
 649 -- *
 650 -- * Modify the civicrm_note  Table Structure
 651 -- *
 652 -- *******************************************************/
 653
 654
 655    ALTER TABLE `civicrm_note` ADD `subject` varchar(255) DEFAULT NULL COMMENT 'subject of note description';
 656
 657 -- /*******************************************************
 658 -- *
 659 -- * Modify the civicrm_relationship  Table Structure
 660 -- *
 661 -- *******************************************************/
 662
 663
 664    ALTER TABLE `civicrm_relationship` ADD `description` varchar(255) COMMENT 'Optional verbose description for the relationship.' ;
 665
 666 -- /*******************************************************
 667 -- *
 668 -- * Modify the civicrm_saved_search Table Structure
 669 -- *
 670 -- *******************************************************/
 671
 672   ALTER TABLE `civicrm_saved_search` ADD `mapping_id` INT UNSIGNED COMMENT 'Foreign key to civicrm_mapping used for saved search-builder searches.';
 673   ALTER TABLE `civicrm_saved_search` ADD INDEX (`mapping_id`);
 674   ALTER TABLE `civicrm_saved_search` ADD FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping` (`id`);
 675
 676   ALTER TABLE `civicrm_saved_search` DROP FOREIGN KEY `civicrm_saved_search_ibfk_1`;
 677
 678   ALTER TABLE `civicrm_saved_search` DROP `query`,
 679              DROP `domain_id`,
 680              DROP `is_active`;
 681 -- /*******************************************************
 682 -- *
 683 -- * Modify the civicrm_custom_value  Table Structure
 684 -- *
 685 -- *******************************************************/
 686
 687   UPDATE `civicrm_custom_value`, `civicrm_custom_field`
 688     SET `char_data` = CONCAT(char(1),`char_data`,char(1))
 689     WHERE `custom_field_id` = `civicrm_custom_field`.`id`
 690       AND `html_type` IN ('multi-Select','CheckBox');
 691