PHP Application Development With ADODB (part 1)

想你了的他他

版权声明：原创作品，允许转载，转载时请务必以超链接形式标明文章原始出版、作者信息和本声明。否则将追究法律责任。http://blog.csdn.net/mayongzhan - 马永占,myz,mayongzhan

PHP Application Development With ADODB (part 1)

PHP 使用ADODB进行应用程序开发 (部分 1)

Make your PHP scripts portable across databases with the powerful ADODB database abstraction library.

使用强大的ADODB数据抽象库 方便的连接PHP与数据库.

Any Port In A Storm

As a developer, one of the most important things to consider when developing a Web application is portability. Given the rapid pace of change in the Web world, it doesn't do to bind your code too tightly to a specific operating system, RDBMS or programming language; if you do, you'll find yourself reinventing the wheel every time things change on you (and they will - take my word for it).

作为一个开发者,开发一个web程序时最重要的事是轻便. 网络发展速度非常快, 介于此你的代码不能过于紧密的和特别操作系统,RDBMS,程序语言相联系; 如果你这样做了,你会发现你是在重复发明轮子,每次换环境使用你的程序都会使你改变你的程序 (这个会发生 – 相信我).

That's where this article comes in.

这就是这篇文章的来源.myz.

Over the course of this two-part tutorial, I'm going to be showing you how to make your code a little more portable, by using a database abstraction layer for all your RDBMS connectivity. This database abstraction layer allows you to easily switch between one RDBMS and another, without requiring either a code rewrite or a long, tortuous retest cycle. In the long run, this will save you time, save your customers money, and maybe make your life a little simpler.

通过这两篇指导, 我将向你展示如果使你的代码轻便一些,使用数据抽象层来连接你的数据管理管理系统.这个数据抽象层可以方便的实现从一个RDBMS迁移到另外一个,而不需要重写代码和重复测试. 最后, 节约了你的时间, 节约了你客户的资金, 也许还会使你的生活更简单一些.

Before we get started, one caveat: while portability is something you should strive for regardless of which language or platform you work on, it's impossible to cover every single possibility in this article...and I don't plan to. Instead, I'll be restricting myself to my favourite Web programming language, PHP, and the ADODB database abstraction library, also written in PHP. Similar libraries exist for most other programming languages, and you should have no trouble adapting the techniques in this article to other platforms.

在我们开始之前的警告: 如果你要轻便的使用的你的程序而不顾你工作的语言或平台, 很遗憾这篇文章不可能照顾到所有的细节...而且我也没打算这么做.我将使用我喜欢的web开发语言PHP和ADODB 数据库抽象库. 大多数程序语言都有类似的库存在, 你可以毫无困难的利用本文的技术应用到其他平台.

Let's get started!

让我们开始吧!马永占!

A Little Insulation

First up, let's get the lingo straight: what the heck is a database abstraction library anyway?

If you've worked with different databases, you've probably seen that each database operates in a slightly different manner from the others. The data types aren't always uniform, and many of them come with proprietary extensions (transactions, stored procedures et al) that aren't supported elsewhere. Additionally, the API to interact with these databases is not uniform; PHP itself comes with a different API for each supported database type,

如果你工作需要许多不同的数据库,你大概会发现每个数据库系统和其他数据库系统有一点差别.数据类型不统一,还有它们特有的扩展(事务,存储过程等等),这些扩展在别的系统不被支持.还有这些数据管理的API不统一;PHP使用不同的API来支持不同的数据库.

For all these reasons, switching from one database to another is typically a complex process, one which usually involves porting data from one system to another (with the assorted datatyping complications), rewriting your code to use the new database API, and testing it to make sure it all works. And that's where a database abstraction layer can help.

总上所述, 不同数据库之间的转换是一个复杂的过程, 把数据从一个数据库系统放到另外一个 (需要考虑复杂的数据类型因素), 用新的API重写代码, 然后进行测试. 数据抽象层可以帮助你完成这些.

Typically, a database abstraction layer functions as a wrapper around your code, exposing a set of generic methods to interact with a database server. These generic methods are internally mapped to the native API for each corresponding database, with the abstraction layer taking care of ensuring that the correct method is called for your selected database type. Additionally, most abstraction layers also incorporate a generic superset of datatypes, which get internally converted into datatypes native to the selected RDBMS.

举个例子, 数据库抽象层函数就象一个包装,包在你代码的外面, 仅暴露一组通用方法来和与数据库服务器相结合. 这些通用方法在内部与原来的连接数据库的API相映射, 抽象层确保你在选择不同数据库时调用正确的方法.而且, 许多数据抽象层合并成一个数据类型父集, 在其内部连接不同的 RDBMS.

In order to better understand the difference, consider the following diagram:

为了更好的理解这些概念请看下面的图表:

　　　　图贴不了...............................没关系不要紧........................

<shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock></shapetype><shape id="_x0000_i1025" style="WIDTH: 378pt; HEIGHT: 378pt" alt="Output image" type="#_x0000_t75"><imagedata o:href="http://www.melonfire.com/community/columns/trog/.collateral/00142/image1.gif" src="file:///C:%5CDOCUME~1%5CADMINI~1%5CLOCALS~1%5CTemp%5Cmsohtml1%5C02%5Cclip_image001.gif"></imagedata></shape>

As you can see, without an abstraction layer in place, you need to use a different API call for each of the three database types. With an abstraction layer in place, however, you can transparently use a single, generic call, and have the abstraction layer convert it into the native API call.

你可以看到, 不用抽象层的地方, 你需要使用不同的API调用类型不同的三种数据库. 使用抽象层的地方, 你可以看到只需要一个通用的调用,然后抽象层将它们转换成不通的连接,去调用原来的API.

A number of different abstraction layers are available for PHP, most notably the PEAR DBI, Metabase and PHPLib. The one I'm going to use in this article is named ADODB (Active Data Objects DataBase), and it's one of the most full-featured and efficient PHP abstraction libraries available today. Developed by John Lim, the library currently supports a wide variety of database systems, including MySQL, PostgreSQL, Oracle, Interbase, Microsoft SQL Server, Access, ODBC and others, and has been used in a number of well-known open-source PHP projects, including phpLens, PostNuke and Webodex.

PHP有许多不同的抽象层工具, 做有名的是PEAR DBI, Metabase and PHPLib. 还有一个是我将在这篇文章中使用的 ADODB (Active Data Objects DataBase), ADODB在现在是最有名最有效率的PHP抽象库之一. John Lim开发, 这个库支持各种不同的数据库系统, 包括 MySQL, PostgreSQL, Oracle, Interbase, Microsoft SQL Server, Access, ODBC 等等, 而且已经被许多有名的开源PHP工程所引用, 包括 phpLens, PostNuke and Webodex.

You can download a copy of ADODB from http://php.weblogs.com/adodb - get your copy now, set it up, and flip the page for an example of how it can be used.

你可以从http://php.weblogs.com/adodb 下载ADODB, 安装, 然后继续看本文的例子和占的翻译来了解ADODB是怎样使用的.

The Bookworm Turns

Before we get into the code, you might want to take a quick look at the database table I'll be using throughout this article. Here it is:

在我们写代码之前,请看一下下面的数据库表,我将用到下面的表:

mysql> SELECT * FROM library;

+----+-------------------+----------------+

| id | title      | author    |

+----+-------------------+----------------+

| 14 | <place w:st="on"><placename w:st="on">Mystic</placename><placetype w:st="on">River</placetype></place>   | Dennis Lehane |

| 15 | For Kicks    | Dick Francis |

| 16 | XML and PHP   | Vikram Vaswani |

| 17 | Where Eagles Dare | Jack Higgins |

+----+-------------------+----------------+

As you might have guessed, the "library" table contains a list of all the books currently taking up shelf space in my living room. Each record within the table is identified by a unique number (the geek term for this is "foreign key", but you can forget that one immediately).

你可以这样假设, 这个 "library" 表 包括所有书的编号,这个编号可以正确找到每一本书.每一条记录根据一个唯一的数字来区分 (这叫做 "foreign key", 目前我们不需要太注意它).

Now, let's suppose I want to display a list of my favourite books on my personal Web site. Everything I need is stored in the table above; all yours truly has to do is write a script to pull it out and massage it into a readable format. Since PHP comes with out-of-the-box support for MySQL, accomplishing this is almost as simple as it sounds.

现在我想把我的这些书放到我的个人网站上. 把所有的书都放到一个表中; 写一个脚本把这些内容按照指定格式显示. 使用PHP从MySQL读出这些,然后格式化, 完成这个很简单.

<?php

// uncomment this to see plaintext output in your browser

// header("Content-Type: text/plain");

// open connection to database

$connection = mysql_connect("localhost", "john", "doe") or die ("Unable to connect!");

// select database

mysql_select_db("db278") or die ("Unable to select database!");

// execute query

$query = "SELECT * FROM library";

$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

// iterate through rows and print column data

// in the form TITLE - AUTHOR

while ($row = mysql_fetch_row($result))

{

echo "$row[1] - $row[2]\n";

}

// get and print number of rows in resultset

echo "\n[" . mysql_num_rows($result) . " rows returned]\n";

// close database connection

mysql_close($connection);

?>

Here's what the output looks like:

输出:

<place w:st="on"><placename w:st="on"><span lang="EN-US" style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: Courier; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体">Mystic</span></placename><span lang="EN-US" style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: Courier; mso-font-kerning: 0pt; mso-bidi-font-family: 宋体"> <placetype w:st="on">River</placetype></span></place> - Dennis Lehane

For Kicks - Dick Francis

XML and PHP - Vikram Vaswani

Where Eagles Dare - Jack Higgins

[4 rows returned]

The process here is fairly straightforward: connect to the database, execute a query, retrieve the result and iterate through it. The example above uses the mysql_fetch_row() function to retrieve each row as an integer-indexed array, with the array indices corresponding to the column numbers in the resultset; however, it's just as easy to retrieve each row as an associative array (whose keys correspond to the column names) with mysql_fetch_assoc(), or an object (whose properties correspond to the column names) with mysql_fetch_object().

过程相当易懂: 连接数据库, 执行一个查询,利用循环得到结果. 这个例子使用 mysql_fetch_row() 函数,循环得到每一组数据放在integer-indexed array中, 根据数据索引得到数据集中相应的数据; 也可以使用 mysql_fetch_assoc()生成an associative array来完成以上工作 (whose keys correspond to the column names) ; 或者使用 mysql_fetch_object()生成 object (whose properties correspond to the column names).

The problem with this script? Since I've used MySQL-specific functions to interact with the database, it's going to crash and burn the second I switch my data over to PostgreSQL or Oracle. Which is where the database abstraction layer comes in.

这段代码有问题吗? 我们使用MySQL特有的函数来连接数据库, 如果我想把我的数据迁移到 PostgreSQL or Oracle就会出现问题.马永. 数据抽象层诞生于此.

Anatomy Class

In order to demonstrate how the abstraction layer works, I'll use it to rewrite the previous example - take a look:

为了演示数据抽象层是怎样工作的,我重写了上面的例子 :

<?php

// uncomment this to see plaintext output in your browser

// header("Content-Type: text/plain");

// include the ADODB library

include("adodb.inc.php");

// create an object instance

// configure it for a MySQL connection

$db = NewADOConnection("mysql");

// open connection to database

$db->Connect("localhost", "john", "doe", "db278") or die("Unable to connect!");

// execute query

$query = "SELECT * FROM library";

$result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg());

// iterate through resultset

// print column data in format TITLE - AUTHOR

while (!$result->EOF)

{

echo $result->fields[1] . " - " . $result->fields[2] . "\n";

  $result->MoveNext();

}

// get and print number of rows in resultset

echo "\n[" . $result->RecordCount() . " rows returned]\n";

// close database connection

$db->Close();

?>

This output of this snippet is equivalent to that of the previous one; however, since it uses the ADODB abstraction library, rather than PHP's native API, to interact with the database server, it holds out the promise of continuing to work no matter which database I use. I'll show you how in a minute - but first, a quick explanation of the functions used above:

输出的结果同上一个例子; 这里例子使用了 ADODB抽象库代替原来的PHP API, 连接数据库, 保证在任何数据库上都可以正常运行.我将告诉你这是怎样办到的 – 但是首先, 对程序进行简单的解释:

1. The first step is, obviously, to include the abstraction layer in your script.

1. 第一步, 在你的代码中包含抽象层.

<?

// include the ADODB library

include("adodb.inc.php");

?>

Note that the ADODB library doesn't consist of just this file - in fact, there are over thirty different files included with the library, many of them drivers for different databases. You don't need to worry about including each and every one; simply include the main class file, as above, and it will invoke the appropriate drivers or additional classes as required.

注意 事实想ADODB 不是只有一个文件, 而是超过三十个不同的文件组成,不同的文件驱动不同的数据库. 你不用担心去包含所有的这些; 只需要包含主类文件就可以了, 它会调用适当的驱动或者另外需要的东西.

2. Next, create an instance of the ADODB class.

2. 接下来建立 ADODB 类.

<?

// create an object instance

// configure library for a MySQL connection

$db = NewADOConnection("mysql");

?>

The parameter passed to the object constructor tells ADODB which type of database you're trying to connect to. In this case, I've used the argument "mysql", since I'm going to be connecting to a MySQL database server; you could just as easily use "pgsql" or "oci8" or...

通过对象构造传递你要使用的数据库类型到 ADODB. 我使用了"mysql", 我将会连接到MySQL 数据库服务器; 你也可以使用 "pgsql" 或 "oci8" 或...

3. Next, it's time to open up a connection to the database. This is accomplished via the Connect() method, which must be passed a set of connection parameters.

3. 下一步, 打开数据库连接. 通过 Connect() 方法, Connect()方法里写入服务器的一些信息.

<?

// open connection to database

$db->Connect("localhost", "john", "doe", "db278") or die("Unable to connect!");

?>

Reading this may make your head hurt, but there *is* method to the madness - roughly translated, the line of code above attempts to open up a connection to the MySQL database named "db278", on the host named "localhost", with the username "john" and password "doe".

看到这里你也许会有一些头疼,mayongzhan, 这段代码是要连接到名字叫”db<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="278" unitname="”">278”</chmetcnv>的数据库 ,这个数据库在名为"localhost"的主机上,使用登陆名"john" 和密码 "doe".

4. Once the Connect() method does its job, the object's Execute() method can be used to execute SQL queries on that database.

4. 一旦 Connect() 方法开始工作,对象的 Execute()方法就能执行SQL语句了.

<?

// execute query

$query = "SELECT * FROM library";

$result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg());

?>

Successful query execution returns a new object containing the results of the query. Note the special ErrorMsg() method, which can be used to obtain the last error message generated by the system.

正确的查询会返回一个新的包含查询结果的对象. 注意 ErrorMsg() 方法, 这个方法会获得系统产生的最后一条错误信息.

5. The result object returned in the previous step exposes methods and properties that can be used to extract specific fields or elements from the returned resultset.

5. 结果对象在上一步产生,这个对象可以被用于产生具体的域或者元素.

<?

// iterate through resultset

// print column data in format TITLE - AUTHOR

while (!$result->EOF)

{

echo $result->fields[1] . " - " . $result->fields[2] . "\n";

  $result->MoveNext();

}

?>

In this case, the object's MoveNext() method is used, in combination with a "while" loop, to iterate through the returned resultset and display individual fields (these individual fields are accessed as array elements of the object's "fields" property). This data is then printed to the output device.

这段代码中使用了MoveNext() 方法, 结合 "while" , 循环显示结果.通过echo输出到浏览器.

Once all the rows in the resultset have been processed, the object's RecordCount() method is used to print the number of rows in the resultset.

结果集中所有行被显示, RecordCount()方法用于记录结果集的行数.

<?

// get and print number of rows in resultset

echo "\n[" . $result->RecordCount() . " rows returned]\n";

?>

6. Finally, with all the heavy lifting done, the Close() method is used to gracefully close the database connection and disengage from the database.

6. 最后, 所有的事都完成, Close()方法关闭数据库连接并脱离数据库.

<?

// close database connection

$db->Close();

?>

In the event that someone (maybe even me) decides to switch to a different database, the only change required in the script above would be to the line instantiating the connection object - a new argument would need to be passed to the object constructor, with a new database type. Everything else would stay exactly the same, and my code would continue to work exactly as before.

当你要选择另外一个数据库时,只需要改变示例中建立对象那段代码中的一小部分 – 传递到新建对象的构造中. 所有的东西都会保持正确, 代码也可以象以前一样正常运行.

This is the beauty of an abstraction layer - it exposes a generic API to developers, allowing them to write one piece of code that can be used in different situations, with all the ugly bits hidden away and handled internally. Which translates into simpler, cleaner code, better script maintainability, shorter development cycles and an overall Good Feeling. Simple, huh?

这就是抽象层最好的地方 – 只留给开发者一个通用的 API, 让他们写一次代码可以用在所有的地方,(myz:</s