ProHoster >> Blog >> Administrimi >> Studimi mbi realizimin e logjikës së biznesit në nivelin e funksioneve të pagabueshme PostgreSQL

Studimi mbi realizimin e logjikës së biznesit në nivelin e funksioneve të pagabueshme PostgreSQL

Shtysa pĂ«r tĂ« shkruar kĂ«tĂ« etyd ishte artikulli «GjatĂ« karantinĂ«s ngarkesa u rrit 5 herĂ«, por ne ishim tĂ« pĂ«rgatitur». Si Lingualeo kaloi nĂ« PostgreSQL me 23 milion pĂ«rdorues. MĂ« duket interesante edhe njĂ« artikull i botuar 4 vjet mĂ« parĂ« — Implementimi i logjikĂ«s biznesore nĂ« MySQL.

MĂ« ka dukur interesante qĂ« e njĂ«jta mendim—tĂ« realizoj logjikĂ«n biznesore nĂ« DB".

Studimi mbi realizimin e logjikës së biznesit në nivelin e funksioneve të pagabueshme PostgreSQL

më ka ardhur në mendje jo vetëm mua.

Gjithashtu doja të ruaja për të ardhmen, për vete së pari, ide interesante që janë shfaqur gjatë implementimit. Sidomos duke marrë parasysh se relativisht kohët e fundit është marrë një vendim strategjik për të ndryshuar arkitekturën dhe për të kaluar logjikën biznesore në nivelin backend. Pra, gjithçka që është arritur, së shpejti nuk do t'i nevojitet askujt dhe nuk do të jetë interesante për askënd.

Metodat e pĂ«rshkruara nuk janĂ« ndonjĂ« zbulim dhe as pĂ«rjashtim know how, gjithçka sipas klasikes dhe Ă«shtĂ« realizuar shumĂ« herĂ« (pĂ«r shembull, unĂ« e kam aplikuar njĂ« qasje tĂ« tillĂ« 20 vjet mĂ« parĂ« nĂ« Oracle). Thjesht vendosa t'i bashkoj tĂ« gjitha nĂ« njĂ« vend. Mundet ndonjĂ«herĂ« tĂ« ndihmojĂ« dikĂ«. Siç tregon praktika — shpesh tĂ« njĂ«jtĂ«n ide e kanĂ« menduar nĂ« mĂ«nyrĂ« tĂ« pavarur njerĂ«z tĂ« ndryshĂ«m. Dhe gjithashtu, pĂ«r ta mbajtur si kujtim pĂ«r veten, Ă«shtĂ« e dobishme.

Natyrisht, asgjĂ« nĂ« kĂ«tĂ« botĂ« nuk Ă«shtĂ« perfekte, gabimet dhe shkrimet e gabuara, fatkeqĂ«sisht, janĂ« tĂ« mundshme. Kritika dhe komente janĂ« gjithmonĂ« tĂ« mirĂ«pritura dhe tĂ« pritura. Dhe njĂ« detaj i vogĂ«l tjetĂ«r — detajet specifike tĂ« realizimit janĂ« lĂ«nĂ« jashtĂ«. MegjithatĂ«, gjithçka pĂ«rdoret ende nĂ« njĂ« projekt qĂ« funksionon realisht. Pra, ky artikull Ă«shtĂ« si njĂ« studim dhe pĂ«rshkrim i konceptit tĂ« pĂ«rgjithshĂ«m, ashtu siç Ă«shtĂ«. Shpresoj se pĂ«r tĂ« kuptuar pamjen e pĂ«rgjithshme, detajet janĂ« tĂ« mjaftueshme.

Ideja e pĂ«rgjithshme — "ndaje dhe sundo, fsheh dhe zotĂ«ro"

Ideja klasike — njĂ« skemĂ« e veçantĂ« pĂ«r tabelat, njĂ« skemĂ« e veçantĂ« pĂ«r funksionet e ruajtura.
Klienti nuk ka qasje tĂ« drejtpĂ«rdrejtĂ« nĂ« tĂ« dhĂ«na. ÇfarĂ«do qĂ« klienti mund tĂ« realizojĂ« — Ă«shtĂ« vetĂ«m tĂ« thĂ«rrasĂ« funksionin e ruajtur dhe tĂ« pĂ«rpunojĂ« pĂ«rgjigjen qĂ« merr.

Rollet

CREATE ROLE store;


CREATE ROLE sys_functions;


CREATE ROLE loc_audit_functions;


CREATE ROLE service_functions;


CREATE ROLE business_functions;

Skemat

Skema e ruajtjes së tabelave

Tabela që realizojnë entitetet tematikë.

KRIJO SKEMË store AUTORIZIMI store;

Schema e funksioneve sistemike

Funksionet sistemike, në veçanti për regjistrimin e ndryshimeve në tabela.

KRIJO SKEMË sys_functions AUTORIZIMI sys_functions;

Schema e auditit lokal

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

KRIJO SKEMË loc_audit_functions AUTORIZIMI loc_audit_functions;

Schema e funksioneve shërbyese

Funksionet për funksionet shërbyese dhe DML.

KRIJO SKEMË service_functions AUTORIZIMI service_functions;

Schema e funksioneve të biznesit

Funksionet për funksionet përfundimtare të biznesit që thirren nga klienti.

KRIJO SKEMË business_functions AUTORIZIMI business_functions;

TĂ« drejtat e aksesit

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

KRIJO ROL 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;

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

KRIJO ROL user_role;

Privilegjet midis skemave

GRANT
Pasi tĂ« gjitha funksionet krijohen me atributin SECURITY DEFINER nevojitet instruksion HEQ EKZEKUTIMIN NGA TË GJITHA FUNKSIONET
 NGA publiku;

ANULLO HUMBISHT ON ALL FUNKSION NË SKEMËN sys_functions NGA publik ; 
ANULLO HUMBISHT ON ALL FUNKSION NË SKEMËN loc_audit_functions NGA publik ; 
ANULLO HUMBISHT ON ALL FUNKSION NË SKEMËN service_functions NGA publik ; 
ANULLO HUMBISHT ON ALL FUNKSION NË SKEMËN business_functions NGA publik ; 

JEP UPOTIM PËR SKEMËN sys_functions PËR rol_dba ; 
JEP HUMBISHT ON ALL FUNKSION NË SKEMËN sys_functions PËR rol_dba ;
JEP UPOTIM PËR SKEMËN loc_audit_functions PËR rol_dba ; 
JEP HUMBISHT ON ALL FUNKSION NË SKEMËN loc_audit_functions PËR rol_dba ;
JEP UPOTIM PËR SKEMËN service_functions PËR rol_dba ; 
JEP HUMBISHT ON ALL FUNKSION NË SKEMËN service_functions PËR rol_dba ;
JEP UPOTIM PËR SKEMËN business_functions PËR rol_dba ; 
JEP HUMBISHT ON ALL FUNKSION NË SKEMËN business_functions PËR rol_dba ;
JEP HUMBISHT ON ALL FUNKSION NË SKEMËN business_functions PËR rol_perdorues ;

JEP TË GJITHA TË DREJTAT PËR SKEMËN magazinĂ« PËR GRUPIN business_functions ;
JEP TË GJITHA TË DREJTAT PËR TË GJITHA TABELAT NË SKEMËN magazinĂ« PËR business_functions ;
JEP UPOTIM PËR TË GJITHA SEKUENCAT NË SKEMËN magazinĂ« PËR business_functions ;

Pra, skema e DB është gati. Tani mund të fillojmë me mbushjen e të dhënave.

Tabelat e synimit

Krijimi i tabelave është trivial. Nuk ka asnjë veçori, përveç se është vendosur të hiqet dorë nga përdorimi SERIAL dhe të gjenerohen sekuenca në mënyrë të qartë. Po ashtu, maksimalisht do të shfrytëzohet instruksioni

KOMENT PËR ...

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

Auditi lokal

Për të mbajtur një regjistër të aktiviteteve të funksioneve të ruajtura dhe modifikimeve në tabelat e synuara, përdoret një tabelë auditi lokale, e cila përfshin detaje të lidhjes së klientit, etiketën e modulit të thirrur, vlerat aktuale të parametrave hyrës dhe dalës në formatin JSON.

Funksionet sistemike

Janë të destinuara për regjistrimin e ndryshimeve në tabelat e synuara. Përbëjnë funksione trigger.

Shablloni — funksion sistemik

---------------------------------------------------------
-- 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 e synuara.

Shablloni — funksioni shĂ«rbyes

--INSERT
--KTHIM id TË RRESHTIT TË RI
KRIJO OSE ZËVENDËSO FUNKTIONIN service_functions.table_insert ( new_column store.table.column%TIP )
KTHEH NUMËR SI integer AS $$
DECLAROJ
  new_id integer ;
BEGIN
  -- Generoni id të ri
  new_id = nextval('store.table.seq');

  -- Shto në tabelë
  SHTO NË store.table
  ( 
    id ,
    column
   )
  VLERAT
  (
   new_id ,
   new_column
   );

KTHEH new_id ;
END
$$ GJUHA plpgsql PËR SIGURINË DEFINER;

--Fshij
--KTHIM NUMRAT E RRESHTEVE TË FSHIRA
KRIJO OSE ZËVENDËSO FUNKTIONIN service_functions.table_delete ( current_id integer ) 
KTHEH NUMËR SI integer AS $$
DECLAROJ
  rows_count integer  ;    
BEGIN
  FSHIJ NGA store.table KU id = current_id; 

  MERR DIAGNOSTIKAT rows_count = ROW_COUNT;                                                                           

  KTHEH rows_count ;
END
$$ GJUHA plpgsql PËR SIGURINË DEFINER;

-- PËRDITËSO DETAJET
-- KTHIM NUMRAT E RRESHTEVE TË PËRDITËSUARA
KRIJO OSE ZËVENDËSO FUNKTIONIN service_functions.table_update_column 
(
  current_id integer 
  ,new_column store.table.column%TIP
) 
KTHEH NUMËR SI integer AS $$
DECLAROJ
  rows_count integer  ; 
BEGIN
  PËRDITËSO  store.table
  SET
    column = new_column
  KU id = current_id;

  MERR DIAGNOSTIKAT rows_count = ROW_COUNT;                                                                           

  KTHEH rows_count ;
END
$$ GJUHA plpgsql PËR SIGURINË DEFINER;

Funksionet e Biznesit

Destinuara pĂ«r funksionet finale tĂ« biznesit tĂ« thirrura nga klienti. Kthehen gjithmonĂ« — JSON. PĂ«r kapjen dhe regjistrimin e gabimeve tĂ« ekzekutimit, pĂ«rdoret blloku PËRJASHTIM.

Malli — funksioni i biznesit

CREATE OR REPLACE FUNCTION business_functions.business_function_template(
--Parametrat e inputit        
 )
RETURNS JSON AS $$
DECLARE
  ------------------------
  --për kapjen e përjashtimeve
  error_message text ;
  error_json json ;
  result json ;
  ------------------------ 
BEGIN
--REGJISTRIMI
  PERFORM loc_audit_functions.make_log
  (
    'business_function_template',
    'NISUR',
    json_build_object
    (
	--Parametrat e Hyrjes
    ) 
   );

  PERFORM business_functions.notice('business_function_template');            

  --NISNI PJEKAT E BUSINESSIT
  --MËRDITI PJEKAT E BUSINESSIT

  -- REZULTATI I SUKSESHEM
  PERFORM business_functions.notice('rezultati');
  PERFORM business_functions.notice(result);

  PERFORM loc_audit_functions.make_log
  (
    'business_function_template',
    'KUPTOVA', 
    json_build_object( 'rezultati',result )
  );

  RETURN result ;
----------------------------------------------------------------------------------------------------------
-- KAPJA E PËRJASHTIMEVE
EXCEPTION                        
  WHEN OTHERS THEN    
    PERFORM loc_audit_functions.make_log
    (
      'business_function_template',
      'NISUR',
      json_build_object
      (
	--Parametrat e Hyrjes	
      ) , 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
     (s
       '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;

PĂ«rfundimi

Për të përshkruar një pamje të përgjithshme, mendoj se është mjaft e mjaftueshme. Nëse dikush është i interesuar për detaje dhe rezultate, shkruani komente, do të jem i lumtur të shtoj detaje të tjera.

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          | KËRKESË E NISUR
jsonb_pretty    | {
                |     "dko": {
                |         "id": 4,
                |         "type": "Lloji1",
                |         "title": "KRIJUAR NGA addKD",
                |         "Weight": 10,
                |         "Tr": "300",
                |         "reduction": 10,
                |         "isTrud": "E VERTETË",
                |         "description": "përshkrim",
                |         "lowerTr": "100",
                |         "measurement": "matja1",
                |         "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          |  KISHA E RREGULLT
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          |  KISHA E RREGULLT
jsonb_pretty    | {
                |     "SQLERRM": "sintaksë hyrëse e pavlefshme për tipin numerik: "null""
                | }
-[ REGJISTRI 4 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1075
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  KISHA E RREGULLT-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          |  KISHA E RREGULLT-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          |  KISHA E RREGULLT-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          |  KISHA E RREGULLT-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          |  KISHA E RREGULLT-MESSAGE_TEXT
jsonb_pretty    | {
                |     "MESSAGE_TEXT": "sintaksë hyrëse e pavlefshme për tipin numerik: "null""
                | }
-[ REGJISTRI 9 ]-
date_trunc      | 2020-08-19 13:15:46
id              | 1080
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  KISHA E RREGULLT-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          |  KISHA E RREGULLT-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          |  KISHA E RREGULLT-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          |  KISHA E RREGULLT-PG_EXCEPTION_CONTEXT
jsonb_pretty    | {
usename         | emp1
log_module      | addKD
log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a
status          |  KISHA E RREGULLT-MESSAGE_TEXT
jsonb_pretty    | {
                |     "MESSAGE_TEXT": "sintaksë hyrëse e pavlefshme për tipin numerik: "null""
                | }

Burimi: habr.com

Bli njĂ« hosting tĂ« besueshĂ«m pĂ«r faqet me mbrojtje DDoS, VPS VDS serverĂ« đŸ”„ Bli njĂ« hosting tĂ« besueshĂ«m pĂ«r faqet me mbrojtje DDoS, VPS VDS serverĂ« | ProHoster