CRM/Upgrade/Incremental/sql/3.2.alpha3.mysql.tpl

   1 -- CRM-6228
   2
   3 {include file='../CRM/Upgrade/3.2.alpha3.msg_template/civicrm_msg_template.tpl'}
   4
   5 -- CRM-6144
   6    UPDATE civicrm_option_value
   7 LEFT JOIN civicrm_option_group ON ( civicrm_option_value.option_group_id = civicrm_option_group.id )
   8       SET civicrm_option_value.is_reserved = 1, civicrm_option_value.is_active = 0
   9     WHERE civicrm_option_group.name = 'activity_type'
  10       AND civicrm_option_value.name = 'Close Case';
  11
  12 -- CRM-6102
  13 ALTER TABLE civicrm_preferences
  14     ADD sort_name_format TEXT COMMENT 'Format to display contact sort name' AFTER mailing_format,
  15     ADD display_name_format TEXT COMMENT 'Format to display the contact display name' AFTER  mailing_format;
  16
  17 UPDATE civicrm_preferences
  18     SET display_name_format = '{literal}{contact.individual_prefix}{ }{contact.first_name}{ }{contact.last_name}{ }{contact.individual_suffix}{/literal}',
  19         sort_name_format    = '{literal}{contact.last_name}{, }{contact.first_name}{/literal}'
  20     WHERE is_domain = 1;
  21
  22 -- CRM-1496
  23    INSERT INTO
  24    `civicrm_option_group` (`name`, {localize field='description'}`description`{/localize}, `is_reserved`, `is_active`)
  25 VALUES
  26     ('currencies_enabled',{localize}'{ts escape="sql"}List of currencies enabled for this site{/ts}'{/localize}, 0, 1);
  27
  28 -- INSERT Default currency
  29    SELECT @option_group_id_currency       := max(id) from civicrm_option_group where name = 'currencies_enabled';
  30    INSERT INTO
  31    `civicrm_option_value` (`option_group_id`, {localize field='label'}`label`{/localize}, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, {localize field='description'}`description`{/localize}, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `visibility_id`)
  32    VALUES
  33    (@option_group_id_currency, {localize}'{ts escape="sql"}USD ($){/ts}'{/localize}, 'USD', 'USD',  NULL, 0, 1, 1, {localize} NULL{/localize} , 0, 0, 1, NULL, NULL);
  34
  35
  36 -- CRM-1496
  37
  38 -- add currency field, set it to default value and modify it to not null
  39 -- civicrm_contribution_recur
  40    ALTER TABLE `civicrm_contribution_recur` ADD COLUMN `currency` varchar(3) NULL COMMENT '3 character string, value from config setting or input via user.';
  41    UPDATE `civicrm_contribution_recur` SET `currency` = '{$config->defaultCurrency}';
  42    ALTER TABLE `civicrm_contribution_recur` MODIFY COLUMN `currency` varchar(3) NOT NULL COMMENT '3 character string, value from config setting or input via user.';
  43 -- civicrm_grant
  44    ALTER TABLE `civicrm_grant` ADD COLUMN `currency` varchar(3) NULL COMMENT '3 character string, value from config setting or input via user.';
  45    UPDATE `civicrm_grant` SET `currency` = '{$config->defaultCurrency}';
  46    ALTER TABLE `civicrm_grant` MODIFY COLUMN `currency` varchar(3) NOT NULL COMMENT '3 character string, value from config setting or input via user.';
  47 -- civicrm_entity_financial_trxn
  48    ALTER TABLE `civicrm_entity_financial_trxn` ADD COLUMN `currency` varchar(3) NULL COMMENT '3 character string, value from config setting or input via user.';
  49    UPDATE `civicrm_entity_financial_trxn` SET `currency` = '{$config->defaultCurrency}';
  50    ALTER TABLE `civicrm_entity_financial_trxn` MODIFY COLUMN `currency` varchar(3) NOT NULL COMMENT '3 character string, value from config setting or input via user.';
  51 -- civicrm_product
  52    ALTER TABLE `civicrm_product` ADD COLUMN `currency` varchar(3) NULL COMMENT '3 character string, value from config setting or input via user.';
  53    UPDATE `civicrm_product` SET `currency` = '{$config->defaultCurrency}';
  54    ALTER TABLE `civicrm_product` MODIFY COLUMN `currency` varchar(3) NOT NULL COMMENT '3 character string, value from config setting or input via user.';
  55 -- civicrm_pcp
  56    ALTER TABLE `civicrm_pcp` ADD COLUMN `currency` varchar(3) NULL COMMENT '3 character string, value from config setting or input via user.';
  57    UPDATE `civicrm_pcp` SET `currency` = '{$config->defaultCurrency}';
  58    ALTER TABLE `civicrm_pcp` MODIFY COLUMN `currency` varchar(3) NOT NULL COMMENT '3 character string, value from config setting or input via user.';
  59 -- civicrm_pledge
  60    ALTER TABLE `civicrm_pledge` ADD COLUMN `currency` varchar(3) NULL COMMENT '3 character string, value from config setting or input via user.';
  61    UPDATE `civicrm_pledge` SET `currency` = '{$config->defaultCurrency}';
  62    ALTER TABLE `civicrm_pledge` MODIFY COLUMN `currency` varchar(3) NOT NULL COMMENT '3 character string, value from config setting or input via user.';
  63 -- civicrm_contribution_soft
  64    ALTER TABLE `civicrm_contribution_soft` ADD COLUMN `currency` varchar(3) NULL COMMENT '3 character string, value from config setting or input via user.';
  65    UPDATE `civicrm_contribution_soft` SET `currency` = '{$config->defaultCurrency}';
  66    ALTER TABLE `civicrm_contribution_soft` MODIFY COLUMN `currency` varchar(3) NOT NULL COMMENT '3 character string, value from config setting or input via user.';
  67 -- civicrm_pledge_payment
  68    ALTER TABLE `civicrm_pledge_payment` ADD COLUMN `currency` varchar(3) NULL COMMENT '3 character string, value from config setting or input via user.';
  69    UPDATE `civicrm_pledge_payment` SET `currency` = '{$config->defaultCurrency}';
  70    ALTER TABLE `civicrm_pledge_payment` MODIFY COLUMN `currency` varchar(3) NOT NULL COMMENT '3 character string, value from config setting or input via user.';
  71
  72 -- Fixing length of currency VARCHAR(64) to VARCHAR(3)
  73 -- civicrm_financial_trxn
  74    ALTER TABLE `civicrm_financial_trxn` MODIFY `currency` VARCHAR(3);
  75 -- civicrm_contribution
  76    ALTER TABLE `civicrm_contribution` MODIFY `currency` VARCHAR(3);
  77 -- civicrm_participant
  78    UPDATE `civicrm_participant` SET `fee_currency` = '{$config->defaultCurrency}' WHERE `fee_currency` IS NULL;
  79    ALTER TABLE `civicrm_participant` MODIFY `fee_currency` VARCHAR(3) NOT NULL;
  80
  81
  82 -- CRM-6138
  83 {include file='../CRM/Upgrade/3.2.alpha3.languages/languages.tpl'}
  84
  85 ALTER TABLE `civicrm_contact`
  86   ADD COLUMN `preferred_language` varchar(5) DEFAULT NULL COMMENT 'Which language is preferred for communication. FK to languages in civicrm_option_value.';
  87
  88 -- CRM-3854
  89 ALTER TABLE `civicrm_country`
  90   ADD COLUMN `address_format_id` int(10) unsigned DEFAULT NULL COMMENT 'Format to display the address, country specific';
  91
  92 CREATE TABLE `civicrm_address_format` (
  93   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  94   `format` text COLLATE utf8_unicode_ci DEFAULT NULL,
  95   PRIMARY KEY (`id`)
  96 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  97
  98 -- CRM-6154
  99    ALTER TABLE civicrm_domain
 100       ADD `locale_custom_strings` text COLLATE utf8_unicode_ci COMMENT 'String Overrides';
 101
 102 -- CRM-6181
 103 UPDATE `civicrm_contact` SET `is_deleted` = 0 WHERE `is_deleted` IS NULL;
 104 ALTER TABLE `civicrm_contact` MODIFY COLUMN `is_deleted` boolean NOT NULL DEFAULT 0;
 105
 106 -- CRM-6198
 107
 108 DELETE FROM `civicrm_state_province` WHERE `name` IN ('Freeport', 'Fresh Creek', 'Governor\'s Harbour' , 'Green Turtle Cay', 'Harbour Island', 'High Rock', 'Kemps Bay', 'Marsh Harbour','Nicholls Town and Berry Islands' ,'Rock Sound','Sandy Point','San Salvador and Rum Cay','Bandundu', 'Bas-Congo' ,'Haut-Congo', 'Kasai-Occidental','Katanga', 'Orientale' );
 109
 110 INSERT IGNORE INTO civicrm_state_province
 111         (`name`, `abbreviation`, `country_id` )
 112    VALUES
 113         ( 'Abaco Islands', 'AB',1212),
 114         ( 'Andros Island', 'AN',1212 ),
 115         ( 'Berry Islands', 'BR',1212 ),
 116         ( 'Eleuthera', 'EL', 1212 ),
 117         ( 'Grand Bahama', 'GB', 1212 ),
 118         ( 'Rum Cay','RC', 1212 ),
 119         ( 'San Salvador Island', 'SS', 1212 ),
 120         ( 'Kongo central', '01', 1050 ),
 121   ( 'Kwango', '02', 1050 ),
 122   ( 'Kwilu', '03', 1050 ),
 123   ( 'Mai-Ndombe', '04', 1050 ),
 124   ( 'Kasai', '05', 1050 ),
 125   ( 'Lulua', '06', 1050 ),
 126   ( 'Lomami', '07', 1050 ),
 127   ( 'Sankuru', '08', 1050 ),
 128   ( 'Ituri', '09', 1050 ),
 129   ( 'Haut-Uele', '10', 1050 ),
 130   ( 'Tshopo', '11', 1050 ),
 131   ( 'Bas-Uele', '12', 1050 ),
 132   ( 'Nord-Ubangi', '13', 1050 ),
 133   ( 'Mongala', '14', 1050 ),
 134   ( 'Sud-Ubangi', '15', 1050 ),
 135   ( 'Tshuapa', '16', 1050 ),
 136   ( 'Haut-Lomami', '17', 1050 ),
 137   ( 'Lualaba', '18', 1050 ),
 138   ( 'Haut-Katanga', '19', 1050 ),
 139   ( 'Tanganyika', '20', 1050 );
 140
 141 -- CRM-6159
 142 UPDATE civicrm_mailing_bounce_pattern SET pattern = 'over\\s?quota' WHERE pattern = 'overs?quota';
 143
 144 -- CRM-6180
 145 UPDATE civicrm_state_province SET name = 'Durrës' WHERE name = 'Durrsës';
 146 UPDATE civicrm_state_province SET name = 'Korçë'  WHERE name = 'Korcë';