Written once, run everywhere

About PHP Database API has different functions for different databases, and some people have tried to use PHP's object-oriented features to make some packages. Among them, there are more famous adoDBs, phplib. Later, Pear DB in the Pear project in the world is even more leaders. These packages with an object-oriented database API are generally referred to as database abstraction layers.

This article describes some excellent functions of PEAR DB to fuse the Metabase library in PEAR, and a very good introduction to MDB, which is easy to use, and very powerful. The author is the master of MDB.

I want to get the latest original and translation of my recent PHP / PEAR, please visit my homepage

Write overce - Run Anywhere Written - run everywhere

PEAR MDB Database Abstract Layerpear MDB Database Abstract Layer

Author: Lukas Smith Translator: taowen

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. this is Java's a marketing slogan, but it is also One of the key features of PHP. Many business models depends on operating system independence to ensure that the product can be sold to a wide range of customer groups. Thus, why should you tie your own database manufacturers? The database abstraction layer allows you to develop your app independently. However, usually their impact of performance exceeds what you want, or they are not abstract to eliminate all code related to the specific database.

What Will this Article TEACH ME? 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 This article will have a good introduction to the database abstract package PEAR MDB. The focus of the article will be a more advanced feature provided by MDB transcending a similar package, such as data type abstraction, and XML-based SCHEMA management. The basic understanding of PHP and SQL is recommended.

Why Another Database Class? Why is 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 usual, web project The customer has determined that the IT infrastructure that is already added after using the RDBMS (Relational Database Management System). Even if it is not because of the possible data you choose to deploy due to different budgets. In the end, you may be simple as a developer to tie yourself in a vendor. Since then, it means that each supported data remains version or sacrifices more performance but more than necessary, easy-to-use: walk into the 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. MDB is a focus on making the preparation of RDBMS The unrelated PHP program is a database abstraction layer for a simple process. Most of the other PHP's so-called database abstraction layers provide a public API and very limited abstraction (mostly for sequences). On the other hand, MDB can be used to abstract data from all databases to send and receive. Even database schema can be defined as RDBMS-independent format. But it provides these features while still maintaining high performance and easy to use. This is obtained by deeply observing the two popular database abstraction layers, Pear DB, and Metabase, and after they are fused. And during the fusion process, this opportunity cleans the API after they fuse and any design of the impact performance. How does the How Did MDB Come to Be? MDB appear?

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 MEAR DB AND METABASE. AFTER MUCH Discussion At my company we deci Ded to take up this task. After SEVERAL MONTHS of HARD WORK WORK. As early as 2001, I am looking for a database abstraction that can be able to make my company's program frame with RDBMS. package. This goal is to reduce the number of code related to a particular database to zero. I found that a single package that provides such a function is Metabase. But Metabase has some parts because it is uncomfortable with PHP3 compatible API. Despite this, we decided that Metabase is our only choice. But even after adding a performance improvement to Metabase, we still feel that we give up too much performance. We met the author of Metabase at the 2001 PHP International Conference, and we talked about the benefits of making things like Metabase as part of the PEAR project. Soon, a discussion was started on the PEAR mail list on Pear DB and Metabase. After our company has conducted many discussions, we decided to assume this task. After several months of hard work, we now have the first stable release of MDB. What does mdb do for you? What did MDB give 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: MDB combines most of the features of Pear DB and Metabase. In fact, the only one in the Pear DB is no longer existing as a result set returns an object. We gave up this feature because this characteristic is not commonly used and is very obvious for performance loss. Many development time is used in making APIs as much as possible. Finally, MDB is very highly providing these features at least as fast as Pear DB and is much faster than Metabase. The most important characteristic of the list: OO-style APIprepared queries emulationfull data type abstraction for all data passed to and from the database (including LOB support) transaction supportdatabase / table / index / sequence creation / dropping / alteringRDBMS independent database schema managementIntegrated into the Pear Framework (Pear Installer, Pear Error Handling ETC.)

OO-style API pre-sub-query simulation to all data types that are passed and from the data removed (including LOB support) transaction support database / table / index / sequence creation / discard / change RDBMS-independent database SCHEMA Management inherits into the PEAR framework (PEAR installer, PEAR error handling, etc.)

So how does it work? So how do it use?

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 issu.mdb offers some very advanced abstract characteristics. Remember that these features are only important to choose from. But it is very important to use them when writing RDBMS-independent PHP programs. A display using MDB is a "link and literature" section of the end of the article. As mentioned earlier, the focus of the article describes those features that make MDB different from other PHP database abstraction layers. You can find the code for all of these example scripts in the CD packaging with this period. But first we will need to get MDB installed. This is actually quite easy using the PEAR installer. I can not 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:.. but first we You need to install the MDB. This is actually very easy to use the PEAR installer. I can't tell the PEAR installer in this article but I heard that the next phase will discuss the pear framework in the PEAR framework. Let the installer run in Windows work as but support still have a little quirky. For * NIX systems you need PHP CGI version installed in your system and simply run the command:

Lynx -Source Go-pear.org | PHP

After Completion The Installation Process You Simply Need To Type One More Command and You Are All Set. You only need to enter a line of command after the installation is complete.

Pear Install MDB

If The Above Does NOT Work for you there is always the option of getting the package................ If the previous process is not used by you, there is always a The PEAR MDB home page is directly obtained option. The URL is listed in the finals of the article. MAKING Uses of Data Type Abstract uses data types

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: Because most databases tend to have some personality or quirks, it is very important to hide developers to developers for MDB. MDB achieves this: Text, Boolean, Integer, Decimal, Float, Date, Time, Time Stamp, Large Objects. All data passed to the database and the data obtained from the database can be converted into an internal format of MDB or transform from the internal format of the database. This section related example scripts can be found in the DATYPE directory. Let's take a look at the query below:

$ 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 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 Format. This query is to fail if it is sent to the database. The reason is that the value stored in the $ name needs to be converted to the correct string format. This may mean that the content of $ name may have a special escape character or surrounded by quotation marks. Pear DB provides this method DB: .quote (). This method is called MDB :: getTextValue () in MDB. The difference is that MDB provides such a function to each previously listed data. Therefore, 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);

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. In order to make A demo, let us assume that I just want to get the first line. MDB :: queryRow () gets the first line, it releases the result set and returns its content, so it is 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: But different RDBMS returns a format used in data such as a different date. different. Therefore, if we have to calculate some data, regardless of the selected RDBMS, it is important to return the data in the same format. This can be completed by MDB semi-automatic. All you need to do is telling your results that will be what types, MDB will process the work of 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 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 OPTIONALL. This tells 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 an optional parameter. Data can also be set with MDB :: SetResultTypes () after things. Depending on the database obtained, it will then return the data returned by the corresponding conversion. The data format of the TimeStamps inside the MDB is to follow 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.

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:. Because a considerable number of RDBMS returns the integer data in the same manner, no Necessary to convert integer data. Thus, in order to obtain a slight performance improvement you can do: $ TIMESTAMP '); $ Result = $ MDB-> QueryRow ($ query, $ typees);

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 This is used by the first column of the result set will be converted. Of course, if MDB is used to return a database of different numbers, this may become a problem. However, slight performance improvements may not be worthy of this risk. But once again, it shows the use of these features is only available.

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 shows an example of using pre-prepared queries. If you have to run a lot of queries and the only difference is that the data is passed to the database, but the structure of the query is still the same, these can be quite convenient. Advanced databases can save performance in memory in memory to accelerate 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 ($ ALDATA AS $ ROW) {$ MDB-> Execute ($ P_QUERY, NULL, $ ROW, $ Param_Types);

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 10 NEED TO SET DATA TYPES. All four arrays stored in the AllData will be used for the Execute statement. The data will be automatically converted to the correct format. Because this is a plug-in statement, the second parameter of MDB :: execute () is set to NULL because we will need to set the data type if we will have any result. 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 (). There is also a LOB (large object) in the supported data type, which makes we can save files in the database. Binary files are stored in the BLOB (binary large object) and the ordinary text file is stored in the Clob (character large object). In MDB, you can only store LOB using pre-pre-pre-pre-pre-pre-sub-INSERT and UPDATE query. Using MDBA :: SetParamblob () or MDB :: SetParamClob () You can set the value of the LOB domain that pre-prepares the query. Both functions are expected to deliver 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 Be a Very Long String Container The Clob Data'); $ Clob = $ MDB-> CreateLob ($ Character_LOB);

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 Well Store The Above Lob's in The Database. If you can see, MDB :: CreateLob () Transfer a relationship array. The value of the TYPE key may be one of the following: Data, InputFile or OutputFile. The first two times for 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 LOBs from the database. Depending on whether you use data or inputfile you need to specify a value to specify a value, like the example above. Now, we will store the previous LOB to 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');

$ results = $ 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'. For the database Get the above file, we need to select data from the database and create a LOB object using MDB :: CreateLob (). This time we will set 'type' for 'OutputFile'

$ MDB-> Query ('SELECT B_DATA from files where id = 1');

$ binary_lob = array ('type' => 'outputfile', 'result' => $ results, 'Row' => 0, 'Field' => 'b_data', 'binary' => 1, 'filename' => './myfile2.gif'); $ binary_lob); Now We can read the lob from the result set using mdb :: readlob (). Passing a foreth 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 (). Now we can use MDB :: readlob () to read LOBs from the result set. Passing length 0 Give MDB :: Readlob () means that the entire LOB is read and stored in the file you specified in front of it. Once the task is completed, we can release the resources. You can also set any length greater than zero and check MDB :: endOflob () using a while loop to read the LOB.

$ 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. Note that you don't give this acquisition function and Bulk get functions like MDB :: fetchall () because it will cause problems in most PHP database extensions. At some time, MDB may be able to use the BULK acquisition function to get LOB.

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 If we see this section, the Native Data Type Set of the MDB feature itself is automatically mapped in the native data type in the database. This guarantees what kind of data we send and receive from the database, it can use the same format with the useful RDBMS. As I mentioned in this section, this is clear that the data type used by the database is MDB expected. This requirement is used to ensure that the price takes place is small. The next section will teach us how MDB is assisted to use the correct data type in the database. Making Use of XML Schema Files Using 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 Section Can Be found in the xml_schema Directory. Using the features described in the previous paragraph, you can write a real database independent program. But MDB attempts to take a step forward: it allows you to define your schema with XML. A manager converts this SCHEMA to the necessary SQL statements for each RDBMS. This means 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 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 IT DOES NOT YET EXST. 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. We will now write an XML Schema file from the head. First, we must define an XML document. The database definition is included in a Database tag. The name of the database is defined using the NAME tab. The CREATE tag tells the Manager database to be created when it does not exist. If you split your schema file into several files you first submit to the manager to 1. auth 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. As you may have guessed from the name of the database auth The purpose of this database is to store user data for a simple verification program. Listing 2 defines a table we can store user data.

Listing 2

User user_id integer 1 1 < Default> 0 handle text 20 1 < / default> is_Active boolean 1 n

As you can see, things can get a bit lengthy here as to be expected when using XML No need to worry:. We are working on a browser based tool called MDB_frontend that will make this process much easier I will talk about this project further. down into this article a bit more Hopefully, the advantage of this pretty verbose representation of the table is that things are somewhat self explanatory The table in the last example is called users and we have defined 3 fields:.. user_id of type integer, handle of type text and is_active of type boolean. Remember that MDB handles the type abstraction for you if you pass it the necessary metadata as shown in the previous section. you also need not to worry about what MDB maps these types to in your RDBMS. The Other tags you can use in Each of the Field Declarations Are Optional: Length, notnull, unsigned and default. If you can see, if you use XML, you can expect any lengthy. Don't worry: We have a browser-based tool called MDB_Frontend makes this process easier. I will talk about this project behind this article. It is possible that this is very distinctive, and the advantage of the table description is very obvious. The table in the previous example is called Users and we define 3 domains: Type User_ID, type is a type of Handle and type for text. Remember if you pass the necessary metadata MDB as you like as the previous section for you to process the type abstraction. You don't need MDB to map these types to what you have in your RDBMS. Other tags that can also be used in each domain declaration are optional: Length, NotnULl, Unsigned, and Default. The next thing that we now the user_id is to ensure That User_id is unique by Placing The Proper Index on The user_id field. The index definition goes forin the declaration tag (Listing 3). The next thing we need to do now is Make sure the user_id is unique by placing the appropriate index at the user_id domain. Index definitions are within the declaration tag (Listing 3).

Listing 3:

User 1 user_id_index user_id ascending 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 Weavence Missing Now Is a Sequence To Generate Unique User ID's for US: Defines in Listing 3 Creating a unique rising sorted index of User_ID_Index in the domain USER_ID. Of course, we can simply add another domain tag to specify more than one domain in the index definition. We still have not mentioned the sequence of the unique user ID.

users_user_id 1

user_id

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 (). an example of a very detour . I saw a row, we saw a SEQUENCE tag first, followed by a Name tab of a specified sequence name. He follows a Start tag that defines the initial value of the sequence. Now let's open an optional ON label. Here we need to set the specified domain in a table. This information is the maximum value of the user_id domain for the manager to set the value of the sequence to the User_ID domain. If the UserS table is empty, the value specified in the START tag is used as an alternative. Note that the value specified in the START tag is the first value returned by we 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. Of course, you can also initialize the table using any value. For example, you might want to use the management user you always want to include management users in your program to initialize the previous form. In order to do this, we need to add an initialization tag to the table tag. Listing 4 defines a row after another one-to-use INSERT tag included.

Listing 4

Userses user_id 1 handle default is_Active y 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 As you can see from the previous example, all we need to do is set values ​​for each domain for the table. We now know the necessary basic knowledge to create an MDB XML Schema. The next step is to pass this Schema file to the MDB Manager.

$ manager = new MDB_Manager; $ INPUT_FILE = 'Auth.Schema'; // We do not have to connect to a specify a specific Database At this time $ dsn = "mysql: // $ user: $ pass @ $ host"; $ Manager-> Connect ($ DSN); $ Manager-> Updatedatabase ($ INPUT_FILE, $ INPUT_FILE. '.before');

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. We now have a new name called Auth database, it has a table called User. There is an index in the domain USER_ID. And there is also a line in the table. We also have a sequence called User_user_id, which will be initialized to 1. Therefore, the next value in the sequence is 2. Finally, a copy of Schema is created with name auth.schema.beforefore. This is because we pass the optional second parameters for MDB_manger :: updatedatabase (). In the next section we will see why you want to create this copy. 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 word). All of this is very amazing but it becomes better. Many situations need to make changes in certain places. For example, we may decide to turn the name of the table from Users into people. We may also need to add a domain PWD to store the cryptographic domain (please check the reserved word of TextBox).

Reserved Words Reserved Word

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. We did not call the reason why the domain is Password is that it is a reserved word in Interbase. Because we need RDBMS independence, the MDB manager either gives a warning either when the fail_on_invalid_names option is set to really (this is default) failed.

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:.. In the past, when You may now be in the pain of this new Schema now. However, since MDB is automatically completed. In Listing 5, we have modified our form definition: Listing 5

people Users PWD text 32 < NOTNULL> 1

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 (). T e ion '.................................................................... Because we rename the table from the user, we also need to change all the references to the original name, such as the sequence we established. The index in the ON label needs to be changed to the PEOPLE table. In order to achieve this, we pass the new old version of SHCEMA to the manager. This wine is why we create a .before file when we call MDB_Manager :: updatedatabase () for the first time. This ensures that we have an old version of SHCEMA to compare with the new version.

$ INPUT_FILE = 'Auth.Schema'; $ Manager-> Updatedatabase ($ INPUT_FILE, $ INPUT_FILE. '.'. ');

That's all! The users Table is now Called People and Now We Also Have a PWD Field. All is this! Users performance is called People and we also have a PWD domain.

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. I have to look at the last feature of the XML Schema format. This feature is especially important if you want to programmatize managers. Suppose you have several customers who have the same verification program running in your database server. Each customer has a server running on this server with the same SCHEMA only with a minute difference: the name of the database. It is possible to save the Schema file for each customer. It is feasible because the update cycle may not be the same, this is not the case of our example verification program. All customers are updated at the same time. The XML Schema file allows us to use variables for this purpose.

name

We can set the variable name at run time to whatver we may need. We now set the variable to anything we need at runtime.

Foreach ($ clients as $ name) {$ variables = array ('name' => $ name) $ Manager-> Updatedatabase ($ INPUT_FILE, $ INPUT_FILE. '. BEFORE', $ VARIABLES);

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. XML Schema Management is another very important part of the database abstraction concept provided by MDB. It allows us to keep our Schema definitions independent of specific RDBMS. However, using this format also ensures that the use of the correct native data type thus and MDB can correctly map its native data type. Finally, because the data is based on XML, the tools written or read the XML Schema files are easier. Sounds Great But My Application Already Uses ... It sounds good but my app has been used ...

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 TOCIFIC Questions Feel Free To Email Me. Most readers may find that they are in such a situation - they already have a large number of programs running on other database abstraction layers. Due to the origin of MDB, most PEAR DB users should find that MDB feels very similar because MDB's API is based on PEAR DB. Metabase users should find that all of their preferences have corresponding things in MDB. The XML Schema format and Metabase are the same. A complete guide to guide you to transplant the already written program to MDB beyond the scope of this article, but I will use this opportunity to give some tips. If you have any specific questions, you can ask me if you feel free.

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:. to put your PEAR DB The program ported to MDB, the best starting point is Pear Wrapper. You can use Pear Wrapper to run your program. Wrapper, of course, has added some additional burden, so you may have some need to transplant the native interface. Then the first step is to list all the PEAR DB functions currently used by your program. Then look at the difference between Wrapper from in any API. There are two key differences you have to pay attention to: Results set is no longer object and all query methods for data types that allow you to pass the result set will result in a little change in the parameter order. The first difference means that the acquisition function cannot be measured on the target. $ results = $ db-> query ($ sql); $ row = $ result-> fetchrow ();

You will now Have to Call The MDB Object for Fetching: You must now call MDB objects to get:

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

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 second difference by looking at the wrapper can easily be resolved. If you can see again in Wrapper, you can easily pass NULL in the data type of the result set. Now, you should be able to use MDB. Of course, you don't really get the benefits of the high-level characteristics of MDB. This is most likely to make some changes to your current database schema. The manager can attempt to reverse the XML Schema file from the existing database. 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 fix the generated XML Schema, but it will give you a good start.

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. If you want to put your already existing programs from Metabase to MDB you will have to change all function calls. Viewing Metabase Wrapper needs to change what will become very obvious. If you know that regular expressions you might be able to do most of this replacement. In any case, you should go forward and run your original favorite level of abstract characteristics but now is MDB. You may pay attention to the function name becomes more short. If you do some performance tests, you will also see how to improve the performance. So What Does The Future Look Like for MDB? So what will MDB look like?

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 The MDB may no longer be the original 1.0 Release. After the original MySQL and PostgreSQL drivers, MDB will also have an ODBC driver and possible more drivers. This is one of the key areas of the MDB development process. Once MDB keeps Pear DB in the drive, it is likely to be a 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. But there is another key area in development: MDB_Frontend project. MDB_Frontend will be a phpMyAdmin based on MDB and MDB managers. With this tool, you will be able to browse the database stored in the RDBMS supported by MDB. MDB_Frontend will also display native and MDB data types. The simulated characteristics such as the sequence in MySQL will be hidden. Users will only see a list of sequence lists instead of a stored sequence finger, and in MySQL is how the sequence is simulated. And MDB_Frontend will help transplant the existing databases to comply with the original data types that MDB expected. It will also help create and update the XML Schema file. Some initial work have been completed but many work needs to be added before public release. 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 Thankfful for any Other feedback Like Feature Requests. Drive and MDB_Frontend are all focus of current development, and there are many users in MDB that may be needed: like Bulk to get the integration of the LOB domain, Others may need external and primary key support. If you have been involved in testing and implementation, open source things will accelerate. But I am also very grateful to the feedback on the characteristics of demand.

Some Final Thoughts 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 beh huge success, we are supentian mdb will be imp / us ARE Happy To Have Helped Making PHP Even Better. After the hard work of months, MDB is approved in the current Pear DB and Metabase users. I also hope that users who have not been convinced by other database abstraction layers to realize MDB to them. Of course, there are still many programs that need special tailors for RDBMS, and tools like MDB are simply an unnecessary additional burden and restrictions. In general, I am very pleased that we make a decision to lead MDB development in our company. At the beginning, I have a little worried about trying to please Pear DB and Metabase but the result may not be a bit everywhere. Another source of concern is whether the PHP community will help it develop. I am very happy with the PHP community and help write the driver and the core of the MDB. Therefore, we think this project is a great success. We also believe that MDB will get greater improvements. And we are very happy to help PHP. 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. Lukas Smith is the lead author of PEAR DB. It actively contributes to multiple PHP open projects and is a builder of Backendmeida, which focuses on PHP development.

Links and Literature links and literature

Pear MDB HomePage: Pear.php.Net/package-info.php?package=mdbpear 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=/browse.html/package/20.html

Simple Benchmark: Freshmeat.net/screenshots/30313/

转载请注明原文地址:https://www.9cbs.com/read-26593.html

---

9cbs

New Post(0)