數據庫設計
編者按：這是涉及可自由地獲得的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; 書評、評級和為一本書的平均評級： SELECT * FROM RATING WHERE ARTICLE_NO=...; SELECT AVG(SCORE) FROM RATING WHERE ARTICLE_NO=...; 將客戶帶入PostgreSQL的生活
關於用C編程PostgreSQL的好處是你能使用嵌入式SQL。（而且，至少我喜歡它)它不是很好地文檔化，但是ESQL預處理器ecpg運行並能產生PostgreSQL接口代碼就好。Sql 的定式思維方法有時妨礙了我；但是，開發客戶程序並不是很難的。
我說過“不是很好地文檔化”嗎？那是一個保守說法。否則PostgreSQL完整的HTML 文檔在這方面非常缺乏。我從書本得到的ESQL知識是初級的，而且聯機文檔沒幫助太多，因此我不得不自己了解如何由ecpg將C的變量強制轉換為NUMERIC值--還有其他東西，而且，ESQL預處理器不是很詳細，且無論何時它碰到任何小錯誤，總是似乎完全釋放出來，這對任何從事又長期准備的項目的人來說將是一個持久戰。
在編程PostgreSQL的客戶程序時，我碰到了一些小錯誤。例如，如果文檔記錄是可能的話，在聲明一個光標（cursor)時，ecpg將不接受一個 FOR READ ONLY子句 。ORDER BY子句甚至沒被實現。我遇見的問題大都ecpg預處理器有關。Postgres有一個 C API(不管怎麼說，ESQL需要被編譯進一些東西)，它可能是優秀的，但是我沒使用它(這就是生活)。當有ESQL時，我准備使用ESQL。
這是摘自postgres-client.pgc的list_books()函數：
void list_books(void) { EXEC SQL BEGIN DECLARE SECTION; int article_no; char author_first_names[30]; char author_last_names[30]; char title[30]; char isbn[14]; int wholesale_price; int retail_price; int copies_available; EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE book_cursor CURSOR FOR SELECT ARTICLE_NO, AUTHOR_FIRST_NAMES, AUTHOR_LAST_NAMES, TITLE, ISBN, WHOLESALE_PRICE, RETAIL_PRICE, COPIES_AVAILABLE FROM BOOK;
EXEC SQL OPEN book_cursor;
while (1) { EXEC SQL FETCH NEXT FROM book_cursor INTO :article_no, :author_first_names, :author_last_names, :title, :isbn, :wholesale_price, :retail_price, :copies_available; if (sqlca.sqlcode == 100) /* 100 == NOT FOUND */ break; /* bail out */
printf("\nArticle no. %d\n", article_no); printf("%s, %s:\n", author_last_names, author_first_names); printf(" %s (%s)\n", title, isbn); printf("Bought at %d; selling at %d; %d copies available\n\n", wholesale_price, retail_price, copies_available); };
EXEC SQL CLOSE book_cursor; }
代碼是相當直觀。它聲明一些主機變量，在一個BEGIN/END DECLARE SECTION構造中包裝聲明，打開一個SELECT光標查詢，並且然後一行一行地取到主機變量中，光標然後關閉。
我使用了更舊, 一致反對的sqlcode變量而不是更現代的sqlstate，因為這種方式更容易檢查一個NOT FOUND情形。
把客戶帶入MySQL的生活Mysql的C API是相當易用的。核心元素是包含有關數據庫連接的信息和其狀態的結構，它通過連接MySQL服務器進行初始化。該結構的一根指針必須被傳遞給所有的 MySQL 客戶函數。
查詢以字符串提交；這意味著一個人必須處理 C 字符串變換功能，包含空字節(\0) 的數據應該能使用，情況變得更復雜了，因為隨後傳遞了一個計數字符串而不是一個 C字符串。
為了獲取查詢結果，一個指向MYSQL_RES結構的指針和一個數值變量用適當的 API 函數初始化，然後將一行取進一個MYSQL_ROW變量，它是一個字符串數組，直接將結果放進整數變量，就像Postgresql的ESQL的實現能做的那樣，但這是不可能的，結果緩沖區隨後被釋放。只要你能理解，語義幾乎與在ESQL使用光標相同。
list_books(void) { int count; MYSQL_RES *result;
mysql_query(&bookstore, "SELECT ARTICLE_NO, AUTHOR_FIRST_NAMES,\ AUTHOR_LAST_NAMES, TITLE, ISBN, WHOLESALE_PRICE, RETAIL_PRICE,\ COPIES_AVAILABLE FROM BOOK"); result = mysql_store_result(&bookstore);
for(count = mysql_num_rows(result); count > 0; count--) { MYSQL_ROW record; record = mysql_fetch_row(result);
printf("\nArticle no. %s\n", record[0]); printf("%s, %s:\n", record[2], record[1]); printf(" %s (%s)\n", record[3], record[4]); printf("Bought at %s; selling at %s; %s copies available\n\n", record[5], record[6], record[7]); };
mysql_free_result(result); }
mysql_free_result ( 結果 ); }
API函數簡明扼要，但足夠了, Texinfo格式的文檔作為Mysql文檔的主要來源。
把客戶帶入mSQL的 上把顧客帶到生活mSQL和Mysql C API 之間的差別非常非常小。這樣, 甚至可能有一個自動變換器。主要的差別是： mSQL 不存儲連接數據塊, 僅存一個數字(int bookstore) 一些 mSQL 函數不拿連接作為一個參數 mSQL 函數名字是Pascal風格(他們使用大寫首字符而不是下劃線）
方便的MONEY數據類型是一個有2個的十進制位的固定精度小數類型。為了使mSQL正確地在MONEY列中將分幣(cent)存入整數數字裡，我需要轉換他們，強制到float，分離他們並且在add_new_book()函數中的sprintf語句格式化他們。
這是list_books(), 移植到 mSQL ：
void list_books(void) { int count; m_result *result;
msqlQuery(bookstore, "SELECT ARTICLE_NO, AUTHOR_FIRST_NAMES,\ AUTHOR_LAST_NAMES, TITLE, ISBN, WHOLESALE_PRICE, RETAIL_PRICE,\ COPIES_AVAILABLE FROM BOOK"); result = msqlStoreResult();
for(count = msqlNumRows(result); count > 0; count--) { m_row record; record = msqlFetchRow(result);
printf("\nArticle no. %s\n", record[0]); printf("%s, %s:\n", record[2], record[1]); printf(" %s (%s)\n", record[3], record[4]); printf("Bought at %s; selling at %s; %s copies available\n\n", record[5], record[6], record[7]); };
msqlFreeResult(result); }
mSQL的 C API文檔可以在mSQL 手冊裡找到，它以PostScript和一個大的HTML文件與mSQL一起發行。
一些早期結論所有這3個討論的數據庫系統是相當容易安裝、設置和編程。實現C API的客戶庫是很小的；與現今的比如GUI工具箱，他們的大小是可以忽略的，並且在客戶程序的二進制大小或存儲器足跡（footprint）沒有太大的差別。
Postgresql的ESQL API的不斷增加的冗長和更長的准備時間通過少花些精力在轉換字符串到非字符串後反過來進行彌補。
到目前為止, 我沒有說過任何關於性能的事情。我將在這個系列的下一部分做深入研究。