在上期文章中，我们研究了如何在 CGI 程序中使用关系数据库服务器。关系数据库非常有帮助，因为它们为我们提供了一种相对简单的存储和检索信息的方法。

关系数据库中的所有内容都存储在二维表中，每行代表一个记录，每列代表该记录中的一个字段。当我们使用 ASCII 文本文件来存储信息时，通常需要编写自己的例程来保存和检索信息；通过将信息结构化为表格，我们可以节省大量时间和精力，并确保当程序的多个实例尝试修改信息时，我们的数据不会被损坏。

与关系数据库的通信是使用结构化查询语言 (SQL) 进行的。由于 SQL 不是一种编程语言，如果我们要执行 SQL 查询，则必须将 SQL 查询嵌入到 C 或 Perl 程序中。

上个月，我们看了一组 CGI 程序，这些程序允许访问我们网站的访问者发送和接收基于 Web 的明信片。用户输入关于他们自己和他们的明信片收件人的信息，以及图形的名称和简短的文本消息。每张明信片都存储在 postcards 表中，表中的每一行代表一张明信片。通过为每一行提供一个唯一的 ID 号，我们的程序能够从数据库中检索单独的明信片。

实际上，我们上个月检查的程序 send-postcard.pl 演示了如何从 HTML 表单中获取输入，并将其转换为存储在数据库中的明信片，以及如何获取该唯一的 ID 号并使用它通过 show-postcard.pl 显示明信片。唯一的 ID 号由 send-postcard.pl 在 postcards 表中创建新行时生成，并确保用户可以检索自己的明信片，同时使其相对难以找到发送给其他人的明信片。

我们正在检查的程序是用足够可移植的 SQL 版本编写的，它们应该可以在几乎任何关系数据库服务器上运行。但是，我只在使用 MySQL 测试过这些程序，MySQL 是一个适用于多个 Unix 版本（包括 Linux）的优秀的小型关系数据库产品。您可以在 Web 上 http://www.tcx.se/ 找到更多关于 MySQL 的信息。

send-postcard.pl 版本的其中一个问题是，它要求用户命名要插入到她发送的明信片中的图形文件。虽然这听起来是个不错的主意，但它存在几个问题，最明显的是用户可以命名服务器上不存在的文件。此外，用户无法知道服务器上存在哪些图形，除非编写另一个程序来显示目录列表。

此外，将图形的文件名与其他明信片信息一起存储效率低下，并可能导致同步问题。毕竟，如果您决定将文件名从 foo.gif 更改为 bar.gif 会发生什么？在我们当前的模式下，我们将不得不遍历整个“postcards”表，并重命名 foo.gif 出现的每一行。这并非难事，但这意味着我们在进行此类更改之前必须仔细考虑。

最后，存储图形文件的名称引入了拼写错误的可能性。用户可能会无意中输入 fooo.gif，从而阻止明信片的收件人看到所需的图形。

为了解决所有这些问题，我们将把图形文件的名称移动到一个单独的表中，为每个图形提供一个唯一的 ID 号，就像我们为每张明信片所做的那样。有了这个系统，我们就可以在 postcards 表中通过数字引用图形。我们仍然可以通过这种方式引用无限数量的图形，但我们通过使用整数而不是字符串来实现，从而节省了文件系统上的存储空间。此外，这种合并使我们能够更改一个或多个图形文件名或位置，而无需担心破坏程序的输出。

我们可以使用 SQL SELECT 命令从数据库中的表中检索一行或多行，该命令返回与我们的选择条件匹配的所有行。因此，如果我们有兴趣从 postcards 表中检索 ID 号为 12345 的行，我们可以通过发送查询来做到这一点

select * from postcards where id_number = 12345

这将返回一个小表，该表的行都具有等于“12345”的 id_number。由于 id_number 保证是唯一的，我们可以预期 SELECT 命令返回的表将由单行和 postcards 表中包含的所有列组成。如果 id_number 不能保证是唯一的，那么 SELECT 很可能会从表中返回多行，而不仅仅是一行。

我们可以将 SQL 查询包装在程序中，而 CGI 程序是成熟的程序；因此，我们可以轻松编写一个 CGI 程序，该程序

让我们创建一个新表，其中第一列唯一标识图形，第二列命名图形。我使用 MySQL 附带的交互式 mysql 程序来完成此操作，我通过键入 mysql<\!s>test 进入该程序。要创建新表，请输入以下行

create table graphics (
        graphic_id mediumint not null primary key,
        graphic_file varchar(60) not null)

交互式 mysql 程序通过向我们提供以下消息来指示命令已成功执行

Query OK, 0 rows affected (1.13 sec)

我们现在可以使用以下命令将图形插入到 graphics 表中

INSERT INTO graphics (graphic_id,graphic_file)
        VALUES (12345, foo.gif);

现在，如果我们询问 graphics 表的内容，我们会得到以下结果

mysql> select * from graphics;
1 rows in set (0.04 sec)
+------------+--------------+
| graphic_id | graphic_file |
+------------+--------------+
|      12345 | foo.gif      |
+------------+--------------+

首先，我们需要修改 postcards 表的定义，使其现在期望获取图形的 ID 号，而不是图形文件名。要创建 postcards 表，请使用以下 SQL 命令

create table postcards (
        id_number mediumint not null primary key,
        sender_name varchar(60) not null,
        sender_email varchar(50) not null,
        recipient_name varchar(60) not null,
        recipient_email varchar(50) not null,
        graphic_id mediumint null,
        postcard_text blob);

请注意，我们已从上个月定义的表中删除了 graphic_name 列，并将其替换为名为 graphic_id 的列——该列在两个表中具有相同的名称、大小和类型。我们不需要给列相同的名称，但这样做使我们更容易跟踪事物。

用新的 graphic_id 列替换旧的 graphic_name 列的最直接方法是重新创建表，如上面的 SQL 命令所示。但是，此操作会导致丢失表中已有的任何数据。在使用数据库的项目开始时，创建和销毁表是很正常的——至少在您获得正确的定义之前是这样。在表使用一段时间后，您可能不希望销毁您的数据。

为了解决这个问题，我们只需使用 alter table 命令将新列 (graphic_id) 添加到 postcards 表中，该命令允许我们向现有表添加一列或多列。这不是设计数据库的最有效方法，但它消除了对可能依赖于 graphic_name 的程序的担忧。

我们的数据库现在包含两个表：“postcards”，其中包含组装完整的明信片所需的所有信息（包括我们希望显示的图形的 ID 号），以及“graphics”，它将这些 ID 号映射到名称。

我们如何在这两者之间建立关联？我们已经知道可以使用 SQL 的 SELECT 命令从表中检索一列或多列。SELECT 也可用于从多个表中检索列，甚至可以将来自它们的列连接在一起。

例如，让我们从我们的两个表中获取发件人姓名、收件人地址和图形名称，以便每张明信片的图形名称紧挨着唯一的 ID 和收件人地址显示。

首先，让我们在 graphics 表中添加另一个图形文件，只是为了安全起见

mysql> INSERT INTO graphics (
        graphic_id,graphic_file)
        VALUES (67890, bar.gif);
Query OK, 1 rows affected (0.00 sec)
mysql> select * from graphics;
2 rows in set (0.16 sec)
+------------+--------------+
| graphic_id | graphic_file |
+------------+--------------+
|      12345 | foo.gif      |
|      67890 | bar.gif      |
+------------+--------------+

现在让我们创建一个明信片用于我们的示例

insert into postcards (id_number, sender_name,
        sender_email, recipient_name,
        recipient_email, postcard_text, graphic_id)
VALUES (99999, "Bill Clinton",
        "president@whitehouse.gov",
        "Al Gore",
        "vice.president@whitehouse.gov",
        "Please call. I have a new tax idea.",
        12345)

现在让我们检索我们刚刚创建的明信片的发件人姓名、收件人姓名和图形文件名

mysql> select postcards.sender_name,
        postcards.recipient_name,graphics.graphic_file
        -> from postcards,graphics
        -> where postcards.id_number = 99999;
        2 rows in set (0.31 sec)
+--------------+----------------+--------------+
| sender_name  | recipient_name | graphic_file |
+--------------+----------------+--------------+
| Bill Clinton | Al Gore        | foo.gif      |
| Bill Clinton | Al Gore        | bar.gif      |
+--------------+----------------+--------------+

哎呀——这根本不是我们想要的。我们希望它调出我们指定的 ID 的文件名，而不是“graphics”表中所有行和列与我们的明信片的组合。那是我们想做的，但那不是我们告诉计算机要做的。通过以上述方式制定我们的 SQL 查询，我们无意中要求 postcards 中的行与 graphics 中的行进行所有可能的组合，这种组合在数据库圈中被称为两个表的“笛卡尔积”。虽然要求笛卡尔积不是错误，但几乎总是不可取的。在处理特别大的表时，要求对表进行此类组合可能会导致长时间的不必要计算，从而占用数据库服务器，使其无法执行其他任务。

我们如何修改我们的数据库查询，使其执行我们最初想要的操作，即为我们提供图形文件名而不是 postcards 表中的 ID 号？最简单的方法是在两个表之间设置限制，添加到查询中的 WHERE 子句中，如下所示

mysql> select postcards.sender_name,
        postcards.recipient_name,graphics.graphic_file
        -> from postcards,graphics
        -> where postcards.id_number = 99999
        -> and postcards.graphic_id =
                graphics.graphic_id;
        1 rows in set (0.10 sec)
+--------------+----------------+--------------+
| sender_name  | recipient_name | graphic_file |
+--------------+----------------+--------------+
| Bill Clinton | Al Gore        | foo.gif      |
+--------------+----------------+--------------+

现在更像样了。通过要求两个表中 graphic_id 列之间的等效性，我们检索到的信息就好像它来自单个表一样。这就是 SQL 和关系数据库的部分魔力。通过以这种方式组合表，您可以通过将数据放在单独的表中来使数据更易于处理。但是，当您检索信息时，没有人知道它来自单独的表，因为一个新的临时表被返回给调用者。

这似乎是一个愚蠢的例子，但想象一下一家薪酬等级非常严格的公司，其工资单由关系数据库处理。如果您为每位员工提供一个指示薪资的 ID 号，您可以通过更新薪资表中的记录，而不是员工表中的记录，来给每个人加薪（或降薪，具体取决于公司的财务状况）。下次您在员工表和薪资表之间执行 join 时，新薪资将自动反映出来。

既然我们已经了解了如何在 SQL 级别使查询工作，那么让我们考虑将这些查询集成到某些 CGI 程序中所需的必要步骤。在大多数情况下，我们的 CGI 程序不需要太多更改。我们需要修改 send-postcard.pl，以便它将图形 ID 而不是图形文件名插入到 postcards 表中，并且 show-postcard.pl 需要使用我们在上面制定的 SQL 查询，以便从 graphics 表中获取图形文件名以及 postcards 表中的信息。本月未完全重印代码的修订版本。这两个列表以及打印的列表可通过匿名下载文件 ftp.linuxjournal.com/pub/lj/listings/issue43/2508.tgz 获得。

首先，我们将查看 show-postcard.pl 的修订版本。对上个月打印的列表所做的唯一更改是在 SQL 查询中，该查询现在反映了新表

my $command = "";
$command = "select postcards.sender_name,";
$command .= "postcards.sender_email,";
$command .= "postcards.recipient_name,";
$command .= "graphics.graphic_file,";
$command .= "postcards.postcard_text from ";
$command .= "postcards,graphics ";
$command .= "where id_number = $id";
$command .= "and postcards.graphic_id = ";
$command .= "graphics.graphic_id";

仅此一项更改是必要的，因为我们编写原始版本的 show-postcard.pl 的方式。相比之下，想象一下，如果我们最初将信息存储在单个 ASCII 文本文件中，然后将信息拆分到两个文件中，我们将需要重写多少代码。

我们对 send-postcard.pl 的修改几乎同样容易。我们需要在文件顶部添加 $graphic_id 而不是 $graphic_name 的定义

my $graphic_id = $query->param("graphic_id");

当我们将明信片插入到 postcards 表中时，我们必须修改代码，使其使用 graphic_id 列和变量，而不是 graphic_name

$command = "insert into postcards ";
$command .= " (id_number, sender_name, ";
$command .= " (sender_email, recipient_name, ";
$command .= " recipient_email, graphic_id, ";
$command .= " postcard_text) ";
$command .= "values ";
$command .= " ($id_number, \"$sender_name\", ";
$command .= " \"$sender_email\", ";
$command .= " \"$recipient_name\", ";
$command .= " \"$recipient_email\", ";
$command .= " \"$graphic_id\", ";
$command .= " \"$postcard_text\") ";

此版本的代码仍然存在一个问题。我们网站的访问者应该如何知道或记住可用各种图形的 ID 号？我们可以修改 HTML 表单以提供此信息，但我们这样做似乎有点傻，因为我们每次更新表时都必须更新表单。

最简单的解决方案是编写一个小的 CGI 程序来生成 HTML 表单，并根据需要插入值。有很多不同的方法允许用户选择，但在编写此程序时，我决定采用相对简单的方法，即使用单选按钮。一个更注重美感的程序员（或一个期望有很多图形文件的人）可能会选择一个选择列表，但这只是一个次要问题。结果程序 postcard-form.pl 显示在 列表 1 中。

关于我们的明信片发送问题就到此为止了。当然，还有许多其他方法可以扩展或修改这组程序。例如，创建一个 CGI 程序来允许我们输入和编辑 graphics 表中的文件名可能是一个好主意，这样我们就无需使用交互式 mysql 程序进行此类修改。目前，只有精通 SQL 的人才能在 graphics 表中添加、修改和删除元素。我们还可以确保 graphics 表中的 ID 号是按顺序给出的；一些关系数据库供应商提供该功能，允许使用“标识”列，这些列在添加新行时会自动递增。

允许用户预览他们放置在明信片上的图形，或者至少描述图片而不是仅仅向用户展示文件名，这也将是一件好事。此选项可能需要存储每个图形的两个版本，或者向 graphics 表添加另一列，用于描述或预览。

正如您所见，可能性是无限的——而这只是一个相对较小的项目。

本文结束了我们对 SQL 的旋风式游览，尽管未来的专栏无疑将继续使用关系数据库作为存储信息的一种方式。不过，下个月，我们将研究 CGI 程序的效率，包括 Perl 的“CGI lite”模块。

资源