|
关于 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 作者：Lukas Smith 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. 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. 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. 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. How did MDB come to be? 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. What does MDB do for you? 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: OO-style API OO 风格的 API 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. 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: 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. 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: $session = '098f6bcd4621d373cade4e832627b4f6'; 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. // convert $timeout to the MDB timestamp format 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. $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: $types = array('timestamp', 'integer'); 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. 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: $types = array('timestamp'); 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. 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 $alldata = array( $p_query = $mdb->prepareQuery('INSERT INTO numbers VALUES (?,?,?)'); foreach ($alldata as $row) { 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. 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(). $binary_lob = array( $character_lob = array( 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. $p_query = $mdb->prepareQuery('INSERT INTO files (id, b_data, c_data) VALUES (1, ?, ?)'); $mdb->setParamBlob($p_query, 1 , $blob, 'b_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->query('SELECT b_data FROM files WHERE id = 1'); $binary_lob = array( 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($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. 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. Making use of XML schema files 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. 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 version="1.0" encoding="ISO-8859-1" ?> 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. Listing 2 <table>
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). Listing 3: <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: <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(). 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. Listing 4 <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. $manager = new MDB_Manager; 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. 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). 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). 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: Listing 5 <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. $input_file = 'auth.schema'; That's all! The users table is now called people and now we also have a pwd field. 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 version="1.0" encoding="ISO-8859-1" ?> We can now set the variable name at run time to whatever we may need. foreach($clients as $name) { 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. 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. 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: $result = $db->query($sql); You will now have to call the MDB object for fetching: $result = $mdb->query($sql); 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. 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. So what does the future look like for 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. 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. 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. 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. 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. 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/ |
温馨提示：喜欢本站的话，请收藏一下本站！
本站发布的Win7纯净版系统、Win10纯净版和GHOST系统仅为个人学习测试使用，请在下载后24小时内删除，不得用于任何商业用途，否则后果自负，请支持购买微软正版软件！
本站所有资源全部来自于网络资源,如侵犯到您的权益,请及时通知我们($email$),我们会及时处理.
Copyright © 2018-2022 系统之家 关于本站
