Etüüdi kirjutamise innustav motiiv oli artikkel . Samuti tundus huvitav artikkel, mis ilmus 4 aastat tagasi — .
Huvitav oli, et üks ja sama mõte - "rakendada äriloogikat andmebaasis".
tuli pähe mitte ainult mulle.
Samuti tahtsin tulevikuks säilitada, endale huvitavad arendused, mis tekkisid rakendamise käigus. Eriti arvestades, et suhteliselt hiljuti tehti strateegiline otsus arhitektuuri muutmiseks ja äriloogika üleviimiseks tagaküljele. Nii et kõik, mis oli kokku kogutud, varsti ei ole kellelegi vajalik ja kedagi ei huvita.
Kirjeldatud meetodid ei ole mingi avastus ega eriline know how, kõik klassikaline ja on korduvalt rakendatud (mõne aasta eest rakendasin ma näiteks sarnast lähenemist Oracle'is). Üksnes otsustasin kõik ühte kohta koguda. Äkki osutub kellelegi utile. Praktika on näidanud, et üsna sageli tuleb üks ja sama idee sõltumatult erinevatele inimestele meelde. Ja endale mälu jaoks jätta on kasulik.
Loomulikult ei ole selles maailmas miski täiuslik, vead ja trükivead on kahjuks võimalikud. Kriitika ja märkused on igati teretulnud ja oodatud. Ja veel üks väike detail — konkreetsed rakendamise üksikasjad on vahele jäetud. Lõppude lõpuks kasutatakse kõike endiselt reaalses projektis. Nii et artikkel on nagu etüüd ja üldise kontseptsiooni kirjeldus, mitte rohkem. Loodan, et üldise pildi mõistmiseks on üksikasjad piisavad.
Üldine idee — "jaga ja valitse, peida ja omanda"
Idee on klassikaline — eraldi schéma tabelite jaoks, eraldi schéma salvestatud funktsioonide jaoks.
Kliendil ei ole andmetele otseseid juurdepääse. Kõik, mida klient saab teha, on ainult kutsuda salvestatud funktsiooni ja töödelda saadud vastust.
Rollid
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
Schémad
Tabeli salvestamise schéma
Sihtotstarbelised tabelid, mis rakendavad teema entiteete.
CREATE SCHEMA store AUTHORIZATION store ;
Süsteemsete funktsioonide schéma
Süsteemsed funktsioonid, sealhulgas tabelite muutmise logimiseks.
CREATE SCHEMA sys_functions AUTHORIZATION sys_functions ;
Kohalik audit schéma
Funktsioonid ja tabelid lokaalse audititeenuse rakendamiseks talletatud funktsioonide täitmisel ja sihTabela muutmisel.
LOO SCHEMA loc_audit_functions VOLITUS loc_audit_functions;
Teenuste funktsioonide skeem
Funktsioonid teenuste ja DML funktsioonide jaoks.
LOO SCHEMA service_functions VOLITUS service_functions;
Ärifunktsioonide skeem
Funktsioonid lõppkasutaja ärifunktsioonide jaoks, mida kutsub esile klient.
LOO SCHEMA business_functions VOLITUS business_functions;
Juurdepääsuõigused
Roll — DBA omab täielikku juurdepääsu kõigile skeemidele (eraldatud DB omanikust rollist).
LOO ROLL 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;
Roll — KASUTAJA omab õigust TEOSTA skeemis business_functions.
LOO ROLL user_role;
Oigused skeemide vahel
GRANT
Kuna kõik funktsioonid luuakse atribuudi SECURITY DEFINER on vajalik käsk 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 ;
Seega, andmebaasi skeem — on valmis. Võime andmete täitmisega alustada.
Sihitabelid
Tabelite loomine on triviaalne. Pole eripära, välja arvatud et on otsustatud loobuda SERIAL ja genereerida järjestusi selgelt. Lisaks, loomulikult maksimaalne kasutamine käsust
COMMENT ON ...Kommentaarid kõigi objektide kohta, ilma eranditeta.
Loodud audit
Talituste logimiseks ja sihitaalmete muutmiseks kasutatakse lokaalse audititabelit, mis hõlmab ka kliendi ühenduse üksikasju, kutsuva mooduli silti ning tegelikke väärtusi sisendi ja väljundi parameetrites JSON formaadis.
Süsteemifunktsioonid
On ette nähtud muudatuste logimiseks sihitalaetes. Esindavad käivitaja funktsioone.
Mall — süsteemifunktsioon
---------------------------------------------------------
-- SISENE
LOO KAS JA VAHETA FUNKTSIOON sys_functions.table_insert_log ()
RETURN TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'insert' , json_build_object('id', NEW.id) );
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
LOO KAS TRIGGER table_after_insert PEALE SISSELOOD MINU STORAGE.TABLE IGA RIDA KUIDAS TEHA PROTSEDUURI sys_functions.table_insert_log();
---------------------------------------------------------
-- UUENDUS
LOO KAS JA VAHETA FUNKTSIOON sys_functions.table_update_log ()
RETURN 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;
LOO KAS TRIGGER table_after_update PEALE UUENDUST MINU STORAGE.TABLE IGA RIDA KUIDAS TEHA PROTSEDUURI sys_functions.table_update_log ();
---------------------------------------------------------
-- KUSTUTAMINE
LOO KAS JA VAHETA FUNKTSIOON sys_functions.table_delete_log ()
RETURN TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'delete' , json_build_object('id', OLD.id ) );
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
LOO KAS TRIGGER table_after_delete PEALE KUSTUTAMIST MINU STORAGE.TABLE IGA RIDA KUIDAS TEHA PROTSEDUURI sys_functions.table_delete_log ();Teenuse funktsioonid
On mõeldud teenuslike ja DML operatsioonide teostamiseks sihtfailides.
Mall — teenuse funktsioon
--SISENEMINE
--RETURN UUE RIDA ID
LOO KAS JA VAHETA FUNKTSIOON service_functions.table_insert ( new_column store.table.column%TYPE )
RETURN INTEGER AS $$
DECLARE
new_id INTEGER ;
BEGIN
-- Loo uus id
new_id = nextval('store.table.seq');
-- Lisa tabelisse
INSERT INTO store.table
(
id ,
column
)
VALUES
(
new_id ,
new_column
);
RETURN new_id ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
--KUSTUTAMINE
--RETURN KUSTUTATUD REA NUMBRID
LOO KAS JA VAHETA FUNKTSIOON service_functions.table_delete ( current_id INTEGER )
RETURN 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;
-- UUENDUSE DETAILID
-- RETURN UUE KUSTUTATUD REA NUMBRID
LOO KAS JA VAHETA FUNKTSIOON service_functions.table_update_column
(
current_id INTEGER
,new_column store.table.column%TYPE
)
RETURN 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;Ärifunktsioonid
On mõeldud lõplike ärifunktsioonide jaoks, mida kutsub esile klient. Tagastab alati — JSON. Täitmisvigade tabamiseks ja logimiseks kasutatakse plokki ERAND.
Mall — ärifunktsioon
LOOJA VÕI VAHETA FUNKTSIOON business_functions.business_function_template(
--Sisendparameetrid
)
TULEMUSED JSON AS $$
DEKLAREERIGE
------------------------
--veakäsitsemiseks
veateade tekst ;
veajajson json ;
tulemus json ;
------------------------
ALUSTAGE
--LOGIMINE
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'ALUSTATUD',
json_build_object
(
--SISENDPARAMEETRID
)
);
PERFORM business_functions.notice('business_function_template');
--ALUSTAGE ÄRIVALDKONDA
--LÕPETAGE ÄRIVALDKOND
-- KATSE TULEMUS
PERFORM business_functions.notice('tulemus');
PERFORM business_functions.notice(tulemus);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'LÕPETATUD',
json_build_object( 'tulemus',tulemus )
);
RETURN tulemus ;
----------------------------------------------------------------------------------------------------------
-- VEAKÄSITSEMINA
ERAND
KUI TEISED SIIS
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'ALUSTATUD',
json_build_object
(
--SISENDPARAMEETRID
) , TRUE );
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA',
json_build_object('SQLSTATE',SQLSTATE ), TRUE
);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA',
json_build_object('SQLERRM',SQLERRM ), TRUE
);
GET STACKED DIAGNOSTICS veateade = RETURNED_SQLSTATE ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA-RETURNED_SQLSTATE',json_build_object('RETURNED_SQLSTATE',veateade ), TRUE );
GET STACKED DIAGNOSTICS veateade = COLUMN_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA-COLUMN_NAME',
json_build_object('COLUMN_NAME',veateade ), TRUE );
GET STACKED DIAGNOSTICS veateade = CONSTRAINT_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA-CONSTRAINT_NAME',
json_build_object('CONSTRAINT_NAME',veateade ), TRUE );
GET STACKED DIAGNOSTICS veateade = PG_DATATYPE_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA-PG_DATATYPE_NAME',
json_build_object('PG_DATATYPE_NAME',veateade ), TRUE );
GET STACKED DIAGNOSTICS veateade = MESSAGE_TEXT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA-MESSAGE_TEXT',json_build_object('MESSAGE_TEXT',veateade ), TRUE );
GET STACKED DIAGNOSTICS veateade = SCHEMA_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA-SCHEMA_NAME',json_build_object('SCHEMA_NAME',veateade ), TRUE );
GET STACKED DIAGNOSTICS veateade = PG_EXCEPTION_DETAIL ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA-PG_EXCEPTION_DETAIL',
json_build_object('PG_EXCEPTION_DETAIL',veateade ), TRUE );
GET STACKED DIAGNOSTICS veateade = PG_EXCEPTION_HINT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA-PG_EXCEPTION_HINT',json_build_object('PG_EXCEPTION_HINT',veateade ), TRUE );
GET STACKED DIAGNOSTICS veateade = PG_EXCEPTION_CONTEXT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' VIGA-PG_EXCEPTION_CONTEXT',json_build_object('PG_EXCEPTION_CONTEXT',veateade ), TRUE );
TÕSTA HOIATUS 'ALARM: %' , SQLERRM ;
VALIGE json_build_object
(
'isError' , TRUE ,
'veateade' , SQLERRM
) INTO veajajson ;
RETURN veajajson ;
LÕPP
$$ KEEL plpgsql TURVALISUSE DEFINIITOR;Kokkuvõte
Üldise pildi kirjeldamiseks arvan, et piisab sellest. Kui kedagi huvitavad detailid ja tulemused, kirjutage kommentaare, täiendan hea meelega pilti lisaviibudega.
P.S.
Lihtsa veateate logimine — sisendi parameetri tüüp
-[ RECORD 1 ]-
date_trunc | 2020-08-19 13:15:46
id | 1072
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ALUSTATUD
jsonb_pretty | {
| "dko": {
| "id": 4,
| "type": "Tüüp1",
| "title": "LOODUD addKD poolt",
| "Weight": 10,
| "Tr": "300",
| "reduction": 10,
| "isTrud": "TÕENE",
| "description": "kirjeldus",
| "lowerTr": "100",
| "measurement": "mõõtmed1",
| "methodology": "m1",
| "passportUrl": "failid",
| "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 | VIGA
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 | VIGA
jsonb_pretty | {
| "SQLERRM": "kehtetu sisendi süntaks numbrilise tüübi jaoks: "null""
| }
-[ RECORD 4 ]-
date_trunc | 2020-08-19 13:15:46
id | 1075
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | VIGA-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 | VIGA-KOLUMNI_NIMI
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 | VIGA-PIIRANG_NIMI
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 | VIGA-PG_ANDMEDÜÜT
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 | VIGA-SÕNUMI_TEKST
jsonb_pretty | {
| "MESSAGE_TEXT": "kehtetu sisendi süntaks numbrilise tüübi jaoks: "null""
| }
-[ RECORD 9 ]-
date_trunc | 2020-08-19 13:15:46
id | 1080
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | VIGA-SKEEMI_NIMI
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 | VIGA-PG_ERINDUSE_DETAL
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 | VIGA-PG_ERINDUSE_NIIDE
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 | VIGA-PG_ERINDUSE_KONTEKST
jsonb_pretty | {
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | VIGA-SÕNUMI_TEKST
jsonb_pretty | {
| "MESSAGE_TEXT": "kehtetu sisendi süntaks numbrilise tüübi jaoks: "null""
| }Allikas: habr.com
