Creating an Oracle view of articles to display EPiServer Content in Lotus Notes

We’re currently building out our corporate intranet pages in EPiServer, after many years of being housed in the Lotus Domino arena. While EPiServer does a great job of serving pages, we’ve got lots and lots of custom Domino apps that still need to be accessed that we probably won’t be converting completely to .NET any time soon. For this reason, we’ve got to make EPiServer a bit more flexible to work with the other systems.

One of these applications is a mail program that allows our Corporate Communications to select an article posted to our Intranet, pull some content from it, and mail a snapshot of the content to a particular group (managed in our Lotus Notes global directory). So in this instance, what I had to do was create a view that Lotus Notes could access in order to generate these messages.

The first step was determining which fields needed to be available to our Lotus Notes program. For this program, Lotus Notes needed to access the Page ID, Name, the SEO-friendly link name, the date the article was published, the type of page being published, and a portion of the body’s content.

After some investigation, I found all of these fields in a combination of the TBLTREE, TLPROPERTY and TBLPAGELANGUAGE tables in the EPiServer database. I then isolated the two parent ID’s that I wanted to grab children of. For our instance, we wanted our blog posts (ID = 566), and our news items (ID = 572). I then determined the Page Definition ID’s for the body (content) fields that we needed, decided upon a nesting level (which directly correlated with our URL or page structure syntax) for each of these (2 and 4) and decided to grab only articles posted in English.

As an aside, I’m not a big fan of hard coding values, but this needed to be a purely Oracle View, meaning the EPi libraries were not used at all in this determination. Furthermore, our page structure is always going to follow the same nesting syntax and the base parents 566 and 572 will not be going anywhere – so I think we were safe in this instance.

Without further ado, the code itself. .

CREATE OR REPLACE VIEW MY_VIEW_NAME
( PAGEID, NAME, LINKNAME, PUBLISHDATE, PAGELEVEL, CONTENT )
AS
SELECT DISTINCT lang.FKPageId
    , lang.NAME
    , lang.URLSEGMENT
    , lang.STARTPUBLISH
    , tree.NESTINGLEVEL
    , dbms_lob.SUBSTR(LONGSTRING, 4000, 1)
FROM
    TBLTREE tree
    , TBLPROPERTY prop
    , TBLPAGELANGUAGE lang 
WHERE
    (  
        (
            tree.FKPARENTID = 572 -- 572 is news parent
                AND tree.NESTINGLEVEL = 2 -- level two using the syntax Month-Year/article-name
                AND (prop.FKPAGEDEFINITIONID = 244) --244 is main body field for news articles
                AND (lang.FKPAGEID = prop.FKPAGEID)
        )         
        OR
        (
            tree.FKPARENTID = 566 -- 566 is blog parent
                AND tree.NESTINGLEVEL = 4 -- level four using the syntax Dates/Year/Month/article-name
                AND (prop.FKPAGEDEFINITIONID = 289) --289 is main body field for blog articles
                AND (lang.FKPAGEID = prop.FKPAGEID)
        )
    )
    AND (lang.FKLANGUAGEBRANCHID = 1) -- Only grab English titles
    AND (tree.FKCHILDID = lang.FKPAGEID)

WITH READ ONLY
/   

So now we have a nice little view containing the ID, Name, URL Segment, Publish Date, Nesting Level and 4000 characters of the Body’s content. On the Lotus Notes side, a query is made against this view to determine whether or not the nesting level is either a 2 (indicating a news post) or a 4 (indicating a blog post), and then the ID is appended to the proper link format based on the type of post:  http://mydomain/public/PageTemplates/Blog/Pages/item.aspx?id= for blog items or http://mydomain/public/PageTemplates/News.aspx?id= for news items. That’s all there is to it.