|
關(guān)于 PHP 的數(shù)據(jù)庫 API 對于不同的數(shù)據(jù)庫有不同的函數(shù)，一直以來就有人嘗試使用 PHP 的面向?qū)ο蟮墓δ苓M行一些封裝。其中有比較著名的ADODB，PHPLIB。后來舉世矚目的 PEAR 項目中的 PEAR DB 更是其中的佼佼者。這些用面向?qū)ο髮?shù)據(jù)庫 API 進行的封裝的包一般稱為數(shù)據(jù)庫抽象層。
本文介紹的是 PEAR 中對 PEAR DB 進行融合 Metabase 庫的一些優(yōu)秀功能之后產(chǎn)生的注重效率，而且簡單易用，功能非常強大的 MDB 的一個非常好的介紹。作者就是 MDB 的主創(chuàng)人員。
想獲得我最近關(guān)注的 PHP/PEAR 的最新原創(chuàng)和譯文，請訪問我的主頁
Write once - run anywhere
一次編寫——隨處運行
PEAR MDB Database Abstraction Layer
PEAR MDB 數(shù)據(jù)庫抽象層
作者：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的關(guān)鍵特性之一。許多商業(yè)模型依賴于操作系統(tǒng)無關(guān)性來保證產(chǎn)品能夠銷售給廣泛的客戶群體。因而，為什么要把你自己綁在某種數(shù)據(jù)庫廠商的身上呢？數(shù)據(jù)庫抽象層使得你能夠與數(shù)據(jù)庫獨立的開發(fā)你的應用程序。但是，通常情況下它們對性能的影響超過了你所希望的，要么他們并不足夠抽象以消除所有和特定數(shù)據(jù)庫相關(guān)的代碼。
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.
這篇文章將對數(shù)據(jù)庫抽象包 PEAR MDB 有一個很好的介紹。文章的焦點將是對 MDB 超越類似包所提供的更先進的特性，例如數(shù)據(jù)類型抽象和基于 XML 的 schema 管理。對 PHP 和 SQL 的基本理解是推薦的。
Why another database class?
為什么另外再要一個數(shù)據(jù)庫類？
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 工程在客戶已經(jīng)確定了要使用那種 RDBMS （關(guān)系型數(shù)據(jù)庫管理系統(tǒng)）之后被添加給已經(jīng)存在的 IT 基礎(chǔ)結(jié)構(gòu)。即使那并不是因為不同的預算可能影響的你選擇何種數(shù)據(jù)用于部署的情況。最終，你作為開發(fā)者可能簡單的偏好于不把自己綁在某個廠商身上。自此，意味著給每個支持的數(shù)據(jù)保持版本或者犧牲更多性能但是獲得多于必須的易用性：走入 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 無關(guān)的 PHP 程序成為簡單的過程的數(shù)據(jù)庫抽象層。大部分其他的 PHP 的所謂數(shù)據(jù)庫抽象層緊緊給所有支持的數(shù)據(jù)庫提供了一個公用 API 以及非常有限的抽象（大部分只是針對序列的）。MDB 另一方面能夠用來抽象所有數(shù)據(jù)庫發(fā)送和接收的數(shù)據(jù)。甚至數(shù)據(jù)庫 schema 都能被定義為 RDBMS 無關(guān)的格式。但是它提供這些功能的同時仍然保持了很高的性能以及簡單易用。這是通過深入觀察兩個流行的數(shù)據(jù)庫抽象層，PEAR DB 和 Metabase， 之后并且對它們進行了融合后獲得的。而且在融合過程中，趁著這個機會清理了它們?nèi)诤虾蟮?API 以及任何影響性能的設(shè)計。
How did MDB come to be?
MDB 是怎樣出現(xiàn)的？
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 獨立的數(shù)據(jù)庫抽象包。這個目標是把特定數(shù)據(jù)庫相關(guān)的代碼數(shù)量減少到零。我發(fā)現(xiàn)提供這樣的功能的唯一的一個包是 Metabase。但是 Metabase有一些部分是因為為了和 PHP3 兼容的讓人不舒服的 API。盡管如此，我們決定 Metabase 是我們唯一的選擇。但是即使是在給 Metabase 增加了一個性能改進的補丁之后，我們?nèi)匀桓械轿覀兎艞壛颂嗟男阅�。我們�?2001 年的 PHP 國際會議上碰到了 Metabase 的作者，并且我們談論了讓像 Metabase 這樣的東西成為 PEAR 工程一部分的好處。后來不久，在 PEAR 郵件列表上就 PEAR DB 和 Metabase 融合的可能的好處又開始了一場討論。在我們公司進行了許多討論之后，我們決定承擔這個任務。數(shù)個月的艱辛工作之后，我們現(xiàn)在有了 MDB 的第一個穩(wěn)定的 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 結(jié)合了 PEAR DB 和 Metabase 的大部分特性。實際上，PEAR DB 的特性中唯一不再存在的是作為結(jié)果集返回一個對象。我們放棄了這個特性是因為這個特性不常用而且對于性能的損失是非常明顯的。許多開發(fā)上的時間用在了使得 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
預準備的查詢模擬
給所有傳遞進來以及從數(shù)據(jù)庫中取出的數(shù)據(jù)的完全的數(shù)據(jù)類型抽象（包括 LOB 支持）
事務支持
數(shù)據(jù)庫/表/索引/序列創(chuàng)建/拋棄/改變
RDBMS 無關(guān)的數(shù)據(jù)庫 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 無關(guān)的 PHP 程序時使用它們是非常重要的。一個展示使用 MDB 是多么簡單的例子在文章的結(jié)尾的 "鏈接和文獻" 部分。如前面所說，文章的焦點是介紹使得 MDB 與其他 PHP 數(shù)據(jù)庫抽象層不同的那些特性。你可以在隨本期文章一同包裝的 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 系統(tǒng)你需要 PHP 的 CGI 版本安裝在了你的系統(tǒng)并且簡單地運行下面地命令：
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
利用數(shù)據(jù)類型抽象
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:
因為大部分數(shù)據(jù)庫傾向于有一些個性或者怪癖，對于MDB來說把這些不同之處給開發(fā)者隱藏起來非常重要。MDB 通過定義自己的內(nèi)部數(shù)據(jù)類型來達到這點：text，boolean，integer，decimal，float，date，time，time stamp，large objects（文件）。所有傳遞給數(shù)據(jù)庫和從數(shù)據(jù)庫獲取的數(shù)據(jù)都能轉(zhuǎn)換成 MDB 的內(nèi)部格式或者從數(shù)據(jù)庫的內(nèi)部格式轉(zhuǎn)化回來。本節(jié)相關(guān)的例子腳本能夠再 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.
這個查詢?nèi)绻l(fā)送給數(shù)據(jù)庫的話八成要失敗。原因是存儲在 $name 中的值需要轉(zhuǎn)換為正確的字符串格式。這也許意味著 $name 的內(nèi)容可能有特殊的轉(zhuǎn)義字符或者被引號包圍。PEAR DB 為此提供了方法 DB:.quote()。在 MDB 中這個方法叫 MDB::getTextValue()。不同之處是 MDB 給每種前面所列的數(shù)據(jù)類型都提供了這樣的函數(shù)。因而我們也能夠把 $timeout 轉(zhuǎn)換為正確的格式。
// 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() 獲得第一行，它釋放結(jié)果集并且返回其內(nèi)容，因而它正是我們所要的。
$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 返回像日期這樣的數(shù)據(jù)時用的格式是不同的。因此，如果我們?nèi)缓笠獙σ恍⿺?shù)據(jù)進行計算，不管選擇的 RDBMS 是什么，把數(shù)據(jù)以相同的格式返回是重要的。這個可以由 MDB 半自動地完成。你所有需要做的是告訴你的結(jié)果列將是什么樣的類型，MDB將處理轉(zhuǎn)換的工作。最簡單的辦法是把這樣的信息傳遞給查詢函數(shù)。
$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 結(jié)果集的第一列類型是 'timestamp' 以及第二列是'integer'。所有查詢函數(shù)能夠接受這樣的元信息作為可選的參數(shù)。數(shù)據(jù)還能事后用 MDB::setResultTypes() 來設(shè)置。取決于數(shù)據(jù)獲取于的數(shù)據(jù)庫，它然后將被相應的轉(zhuǎn)換返回的數(shù)據(jù)。MDB 內(nèi)部的 timestamps 的數(shù)據(jù)格式是遵循 ISO 8601 標準的。其他像 PEAR::Date 這樣的包能夠處理這種格式。MDB 還在 MDB_Date 類中提供了一些數(shù)據(jù)格式轉(zhuǎn)換函數(shù)，它們能夠被可選的包含。
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 以相同的方法返回整數(shù)數(shù)據(jù)，沒有必要轉(zhuǎn)換整數(shù)數(shù)據(jù)。因而，為了獲得稍許的性能改進你能夠這么做：
$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.
這樣只有結(jié)果集的第一列會被轉(zhuǎn)換。當然，如果 MDB 用于返回整數(shù)不同的數(shù)據(jù)庫，這可能成為一個問題。然而，稍許的性能改善可能并不值得冒這個風險。但是再一次的，它顯示了這些特性的使用僅僅是供選擇的。
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 展示了一個使用預準備的查詢的例子。如果你必須運行大量查詢而唯一的差別是數(shù)據(jù)傳遞給數(shù)據(jù)庫，但是查詢的結(jié)構(gòu)還是一樣的，這些能夠相當?shù)姆奖�。高級的�?shù)據(jù)庫能夠在內(nèi)存中儲存解析好的查詢來加速性能。
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 中儲存的所有四個數(shù)組將用于 execute 語句。數(shù)據(jù)將自動被轉(zhuǎn)換為正確的格式。因為這是一個插入語句，MDB::execute() 的第二個參數(shù)被設(shè)置為 NULL 因為我們將沒有任何結(jié)果列需要我們設(shè)置數(shù)據(jù)類型。
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().
在支持的數(shù)據(jù)類型中還有 LOB （大對象），它使得我們能夠在數(shù)據(jù)庫中儲存文件。二進制文件儲存在 BLOB （二進制大對象）中而且普通文本文件儲存在 CLOB （字符大對象）中。在 MDB 中你僅僅能夠使用預準備的 INSERT 和 UPDATE 查詢儲存 LOB。使用 MDBA::setParamBlob() 或者 MDB::setParamClob() 你能夠設(shè)置預準備查詢的 LOB 域的值。兩個函數(shù)都預期傳遞一個 LOB 對象，而它能夠使用 MDB::createLob() 創(chuàng)建。
$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() 被傳遞一個關(guān)系數(shù)組。Type 鍵的值可能是以下中的一個：data, inputfile 或者 outputfile。前兩個用于你想要把 LOB 寫入數(shù)據(jù)庫的時候。如果你有一個儲存在變量中的 LOB，你應當在 需要使用 inputfile 時從文件直接讀取 LOB。最后，outpufile 應當在你想要從數(shù)據(jù)庫中讀取 LOB 時使用。取決于你是否使用數(shù)據(jù)或者 inputfile 你需要給 Filename 鍵或者 Data 鍵指定一個值，像上面的例子那樣。現(xiàn)在，我們將把前面的 LOB 儲存到數(shù)據(jù)庫中去。
$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'.
為了從數(shù)據(jù)庫中獲取上面的文件，我們需要首先從數(shù)據(jù)庫中選擇數(shù)據(jù)并且使用 MDB::createLob() 創(chuàng)建 LOB 對象。這次我們將設(shè)置 '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().
現(xiàn)在我們能夠使用 MDB::readLob() 從結(jié)果集中讀取 LOB。傳遞長度 0 給 MDB::readLob() 意味著整個 LOB 被讀取和儲存在我們前面指定的文件中。一旦任務完成了，我們可以把資源釋放了。你也可以設(shè)置任何大于零的長度并且使用一個 while 循環(huán)檢查 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.
注意你不要把這個獲取函數(shù)和 bulk 獲取函數(shù)像 MDB::fetchAll()搞混了，因為這將在大部分 PHP 數(shù)據(jù)庫擴展中導致問題。在一些時候，MDB 可能能夠使用 bulk 獲取函數(shù)獲得 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.
如我們在這節(jié)所見，MDB 特性本身的原生數(shù)據(jù)類型集自動映射于數(shù)據(jù)庫中的原生數(shù)據(jù)類型。這保證了無論我們發(fā)送和從數(shù)據(jù)庫接收什么樣的數(shù)據(jù)，它都能與使用的 RDBMS 無關(guān)的使用相同的格式。如我在本節(jié)開篇已經(jīng)提到的，這明顯需要數(shù)據(jù)庫使用的數(shù)據(jù)類型是 MDB 預期的。這種需要被用于確保映射所耗費的代價很小。下一節(jié)將教給我們 MDB 如何輔助在數(shù)據(jù)庫中使用正確的數(shù)據(jù)類型。
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.
利用在上個段落中描述的特性，你能編寫真正的數(shù)據(jù)庫獨立的程序。但是 MDB 嘗試向前更加邁出一步：它允許你用 XML 定義你的 schema。一個管理器把這種 schema 轉(zhuǎn)換為給每種 RDBMS 的必要的 SQL 語句。這意味著你能對所有支持的 RDBMS 使用相同的 schema。本節(jié)的例子能夠在 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.
我們現(xiàn)在將從頭編寫一個 XML schema 文件。首先，我們必須定義一個 XML 文檔。數(shù)據(jù)庫定義是包含在一個 database 標簽之中的。數(shù)據(jù)庫的名字是使用 name 標簽定義的。create 標簽告訴管理器數(shù)據(jù)庫是否需要在它不存在的時候被創(chuàng)建。如果你把你的 schema 文件分割成好幾個文件你你首先提交給管理器的那個文件中把 create 設(shè)置為 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.
可能你已經(jīng)從數(shù)據(jù)庫名 auth 猜出了這個數(shù)據(jù)庫的目的是用于儲存簡單的驗證程序的用戶數(shù)據(jù)。Listing 2 定義了在其中我們能儲存用戶數(shù)據(jù)的表。
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 使得這個過程更加簡單。我將在這篇文章的后面談論這個工程�？赡苓@極其詳細地表格描述的優(yōu)點是非常明顯。前面例子中的表格被稱為 users 并且我們定義了 3 個域：類型為整數(shù)的 user_id，類型為文本的 handle 和類型為邏輯型的 is_active。記住如果你如前一節(jié)那樣傳遞了必要的元數(shù)據(jù) 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).
下一件我們現(xiàn)在需要做的事情是通過在 user_id 域放置恰當?shù)乃饕_保 user_id 是唯一的。索引定義就在聲明標簽之內(nèi)（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 中創(chuàng)建一個唯一的上升排序的名為 user_id_index 的索引。當然，我們可以簡單地添加另外一個域標簽在索引定義中指定多于一個的域。我們現(xiàn)在仍然沒有提到的是為我們產(chǎn)生唯一的用戶 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 標簽�，F(xiàn)在，我們打開一個可選的 on標簽。這兒我們需要設(shè)置一個表中的指定域。這個信息是管理器用來把序列的值設(shè)置為 users 表的 user_id 域的最大值。如果 users 表是空的，作為替代使用的是 start 標簽中指定的值。請注意在 start 標簽中指定的值是我們調(diào)用 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.
如你從上個例子中能看到的那樣，所有我們需要做的就是給表的每個域設(shè)定值。我們現(xiàn)在已經(jīng)知道了必要的基礎(chǔ)知識來創(chuàng)建一個 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.
我們現(xiàn)在有了一個新的名字叫 auth 的數(shù)據(jù)庫，它有一個表叫 users。在域 user_id 有一個索引。而且在表中還有一行。我們還有一個序列稱為 users_user_id，它將被初始化為 1。因此序列中的下一個值就是 2。最后，schema 的一個拷貝以名字auth.schema.before 被創(chuàng)建。這是因為我們給 MDB_Manger::updateDatabase() 傳遞了可選的第二個參數(shù)。在下一節(jié)我們將看到為什么要創(chuàng)建這個拷貝。
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 選項被設(shè)置為真的時候（這是缺省值）失敗。
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:
在過去的時候，你可能現(xiàn)在正處于把你所有已經(jīng)有的東西變成這種新的 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.
現(xiàn)在我們想要管理器來作出必要的改變，但是在此之前我像提一下可能的陷阱。因為我們把表從 users 更名為 people，我們還需要把所有對原來名字的引用進行更改，比如我們建立的序列。在 on 標簽中的索引需要更改為指向 people 表。為了達到這個目的，我們把 shcema 的新舊版本傳遞給管理器。這酒是為什么我們在第一次調(diào)用 MDB_Manager::updateDatabase() 時我們創(chuàng)建一個 .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 表現(xiàn)在稱為 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.
我現(xiàn)在要看看 XML schema 格式的最后一個特性。如果你想要編程性的使用管理器，這個特性尤其重要。假設(shè)你有好幾個有相同驗證程序運行在你的數(shù)據(jù)庫服務器的客戶。 每個客戶有一個服務器運行在這個服務器有相同的 schema 只有微小的區(qū)別：數(shù)據(jù)庫的名字�？赡転槊總�客戶單獨保存 schema 文件是可行的因為更新周期可能不是一樣的，這不是我們例子驗證程序的情況。這兒所有的客戶同時更新。XML schema 文件允許我們?yōu)榇丝梢允褂米兞俊?/P>
<?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.
我們現(xiàn)在在運行時設(shè)置變量為任意我們需要的東西。
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 提供的數(shù)據(jù)庫抽象概念的另外一個非常重要的部分。它使得我們保持我們的 schema 定義與特定的 RDBMS 無關(guān)。但是使用這個格式還確保了使用正確的原生數(shù)據(jù)類型因而 MDB 能夠正確地映射它的原生數(shù)據(jù)類型。最后，因為數(shù)據(jù)是基于 XML 的，編寫產(chǎn)生或者讀取 XML schema 文件的工具要容易一些。
Sounds great but my application already uses ...
聽起來不錯但是我的應用程序已經(jīng)使用了……
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.
大部分讀者可能發(fā)現(xiàn)它們處于這樣的境地——他們已經(jīng)有了大量運行于其他數(shù)據(jù)庫抽象層的程序。由于 MDB 的出身，大部分 PEAR DB 的用戶應當發(fā)現(xiàn) MDB 感覺上非常類似，因為 MDB 的 API 是基于 PEAR DB 的。Metabase 用戶應當發(fā)現(xiàn)他們所有偏愛的功能都在 MDB 中有對應的東西。XML schema 格式和 Metabase 中的是一摸一樣的。一個完全的指導來引導你把已經(jīng)寫好的程序移植到 MDB 中超出了本文的范圍，但是我將利用這個機會給一些提示。如果你有任何具體的問題，放心的發(fā)信來詢問我。
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 函數(shù)。然后看看 wrapper 從中找出任何 API 上的區(qū)別。有兩個你要注意的關(guān)鍵區(qū)別：結(jié)果集不再是對象而且所有的允許你傳遞結(jié)果集的數(shù)據(jù)類型的查詢方法將導致參數(shù)順序上的少許改變。第一個區(qū)別意味著不能再結(jié)果對象上調(diào)用獲取函數(shù)。
$result = $db->query($sql);
$row = $result->fetchRow();
You will now have to call the MDB object for fetching:
你現(xiàn)在必須調(diào)用 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.
第二個區(qū)別通過觀察 wrapper 可以輕易的被解決。如你再 wrapper 中能看到的，你可以再 MDB 期望得到結(jié)果集的數(shù)據(jù)類型的地方簡單地傳遞 NULL�，F(xiàn)在，你地程序應當能夠使用 MDB。當然，你現(xiàn)在沒有真正得到了 MDB 地高級特性優(yōu)點的益處。這最有可能的是需要對你當前的數(shù)據(jù)庫 schema 進行一些改動。管理器能夠嘗試反向地從已經(jīng)存在的數(shù)據(jù)庫中獲取 XML schema 文件。一個非常簡單的前端可以在 MDB 包中找到：reverse_engineer_xml_schema.php 腳本。極有可能你將需要手動修正產(chǎn)生的 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.
如果你想要把你已經(jīng)存在的程序從 Metabase 移植到 MDB 你將必須改動所有的函數(shù)調(diào)用。查看 Metabase wrapper 需要改動什么將變得非常明顯。如果你知道正則表達式你可能能夠完成大部分這樣的替換工作。無論如何，你應當向前并且運行你原來喜愛的高級抽象特性但是現(xiàn)在用的是 MDB。你可能注意得到的是函數(shù)名變得更加簡短了。如果你作一些性能測試，你也將看到可觀的性能改善。
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.
本文發(fā)表時 MDB 可能已經(jīng)不再是原來的 1.0 release 了。在原來的 MySQL 和 PostGreSQL 驅(qū)動之后，MDB還將有一個 ODBC 驅(qū)動以及可能的更多的驅(qū)動。這是 MDB 開發(fā)過程中關(guān)注的關(guān)鍵區(qū)域之一。一旦 MDB 在驅(qū)動方面跟上了 PEAR DB，它很有可能成為 PEAR 框架中標準的數(shù)據(jù)庫抽象層。
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.
但是還有另外一個開發(fā)中的關(guān)鍵領(lǐng)域：MDB_frontend 工程。MDB_frontend將成為基于 MDB 和 MDB 管理器的 phpMyadmin。有了這個工具，你將能夠瀏覽儲存在 MDB 支持的 RDBMS 中的數(shù)據(jù)庫。MDB_frontend 將同時顯示原生和 MDB 數(shù)據(jù)類型。模擬的特性比如 MySQL 中的序列將被隱藏。用戶將僅僅看到一個序列列表而不是一個儲存序列指的表，而在 MySQL 中這就是序列是如何被模擬的。而且 MDB_frontend 將幫助移植已經(jīng)存在的數(shù)據(jù)庫來符合 MDB 預期使用的原生數(shù)據(jù)類型。它還將幫助創(chuàng)建和更新 XML schema 文件。一些初期的工作已經(jīng)完成了但是很多工作需要在公開發(fā)布之前被添加。
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.
驅(qū)動和 MDB_frontend 是當前開發(fā)的所有焦點，在 MDB 中還有許多用戶可能需要的：像 bulk 獲取 LOB 域的集成，其他人可能需要外部和主鍵支持。如一直以來的那樣如果你參與測試和實現(xiàn)，開源的東西將加快很多。但是我也很感謝像特性需求合陽的反饋。
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.
在數(shù)月的艱辛工作之后，MDB 正在當前的 PEAR DB 和 Metabase 用戶中獲得認可。我還希望當前還沒有被其他數(shù)據(jù)庫抽象層說服的用戶意識到 MDB 給他們的好處。當然，還是有許多程序需要對 RDBMS 進行特殊剪裁，對于這種情況像 MDB 這樣的工具僅僅是增加了不必要的額外負擔和限制�？偟膩碚f，我非常高興我們在我們的公司中作出領(lǐng)導 MDB 開發(fā)的決定。在起初，我對嘗試同時取悅 PEAR DB 和 Metabase 的用戶但是結(jié)果可能到處不討好多少有些擔心。另外一個關(guān)心的來源是 PHP 社區(qū)是否將幫助其開發(fā)。我非常高興 PHP 社區(qū)來了并且?guī)椭珜戲?qū)動以及 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 開發(fā)的 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: URL
出處：CSDN
責任編輯：cjj
◎進入論壇網(wǎng)絡編程版塊參加討論
|
