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

   1 {* file to handle db changes in 4.7.alpha1 during upgrade *}
   2
   3 -- Add new columns for multilingual purpose
   4 ALTER TABLE `civicrm_action_schedule` ADD COLUMN `filter_contact_language` varchar(128) DEFAULT NULL COMMENT 'Used for multilingual installation';
   5 ALTER TABLE `civicrm_action_schedule` ADD COLUMN `communication_language` varchar(8) DEFAULT NULL COMMENT 'Used for multilingual installation';
   6 ALTER TABLE `civicrm_action_schedule` MODIFY COLUMN mapping_id varchar(64);
   7 -- Q: Should we validate that local civicrm_action_mapping records have expected IDs?
   8
   9 -- CRM-16354
  10 SELECT @option_group_id_wysiwyg := max(id) from civicrm_option_group where name = 'wysiwyg_editor';
  11
  12 UPDATE civicrm_option_value SET name = 'Textarea', {localize field='label'}label = 'Textarea'{/localize}
  13   WHERE value = 1 AND option_group_id = @option_group_id_wysiwyg;
  14
  15 DELETE FROM civicrm_option_value WHERE name IN ('Joomla Default Editor', 'Drupal Default Editor')
  16   AND option_group_id = @option_group_id_wysiwyg;
  17
  18 UPDATE civicrm_option_value SET is_active = 1, is_reserved = 1 WHERE option_group_id = @option_group_id_wysiwyg;
  19
  20 --CRM-16719
  21 SELECT @option_group_id_report := max(id) from civicrm_option_group where name = 'report_template';
  22
  23 UPDATE civicrm_option_value SET {localize field="label"}label = 'Activity Details Report'{/localize}
  24   WHERE value = 'activity' AND option_group_id = @option_group_id_report;
  25
  26 UPDATE civicrm_option_value SET {localize field="label"}label = 'Activity Summary Report'{/localize}
  27   WHERE value = 'activitySummary' AND option_group_id = @option_group_id_report;
  28
  29 --CRM-11369
  30 UPDATE civicrm_participant_status_type SET {localize field='label'}label = 'Pending (pay later)'{/localize} WHERE name = 'Pending from pay later';
  31 UPDATE civicrm_participant_status_type SET {localize field='label'}label = 'Pending (incomplete transaction)'{/localize} WHERE name = 'Pending from incomplete transaction';
  32
  33 --CRM-16853 PCP Owner Notification
  34 --CRM-16853 Contribution Invoice Receipt Translation
  35
  36 {include file='../CRM/Upgrade/4.7.alpha1.msg_template/civicrm_msg_template.tpl'}
  37
  38 -- CRM-16478 Remove custom fatal error template path
  39 DELETE FROM civicrm_setting WHERE name = 'fatalErrorTemplate';
  40
  41 UPDATE civicrm_state_province SET name = 'Bataan' WHERE name = 'Batasn';
  42
  43 --CRM-16914
  44 ALTER TABLE civicrm_payment_processor
  45 ADD COLUMN
  46 `payment_instrument_id` int unsigned   DEFAULT 1 COMMENT 'Payment Instrument ID';
  47
  48 ALTER TABLE civicrm_payment_processor_type
  49 ADD COLUMN
  50 `payment_instrument_id` int unsigned   DEFAULT 1 COMMENT 'Payment Instrument ID';
  51
  52 -- CRM-16447
  53 UPDATE civicrm_state_province SET name = 'Northern Ostrobothnia' WHERE name = 'Nothern Ostrobothnia';
  54
  55 -- CRM-14078
  56 UPDATE civicrm_option_group SET {localize field="title"}title = '{ts escape="sql"}Payment Methods{/ts}'{/localize} WHERE name = 'payment_instrument';
  57 UPDATE civicrm_navigation SET label = '{ts escape="sql"}Payment Methods{/ts}' WHERE name = 'Payment Instruments';
  58
  59 -- CRM-16176
  60 {if $multilingual}
  61   {foreach from=$locales item=locale}
  62      ALTER TABLE civicrm_relationship_type ADD label_a_b_{$locale} varchar(64);
  63      ALTER TABLE civicrm_relationship_type ADD label_b_a_{$locale} varchar(64);
  64      ALTER TABLE civicrm_relationship_type ADD description_{$locale} varchar(255);
  65
  66      UPDATE civicrm_relationship_type SET label_a_b_{$locale} = label_a_b;
  67      UPDATE civicrm_relationship_type SET label_b_a_{$locale} = label_b_a;
  68      UPDATE civicrm_relationship_type SET description_{$locale} = description;
  69   {/foreach}
  70
  71   ALTER TABLE civicrm_relationship_type DROP label_a_b;
  72   ALTER TABLE civicrm_relationship_type DROP label_b_a;
  73   ALTER TABLE civicrm_relationship_type DROP description;
  74 {/if}
  75
  76 -- CRM-13283
  77 CREATE TABLE IF NOT EXISTS `civicrm_status_pref` (
  78      `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Status Preference ID',
  79      `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this Status Preference for',
  80      `name` varchar(255) NOT NULL   COMMENT 'Name of the status check this preference references.',
  81      `hush_until` date   DEFAULT NULL COMMENT 'expires ignore_severity.  NULL never hushes.',
  82      `ignore_severity` int unsigned   DEFAULT 1 COMMENT 'Hush messages up to and including this severity.',
  83      `prefs` varchar(255)    COMMENT 'These settings are per-check, and can\'t be compared across checks.',
  84      `check_info` varchar(255)    COMMENT 'These values are per-check, and can\'t be compared across checks.'
  85 ,
  86     PRIMARY KEY ( `id` )
  87
  88     ,     INDEX `UI_status_pref_name`(
  89         name
  90   )
  91
  92 ,          CONSTRAINT FK_civicrm_status_pref_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)
  93 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;
  94
  95 -- CRM-17005
  96 UPDATE civicrm_country SET name = 'PALESTINIAN TERRITORY' WHERE name = 'PALESTINIAN TERRITORY, OCCUPIED';
  97
  98 -- CRM-17145 update Activity detail data type
  99 ALTER TABLE `civicrm_activity` CHANGE `details` `details` LONGTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Details about the activity (agenda, notes, etc).';
 100
 101 -- CRM-17136 Added Punjab Province for Pakistan
 102 INSERT IGNORE INTO civicrm_state_province (id, country_id, abbreviation, name) VALUES
 103 (NULL, 1163, "PB", "Punjab");
 104
 105 -- CRM-16195: Move relative date filters from code to database
 106 INSERT INTO
 107    `civicrm_option_group` (`name`, {localize field='title'}`title`{/localize}, `is_reserved`, `is_active`, `is_locked`)
 108    VALUES
 109    ('relative_date_filters'         , {localize}'{ts escape="sql"}Relative Date Filters{/ts}'{/localize}              , 1, 1, 0);
 110
 111 SELECT @option_group_id_date_filter    := max(id) from civicrm_option_group where name = 'relative_date_filters';
 112
 113 INSERT INTO
 114    `civicrm_option_value` (`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`)
 115    VALUES
 116    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Today{/ts}'{/localize}, 'this.day', 'this.day', NULL, NULL, NULL,1, 0, 0, 1, NULL, NULL),
 117    (@option_group_id_date_filter, {localize}'{ts escape="sql"}This week{/ts}'{/localize}, 'this.week', 'this.week', NULL, NULL, NULL,2, 0, 0, 1, NULL, NULL),
 118    (@option_group_id_date_filter, {localize}'{ts escape="sql"}This calendar month{/ts}'{/localize}, 'this.month', 'this.month', NULL, NULL, NULL,3, 0, 0, 1, NULL, NULL),
 119    (@option_group_id_date_filter, {localize}'{ts escape="sql"}This quarter{/ts}'{/localize}, 'this.quarter', 'this.quarter', NULL, NULL, NULL,4, 0, 0, 1, NULL, NULL),
 120    (@option_group_id_date_filter, {localize}'{ts escape="sql"}This fiscal year{/ts}'{/localize}, 'this.fiscal_year', 'this.fiscal_year', NULL, NULL, NULL,5, 0, 0, 1, NULL, NULL),
 121    (@option_group_id_date_filter, {localize}'{ts escape="sql"}This calendar year{/ts}'{/localize}, 'this.year', 'this.year', NULL, NULL, NULL,6, 0, 0, 1, NULL, NULL),
 122    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Yesterday{/ts}'{/localize}, 'previous.day', 'previous.day', NULL, NULL, NULL,7, 0, 0, 1, NULL, NULL),
 123    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Previous week{/ts}'{/localize}, 'previous.week', 'previous.week', NULL, NULL, NULL,8, 0, 0, 1, NULL, NULL),
 124    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Previous calendar month{/ts}'{/localize}, 'previous.month', 'previous.month', NULL, NULL, NULL,9, 0, 0, 1, NULL, NULL),
 125    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Previous quarter{/ts}'{/localize}, 'previous.quarter', 'previous.quarter', NULL, NULL, NULL,10, 0, 0, 1, NULL, NULL),
 126    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Previous fiscal year{/ts}'{/localize}, 'previous.fiscal_year', 'previous.fiscal_year', NULL, NULL, NULL,11, 0, 0, 1, NULL, NULL),
 127    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Previous calendar year{/ts}'{/localize}, 'previous.year', 'previous.year', NULL, NULL, NULL,12, 0, 0, 1, NULL, NULL),
 128    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Last 7 days including today{/ts}'{/localize}, 'ending.week', 'ending.week', NULL, NULL, NULL,13, 0, 0, 1, NULL, NULL),
 129    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Last 30 days including today{/ts}'{/localize}, 'ending.month', 'ending.month', NULL, NULL, NULL,14, 0, 0, 1, NULL, NULL),
 130    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Last 60 days including today{/ts}'{/localize}, 'ending_2.month', 'ending_2.month', NULL, NULL, NULL,15, 0, 0, 1, NULL, NULL),
 131    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Last 90 days including today{/ts}'{/localize}, 'ending.quarter', 'ending.quarter', NULL, NULL, NULL,16, 0, 0, 1, NULL, NULL),
 132    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Last 12 months including today{/ts}'{/localize}, 'ending.year', 'ending.year', NULL, NULL, NULL,17, 0, 0, 1, NULL, NULL),
 133    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Last 2 years including today{/ts}'{/localize}, 'ending_2.year', 'ending_2.year', NULL, NULL, NULL,18, 0, 0, 1, NULL, NULL),
 134    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Last 3 years including today{/ts}'{/localize}, 'ending_3.year', 'ending_3.year', NULL, NULL, NULL,19, 0, 0, 1, NULL, NULL),
 135    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Tomorrow{/ts}'{/localize}, 'starting.day', 'starting.day', NULL, NULL, NULL,20, 0, 0, 1, NULL, NULL),
 136    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Next week{/ts}'{/localize}, 'next.week', 'next.week', NULL, NULL, NULL,21, 0, 0, 1, NULL, NULL),
 137    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Next calendar month{/ts}'{/localize}, 'next.month', 'next.month', NULL, NULL, NULL,22, 0, 0, 1, NULL, NULL),
 138    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Next quarter{/ts}'{/localize}, 'next.quarter', 'next.quarter', NULL, NULL, NULL,23, 0, 0, 1, NULL, NULL),
 139    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Next fiscal year{/ts}'{/localize}, 'next.fiscal_year', 'next.fiscal_year', NULL, NULL, NULL,24, 0, 0, 1, NULL, NULL),
 140    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Next calendar year{/ts}'{/localize}, 'next.year', 'next.year', NULL, NULL, NULL,25, 0, 0, 1, NULL, NULL),
 141    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Next 7 days including today{/ts}'{/localize}, 'starting.week', 'starting.week', NULL, NULL, NULL,26, 0, 0, 1, NULL, NULL),
 142    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Next 30 days including today{/ts}'{/localize}, 'starting.month', 'starting.month', NULL, NULL, NULL,27, 0, 0, 1, NULL, NULL),
 143    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Next 60 days including today{/ts}'{/localize}, 'starting_2.month', 'starting_2.month', NULL, NULL, NULL,28, 0, 0, 1, NULL, NULL),
 144    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Next 90 days including today{/ts}'{/localize}, 'starting.quarter', 'starting.quarter', NULL, NULL, NULL,29, 0, 0, 1, NULL, NULL),
 145    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Next 12 months including today{/ts}'{/localize}, 'starting.year', 'starting.year', NULL, NULL, NULL,30, 0, 0, 1, NULL, NULL),
 146    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Current week to-date{/ts}'{/localize}, 'current.week', 'current.week', NULL, NULL, NULL,31, 0, 0, 1, NULL, NULL),
 147    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Current calendar month to-date{/ts}'{/localize}, 'current.month', 'current.month', NULL, NULL, NULL,32, 0, 0, 1, NULL, NULL),
 148    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Current quarter to-date{/ts}'{/localize}, 'current.quarter', 'current.quarter', NULL, NULL, NULL,33, 0, 0, 1, NULL, NULL),
 149    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Current calendar year to-date{/ts}'{/localize}, 'current.year', 'current.year', NULL, NULL, NULL,34, 0, 0, 1, NULL, NULL),
 150    (@option_group_id_date_filter, {localize}'{ts escape="sql"}To end of yesterday{/ts}'{/localize}, 'earlier.day', 'earlier.day', NULL, NULL, NULL,35, 0, 0, 1, NULL, NULL),
 151    (@option_group_id_date_filter, {localize}'{ts escape="sql"}To end of previous week{/ts}'{/localize}, 'earlier.week', 'earlier.week', NULL, NULL, NULL,36, 0, 0, 1, NULL, NULL),
 152    (@option_group_id_date_filter, {localize}'{ts escape="sql"}To end of previous calendar month{/ts}'{/localize}, 'earlier.month', 'earlier.month', NULL, NULL, NULL,37, 0, 0, 1, NULL, NULL),
 153    (@option_group_id_date_filter, {localize}'{ts escape="sql"}To end of previous quarter{/ts}'{/localize}, 'earlier.quarter', 'earlier.quarter', NULL, NULL, NULL,38, 0, 0, 1, NULL, NULL),
 154    (@option_group_id_date_filter, {localize}'{ts escape="sql"}To end of previous calendar year{/ts}'{/localize}, 'earlier.year', 'earlier.year', NULL, NULL, NULL,39, 0, 0, 1, NULL, NULL),
 155    (@option_group_id_date_filter, {localize}'{ts escape="sql"}From start of current day{/ts}'{/localize}, 'greater.day', 'greater.day', NULL, NULL, NULL,40, 0, 0, 1, NULL, NULL),
 156    (@option_group_id_date_filter, {localize}'{ts escape="sql"}From start of current week{/ts}'{/localize}, 'greater.week', 'greater.week', NULL, NULL, NULL,41, 0, 0, 1, NULL, NULL),
 157    (@option_group_id_date_filter, {localize}'{ts escape="sql"}From start of current calendar month{/ts}'{/localize}, 'greater.month', 'greater.month', NULL, NULL, NULL,42, 0, 0, 1, NULL, NULL),
 158    (@option_group_id_date_filter, {localize}'{ts escape="sql"}From start of current quarter{/ts}'{/localize}, 'greater.quarter', 'greater.quarter', NULL, NULL, NULL,43, 0, 0, 1, NULL, NULL),
 159    (@option_group_id_date_filter, {localize}'{ts escape="sql"}From start of current calendar year{/ts}'{/localize}, 'greater.year', 'greater.year', NULL, NULL, NULL,44, 0, 0, 1, NULL, NULL),
 160    (@option_group_id_date_filter, {localize}'{ts escape="sql"}To end of current week{/ts}'{/localize}, 'less.week', 'less.week', NULL, NULL, NULL,45, 0, 0, 1, NULL, NULL),
 161    (@option_group_id_date_filter, {localize}'{ts escape="sql"}To end of current calendar month{/ts}'{/localize}, 'less.month', 'less.month', NULL, NULL, NULL,46, 0, 0, 1, NULL, NULL),
 162    (@option_group_id_date_filter, {localize}'{ts escape="sql"}To end of current quarter{/ts}'{/localize}, 'less.quarter', 'less.quarter', NULL, NULL, NULL,47, 0, 0, 1, NULL, NULL),
 163    (@option_group_id_date_filter, {localize}'{ts escape="sql"}To end of current calendar year{/ts}'{/localize}, 'less.year', 'less.year', NULL, NULL, NULL,48, 0, 0, 1, NULL, NULL),
 164    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Previous 2 days{/ts}'{/localize}, 'previous_2.day', 'previous_2.day', NULL, NULL, NULL,49, 0, 0, 1, NULL, NULL),
 165    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Previous 2 weeks{/ts}'{/localize}, 'previous_2.week', 'previous_2.week', NULL, NULL, NULL,50, 0, 0, 1, NULL, NULL),
 166    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Previous 2 calendar months{/ts}'{/localize}, 'previous_2.month', 'previous_2.month', NULL, NULL, NULL,51, 0, 0, 1, NULL, NULL),
 167    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Previous 2 quarters{/ts}'{/localize}, 'previous_2.quarter', 'previous_2.quarter', NULL, NULL, NULL,52, 0, 0, 1, NULL, NULL),
 168    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Previous 2 calendar years{/ts}'{/localize}, 'previous_2.year', 'previous_2.year', NULL, NULL, NULL,53, 0, 0, 1, NULL, NULL),
 169    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Day prior to yesterday{/ts}'{/localize}, 'previous_before.day', 'previous_before.day', NULL, NULL, NULL,54, 0, 0, 1, NULL, NULL),
 170    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Week prior to previous week{/ts}'{/localize}, 'previous_before.week', 'previous_before.week', NULL, NULL, NULL,55, 0, 0, 1, NULL, NULL),
 171    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Month prior to previous calendar month{/ts}'{/localize}, 'previous_before.month', 'previous_before.month', NULL, NULL, NULL,56, 0, 0, 1, NULL, NULL),
 172    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Quarter prior to previous quarter{/ts}'{/localize}, 'previous_before.quarter', 'previous_before.quarter', NULL, NULL, NULL,57, 0, 0, 1, NULL, NULL),
 173    (@option_group_id_date_filter, {localize}'{ts escape="sql"}Year prior to previous calendar year{/ts}'{/localize}, 'previous_before.year', 'previous_before.year', NULL, NULL, NULL,58, 0, 0, 1, NULL, NULL),
 174    (@option_group_id_date_filter, {localize}'{ts escape="sql"}From end of previous week{/ts}'{/localize}, 'greater_previous.week', 'greater_previous.week', NULL, NULL, NULL,59, 0, 0, 1, NULL, NULL),
 175    (@option_group_id_date_filter, {localize}'{ts escape="sql"}From end of previous calendar month{/ts}'{/localize}, 'greater_previous.month', 'greater_previous.month', NULL, NULL, NULL,60, 0, 0, 1, NULL, NULL),
 176    (@option_group_id_date_filter, {localize}'{ts escape="sql"}From end of previous quarter{/ts}'{/localize}, 'greater_previous.quarter', 'greater_previous.quarter', NULL, NULL, NULL,61, 0, 0, 1, NULL, NULL),
 177    (@option_group_id_date_filter, {localize}'{ts escape="sql"}From end of previous calendar year{/ts}'{/localize}, 'greater_previous.year', 'greater_previous.year', NULL, NULL, NULL,62, 0, 0, 1, NULL, NULL);
 178
 179 -- CRM-16873
 180 {if $multilingual}
 181   {foreach from=$locales item=loc}
 182      ALTER TABLE civicrm_contribution_page DROP for_organization_{$loc};
 183   {/foreach}
 184 {else}
 185      ALTER TABLE civicrm_contribution_page DROP for_organization;
 186 {/if}
 187 ALTER TABLE civicrm_contribution_page DROP is_for_organization;