Webhooks http extension » Historie » Version 10
[X] Philipp Wolak, 05.09.2024 13:10
| 1 | 9 | [E] Axel S | {{TOC}} |
|---|---|---|---|
| 2 | |||
| 3 | 1 | [X] Philipp Wolak | h1. Webhooks |
| 4 | |||
| 5 | 8 | [E] Axel S | * Ticket: https://redmine.prodat-sql.de/issues/20417 |
| 6 | |||
| 7 | |||
| 8 | 2 | [E] Axel S | h2. Technische Voraussetzung |
| 9 | 5 | [E] Axel S | |
| 10 | * Ticket: https://redmine.prodat-sql.de/issues/20712 |
||
| 11 | 3 | [E] Axel S | |
| 12 | 7 | [E] Axel S | # Installation PostgreSQL HTTP Extension https://github.com/pramsey/pgsql-http |
| 13 | 1 | [X] Philipp Wolak | #* Version für Windows x64 und PG13: !pg13http_w64.zip! |
| 14 | #* Weitere Versionen: http://www.postgresonline.com/journal/archives/371-http-extension.html |
||
| 15 | # Datenbank-Update ausführen |
||
| 16 | #* https://ci.prodat-sql.de/sources/dbupdates/edit/?q=2024-01-17%2016:57:19 |
||
| 17 | 10 | [X] Philipp Wolak | # Pfad zu Zertifikatsdatei in dynamischen Einstellungen hinterlegen: |
| 18 | <pre><code class="sql"> |
||
| 19 | SELECT tsystem.settings__set('HTTP-curl-ca-bundle-PATH', REPLACE(current_setting('data_directory'), 'data', 'ssl/certs/curl-ca-bundle.crt')); |
||
| 20 | </code></pre> |
||
| 21 | 8 | [E] Axel S | |
| 22 | 3 | [E] Axel S | |
| 23 | h2. Verwendung: |
||
| 24 | |||
| 25 | * unsere eigenen Wrapperfunktionen verwenden: @TSystem.http_get_request(uri varchar, data json)@, ... TODO: bei Bedarf weitere erstellen |
||
| 26 | 1 | [X] Philipp Wolak | ** kümmern sich um den Pfad der vertauenswürdigen Root-CAs (Parameter @CURLOPT_CAINFO@) |
| 27 | 4 | [E] Axel S | ** versuchen den HTTP-Request im Fehlerfall erneut |
| 28 | 3 | [E] Axel S | |
| 29 | |||
| 30 | h2. Konfiguration |
||
| 31 | 1 | [X] Philipp Wolak | |
| 32 | * Webhooks werden werden über das tabellarische Modul Webhooks konfiguriert |
||
| 33 | {{collapse(Hauptmenü) |
||
| 34 | !clipboard-202312051021-nzwgt.png! |
||
| 35 | }} |
||
| 36 | * Jeder Eintrag führt zur Anlage eines Datenbank-Triggers der bei Ausführung einen HTTP-Request an die konfigurierte URL schickt. |
||
| 37 | * *Beispielkonfigurationen:* |
||
| 38 | |_. wh_name |_. wh_schema |_. wh_table |_. wh_timing |_. wh_event |_. wh_url |_. wh_data |_. wh_data_type | |
||
| 39 | | slack_webhook_eingrech | public | eingrechdokument | AFTER | INSERT | https://hooks.slack.com/triggers/...... | <notextile>'{"Rechnungsnummer": "' || ($1).beld_dokunr || '"}'</notextile> | application/json | |
||
| 40 | | slack_webhook_bdepab | public | bdepab | AFTER | INSERT | https://hooks.slack.com/triggers/....... | <notextile>'{ |
||
| 41 | "mitarbeiter": "' || (SELECT nameAufloesen(ll_db_usename) FROM llv WHERE ($1).bdab_minr = ll_minr) || '", |
||
| 42 | "bdab_id"": "' || ($1).bdab_id || '", |
||
| 43 | "bdab_anf"": "' || ($1).bdab_anf || '", |
||
| 44 | "bdab_end"": "' || ($1).bdab_end || '", |
||
| 45 | "abw_grund"": "' || (SELECT ab_txt FROM bdeabgruende WHERE ($1).bdab_aus_id = ab_id) || '" |
||
| 46 | }'</notextile> | application/json | |
||
| 47 | | teams_webhook_eingrech | public | eingrechdokument | AFTER | INSERT | https://.....webhook.office.com/webhookb2/........ | <notextile>'{ |
||
| 48 | "type"":"message", |
||
| 49 | "attachments":[ |
||
| 50 | { |
||
| 51 | "contentType":"application/vnd.microsoft.card.adaptive", |
||
| 52 | "contentUrl":null, |
||
| 53 | "content":{ |
||
| 54 | "$schema":"http://adaptivecards.io/schemas/adaptive-card.json", |
||
| 55 | "type"":"AdaptiveCard", |
||
| 56 | "version":"1.2", |
||
| 57 | "body":[ |
||
| 58 | { |
||
| 59 | "type": "TextBlock", |
||
| 60 | "text": "neue Eingangsrechnung: ' || ($1).beld_dokunr || '" |
||
| 61 | } |
||
| 62 | ] |
||
| 63 | } |
||
| 64 | } |
||
| 65 | ] |
||
| 66 | }'</notextile> | application/json | |