<a href="mailto:[email protected]">[email protected]</a>
Abstract. PostgreSQL is an excellent implementation of relational database, fully featured, open source, and free to use. Nearly nontrivial computer applications manipulate large amounts of data, and a lot of applications are written primarily to deal with data rather than perform calculations. Some writers estimate that 80% of all application development in the world today is connected in some way to complex data stored in a database, so databases are very important foundation to many applications. This article mainly about the usage of SQL shell of PostgreSQL(psql).
Key Words. Database, PostgreSQL, psql
1. Introduction
PostgreSQL是一款開源的關系－對象資料庫，其授權方式為BSD形式的開源協定，比OpenCASCADE的LGPL協定更為自由。将這些高品質的開源産品組合一下，應該可以創造出實用的軟體，提高工作效率。
如下圖所示為一款産于英國劍橋的工廠輔助設計管理系統PDMS的主要界面：
Figure 1.1 AVEVA Plant(PDMS) GUI
像AVEVA Plant(PDMS)這樣的産品，最終的結果都是以資料庫的形式将模型及其他資訊儲存。是以，需要有資料庫管理系統來對這些資料進行管理。不管是以樹形的方式，還是以三維模型的方式，都是其統一資料模型的一種表現形式。基于Observer設計模式定義：
定義對象間的一對多的依賴關系，當一個對象的狀态發生變化時，所有依賴于它的對象都得到通知，并自動更新。
由Observer定義可知，樹形顯示及三維顯示都是資料模型的Observer。不管是在樹上修改還是在三維模型中以直覺的互動方式修改模型，根本上還是修改了資料模型。并且在一個Observer中修改了資料模型，另一個Observer中會得到通知并自動更新和資料模型保持一緻。其核心資料模型歸根到底是由一個高性能的資料庫來管理，由此可見資料庫管理系統的重要性。
Figure 1.2 PDMS Architecture
我認為的PDMS軟體架構如圖1.2所示，樹形視圖、三維視圖及管道ISO圖和安裝圖等都是資料模型的觀察者。因為對資料模型的存儲非常重要，是以資料管理系統：資料庫的需求就顯而易見。但是對于應用開發而言，提供一個MVC架構來統一資料的增、删、改的接口更為重要。因為其好處更多：
v 接口統一，便于程式開發及給使用者一緻的操作，便于使用者輕松掌握軟體；
v 隻有基于統一的接口，才能基于此提供Undo/Redo功能；
v 便于與Tcl, Python等腳本語言綁定，為程式提供二次開發功能；
v 基于腳本綁定，為程式提供自動化測試，有力保證軟體品質；
綜上所述可知，OpenCASCADE提供的OCAF架構在思想上對于軟體開發的重要性。不過本文主要記錄如何在Windows平台上使用另一款高品質的開源資料庫管理系統PostgreSQL。了解PostgreSQL，就解決了軟體底層資料的管理，為軟體開發的資料模型提供根本保障。
2.Creating User Records
在Windows系統中，打開指令視窗并将PostgreSQL程式所在目錄置為目前路徑，然後運作createuser.exe程式，如下圖所示：
Figure 2.1 Create User by createuser.exe
Figure 2.2 View user in pgAdmin
－U選項用來指定建立新使用者時使用的賬号，必須為PostgreSQL具有建立使用者權限的使用者；
－P選項為使用程式createuser建立的新使用者的使用者名；
當然，也可以在pgAdmin中直接建立使用者，圖形化的界面更為直覺。
3.Creating the Database
在Windows系統中建立資料庫使用是程式createdb.exe，用法如下：
Figure 3.1 Create the Database by createdb.exe
Figure 3.2 Databases in pgAdmin
新版本9.4的createdb.exe建立出來的使用者沒有詢問是否有建立新資料庫的權限。修改後即可。成功建立資料庫後，就可以輸入以下指令來連接配接了：
Figure 3.3 Connect to the database in psql
Figure 3.4 Server status
如圖3.4所示，連接配接成功後，會從伺服器狀态中看到相關的連接配接資訊。
4.Creating the Tables
連接配接到資料庫後，psql提供了一些基本指令，如下表4.1所示：
Table 4.1 Basic psql Commands
由上表可知，可以使用psql的\i指令來執行相關的表格建立、插入資料等操作。
-- customer table
CREATE TABLE customer
(
customer_id serial ,
title char(4) ,
fname varchar(32) ,
lname varchar(32) NOT NULL,
addressline varchar(64) ,
town varchar(32) ,
zipcode char(10) NOT NULL,
phone varchar(16) ,
CONSTRAINT customer_pk PRIMARY KEY(customer_id)
);
-- item table
CREATE TABLE item
item_id serial ,
description varchar(64) NOT NULL,
cost_price numeric(7,2) ,
sell_price numeric(7,2) ,
CONSTRAINT item_pk PRIMARY KEY(item_id)
-- orderinfo table
CREATE TABLE orderinfo
orderinfo_id serial ,
customer_id integer NOT NULL,
date_placed date NOT NULL,
date_shipped date ,
shipping numeric(7,2) ,
CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id)
-- stock table
CREATE TABLE stock
item_id integer NOT NULL,
quantity integer NOT NULL,
CONSTRAINT stock_pk PRIMARY KEY(item_id)
-- orderline table
CREATE TABLE orderline
orderinfo_id integer NOT NULL,
CONSTRAINT orderline_pk PRIMARY KEY(orderinfo_id, item_id)
-- barcode table
CREATE TABLE barcode
barcode_ean char(13) NOT NULL,
CONSTRAINT barcode_pk PRIMARY KEY(barcode_ean)
将上述sql儲存為create_tables-bpsimple.sql，并在psql中執行，如下圖所示：
Figure 4.1 Create Tables by SQL File
Figure 4.2 Tables in pgAdmin
5.Populating the Tables
與建立表的方式一樣，将下述SQL儲存為檔案pop_tablenames.sql，并在psql中執行\i指令，将資料都插入到相應的表格中，如下所示：
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Miss','Jenny','Stones','27 Rowan Avenue','Hightown','NT2 1AQ','023 9876');
VALUES('Mr','Andrew','Stones','52 The Willows','Lowtown','LT5 7RA','876 3527');
INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone)
VALUES('Miss','Alex','Matthew','4 The Street','Nicetown','NT2 2TX','010 4567');
VALUES('Mr','Adrian','Matthew','The Barn','Yuleville','YV67 2WR','487 3871');
VALUES('Mr','Simon','Cozens','7 Shady Lane','Oakenham','OA3 6QW','514 5926');
VALUES('Mr','Neil','Matthew','5 Pasture Lane','Nicetown','NT3 7RT','267 1232');
VALUES('Mr','Richard','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982');
VALUES('Mrs','Ann','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982');
VALUES('Mrs','Christine','Hickman','36 Queen Street','Histon','HT3 5EM','342 5432');
VALUES('Mr','Mike','Howard','86 Dysart Street','Tibsville','TB3 7FG','505 5482');
VALUES('Mr','Dave','Jones','54 Vale Rise','Bingham','BG3 8GD','342 8264');
VALUES('Mr','Richard','Neill','42 Thatched Way','Winersby','WB3 6GQ','505 6482');
VALUES('Mrs','Laura','Hardy','73 Margarita Way','Oxbridge','OX2 3HX','821 2335');
VALUES('Mr','Bill','O Neill','2 Beamer Street','Welltown','WT3 8GM','435 1234');
VALUES('Mr','David','Hudson','4 The Square','Milltown','MT2 6RT','961 4526');
INSERT INTO item(description, cost_price, sell_price)
VALUES('Wood Puzzle', 15.23, 21.95);
VALUES('Rubik Cube', 7.45, 11.49);
VALUES('Linux CD', 1.99, 2.49);
VALUES('Tissues', 2.11, 3.99);
VALUES('Picture Frame', 7.54, 9.95);
VALUES('Fan Small', 9.23, 15.75);
VALUES('Fan Large', 13.36, 19.95);
VALUES('Toothbrush', 0.75, 1.45);
VALUES('Roman Coin', 2.34, 2.45);
VALUES('Carrier Bag', 0.01, 0.0);
VALUES('Speakers', 19.73, 25.32);
INSERT INTO orderinfo(customer_id, date_placed, date_shipped, shipping)
VALUES(3,'03-13-2000','03-17-2000', 2.99);
VALUES(8,'06-23-2000','06-24-2000', 0.00);
VALUES(15,'09-02-2000','09-12-2000', 3.99);
VALUES(13,'09-03-2000','09-10-2000', 2.99);
VALUES(8,'07-21-2000','07-24-2000', 0.00);
INSERT INTO stock(item_id, quantity) VALUES(1,12);
INSERT INTO stock(item_id, quantity) VALUES(2,2);
INSERT INTO stock(item_id, quantity) VALUES(4,8);
INSERT INTO stock(item_id, quantity) VALUES(5,3);
INSERT INTO stock(item_id, quantity) VALUES(7,8);
INSERT INTO stock(item_id, quantity) VALUES(8,18);
INSERT INTO stock(item_id, quantity) VALUES(10,1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(1, 4, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(1, 7, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(1, 9, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(2, 1, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(2, 10, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(2, 7, 2);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(2, 4, 2);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(3, 2, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(3, 1, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(4, 5, 2);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(5, 1, 1);
INSERT INTO orderline(orderinfo_id, item_id, quantity) VALUES(5, 3, 1);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6241527836173', 1);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6241574635234', 2);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6264537836173', 3);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6241527746363', 3);
INSERT INTO barcode(barcode_ean, item_id) VALUES('7465743843764', 4);
INSERT INTO barcode(barcode_ean, item_id) VALUES('3453458677628', 5);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6434564564544', 6);
INSERT INTO barcode(barcode_ean, item_id) VALUES('8476736836876', 7);
INSERT INTO barcode(barcode_ean, item_id) VALUES('6241234586487', 8);
INSERT INTO barcode(barcode_ean, item_id) VALUES('9473625532534', 8);
INSERT INTO barcode(barcode_ean, item_id) VALUES('9473627464543', 8);
INSERT INTO barcode(barcode_ean, item_id) VALUES('4587263646878', 9);
INSERT INTO barcode(barcode_ean, item_id) VALUES('9879879837489', 11);
INSERT INTO barcode(barcode_ean, item_id) VALUES('2239872376872', 11);
輸入指令如下圖所示：
Figure 5.1 Insert data to tables
6.Accessing the Data
在psql中輸入\dt指令來檢視資料庫中的表格，如下圖6.1所示：
Figure 6.1 Use \dt command to list tables
插入資料後，可以用SELECT指令來簡單檢視一下資料，如下圖所示：
Figure 6.2 Query the data
也可以用同樣的指令來查詢其他表中的資料。當然也可以用pgAdmin來檢視資料，如下圖6.3所示：
Figure 6.3 View and Edit Data in pgAdmin
7. Summary
通過對國外軟體的簡單介紹，說明了資料庫管理系統在軟體中的重要作用，并說明了在資料庫與應用層之間的資料架構的重要性。
由于PostgreSQL基于類似BSD協定，開源且免費使用，很自由，是以選擇PostgreSQL來學習資料庫的知識。
通過使用psql來建立資料表及插入測試資料，便于對PostgreSQL做進一步的學習。
8. References
1. Neil Matthew, Richard Stones. Beginning Databases with PostgreSQL. Apress. 2005
2. Richard Blum. PostgreSQL 8 FOR Windows. The McGraw-Hill. 2007
<a href="http://www.postgresql.org/docs/books/"></a>