CSDN博客

img taowen2002

一次编写,随处运行

发表于2004/2/4 10:00:00  1959人阅读

关于 PHP 的数据库 API 对于不同的数据库有不同的函数,一直以来就有人尝试使用 PHP 的面向对象的功能进行一些封装。其中有比较著名的ADODB,PHPLIB。后来举世瞩目的 PEAR 项目中的 PEAR DB 更是其中的佼佼者。这些用面向对象对数据库 API 进行的封装的包一般称为数据库抽象层。

本文介绍的是 PEAR 中对 PEAR DB 进行融合 Metabase 库的一些优秀功能之后产生的注重效率,而且简单易用,功能非常强大的 MDB 的一个非常好的介绍。作者就是 MDB 的主创人员。

想获得我最近关注的 PHP/PEAR 的最新原创和译文,请访问我的主页 

Write once - run anywhere
一次编写——随处运行

PEAR MDB Database Abstraction Layer
PEAR MDB 数据库抽象层

作者:Lukas Smith
译者:taowen

While this is a Java marketing phrase it is also a key feature of PHP. Many business models depend on operation system independence to ensure that products can be sold to a wide range of customers. So why lock yourself in with a specific database vendor? Database abstraction layers allow you to develop your application independent of a database. But often they eat more performance than you are willing to give or they do not abstract enough to eliminate all database specific code.
这是Java的一句行销口号,但是它同时也是PHP的关键特性之一。许多商业模型依赖于操作系统无关性来保证产品能够销售给广泛的客户群体。因而,为什么要把你自己绑在某种数据库厂商的身上呢?数据库抽象层使得你能够与数据库独立的开发你的应用程序。但是,通常情况下它们对性能的影响超过了你所希望的,要么他们并不足够抽象以消除所有和特定数据库相关的代码。

What will this article teach me?
这篇文章将教给我什么?

This article will give a good introduction to the database abstraction package PEAR MDB. The focus will be explaining the more advanced features of MDB like data type abstraction and the XML based schema management that go beyond what other similar packages offer. A basic level of understanding of PHP and SQL is recommended.
这篇文章将对数据库抽象包 PEAR MDB 有一个很好的介绍。文章的焦点将是对 MDB 超越类似包所提供的更先进的特性,例如数据类型抽象和基于 XML 的 schema 管理。对 PHP 和 SQL 的基本理解是推荐的。

Why another database class?
为什么另外再要一个数据库类?

Often, web projects are added to existing IT infrastructures, where the client already made a choice of what RDBMS (relational database management system) to work with. Even if that is not the case different budgets might affect what database you chose for deployment. Finally, you as the developer simply might prefer not to lock yourself in with a specific vendor. So far this meant to keep multiple versions for each supported database or giving up more performance and ease of use than necessary: Enter PEAR MDB.
通常, web 工程在客户已经确定了要使用那种 RDBMS (关系型数据库管理系统)之后被添加给已经存在的 IT 基础结构。即使那并不是因为不同的预算可能影响的你选择何种数据用于部署的情况。最终,你作为开发者可能简单的偏好于不把自己绑在某个厂商身上。自此,意味着给每个支持的数据保持版本或者牺牲更多性能但是获得多于必须的易用性:走入 PEAR MDB 吧。

MDB is a database abstraction layer that aims to make writing RDBMS independent PHP application development a straightforward process. Most other so called database abstraction layers for PHP only provide a common API for all supported databases and only very limited abstraction (mostly only for sequences). MDB on the other hand can be used to abstract all data being send and received from the database. Even database schemas can be defined in a RDBMS independent format. But it does this while retaining a high level of performance and ease of use. This was achieved by closely examining two popular database abstraction layers, PEAR DB and Metabase, and merging them. But during the merging the opportunity was also used to clean up their merged APIs as well as any performance hindering design patterns.
MDB 是着眼于使得编写 RDBMS 无关的 PHP 程序成为简单的过程的数据库抽象层。大部分其他的 PHP 的所谓数据库抽象层紧紧给所有支持的数据库提供了一个公用 API 以及非常有限的抽象(大部分只是针对序列的)。MDB 另一方面能够用来抽象所有数据库发送和接收的数据。甚至数据库 schema 都能被定义为 RDBMS 无关的格式。但是它提供这些功能的同时仍然保持了很高的性能以及简单易用。这是通过深入观察两个流行的数据库抽象层,PEAR DB 和 Metabase, 之后并且对它们进行了融合后获得的。而且在融合过程中,趁着这个机会清理了它们融合后的 API 以及任何影响性能的设计。

How did MDB come to be?
MDB 是怎样出现的?

Back in fall 2001, I was looking for a database abstraction package that would make my companies application framework RDBMS independent. The goal was to reduce database specific code to zero. The only package I found that offered such features was Metabase. But Metabase had a somewhat uncomfortable API that was partly a result of the compatibility to PHP3. This also made Metabase slower than it needed to be for our purposes, since we did not need PHP3 compatibility. Nonetheless, we decided that Metabase is our only option. But even after adding a performance enhancing patch to Metabase we felt that we were giving up too much performance. We met with the author of Metabase at the International PHP Conference 2001 and we talked about the benefits of having something like Metabase as part of the PEAR project. Shortly afterwards a discussion began once more in the PEAR mailing list about the potential benefits of a merge of PEAR DB and Metabase. After much discussion at my company we decided to take up this task. After several months of hard work we now have the first stable release of MDB.
早在 2001 年的秋天,我就在寻找一种可能能够让我公司的程序框架与 RDBMS 独立的数据库抽象包。这个目标是把特定数据库相关的代码数量减少到零。我发现提供这样的功能的唯一的一个包是 Metabase。但是 Metabase有一些部分是因为为了和 PHP3 兼容的让人不舒服的 API。尽管如此,我们决定 Metabase 是我们唯一的选择。但是即使是在给 Metabase 增加了一个性能改进的补丁之后,我们仍然感到我们放弃了太多的性能。我们在 2001 年的 PHP 国际会议上碰到了 Metabase 的作者,并且我们谈论了让像 Metabase 这样的东西成为 PEAR 工程一部分的好处。后来不久,在 PEAR 邮件列表上就 PEAR DB 和 Metabase 融合的可能的好处又开始了一场讨论。在我们公司进行了许多讨论之后,我们决定承担这个任务。数个月的艰辛工作之后,我们现在有了 MDB 的第一个稳定的 release。

What does MDB do for you?
MDB 给你提供了什么?

MDB combines most of the features of PEAR DB and Metabase. Actually, the only feature that is missing is PEAR DB's feature of returning an object as a result set. This feature was dropped because the feature's usage never became abundant but the performance penalty was quite apparent. A lot of development time was spend on making the API as intuitive as possible as well. Finally, MDB provides this functionality at a very high level of performance that is at least as fast as PEAR DB and much faster than Metabase. Here is the list of the most important features:
MDB 结合了 PEAR DB 和 Metabase 的大部分特性。实际上,PEAR DB 的特性中唯一不再存在的是作为结果集返回一个对象。我们放弃了这个特性是因为这个特性不常用而且对于性能的损失是非常明显的。许多开发上的时间用在了使得 API 尽可能的好用。最终,MDB 非常高地提供了这些功能至少和 PEAR DB 一样快而且比 Metabase 快很多。这些最重要地特性的列表:

OO-style API
prepared queries emulation
full data type abstraction for all data passed to and from the database (including LOB support)
transaction support
database/table/index/sequence creation/dropping/altering
RDBMS independent database schema management
Integrated into the PEAR framework (PEAR Installer, PEAR error handling etc.)

OO 风格的 API
预准备的查询模拟
给所有传递进来以及从数据库中取出的数据的完全的数据类型抽象(包括 LOB 支持)
事务支持
数据库/表/索引/序列创建/抛弃/改变
RDBMS 无关的数据库 schema 管理
继承进 PEAR 框架(PEAR 安装程序,PEAR 错误处理等)

So how does it work?
那么它如何使用呢?

MDB provides some very advanced abstraction features. It is important to keep in mind that these features are optional. But using them is critical in writing RDBMS independent PHP applications. An example of how the basics of MDB work can be found under Links & Literature at the end of the article. As stated earlier, the focus of the article is to introduce the features that set MDB apart from other database abstraction layers for PHP. You can find example scripts for all code examples found in this article on the CD that is packaged with this issue.
MDB 提供了一些非常先进的抽象特性。记住这些特性只是供选择的是很重要的。但是在编写 RDBMS 无关的 PHP 程序时使用它们是非常重要的。一个展示使用 MDB 是多么简单的例子在文章的结尾的 "链接和文献" 部分。如前面所说,文章的焦点是介绍使得 MDB 与其他 PHP 数据库抽象层不同的那些特性。你可以在随本期文章一同包装的 CD 中找到所有这些例子脚本的代码。

But first we will need to get MDB installed. This is actually quite easy using the PEAR installer. I cannot cover the entire PEAR Installer within this article but I hear the next issue will talk about great details about all the ins and outs of the PEAR framework. There is work going on to make the Installer work on Windows but the support is still a bit flaky. For *nix systems you will need a CGI version of PHP installed on your system and simply run the following command:
但是,首先我们需要把 MDB 安装上去。使用 PEAR 安装程序这其实非常容易。我不能在这篇文章中完整的讲述 PEAR 安装程序但是我听说下一期将非常详细的讨论 PEAR 框架的里里外外。让安装程序运行于 Windows 的工作在进行当作但是支持仍然有一点古怪。对于 *nix 系统你需要 PHP 的 CGI 版本安装在了你的系统并且简单地运行下面地命令:

lynx -source go-pear.org|php

After completing the installation process you simply need to type one more command and you are all set.
在安装完成之后你只需要再输入一行命令那么就全部搞定了。

pear install MDB

If the above does not work for you there is always the option of getting the package directly from the PEAR MDB homepage. The URL is listed at the bottom of the article.
如果前面的过程对你来说不管用,总是有从 PEAR MDB 主页中直接获得包的选项。URL 列于文章的最后。

Making use of data type abstraction
利用数据类型抽象

Since most databases tend to have some specialities or quirks it is important for MDB to hide these differences from the developer. MDB achieves this by defining its own internal data types: text, boolean, integer, decimal, float, date, time, time stamp, large objects (files). All data that is passed to and from the database may be converted from MDB's internal format to the databases internal format. The accompanying example scripts to this section can be found in the datatype directory. Let us look at the following query:
因为大部分数据库倾向于有一些个性或者怪癖,对于MDB来说把这些不同之处给开发者隐藏起来非常重要。MDB 通过定义自己的内部数据类型来达到这点:text,boolean,integer,decimal,float,date,time,time stamp,large objects(文件)。所有传递给数据库和从数据库获取的数据都能转换成 MDB 的内部格式或者从数据库的内部格式转化回来。本节相关的例子脚本能够再 datatype 目录中找到。让我们看看下面的查询:

$session = '098f6bcd4621d373cade4e832627b4f6';
// set time out to 30 minutes
$timeout = time()+60*30;
// SELECT query showing how the datatype conversion works
$query = 'SELECT createtime, user_id FROM sessions';
$query .= ' WHERE session = '.$session;
$query .= ' AND lastaccess < '.$timeout;

This query will most likely fail if it were send to a database. The reason being that the value stored in $name would need to be converted to the correct string format. This would mean the contents of $name would have to have special characters escaped and quotes placed around. PEAR DB provides the method DB:.quote() for this. In MDB the method is called MDB::getTextValue(). The difference is that MDB offers such a method for every data type listed above. So we can also convert $timeout to the correct format.
这个查询如果发送给数据库的话八成要失败。原因是存储在 $name 中的值需要转换为正确的字符串格式。这也许意味着 $name 的内容可能有特殊的转义字符或者被引号包围。PEAR DB 为此提供了方法 DB:.quote()。在 MDB 中这个方法叫 MDB::getTextValue()。不同之处是 MDB 给每种前面所列的数据类型都提供了这样的函数。因而我们也能够把 $timeout 转换为正确的格式。

// convert $timeout to the MDB timestamp format
$timeout = MDB_date::unix2Mdbstamp($timeout);
// SELECT query showing how the datatype conversion works
$query = 'SELECT createtime, user_id FROM sessions';
$query .= ' WHERE session = '.$mdb->getTextValue($session);
$query .= ' AND lastaccess < '.$mdb->getTimestampValue($timeout);

For the sake of the example let us assume that we only want to retrieve the first row. MDB::queryRow() fetches the first row, he frees the result set and returns the content, so it is exactly what we want.
为了作个演示,让我们假定我仅仅想要获取第一行。MDB::queryRow() 获得第一行,它释放结果集并且返回其内容,因而它正是我们所要的。

$result = $mdb->queryRow($query);

But different RDBMS return data like dates in different formats. So, if I then want to do some date arithmetic it is important that data is always returned in the same format regardless of the RDBMS chosen. This can be done semi-automatically by MDB. All you need to do is tell MDB what type your result columns will have and MDB handles the conversion. The easiest way is to pass such information with the query method call:
但是不同的 RDBMS 返回像日期这样的数据时用的格式是不同的。因此,如果我们然后要对一些数据进行计算,不管选择的 RDBMS 是什么,把数据以相同的格式返回是重要的。这个可以由 MDB 半自动地完成。你所有需要做的是告诉你的结果列将是什么样的类型,MDB将处理转换的工作。最简单的办法是把这样的信息传递给查询函数。

$types = array('timestamp', 'integer');
$result = $mdb->queryRow($query, $types);

This tells MDB that the first column of the result set is of the type `timestamp' and the second is of the type `integer'. All methods that allow querying can take such meta-information as an optional parameter. The data can also be set later using MDB::setResultTypes(). Depending on the database that the data is retrieved from, it will then convert the returned data accordingly. The MDB internal data format for timestamps is the ISO 8601 standard. Other packages such as PEAR::Date can handle this format. MDB also provides a small number of methods for date format conversion in the MDB_Date class that can be included optionally.
这告诉 MDB 结果集的第一列类型是 'timestamp' 以及第二列是'integer'。所有查询函数能够接受这样的元信息作为可选的参数。数据还能事后用 MDB::setResultTypes() 来设置。取决于数据获取于的数据库,它然后将被相应的转换返回的数据。MDB 内部的 timestamps 的数据格式是遵循 ISO 8601 标准的。其他像 PEAR::Date 这样的包能够处理这种格式。MDB 还在 MDB_Date 类中提供了一些数据格式转换函数,它们能够被可选的包含。

Since pretty much every RDBMS returns integer data the same way there is no need to convert integer data. So, in order to gain a slight performance improvement you could do the following:
因为相当多的 RDBMS 以相同的方法返回整数数据,没有必要转换整数数据。因而,为了获得稍许的性能改进你能够这么做:

$types = array('timestamp');
$result = $mdb->queryRow($query, $types);

This way only the first column of the result set would be converted. Of course this may become an issue if MDB would be used in conjunction with a database that does return integers differently. However unlikely, the slight performance increase might not be worth this risk. But again it shows that the usage of these features is optional.
这样只有结果集的第一列会被转换。当然,如果 MDB 用于返回整数不同的数据库,这可能成为一个问题。然而,稍许的性能改善可能并不值得冒这个风险。但是再一次的,它显示了这些特性的使用仅仅是供选择的。

Listing 1 shows an example use of prepared queries. These can be quite convenient if you have to run a number of queries where the only difference is in the data that is being passed to the database while the structure of the query remains the same. Advanced databases can store the parsed query in memory to offer a performance boost.
Listing 1 展示了一个使用预准备的查询的例子。如果你必须运行大量查询而唯一的差别是数据传递给数据库,但是查询的结构还是一样的,这些能够相当的方便。高级的数据库能够在内存中储存解析好的查询来加速性能。

Listing 1

$alldata = array(
array(1, 'one', 'un'),
array(2, 'two', 'deux'),
array(3, 'three', 'trois'),
array(4, 'four', 'quatre')
);

$p_query = $mdb->prepareQuery('INSERT INTO numbers VALUES (?,?,?)');
$param_types = array('integer', 'text', 'text');

foreach ($alldata as $row) {
$mdb->execute($p_query, NULL, $row, $param_types);
}

Each of the 4 arrays that are stored in $alldata will be used in an execute statement. The data will automatically be converted to the correct format. Since this is an insert statement the second parameter for MDB::execute() is set to NULL because we will not have any result columns for which we would need to set data types.
在 $alldata 中储存的所有四个数组将用于 execute 语句。数据将自动被转换为正确的格式。因为这是一个插入语句,MDB::execute() 的第二个参数被设置为 NULL 因为我们将没有任何结果列需要我们设置数据类型。

Among the supported data type are also LOB's (Large OBjects) which allow you to store files into a database. Binary files are stored in BLOBs (Binary Large OBject) and normal text files are stored on CLOBs (Character Large OBject). In MDB you can only store LOB's using prepared INSERT and UPDATE queries. Using either MDB::setParamBlob() or MDB::setParamClob() you can set the values of the LOB field in a prepared query. Both methods expect to be passed a LOB object however which can be created using MDB::createLob().
在支持的数据类型中还有 LOB (大对象),它使得我们能够在数据库中储存文件。二进制文件储存在 BLOB (二进制大对象)中而且普通文本文件储存在 CLOB (字符大对象)中。在 MDB 中你仅仅能够使用预准备的 INSERT 和 UPDATE 查询储存 LOB。使用 MDBA::setParamBlob() 或者 MDB::setParamClob() 你能够设置预准备查询的 LOB 域的值。两个函数都预期传递一个 LOB 对象,而它能够使用 MDB::createLob() 创建。

$binary_lob = array(
'Type' => 'inputfile',
'FileName' => './myfile.gif'
);
$blob = $mdb->createLob($binary_lob);

$character_lob = array(
'Type' => 'data',
'Data' => 'this would be a very long string container the CLOB data'
);
$clob = $mdb->createLob($character_lob);

As you can see MDB::createLob() is passed an associative array. The value for the Type key may be one of the following: data, inputfile or outputfile. The first two are used when you want to write a LOB into the database. If you have the LOB stored in a variable you should use data while inputfile should be used to read the LOB directly from a file. Finally, outputfile should be used when you want to retrieve a LOB from the database. Depending on if you are using data or inputfile you need to specify a value for the Filename key or the Data key as seen in the above example. Now, we will store the above LOB's in the database.
如你能看到的,MDB::createLob() 被传递一个关系数组。Type 键的值可能是以下中的一个:data, inputfile 或者 outputfile。前两个用于你想要把 LOB 写入数据库的时候。如果你有一个储存在变量中的 LOB,你应当在 需要使用 inputfile 时从文件直接读取 LOB。最后,outpufile 应当在你想要从数据库中读取 LOB 时使用。取决于你是否使用数据或者 inputfile 你需要给 Filename 键或者 Data 键指定一个值,像上面的例子那样。现在,我们将把前面的 LOB 储存到数据库中去。

$p_query = $mdb->prepareQuery('INSERT INTO files (id, b_data, c_data) VALUES (1, ?, ?)');

$mdb->setParamBlob($p_query, 1 , $blob, 'b_data');
$mdb->setParamClob($p_query, 2 , $clob, 'c_data');

$result = $mdb->executeQuery($p_query);

In order to fetch the above file from the database we will need to first select the data from the database and create a LOB object using MDB::createLob(). This time we will set `Type' to `outputfile'.
为了从数据库中获取上面的文件,我们需要首先从数据库中选择数据并且使用 MDB::createLob() 创建 LOB 对象。这次我们将设置 'Type' 为 'outputfile'

$mdb->query('SELECT b_data FROM files WHERE id = 1');

$binary_lob = array(
'Type' => 'outputfile',
'Result' => $result,
'Row' => 0,
'Field' => 'b_data',
'Binary' => 1,
'FileName' => './myfile2.gif'
);
$blob = $mdb->createLob($binary_lob);

Now we can read the LOB from the result set using MDB::readLob(). Passing a length of 0 to MDB::readLob() means that the entire LOB is read and stored in the file we specified above. Once we are done we can free the resources. Alternatively, you can set any length larger than zero and read the LOB using a while loop checking MDB::endofLob().
现在我们能够使用 MDB::readLob() 从结果集中读取 LOB。传递长度 0 给 MDB::readLob() 意味着整个 LOB 被读取和储存在我们前面指定的文件中。一旦任务完成了,我们可以把资源释放了。你也可以设置任何大于零的长度并且使用一个 while 循环检查 MDB::endofLob() 来读取 LOB。

$mdb->readLob($blob, $data, 0);

It is important to note that you may not mix this method of fetching with the bulk fetching methods like MDB::fetchAll() as this will cause problems in most PHP database extensions. At some point MDB may be able to retrieve LOB's using the bulk fetching methods.
注意你不要把这个获取函数和 bulk 获取函数像 MDB::fetchAll()搞混了,因为这将在大部分 PHP 数据库扩展中导致问题。在一些时候,MDB 可能能够使用 bulk 获取函数获得 LOB。

As we have seen in this section MDB features its own set of native data types that are automatically mapped to native data types in the database. This ensures that no matter what data we send or retrieve from the database it will always be in the same format no matter what RDBMS is used. As I have mentioned in the opening paragraph of this section this obviously requires that the data types used in the database are what MDB expects. This requirement was made to ensure that the mapping is done with a minimal performance loss. The next section will teach us how MDB assists with using the correct data types in the database.
如我们在这节所见,MDB 特性本身的原生数据类型集自动映射于数据库中的原生数据类型。这保证了无论我们发送和从数据库接收什么样的数据,它都能与使用的 RDBMS 无关的使用相同的格式。如我在本节开篇已经提到的,这明显需要数据库使用的数据类型是 MDB 预期的。这种需要被用于确保映射所耗费的代价很小。下一节将教给我们 MDB 如何辅助在数据库中使用正确的数据类型。

Making use of XML schema files
使用 XML schema 文件

With the features described in the last paragraph you can write truly database independent applications. But MDB tries to go one step further: It allows you to define your schemas in XML. A manager converts this schema into the necessary SQL statements for each RDBMS. This means that you can use the same schema for any of the supported RDBMS. The examples for this section can be found in the xml_schema directory.
利用在上个段落中描述的特性,你能编写真正的数据库独立的程序。但是 MDB 尝试向前更加迈出一步:它允许你用 XML 定义你的 schema。一个管理器把这种 schema 转换为给每种 RDBMS 的必要的 SQL 语句。这意味着你能对所有支持的 RDBMS 使用相同的 schema。本节的例子能够在 xml_schema 目录中找到。

We will now write an XML schema file from scratch. First we must define an XML document. The database definition is contained within a database tag. The name of the database is defined using the name tag. The create tag tells the manager if the database should be created if it does not yet exist. If you split up your schema into several files you will only need to set create to 1 in the file you will submit first to the manager.
我们现在将从头编写一个 XML schema 文件。首先,我们必须定义一个 XML 文档。数据库定义是包含在一个 database 标签之中的。数据库的名字是使用 name 标签定义的。create 标签告诉管理器数据库是否需要在它不存在的时候被创建。如果你把你的 schema 文件分割成好几个文件你你首先提交给管理器的那个文件中把 create 设置为 1。

<?xml version="1.0" encoding="ISO-8859-1" ?>
<database>
<name>auth</name>
<create>1</create>
</database>

As you may have guessed from the database name auth the purpose of this database is to store user data for a simple authentication application. Listing 2 defines a table in which we can store the user data.
可能你已经从数据库名 auth 猜出了这个数据库的目的是用于储存简单的验证程序的用户数据。Listing 2 定义了在其中我们能储存用户数据的表。

Listing 2

<table>
<name>users</name>
<declaration>
<field>
<name>user_id</name>
<type>integer</type>
<notnull>1</notnull>
<unsigned>1</unsigned>
<default>0</default>
</field>
<field>
<name>handle</name>
<type>text</type>
<length>20</length>
<notnull>1</notnull>
<default></default>
</field>
<field>
<name>is_active</name>
<type>boolean</type>
<notnull>1</notnull>
<default>N</default>
</field>
</declaration>
</table>


As you can see, things can get a bit lengthy here as to be expected when using XML. No need to worry: We are working on a browser based tool called MDB_frontend that will make this process much easier. I will talk about this project further down into this article a bit more. Hopefully, the advantage of this pretty verbose representation of the table is that things are somewhat self explanatory. The table in the last example is called users and we have defined 3 fields: user_id of type integer, handle of type text and is_active of type boolean. Remember that MDB handles the type abstraction for you if you pass it the necessary metadata as shown in the previous section. You also need not to worry about what MDB maps these types to in your RDBMS. The other tags you can use in each of the field declarations are optional: length, notnull, unsigned and default.
如你能看到的,如使用 XML 时可以预期的,东西变得有一些冗长。不用担心:我们有一个基于浏览器的工具称为 MDB_frontend 使得这个过程更加简单。我将在这篇文章的后面谈论这个工程。可能这极其详细地表格描述的优点是非常明显。前面例子中的表格被称为 users 并且我们定义了 3 个域:类型为整数的 user_id,类型为文本的 handle 和类型为逻辑型的 is_active。记住如果你如前一节那样传递了必要的元数据 MDB 为你处理类型抽象。你还不需要 MDB 把这些类型映射为你的 RDBMS 中的什么。在每个域声明中还能使用的其他标签是可选的:length,notnull,unsigned 和 default。

The next thing that we now need to do is to ensure that the user_id is unique by placing the proper index on the user_id field. The index definition goes within the declaration tag (Listing 3).
下一件我们现在需要做的事情是通过在 user_id 域放置恰当的索引确保 user_id 是唯一的。索引定义就在声明标签之内(Listing 3)。

Listing 3:

<table>
<name>users</name>
<declaration>
<index>
<unique>1</unique>
<name>user_id_index</name>
<field>
<name>user_id</name>
<sorting>ascending</sorting>
</field>
</index>
</declaration>
</table>

The definition in listing 3 would create a unique ascending index named user_id_index on the field user_id. Of course, we could have specified more than one field in the index definition by simply adding another field tag. What we are still missing now is a sequence to generate unique user id's for us:
在 listing 3 中的定义在域 user_id 中创建一个唯一的上升排序的名为 user_id_index 的索引。当然,我们可以简单地添加另外一个域标签在索引定义中指定多于一个的域。我们现在仍然没有提到的是为我们产生唯一的用户 id 的序列。

<sequence>
<name>users_user_id</name>
<start>1</start>
<on>
<table>users</table>
<field>user_id</field>
</on>
</sequence>

The last example is pretty mind blowing. Going through line by line we see that we first open a sequence tag followed by a name tag which specifies the name of the sequence. This is followed by a start tag that defines the initial value of the sequence. Now, we open an optional on tag. Here we need to set a specific field within a table. This information is used by the manager to set the value of the sequence to the maximum value in the user_id field of the users table. If the users table is empty the value specified in the start tag is used instead. Please note that the value specified in the start tag is the first value that will be returned if you call MDB::nextId().
上一个例子非常的绕弯。一行行看过来,我们看到首先打开一个 sequence 标签,跟着一个指定序列名字的 name 标签。这之后跟着一个定义序列初始值的 start 标签。现在,我们打开一个可选的 on标签。这儿我们需要设置一个表中的指定域。这个信息是管理器用来把序列的值设置为 users 表的 user_id 域的最大值。如果 users 表是空的,作为替代使用的是 start 标签中指定的值。请注意在 start 标签中指定的值是我们调用 MDB::nextId() 返回的第一个值。

Of course, you can also initialize a table with any values. For example you may want to initialize the above table with a maintenance user that you always want to include with your application. To do this we need to add an initialization tag to the table tag. Listing 4 defines one row after another enclosed with an insert tag.
当然,你也能使用任何值初始化表。例如你可能想要用你总是想要包含在你的程序中的管理用户来初始化前面的表格。为了这么做,我们需要把一个 initialization 标签添加给 table 标签。Listing 4 定义了一在另外一用 insert 标签包括的行之后的行。

Listing 4

<table>
<name>users</name>
<initialization>
<insert>
<field>
<name>user_id</name>
<value>1</value>
</field>
<field>
<name>handle</name>
<value>default</value>
</field>
<field>
<name>is_active</name>
<value>Y</value>
</field>
</insert>
</initialization>
</table>

As you can see from the last example all we have to do is to define a value for each field of the table. We now have the necessary basics to create an XML schema for MDB. The next step is to pass this schema file to the MDB manager.
如你从上个例子中能看到的那样,所有我们需要做的就是给表的每个域设定值。我们现在已经知道了必要的基础知识来创建一个 MDB 的 XML schema。下一步是把这个 schema 文件传递给 MDB 管理器。

$manager = new MDB_Manager;
$input_file = 'auth.schema';
// we do not have to connect to a specify a specific database at this time
$dsn = "mysql://$user:$pass@$host";
$manager->connect($dsn);
$manager->updateDatabase($input_file, $input_file. '.before');

We now have a new database called auth with a table called users. There is one index on the field user_id. There is one row in the table as well. We also have a sequence called users_user_id which will be initialized at 1. The next value in the sequence will therefore be 2. Finally, a copy of the schema was created with the name auth.schema.before. This happened because we passed the optional second parameter to MDB_Manager::updateDatabase(). In the next section we will see why this copy is created.
我们现在有了一个新的名字叫 auth 的数据库,它有一个表叫 users。在域 user_id 有一个索引。而且在表中还有一行。我们还有一个序列称为 users_user_id,它将被初始化为 1。因此序列中的下一个值就是 2。最后,schema 的一个拷贝以名字auth.schema.before 被创建。这是因为我们给 MDB_Manger::updateDatabase() 传递了可选的第二个参数。在下一节我们将看到为什么要创建这个拷贝。

This is all fairly amazing but it gets better. It is often the case an application needs to be changed at some point. For example we may decide we want to change the name of the table from users to people. We also want to add a field called pwd to store the password field (please check the textbox Reserved Words).
所有这些都非常令人惊奇但是它变得更好。许多情况下程序需要在某些地方作出改变。例如我们可能决定需要把表的名字从 users 变成 people。我们可能还需要增加一个域 pwd 来储存密码域(请检查 textbox 的保留字)。

Reserved Words
保留字

The reason we do not call the field password is that this is a reserved word for field names in Interbase. Since we want to be RDBMS independent the MDB manager will either issue a warning or fail if the option fail_on_invalid_names is set to true (which is the default).
 我们没有称那个域为 password 的原因是那是 Interbase 中一个域名的保留字。因为我们需要 RDBMS 独立,MDB 管理器要么给出一个警告要么在 fail_on_invalid_names 选项被设置为真的时候(这是缺省值)失败。

In the old days you would now be in a bit of pain to alter all your existing installations to this new schema. But thanks to MDB this can be automated. In listing 5 are the changes we make to our table definition:
在过去的时候,你可能现在正处于把你所有已经有的东西变成这种新的 schema 的痛苦之中。但是由于 MDB 这些工作能够自动完成。在 listing 5 中是我们对我们的表格定义进行的修改:

Listing 5

<table>
<name>people</name>
<was>users</was>
<declaration>
<field>
<name>pwd</name>
<type>text</type>
<length>32</length>
<notnull>1</notnull>
<default></default>
</field>
</declaration>
</table>

Now we want the manager to make the necessary alterations, but before I want to mention a possible pitfall. Since we renamed the table users to people we also have to change all references to the old name like in the sequence we build. There the reference in the on tag needs to be changed to point to the people table. To achieve this we pass the new and the old version of the schema to the manager. This is why we created a .before file when we first called MDB_Manager::updateDatabase(). This ensures that we have an old version of the schema to compare the new version with.
现在我们想要管理器来作出必要的改变,但是在此之前我像提一下可能的陷阱。因为我们把表从 users 更名为 people,我们还需要把所有对原来名字的引用进行更改,比如我们建立的序列。在 on 标签中的索引需要更改为指向 people 表。为了达到这个目的,我们把 shcema 的新旧版本传递给管理器。这酒是为什么我们在第一次调用 MDB_Manager::updateDatabase() 时我们创建一个 .before 文件的原因。这确保了我们有一个旧版本的 shcema 来与新的版本进行比照。

$input_file = 'auth.schema';
$manager->updateDatabase($input_file, $input_file.'.before');

That's all! The users table is now called people and now we also have a pwd field.
所有的就是这样!users 表现在称为 people 并且我们也有了一个 pwd 域。

I now want to look at one last feature of the XML schema format. This feature is especially important if you want to programmatically use the manager. Imagine that you have several customers that have the same authentication application running on your database server. Every customer has a database running on this server with the same schema but one minor difference: the name of the database. While it may be feasible to keep separate schema files for each client because the update cycles will not be the same this is not the case for our sample authentication application. Here all clients will be updated at the same time. The XML schema format allows us to use the variable tag for this.
我现在要看看 XML schema 格式的最后一个特性。如果你想要编程性的使用管理器,这个特性尤其重要。假设你有好几个有相同验证程序运行在你的数据库服务器的客户。 每个客户有一个服务器运行在这个服务器有相同的 schema 只有微小的区别:数据库的名字。可能为每个客户单独保存 schema 文件是可行的因为更新周期可能不是一样的,这不是我们例子验证程序的情况。这儿所有的客户同时更新。XML schema 文件允许我们为此可以使用变量。

<?xml version="1.0" encoding="ISO-8859-1" ?>
<database>
<name><variable>name</variable></name>
</database>

We can now set the variable name at run time to whatever we may need.
我们现在在运行时设置变量为任意我们需要的东西。

foreach($clients as $name) {
$variables = array('name' => $name)
$manager->updateDatabase($input_file, $input_file.'.before', $variables);
}

The XML schema management is another important piece in the database abstraction concept that MDB provides. It allows us to keep our schema definition independent of a specific RDBMS. But using this format also ensures that the correct native data types are used so that MDB can correctly map its native data types. Finally, since the format is based on XML it is much easier to write tools that generate or read XML schema files.
XML schema 管理是 MDB 提供的数据库抽象概念的另外一个非常重要的部分。它使得我们保持我们的 schema 定义与特定的 RDBMS 无关。但是使用这个格式还确保了使用正确的原生数据类型因而 MDB 能够正确地映射它的原生数据类型。最后,因为数据是基于 XML 的,编写产生或者读取 XML schema 文件的工具要容易一些。

Sounds great but my application already uses ...
听起来不错但是我的应用程序已经使用了……

Most readers probably find themselves in a position where they already have a number of applications that run on some other database abstraction layer. Due to MDB's heritage most PEAR DB users should find that MDB feels very similar, since the API of MDB is based on that of PEAR DB. Metabase users should find that all their favourite functions have their counterpart in MDB. The XML schema format is exactly the same as in Metabase. A complete guide to porting your existing applications to MDB is beyond the scope of this article, instead I will use this space to give some tips. If you have any specific questions feel free to email me.
大部分读者可能发现它们处于这样的境地——他们已经有了大量运行于其他数据库抽象层的程序。由于 MDB 的出身,大部分 PEAR DB 的用户应当发现 MDB 感觉上非常类似,因为 MDB 的 API 是基于 PEAR DB 的。Metabase 用户应当发现他们所有偏爱的功能都在 MDB 中有对应的东西。XML schema 格式和 Metabase 中的是一摸一样的。一个完全的指导来引导你把已经写好的程序移植到 MDB 中超出了本文的范围,但是我将利用这个机会给一些提示。如果你有任何具体的问题,放心的发信来询问我。

To port your PEAR DB application to MDB the best place to start is the PEAR wrapper. For one you can run your application using the PEAR wrapper. The wrapper of course does add a little bit of overhead so you will probably want to port to the native interface at some point. The first step then should be listing all PEAR DB methods that your application currently uses. Then look at the wrapper for any differences in the API. There are two key differences you will notice: result sets are not objects anymore and all of the querying methods allow you to pass the data types of the result set which will result in slight changes in the parameter order. The first difference means that instead of calling the fetch method on the result object:
为了把你的 PEAR DB 程序移植到 MDB,最好的起点是 PEAR wrapper。你能使用 PEAR wrapper 来运行你的程序。wrapper 当然增加了一些额外负担,因而你可能有些想要移植到原生的接口。那么第一步是列出所有你程序当前使用的 PEAR DB 函数。然后看看 wrapper 从中找出任何 API 上的区别。有两个你要注意的关键区别:结果集不再是对象而且所有的允许你传递结果集的数据类型的查询方法将导致参数顺序上的少许改变。第一个区别意味着不能再结果对象上调用获取函数。

$result = $db->query($sql);
$row = $result->fetchRow();

You will now have to call the MDB object for fetching:
你现在必须调用 MDB 对象来进行获取:

$result = $mdb->query($sql);
$row = $mdb->fetchRow($result);

The second difference is quite easily fixed by looking at the wrapper. As you can see in the wrapper you may simply pass NULL where MDB would otherwise expect data types in the result set. Now, your application should work with MDB. Of course, you are now not really taking advantage of the advanced features of MDB. This most likely will require some changes to your current database schema. The manager can attempt to reverse engineer an XML schema file from an existing database. A very simple front end can be found in the MDB package: the reverse_engineer_xml_schema.php script. Most likely you will need to manually fix the resulting XML schema file, but it will give you a nice starting point.
第二个区别通过观察 wrapper 可以轻易的被解决。如你再 wrapper 中能看到的,你可以再 MDB 期望得到结果集的数据类型的地方简单地传递 NULL。现在,你地程序应当能够使用 MDB。当然,你现在没有真正得到了 MDB 地高级特性优点的益处。这最有可能的是需要对你当前的数据库 schema 进行一些改动。管理器能够尝试反向地从已经存在的数据库中获取 XML schema 文件。一个非常简单的前端可以在 MDB 包中找到:reverse_engineer_xml_schema.php 脚本。极有可能你将需要手动修正产生的 XML schema 恩见,但是它将给你一个很好的开始。

If you want to port your existing application from Metabase to MDB you will have to change all of your function calls. Looking at the Metabase wrapper it will become quite obvious what changes need to be made. If you know regular expressions well you might even be able to get most of the work done with a few such replacements. Anyways, you should be up and running your old beloved advanced abstraction features but now using MDB in no time. What you will probably notice is that the method names are much shorter now. If you do some benchmarking you will also see a nice performance increase.
如果你想要把你已经存在的程序从 Metabase 移植到 MDB 你将必须改动所有的函数调用。查看 Metabase wrapper 需要改动什么将变得非常明显。如果你知道正则表达式你可能能够完成大部分这样的替换工作。无论如何,你应当向前并且运行你原来喜爱的高级抽象特性但是现在用的是 MDB。你可能注意得到的是函数名变得更加简短了。如果你作一些性能测试,你也将看到可观的性能改善。

So what does the future look like for MDB?
那么 MDB 将来会是什么样子呢?

At the time this article publishes MDB will have moved on from the original 1.0 release. Next to the original MySQL and PostGreSQL drivers MDB will also have an ODBC driver and possibly even more drivers. This is one key area that is focused on during the development of MDB. Once MDB has caught up with PEAR DB in terms of drivers it is likely to become the standard database abstraction layer in the PEAR framework.
本文发表时 MDB 可能已经不再是原来的 1.0 release 了。在原来的 MySQL 和 PostGreSQL 驱动之后,MDB还将有一个 ODBC 驱动以及可能的更多的驱动。这是 MDB 开发过程中关注的关键区域之一。一旦 MDB 在驱动方面跟上了 PEAR DB,它很有可能成为 PEAR 框架中标准的数据库抽象层。

But there is another key area of development: the MDB_frontend project. The MDB_frontend will be a phpMyAdmin like webfrontend based on MDB and the MDB manager. With this tool you will be able to browse databases stored on any RDBMS that MDB supports. The MDB_frontend will show both the native and the MDB data types. Emulated features such as sequences in MySQL will be hidden. The user will simply see a list of sequences and not a table storing the value of the sequence which is how sequences are emulated in MySQL. Furthermore the MDB_frontend will assist in porting existing databases to match the native data types that MDB expects to be used. It will also help in creating and updating XML schema files. Some initial work has been completed but much more work is needed before a public release can be expected.
但是还有另外一个开发中的关键领域:MDB_frontend 工程。MDB_frontend将成为基于 MDB 和 MDB 管理器的 phpMyadmin。有了这个工具,你将能够浏览储存在 MDB 支持的 RDBMS 中的数据库。MDB_frontend 将同时显示原生和 MDB 数据类型。模拟的特性比如 MySQL 中的序列将被隐藏。用户将仅仅看到一个序列列表而不是一个储存序列指的表,而在 MySQL 中这就是序列是如何被模拟的。而且 MDB_frontend 将帮助移植已经存在的数据库来符合 MDB 预期使用的原生数据类型。它还将帮助创建和更新 XML schema 文件。一些初期的工作已经完成了但是很多工作需要在公开发布之前被添加。

While drivers and the MDB_frontend are the focus of all development currently, there are other things that MDB users may need: Like the integration of bulk fetching of LOB fields, others may need foreign and primary key support. As always in opensource things will go faster if you participate in testing and implementation. But I am also thankful for any other feedback like feature requests.
驱动和 MDB_frontend 是当前开发的所有焦点,在 MDB 中还有许多用户可能需要的:像 bulk 获取 LOB 域的集成,其他人可能需要外部和主键支持。如一直以来的那样如果你参与测试和实现,开源的东西将加快很多。但是我也很感谢像特性需求合阳的反馈。

Some final thoughts
一些文后的思考

After months of hard work MDB is gaining acceptance among the current PEAR DB and Metabase users. I also hope that people that so far have not been convinced by other database abstraction layers realize the benefits that MDB holds for them. Of course, there are still a lot of applications that need to be tailored specifically to one RDBMS where a tool like MDB just ads unnecessary overhead and restrictions. Overall I am very pleased that we made the decision in my company to lead the MDB development. In the beginning, we were all a bit worried that by attempting to please both the PEAR DB and Metabase users the result would end up pleasing neither side. Another source of concern was if the PHP community would assist in the development or not. I am very happy that the PHP community came through and helped in writing drivers and helping on the core of MDB as well. Therefore we consider this project to be a huge success. We are sure that together MDB will be improved even further. And we are happy to have helped making PHP even better.
在数月的艰辛工作之后,MDB 正在当前的 PEAR DB 和 Metabase 用户中获得认可。我还希望当前还没有被其他数据库抽象层说服的用户意识到 MDB 给他们的好处。当然,还是有许多程序需要对 RDBMS 进行特殊剪裁,对于这种情况像 MDB 这样的工具仅仅是增加了不必要的额外负担和限制。总的来说,我非常高兴我们在我们的公司中作出领导 MDB 开发的决定。在起初,我对尝试同时取悦 PEAR DB 和 Metabase 的用户但是结果可能到处不讨好多少有些担心。另外一个关心的来源是 PHP 社区是否将帮助其开发。我非常高兴 PHP 社区来了并且帮助撰写驱动以及 MDB 的核心。因而我们认为这个项目是一个极大的成功。我们还一并相信 MDB 将得到更大的改进。而且我们对帮助 PHP 变得更好感到高兴。

Lukas Smith is the lead author of PEAR MDB. He actively contributes to various PHP opensource projects and is a founder of the company BackendMedia which specializes in PHP development.
Lukas Smith 是 PEAR DB 的主要作者。它积极地给多个 PHP 开远项目进行贡献并且是专注于 PHP 开发的 BackendMeida 公司的建立者。

Links and Literature
链接 和 文献

PEAR MDB homepage: pear.php.net/package-info.php?package=MDB

PEAR MDB documentation: www.backendmedia.com/MDB/docs/

PEAR MDB sample script: cvs.php.net/co.php/pear/MDB/MDB_test.php

PEAR DB homepage: pear.php.net/package-info.php?package=DB

Metabase homepage: www.phpclasses.org/mirrors.html?page=%2Fbrowse.html%2Fpackage%2F20.html

Simple benchmark: freshmeat.net/screenshots/30313/

0 0

相关博文

我的热门文章

img
取 消
img