Linux教程網 >> Unix知識 >> 關於Unix >> Linux數據庫大比拚(二)

Linux數據庫大比拚(二)

日期：2017/3/6 15:34:48 编辑：關於Unix

數據庫設計編者按：這是涉及可自由地獲得的 Linux DBMS的系列文章的第二部分。為了測試並比較3個數據庫管理系統，我當然需要一個數據庫來管理他們。在讀完了《SQL傻瓜書》後，我有了一些如何設計現實的數據庫的基本知識，因此我揀選了一特別無聊的真實世 數據庫設計
編者按：這是涉及可自由地獲得的Linux DBMS的系列文章的第二部分。

為了測試並比較3個數據庫管理系統，我當然需要一個數據庫來管理他們。在讀完了《SQL傻瓜書》後，我有了一些如何設計現實的數據庫的基本知識，因此我揀選了一特別無聊的真實世界情形(一個因特網書店，其他？)並且寫下了一個小型數據庫設計以便探討一點Sql的能力。

在這個數據庫裡有5個表，BOOK保存可得到的書的登記信息；CUSTOMER包含登記的顧客。對每份訂單，BOOKORDER創建一行，參照其顧客。對每個定購的項目，在ORDER_POSITION裡引用它的訂單號。作為一種獎勵，針對書的排名我增加了一RATING表。

下列的SQL代碼可裝入到一個數據庫系統的SQL監控程序並且被接受應該沒有任何問題。所有的表在創建前被刪除因此確保他們不會在創建之前已經存在。

　

DROP TABLE BOOK;
CREATE TABLE BOOK (
ARTICLE_NO INTEGER PRIMARY KEY,
AUTHOR_FIRST_NAMES CHARACTER(30),
AUTHOR_LAST_NAMES CHARACTER(30),
TITLE CHARACTER(30),
ISBN CHARACTER(13) UNIQUE,
WHOLESALE_PRICE NUMERIC(4,2),
RETAIL_PRICE NUMERIC(4,2),
COPIES_AVAILABLE INTEGER
);
DROP TABLE CUSTOMER;
CREATE TABLE CUSTOMER (
CUSTOMER_NO INTEGER PRIMARY KEY,
FIRST_NAMES CHARACTER(30),
LAST_NAMES CHARACTER(30),
STREET CHARACTER(30),
HOUSE_NO SMALLINT,
POSTCODE CHARACTER(7),
TOWN CHARACTER(30),
ISO_COUNTRY_CODE CHARACTER(2)
);
DROP TABLE BOOKORDER;
CREATE TABLE BOOKORDER (
ORDER_NO INTEGER PRIMARY KEY,
CUSTOMER_NO INTEGER NOT NULL,
ORDERED DATE,
DELIVERY DATE,
STATUS CHARACTER(8)
CHECK (STATUS IN (′ACCEPTED′,
′DELAYED′,
′SHIPPED′,
′RETURNED′,
′PAID′)),
CONSTRAINT CUSTOMER_FK FOREIGN KEY (CUSTOMER_NO)
REFERENCES KUNDE (KUNDENNAME)
);
DROP TABLE ORDER_POSITION;
CREATE TABLE ORDER_POSITION (
POSITION_NO INTEGER PRIMARY KEY,
ORDER_NO INTEGER NOT NULL,
ARTICLE_NO INTEGER NOT NULL,
NUMBER SMALLINT,
CONSTRAINT ORDER_FK FOREIGN KEY (ORDER_NO)
REFERENCES BOOKORDER (ORDER_NO),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);
DROP TABLE RATING;
CREATE TABLE RATING (
RATING_NO INTEGER PRIMARY KEY,
ARTICLE_NO INTEGER NOT NULL,
SCORE NUMERIC(1,0),
COMMENT CHARACTER VARYING(300),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);
當然，這是一個極其簡單的數據庫。它看上去真實，但是它不適用於真實世界的應。它不保存顧客記錄或任何東西，並且它甚至沒有書的出版商的列。它只是一個測試環境。

注意我不想花大氣力強制CUSTOMER.ISO_COUNTRY_CODE為今天是實際有效的編碼。我在代碼做了一點限制以測試數據庫系統是否接受他們；我沒嘗試使數據庫無懈可擊。

改變設計適應PostgreSQL
當我將遵循ANSI標准的CREATE TABLE語句裝入Postgresql的psql監控視程序是，我遇到的困難是很少的。我得到一些警告：外部關鍵字限制被接受但還沒有實現，而且我不得不裁減RATING的COMMENT字段到255個字符，因為這是PostgreSQL的CHARACTER VARYING類型的字段的最大字段寬度。系統為存儲大量數據提供BLOB數據類型，但是它們不在標准版本內，因此我決定了不使用他們。另外的問題是相當愚蠢--因為我不能找到有關PostgreSQL如何強制NUMERIC到C數據類型，也因為我不想使用float以避免捨入，我決定使得貨幣字段為分值(cent)的整數數字。

我最後得到了這個略有不同的腳本：

DROP TABLE BOOK;
CREATE TABLE BOOK (
ARTICLE_NO INTEGER PRIMARY KEY,
AUTHOR_FIRST_NAMES CHARACTER(30),
AUTHOR_LAST_NAMES CHARACTER(30),
TITLE CHARACTER(30),
ISBN CHARACTER(13) UNIQUE,
WHOLESALE_PRICE INTEGER,
RETAIL_PRICE INTEGER,
COPIES_AVAILABLE INTEGER
);
DROP TABLE CUSTOMER;
CREATE TABLE CUSTOMER (
CUSTOMER_NO INTEGER PRIMARY KEY,
FIRST_NAMES CHARACTER(30),
LAST_NAMES CHARACTER(30),
STREET CHARACTER(30),
HOUSE_NO SMALLINT,
POSTCODE CHARACTER(7),
TOWN CHARACTER(30),
ISO_COUNTRY_CODE CHARACTER(2)
);
DROP TABLE BOOKORDER;
CREATE TABLE BOOKORDER (
ORDER_NO INTEGER PRIMARY KEY,
CUSTOMER_NO INTEGER NOT NULL,
ORDERED DATE,
DELIVERY DATE,
STATUS CHARACTER(8)
CHECK (STATUS IN (′ACCEPTED′,
′DELAYED′,
′SHIPPED′,
′RETURNED′,
′PAID′)),
CONSTRAINT CUSTOMER_FK FOREIGN KEY (CUSTOMER_NO)
REFERENCES KUNDE (KUNDENNAME)
);
DROP TABLE ORDER_POSITION;
CREATE TABLE ORDER_POSITION (
POSITION_NO INTEGER PRIMARY KEY,
ORDER_NO INTEGER NOT NULL,
ARTICLE_NO INTEGER NOT NULL,
NUMBER SMALLINT,
CONSTRAINT ORDER_FK FOREIGN KEY (ORDER_NO)
REFERENCES BOOKORDER (ORDER_NO),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);
DROP TABLE RATING;
CREATE TABLE RATING (
RATING_NO INTEGER PRIMARY KEY,
ARTICLE_NO INTEGER NOT NULL,
SCORE SMALLINT,
COMMENT CHARACTER VARYING(255),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);
　

使設計適應MySQL
MySQL象PostgreSQL一樣忽略外部關鍵字的限制，但是它搞了個UNIQUE限制。最後的腳本與PostgreSQL腳本差不多：

DROP TABLE BOOK;
CREATE TABLE BOOK (
ARTICLE_NO INTEGER PRIMARY KEY,
AUTHOR_FIRST_NAMES CHARACTER(30),
AUTHOR_LAST_NAMES CHARACTER(30),
TITLE CHARACTER(30),
ISBN CHARACTER(13),
WHOLESALE_PRICE INTEGER,
RETAIL_PRICE INTEGER,
COPIES_AVAILABLE INTEGER
);
DROP TABLE CUSTOMER;
CREATE TABLE CUSTOMER (
CUSTOMER_NO INTEGER PRIMARY KEY,
FIRST_NAMES CHARACTER(30),
LAST_NAMES CHARACTER(30),
STREET CHARACTER(30),
HOUSE_NO SMALLINT,
POSTCODE CHARACTER(7),
TOWN CHARACTER(30),
ISO_COUNTRY_CODE CHARACTER(2)
);
DROP TABLE BOOKORDER;
CREATE TABLE BOOKORDER (
ORDER_NO INTEGER PRIMARY KEY,
CUSTOMER_NO INTEGER NOT NULL,
ORDERED DATE,
DELIVERY DATE,
STATUS CHARACTER(8),
CONSTRAINT CUSTOMER_FK FOREIGN KEY (CUSTOMER_NO)
REFERENCES KUNDE (KUNDENNAME)
);
DROP TABLE ORDER_POSITION;
CREATE TABLE ORDER_POSITION (
POSITION_NO INTEGER PRIMARY KEY,
ORDER_NO INTEGER NOT NULL,
ARTICLE_NO INTEGER NOT NULL,
NUMBER SMALLINT,
CONSTRAINT ORDER_FK FOREIGN KEY (ORDER_NO)
REFERENCES BOOKORDER (ORDER_NO),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);
DROP TABLE RATING;
CREATE TABLE RATING (
RATING_NO INTEGER PRIMARY KEY,
ARTICLE_NO INTEGER NOT NULL,
SCORE NUMERIC(1,0),
COMMENT CHARACTER VARYING(255),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);
使設計適應 mSQL
因為mSQL是一個精簡的數據庫管理器(的確，有些人可能懷疑MySQL和mSQL是否是數據庫管理系統)，它放棄了大多數Sql的功能而僅僅接受SQL的一個嚴格限制的子集。這樣，mSQL的腳本看上有很大不同：

DROP TABLE BOOK
CREATE TABLE BOOK (
ARTICLE_NO INTEGER NOT NULL,
AUTHOR_FIRST_NAMES CHARACTER(30),
AUTHOR_LAST_NAMES CHARACTER(30),
TITLE CHARACTER(30),
ISBN CHARACTER(13),
WHOLESALE_PRICE MONEY,
RETAIL_PRICE MONEY,
COPIES_AVAILABLE INTEGER
)
DROP TABLE CUSTOMER
CREATE TABLE CUSTOMER (
CUSTOMER_NO INTEGER NOT NULL,
FIRST_NAMES CHARACTER(30),
LAST_NAMES CHARACTER(30),
STREET CHARACTER(30),
HOUSE_NO SMALLINT,
POSTCODE CHARACTER(7),
TOWN CHARACTER(30),
ISO_COUNTRY_CODE CHARACTER(2)
)
DROP TABLE BOOKORDER
CREATE TABLE BOOKORDER (
ORDER_NO INTEGER NOT NULL,
CUSTOMER_NO INTEGER NOT NULL,
ORDERED DATE,
DELIVERY DATE,
STATUS CHARACTER(1)
)
DROP TABLE ORDER_POSITION
CREATE TABLE ORDER_POSITION (
POSITION_NO INTEGER NOT NULL,
ORDER_NO INTEGER NOT NULL,
ARTICLE_NO INTEGER NOT NULL,
NUMBER SMALLINT
)
DROP TABLE RATING
CREATE TABLE RATING (
RATING_NO INTEGER NOT NULL,
ARTICLE_NO INTEGER NOT NULL,
SCORE SMALLINT,
COMMENT TEXT(255)
)
幾乎所有的約束都不見了，並且NUMERIC和CHARACTER VARYING分別由MONEY和TEXT代替。

在mSQL的監視程序中有令人沮喪的不足：它似乎不能接受從標准輸入輸入SQL腳本，這樣, 需要剪切/粘貼代碼。mSQL也討厭分號；最終我只能一個一個地輸入命令並用\g（“go”斜槓命令)終止每條命令。

實現測試客戶
為了比較3個數據庫管理器，我決定為執行在bookstore數據庫上的交易的目的用C寫了一個測試客戶。結果，我實現了一些操作，它們能比較API。為了性能比較，我隨後充分實現了它們，並且把一個非交互式模式加入客戶程序，因此它能自己運行，產生隨意的數據且隨機執行交易。

我決定了在樣品數據庫上實現下列行動：

增加一本新書： INSERT INTO BOOK (...) VALUES (...);
刪除一本存在的書： DELETE FROM BOOK WHERE ARTICLE_NO=...;
增加一個顧客： INSERT INTO CUSTOMER (...) VALUES (...);
刪除一個顧客： DELETE FROM CUSTOMER WHERE CUSTOMER_NO=...;
訂書的一個顧客： INSERT INTO BOOKORDER (...) VALUES (...); INSERT INTO ORDER_POSITION (...) VALUES (...);;
評估一本書的一個顧客： INSERT INTO RATING (...) VALUES (...);
改變一份訂單的狀態： UPDATE BOOKORDER SET STATUS=... WHERE ORDER_NO=...;
然後，能生成下列報表：

書籍列表： SELECT * FROM BOOK;
顧客列表： SELECT * FROM CUSTOMER;
正在投遞的交貨表，按狀態排序： SELECT * FROM BOOKORDER ORDER BY STATUS;
書籍的利潤額，最後有平均值： SELECT RETAIL_PRICE-WHOLESALE_PRICE FROM BOOK; SELECT AVG(RETAIL_PRICE-WHOLESALE_PRICE) FROM BOOK;
書評、評級和為一本書的平均評級