Hallo,
da ich einfache MySQL Syntax eher nur lesen kann, wende ich mich an Euch eine Abfrage für eine postfix Filtertabelle zu formulieren. Postfix kennt eine Mailadresse und übergibt sie als "%s", das Ergebnis der Abfrage wird 'greylist' oder '' lauten. Für die Übergabe habe ich offensichtlich nur die Variable query, die ich mit normaler MySQL-Syntax füllen kann. Nun zur Komplexität der Fragestellung:
Es gibt die Tabelle 'mail_users', diese enthält u.a. 'id', 'email' und 'policy'. - 'id' - int(11) - AUTO_INCREMENT - 'email' - varchar(255) = vollständige Emailadresse eines lokalen Kontos - 'policy' - enum('greylist,'') = postfix erwartet von der Abfrage eintweder nix oder den Namen einer vorher konfigurierten polica, hier 'greylist'
Soweit, so einfach: "SELECT policy FROM mail_users WHERE email='%s' AND policy='greylist' LIMIT 1" liefert mit 'greylist' oder nichts, so wie ich es will.
Nun können aber auch Adressen in der DB sein, die einer Weiterleitung dienen oder Alias auf ein Konto sind, das steht in der Tabelle 'mail_virtual'. - 'id' - int(11) - AUTO_INCREMENT - 'email' - varchar(255) = vollständige Emailadresse - 'popaccount' - int(11) - referenzier damit auf die 'id' in 'mail_users' oder ist schlicht '0', wenn es kein lokales Konto gibt - 'destination' - text - eine Zieladresse - 'iscachtall' - tinyint(1) - '0' oder '1' für das selbsterklärende Nein oder Ja
Nehmen wir an, ich habe das Konto ronny@seffner.de, die aliase info@seffner.de, forward@seffner.de dazu und das catchall-Konto bernd@seffner.de, sähen die Tabellen wohl wie folgt aus:
mail_users 11, ronny@seffner.de, greylist 84, bernd@seffner.de, greylist
mail_virtual 41, ronny@seffner.de, 11, ronny@seffner.de, 0 57, info@seffner.de, 11, ronny@seffner.dem, 0 133, bernd@seffner.de, 84, bernd@seffner.de, 1 287, forward@seffner.de, 0, klaus@gmail.com, 0
Da ich meine Policies nur für lokale Konten durchsetzen will, hat sich der Entwickler hinter dem Adminpanel wohl gedacht, gehört die Policy ans Konto und nicht die Mailadresse. Aus Sicht des Webfrontends wird 'greylist' nur für ronny@seffner.de und bernd@seffner.de gesetzt soll aj aber für alle zugehörigen Aliase und ggf. auch für catchall gelten.
Wie muss nun die Abfrage für meine o.g. 'query' Variable lauten wenn die Übergaben von %s als: ronny@seffner.de dann 'greylist', info@seffner.de dann 'greylist', bernd@seffner.de dann 'greylist', irgendwas@seffner.de dann 'greylist', forward@seffner.de dann '' liefern soll?
Sicherlich wäre es für diese Aufgabenstellung einfacher, wenn die 'policy' an der Adresse in 'mail_virtual' stehen würde, aber das bedeutet ein aufwändiges Ändern des Webfrontends und löst das catchall-Problem noch nicht.
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
www.seffner.de | ronny@seffner.de | +49 35245 72950
Hallo Ronny,
ich mach mal bisschen Tofu.
Ich gehe davon aus, du wolltest mit deiner Problembeschreibung ausdrücken, dass eMails, die an Adressen, welche in der mail_users tabelle stehen, ge-graylistet werden, und welche, die an externe weitergeleitet werden, nicht.
2 Lösungen: - Subselect: SELECT greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE email = %s)
- Join: SELECT u.greylist as greylist FROM mail_users u, mail_virtual v WHERE u.email = v.destination AND v.email = %s
Du hattest oben noch drangehängt "AND policy='greylist' LIMIT 1" (oder beim Join "AND u.policy = 'greylist' LIMIT 1") - sollte eigtl. nicht nötig sein, aber wenn das von mir vorgeschlagene nicht geht, kannstes ja wieder ranhängen.
Viele Grüße morphium
Am 17. Februar 2013 15:02 schrieb Ronny Seffner ronny@seffner.de:
Hallo,
da ich einfache MySQL Syntax eher nur lesen kann, wende ich mich an Euch eine Abfrage für eine postfix Filtertabelle zu formulieren. Postfix kennt eine Mailadresse und übergibt sie als "%s", das Ergebnis der Abfrage wird 'greylist' oder '' lauten. Für die Übergabe habe ich offensichtlich nur die Variable query, die ich mit normaler MySQL-Syntax füllen kann. Nun zur Komplexität der Fragestellung:
Es gibt die Tabelle 'mail_users', diese enthält u.a. 'id', 'email' und 'policy'.
- 'id' - int(11) - AUTO_INCREMENT
- 'email' - varchar(255) = vollständige Emailadresse eines lokalen Kontos
- 'policy' - enum('greylist,'') = postfix erwartet von der Abfrage eintweder
nix oder den Namen einer vorher konfigurierten polica, hier 'greylist'
Soweit, so einfach: "SELECT policy FROM mail_users WHERE email='%s' AND policy='greylist' LIMIT 1" liefert mit 'greylist' oder nichts, so wie ich es will.
Nun können aber auch Adressen in der DB sein, die einer Weiterleitung dienen oder Alias auf ein Konto sind, das steht in der Tabelle 'mail_virtual'.
- 'id' - int(11) - AUTO_INCREMENT
- 'email' - varchar(255) = vollständige Emailadresse
- 'popaccount' - int(11) - referenzier damit auf die 'id' in 'mail_users'
oder ist schlicht '0', wenn es kein lokales Konto gibt
- 'destination' - text - eine Zieladresse
- 'iscachtall' - tinyint(1) - '0' oder '1' für das selbsterklärende Nein
oder Ja
Nehmen wir an, ich habe das Konto ronny@seffner.de, die aliase info@seffner.de, forward@seffner.de dazu und das catchall-Konto bernd@seffner.de, sähen die Tabellen wohl wie folgt aus:
mail_users 11, ronny@seffner.de, greylist 84, bernd@seffner.de, greylist
mail_virtual 41, ronny@seffner.de, 11, ronny@seffner.de, 0 57, info@seffner.de, 11, ronny@seffner.dem, 0 133, bernd@seffner.de, 84, bernd@seffner.de, 1 287, forward@seffner.de, 0, klaus@gmail.com, 0
Da ich meine Policies nur für lokale Konten durchsetzen will, hat sich der Entwickler hinter dem Adminpanel wohl gedacht, gehört die Policy ans Konto und nicht die Mailadresse. Aus Sicht des Webfrontends wird 'greylist' nur für ronny@seffner.de und bernd@seffner.de gesetzt soll aj aber für alle zugehörigen Aliase und ggf. auch für catchall gelten.
Wie muss nun die Abfrage für meine o.g. 'query' Variable lauten wenn die Übergaben von %s als: ronny@seffner.de dann 'greylist', info@seffner.de dann 'greylist', bernd@seffner.de dann 'greylist', irgendwas@seffner.de dann 'greylist', forward@seffner.de dann '' liefern soll?
Sicherlich wäre es für diese Aufgabenstellung einfacher, wenn die 'policy' an der Adresse in 'mail_virtual' stehen würde, aber das bedeutet ein aufwändiges Ändern des Webfrontends und löst das catchall-Problem noch nicht.
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
www.seffner.de | ronny@seffner.de | +49 35245 72950
Lug-dd maillist - Lug-dd@mailman.schlittermann.de https://ssl.schlittermann.de/mailman/listinfo/lug-dd
Hallo Rene und morphium
Ich gehe davon aus, du wolltest mit deiner Problembeschreibung ausdrücken, dass eMails, die an Adressen, welche in der mail_users tabelle stehen, ge-graylistet werden, und welche, die an externe weitergeleitet werden, nicht.
Ich glaube nicht.
Es gibt leider zwei Tabellen. In der einen - mail_virtual - sind alle Mailadressen definiert, mit denen der Server etwas anfangen soll. In der anderen - mail_users - sind die lokalen Konten hinterlegt.
Ich möchte beim Abarbeiten von Policies pro Mail, für die mein Server zuständig ist, eine Verarbeitung vornehmen. Die Entscheidung was/ob zu tun ist steht nun leider nicht in der mail_virtual - also jeder Mailadresse für die ich zuständig bin, sondern in der mail_users -also am Briefkasten (aus auch einer gewissen Logik entspricht). Aber es macht die Abfrage kompliziert.
Ich will nun für eine Mailadresse herausbekommen, ob dazu ein Konto existiert (was durch Existenz mail_users definiert ist) und dann dort entsprechende Option auslesen.
Der einfache Fall, Emailadresse ist gleich Kontoadresse, gelingt mir - ist ja nur eine Tabelle zu fragen. Der komplexere Fall in mail_virtual ist ein Alias auf ein Konto, hat Rene versucht zu lösen (leider erfolglos, wie es scheint). Der wirklich komplizierte Fall, das Konto ist ein catch_all hat noch gar keiner von Euch angegangen.
Ist das Problem jetzt klarer?
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
www.seffner.de | ronny@seffner.de | +49 35245 72950
Ronny Seffner ronny@seffner.de wrote:
Der einfache Fall, Emailadresse ist gleich Kontoadresse, gelingt mir - ist ja nur eine Tabelle zu fragen. Der komplexere Fall in mail_virtual ist ein Alias auf ein Konto, hat Rene versucht zu lösen (leider erfolglos, wie es scheint). Der wirklich komplizierte Fall, das Konto ist ein catch_all hat noch gar keiner von Euch angegangen.
Ist das Problem jetzt klarer?
Ich hoffe. Du hast N Tabellen, die in einer bestimmten Reihenfolge abzufragen sind. Wenn in der ersten nix ist, dann die nächste und so. Du brauchst den ersten Treffer, und nur diesen einen. Und Du kannst nur 1 SQL machen.
Ja?
Also, Du hast 3 Tabellen r1, r2 und r3.
test=*# select * from r1; i --- 1 (1 row)
Time: 0,160 ms test=*# select * from r2; i --- 2 (1 row)
Time: 0,162 ms test=*# select * from r3; i --- 3 (1 row)
Du suchst den ersten Treffer, wo i=3 ist:
test=*# select i from (select 1 as o, i from r1 where i = 3 union select 2, i from r2 where i = 3 union select 3, i from r3 where i=3) foo order by o limit 1; i --- 3 (1 row)
Sowas in der Art? Mußt natürlich an Deine Bedingungen anpassen. Wichtig ist, daß alle 3 Selects dieselbe Struktur zurück liefern. Die extra Spalte o dient der Sortierung (Wichtung), das LIMIT 1 ist sicherlich klar.
Man könnte da auch eine Stored Procedure schreiben, in PG kein Thema, aber ich weiß nicht, was MySQL da bietet. Hätte den Charme, daß nur soviele Abfragen ausgeführt werden, bis ein Treffer da ist.
Andreas
Hallo Andreas,
Ich hoffe. Du hast N Tabellen, die in einer bestimmten Reihenfolge abzufragen sind. Wenn in der ersten nix ist, dann die nächste und so. Du
Nein. Mit meinem Post von heute Vormittag habe ich das hoffentlich durch die SQL-Exporte klar gestellt.
brauchst den ersten Treffer, und nur diesen einen. Und Du kannst nur 1 SQL machen.
Ja.
Danke.
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
www.seffner.de | ronny@seffner.de | +49 35245 72950
Ronny Seffner ronny@seffner.de wrote:
Hallo Andreas,
Ich hoffe. Du hast N Tabellen, die in einer bestimmten Reihenfolge abzufragen sind. Wenn in der ersten nix ist, dann die nächste und so. Du
Nein. Mit meinem Post von heute Vormittag habe ich das hoffentlich durch die SQL-Exporte klar gestellt.
Ja.
Anmerkung dazu: bist Du Dir sicher, MyISAM verwenden zu wollen?
Bedenke: Du wirst ganz sicher mal Backups der Datenbank machen wollen. MyISAM sperrt solange die Tabelle. Du wirst dann recht häßliche Probleme bekommen.
brauchst den ersten Treffer, und nur diesen einen. Und Du kannst nur 1 SQL machen.
Ja.
Dann hatte ich ja diesen Part wenigstens verstanden ;-)
-- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
Grüße ins Nachbardorf ;-)
Andreas
Hi Andreas,
Anmerkung dazu: bist Du Dir sicher, MyISAM verwenden zu wollen?
Nein. Als DB-DAU (oder ein Wenigchen mehr ;-) nahm ich das, was mir vorgesetzt wurde. Das MyISAM mit Locking - oder was auch immer - Probleme hat ist mir bereits aufgefallen. Die Frage "mirgriert man das so einfach nach InnoDB" habe ich mir bisher damit beantwortet: man müsste sich die funktionalen Unterschiede der engines ansehen und dann rausfinden ob man Funktionsverlustlos wechseln kann bzw. was hat sich der Entwickler einst gedacht (falls er gedacht hat). Ist und bleibt sicher ein Wunschtraum, dass Du jetzt schreibst "geht ganz einfach" ;-)
Grüße ins Nachbardorf ;-)
Ja. Fiel mir neulich auch auf ;-)
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
www.seffner.de | ronny@seffner.de | +49 35245 72950
Ronny Seffner ronny@seffner.de wrote:
Hi Andreas,
Anmerkung dazu: bist Du Dir sicher, MyISAM verwenden zu wollen?
Nein. Als DB-DAU (oder ein Wenigchen mehr ;-) nahm ich das, was mir vorgesetzt wurde. Das MyISAM mit Locking - oder was auch immer - Probleme hat ist mir bereits aufgefallen. Die Frage "mirgriert man das so einfach
Ja, locking ist ein echtes Problem mit MyISAM. IIRC jedes Update eines Records sperrt die Tabelle, ein mysqldump auch, und zwar für die Zeit des kompletten Dumps. Ohne jetzt zuviel plaudern zu wollen - ich sehe die Effekte immer in meinen Nachtschichten.
nach InnoDB" habe ich mir bisher damit beantwortet: man müsste sich die funktionalen Unterschiede der engines ansehen und dann rausfinden ob man Funktionsverlustlos wechseln kann bzw. was hat sich der Entwickler einst gedacht (falls er gedacht hat). Ist und bleibt sicher ein Wunschtraum, dass
Vermutlich hat er nicht gedacht bzw. auf das 'Default' verlassen - das war bis 5.5 nunmal MyISAM. Wenn Du nicht gerade Volltextsuche brauchst hast Du mit InnoDB sehr wahrscheinlich keine Probleme bzw. weniger als mit MyISAM. Schreiben ist u.U. etwas lahmer, aber ich bin kein MySQL-Experte.
Dafür hättest noch andere Vorteile, wie z.B. referrentielle Integrität und so.
Unabhängig davon würde ich MySQL nicht verwenden, aber das soll jetzt mal nicht das Thema sein. Nur kurz: in PG ist es deutlich angenehmer, komplexe Logig in Funktionen unterzubringen. Auch ist die Unterstützung mit Indexen (funktionale, partielle) besser, pro Abfrage und Table können unbegrenzt viele Indexe genutzt werden (MySQL IIRC nur einen), Check-Constraints funktionieren (MySQL prüft die Syntax, ignoriert Check-Constraints aber vollständig) und so weiter...
Der erste Punkt wäre für Dich aber sicherlich interessant.
Du jetzt schreibst "geht ganz einfach" ;-)
IIRC geht das auch relativ einfach. Ich vermute mal blind, daß es dazu via Google Tonnen von Anleitungen gibt.
Andreas
Hallo Andreas,
IIRC geht das auch relativ einfach. Ich vermute mal blind, dass es dazu
via
Google Tonnen von Anleitungen gibt.
Mal abgesehen von der Verantwortung zu prüfen ob man z.B. den Volltextindex braucht (einer der Unterschiede) ists wirklich einfach : ALTER TABLE ... ENGINE=InnoDB
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
www.seffner.de | ronny@seffner.de | +49 35245 72950
Ronny Seffner ronny@seffner.de wrote:
Hallo Andreas,
IIRC geht das auch relativ einfach. Ich vermute mal blind, dass es dazu
via
Google Tonnen von Anleitungen gibt.
Mal abgesehen von der Verantwortung zu prüfen ob man z.B. den Volltextindex braucht (einer der Unterschiede) ists wirklich einfach : ALTER TABLE ...
schrieb ich ja. Es gibt sonst keine Unterschiede, soweit ich weiß. Also nix, was MyISAM kann und InnoDB nicht. Andersrum schon.
Andreas
Am 18.02.2013 15:09, schrieb Ronny Seffner:
Der komplexere Fall in mail_virtual ist ein Alias auf ein Konto, hat Rene versucht zu lösen (leider erfolglos, wie es scheint).
Was klappt mit meiner Lösung nicht? Die ist getestet mit Deinen Angaben. Sollten Eure Tabellen doch anders aussehen, bitte exakt posten.
Der wirklich komplizierte Fall, das Konto ist ein catch_all hat noch gar keiner von Euch angegangen. Ist das Problem jetzt klarer?
Nö - kannst Du die Datensätzte für "das Konto ist ein catch_all" bitte posten?
Gruß René Thiel (Rennkuckuck) mailto:reti@rennkuckuck.de
Hallo Rene, Hallo morphium
um nicht falsch verstanden zu werden. Ich bin Dir und 'morphium' für die selbstlose Hilfe dankbar.
Was klappt mit meiner Lösung nicht? Die ist getestet mit Deinen Angaben. Sollten Eure Tabellen doch anders aussehen, bitte exakt posten.
Zu aller Erst muss ich eingestehen, ich habe Eure Lösungen verwechselt ;-(
morphium : subselect : geht für Konten und Aliase, nicht für Catch All morphium : join : geht für Konten und Aliase, nicht für Catch All
Zumindest das 'LIMIT 1' hatte ich zur Sicherheit eingesetzt, weil Postfix hier kein Array sondern nur einen Wert erwartet und ich nicht weiß, wie er sonst reagiert.
Rene : gesuchten anzeigen : geht nur für das Konto, nicht für ein Alias und auch nicht für Catch All
Einige meiner Angaben hier waren vereinfacht oder schlicht falsch abgetippt, das habe ich aber bei der Übertragung Eurer Lösungen wieder korrigiert. Zur Sicherheit der Strukturexport meiner beiden Tabellen:
CREATE TABLE IF NOT EXISTS `mail_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(255) NOT NULL DEFAULT '', `password` varchar(128) NOT NULL DEFAULT '', `password_enc` varchar(128) NOT NULL DEFAULT '', `uid` int(11) NOT NULL DEFAULT '0', `gid` int(11) NOT NULL DEFAULT '0', `homedir` varchar(255) NOT NULL DEFAULT '', `maildir` varchar(255) NOT NULL DEFAULT '', `postfix` enum('Y','N') NOT NULL DEFAULT 'Y', `domainid` int(11) NOT NULL DEFAULT '0', `customerid` int(11) NOT NULL DEFAULT '0', `quota` bigint(13) NOT NULL DEFAULT '0', `pop3` tinyint(1) NOT NULL DEFAULT '1', `imap` tinyint(1) NOT NULL DEFAULT '1', `username` varchar(255) NOT NULL DEFAULT '', `policy_greylist` enum('policy_greylist','') NOT NULL DEFAULT '', `bypass_virus_checks` char(1) NOT NULL DEFAULT 'N', `bypass_spam_checks` char(1) NOT NULL DEFAULT 'N', `spam_tag2_level` float(4,1) NOT NULL DEFAULT '5.0', `spam_kill_level` float(4,1) NOT NULL DEFAULT '20.0', PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=642 ;
CREATE TABLE IF NOT EXISTS `mail_virtual` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(255) NOT NULL DEFAULT '', `email_full` varchar(255) NOT NULL DEFAULT '', `destination` text NOT NULL, `domainid` int(11) NOT NULL DEFAULT '0', `customerid` int(11) NOT NULL DEFAULT '0', `popaccountid` int(11) NOT NULL DEFAULT '0', `iscatchall` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `email` (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1017 ;
Allen Lösungsansätzen fehlt noch das Thema 'iscatchall'. Mir ist klar, dass hier mit Stringoperationen und Bedingungen gearbeitet werden muss denn: Wenn eine Mailadresse in mail_virtual.email nicht existiert, so ist doch zu prüfen ob die Domain der angefragten Emailadresse (%s) in einer (oder mehreren) mail_virtual.email auch Verwendung findet und ob diese(r) Eintr[a|ä]g(e) dann noch iscatchall=1 haben. In diesem Fall ist der betreffener mail_virtual Eintrag wieder in Richtung mail_users zu prüfen ob dort policy_greylist gesetzt ist.
Das fällt mir ja schon in normalen Worten schwer.
Nö - kannst Du die Datensätzte für "das Konto ist ein catch_all" bitte posten?
INSERT INTO `mail_virtual` (`id`, `email`, `email_full`, `destination`, `domainid`, `customerid`, `popaccountid`, `iscatchall`) VALUES (2, 'ronny@i-t-sys.de', 'ronny@i-t-sys.de', 'ronny@i-t-sys.de', 1, 3, 548, 1), (901, 'info@i-t-sys.de', 'info@i-t-sys.de', 'ronny@i-t-sys.de', 1, 3, 0, 0);
INSERT INTO `mail_users` (`id`, `email`, `password`, `password_enc`, `uid`, `gid`, `homedir`, `maildir`, `postfix`, `domainid`, `customerid`, `quota`, `pop3`, `imap`, `username`, `policy_greylist`, `bypass_virus_checks`, `bypass_spam_checks`, `spam_tag2_level`, `spam_kill_level`) VALUES (548, 'ronny@i-t-sys.de', '', '*****', 2000, 2000, '/var/kunden/mail/', 'its/ronny@i-t-sys.de/', 'Y', 1, 3, 0, 1, 1, 'ronny@i-t-sys.de', 'policy_greylist', 'N', 'N', 5.0, 20.0);
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
www.seffner.de | ronny@seffner.de | +49 35245 72950
Am 19.02.2013 11:32, schrieb Ronny Seffner:
Rene: gesuchten anzeigen: geht nur für das Konto, nicht für ein Alias
Falsch: info@seffner.de ist laut Deiner eMail ein Alias, deshalb frage ich ja: "WHERE `mail_virtual`.`email` = 'info@seffner.de'"
auch nicht für Catch All
Siehe unten...
Allen Lösungsansätzen fehlt noch das Thema 'iscatchall'. Mir ist klar, dass hier mit Stringoperationen und Bedingungen gearbeitet werden muss denn: Wenn eine Mailadresse in mail_virtual.email nicht existiert, so ist doch zu prüfen ob die Domain der angefragten Emailadresse (%s) in einer (oder mehreren) mail_virtual.email auch Verwendung findet und ob diese(r) Eintr[a|ä]g(e) dann noch iscatchall=1 haben. In diesem Fall ist der betreffener mail_virtual Eintrag wieder in Richtung mail_users zu prüfen ob dort policy_greylist gesetzt ist.
Das fällt mir ja schon in normalen Worten schwer.
Das merkt man - in den bisherigen eMails stand gar nicht drin, was Du überhaupt mit "catchall-Problem" meintest und selbst jetzt besteht noch Klärungsbedarf:
1.a) In mail_virtual.email stehen _alle_ Adressen? 1.b) Jeder Datensatz in mail_users hat _mindestens_ einen zugehörigen Datensatz in mail_virtual? 1.c) iscatchall ist pro Domain höchstens einmal gesetzt?
2. Falls eine Adresse nicht gefunden wird, soll ersatzweise diejenige Adresse mit gleich lautender Domain und iscatchall=1 abgefragt werden?
Wenn Du mir das so bestätigst oder _verständlich_ korrigierst, dann setze ich mich nochmal dran.
Gruß René Thiel (Rennkuckuck) mailto:reti@rennkuckuck.de
Versuch's mal damit:
SELECT IF( ( SELECT COUNT(*) FROM `mail_virtual` WHERE `email` = 'ronny@i-t-sys.de' ) > 0, ( SELECT `policy_greylist` FROM `mail_users` RIGHT JOIN `mail_virtual` ON `mail_users`.`id` = `mail_virtual`.`popaccountid` WHERE `mail_virtual`.`email` = 'ronny@i-t-sys.de' ) , ( SELECT `policy_greylist` FROM `mail_users` RIGHT JOIN `mail_virtual` ON `mail_users`.`id` = `mail_virtual`.`popaccountid` WHERE `mail_virtual`.`email` LIKE (CONCAT('%',(SUBSTRING_INDEX('ronny@i-t-sys.de', '@', -1)))) AND `mail_virtual`.`iscatchall` = 1 ) )
Getestet mit zu wenigen Datensätzen...
Gruß René Thiel (Rennkuckuck) mailto:reti@rennkuckuck.de
Hi René,
ich habe Deiner Lösung mal die gleichen "Optimierungen" zukommen lassen, die postmap offenbar benötigt und das "@" im CONCAT ergänzt (damit mal nicht eine Domain Teil einer anderen sein kann), die Klammern um die IF expressions hattest Du ja schon:
SELECT IF( ( SELECT COUNT(*) FROM `mail_virtual` WHERE `email` = 'ronny@i-t-sys.de' ) > 0, ( SELECT `policy_greylist` FROM `mail_users` RIGHT JOIN `mail_virtual` ON `mail_users`.`id` = `mail_virtual`.`popaccountid` WHERE `mail_virtual`.`email` = 'ronny@i-t-sys.de' ) , ( SELECT `policy_greylist` FROM `mail_users` RIGHT JOIN `mail_virtual` ON `mail_users`.`id` = `mail_virtual`.`popaccountid` WHERE `mail_virtual`.`email` LIKE (CONCAT('%%@','%d')) AND `mail_virtual`.`iscatchall` = 1 ) )
Fazit:
policy:gesetzt, catchall:gesetzt (+ erwartetes Resultat, -nicht erwartetes Resultat) ------------------------------------- + Konto ronny@ angefragt liefert die policy - Alias info@ angefragt liefert ein leeres Resultat (exit 0) + fiktiven catach-all Alias angefragt liefert die policy
policy:entfernt, catchall:gesetzt --------------------------------------- - Konto ronny@ angefragt liefert postmap Fehler + Alias info@ angefragt liefert ein leeres Resultat (exit 0) - fiktiven catach-all Alias angefragt liefert postmap Fehler
policy:entfernt, catchall:entfernt ---------------------------------------- - Konto ronny@ angefragt liefert postmap Fehler + Alias info@ angefragt liefert ein leeres Resultat (exit 0) + fiktiven catach-all Alias angefragt liefert leeres Resultat (exit 0)
policy:gesetzt, catchall:entfernt --------------------------------------- + Konto ronny@ angefragt liefert die policy - Alias info@ angefragt liefert ein leeres Resultat (exit 0) - fiktiven catach-all Alias angefragt liefert leeres Resultat (exit 0)
Der postmap-Fehler ist wie bei morphiums Lösung :
postmap: warning: table "mysql:/root/mysql-rene": empty lookup result for: "ronny@i-t-sys.de" -- ignored (Adresse natürlich variabel)
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
www.seffner.de | ronny@seffner.de | +49 35245 72950
Nochmal mit Catchall-Problem. Das hatte ich vorher nicht auf dem Schirm, sorry.
Am 17. Februar 2013 15:20 schrieb morphium morphium@morphium.info:
2 Lösungen:
- Subselect:
SELECT greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE email = %s)
SELECT IF ( SELECT COUNT(*) FROM mail_virtual WHERE email = '%s' > 0, SELECT greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE email = '%s'), SELECT greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE iscatchall = 1 AND email LIKE CONCAT('%@',SUBSTRING_INDEX('%s', '@', -1))) )
- Join:
SELECT u.greylist as greylist FROM mail_users u, mail_virtual v WHERE u.email = v.destination AND v.email = %s
das schreib ich um wenn das oben nicht geht ;-)
Viele Grüße!
Hallo morphium,
SELECT IF ( SELECT COUNT(*) FROM mail_virtual WHERE email = '%s' > 0, SELECT greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE email = '%s'), SELECT greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE iscatchall = 1 AND email LIKE CONCAT('%@',SUBSTRING_INDEX('%s', '@', -1))) )
wenn ich das so - incl. der Zeilenumbrüche - übertrage, erhalte ich:
" #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) FROM mail_virtual WHERE email = 'ronny@i-t-sys.de' > 0, SELEC' at line 3"
Ich habe die drei Expressions mal je mit einfachen Klammern umschlossen und zumindest im phpMyAdmin mit Emailadressen statt %s funktioniert es:
SELECT IF ( (SELECT COUNT(*) FROM mail_virtual WHERE email = 'ronny@seffner.de') > 0, (SELECT policy_greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE email = 'ronny@seffner.de')), (SELECT policy_greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE iscatchall = 1 AND email LIKE CONCAT('%@',SUBSTRING_INDEX('ronny@seffner.de', '@', -1)))) )
Abgelegt in einer Datei, aufgerufen mit 'postmap -q ronny@seffner.de mysql:/datei' tut es nicht ;-(
ns3:~# postmap -q ronny@seffner.de mysql:/root/mysql-morphium postmap: fatal: db_common_parse: /root/mysql-morphium: Invalid query template: SELECT IF ( (SELECT COUNT(*) FROM mail_virtual WHERE email = '%s') > 0, (SELECT policy_greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE email = '%s')), (SELECT policy_greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE iscatchall = 1 AND email LIKE CONCAT('%@',SUBSTRING_INDEX('%s', '@', -1)))))
Das bringt mich zum 'man mysql_table' und dort finde ich u.a.:
%% This is replaced by a literal '%' character. %d When the input key is an address of the form user@domain, %d is replaced by the SQL quoted domain part of the address. Otherwise, the query is sup‐ pressed and returns no results.
Soll das %@ jetzt %%@ werden und kann man im CONCAT nun mittest des %d optimieren? Der Versuch mit dem %%@ brachte :
postmap -q ronny@seffner.de mysql:/root/mysql-morphium postmap: warning: table "mysql:/root/mysql-morphium": empty lookup result for: "ronny@seffner.de" -- ignored
Also gleich beides:
SELECT IF ( (SELECT COUNT(*) FROM mail_virtual WHERE email = '%s') > 0, (SELECT policy_greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE email = '%s')), (SELECT policy_greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE iscatchall = 1 AND email LIKE CONCAT('%%@','%d'))) )
Und schon geht’s fast.
Zumindest wird die gesetzte policy in allen erdenklichen Kombinationen von policy und greylist korrekt geliefert, sofern sie gesetzt ist. Was postfix nun nicht mag, ist ein leeres Resultat und leider kommt das als Ergebnis, wenn die policy nicht gesetzt ist.
ns3:~# postmap -q ronny@seffner.de mysql:/root/mysql-morphium postmap: warning: table "mysql:/root/mysql-morphium": empty lookup result for: "ronny@seffner.de" -- ignored
Wenn ich mittels postmap mit meiner ursprünglichen und einfachen Abfrage gegen die nicht gesetzte policy teste liefert postmap korrekt einen exit 0, also liegt die neue Ausgabe in der neuen Abfrage begründet.
Offenbar nur noch ein bisschen Feintuning.
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
www.seffner.de | ronny@seffner.de | +49 35245 72950
Hallo Ronny!
2013/2/20 Ronny Seffner ronny@seffner.de:
Ich habe die drei Expressions mal je mit einfachen Klammern umschlossen
Stimmt, die hatte ich vergessen :)
%% This is replaced by a literal '%' character.
Ah, okay, wusste nicht dass postfix das einzelne % zerhaut.
SELECT IF ( (SELECT COUNT(*) FROM mail_virtual WHERE email = '%s') > 0, (SELECT policy_greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE email = '%s')), (SELECT policy_greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE iscatchall = 1 AND email LIKE CONCAT('%%@','%d'))) )
Und schon geht’s fast.
Siehste, da haben wir dir ja schon ein bisschen SQL beigebracht ;-)
Das Problem liegt nun warsch. in dem bei dir vorhandenen "AND policy='greylist'", denn meine Abfrage liefert einen leeren String (1 Zeile mit 1 Feld) zurück (eben den Wert von "greylist"), deins liefert 0 Zeilen zurück wenn policy nicht 'greylist' ist (zumindest denke ich das - ich hab kein testsetup wo ich das alles ausprobiere... mein Kopf ist mein SQL parser ;) ).
Bauen wir auch gleich noch dein gewünschtes Limit 1 mit ein, dann sieht es so aus:
SELECT IF ( (SELECT COUNT(*) FROM mail_virtual WHERE email = '%s') > 0, (SELECT policy_greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE email = '%s') AND policy_greylist = 'greylist' LIMIT 1), (SELECT policy_greylist FROM mail_users WHERE email = (SELECT destination FROM mail_virtual WHERE iscatchall = 1 AND email LIKE CONCAT('%%@','%d')) AND policy_greylist = 'greylist' LIMIT 1) )
Gehts dann? Sonst ist der Fehler noch woanders ;)
Viele Grüße!
Hallo morphium,
Bauen wir auch gleich noch dein gewünschtes Limit 1 mit ein, dann sieht es so aus:
Das liefert in allen Zuständen, die erwarteten Ergebnisse. Super.
Jetzt versuche ich mich mal noch im Verstehen und Interpretieren. LIMIT 1 begrenzt die Ergebnisliste auf maximal 1 _ODER_ genau 1 - sprich beugt das nun dem leeren Resultat vor (hmm, sind zwei Fragen)? Was genau passiert durch das "AND plicy_greylist = 'greylist'"? Wird hier sichergestellt, dass andere Policies (wie z.B. 'whitelist') eben nicht matchen - klar.
Also allein das LIMIT 1 and den richtigen Stellen liefert postmap was es hören will eine policy (als String) oder einen leeren String ('').
Vielen lieben Dank.
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen
www.seffner.de | ronny@seffner.de | +49 35245 72950
Hallo nochmal!
Am 20. Februar 2013 16:47 schrieb Ronny Seffner ronny@seffner.de:
Hallo morphium,
Bauen wir auch gleich noch dein gewünschtes Limit 1 mit ein, dann sieht es so aus:
Das liefert in allen Zuständen, die erwarteten Ergebnisse. Super.
Jetzt versuche ich mich mal noch im Verstehen und Interpretieren. LIMIT 1 begrenzt die Ergebnisliste auf maximal 1 _ODER_ genau 1 - sprich beugt das nun dem leeren Resultat vor (hmm, sind zwei Fragen)?
Das LIMIT 1 begrenzt auf maximal 1 - hat nichts mit dem leeren Ergebnis zu tun.
Was genau passiert durch das "AND plicy_greylist = 'greylist'"? Wird hier sichergestellt, dass andere Policies (wie z.B. 'whitelist') eben nicht matchen - klar.
Das stellt sicher, dass nur ein Ergebnis zurückgeliefert wird (1 Zeile, 1 Feld), wenn das Ergebnis greylist ist. Vorher war in dem Fall, dass es nicht greylist war, dein result-set trotzdem 1 Zeile groß, der String darin war eben ''. Jetzt sind es 0 Zeilen.
Also allein das LIMIT 1 and den richtigen Stellen liefert postmap was es hören will eine policy (als String) oder einen leeren String ('').
Nein, das policy_grelist = 'greylist'. Hoffe ich. :-)
Viele Grüße
Am 17.02.2013 15:02, schrieb Ronny Seffner:
Wie muss nun die Abfrage für meine o.g. 'query' Variable lauten wenn die Übergaben von %s als: ronny@seffner.de dann 'greylist', info@seffner.de dann 'greylist', bernd@seffner.de dann 'greylist', irgendwas@seffner.de dann 'greylist', forward@seffner.de dann '' liefern soll?
Hallo Ronny,
alle anzeigen: SELECT `mail_virtual`.`email`, `policy` FROM `mail_users` RIGHT JOIN `mail_virtual` ON `mail_users`.`id` = `mail_virtual`.`popaccount`;
gesuchten anzeigen: SELECT `policy` FROM `mail_users` RIGHT JOIN `mail_virtual` ON `mail_users`.`id` = `mail_virtual`.`popaccount` WHERE `mail_virtual`.`email` = 'info@seffner.de';
Gruß René Thiel (Rennkuckuck) mailto:reti@rennkuckuck.de
lug-dd@mailman.schlittermann.de
-
Andreas Kretschmer -
morphium -
Rene Thiel -
Ronny Seffner