Etüdi kirjutamise ajendiks oli artikkel . Samuti näis huvitav artikkel, mis ilmus 4 aastat tagasi — .
Huvitav oli, et üks ja sama mõte —rakendada äriloogikat andmebaasis".
tuli pähe mitte ainult mulle.
Tulevikuks tahtsin säilitada endale huvitavad arengud, mis tekkisid rakendamise käigus. Eriti arvestades, et suhteliselt hiljuti tehti strateegiline otsus arhitektuuri muutmiseks ja äriloogika viimiseks backend-tasandile. Nii et kõik, mis on välja arendatud, ei lähe varsti kellelegi vajalikuks ja ei huvita kedagi.
Kirjeldatud meetodid ei ole mingi avastus ega erakordne know how, kõik klassika järgi ja on korduvalt rakendatud (nt mina rakendasin sarnast lähenemist 20 aastat tagasi Oracle'is). Lihtsalt otsustasin kõik ühte kohta koguda. Äkki on kellelegi kasu. Praktika on näidanud, et sama idee tuleb sageli erinevatelt inimestelt sõltumatult. Ja endale meenutamiseks on see kasulik.
Loomulikult ei ole selles maailmas midagi täiuslikku, paraku võivad vead ja trükivead esineda. Kritikad ja märkused on alati teretulnud ja oodatud. Ja veel üks väike detail - konkreetseid rakenduse üksikasju on jäetud vahele. Lõppude lõpuks kasutatakse kõike praegu aktiivses projektis. Nii et artikkel on pigem sketš ja üldise kontseptsiooni kirjeldus, mitte rohkem. Loodan, et üldise pildi mõistmiseks on üksikasjad piisavad.
Üksikasjaline idee - "jaga ja valitse, peida ja valitse"
Idee on klassikaline - eraldi skeem tabelite jaoks, eraldi skeem salvestatud funktsioonide jaoks.
Kliendil ei ole otsepääsu andmetele. Kõik, mida klient saab teha, on vaid kutsuda salvestatud funktsiooni ja töödelda saadud vastust.
Rollid
LOO ROLL store;
LOO ROLL sys_functions;
LOO ROLL loc_audit_functions;
LOO ROLL service_functions;
LOO ROLL business_functions;
Skeemid
Tabelite salvestamise skeem
Sihtmägid, mis rakendavad teemaüksusi.
LOO SCHEMA store AUTORISEERIMINE store;
Süsteemifunktsioonide skeem
Süsteemifunktsioonid, sealhulgas tabelite muudatuste logimise jaoks.
LOO SCHEMA sys_functions AUTORISEERIMINE sys_functions;
Kohalik auditi skeem
Funktsioonid ja tabelid kohaliku auditi läbiviimiseks salvestatud funktsioonide ja sihtmärkide tabelite muudatuste osas.
LOO SCHEMA loc_audit_functions AUTORISEERIMINE loc_audit_functions;
Teenusefunktsioonide skeem
Funktsioonid teenuse- ja DML-funktsioonide jaoks.
LOO SCHEMA service_functions AUTORISEERIMINE service_functions;
Ärifunktsioonide skeem
Funktsioonid lõppäri funktsioonide jaoks, mida kutsub esile klient.
LOO SCHEMA business_functions AUTORISEERIMINE business_functions;
Ligipääs
Roll — DBA omab täielikku juurdepääsu kõigile skeemidele (erineb DB omanikust).
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 — USER omab privileegi EXECUTE skeemis business_functions.
LOO ROLL user_role;
Privileegid skeemide vahel
GRANT
Kuna kõik funktsioonid luuakse atribuudiga SECURITY DEFINER vajalik on käsk TÕKISTA TÄITMINE KÕIKIDE FUNKTSIOONIDE… KUNAGINE avalik;
Tühista täitmisõigus kõigilt funktsioonidelt skeemis sys_functions avalikult;
Tühista täitmisõigus kõigilt funktsioonidelt skeemis loc_audit_functions avalikult;
Tühista täitmisõigus kõigilt funktsioonidelt skeemis service_functions avalikult;
Tühista täitmisõigus kõigilt funktsioonidelt skeemis business_functions avalikult;
Anna kasutusõigus skeemile sys_functions rollile dba_role;
Anna täitmisõigus kõigile funktsioonidele skeemis sys_functions rollile dba_role;
Anna kasutusõigus skeemile loc_audit_functions rollile dba_role;
Anna täitmisõigus kõigile funktsioonidele skeemis loc_audit_functions rollile dba_role;
Anna kasutusõigus skeemile service_functions rollile dba_role;
Anna täitmisõigus kõigile funktsioonidele skeemis service_functions rollile dba_role;
Anna kasutusõigus skeemile business_functions rollile dba_role;
Anna täitmisõigus kõigile funktsioonidele skeemis business_functions rollile dba_role;
Anna täitmisõigus kõigile funktsioonidele skeemis business_functions rollile user_role;
Anna kõik privillegiid skeemile store rühmale business_functions;
Anna kõik privillegiid kõigile tabelitele skeemis store rühmale business_functions;
Anna kasutusõigus kõikidele järjestustele skeemis store rühmale business_functions;
Nii et andmebaasi skeem on valmis. Saame alustada andmete täitmist.
Eesmärgipärased tabelid
Tabelite loomine on triviaalne. Pole mingeid erilisi nüansse, välja arvatud see, et on otsustatud loobuda kasutamisest SERIAL ja genereerida järjestused selgelt. Lisaks, loomulikult maksimaalne kasutamine käsust
COMMENT ON ...Kommentaarid keskkondades ja projekti klastrites. Selline põhimõte on õige objektide jaoks, ilma eranditeta.
Kohalik auditi
Salvestatud funktsioonide täitmise ja sihtlaudade muutmise jälgimiseks kasutatakse kohalikku auditi tabelit, mis hõlmab klientide ühenduse andmeid, kutsutud mooduli silti ning tegelikke sisendi ja väljundi parameetrite väärtusi JSON-formaadis.
Süsteemifunktsioonid
Mõeldud sihtlaudade muutuste logimiseks. Need on käivitusteadmise funktsioonid.
Mall — süsteemifunktsioon
---------------------------------------------------------
-- 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 ();Teenusfunktsioonid
Need on mõeldud sihttablettide teenus- ja DML-tegevuste teostamiseks.
Mall - teenusfunktsioon
--INSERT
--RETURN id OF NEW ROW
CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
RETURNS integer AS $$
DECLARE
new_id integer ;
BEGIN
-- Generate new id
new_id = nextval('store.table.seq');
-- Insert into table
INSERT INTO store.table
(
id ,
column
)
VALUES
(
new_id ,
new_column
);
RETURN new_id ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
--DELETE
--RETURN ROW NUMBERS DELETED
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;
-- UPDATE DETAILS
-- RETURN ROW NUMBERS UPDATED
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;Äriprotsessid
On suunatud lõppkasutajale. Tagastavad alati — JSON. Vigade käsitlemiseks ja logimiseks kasutatakse plokki ERAND.
Mall — ärifunktsioon
LOO JA ASENDA FUNKTSIOON business_functions.business_function_template(
--Sisendparameetrid
)
RETURN JSON AS $$
DEKLAREERIMINE
------------------------
--erandite püüdmiseks
error_message text ;
error_json json ;
result json ;
------------------------
ALGUS
--LOGIMINE
TEHA loc_audit_functions.make_log
(
'business_function_template',
'ALUSTATUD',
json_build_object
(
--Sisendparameetrid
)
);
TEHA business_functions.notice('business_function_template');
--ALUSTA ÄRILIST OSA
--LÕPETA ÄRILINE OSA
-- EDUKAS TULEMUS
TEHA business_functions.notice('tulemus');
TEHA business_functions.notice(result);
TEHA loc_audit_functions.make_log
(
'business_function_template',
'LÕPETATUD',
json_build_object( 'tulemus',result )
);
RETURN result ;
----------------------------------------------------------------------------------------------------------
-- ERITEATE PÜÜDMINE
ERITEATED
KUIDAS MUUD
TEHA loc_audit_functions.make_log
(
'business_function_template',
'ALUSTATUD',
json_build_object
(
--Sisendparameetrid
) , TRUE );
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA',
json_build_object('SQLSTATE',SQLSTATE ), TRUE
);
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA',
json_build_object('SQLERRM',SQLERRM ), TRUE
);
HANKIGE TÕSTETUD DIAGNOSTIKA error_message = RETURNED_SQLSTATE ;
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA-RETURNED_SQLSTATE',json_build_object('RETURNED_SQLSTATE',error_message ), TRUE );
HANKIGE TÕSTETUD DIAGNOSTIKA error_message = COLUMN_NAME ;
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA-KOONDELM',
json_build_object('COLUMN_NAME',error_message ), TRUE );
HANKIGE TÕSTETUD DIAGNOSTIKA error_message = CONSTRAINT_NAME ;
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA-KOHUSTUSNIMI',
json_build_object('CONSTRAINT_NAME',error_message ), TRUE );
HANKIGE TÕSTETUD DIAGNOSTIKA error_message = PG_DATATYPE_NAME ;
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA-PG_DATATYPE_NAME',
json_build_object('PG_DATATYPE_NAME',error_message ), TRUE );
HANKIGE TÕSTETUD DIAGNOSTIKA error_message = MESSAGE_TEXT ;
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA-SÕNUMI_TEKST',json_build_object('MESSAGE_TEXT',error_message ), TRUE );
HANKIGE TÕSTETUD DIAGNOSTIKA error_message = SCHEMA_NAME ;
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA-SKEEMI_NIMI',json_build_object('SCHEMA_NAME',error_message ), TRUE );
HANKIGE TÕSTETUD DIAGNOSTIKA error_message = PG_EXCEPTION_DETAIL ;
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA-PG_EXCEPTION_DETAIL',
json_build_object('PG_EXCEPTION_DETAIL',error_message ), TRUE );
HANKIGE TÕSTETUD DIAGNOSTIKA error_message = PG_EXCEPTION_HINT ;
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA-PG_EXCEPTION_HINT',json_build_object('PG_EXCEPTION_HINT',error_message ), TRUE );
HANKIGE TÕSTETUD DIAGNOSTIKA error_message = PG_EXCEPTION_CONTEXT ;
TEHA loc_audit_functions.make_log
(
'business_function_template',
' VIGA-PG_EXCEPTION_CONTEXT',json_build_object('PG_EXCEPTION_CONTEXT',error_message ), TRUE );
TÕSTA HOIATUS 'ALARM: %' , SQLERRM ;
VALI json_build_object
(
'isError' , TRUE ,
'errorMsg' , SQLERRM
) INTO error_json ;
RETURN error_json ;
LÕPP
$$ LANGUAGEL plpgsql TURVAMI NIMETUS;Kokkuvõte
Üldise pildi kirjeldamiseks arvan, et piisab sellest. Kui keegi on huvitatud detailidest ja tulemustest, kirjutage kommentaaridesse, täiendaksin hea meelega maali lisadega.
P.S.
Lihtsa vea 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 | STARTED
jsonb_pretty | {
| "dko": {
| "id": 4,
| "type": "Type1",
| "title": "Loodud addKD poolt",
| "Weight": 10,
| "Tr": "300",
| "reduction": 10,
| "isTrud": "TRUE",
| "description": "kirjelduse",
| "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 | ERROR
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 | ERROR
jsonb_pretty | {
| "SQLERRM": "väärne sisestus 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 | ERROR-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 | ERROR-COLUMN_NAME
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 | ERROR-CONSTRAINT_NAME
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 | ERROR-PG_DATATYPE_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 | ERROR-MESSAGE_TEXT
jsonb_pretty | {
| "MESSAGE_TEXT": "väärne sisestus 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 | ERROR-SCHEMA_NAME
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 | ERROR-PG_EXCEPTION_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 | ERROR-PG_EXCEPTION_HINT
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 | ERROR-PG_EXCEPTION_CONTEXT
jsonb_pretty | {
usename | emp1
log_module | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status | ERROR-MESSAGE_TEXT
jsonb_pretty | {
| "MESSAGE_TEXT": "väärne sisestus süntaks numbrilise tüübi jaoks: "null""
| }Allikas: habr.com
