2007-12-27

SQL語法 建立資料庫(CREATE DATABASE)

以下是利用SQL語法建立資料庫的方法:
語法:Create database 名稱

此圖是說,先點選資料庫下方隨意一個資料庫之後上方的SQL查詢會隨著亮起來,就可以點選進去輸入我們要建立資料庫的語法,則語法為 Create database 資料庫名稱 ,這樣就可以了,大小寫都沒關係^^最後在按上面的執行查詢就可以了!!!!!

接下來可以關閉這個SQL查詢視窗,則系統會問要不要儲存這些變更,其實沒有關係的!!可以按否~~~如果你的SQL語法不熟可以保留,日後可以拿出來觀看......


此圖是說,輸入完語法之後,將資料庫的目錄結合起來之後,則在上面按重新整理,最後會發現我們的資料庫就可以建立完成了~~~

2007-12-14

如何建立資料表

資料庫簡單來講一定要先有一份資料表才能做出以下的查詢或表單...跟隨著這個簡單的觀念之後,我們接下來可以來試試看如何建立一份資料表....

首先連上我們的伺服器之後呢....我們只會看到 資料庫、表空間、群組角色、登入角色 這些目錄...
我們可以先展開資料庫的目錄,也只會看見系統幫我們預設好的一個資料庫叫 postgres,呵呵...不要管它!!!先教大家如何建立一個自己的資料庫吧...我們可以選取資料庫的目錄接下來利用上面的 編輯--新物件,就可以有一個 新資料庫 如圖:

接下來我們在做這樣的設定就可以了.......給予資料庫名稱及擁有人!!!

接下來則會看見我們新增了一個自己的資料庫嘍^^....我們展開這個目錄吧.....

可以藉由上方所看到的....這個就是建立我們第一筆資料表的方法唷^^
那麼可以藉由以下的圖片我們來建立這個資料表的結構吧......

利用欄位來依序新增其架構.......(資料型別要熟記唷!!!!在此不多說了)

接下來我們可以新增一個PRIMARY KEY,此時可以利用 約束 選項!!!!!將裡面的屬性及欄位 設定好唷^^

接下來就可以輸入資料了......^^

2007-12-12

windows上安裝postgreSQL



我如何在postgreSQL建立我第一筆的資料表勒mailto:~~~<@$@#@%$$@#@$#$@$大致上先講講我在windows上面的安裝過程~~其實不需要注意太多呢!!! http://www.postgresql.org/ftp/binary/v8.2.5/win32/ 可以從這邊下載postgreSQL 8.2.5版 或是http://postgresql-chinese.blogspot.com/2007/03/postgresql_01.html 都可以找的到!!!!!


開始安裝嘍~~~
基本上我看到這個畫面是直接點選Next.....



基本上我想下面的密碼是一定要設至的吧^^...這是在開啟資料庫所設的密碼,port為5432,其餘按照預設就好了。
接下來還會看到一個很多很多的選取清單的方格的選項~~~嗯@@??我看....還是給它預設就好了!!然後...就一直安裝到結束嘍!!!!!!!!!
接下來,第一個安裝檔就建至完成嘍!!!然後緊接著就可以安裝第二個安裝檔嘍!!^^....一樣的方法至到結束就好了!!!!!!!!!!!
安裝完畢後,就可以在所有程式這邊看到postSQL8.2的目錄了.....基本上選取 pgAdminIII來玩我們的資料庫吧..............Go!!!!!!!!!!!!!!

2007-11-02

PostgreSQL VS MySQL 效能的新比較(FreeBSD 7)

更新:2007-11-02
對映章節:
Introducing FreeBSD 7.0 (PDF 檔)

內容:
這是來自 FreeBSD 的效能報告圖...











延伸閱讀(Link):

Ubuntu 使用 Debian 第三方套件庫安裝 pgAdmin III 1.8.0

更新:2007-11-02
對映章節:

內容:
我們曾在 2007-08 時提到
Ubuntu 7.04 上安裝 pgAdmin III 的注意事項,
在今時 Ubuntu 7.10 也在上個月發佈了,
pgAdmin 也從 1.6 正式踏入 1.8 版 ,
PostgreSQL 也即將正式進入 8.3 時代...

pgAdmin 在 Ubuntu 官方套件庫一直停在 1.4.x版,
這使得我們必需以安裝第三方套件的方式來進行安裝 pgadmin 1.8,
目前 PostgreSQL 僅對 GNU/Debian Etch(Stable) 提供編譯好的 deb,
所以我們必需加入這套件庫來源
格式:
deb [MIRROR URL]/pgadmin3/release/debian etch pgadmin
或採用如下 APT 語段加入到您的 /etc/apt/sources.list:

deb http://ftp4.tw.postgresql.org/pgadmin3/release/debian/ etch pgadmin

(採用 Ubuntu Synaptc套件管理員介面加入)


另必需加入 pgAdmin 套件認證 key (終端機操作)

$wget -q -O - http://www.pgadmin.org/pgp/archive_key_debian_ubuntu.gpg | sudo apt-key add -


(完成上述動作後, 即可在您套件庫中找到最新的 1.8 版)


安裝後即可在
[應用程式] -> [系統工具] -> pgAdmin III 啟動

延伸閱讀(Link):

2007-10-26

pgAdmin III 1.8正式版釋出與Windowse版本安裝教學

更新:2007-10-26
對映章節:
pgAdmin 新聞頁面
http://www.pgadmin.org/news.php

內容:
pgAdmin 開發團隊正式宣告發佈 pgAdmin 1.8.0,
這是開源碼(Open Source)圖形化 PostgreSQL 管理者工具組,
可安裝於 Windows, Linux, FreeBSD, Mac OS X 與 Solaris,
現在您可從下列來取得原始碼與多樣平台的二進制安裝包:

http://www.pgadmin.org/download/

本次的版本追加了十多項重大的功能, 超過30項的原有功能的改進,
及部份的 bug 修正, 最重的是有超過 2,100個中文字串的
pgAdmin 完整介面的翻譯量.
完整的說明: http://www.pgadmin.org/news.php

(pgAdmin III 的啟動畫面)

(100% 的中文用戶管理介面)


Windwos版本的安裝教學:

從官方站點取得 win32 下的 pgadmin3-1.8.0.zip,
解開壓縮檔, pgadmin3.msi 為安裝程式,
upgrade.bat為提供給原已安裝舊版的用戶進行用.


(開始進行安裝的畫面)


(請勾選接受授權協議, 再進行下一步)


(安裝的功能組件, 亦可進行變更安裝目錄)


(完成安裝)


延伸閱讀(Link):

2007-10-09

postgresql-8.3-beta1 版释出

说明
http://www.postgresql.org/about/news.872

该版本增加了很多客户要求的改进,包括:

Full text search is now a built-in feature
全文检索成为内建特性。

Support for the SQL/XML standard, including a new xml builtin data type
支持SQL/XML标准,包括新的xml内建数据类型

enum data types
枚举数据类型

UUID data type, similar to that defined by RFC 4122
UUID数据类型,与RFC4122中定义的相同

Arrays of composite types
组合类型数组

ORDER BY ... NULLS FIRST/LAST


Updatable cursors (UPDATE/DELETE WHERE CURRENT OF cursor_name)
可更新游标(UPDATE/DELETE WHERE CURRENT OF cursor_name)

Per-function parameter settings
每函数参数设定

User-defined types can now have type modifiers (parameters)
用户定义类型现在可以变更类型(参数)。

Declarations such as varchar(42) are no longer restricted to use by built-in data types.
类似varchar(42)的声明不再被内建数据类型限制使用。 (这句不是很明白)

Automatic plan invalidation when table definitions change
当表定义改变时,自动规划失效

This will particularly ease usage of temporary tables in PL/PgSQL functions.
在PL/PgSQL函数中临时表更加易用。

Numerous improvements in logging and statistics collection capabilities, including the ability to emit postmaster log messages in CSV format that can be directly loaded into a database table for analysis
诸多日志和数据统计收集能力方面的改进。包括以CSV格式输出postmaster日志消息的能力。该CSV文件可以之间导入到一个数据表中来进行分析。

SSPI/GSSAPI authentication support
支持SSPI/GSSAPI 认证。

Multiple autovacuum worker processes, and other autovacuum improvements
支持多自动清理(Autovacuum )工作进程,其他的自动清理(Autovacuum )相关改进

Autovacuum is now considered mature enough to be enabled by default.
自动清理(Autovacuum )现在已经足够成熟,默认启用。

The entire PostgreSQL system can now be compiled with Microsoft Visual C++
整个PostgreSQL 系统现在可以使用Microsoft Visual C++ 来编译。

Major performance improvements in this release include:
这个版本包括的主要性能改进:

Asynchronous commit option to allow transactions to be reported committed before they have actually been flushed to disk
异步提交选项 允许事务在实际写入到磁盘之前报告 已提交。(这将提高系统的并行性能,但是最坏的情况下,系统崩溃之后,最近的一些已报告提交的事务将没有产生实际的影响)

"Distributed" checkpoints to spread out the I/O load of a checkpoint
"分布" 检查点将分散一个检查点的I/O负荷。

Heap-Only Tuples (HOT) to reduce overhead of updates
Heap-Only Tuples(HOT)将降低更新的代价。(这个从早先的一些说明来看,性能提升不少)

Just-in-time background writer strategy to improve disk write efficiency
即时后端写策略将改善磁盘写效率。


Reduction of on-disk data size through reducing both per-tuple and per-field overheads
通过降低每个元组和每隔字段的代价(大小)来降低磁盘数据大小。

Efficiency improvements for large sequential scans, including prevention of cache flushing and "piggybacking" to let concurrent scans read the table only once
有效的改善了大的线性扫描,改进包括防止缓存刷新 和 "捎带技术"-让并发的扫描只读取数据表一次。

Top-N sorting
Top-N 排序


Lazy XID assignment to reduce the cost of read-only transactions
延迟XID赋值 将降低只读事务的代价。


下载地址
http://www.postgresql.org/ftp/binary/v8.3beta1/win32/

2007-10-01

全文检索软件公开

更新:2007-10-01
對映章節:
来自日本PostgreSQLユーザ会 mailing-list的消息,发送者石井达夫先生。

內容:
全文检索软件已经公开,是在“独立行政法人情報処理推進機構(IPA)”的2006年度“自治体における

オープンソースソフトウェア活用に向けての導入実証” project中开发的。

它是个完全基于BSD许可证的自由软件,可以自由使用。

1、用户定义函数normalize
输入的正文以一定的规则“正规化”,具有以下功能:
- 删除换行code
- 字符集JIS X 0208的字母数字转换为ASCII
- JIS X 0201向JIS X 0208的转换(这段我看不懂,他们的编码,简单说就是这样)
- ASCII大写转换为小写
通过这个规则可以消除掉误差

实例如下:
test=# SELECT normalize('01234567890123abcあいうえおABC');
normalize
--------------------------------
01234567890123abcあいうえおabc
(1 row)

test=# SELECT normalize('ガギグゲゴ');
normalize
------------
ガギグゲゴ
(1 row)

2、用户定义函数wakachi
使用mecab(http://mecab.sourceforge.jp/)解析输入的字符串,变成用空格分割单词的形式,这就是所说的“わかち書き”(以空格分隔单词的意思)。
实例如下:
test=# SELECT wakachi('wakachiはPostgreSQLのユーザ定義C関数です');
wakachi
--------------------------------------------------
wakachi は PostgreSQL の ユーザ 定義 C 関数 です

3、此外
- functions
这两个函数与TSearch2结合进行全文检索的例子(mail 只是这样说,可能是指这个软件自带范例)
- utf8_and_euc_jp_jis
日文编码相关如何如何,不翻译了

题外话:
感慨一下,日本人为自己很下功夫,当然也必须承认,背后财团的支持也是不可少的。
我们没有财力支持,他们可以把这个作为自己的职业,而我们不能。

2007-09-19

PostgreSQL 8.2.5, 8.1.10 小版次更新公告

更新:2007-09-17
對映章節:
http://www.postgresql.org/about/news.862

內容:
PostgreSQL 公告更新本次小版次包括 8.2, 8.1, 8.0, 7.4 與 7.3 版.
本次主要修正大多屬安全性議題的更新.

附加的安全性修正在 dblink 與 pgstattuple,
一個可能存在 vacuum 的 index-corruption 問題,
修正 GIN indexing, 與 logging 改良.
完整的修正與改進清單請見
the release notes.

相關版本如下 :

延伸閱讀(Link):
PostgreSQL 8.2.5 詳細更新完整內容

2007-09-09

PL/pgSQL:呼叫函數、SQL查詢與回傳值處理

更新:2007-09-09
對映章節:

內容:

使用 PL/pgSQL 不外乎就是撰寫資料庫的預儲程序(Stored Procedure)、函數(Function)、觸發器(Trigger) 等。設計這些內部程序,其中除了迴圈、真假值判斷、回傳等等特別需求的語法外,主要的內容還是由各種 SQL 查詢命令或是呼叫其他已存在的函數所構成。由於使用 PL/pgSQL 呼叫其他函數或執行SQL命令時,多半是要等待回傳值、資料列並更進一步處理,所以與平時用前端程式或SQL命令列對函數與SQL指令的操作上,有比較不同的習慣性用法。要了解在 PL/pgSQL 處理各種呼叫查詢,必須從回傳值處理的角度去深入。

以下是幾個常用處理函數和SQL命令回傳值的方法:

  1. 將回傳資料列指向 rs 變數﹝將 rs 定義為 record 類型﹞
    SELECT * INTO rs FROM mytable;

    然後可讀取回傳資料列的各欄位內容:

    rs.id
    rs.name
    rs.address
    ...

    註:此 SELECT INTO 使用方法很特別,並非是你想的那樣,請參考下文說明。


  2. 只回傳單獨欄位內容並指向 addr 變數﹝將 addr 定義成與 address 欄位類型相同﹞

    SELECT address INTO addr FROM mytable;


  3. 拋棄所有查詢和函數的回傳值
    EXECUTE myfunction();
    PERFORM myfunction();

    註:EXECUTE 和 PERFORM 的詳細差異不在本文討論範圍,請參考官方說明文件。


一般來說,在 PL/pgSQL 之中我們還是可以照常使用 SELECT、UPDATE、DELETE 等指令,差別在於執行查詢命令時『有無回傳值』。另外,以下有幾點,是使用 PL/pgSQL 呼叫及執行任何 SQL 命令時該注意的重點:


任何命令所回傳的值不能隨便忽略


在過去使用前端外部程式去執行 SQL 命令,你可以忽略不管回傳值的問題,就算有回傳任何資料,我們也可以省略不處理它;但在 PL/pgSQL 中就有所不同了,尤其在『觸發器(Trigger) 』函數的設計中,更是不可放任回傳值不處理,所以,通常在觸發器中我們如果要拋棄回傳值,請使用 PERFORM 。


SELECT INTO 的不同


在 PL/pgSQL 與平時使用 SELECT INTO 是完全不同的意義,後者是建立一個新的資料表,並將所查尋到的數個資料列寫入此資料表。而在 PL/pgSQL 中只是取得查詢資料列到自訂變數之中,並不會建立新的資料表。

延伸閱讀(Link):
PostgreSQL 的 Stored Procedure 簡易實例
PostgreSQL 觸發器(Trigger) 入門
Stored Procedure 實例:資料分頁

2007-09-04

PostgreSQL 每週新聞摘要 - September 03 2007

更新:2007-09-04
對映章節:
http://people.planetpostgresql.org/dfetter/index.php?/archives/134-guid.html

內容:

  1. Npgsql 的使用執照更改為 BSD 授權.
    (Npgsql 是 PostgreSQL 作為提供 .Net 資料的提供者.
    主要目的是允許任何 .Net framework 程序開發能存取資料庫伺服器.
    100% 應用在 C# code. 並且工作在 Postgresql 7.x 與 8.x.)
    專案站點:
    http://pgfoundry.org/projects/npgsql/

  2. Slony-I 1.2.11 發佈.
    (Slony-I 是一個
    主對多從式"master to multiple slaves"複寫(replication)系統.
    支持串聯效應"cascading" (例如. 一個節點"node"A能抄寫到另一個節點 B,
    節點 B 或 A 都能再各自抄寫到其它節點) 與災難復原"failover".
    專案站點:
    http://slony.info/

  3. SE-PostgreSQL 8.2.4-1.0 發佈.
    (SE-PostgreSQL 是安全強化建構在
    Security Enhanced Linux (SELinux)'s 上的擴展版)
    專案站點:
    http://code.google.com/p/sepgsql/

感謝參與 PostgreSQL 一切開發工作與相關活動的參與人們!!!

延伸閱讀(Link):
PostgreSQL 複寫叢集系統 Slony-I(一)
PostgreSQL 複寫叢集系統 Slony-I(二) (簡體)

2007-09-01

Stored Procedure 案例:動態排列資料

在資料整理時,我們常碰到有些資料需要往前移動,有些則要往後移動,尤其在資料的顯示流程規劃上就常常要使用到這種 功能。可是傳統資料庫的排序功能,不外乎就是使用日期、時間、資料編號做為排序依據,然後利用 ORDER BY 命令達成資料排序的目的,除此之外,我們無法動態調整資料列的先後次序。


一種解決辦法是為每個資料列增加新的欄位﹝例子中取名為mytable_after﹞,用來記錄該筆資料列的前一筆資料編號。所以我們在讀取資料時就使用該欄位記錄的值,逐一列出每筆資料。操作流程大致上是:

讀取第一筆資料 Amytable_after 為 0
讀取欄位 mytable_after = A 的第二筆資料 B
重覆操作直到找不到下一筆資料
此方法讀取過程繁複,若交由外部程式去做,將會不停的發送 SELECT 命令給 SQL Server,可想而之,其效能必定差矣。所以,我們可以建立 PostgreSQL 預儲程序,讓 PostgreSQL 在內部操作處理,然後直接回傳已排序完成的資料流。

PostgreSQL 動態排列實作例子:

CREATE OR REPLACE FUNCTION mytable_dynlist()
RETURNS SETOF mytable AS
$BODY$DECLARE
rscount Integer;
rsnext Integer;
rs record;

BEGIN
    select count(*) INTO rscount from mytable;
rsnext = 0;
FOR i IN 1 ..
rscount LOOP
SELECT * INTO rs from mytable where mytable_after = rsnext LIMIT 1;
rsnext = rs.mytable_id;
RETURN NEXT rs;
END LOOP;

END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION
mytable_dynlist() OWNER TO somebody;

當 資料不多時,這種在資料庫內部處理動態排列很省事,在前端的程式也很好寫,但是,資料量一大,這種方式會造成 SQL Server 相當大的負擔。所以,當資料量多或是在前端程式有做多台機器的負載平衡,還是建議在前端先將所有要排序的資料下載後,在前端的程式做排序處理。



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

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/

2007-07-27

感謝校正(pgAdmin III 1.8 Bata2)

更新:2007-07-27
對映章節:

內容:
首先感謝中國朋友: 唐荣华 來信的指正
(原信件內容)

郭先生:
您好,在PGADMIN III 1.8.0中,新建函数的属性中,返回类型有"返回设置"?
我猜想是否有误?是否应该是:“返回集合”?
谢谢!

阿益非常樂見諸如此類的往來信件
能認識更多中文使用的朋友們.
pgAdmin 3 1.8 即將進入正式發佈期
阿益當前的正體與簡體中文語系檔完成 100% 進度
不要小看這 pgAdmin 3 管理工具,
裡頭可是包著 2160 個翻譯字串哦!
可別以為您的一般用途能見到 pgAdmin 3 的功能全貎@@~

本次更新如下:
官方翻檔進度:
http://www.pgadmin.org/translation/status.php
預計到下週一會同步阿益的更新內容.

更正感謝: (中國朋友: 唐荣华 指正)
返回設置 - >
回傳集合 Returns set
回傳一組集合?
===============================
更新更適切的字串:
Slony(複寫叢集系統)
Trigger enabled?
進行除錯(&D) &Debugging
管理巨集組合鍵... Manage Macros
巨集組合鍵(&M) &Macros
Edit and delete macros
&Favourites 巨集書籤(&F)
===============================
回传集合 Returns set
回传一组集合?
Slony(复写丛集系统)
Trigger enabled?
进行除错(&D) &Debugging
管理巨集组合键... Manage Macros
巨集组合键(&M) &Macros
Edit and delete macros
&Favourites 巨集书签(&F)


延伸閱讀(Link):

2007-07-18

"分享即學習"的良性互動, 是提升知識的原動力.

更新:2007-07-18
對映章節:
感謝 鄧大哥對這篇文章中的觀念指正
PostgreSQL 與 地理資訊系統 ( Geographic Information Syste...
受制於 Blogger 版面功能的不足, 怕更多想對 PostgreSQL 了解的朋友,
因為阿益的文章中的錯誤, 且可能未留意到 鄧大哥在該篇文章裡留的珍貴意見,
阿益特地將該篇的意見搬上文章區,
順道一提: 我們需要更多像 鄧大哥“分享即學習”的良性互動.……

原意見內容:

阿益,你好
無意中,因為在寫一個教案而查到你的Blog,你們果然是PostgreSQL的專家,Blog的內容很充實,獲益良多。

但本篇關於GIS的部份,有幾點需澄清的:
1.GIS並非只是圖資系統。GIS是一種集合大量處理空間資訊函式庫的系統,以用來分析、儲存、展示地理空間資訊,地圖只是其中一項展示功能。

2.PostgreSQL並非唯一在開放源碼中擁有地理資訊處理能力的。Free/Open Source Software for Geoinformatics(地理資訊開放源碼自由軟體)不下20種,除了PostgreSQL/PostGIS之外,還有GRASS, MapServer, GeoServer, QGIS, OpenEV, DeeGee, Geotools.......,詳細的資訊可到www.osgeo.org查詢。而mySQL with Spatial extension與PostgreSQL/PostGIS對空間資訊也有類似的處理能力。雖然本人為PostgreSQL/PostGIS的愛用者,但 PostgreSQL/PostGIS並非是唯一的選擇。

3.OpenGIS並非是架構上的地理SQL標準。OpenGIS始於北 美,加拿大政府更是極積地發展,Refractions接受加拿大BC省政府不少補助而投入開發符合OpenGIS標準制度的地理資料運算函式,進而發展 出依附在PostgreSQL上的PostGIS。事實上,OpenGIS是一種地理資料開放的概念,OGC(Open Geospatial Consortium)是為了達成此一概念而成立的組織,專門訂立地理空間的標準,這一系列的標準可分為資訊架構(含資訊編碼)和服務架構,其中 PostgreSQL/PostGIS採用對於地理空間資料以Simple Feature for SQL為標準(資訊編碼),並可以接受WKT、WKB、GML等資料格式I/O。


鄧東波
Dongpo Deng
Co-founder, OSGeo Taiwan
dongpo@iis.sinica.edu.tw
My Blog: http://geocyber.org
OSGeo Taiwan: http://wiki.osgeo.org/index.php/Taiwan


延伸閱讀(Link):
PostgreSQL 與 地理資訊系統 ( Geographic Information Syste...

2007-07-08

PostgreSQL 與 地理資訊系統 ( Geographic Information Systems,GIS )

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

內容:
前天收到一位關注到 PostgreSQL 功能的長官來信,
問到除了購買 Oracle 數百萬NTD幣值產品外,
是否有更值得投入研究發展的解決方案...
答案是有的 PostgreSQL 在 GIS 上的應用, 另外阿益個人建議這數百萬NTD
幣另加上聘用 Oracle DBA 的每月開發固定成本,
若是全數拿來投入開放源碼的 PostgreSQL 並加以回饋技術與問題到 PostgreSQL
開發團隊中, PostgreSQL 的先進程度一定會更加速進展...
在此順便整理一下 PostgreSQL 這部份的功能強項:

地理資訊系統 ( Geographic Information Systems,GIS )
又被譯為圖資系統, 空間信息系統, 大多用於大型商業地理的研究專案中...

PostgreSQL 是目前最開放且唯一在開放源碼中擁有地理資訊處理能力的
物件關聯型資料庫管理系統,
除了內置的豐富的地理定位用的資料類型(Types)與函數群可用外,
更有獨立的相關地理資訊研究專案 PostGIS, 您可以參考
http://postgis.refractions.net/


OpenGIS 是一個架構上的地理 SQL 標準, 由 OGC 通過生成向量資料(矢量數據)解決了這些問題,向量資料也指幾何物件,如點、線、多邊形及其組合,可以在啟用三維資料庫的 PostgreSQL 中存取(PostgreSQL 的 OpenGIS 實現標準稱為 PostGIS)。
存儲在 PostgreSQL 資料庫中的 GIS 資料完全可以使用 SQL-92 來搜尋。

PostgreSQL 最具體的研究機構是在本站2月份中的文章中
PostgreSQL 子專案與相關站點(彙報篇)中提到的

IGN--法國國家地理學院
http://postgis.refractions.net/documentation/casestudies/ign/

在 PostgreSQL 中已經定義了一些基本的集合實體類型,這些類型包括: 點(POINT)、線(LINE)、線段(LSEG)、方形(BOX)、多邊形(POLYGON)和圓(CIRCLE)等;
另外,PostgreSQL定義了一系列的函數和操作符來實現幾何類型的操作和運算; 同時,PostgreSQL引入空間數據索引 R-tree。

PostGIS 還提供以下功能:

  • 資料庫的坐標變換計算
  • 球型體長度運算
  • 三維的幾何資料類型(Types)
  • 地理空間的聚集函數
  • 地理網格(柵格)標示數據類型
PostGIS 不論在功能還是擴展性方面都不落後於商業 GIS 平台的空間資料庫,
且由於其源始碼的公開性,更加容易吸引廣大愛好者參與到 PostGIS 的開發中,
不斷完善並持續改良現有的功能同時進一步擴展新的特性。
當然也更歡迎您成為其中的一份力量...

延伸閱讀(Link):
IBM - Find your way around open source GIS
PostGIS开启开源空间数据库的未来

2007-07-02

sepgsql(简体)

更新:2007-07-02

內容:
又一个日本人发起的project:Security Enhanced PostgreSQL,昨天刚刚发布beta版,预定大约1个月之后发表正式版。它基于SELinux,能够提供细粒度(fine grained)的访问控制。

网址: http://code.google.com/p/sepgsql/

关于SELinux我上传一份pdf简体中文howto在我们的Google Group:Getting Started with SE Linux HOWTO,文档的生成时间比较早,与现在的版本并不一致,仅供参考。(作者:Faye Coker,翻译:邹立巍)

2007-06-29

pgAdmin III 正體/簡體中文語系檔(更新公告)

更新:2007-06-30
對映章節:

內容:
為了明天在 KaLug 要跟大家分享 pgAdmin III 時, 能看到親切的完整中文介面.
也為了趕上即將在下個月發表的 PostgreSQL 8.3 + pgAdmin III 1.8 同時的發表.
台灣時間 23:30 翻譯完成也寄發給專案負責人, 估計最快明天最慢下週一
官方載點即會更新至 100%
http://www.pgadmin.org/translation/status.php
想用的人可自行下載 *.mo 覆蓋原語系 mo 檔即可(1.8 向下相容至 1.4版用戶)

阿益完整對即將來到的 pgAdmin III 1.8 語系檔校對與增加譯文串約 300 行
希望的就是讓身為中文語系的廣大 PostgreSQL 濳在新手們和Geek們
能藉由中文來加速推廣 PostgreSQL.

當然也更歡迎您加入這網誌的行列~Ubuntu!!!(以慈待人)


延伸閱讀(Link):

2007-06-28

PostgreSQL 安裝bin包裝的設計美學 Mac OS X

更新:2007-06-27
對映章節:

內容:
最近阿益獲得一部 Apple iBook G4
當然要看看這非主流但廣受視覺美學者最愛的 Mac OS X
在安裝 Bin包的製作上, 是否也承繼Apple 的美學與品質至上的精神...

PostgreSQL Tools for Mac OS X

一個專為 Mac OS 上製作的 Client/Server 開放專案
從這專案就可以體會雖然 Mac OS 不是一個開放平台
但 PostgreSQL 的 Mac 擁護群仍發揮設計美學製作與投入.

首先您會有二個來源選擇
http://sourceforge.net/projects/pgsqlformac/ (主要來源)

http://www.macupdate.com/info.php/id/8561/postgresql
該專案有張自拍的快照(果然Mac上的開發者, 對視覺的敏銳度比其OS來的高)

下載 dmg包(Mac OS用的二進制安裝包)大約 70MB

安裝的第一個畫面

哇~佩服!!!連 Server 包裝都能如此用心在介面上的設計

Client 文件夾的內容


(未完...)


延伸閱讀(Link):

2007-06-25

PostgreSQL,MySQL與Firebird三者的性能徹底大比較

更新:2007-06-25
文章轉自:日本ITpro
「MySQL,PostgreSQLとFirebirdの性能をユーザー会メンバーが徹底比較,判明した"意外な結果”」---OSC2007.DBより


內容:
日本6月23日 2007.DB(OSC2007.DB)大會主題本次針對三大開源碼資料庫系統 PostgreSQL,MySQL 與 Firebird 三者的性能徹底大比較, 邀請了在日本此三大非營利法人團體的技術主導員與各愛好者共同參與。

(會場現況)


(三大方代表人)
左起日本MySQL-堤井泰志氏,Firebird日本-木村明治氏,日本PostgreSQL-岡裕生氏


主要比較基準:
單機 2.4GHz 的 Pentium4,1G-RAM
IDE ATA100 7200rpm 60G硬碟
資料庫系統平台
MySQL 5.0.6-beta(使用 InnoDB),PostgreSQL 8.1,Firebird 2.0.1。


證明的結果,PostgreSQL 打破以往人們對 MySQL 擁有快速的 SELECT 的概念, 在以下的統計圖表中。這主要的原因在於 MySQL , 對於具有事務交易的引擎 InnoDB 開發與效能未能如 PostgreSQL 的成熟發展, MySQL 主要強項仍為早年發展至今的的 MyISAM 缺省引擎(但不具事務交易功能)。另外 Firebird 是高速超越整體與 MySQL 和 PostgreSQL 的比較。但是 "MySQL 和 PostgreSQL 能承受同時 500 連接數或更多量的訪查"。








如何選擇適合的後端資料庫...
如上述的測試結果, 三者各具所長, 在客戶端(Client)連結數同時連結到DB Server少於 500的情況下, Firebird 確實提供最快速的回應首選, 但若 Client 連結數同時量高達 500時, 使用 PostgreSQL 或者 MySQL 會比 Firebird 來的適合.
但若更加考慮大環境是否重視資料嚴謹度與是否需要事務交易功能者, 此時針對大多數時間僅提供查詢為主要任務者, 仍建議採用 MySQL(MyISAM:無事務交易功能) 效率比 PostgreSQL 好, 但若使用在類似 ERP 等高度重視資料正確性的情況下使用 PostgreSQL 絕對比使用 MySQL(InnoDB:事務交易功能)來的更棒.

延伸閱讀(Link):

2007-06-07

PostgreSQL 系統安全認證(一)

更新:2007-06-07
對映章節:

內容:
PostgreSQL 是一個非常重視安全性與反饋修補快速的資料庫系統...
也是目前唯一取得 ISO/IEC15408 安全認證的開放源碼專案產品
PostgreSQL 日本NTT協助取得 ISO/IEC15408 安全認證

PostgreSQL 最主要的設計理由:
開發真正能運作在大型企業與政府組織的資料倉儲系統,
因為涉及的使用人數鉅多與強調安全性,
必須擁有嚴密的控管機制存在,
且必須保持快速反應安全性問題修正的能力.

系統安全依賴SA/DBA(系統工程師/資料庫管理師)的經驗累積與能力培養,
安全性調校-涉及的知識與相關操作, 是必須擁有對其基本管理者再進階時,
加以探討的強化部份, 而非本末倒置空談安全性議題,
卻對SA/DBA工作內容半知不解之人.

除了原使用的系統OS的安全防護, 依靠您對使用的作業系統的管理程度能力外,
PostgreSQL 使用了下述幾個層級來進行有關安全性的層級防制力.

層級

  1. SSL連線加密模式(postgreslq.conf)
    • 透過啟用PostgreSQL組態檔的 SSL 加密模式, 來防止封包監聽程序.
  2. 連接用戶認證模式與種類(pg_hba.conf)
    • 可各別對特定的 user, DB, IP, IP-Sub, 使用不同的用戶認證模式.
    • 認證模式的種類包括(詳情->密碼學):
      trust, password, crypt, MD5, Krb4, Krb5, Reject, inetd.
  3. 資料庫物件權限(GRANT, REVOKE)
    1. 對特定的資料庫, 資料表, 進行SQL使用授權管理.
在下個講次, 說明如何進行....

延伸閱讀(Link):

2007-06-04

phpPgAdmin 4.1.2 發佈

更新:2007-06-01
對映章節:

內容:
phpPgAdmin 4.1 在本月發佈安全更新版次 4.1.2,
這是一套以 Web 應用為基礎, 採用 php 撰寫的 PostgreSQL 管理套件,
在中文介面方面, 除舊有的 BIG5, GB2312的語系支援外,
阿益追加了 UTF-8 系的簡體與正體版入其專案中...

請前往官方下載:
http://phppgadmin.sourceforge.net/

CVS版(簡繁中文)文件下載:
http://phppgadmin.cvs.sourceforge.net/phppgadmin/webdb/lang/

延伸閱讀(Link):

PGCon 2007 年度大會的出席者們

更新:2007-06-04
對映章節:
http://www.postgresql.org/files/community/pgcon07/pgcon_group_photo.html

內容:


一年一度的 PGCon 2007 大會圓滿結束了, 參與者幾乎150名來自全球的人們...
今年是在渥太華(Ottawa)是每年匯聚 PostgreSQL 開發者與熱愛者的國際會議,
會期持續五天之久, 包括每天的對 PostgreSQL 開發者的研究講習與心得報告,
(好想參加哦@@)...
重要的是要感謝來自全球各地開發者的自由貢獻!!!

延伸閱讀(Link):

2007-05-15

【PostgreSQL观察】第36回 PostgreSQL的安全补丁(三)(简体)

更新:2007-05-15
對映章節:
http://itpro.nikkeibp.co.jp/article/COLUMN/20070507/270174/?ST=oss&P=3

按照石井先生的惯例,最后要讲些别的。

停止支持Windows平台下的PostgreSQL 8.0、8.1
理由主要是开发人员的不足,另外8.2版本特别针对Windows平台的固有问题进行了大规模的改良,难于向8.0、8.1移植也是一个很重要的原因。
不过并不是现在马上截止,而是从正在开发中的8.3正式发布起停止。由于PostgreSQL 8.3最早于2007年07月正式发布,在此之前还会进行少量的维护工作。
总之,Windows平台下的8.0、8.1的用户,最好从现在就考虑向8.2的移植吧。

正在开发中PostgreSQL 8.3的状况
关于8.3的最新状况,本来已经进行完功能确定的工作,大量的新功能和改良必须提交到cvs上。但是以上次提到的HOT为代表的大型功能比核心开发人员考虑的要多,目前进度落后。

PostgreSQL Conference 2007
从去年开始的PostgreSQL Conference,今年5月21-24日在渥太华举行(21-22日是tutorial)。延续自去年,仍然安排来自PGCluster的三谷先生的讲座。

关于PostgreSQL Conference 2007的详细资料:
http://www.pgcon.org/2007/

我预计也会参加,下次的“PostgreSQL观察”将为大家传达会议的情况。

(最后一篇内容本来不少,因为中间相当篇幅讲DISCARD命令,与前边从BLOG翻译来的一样,所以略过)

2007-05-14

PostgreSQL 的優點不應該受限於購買商業書籍的人數!

阿益我這幾天無事亂想...
決定不找出版商了!
但書的撰寫仍會開始進行,
並且在本站的頂部列了撰書專屬的內容討論區與(PDF)下載點.
目標和進度將更加公開(預計同時進行正體中文版與簡體版本)

阿益個人的理由是:
商業出版雖然可以讓小弟賺到一點小利與聲望,
但另一方面卻等同把 PostgreSQL 的知識與更廣的推廣工作,
受制於必須付費購買受地區限制(台灣)的商業出版書籍的閱讀人數,
阿益深感這違背了自己當初投入決定協助 PostgreSQL 在中文社群上推廣的精神.

若能採用網路圖書的方式來推廣,
也許更能使這本書的內容傳播到包括中國, 香港, 台灣..更多中文華人手上,
再加上 PG 中國的PostgreSQL技術討論區與本站的網誌, 在三者並行的推廣情況下,
期待更多專業的華人技術員參與 PostgreSQL 的全球開發團隊,
讓 PostgreSQL 能不斷的邁向全球最先進的開放源資料庫系統!!!

延伸閱讀(Link):

2007-05-13

【PostgreSQL观察】第36回 PostgreSQL的安全补丁(二)(简体)

更新:2007-05-13
對映章節:
http://itpro.nikkeibp.co.jp/article/COLUMN/20070507/270174/?ST=oss&P=2

利用临时schema的攻击
PostgreSQL具有“临时表”功能,仅在session期间有效,可以被短时间使用。这些临时表放在一个特殊的数据表“临时schema”中,同样包含在pg_catalog中,比其他schema优先被搜索。
也就是,利用pg_catalog进行同样方式的攻击是可能的,这就是这次新发现的安全脆弱性。
这个安全漏洞的编号是CVE-2007-2138

难于对策的利用临时schema的攻击
可以采取与上次同样的对策,设定适当的schema搜索路径,但是这里有一个问题:临时schema的名字不是固定的。
在psql中利用\dn命令看一下就可以知道这一点:
test=> CREATE TEMP TABLE t1(io int);
CREATE TABLE

test=> \dn
  List of schemas
  Name      | Owner
--------------------+---------
information_schema  | t-ishii
pg_catalog      | t-ishii
pg_temp_1      | t-ishii
pg_toast       | t-ishii
public        | t-ishii
(5 rows)

pg_temp_1就是临时schema,“_1”部分是根据session变化的。

假想schema名「pg_temp」的引进
本次的安全性补丁引进了表示临时schema的假想schema叫做“pg_temp”,将它放在schema搜索路径的最后就可以。
从上边的说明可以看出,这个安全对策仅仅做PostgreSQL的版本升级是不够的,在使用SECURITY DEFINER函数的地方必须修正schema搜索路径明确指定使用pg_temp。
详细情况可以查阅PostgreSQL技术手册中的CREATE FUNCTION命令,慎重起见在这里预先说明一下。另外,这里
的函数是用PL/pgSQL编写。

(1)保存现在的schema搜索路径、设置新的路径:
old_path := pg_catalog.current_setting('search_path');
PERFORM pg_catalog.set_config('search_path', 'admin, pg_temp', true);
在函数的最开始增加这两行代码。
关键点是,schema搜索路径仅仅包含用户不能创建对象的schema(例子中是admin),而pg_temp要放在最后。因为public schema任何用户都可写入,搜索路径中最好不要包含它。函数中使用public对象的地方,显式地使用schema修饰符,例如:public.t1。
set_config()最后的参数为true,意味着当函数中发生错误而将事务中断的时候,将重新设定的schema搜索路径返回原始设置。这个也是很重要的。

(2)执行通常的函数处理
没什么特别的变动。

(3)
将schema搜索路径返回初始设定
PERFORM pg_catalog.set_config('search_path',old_path,true);
这个要写在return语句前边。

新发布的版本
实现以上对策的版本是:8.2.4、8.1.9、8.0.13、7.4.17、7.3.19,如果正在使用SECURITY DEFINER函数的话,尽快作版本升级是非常必要的。再者,单纯做版本升级是没有用的,必须注意还需要修改函数。因为SQL函数难于对应,使用PL/pgSQL将它重写吧。
另外,本次发布的补丁包括VACUUM FULL可能破坏数据等比较重要的bug修正,即使没有使用
SECURITY DEFINER函数,也推荐进行版本升级。

2007-05-12

【PostgreSQL观察】第36回 PostgreSQL的安全补丁(一)(简体)

更新:2007-05-12
對映章節:
http://itpro.nikkeibp.co.jp/article/COLUMN/20070507/270174/

翻译自石井达夫先生的文章

新的安全漏洞
在PostgreSQL观察第35回,传达了来自核心开发成员的安全补丁发表预告以及关于SECURITY DEFINER函数(可以使用其他用户权限的函数)的安全警告,这次新发现的安全漏洞正是与SECURITY DEFINER有关。

SCHAMA搜索路径(search path)的问题
在讨论本次发现的安全漏洞之前,我们先复习一下上回提到的“安全警告”,他们的根源是一样的。
PostgreSQL中有Schema的概念,也就是命名空间,在不同的schema中可以创建名字相同的数据表,这就像在UNIX中可以在不同目录下创建同名文件一样。它有两种用法:
一种是显式指定schema名:public.t1指定public schema下的t1对象。
另一种方法是 使用schema search path,指定当省略schema名称的时候,优先使用哪一个schema。可以用“show search_path”命令来确认当前的 schema search path:
test=> show search_path;
search_path
----------------
"$user",public
(1 row)
这个例子显示以当前登陆用户名为第一优先schema名称,然后是名为public的schema。顺便提一下public这个特殊schema,它是缺省的schema,如果什么也不指定的话,对象将被建立在public中。

利用pg_catalog schema的攻击
除此之外还有持有系统catalog、聚集函数(aggregate)、操作符(operator)的pg_catalog schema,即使它没有包含在搜索路径中一般也被当作搜索对象,并经常被最先搜索。因为所有的聚集函数和操作符都被pg_catalog持有,这样的考虑是适当的。
但是如果显式将pg_catalog放到搜索路径中的话,将以指定的顺序进行搜索。如果事先准备与pg_catalog中的聚集函数和操作符同名的对象,pg_catalog中登录的“正规”对象会被替代。这样,SECURITY DEFINER函数中使用的对象可以被替换为恶意用户提供的对象,以他人的权限实施攻击。
相应的对策是,在SECURITY DEFINER函数的搜索路径中仅仅设定安全的schema。
上次说提出的“安全警告”正是这样。

2007-05-11

決定找出版社談談囉~PostgreSQL 8.3 目錄(暫定)

這是預定的內容, 各位如果有好的出版商麻煩介紹一下囉~
明天起來去打電話問問出版社...
各位朋友順便幫阿益看看內容給點建議~
感謝您^.^

PostgreSQL 8.3 目錄(暫定)

第一部份 PostgreSQL基本知識篇 5

Chapter 1. PostgreSQL 全球最先進的開放源碼資料庫系統 5

1.1. 什麼是 PostgreSQL ? 5

1.2. 歷年國際上獲獎的證明 5

1.3. 長達21年嚴謹的發展歷史 5

1.4. 能在最多種作業系統平台上運行 5

1.5. 免費且自由的使用授權與完全公開源碼 5

1.6. 先進的物件導向型資料庫系統 5

1.7. 唯一完全屬於社群構建的企業級資料庫系統 5

1.8. 最服從國際SQL標準規範開發的模範生 5

1.9. 擁有最寬廣的程式語言中介體 5

1.10. 允許使用者自訂函數 5

1.11. 完整的國際化支持 5

1.12. 全文搜尋功能 5

1.13. XML的支持 5

1.14. 超越商業型資料庫系統的權威測試報告 5

1.15. 通過國際 ISO15408 軟體品質安全性認證 5

1.16. 全球大型組織使用中的品質實例 6

Chapter 2. 許可執照 6

2.1. BSD 許可執照的特徵 6

2.2. PostgreSQL 為核心商業化的國際型企業 6

2.2.1. EnterpriseDB, Inc. 6

2.2.2. Command Prompt, Inc. 6

2.2.3. Fujitsu Supported PostgreSQL (FSP) 6

Chapter 3. 從舊版本到新版本間的改變 7

3.1. 版本7.4到版本8.0的改變 7

3.2. 版本8.0到版本8.3的改變 7

Chapter 4. 關聯型資料庫的基本知識 7

4.1. 關聯型資料模型的基本概念 7

4.2. 資料庫管理系統的角色 7

4.3. SQL國際標準(SQL92, SQL99, SQL2003) 7

4.4. SQL基礎理解, SQL分組化(DDL/DML/DCL) 7

第二部份 PostgreSQL 伺服器管理篇 7

Chapter 5. 安裝方式 7

5.1. initdb 命令 7

5.2. 資料庫叢集的概念 7

5.3. 樣版資料庫 7

Chapter 6. 標準工具的使用方法 7

6.1. psql, pg_ctl, postmaster, createdb, dropdb, createlang, droplang, createuser, dropuser, vacuumdb 7

Chapter 7. 組態配置文件檔 7

7.1. postgresql.conf 7

7.2. pg_hba.conf 7

7.3. SET/SHOW 宣告的使用 7

Chapter 8. 備份策略 8

8.1. 使用 pg_dump, pg_dumpall, pg_restore, psql 命令 8

8.2. 複製資料目錄夾的備份與還原 8

8.3. PITR 的概念 8

8.4. 使用COPY 宣告與 copy 命令 8

Chapter 9. 管理者基本任務 8

9.1. 創建/刪除/變更資料庫使用者 8

9.2. VACUUM/ANALYZE 的使用與預算 8

9.3. 系統摘要資訊的取得 8

9.4. 資訊架構模式與系統目錄 8

9.5. 權限表上GRANT/REVOKE 宣告 8

第三部份 PostgreSQL 程序開發篇 8

Chapter 10. SQL 命令 8

10.1. SELECT 宣告 8

10.2. INSERT/UPDATE/DELETE 宣告 8

10.3. 序列數(Sequences) 8

10.4. 資料類型 8

10.5. 二進制陣列類型與大型物件 8

10.6. 表的定義 8

10.7. ALTER TABLE, DROP TABLE, CREATE TABLE AS 8

10.8. 索引 9

10.9. 視觀表(Views) 9

10.10. 規則系統與觸發器 9

10.11. 架構模式(Schemas) 9

10.12. PREPARE 9

10.13. 游標(Cursor) 9

10.14. 共同值域(Domain)與類型定義 9

10.15. 函數定義與PL/pgSQL 9

Chapter 11. 建構函數 9

11.1. 聚合函數 9

11.2. 算術函數與運算子 9

11.3. 字串函數 9

11.4. 字串運算子 9

11.5. 時間函數 9

Chapter 12. 事務交易概要 9

12.1. 事務交易的宣告 9

12.2. 事務交易隔離層級 9

12.3. (LOCK) 9

12.4. LISTEN/NOTIFY 宣告 9

Chapter 13. 與程式語言建立連線 9

13.1. C libpg 10

13.2. Java JDBC 10

13.3. Ruby 10

13.4. Perl 10

13.5. PHP 10

13.6. .NET ODBC 10

第四部份 PostgreSQL圖形化管理介面(GUI) 10

Chapter 14. pgAdmin III 1.8 10

14.1. 取得與安裝 10

14.2. 介面與功能介紹 10

Chapter 15. phppgadmin 4 10

15.1. 取得與安裝 10

15.2. 介面與功能介紹 10

Chapter 16. 商業軟體簡介 10

16.1. Navicat PostgreSQL 10

16.2. EMS PostgreSQL Manager 10

第五部份 PostgreSQL 龐大的關聯軟體專案庫 11

Chapter 17. PgFoundry GBorg 簡介 11

Chapter 18. 主要子專案 11

18.1. Slony-I 複寫叢集系統 11

18.2. pgpool-II 負載平衡叢集系統-複寫-連結池 11

18.3. pgCluster 同步多點叢集系統 11

18.4. 常用子專案功能說明索引 11

附錄 11

A. PostgreSQL CE 認證考試 11

B. Windows 環境的安裝 11

C. 網路資源 11

D. 本書附屬的CD-ROM 11

網誌存檔

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)