資料庫函數代碼管理 - 自動備份函數\過程代碼到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. 事件觸發器文法

<a href="https://www.postgresql.org/docs/9.6/static/sql-createeventtrigger.html">https://www.postgresql.org/docs/9.6/static/sql-createeventtrigger.html</a>

解說

event：指事件，

ddl_command_start, ddl_command_end, table_rewrite and sql_drop。

<a href="https://www.postgresql.org/docs/9.6/static/event-trigger-definition.html">https://www.postgresql.org/docs/9.6/static/event-trigger-definition.html</a>

filter_variable：tag

filter_value：指event對應的command tag，比如本文要用到的create function，詳見如下

<a href="https://www.postgresql.org/docs/9.6/static/event-trigger-matrix.html">https://www.postgresql.org/docs/9.6/static/event-trigger-matrix.html</a>

2. 事件觸發器函數的文法

與語言有關，比如plpgsql語言寫的事件觸發器函數為

3. 事件觸發器相關的系統函數調用

<a href="https://www.postgresql.org/docs/9.6/static/functions-event-triggers.html">https://www.postgresql.org/docs/9.6/static/functions-event-triggers.html</a>

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 of the object in the catalog

objsubid

integer

object sub-id (e.g. attribute number for columns)

command_tag

text

command tag

object_type

type of the object

schema_name

name of the schema the object belongs in, if any; otherwise null. no quoting is applied.

object_identity

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)

有了這些要素，我們就可以利用udf，實時的記錄函數的内容，并提到版本管理庫了。

在代碼中，我們可以得到目前pg已經定義了哪些hook，允許你使用其進行旁路。

例子

這些插件使用到資料庫的hook，比如用來統計sql的資源開銷，認證延遲等。

本文的case，你如果要将create function的内容，自動寫入svn，也能使用鈎子完成，不再舉例。

我們除了可以将代碼存入版本管理軟體github、gitlab、svn等，還有一種簡便的方法，比如存入資料庫的表裡面。

1. 建立存儲函數代碼的表

2. 建立事件觸發器函數

3. 建立事件觸發器

4. 測試

4.1 建立函數

4.2 建立同名，但是參數不同的函數

4.3 建立完全相同的函數，寫入不同的schema

4.4 覆寫建立原有函數

4.5 檢視函數内容記錄

4.6 回退測試

比如你想将某個函數，回退到以前的版本，在svn_func表中標明一條id的content, 執行即可。

前面的例子介紹了如何将函數版本存入表中，如果你想将函數内容存入代碼管理庫，也很簡單，下面提供一些僞代碼。

1. 建立進階過程語言，通過他們編寫的函數與代碼管理庫互動。

2. 編寫對應的pl函數，輸入為content等, 寫入代碼管理庫。

假設函數名為plpython_svn(content,其他參數);

3. 将第二步編寫的函數，通過事件觸發器調用。

4. 建立事件觸發器函數

5. 建立事件觸發器

1. 通過事件觸發器、udf，我們可以将ddl的内容寫入表中，也可以送出到代碼管理庫中。

2. 事件觸發器其他用途，譬如我們使用邏輯複制，ddl不記錄在redo中，幸好可以通過事件觸發器完成ddl複制。

<a href="https://github.com/digoal/blog/blob/master/201504/20150429_01.md">《postgresql oracle 相容性之 - 事件觸發器實作類似oracle的資源回收筒功能》</a>

<a href="https://github.com/digoal/blog/blob/master/201412/20141211_01.md">《postgresql 事件觸發器 - ddl審計 , ddl邏輯複制 , 打造ddl統一管理入》</a>

<a href="https://github.com/digoal/blog/blob/master/201303/20130313_01.md">《postgresql 事件觸發器 - postgresql 9.3 event trigger》</a>

pgsql-http插件

<a href="https://github.com/pramsey/pgsql-http">https://github.com/pramsey/pgsql-http</a>