Perl Cd Bookshelf [Electronic resources] نسخه متنی

Connecting the FAQ Web Site to the XML Database

The XSQL servlet, introduced in Oracle8i and refined in Oracle9i and Oracle Database 10g, provides a robust and flexible platform for this type of application. We will use it in the following ways:

To generate the content from data stored in the database

To provide a keyword search interface

To display the results from the various queries in the browser using an interactive interface

To manage the database connections

Figure 14-1 illustrates the FAQ web site’s home page, which is dynamically generated from the XSQL Servlet using a combination of XML and XSL files.

Figure 14-1: FAQ web site

Building the XSQL Home Page

From previous chapters, you know that the elements of an XSQL page consist of the following sections:

<?xml version="1.0" encoding='UTF-8'?>
<!-- Stylesheet PIs -->
<!-- Database Connection -->
<!-- XSQL code and database queries -->

However, to allow the web site to be modular and extensible, we will be parameterizing the XSQL pages where their content will be added using XML files passed as an HTTP parameter. The following is the index.xsql page that will serve as the base module:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="msie" href="?>
<?xml-stylesheet type="text/xsl" media="mozilla" 
href="?>
<page xmlns:xsql="urn:oracle-xsql">
<content>
<xsql:if-param name="pagename" exists="yes">
<xsql:include-xsql reparse="yes" href="/>
</xsql:if-param>
<xsql:if-param name="pagename" exists="no">
<xsql:include-xsql reparse="yes" href=" lang=all"/>
</xsql:if-param>
</content>
</page>

Note that it uses the <xsql:if-param> element introduced in Oracle Database 10g XDK to conditionally include XML files. The first <xsql:if-param> element is called if a pagename parameter supplying the filename of an XSQL page is passed in, while the second is called when no parameter is supplied. Therefore, when this page is first invoked, as shown in Figure 14-1, the list.xsql page is included with parameters that specify the query should return all FAQs across all programming languages.

Also included are two <?xml-stylesheet?> processing instructions. These differ by the media and href attributes that the XSQL Servlet uses to perform the appropriate stylesheet transformation for the requesting browser.

Creating the FAQ List

The list.xsql page is the one that queries the database, returning XML from which the HTML page is created after the stylesheet processing. To create this page, you need to specify the database queries that will be submitted as well as the connection information for JDBC access.

You can begin by setting up the database connection. The following element declares the title of the page, the XSQL namespace, and the alias for the database connection located in the XSQLConfig.xml file:

<page title="Oracle XML FAQ Demo" connection="xdkus" id="1" xmlns:xsql=
"urn:oracle-xsql">

The xdkus entry in the XSQLConfig.xml file that will be used to connect to the database is as follows:

<connection name="xdkus">
<username>xdkus</username>
<password>xdkus</password>
<dburl>jdbc:oracle:thin:@localhost:1521:orcl</dburl>
<driver>oracle.jdbc.driver.OracleDriver</driver>
</connection>

Since the FAQs are stored as complete XML documents, if you want to produce an initial summary listing, as shown in Figure 14-1, you need to extract the information instead of displaying the entire document. Although applying a stylesheet on each returned FAQ could do this, it would be very inefficient and doesn’t leverage the power of the XML database. Instead, you can use XPaths as illustrated in the following SQL query to retrieve only the data required:

SELECT extractValue(value(x),'/FAQ/TITLE') as title,
extractValue(value(x),'/FAQ/@status') as status,
extractValue(value(x),'/FAQ/@id') as id
FROM faq x
order by id

Retrieving the IDs for each FAQ allows you to use them to construct a link to the full question and answer. This can be done in the following XSL template included within list.xsl:

<xsl:for-each select="page/ROWSET/ROW">
<tr>
<xsl:attribute name="class">
<xsl:choose>
<xsl:when test="position() mod 2 = 1">rowodd</xsl:when>
<xsl:when test="position() mod 2 = 0">roweven</xsl:when>
</xsl:choose>
</xsl:attribute>
<td>
<xsl:value-of select="ID"/>:</td>
<td>
<a href=" 
Onclick="NewWindow('','{ID}','600','400',
'yes');
return false;">
<xsl:value-of select="TITLE"/>
</a>
<xsl:if test="STATUS ='hot'">
<img src="/image/library/english/10158_{STATUS}.gif"/>
</xsl:if>
</td>
</tr>
</xsl:for-each>

This template does several things. First, it produces the alternating row colors in the table by using <xsl:choose> to add a CSS attribute ROWODD or ROWEVEN. It then makes a link of <TITLE> using JavaScript to pass the FAQ ID to showanswer.xsql to display a new window with the answer. Finally, to indicate popular FAQs, the template adds the hot.gif image.

Filtering the FAQ List

Since the FAQs are categorized by programming language and component, you will want to retrieve only relevant FAQs. You can do this by passing parameters into a query. The XSQL Servlet facilitates your capability to do this, as illustrated in the following section from list.xsql:

<xsql:if-param name="lang" not-equals="all">
<xsql:action handler="oracle.xml.sample.xsql.portal.Paging"
rows-per-page="15" url-pagename="index.xsql?pagename=list&amp;
cat={@cat}&amp;lang={@lang}&amp;">
<![CDATA[
SELECT count(1)
FROM faq x
where extractValue(value(x),'/FAQ/LANGUAGE/text()') ='{@lang}'
and extractValue(value(x),'/FAQ/CATEGORY/text()') ='{@cat}'
]]>
</xsql:action>
<xsql:query skip-rows="{@paging-skip}" max-rows="{@paging-max}">
<![CDATA[
SELECT extractValue(value(x),'/FAQ/TITLE') as title,
extractValue(value(x),'/FAQ/@status') as status,
extractValue(value(x),'/FAQ/@id') as id
FROM faq x
where extractValue(value(x),'/FAQ/LANGUAGE/text()') ='{@lang}'
and extractValue(value(x),'/FAQ/CATEGORY/text()') ='{@cat}'
order by id
]]>
</xsql:query>
</xsql:if-param>

Once again, <xsql:if-param> is used to be able to switch in another application section. Once the category parameter, @cat, and the language parameter, @lang, are initialized by selecting from the pick lists, the queries are populated and submitted. The first query returns the FAQ count that is used for pagination and the second query returns the list of FAQs. Note that you can use XPaths in both sections of the query and that, when used in the predicate, text() is appended in order to perform the string match.