8.4　CD数据库应用程序

现在，你将看到如何创建一个简单的数据库来保存CD唱片的信息，然后编写一些代码来访问这些数据。为尽量保持代码的简单，使其易于理解，你将仅仅使用3个数据库表，而且它们之间的关系也非常简单。

首先，创建一个新的数据库，然后将其作为当前的数据库：

现在，你已准备好设计和创建你需要的表了。

这个例子会比以前的稍微复杂一点，因为你将把CD唱片分成3个不同的元素：艺术家（或组合）、主标题和曲目。如果考虑到一套CD收藏以及它的组成元素，你会意识到每张CD都由不同的曲目组成，但不同CD之间又在许多方面相互关联：通过艺术家或组合、通过制作公司、通过音乐表现风格等。

如果试图以一种灵活的方式来保存所有这些不同的元素，你的数据库将变得相当复杂，但在本例中，你将仅限于使用两种最重要的关系。

首先，每张CD由不同数目的曲目组成，所以你将把曲目数据储存在一个独立于其他CD数据的表中。其次，每位艺术家（或乐队）经常会有多张专辑，所以只将艺术家的信息存储一次，然后单独提取属于该艺术家的所有CD是非常有用的。我们不会尝试将乐队拆分成不同的艺术家（乐队的每个成员可能都有属于自己的专辑）或处理合集CD——这是为了尽量保持例子的简单！

同样，你也需要保持关系的简单——每个艺术家（也可能是乐队名称）可能制作一张或多张CD，每张CD包含一个或多个曲目。这种关系如图8-8所示。

图　8-8

8.4.1　创建表

现在，你需要确定表的实际结构。我们从主表——CD表开始，它保存大部分的信息。你需要保存一个CD ID、一个分类号、一个标题以及一些你自己的标注。你还需要一个来自artist表的ID号来表明是哪位艺术家制作了这张专辑。

artist表很简单，它仅仅保存艺术家的名字和一个唯一的艺术家ID号。track表也很简单，你只需要一个CD ID来表明曲目属于哪张CD、一个曲目号和一个曲目标题。

首先是CD表：

这创建了表cd，它包含下面一些列。

❑　id列，包含一个自动增加的整数，它是表的主键。

❑　最长为70个字符的title。

❑　artist_id，在artist表中使用的一个整数。

❑　最长为30个字符的catalogue号。

❑　最长为100个字符的notes。

注意，只有notes列可以为NULL，所有其他的列都必须含有值。

下面是artist表：

你又有了一个id列和一个艺术家name列。

最后是track表：

注意，这次你用不同的方法来声明主键。track表的不寻常之处在于每张CD的ID会出现多次，而对于任何指定曲目的ID，例如曲目1，也会在不同的CD中出现多次。但是，这两者的结合将永远是唯一的，所以我们将主键声明为这两列的结合。这被称为是联合键，因为它由多列联合组成。

将这些SQL语句存储在文件create_table.sql中，并将该文件保存在当前目录中，然后开始创建数据库及其中的表。当这些表已存在时，我们提供的脚本样例还包含额外的命令用于丢弃这些表，但默认情况下，这些命令是被注释掉的。

注意我们使用.命令将create_tables.sql文件作为输入。

你也可以使用MySQL查询浏览器（MySQL Query Browser），通过执行SQL或简单地输入数据来创建表。

一旦创建好表，你就可以通过MySQL管理器（MySQL Administrator）来查看它，如图8-9所示。在图中，你正在检查blpcd数据库的indices标签（或schema，这取决于你的首选术语）。

图　8-9

你可以通过选择编辑表（在Tables标签中右键单击或双击表名）看到列的详细信息。如图8-10所示。

图　8-10

你注意到图8-10中针对cd_id列和track_id列的两个关键符号了吗？它表示这两个列都属于联合主键。曲目标题可以为NULL（注意NOT NULL并没有被选中）表示我们允许CD曲目没有标题，这种情况虽然少见，但并非不会出现。

8.4.2　添加数据

现在，你需要添加一些数据。最好的检查数据库设计的方法是，添加一些样本数据并检查它们是否都能正常工作。

我们在这里将仅仅展示一个测试输入数据的例子，因为所有的输入都基本相似——它们仅仅是加载不同的表，所以它并不是理解发生何事的关键。下面有两个要点需要注意。

❑　这个脚本将删除任何已有的数据以确保脚本是干净的。

❑　在ID字段中插入数值，而不是让AUTO_INCREMENT来自动分配。在这里这样做会更安全，因为不同的插入操作需要知道哪些值已被使用以确保数据关系是完全正确的，因此最好强制指定数值，而不是允许AUTO_INCREMENT函数来自动分配数值。

这个文件叫做insert_data.sql，它可以使用你前面见到的.命令来执行。

接着是专辑中剩下的曲目，然后是下一张专辑：

等等：

直到最后的曲目：

接着将它保存为pop_tables.sql，并像前面那样在mysql提示符下用.命令执行它。

注意在cd 5 (I Giorni)曲目3中，曲目In un'altra vita中有撇号。为了将其插入到数据库中，你必须用反斜杠（\）来引用撇号。

现在是时候检查你的数据是否合理了。你可以使用mysql命令行客户端和一些SQL语句来进行检查。首先，从数据库中选出每张专辑的头两首曲目：

如果在MySQL查询浏览器中尝试这个SQL语句，你可以看到提取出的数据很好，如图8-11所示。

图　8-11

这个SQL语句看起来很复杂，但是如果你将该语句分解开来看，它就不是那么难理解了。

先忽略SELECT命令中的AS部分，第一部分仅仅是：

它只是通过使用标记tablename.column来说明你想要显示哪些列。

SELECT语句的AS部分SELECT artist.name, cd.title AS "CD Title", track.track_id和track.title AS "Track"只是在输出中重命名列名。因此，来自cd表的title列（cd.title）的标题栏被命名为"CD Title"，track.title列被命名为"Track"。AS的使用给了我们更友好的输出，它是在命令行中针对SQL语句的一个有用的字句，但当你通过其他编程语言来调用SQL语句时，你几乎不会用到它。

接下来的部分也非常地简单易懂，它告诉服务器你使用的表名：

WHERE子句是需要点技巧的部分：

第一部分告诉服务器artist表中的ID应与cd表中的artist_id相同。记住，你仅仅保存了一次艺术家的名字并在CD表中使用ID来引用它。下一部分，track.cd_id = cd.id，为表track和cd做同样的事情，即告诉服务器track表的cd_id列应与cd表中的id列相同。第三部分，track.track_id<3，减少了返回数据的数量以使得你仅仅从每张CD中得到曲目1和曲目2。最后，你使用AND把3个条件结合起来，因为你想让这3个条件同时都为真。

8.4.3　使用C语言访问数据

我们并不准备在本章中编写一个带有GUI的完整的应用程序，而是专心于编写一个接口文件，从而允许你以一种合理而又简单的方式通过C语言来访问数据。编写这类代码的一个常见问题是无法知道返回的结果数，以及如何在客户端代码和访问数据库的代码间传递这些结果。在这个应用程序中，为了保持简单并专注于数据库接口（这是代码中的重要部分），我们将使用固定大小的结构。但在实际的程序中，这可能是不能接受的。一种常见的解决方法（它同时也有助于减少网络流量）是每次总是提取一行数据，正如你在本章前面看到的函数mysql_use_result和mysql_fetch_row一样。

1．接口定义

我们先从头文件app_mysql.h开始，它定义了结构和函数：

首先是一些结构：

然后是一对函数，它们用于连接数据库以及从数据库断开连接：

现在，我们转向操纵数据的函数。注意，没有创建或删除艺术家的函数。你将在后台实现它，根据需要创建艺术家条目，然后当它们不再被任何专辑使用的时候将其删除。

搜索函数相当通用：你传递一个字符串，然后它将在artist、title或catalogue条目中搜索该字符串。

2．测试应用程序接口

在实现接口之前，你将编写一些代码来使用它。这看起来可能有点奇怪，但在开始实现接口之前了解一下它将如何运转通常是个好方法。

下面是app_test.c的源代码。首先是一些includes和structs：

应用程序要做的第一件事始终是，初始化一个数据库连接并提供一个正确的用户名和密码（一定要用自己的用户名和密码）：

然后，测试添加一张CD：

现在，搜索CD，并从找到的第一张CD中提取信息：

最后，删除CD：

然后断开连接并退出：

3．实现接口

现在是最困难的部分：实现你指定的接口。这些都包含在文件app_mysql.c中。

首先是一些基本的includes、你需要的全局连接结构和一个标志dbconnected，你将使用它来确保程序不会在没有建立连接的情况下尝试访问数据。你还使用一个内部函数get_artist_id来改善代码的结构。

连接到一个数据库是非常简单的，就像你在本章前面看到的那样。断开连接就更加简单了：

现在通过函数add_cd开始真正的工作。首先需要给出一些声明和进行健全性检查以确保你已连接到了数据库。你将在所有编写的可外部访问的函数中看到这一切。

记住，我们说过代码将自动关注艺术家的名字：

下一步是检查艺术家是否已经存在，如果不存在，你就创建一个。这些都由函数get_artist_id来实现，你将在稍后看到该函数。

在有了一个artist_id之后，你可以插入主CD记录了。注意，我们使用mysql_escape_string来保护CD标题中的任何特殊字符。

当你为此CD添加曲目时，你需要知道插入CD记录时使用的ID。你把此列设置为自动增加列，因此数据库会自动分配ID，但是你需要明确地提取数值。这可以通过使用你在本章前面见到的特殊函数LAST_INSERT_ID来完成。

你不必担心其他客户端同时插入CD时会导致ID混乱，MySQL会基于每个客户的连接来跟踪分配的ID，所以即使你在提取ID之前有另一个程序插入了一张CD，你仍然可以得到对应于你的行的ID，而不是由其他程序插入的行所对应的ID。

最后，设置新加入行的ID并返回成功或失败：

现在，让我们看一下get_artist_id的实现，其过程跟插入CD记录非常相似：

现在，继续添加CD的曲目信息。你仍然需要保护曲目标题中的特殊字符：

现在，根据给定的CD的ID值来提取CD信息。你将使用一个数据库联合在提取CD信息的同时提取艺术家的ID。这是很好的练习：数据库擅长于了解如何高效地执行复杂查询，所以如果一个任务可以仅仅通过SQL语句就能让数据库来完成，就决不要自己来编写程序代码。这样不仅可以节省自己的精力，不必编写额外的代码，而且通过让数据库尽可能多地完成复杂工作，也可以提高程序的执行效率。

接下来，你要实现曲目信息的提取。注意，你通过SQL语句中指定一个ORDER BY子句来确保曲目以一个有意义的顺序返回。而且，由数据库来完成这些工作将比我们以任意顺序提取数据，并自己编写代码来排序更有效率。

至此，你已添加并提取了CD的相关信息，现在是时候搜索CD了。你通过限制返回结果的数目来保持接口的简单，但是你仍然想让函数告诉你共有多少行，即使这多于你能够提取的结果数。

现在，清空结果结构并保护查询字符串中的特殊字符：

接着，你构造一个查询字符串。注意它需要使用相当多的%字符，因为%既是SQL语句中用来匹配任何字符串的字符，也是sprintf中的一个特殊字符。

现在，你可以执行查询了：

最后，你将实现删除CD的方法。为了符合我们默默地管理艺术家条目的策略，当删除一张CD时，如果没有其他CD包含同一个艺术家字符串，你将删除这张CD对应的艺术家。奇怪的是，SQL没有一次从多个表中删除数据的方法，所以你必须依次从每个表中删除数据。

这完成了所有的代码。

考虑到完整性，我们添加一个makefile文件来使你的工作更为轻松。你可能需要根据MySQL安装的情况来调整include路径。

在后面的章节中，你将看到这个接口被用于真正的GUI。至于现在，如果你想观察执行代码所引起的数据库改变，我们建议你在一个窗口中运行gdb调试器来单步运行代码，同时在另一个窗口中观察数据库数据的变化。如果使用MySQL查询浏览器，请记住你需要刷新数据显示才能看到数据的变化。