ProHoster > Blog > Administrim > Artikull mbi zbatimin e logjikës së biznesit në nivelin e funksioneve të ruajtura PostgreSQL

Artikull mbi zbatimin e logjikës së biznesit në nivelin e funksioneve të ruajtura PostgreSQL

Motivi pĂ«r tĂ« shkruar kĂ«tĂ« studim ishte njĂ« artikull «GjatĂ« karantinĂ«s ngarkesa u rrit pesĂ« herĂ«, por ne ishim tĂ« gatshĂ«m». Si Lingualeo u kalua nĂ« PostgreSQL me 23 milion pĂ«rdorues. Po ashtu, duket interesante njĂ« artikull i botuar 4 vjet mĂ« parĂ« — Realizimi i logjikĂ«s biznesore nĂ« MySQL.

Më dukej interesante se një mendim i tillë -"të realizohej logjika biznesore në DB".

Artikull mbi zbatimin e logjikës së biznesit në nivelin e funksioneve të ruajtura PostgreSQL

nuk ishte vetëm ideja ime.

Po ashtu, për të ardhmen doja të ruaja, për veten time para së gjithash, zhvillimet interesante që janë shfaqur gjatë realizimit. Veçanërisht duke marrë parasysh se kohët e fundit u mor vendimi strategjik për të ndryshuar arkitekturën dhe për të transferuar logjikën biznesore në nivelin backend. Pra, gjithçka që është zhvilluar, së shpejti nuk do t'i nevojitet askujt dhe askujt nuk do t'i duket interesante.

Metodat e pĂ«rshkruara nuk janĂ« ndonjĂ« zbulim apo ekskluziv know how, gjithçka sipas klasikĂ«s dhe Ă«shtĂ« realizuar disa herĂ« (pĂ«r shembull unĂ« aplikova njĂ« qasje tĂ« tillĂ« 20 vjet mĂ« parĂ« nĂ« Oracle). Thjesht vendosa t'i mbledh tĂ« gjitha nĂ« njĂ« vend. Ndoshta do tĂ« ndihmojĂ« dikĂ«. Siç tregoi praktika — shpesh e njĂ«jta ide i vjen nĂ« mend disa njerĂ«zve nĂ« mĂ«nyrĂ« tĂ« pavarur. Edhe pĂ«r veten time Ă«shtĂ« e dobishme tĂ« mbaj nĂ« kujtesĂ«.

Sigurisht, asgjĂ« nĂ« kĂ«tĂ« botĂ« nuk Ă«shtĂ« e pĂ«rsosur, gabimet dhe shkrimet e gabuara, fatkeqĂ«sisht, janĂ« tĂ« mundshme. Kretikat dhe vĂ«rejtjet priten me kĂ«naqĂ«si dhe janĂ« tĂ« pritura. Dhe njĂ« detaj i vogĂ«l tjetĂ«r — detajet konkrete tĂ« realizimit janĂ« lĂ«nĂ« mĂ«njanĂ«. Prandaj gjithçka pĂ«rdoret pĂ«r momentin nĂ« njĂ« projekt qĂ« funksionon realisht. Pra, artikulli Ă«shtĂ« si njĂ« studim dhe pĂ«rshkrim i konceptit tĂ« pĂ«rgjithshĂ«m, asgjĂ« mĂ« shumĂ«. Shpresoj se pĂ«r tĂ« kuptuar pamjen e pĂ«rgjithshme, detajet janĂ« tĂ« mjaftueshme.

Ideja kryesore — «ndarje dhe sundim, fshehje dhe zotĂ«rim»

Ideja Ă«shtĂ« klasike — njĂ« skemĂ« e veçantĂ« pĂ«r tabelat, njĂ« skemĂ« e veçantĂ« pĂ«r funksionet e ruajtura.
Klienti nuk ka akses direkt nĂ« tĂ« dhĂ«na. Gjithçka qĂ« klienti mund tĂ« bĂ«jĂ« — Ă«shtĂ« tĂ« thĂ«rrasĂ« vetĂ«m njĂ« funksion tĂ« ruajtur dhe tĂ« pĂ«rpunojĂ« pĂ«rgjigjen e marrĂ«.

Rolet

KRIJO ROLIN store;


KRIJO ROLIN sys_functions;


KRIJO ROLIN loc_audit_functions;


KRIJO ROLIN service_functions;


KRIJO ROLIN business_functions;

Skemat

Skema e ruajtjes së tabelave

Tabelat e synuara që realizojnë entitetet tematike.

KRIJO SKEMË store AUTORIZIMI store;

Skema e funksioneve sistemore

Funksionet sistemore, veçanërisht për regjistrimin e ndryshimeve të tabelave.

KRIJO SKEMË sys_functions AUTORIZIMI sys_functions;

Skema e auditimit lokal

Funksionet dhe tabelat për realizimin e auditit lokal të ekzekutimit të funksioneve të ruajtura dhe ndryshimin e tabelave të synuara.

Krijo skemën loc_audit_functions AUTORIZIMI loc_audit_functions;

Skema e funksioneve shërbimore

Funksionet për funksionet shërbimore dhe DML.

Krijo skemën service_functions AUTORIZIMI service_functions;

Skema e funksioneve të biznesit

Funksionet për funksionet përfundimtare të biznesit të thirrura nga klienti.

Krijo skemën business_functions AUTORIZIMI business_functions;

TĂ« drejtat e aksesit

Rolet — DBA ka qasje tĂ« plotĂ« nĂ« tĂ« gjitha skemat (e ndarĂ« nga roli DB Owner).

Krijo rolin dba_role;
JEP RREH DO KONTROLLON dba_role;
JEP RREH FUNKSIONET SYS dba_role;
JEP RREH LOC_AUDIT_FUNCTIONS dba_role;
JEP RREH SERVICE_FUNCTIONS dba_role;
JEP RREH BUSINESS_FUNCTIONS dba_role;

Rolet — USER ka privilegjin EKZEKUTO nĂ« skemĂ«n business_functions.

Krijo rolin user_role;

Privilegjet midis skemave

GRANT
Duke qenĂ« se tĂ« gjitha funksionet krijohen me atributin SECURITY DEFINER nevojitet udhĂ«zimi REVOKO EKZEKUTIMIN PËR TË GJITHA FUNKSIONET
 NGA publik;

REVOKO EKZEKUTIMIN PËR TË GJITHA FUNKSIONET NË SKEMË SYS_FUNCTIONS NGA publik;
REVOKO EKZEKUTIMIN PËR TË GJITHA FUNKSIONET NË SKEMË LOC_AUDIT_FUNCTIONS NGA publik;
REVOKO EKZEKUTIMIN PËR TË GJITHA FUNKSIONET NË SKEMË SERVICE_FUNCTIONS NGA publik;
REVOKO EKZEKUTIMIN PËR TË GJITHA FUNKSIONET NË SKEMË BUSINESS_FUNCTIONS NGA publik;

JEP PËRDORIM NË SKEMË SYS_FUNCTIONS DHE dba_role;
JEP EKZEKUTIMIN PËR TË GJITHA FUNKSIONET NË SKEMË SYS_FUNCTIONS DHE dba_role;
JEP PËRDORIM NË SKEMË LOC_AUDIT_FUNCTIONS DHE dba_role;
JEP EKZEKUTIMIN PËR TË GJITHA FUNKSIONET NË SKEMË LOC_AUDIT_FUNCTIONS DHE dba_role;
JEP PËRDORIM NË SKEMË SERVICE_FUNCTIONS DHE dba_role;
JEP EKZEKUTIMIN PËR TË GJITHA FUNKSIONET NË SKEMË SERVICE_FUNCTIONS DHE dba_role;
JEP PËRDORIM NË SKEMË BUSINESS_FUNCTIONS DHE dba_role;
JEP EKZEKUTIMIN PËR TË GJITHA FUNKSIONET NË SKEMË BUSINESS_FUNCTIONS DHE dba_role;
JEP EKZEKUTIMIN PËR TË GJITHA FUNKSIONET NË SKEMË BUSINESS_FUNCTIONS DHE user_role;

JEP TË GJITHA PRIVILEGJET NË SKEMË STORE grupit business_functions;
JEP TË GJITHA PRIVILEGJET NË TË GJITHA TABELAT NË SKEMË STORE DHE business_functions;
JEP PËRDORIM PËR TË GJITHA SEQUENCE NË SKEMË STORE DHE business_functions;

Pra skema e DB është gati. Mund të fillojmë të plotësojmë me të dhëna.

Tabelat e synuara

Krijimi i tabelave është triviale. Asnjë veçori, përveç faktit se është vendosur të hiqet dorë nga përdorimi i SERIAL dhe të gjenerohen sekuencat në mënyrë të qartë. Plus, sigurisht, përdorimi maksimal i udhëzimit

KOMENT PËR ...

Komentet për të gjitha objektet, pa përjashtim.

Auditimi lokal

Për mbajtjen e regjistrit të ekzekutimit të funksioneve të ruajtura dhe ndryshimin e tabelave të synuara përdoret tabela e auditit lokal, që përfshin gjithashtu detajet e lidhjes së klientit, etiketën e modulit të thirrur, vlerat faktike të parametrave hyrës dhe dalës në formatin JSON.

Funksionet sistemore

Destinuar për regjistrimin e ndryshimeve në tabelat e synuara. Ajo përbën funksione të ndërlidhura.

Shablloni — funksioni sistemor

---------------------------------------------------------
-- 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 ();

Funksionet shërbyese

Janë të destinuara për realizimin e operacioneve shërbyese dhe DML mbi tabelat përkatëse.

Shabllon — funksioni shĂ«rbyes

--INSERT
--Kthe id e RRESHTIT TË RI
CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
RETURNS integer AS $$
DECLARE
  new_id integer ;
BEGIN
  -- Gjeneroni id të re
  new_id = nextval('store.table.seq');

  -- Shtoni në tabelë
  INSERT INTO store.table
  ( 
    id ,
    column
   )
  VALUES
  (
   new_id ,
   new_column
   );

RETURN new_id ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

--DELETE
--Kthe NUMRAT E RRESHTEVE TË FSHIRË
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;
 
-- PËRDITËSO DETAJET
-- Kthe NUMRAT E RRESHTEVE TË PËRDITËSUAR
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;

Funksionet e biznesit

JanĂ« tĂ« destinuara pĂ«r funksionet e biznesit tĂ« fundit qĂ« i thĂ«rret klienti. KthejnĂ« gjithmonĂ« — JSON. PĂ«r kapjen dhe regjistrimin e gabimeve gjatĂ« ekzekutimit, pĂ«rdoret blloku PËRFUNDIMI.

Shabllon — funksioni i biznesit

KRIJONI O RIVENDOSNI FUNKSIONIN business_functions.business_function_template(
--Parametrat e inputit        
 )
KTHEN JSON AS $$
SHIKIMI
  ------------------------
  --për kapjen e përjashtimeve
  mesazhi_i_grevs text;
  error_json json;
  rezultati json;
  ------------------------ 
KONFIRMIM
--REGJISTRIMI
  PERFORM loc_audit_functions.make_log
  (
    'business_function_template',
    'FILLUAR',
    json_build_object
    (
	--PARAMETRAT E Hyrjes
    ) 
   );

  PERFORM business_functions.notice('business_function_template');            

  --FILLIMI I PËRGJEGJES
  --Mbyllja e PËRGJEGJES

  -- REZULTATI I SUKSESËSHËM
  PERFORM business_functions.notice('rezultati');
  PERFORM business_functions.notice(rezultati);

  PERFORM loc_audit_functions.make_log
  (
    'business_function_template',
    'PËRFUNDIMI', 
    json_build_object( 'rezultati', rezultati )
  );

  KTHEN rezultati;
----------------------------------------------------------------------------------------------------------
-- KAPJA E PËRJASHTIMEVE
PËRBËRJA                        
  NDODH TË TJERËT PASTAJ    
    PERFORM loc_audit_functions.make_log
    (
      'business_function_template',
      'FILLUAR',
      json_build_object
      (
	--PARAMETRAT E Hyrjes	
      ) , TRUE );

     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' GABIM',
       json_build_object('SQLSTATE', SQLSTATE), TRUE 
     );

     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' GABIM',
       json_build_object('SQLERRM', SQLERRM), TRUE 
      );

     MERRNI DIAGNOSTIKAT E MBLEDHURA mesazhi_i_grevs = RETURNED_SQLSTATE;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      ' GABIM-RETURNED_SQLSTATE', json_build_object('RETURNED_SQLSTATE', mesazhi_i_grevs), TRUE );

     MERRNI DIAGNOSTIKAT E MBLEDHURA mesazhi_i_grevs = EMRI_I_KOLUMNËS;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' GABIM-EMRI_I_KOLUMNËS',
       json_build_object('EMRI_I_KOLUMNËS', mesazhi_i_grevs), TRUE );

     MERRNI DIAGNOSTIKAT E MBLEDHURA mesazhi_i_grevs = EMRI_I_KONSTRAINTIT;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      ' GABIM-EMRI_I_KONSTRAINTIT',
      json_build_object('EMRI_I_KONSTRAINTIT', mesazhi_i_grevs), TRUE );

     MERRNI DIAGNOSTIKAT E MBLEDHURA mesazhi_i_grevs = EMRI_I_TIPIT_PG;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' GABIM-EMRI_I_TIPIT_PG',
       json_build_object('EMRI_I_TIPIT_PG', mesazhi_i_grevs), TRUE );

     MERRNI DIAGNOSTIKAT E MBLEDHURA mesazhi_i_grevs = MESAZHI_TEKST;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' GABIM-MESAZHI_TEKST', json_build_object('MESAZHI_TEKST', mesazhi_i_grevs), TRUE );

     MERRNI DIAGNOSTIKAT E MBLEDHURA mesazhi_i_grevs = EMRI_I_SCHEMA;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' GABIM-EMRI_I_SCHEMA', json_build_object('EMRI_I_SCHEMA', mesazhi_i_grevs), TRUE );

     MERRNI DIAGNOSTIKAT E MBLEDHURA mesazhi_i_grevs = DETAJI_I_PG_NDODHJEVE;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      ' GABIM-DETAJI_I_PG_NDODHJEVE',
      json_build_object('GABIM-DETAJI_I_PG_NDODHJEVE', mesazhi_i_grevs), TRUE );

     MERRNI DIAGNOSTIKAT E MBLEDHURA mesazhi_i_grevs = KËSHILLA_E_PG_NDODHJEVE;
     PERFORM loc_audit_functions.make_log
     (
       'business_function_template',
       ' GABIM-KËSHILLA_E_PG_NDODHJEVE', json_build_object('GABIM-KËSHILLA_E_PG_NDODHJEVE', mesazhi_i_grevs), TRUE );

     MERRNI DIAGNOSTIKAT E MBLEDHURA mesazhi_i_grevs = KONTEKSTI_PG_NDODHJEVE;
     PERFORM loc_audit_functions.make_log
     (
      'business_function_template',
      ' GABIM-KONTEKSTI_PG_NDODHJEVE', json_build_object('GABIM-KONTEKSTI_PG_NDODHJEVE', mesazhi_i_grevs), TRUE );                                      

    NGRISNI PARANDALIM 'ALARM: %', SQLERRM;

    SELECT json_build_object
    (
      'isError', TRUE,
      'errorMsg', SQLERRM
     ) INTO error_json;

  KTHEN  error_json;
FUND
$$ GJUHË plpgsql TË DREJTUESIT TË SIGURISË;

PĂ«rfundimi

Për të përshkruar pamjen e përgjithshme, mendoj se është mjaft e mjaftueshme. Nëse dikujt i interesojnë detajet dhe rezultatet, shkruani komente, me kënaqësi do të plotësoj pamjen me nuanca shtesë.

P.S.

Regjistrimi i një gabimi të thjeshtë - lloji i parametrave të hyrjes

-[ REGJISTRI 1 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1072
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          | NIS ËSHT GJATË
jsonb_pretty    | {
                |     "dko": {
                |         "id": 4,
                |         "type": "Type1",
                |         "title": "KRIJUAR NGA addKD",
                |         "Weight": 10,
                |         "Tr": "300",
                |         "reduction": 10,
                |         "isTrud": "E VERTETË",
                |         "description": "përshkrim",
                |         "lowerTr": "100",
                |         "measurement": "masa1",
                |         "methodology": "m1",
                |         "passportUrl": "files",
                |         "upperTr": "200",
                |         "weightingFactor": 100.123,
                |         "actualTrValue": null,
                |         "upperTrCalcNumber": "120"
                |     },
                |     "CardId": 3
                | }
-[ REGJISTRI 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"
                | }
-[ REGJISTRI 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": "sintaksë e pavlefshme për llojin numerik: "null""
                | }
-[ REGJISTRI 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"
                | }
-[ REGJISTRI 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": ""
                | }

-[ REGJISTRI 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": ""
                | }
-[ REGJISTRI 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": ""
                | }
-[ REGJISTRI 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": "sintaksë e pavlefshme për llojin numerik: "null""
                | }
-[ REGJISTRI 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": ""
                | }
-[ REGJISTRI 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": ""
                | }
-[ REGJISTRI 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": ""
                | }
-[ REGJISTRI 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": "sintaksë e pavlefshme për llojin numerik: "null""
                | }

Burimi: habr.com

Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS đŸ”„ Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS | ProHoster