PostgreSQL 影片

Loading...
Google

2007-08-31

Windows下安装Rails的PostgreSQL驱动

更新:2007- 09-01
內容:
Ruby下有两种PostgreSQL驱动:

第一种,原生Ruby驱动,纯Ruby编写
Windows下安装:
>gem install postgres-pr
或者直接从http://rubyforge.org/frs/download.php/3858/postgres-pr-0.4.0.gem下载,目前最新版本为0.4.0。下载完成之后执行
>gem install postgres-pr-0.4.0.gem

第二种,基于libpq C库实现,性能更好一些。
Windows下安装:
http://rubyforge.org/frs/download.php/9554/ruby-postgres-0.7.1.2006.04.06-mswin32.gem下载,目前最新版本为0.7.1。下载完成之后执行
>gem install ruby-postgres-0.7.1.2006.04.06-mswin32.gem
安装完成后,将PostgreSQL安装目录下的bin目录路径添加到Windows环境变量path中。或者将bin目录下的所有dll文件拷贝到Ruby安装目录的bin目录下。

配置:

修改 config/database.yaml文件
删除所有内容并添加以下内容


development:
adapter: postgresql
host: 127.0.0.1
port: 5432
username: test
password: test
database: test
encoding: UTF-8
test:
adapter: postgresql
host: 127.0.0.1
port: 5432
username: test
password: test
database: test
encoding: UTF-8
production:
adapter: postgresql
host: 127.0.0.1
port: 5432
username: test
password: test
database: test
encoding: UTF-8



其他可选参数为:


allow_concurrency: false
schema_search_path: myapp,sharedapp,public
schema_order: myapp,sharedapp,public
min_messages: NOTICE


这些参数中
adapter参数必须指定为 postgresql (如果使用PostgreSQL数据库的话)
host为数据库服务器所在主机,默认为localhost
port为数据库服务器所监听的端口号,默认为 5432,
username为登录用户名,默认为空
password为登录密码,默认为空
database为连接的数据库名称,也必须指定。

encoding为客户端编码,默认为该数据库的默认客户端编码,,一般情况下建议和页面编码一致,推荐使用UTF-8,驱动通过执行SET client_encoding TO 语句来设置。

allow_concurrency默认为false,表示是否允许并发访问,从驱动的代码来看,postgres-pr并未实现并发访问,所以,当使用postgres-pr驱动时,只能设置为false或者不设置该参数。而基于libpq C库实现的ruby-postgres支持该参数,可已设置为true,不过,建议不设置该参数。

schema_search_path和schema_order等效,为schema搜索顺序。
从activerecord中的代码来看:
pga.schema_search_path = config[:schema_search_path] ll config[:schema_order]
会优先使用schema_search_path配置,如果schema_search_path没有设置,则使用schema_order参数,如果二者都没有设定,则使用当前数据库默认schema_search_path

min_messages 为客户端信息的详细程度,可设定为 DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL, 和PANIC。越靠后的数值细节越少,默认值为NOTICE。 驱动通过执行 SET client_min_messages TO 语句来设置。

测试一下,假定Rails程序为MyApp
C:\workspace\MyApp>ruby script/console
>> Loading development environment.
>> ActiveRecord::Base.find_by_sql "SELECT * FROM tests "
>> …

更多设置,请参考http://wiki.rubyonrails.com/rails/pages/PostgreSQL
客户端信息的详细程度 ,请参考http://www.pgsqldb.org/pgsqldoc-8.1c/runtime-config-logging.html

PostgreSQL 觸發器(Trigger) 入門

Trigger - 觸發器,與交易資料鎖定一樣,是商用資料庫系統不可或缺的重要功能之一。你可曾要插入資料到資料庫的時候,同時又需要連帶更新其他表單的資料?在過去,你一定也是在前端應用程式,使用 INSERT INTO 然後再用 UPDATE,或是用一句句的 INSERT 命令來完成這任務。許多諸如此類的同步操作,假若都在資料庫系統外一步步下達命令完成,不僅是程式設計上繁複,效能上也不彰。觸發器的功能就是為了解決這類問題而設計的,當你更新或查詢某個資料表時會觸動觸發器,觸發器就會照您所設計的流程,同步去插入、更新、刪除其他資料,你不再需要重復下達多次的 SQL命令就能達成一連串資料的同步處理。


深入了解觸發器函數之前,如果能有個情境會比較好了解其中運作,你可以先想像一下有兩個資料表 mytable 和 mytable_counter 還有其中的各項欄位如下:


  1. mytable - 客戶清單
    • mytable_id - 新增客戶的月份
  2. mytable_counter - 每月份的客戶數量統計表
    • mc_mytable_id - 統計的月份
    • mc_count - 當月客戶總數


如果現在要設計一個機制統計每月客戶數量,你會怎麼做呢?以往傳統做法應該都是在 INSERT INTO mytable 之後,再 UPDATE mytable_counter。可是今天我們要把 UPDATE 的動作寫成觸發器函數,讓日後只要我們 INSERT 資料進去 mytable,PostgreSQL就會自動同步幫我們去 UPDATE mytable_counter 的 mc_count欄位,不需要我們再另外下達 UPDATE 的指令。


以下是一個簡單的觸發器函數例子:

CREATE OR REPLACE FUNCTION pg_mytrigger()
RETURNS "trigger" AS
$BODY$BEGIN
UPDATE mytable_counter SET mc_count = (mc_count + 1) WHERE mc_
mytable_id = NEW.mytable_id;
RETURN NEW;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION
pg_mytrigger() OWNER TO somebody;

其中要注意的,NEW.mytable_id 代表新增加到 mytable 的資料列,其 mytable_id 欄位。在這例子中用來找到當月的統計紀錄,並予以統計數量加一。


套用觸發器函數

到此,觸發器函數到這階段已經建立好了,下一步要決定套用在哪一個資料表,也就是我們要設定哪一個資料表被 INSERT 時,PostgreSQL會去更新統計表的記錄。我們可以用以下指令套用觸發器函數到目標資料表上:

CREATE TRIGGER pg_mytrigger
BEFORE INSERT
ON mytable
FOR EACH ROW
EXECUTE PROCEDURE pg_mytrigger();

這 段命令需要解釋的是 BEFORE 這參數,觸發器雖說是『同時』照你的流程執行,但實際上是分為 BEFORE 和 AFTER,差別在於 BEFORE 是在『更新、查詢資料表之前』執行觸發器預訂的函數,而 AFTER 是在『之後』執行。也就是 BEFORE是先執行觸發器再新增或刪除客戶資料,AFTER是先新增、刪除資料再去執行觸發器。


後記

上 面的例子只是為了說明觸發器 (Trigger) 的功用和使用方法,已經簡化了很多內容,實際上還有許多需要考慮的地方,例如當統計資料表找不到當月的統計資料怎麼辦﹝因為該客戶可能是當月第一個客戶 ﹞?所以必須還要修改觸發器函數去判斷修正這一部份。另外一方面,目前只談到 INSERT 時執行觸發器函數,我們也可以在 DELETE 時執行觸發器函數為統計數量減一,這些都是觸發器可以做得到的,以後再花時間進一步討論。

2007-08-30

Stored Procedure 實例:資料分頁

資料分頁的功能常見於 Web 應用程式,許多人應該對這功能的程式撰寫不陌生,本文嘗試利用 Stored Procedure 的方式完成這工作,我們將在 PostgreSQL 內部建立一個 Function 做 SELECT 並預先處理分頁,最後再輸出給前端程式
PostgreSQL 資料分頁 Stored Procedure 實作例子:
CREATE OR REPLACE FUNCTION mytable_list(ps bigint, page bigint)
RETURNS SETOF mytable AS
$BODY$DECLARE
rs mytable;
BEGIN
FOR rs IN select * from mytable LIMIT ps OFFSET ps*(page-1) LOOP
RETURN NEXT rs;
END LOOP;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION
mytable_list(ps bigint, page bigint) OWNER TO somebody;
引數說明:
ps - 設定每一頁的資料列數量
page - 指定目前讀取的頁碼

延伸閱讀(Link):
PostgreSQL 的 Stored Procedure 簡易實例

2007-08-29

PostgreSQL 的 Stored Procedure 簡易實例

增進 SQL 處理效率的一個很重要的方法就是使用 Stored Procedure ,一般商用級以上的資料庫系統都會內建這種功能。

以下是在 PostgreSQL 中建立一個 Stored Procedure 的最簡單例子:

-- 執行結果:執行 select * from mytable where table_id = id 並回傳所有符合的資料列

CREATE OR REPLACE FUNCTION pg_myfunc(id integer)
RETURNS SETOF mytable AS
$BODY$DECLARE
rs RECORD;
BEGIN
FOR rs IN select * from mytable where table_id = id LOOP
RETURN NEXT rs;
END LOOP;
END;$BODY$


LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION pg_myfunc(id integer) OWNER TO somebody;

如同 Oracle PL/SQL一般,PostgreSQL也有自己的程序語言 PL/pgSQL,其中最常用到的幾種語法使用:

IF 判斷式

IF ... THEN

ELSIF ... THEN

ELSE

ENDIF;

FOR LOOP迴圈

FOR i IN 1 .. 10 LOOP
...
END LOOP;

當找不到任何符合的資料列時

SELECT * FROM mytable WHERE mytable_id = id;
IF not found THEN
-- 找不到任何符合的資料列時執行這裡的程序

END IF;

全部的 Function 都建立完成了,接著就要開始使用已建立的 Stored Procedure。

執行 Functions 有兩種SQL命令可以使用:

Function 沒有回傳值時使用 SELECT pg_myfunc();

Function 回傳值時使用 SELECT * FROM pg_myfunc();

2007-08-26

為什麼 PostgreSQL 足以替代 MySQL

更新:2007-08-28(Update:3)
2007-08-26 (感謝本文的熱心朋友回應)
對映章節:
http://developer.postgresql.org/index.php/Why_PostgreSQL_instead_of_MySQL

內容:
以下內容來自 PostgreSQL 國際官網上的wiki的翻譯內容

12 顆銀彈: PostgreSQL 打擊 MySQL

  1. 一個完全整合進核心(內核, core)的資料庫引擎

  2. 在全部環境之下都擁有健全的 ACID 事務交易(transactional)行為
    (Wiki::ACID)

  3. BSD 授權協議明白地證明它是開放源碼(open-source softwares)

  4. 目標就是一定要與 SQL:2003 相容
    (Wiki::SQL:2003)

  5. 開發者對於程式碼的品質,正確性及測試有著相當的狂熱

  6. 未定義或不支持的操作一定不會一聲不響地就造成錯誤
    (即一定會給出適當的錯誤信息,而不是毫無提示地操作失敗)。

  7. MVCC讓你的資料永遠保有一致性
    同作控制(Concurrency Control) - 簡介

  8. 複雜的 Join (結合)會盡可能自動地處理

  9. 就是被設計來給必須承受大量連續性(讀/寫)資料的使用者

  10. 事務交易式(Transactional) DDL 讓你安全地修改資料庫

  11. 有多種成熟的伺服器端程式設計選擇

  12. 從使用者到核心團隊都是最棒的社群支持者
延伸閱讀(Link):
Why PostgreSQL Instead of MySQL: Comparing Reliability and Speed in 2007.
Transactional DDL in PostgreSQL: A Competitive Analysis.

2007-08-25

Oracle 完全相容的資料庫公司 - EnterpriseDB

更新:2007-08-25
對映章節:

內容:
最近位於 EnterpriseDB 公司首頁的二張大大的主封面圖,
也許看完後能更鼓舞您學習與遷移使用 PostgreSQL 的信心...
也間接給了您提供了一家國際性 PostgreSQL 商業付費型的 24*7 的服務公司
(很多台灣的大型公司就是要看到"收費"二個字才肯放心使用...)
總資訊購買成本卻不到原使用 Oracle 的 1/3 ...
節省下來的2/3拿來訓練和"養"PostgreSQL的高級工程師(人力資產)
至少可以達 5年以上, 產生 5-1x人次之多(TW計價)
這樣的人力投資報酬率如果您是位好的 CIO ...?!

(EnterpriseDB 與 Oracle 完全相容的資料庫公司)


且 EnterpriseDB 公司也開始進入了台灣市場
(阿益在上個月也接到了從香港 EnterpriseDB公司打來的電話
內容大至是 EnterpriseDB 要派工程師到台北
與可能成為客戶們進行直接的技術座談...)

(在原 Oracle 應用程序可不變更重寫執行在 EnterpriseDB - 約 80%)


另外在 EnterpriseDB 公司也在其自家網站中也說明了
為何 Sony Online Entertainment(Sony線上娛樂公司)
從 Oracle 遷移到 EnterpriseED的理由與過程...

延伸閱讀(Link):
EnterpriseDB 獲選為Linux商業應用大獎-最佳企業級資料庫
由 EnterpriseDB 論數據庫開源模式

2007-08-23

Ubuntu 7.0+ 初學 PostgreSQL 者注意事項

更新:2007-11-02 (Update:01)
對映章節:

內容:
阿益最近在實體程課中將 Debian/UbuntuPostgreSQL
當成 LPIC 與 SCJP 班的正式必要教材...
Ubuntu 比 Debian 多了幾項使用者上安全性的控管機制
原設計是美意, 卻可能造成對初學 PostgreSQL 者上的不解...

這點讓阿益在經歷二梯次的 LPIC 教學後,
認為還是有必要寫篇給初學者參考,
必竟讓 PostgreSQL 親近於使用者是有必要的!

  1. 已由 APT 安裝後, 對於 PostgreSQL Server
    停用系統開機後即長駐, 改由手動載入者, 可到

    [系統] -> [系統管理] -> [服務]

    中取消選取 "PostgreSQL 資料庫伺服器".
    日後手動的啟停用經由終端機方式進行.

    $sudo /etc/init.d/postgresql-8.2 [start/stop/restart/...]


  2. 使用PostgreSQL Client 終端機命令: psql
    進行 PostgreSQL 終端機服務時, 出現類似以下情況之一:

    psql: 嚴重錯誤: Ident 驗證使用者"postgres" 失敗
    psql: 嚴重錯誤: 資料庫"up"不存在
    psql: 嚴重錯誤: role "up" does not exist

    主要原因為受到 PostgreSQL 中預設僅存在 postgres 用戶,
    受制於 pg_hba.conf 中對 Local 採用 Unix Sockets Ident
    驗正用戶身份(可自行調變驗正方式), 對於初學者最快上手的方式,
    為先轉為 postgres 用戶
    $sudo su postgres
    後,使用命令分別建立新使用者與新使用者同名的資料庫
    $createuser [您的用戶名]
    $createdb [您的用戶名]

    來創建您在 PostgreSQL 中的帳號,
    並自動易於使用同用戶名的預設資料庫.

  3. 使用 APT 安裝 PostgreSQL 後, 會自動幫您建構
    postgres 用戶與其專屬的 Home 目錄.
    切換成 postgres 用戶在 Ubuntu 必須擁有 sudo
    #sudo su postgres
    專屬的 Home 目錄與DB存放
    (限定專屬 postgres 700 rwx --- ---)
    /var/lib/postgresql/[version]/

  4. 預設安全性情況下, PostgreSQL 不接受遠端 TCP 連線
    (PostgreSQL 組態檔全集中在 /etc/postgresql/[version]/main/*)
    必須手動修改
    postgresql.conf (PostgreSQL系統組態文件)
    #listen_addresses='localhost' =>
    listen_addresses='*'
    *表示 All 後重啟 PostgreSQL Server.

    並配合適當修改
    pg_hba.conf (PostgreSQL 主機基礎的存取權限文件)
    中您需要的條件與來源 IP 來放寬安全性限制.
    格式:
    # TYPE DATABASE USER CIDR-ADDRESS METHOD
    host all all 0.0.0.0 0.0.0.0 md5
    #這行表示為接受以 host 型態來源IP為任意的連線
    到本地任一資料庫及任一使用者均可接受連線, 但認證方式採用 md5.

延伸閱讀(Link):

2007-08-22

Ubuntu 7.04 上安裝 pgAdmin III 的注意事項

更新:2007-08-23(UPDATE:1)
2007-06-04
對映章節:

內容:
阿益多年來都使用著文字介面的 GNU/Debian Linux,
X-Windwos最多也僅被阿益當成測試和練習了解 X 的運作原理用,
最近 Debian 的姐妹作 Ubuntu 推出的 Desktop 版,
讓阿益深感就是要這樣"平易近人"的安裝和操作方式才行讓一般使用者接受,
且易於取代 XP.



在 Ubuntu 7.04 版上欲安裝 pgAdmin III GUI 的管理介面工具,
除了可使用文字終端機與命令 apt-get 進行套件安裝外,
亦可使用 [系統] -> [管理] -> [Synaptic套件管理員],
利用 "搜尋" pgadmin 即會找到 pgAdmin 1.4.x 版,
但當前的 pgAdmin III 版本為 1.6+,
使得您即使安裝了 Ubuntu 儲倉庫裡的 1.4 版,
仍導致您無法確保能完全透過 GUI 來管理 PostgreSQL.

這時我們必須安照 pgAdmin Project 的建議方式, 如下:
原文: http://www.pgadmin.org/download/ubuntu.php

pgAdmin III 套件是有效在 Ubuntu feisty 發行版本中被使用.

  • 在安裝之前您必須取得被簽署的 pgAdmin III GPG Key.
    這個動作用來確保您下載的 pgAdmin III packages 是
    正確且安全性受到檢驗合格(類似md5的sum check)
    當然, 進行這動作要確保您擁有root權限:

  • APT 貯藏庫供給簡化安裝方式. 在你開始安裝之前,
    請撰擇從 PostgreSQL 映像站台(mirrors) 清單中的一個映像站點.
    點擊一下國旗標, 並且複製網址 URL 並重新轉向網址.
    http://www.postgresql.org/download/mirrors-ftp

  • 在每個映像站, 這個套件的位置在:
    /pgadmin3/release/ubuntu
    之後, 編輯 /etc/apt/sources.list 文檔並增加以下的行列:
    deb [MIRROR URL]/pgadmin3/release/ubuntu feisty pgadmin

    範例, 給台灣用戶, 您能用:
deb http://ftp3.tw.postgresql.org/pub/Unix/Database/postgresql/pgadmin3/release/ubuntu feisty pgadmin
  • 簡單的使用 Synaptic 來安裝 pgAdmin III 同樣的您將能附加任何其它套件或執行以下指今從指令提示符中:
    sudo apt-get update

    sudo apt-get install pgadmin3

這樣您就能正常使用最新版本的 pgAdmin III 1.6+ 並且等待迎接 1.8 版的到來.

延伸閱讀(Link):
http://www.ubuntu.com/

2007-08-20

Ruby on Rails - PostgreSQL for Debian/Ubuntu 安裝

更新:2007-08-20
對映章節:

內容:
這是篇基礎的安裝教學, 總是有學生問到,
阿益整理分享給需要的朋友們:

GNU/Debian & Ubuntu 是個很棒的自由系統,
安裝的方式雷同, 唯一必需留意的是
Ubuntu 必須是 root 來進行以下的工作過程 (sudo)
APT: aptitude 亦可替換成其它套件管理程式, 例如 apt-get

  1. Ruby
    早已經進了Debian/Ubuntu的套件庫中
    細節: http://packages.debian.org/testing/interpreters/ruby1.8
    所以基本上安裝很簡易, 只要執行 APT 工具即可完成:
    #aptitude install ruby1.8
    相依性自動也幫你安裝了 irb1.8, rdoc1.8

  2. RubyGems
    是 Ruby 專屬的套件管理程式, 同 Ruby 也在官方套件庫裡了
    細節: http://packages.debian.org/testing/interpreters/rubygems
    安裝方式也同Ruby, 且是目前最新的版本:
    #aptitude install rubygems
    rubygems 相依性依賴著 ruby1.8, 安裝後會即有 gem 命令工具存在.

  3. Rails
    是 Ruby MVC網頁開發快速建構的強項,
    必須使用RubyGems套件管理程式來進行安裝與後期的更新管理,
    使用 gem 命令來進行:
    #gem install -y rails
    參數 -y 亦可改寫成 --include-dependencies 意同自動安裝相依性套件,
    安裝後即可使用 rails 命令來創建 rails project.

  4. PostgreSQL for Ruby
    這點可能不同與其它平台的方式, 在其它的OS必須使用
    #gem install ruby-postgres 或者 #gem install postgres
    (二者結果相同)來進行安裝 PostgreSQL libpg C 函數庫的連結.
    但在 GNU/Debian & Ubuntu 更可以輕易的使用如下方式安裝:
    #aptitude install libpgsql-ruby
    效果同等上述其它OS的操作方式.
    細節: http://packages.debian.org/unstable/interpreters/libpgsql-ruby
    細節: http://packages.qa.debian.org/libp/libpgsql-ruby.html

  5. 測試 PostgreSQL for Ruby 的有效性
    irb 命令是 Ruby 互動式的命令列環境, 透過它可快速測試 RoR
    # irb
    irb(main):001:0> require 'postgres'
    => true
    irb(main):002:0> exit

延伸閱讀(Link):

2007-08-02

MySQL 的私有化掌控(摘自:Linuxpilot VolL59)

更新:2007-08-02
對映章節:
Linuxpilot 國際中文版 Vol.59 關於 PostgreSQL 文摘(二)
軟體 人性 見聞錄 (阿益心得文摘)

內容:
這是這篇文章中的說明(阿益只是貼上來哦)

MySQL, 就是真正有目的, 有原因的私有化,
所有的原程式碼都保證只由少數人編寫,
並經領導者控制, 預留空間可作為隨時更改授權的後著....

這段話也許可以讓您體會背後的優缺點...
留下伏筆給大家自行思考更嚴重的問題...


延伸閱讀(Link):
Linuxpilot 國際中文版 Vol.59

Linuxpilot 國際中文版 Vol.59 關於 PostgreSQL 文摘(一)

更新:2007-08-02
內容:
阿益每個月固定會取得Linuxpilot國際中文版(雜誌月刊)

2007-AUG Vol.59
http://www.linuxpilot.net/
當然要盡能力分享其中
有關 PostgreSQL 的部份文摘分享給大家...

令人感動的是 PostgreSQL 商業化公司
EnterpriseDB 在本期雜誌中成為主角了
(隨書附送 EnterpriseDB 8.2 CD)

眼看著它從美洲市場開始走入亞洲市場與國際化
正式對 Oracle 和其它 Database 進行商業市場的角逐...



專章介紹 EnterpriseDB 8.2 的特點(其實也等同是 PostgreSQL 8.2 的特點)

更看到連EnterpriseDB的產品代理商已出現在香港和台灣二地了
這是阿益所樂見的, 這代表 PostgreSQL 已獲得企業界的肯定
大大的 EnterpriseDB 行銷標題:

IT 巨鯊噬去了龐大的資料庫成本, 效能與功能卻絲毫不減.

(謎之章:那換成 PostgreSQL... IT 的巨象踏扁了龐大的資料庫成本...)
EnterpriseDB是關聯式資料庫管理系統(RDBMS), 非常適合高承載, 任
務導向的應用程式, 絕大部分為 Oracle 資料庫所撰寫的應用程式都可以
直接執行, 無需修改程式碼.
價格只有Oracle的 1/6 但效能卻可以直上 Oracle 的 400%+.


延伸閱讀(Link):
http://www.linuxpilot.net/

PostgreSQL & Google-Analytics Running...

::Planet PostgreSQL::

PostgreSQL Information Page

PostgreSQL日記(日本 石井達夫先生Blog)

PostgreSQL News

黑喵的家 - 資料庫相關

Google 網上論壇
PostgreSQL 8 DBA 專業指南中文版
書籍內容討論與更多下載區(造訪此群組)
目錄下載: PostgreSQL_8 _DBA_Index_zh_TW.pdf (更新:2007-05-18)

全球訪客分佈圖(Google)

全球訪客分佈圖(Google)