| 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'; |