Vorschlag: Tutorial zum Senden von E-Mails

Florian

Zur (vorläufigen) Vollendung meiner REI3-App würde ich gerne noch getriggerte Benachrichtigungen per Mail verschicken. Zum Beispiel soll der Laborassistent eine Mail erhalten, sobald eine neue Bestellung eingeht. Idealerweise enthielte die Mail bereits ein paar Informationen zur Bestellung und vielleicht sogar einen Link zu der Bestellung selbst auf REI3.

Soweit ich es in der Tickets-App sehe, sind dafür Backend- und Frontend-Funktionen nötig. Leider übersteigt die Komplexität des Mail-Moduls in dieser App meine Fähigkeit, das für mich Wesentliche heraus zu destillieren.

Bitte versteht es absolut nicht als Kritik, aber ich habe gemerkt, dass ich mit ausschliesslich der vorhandenen Dokumentation in Bezug auf Funktionen nicht so gut vorankomme. Wenn ihr es bei Gelegenheit einrichten könnt, wäre daher ein kleines Tutorial oder ein MWE zum Thema Mailversand super hilfreich... 😀 🙏

lsw-fabian

In Tickets sind viele Trigger und Comfortfunktion zum E-Mail-Versand inkludiert, dass macht die App wirklich zu keinem guten Beispiel 😃 (vielleicht ist Abwesenheit hier der bessere Showcase).

Theoretisch ist das Senden von E-Mail gar nicht so schwer - wenn das getriggert passieren soll, brauchst du dafür auch keine Frontend Funktion sondern nur eine im Backend.

Text:
Theoretisch solltest du für den Text in der E-Mail ein Template erstellen und das nicht nicht in der Funktion stehen haben (das lässt sich einfach viel leichter anpassen) - ich empfehle dir, dir dafür eine neue Relation anzulegen.
Verwende dann einen Platzhalter wie etwa [LINK] - wenn du einen Link zum Datensatz erstellen willst

Funktion: Du benötigst eine Backend Trigger Funktion für dein Vorhaben. Diese Funktion musst du auch in der Relation als Trigger (After oder Before) hinterlegen. Wenn die E-Mail nur bei neuen Bestellungen gesendet werden soll, reicht es diesen auf "Insert" zu lassen. -Je nachdem von welchen Relationen zu welcher Zeit du deine Daten holst, musst du den Trigger nachgelagert als "After" Trigger laufen lassen.

Ich habe dir mal eine vereinfachte Version erstellt - in dieser hole ich per SQL Abfrage nur den Empfänger aus einem Datensatz, alle anderen Optionen habe ich fest definiert. -Vielleicht reicht dir das schon als Hilfestellung für die grundlegende Funktion. Themen wie das Hinzufügen des Links würde ich danach angehen. Alles was nicht befüllt ist, ist optional (zB CC und Anhänge) und muss nicht mit Daten befüllt werden.

$BODY$
DECLARE
	subject text := 'Neue Bestellung eingegangen';
	body text := 'Dies ist ein Test Body';
	to_list text;
	cc_list text;
	bcc_list text;
	account_name text := 'MeinSendeAccount'; --Name deines im Adminpanel hinterlegten SMTP Accounts
	attach_record_id integer; -- Solltest du Anhänge hinzufügen wollen
	attach_attribute_id uuid; -- die UUID des Anhang-Attributes

BEGIN
	--hole Empfänger
	SELECT (test_nm_copy.node.mail) INTO to_list
	FROM {test_nm_copy}.[node]
	WHERE (test_nm_copy.node.id) = NEW.(test_nm_copy.node.id); --NEW. bezieht sich auf den aktuellen Datensatz


	PERFORM instance.mail_send(subject,body,to_list,bcc_list,account_name,attach_record_id,attach_attribute_id);

	RETURN 0;
END;
$BODY$

Die Idee für ein Tutorial-Video nehme ich gerne mit auf - ich denke aber, dass einige andere, grundlegendere Themen da zu erst dran kommen müssen.

gabriel

Keine Sorge - uns ist bewusst, dass es sehr viele Stellen in REI3 gibt, wo mehr Dokumentation, Beispiele oder auch nur eine bessere Benutzeroberfläche die Benutzerung deutlich einfacher machen könnte. Kritik/Feedback ist da auf jeden Fall angebracht und auch gerne gesehen 🙂

Da wir, neben Projekten und Entwicklung neuer Features für Kundenanforderungen, gut zu tun haben, verlassen wir uns auf Feedback, um Verbesserungen zu priorisieren. Anderweitig stecken wir viel Energie in Verbesserungen, die dann später vielleicht keiner braucht - ein klassischer Fehler 😉

In diesem Fall könnten wir eine Vorlage für diese Art von Funktion einbauen. Quasi: "Wie erzeuge ich E-Mails?". Ich packs mit auf die Taskliste.

Glücklicherweise ist dieser Fall sogar sehr einfach - kurz umrissen:

Es reicht eine einzelne Backend-Funktion, die du bspw. per Trigger anstoßen kannst. (Wenn Datensatz für eine Bestellung erstellt worden ist, sende E-Mail.)
In dieser Backend-Funktion musst du einmal die Funktion instance.mail_send() aufrufen - die Parameter hierfür sind im Kontextmenü im Editor für Backend-Funktionen zu sehen.
Der knifflige Teil könnte sein, dass du Textvorlagen hast und dann vielleicht Platzhalter austauschen möchtest. Aber auch das ist verhältnismäißg einfach lösbar:
- Textbausteine holst du per SELECT von anderen Relationen. Und dann kannst du per Funktionen zum Ersetzen von Text diese Platzhalter mit Werten wie bspw. eine Anrede vom Empfänger austauschen.

Die Anwendung "Tickets" ist eine unserer komplexesten Apps. Sehr cool, um zu zeigen, was möglichst ist, aber vielleicht nicht das beste Beispiel für einfache Anforderungen. In der App "Abwesenheit" ist auch ein Mailversand drinne - vor allem ist dieser deutlich simpler implementiert und eignet sich daher besser als Vorlage.

@lsw-fabian: Haben wir mal wieder fast gleichzeitig, die gleiche Anfrage bearbeitet 🙂

lsw-fabian

@gabriel Stimmt 🙂 - musste auch schon schmuntzeln

Florian

Erneut besten Dank für eure Erklärungen und das Code-Beispiel. Leider schaffe ich es bisher dennoch nicht, eine E-Mail zu erzeugen. Das habe ich bisher gemacht:

Die Backend Funktion send_mail habe ich als Trigger markiert und maximal vereinfacht:

$BODY$
DECLARE
	subject text := 'Neue Bestellung';
	body text := 'Dies ist ein Test Body';
	to_list text := 'my@email.com';
BEGIN
	PERFORM instance.mail_send(subject,body,to_list);
	RETURN 0;
END;
$BODY$

Dann habe ich die Funktion als Trigger bei der Relation order eingetragen mit Startet AFTER, INSERT 1, UPDATE 0, DELETE 0, EACH ROW 0, DEFERRED 0. Wenn ich nun aber versuche, eine neue order zu erstellen, kommt die folgende Fehlermeldung:
ERROR: cannot return non-composite value from function returning composite type (SQLSTATE 42804)

Ich hatte die Funktion überhaupt nur deshalb aufs Minimum vereinfacht, weil ich mit der von Fabian vorgeschlagenen Funktion zuvor schon eine andere Fehlermeldung erhalten hatte:
ERROR: function instance.mail_send(text, text, text, text, text, integer, uuid) does not exist (SQLSTATE 42883)

Könnt ihr mit den Fehlermeldungen etwas anfangen und mir vielleicht sogar sagen, was ich anders machen müsste?

gabriel

Florian
Mit Postgres PL/SQL muss man erstmal warm werden (das ist die Sprache, mit der Backend-Funktionen ausgeführt wird).

Zu deinen Fehlern:
In dem Beispiel von @lsw-fabian hast du eine reguläre Funktion, die du bspw. aus einer Trigger-Funktion hättest starten können - deswegen hat sie den Return-Wert '0'. In einer Trigger-Funktion musst du immer entweder NEW (bei neuen oder veränderten Datensätzen) oder OLD (bei gelöschten Datensätzen) zurückgeben. Ansonsten kriegst du den Fehler: ERROR: cannot return non-composite value... - für einen INSERT-Trigger wäre RETURN NEW; korrekt.

Trigger-Funktionen sind für viele Anwendungsfälle extrem praktisch - leider haben sie aber spezielle Regeln. Das ist allgemein das Thema - PL/SQL ermöglicht sehr vieles, etwas Einarbeitung ist aber leider notwendig.

Wegen der 2. Fehlermeldung: @lsw-fabian hat den Parameter für CC-Adressen in instance.mail_send() vergessen - damit stimmen beim Aufruf die Parameter nicht. Alle Parameter nach Body sind optional (kannst du im Kontextmenü der Funktion nachschauen) - wenn sie angegeben werden, müssen Reihenfolge und Typ aber stimmen.

So wie du es hast, ist es richtig. Sobald der Rückgabewert in der Funktion auf RETURN NEW; gestellt ist, sollte es funktionieren.

lsw-fabian

Stimmt, da ist mir auf die schnelle ein Fehler unterlaufen. Danke fürs korrigieren @gabriel

Florian

Ich bin schon ein ganzes Stück vorangekommen (denke ich). Und zwar kann ich bereits vorhandene Datensätze auslesen und zum Mailversand verwenden. Was noch nicht gelingt, ist das Auslesen von Daten aus dem neuen NEW Datensatz. Hier mein Code:

$BODY$
DECLARE
	mail_event text:= 'order_new'; -- Bezeichnung in Relation `mail`
	mail_subject text;
	mail_body text;
	mail_recipient_id integer;
	mail_recipient text;

	order_orderer_id integer;
	order_orderer text;
	order_date_ready text;
	order_note text;

BEGIN

	SELECT subject, body, recipient 
	INTO mail_subject, mail_body, mail_recipient_id
	FROM {kus_physlab}.[mail]
	WHERE event = mail_event;

	SELECT email INTO mail_recipient
	FROM {kus_physlab}.[user]
	WHERE id = mail_recipient_id;

	
	-- Versuche, das Feld `note` aus der neuen `order` zu lesen:

	-- SELECT (kus_physlab.order.note) INTO order_note
	-- FROM {kus_physlab}.[order]
	-- WHERE (kus_physlab.order.id) = NEW.(kus_physlab.order.id);
	-- mail_body := 'order_note: ' || order_note;

	-- mail_body := 'order_note: ' || (NEW.note);
	
	-- mail_body := 'order_note: ' || NEW.note;


	-- Auch das Setzen von Text im neuen Datensatz gelingt nicht:

	NEW.test_text := 'Mein neuer Test-Text.';
	
	PERFORM instance.mail_send(mail_subject,mail_body,mail_recipient);

	RETURN NEW;
END;
$BODY$

Zwei Dinge funktionieren im Code einfach nicht:

Auf keine der drei auskommentierten Arten kann ich die Relation note aus dem neuen Datensatz order auslesen und im Body der Mail verschicken. Es erscheint immer die Fehlermeldung Column 'body" of relation "mail_spool' has no value or could not be resolved.
Ebenfalls schaffe ich es nicht, testweise einen Eintrag im NEW Datensatz zu verändern, siehe Quellcode. In der Dokumentation Trigger Functions wird z.B. NEW.last_date verwendet, weshalb ich dachte, dass ich mit meinem Befehl einfach den Inhalt des Attributs test_text verändern könnte.

Könnt ihr meinem obigen Code entnehmen, weshalb das nicht funktioniert?

Und noch eine dritte Frage: Was wäre eine gute Methode, Backend Funktionen zu debuggen, also zum Beispiel schnell sehen zu können, ob das Auslesen von Daten funktioniert. Ich schreibe das gerade immer in die zu versendende Mail, muss dann aber immer erst mal auf sie warten...

gabriel

Hallo Florian,

die Fehlermeldung Column 'body" of relation "mail_spool' has no value or could not be resolved kommt, wenn du beim Aufruf der Funktion instance.mail_send() einen NULL-Wert im Body-Parameter hast.

Aber ich glaube, ich sehe das Problem grade in einen deiner vorherigen Posts: Du musst deinen Trigger EACH ROW ausführen - anderweitig wird die Variable NEW nicht befüllt. Ein Trigger der nicht EACH ROW ausgeführt wird, läuft nur einmal auch wenn hunderte Datensätze erzeugt werden.

Stattdessen möchtest du mit jeden erzeugten Datensatz (mit jeder Row) etwas tun. Sobald du den Trigger auf EACH ROW umschaltest, kannst du sowohl NEW auslesen, als auch neue Werte zuweisen.

Bzgl. des Debuggings: Da gibt es mehrere Optionen. Für allgemeine Tests (nicht Trigger-bezogen) liefere ich Ergebnisse oder Fehler normalerweise als RETURN-Wert - dann kann ich direkt im Builder die Funktion testweise ausführen. Wenn du aber Code direkt in einem Trigger trouble-shooten möchtest, würde sich die instance.log_error()-Funktion anbieten.

Du hast bei den Platzhaltern (rechts) die Liste aller verfügbaren Instanzfunktionen und jeweils die Kontexthilfe dazu. Mit Log-Error lässt sich beliebiger Text ins REI3-Log schreiben, was du dann im Admin-Panel auslesen kannst.

Florian

Wow, es macht richtig Spass, ein bisschen an der Oberfläche von Postgres PL/SQL zu kratzen.

Es ist zwar kein richtiges MWE mehr, aber für alle, die Mails mit Datensätzen verschicken wollen, halte ich hier mal meinen funktionierenden Zwischenstand fest:

$BODY$
DECLARE
	mail_event text:= 'order_new';
	mail_subject text;
	mail_body text;
	mail_recipient text;

	order_orderer text;
	order_date_ready text;
	order_note text;
	order_experiments text;
BEGIN

	SELECT m.subject, m.body, u.email 
		INTO mail_subject, mail_body, mail_recipient
	FROM kus_physlab.mail m
		JOIN kus_physlab.user u ON u.id = m.user_id_recipient
	WHERE m.event = mail_event;

	SELECT u.nickname 
		INTO order_orderer
	FROM kus_physlab.user u
	WHERE u.id = NEW.user_id_orderer;
	
	order_date_ready := TO_CHAR(TO_TIMESTAMP(NEW.date_ready), 'DD.MM.YYYY');
	order_note := NEW.note;

	mail_subject := REPLACE(mail_subject, '[order_orderer]', order_orderer);
	mail_subject := REPLACE(mail_subject, '[order_date_ready]', order_date_ready);
	mail_body := REPLACE(mail_body, '[order_orderer]', order_orderer);
	mail_body := REPLACE(mail_body, '[order_date_ready]', order_date_ready);
	mail_body := REPLACE(mail_body, '[order_note]', order_note);
	PERFORM instance.mail_send(mail_subject,mail_body,mail_recipient);

	RETURN NEW;
END;
$BODY$

Dabei war es für mich super hilfreich, schnell sehen zu können, ob Daten (hier NEW.note) richtig extrahiert wurden:

	PERFORM instance.log_warning(NEW.note, 'kus_physlab');

Florian

Zwei Probleme habe ich aber noch.

1) Ich schaffe es nicht, die Namen aller Experimente, welche in der neuen Bestellung angegeben wurden, auszulesen und anzeigen zu lassen. Mein Ansatz ist:

	SELECT e.name
		INTO order_experiments
	FROM kus_physlab.order_experiment oe
		JOIN kus_physlab.experiment e ON e.id = oe.experiment_id
	WHERE oe.order_id = NEW.id;

	mail_body := 'order_experiments: ' || order_experiments;
	PERFORM instance.log_warning(mail_body, 'kus_physlab');

Wenn ich dies ausprobiere, erhalte ich die Fehlermeldung Spalte "message" of relation "log" beinhaltet keinen Wert oder kann nicht aufgelöst werden.
a) Was muss ich machen, damit die Namen angezeigt werden, also z.B. als kommaseparierte Liste oder mit Zeilenumbrüchen dazwischen?
b) Gibt es eine Lösung, die robust ist für den Fall, dass der Bestellung bisher keine Experimente hinzugefügt wurden?

2) Es klappt nicht, Feldwerte in der Backend Funktion zu setzen. Mit diesen Zeilen

	NEW.test := 'Test-Text';
	PERFORM instance.log_warning(NEW.test, 'kus_physlab');

Wird im Log zwar der Test-Text angezeigt, aber nicht im Formular in der App.

Habt ihr vielleicht noch eine Idee, was ich falsch mache?

gabriel

Hallo Florian,

zu deinen Themen:

1) s. a) weiter unten.

a) Dein SELECT hier kriegt im Zweifel mehr als eine Datenzeile zurück. Wenn du mehrere Datenzeilen zu einen einzelnen Wert zusammenbauen willst, musst du aggregieren. Das wäre bspw. sowas: SELECT STRING_AGG(e.name,', ') INTO order_experiments. Dann erhältst du eine Komma-separierte Liste an Werten als einen TEXT-Wert in deiner Variable. Das wird auch der Grund sein, warum du beim Loggen einen Fehler bekommst - mail_body war mutmaßlich NULL.

b) Hierzu machst du eine Abfrage gegen NULL, wie z. B.:
IF order_experiments IS NULL THEN RETURN NEW; END IF;

Wenn eine Aggregation keinen Input erhält, ergibt das Ergebnis NULL. Das speicherst du in order_experiments und damit kannst du auch prüfen, ob was zurück gekommen ist.

2) Da muss ich grade raten, aber kann es sein, dass du einen AFTER-Trigger nutzt?

Der BEFORE-Trigger gibt dir eine Zeile (row) zum Bearbeiten und speichert dann diese als NEW ab. Ein AFTER-Trigger feuert, nachdem die Zeile bereits in die Datenbank geschrieben worden ist - d. h. Änderungen an NEWwerden ignoriert.

Wir bieten bei Interesse auch Crash-Kurse an, wenn du mit REI3 in Zukunft mehr machen möchtest. Spart oft sehr viel Zeit - es ist natürlich auch ok, wenn du dich selbst reinfuchsen willst 😉

Florian

2) Na klar, du hast komplett recht. Ich weiss nicht mehr, weshalb ich beim Einrichten des Triggers von den Standardeinstellungen abgewichen bin, ohne genau zu wissen, wofür sie gut sind. Aber wie so oft in solchen Fällen ist es am Ende ärgerlich, wie viel Zeit deshalb hinter raus draufgeht...

1) Kann es sein, dass ich auf diese Weise gar nicht an Daten der n:m-Beziehung kommen kann? Ich habe den Verdacht, dass die Zeilen in order_experiment erst geschrieben werden, nachdem die neue Zeile in order geschrieben und die Triggerfunktion ausgeführt wurde.

Mit dem folgenden Code

	SELECT STRING_AGG(oe.id::TEXT,', ')
		INTO oe_id
	FROM kus_physlab.order_experiment oe
	WHERE oe.order_id = NEW.id;

	IF oe_id IS NULL THEN
		oe_id := 'NULL';
	END IF;

	temp_text := 'oe_id: ' || oe_id;
	PERFORM instance.log_warning(temp_text, 'kus_physlab');

bekomme ich jedenfalls auch immer NULL ausgegeben.

gabriel

Florian
Um dazu was sagen zu können, müsste ich genau wissen, welche Relationen du hast und wie deine Formulare aussehen.

Wenn du bspw. auf einem Formular mehrere Relationen per Join verbunden hast und dann einen neuen Datensatz erstellst, wird zuerst der Datensatz der Relation 0 angelegt (was den Trigger für diese Relation auslöst) und danach die Datensätze der anderen Relationen. Muss auch so sein, weil ansonsten die Referenz auf den ursprünglichen Datensatz nicht möglich ist.

Für genau diesen Fall gibt es ironischerweise den AFTER-Trigger mit der DEFERRED Option. Wenn DEFERRED aktiv ist, wird ein Trigger erst dann ausgeführt, wenn alle Veränderungen durch sind. Das würde dieses Problem lösen - mit einem AFTER-Trigger kannst du aber keine Änderungen an NEW vornehmen. Es hält dich aber keiner davon ab, per UPDATE-Statement diese Änderungen durchzuführen.

Florian

Juhu, es funktioniert alles! Sensationell!

Jetzt habe ich also vermutlich alle ungeeigneten Einstellungen für Trigger-Funktionen mal durchprobiert und trotz richtiger Ideen für die Datenbankabfragen häufig Fehler produziert. Solltet ihr mal ein Tutorial zu diesem Thema machen, wisst ihr jetzt immerhin, was ein Anfänger alles nicht weiss und wo sich Hinweise lohnen. 😉 Als Lehrer weiss ich, dass es manchmal nicht so leicht ist, sich in die Gedanken der Schüler hineinzuversetzen...

gabriel

Florian
Der Löwenanteil von REI3-Authoren nutzt auch keine Trigger. Es ist eher eine Expertenfunktion aktuell. Aber du hast Recht, das ist eines von den Themen, wo Schulungsmaterialen auf jeden Fall hilfreich wären. Die Liste an Themen für die nächsten Youtube-Videos wächst an 🙂