MathJax

MathJax-2

MathJax-3

Google Code Prettify

置頂入手筆記

EnterproseDB Quickstart — 快速入門筆記

由於考慮採用 EnterpriseDB 或是直接用 PostgreSQL 的人,通常需要一些入手的資料。這邊紀錄便提供相關快速上手的簡單筆記 ~ 這篇筆記以 資料庫安裝完畢後的快速使用 為目標,基本紀錄登入使用的範例:

2016年6月30日 星期四

在 PostgreSQL 透過 Foreign Data Wrapper 與 Oracle 資料庫交換資料

在 PostgreSQL 企業版 EnterpriseDB 中,所提供的 Oracle 相容性,可以用 DBLink 與 Oracle 資料庫交換資料。而在原生的 PostgreSQL 上,則可以透過 Foreign Data Wrapper 去存取位於 Oracle 資料庫上的的外部資料。Foreign Data Wrapper 是一種 PostgreSQL Extension,因此安裝 Foreign Data Wrapper 的方式,和安裝一般社群取得的 PostgreSQL Extension 步驟是差不多的。

在與 Oracle 交換資料的需求上,對於企業版 EnterpriseDB 使用者來說,一般還是建議使用 Foreign Data Wrapper,盡量擺脫 DBLink,以便降低對於 Oracle 資料庫功能的依賴,也讓企業在未來在社群版 PostgreSQL 及企業版 EnterpriseDB 之間能夠擁有更好的選擇彈性。

以下將由 CentOS 上的企業版 EnterpriseDB 進行示範。

2016年6月16日 星期四

PostgreSQL / EnterpriseDB / Oracle 三者的 DATE datatype 的差異

在 EDB 中,edb_redwood_date 是 Oracle 相容性的設置參數(位在 $PGDATA/postgresql.conf 裡面),這會將一些 PostgreSQL 原生的時間格式,微調成能夠相容於 Oracle 的設定。

根據 EnterpriseDB 的 Oracle 相容性使用手冊 Database Compatibility for Oracle® Developer's Guide v9.5 - 3.2.4 Date/Time Types ,以及 PostgreSQL 原生版文件 PostgreSQL: Documentation: 9.1: Date/Time Types - Table 8-9. Date/Time Types。可見 EDB 為了 Oracle 相容性緣故,於啟用 edb_redwood_date 之後(預設為啟用),會將 DATE 轉換成 PostgreSQL 原生的 TIMESTAMP(0)。

下圖為一個小測試:

不過根據 PostgreSQL 原生文件所述:
原生的 DATE 為 4 Bytes 的資料型態
而 TIMESTAMP 為 8 Bytes 的資料型態

Oracle 中的 DATE 為 7 Bytes 的資料型態

所以 EDB 的預設 DATE 為 TIMESTAMP(0)(也就是 8 Byte 僅含日期的時間)是 Oracle 相容所必須的設計。

因此,如果沒有要在 EDB 上面進行額外開發原生 PostgreSQL 功能的需求,就無須擔心 EDB 自動轉換的問題,保持預設的 edb_redwood_date = on 就好,以達到最佳 Oracle 相容性。





參考:
Database Compatibility for Oracle® Developer's Guide v9.5 - 3.2.4 Date/Time Types

PostgreSQL: Documentation: 9.1: Date/Time Types - Table 8-9. Date/Time Types

How many bytes does a DATE use? | Oracle Community

2016年6月13日 星期一

EnterpriseDB 功能 - EDB*Wrap 使用筆記

EDB*Wrap 工具是 EDB 的企業版功能,該功能用在加密資料庫裡面的 Function/Stored Procedure/Package 等程式,如此達到保護公司資產的需求。
以下利用 Oracle 相容性建立一個 PL/SQL 的 Package 示範在資料庫中顯示原始碼,並再利用 EDB*Wrap 工具後,觀察匯入後的加密原始碼。

首先,先建立一個簡單的 Package:
edb=# CREATE OR REPLACE PACKAGE pkgedbdemo
edb-# AS
edb$#  function funtest() return text;
edb$# END pkgedbdemo;
CREATE PACKAGE

edb=# CREATE OR REPLACE PACKAGE BODY pkgedbdemo AS
edb$#  function funtest() return text
edb$#  AS
edb$#   BEGIN
edb$#    return cast('test' as text);
edb$#   END;
edb$# END pkgedbdemo;
CREATE PACKAGE BODY

由於 EDB 支援的 Oracle 相容性,我們可以利用在 Oracle 中查看Function/Stored Procedure/Package 原始碼的方法:How to get package information from sqltext in oracle 10g,一樣在 EDB 中取得 Oracle 相容性建立的物件。
edb=# SELECT text FROM user_source
edb-# WHERE name = 'PKGEDBDEMO'
edb-# AND type = 'PACKAGE';
                         text
 ------------------------------------------------------
CREATE OR REPLACE PACKAGE enterprisedb.pkgedbdemo IS
 FUNCTION funtest() RETURN text;
 END
(3 rows)

edb=# SELECT text FROM user_source
edb-# WHERE name = 'PKGEDBDEMO'
edb-# AND type = 'PACKAGE BODY';
                           text
-----------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY enterprisedb.pkgedbdemo IS
 FUNCTION funtest() RETURN text IS
  BEGIN
   return cast('test' as text);
  END;
END
(6 rows)

edb=# -- 另外一方法
edb=# SELECT text FROM user_source
edb-# WHERE name = 'PKGEDBDEMO'
edb-# AND type LIKE 'PACKAGE%';
                           text
-----------------------------------------------------------
CREATE OR REPLACE PACKAGE enterprisedb.pkgedbdemo IS
 FUNCTION funtest() RETURN text;
 END
CREATE OR REPLACE PACKAGE BODY enterprisedb.pkgedbdemo IS
 FUNCTION funtest() RETURN text IS
  BEGIN
   return cast('test' as text);
  END;
END
(9 rows)

此外,原生 PL/pgSQL 的原始碼檢視方法,可以參考:Re: View user defined functionsGet definition of function, sequence, type etc. in Postgresql with SQL query - Stack Overflow。利用原生語法,也能窺見 Oracle 的 PL/SQL 語法如何在 EDB 中轉換成原生的 Procedural Language 語法:
edb=# -- 方法一
edb=# SELECT prosrc FROM pg_proc
edb-# WHERE proname LIKE '%funtest%';
            prosrc
---------------------------------
                               +
  BEGIN                        +
   return cast('test' as text);+
  END
(1 row)

edb=# -- 方法二
edb=# SELECT pg_get_functiondef(oid)
edb-# FROM pg_proc
edb-# WHERE proname = 'funtest';
                pg_get_functiondef
-------------------------------------------------
CREATE OR REPLACE FUNCTION pkgedbdemo.funtest()+
 RETURNS text                                  +
 LANGUAGE edbspl                               +
AS $function$                                  +
  BEGIN                                        +
   return cast('test' as text);                +
  END$function$                                +

(1 row)

edb=# -- 方法三
edb=# SELECT funsrc
edb-# FROM pg_function
edb-# WHERE funname = 'funtest';
            prosrc
---------------------------------
                               +
  BEGIN                        +
   return cast('test' as text);+
  END
(1 row)

接著,上面的 Package 想要隱藏起來不被看到,就可以使用 EDB*Wrap 工具:該工具的用法,是先將希望加密的 PL/SQL 或 PL/pgSQL 原始碼利用該工具加密之後,再將加密過的檔案載入資料庫中。
[enterprisedb@myvm ~]$ cat pkgedbdemo.txt
CREATE OR REPLACE PACKAGE enterprisedb.pkgedbdemo IS
 FUNCTION funtest() RETURN text;
END;
CREATE OR REPLACE PACKAGE BODY enterprisedb.pkgedbdemo IS
FUNCTION funtest() RETURN text IS
 BEGIN
  return cast('test' as text);
 END;
END;
[enterprisedb@myvm ~]$ edbwrap -i pkgedbdemo.txt

EDB*Wrap Utility: Release 9.5.3.8

Copyright (c) 2004-2015, EnterpriseDB Corporation. All Rights Reserved.

Using encoding UTF8 for input
Processing pkgedbdemo.txt to pkgedbdemo.plb

將產生出的檔案載入 EDB 中,取代既有的程式,並再查詢原始碼:
edb=# \i pkgedbdemo.plb
CREATE PACKAGE BODY
edb=# SELECT text FROM user_source
edb-# WHERE name = 'PKGEDBDEMO'
edb-# AND type LIKE 'PACKAGE%';
                                   text
------------------------------------------------------------------------------
 $__EDBwrapped__$
 UTF8
 d5u3HfdLfD5e3jdH/GMPYse0fCXj26+0NUbZn6BnouTCnfdckQOi6+UWmvOnFlULY+ej0KUpkb0b
 x9i51l8XamoYErnIU2i+DIUlrvzvju8wKsLTdT8N5jfXUljOFeoNGna0kWx6zbze8j1Yg0lNLWX+
 52qCNaYsysb3idAUDk23uOE=
 $__EDBwrapped__$
 $__EDBwrapped__$
 UTF8
 d86fuzolWN11OtdWbaHS8UOV52E2YISSuFNi3xS0S1LEzDbBgKzi4bJi7/CP9oXwNwfNyWlKg55s
 T5Ajbb8pDYhrnJQl+4jQ0Cy7Rcqo6S0l0Tlx7fwX0ObEBabKrs0vog+s0lcSPRNxIxUiUP5C2ycJ
 lsjJP+E3j9ju7+exuXnlh/Ae+8NhH9hQdlaaOV8KyOyuOo0d6XVihxkARKaF5zQn7xesiK59uA==
 $__EDBwrapped__$
 (12 rows)

不管是 Package,或是 Package Body,都已經被加密成功。
使用手冊建議,一般只要加密 Package Body 就好了,不然要用 Package 的人就很難查裡面有什麼內容可以用了...
[enterprisedb@myvm ~]$ cat pkgedbdemo_body.txt
CREATE OR REPLACE PACKAGE BODY enterprisedb.pkgedbdemo IS
FUNCTION funtest() RETURN text IS
 BEGIN
  return cast('test' as text);
 END;
END;
[enterprisedb@myvm ~]$ edbwrap -i pkgedbdemo_body.txt

EDB*Wrap Utility: Release 9.5.3.8

Copyright (c) 2004-2015, EnterpriseDB Corporation. All Rights Reserved.

Using encoding UTF8 for input
Processing pkgedbdemo_body.txt to pkgedbdemo_body.plb
edb=# \i pkgedbdemo_body.plb
CREATE PACKAGE BODY
edb=# SELECT text FROM user_source
edb-# WHERE name = 'PKGEDBDEMO'
edb-# AND type LIKE 'PACKAGE%';
                                     text
------------------------------------------------------------------------------
 CREATE OR REPLACE PACKAGE enterprisedb.pkgedbdemo IS
  FUNCTION funtest() RETURN text;
  END
 $__EDBwrapped__$
 UTF8
 d86fuzolWN11OtdWbaHS8UOV52E2YISSuFNi3xS0S1LEzDbBgKzi4bJi7/CP9oXwNwfNyWlKg55s
 T5Ajbb8pDYhrnJQl+4jQ0Cy7Rcqo6S0l0Tlx7fwX0ObEBabKrs0vog+s0lcSPRNxIxUiUP5C2ycJ
 lsjJP+E3j9ju7+exuXnlh/Ae+8NhH9hQdlaaOV8KyOyuOo0d6XVihxkARKaF5zQn7xesiK59uA==
 $__EDBwrapped__$
 (9 rows)

像上面這樣就兼顧使用和加密,摸蜊仔兼洗褲~


參考: