| DBPrism Home Page | DBPrism Content Management System | Dynamic Content

DBPrism CMS News Feed from Moreover.com

News Feed from Moreover.com (TM) are built by a simple Java Stored Procedure that selects the news from two tables, these tables are populated by a database job that connects to Moreover web site. Here SQL description for these tables:

CREATE TABLE NEWS_CATEGORIES
 (
  id                         VARCHAR2(10),
  lang                       VARCHAR2(32),
  title                      VARCHAR2(4000),
  CONSTRAINT news_category_pk PRIMARY KEY (id)
 )
CREATE TABLE NEWS_ARTICLES
 (
  art_cat_id                    VARCHAR2(10) NOT NULL,
  lang                          VARCHAR2(32),
  id                            VARCHAR2(10) NOT NULL,
  url                           VARCHAR2(4000),
  headline_text                 VARCHAR2(4000),
  source                        VARCHAR2(255),
  media_type                    VARCHAR2(255),
  m_cluster                       VARCHAR2(255),
  tagline                       VARCHAR2(255),
  document_url                  VARCHAR2(4000),
  harvest_time                  VARCHAR2(255),
  access_registration           VARCHAR2(255),
  access_status                 VARCHAR2(255),
  CONSTRAINT article_pk PRIMARY KEY (id),
  CONSTRAINT article_category_fk FOREIGN KEY (art_cat_id)
      REFERENCES NEWS_CATEGORIES(id)
 )

An anonymous PLSQL block is used for uploading the news and invalidating the content using the External Cache Invalidator Server. Here the code:

create or replace procedure UploadNews(category VARCHAR2) as
  jobId BINARY_INTEGER;
  par cache.cache_parameters;
begin
  -- Uncomment these lines if your server is behind a firewall
  --cmsNews.setSystemProperty('proxySet','true');
  --cmsNews.setSystemProperty('proxyHost','proxy.unicen.edu.ar');
  --cmsNews.setSystemProperty('proxyPort','8080');
  cmsNews.doImport(category);

/* invalidate Cocoon's sitemap entry:
   <map:match pattern="content/news.xml">
    <map:generate type="db" src="/cms/cmsNews.list">
        <map:parameter name="Cache-Control" value="External"/>
        <map:parameter name="category" value="Java news"/>
    </map:generate>
    <map:serialize/>
   </map:match>
 */
  par.num_vals := 1;
  par.names(1) := 'category';
  par.vals(1) := category;
  cache.invalidate('cachehost',8888,'/cms/','cmsNews.list',par);
end UploadNews;

Unlike the previous one example the news feed do not require an entry into the CMS metadata because it directly called by Cocoon content aggregation, here the definition into cocoon's sitemap.xmap file:

....
   <!-- Generates the content news with information extracted from moreover.com
   -->
   <map:match pattern="content/news.xml">
    <map:generate src="/cms/cmsNews.list">
        <map:parameter name="Cache-Control" value="External"/>
        <map:parameter name="category" value="Java news"/>
    </map:generate>
    <map:serialize/>
   </map:match>
...
   <map:match pattern="**.html">
    <map:aggregate element="document">
     <map:part src="cocoon:/header/{1}.xml"/>
     <map:part src="cocoon:/content/{1}.xml"/>
     <map:part src="cocoon:/content/news.xml"/>
    </map:aggregate>
    <map:transform type="extractor"/>
    <map:transform src="stylesheets/document2html.xsl"/>
    <map:serialize type="html"/>
   </map:match>

It means that every CMS page is composed (aggreggated into the tag document) with the content of the page metadata (header), the content as is, and the news feed, then the extractor gets the content of SVG images and finally is transformed to html with the stylesheet document2html.xsl.

OK, I will explain now step by step the CMS News Feed's code.


	public static void doList(String category) throws SQLException {

CMS News Feed has a main entry point called doList with only one argument category of type String which defines the category of Moreover.com news to show (for example Java News).

This stored procedure get the content of the news from the previous one tables using Oracle XML Utility.

    Connection cn = new OracleDriver().defaultConnection();
    oracle.xml.sql.query.OracleXMLQuery q =
    new oracle.xml.sql.query.OracleXMLQuery(cn,
      "select a.id as \"@id\",a.url \"@url\",a.headline_text headline_text,a.source source "+
      "from news_categories c, news_articles a "+
      "where c.title='"+category+"' and c.id=a.art_cat_id");
    q.setRowsetTag("moreovernews");
    q.setRowTag("article");
    q.useLowerCaseTagNames();
    q.setRowIdAttrName(null);

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 news_categories and news_articles join.

This XML Query will return an XML result like this:

      <moreovernews>
        <article id="_29976230">
          <url>http://c.moreover.com/click/here.pl?x29976219</url>
          <headline_text>Microsoft memo: Beat Linux</headline_text>
        </article>
        <!-- more articles here ...-->
      </moreovernews>

Finally, the transformed document is sent to CMS output using DBPrism's Java XML Toolkit Procedures.


	Jxtp.prolog(); // Adds DOM tree Jxtp.p(q.getXMLDOM().getDocumentElement()); Jxtp.epilog();