分类:
2008-04-21 16:06:59
简介
PHP 支持简单的 Web 应用程序开发和部署环境。这是它得到普遍应用的原因之一。DB2 9的原生 XML 功能进一步简化了开发过程。这种简化体现在以下方面:
|
为突出使用 DB2 原生 XML 支持对 PHP 应用程序代码和关系模式设计的影响,该情景将创建一个并行环境,该环境使用不包含任何 XML 功能的数据库(例如,MySQL)。我们将研究这两个环境在应用程序代码、数据库查询和关系模式方面的差异。还将说明选择特定代码、模式或查询以及备选方案(如果可能)的理由。
情景
该情景模拟向注册客户出售古董银器的在线商店。因为该情景的一个目的是说明不同的数据库环境以及它们对应用程序代码的影响,所以我们将对两个应用程序进行同时说明,一个应用程序使用 DB2 原生 XML,另一个应用程序使用类似 MySQL 的开放源码 RDMS,具有有限的 XML 功能或没有任何 XML 功能。
因此,访问 Web 站点的客户将看到一个包含两个垂直面板的页面。每个面板都将显示同一应用程序的一个版本,提供相同的用户体验,但在后端使用不同的数据库:
为显示应用程序代码的差异,每个面板进一步分为两个水平框架,上面的框架显示在线商店,下面部分显示代码段。当用户进行任何操作(如单击某一种类或产品图像)时,上面部分都会生成一个新页面。下面部分显示创建此页面所需的代码。
图 1. 示例应用程序面板
这将说明,虽然在任何一个应用程序中用户的体验没有变化,但代码复杂性发生了很大变化。这种对比将突出用 PHP 编写的普通 SMB 应用程序使用 DB2 原生 XML 功能的好处。
注意:我们对此情景的假设是业务数据已经是 XML 格式的,尽管数据库可能没有任何 XML 功能。这将产生可用于数据库的使用 XML 功能的 PHP 应用程序代码(如简单 DOM)。具有有限 XML 功能或无 XML 功能的数据库将 XML 数据存储为 CLOB/BLOB 数据类型,或分割到关系字段中。
在 Web 站点中浏览时的功能和用户体验
Web 站点将为用户提供索引,列出商店中所有可用银器的种类和品牌。用户单击某一种类或品牌时,将显示该种类或品牌的货品列表。选择列表中的任何货品都会在页面中显示该货品的详细信息。用户可以将这些货品添加到购物车中。一旦用户提交了订单,将会创建采购订单并根据此采购订单向用户提供发票。用户可以随时检查购物车中的货品。用户还能够得到他们过去已订购的所有货品的报告。
应用程序体系结构
图 2 显示了示例应用程序的基本体系结构。
图 2. 应用程序体系结构
关系和 XML 模式
XML 文档和模式
原生 XML 存储不需要 XML 列与特定 XML 模式关联。需要对插入到数据库中的 XML 文档进行的任何验证都在插入语句中使用 SQL/XML 函数显式地进行。附录中包含 XML 文档示例。
关系模式
对于这两个数据库,用于存储这些 XML 文档的关系模式将有所不同。
对于 DB2 原生 XML,将有三个表,每个表包含两列。
图 3. DB2 原生 XML 模式
对于无 XML 支持的 RDBMS,将有四个表,每个表包含多个列:
图 4. 无 XML 支持的情景的关系模式
可以看出,与无 XML 支持的 RDBMS 相比,DB2 原生 XML 的关系模式非常简单。
我们已经通过将采购订单文档存储为 BLOB,在基本关系数据库中尽量保持采购订单表模式简单。当查看生成订单历史记录时,这种操作的作用将非常明显。
DB2 PHP 驱动程序
在开始讲述 PHP 应用程序代码之前,我们先了解一下 PHP 的 DB2 驱动程序。ibm_db2 驱动程序支持两种连接数据库的方法:编目 和非编目。编目连接可以是本地数据库(如果有 DB2 服务器在本地运行),也可以是远程 DB2 服务器节点。第二种方法通常用于远程非编目连接,需要构建连接字符串(类似于 JDBC URL)以建立非编目连接。以下代码连接编目数据库。(客户机应用程序不需要知道或关注编目连接时本地的还是远程的。)
$conn = db2_connect($dbname, $dbuser, $dbpass); if(!$conn) { echo db2_conn_errormsg(); die("Unable to connect to database!"); } |
$conn =db2_connect($dbname, $dbuser, $dbpass); |
$fileContents = file_get_contents("products/p1.xml"); |
$dom = simplexml_load_string($fileContents); |
$prodID = (string) $dom["pid"]; |
$stmt =db2_prepare($conn, "INSERT INTO xmlproduct VALUES (?, ?)"); |
db2_execute($stmt, array($prodID, $fileContents); |
$fileContents = file_get_contents("$products/p1.xml"); $dom = simplexml_load_string($fileContents); |
$prodID = (string) $dom["pid"]; $prodName = (string) $dom->description->name; $prodDetails = (string) $dom->description->details; $prodPrice = (float) $dom->description->price; |
$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)); } } |
if(!$prodBrand) $prodBrand = " "; if(!$prodCategory) $prodCategory = " "; if(!$prodImgFull) $prodImgFull = " "; |
$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 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 |
$stmt = db2_exec($conn, "SELECT * FROM Categories"); while(list($cat) = db2_fetch_array($stmt)) { echo " "; } |
$stmt = db2_exec($conn, "SELECT DISTINCT(category) FROM SQLPRODUCT"); while(list($cat) = db2_fetch_array($stmt)) { echo " ";} |
$xquery =for $i in $t/product let $thumb := $i/description/images/image[@type="thumbnail"] where $i/description/category = " . htmlentities($category) . " return ; $stmt = db2_prepare($conn, "SELECT XMLSERIALIZE(XMLQUERY( $xquery' PASSING BY REF T.DESCRIPTION AS \"t\" RETURNING SEQUENCE) AS CLOB(32K)) FROM xmlproduct AS t"); db2_execute($stmt); while(list($product) = db2_fetch_array($stmt)){echo $product;} |
$sql = "SELECT P.Pid, P.Name, I.Location FROM sqlproduct P, sqlimages I WHERE P.Category = ? AND I.Pid = P.Pid AND I.Type = ?"); $stmt = db2_prepare($conn, $sql); db2_execute($stmt, array($category, "thumbnail")); |
while(list($prodPid, $prodName, $prodImg) = db2_fetch_array($stmt)) { ?> } ?> |
CREATE PROCEDURE getProduct(IN id VARCHAR(10)) DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN BEGIN DECLARE c_cur CURSOR WITH RETURN FOR SELECT XMLSERIALIZE(XMLQUERY(for $i in $t/product let $thumb := $i/description/images/image[@type="thumbnail"] let $name := $i/description/name/text() let $details := $i/description/details/text() let $price := $i/description/price let $size := $i/description/size return {$name}{ for $j in $i/description/images/image[@type != "thumbnail"][1] return src="data/images/{$thumb}.jpg" width="200"/> }
{ for $j in $i/description/images/image[@type != "thumbnail"] [position() != 1] return } PASSING T.DESCRIPTION AS "t" RETURNING SEQUENCE) AS CLOB(32K)) FROM xmlproduct T WHERE Pid = id; OPEN c_cur; END; END |
$stmt = db2_prepare($conn, "CALL getProduct(?)"); db2_execute($stmt, array($pid)); list($product) = db2_fetch_array($stmt); echo $product; |
$stmt = db2_prepare($conn, "SELECT P.Name, P.Details, P.Price, P.Size, I.Location, I.Alias FROM sqlproduct P, sqlimages I WHERE P.Pid = ? AND P.Pid = I.Pid AND I.Type = ? FETCH FIRST ROW ONLY"); db2_execute($stmt, array($pid, full')); list($prodName, $prodDetails, $prodPrice, $prodSize, $prodImgThumb, $prodImgAlias) = db2_fetch_array($stmt); |
|
$stmt = db2_prepare($conn, "SELECT DISTINCT(Location) FROM sqlimages WHERE Pid = ? AND Type = ? AND NOT Location = ?"); db2_execute($stmt, array($pid, full', $prodImgThumb)); while(list($prodImg) = db2_fetch_array($stmt)) { ?> } ?> |
function getCart($cart, $taxrate) { global $conn; $result = ""; $xquery = for $dummy in (1) |
let $items := for $i in $cart/items/item let $product := db2-fn:xmlcolumn("XMLPRODUCT.DESCRIPTION") /product[@pid = $i/@pid]/description let $name := $product/name/text() let $price := $product/price/text() let $itemPrice := if($price = 0 or empty($price)) then ("$0.00") else (concat("$", $price)) return |
{xs:integer($i/@quantity)} | {$itemPrice} |
{$items} | ||
Subtotal |
Total all ${ sum( $items/noframes/text() ) } | ||
Tax ({$tax * 100}%) |
${ xs:decimal(sum($items/noframes/text())) * $tax } | ||
Grand Total< /strong> | ${ xs:decimal(sum($items/noframes/text())) * (1 + $tax) } < /strong> |
$stmt = db2_prepare($conn, "VALUES( XMLSERIALIZE( XMLQUERY($xquery' PASSING BY REF CAST(? AS XML) AS \"cart\" , CAST(? AS DECIMAL (10,8)) AS \"tax\" RETURNING SEQUENCE) AS CLOB(32K)))"); |
if($stmt) { if(!db2_execute($stmt, array($cart, $taxrate))) { return db2_stmt_errormsg($stmt); } list($result) = db2_fetch_array($stmt); if(!$result) return db2_stmt_errormsg($stmt); } else { $result = db2_stmt_errormsg(); }return $result; } |
$server = new SoapServer(null, array(uri' = > ')); $server->addFunction(getCart'); $server->handle(); ?> |
$cartXML = " foreach($cart as $cpid => $quantity) { $cartXML .= " } $cartXML .= " |
$client = new SoapClient(null, array(location' => ', uri' => ')); $taxrate = 0.0; |
echo $client->getCart($cartXML, $taxrate); ?> |
foreach($cart as $pid => $quantity) { $stmt = db2_prepare($conn, "SELECT Name, Price FROM sqlproduct WHERE Pid = ?"); db2_execute($stmt, array($pid)); if($stmt) { list($prodName, $prodPrice) = db2_fetch_array($stmt); ?> | ||
$ |
$stmt = db2_prepare($conn, "VALUES (NEXT VALUE FOR POid)"); db2_execute($stmt); list($POid) = db2_fetch_array($stmt); foreach($cart as $pid => $quantity) { $xquery = $t/product/description/price/text()'; $stmt = db2_prepare($conn, "SELECT XMLSERIALIZE(XMLQUERY($xquery' PASSING BY REF T.DESCRIPTION AS \"t\" RETURNING SEQUENCE) AS VARCHAR(8)) FROM xmlproduct AS t WHERE Pid = ?"); db2_execute($stmt, array($pid)); list($price) = db2_fetch_array($stmt); |
$PO .= " } |
$stmt = db2_prepare($conn, "INSERT INTO xmlporder (POid, POrder) VALUES (?, ?)"); db2_execute($stmt, array($POid, $PO)); |
$xquery = for $po in $t/purchaseOrder let $sum := for $item in $po/items/item return $item/@quantity * $item/@price let $items := for $item in $po/items/item |
let $name := for $i in db2-fn:xmlcolumn("XMLPRODUCT.DESCRIPTION")/product where $i/@pid = $item/@pid return $i/description/name/text() return | ||
{$name} | {xs:string($item/@quantity)} | ${xs:string($item/@price)} |
Total | ${sum($sum)} |
$stmt = db2_prepare($conn, "SELECT XMLSERIALIZE(XMLQUERY($xquery' PASSING BY REF T.PORDER AS \"t\" RETURNING SEQUENCE) AS CLOB(32K)) FROM xmlporder AS t WHERE POid = ?"); db2_execute($stmt, array($id)); list($po) = db2_fetch_array($stmt); echo $po; |
$stmt = db2_prepare($conn, "SELECT POrder FROM sqlporder WHERE POid = ?"); db2_execute($stmt, array($id)); $sum = 0.0; while(list($po) = db2_fetch_array($stmt)) { |
$dom = simplexml_load_string($po); foreach($dom->items->item as $item) { $cpid = (string) $item[pid']; $price = (float) $item[price']; |
$sum += $price * (integer) $item[quantity']; |
$stmt2 = db2_prepare($conn, "SELECT Name FROM sqlproduct WHERE Pid = ?"); db2_execute($stmt2, array($cpid)); if($stmt2) { list($prodName) = db2_fetch_array($stmt2); ?> | ||
$ |
$xquery = for $po in $t/purchaseOrder let $items := for $item in $po/items/item |
let $name := for $i in db2-fn:xmlcolumn("XMLPRODUCT.DESCRIPTION")/product where $i/@pid = $item/@pid return $i/description/name/text() return {xs:string($item/@quantity)} x @ ${xs:string($item/@price)} {$name} return Order #{xs:string($po/@id)} placed on {xs:string($po/@orderDate)}{$items} $stmt = db2_prepare($conn, "SELECT XMLSERIALIZE(XMLQUERY ($xquery' PASSING BY REF T.PORDER AS \"t\" RETURNING SEQUENCE) AS CLOB(32K)) FROM xmlporder AS t ORDER BY POid DESC"); db2_execute($stmt); while(list($po) = db2_fetch_array($stmt)) { echo $po; } |
$stmt = db2_prepare($conn, "SELECT POid, POrder FROM sqlporder ORDER BY POid DESC"); db2_execute($stmt); while(list($POid, $po) = db2_fetch_array($stmt)) { |
$dom = simplexml_load_string($po); ?> Order # |
$stmt2 = db2_prepare($conn, "SELECT Name FROM sqlproduct WHERE Pid = ?"); db2_execute($stmt2, array( (string) $item[pid'] )); while(list($prodName) = db2_fetch_array($stmt2)) { ?> x @ $ } } } |
CREATE UNIQUE INDEX prod_pid ON xmlproduct(description) GENERATE KEY USING XMLPATTERN /product/@pid' AS SQL VARCHAR(10) CREATE INDEX prod_name ON xmlproduct(description) GENERATE KEY USING XMLPATTERN /product/description/name' AS SQL VARCHAR(128) CREATE INDEX prod_category ON xmlproduct(description) GENERATE KEY USING XMLPATTERN /product/description/category' AS SQL VARCHAR(128) CREATE INDEX prod_brand ON xmlproduct(description) GENERATE KEY USING XMLPATTERN /product/description/brand' AS SQL VARCHAR(128) |