|
This example shows other posibility to make dynamic content with Java Stored
Procedures, using Oracle XML query running inside the database. This approach
is clearer than the previous one but in several times takes more time for execution.
CMS FAQ Builder uses two tables for storing FAQ's sections and topics.
Here SQL description for these tables:
 | | |
|
CREATE TABLE FAQ_SECTIONS
(
section_id NUMBER(10) NOT NULL,
id VARCHAR2(255),
lang VARCHAR2(32),
title VARCHAR2(4000),
owner VARCHAR2(255),
created DATE,
modified DATE,
deleted VARCHAR2(1),
created_by VARCHAR2(255),
modified_by VARCHAR2(255),
CONSTRAINT faq_section_pk PRIMARY KEY (section_id)
)
CREATE TABLE FAQ_TOPICS
(
topic_id NUMBER(10) NOT NULL,
id VARCHAR2(255),
lang VARCHAR2(32),
tp_section_id NUMBER(10) NOT NULL,
owner VARCHAR2(255),
question VARCHAR2(4000),
answer VARCHAR2(4000),
created DATE,
modified DATE,
deleted VARCHAR2(1),
created_by VARCHAR2(255),
modified_by VARCHAR2(255),
CONSTRAINT topic_pk PRIMARY KEY (topic_id),
CONSTRAINT topic_section_fk FOREIGN KEY (tp_section_id)
REFERENCES FAQ_SECTIONS(section_id)
)
| |
| | |
Also uses an Object View for quick access to the list of FAQs. Here the SQL
definitions and the trigger associated with this view used when inserts FAQs directly
through an XML faq-v10.dtd compliant file and an XSQL Page.
| | |
|
create or replace view faqs as
select s.title,
f.id,
f.question,
f.answer
from faq_sections s,faq_topics f
where f.tp_section_id=s.section_id;
create or replace trigger instead_of_insert_on_faqs
instead of insert on faqs
for each row
declare
theSectionId faq_sections.section_id%TYPE;
theTopicId faq_topics.topic_id%TYPE;
begin
-- Check if the section being inserted already exists in the faq_sections table
begin
select section_id into theSectionId
from faq_sections
where title=:new.title;
exception
when no_data_found then
-- if the section not found, create a new row in the faq_sections table
insert into faq_sections (section_id,id,lang,title,owner,
created,modified,created_by,modified_by,deleted)
values (faq_sections_seq.nextval,
null,
'en',
:new.title,
user,
sysdate,
sysdate,
user,
user,
'N')
returning section_id into theSectionId;
end;
-- Check if the faq being inserted already exists in the faq_topics table
begin
select topic_id into theTopicId
from faq_topics
where id=:new.id;
-- We found an existing faq, so update faq_topics instead of inserting
update faq_topics
set question = :new.question,
answer = :new.answer,
modified = sysdate,
modified_by = user,
deleted = 'N'
where topic_id=theTopicId;
exception
when no_data_found then
-- if the faq not found, create a new row in the faq_topics table
insert into faq_topics
(topic_id,id,lang,tp_section_id,owner,question,answer,
created,created_by,modified,modified_by,deleted)
values (faq_topics_seq.nextval,:new.id,'en',theSectionId,user,:new.question,:new.answer,
sysdate,user,sysdate,user,'N');
end;
end;
| |
| | |
Like the previous one example before show the java source, I'll show the corresponding entry into setupcms.xml file, which set
the association of this dynamic services with the CMS structure.
| | |
|
<CMS:document src="/docs/faq.xml"/>
| |
| | |
It means that the FAQ Builder content will be located in the CMS Section
Documentation and unlike Search Engine it has a parent
which is the page of the section Documentation, this parent is automatically
calculated by the upload utility.
OK, I will explain now step by step the CMS FAQ Builder's code.
| | |
|
public void doList(String ext)
throws SQLException,IOException,SAXException,XSLException {
InputSource in = null;
XSLProcessor processor = new XSLProcessor();
| |
| | |
CMS FAQ Builder has a main entry point called doList with only
one argument ext of type String which could be either html,
or wml, or xml given the posibilty of return different
output based on the extension of the request.
Because this FAQ Builders require transform the Oracle XML results into document-v10.dtd
output, at this point it creates an instance of the XMLProcessor class to use them later.
| | |
|
Connection cn = new OracleDriver().defaultConnection();
oracle.xml.sql.query.OracleXMLQuery q =
new oracle.xml.sql.query.OracleXMLQuery(cn,
"select s.title title,t.id id,t.question question,t.answer answer "+
"from faq_sections s ,faq_topics t "+
"where s.section_id=t.tp_section_id");
q.setRowsetTag("faqs");
q.setRowTag("faq");
q.useLowerCaseTagNames();
| |
| | |
Get a default connection because it is a Java Stored Procedure and is already
connected on the caller enviroment, and creates an instance of Oracle XML Query with
the given select on faq_section and faq_topics join.
This XML Query will return an XML result like this:
| | |
|
<faqs>
<faq>
<title>
Section title
</title>
<id>
faq_id
</id>
<question>
question in plain text
</question>
<answer>
answer in XML text (document-v10.dtd s3 tags)
</answer>
<faq>
</faqs>
| |
| | |
Which is converted to document-v10.dtd specification through a stylesheet stored
in the CMS repository. The code below shows this process.
| | |
|
CMSDocURLStreamHandlerFactory.enableCMSDocURLs();
URL xslURL = new URL("cms:/cmstables-to-document.xsl");
Document doc = q.getXMLDOM();
XMLDocument theXMLDoc = new XMLDocument();
Util.cloneXMLFragment(doc,theXMLDoc);
XSLStylesheet theXSLStylesheet = processor.newXSLStylesheet(xslURL.openStream());
PrintWriter pw = new PrintWriter(Jxtp.getWriter());
processor.processXSL(theXSLStylesheet,theXMLDoc,pw);
| |
| | |
 |
Documents stored into the CMS repository could be retrieved by urls, before this
step is necessary to enable the respective url handler factory calling the static
method enableCMSDocURLs() of the class CMSDocURLStreamHandlerFactory,
after this, urls with the syntax cms:/path/docname.ext#version
will be retrived from the CMS content repository. The stylesheet
cms:/cmstables-to-document.xsl was uploaded by the
Upload Utility at initialization time. Look at the
Stylesheets section for more details of the stylesheet.
|
Finally, the transformed document is sent to CMS output getting the Java XML Toolkit Procedures writer object
( Jxtp.getWriter()), this object has the XML buffer which is sent to
Cocoon. The XML text below is a fragment example of the result of applying the
stylesheet to the previous one XML Query.
| | |
|
<body>
<s1 title="Frequently Asked Questions">
<s2 title="Section title">
<anchor name="faq_id"/>
<s3 title="question">
Answer whith disable-output-escaping="yes"
</s3>
</s2>
</s1>
</body>
| |
| | |
|
