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

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

标签

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
25天前
|
SQL 数据挖掘 测试技术
南大通用GBase8s数据库:LISTAGG函数的解析
南大通用GBase8s数据库:LISTAGG函数的解析
|
25天前
|
SQL 测试技术 数据库
|
2月前
|
SQL 数据库 数据库管理
数据库SQL函数应用技巧与方法
在数据库管理中,SQL函数是处理和分析数据的强大工具
5-20|本文目标为svn合并代码
5-20|本文目标为svn合并代码
|
4月前
|
SQL 数据处理 数据库
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
482 0
|
4月前
|
SQL Oracle 关系型数据库
SQL Server 2016 自动备份、收缩数据库
SQL Server 2016 自动备份、收缩数据库
73 0
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库基础第二篇(函数)
MySQL数据库基础第二篇(函数)
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一
48 5
|
6月前
|
关系型数据库 MySQL 数据库
MySQL数据库——函数-字符串函数、数值函数、日期函数、流程函数
MySQL数据库——函数-字符串函数、数值函数、日期函数、流程函数
53 2
下一篇
DataWorks