PEARMDB database abstraction layer-write once-run anywhere

Writeonce-runanywhere: running it everywhere is a marketing slogan of 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 wide range of customers. So why bind yourself to a database factory SyntaxHighlighter. all ();

Write once-run anywhere: running it everywhere is a marketing slogan of 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 wide range of customers. Therefore, why do you need to tie yourself to a database vendor? The database abstraction layer allows you to develop your applications independently from the database. However, in general, their impact on performance exceeds what you want, or they are not abstract enough to eliminate all code related to a specific database. What will this article teach me? This article will give a good introduction to the database abstraction package pear mdb. The focus of this article will be on more advanced features provided by MDB over similar packages, such as data type abstraction and XML-based schema management. The basic understanding of PHP and SQL is recommended. Why another database class? Generally, a web project is added to an existing IT infrastructure after the customer determines that the RDBMS (relational database management system) is used. Even if it is not because different budgets may affect the data you choose for deployment. In the end, as a developer, you may simply prefer not to bind yourself to a vendor. Since then, it means to maintain the version of each supported data or sacrifice more performance, but more usability than is required: Go to pear mdb. MDB is a database abstraction layer designed to make PHP programs unrelated to RDBMS become simple processes. Most of the other PHP's so-called database abstraction layers provide a public API and very limited abstraction for all supported databases (most of which are only for sequences ). MDB can also be used to abstract the data sent and received by all databases. Even the database schema can be defined as an RDBMS-independent format. However, it provides these features while maintaining high performance and ease of use. This is achieved through in-depth observation of two popular database abstraction layers, pear db and Metabase, which are then integrated. In addition, this opportunity is taken advantage of the convergence process to clear their integrated APIs and any design that affects performance. How does MDB appear? As early as the fall of 2001, I was looking for an abstract package that could make my company's program framework separate from RDBMS. This goal is to reduce the number of code related to a specific database to zero. I found that the only package providing such a feature is Metabase. However, some Metabase APIs are designed to be compatible with PHP3 to make it uncomfortable. However, we decided that Metabase was our only choice. However, even after a performance improvement patch is added to Metabase, we still feel that we have given up too much performance. We met the author of Metabase at the PHP International Conference in 2001 and talked about the benefits of making something like Metabase part of the PEAR project. Soon afterwards, we started a discussion on the possible advantages of pear db and Metabase integration on the PEAR mail list. After much discussion in our company, we decided to undertake this task. After months of hard work, we now have MDB's first stable release. What does MDB provide to you? MDB combines most of pear db and Metabase features. In fact, the only feature of pear db that no longer exists is to return an object as a result set. We gave up this feature because it is not commonly used and the performance loss is very obvious. A lot of development time is used to make the API as easy as possible. In the end, MDB provides these features at least as fast as pear db and much faster than Metabase. List of these most important features: the query prepared by the OO-style API simulates the full data type abstraction (including LOB support) of all the data passed in and retrieved from the database) transactions support database/table/index/sequence creation/discard/change RDBMS-independent database schema management inherited into the PEAR framework (PEAR installer, PEAR error handling, etc.) how can it be used? MDB provides some advanced abstract features. Remember that these features are only important for selection. However, it is very important to use RDBMS-independent PHP programs. A simple example of how to use MDB is shown at the end of the article in the "link and document" section. As mentioned above, 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 these example scripts in the CD packaged together with this article. However, we need to install MDB first. It is actually very easy to use the PEAR installer. I cannot fully describe the PEAR installer in this article, but I have heard that the PEAR framework will be discussed in detail in the next issue. Let the installation program run on Windows, but the support is still a little odd. For * nix systems, you need to install the CGI version of PHP on your system and simply run the following command: lynx-source go-pear.org | php after the installation is complete, you just need to enter a line of command then all done. Pear install MDB if the previous process does not work for you, you always have the option to directly obtain the package from the pear mdb homepage. The URL is listed at the end of the article. Data type abstraction is used because most databases tend to have some personality or quirks. it is very important for MDB to hide these differences for developers. MDB defines its own internal data types to achieve this: text, boolean, integer, decimal, float, date, time, time stamp, large objects (file ). All data transmitted to and obtained from the database can be converted to the internal format of MDB or be converted back from the internal format of the database. The example script in 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 <. $ timeout; this query fails if it is sent to the database. The reason is that the value stored in $ name needs to be converted to the correct string format. This may mean that the content of $ name may have special escape characters or be surrounded by quotation marks. Pear db provides the method DB:. quote (). In MDB, this method is called MDB: getTextValue (). The difference is that MDB provides such a function for each data type listed above. Therefore, we can 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); for demonstration, let's assume that I only want to obtain the first line. MDB: queryRow () gets the first line. it releases the result set and returns its content, so it is exactly what we want. $ Result = $ mdb-> queryRow ($ query); however, different RDBMS uses different formats to return data such as dates. Therefore, it is important to return data in the same format no matter what the RDBMS is. This can be done semi-automatically by MDB. All you need to do is to tell you what type the Result column will be, and MDB will handle the conversion work. The simplest way is to pass such information to the query function. $ Types = array (timestamp, integer); $ result = $ mdb-> queryRow ($ query, $ types ); this indicates that the first column type of the MDB result set is timestamp and the second column is integer. All query functions can accept such meta information as optional parameters. Data can also be set later with MDB: setResultTypes. Depends on the database to which the data is obtained, and the data will be converted accordingly. The data format of timestamps in MDB follows the ISO 8601 standard. Other packages such as PEAR: Date can process this format. MDB also provides some data format conversion functions in the MDB_Date class, which can be optional. Because a considerable number of RDBMS return integer data in the same way, there is no need to convert integer data. Therefore, to achieve a slight performance improvement, you can do this: $ types = array (timestamp); $ result = $ mdb-> queryRow ($ query, $ types ); in this way, only the first column of the result set will be converted. Of course, if MDB is used to return databases with different integers, this may become a problem. However, a slight improvement in performance may not be worth this risk. However, once again, it shows that these features are only available for choice. 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 transferred to the database, but the query structure is the same, which can be quite convenient. Advanced databases can store parsed queries in memory to accelerate performance. Listing 1 $ alldata = 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 for the execute statement. The data is automatically 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 need to set any data type for the result column. The supported data types include LOB (large object), which enables us to store files in the database. Binary files are stored in BLOB (a large binary object) and common text files are stored in CLOB (a large character object. In MDB, you can only use pre-prepared INSERT and UPDATE queries to store LOB. You can use MDBA: setParamBlob () or MDB: setParamClob () to set the values of the pre-prepared LOB fields. Both functions are expected to pass 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 wocould 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 data, inputfile, or outputfile. The first two are used when you want to write the LOB into the database. If you have a LOB stored in the variable, you should directly read the LOB from the file when you need to use inputfile. Finally, outpufile should be used when you want to read LOB from the database. Depends on whether you use data or