crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/CRM/Upgrade/Incremental/sql/4.4.alpha1.mysql.tpl

   1 {include file='../CRM/Upgrade/4.4.alpha1.msg_template/civicrm_msg_template.tpl'}
   2
   3 -- CRM-12357
   4 SELECT @option_group_id_cvOpt := max(id) FROM civicrm_option_group WHERE name = 'contact_view_options';
   5 SELECT @max_val := MAX(ROUND(op.value)) FROM civicrm_option_value op  WHERE op.option_group_id  = @option_group_id_cvOpt;
   6 SELECT @max_wt := MAX(ROUND(val.weight)) FROM civicrm_option_value val WHERE val.option_group_id = @option_group_id_cvOpt;
   7
   8 INSERT INTO
   9    `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`)
  10 VALUES
  11   (@option_group_id_cvOpt, {localize}'{ts escape="sql"}Mailings{/ts}'{/localize}, @max_val+1, 'CiviMail', NULL, 0, NULL,  @max_wt+1, 0, 0, 1, NULL, NULL);
  12
  13 INSERT INTO civicrm_setting
  14   (domain_id, contact_id, is_domain, group_name, name, value)
  15 VALUES
  16   ({$domainID}, NULL, 1, 'Mailing Preferences', 'write_activity_record', '{serialize}1{/serialize}');
  17
  18 -- CRM-12580
  19 ALTER TABLE civicrm_contact ADD  INDEX index_is_deleted_sort_name(is_deleted, sort_name, id);
  20 ALTER TABLE civicrm_contact DROP INDEX index_is_deleted;
  21
  22 -- CRM-12495
  23 DROP TABLE IF EXISTS `civicrm_task_status`;
  24 DROP TABLE IF EXISTS `civicrm_task`;
  25 DROP TABLE IF EXISTS `civicrm_project`;
  26
  27 -- CRM-12425
  28 SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Spam';
  29 INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern)
  30 VALUES (@bounceTypeID, 'X-HmXmrOriginalRecipient');
  31
  32 -- CRM-12716
  33 UPDATE civicrm_custom_field SET text_length = NULL WHERE html_type = 'TextArea' AND text_length = 255;
  34
  35 -- CRM-12288
  36
  37 SELECT @option_group_id_activity_type := max(id) from civicrm_option_group where name = 'activity_type';
  38 SELECT @max_val    := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_activity_type;
  39 SELECT @max_wt     := max(weight) from civicrm_option_value where option_group_id=@option_group_id_activity_type;
  40
  41 INSERT INTO civicrm_option_value
  42    (option_group_id, {localize field='label'}label{/localize}, {localize field='description'}description{/localize}, value, name, weight, filter, component_id)
  43 VALUES
  44    (@option_group_id_activity_type, {localize}'Inbound SMS'{/localize},{localize}'Inbound SMS'{/localize}, (SELECT @max_val := @max_val+1), 'Inbound SMS', (SELECT @max_wt := @max_wt+1), 1, NULL),
  45    (@option_group_id_activity_type, {localize}'SMS delivery'{/localize},{localize}'SMS delivery'{/localize}, (SELECT @max_val := @max_val+1), 'SMS delivery', (SELECT @max_wt := @max_wt+1), 1, NULL);
  46
  47 -- CRM-13015 replaced if $multilingual w/ localize method
  48 UPDATE `civicrm_option_value` SET {localize field="label"}label = '{ts escape="sql"}Outbound SMS{/ts}'{/localize}
  49   WHERE name = 'SMS' and option_group_id = @option_group_id_activity_type;
  50
  51 -- CRM-12689
  52 ALTER TABLE civicrm_action_schedule
  53   ADD COLUMN limit_to tinyint(4) DEFAULT '1' COMMENT 'Is this the recipient criteria limited to OR in addition to?'  AFTER recipient;
  54
  55 -- CRM-12653
  56 SELECT @uf_group_contribution_batch_entry     := max(id) FROM civicrm_uf_group WHERE name = 'contribution_batch_entry';
  57 SELECT @uf_group_membership_batch_entry       := max(id) FROM civicrm_uf_group WHERE name = 'membership_batch_entry';
  58
  59 INSERT INTO civicrm_uf_field
  60        ( uf_group_id, field_name, is_required, is_reserved, weight, visibility, in_selector, is_searchable, location_type_id, {localize field='label'}label{/localize}, field_type)
  61 VALUES
  62       ( @uf_group_contribution_batch_entry, 'soft_credit', 0, 0, 10, 'User and User Admin Only', 0, 0, NULL, {localize}'Soft Credit'{/localize}, 'Contribution'),
  63       ( @uf_group_membership_batch_entry, 'soft_credit', 0, 0, 13, 'User and User Admin Only', 0, 0, NULL, {localize}'Soft Credit'{/localize}, 'Membership');
  64
  65 -- CRM-12809
  66 ALTER TABLE `civicrm_custom_group`
  67   ADD COLUMN `is_reserved` tinyint(4) DEFAULT '0' COMMENT 'Is this a reserved Custom Group?';
  68
  69 --CRM-12986 fix event_id & contact_id to NOT NULL fields on participant table
  70 SET foreign_key_checks = 0;
  71 ALTER TABLE `civicrm_participant`
  72   CHANGE COLUMN `event_id` `event_id` INT(10) UNSIGNED NOT NULL,
  73   CHANGE COLUMN `contact_id` `contact_id` INT(10) UNSIGNED NOT NULL;
  74 SET foreign_key_checks = 1;
  75
  76 -- CRM-12964 civicrm_print_label table creation
  77 CREATE TABLE IF NOT EXISTS `civicrm_print_label` (
  78   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  79   `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'User title for for this label layout',
  80   `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'variable name/programmatic handle for this field.',
  81   `description` text COLLATE utf8_unicode_ci COMMENT 'Description of this label layout',
  82   `label_format_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'This refers to name column of civicrm_option_value row in name_badge option group',
  83   `label_type_id` int(10) unsigned DEFAULT NULL COMMENT 'Implicit FK to civicrm_option_value row in NEW label_type option group',
  84   `data` longtext COLLATE utf8_unicode_ci COMMENT 'contains json encode configurations options',
  85   `is_default` tinyint(4) DEFAULT '1' COMMENT 'Is this default?',
  86   `is_active` tinyint(4) DEFAULT '1' COMMENT 'Is this option active?',
  87   `is_reserved` tinyint(4) DEFAULT '1' COMMENT 'Is this reserved label?',
  88   `created_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_contact, who created this label layout',
  89   PRIMARY KEY (`id`),
  90   KEY `FK_civicrm_print_label_created_id` (`created_id`),
  91   CONSTRAINT `FK_civicrm_print_label_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL
  92 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
  93
  94 -- CRM-12964 adding meta-data
  95 INSERT INTO
  96    `civicrm_option_group` (`name`, {localize field='title'}`title`{/localize}, `is_reserved`, `is_active`)
  97 VALUES
  98    ('label_type', {localize}'{ts escape="sql"}Label Type{/ts}'{/localize}, 1, 1),
  99    ('name_badge', {localize}'{ts escape="sql"}Name Badge Format{/ts}'{/localize}, 1, 1);
 100
 101 SELECT @option_group_id_label_type := max(id) from civicrm_option_group where name = 'label_type';
 102 SELECT @option_group_id_name_badge := max(id) from civicrm_option_group where name = 'name_badge';
 103
 104 INSERT INTO
 105    `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`)
 106 VALUES
 107  (@option_group_id_label_type, {localize}'{ts escape="sql"}Event Badge{/ts}'{/localize}, 1, 'Event Badge', NULL, 0, NULL, 1, 0, 0, 1, NULL, NULL),
 108  (@option_group_id_name_badge, {localize}'{ts escape="sql"}Avery 5395{/ts}'{/localize}, '{literal}{"name":"Avery 5395","paper-size":"a4","metric":"mm","lMargin":13.5,"tMargin":3,"NX":2,"NY":4,"SpaceX":15,"SpaceY":8.5,"width":85.7,"height":59.2,"font-size":12,"orientation":"portrait","font-name":"helvetica","font-style":"","lPadding":0,"tPadding":0}{/literal}', 'Avery 5395', NULL, 0, NULL, 1, 0, 0, 1, NULL, NULL);
 109
 110 -- CRM-12964 adding navigation
 111 UPDATE civicrm_navigation
 112    SET url  = 'civicrm/admin/badgelayout&reset=1',
 113        name = 'Event Name Badge Layouts',
 114        label= '{ts escape="sql" skip="true"}Event Name Badge Layouts{/ts}'
 115  WHERE name = 'Event Badge Formats';
 116
 117 --CRM-12539 change 'Greater London' to 'London'
 118 UPDATE `civicrm_state_province` SET `name` = 'London' WHERE `name` = 'Greater London';
 119
 120 UPDATE `civicrm_premiums` SET {localize field="premiums_nothankyou_label"}premiums_nothankyou_label = '{ts escape="sql"}No thank-you{/ts}'{/localize};
 121
 122 -- CRM-13015 Change address option labels from Additional Address to Supplemental Address
 123 SELECT @option_group_id_addroptions := max(id) from civicrm_option_group where name = 'address_options';
 124
 125 UPDATE civicrm_option_value
 126   SET {localize field="label"}label = '{ts escape="sql"}Supplemental Address 1{/ts}'{/localize}
 127   WHERE name = 'supplemental_address_1' AND option_group_id = @option_group_id_addroptions;
 128
 129 UPDATE civicrm_option_value
 130   SET {localize field="label"}label = '{ts escape="sql"}Supplemental Address 2{/ts}'{/localize}
 131   WHERE name = 'supplemental_address_2' AND option_group_id = @option_group_id_addroptions;
 132
 133 -- CRM-12717
 134 UPDATE `civicrm_navigation` SET label = '{ts escape="sql"}Misc (Undelete, PDFs, Limits, Logging, Captcha, etc.){/ts}', name = 'Misc (Undelete, PDFs, Limits, Logging, Captcha, etc.)'
 135 WHERE url = 'civicrm/admin/setting/misc&reset=1';
 136
 137 -- CRM-13112
 138 ALTER TABLE civicrm_survey
 139   ADD is_share TINYINT( 4 ) NULL DEFAULT '1' COMMENT 'Can people share the petition through social media?';
 140
 141 -- CRM-12439
 142 {if $multilingual}
 143   ALTER TABLE `civicrm_uf_group`
 144     ADD `description` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Optional verbose description of the profile.' AFTER `group_type`;
 145 {else}
 146   ALTER TABLE `civicrm_uf_group`
 147     ADD `description` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Optional verbose description of the profile.' AFTER `title`;
 148 {/if}
 149
 150 --CRM-13142
 151 UPDATE
 152   civicrm_uf_field uf
 153   INNER JOIN
 154   civicrm_uf_group ug ON uf.uf_group_id = ug.id AND ug.is_reserved = 1 AND name = 'membership_batch_entry'
 155 SET uf.is_reserved = 0
 156 WHERE uf.field_name IN ('join_date', 'membership_start_date', 'membership_end_date');
 157
 158 --CRM-13155 - Add searching for recurring contribution data to search has been successfully created.
 159 ALTER TABLE `civicrm_contribution_recur`
 160  CHANGE COLUMN `next_sched_contribution` `next_sched_contribution_date` DATETIME NULL DEFAULT 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.' AFTER `cycle_day`;
 161