Merge remote-tracking branch 'upstream/4.5' into 4.5-master-2015-02-16-17-51-20

[civicrm-core.git] / sql / civicrm_upgradedb_v1.3_v1.4_40.mysql

-- +--------------------------------------------------------------------+
-- | CiviCRM version 3.2                                                |
-- +--------------------------------------------------------------------+
-- | Copyright CiviCRM LLC (c) 2004-2010                                |
-- +--------------------------------------------------------------------+
-- | This file is a part of CiviCRM.                                    |
-- |                                                                    |
-- | CiviCRM is free software; you can copy, modify, and distribute it  |
-- | under the terms of the GNU Affero General Public License           |
-- | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
-- |                                                                    |
-- | CiviCRM is distributed in the hope that it will be useful, but     |
-- | WITHOUT ANY WARRANTY; without even the implied warranty of         |
-- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
-- | See the GNU Affero General Public License for more details.        |
-- |                                                                    |
-- | You should have received a copy of the GNU Affero General Public   |
-- | License and the CiviCRM Licensing Exception along                  |
-- | with this program; if not, contact CiviCRM LLC                     |
-- | at info[AT]civicrm[DOT]org. If you have questions about the        |
-- | GNU Affero General Public License or the licensing of CiviCRM,     |
-- | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
-- +--------------------------------------------------------------------+
-- /*******************************************************
-- *
-- * Clean up the exisiting tables
-- *
-- *******************************************************/
DROP TABLE IF EXISTS civicrm_contribution_product;
DROP TABLE IF EXISTS civicrm_sms_history;
DROP TABLE IF EXISTS civicrm_premiums_product;
DROP TABLE IF EXISTS civicrm_product;
DROP TABLE IF EXISTS civicrm_premiums;
DROP TABLE IF EXISTS civicrm_contribution_recur;


-- /*******************************************************
-- *
-- * adding of new tables
-- *
-- *******************************************************/

-- /*******************************************************
-- *
-- * civicrm_contribution_product
-- *
-- *******************************************************/
CREATE TABLE civicrm_contribution_product (
     id int unsigned NOT NULL AUTO_INCREMENT  ,
     product_id int unsigned NOT NULL   ,
     contribution_id int unsigned NOT NULL   ,
     product_option varchar(255)    COMMENT 'Option value selected if applicable - e.g. color, size etc.',
     quantity int    ,
     fulfilled_date date    COMMENT 'Optional. Can be used to record the date this product was fulfilled or shipped.',
     start_date date    COMMENT 'Actual start date for a time-delimited premium (subscription, service or membership)',
     end_date date    COMMENT 'Actual end date for a time-delimited premium (subscription, service or membership)',
     comment text
,
    PRIMARY KEY ( id )

,      INDEX FKEY_contribution_id ( contribution_id ) ,
     FOREIGN KEY (contribution_id) REFERENCES civicrm_contribution(id)

)  TYPE=InnoDB  ;


-- /*******************************************************
-- *
-- * civicrm_contribution_recur
-- *
-- *******************************************************/
CREATE TABLE civicrm_contribution_recur (
     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution Recur ID',
     contact_id int unsigned NOT NULL   COMMENT 'Foreign key to civicrm_contact.id .',
     amount decimal(20,2) NOT NULL   COMMENT 'Amount to be contributed or charged each recurrence.',
     frequency_unit enum('day', 'week', 'month', 'year')   DEFAULT 'month' COMMENT 'Time units for recurrence of payment.',
     frequency_interval int unsigned NOT NULL   COMMENT 'Number of time units for recurrence of payment.',
     installments int unsigned    COMMENT 'Total number of payments to be made. Set this to 0 if this is an open-ended commitment i.e. no set end date.',
     start_date date NOT NULL   COMMENT 'The date the first scheduled recurring contribution occurs.',
     create_date date NOT NULL   COMMENT 'When this recurring contribution record was created.',
     modified_date date NOT NULL   COMMENT 'Last updated date for this record.',
     cancel_date date NOT NULL   COMMENT 'Date this recurring contribution was cancelled by contributor- if we can get access to it ??',
     is_active tinyint NOT NULL   COMMENT 'Set to false by contributor cancellation or greater than max permitted failures - if we know about that.',
     cycle_day int unsigned NOT NULL  DEFAULT 1 COMMENT 'Day in the period when the payment should be charged e.g. 1st of month, 15th etc.',
     next_sched_contribution date NOT NULL   COMMENT 'At Groundspring this was used by the cron job which triggered payments. If we\'re not doing that but we know about payments, it might still be useful to store for display to org andor contributors.',
     failure_count int unsigned   DEFAULT 0 COMMENT 'Number of failed charge attempts since last success. Business rule could be set to deactivate on more than x failures.',
     failure_retry_date date    COMMENT 'At Groundspring we set a business rule to retry failed payments every 7 days - and stored the next scheduled attempt date there.',
     processor_id varchar(255)    COMMENT 'Possibly needed to store a unique identifier for this recurring payment order - if this is available from the processor??',
     auto_renew tinyint NOT NULL  DEFAULT 0 COMMENT 'Some systems allow contributor to set a number of installments - but then auto-renew the subscription or commitment if they do not cancel.'
,
    PRIMARY KEY ( id )

,      INDEX FKEY_contact_id ( contact_id ) ,
     FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)

)  TYPE=InnoDB  ;


-- /*******************************************************
-- *
-- * civicrm_premiums
-- *
-- * table - settings for the Premiums features for a given contribution page
-- *
-- *******************************************************/
CREATE TABLE civicrm_premiums (
     id int unsigned NOT NULL AUTO_INCREMENT  ,
     entity_table varchar(64) NOT NULL   COMMENT 'Joins these premium settings to another object. Always civicrm_contribution_page for now.',
     entity_id int unsigned NOT NULL   ,
     premiums_active tinyint NOT NULL  DEFAULT 0 COMMENT 'Is the Premiums feature enabled for this page?',
     premiums_intro_title varchar(255)    COMMENT 'Title for Premiums section.',
     premiums_intro_text text    COMMENT 'Displayed in <div > at top of Premiums section of page. Text and html allowed.',
     premiums_contact_email varchar(100)    COMMENT 'This email address is included in receipts if it is populated and a premium has been selected.',
     premiums_contact_phone varchar(50)    COMMENT 'This phone number is included in receipts if it is populated and a premium has been selected.',
     premiums_display_min_contribution tinyint NOT NULL   COMMENT 'Boolean. Should we automatically display minimum contribution amount text after the premium descriptions.'
,
    PRIMARY KEY ( id )

)  TYPE=InnoDB  ;

-- /*******************************************************
-- *
-- * civicrm_product
-- *
-- * able - stores "product info" for premiums and can be used for non-incentive products
-- *
-- *******************************************************/
CREATE TABLE civicrm_product (
     id int unsigned NOT NULL AUTO_INCREMENT  ,
     domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this product class.',
     name varchar(255) NOT NULL   COMMENT 'Required product/premium name',
     description text    COMMENT 'Optional description of the product/premium.',
     sku varchar(50)    COMMENT 'Optional product sku or code.',
     options text    COMMENT 'Store comma-delimited list of color, size, etc. options for the product.',
     image varchar(255)    COMMENT 'Full or relative URL to uploaded image - fullsize.',
     thumbnail varchar(255)    COMMENT 'Full or relative URL to image thumbnail.',
     price decimal(20,2)    COMMENT 'Sell price or market value for premiums. For tax-deductible contributions, this will be stored as non_deductible_amount in the contribution record.',
     min_contribution decimal(20,2)    COMMENT 'Minimum contribution required to be eligible to select this premium.',
     cost decimal(20,2)    COMMENT 'Actual cost of this product. Useful to determine net return from sale or using this as an incentive.',
     is_active tinyint NOT NULL   COMMENT 'Disabling premium removes it from the premiums_premium join table below.',
     period_type enum('rolling', 'fixed')   DEFAULT 'rolling' COMMENT 'Rolling means we set start/end based on current day, fixed means we set start/end for current year or month
(e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006) ',
     fixed_period_start_day int   DEFAULT 0101 COMMENT 'Month and day (MMDD) that fixed period type subscription or membership starts.',
     duration_unit enum('day', 'month', 'week', 'year')   DEFAULT 'year' ,
     duration_interval int    COMMENT 'Number of units for total duration of subscription, service, membership (e.g. 12 Months).',
     frequency_unit enum('day', 'month', 'week', 'year')   DEFAULT 'month' COMMENT 'Frequency unit and interval allow option to store actual delivery frequency for a subscription or service.',
     frequency_interval int    COMMENT 'Number of units for delivery frequency of subscription, service, membership (e.g. every 3 Months).'
,
    PRIMARY KEY ( id )

,      INDEX FKEY_domain_id ( domain_id ) ,
     FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)

)  TYPE=InnoDB  ;

-- /*******************************************************
-- *
-- * civicrm_premiums_product
-- *
-- * joins premiums (settings) to individual product/premium items - determines which products are available for a given contribution page
-- *
-- *******************************************************/
CREATE TABLE civicrm_premiums_product (
     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution ID',
     premiums_id int unsigned NOT NULL   COMMENT 'Foreign key to premiums settings record.',
     product_id int unsigned NOT NULL   COMMENT 'Foreign key to each product object.',
     sort_position int unsigned NOT NULL
,
    PRIMARY KEY ( id )

,      INDEX FKEY_premiums_id ( premiums_id ) ,
     FOREIGN KEY (premiums_id) REFERENCES civicrm_premiums(id)
,      INDEX FKEY_product_id ( product_id ) ,
     FOREIGN KEY (product_id) REFERENCES civicrm_product(id)

)  TYPE=InnoDB  ;


-- /*******************************************************
-- *
-- * civicrm_sms_history
-- *
-- * SMS History can be linked to any object in the application.
-- *
-- *******************************************************/
CREATE TABLE civicrm_sms_history (

     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'SMS History ID',
     message text    COMMENT 'Contents of the SMS.',
     contact_id int unsigned NOT NULL   COMMENT 'FK to Contact who is sending this SMS',
     sent_date date    COMMENT 'When was this SMS sent'
,
    PRIMARY KEY ( id )

,      INDEX FKEY_contact_id ( contact_id ) ,
     FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)

)  TYPE=InnoDB  ;


-- /*******************************************************
-- *
-- * Modify the civicrm_contact  Table Structure
-- * (keep this in for 1.4 to 1.5 as it was added late to 1.4)
-- *******************************************************/

    ALTER TABLE `civicrm_contact` MODIFY `preferred_mail_format` enum('Text', 'HTML', 'Both')   DEFAULT 'Both';


-- /*******************************************************
-- *
-- * Modify the civicrm_contribution  Table Structure
-- *
-- *******************************************************/

   ALTER TABLE `civicrm_contribution` ADD `solicitor_id` int unsigned COMMENT 'FK to Solicitor ID';

   ALTER TABLE `civicrm_contribution` ADD INDEX (`solicitor_id`);

   ALTER TABLE `civicrm_contribution` ADD FOREIGN KEY (`solicitor_id`) REFERENCES `civicrm_contact` (`id`);

   ALTER TABLE `civicrm_contribution` ADD note text    COMMENT 'Note and/or Comment.';

   ALTER TABLE `civicrm_contribution` ADD recur_contribution_id int unsigned    COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each contribution made in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.';

   ALTER TABLE `civicrm_contribution` ADD INDEX (`recur_contribution_id`);

   ALTER TABLE `civicrm_contribution` ADD CONSTRAINT FOREIGN KEY (recur_contribution_id) REFERENCES civicrm_contribution_recur(id);


-- /*******************************************************
-- *
-- * Modify the civicrm_contribution_page Table Structure
-- *
-- *******************************************************/

   ALTER TABLE `civicrm_contribution_page` ADD footer_text text    COMMENT 'Text and html allowed. Displayed at the bottom of the first page of the contribution wizard.';


-- /*******************************************************
-- *
-- * Modify the civicrm_custom_field Table Structure
-- *
-- *******************************************************/

   ALTER TABLE `civicrm_custom_field` ADD is_search_range tinyint   DEFAULT 0 COMMENT 'Is this property range searchable.';
   ALTER TABLE `civicrm_custom_field` ADD start_date_years int unsigned    COMMENT 'Date may be up to start_date_years years prior to tcurrent date  ';
   ALTER TABLE `civicrm_custom_field` ADD end_date_years int unsigned    COMMENT 'Date may be up to end_date_years years after to tcurrent date ';
   ALTER TABLE `civicrm_custom_field` ADD date_parts varchar(255)    COMMENT 'which date part included in display ';
   ALTER TABLE `civicrm_custom_field` ADD note_columns int unsigned    COMMENT ' Number of columns in Note Field ';
   ALTER TABLE `civicrm_custom_field` ADD note_rows int unsigned    COMMENT ' Number of rows in Note Field ';


-- /*******************************************************
-- *
-- * Modify the civicrm_domain Table Structure
-- *
-- *******************************************************/

   ALTER TABLE `civicrm_domain` ADD email_return_path varchar(64)    COMMENT 'The domain from which outgoing email for this domain will appear to originate';


-- /*******************************************************
-- *
-- * Modify the civicrm_location Table Structure
-- *
-- *******************************************************/

   ALTER TABLE `civicrm_location` ADD  name varchar(255);


-- /*******************************************************
-- *
-- * Modify the civicrm_uf_field  Table Structure
-- *
-- *******************************************************/

   ALTER TABLE `civicrm_uf_field` ADD is_searchable tinyint   DEFAULT 0 COMMENT 'Is this field included search form of profile?';

   ALTER TABLE `civicrm_uf_field` ADD label varchar(255)    COMMENT 'To save label for fields.';

   ALTER TABLE `civicrm_uf_field` ADD field_type varchar(255)    COMMENT 'This field saves field type (ie individual,household.. field etc).';


-- /*******************************************************
-- *
-- * Modify the civicrm_uf_group  Table Structure
-- *
-- *******************************************************/

   ALTER TABLE `civicrm_uf_group` ADD limit_listings_group_id int unsigned    COMMENT 'Group id, foriegn key from civicrm_group';

   ALTER TABLE `civicrm_uf_group` ADD INDEX (`limit_listings_group_id`);

   ALTER TABLE `civicrm_uf_group` ADD FOREIGN KEY (limit_listings_group_id) REFERENCES civicrm_group(id);

   ALTER TABLE `civicrm_uf_group` ADD post_URL varchar(255)    COMMENT 'Redirect to URL.';


-- /*******************************************************
-- *
-- * update civicrm_uf_field with the default values (also fix label)
-- *
-- *******************************************************/

   UPDATE civicrm_uf_field SET is_searchable = 1 WHERE in_selector = 1 AND visibility = 'Public User Pages and Listings';

   UPDATE civicrm_uf_field SET label=field_name, field_type='Individual';


-- /*******************************************************
-- *
-- * Drop Old Tables
-- *
-- *******************************************************/

   DROP TABLE IF EXISTS civicrm_module_profile;


-- /*******************************************************
-- *
-- * update abbreviation for New Hampshire in case it is still wrong
-- *
-- *******************************************************/

   UPDATE civicrm_state_province SET abbreviation = 'NH' WHERE id = 1028;


-- /*******************************************************
-- *
-- * update the name of the Mexican state of Morelos
-- *
-- *******************************************************/

   UPDATE civicrm_state_province SET name = 'Morelos' WHERE id = 3819;


-- /*******************************************************
-- *
-- * update the custom fields set options per line if it is null
-- *
-- *******************************************************/

   UPDATE civicrm_custom_field SET  options_per_line=0  WHERE options_per_line IS NULL;