|
Developer: PHP
Building Database-Driven PHP Applications on Oracle XML DB
by Yuli Vasiliev
Learn how to exploit Oracle XML DB capabilities when developing XML applications with PHP.
Although, when working with Oracle XML DB from the PHP scripting language, you can still process XML data on the client side by using PHP's XML extensions, there are many advantages to processing XML content inside the database, including the following:
- Benefiting from the XML-specific memory optimizations
- Eliminating overhead associated with parsing XML documents
- Reducing disk I/O operations and network traffic between the client and the database
Moving the XML processing to the database tier is particularly useful when you are dealing with large XML documents stored in the database. This is because your application doesn't need to transfer a large amount of data between tiers when processing XML inside the database—instead, it sends only the final product across the wire.
This article explains how to take advantage of XMLType views when you need to wrap existing relational or object-relational data in XML formats. It also demonstrates two ways in which XML can be transformed with XSLT—first, via XSL transformations on the client side (by means of the libxslt XSLT processor included in PHP 5 by default), and second, via transformation logic inside the database. Finally, it presents an overview of how to employ Oracle XML DB features when breaking up data for pagination in a file-oriented PHP application that stores its data in the XML repository.
Generating XML from SQL Data
As a PHP coder developing XML applications, you may be interested in a mechanism that will allow you to transform relational data stored in a database into XML format with minimal effort. Actually, there are a few different ways in which an XML application can access data stored in relational or object tables. For example, if your PHP application uses the PHP's DOM classes to operate on XML, you can simply use a SQL query that will retrieve relational data and then put this data into the internal XML tree of a DomDocument instance with the help of its methods, such as createElement() and appendChild(). For details on how this can be implemented, see my Oracle Magazine article "Using PHP 5 with Oracle XML DB".
Another way you can access relational and object-relational data from your XML application is by using XMLType views. This technique allows you to wrap existing relational and object-relational data in XML formats, without physically migrating this data into XML. One way to construct an XMLType view is by using Oracle-provided SQL/XML generation functions such as XMLELEMENT(), XMLATTRIBUTE(), XMLFOREST(), and XMLAGG(). For example, logging onto the SCOTT/TIGER demonstration schema, you can create an XMLType view on the default DEPT and EMP table, by issuing the following SQL command (note that before you can create a view in Oracle Database 10g Release 2, you need to have executed GRANT CREATE VIEW TO SCOTT first):
CREATE OR REPLACE VIEW dept_v OF XMLType WITH OBJECT ID
(EXTRACT(OBJECT_VALUE, '/DEPARTMENT/@deptno').getNumberVal())
AS SELECT XMLELEMENT("DEPARTMENT",
XMLFOREST(DEPTNO, DNAME),
(SELECT XMLELEMENT("EMPLOYEES",
XMLAGG(
XMLELEMENT("EMPLOYEE",
XMLFOREST(EMPNO, ENAME, JOB)
)
)
)
FROM emp e WHERE e.deptno = d.deptno
)
)
FROM dept d;
Once you have created an XMLType view, you can issue queries against it to retrieve data in XML format. In this case, you don't need to construct XML documents on the client side (with PHP). Instead, you simply retrieve a well-formed XML document you can begin to use immediately. For example, you might retrieve a required XML document by issuing the appropriate query against the XMLType view and then simply load the retrieved XML document into the internal XML tree of a DomDocument instance, using its loadXML() method as shown in Listing 1:
Listing 1: Querying an XMLType view from PHP code
<?php
//File: XMLFromSQL.php
$user = 'scott';
$pswd = 'tiger';
$db ='(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
)
(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))
)';
$sql = "SELECT value(d).GetStringVal() as RESULT FROM dept_v d
WHERE extractValue(OBJECT_VALUE,'/DEPARTMENT/DEPTNO')=:deptno";
$deptno=10;
//Connect to the database and obtain info on a given department in XML format
$conn = oci_connect($user, $pswd, $db);
$query = oci_parse($conn, $sql);
oci_bind_by_name($query, ":deptno", $deptno, 2);
oci_execute($query);
oci_fetch($query);
$strXMLData = oci_result($query, 'RESULT');
//Create a new DOM document and load XML into its internal XML tree
$doc = new DOMDocument("1.0", "UTF-8");
$doc->loadXML($strXMLData);
//For simplicity, just print out the XML document
print $doc->saveXML();
?>
If you run the above script, it should produce the following XML document (note that the tags may or may not be displayed in your browser):
<?xml version="1.0"?>
<DEPARTMENT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<EMPLOYEES>
<EMPLOYEE>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
</EMPLOYEE>
<EMPLOYEE>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
</EMPLOYEE>
<EMPLOYEE>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
</EMPLOYEE>
</EMPLOYEES>
</DEPARTMENT>
You can still do without XMLType views when using SQL/XML functions for generating XML documents. For example, you might use the following string for the SQL query in the XMLFromSQL.php script to achieve the same general result:
$sql = 'SELECT XMLELEMENT("DEPARTMENT",
XMLFOREST(DEPTNO, DNAME),
(SELECT XMLELEMENT("EMPLOYEES",
XMLAGG(
XMLELEMENT("EMPLOYEE",
XMLFOREST(EMPNO, ENAME, JOB)
)
)
)
FROM emp e WHERE e.deptno = d.deptno
)
).getStringVal() as RESULT
FROM dept d WHERE deptno=:deptno';
As you can see, the above SQL query is more complicated than the one in Listing 1. Thus, another advantage to using XMLType views is that it makes it easy for PHP developers to construct SQL queries to obtain XML content generated from relational or object-relational data.
So far you have seen how to wrap existing relational and object-relational data in XML formats, using Oracle-provided SQL/XML generation functions such as XMLELEMENT(), XMLFOREST(), and XMLAGG(). With an efficient alternative available for Oracle Database 10 g Release 2, you can generate XML from SQL data by using the XQuery language, which is supported in Oracle XML DB through a native implementation of the XMLQuery() and XMLTable() SQL XML functions. For example, you might rewrite the query string in the XMLFromSQL.php script to use the XMLQuery() function as follows:
$sql = 'SELECT XMLQuery('.
"'".'for $i in ora:view("dept")/ROW
where $i/DEPTNO = $deptno
return
{$i/DEPTNO,
$i/DNAME}
{for $j in ora:view("emp")/ROW
where $j/DEPTNO = $i/DEPTNO
return {($j/EMPNO, $j/ENAME, $j/JOB)}}
'."'".'
PASSING XMLElement("deptno", :deptno) AS "deptno"
RETURNING CONTENT).GetStringVal() as RESULT FROM DUAL';
If you run the XMLFromSQL.php script, it should produce the same output as before.
Transforming XML with XSLT
XSLT is a stylesheet language that allows you to transform one XML document into another or convert it into HTML, WML, or another text-based format. Actually, you can use XSLT directly from PHP for solving a wide range of problems. For this task you might create a function that performs an XSL transformation by use of two parameters: a string containing an XML document to be transformed and an XSL stylesheet filename (including the path) that contains the instructions for transforming the XML elements in that XML document. Consider the XSLTrans() function in Listing 2.
Listing 2: PHP code applying an XSL transformation to an XML document
<?php
//File: XMLTrans.php
//the XSLTrans() function performs an XSL transformation and takes two parameters:
//a string containing an XML document to be transformed,
//a string containing an XSL stylesheet filename (including the path)
//that contains the instructions for transformation
function XSLTrans($xml_str, $xsl_file) {
// load the string containing the xml doc and XSL stylesheet file as domdocuments
$xsl = new DomDocument();
$xsl->load($xsl_file);
$inputdom = new DomDocument();
$inputdom->loadXML($xml_str);
// create the XSLT processor and import the stylesheet
$proc = new XsltProcessor();
$xsl = $proc->importStylesheet($xsl);
// transform the xml document
return $newdom = $proc->transformToXML($inputdom);
}
?>
Before you can use the XSLTrans() function, however, make sure you have the XSL extension enabled on your PHP server. For this, you might want to use the following simple script:
<?php
phpinfo();
?>
If you cannot find the XSL section within the output produced by the above script, the XSL extension has not been enabled. To enable it on Windows, you must uncomment the extension=php_xsl.dll line in the php.ini file. On UNIX-based systems, you must reinstall PHP, adding the argument with-xsl to the configure line.
Once you have the XSL extension enabled, you can use the newly created XSLTrans() function to transform XML into any text-based format, depending on the XSL stylesheet passed in. For example, you might want to transform an XML document into HTML that can be then displayed as part of a page built by PHP. Turning back to the XML document produced by the XMLFromSQL.php script discussed in the preceding section, you might use the XSL stylesheet shown in Listing 3 to transform that XML into HTML:
Listing 3: XSL stylesheet to transform a department XML document into HTML
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html" encoding="utf-8" indent="yes" />
<xsl:template match="DEPARTMENT">
<HTML>
<HEAD/>
<BODY BGCOLOR="#003333" text="#FFFFCC">
<FONT FACE="Arial, Helvetica, sans-serif">
<h4>Department code: <xsl:value-of select="DEPTNO"/></h4>
<h4>Department name: <xsl:value-of select="DNAME"/></h4>
<h4>Employees:</h4>
<TABLE BORDER="1">
<TR>
<TH>EMPNO</TH>
<TH>NAME</TH>
<TH>TITLE</TH>
</TR>
<xsl:apply-templates select="//EMPLOYEE" />
</TABLE>
</FONT>
</BODY>
</HTML>
</xsl:template>
<xsl:template match="EMPLOYEE">
<TR>
<TD><xsl:value-of select="EMPNO"/></TD>
<TD><xsl:value-of select="ENAME"/></TD>
<TD><xsl:value-of select="JOB"/></TD>
</TR>
</xsl:template>
</xsl:stylesheet>
Assuming that you have saved the stylesheet file as dept.xsl in the directory in which you store PHP scripts, you can now revise the XMLFromSQL.php script discussed earlier so that it produces formatted output instead of just an XML tree. The revised code, placed in the XMLToHTML.php script file, is shown in Listing 4:
Listing 4: Retrieving XML from the database and transforming it into HTML
<?php
//File: XMLToHTML.php
require_once "XMLTrans.php";
$user = 'scott';
$pswd = 'tiger';
$db ='(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
)
(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))
)';
$sql = "SELECT value(d).GetStringVal() as RESULT FROM dept_v d
WHERE extractValue(OBJECT_VALUE,'/DEPARTMENT/DEPTNO')=:deptno";
$deptno=10;
$conn = oci_connect($user, $pswd, $db);
$query = oci_parse($conn, $sql);
ocibindbyname($query, ":deptno", $deptno, 2);
oci_execute($query);
oci_fetch($query);
$strXMLData = oci_result($query, 'RESULT');
print XSLTrans($strXMLData, "dept.xsl");
?>
As you can see, the XSLTrans() function in this script takes a department ML document retrieved from the database as the first parameter and the dept.xsl stylesheet to be applied to that XML as the second one. As a result, the function returns the HTML tags, which you send to the browser with the help of the standard PHP's print function. So, the XMLToHTML.php script should produce the following output:
Department code: 10
Department name: ACCOUNTING
Employees:
| EMPNO |
NAME |
TITLE |
| 7782 |
CLARK |
MANAGER |
| 7839 |
KING |
PRESIDENT |
| 7934 |
MILLER |
CLERK |
Moving Business Logic to the Database
As you have learned from the preceding section, you typically apply an XSL transformation in PHP 5with the help of PHP's XSL extension. To do this, you first load the XML document and the XSL stylesheet as DOM documents and then perform the transformation, by using the transformToDoc() transformation method of the PHP's xsltlib XSLT processor. Obviously, this approach can become very inefficient when you're dealing with large XML documents. This is because DOM APIs can be used only after the entire XML document is loaded into memory and parsed, which may consume a lot of memory for recording and add overhead associated with parsing the document, thus decreasing the performance of your application. In contrast, performing XSL transformations inside the database allows Oracle XML DB to optimize memory usage, reduce I/O consumption, and eliminate network traffic. To perform an XSL transformation inside the database, you use the XMLTransform() XMLType function, which takes as arguments an XML document to be transformed and an XSL stylesheet containing the transformation instruction to be applied to that XML document. Depending on the transformation instruction specified in the stylesheet passed in, the XMLTransform() function returns the processed output in HTML, XML, or another text-based format. Because the XSL stylesheet passed in the XMLTransform() function must be an XMLType instance itself, you can store XSL stylesheets in XMLType tables or columns or inside the Oracle XML DB repository.
As for the dept.xsl stylesheet presented in Listing 3, you might want to load it into the XML repository, because you already have it saved as a file in your local file system. To do this, you might use, say, the FTP protocol. The first step is to save the dept.xsl file to the user home directory. Then you can upload the dept.xsl file to the XML repository by using a standard command-line FTP tool, as follows:
ftp> open localhost 2100
Connected to localhost.
220 localhost FTP Server (Oracle XML DB/Oracle Database 10g Enterprise Edition
Release 10.1.0.2.0) ready.
User (localhost:(none)): scott
331 pass required for SCOTT
Password:
230 USR logged in
ftp> cd /public
250 CWD Command successful
ftp> put dept.xsl
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 955 bytes sent in 0.00 Seconds 955000.00Kbytes/sec
ftp> quit
221 QUIT Goodbye.
The above example assumes that the Oracle XML DB FTP server port number is set to 2100—the default value in Oracle Database releases before 10g Release 2. In Oracle 10g R2, FTP is disabled by default, for security reasons—that is, the FTP port number is set to 0. To enable FTP, you must set the FTP port number to an appropriate value, such as 2100, which you can easily do by using Oracle Enterprise Manager, a graphical tool supplied with Oracle Database.
Once you have a stylesheet uploaded into the XML repository, you can pass it into the XMLTransform() function as the second parameter. The XSLTOnDB.php script in Listing 5 illustrates how the XMLTransform() function can be used directly in the SQL query.
Listing 5: Performing an XSL transformation on the database side
<?php
//File: XSLTOnDB.php
$user = 'scott';
$pswd = 'tiger';
$db ='(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
)
(CONNECT_DATA=(SID=orcl)(SERVER=DEDICATED))
)';
$sql = "SELECT XMLTRANSFORM(object_value,xdbUriType('/public/dept.xsl').getXML()).GetStringVal()
AS RESULT FROM dept_v d WHERE extractValue(OBJECT_VALUE,'/DEPARTMENT/DEPTNO')=:deptno";
$deptno=10;
$conn = oci_connect($user, $pswd, $db);
$query = oci_parse($conn, $sql);
ocibindbyname($query, ":deptno", $deptno, 2);
oci_execute($query);
oci_fetch($query);
$strHTML = oci_result($query, 'RESULT');
print $strHTML;
?>
This example illustrates that in some cases, you can avoid having to write PL/SQL code when you want to implement some business logic on the database side. Instead, you simply include the necessary functions in the SQL query to make the database accomplish the task.
It is important to note that, when transforming schema-based XML documents, Oracle XML DB provides XML-specific memory optimizations, significantly reducing the memory required to perform XSL transformations. Also note, however, that the dept_v view, which you query in the XSLTOnDB.php script shown in Listing 5, is a non-schema-based XMLType view—that is, its resultant XML doesn't conform to any XML schema registered against the database.
Fortunately, aside from XML-schema-based XMLType columns and tables, Oracle XML DB allows you to create XML schema-based views defined by the queries that reference relational or object-relational tables. One way to create an XML schema-based XMLType view is by using SQL/XML generation functions—the same ones you use for creating non-schema-based XMLType views. But first you need to create an appropriate XML schema document and register it against the database. Turning to the dept_v XMLType view discussed earlier in this article, you might want to create and register the dept XML schema that will be used to validate dept XML documents contained in that view. To do this, you might use the PL/SQL code shown in Listing 6.
Listing 6: Registering the dept XML schema against the database
BEGIN
dbms_xmlschema.registerSchema('http://localhost:8080/public/dept.xsd',
xmltype('<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">
<xs:element name = "DEPARTMENT">
<xs:complexType>
<xs:sequence>
<xs:element name = "DEPTNO" type = "xs:positiveInteger" />
<xs:element name = "DNAME" type = "xs:string"/>
<xs:element name = "EMPLOYEES">
<xs:complexType>
<xs:sequence>
<xs:element name = "EMPLOYEE" maxOccurs = "unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name = "EMPNO" type = "xs:positiveInteger"/>
<xs:element name = "ENAME" type = "xs:string"/>
<xs:element name = "JOB" type = "xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'), TRUE, TRUE, FALSE, TRUE);
END;
/
Once you have registered the dept XML schema, you can create an XMLType view based on that schema, as follows:
CREATE OR REPLACE VIEW shm_dept_v OF XMLType
XMLSCHEMA "http://localhost:8080/public/dept.xsd" ELEMENT "DEPARTMENT"
WITH OBJECT ID (EXTRACT(object_value, '/DEPARTMENT/DEPTNO').getNumberVal()) AS
SELECT
XMLElement("DEPARTMENT",
XMLAttributes('http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xs",
'http://localhost:8080/public/dept.xsd' AS "xs:schemaLocation"),
XMLForest(
d.deptno "DEPTNO",
d.dname "DNAME"),
(SELECT XMLELEMENT(
"EMPLOYEES",
XMLAGG(XMLElement(
"EMPLOYEE",
XMLForest(
e.empno "EMPNO",
e.ename "ENAME",
e.job "JOB")
)
)
)
FROM emp e WHERE e.deptno = d.deptno)
)
FROM dept d;
That's all there is to it. To put the new shm_dept_v XMLType view into action, you might change the SQL query in the XSLTOnDB.php script shown in Listing 5 to use shm_dept_v in place of dept_v , as follows:
$sql = "SELECT XMLTRANSFORM(object_value,xdbUriType('/public/dept.xsl').getXML()).GetStringVal()
AS RESULT FROM shm_dept_v d WHERE extractValue(OBJECT_VALUE,'/DEPARTMENT/DEPTNO')=:deptno";
If you now run the XSLTOnDB.php script, you should see the same output as before. Note, however, that unlike dept_v , shm_dept_v is a schema-based XMLType view—its resultant XML conforms to the registered XML schema. What this means in practice is that Oracle XML DB will provide XML-specific optimizations and eliminate overhead associated with parsing XML when performing XSL transformations on the XML documents retrieved from the shm_dept_v view.
Performing XML Transformations with XQuery
As you have learned from the previous sections, applying XSL transformations is the most common way to convert XML into HTML for browser display. However, with the advent of Oracle Database 10g Release 2, you get a new choice: XML transformations can be accomplished with the XMLQuery() or XMLTable() SQL/XML functions mentioned earlier in the article. Turning to the XSLTOnDB.php script shown in Listing 5, you might rewrite the query string to use the XMLQuery() function, as follows:
$sql = 'SELECT XMLQuery('."'".'for $i in ora:view("dept")/ROW
where $i/DEPTNO = $deptno
return (<HTML>
<HEAD/>
<BODY BGCOLOR="#003333" text="#FFFFCC">
<FONT FACE="Arial, Helvetica, sans-serif">
<h4>Department code:{$i/DEPTNO}</h4>
<h4>Department name:{$i/DNAME}</h4>
<h4>Employees:</h4>
<TABLE BORDER="1">
<TR>
<TH>EMPNO</TH>
<TH>NAME</TH>
<TH>TITLE</TH>
</TR>
{for $j in ora:view("emp")/ROW
where $j/DEPTNO = $i/DEPTNO
return <TR> {(<TD>{$j/EMPNO}</TD>,
<TD>{$j/ENAME}</TD>,
<TD>{$j/JOB}</TD>)}</TR>}
</TABLE>
</FONT>
</BODY>
</HTML>)'."'".
' PASSING XMLElement("deptno", :deptno) AS "deptno"
RETURNING CONTENT).GetStringVal() as RESULT FROM DUAL';
This example illustrates how an XML transformation can be accomplished inside the database with XQuery. Although the XSLTOnDB.php script will produce the same output as before, there are a few important issues to consider when using XQuery. First, it is important to remember that XQuery, unlike XSLT, is a typed language whose type system is based on XML Schema. What this means in practice is that XQuery will generate an error message if you try to perform operations such as assignment and comparison among variables of different types. Consider the following simple XML query issued against the dept table:
SELECT XMLQuery(
'for $i in ora:view("dept")/ROW
let $deptno:=10
where $i/DEPTNO = $deptno
return <DEPARTMENT>
{$i/DEPTNO,
$i/DNAME,
$i/LOC}
</DEPARTMENT>'
RETURNING CONTENT) AS RESULT FROM DUAL;
This should produce the following XML:
<DEPARTMENT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DEPARTMENT>
But trying to specify the department number as a string:
SELECT XMLQuery(
'for $i in ora:view("dept")/ROW
let $deptno:="10"
where $i/DEPTNO = $deptno
return <DEPARTMENT>
{$i/DEPTNO,
$i/DNAME,
$i/LOC}
</DEPARTMENT>'
RETURNING CONTENT) AS RESULT FROM DUAL;
will result in the following error message:
ERROR at line 1:
ORA-19162: XP0004 - XQuery type mismatch: invalid argument types 'xs:decimal',
'xs:string' for function '='
Breaking Up XML Data for Pagination
When you need to display numerous records from the database, you will probably not want to retrieve all of them at once. Instead, you might want to break up these records into pages, so that each page fits on the screen, and then display a given page only when the user requests it, eliminating the need to retrieve the data for pages the user has not requested. To retrieve a given page, you might use the appropriate SQL query executed from your PHP code. But what should you do if you are dealing with a file-oriented PHP application that stores its data in the Oracle XML repository and manipulates it by using, say, the FTP protocol? Actually, there may be several approaches to solving this problem. For example, you might have a PHP script that will use a parameter file to interact with Oracle XML DB. In this file, you are telling Oracle XML DB which data your PHP application needs to receive and how and where the data must appear in the XML repository so the application can process it correctly. Assuming that this parameter file is organized as a schema-based XML document and the corresponding XML schema has already been registered with the database, uploading this file to the Oracle XML DB repository will result in insertion of a row into the underlying XMLType table. Because Oracle allows you to define triggers on XMLType tables, you might create a BEFORE INSERT OR UPDATE trigger on that table. The trigger will fire every time your PHP application sends a parameter file to the XML repository. Depending on the parameters contained in the parameter file, the trigger will generate XML documents and put them into the XML repository folder, where your PHP application expects them to appear.
To start with, you might want to create a pagination XML schema. When registering this schema with the database, Oracle Database will create all the underlying database objects that will store data from the parameter files sent by your PHP application. You can both create a pagination XML schema and register it with the database in one step from SQL, connected as SCOTT/TIGER, using the DBMS_XMLSCHEMA.registerSchema() function as shown in Listing 7. (Note that in Oracle Database 10g Release 2, you first need to grant all the necessary privileges, in order to register XML schemas, to the scott user. For details, see Oracle Database 10g Release 2 (10.2) Documentation, specifically the "Privileges Needed for XML Schema Evolution" subsection in the XML DB Developer's Guide.)
Listing 7: Setting up the XML database for storing parameter files containing information for pagination
BEGIN
DBMS_XMLSCHEMA.registerSchema(
'http://localhost:8080/public/pagination.xsd',
xmltype('<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"
version="1.0" xdb:storeVarrayAsTable="true">
<xs:element name="PAGINATION" type="PageType" xdb:defaultTable="PAGINATION"/>
<xs:complexType name="PageType" xdb:SQLType="PAGE_T">
<xs:sequence>
<xs:element name="USR" type="xs:string" xdb:SQLName="USR"/>
<xs:element name="TBL" type="xs:string" xdb:SQLName="TBL"/>
<xs:element name="PERPAGE" type="xs:integer" xdb:SQLName="PERPAGE"/>
<xs:element name="ROWSETTAG" type="xs:string" xdb:SQLName="ROWSETTAG"/>
<xs:element name="ROWTAG" type="xs:string" xdb:SQLName="ROWTAG"/>
<xs:element name="ROOTDIR" type="xs:string" xdb:SQLName="ROOTDIR"/>
</xs:sequence>
</xs:complexType>
</xs:schema>'),
TRUE,TRUE,FALSE,TRUE
);
END;
/
When the above PL/SQL code is completed, Oracle automatically creates the pagination XMLType table, which will be used to store Pagination XML documents conforming to the XML schema shown in Listing 7. As mentioned earlier, your file-oriented PHP application will use a Pagination XML document as the parameter file through which you are telling Oracle XML DB not only which data you want to receive but also some details on how and where this data must appear in the XML repository. Wrapping the passed parameters in XML tags, you will specify the following information in this XML file:
- The USR tag contains the name of the current user; it is assumed that your PHP application users are to sign in before accessing any data.
- The TBL tag contains the name of the table or view to be queried.
- PERPAGE contains the number of rows to be included in each generated XML document containing data for a single page of the PHP application.
- ROWSETTAG contains the root tag name that will be used for each generated XML document containing data for a single page of the PHP application.
- ROWTAG contains the row tag name that will be used for each row in the generated XML document containing data for a single page of the PHP application.
- ROOTDIR contains the XML repository folder in which Oracle XML DB will create a folder and put the generated XML documents into it.
When developing a real-world application, you may need to use some additional parameters to get the desired functionality. For example, you might want to use SQL queries that are more sophisticated than just SELECT * FROM tbl. You can use some extra tags, such as WHERECLS, for specifying the where clause of the query, and FIELDS, for specifying the list of columns to be used in the query instead of *.
Turning back to the pagination table, the next step is to create a BEFORE INSERT OR UPDATE trigger on it. As mentioned, this trigger will fire every time your PHP application sends a parameter file to the XML repository. With PL/SQL it might look like the one in Listing 8.
Listing 8: Setting up a BEFORE INSERT OR UPDATE trigger on the pagination table of XMLType
CREATE OR REPLACE TRIGGER CREATE_PAGES
BEFORE INSERT OR UPDATE ON PAGINATION
FOR each row
BEGIN
DECLARE
ctxHndl DBMS_XMLGEN.CTXHANDLE;
XMLdoc XMLType;
CLBcnt CLOB;
counter INTEGER;
fld VARCHAR2(50);
usr VARCHAR2(50);
tbl VARCHAR2(50);
perpage INTEGER;
rowsettag VARCHAR2(50);
rowtag VARCHAR2(50);
rootfld VARCHAR2(50);
BEGIN
SELECT extractValue(:new.object_value, '/PAGINATION/USR') INTO usr FROM dual;
SELECT extractValue(:new.object_value, '/PAGINATION/TBL') INTO tbl FROM dual;
SELECT extractValue(:new.object_value, '/PAGINATION/PERPAGE') INTO perpage FROM dual;
SELECT extractValue(:new.object_value, '/PAGINATION/ROWSETTAG') INTO rowsettag FROM dual;
SELECT extractValue(:new.object_value, '/PAGINATION/ROWTAG') INTO rowtag FROM dual;
SELECT extractValue(:new.object_value, '/PAGINATION/ROOTDIR') INTO rootfld FROM dual;
ctxHndl:= DBMS_XMLGEN.NEWCONTEXT('SELECT * FROM '||tbl);
DBMS_XMLGEN.SETMAXROWS(CtxHndl, perpage);
DBMS_XMLGEN.SETROWSETTAG(CtxHndl, rowsettag);
DBMS_XMLGEN.SETROWTAG(CtxHndl, rowtag);
BEGIN
SELECT path INTO fld FROM PATH_VIEW WHERE under_path
(RES, rootfld||'/'||usr||tbl) = 1 AND rownum=1;
DELETE FROM resource_view WHERE under_path(res, rootfld||'/'||usr||tbl) = 1;
DBMS_XDB.DELETERESOURCE(rootfld||'/'||usr||tbl);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is no folder');
END;
IF(DBMS_XDB.CREATEFOLDER(rootfld||'/'||usr||tbl)) THEN
DBMS_OUTPUT.PUT_LINE('Folder is created');
ELSE
DBMS_OUTPUT.PUT_LINE('Cannot create folder');
END IF;
counter:=0;
LOOP
counter:=counter+1;
CLBcnt:= DBMS_XMLGEN.GETXML(ctxHndl);
EXIT WHEN DBMS_XMLGEN.GETNUMROWSPROCESSED(ctxHndl)=0;
XMLdoc:=XMLType(CLBcnt).createNonSchemaBasedXML();
IF(DBMS_XDB.CREATERESOURCE
(rootfld||'/'||usr||tbl||'/'||tbl||counter||'.xml', XMLdoc)) THEN
DBMS_OUTPUT.PUT_LINE('Resource is created');
ELSE
DBMS_OUTPUT.PUT_LINE('Cannot create resource');
END IF;
END LOOP;
END;
END;
/
As you can see, the trigger code is straightforward. After the variable declaration, you extract the values of the parameters contained in the Pagination XML document that is being uploaded into the XML repository. Next, you call DBMS_XMLGEN functions and procedures to generate XML documents in accordance with the parameters obtained from the Pagination XML document. At the same time, you create an XML repository folder to receive the generated XML documents. It is interesting to note that the name of this folder comprises two names: the name of the current user (PHP application user), as it is specified in the USR tag in the uploaded Pagination XML document, and the name of the table or view to be queried, as that name is specified in the TBL tag. If a folder with a given name already exists, thereby indicating that the user has already queried this table or view at least once, you first delete this folder, along with all of its contents. So, this technique allows you to avoid increasing the amount of out-of-date data in the XML repository. On the other hand, including the name of the application user in the folder name guarantees that two users working with your PHP application simultaneously will not have trouble when accessing the same table or view.
Now that you have an idea of how the CREATE_PAGES trigger works, you are ready to try it out. To do this, you might use simple PHP code such as that in Listing 9.
Listing 9: Making a Pagination XML document and uploading it into the XML repository by FTP from PHP code
<?php
//File: PaginationXML.php
$host=localhost;
$port=2100;
$timeout=30;
$db_user='scott';
$db_pswd='tiger';
$php_user='usr';
$db_tbl='emp';
$perpage=5;
$rowsettag ='EMPLOYEES';
$rowtag ='EMPLOYEE';
$root_dir='/public';
//the file name is composed of the current user name and the name of the table to be queried
$file=$php_user.$db_tbl.'.xml';
$cnt='<?xml version="1.0"?>
<PAGINATION xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080/public/pagination.xsd">
<USR>'.$php_user.'</USR>
<TBL>'.$db_tbl.'</TBL>
<PERPAGE>'.$perpage.'</PERPAGE>
<ROWSETTAG>'.$rowsettag.'</ROWSETTAG>
<ROWTAG>'.$rowtag.'</ROWTAG>
<ROOTDIR>'.$root_dir.'</ROOTDIR>
</PAGINATION>';
//create a temp file and write the above XML content in it
$temp = tmpfile();
fwrite($temp, $cnt);
fseek($temp, 0);
//connect to the Oracle FTP server and upload the file into Oracle XML DB repository
$con_id = ftp_connect($host, $port, $timeout);
$login = ftp_login($con_id, $db_user, $db_pswd);
ftp_chdir($con_id, $root_dir);
ftp_fput($con_id, $file, $temp, FTP_ASCII);
//for test, change to the newly created directory
ftp_chdir($con_id, $php_user.$db_tbl);
// get all the files there
$files = ftp_nlist($con_id, ".");
//and then output them
var_dump($files);
?>
For the sake of simplicity, this script uses a predefined user name, namely usr. In a real-world application, however, you most probably will use the current user name stored in a session variable. The above script will generate the following Pagination XML document and upload it into the /public repository folder as usremp.xml:
<?xml version="1.0"?>
<PAGINATION xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080/public/pagination.xsd">
<USR>usr</USR>
<TBL>emp</TBL>
<PERPAGE>5</PERPAGE>
<ROWSETTAG>EMPLOYEES</ROWSETTAG>
<ROWTAG>EMPLOYEE</ROWTAG>
<ROOTDIR>/public</ROOTDIR>
</PAGINATION>
When the above XML document is uploaded into the XML repository, the CREATE_PAGES trigger on the pagination XMLType table is fired. The trigger first queries the emp table; generates XML from the retrieved SQL data, putting five employee records into each XML document; and finally stores the generated XML documents in the /public/usremp repository folder. As a result, you have three emp XML documents in the /public/usremp folder. This can be seen in the output produced by the PaginationXML.php script:
array(3) {
[0]=>
string(8) "emp1.xml"
[1]=>
string(8) "emp2.xml"
[2]=>
string(8) "emp3.xml"
}
Alternatively, you can browse through the /public/usremp repository folder via HTTP. To do this, you simply enter the following URL in your browser:
http://localhost:8080/public/usremp
The browser, in turn, will ask you to enter your user name and password in the Enter Password Network dialog box. You should enter scott/tiger. As a result, the browser will display the index of the /public/usremp repository folder:
The above result was expected, because the emp table contains 14 rows by default and you set the maximum number of records within an emp XML document to be five—each emp XML document can contain no more than five records. To make sure it has done so, you can click on, say, emp1.xml. As a result, the emp1.xml document will be sent to the browser and displayed as follows (note that the tags may or may not be displayed in your browser):
<?xml version="1.0"?>
<EMPLOYEES>
<EMPLOYEE>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</EMPLOYEE>
...
</EMPLOYEES>
Conclusion
As you no doubt have realized, most of the XML functionality provided by PHP or PHP's extensions can also be implemented by means of Oracle XML DB. The latter approach, in most cases, is more efficient, because it allows you not only to centralize control over the key business logic of your application but also to improve its performance. Sometimes, you don't even need to write PL/SQL code when moving some business logic from PHP to the database. Instead, you simply construct a SQL query that contains functions for getting the job done.
If you are developing a file-oriented XML application, you can take advantage of a hierarchically organized Oracle XML DB repository that allows you to move XML in and out of the database by using an internet protocol, such as FTP. Note, however, that each resource stored in an XML repository represents a row in the appropriate underlying database table and can thus still be accessed from SQL. When you create the storage for the application data by registering an annotated XML schema, you can explicitly specify the XMLType table that will be used for storing the XML documents processed by your application. In this case, you might move some business logic to the database by, say, defining triggers on that XMLType table.
Yuli Vasiliev (jvyul@yahoo.com) is a software developer, freelance author, and consultant who focuses mainly on Oracle objects and Oracle XML technology.
Send us your comments |
Bookmark
