Configuration "diff" across Oracle WebCenter Sites instances
- by Mark Fincham-Oracle
Problem Statement
With many Oracle WebCenter Sites environments - how do you know if the various configuration assets and settings are in sync across all of those environments?
Background
At Oracle we typically have a "W" shaped set of environments.
For the "Production" environments we typically have a disaster recovery clone as well and sometimes additional QA environments alongside the production management environment. In the case of www.java.com we have 10 different environments.
All configuration assets/settings (CSElements, Templates, Start Menus etc..) start life on the Development Management environment and are then published downstream to other environments as part of the software development lifecycle.
Ensuring that each of these 10 environments has the same set of Templates, CSElements, StartMenus, TreeTabs etc.. is impossible to do efficiently without automation.
Solution Summary
The solution comprises of two components.
A JSON data feed from each environment.
A simple HTML page that consumes these JSON data feeds.
Data Feed:
Create a JSON WebService on each environment.
The WebService is no more than a SiteEntry + CSElement.
The CSElement queries various DB tables to obtain details of the assets/settings returning this data in a JSON feed.
Report:
Create a simple HTML page that uses JQuery to fetch the JSON feed from each environment and display the results in a table.
Since all assets (CSElements, Templates etc..) are published between environments they will have the same last modified date. If the last modified date of an asset is different in the JSON feed or is mising from an environment entirely then highlight that in the report table.
Example
Solution Details
Step 1: Create a Site Entry + CSElement that outputs JSON
Site Entry & CSElement Setup
The SiteEntry should be uncached so that the most recent configuration information is returned at all times.
In the CSElement set the contenttype accordingly:
Step 2: Write the CSElement Logic
The basic logic, that we repeat for each asset or setting that we are interested in, is to query the DB using <ics:sql> and then loop over the resultset with <ics:listloop>. For example:
<ics:sql sql="SELECT name,updateddate FROM Template WHERE status != 'VO'"
listname="TemplateList" table="Template" />
"templates": [
<ics:listloop listname="TemplateList">
{"name":"<ics:listget listname="TemplateList" fieldname="name"/>",
"modified":"<ics:listget listname="TemplateList" fieldname="updateddate"/>"},
</ics:listloop>
],
A comprehensive list of SQL queries to fetch each configuration asset/settings can be seen in the appendix at the end of this article.
For the generation of the JSON data structure you could use Jettison (the library ships with the 11.1.1.8 version of the product), native Java 7 capabilities or (as the above example demonstrates) you could roll-your-own JSON output but that is not advised.
Step 3: Create an HTML Report
The JavaScript logic looks something like this..
1) Create a list of JSON feeds to fetch:
ENVS['dev-mgmngt'] = 'http://dev-mngmnt.example.com/sites/ContentServer?d=&pagename=settings.json';
ENVS['dev-dlvry'] = 'http://dev-dlvry.example.com/sites/ContentServer?d=&pagename=settings.json';
ENVS['test-mngmnt'] = 'http://test-mngmnt.example.com/sites/ContentServer?d=&pagename=settings.json';
ENVS['test-dlvry'] = 'http://test-dlvry.example.com/sites/ContentServer?d=&pagename=settings.json';
2) Create a function to get the JSON feeds:
function getDataForEnvironment(url){
return $.ajax({
type: 'GET',
url: url,
dataType: 'jsonp',
beforeSend: function (jqXHR, settings){
jqXHR.originalEnv = env;
jqXHR.originalUrl = url;
},
success: function(json, status, jqXHR) {
console.log('....success fetching: ' + jqXHR.originalUrl);
// store the returned data in ALLDATA
ALLDATA[jqXHR.originalEnv] = json;
},
error: function(jqXHR, status, e) {
console.log('....ERROR: Failed to get data from [' + url + '] ' + status + ' ' + e);
}
});
}
3) Fetch each JSON feed:
for (var env in ENVS) {
console.log('Fetching data for env [' + env +'].');
var promisedData = getDataForEnvironment(ENVS[env]);
promisedData.success(function (data) {});
}
4) For each configuration asset or setting create a table in the report
For example, CSElements:
1) Get a list of unique CSElement names from all of the returned JSON data.
2) For each unique CSElement name, create a row in the table
3) Select 1 environment to represent the master or ideal state
(e.g. "Everything should be like Production Delivery")
4) For each environment, compare the last modified date of this envs CSElement to the master.
Highlight any differences in last modified date or missing CSElements.
5) Repeat...
Appendix
This section contains various SQL statements that can be used to retrieve configuration settings from the DB.
Templates
<ics:sql
sql="SELECT name,updateddate FROM Template WHERE status != 'VO'"
listname="TemplateList"
table="Template" />
CSElements
<ics:sql
sql="SELECT name,updateddate FROM CSElement WHERE status != 'VO'"
listname="CSEList"
table="CSElement" />
Start Menus
<ics:sql sql="select sm.id, sm.cs_name, sm.cs_description, sm.cs_assettype,
sm.cs_assetsubtype, sm.cs_itemtype, smr.cs_rolename, p.name
from StartMenu sm, StartMenu_Sites sms, StartMenu_Roles smr, Publication p
where sm.id=sms.ownerid and sm.id=smr.cs_ownerid and sms.pubid=p.id
order by sm.id"
listname="startList"
table="Publication,StartMenu,StartMenu_Roles,StartMenu_Sites"/>
Publishing Configurations
<ics:sql
sql="select id, name, description, type, dest, factors from PubTarget"
listname="pubTargetList"
table="PubTarget" />
Tree Tabs
<ics:sql
sql="select tt.id, tt.title, tt.tooltip, p.name as pubname, ttr.cs_rolename, ttsect.name as sectname
from TreeTabs tt, TreeTabs_Roles ttr, TreeTabs_Sect ttsect,TreeTabs_Sites ttsites
LEFT JOIN Publication p on p.id=ttsites.pubid
where p.id is not null and tt.id=ttsites.ownerid and ttsites.pubid=p.id and
tt.id=ttr.cs_ownerid and tt.id=ttsect.ownerid
order by tt.id"
listname="treeTabList"
table="TreeTabs,TreeTabs_Roles,TreeTabs_Sect,TreeTabs_Sites,Publication" />
Filters
<ics:sql
sql="select name,description,classname from Filters"
listname="filtersList"
table="Filters" />
Attribute Types
<ics:sql
sql="select id,valuetype,name,updateddate from AttrTypes where status != 'VO'"
listname="AttrList"
table="AttrTypes" />
WebReference Patterns
<ics:sql
sql="select id,webroot,pattern,assettype,name,params,publication from WebReferencesPatterns"
listname="WebRefList"
table="WebReferencesPatterns" />
Device Groups
<ics:sql
sql="select id,devicegroupsuffix,updateddate,name from DeviceGroup"
listname="DeviceList"
table="DeviceGroup" />
Site Entries
<ics:sql
sql="select se.id,se.name,se.pagename,se.cselement_id,se.updateddate,cse.rootelement from SiteEntry se
LEFT JOIN CSElement cse on cse.id = se.cselement_id where se.status != 'VO'"
listname="SiteList"
table="SiteEntry,CSElement" />
Webroots
<ics:sql
sql="select id,name,rooturl,updatedby,updateddate from WebRoot"
listname="webrootList"
table="WebRoot" />
Page Definitions
<ics:sql
sql="select pd.id, pd.name, pd.updatedby, pd.updateddate, pd.description, pdt.attributeid,
pa.name as nameattr, pdt.requiredflag, pdt.ordinal
from PageDefinition pd, PageDefinition_TAttr pdt, PageAttribute pa where pd.status != 'VO'
and pa.id=pdt.attributeid and pdt.ownerid=pd.id order by pd.id,pdt.ordinal"
listname="pageDefList"
table="PageDefinition,PageAttribute,PageDefinition_TAttr" />
FW_Application
<ics:sql
sql="select id,name,updateddate from FW_Application where status != 'VO'"
listname="FWList" table="FW_Application" />
Custom Elements
<ics:sql
sql="select elementname from ElementCatalog where elementname like 'CustomElements%'"
listname="elementList"
table="ElementCatalog" />
