CRM/Upgrade/Incremental/sql/3.1.alpha1.mysql.tpl

   1 --  CRM-4795
   2 --  modify type fee_level of civicrm_participant and amount_level of civicrm_contribution
   3
   4     ALTER TABLE civicrm_participant MODIFY column fee_level text collate utf8_unicode_ci default NULL COMMENT 'Populate with the label (text) associated with a fee level for paid events with multiple levels. Note that we store the label value and not the key';
   5
   6     ALTER TABLE civicrm_contribution MODIFY column amount_level text collate utf8_unicode_ci default NULL;
   7
   8 --  subtype upgrade TODOs:
   9 --  make changes for CRM-4970
  10
  11 --  modify contact_type column definition
  12     ALTER TABLE  `civicrm_contact` MODIFY column contact_type varchar(64) collate utf8_unicode_ci DEFAULT NULL COMMENT 'Type of Contact';
  13
  14 --  add table definiton and data for civicrm_contact_type table
  15     CREATE TABLE civicrm_contact_type (
  16         id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contact Type ID',
  17         name varchar(64)    COMMENT 'Internal name of Contact Type (or Subtype).',
  18         {localize field='label'}label varchar(64)       COMMENT ' localized Name of Contact Type.'{/localize},
  19         {localize field='description'}description text  COMMENT ' localized Optional verbose description of the type.'{/localize},
  20         image_URL varchar(255)  COMMENT 'URL of image if any.',
  21         parent_id int unsigned  COMMENT 'Optional FK to parent contact type.',
  22         is_active tinyint       COMMENT 'Is this entry active?',
  23         is_reserved tinyint     COMMENT 'Is this contact type a predefined system type',
  24         PRIMARY KEY ( id ),
  25         UNIQUE INDEX contact_type(name),
  26         CONSTRAINT FK_civicrm_contact_type_parent_id FOREIGN KEY (parent_id) REFERENCES civicrm_contact_type(id)
  27     )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
  28
  29     INSERT INTO civicrm_contact_type
  30         ( id, name,{localize field='label'}`label`{/localize},image_URL, parent_id, is_active,is_reserved )
  31     VALUES
  32         (1, 'Individual',    {localize}'Individual'{/localize},  NULL, NULL, 1,1),
  33         (2, 'Household',     {localize}'Household'{/localize},   NULL, NULL, 1,1),
  34         (3, 'Organization',  {localize}'Organization'{/localize},NULL, NULL, 1,1);
  35
  36
  37 --  CRM-5218
  38 --  added menu for contact types in navigation
  39     SELECT @domain_id   := min(id) FROM civicrm_domain;
  40     SELECT @nav_ol      := id FROM civicrm_navigation WHERE name = 'Option Lists';
  41     SELECT @nav_ol_wt   := max(weight) from civicrm_navigation WHERE parent_id = @nav_ol;
  42     INSERT INTO `civicrm_navigation`
  43         ( domain_id, url, label, name,permission, permission_operator, parent_id, is_active, has_separator, weight )
  44     VALUES
  45         (  @domain_id,'civicrm/admin/options/subtype&reset=1', 'Contact Types', 'Contact Types', 'administer CiviCRM', '', @nav_ol, '1', NULL, @nav_ol_wt+1 );
  46
  47 --  make changes for CRM-5100
  48     ALTER TABLE `civicrm_relationship_type`
  49         ADD `contact_sub_type_a` varchar(64) collate utf8_unicode_ci DEFAULT NULL AFTER `contact_type_b`,
  50         ADD `contact_sub_type_b` varchar(64) collate utf8_unicode_ci DEFAULT NULL AFTER `contact_sub_type_a`;
  51
  52 --  Upgrade FCKEditor to CKEditor CRM-5226
  53     {if $multilingual}
  54         {foreach from=$locales item=locale}
  55         UPDATE civicrm_option_value SET label_{$locale} ='CKEditor' WHERE label_{$locale} = 'FCKEditor';
  56         {/foreach}
  57     {else}
  58         UPDATE civicrm_option_value SET label ='CKEditor' WHERE label = 'FCKEditor';
  59     {/if}
  60 --  CRM-5106
  61 --  Added Autocomplete search options in civicrm_preferences 'Admin Search Settings' form
  62
  63     ALTER TABLE `civicrm_preferences`
  64         ADD `contact_autocomplete_options` VARCHAR( 255 ) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'What Autocomplete has to return';
  65
  66 --  Added default value checked for sort_name and email
  67     UPDATE `civicrm_preferences` SET `contact_autocomplete_options` = '\ 11\ 12\ 1' WHERE `civicrm_preferences`.`id` =1 LIMIT 1;
  68
  69 --  Insert values for option group
  70     INSERT INTO
  71         `civicrm_option_group` (`name`, {localize field='description'}`description`{/localize}, `is_reserved`, `is_active`)
  72     VALUES
  73         ('contact_autocomplete_options', {localize}'Autocomplete Contact Search'{/localize}   , 0, 1);
  74
  75     SELECT @option_group_id_acsOpt := max(id) from civicrm_option_group where name = 'contact_autocomplete_options';
  76
  77     INSERT INTO `civicrm_option_value`
  78         (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`)
  79     VALUES
  80         (@option_group_id_acsOpt, {localize}'Email Address'{/localize}  , 2, 'email',           NULL, 0, NULL, 2,  0, 0, 1, NULL, NULL),
  81         (@option_group_id_acsOpt, {localize}'Phone'{/localize}          , 3, 'phone',           NULL, 0, NULL, 3,  0, 0, 1, NULL, NULL),
  82         (@option_group_id_acsOpt, {localize}'Street Address'{/localize} , 4, 'street_address',  NULL, 4, NULL, 0,  0, 0, 1, NULL, NULL),
  83         (@option_group_id_acsOpt, {localize}'City'{/localize}           , 5, 'city',            NULL, 0, NULL, 5,  0, 0, 1, NULL, NULL),
  84         (@option_group_id_acsOpt, {localize}'State/Province'{/localize} , 6, 'state_province',  NULL, 6, NULL, 0,  0, 0, 1, NULL, NULL),
  85         (@option_group_id_acsOpt, {localize}'Country'{/localize}        , 7, 'country',         NULL, 0, NULL, 7,  0, 0, 1, NULL, NULL);
  86
  87 --  CRM-5095
  88     ALTER TABLE `civicrm_price_set`
  89         ADD `extends` VARCHAR( 255 ) COLLATE utf8_unicode_ci NOT NULL COMMENT 'What components are using this price set?';
  90
  91     UPDATE `civicrm_price_set` SET extends = (select id from civicrm_component where name = 'CiviEvent');
  92
  93 --  CRM-4160
  94     ALTER TABLE `civicrm_acl`
  95         MODIFY `operation` enum('All', 'View', 'Edit', 'Create', 'Delete', 'Grant', 'Revoke', 'Search') COLLATE utf8_unicode_ci NOT NULL   COMMENT 'What operation does this ACL entry control?';
  96
  97 --  CRM-5285
  98     UPDATE civicrm_state_province SET name = 'Haifa' where id = 3115;
  99
 100 --  CRM-5287
 101     UPDATE civicrm_state_province SET name = 'Jerusalem' where id = 3117;
 102
 103 --  CRM-5224
 104     /* Set references to obsolete UK counties to NULL */
 105     UPDATE `civicrm_address` SET `state_province_id` = NULL WHERE `state_province_id` IN
 106         ('2596', '2599', '2600', '2601', '2602', '2603', '2604', '2605', '2607', '2608', '2609', '2610', '2611',
 107         '2613', '2614', '2615', '2616', '2617', '2618', '2619', '2621', '2623', '2624', '2625', '2627', '2628',
 108         '2629', '2630', '2631', '2632', '2633', '2636', '2637', '2638', '2640', '2641', '2642', '2644', '2645',
 109         '2646', '2650', '2653', '2656', '2658', '2667', '2672', '2673', '2676', '2677', '2679', '2680', '2681',
 110         '2683', '2684', '2685', '2686', '2690', '2691', '2693', '2695', '2696', '2697', '2698', '2700', '2701',
 111         '2701', '2702', '2703', '2704', '2706', '2707', '2708', '2710', '2711', '2713', '2714', '2716', '2717',
 112         '2719', '2720', '2721', '2722', '2724', '2725', '2727', '2728', '2729', '2730', '2731', '2732', '2733',
 113         '2736', '2737', '2739', '2740', '2741', '2745', '2751', '2753', '2754', '2755', '2756', '2758', '2759',
 114         '2760', '2762', '2763', '2764', '2765', '2767', '2768', '2769', '2771', '2772', '2775', '2776', '2781',
 115         '2782', '2783', '2784', '2787', '2788', '2789', '2790', '2792', '2794', '2795', '2796', '2797', '2798',
 116         '2799', '2800', '2801', '2802', '2803', '2805', '2806', '2807', '2808', '2809', '2810', '2816', '2817',
 117         '2819', '2820', '2821', '2822', '2824', '2825', '9987', '9995', '9996', '9997', '2812', '2718', '2715' );
 118
 119     /* Delete obsolete UK counties */
 120     DELETE FROM `civicrm_state_province` WHERE `id` IN
 121         ('2596', '2599', '2600', '2601', '2602', '2603', '2604', '2605', '2607', '2608', '2609', '2610', '2611',
 122         '2613', '2614', '2615', '2616', '2617', '2618', '2619', '2621', '2623', '2624', '2625', '2627', '2628',
 123         '2629', '2630', '2631', '2632', '2633', '2636', '2637', '2638', '2640', '2641', '2642', '2644', '2645',
 124         '2646', '2650', '2653', '2656', '2658', '2667', '2672', '2673', '2676', '2677', '2679', '2680', '2681',
 125         '2683', '2684', '2685', '2686', '2690', '2691', '2693', '2695', '2696', '2697', '2698', '2700', '2701',
 126         '2701', '2702', '2703', '2704', '2706', '2707', '2708', '2710', '2711', '2713', '2714', '2716', '2717',
 127         '2719', '2720', '2721', '2722', '2724', '2725', '2727', '2728', '2729', '2730', '2731', '2732', '2733',
 128         '2736', '2737', '2739', '2740', '2741', '2745', '2751', '2753', '2754', '2755', '2756', '2758', '2759',
 129         '2760', '2762', '2763', '2764', '2765', '2767', '2768', '2769', '2771', '2772', '2775', '2776', '2781',
 130         '2782', '2783', '2784', '2787', '2788', '2789', '2790', '2792', '2794', '2795', '2796', '2797', '2798',
 131         '2799', '2800', '2801', '2802', '2803', '2805', '2806', '2807', '2808', '2809', '2810', '2816', '2817',
 132         '2819', '2820', '2821', '2822', '2824', '2825', '9987', '9995', '9996', '9997', '2812', '2718', '2715' );
 133
 134     /* Update the names of several existing UK counties */
 135     UPDATE `civicrm_state_province` SET `name`='Gwent'              WHERE `id`='2612';
 136     UPDATE `civicrm_state_province` SET `name`='Bristol, City of'   WHERE `id`='2620';
 137     UPDATE `civicrm_state_province` SET `name`='Co Londonderry'     WHERE `id`='2648';
 138     UPDATE `civicrm_state_province` SET `name`='Na h-Eileanan Siar' WHERE `id`='2666';
 139     UPDATE `civicrm_state_province` SET `name`='Glasgow City'       WHERE `id`='2674';
 140     UPDATE `civicrm_state_province` SET `name`='Mid Glamorgan'      WHERE `id`='2804';
 141     UPDATE `civicrm_state_province` SET `name`='Greater London'     WHERE `id`='9999';
 142     UPDATE `civicrm_state_province` SET `name`='County Durham'      WHERE `id`='2657';
 143
 144     /* Create additional UK counties */
 145     INSERT INTO `civicrm_state_province`
 146         (id, `name`, `abbreviation`, `country_id`)
 147     VALUES
 148         (10013, 'Clwyd',             'CLD', 1226),
 149         (10014, 'Dyfed',             'DFD', 1226),
 150         (10015, 'South Glamorgan',   'GNS', 1226);
 151
 152 --  CRM-5288
 153     SELECT @domain_id := min(id) FROM civicrm_domain;
 154     SELECT @nav_contrbutionID    := id FROM civicrm_navigation WHERE name = 'Contributions';
 155     SELECT @nav_contribution_wt  := max(weight) from civicrm_navigation WHERE parent_id = @nav_contrbutionID;
 156
 157     UPDATE civicrm_navigation
 158         SET has_separator = 1
 159         WHERE civicrm_navigation.parent_id= @nav_contrbutionID AND civicrm_navigation.weight = @nav_contribution_wt;
 160
 161     INSERT INTO civicrm_navigation
 162         ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 163     VALUES
 164         ( @domain_id, 'civicrm/admin/price&reset=1&action=add', '{ts escape="sql"}New Price Set{/ts}',     'New Price Set',     'access CiviContribute,administer CiviCRM', 'AND',  @nav_contrbutionID, '1', NULL, @nav_contribution_wt+1 ),
 165         ( @domain_id, 'civicrm/admin/price&reset=1',            '{ts escape="sql"}Manage Price Sets{/ts}', 'Manage Price Sets', 'access CiviContribute,administer CiviCRM', 'AND',  @nav_contrbutionID, '1', NULL, @nav_contribution_wt+2 );
 166
 167
 168     SELECT @nav_contrbutionID_admin    := id FROM civicrm_navigation WHERE name = 'CiviContribute';
 169     SELECT @nav_contribution_wt_admin  := max(weight) from civicrm_navigation WHERE parent_id = @nav_contrbutionID_admin;
 170
 171     UPDATE civicrm_navigation
 172         SET has_separator = 1
 173         WHERE civicrm_navigation.parent_id= @nav_contrbutionID_admin AND civicrm_navigation.weight = @nav_contribution_wt_admin;
 174
 175     INSERT INTO civicrm_navigation
 176         ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 177     VALUES
 178         ( @domain_id, 'civicrm/admin/price&reset=1&action=add', '{ts escape="sql"}New Price Set{/ts}',     'New Price Set',     'access CiviContribute,administer CiviCRM', 'AND',  @nav_contrbutionID_admin, '1', NULL, @nav_contribution_wt_admin+1 ),
 179         ( @domain_id, 'civicrm/admin/price&reset=1',            '{ts escape="sql"}Manage Price Sets{/ts}', 'Manage Price Sets', 'access CiviContribute,administer CiviCRM', 'AND',  @nav_contrbutionID_admin, '1', NULL, @nav_contribution_wt_admin+2 );
 180
 181
 182     SELECT @nav_customize_admin    := id FROM civicrm_navigation WHERE name = 'Customize';
 183     SELECT @nav_customize_wt_admin := max(weight) from civicrm_navigation WHERE parent_id = @nav_customize_admin;
 184     INSERT INTO civicrm_navigation
 185         ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
 186     VALUES
 187         ( @domain_id, 'civicrm/admin/price&reset=1', '{ts escape="sql"}Price Sets{/ts}', 'Price Sets', 'administer CiviCRM', '',  @nav_customize_admin, '1', NULL, @nav_customize_wt_admin+1 );
 188
 189     UPDATE civicrm_navigation
 190         SET url = 'civicrm/admin/price&reset=1'
 191         WHERE civicrm_navigation.url = 'civicrm/event/price&reset=1';
 192
 193     CREATE TABLE civicrm_acl_contact_cache (
 194         id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
 195         user_id int unsigned    COMMENT 'FK to civicrm_contact (could be null for anon user)',
 196         contact_id int unsigned NOT NULL   COMMENT 'FK to civicrm_contact',
 197         operation enum('All', 'View', 'Edit', 'Create', 'Delete', 'Grant', 'Revoke') NOT NULL   COMMENT 'What operation does this user have permission on?'
 198         ,
 199         PRIMARY KEY ( id ) ,
 200         UNIQUE INDEX UI_user_contact_operation(user_id, contact_id, operation ) ,
 201         CONSTRAINT FK_civicrm_acl_contact_cache_user_id FOREIGN KEY (user_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE,
 202         CONSTRAINT FK_civicrm_acl_contact_cache_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE
 203     )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
 204
 205 -- CRM-4690
 206     INSERT INTO civicrm_payment_processor_type
 207         ( name, title, description, is_active, is_default, user_name_label, password_label, signature_label, subject_label,  class_name, url_site_default, url_api_default, url_recur_default, url_button_default, url_site_test_default, url_api_test_default, url_recur_test_default, url_button_test_default, billing_mode, is_recur, payment_type)
 208     VALUES
 209         ( 'Realex', 'Realex Payment', NULL, 1, 0, 'Merchant ID', 'Password', NULL, 'Account', 'Payment_Realex', 'https://epage.payandshop.com/epage.cgi', NULL, NULL, NULL, 'https://epage.payandshop.com/epage-remote.cgi', NULL, NULL, NULL, 1, 0, 1);
 210
 211 -- CRM-4802
 212     UPDATE civicrm_payment_processor_type
 213         SET url_recur_default      = 'https://www.paypal.com/',
 214             url_recur_test_default = 'https://www.sandbox.paypal.com/',
 215             is_recur = 1
 216         WHERE name = 'PayPal';
 217
 218     UPDATE civicrm_payment_processor
 219         SET is_recur  = 1,
 220             url_recur = 'https://www.paypal.com/'
 221         WHERE payment_processor_type = 'PayPal' AND is_test = 0;
 222
 223     UPDATE civicrm_payment_processor
 224         SET is_recur  = 1,
 225             url_recur = 'https://www.sandbox.paypal.com/'
 226         WHERE payment_processor_type = 'PayPal' AND is_test = 1;
 227
 228 --  CRM-5313
 229 --  migrate the contribution id's to participant id's in lineitem table
 230     UPDATE civicrm_line_item AS li
 231         LEFT JOIN civicrm_participant_payment AS pp ON (pp.contribution_id = li.entity_id)
 232         SET li.entity_id    = pp.participant_id,
 233             li.entity_table = 'civicrm_participant'
 234         WHERE pp.contribution_id = li.entity_id AND
 235               li.entity_table    = 'civicrm_contribution';
 236
 237 --  CRM-5317
 238 --  copy name to value, since we want to use value instead of name.
 239     UPDATE  civicrm_option_value as vals
 240         LEFT  JOIN  civicrm_option_group as groups ON ( groups.id = vals.option_group_id )
 241         INNER JOIN  civicrm_price_field fields ON ( groups.name = CONCAT( 'civicrm_price_field.amount.', fields.id ) )
 242         SET  vals.value=vals.name;
 243
 244 --  CRM-5244
 245     ALTER TABLE civicrm_mail_settings
 246         ADD `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this match entry for' AFTER id;
 247
 248     UPDATE civicrm_mail_settings SET domain_id = @domain_id;
 249
 250 -- Date migration CRM-4312
 251     ALTER TABLE civicrm_custom_field
 252         CHANGE date_parts date_format VARCHAR( 64 )  NULL DEFAULT NULL COMMENT 'date format for custom date',
 253         ADD time_format int unsigned  COMMENT 'time format for custom date' AFTER date_format;
 254
 255     ALTER TABLE `civicrm_preferences_date`
 256         CHANGE `format` `date_format` VARCHAR( 64 )  NULL DEFAULT NULL COMMENT 'The date type',
 257         ADD `time_format` VARCHAR( 64 ) NULL DEFAULT NULL COMMENT 'store time format' AFTER `date_format`;
 258         ALTER TABLE `civicrm_preferences_date`
 259             DROP `minute_increment`;
 260
 261     DELETE FROM civicrm_preferences_date WHERE name IN ('datetime','duration','fixed','manual' );
 262
 263     UPDATE civicrm_preferences_date
 264         SET date_format = ''
 265         WHERE name IN ( 'activityDate', 'activityDatetime', 'birth', 'custom', 'mailing', 'relative' );
 266
 267     UPDATE civicrm_preferences_date SET name = 'searchDate' WHERE name = 'relative';
 268     UPDATE civicrm_preferences_date SET name = 'activityDateTime' WHERE name = 'activityDatetime';
 269     UPDATE civicrm_preferences_date SET time_format = 1 WHERE name = 'activityDatetime';
 270 --  CRM-5263
 271     ALTER TABLE civicrm_country
 272   ADD is_province_abbreviated tinyint(4) default '0' COMMENT 'Should state/province be displayed as abbreviation for contacts from this country?';
 273
 274 -- message templates, CRM-3507
 275     ALTER TABLE civicrm_msg_template
 276         CHANGE msg_subject msg_subject TEXT NULL COMMENT 'Subject for email message.',
 277         ADD workflow_id int(10) unsigned default NULL COMMENT 'a pseudo-FK to civicrm_option_value AFTER is_active',
 278         ADD is_default  tinyint(4) default '1'  COMMENT 'is this the default message template for the workflow referenced by workflow_id?' AFTER workflow_id,
 279         ADD is_reserved tinyint(4) default NULL COMMENT 'is this the reserved message template which we ship for the workflow referenced by workflow_id?' AFTER is_default;
 280
 281     {include file='../CRM/Upgrade/3.1.alpha1.msg_template/civicrm_msg_template.tpl'}