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
ext
html
wml
xml
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);
enableCMSDocURLs()
CMSDocURLStreamHandlerFactory
Finally, the transformed document is sent to CMS output getting the Java XML Toolkit Procedures writer object (
Jxtp.getWriter()
<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>
