Simplify XML application _php tutorials with DB2 9 native XML and PHP

Introduction
PHP supports a simple WEB application development and deployment environment. This is one of the reasons why it has been widely used. The native XML functionality of DB2 9 further simplifies the development process. This simplification is reflected in the following areas:

Less application code and reduced complexity A simpler relational pattern Better manage the evolution of patterns that occur as a result of changing business needs

In this article, we will build on that basis and demonstrate the usefulness of simplifying application code and relational schemas with the DB2 native XML functionality. The impact of changes in business requirements on data (schema evolution) and the impact on application code and relational patterns are also described.
To illustrate our reasoning, the store sells antique silverware to registered customers through a mock-up of the online store's usage scenario.
Some of the points that we will describe in this scenario include:

How easy it is to set up a PHP environment How easy it is to integrate DB2 native XML functionality with PHP applications, including WEB services written in PHP and XQuery Use XQuery, stored procedures, and views to put business logic and data transformations into the database.

The DB2 XML functionality used in this scenario will include the following:

Storing an XML document in a column by a columnar structure Search and publish using XQuery Supporting XML in DB2 stored procedures and views Using XML indexes to improve performance

To highlight the impact of using DB2 native XML support on the PHP application code and relational schema design, the scenario creates a parallel environment that uses a database that does not contain any XML functionality (for example, MySQL). We will look at the differences between the two environments in terms of application code, database queries, and relational schemas. It also explains the reasons for selecting a specific code, pattern, or query and, if possible, an alternative scenario.
Scenario
This scenario simulates an online store selling antique silverware to registered customers. Because one of the purposes of this scenario is to illustrate the different database environments and their impact on application code, we will explain to two applications that one application uses DB2 native XML and the other application uses MySQL-like open source RDMS with limited XML functionality or without any XML functionality.
As a result, customers who visit the WEB site will see a page with two vertical panels. Each panel will display a version of the same application, providing the same user experience, but using a different database on the backend:

DB2 with native XML support Other RDBMS (in this case, a DB2 that does not use any XML functionality)

To show the differences in the application code, each panel is further divided into two horizontal frames, the upper frame showing the online store, and the following section showing the code snippet. When a user makes any action, such as clicking a class 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 will show that although the user experience has not changed in any one application, the complexity of the code has changed a lot. This contrast will highlight the benefits of using DB2 native XML functionality for ordinary SMB applications written in PHP.
Note: Our assumption of this scenario is that the business data is already in XML format, although the database may not have any XML capabilities. This produces PHP application code (such as the simple DOM) that can be used for the database using XML functionality. A database with limited XML functionality or no XML functionality stores XML data as a Clob/blob data type, or is split into a relationship field.
features and user experience when browsing in a Web site
The WEB site will provide users with an index that lists the types and brands of all the silverware available in the store. When a user clicks on a class or brand, a list of items of that kind or brand is displayed. Select any item in the list to display the item details on the page. Users can add these items to the shopping cart. Once the user submits the order, a purchase order is created and an invoice is provided to the user based on the purchase order. Users can check the items in the shopping cart at any time. Users are also able to get reports on all items they have ordered 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>
Relational and XML schemas
XML Documents and schemas
Native XML storage does not require XML columns to be associated with a particular XML schema. Any validation required for an XML document inserted into the database is explicitly performed using the Sql/xml function in the INSERT statement. The appendix contains examples of XML documents.
Relationship Mode
For these two databases, the relational schema 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= ' the relationship pattern of native XML stories 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 patterns for scenarios with no XML support
498) this.width=498; ' OnMouseWheel = ' javascript:return big (This) ' height=261 alt= ' relationship pattern for scenarios with no XML support src= Uploadimg/20060809/1134563.jpg "width=563>
As you can see, the relational schema for DB2 native XML is simple compared to an RDBMS without XML support.
We have kept the purchase order table pattern as simple as possible in the basic relational database by storing the purchase order document as a BLOB. This action is very noticeable when you view the build order history.
DB2 PHP Drivers
Before we start talking about PHP application code, let's take a look at PHP's DB2 driver. The IBM_DB2 driver supports two methods of connecting to a database: cataloging and non-cataloging. The catalog connection can be a local database (if a DB2 server is running locally) or a remote DB2 server node. The second method is typically used for remote non-cataloging connections, and a connection string (similar to a JDBC URL) needs to be constructed to establish a non-cataloging connection. The following code connects to the catalog database. (The client application does not need to know or focus on whether the catalog connection is local or remote.) ）

$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 actually be closed because the connection handle will remain in the request. For more information about the IBM DB2 driver for PHP, please visit http://www.php.net/manual/en/ref.ibm-db2.php. In the following code snippet, assume that the $conn is a valid connection handle.
Populating the database
Before a Web site can be opened, the database needs to be populated with customer information and product catalogs. For our scenario, we will not elaborate on how to get this data. Assume that it is contained in a file in the local file system as an XML document. The following shows an example of the PHP snippet 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 extract the ID using the PHP SimpleXml API.
Note: Using this API is much easier than manipulating DOM objects, which is the only option before PHP version 5.

2. To create a database connection:
   

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

   
   
4. Open the document from the file and become a variable:
   

   $fileContents = file_get_contents ("Products/p1.xml");

   
   
6. Create a simple XML object from this variable:
   

   $dom = simplexml_load_string ($fileContents);

   
   
8. Extract the product ID from the document:
   

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

   
   
10. Create the prepared statement and insert the XML document into the database:
    

    $stmt =db2_prepare ($conn, "INSERT into Xmlproduct VALUES (?,?)");

    
    
12. Pass the product ID extracted from the document along with the document as a parameter to the query:
    

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

    
    

Note there is no difference between inserting data into an XML column and inserting data into any CLOB column. Because this new version of DB2 allows implicit parsing of XML data at insert time, we do not need to explicitly call Xmlparse on incoming values. If we want to preserve extraneous spaces around XML tags, you can use the Xmlparse function with the reserve whitespace option.
Note: The queries in these snippets are represented in italics to distinguish it from the PHP application code.
non-XML RDBMS
Because this database does not have any XML capabilities, the product documentation needs to be decomposed into two relational tables. The mapping information between the relational schema and the XML schema is directly embedded in the PHP application code.

2. Load the document into the DOM first:
   

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

   
   
3. The
4. now splits 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;

   
   
6. the image URL for each product needs to be stored in a separate image table:
   

   $images = a Rray (); 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)); } }

   
   
8. The current implementation of the IBM_DB2 driver does not properly handle a NULL variable as a parameter in order to execute a function; therefore we use a non-mandatory solution:
   

   if (! $prodBrand) $prodBrand = ""; if (! $prodCategory) $prodCategory = ""; if (! $prodImgFull) $prodImgFull = "";

   
   
10. 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 a home page
The home page contains an index of the kinds and brands of all available products in the online store. The right-hand area of the index displays a list of all items.
Figure 5. Home
498) this.width=498; ' OnMouseWheel = ' javascript:return big (This) ' height=451 alt= home src= '/files/uploadimg/20060809/ 1134564.jpg "width=438>
Create an indexed list of categories and brands
Indexes are created by querying the list of only one class and brand for all products in the database. This list is created when the application is started.
DB2 Viper

2. The DB2 view is created first to use the XQuery listing kind, and XQuery loops through all the products and returns all but one of the classes:

   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 (+)) from Xmlproduct as T

   
   
3. Now call the view 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 makes it easy to query the view, which helps to understand the structure of the product XML from the application code. You need to change the XQuery in the view to find the branding element, and the same SQL call needs to see the brand column.
   The effect of schema evolution on index lists
   According to customer feedback, we need to allow users to browse the site to find silver-plated goods or products made from sterling silver. Let's take a look at the effects of adding subcategories to the index on the following items: XML schemas, relational schemas, queries, and PHP application code.
   impact on XML schemas and document instances
   Adds a new attribute (CATX) to the kind element in the product XML schema. All new XML documents for the product are now properly populated with either sterling silver or silver plating:
   Miscellaneous
   impact on the relationship model
   • DB2 Viper
     This will not require any changes to the relational schema because the XML document is stored in a single column.
   • Non-XML RDBMS
     In the basic relational database, you will need to change the schema of the product table and add another column named Catx. This may involve deleting and re-inserting all product documentation.
   impact on the query
   • DB2 Viper The XQuery required to create the index changes to include this new attribute in the condition. Similarly, the XQuery used to list goods based on the selection in the index will also change to include the new condition.
   • Non-XML RDBMS
     The INSERT statement changes to include the new column.
   The query that is required to create the index changes to include the new column in the WHERE clause. Similarly, queries for listing items based on the selection in the index will also change to include the new condition.
   impact on application code
   • DB2 Viper
     The application code will not have any changes.
   • Non-XML RDBMS
     
     • Additional DOM code will be required to split the sub-category information.
     • The INSERT statement will require additional parameters.
     • All data may need to be reinserted, which causes the end user to be unable to operate for a period of time.
   List items When a user clicks a category or brand
   When a user clicks a particular category or brand, a list of all items in that category or brand is generated. Each item in the list has a short description and a URL to the thumbnail image. This list appears in the home page and is formatted in it.
   Figure 6. A list of items in a category
   498) this.width=498; ' OnMouseWheel = ' javascript:return big (This) ' height=356 alt= a list of goods in a category src= "/files/uploadimg/ 20060809/1134565.jpg "width=572>
   DB2 Viper
   In DB2, XQuery not only creates a list, but also translates it into HTML output, so that the browser can use it directly. With this functionality of XQuery, not only can the business logic be rolled out, but it can also be published to the database server, effectively making the middle-tier application very simple. That's why you use PHP without using 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 http://www.bkjia.com/phpjc/446840.html www.bkjia.com True http://www.bkjia.com/phpjc/446840.html techarticle Introduction PHP supports simple WEB Application development and deployment environments. This is one of the reasons why it has been widely used. The native XML functionality of DB2 9 further simplifies the development process. This simplification ... 