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

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

标签

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数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
11天前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
31 4
|
25天前
|
关系型数据库 MySQL 数据库
Navicat备份数据库
涵盖`Navicat`数据库备份、数据安全及备份策略等主题。文库采用精美主题,提升阅读体验。
16 1
Navicat备份数据库
|
28天前
|
SQL NoSQL Java
彻底革新你的数据库操作体验!Micronaut数据访问技巧让你瞬间爱上代码编写!
【9月更文挑战第10天】Java开发者们一直在寻找简化应用程序与数据库交互的方法。Micronaut作为一个现代框架,提供了多种工具和特性来提升数据访问效率。本文介绍如何使用Micronaut简化数据库操作,并提供具体示例代码。Micronaut支持JPA/Hibernate、SQL及NoSQL(如MongoDB),简化配置并无缝集成。通过定义带有`@Repository`注解的接口,可以实现Spring Data风格的命名查询。
50 6
|
28天前
|
SQL 数据库 数据安全/隐私保护
如何手动备份数据库?
如何手动备份数据库?
48 1
|
1月前
|
前端开发 数据库
数据库表设计生成代码
BizWorks ToolKit插件集成Mybatis-Plus代码生成工具,支持从数据库表生成代码,便于研发过程中数据模型变更后的代码同步。本文介绍批量生成代码的方法、配置说明及项目示例。配置文件`*.mp.yaml`用于描述生成行为,可放置于`src/main/resource/bizworks/mybatis-plus/`路径下。配置包括数据库信息、输出目录及包名等。通过IDEA右键菜单即可启动代码生成。具体配置和示例详见文档。
25 2
|
1月前
|
前端开发 数据库 开发者
数据模型(数据库表设计)生成代码
BizWorks ToolKit 插件集成 Mybatis-Plus 代码生成工具,支持从数据库表批量生成代码,简化开发流程。本文详细介绍配置方法及项目示例,包括配置文件格式、生成选项及具体操作步骤,帮助开发者快速实现代码同步更新。配置文件 `.mp.yaml` 支持自定义输出目录、生成组件等,适用于多种项目结构。
31 0
|
1月前
|
关系型数据库 数据库 网络虚拟化
Docker环境下重启PostgreSQL数据库服务的全面指南与代码示例
由于时间和空间限制,我将在后续的回答中分别涉及到“Python中采用lasso、SCAD、LARS技术分析棒球运动员薪资的案例集锦”以及“Docker环境下重启PostgreSQL数据库服务的全面指南与代码示例”。如果你有任何一个问题的优先顺序或需要立即回答的,请告知。
48 0
|
1月前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能,它能够帮助数据库管理员在数据丢失或损坏的情况下,有效地恢复数据。通过定期备份数据库和事务日志,并在需要时按照正确的步骤恢复,可以最大限度地减少数据丢失的风险。需要注意的是,恢复数据是一个需要谨慎操作的过程,建议在执行恢复操作之前,详细了解相关的操作步骤和注意事项,以确保数据的安全和完整。
64 0
|
2月前
|
JSON 数据格式 Java
化繁为简的魔法:Struts 2 与 JSON 联手打造超流畅数据交换体验,让应用飞起来!
【8月更文挑战第31天】在现代 Web 开发中,JSON 成为数据交换的主流格式,以其轻量、易读和易解析的特点受到青睐。Struts 2 内置对 JSON 的支持,结合 Jackson 库可便捷实现数据传输。本文通过具体示例展示了如何在 Struts 2 中进行 JSON 数据的序列化与反序列化,并结合 AJAX 技术提升 Web 应用的响应速度和用户体验。
78 0
|
2月前
|
开发者 前端开发 JavaScript
如何解锁Play Framework的模块化力量?揭秘构建未来Web应用的关键策略
【8月更文挑战第31天】在现代软件开发中,模块化设计对于构建可维护和可扩展的应用程序至关重要。Play Framework作为一个高性能Web应用框架,提供了强大的模块化支持,使开发者能够将应用分解为独立且可重用的模块。本文将探讨Play Framework中模块化设计的最佳实践,并通过示例代码展示如何创建模块、使用依赖注入管理模块依赖、模块化前端资源及测试模块,帮助开发者构建结构清晰、易于维护和扩展的应用程序。
29 0