台灣的Web工程師

想必大家的有經驗，在專案交付的時候，必須有一堆文件得跟著交付，而其中一份文件，就是DB Schema的說明，

正常來說，是一邊進行DB Schema文件的修改，一邊進行實際上的DB Schema修改，

但我們都知道，在開發時期，DB Schema是改了又改，因為Schema不改的話，程式進行不下去，

但是文件不改的話，程式卻是可以進行的，所以一來一往之後，文件跟現實的Schema就會越差越遠。

為了解決這個問題，最好的方法是：專案完成後，再由DB Schema轉成文件。

而為了達成這個目的，有幾個必須的步驟：

１。在建立Table Schema時進行註解，也包含Table本身的註解。

２。用工具將Table Schema轉成文件。

工具的部份，目前筆者並沒有聽到比較好的免費工具，不過為了專案的需求，

筆者利用上班的時間，自己寫了一套，反正之後每個專案都會用到，所以值得投資。

筆者自己寫的程式內容如下，可以供大家參考：

１。將DB Schema轉成HackMD（用JAVA寫的）

２。將HackMD轉成HTML（用NodeJS的aglio套件）

而第一步在讀取DB Schema的時候，就發現

問題１：取得所有Table Name，最好也有Table的註解。

問題２：取得特定Table的欄位、型態、註解等。

其實只要解決這二個問題，程式上其實並沒有什麼困難，事實上筆者解決這二個問題後，花不到半天就把所有功能解決了。

因為要找這二道SQL比較麻煩，所以就在這裡記錄並分享出來：

問題１：取得所有Table Name，最好也有Table的註解。=> SQL如下：

—————————————————————————————————————————————————————

 SELECT 
     A.schema, A.name, A.owner, A.type, B.description
 FROM (
     SELECT n.nspname as schema,
         c.relname as name,
         CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as type,
         pg_catalog.pg_get_userbyid(c.relowner) as owner,
         relfilenode
     FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     WHERE
         c.relkind IN ('r','s','') AND n.nspname !~ '^pg_toast' AND n.nspname ~ '^(public)$'
     ORDER BY 1,2
 ) A LEFT JOIN pg_catalog.pg_description B ON A.relfilenode = B.objoid AND B.objsubid = 0;

—————————————————————————————————————————————————————

問題２：取得特定Table的欄位、型態、註解等。=> SQL如下：

—————————————————————————————————————————————————————

SELECT D.column, D.type, D.characterMaximumLength, B.description
FROM (
    SELECT 
        c.relname AS name,
        CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as type,
        pg_catalog.pg_get_userbyid(c.relowner) as owner,
        relfilenode
    FROM pg_catalog.pg_class c
    WHERE
        c.relkind IN ('r','s','') AND c.relname = #{tableName}
    ORDER BY 1,2
) A
LEFT JOIN (
    SELECT 
        column_name AS column, ordinal_position, data_type AS type, table_name, character_maximum_length characterMaximumLength 
    FROM information_schema.columns 
) D ON A.name = D.table_name 
LEFT JOIN pg_catalog.pg_description B ON A.relfilenode = B.objoid AND D.ordinal_position = B.objsubid;

—————————————————————————————————————————————————————

對了，還有一個取得Table的PK，SQL如下：

—————————————————————————————————————————————————————

SELECT a.attname AS column, format_type(a.atttypid, a.atttypmod) AS type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = #{tableName}::regclass
AND    i.indisprimary;

—————————————————————————————————————————————————————

以上分享給大家參考。