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

   1 {* file to handle db changes in 5.29.alpha1 during upgrade *}
   2
   3 {* https://github.com/civicrm/civicrm-core/pull/17824 *}
   4 UPDATE civicrm_status_pref SET name = 'checkExtensionsOk' WHERE name = 'extensionsOk';
   5 UPDATE civicrm_status_pref SET name = 'checkExtensionsUpdates' WHERE name = 'extensionUpdates';
   6
   7 -- The RelationshipCache is a high-level index/cache for querying relationships.
   8 DROP TABLE IF EXISTS `civicrm_relationship_cache`;
   9 CREATE TABLE `civicrm_relationship_cache` (
  10      `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Relationship Cache ID',
  11      `relationship_id` int unsigned NOT NULL   COMMENT 'id of the relationship (FK to civicrm_relationship.id)',
  12      `relationship_type_id` int unsigned NOT NULL   COMMENT 'id of the relationship type',
  13      `orientation` char(3) NOT NULL   COMMENT 'The cache record is a permutation of the original relationship record. The orientation indicates whether it is forward (a_b) or reverse (b_a) relationship.',
  14      `near_contact_id` int unsigned NOT NULL   COMMENT 'id of the first contact',
  15      `near_relation` varchar(64)    COMMENT 'name for relationship of near_contact to far_contact.',
  16      `far_contact_id` int unsigned NOT NULL   COMMENT 'id of the second contact',
  17      `far_relation` varchar(64)    COMMENT 'name for relationship of far_contact to near_contact.',
  18      `is_active` tinyint   DEFAULT 1 COMMENT 'is the relationship active ?',
  19      `start_date` date    COMMENT 'date when the relationship started',
  20      `end_date` date    COMMENT 'date when the relationship ended',
  21      PRIMARY KEY (`id`),
  22      UNIQUE INDEX `UI_relationship`(relationship_id, orientation),
  23      INDEX `index_nearid_nearrelation`(near_contact_id, near_relation),
  24      INDEX `index_nearid_farrelation`(near_contact_id, far_relation),
  25      INDEX `index_near_relation`(near_relation),
  26      CONSTRAINT FK_civicrm_relationship_cache_relationship_id FOREIGN KEY (`relationship_id`) REFERENCES `civicrm_relationship`(`id`) ON DELETE CASCADE,
  27      CONSTRAINT FK_civicrm_relationship_cache_relationship_type_id FOREIGN KEY (`relationship_type_id`) REFERENCES `civicrm_relationship_type`(`id`) ON DELETE CASCADE,
  28      CONSTRAINT FK_civicrm_relationship_cache_near_contact_id FOREIGN KEY (`near_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,
  29      CONSTRAINT FK_civicrm_relationship_cache_far_contact_id FOREIGN KEY (`far_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE
  30 )  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  31
  32 -- Fix missing resubscribeUrl token. There doesn't seem to be any precedent
  33 -- for doing an upgrade for these, since the last update was in 2009 when
  34 -- the token went missing and it had no upgrade script for it. Also unlike
  35 -- message templates, there doesn't seem to be a way to tell whether it's
  36 -- been changed. Using ts is a bit unreliable if the translation has changed
  37 -- but it would be no worse than now and just end up not updating it.
  38 -- Also, I'm drawing a blank on why the %3 is replaced differently during
  39 -- install than during upgrade, hence the OR clause.
  40 {capture assign=unsubgroup}{ldelim}unsubscribe.group{rdelim}{/capture}
  41 {capture assign=actresub}{ldelim}action.resubscribe{rdelim}{/capture}
  42 {capture assign=actresuburl}{ldelim}action.resubscribeUrl{rdelim}{/capture}
  43 UPDATE civicrm_mailing_component
  44 SET body_text = '{ts escape="sql" 1=$unsubgroup 2=$actresub 3=$actresuburl}You have been un-subscribed from the following groups: %1. You can re-subscribe by mailing %2 or clicking %3{/ts}'
  45 WHERE component_type  = 'Unsubscribe'
  46 AND (body_text = '{ts escape="sql" 1=$unsubgroup 2=$actresub}You have been un-subscribed from the following groups: %1. You can re-subscribe by mailing %2 or clicking %3{/ts}'
  47   OR body_text = '{ts escape="sql" 1=$unsubgroup 2=$actresub}You have been un-subscribed from the following groups: %1. You can re-subscribe by mailing %2 or clicking {/ts}');