当前位置：操作系统 > 玩转网络 >>
答案：数据库设计
编者按：这是涉及可自由地获得的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的不断增加的冗长和更长的准备时间通过少花些精力在转换字符串到非字符串后反过来进行弥补。
到目前为止, 我没有说过任何关于性能的事情。我将在这个系列的下一部分做深入研究。
上一个：基于MySQL的高性能数据库应用开发
下一个：Linux数据库大比拚(一)
