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

Designing the FAQ Schema

Many developers feel it is easier to design an XML schema from a sample instance XML document, whereas other developers design the XML schema from a data-modeling perspective. In either case, you need to start by enumerating the data types that you will need. From the FAQ site requirements, you can determine the types and their content as set forth in Table 14-1.

Note that even though LANGUAGE and CATEGORY each will be a specific list, they have not been identified as such in the schema. While the choice to do this has the consequence of limiting the storage model, the selection can be handled on the insert side by presenting a list of languages or categories when the FAQ is generated. It is important not to unnecessarily constrain your storage, because doing so limits extensibility. In this case, regardless of whether there is a finite list, the selections are stored as strings in a column and any index created would accommodate additional strings with negligible impact.

Besides the actual FAQ content, the site maintenance would benefit from additional metadata about the FAQ, such as the following:

With these types enumerated, you can design an XML schema that takes into account what types are needed within other types. For example, you would like <ANSWER> to be able to contain <PARAGRAPH>, <KEYWORD>, and <CODE> elements, and you would like the metadata information, such as the status, id, and lastupdate, to be attributes instead of child elements of <FAQ>. Taking these dependencies into account, you could produce the following FAQ XML schema:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xdb="http://xmlns.oracle.com/xdb" 
xmlns:xs="http://www.w3.org/2001/XMLSchema" 
elementFormDefault="qualified">
<!-- Defile the CONTENT_TYPE-->
<xs:complexType name="CONTENT_TYPE" mixed="true" xdb:SQLType="CLOB" 
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="CODE" type="xs:string"/>
<xs:element name="PARAGRAPH" nillable="true">
<xs:complexType mixed="true">
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="LINK" nillable="true">
<xs:complexType mixed="true">
<xs:attribute name="href" type="xs:string"/>
<xs:attribute name="type" type="xs:string"/>
</xs:complexType>
</xs:element>
<xs:element name="KEYWORD" type="xs:string"/>
<xs:element name="CODE" type="xs:string"/>
</xs:choice>
</xs:complexType>
</xs:element>
<xs:any processContents="skip"/>
</xs:choice>
</xs:complexType>
<!-- Define FAQ Element -->
<xs:element name="FAQ" xdb:maintainDOM="false" xdb:defaultTable="FAQ">
<xs:complexType>
<xs:sequence>
<xs:element name="TITLE" xdb:SQLName="TITLE">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="100"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="QUESTION" type="CONTENT_TYPE" nillable="true"
xdb:SQLName="QUESTION"/>
<xs:element name="ANSWER" type="CONTENT_TYPE" xdb:SQLName="ANSWER"/>
<xs:element name="CATEGORY" type="xs:string" xdb:SQLType="VARCHAR2"
xdb:SQLName="CATEGORY"/>
<xs:element name="LANGUAGE" type="xs:string" minOccurs="0" 
xdb:SQLType="VARCHAR2" xdb:SQLName="LANGUAGE"/>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:positiveInteger" default="0001" 
xdb:SQLType="NUMBER" xdb:SQLName="ID"/>
<xs:attribute name="lastupdate" type="xs:date" default="2003-03-11" 
xdb:SQLType="DATE" xdb:SQLName="LASTUPDATE"/>
<xs:attribute name="status" type="xs:string" default="pending" 
xdb:SQLType="VARCHAR2" xdb:SQLName="STATUS"/>
</xs:complexType>
</xs:element>
</xs:schema>

There are several points to note about this schema. First, there are attributes with the name xdb:SQLType that are used to tell the registration process to which database type XML types should be mapped and xdb:SQLName to specify the column or object name.

Next, note that a type called CONTENT_TYPE is defined and is used in both the definition of QUESTION and ANSWER elements. This is a useful technique when you are using the same XML datatype for more than one element definition. Also to add content flexibility within these types, <xs:anyprocessContents="skip"> is added. This tells the XML schema processor in the Oracle XML DB to ignore any elements within <QUESTION> or <ANSWER> other than the <CODE> and <PARAGRAPH> elements.

Finally, note the attribute within the FAQ element, xdb:maintainDOM="false". This is a special notation to the XML database that specifies that it is not important for this application to have these documents stored with all white space, comments, processing instructions, etc. preserved. This lets the XML database create only the necessary objects and indexes to retrieve the content, thereby improving performance.

Creating a Database User

Before proceeding with the database operations, you need to create a user with the appropriate privileges to store, query, and search FAQs using Oracle XML DB. To create, in this case, an xdkus user, you need to use the sys/password logon as SYSDBA and execute the following commands from SQLPlus:

CREATE USER xdkus IDENTIFIED BY xdkus
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

Then, to make sure this user has the necessary privileges to access the resources needed, such as the Oracle Text index and Oracle XML DB repository, execute the following:

GRANT CONNECT, RESOURCE TO xdkus;
GRANT ctxapp TO xdkus;

Registering the FAQ Schema

Now that you have the FAQ schema, you need to register it with the XML database to create the database schema. While you can use the FTP or WebDAV interface to load the XML schema file to the Oracle XML DB repository, as discussed in Chapter 8, we simply copy the file into the /public directory. You can do this from SQL, connected as XDKUS/XDKUS, using the DBMS_XMLSCHEMA.registerURI() function that takes an XML DB repository URI path from which to retrieve the schema file as follows:

begin
DBMS_XMLSCHEMA.registerURI('http://localhost:8080/public/faq.xsd',
'/public/faq.xsd',TRUE,TRUE,FALSE,TRUE);
end;
/

Note that this file is being retrieved from the /public directory; however, this is a directory inside the Oracle XML DB repository and not your local file system. There are two important reasons to store the schema in the database. First, XML documents are sensitive to the character encoding they were created in. This cannot be overridden by specifying the encoding in the XML declaration, as that attribute is only a hint to the parser if it can’t determine it from the first few bytes. By storing the schema in the database, the encoding is converted, if necessary, to the database encoding, thus ensuring the document will be successfully parsed.

The other reason to use the repository is that you can be assured the schema will always be available as a link without the need to configure proxies or firewalls and instantly retrievable for validation.

You can easily store the FAQ schema by using FTP or creating a WebDAV folder under Windows. With the XML database running, launch Internet Explorer and select File | Open. Enter http://localhost:8080/public/, check the Open As Web Folder check box, and then click OK. You will now see the /public directory in the XML database repository and can copy faq.xsd to it. Now you can register the schema.

Loading the FAQs

Now that you have created the database schema, you can store the FAQs. But first, to prevent duplicate FAQs, you can create a SQL unique constraint on the value of <TITLE>. This can be done from SQL with the following:

ALTER TABLE faq
ADD CONSTRAINT TITLE_IS_UNIQUE
UNIQUE(xmldata."TITLE");

As discussed in the previous chapter, the XMLDATA refers to the XML Type Object storing the FAQs and XMLDATA. "TITLE" can be used to refer to its child TITLE element.

To load Glossary.xml, the database directory XMLDIR is created using BFILE, by using the sys/password logon as SYSDBA and executing the following commands from SQLPlus:

CREATE DIRECTORY XMLDIR AS '&os_directory';
GRANT READ ON DIRECTORY XMLDIR TO xdkus;

At this point you have an option of uploading the FAQs via FTP or using web folders that use WebDAV as xdkus user. When attempting to use FTP, you should use a client that permits copying files by generating successive PUT commands. Since the XML database’s FTP server only supports the basic set of FTP commands, multiple file operations and wildcard expansions are not available.

You can also create new directories or just use an existing directory in the repository to load the FAQs. However, to ensure the FAQs are stored in the default table created by the XML schema registration, which is called FAQ, the schemaLocation attribute in each FAQ has to be the registered XML schema URL in the Oracle XML DB. In this example, it is xsi:noNamespaceSchemaLocation="http://localhost:8080/public/faq.xsd". Therefore, each FAQ must look like the following:

<FAQ xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080/public/faq.xsd"
id="0001" lastupdate="2002-02-26">…</FAQ>

Once you have the files loaded, you can confirm their successful storage by issuing this command from SQL*Plus:

SQL> SELECT COUNT(1) FROM FAQ;
COUNT(1)
----------
108 

Now that the XML and database schema is designed and the data loaded, you can turn to interfacing the database to the web site using the XSQL Servlet from the XDK.