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

   1 -- CRM-3507: upgrade message templates (if changed)
   2 {include file='../CRM/Upgrade/3.1.beta1.msg_template/civicrm_msg_template.tpl'}
   3
   4 --  CRM-5388
   5 -- we definitely shouldn't fix non-English strings, so skipping the multilingual part
   6 {if !$multilingual}
   7 -- prefix
   8     UPDATE civicrm_option_value SET label = 'Mr.' , name = 'Mr.'  WHERE label = 'Mr'  AND name = 'Mr';
   9     UPDATE civicrm_option_value SET label = 'Ms.' , name = 'Ms.'  WHERE label = 'Ms'  AND name = 'Ms';
  10     UPDATE civicrm_option_value SET label = 'Mrs.', name = 'Mrs.' WHERE label = 'Mrs' AND name = 'Mrs';
  11     UPDATE civicrm_option_value SET label = 'Dr.',  name = 'Dr.'  WHERE label = 'Dr'  AND name = 'Dr';
  12
  13 -- suffix
  14     UPDATE civicrm_option_value SET label = 'Jr.',  name = 'Jr.'  WHERE label = 'Jr'  AND name = 'Jr';
  15     UPDATE civicrm_option_value SET label = 'Sr.',  name = 'Sr.'  WHERE label = 'Sr'  AND name = 'Sr';
  16 {/if}
  17
  18 --  CRM-5435
  19 ALTER TABLE `civicrm_contribution_soft`
  20     ADD CONSTRAINT `FK_civicrm_contribution_soft_pcp_id` FOREIGN KEY (`pcp_id`) REFERENCES `civicrm_pcp` (`id`) ON DELETE SET NULL;
  21
  22 ALTER TABLE `civicrm_contribution_soft`
  23     CHANGE `pcp_id` `pcp_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_pcp.id';
  24
  25 ALTER TABLE `civicrm_pcp_block`
  26     ADD CONSTRAINT `FK_civicrm_pcp_block_supporter_profile_id` FOREIGN KEY (`supporter_profile_id`) REFERENCES `civicrm_uf_group` (`id`) ON DELETE SET NULL;
  27
  28 ALTER TABLE `civicrm_pcp_block`
  29     CHANGE `supporter_profile_id` `supporter_profile_id` int(10) unsigned default NULL COMMENT 'FK to civicrm_uf_group.id. Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?';
  30
  31 -- CRM-5322
  32
  33   SELECT @option_group_id_sfe := max(id) from civicrm_option_group where name = 'safe_file_extension';
  34   SELECT @max_val             := MAX(ROUND(op.value)) FROM civicrm_option_value op WHERE op.option_group_id  = @option_group_id_sfe;
  35   SELECT @max_wt              := max(weight) from civicrm_option_value where option_group_id= @option_group_id_sfe;
  36
  37   INSERT INTO civicrm_option_value
  38     (option_group_id,      {localize field='label'}label{/localize}, value,                           filter, weight) VALUES
  39     (@option_group_id_sfe, {localize}'docx'{/localize},              (SELECT @max_val := @max_val+1), 0,      (SELECT @max_wt := @max_wt+1)),
  40     (@option_group_id_sfe, {localize}'xlsx'{/localize},              (SELECT @max_val := @max_val+1), 0,      (SELECT @max_wt := @max_wt+1));
  41
  42 --
  43 -- handle schema changes from v3.0.3 once again, CRM-5463
  44 --
  45 -- CRM-5333
  46 -- Delete duplicate records in target and assignment exists if any
  47
  48 DELETE cat.* FROM civicrm_activity_target cat
  49              INNER JOIN ( SELECT id, activity_id, target_contact_id
  50                           FROM civicrm_activity_target
  51                           GROUP BY activity_id, target_contact_id HAVING count(*) > 1 ) dup_cat
  52                      ON ( cat.activity_id = dup_cat.activity_id
  53                           AND cat.target_contact_id = dup_cat.target_contact_id
  54                           AND cat.id <> dup_cat.id );
  55
  56 DELETE caa.* FROM civicrm_activity_assignment caa
  57               INNER JOIN ( SELECT id, activity_id, assignee_contact_id
  58                            FROM civicrm_activity_assignment
  59                            GROUP BY activity_id, assignee_contact_id HAVING count(*) > 1 ) dup_caa
  60                       ON ( caa.activity_id = dup_caa.activity_id
  61                            AND caa.assignee_contact_id = dup_caa.assignee_contact_id
  62                            AND caa.id <> dup_caa.id );
  63
  64
  65 -- Drop unique indexes of activity_target and activity_assignment
  66
  67 ALTER TABLE  civicrm_activity_assignment
  68 DROP INDEX `UI_activity_assignee_contact_id` ,
  69 ADD UNIQUE INDEX `UI_activity_assignee_contact_id` (`assignee_contact_id`,`activity_id`);
  70
  71 ALTER TABLE  civicrm_activity_target
  72 DROP INDEX `UI_activity_target_contact_id` ,
  73 ADD UNIQUE INDEX `UI_activity_target_contact_id` (`target_contact_id`,`activity_id`);
  74
  75 -- CRM-5437
  76 UPDATE civicrm_participant_status_type SET class = 'Pending' WHERE class NOT IN ('Positive', 'Pending', 'Waiting', 'Negative');
  77
  78 -- CRM-5451
  79 ALTER TABLE `civicrm_custom_group`
  80 DROP FOREIGN KEY `FK_civicrm_custom_group_created_id`;
  81
  82 ALTER TABLE `civicrm_custom_group`
  83 ADD CONSTRAINT `FK_civicrm_custom_group_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL;
  84
  85 ALTER TABLE `civicrm_event`
  86 DROP FOREIGN KEY `FK_civicrm_event_created_id`;
  87
  88 ALTER TABLE `civicrm_event`
  89 ADD CONSTRAINT `FK_civicrm_event_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL;
  90
  91 ALTER TABLE `civicrm_contribution_page`
  92 DROP FOREIGN KEY `FK_civicrm_contribution_page_created_id`;
  93
  94 ALTER TABLE `civicrm_contribution_page`
  95 ADD CONSTRAINT `FK_civicrm_contribution_page_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL;
  96
  97 ALTER TABLE `civicrm_uf_group`
  98 DROP FOREIGN KEY `FK_civicrm_uf_group_created_id`;
  99
 100 ALTER TABLE `civicrm_uf_group`
 101 ADD CONSTRAINT `FK_civicrm_uf_group_created_id` FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL;
 102
 103 -- CRM-5471
 104 UPDATE civicrm_mailing_bounce_pattern
 105    SET pattern = 'delivery to the following recipient(s)? failed'
 106  WHERE pattern = 'delivery to the following recipients failed';
 107
 108 --v3.0.3 changes end.
 109