数据库函数代码管理 - 自动备份函数\过程代码到SVN\github\gitlab-阿里云开发者社区

开发者社区> 阿里云数据库> 正文
登录阅读全文

数据库函数代码管理 - 自动备份函数\过程代码到SVN\github\gitlab

简介:

标签

PostgreSQL , 存储过程 , plpgsql , plpython , pljava , svn , github , 版本管理


背景

市面上有很多代码的版本管理软件,比如gitlab, github, svn等等。

商业数据库的编程能力较强,比如Oracle的PL/SQL,很多传统企业会将对一致性、可靠性要求非常高的业务逻辑放到数据库中,这就造成了数据库内不仅仅存储数据,也存储了部分业务代码。

PostgreSQL 作为开源界最高级的开源数据库,同样支持强大的函数语言plpgsql,同时通过语言扩展,支持pljava, plpython, pltcl, plperl等等,同样,使用PostgreSQL数据库函数处理业务逻辑,也可以像商业数据库一样保证数据一致性、可靠性。

但是问题来了,数据库中存储的业务逻辑代码,如何管理呢?

一种方法是在人工管理,在数据库中执行前、后保留代码到gitlab, github, svn等自建或公共的代码库中。

另一种方法是让数据库直接对接代码库,实时将函数代码提交到代码库。

我们看看PostgreSQL如何实现?

机制

如果要让数据库自动、实时的将修改或新建的函数代码内容提交到代码库,首先要有一个自动机制。

PostgreSQL 有两种机制可以实现:

1. 一种是事件触发器,在执行DDL时,自动触发,此时可以提取DDL内容,然后你想干什么就随你了,PostgreSQL可通过自定义函数操作GITHUB,GITLAB,SVN等。

2. 另一种方法是HOOK,是的,PostgreSQL提供了很多HOOK,允许用户通过钩子做一些旁路逻辑,比如我们在EXECUTE后,截获execute的内容并处理它。截获后PostgreSQL可通过自定义函数操作GITHUB,GITLAB,SVN等。

事件触发器

PostgreSQL的事件触发器指在发生某些DDL事件后,可以触发调用事件触发器函数,函数中我们可以处理很多东西。

1. 事件触发器语法

https://www.postgresql.org/docs/9.6/static/sql-createeventtrigger.html

CREATE EVENT TRIGGER name  
    ON event  
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]  
    EXECUTE PROCEDURE function_name();  

解说

event:指事件,

ddl_command_start, ddl_command_end, table_rewrite and sql_drop。

https://www.postgresql.org/docs/9.6/static/event-trigger-definition.html

filter_variable:TAG

filter_value:指event对应的command tag,比如本文要用到的CREATE FUNCTION,详见如下

https://www.postgresql.org/docs/9.6/static/event-trigger-matrix.html

2. 事件触发器函数的语法

与语言有关,比如plpgsql语言写的事件触发器函数为

create or replace function function_name() returns event_trigger as $$  
declare  
  ...  
begin  
  ...  
end;  
$$ language plpgsql strict;  

3. 事件触发器相关的系统函数调用

https://www.postgresql.org/docs/9.6/static/functions-event-triggers.html

3.1 pg_event_trigger_ddl_commands() 捕获命令结束时的信息,本文要用到objid字段,即函数的objectid,然后调用pg_get_functiondef(oid)得到函数的定义。

Name Type Description
classid Oid OID of catalog the object belongs in
objid Oid OID of the object in the catalog
objsubid integer Object sub-id (e.g. attribute number for columns)
command_tag text command tag
object_type text Type of the object
schema_name text Name of the schema the object belongs in, if any; otherwise NULL. No quoting is applied.
object_identity text Text rendering of the object identity, schema-qualified. Each and every identifier present in the identity is quoted if necessary.
in_extension bool whether the command is part of an extension script
command pg_ddl_command A complete representation of the command, in internal format. This cannot be output directly, but it can be passed to other functions to obtain different pieces of information about the command.

3.2 pg_event_trigger_dropped_objects() 捕获被DROP的对象

与本文无关,不列出

3.3 pg_event_trigger_table_rewrite_oid()和pg_event_trigger_table_rewrite_reason() 捕获table rewrite事件涉及的表和原因

与本文无关,不列出

4. 获取函数定义,pg_get_functiondef(oid)

postgres=# \df *.*def*  
                                       List of functions  
   Schema   |            Name             | Result data type |  Argument data types  |  Type     
------------+-----------------------------+------------------+-----------------------+---------  
 pg_catalog | pg_get_function_arg_default | text             | oid, integer          | normal  
 pg_catalog | pg_get_functiondef          | text             | oid                   | normal  
 pg_catalog | pg_get_indexdef             | text             | oid                   | normal  
 pg_catalog | pg_get_indexdef             | text             | oid, integer, boolean | normal  
 pg_catalog | pg_get_ruledef              | text             | oid                   | normal  
 pg_catalog | pg_get_ruledef              | text             | oid, boolean          | normal  
 pg_catalog | pg_get_triggerdef           | text             | oid                   | normal  
 pg_catalog | pg_get_triggerdef           | text             | oid, boolean          | normal  
 pg_catalog | pg_get_viewdef              | text             | oid                   | normal  
 pg_catalog | pg_get_viewdef              | text             | oid, boolean          | normal  
 pg_catalog | pg_get_viewdef              | text             | oid, integer          | normal  
 pg_catalog | pg_get_viewdef              | text             | text                  | normal  
 pg_catalog | pg_get_viewdef              | text             | text, boolean         | normal  

有了这些要素,我们就可以利用UDF,实时的记录函数的内容,并提到版本管理库了。

HOOK(钩子)

在代码中,我们可以得到目前PG已经定义了哪些HOOK,允许你使用其进行旁路。

grep -i hook src/tools/pgindent/typedefs.list  

ClientAuthentication_hook_type  
CoerceParamHook  
ExecutorCheckPerms_hook_type  
ExecutorEnd_hook_type  
ExecutorFinish_hook_type  
ExecutorRun_hook_type  
ExecutorStart_hook_type  
ExplainOneQuery_hook_type  
FmgrHookEventType  
GucBoolAssignHook  
GucBoolCheckHook  
GucEnumAssignHook  
GucEnumCheckHook  
GucIntAssignHook  
GucIntCheckHook  
GucRealAssignHook  
GucRealCheckHook  
GucShowHook  
GucStringAssignHook  
GucStringCheckHook  
PGNoticeHooks  
ParamFetchHook  
ParseParamRefHook  
ParserSetupHook  
PostParseColumnRefHook  
PreParseColumnRefHook  
ProcessUtility_hook_type  
VariableAssignHook  
check_password_hook_type  
create_upper_paths_hook_type  
emit_log_hook_type  
explain_get_index_name_hook_type  
fmgr_hook_type  
get_attavgwidth_hook_type  
get_index_stats_hook_type  
get_relation_info_hook_type  
get_relation_stats_hook_type  
join_search_hook_type  
needs_fmgr_hook_type  
object_access_hook_type  
planner_hook_type  
post_parse_analyze_hook_type  
row_security_policy_hook_type  
set_join_pathlist_hook_type  
set_rel_pathlist_hook_type  
shmem_startup_hook_type  

例子

这些插件使用到数据库的HOOK,比如用来统计SQL的资源开销,认证延迟等。

contrib/pg_stat_statements/pg_stat_statements.c  

contrib/auto_explain/auto_explain.c  

contrib/auth_delay/auth_delay.c  

本文的CASE,你如果要将CREATE FUNCTION的内容,自动写入SVN,也能使用钩子完成,不再举例。

本地表存储函数代码,版本管理

我们除了可以将代码存入版本管理软件GITHUB、GITLAB、SVN等,还有一种简便的方法,比如存入数据库的表里面。

例子

1. 创建存储函数代码的表

create table svn_func(  
  id serial8 primary key,  -- 序列  
  tx int8, -- 事务号  
  objid oid, -- 函数唯一标示 pg_proc.oid  
  object_type text, -- 类型  
  schema_name text, -- schema name  
  object_identity text, -- 全长对象名: schema_name.object_name  
  in_extension bool, -- 对象是否属于extension  
  crt_time timestamp, -- DDL时间  
  content text  -- DDL翻译成文本  
);  

2. 创建事件触发器函数

create or replace function push_to_svn_func() returns event_trigger as $$  
declare  
  r record;  
begin  
  for r in SELECT * FROM pg_event_trigger_ddl_commands() LOOP  
    insert into svn_func(tx, objid, object_type, schema_name, object_identity, in_extension, crt_time, content)  
      values   
       (  
          txid_current(),  
      r.objid,   
          r.object_type,  
          r.schema_name,  
          r.object_identity,  
          r.in_extension,  
          now(),  
          pg_get_functiondef(r.objid)  
    );  
  end LOOP;  
end;  
$$ language plpgsql strict;  

3. 创建事件触发器

create event trigger et1 on ddl_command_end  when TAG in ('create function') execute procedure push_to_svn_func();  

4. 测试

4.1 创建函数

create or replace function f123(id int) returns int as $$                                                           
declare  
begin  
return id+1;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  

4.2 创建同名,但是参数不同的函数

create or replace function f123(id int, diff int) returns int as $$  
declare  
begin  
return id+diff;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  

4.3 创建完全相同的函数,写入不同的SCHEMA

postgres=# create schema test;  
CREATE SCHEMA  
postgres=# create or replace function test.f123(id int, diff int) returns int as $$  
declare  
begin  
return id+diff;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  

4.4 覆盖创建原有函数

postgres=# create or replace function test.f123(id int, diff int) returns int as $$  
declare  
begin  
return id+diff;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  

4.5 查看函数内容记录

postgres=# select * from svn_func;  
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------  
id              | 1  
tx              | 46056990  
objid           | 172533  
object_type     | function  
schema_name     | public  
object_identity | public.push_to_svn_func()  
in_extension    | f  
crt_time        | 2017-03-05 13:37:25.518273  
content         | CREATE OR REPLACE FUNCTION public.push_to_svn_func()                                                           +  
                |  RETURNS event_trigger                                                                                         +  
                |  LANGUAGE plpgsql                                                                                              +  
                |  STRICT                                                                                                        +  
                | AS $function$                                                                                                  +  
                | declare                                                                                                        +  
                |   r record;                                                                                                    +  
                | begin                                                                                                          +  
                |   for r in SELECT * FROM pg_event_trigger_ddl_commands() LOOP                                                  +  
                |     insert into svn_func(tx, objid, object_type, schema_name, object_identity, in_extension, crt_time, content)+  
                |       values                                                                                                   +  
                |        (                                                                                                       +  
                |           txid_current(),                                                                                      +  
                |   r.objid,                                                                                                     +  
                |           r.object_type,                                                                                       +  
                |           r.schema_name,                                                                                       +  
                |           r.object_identity,                                                                                   +  
                |           r.in_extension,                                                                                      +  
                |           now(),                                                                                               +  
                |           pg_get_functiondef(r.objid)                                                                          +  
                | );                                                                                                             +  
                |   end LOOP;                                                                                                    +  
                | end;                                                                                                           +  
                | $function$                                                                                                     +  
                |   
-[ RECORD 2 ]---+----------------------------------------------------------------------------------------------------------------  
id              | 2  
tx              | 46056991  
objid           | 172508  
object_type     | function  
schema_name     | public  
object_identity | public.f123(integer)  
in_extension    | f  
crt_time        | 2017-03-05 13:37:50.630288  
content         | CREATE OR REPLACE FUNCTION public.f123(id integer)                                                             +  
                |  RETURNS integer                                                                                               +  
                |  LANGUAGE plpgsql                                                                                              +  
                |  STRICT                                                                                                        +  
                | AS $function$                                                                                                  +  
                | declare                                                                                                        +  
                | begin                                                                                                          +  
                | return id+1;                                                                                                   +  
                | end;                                                                                                           +  
                | $function$                                                                                                     +  
                |   
-[ RECORD 3 ]---+----------------------------------------------------------------------------------------------------------------  
id              | 3  
tx              | 46056992  
objid           | 172573  
object_type     | function  
schema_name     | public  
object_identity | public.f123(integer,integer)  
in_extension    | f  
crt_time        | 2017-03-05 13:38:38.068266  
content         | CREATE OR REPLACE FUNCTION public.f123(id integer, diff integer)                                               +  
                |  RETURNS integer                                                                                               +  
                |  LANGUAGE plpgsql                                                                                              +  
                |  STRICT                                                                                                        +  
                | AS $function$                                                                                                  +  
                | declare                                                                                                        +  
                | begin                                                                                                          +  
                | return id+diff;                                                                                                +  
                | end;                                                                                                           +  
                | $function$                                                                                                     +  
                |   
-[ RECORD 4 ]---+----------------------------------------------------------------------------------------------------------------  
id              | 4  
tx              | 46056994  
objid           | 172575  
object_type     | function  
schema_name     | test  
object_identity | test.f123(integer,integer)  
in_extension    | f  
crt_time        | 2017-03-05 13:39:06.332268  
content         | CREATE OR REPLACE FUNCTION test.f123(id integer, diff integer)                                                 +  
                |  RETURNS integer                                                                                               +  
                |  LANGUAGE plpgsql                                                                                              +  
                |  STRICT                                                                                                        +  
                | AS $function$                                                                                                  +  
                | declare                                                                                                        +  
                | begin                                                                                                          +  
                | return id+diff;                                                                                                +  
                | end;                                                                                                           +  
                | $function$                                                                                                     +  
                |   
-[ RECORD 5 ]---+----------------------------------------------------------------------------------------------------------------  
id              | 5  
tx              | 46056995  
objid           | 172575  
object_type     | function  
schema_name     | test  
object_identity | test.f123(integer,integer)  
in_extension    | f  
crt_time        | 2017-03-05 13:39:14.66429  
content         | CREATE OR REPLACE FUNCTION test.f123(id integer, diff integer)                                                 +  
                |  RETURNS integer                                                                                               +  
                |  LANGUAGE plpgsql                                                                                              +  
                |  STRICT                                                                                                        +  
                | AS $function$                                                                                                  +  
                | declare                                                                                                        +  
                | begin                                                                                                          +  
                | return id+diff;                                                                                                +  
                | end;                                                                                                           +  
                | $function$                                                                                                     +  
                |   

4.6 回退测试

比如你想将某个函数,回退到以前的版本,在svn_func表中选定一条ID的content, 执行即可。

do language plpgsql $$  
declare  
  sql text;  
begin  
  select content into sql from svn_func where id=2;  
  execute sql;  
end;  
$$;  

数据库直连代码库(github,gitlab,svn...)

前面的例子介绍了如何将函数版本存入表中,如果你想将函数内容存入代码管理库,也很简单,下面提供一些伪代码。

例子

1. 创建高级过程语言,通过他们编写的函数与代码管理库交互。

pljava u, plpython u, .....  

2. 编写对应的pl函数,输入为content等, 写入代码管理库。

假设函数名为plpython_svn(content,其他参数);

3. 将第二步编写的函数,通过事件触发器调用。

4. 创建事件触发器函数

create or replace function push_to_svn_func() returns event_trigger as $$  
declare  
  r record;  
begin  
  for r in SELECT * FROM pg_event_trigger_ddl_commands() LOOP  
    insert into svn_func(tx, objid, object_type, schema_name, object_identity, in_extension, crt_time, content)  
      values   
       (  
          txid_current(),  
      r.objid,   
          r.object_type,  
          r.schema_name,  
          r.object_identity,  
          r.in_extension,  
          now(),  
          pg_get_functiondef(r.objid)  
    );  
      -- 调用plpython_svnc(....), 将内容提交到SVN  
  end LOOP;  
end;  
$$ language plpgsql strict;  

5. 创建事件触发器

create event trigger et1 on ddl_command_end  when TAG in ('create function') execute procedure push_to_svn_func();  

小结

1. 通过事件触发器、UDF,我们可以将DDL的内容写入表中,也可以提交到代码管理库中。

2. 事件触发器其他用途,譬如我们使用逻辑复制,DDL不记录在REDO中,幸好可以通过事件触发器完成DDL复制。

参考

https://www.postgresql.org/docs/9.6/static/functions-event-triggers.html

https://www.postgresql.org/docs/9.6/static/event-trigger-definition.html

《PostgreSQL Oracle 兼容性之 - 事件触发器实现类似Oracle的回收站功能》

《PostgreSQL 事件触发器 - DDL审计 , DDL逻辑复制 , 打造DDL统一管理入》

《PostgreSQL 事件触发器 - PostgreSQL 9.3 Event Trigger》

《PostgreSQL 事件触发器 - DDL审计 , DDL逻辑复制 , 打造DDL统一管理入》

pgsql-http插件

https://github.com/pramsey/pgsql-http

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接