PEAR MDB Database Abstraction Layer-write once-run anywhere

Data | database Write Once-run Anywhere
Write once-run anywhere

This is a Java marketing slogan, but it is also one of the key features of PHP. Many business models rely on operating system independence to ensure that products can be sold to a wide range of customer groups. So why do you tie yourself to some kind of database vendor? The database abstraction layer allows you to independently develop your application with the database. However, they typically have more performance impact than you would like, or they are not sufficiently abstract to eliminate all the code associated with a particular database.

What is this article going to teach me?

This article will have a good introduction to the database abstraction package, the PEAR MDB. The focus of this article will be on the more advanced features provided by MDB beyond similar packages, such as data type abstraction and xml-based schema management. A basic understanding of PHP and SQL is recommended.

Why do I need another database class?

Typically, Web engineering is added to an existing IT infrastructure after the customer has determined to use that RDBMS (relational database management system). Even if it's not because different budgets may affect what data you choose for deployment. Ultimately, as a developer, you might simply prefer not to tie yourself to a particular vendor. Since then, it means maintaining a version of each supported data or sacrificing more performance but gaining more than necessary ease of use: Walk into the PEAR MDB.

An MDB is a database abstraction layer that focuses on making an RDBMS-independent PHP program a simple process. Most other PHP's so-called database abstraction layers provide a common API and a very limited abstraction for all supported databases (mostly just for sequences). The MDB, on the other hand, can be used to abstract data sent and received by all databases. Even database schemas can be defined as RDBMS-independent formats. But it provides these features while still maintaining high performance and simplicity. This is achieved by looking at two popular database abstraction layers, PEAR DB and Metabase, and then merging them. And in the fusion process, taking advantage of this opportunity to clean up their fusion of the API and any impact on the performance of the design.

How does an MDB come into being?

Back in the fall of 2001, I was looking for a database abstraction package that might allow my company's program framework and RDBMS to stand alone. The goal is to reduce the number of code associated with a particular database to zero. I find that the only package that provides such a feature is Metabase. But metabase is partly because of the uncomfortable API that is compatible with PHP3. Nevertheless, we decided that Metabase was our only option. But even after adding a patch of performance improvement to Metabase, we still feel that we have given up too much performance. We met Metabase's author at the 2001 PHP International Conference and we talked about the benefits of making things like Metabase a part of the PEAR project. Shortly thereafter, a discussion began on the possible benefits of pear DB and Metabase fusion on the Pear mailing list. After a lot of discussion in our company, we decided to take the task. After a few months of hard work, we now have the first stable release of the MDB.

What does an MDB offer you?

The MDB combines most of the features of PEAR DB and Metabase. In fact, the only thing that no longer exists in the attributes of PEAR DB is to return an object as a result set. We have given up this feature because it is not commonly used and is very noticeable for performance losses. Much of the time spent on development is used to make the API as useful as possible. Eventually, the MDB very high ground provides these features at least as fast as PEAR DB and much faster than Metabase. List of these most important features:

OO-style API
Pre-prepared query impersonation
Complete data type abstraction (including LOB support) for all data that is passed in and fetched from the database
Transaction support
Database/table/index/sequence creation/Discard/change
RDBMS-Independent Database schema management
Inherit into pear frame (pear installer, pear error handling, etc.)

So how does it work?

The MDB provides some very advanced abstraction features. It is important to remember that these features are only optional. But it's important to use them when writing RDBMS-independent PHP programs. An example of how simple the use of an MDB is in the "Links and Documentation" section of the end of the article. As mentioned earlier, the focus of the article is to introduce those features that make the MDB different from the other PHP database abstraction layers. You can find the code for all of these example scripts in the CD that is packaged with this installment.

But first we need to install the MDB. Using PEAR setup This is actually very easy. I can't fully describe the pear installer in this article but I hear that the next issue will be a very detailed discussion of the inside and outside of the pear framework. It is still a bit odd to have the installer run on Windows as a work in progress. For the *nix system you need the PHP CGI version installed on your system and simply run the following command:

Lynx-source go-pear.org|php


You just need to type in one more command after the installation is complete so it's all done.

Pear Install MDB


If the previous process doesn't work for you, there's always the option to get the package directly from the PEAR MDB home page. The URL is listed at the end of the article.

Using data type Abstraction

Because most databases tend to have some personality or quirks, it is important for MDB to hide these differences from the developer. The MDB achieves this by defining its own internal data type: Text,boolean,integer,decimal,float,date,time,time Stamp,large objects (file). All data passed to and from the database can be converted into an MDB's internal format or converted back from the internal format of the database. The relevant example scripts in this section can be found in the datatype directory. Let's look at the following query:

$session = ' 098f6bcd4621d373cade4e832627b4f6 ';
Set 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 probably fail if sent to the database. The reason is that the values stored in the $name need to be converted to the correct string format. This may mean that the contents of the $name may have special escape characters or be surrounded by quotes. PEAR DB provides a method Db:.quote () for this purpose. This method in the MDB is called Mdb::gettextvalue (). The difference is that the MDB provides such a function for each of the data types listed earlier. So we can also convert $timeout to the correct format.

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);


To make a demo, let's assume I just want to get the first line. Mdb::queryrow () Gets the first row, it releases the result set and returns its contents, so it's exactly what we want.

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


But different RDBMS have different formats for returning data such as dates. Therefore, if we then have to compute some data, regardless of what the RDBMS is selected, it is important to return the data in the same format. This can be done semi-automatic by the MDB. All you need to do is tell your results column what kind of type the MDB will handle the conversion work for. The easiest way to do this is to pass the information to the query function.

$types = Array (' timestamp ', ' integer ');
$result = $mdb->queryrow ($query, $types);


This tells the MDB that the first column type of the result set is ' timestamp ' and the second column is ' Integer '. All query functions can accept such meta information as optional parameters. The data can also be set later with Mdb::setresulttypes (). Depending on the database that the data is obtained from, it will then be returned by the corresponding conversion of the data. The timestamps data format within the MDB is consistent with the ISO 8601 standard. Others like PEAR::D ate such a package can handle this format. The MDB also provides some data format conversion functions in the Mdb_date class that can be optionally included.

Because quite a few RDBMS return integer data in the same way, it is not necessary to convert the integer data. Thus, you can do this in order to achieve a slight performance improvement:

$types = Array (' timestamp ');
$result = $mdb->queryrow ($query, $types);


This allows only the first column of the result set to be converted. Of course, this can be a problem if the MDB is used to return a different database of integers. However, a slight performance improvement may not be worth the risk. But again, it shows that the use of these features is only optional.

Listing 1 shows an example of using a prepared query. If you have to run a large number of queries and the only difference is that the data is passed to the database, the query structure is still the same, which can be quite handy. Advanced databases can store parsed queries in memory to speed performance.

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);
}


All four arrays stored in the $alldata will be used for the EXECUTE statement. The data will automatically be converted to the correct format. Because this is an INSERT statement, the second parameter of Mdb::execute () is set to NULL because we will not have any result columns that require us to set the data type.

There are also LOB (large objects) in the supported data types that enable us to store files in the database. Binary files are stored in BLOBs (binary large objects) and regular text files are stored in CLOB (character large objects). In an MDB you can only use a prepared INSERT and UPDATE query to store the LOB. Using Mdba::setparamblob () or Mdb::setparamclob () you can set the value of the LOB field for a prepared query. Two functions are expected to pass a LOB object, and it can be created using Mdb::createlob ().

$binary _lob = Array (
' Type ' => ' inputfile ',
' FileName ' => './myfile.gif '
);
$blob = $mdb->createlob ($binary _lob);

$character _lob = Array (
' Type ' => ' data ',
' Data ' => ' This would is a very long string container the CLOB data '
);
$clob = $mdb->createlob ($character _lob);


As you can see, Mdb::createlob () is passed an array of relationships. The value of the Type key may be one of the following: data, Inputfile, or outputfile. The first two are used when you want to write the LOB to the database. If you have a lob stored in a variable, you should read the LOB directly from the file when you need to use Inputfile. Finally, Outpufile should be used when you want to read the LOB from the database. Depending on whether you use data or inputfile you need to assign a value to the Filename key or the Data key, as in the example above. Now, we're going to store the LOB in front of the database.

$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);


To get the above file from the database, we need to first select the data from the database and use Mdb::createlob () to create the LOB object. This time we'll set ' Type ' as ' 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 length 0 to Mdb::readlob () means that the entire LOB is read and stored in the file we specified earlier. Once the task is complete, we can release the resources. You can also set any length greater than 0 and use a while loop to check Mdb::endoflob () to read the LOB.

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


Notice that you don't confuse the Get function with the bulk get function like Mdb::fetchall () because it will cause problems in most PHP database extensions. At some point, the MDB might be able to use the bulk fetch function to get the LOB.

As we see in this section, the native data type set of the MDB attribute itself is automatically mapped to the native data type in the database. This ensures that no matter what data we send and receive from the database, it can be used in the same format regardless of the RDBMS used. As I mentioned at the beginning of this section, this obviously requires that the data types used by the database are expected by the MDB. This need is used to ensure that the cost of mapping is very small. The next section will teach us how the MDB assists in using the correct data type in the database.


Using XML schema files

With the features described in the previous paragraph, you can write a real database-independent program. But the MDB tries to take a step forward: it allows you to define your schema 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 all supported RDBMS. Examples of 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 data base label. The name of the database is defined by using name tags. The Create label tells the manager whether the database needs to be created when it does not exist. If you split your schema file into several files, you set the Create to 1 in the file that you first submitted to the manager.

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


Perhaps you have guessed from the database name auth that the purpose of this database is to store user data for a simple validator. Listing 2 defines a table in which we can store user data.

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, as you can expect with XML, things get a little verbose. Don't worry: We have a browser-based tool called mdb_frontend making the process simpler. I'll talk about the project later in the article. Perhaps the advantage of this extremely detailed tabular description is very obvious. The tables in the previous example are called Users and we define 3 domains: user_id of type integers, handle of text, and is_active of type logic. Remember that if you pass the necessary metadata MDB as you did in the previous section, you are dealing with type abstractions. You don't need an MDB to map these types to what's in your RDBMS. Additional tags that can be used in each domain declaration are optional: length,notnull,unsigned and default.

The next thing we need to do now is to make sure user_id is unique by placing the appropriate index in the USER_ID domain. The index definition is within the declaration tag (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 creates a unique ascending-sorted index in the domain user_id named User_id_index. Of course, we can simply add another field label to specify more than one field in the index definition. What we still don't mention is the sequence that produces a unique user ID for us.

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


The previous example is very detours. One line. Looking over, we see first opening a sequence tag, followed by a name tag that specifies the name of the sequence. This follows a start tag that defines the initial value of the sequence. Now, we open an optional on tag. Here we need to set a specified field in a table. This information is the maximum value that the manager uses to set the value of the sequence to the user_id field of the Users table. If the users table is empty, the value specified in the Start label is used as an alternative. Note that the value specified in the start tag is the first value that we call Mdb::nextid ().

Of course, you can also use any value to initialize the table. For example, you might want to initialize the preceding table with an administrative user that you always want to include in your program. To do this, we need to add a initialization tag to the table label. Listing 4 defines a row after another line that is included with the Insert label.

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 set a value for each field in the table. We now know the basic knowledge necessary to create an XML schema for an MDB. The next step is to pass the schema file to the MDB manager.

$manager = new Mdb_manager;
$input _file = ' Auth.schema ';
We don't have to connect to a specify a specific
$DSN = "mysql://$user: $pass @ $host";
$manager->connect ($DSN);
$manager->updatedatabase ($input _file, $input _file. '. before ');


We now have a new name called Auth database, which has a table called users. There is an index in the domain user_id. And there's a row in the table. We also have a sequence called users_user_id, which will be initialized to 1. So the next value in the sequence is 2. Finally, a copy of the schema is created with the name Auth.schema.before. This is because we passed the optional second argument to Mdb_manger::updatedatabase (). In the next section we'll see why we're creating this copy.

All of this is amazing but it's getting better. In many cases, procedures need to be changed in some places. For example, we may decide to change the name of the table from users to people. We may also need to add a domain pwd to store the password field (check the reserved word for the textbox).


Reserved word

The reason we don't call that domain password is that it's a reserved word for a domain name in InterBase. Because we need RDBMS independence, the MDB manager either gives a warning or fails when the Fail_on_invalid_names option is set to True (this is the default value).

In the past, you may now be in the midst of the pain of turning everything you already have into this new schema. However, because the MDB can do this work automatically. In Listing 5 We have made changes to our table definitions:

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 changes, but I like to mention a possible trap before that. Because we renamed the table from users to people, we also need to change all references to the original name, such as the sequence we built. The index in the on label needs to be changed to point to the people table. To achieve this, we passed the old and new versions of Shcema to the manager. This wine is why we created a. Before file the first time we called Mdb_manager::updatedatabase (). This ensures that we have an older version of Shcema to compare with the new version.

$input _file = ' Auth.schema ';
$manager->updatedatabase ($input _file, $input _file. Before ');


That's all it is! The users table is now called people and we also have a PWD domain.

I will now look at the last feature of the XML schema format. This feature is especially important if you want to use a programmatic manager. Suppose you have several customers with the same validator running on your database server. Each customer has a server running on this server that has the same schema only a tiny difference: the name of the database. It is possible to save schema files separately for each customer because the update cycle may not be the same, which is not the case for our example verifier. All customers here are updated at the same time. The XML schema file allows us to use variables for this purpose.

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


We now set the variables at run time to whatever we need.

foreach ($clients as $name) {
$variables = Array (' name ' => $name)
$manager->updatedatabase ($input _file, $input _file. Before ', $variables);
}


XML Schema management is another very important part of the database abstraction concepts provided by MDB. It allows us to keep our schema definition independent of a particular RDBMS. But using this format also ensures that the correct native data type is used so that the MDB can correctly map its native data type. Finally, because the data is xml-based, it is easier to write tools that generate or read XML schema files.


Sounds good, but my application has already used ...

Most readers may find themselves in a situation where they already have a large number of programs running on other database abstraction layers. Because of the origin of the MDB, most of the users of PEAR DB should find that the MDB feels very similar because the MDB API is based on Pear db. Metabase users should find that all their favorite features are in the MDB. The XML schema format is the same as the one in the Metabase. A complete guide to porting your already-written program to the MDB is beyond the scope of this article, but I'll take this opportunity to give some hints. If you have any specific questions, please feel free to ask me for a letter.

The best starting point for porting your pear DB program to an MDB is pear wrapper. You can use PEAR wrapper to run your program. Wrapper of course adds some extra burdens, so you might want to migrate to native interfaces. So the first step is to list all the PEAR DB functions that your program is currently using. Then look at the wrapper from which to find any API differences. There are two key differences you should be aware of: The result set is no longer an object and all the query methods that allow you to pass the data type of the result set will result in a slight change in the order of the parameters. The first difference means that the FETCH function cannot be invoked on the result object.

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


You must now call the MDB object to get it:

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


The second difference can be easily solved by observing the wrapper. As you can see in the wrapper, you can simply pass NULL where the MDB expects the data type of the result set. Your program should now be able to use the MDB. Of course, you're not really getting the benefits of the advanced features of MDB. It is most likely that you will need to make some changes to your current database schema. The manager is able to attempt to retrieve the XML schema file from the existing database in reverse. A very simple front-end can be found in the MDB package: reverse_engineer_xml_schema.php script. It is very likely that you will need to manually revise the resulting XML schema en see, but it will give you a good start.

If you want to migrate your existing programs from Metabase to MDB you will have to change all of the function calls. Looking at what Metabase wrapper needs to change will become very obvious. If you know regular expressions you might be able to do most of these replacements. In any case, you should move forward and run the advanced abstraction you liked, but now you're using an MDB. What you may notice is that the function name becomes shorter. If you perform some performance tests, you will also see considerable performance improvements.


So what will the MDB look like in the future?

The MDB may no longer be the original 1.0 release when this article is published. After the original MySQL and PostGreSQL drivers, the MDB will also have an ODBC driver and possibly more drivers. This is one of the key areas of concern in the MDB development process. Once the MDB is in the driver's way with pear DB, it is likely to become the standard database abstraction layer in the Pear framework.

But there is another key area of development: Mdb_frontend Engineering. Mdb_frontend will become a phpmyadmin based on the MDB and MDB manager. With this tool, you will be able to browse the databases stored in the RDBMS supported by the MDB. Mdb_frontend will display both native and MDB data types. Simulated features such as the sequence in MySQL will be hidden. The user will only see a list of sequences rather than a table of stored sequences, and in MySQL this is how the sequence is modeled. And Mdb_frontend will help migrate existing databases to match the native data types that the MDB expects to use. It will also help create and update XML schema files. Some initial work has been done but a lot of work needs to be added before public release.

Drivers and Mdb_frontend are all the focus of the current development, and there are many other users in the MDB who might need it: like bulk to get the integration of LOB domains, others may need external and primary key support. As always, if you are involved in testing and implementation, open-source things will accelerate a lot. But I am also grateful for the feedback like feature requirements Heyang.


Some thoughts after the text

After months of hard work, the MDB is gaining recognition among current PEAR DB and Metabase users. I also want users who are not currently persuaded by other database abstractions to be aware of the benefits that MDB gives them. Of course, there are many programs that require special tailoring of the RDBMS, and tools such as MDB simply add unnecessary burdens and limitations. In general, I am very pleased that we have made the decision to lead the MDB development in our company. At first, I was worried about trying to please both PEAR DB and Metabase, but the result could be a lot less flattering. Another source of concern is whether the PHP community will help it develop. I'm very happy that the PHP community has come and helped compose the drive and the core of the MDB. So we think this project is a great success. We also believe that the MDB will be much improved. And we're happy to be able to help PHP get better.

About the author
Lukas Smith is the main author of PEAR DB. It actively contributes to multiple PHP Kaiyuan projects and is the creator of the Backendmeida company focused on PHP development.


Links and documentation

PEAR MDB Homepage:http://pear.php.net/package-info.php?package=mdb
PEAR MDB documentation:http://www.backendmedia.com/mdb/docs/
PEAR MDB Sample script:http://cvs.php.net/co.php/pear/mdb/mdb_test.php
PEAR DB homepage:http://pear.php.net/package-info.php?package=db
Metabase homepage:http://www.phpclasses.org/mirrors.html?page=%2fbrowse.html%2fpackage%2f20.html
Simple benchmark:http://freshmeat.net/screenshots/30313/