Use DB2 9 native XML and PHP to simplify XML applications

Introduction
PHP supports Simple Web application development and deployment environments. This is one of the reasons for its widespread application. The Native XML feature of DB2 9 further simplifies the development process. This simplification is embodied in the following:

Less application code, less complexity Simple link mode Better management of pattern evolution due to business changes

In this article, we will build on this basis and demonstrate the effectiveness of using the DB2 Native XML feature to simplify application code and relational patterns. It also describes the impact of business demand changes on data (schema evolution) and application code and relational patterns.
To illustrate our reasoning, we will describe through a simulated online store that sells antique silver ware to registered customers.
The following are some highlights of the scenario:

Setting the PHP Environment Ease of integration of DB2 Native XML Functions with PHP applications (including Web Services written in PHP and XQuery) Use XQuery, stored procedures, and views to convert business logic and data into databases.

The DB2 XML feature used in this scenario includes the following:

Store XML documents in columns in a Breakdown Structure Search and publish using XQuery Support for XML in DB2 stored procedures and views Improve Performance Using XML Indexes

To highlight the impact of native DB2 XML support on PHP application code and relational schema design, this scenario creates a parallel environment that uses a database that does not contain any XML features (for example, mySQL ). We will study the differences between the two environments in application code, database queries, and relational patterns. The reason for selecting a specific code, mode, or query, and alternative (if possible) is also described.
Scenario
This scenario simulates online stores that sell antique silver to registered customers. Because one purpose of this scenario is to describe different database environments and their impact on application code, we will describe the two applications at the same time. One application uses DB2 Native XML, another application uses MySQL-like open source code RDMS with limited XML functionality or no XML functionality.
Therefore, customers visiting the Web site will see a page containing two vertical panels. Each panel displays a version of the same application, providing the same user experience, but using different databases at the backend:

DB2 with native XML support Other RDBMS (in this example, DB2 without any XML function)

To show the differences in application code, each panel is further divided into two horizontal frameworks. The above frameworks show online stores, and the following sections show code segments. When you perform any operation (such as clicking a type or product image), the above section generates a new page. The following section shows the Code required to create this page.
Figure 1. Sample Application panel
498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 355 alt = sample application panel src = "/files/uploadimg/20060809/1134560 .jpg" width = 572>
This shows that although the user experience in any application has not changed, the complexity of the Code has greatly changed. This comparison highlights the benefits of using the DB2 Native XML feature for common SMB applications written in PHP.
Note: we assume that the business data is already in XML format, although the database may not have any XML functions. This will generate PHP application code (such as simple DOM) that can be used to use XML functions in databases ). Databases with limited XML functionality or no XML functionality store XML data as CLOB/BLOB data types, or split them into relational fields.
Features and user experience during Web site browsing
The Web site will provide you with an index to list the types and brands of all the silver available in the store. When you click a type or brand, a list of products of the type or brand is displayed. If you select any item in the list, the details of the item are displayed on the page. You can add these items to the shopping cart. Once the user submits the order, a purchase order will be created and an invoice will be provided to the user based on this purchase order. You can check the items in the shopping cart at any time. Users can also get a report on all the items they have purchased in the past.
Application Architecture
Figure 2 shows the basic architecture of the sample application.
Figure 2. Application Architecture
498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 431 alt = application architecture src = "/files/uploadimg/20060809/1134561 .gif" width = 473>
Relationship and XML Mode
XML document and Mode
Native XML storage requires no association between XML columns and specific XML modes. Any validation of the XML document inserted into the database is performed in the insert statement explicitly using the SQL/XML letter. The appendix contains XML document examples.
Link Mode
For these two databases, the relational modes used to store these XML documents will be different.
For DB2 Native XML, there will be three tables, each containing two columns.
Figure 3. DB2 Native XML Schema
498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 72 alt = "link mode of the native XML scenario" src = "/files/uploadimg/20060809/1134562 .gif" width = 460>
For RDBMS without XML support, there will be four tables, each containing multiple columns:
Figure 4. Relational modes of scenarios without XML support
498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 261 alt = "link mode for scenarios without XML support" src = "/files/uploadimg/20060809/1134563 .jpg" width = 563>
It can be seen that, compared with RDBMS without XML support, the relational schema of DB2 Native XML is very simple.
By storing the procurement order document as BLOB, we try to keep the purchase order table mode as simple as possible in the basic relational database. When you view the history of generated orders, this operation is very effective.
DB2 PHP driver
Before proceeding to the PHP application code, let's take a look at the PHP DB2 driver. The ibm_db2 driver supports two ways to connect to the database: cataloguing and non-cataloguing. The directory connection can be a local database (if a DB2 server is running locally) or a remote DB2 server node. The second method is usually used for remote Non-cataloguing connections. You need to build a connection string (similar to a jdbc url) to establish a non-cataloguing connection. Run the following code to connect to the cataloguing database. (The client application does not need to know or pay attention to the local or remote connection during cataloguing .)

$ Conn = db2_connect ($ dbname, $ dbuser, $ dbpass ); If (! $ Conn ){ Echo db2_conn_errormsg (); Die ("Unable to connect to database! "); }

You can also use db2_pconnect to create a persistent connection to the database. When db2_close is called, the persistent connection will not be closed because the connection handle will be retained in the request. For more information about the IBM DB2 driver for PHP, visit http://www.php.net/manual/en/ref.ibm-db2.php. In the following code segment, assume that $ conn is a valid connection handle.
Fill Database
Before activating a Web site, you need to use customer information and product catalog to fill the database. We will not detail how to obtain this data for our scenario. Assume that the file is included in the local file system as an XML document. The following shows an example of the PHP code segment required to connect to the database and execute the SQL insert statement.
DB2 Viper
Because each product document contains the product ID attribute, we need to use the SimpleXml API of PHP to extract this ID.
Note: using this API is much easier than operating DOM objects. This object is the only choice before PHP version 5.

1. Create a database connection:
   

   $ Conn = db2_connect ($ dbname, $ dbuser, $ dbpass );

   
   
2. Open the document from the file to become a variable:
   

   $ FileContents = file_get_contents ("products/p1.xml ");

   
   
3. Create a simple XML object from this variable:
   

   $ Dom = simplexml_load_string ($ fileContents );

   
   
4. Extract the product ID from the document:
   

   $ ProdID = (string) $ dom ["pid"];

   
   
5. Create prepared statements and insert the XML document into the database:
   

   $ Stmt = db2_prepare ($ conn, "insert into xmlproduct VALUES (?, ?) ");

   
   
6. Pass the product ID extracted from the document as a parameter together with the document to the query:
   

   Db2_execute ($ stmt, array ($ prodID, $ fileContents );

   
   

Note that inserting data into an XML column is no different from inserting data into any CLOB column. Because the new version of DB2 allows implicit Analysis of XML data during insertion, we do not need to explicitly call XMLPARSE for the input value. If we want to retain irrelevant spaces around the XML tag, we can use the XMLPARSE function with the reserve whitespace option.
Note: The queries in these code segments are represented in italics to distinguish them from the PHP application code.
Non-XML RDBMS
Because this database does not have any XML functions, the product documentation must be decomposed into two Relational Tables. The ing information between the link mode and the XML mode is directly embedded in the PHP application code.

1. First load the document to the DOM:
   

   $ FileContents = file_get_contents ("$ products/p1.xml "); $ Dom = simplexml_load_string ($ fileContents );

   
   
2. Now, separate a single element of the product into a local variable:
   

   $ ProdID = (string) $ dom ["pid"]; $ ProdName = (string) $ dom-> description-> name; $ ProdDetails = (string) $ dom-> description-> details; $ ProdPrice = (float) $ dom-> description-> price;

   
   
3. The image URLs of each product must be stored in a separate image table:
   

   $ Images = array (); Foreach ($ dom-> description-> images-> image as $ image ){ Switch (string) $ image [type']) { Case thumbnail ': $ prodImgThumb = (string) $ image; $ ProdImgAlias = (string) $ image [alias ']; If (! $ ProdImgAlias) $ prodImgAlias = NULL; $ Stmt = db2_prepare ($ conn, "insert into sqlimages (Pid, Type, Alias, Location) VALUES (?, ?, ?, ?) "); Db2_execute ($ stmt, array ($ prodID, thumbnail ', $ prodImgAlias, $ prodImgThumb )); Case full ': $ ProdImgFull = (string) $ image; $ ProdImgAlias = (string) $ image [alias ']; If (! $ ProdImgAlias) $ prodImgAlias = NULL; $ Stmt = db2_prepare ($ conn, "insert into sqlimages (Pid, Type, Alias, Location) VALUES (?, ?, ?, ?) "); Db2_execute ($ stmt, array ($ prodID, full ', $ prodImgAlias, $ prodImgFull )); } }

   
   
4. Currently, the ibm_db2 driver cannot properly process NULL variables as parameters to execute functions. Therefore, we use an unforced solution:
   

   If (! $ ProdBrand) $ prodBrand = ""; If (! $ ProdCategory) $ prodCategory = ""; If (! $ ProdImgFull) $ prodImgFull = "";

   
   
5. Now save the product information in the product table:
   

   $ Stmt = db2_prepare ($ conn, "insert into sqlproduct (Pid, Name, Details, Brand, Category, Price, Weight, Size, Description) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) "); Db2_execute ($ stmt, array ($ prodID, $ prodName, $ ProdDetails, $ prodBrand, $ prodCategory, $ ProdPrice, $ prodWeight, $ prodSize, $ fileContents ));

   
   

Create Homepage
The home page contains indexes of the types and brands of all available products in the online store. The right area of the index displays a list of all items.
Figure 5. Home Page
498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 451 alt = home src = "/files/uploadimg/20060809/1134564 .jpg" width = 438>
Create an index list for the category and Brand
The index is created by querying the list of unique types and brands of all products in the database. This list is created when the application is started.
DB2 Viper

1. First, create a DB2 view to use XQuery to list types. XQuery loops in all products and returns all unique types:

   Create view Categories (Category) as select distinct (XMLCAST ( XMLQUERY (for $ I in $ t/product/description/category return $ I' Passing by ref t. description as "t" returning sequence) As varchar (128) FROM Xmlproduct AS t

   
   
2. Now the view is called from the application:

   $ Stmt = db2_exec ($ conn, "SELECT * FROM Categories "); While (list ($ cat) = db2_fetch_array ($ stmt )){ Echo" ";}

   
   The application code in both cases is similar. Creating an XML data view allows us to easily query the view, which helps us understand the XML structure of the product from the application code. You need to change the XQuery in the view to find the Brand element. Similarly, you also need to view the Brand column for SQL calls.
   Influence of Pattern Evolution on index list
   Based on Customer feedback, we need to allow users to browse the site to find silver-plated goods or goods manufactured in sterling silver. Let's take a look at the impact of adding subcategories to the index on the following items: XML mode, relational mode, query, and PHP application code.
   Impact on XML mode and document instances
   Add a new attribute (catx) to the category element in the product XML mode ). All new XML documents of the product are now properly filled with silver or sterling silver:
   Miscellaneous
   Impact on link mode
   • DB2 Viper
     This does not require any changes to the relational mode, because the XML document is stored in a single column.
   • Non-XML RDBMS
     In the Basic relational database, you need to change the schema of the product table and add another column named catx. This may involve deleting and re-inserting all product documents.
   Impact on queries
   • The XQuery required for DB2 Viper to create an index will change to include this new attribute in the condition. Similarly, the XQuery used to list items based on the selection in the index will also change to include new conditions.
   • Non-XML RDBMS
     The insert statement changes to include new columns.
   The query required to create an index will change to include this new column in The WHERE clause. Similarly, queries used to list items based on the selection in the index will also change to include new conditions.
   Impact on application code
   • DB2 Viper
     The application code will not be changed.
   • Non-XML RDBMS
     
     • Separate the required DOM code to separate the sub-category information.
     • The INSERT statement requires additional parameters.
     • All data may need to be inserted again, which causes end users to be unable to operate for a period of time.
   Items are listed when you click a type or brand.
   When you click a specific category or brand, a list of all products of the category or brand is generated. Each item in the list has a brief description and a URL to the thumbnail image. This list is displayed on the home page and its format is set.
   Figure 6. Goods List in a category
   498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 356 alt = src = "/files/uploadimg/20060809/1134565 .jpg" width = 572>
   DB2 Viper
   In DB2, XQuery not only creates a list, but also converts it to HTML output, so that the browser can directly use it. With this feature of XQuery, not only business logic can be introduced, but also can be released to the database server, so as to effectively make the intermediate application very simple. This is precisely because PHP is used instead of Java.™Or VS. NET®.

   $ Xquery = for $ I in $ t/product Let $ thumb: = $ I/description/images/image [@ type = "thumbnail"] Where $ I/description/category = ". htmlentities ($ category )." Return