2010年5月12日 星期三

Postgresql Trigger -- (II) 待完

做個Trigger的範例。歷程功能。

相信大家都曾經碰過這樣的狀況,  有一些非常非常重要的資料表，像是金錢往來的會計資料，記錄進出貨的庫存資料，我們希望對它做的所有操作，都能留下記錄，以便萬一出事的時候，可以查到倒底是誰動過資料。

要達到這樣的效果，有兩種做法，一種是在應用程式裡面做，一開始的時候，就開兩張資料表，一張主資料表，一張歷程資料表，主資料表中記下誰，在什麼時候動過資料，歷程資料表中則多一個欄位，記錄是做了什麼動作（新增、修改、刪除）

可是世界往往不像我們想像的那麼美好，有誰會知道，有一天某張資料表會變得那麼重要，重要到要幫它做歷程呢？

因此，大部份狀況，我們都是臨時決定，或被通知，要幫某資料表做歷程。在這種狀況下，往往系統都已經上線使用，要請廠商修改，都要花一筆錢。這種狀況下，PostgreSQL的Trigger就幫上忙了。

第二種作法就是，你可以臨時加開一張歷程表，利用Trigger，設定主資料表有任何動作時（新增、修改、刪除），都要到歷程表中寫入歷程資料。但是，要特別提出來的，主資料表中，一定要記下誰，在什麼時候動過資料，這種做法才能夠適用，因此，記得，在採購任何系統時，一定要記得提出，每張資料表中，必需有operateBy (最後資料操作者)，這個欄位，以免碰上要加歷程功能時，廠商還要修改加錢。

如果你忘記了，那Trigger只能幫我們留下，主資料表在什麼時候 operateAt，被做了什麼操作operate。至於是誰，你可能要到系統的登入記錄去找找，那個時間，有那一個有權限的人登入了系統。至於是不是他做的，那就還有得爭了。

-- Drop Table exgRateLog;
-- Drop Table exgRate;


Create Table exgRate (
   exgRateId serial primary key, -- 主鍵
   factTime timestamp,    　       -- 匯入時間
   currency char(3),          　　   -- 貨幣別
   purpose varchar(2),   　        -- 匯率種類
   currType varchar(10),     　    -- 貨幣種類
   rate numeric(18,6),         　   -- 匯率
   operateAt timestamp,
   operateBy varchar(15)
);






Create Table exgRateLog (
   exgRateId serial primary key, -- 主鍵
   factTime timestamp,    　       -- 匯入時間
   currency char(3),          　　   -- 貨幣別
   purpose varchar(2),   　        -- 匯率種類
   currType varchar(10),     　    -- 貨幣種類
   rate numeric(18,6),         　   -- 匯率
   operateAt timestamp,
   operateBy varchar(15),
   operate char(1)
);
張貼者： 於 1 則留言:

2010年5月11日 星期二

Postgresql Trigger -- (I)

Trigger 中文名稱叫觸發器。

為什麼要叫觸發器? 因為我們希望，我們可以在某個資料表上，設定在某個動作（新增、修改、刪除）之前或之後，PostgreSQL能夠被觸發，自動執行一段我們設計好的程式碼。

所以，觸發器的使用設計步驟可分為兩個動作

1. 設計要自動執行的程式碼，建立一個函數Function

2. 對要監控的資料表，設定觸發器，以呼叫已建立的函數。

3. 測試
張貼者： 於 沒有留言:

2010年5月10日 星期一

Partition Table -- (III)

因為partition table 談到rule 和 trigger，　所以打算介紹一下。

前面在談到
－－－
開發rule 或 trigger, 對操作主資料表的行為（新增、修改、刪除），重導到子資料表去。
－－－
這個東西，對一般的開發者來說，恐怕是十分陌生。簡單講，當你不想改，或無法改客戶端程式的時候，你就需要rule或trigger。

想像一下下面的場景，公司或你，從某廠商那裡採購了一套系統，剛開始大家用得非常高興，經過一年兩年，你有新的需求，跟廠商談了以後，他給了一個天價，要不然就不做，或是很不幸地，這家廠商倒了，你也找不到人來幫你改。

這時候，這套系統對公司來說已經是不可或缺；你又沒錢，或是有錢但禁不起轉換系統的風險，而你需要的，只是小小的一個功能修改。你該怎麼做？

如果你使用PostgreSQL做為後端的資料庫，PostgreSQL支援trigger和rule來幫你，讓你不需要修改廠商的程式，也可以達到你要的效果。
張貼者： 於 1 則留言:

Partition Table -- (II)

根據PostgreSQL網站的資料
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
PostgreSQL支援 1. Range Partiontion    2. List Partition   兩種分割方式。實際上, 這兩種分割方式只是應用的不同, 它底層的機制, 是相同的。

我們最常使用到的分割，大概就是Range Partition。我們常會碰到隨著日期增加，資料量也不斷增加的資料，像是銀行的匯率資料，計程車隊的GPS定位資料等。這些資料，有些是不能丟，必需保存做為統計資料使用，這種資料，就必需使用partition的技術，進行保存，以方便後續的查詢使用。

但是，以系統設計的觀點來看，當你的資料會隨時間無限制增大的時候，整個系統的效能，勢必會被拖垮，容我提醒一聲，Patition並不是萬靈丹，它只是讓你操控大容量資料的一個手段而已，以之前提到的案例，銀行的匯率資料，過了一年（一個會計經營年度），恐怕就沒有什麼使用的價值，GPS定位資料，過了一個月（一個拆帳結算周期），恐怕也就沒什麼再使用的必要，在系統設計時，必需設計一個定期清理的機制，每年每月，將資料搬到備份表或歷程表中。只留下有使用價值的資料區間。

系統要使用的，必需是一個穩定的資料量或資料區間，否則，你就無法保證系統的穩定性。在這個設計前提下，我們才可以使用Partition的技術，技術是為系統服務的，絕對不要陷入為技術而技術的境地，這會傷害到你個人的價值。

簡單地講，PostgreSQL Partition Table的使用步驟如下：

1. 建立一個主資料表 Master table，主資料表必需有一個關鍵欄位，做為分隔界限的依據。
2. 繼承主資料表，建立子資料表 child table
3. 在子資料表建立限制Constraint, 限定關鍵欄位的區間間隔。
4. 在子資料表針對可能的查詢欄位，建立索引index
5. 開發rule 或 trigger, 對操作主資料表的行為（新增、修改、刪除），重導到子資料表去。
6. 把postgresql.conf 設定檔中的constraint exclusion參數打開，這樣針對主資料表的查詢，會針對子資料表進行最佳化。

完成上述的步驟後，我們對主資料表的新增、修改、刪除，PostgreSQL會自動地，對相應的子資料表做動作，當我們對資料表做出大資料量的查詢時，PostgreSQL會將查詢改寫，針對子資料表查詢，我們不會感覺到子資料表partition table 的存在。
張貼者： 於 沒有留言:

2010年5月8日 星期六

Partition Table -- (I)

打算深入地談談partition table。
如果曾經開發過一些真實運行的系統，那就一定會碰上一個問題，資料量過大。

舉個例子來說，像是計程車業用的GPS衛星定位系統，每台車，每隔三分鐘就傳回最新的位置，一天就會累積 (60 min / 3 duration) *24 = 480 筆資料，一年就是 175,200筆資料，以200台加盟計程車來算，一年會累積的資料大概就會有3504萬筆資料。

三千五百多萬筆資料，可以想見的，如果要你查查某台車，在某個時間所經過的位置，那對資料庫會是多大的負擔。所以當我們碰上巨量的資料要儲存、查詢時，就需要資料庫支援一種特殊的機制 Patitioning，將巨量的資料分散成為小的資料區塊。

這也就是要談的 PostgreSQL partition table.
張貼者： 於 沒有留言:

2010年5月5日 星期三

PostgreSQL Crosstab -- (VII)

之前資料格式有點雜亂, 利用 group by 整理一下
交叉表三 Version 2
------------------------------------
select currency, purpose, sum(即期)as 即期,sum(現金) as 現金
From
(
SELECT *
FROM crosstab(
  'select currency, purpose, currType, rate From exgRate',
  'Select distinct currType from exgRate' )
  AS t(
       currency char(3),
       purpose varchar(10),
       "即期" numeric(18,6),
       "現金" numeric(18,6)
       ) order by purpose, currency
) dt group by currency, purpose

張貼者： 於 沒有留言:

2010年5月4日 星期二

PostgreSQL Crosstab -- (VI)

交叉表三
－－－－－－－－－－－－－－－－－－－
SELECT *
FROM crosstab(
  'select currency, purpose, currType, rate From exgRate',
  'Select distinct currType from exgRate' )
  AS t(
       currency char(3),
       purpose varchar(10),
       "即期" numeric(18,6),
       "現金" numeric(18,6)
       ) order by purpose, currency;

張貼者： 於 沒有留言:

PostgreSQL Crosstab -- (V)

交叉表二
－－－－－－－－－－－－－－－－－－－

SELECT *
FROM crosstab(
  'select currency, currType, purpose, rate From exgRate',
  'Select distinct purpose from exgRate' )
  AS t(
       currency char(3),
       currType varchar(10),
       "買入" numeric(18,6),
       "賣出" numeric(18,6)
       ) order by currType, currency


    
看起來，資料表還需要進一步的資料整理，不過功能是OK了
張貼者： 於 沒有留言:

PostgreSQL Crosstab -- (IV)

接下來，就是以輸入的基礎資料表，測試crosstab函式的功能。
crosstab有兩道SQL，第一道稱為Source SQL，指定資料的來源；第二道稱為Category SQL，指定crosstab要由那個欄位延伸展開。

在這個例子中，我們將資料依不同幣別，進行展開。

交叉表一
－－－－－－－－－－－－－－－－－－－
SELECT *
FROM crosstab(
  'select currType, purpose, currency, rate From exgRate',
  'Select distinct currency from exgRate Order by currency' )
  AS t(
       currType varchar(10),
       purpose varchar(10),
       AUD numeric(18,6),
       CAD numeric(18,6),
       GBP numeric(18,6),
       HKD numeric(18,6),
       USD numeric(18,6)
       )
 order by currType;

張貼者： 於 沒有留言:

PostgreSQL Crosstab -- (III)

接下來要建個測試資料，我就以之前提到的匯率應用做例子，建個匯率表exgRate

建資料表的SQL如下


Create Table exgRate (
   exgRateId serial primary key, -- 主鍵
   factTime timestamp,    　       -- 匯入時間
   currency char(3),          　　   -- 貨幣別
   purpose varchar(2),   　        -- 匯率種類
   currType varchar(10),     　    -- 貨幣種類
   rate numeric(18,6)         　   -- 匯率
);

插入資料的SQL如下：
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','買入','現金',31.08);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','買入','現金',3.911);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','買入','現金',46.51);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','買入','現金',28.24);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','買入','現金',30.27);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','買入','即期',31.37);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','買入','即期',4.014);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','買入','即期',47.36);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','買入','即期',28.45);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','買入','即期',30.53);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','賣出','現金',31.62);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','賣出','現金',4.087);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','賣出','現金',48.44);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','賣出','現金',28.95);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','賣出','現金',31.08);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','賣出','即期',31.49);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','賣出','即期',4.084);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','賣出','即期',47.86);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','賣出','即期',28.71);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','賣出','即期',30.81);


比較有趣的是這道SQL指令中，用到to_timestamp的函數。因為匯率是不斷更新變動的，所以我們必需記錄匯率輸入的時間（精確到秒），這樣在換匯時，才能知道要用什麼時候的匯率。


張貼者： 於 沒有留言:

PostgreSQL Crosstab -- (II)

PostgreSQL的預設安裝，是沒有啟用crosstab功能的。如果你需要crosstab，你需要

1. 檢查函式庫
檢查一下C:\Program Files\PostgreSQL\8.4\lib目錄中，有沒有名為tablefunc.dll的函式庫檔。

2. 裝載tablefunc功能
開啟　C:\Program Files\PostgreSQL\8.4\share\contrib目錄，找到名為tablefunc.sql的檔案，執行它。

3. 檢查資料庫的函式
如下圖，如果安裝成功，你應該多了11個資料庫的函式
其它平台的安裝，我就不細講了，相信能用其它平台的人，也應該有能力照貓畫虎才是。
張貼者： 於 沒有留言:

PostgreSQL Crosstab -- (I)

我準備用長一點的篇幅來講講Crosstab。Crosstab的中文，在postgresql中一般翻做「交叉表」，而微軟系列一般翻做「樞紐分析表」「樞軸表」，我個人喜歡交叉表。

那什麼是Crosstab呢? 簡單講，如果我現在有一個匯率表如下，
－－－－－－－－－－－－－－－－－－－－－
貨幣種類  匯率種類      AUD  CAD  GBP  HKD  USD
即期      　買入    　      28.45 30.53 47.36 4.014 31.37
即期      　賣出        　  28.71 30.81 47.86 4.084 31.49
現金      　買入          　28.24 30.27 46.51 3.911 31.08
現金      　賣出          　28.95 31.08 48.44 4.087 31.622

我在資料庫中的資料設計是不是就照著看到的做就好？很明顯的答案是NO，舉例來說，如果明天要支援的貨幣多一個日元JPY呢? 那是不是要去alter table 變更資料表的設計??
換一步說，明天，可能人家要的資料會變這樣
－－－－－－－－－－－－－－－－－－－－－
貨幣種類  幣別  買入 賣出
即期      AUD  28.45 28.71
即期      CAD  30.53 30.81
即期      GBP  47.36 47.86
即期      HKD  4.014 4.084
即期      USD  31.37 31.49
現金      AUD  28.24 28.95
現金      CAD  30.27 31.08
現金      GBP  46.51 48.44
現金      HKD  3.911 4.087
現金      USD  31.08 31.622

甚至可能是這樣，同時還要多支援「遠期10天」「遠期30天」，兩個貨幣種類
－－－－－－－－－－－－－－－－－－－－－
匯率種類  幣別  即期 現金    
買入      AUD  28.45 28.24   
買入      CAD  30.53 30.27   
買入      GBP  47.36 46.51   
買入      HKD  4.014 3.911   
買入      USD  31.37 31.08   
賣出      AUD  28.71 28.95   
賣出      CAD  30.81 31.08   
賣出      GBP  47.86 48.44   
賣出      HKD  4.084 4.087   
賣出      USD  31.49 31.622  


哇，又要人仰馬翻去重新整理一次嗎？我們有更聰明的解法，資料庫的設計，應該如下，把所有可能變顯示欄位的資料項，變成一個個欄位，之後再採用Crosstab的方式，去動態產生所有的資料表。
------------------------------------------------------------
幣別     匯率種類 貨幣種類 匯率
 USD     買入     現金    31.08
 HKD     買入     現金    3.911
 GBP     買入    現金    46.51
 AUD     買入    現金    28.24
 CAD     買入    現金    30.27
 USD     買入    即期    31.37
 HKD     買入    即期    4.014
 GBP     買入    即期    47.36
 AUD     買入    即期    28.45
 CAD     買入    即期    30.53
 USD     賣出    現金    31.62
 HKD     賣出    現金    4.087
 GBP     賣出    現金    48.44
 AUD     賣出    現金    28.95
 CAD     賣出    現金    31.08
 USD     賣出    即期    31.49
 HKD     賣出    即期    4.084
 GBP     賣出    即期    47.86
 AUD     賣出    即期    28.71
 CAD     賣出    即期    30.81

這樣子，前面三個表，就是最後這個「資料表」的「交叉表」，PostgreSQL以tablefunc套件，支援交叉表的實作。




張貼者： 於 沒有留言:

資料庫移植性　移植什麼？

有個同行跟我說，他們家的系統移植性很棒，DB2, Oracle, MS SQL每個資料庫都可以搭

我心裡在說，那是你沒碰上要一秒內要處理上百萬上千萬筆資料的狀況，沒碰上那種連IBM大暴龍都搞不定的運算，等再過五年，你再來說說這話。

實際上，你就是會碰上這種操資料庫，就是會碰上需要那種需要某牌資料庫特異功能的應用，碰上這種東西，資料庫移植性，就變成一句笑話。

讓我們面對現實吧，鳥托邦並不存在，我們不能保證完美的資料庫移植性。我們應該要考慮的是，我的系統要支援幾個資料庫?　為什麼? 通常，考慮的因素是


一、商業的需求
客戶已經講的很清楚，他們家就是用DB2 Oracle，維護人員受的訓也都是DB2, Oracle。如果你不用，那最好你們家的系統強悍無比，Domain knowledge比客戶還精，客戶不找你不行，要不然，接不到案子不要怪別人。

二、功能的需求
如果客戶熟悉的資料庫，功能不符合你系統的需求。像是你就是需要某牌資料庫的特異功能，才能把他要的功能或報表跑出來，那你可以堅持，要不然，還是別太鐵齒。

三、預算的需求
這就更簡單了，付錢的是老大，老大要什麼，我們就給什麼。如果老大沒錢，那更簡單，我們就是老大，就用PostgreSQL吧，畢竟系統原生就是在PostgreSQL環境發展的。

資料庫移植性，這其實是一個角力的問題，其實終端客戶才不管你用什麼資料庫，只是有時候你需要去符合客戶IT部門的某些政策，某些規定。碰上這種東西，我都是叫業務先去談上線，再談移植計劃。先把系統裝進去，符合客戶的需求，終端客戶用上手了，我們再來談資料庫移植。

而在大多的的狀況下，移植資料庫要花時間改程式，要花時間測試，更重要的，是終端客戶也要花時間確認沒有問題，客戶會願意花時間去做這些沒用的功嗎？有這些時間，不如幫他多寫一隻新功能，滿足他一個新需求，資料庫移植這件事，自然就無疾而終了。

但是，這種作法，有時候也會碰上鐵板，特別是在應用很成熟的領域裡。在月度工作會議裡，終端客戶的部門沒有新需求提出，IT部門就會把這件事拉出來講。我的作法是，就配合嘛，反正這領域沒有新功能好寫了，那就排進度做移植，擴充支援的資料庫，也是增強這個產品線的競爭力，要不然，客戶簽維護是讓我們大眼看小眼嗎? 總要有個交待吧，是吧 !!
張貼者： 於 沒有留言:

PostgreSQL 9.0出來了, So What ??

PostgreSQL 9.0出來了, 那又怎麼樣 ??


最近PostgreSQL 9.0出來了，大家都十分的興奮，看了看，零零總總共２００個修改，其中有許多是許多人想很久的，像是64位元視窗平台的支援，支援Python 3等。

不過，就我個人而言，我想說的是「那又怎樣？」

我開發的平台是Java，配合Hibernate建立的ORM映射層，比較關心的是後端PL/pgSQL的功能有沒有新發展。9.0 的出現，並沒有什麼殺手級的新功能，讓我一定要馬上昇級到9.0，也就是說9.0並沒有大幅改變我的工作。

相反的，9.0 為我帶來不必要的昇級負擔。年輕的時候，一有新版本出來，總會想要馬上昇級到最新版本，但是，年紀漸長後，卻出現不同的思考。

對我來說，發展的是ERP進銷存之類的商用軟體，如果不是在開發功能時需要新版的新功能，我為什麼要昇級？客戶端已佈署好、運作正常的系統，為什麼要昇級? 為了爽嗎？不必吧!! 就算客戶的IT部門提到，我總會問，為什麼要昇級，是現在的功能不符需求，需要新版本的新功能嗎？客戶買的是一個系統，不是買一個資料庫引擎。如果是系統的需求，客戶不提，我也會主動去幫客戶昇級，如果不是，昇級過程發生的風險，是我負責，還是客戶自己扛？

狀況很明顯，昇級是要走的，但是什麼時候走，必需由我決定。

根據我的經驗，新版本剛出現的時候，絕對不是該昇級的時候。我會等到9.2, 9.4, 9.6，甚至是10.0出現的時候，再針對9.X系列，挑一個穩定的平台出來做移植，並進行所有功能的測試。至於現在，並不是我會考慮昇級的時間，再等一兩年，這一兩年，好好測測新功能，才是該做的事情。

絕對..絕對..不要趕流行，可以玩玩新功能，可以利用新功能，想想系統可以加什麼賣點，但是絕對不要為了昇級而昇級。這是一個老工程師的肺腑之言。
張貼者： 於 沒有留言:
訂閱： 文章 (Atom)