crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/sql/civicrm_upgradedb_v1.3_v1.4_41.mysql

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