Der AnstoĂ fĂŒr das Schreiben dieses Essays war ein Artikel . Ich fand auch den vor vier Jahren veröffentlichten Artikel interessant â .
Es schien interessant zu sein, dass dieselbe Idee â "die GeschĂ€ftslogik in der DB zu implementieren".
nicht nur mir allein in den Kopf kam.
AuĂerdem wollte ich fĂŒr die Zukunft einige interessante Entwicklungen festhalten, die wĂ€hrend der Implementierung entstanden sind. Besonders in Anbetracht der Tatsache, dass vor relativ kurzer Zeit eine strategische Entscheidung getroffen wurde, die Architektur zu wechseln und die GeschĂ€ftslogik auf die Backend-Ebene zu verlagern. So werden alles, was erarbeitet wurde, bald niemandem mehr von Nutzen sein und niemanden interessieren.
Die beschriebenen Methoden sind kein Geheimnis und nichts AuĂergewöhnliches, Know-how, alles nach Klassik und wurde mehrfach umgesetzt (ich selbst habe einen Ă€hnlichen Ansatz vor 20 Jahren bei Oracle angewendet). Ich habe einfach alles an einem Ort zusammengefasst. Vielleicht kommt es jemandem zugute. Wie die Praxis gezeigt hat, kommt dieselbe Idee oft unabhĂ€ngig von verschiedenen Menschen. AuĂerdem ist es nĂŒtzlich, um etwas fĂŒr sich selbst als Erinnerung zu hinterlassen.
NatĂŒrlich ist nichts in dieser Welt perfekt, Fehler und Tippfehler sind leider möglich. Kritik und Anmerkungen sind jederzeit willkommen und erwartet. Und noch ein kleines Detail â spezifische Implementierungsdetails wurden weggelassen. SchlieĂlich wird alles momentan in einem funktionierenden Projekt verwendet. Dieser Artikel ist also eher ein Essay und eine Beschreibung des allgemeinen Konzepts, nicht mehr. Ich hoffe, die Details reichen aus, um ein allgemeines Bild zu verstehen.
Die Grundidee â âteile und herrsche, verstecke und besitzeâ
Die Idee ist klassisch â ein separates Schema fĂŒr Tabellen, ein separates Schema fĂŒr gespeicherte Funktionen.
Der Kunde hat keinen direkten Zugriff auf die Daten. Alles, was der Kunde tun kann, ist, eine gespeicherte Funktion aufzurufen und die erhaltene Antwort zu verarbeiten.
Rollen
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
Schemata
Schema zur Speicherung von Tabellen
Zieltabellen, die GeschÀftseinheiten implementieren.
CREATE SCHEMA store AUTHORIZATION store;
Schema der Systemfunktionen
Systemfunktionen, insbesondere zum Protokollieren von TabellenÀnderungen.
CREATE SCHEMA sys_functions AUTHORIZATION sys_functions;
Schema der lokalen Audits
Funktionen und Schemata zur DurchfĂŒhrung eines lokalen Audits von gespeicherten Funktionen und Ănderungen an Zieltabellen.
CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;
Schemata fĂŒr Dienstleistungsfunktionen
Funktionen fĂŒr Dienstleistungs- und DML-Funktionen.
CREATE SCHEMA service_functions AUTHORIZATION service_functions;
Schemata fĂŒr GeschĂ€fts-funktionen
Funktionen fĂŒr die endgĂŒltigen GeschĂ€fts-funktionen, die vom Kunden aufgerufen werden.
CREATE SCHEMA business_functions AUTHORIZATION business_functions;
Zugriffsrechte
Rolle â DBA hat vollstĂ€ndigen Zugriff auf alle Schemata (separat von der Rolle DB Owner).
CREATE ROLE dba_role;
GRANT store TO dba_role;
GRANT sys_functions TO dba_role;
GRANT loc_audit_functions TO dba_role;
GRANT service_functions TO dba_role;
GRANT business_functions TO dba_role;
Rolle â USER hat das Recht EXECUTE im Schema business_functions.
CREATE ROLE user_role;
Berechtigungen zwischen Schemata
GRANT
Da alle Funktionen mit dem Attribut SECURITY DEFINER ist die Anweisung erforderlich REVOKE EXECUTE ON ALL FUNCTION⊠FROM public;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA sys_functions FROM public ;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA loc_audit_functions FROM public ;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA service_functions FROM public ;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA business_functions FROM public ;
GRANT USAGE ON SCHEMA sys_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA sys_functions TO dba_role ;
GRANT USAGE ON SCHEMA loc_audit_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA loc_audit_functions TO dba_role ;
GRANT USAGE ON SCHEMA service_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA service_functions TO dba_role ;
GRANT USAGE ON SCHEMA business_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA business_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA business_functions TO user_role ;
GRANT ALL PRIVILEGES ON SCHEMA store TO GROUP business_functions ;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA store TO business_functions ;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA store TO business_functions ;
Das Datenbankschema ist jetzt bereit. Sie können mit der BefĂŒllung der Daten beginnen.
Zieltabelle
Die Erstellung der Tabellen ist trivial. Es gibt keine Besonderheiten, bis auf die Entscheidung, auf die Verwendung von SERIAL zu verzichten und Sequenzen explizit zu generieren. DarĂŒber hinaus sollte die Anweisung
COMMENT ON ...Kommentare fĂŒr Umgebungen und Clustern des Projekts verwendet wird. Dieses Prinzip bildet die Grundlage fĂŒr ein gutes Objekte, ohne Ausnahmen.
Lokaler Audit
Zur Protokollierung der AusfĂŒhrung gespeicherter Funktionen und Ănderungen an Zieltabellen wird eine lokale Audit-Tabelle verwendet, die unter anderem Details zur Kundenverbindung, das Label des aufgerufenen Moduls sowie die tatsĂ€chlichen Werte der Eingabe- und Ausgabewerte in JSON-Format enthĂ€lt.
Systemfunktionen
Dienen zur Protokollierung von Ănderungen in den Zieltabellen. Sie bestehen aus Triggerfunktionen.
Vorlage â Systemfunktion
---------------------------------------------------------
-- INSERT
CREATE OR REPLACE FUNCTION sys_functions.table_insert_log ()
RETURNS TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'insert' , json_build_object('id', NEW.id) );
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_insert AFTER INSERT ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_insert_log();
---------------------------------------------------------
-- UPDATE
CREATE OR REPLACE FUNCTION sys_functions.table_update_log ()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.column != NEW.column
THEN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'update' , json_build_object('OLD.column', OLD.column , 'NEW.column', NEW.column ) );
END IF ;
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_update AFTER UPDATE ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_update_log ();
---------------------------------------------------------
-- DELETE
CREATE OR REPLACE FUNCTION sys_functions.table_delete_log ()
RETURNS TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'delete' , json_build_object('id', OLD.id ) );
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_delete AFTER DELETE ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_delete_log ();Dienstfunktionen
Sie dienen der DurchfĂŒhrung von Service- und DML-Operationen an den Zieltabelle.
Vorlage â Dienstfunktion
--INSERT
--GIBT ID DER NEUEN ZEILE ZURĂCK
CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
RETURNS integer AS $$
DECLARE
new_id integer ;
BEGIN
-- Generiere neue ID
new_id = nextval('store.table.seq');
-- FĂŒge in die Tabelle ein
INSERT INTO store.table
(
id ,
column
)
VALUES
(
new_id ,
new_column
);
RETURN new_id ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
--DELETE
--GIBT ANZAHL DER GELĂSCHTEN ZEILEN ZURĂCK
CREATE OR REPLACE FUNCTION service_functions.table_delete ( current_id integer )
RETURNS integer AS $$
DECLARE
rows_count integer ;
BEGIN
DELETE FROM store.table WHERE id = current_id;
GET DIAGNOSTICS rows_count = ROW_COUNT;
RETURN rows_count ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- DETAILS AKTUALISIEREN
-- GIBT ANZAHL DER AKTUALISIERTEN ZEILEN ZURĂCK
CREATE OR REPLACE FUNCTION service_functions.table_update_column
(
current_id integer
,new_column store.table.column%TYPE
)
RETURNS integer AS $$
DECLARE
rows_count integer ;
BEGIN
UPDATE store.table
SET
column = new_column
WHERE id = current_id;
GET DIAGNOSTICS rows_count = ROW_COUNT;
RETURN rows_count ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;GeschÀftsfunktionen
Sie sind fĂŒr die endgĂŒltigen GeschĂ€ftsfunktionen vorgesehen, die vom Kunden aufgerufen werden. Sie geben immer zurĂŒck â JSON. Um Fehler bei der AusfĂŒhrung abzufangen und zu protokollieren, wird ein Block verwendet, EXCEPTION.
Vorlage â GeschĂ€fts Funktion
CREATE OR REPLACE FUNCTION business_functions.business_function_template(
--Eingabeparameter
)
RETURNS JSON AS $$
DECLARE
------------------------
--fĂŒr Ausnahmebehandlung
error_message text ;
error_json json ;
result json ;
------------------------
BEGIN
--PROTOKOLLIERUNG
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'STARTED',
json_build_object
(
--Eingabeparameter
)
);
PERFORM business_functions.notice('business_function_template');
--START BUSINESS TEIL
--ENDE BUSINESS TEIL
-- ERFOLGREICHES ERGEBNIS
PERFORM business_functions.notice('result');
PERFORM business_functions.notice(result);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'FINISHED',
json_build_object( 'result',result )
);
RETURN result ;
----------------------------------------------------------------------------------------------------------
-- AUSNAHMEN BEHANDELN
EXCEPTION
WHEN OTHERS THEN
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'STARTED',
json_build_object
(
--Eingabeparameter
) , TRUE );
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR',
json_build_object('SQLSTATE',SQLSTATE ), TRUE
);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR',
json_build_object('SQLERRM',SQLERRM ), TRUE
);
GET STACKED DIAGNOSTICS error_message = RETURNED_SQLSTATE ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-RETURNED_SQLSTATE',json_build_object('RETURNED_SQLSTATE',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = COLUMN_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-COLUMN_NAME',
json_build_object('COLUMN_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = CONSTRAINT_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-CONSTRAINT_NAME',
json_build_object('CONSTRAINT_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_DATATYPE_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_DATATYPE_NAME',
json_build_object('PG_DATATYPE_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-MESSAGE_TEXT',json_build_object('MESSAGE_TEXT',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = SCHEMA_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-SCHEMA_NAME',json_build_object('SCHEMA_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_DETAIL ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_EXCEPTION_DETAIL',
json_build_object('PG_EXCEPTION_DETAIL',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_HINT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_EXCEPTION_HINT',json_build_object('PG_EXCEPTION_HINT',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_CONTEXT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_EXCEPTION_CONTEXT',json_build_object('PG_EXCEPTION_CONTEXT',error_message ), TRUE );
RAISE WARNING 'ALARM: %' , SQLERRM ;
SELECT json_build_object
(
'isError' , TRUE ,
'errorMsg' , SQLERRM
) INTO error_json ;
RETURN error_json ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;Fazit
Um das Gesamtbild zu beschreiben, denke ich, dass das vollkommen ausreicht. Wenn jemand an Details und Ergebnissen interessiert ist â schreibt Kommentare, ich ergĂ€nze das Bild gerne mit zusĂ€tzlichen Feinheiten.
P.S.
Protokollierung eines einfachen Fehlers â Typ des Eingabeparameters
-[ RECORD 1 ]-
date_trunc | 2020-08-19 13:15:46
id | 1072
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | STARTED
jsonb_pretty | {
| "dko": {
| "id": 4,
| "type": "Type1",
| "title": "ERSTELLT VON addKD",
| "Weight": 10,
| "Tr": "300",
| "reduction": 10,
| "isTrud": "TRUE",
| "description": "beschreibung",
| "lowerTr": "100",
| "measurement": "measurement1",
| "methodology": "m1",
| "passportUrl": "files",
| "upperTr": "200",
| "weightingFactor": 100.123,
| "actualTrValue": null,
| "upperTrCalcNumber": "120"
| },
| "CardId": 3
| }
-[ RECORD 2 ]-
date_trunc | 2020-08-19 13:15:46
id | 1073
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER
jsonb_pretty | {
| "SQLSTATE": "22P02"
| }
-[ RECORD 3 ]-
date_trunc | 2020-08-19 13:15:46
id | 1074
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER
jsonb_pretty | {
| "SQLERRM": "ungĂŒltige Eingabesyntax fĂŒr Typ numerisch: "null""
| }
-[ RECORD 4 ]-
date_trunc | 2020-08-19 13:15:46
id | 1075
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER-RETURNED_SQLSTATE
jsonb_pretty | {
| "RETURNED_SQLSTATE": "22P02"
| }
-[ RECORD 5 ]-
date_trunc | 2020-08-19 13:15:46
id | 1076
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER-SPALTENNAME
jsonb_pretty | {
| "COLUMN_NAME": ""
| }
-[ RECORD 6 ]-
date_trunc | 2020-08-19 13:15:46
id | 1077
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER-BEDINGUNGNAME
jsonb_pretty | {
| "CONSTRAINT_NAME": ""
| }
-[ RECORD 7 ]-
date_trunc | 2020-08-19 13:15:46
id | 1078
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER-PG_DATENTYP_NAME
jsonb_pretty | {
| "PG_DATATYPE_NAME": ""
| }
-[ RECORD 8 ]-
date_trunc | 2020-08-19 13:15:46
id | 1079
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER-MELDUNGSTEXT
jsonb_pretty | {
| "MESSAGE_TEXT": "ungĂŒltige Eingabesyntax fĂŒr Typ numerisch: "null""
| }
-[ RECORD 9 ]-
date_trunc | 2020-08-19 13:15:46
id | 1080
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER-SCHEMANAME
jsonb_pretty | {
| "SCHEMA_NAME": ""
| }
-[ RECORD 10 ]-
date_trunc | 2020-08-19 13:15:46
id | 1081
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER-PG_AUSNAHME_DETAIL
jsonb_pretty | {
| "PG_EXCEPTION_DETAIL": ""
| }
-[ RECORD 11 ]-
date_trunc | 2020-08-19 13:15:46
id | 1082
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER-PG_AUSNAHME_HINWEIS
jsonb_pretty | {
| "PG_EXCEPTION_HINT": ""
| }
-[ RECORD 12 ]-
date_trunc | 2020-08-19 13:15:46
id | 1083
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER-PG_AUSNAHME_KONTEXT
jsonb_pretty | {
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | FEHLER-MELDUNGSTEXT
jsonb_pretty | {
| "MESSAGE_TEXT": "ungĂŒltige Eingabesyntax fĂŒr Typ numerisch: "null""
| }Quelle: habr.com
