ProHoster > بلاګ > اداره > د PostgreSQL ذخیره شوي دندو په کچه د سوداګرۍ منطق پلي کولو په اړه یوه مطالعه

د PostgreSQL ذخیره شوي دندو په کچه د سوداګرۍ منطق پلي کولو په اړه یوه مطالعه

د دې سکیچ لیکلو هڅونه مقاله وه "د قرنطین په جریان کې ، د کار بار 5 ځله ډیر شو ، مګر موږ چمتو یو." څنګه Lingualeo د 23 ملیون کاروونکو سره PostgreSQL ته لاړ. ما هغه مقاله هم وموندله چې 4 کاله دمخه خپره شوې وه - په MySQL کې د سوداګرۍ منطق پلي کول.

په زړه پورې ښکاریده چې هماغه فکر - "په ډیټابیس کې د سوداګرۍ منطق پلي کول".

د PostgreSQL ذخیره شوي دندو په کچه د سوداګرۍ منطق پلي کولو په اړه یوه مطالعه

دا یوازې زه نه وم چې ذهن ته راغلم.

همچنان ، د راتلونکي لپاره ، ما غوښتل چې لومړی د ځان لپاره په زړه پورې پرمختګونه وساتم چې د پلي کیدو پرمهال رامینځته شوي. په ځانګړې توګه د دې په پام کې نیولو سره چې په دې وروستیو کې یو ستراتیژیک پریکړه وشوه چې جوړښت بدل کړي او د سوداګرۍ منطق د پس منظر کچې ته انتقال کړي. نو هر څه چې جوړ شوي وي ژر به د هیچا په ګټه نه وي او د هیچا په ګټه به نه وي.

تشریح شوي میتودونه یو ډول کشف یا استثنایی ندي. پوهېږئ چې څنګه، هرڅه کلاسیک دي او څو ځله پلي شوي (د مثال په توګه ، ما 20 کاله دمخه په اوریکل کې ورته طریقه کارولې وه). ما یوازې پریکړه وکړه چې هرڅه په یو ځای کې راټول کړم. په هغه صورت کې چې دا د یو چا لپاره ګټور وي. لکه څنګه چې عمل ښودل شوی، ډیری وختونه ورته نظر په خپلواک ډول مختلف خلکو ته راځي. او دا ګټوره ده چې دا د ځان لپاره د سوغات په توګه وساتئ.

البته، په دې نړۍ کې هیڅ شی بشپړ نه دی، غلطۍ او ټایپونه له بده مرغه ممکن دي. انتقاد او تبصرې په کلکه هرکلی او تمه کیږي او یو بل کوچنی توضیحات - د پلي کولو ځانګړي توضیحات له پامه غورځول شوي. بیا هم، هرڅه لاهم په ریښتینې کاري پروژه کې کارول کیږي. نو، مقاله یوازې یو خاکه او د عمومي مفکورې تشریح دی، نور څه نه. زه امید لرم چې د ټولیز انځور د پوهیدو لپاره کافي توضیحات شتون لري.

عمومي مفکوره "تقسیم او فتح، پټول او خپل ځان" دی.

نظر کلاسیک دی - د میزونو لپاره جلا سکیما، د ذخیره شویو کارونو لپاره جلا سکیما.
پیرودونکي مستقیم معلوماتو ته لاسرسی نلري. ټول پیرودونکي کولی شي ذخیره شوي فنکشن ته زنګ ووهي او ترلاسه شوي ځواب پروسس کړي.

رولونه

CREATE ROLE store;


CREATE ROLE sys_functions;


CREATE ROLE loc_audit_functions;


CREATE ROLE service_functions;


CREATE ROLE business_functions;

سکیمونه

د میز ذخیره کولو سکیم

د هدف میزونه چې د موضوع ادارې پلي کوي.

CREATE SCHEMA store AUTHORIZATION store ;

د سیسټم فعالیت ډیاګرام

د سیسټم دندې، په ځانګړې توګه د میز د بدلونونو د ننوتلو لپاره.

CREATE SCHEMA sys_functions AUTHORIZATION sys_functions ;

د محلي پلټنې سکیم

د ذخیره شوي دندو اجرا کولو محلي پلټنې پلي کولو لپاره دندې او میزونه او په نښه شوي جدولونو کې بدلونونه.

CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;

د خدمت فعالیت ډیاګرام

د خدماتو او DML دندو لپاره دندې.

CREATE SCHEMA service_functions AUTHORIZATION service_functions;

د سوداګرۍ فعالیت ډیاګرام

د وروستي سوداګریزو دندو لپاره دندې چې د پیرودونکي لخوا ویل کیږي.

CREATE SCHEMA business_functions AUTHORIZATION business_functions;

حقونو ته لاسرسی

رول - DBA ټولو سکیمونو ته بشپړ لاسرسی لري (د DB مالک رول څخه جلا شوی).

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;

رول - USER امتیازات لري عملي کول په انځور کې سوداګرۍ_فعالیتونه.

CREATE ROLE user_role;

د سکیمونو ترمنځ امتیازات

بخښنه
ځکه چې ټولې دندې د صفت سره رامینځته کیږي د امنیت پیژندونکی لارښوونې ته اړتیا ده د ټولو کارونو اجرا کول لغوه کړئ... له خلکو څخه؛ 

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 ;

نو د ډیټابیس سکیما چمتو ده. تاسو کولی شئ د معلوماتو ډکول پیل کړئ.

د هدف میزونه

د میزونو جوړول یو کوچنی دی. هیڅ ځانګړي ځانګړتیاوې، پرته له دې چې د کارولو پریکړه یې ونه کړه سريال او په ښکاره ډول ترتیبونه تولیدوي. سربیره پردې، البته، د لارښوونو اعظمي کارول 

COMMENT ON ...

لپاره تبصرې всех توکي، پرته له استثنا.

محلي پلټنه

د ذخیره شوي دندو اجرا کولو او په نښه شوي جدولونو کې بدلونونو ته د ننوتلو لپاره، د محلي پلټنې میز کارول کیږي، کوم چې د نورو شیانو په منځ کې، د پیرودونکي اتصال توضیحات، د ویل شوي ماډل لیبل، او د ننوتلو حقیقي ارزښتونه شامل دي. د محصول پیرامیټونه د JSON په بڼه.

د سیسټم دندې

په هدف جدولونو کې د بدلونونو د ننوتلو لپاره ډیزاین شوی. دوی د محرک دندې دي.

کينډۍ - د سیسټم فعالیت

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

د خدماتو دندې

په نښه شوي میزونو کې د خدماتو او DML عملیاتو پلي کولو لپاره ډیزاین شوی.

کينډۍ - د خدمت فعالیت

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

د سوداګرۍ دندې

د وروستي سوداګریزو دندو لپاره ډیزاین شوی چې د پیرودونکي لخوا ویل کیږي. دوی تل بیرته راځي - JSON. د اجرا کولو غلطیو د مداخلې او ننوتلو لپاره، بلاک وکاروئ استثمار.

کينډۍ - د سوداګرۍ فعالیت

CREATE OR REPLACE FUNCTION business_functions.business_function_template(
--Input parameters        
 )
RETURNS JSON AS $$
DECLARE
  ------------------------
  --for exception catching
  error_message text ;
  error_json json ;
  result json ;
  ------------------------ 
BEGIN
--LOGGING
  PERFORM loc_audit_functions.make_log
  (
    'business_function_template',
    'STARTED',
    json_build_object
    (
	--IN Parameters
    ) 
   );

  PERFORM business_functions.notice('business_function_template');            

  --START BUSINESS PART
  --END BUSINESS PART

  -- SUCCESFULLY RESULT
  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 ;
----------------------------------------------------------------------------------------------------------
-- EXCEPTION CATCHING
EXCEPTION                        
  WHEN OTHERS THEN    
    PERFORM loc_audit_functions.make_log
    (
      'business_function_template',
      'STARTED',
      json_build_object
      (
	--IN Parameters	
      ) , 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;

نتیجه

د عمومي انځور تشریح کولو لپاره، زه فکر کوم چې دا کافی دی. که څوک د توضیحاتو او پایلو سره علاقه لري ، نظرونه ولیکئ ، زه به خوښ شم چې عکس ته اضافي لمس اضافه کړم.

PS

د ساده تېروتنې ننوتل - د پیرامیټ ډول داخلول

-[ 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": "CREATED BY addKD",
                |         "Weight": 10,
                |         "Tr": "300",
                |         "reduction": 10,
                |         "isTrud": "TRUE",
                |         "description": "decription",
                |         "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": "invalid input syntax for type numeric: "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": "invalid input syntax for type numeric: "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": "invalid input syntax for type numeric: "null""
                | }

سرچینه: www.habr.com

Add a comment