sql/civicrm_upgradedb_v1.3_v1.4_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 -- * Clean up the exisiting tables
  27 -- *
  28 -- *******************************************************/
  29 DROP TABLE IF EXISTS civicrm_contribution_product;
  30 DROP TABLE IF EXISTS civicrm_sms_history;
  31 DROP TABLE IF EXISTS civicrm_premiums_product;
  32 DROP TABLE IF EXISTS civicrm_product;
  33 DROP TABLE IF EXISTS civicrm_premiums;
  34 DROP TABLE IF EXISTS civicrm_contribution_recur;
  35
  36
  37 -- /*******************************************************
  38 -- *
  39 -- * adding of new tables
  40 -- *
  41 -- *******************************************************/
  42
  43 -- /*******************************************************
  44 -- *
  45 -- * civicrm_contribution_product
  46 -- *
  47 -- *******************************************************/
  48 CREATE TABLE civicrm_contribution_product (
  49      id int unsigned NOT NULL AUTO_INCREMENT  ,
  50      product_id int unsigned NOT NULL   ,
  51      contribution_id int unsigned NOT NULL   ,
  52      product_option varchar(255)    COMMENT 'Option value selected if applicable - e.g. color, size etc.',
  53      quantity int    ,
  54      fulfilled_date date    COMMENT 'Optional. Can be used to record the date this product was fulfilled or shipped.',
  55      start_date date    COMMENT 'Actual start date for a time-delimited premium (subscription, service or membership)',
  56      end_date date    COMMENT 'Actual end date for a time-delimited premium (subscription, service or membership)',
  57      comment text
  58 ,
  59     PRIMARY KEY ( id )
  60
  61 ,      INDEX FKEY_contribution_id ( contribution_id ) ,
  62      FOREIGN KEY (contribution_id) REFERENCES civicrm_contribution(id)
  63
  64 )  TYPE=InnoDB  ;
  65
  66
  67 -- /*******************************************************
  68 -- *
  69 -- * civicrm_contribution_recur
  70 -- *
  71 -- *******************************************************/
  72 CREATE TABLE civicrm_contribution_recur (
  73      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution Recur ID',
  74      contact_id int unsigned NOT NULL   COMMENT 'Foreign key to civicrm_contact.id .',
  75      amount decimal(20,2) NOT NULL   COMMENT 'Amount to be contributed or charged each recurrence.',
  76      frequency_unit enum('day', 'week', 'month', 'year')   DEFAULT 'month' COMMENT 'Time units for recurrence of payment.',
  77      frequency_interval int unsigned NOT NULL   COMMENT 'Number of time units for recurrence of payment.',
  78      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.',
  79      start_date date NOT NULL   COMMENT 'The date the first scheduled recurring contribution occurs.',
  80      create_date date NOT NULL   COMMENT 'When this recurring contribution record was created.',
  81      modified_date date NOT NULL   COMMENT 'Last updated date for this record.',
  82      cancel_date date NOT NULL   COMMENT 'Date this recurring contribution was cancelled by contributor- if we can get access to it ??',
  83      is_active tinyint NOT NULL   COMMENT 'Set to false by contributor cancellation or greater than max permitted failures - if we know about that.',
  84      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.',
  85      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.',
  86      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.',
  87      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.',
  88      processor_id varchar(255)    COMMENT 'Possibly needed to store a unique identifier for this recurring payment order - if this is available from the processor??',
  89      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.'
  90 ,
  91     PRIMARY KEY ( id )
  92
  93 ,      INDEX FKEY_contact_id ( contact_id ) ,
  94      FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
  95
  96 )  TYPE=InnoDB  ;
  97
  98
  99 -- /*******************************************************
 100 -- *
 101 -- * civicrm_premiums
 102 -- *
 103 -- * table - settings for the Premiums features for a given contribution page
 104 -- *
 105 -- *******************************************************/
 106 CREATE TABLE civicrm_premiums (
 107      id int unsigned NOT NULL AUTO_INCREMENT  ,
 108      entity_table varchar(64) NOT NULL   COMMENT 'Joins these premium settings to another object. Always civicrm_contribution_page for now.',
 109      entity_id int unsigned NOT NULL   ,
 110      premiums_active tinyint NOT NULL  DEFAULT 0 COMMENT 'Is the Premiums feature enabled for this page?',
 111      premiums_intro_title varchar(255)    COMMENT 'Title for Premiums section.',
 112      premiums_intro_text text    COMMENT 'Displayed in <div > at top of Premiums section of page. Text and html allowed.',
 113      premiums_contact_email varchar(100)    COMMENT 'This email address is included in receipts if it is populated and a premium has been selected.',
 114      premiums_contact_phone varchar(50)    COMMENT 'This phone number is included in receipts if it is populated and a premium has been selected.',
 115      premiums_display_min_contribution tinyint NOT NULL   COMMENT 'Boolean. Should we automatically display minimum contribution amount text after the premium descriptions.'
 116 ,
 117     PRIMARY KEY ( id )
 118
 119 )  TYPE=InnoDB  ;
 120
 121 -- /*******************************************************
 122 -- *
 123 -- * civicrm_product
 124 -- *
 125 -- * able - stores "product info" for premiums and can be used for non-incentive products
 126 -- *
 127 -- *******************************************************/
 128 CREATE TABLE civicrm_product (
 129      id int unsigned NOT NULL AUTO_INCREMENT  ,
 130      domain_id int unsigned NOT NULL   COMMENT 'Which Domain owns this product class.',
 131      name varchar(255) NOT NULL   COMMENT 'Required product/premium name',
 132      description text    COMMENT 'Optional description of the product/premium.',
 133      sku varchar(50)    COMMENT 'Optional product sku or code.',
 134      options text    COMMENT 'Store comma-delimited list of color, size, etc. options for the product.',
 135      image varchar(255)    COMMENT 'Full or relative URL to uploaded image - fullsize.',
 136      thumbnail varchar(255)    COMMENT 'Full or relative URL to image thumbnail.',
 137      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.',
 138      min_contribution decimal(20,2)    COMMENT 'Minimum contribution required to be eligible to select this premium.',
 139      cost decimal(20,2)    COMMENT 'Actual cost of this product. Useful to determine net return from sale or using this as an incentive.',
 140      is_active tinyint NOT NULL   COMMENT 'Disabling premium removes it from the premiums_premium join table below.',
 141      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
 142 (e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006) ',
 143      fixed_period_start_day int   DEFAULT 0101 COMMENT 'Month and day (MMDD) that fixed period type subscription or membership starts.',
 144      duration_unit enum('day', 'month', 'week', 'year')   DEFAULT 'year' ,
 145      duration_interval int    COMMENT 'Number of units for total duration of subscription, service, membership (e.g. 12 Months).',
 146      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.',
 147      frequency_interval int    COMMENT 'Number of units for delivery frequency of subscription, service, membership (e.g. every 3 Months).'
 148 ,
 149     PRIMARY KEY ( id )
 150
 151 ,      INDEX FKEY_domain_id ( domain_id ) ,
 152      FOREIGN KEY (domain_id) REFERENCES civicrm_domain(id)
 153
 154 )  TYPE=InnoDB  ;
 155
 156 -- /*******************************************************
 157 -- *
 158 -- * civicrm_premiums_product
 159 -- *
 160 -- * joins premiums (settings) to individual product/premium items - determines which products are available for a given contribution page
 161 -- *
 162 -- *******************************************************/
 163 CREATE TABLE civicrm_premiums_product (
 164      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution ID',
 165      premiums_id int unsigned NOT NULL   COMMENT 'Foreign key to premiums settings record.',
 166      product_id int unsigned NOT NULL   COMMENT 'Foreign key to each product object.',
 167      sort_position int unsigned NOT NULL
 168 ,
 169     PRIMARY KEY ( id )
 170
 171 ,      INDEX FKEY_premiums_id ( premiums_id ) ,
 172      FOREIGN KEY (premiums_id) REFERENCES civicrm_premiums(id)
 173 ,      INDEX FKEY_product_id ( product_id ) ,
 174      FOREIGN KEY (product_id) REFERENCES civicrm_product(id)
 175
 176 )  TYPE=InnoDB  ;
 177
 178
 179 -- /*******************************************************
 180 -- *
 181 -- * civicrm_sms_history
 182 -- *
 183 -- * SMS History can be linked to any object in the application.
 184 -- *
 185 -- *******************************************************/
 186 CREATE TABLE civicrm_sms_history (
 187
 188      id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'SMS History ID',
 189      message text    COMMENT 'Contents of the SMS.',
 190      contact_id int unsigned NOT NULL   COMMENT 'FK to Contact who is sending this SMS',
 191      sent_date date    COMMENT 'When was this SMS sent'
 192 ,
 193     PRIMARY KEY ( id )
 194
 195 ,      INDEX FKEY_contact_id ( contact_id ) ,
 196      FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id)
 197
 198 )  TYPE=InnoDB  ;
 199
 200
 201 -- /*******************************************************
 202 -- *
 203 -- * Modify the civicrm_contact  Table Structure
 204 -- * (keep this in for 1.4 to 1.5 as it was added late to 1.4)
 205 -- *******************************************************/
 206
 207     ALTER TABLE `civicrm_contact` MODIFY `preferred_mail_format` enum('Text', 'HTML', 'Both')   DEFAULT 'Both';
 208
 209
 210 -- /*******************************************************
 211 -- *
 212 -- * Modify the civicrm_contribution  Table Structure
 213 -- *
 214 -- *******************************************************/
 215
 216    ALTER TABLE `civicrm_contribution` ADD `solicitor_id` int unsigned COMMENT 'FK to Solicitor ID';
 217
 218    ALTER TABLE `civicrm_contribution` ADD INDEX (`solicitor_id`);
 219
 220    ALTER TABLE `civicrm_contribution` ADD FOREIGN KEY (`solicitor_id`) REFERENCES `civicrm_contact` (`id`);
 221
 222    ALTER TABLE `civicrm_contribution` ADD note text    COMMENT 'Note and/or Comment.';
 223
 224    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.';
 225
 226    ALTER TABLE `civicrm_contribution` ADD INDEX (`recur_contribution_id`);
 227
 228    ALTER TABLE `civicrm_contribution` ADD CONSTRAINT FOREIGN KEY (recur_contribution_id) REFERENCES civicrm_contribution_recur(id);
 229
 230
 231 -- /*******************************************************
 232 -- *
 233 -- * Modify the civicrm_contribution_page Table Structure
 234 -- *
 235 -- *******************************************************/
 236
 237    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.';
 238
 239
 240 -- /*******************************************************
 241 -- *
 242 -- * Modify the civicrm_custom_field Table Structure
 243 -- *
 244 -- *******************************************************/
 245
 246    ALTER TABLE `civicrm_custom_field` ADD is_search_range tinyint   DEFAULT 0 COMMENT 'Is this property range searchable.';
 247    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  ';
 248    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 ';
 249    ALTER TABLE `civicrm_custom_field` ADD date_parts varchar(255)    COMMENT 'which date part included in display ';
 250    ALTER TABLE `civicrm_custom_field` ADD note_columns int unsigned    COMMENT ' Number of columns in Note Field ';
 251    ALTER TABLE `civicrm_custom_field` ADD note_rows int unsigned    COMMENT ' Number of rows in Note Field ';
 252
 253
 254 -- /*******************************************************
 255 -- *
 256 -- * Modify the civicrm_domain Table Structure
 257 -- *
 258 -- *******************************************************/
 259
 260    ALTER TABLE `civicrm_domain` ADD email_return_path varchar(64)    COMMENT 'The domain from which outgoing email for this domain will appear to originate';
 261
 262
 263 -- /*******************************************************
 264 -- *
 265 -- * Modify the civicrm_location Table Structure
 266 -- *
 267 -- *******************************************************/
 268
 269    ALTER TABLE `civicrm_location` ADD  name varchar(255);
 270
 271
 272 -- /*******************************************************
 273 -- *
 274 -- * Modify the civicrm_uf_field  Table Structure
 275 -- *
 276 -- *******************************************************/
 277
 278    ALTER TABLE `civicrm_uf_field` ADD is_searchable tinyint   DEFAULT 0 COMMENT 'Is this field included search form of profile?';
 279
 280    ALTER TABLE `civicrm_uf_field` ADD label varchar(255)    COMMENT 'To save label for fields.';
 281
 282    ALTER TABLE `civicrm_uf_field` ADD field_type varchar(255)    COMMENT 'This field saves field type (ie individual,household.. field etc).';
 283
 284
 285 -- /*******************************************************
 286 -- *
 287 -- * Modify the civicrm_uf_group  Table Structure
 288 -- *
 289 -- *******************************************************/
 290
 291    ALTER TABLE `civicrm_uf_group` ADD limit_listings_group_id int unsigned    COMMENT 'Group id, foriegn key from civicrm_group';
 292
 293    ALTER TABLE `civicrm_uf_group` ADD INDEX (`limit_listings_group_id`);
 294
 295    ALTER TABLE `civicrm_uf_group` ADD FOREIGN KEY (limit_listings_group_id) REFERENCES civicrm_group(id);
 296
 297    ALTER TABLE `civicrm_uf_group` ADD post_URL varchar(255)    COMMENT 'Redirect to URL.';
 298
 299
 300 -- /*******************************************************
 301 -- *
 302 -- * update civicrm_uf_field with the default values (also fix label)
 303 -- *
 304 -- *******************************************************/
 305
 306    UPDATE civicrm_uf_field SET is_searchable = 1 WHERE in_selector = 1 AND visibility = 'Public User Pages and Listings';
 307
 308    UPDATE civicrm_uf_field SET label=field_name, field_type='Individual';
 309
 310
 311 -- /*******************************************************
 312 -- *
 313 -- * Drop Old Tables
 314 -- *
 315 -- *******************************************************/
 316
 317    DROP TABLE IF EXISTS civicrm_module_profile;
 318
 319
 320 -- /*******************************************************
 321 -- *
 322 -- * update abbreviation for New Hampshire in case it is still wrong
 323 -- *
 324 -- *******************************************************/
 325
 326    UPDATE civicrm_state_province SET abbreviation = 'NH' WHERE id = 1028;
 327
 328
 329 -- /*******************************************************
 330 -- *
 331 -- * update the name of the Mexican state of Morelos
 332 -- *
 333 -- *******************************************************/
 334
 335    UPDATE civicrm_state_province SET name = 'Morelos' WHERE id = 3819;
 336
 337
 338 -- /*******************************************************
 339 -- *
 340 -- * update the custom fields set options per line if it is null
 341 -- *
 342 -- *******************************************************/
 343
 344    UPDATE civicrm_custom_field SET  options_per_line=0  WHERE options_per_line IS NULL;