Tridion Deployer Extensions - Insert DCPs in custom Database - Custom Module

For getting started with tridion deployer extensions please refer
https://sdltridionworld.com/articles/sdltridion2011/tutorials/Deployer_Extensions_With_Eclipse_1.aspx
Below is just sample requirement and sample code of deployer extension.

Requirement

After Successful Publish , Some of  DCP's (Dynamic Component Presentations) should be saved in Custom DB along with Broker DB.
Below Solution gives you example of:

• How to read tridion dynamic component presentations from transcation ZIP file
• How to read config values form cd_deployer_conf.xml  in your Java program config.getParameterValue
• How to read Metadata of Component using data.getMetaData Tridion API
• How to talk to JDBC and insert content 
• How to extend Module 
• How to write Custom Module

Solution

I have certain templates whose DCPs need to be saved in DV

1. Publish XML
2. GSA Content XML
3. GSA Asset XML

All CONTENT(NOT Binary) published with above Component Templates should be saved in custom DB
All BINARY Content published with GSA Asset XML DCP should be saved in custom DB


Sample cd_deployer_conf.xml entry

In your Java Project refer following libraries

Sample Java Code (Deployer Extensions):
package com.tridion.abc.extensions;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;
import java.util.Iterator;

import javax.naming.Context;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.tridion.configuration.Configuration;
import com.tridion.configuration.ConfigurationException;
import com.tridion.deployer.Module;
import com.tridion.deployer.ProcessingException;
import com.tridion.deployer.Processor;
import com.tridion.transport.transportpackage.Component;
import com.tridion.transport.transportpackage.ComponentPresentation;
import com.tridion.transport.transportpackage.ComponentPresentationKey;
import com.tridion.transport.transportpackage.MetaData;
import com.tridion.transport.transportpackage.MetaDataFile;
import com.tridion.transport.transportpackage.ProcessorInstructions;
import com.tridion.transport.transportpackage.Section;
import com.tridion.transport.transportpackage.TransportPackage;
import com.tridion.util.TCMURI;

public class InsertFeedintoDB extends Module {
private static Logger log = LoggerFactory
.getLogger(InsertFeedintoDB.class);
String action = null;
String contentXML = null;
String _GSAContentXML = null;
String _GSAAssetXML = null;
static String _DBSourceURL = null;
private static final String SQL_SELECTBY_CONTENTID = "SELECT CNTN_ID FROM C0064DBA.TIBPUB_EVNT WHERE CNTN_ID=?";
private static final String SQL_INSERT_COMPONENT = "INSERT INTO C0064DBA.TIBPUB_EVNT (PUB_EVNT_ID,CNTN_ID,EVNT_TS, CNTN_XML,EVNT_TYP,SCHM_NM ,GSA_CNT_XML , GSA_AST_XML) VALUES (C0064DBA.PUB_EVNT_SEQ.NEXTVAL,?,?,?,?,?,?,?)";
private static final String SQL_UPDATE_PUBLISHXML = "UPDATE C0064DBA.TIBPUB_EVNT SET CNTN_XML=?, EVNT_TS=?,EVNT_TYP=? WHERE CNTN_ID=?";
private static final String SQL_UPDATE_GSAASSETXML = "UPDATE C0064DBA.TIBPUB_EVNT SET GSA_AST_XML=?, EVNT_TS=?,EVNT_TYP=?  WHERE CNTN_ID=?";
private static final String SQL_UPDATE_GSACONTENTXML = "UPDATE C0064DBA.TIBPUB_EVNT SET GSA_CNT_XML=?, EVNT_TS=?,EVNT_TYP=?  WHERE CNTN_ID=?";

static DataSource _datasource = null;
//String itemXMLCLOB = null;
InputStream itemXMLCLOB;
int fileLength=0;
MetaDataFile compPresMetadata = null;
Connection conn = null;
MetaDataFile componentMeta = null;
String _RppAssetSchemaName = null;
String _RppAssetSchemaNameSpace = null;

public InsertFeedintoDB(Configuration config, Processor processor)
throws ConfigurationException {
super(config, processor);
log.info("InsertRPPFeedintoDB invoked");
}

// This method is called once for each TransportPackage that is
// deployed.
public void process(TransportPackage data) throws ProcessingException {
try {
contentXML = config.getParameterValue("PublishXMLTemplateName");
_GSAContentXML = config
.getParameterValue("GSAContentXMLTemplateName");
_GSAAssetXML = config.getParameterValue("GSAAssetTemplateName");
_DBSourceURL = config.getParameterValue("DBSourceURL");
_RppAssetSchemaName = config.getParameterValue("RppAssetSchemaName");
_RppAssetSchemaNameSpace = config.getParameterValue("RppAssetSchemaNameSpace");
log.debug("XML template value from config :" + "CONTENT XML:"
+ contentXML + "GSA CONTENT XML:" + _GSAContentXML
+ "GSA ASSET XML:" + _GSAAssetXML + "DB Url:"
+ _DBSourceURL);
getDataSource();
conn = getConnectionFromDataSource(_datasource);
ProcessorInstructions instructions = data
.getProcessorInstructions();
action = instructions.getAction();
MetaData compPresMetadataInfo = instructions
.getMetaData("ComponentPresentations");
MetaData compMetadataInfo = instructions.getMetaData("Components");
componentMeta = data.getMetaData("Components",
compMetadataInfo.getName());
compPresMetadata = data.getMetaData("ComponentPresentations",
compPresMetadataInfo.getName());
log.debug("Action " + action + " started for publication "
+ instructions.getPublicationId());

Section section = null;
Iterator<Section> Sections = instructions.getSections();
for (; Sections.hasNext(); processSection(section, data)) {
section = Sections.next();
}
// TODO: Insert into RPP DB
log.info("process started for inserting items into RPP Database");
} catch (ConfigurationException ex) {
log.error("Could not get custom configuration", ex.getMessage());
}
catch (Exception ex) {
log.error("Exception Occured", ex.getMessage());
}
finally {
try {
if (conn!=null)
conn.close();
} catch (Exception ex) {
log.error("InsertRPP DB Exception closing connection"
+ ex.getMessage());
}
}

}

protected void processSection(Section section, TransportPackage data) {
log.debug("RPP Processing Section " + section.getName());
Iterator iterator = section.getFileItems();
Object item;
for (; iterator.hasNext(); processItem(item, section, data)) {
item = iterator.next();
}
Section subSection;
for (Iterator i$ = section.getSubSections().iterator(); i$.hasNext(); processSection(
subSection, data))
subSection = (Section) i$.next();
}

protected void processItem(Object obj, Section section,
TransportPackage data) {
if (obj instanceof ComponentPresentationKey) {
log.debug("Entered ComponentPresentation");
ComponentPresentationKey cpKey = (ComponentPresentationKey) obj;
ComponentPresentation componentPresentation = (ComponentPresentation) compPresMetadata
.getMetaData(cpKey);
log.debug("componentPresentation " + componentPresentation);
log.debug("componentPresentationgetTemplateKey getTitle()"
+ componentPresentation.getTemplateKey().getTitle());
try {
File cpFile = new File((new StringBuilder())
.append(data.getLocationPath())
.append(section.getRelativePath())
.append(cpKey.getName()).toString());
fileLength = (int) cpFile.length();
itemXMLCLOB=(InputStream) new FileInputStream(cpFile); // Added to get input stream from file
//itemXMLCLOB = IOUtils.toString(new FileReader(cpFile));
/**log.debug("itemXMLCLOB"
+ itemXMLCLOB);**/
TCMURI compTCMID = componentPresentation.getComponentKey()
.getId();
Component component = (Component)componentMeta.getMetaData(componentPresentation.getComponentKey());
log.debug("component"
+ component);
log.debug("compTCMID"
+ compTCMID);
log.debug("component.getSchemaNamespace()"
+ component.getSchemaNamespace());

String compID = compTCMID.toString();
String schemaName = null;

String SGID = null;
if(component.getSchemaNamespace().equalsIgnoreCase(_RppAssetSchemaNameSpace))
{
schemaName = _RppAssetSchemaName;
if(component.getCustomMeta().getElementsByTagName("StructureGroupID").item(0)!=null)
SGID = component.getCustomMeta().getElementsByTagName("StructureGroupID").item(0).getChildNodes().item(0).getNodeValue();
log.debug("SGID " + SGID);
}
else if(component.getCustomMeta().getElementsByTagName("BasedOnSchema").getLength() > 0)
schemaName = component.getCustomMeta().getElementsByTagName("BasedOnSchema").item(0).getChildNodes().item(0).getNodeValue();


log.debug("schemaName " + schemaName);
if (componentPresentation.getTemplateKey().getTitle()
.equalsIgnoreCase(contentXML)) {

if (!checkIfRecordExists(compID)) {
log.debug("No record exists fo Id therfore inserting"
+ compID);
// insert row into table Publish itemXMLCLOB

insertComponent(compID, schemaName, contentXML);
} else {
updateComponent(compID, contentXML);
// update table into table Publish itemXMLCLOB
}

}
if (componentPresentation.getTemplateKey().getTitle()
.equalsIgnoreCase(_GSAContentXML)) {
if (!checkIfRecordExists(compID)) {
log.debug("No record exists fo Id therfore inserting"
+ compID);
// insert row into table Publish itemXMLCLOB
insertComponent(compID, schemaName, _GSAContentXML);
} else {
updateComponent(compID, _GSAContentXML);
// update table into table Publish itemXMLCLOB
}
}
if (componentPresentation.getTemplateKey().getTitle()
.equalsIgnoreCase(_GSAAssetXML)) {

if(!schemaName.equalsIgnoreCase(_RppAssetSchemaName) || (schemaName.equalsIgnoreCase(_RppAssetSchemaName) && !SGID.isEmpty()))
{
if (!checkIfRecordExists(compID)) {
log.debug("No record exists fo Id therfore inserting"
+ compID);
insertComponent(compID, schemaName, _GSAAssetXML);

} else {
log.debug("Record exists fo Id therfore updating"
+ compID);
updateComponent(compID, _GSAAssetXML);
}
}

}

} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
log.error("File NOT FOUND" + e.getMessage());

} catch (IOException e) {
// TODO Auto-generated catch block
log.error("IOException" + e.getMessage());
}
}
}

void insertComponent(String compID, String schemaName, String templateName) {
try {
log.debug("Insert started for Id" + compID);
PreparedStatement pstmt = null;
try {
Calendar calendar = Calendar.getInstance();
java.sql.Timestamp ourJavaTimestampObject = new java.sql.Timestamp(
calendar.getTime().getTime());
pstmt = conn.prepareStatement(SQL_INSERT_COMPONENT);
pstmt.setString(1, compID);
pstmt.setTimestamp(2, ourJavaTimestampObject);
if (templateName.equalsIgnoreCase(contentXML))
//pstmt.setString(3, itemXMLCLOB);
pstmt.setAsciiStream(3, itemXMLCLOB,fileLength);
else
pstmt.setString(3, null);
pstmt.setString(4, "P");
pstmt.setString(5, schemaName);
if (templateName.equalsIgnoreCase(_GSAContentXML))
//pstmt.setString(6, itemXMLCLOB);
pstmt.setAsciiStream(6, itemXMLCLOB,fileLength);
else
pstmt.setString(6, null);
if (templateName.equalsIgnoreCase(_GSAAssetXML))
//pstmt.setString(7, itemXMLCLOB);
pstmt.setAsciiStream(7, itemXMLCLOB,fileLength);
else
pstmt.setString(7, null);
pstmt.execute();
} finally {
try {
pstmt.close();
} catch (Exception ex) {
log.error("insertComponent Exception closing stmt"
+ ex.getMessage());
}
}
} catch (Exception ex) {
log.error("insertComponent Exception" + ex.getMessage());
}
}

void updateComponent(String compID, String templateName) {
try {
log.debug("Update started for Id" + compID);
PreparedStatement pstmt = null;
try {
Calendar calendar = Calendar.getInstance();
java.sql.Timestamp ourJavaTimestampObject = new java.sql.Timestamp(
calendar.getTime().getTime());
if (templateName.equalsIgnoreCase(contentXML))
pstmt = conn.prepareStatement(SQL_UPDATE_PUBLISHXML);
if (templateName.equalsIgnoreCase(_GSAContentXML))
pstmt = conn.prepareStatement(SQL_UPDATE_GSACONTENTXML);
if (templateName.equalsIgnoreCase(_GSAAssetXML))
pstmt = conn.prepareStatement(SQL_UPDATE_GSAASSETXML);
//pstmt.setString(1, itemXMLCLOB);
pstmt.setAsciiStream(1, itemXMLCLOB,fileLength);
pstmt.setTimestamp(2, ourJavaTimestampObject);
pstmt.setString(3, "P");
pstmt.setString(4, compID);
pstmt.execute();
} finally {
try {
pstmt.close();
} catch (Exception ex) {
log.error("updateComponent Exception closing stmt"
+ ex.getMessage());
}
}
} catch (Exception ex) {
log.error("updateComponent Exception" + ex.getMessage());
}
}

synchronized private static void getDataSource() {
try {
if (_datasource == null) {
Context ctx = new javax.naming.InitialContext();
_datasource = (DataSource) ctx.lookup(_DBSourceURL);
}
} catch (NamingException ne) {
log.error("Exception in getDataSource InsertRPPFeed"
+ ne.getMessage());
}
}

/**
* This method returns the Connection from the given DataSource.
*
* @param ds
* @return
*
*/
private static Connection getConnectionFromDataSource(DataSource ds) {
Connection conn = null;
boolean retry = false;
int numOfRetries = 0;
do {
try {
conn = ds.getConnection();
} catch (Exception exp) {
if (exp.getClass().getName()
.indexOf("StaleConnectionException") != -1) {
if (numOfRetries > 2) {
retry = true;
numOfRetries++;
} else {
log.error("Exception in getConnectionFromDataSource"
+ exp.getMessage());
}
} else {
log.error("Exception in getConnectionFromDataSource"
+ exp.getMessage());
}
}
} while (retry);
return conn;
}

protected boolean checkIfRecordExists(String componentID) {
PreparedStatement pstmt = null;
ResultSet rset = null;
boolean recordExists = false;
try {
pstmt = conn.prepareStatement(SQL_SELECTBY_CONTENTID);
pstmt.setString(1, componentID);
try {
rset = pstmt.executeQuery();
try {
while (rset.next())
recordExists = true;
} finally {
try {
rset.close();
} catch (Exception ex) {
log.error("checkIfRecordExists Exception closing resultset"
+ ex.getMessage());
}
}
} finally {
try {
pstmt.close();
} catch (Exception ex) {
log.error("checkIfRecordExists Exception closing stmt"
+ ex.getMessage());
}
}
} catch (Exception ex) {
log.error("checkIfRecordExists Exception" + ex.getMessage());
}

return recordExists;

}

}