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

   1 {* file to handle db changes in 4.6.beta1 during upgrade *}
   2 -- See https://issues.civicrm.org/jira/browse/CRM-15361
   3 UPDATE civicrm_mailing SET location_type_id = NULL WHERE location_type_id = 0;
   4 ALTER TABLE civicrm_mailing ADD CONSTRAINT FK_civicrm_mailing_location_type_id FOREIGN KEY FK_civicrm_mailing_location_type_id(`location_type_id`) REFERENCES `civicrm_location_type`(`id`) ON DELETE SET NULL;
   5
   6 SELECT @parent_id := id from `civicrm_navigation` where name = 'Customize Data and Screens' AND domain_id = {$domainID};
   7 SELECT @add_weight_id := weight from `civicrm_navigation` where `name` = 'Search Preferences' and `parent_id` = @parent_id;
   8 UPDATE `civicrm_navigation`
   9 SET `weight` = `weight`+1
  10 WHERE `parent_id` = @parent_id
  11 AND `weight` > @add_weight_id;
  12 INSERT INTO `civicrm_navigation`
  13 ( domain_id, url, label, name, permission, permission_operator, parent_id, is_active, has_separator, weight )
  14 VALUES
  15 ( {$domainID}, 'civicrm/admin/setting/preferences/date?reset=1', '{ts escape="sql" skip="true"}Date Preferences{/ts}', 'Date Preferences', 'administer CiviCRM', '', @parent_id , '1', NULL, @add_weight_id + 1 );
  16
  17
  18 -- CRM-15934
  19 SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Quota';
  20 INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern)
  21     VALUES
  22       (@bounceTypeID, 'doesn.t have enough disk space left'),
  23       (@bounceTypeID, 'exceeded storage allocation'),
  24       (@bounceTypeID, 'running out of disk space');
  25
  26 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = '(disk(space)?|over the allowed|exceed(ed|s)?|storage) quota' WHERE `pattern` = '(disk|over the allowed|exceed(ed|s)?|storage) quota';
  27 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = '(mail|in)(box|folder) ((for user \\w+ )?is )?full' WHERE `pattern` = 'mailbox ((for user w+ )?is )?full';
  28 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'mailbox (has exceeded|is over) the limit' WHERE `pattern` = 'mailbox has exceeded the limit';
  29 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'quota ?(usage|violation|exceeded)' WHERE `pattern` = 'quota (usage|violation|exceeded)';
  30
  31 SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Inactive';
  32 INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern)
  33     VALUES
  34       (@bounceTypeID, 'account that you tried to reach is disabled'),
  35       (@bounceTypeID, 'User banned');
  36
  37 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'not accepting (mail|messages)' WHERE `pattern` = 'not accepting mail';
  38
  39 SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Loop';
  40 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = '(mail( forwarding)?|routing).loop' WHERE `pattern` = '(mail|routing) loop';
  41 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'too many (hops|recursive forwards)' WHERE `pattern` = 'too many hops';
  42
  43 SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Relay';
  44 INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern)
  45     VALUES
  46       (@bounceTypeID, 'unrouteable address'),
  47       (@bounceTypeID, 'We don.t handle mail for'),
  48       (@bounceTypeID, 'we do not relay'),
  49       (@bounceTypeID, 'Rejected by next-hop'),
  50       (@bounceTypeID, 'not permitted to( *550)? relay through this server');
  51
  52 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'relay(ing)? (not permitted|(access )?denied)' WHERE `pattern` = 'relay (not permitted|access denied)';
  53
  54 SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Host';
  55 INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern)
  56     VALUES
  57       (@bounceTypeID, 'server requires authentication'),
  58       (@bounceTypeID, 'authentication (is )?required');
  59
  60 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'server is (down or unreachable|not responding)' WHERE `pattern` = 'server is down or unreachable';
  61
  62 SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Invalid';
  63 INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern)
  64     VALUES
  65       (@bounceTypeID, '5.1.0 Address rejected'),
  66       (@bounceTypeID, 'no valid recipients?'),
  67       (@bounceTypeID, 'RecipNotFound'),
  68       (@bounceTypeID, 'no one at this address'),
  69       (@bounceTypeID, 'misconfigured forwarding address'),
  70       (@bounceTypeID, 'account is not allowed'),
  71       (@bounceTypeID, 'Address .<[^>]*>. not known here'),
  72       (@bounceTypeID, '{literal}Recipient address rejected: ([a-zA-Z0-9-]+\\.)+[a-zA-Z]{2,}{/literal}'),
  73       (@bounceTypeID, 'Non sono riuscito a trovare l.indirizzo e-mail'),
  74       (@bounceTypeID, 'nadie con esta direcci..?n'),
  75       (@bounceTypeID, 'ni bilo mogo..?e najti prejemnikovega e-po..?tnega naslova'),
  76       (@bounceTypeID, 'Elektronski naslov (je ukinjen|ne obstaja)'),
  77       (@bounceTypeID, 'nepravilno nastavljen predal');
  78
  79 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'address(es)?( you (entered|specified))? (could|was)( not|n.t)( be)? found' WHERE `pattern` = 'address(es)? could not be found';
  80 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'address(ee)? (unknown|invalid)' WHERE `pattern` = 'addressee unknown';
  81 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = '(mail )?delivery (to this user )?is not allowed' WHERE `pattern` = 'mail delivery to this user is not allowed';
  82 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'no such (mail drop|mailbox( \\w+)?|(e-?mail )?address|recipient|(local )?user|person)( here)?' WHERE `pattern` = 'no such (mail drop|mailbox( w+)?|(e-?mail )?address|recipient|(local )?user)( here)?';
  83 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'no mailbox (here )?by that name' WHERE `pattern` = 'no mailbox here by that name';
  84 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'recipient (does not exist|(is )?unknown|rejected|denied|not found)' WHERE `pattern` = 'recipient (does not exist|(is )?unknown)';
  85 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = 'unknown (local( |-)part|recipient|address error)' WHERE `pattern` = 'unknown (local( |-)part|recipient)';
  86
  87 SELECT @bounceTypeID := max(id) FROM civicrm_mailing_bounce_type WHERE name = 'Spam';
  88 INSERT INTO civicrm_mailing_bounce_pattern (bounce_type_id, pattern)
  89     VALUES
  90       (@bounceTypeID, 'Client host .[^ ]*. blocked'),
  91       (@bounceTypeID, 'automatic(ally-generated)? messages are not accepted'),
  92       (@bounceTypeID, 'denied by policy'),
  93       (@bounceTypeID, 'has no corresponding reverse \\(PTR\\) address'),
  94       (@bounceTypeID, 'has a policy that( [^ ]*)? prohibited the mail that you sent'),
  95       (@bounceTypeID, 'is likely unsolicited mail'),
  96       (@bounceTypeID, 'Local Policy Violation'),
  97       (@bounceTypeID, 'ni bilo mogo..?e dostaviti zaradi varnostnega pravilnika');
  98
  99 UPDATE `civicrm_mailing_bounce_pattern` SET `pattern` = '(detected|rejected) as spam' WHERE `pattern` = 'detected as spam';