PEARMDB Database Abstraction Layer - Write Once - Run Anywhere_PHP Tutorial

Write once - run anywhere This is a marketing slogan for Java, 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 broad customer base. So why tie yourself to a certain database vendor? The database abstraction layer enables you to develop your applications independently of the database. However, often they impact performance more than you would like, or they are 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 of the article will be on more advanced features of MDBs beyond those offered by similar packages, such as data type abstraction and XML-based schema management. Basic understanding of PHP and SQL recommended. Why do we need another database class? Typically, web projects are added to an existing IT infrastructure after the customer has determined which RDBMS (relational database management system) they want to use. Even that's not the case because different budgets may affect what data you choose to deploy. Ultimately, you as a developer may simply prefer not to tie yourself to any one vendor. From now on, it means keeping versions of each supported data or sacrificing more performance but gaining more usability than necessary: ​​Enter PEAR MDB. MDB is a database abstraction layer focused on making writing RDBMS-agnostic PHP programs a simple process. Most other so-called database abstraction layers for PHP provide a common API for all supported databases and very limited abstractions (mostly just for sequences). MDB on the other hand can be used to abstract all data sent and received by the database. Even the database schema can be defined in an RDBMS-independent format. But it provides these features while still maintaining high performance and simplicity of use. This was achieved by taking a deep look at two popular database abstraction layers, PEAR DB and Metabase, and then merging them. And during the integration process, we took this opportunity to clean up their integrated APIs and any designs that affected performance. How did MDB come about? Back in the fall of 2001, I was looking for a database abstraction package that might make my company's programming framework RDBMS independent. The goal is to reduce the amount of database-specific code to zero. The only package I've found that provides such functionality is Metabase. But some parts of Metabase have an uncomfortable API for compatibility with PHP3. Nonetheless, we decided that Metabase was our only option. But even after adding a performance improvement patch to Metabase, we still felt we were giving up too much performance. We met the authors of Metabase at the PHP International Conference in 2001, and we talked about the benefits of having something like Metabase be part of the PEAR project. Shortly afterwards, another discussion started on the PEAR mailing list about the possible benefits of converging PEAR DB and Metabase. After many discussions in our company, we decided to take on this task. After months of hard work, we now have the first stable release of MDB. What does MDB offer you? MDB combines most of the features of PEAR DB and Metabase. In fact, the only feature of PEAR DB that no longer exists is returning an object as a result set. We dropped this feature because it is not commonly used and the performance penalty is very obvious. A lot of development time is spent making the API as useful as possible. Ultimately, MDB provides these features very highly and is at least as fast as PEAR DB and significantly faster than Metabase. List of these most important features: OO style API Prepared query simulation Full data type abstraction for all data passed in and out of the database (including LOB support) Transaction support database/table/index/sequence creation/ Abandon/change RDBMS-independent database schema management inherited into the PEAR framework (PEAR installer, PEAR error handling, etc.) So how to use it? MDB provides some very advanced abstraction features. It's important to remember that these features are optional only. But it is very important to use them when writing RDBMS-independent PHP programs. An example showing how simple it is to use MDB is in the "Links and Documentation" section at the end of the article. As mentioned earlier, the focus of this article is to introduce the features that make MDB different from other PHP database abstraction layers. You can find the code for all of these example scripts on the CD packaged with this article. But first we need to install MDB. This is actually very easy using the PEAR installer. I can't fully cover the PEAR installer in this article but I hear the next issue will discuss the ins and outs of the PEAR framework in great detail. Work is underway to get the installer to run on Windows but support is still a bit quirky.For *nix systems you will need the CGI version of PHP installed on your system and simply run the following command: lynx -source go-pear.org|php After the installation is complete you only need to enter one more line of command and you are all set . pear install MDB If the previous procedure doesn't work for you, there is always the option of getting the package directly from the PEAR MDB home page. The URL is listed at the end of the article. Leveraging Data Type Abstraction Because most databases tend to have some personality or quirks, it's important for MDBs to hide these differences from developers. MDB achieves this by defining its own internal data types: text, boolean, integer, decimal, float, date, time, time stamp, large objects (files). All data passed to and obtained from the database can be converted to and from the MDB's internal format. The example scripts related to this section can be found in the datatype directory. Let's look at the following query: $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 getTextValue($session); $query .= AND lastaccess getTimestampValue($timeout); To demonstrate, let’s Suppose I only want to get the first row. MDB::queryRow() gets the first row, it frees the result set and returns its contents, so it's exactly what we want. $result = $mdb->queryRow($query); But different RDBMS use different formats to return data like dates. Therefore, if we then want to perform calculations on some data, it is important to return the data in the same format regardless of the RDBMS chosen. This can be done semi-automatically by the MDB. All you need to do is tell what type your result column will be and MDB will handle the conversion. The easiest way is to pass such information to the query function. $types = array(timestamp, integer); $result = $mdb->queryRow($query, $types); This tells MDB that the first column of the result set is of type timestamp and the second column is of integer. All query functions can accept such metainformation as optional parameters. Data can also be set afterwards using MDB::setResultTypes(). Depending on the database the data was fetched from, it will then be transformed accordingly to return the data. The data format of timestamps inside MDB follows the ISO 8601 standard. Other packages like PEAR::Date can handle this format. MDB also provides some data format conversion functions in the MDB_Date class, which can be optionally included. Because quite a few RDBMSs return integer data in the same way, there is no need to convert the integer data. Therefore, to get a slight performance improvement you can do this: $types = array(timestamp); $result = $mdb->queryRow($query, $types); This way only the first column of the result set will be converted. Of course, this can become a problem if the MDB is used to return integers from different databases. However, the slight performance improvement may not be worth the risk. But once again, it shows that the use of these features is only optional. Listing 1 shows an example of using prepared queries. This can be quite convenient if you have to run a large number of queries where the only difference is that the data is passed to the database, but the structure of the query is still the same. Advanced databases can store parsed queries in memory to speed up 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 $alldata will be used in the execute statement. The data will automatically be converted into the correct format. Because this is an insert statement, the second parameter of MDB::execute() is set to NULL because we won't have any result columns for which we need to set the data type. Among the supported data types are LOBs (Large Objects), which allow us to store files in the database. Binary files are stored in BLOBs (Binary Large Objects) and regular text files are stored in CLOBs (Character Large Objects). In an MDB you can only store LOBs using prepared INSERT and UPDATE queries.You can set the value of a LOB field in a prepared query using MDBA::setParamBlob() or MDB::setParamClob(). Both functions expect to be passed a LOB object, which 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 be a very long string container the CLOB data ); $clob = $mdb->createLob($character_lob); As you can see, MDB::createLob() is passed a relational array. 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 LOBs 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 LOBs from the database. Depends on whether you use data or inp

http://www.bkjia.com/PHPjc/531976.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/531976.htmlTechArticleWrite once - run anywhere This is a marketing slogan for Java, but it is also PHP one of the key features. Many business models rely on operating system independence...