org.apache.cocoon.transformation
Class SQLTransformer

java.lang.Object
  org.apache.avalon.framework.logger.AbstractLogEnabled
      org.apache.cocoon.xml.AbstractXMLProducer
          org.apache.cocoon.xml.AbstractXMLPipe
              org.apache.cocoon.transformation.AbstractTransformer
                  org.apache.cocoon.transformation.AbstractSAXTransformer
                      org.apache.cocoon.transformation.SQLTransformer

All Implemented Interfaces:

Poolable, Recyclable, Disposable, Component, Configurable, LogEnabled, Serviceable, SitemapModelComponent, Transformer, XMLPipe, XMLProducer, XMLConsumer, ContentHandler, LexicalHandler

public class SQLTransformer
extends AbstractSAXTransformer

The SQLTransformer can be plugged into a pipeline to transform SAX events into updated or queries and responses to/from a SQL interface.

It is declared and configured as follows:

 <map:transformers default="...">
   <map:transformer name="sql" src="org.apache.cocoon.transformation.SQLTransformer">
     <old-driver>false</old-driver>
     <connection-attempts>5</connection-attempts>
     <connection-waittime>5000</connection-waittime>
   </map:transformer>
 </map:transformers>
 

It can be used in the sitemap pipeline as follows: <map:transform type="sql"> <map:parameter name="own-connection" value="..."/> <map:parameter name="use-connection" value="..."/> <map:parameter name="dburl" value="..."/> <map:parameter name="username" value="..."/> <map:parameter name="password" value="..."/> <map:parameter name="show-nr-or-rows" value="false"/> <map:parameter name="doc-element" value="rowset"/> <map:parameter name="row-element" value="row"/> <map:parameter name="namespace-uri" value="http://apache.org/cocoon/SQL/2.0"/> <map:parameter name="namespace-prefix" value="sql"/> <map:parameter name="clob-encoding" value=""/> </map:transform>

The following DTD is valid: <!ENTITY % param "(own-connection?,(use-connection|(dburl,username,password))?,show-nr-or-rows?,doc-element?,row-element?,namespace-uri?,namespace-prefix?,clob-encoding?)">
<!ELEMENT execute-query (query,(in-parameter|in-xml-parameter|out-parameter)*,execute-query?, %param;)>
<!ELEMENT own-connection (#PCDATA)>
<!ELEMENT use-connection (#PCDATA)>
<!ELEMENT query (#PCDATA | substitute-value | ancestor-value | escape-string| xml)*>
<!ATTLIST query name CDATA #IMPLIED isstoredprocedure (true|false) "false" isupdate (true|false) "false">
<!ELEMENT substitute-value EMPTY>
<!ATTLIST substitute-value name CDATA #REQUIRED>
<!ELEMENT ancestor-value EMPTY>
<!ATTLIST ancestor-value name CDATA #REQUIRED level CDATA #REQUIRED>
<!ELEMENT in-parameter EMPTY>
<!ATTLIST in-parameter nr CDATA #REQUIRED type CDATA #REQUIRED>
<!ELEMENT in-xml-parameter EMPTY>
<!ATTLIST in-xml-parameter nr CDATA #REQUIRED type CDATA #REQUIRED>
<!ELEMENT out-parameter EMPTY>
<!ATTLIST out-parameter nr CDATA #REQUIRED name CDATA #REQUIRED type CDATA #REQUIRED>
<!ELEMENT escape-string (#PCDATA)>
<!ELEMENT xml (#PCDATA)>


Each query can override default transformer parameters. Nested queries do not inherit parent query parameters, but only transformer parameters. Each query can have connection to different database, directly or using the connection pool. If database connection parameters are the same as for any of the ancestor queries, nested query will re-use ancestor query connection.

Connection sharing between queries can be disabled, globally or on per-query basis, using own-connection parameter.

By default, CLOBs are read from the database using getSubString, so that character decoding is performed by the database. Using clob-encoding parameter, this behavior can be overrided, so that data is read as byte stream and decoded using specified character encoding.

Inserting of XML data can be done by using the new sql:xml or SQL:in-xml-parameter tags. - sql:xml must be used like sql:escape-string - sql:in-xml-parameter must be used like sql:in-parameter.

Version:

$Id: SQLTransformer.html 1304258 2012-03-23 10:09:27Z ilgrosso $

Author:

Carsten Ziegeler, Donald Ball, Giacomo Pati (PWR Organisation & Entwicklung), Sven Beauprez, Alfio Saglimbeni, Philipp Hahn, Vadim Gritsenko

Field Summary

static String	CLOB_ENCODING
protected XMLSerializer	compiler
protected Connection	conn The connection shared by top level queries (if configuration allows)
protected int	connectAttempts How many connection attempts to do?
protected int	connectWaittime How long wait between connection attempts?
protected String	connName The "name" of the connection shared by top level queries (if configuration allows)
protected ServiceSelector	datasources The datasource component selector
protected XMLDeserializer	interpreter
static String	MAGIC_ANCESTOR_VALUE
static String	MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE
static String	MAGIC_ANCESTOR_VALUE_NAME_ATTRIBUTE
static String	MAGIC_COLUMN_CASE
static String	MAGIC_CONNECTION
static String	MAGIC_DBURL
static String	MAGIC_DOC_ELEMENT
static String	MAGIC_ERROR
static String	MAGIC_ESCAPE_STRING
static String	MAGIC_EXECUTE_QUERY
static String	MAGIC_IN_PARAMETER
static String	MAGIC_IN_PARAMETER_NR_ATTRIBUTE
static String	MAGIC_IN_PARAMETER_VALUE_ATTRIBUTE
static String	MAGIC_IN_XML_PARAMETER
static String	MAGIC_NAME_ATTRIBUTE
static String	MAGIC_NR_OF_ROWS
static String	MAGIC_NS_PREFIX_ELEMENT
static String	MAGIC_NS_URI_ELEMENT
static String	MAGIC_OUT_PARAMETER
static String	MAGIC_OUT_PARAMETER_NAME_ATTRIBUTE
static String	MAGIC_OUT_PARAMETER_NR_ATTRIBUTE
static String	MAGIC_OUT_PARAMETER_TYPE_ATTRIBUTE
static String	MAGIC_PASSWORD
static String	MAGIC_PROP
static String	MAGIC_QUERY
static String	MAGIC_ROW_ELEMENT
static String	MAGIC_STORED_PROCEDURE_ATTRIBUTE
static String	MAGIC_SUBSTITUTE_VALUE
static String	MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE
static String	MAGIC_UPDATE_ATTRIBUTE
static String	MAGIC_USERNAME
static String	MAGIC_VALUE
static String	MAGIC_XML
static String	NAMESPACE The SQL transformer namespace
protected boolean	oldDriver Is the old-driver turned on?
protected SAXParser	parser
protected org.apache.cocoon.transformation.SQLTransformer.Query	query The current query we are working on
protected int	state The current state of the event receiving FSM
protected static int	STATE_INSIDE_ANCESTOR_VALUE_ELEMENT
protected static int	STATE_INSIDE_ESCAPE_STRING
protected static int	STATE_INSIDE_EXECUTE_QUERY_ELEMENT
protected static int	STATE_INSIDE_IN_PARAMETER_ELEMENT
protected static int	STATE_INSIDE_IN_XML_PARAMETER_ELEMENT
protected static int	STATE_INSIDE_OUT_PARAMETER_ELEMENT
protected static int	STATE_INSIDE_QUERY_ELEMENT
protected static int	STATE_INSIDE_SUBSTITUTE_VALUE_ELEMENT
protected static int	STATE_INSIDE_VALUE_ELEMENT
protected static int	STATE_INSIDE_XML
protected static int	STATE_OUTSIDE

Fields inherited from class org.apache.cocoon.transformation.AbstractSAXTransformer

context, defaultNamespaceURI, EMPTY_ATTRIBUTES, emptyAttributes, ignoreEmptyCharacters, ignoreEventsCount, ignoreHooksCount, ignoreWhitespaces, manager, namespaceURI, objectModel, parameters, recorderStack, request, resolver, response, source, stack

Fields inherited from class org.apache.cocoon.xml.AbstractXMLProducer

contentHandler, EMPTY_CONTENT_HANDLER, lexicalHandler, xmlConsumer

Fields inherited from interface org.apache.cocoon.transformation.Transformer

ROLE

Constructor Summary

SQLTransformer()
Constructor

Method Summary

void	configure(Configuration conf) Configure transformer.
protected void	data(String data) Helper method for generating SAX events
void	dispose() Dispose
protected void	end(String uri, String prefix, String name) Helper method for generating SAX events
protected void	endAncestorValueElement() </ancestor-value>
protected void	endEscapeStringElement() </escape-string>
protected void	endExecuteQueryElement() </execute-query>
protected void	endInParameterElement() </in-parameter>
protected void	endInXmlParameterElement() </xml>
protected void	endOutParameterElement() </out-parameter>
protected void	endQueryElement() </query>
protected void	endSubstituteValueElement() </substitute-value>
String	endTextRecording() Stop recording of text and return the recorded information.
void	endTransformingElement(String uri, String name, String raw) ContentHandler method
protected void	endValueElement() </*>
protected void	endXmlElement() </xml>
protected String	nsQualify(String name, String prefix) Qualifies an element name by giving it a prefix.
void	recycle() Recycle this component
void	service(ServiceManager manager) Serviceable
void	setup(SourceResolver resolver, Map objectModel, String source, Parameters parameters) Setup for the current request.
protected void	start(String uri, String prefix, String name, Attributes attr) Helper method for generating SAX events
protected void	startAncestorValueElement(Attributes attributes) <ancestor-value>
protected void	startEscapeStringElement(Attributes attributes) <escape-string>
protected void	startExecuteQueryElement() <execute-query>
protected void	startInParameterElement(Attributes attributes) <in-parameter>
protected void	startInXmlParameterElement(Attributes attributes) <xml>
protected void	startOutParameterElement(Attributes attributes) <out-parameter>
protected void	startQueryElement(Attributes attributes) <query>
protected void	startSubstituteValueElement(Attributes attributes) <substitute-value>
void	startTransformingElement(String uri, String name, String raw, Attributes attributes) ContentHandler method
protected void	startValueElement(String name) <*>
protected void	startXmlElement(Attributes attributes) <xml>
protected static void	throwIllegalStateException(String message)

Methods inherited from class org.apache.cocoon.transformation.AbstractSAXTransformer

addRecorder, characters, comment, endCDATA, endDocument, endDTD, endElement, endEntity, endParametersRecording, endParametersRecording, endPrefixMapping, endRecording, endSAXRecording, endSerializedXMLRecording, findPrefixMapping, getMutableAttributes, ignorableWhitespace, processingInstruction, removeRecorder, sendEndElementEvent, sendEndElementEventNS, sendEndPrefixMapping, sendEvents, sendParametersEvents, sendStartElementEvent, sendStartElementEvent, sendStartElementEventNS, sendStartElementEventNS, sendStartPrefixMapping, sendTextEvent, setDocumentLocator, setupTransforming, skippedEntity, startCDATA, startDocument, startDTD, startElement, startEntity, startParametersRecording, startPrefixMapping, startRecording, startSAXRecording, startSerializedXMLRecording, startTextRecording

Methods inherited from class org.apache.cocoon.xml.AbstractXMLProducer

setConsumer, setContentHandler, setLexicalHandler

Methods inherited from class org.apache.avalon.framework.logger.AbstractLogEnabled

enableLogging, getLogger, setupLogger, setupLogger, setupLogger

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface org.apache.cocoon.xml.XMLProducer

setConsumer

Field Detail

NAMESPACE

public static final String NAMESPACE

The SQL transformer namespace

See Also:

Constant Field Values

MAGIC_EXECUTE_QUERY

public static final String MAGIC_EXECUTE_QUERY

See Also:

Constant Field Values

MAGIC_CONNECTION

public static final String MAGIC_CONNECTION

See Also:

Constant Field Values

MAGIC_DBURL

public static final String MAGIC_DBURL

See Also:

Constant Field Values

MAGIC_USERNAME

public static final String MAGIC_USERNAME

See Also:

Constant Field Values

MAGIC_PASSWORD

public static final String MAGIC_PASSWORD

See Also:

Constant Field Values

MAGIC_PROP

public static final String MAGIC_PROP

See Also:

Constant Field Values

MAGIC_NR_OF_ROWS

public static final String MAGIC_NR_OF_ROWS

See Also:

Constant Field Values

MAGIC_QUERY

public static final String MAGIC_QUERY

See Also:

Constant Field Values

MAGIC_VALUE

public static final String MAGIC_VALUE

See Also:

Constant Field Values

MAGIC_COLUMN_CASE

public static final String MAGIC_COLUMN_CASE

See Also:

Constant Field Values

MAGIC_DOC_ELEMENT

public static final String MAGIC_DOC_ELEMENT

See Also:

Constant Field Values

MAGIC_ROW_ELEMENT

public static final String MAGIC_ROW_ELEMENT

See Also:

Constant Field Values

MAGIC_IN_PARAMETER

public static final String MAGIC_IN_PARAMETER

See Also:

Constant Field Values

MAGIC_IN_PARAMETER_NR_ATTRIBUTE

public static final String MAGIC_IN_PARAMETER_NR_ATTRIBUTE

See Also:

Constant Field Values

MAGIC_IN_PARAMETER_VALUE_ATTRIBUTE

public static final String MAGIC_IN_PARAMETER_VALUE_ATTRIBUTE

See Also:

Constant Field Values

MAGIC_OUT_PARAMETER

public static final String MAGIC_OUT_PARAMETER

See Also:

Constant Field Values

MAGIC_OUT_PARAMETER_NAME_ATTRIBUTE

public static final String MAGIC_OUT_PARAMETER_NAME_ATTRIBUTE

See Also:

Constant Field Values

MAGIC_OUT_PARAMETER_NR_ATTRIBUTE

public static final String MAGIC_OUT_PARAMETER_NR_ATTRIBUTE

See Also:

Constant Field Values

MAGIC_OUT_PARAMETER_TYPE_ATTRIBUTE

public static final String MAGIC_OUT_PARAMETER_TYPE_ATTRIBUTE

See Also:

Constant Field Values

MAGIC_ESCAPE_STRING

public static final String MAGIC_ESCAPE_STRING

See Also:

Constant Field Values

MAGIC_XML

public static final String MAGIC_XML

See Also:

Constant Field Values

MAGIC_IN_XML_PARAMETER

public static final String MAGIC_IN_XML_PARAMETER

See Also:

Constant Field Values

MAGIC_ERROR

public static final String MAGIC_ERROR

See Also:

Constant Field Values

MAGIC_NS_URI_ELEMENT

public static final String MAGIC_NS_URI_ELEMENT

See Also:

Constant Field Values

MAGIC_NS_PREFIX_ELEMENT

public static final String MAGIC_NS_PREFIX_ELEMENT

See Also:

Constant Field Values

MAGIC_ANCESTOR_VALUE

public static final String MAGIC_ANCESTOR_VALUE

See Also:

Constant Field Values

MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE

public static final String MAGIC_ANCESTOR_VALUE_LEVEL_ATTRIBUTE

See Also:

Constant Field Values

MAGIC_ANCESTOR_VALUE_NAME_ATTRIBUTE

public static final String MAGIC_ANCESTOR_VALUE_NAME_ATTRIBUTE

See Also:

Constant Field Values

MAGIC_SUBSTITUTE_VALUE

public static final String MAGIC_SUBSTITUTE_VALUE

See Also:

Constant Field Values

MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE

public static final String MAGIC_SUBSTITUTE_VALUE_NAME_ATTRIBUTE

See Also:

Constant Field Values

MAGIC_NAME_ATTRIBUTE

public static final String MAGIC_NAME_ATTRIBUTE

See Also:

Constant Field Values

MAGIC_STORED_PROCEDURE_ATTRIBUTE

public static final String MAGIC_STORED_PROCEDURE_ATTRIBUTE

See Also:

Constant Field Values

MAGIC_UPDATE_ATTRIBUTE

public static final String MAGIC_UPDATE_ATTRIBUTE

See Also:

Constant Field Values

CLOB_ENCODING

public static final String CLOB_ENCODING

See Also:

Constant Field Values

STATE_OUTSIDE

protected static final int STATE_OUTSIDE

See Also:

Constant Field Values

STATE_INSIDE_EXECUTE_QUERY_ELEMENT

protected static final int STATE_INSIDE_EXECUTE_QUERY_ELEMENT

See Also:

Constant Field Values

STATE_INSIDE_VALUE_ELEMENT

protected static final int STATE_INSIDE_VALUE_ELEMENT

See Also:

Constant Field Values

STATE_INSIDE_QUERY_ELEMENT

protected static final int STATE_INSIDE_QUERY_ELEMENT

See Also:

Constant Field Values

STATE_INSIDE_ANCESTOR_VALUE_ELEMENT

protected static final int STATE_INSIDE_ANCESTOR_VALUE_ELEMENT

See Also:

Constant Field Values

STATE_INSIDE_SUBSTITUTE_VALUE_ELEMENT

protected static final int STATE_INSIDE_SUBSTITUTE_VALUE_ELEMENT

See Also:

Constant Field Values

STATE_INSIDE_IN_PARAMETER_ELEMENT

protected static final int STATE_INSIDE_IN_PARAMETER_ELEMENT

See Also:

Constant Field Values

STATE_INSIDE_OUT_PARAMETER_ELEMENT

protected static final int STATE_INSIDE_OUT_PARAMETER_ELEMENT

See Also:

Constant Field Values

STATE_INSIDE_ESCAPE_STRING

protected static final int STATE_INSIDE_ESCAPE_STRING

See Also:

Constant Field Values

STATE_INSIDE_XML

protected static final int STATE_INSIDE_XML

See Also:

Constant Field Values

STATE_INSIDE_IN_XML_PARAMETER_ELEMENT

protected static final int STATE_INSIDE_IN_XML_PARAMETER_ELEMENT

See Also:

Constant Field Values

oldDriver

protected boolean oldDriver

Is the old-driver turned on? (default is off)

connectAttempts

protected int connectAttempts

How many connection attempts to do? (default is 5 times)

connectWaittime

protected int connectWaittime

How long wait between connection attempts? (default is 5000 ms)

query

protected org.apache.cocoon.transformation.SQLTransformer.Query query

The current query we are working on

state

protected int state

The current state of the event receiving FSM

datasources

protected ServiceSelector datasources

The datasource component selector

connName

protected String connName

The "name" of the connection shared by top level queries (if configuration allows)

conn

protected Connection conn

The connection shared by top level queries (if configuration allows)

compiler

protected XMLSerializer compiler

interpreter

protected XMLDeserializer interpreter

parser

protected SAXParser parser

Constructor Detail

SQLTransformer

public SQLTransformer()

Constructor

Method Detail

service

public void service(ServiceManager manager)
             throws ServiceException

Serviceable

Specified by:

service in interface Serviceable

Overrides:

service in class AbstractSAXTransformer

Throws:

ServiceException

configure

public void configure(Configuration conf)
               throws ConfigurationException

Configure transformer. Supported configuration elements:

• old-driver
• connect-attempts
• connect-waittime

Specified by:

configure in interface Configurable

Overrides:

configure in class AbstractSAXTransformer

Throws:

ConfigurationException

setup

public void setup(SourceResolver resolver,
                  Map objectModel,
                  String source,
                  Parameters parameters)
           throws ProcessingException,
                  SAXException,
                  IOException

Setup for the current request.

Specified by:

setup in interface SitemapModelComponent

Overrides:

setup in class AbstractSAXTransformer

Throws:

ProcessingException

SAXException

IOException

recycle

public void recycle()

Recycle this component

Specified by:

recycle in interface Recyclable

Overrides:

recycle in class AbstractSAXTransformer

dispose

public void dispose()

Dispose

Specified by:

dispose in interface Disposable

Overrides:

dispose in class AbstractSAXTransformer

throwIllegalStateException

protected static void throwIllegalStateException(String message)

startExecuteQueryElement

protected void startExecuteQueryElement()

<execute-query>

startValueElement

protected void startValueElement(String name)
                          throws SAXException

<*>

Throws:

SAXException

startQueryElement

protected void startQueryElement(Attributes attributes)
                          throws SAXException

<query>

Throws:

SAXException

endQueryElement

protected void endQueryElement()
                        throws ProcessingException,
                               SAXException

</query>

Throws:

ProcessingException

SAXException

endValueElement

protected void endValueElement()
                        throws SAXException

</*>

Throws:

SAXException

endExecuteQueryElement

protected void endExecuteQueryElement()
                               throws SAXException

</execute-query>

Throws:

SAXException

startAncestorValueElement

protected void startAncestorValueElement(Attributes attributes)
                                  throws ProcessingException,
                                         SAXException

<ancestor-value>

Throws:

ProcessingException

SAXException

endAncestorValueElement

protected void endAncestorValueElement()

</ancestor-value>

startSubstituteValueElement

protected void startSubstituteValueElement(Attributes attributes)
                                    throws ProcessingException,
                                           SAXException

<substitute-value>

Throws:

ProcessingException

SAXException

endSubstituteValueElement

protected void endSubstituteValueElement()

</substitute-value>

startEscapeStringElement

protected void startEscapeStringElement(Attributes attributes)
                                 throws ProcessingException,
                                        SAXException

<escape-string>

Throws:

ProcessingException

SAXException

endEscapeStringElement

protected void endEscapeStringElement()
                               throws SAXException

</escape-string>

Throws:

SAXException

startXmlElement

protected void startXmlElement(Attributes attributes)
                        throws ProcessingException,
                               SAXException

<xml>

Throws:

ProcessingException

SAXException

endXmlElement

protected void endXmlElement()
                      throws ProcessingException,
                             SAXException

</xml>

Throws:

ProcessingException

SAXException

startInXmlParameterElement

protected void startInXmlParameterElement(Attributes attributes)
                                   throws ProcessingException,
                                          SAXException

<xml>

Throws:

ProcessingException

SAXException

endInXmlParameterElement

protected void endInXmlParameterElement()
                                 throws ProcessingException,
                                        SAXException

</xml>

Throws:

ProcessingException

SAXException

startInParameterElement

protected void startInParameterElement(Attributes attributes)

<in-parameter>

endInParameterElement

protected void endInParameterElement()

</in-parameter>

startOutParameterElement

protected void startOutParameterElement(Attributes attributes)

<out-parameter>

endOutParameterElement

protected void endOutParameterElement()

</out-parameter>

startTransformingElement

public void startTransformingElement(String uri,
                                     String name,
                                     String raw,
                                     Attributes attributes)
                              throws ProcessingException,
                                     SAXException

ContentHandler method

Overrides:

startTransformingElement in class AbstractSAXTransformer

Parameters:

uri - The namespace of the element.

name - The local name of the element.

raw - The qualified name of the element.

attributes - The attributes of the element.

Throws:

ProcessingException

SAXException

endTransformingElement

public void endTransformingElement(String uri,
                                   String name,
                                   String raw)
                            throws ProcessingException,
                                   IOException,
                                   SAXException

ContentHandler method

Overrides:

endTransformingElement in class AbstractSAXTransformer

Parameters:

uri - The namespace of the element.

name - The local name of the element.

raw - The qualified name of the element.

Throws:

ProcessingException

IOException

SAXException

nsQualify

protected String nsQualify(String name,
                           String prefix)

Qualifies an element name by giving it a prefix.

Parameters:

name - the element name

prefix - the prefix to qualify with

Returns:

a namespace qualified name that is correct

start

protected void start(String uri,
                     String prefix,
                     String name,
                     Attributes attr)
              throws SAXException

Helper method for generating SAX events

Throws:

SAXException

end

protected void end(String uri,
                   String prefix,
                   String name)
            throws SAXException

Helper method for generating SAX events

Throws:

SAXException

data

protected void data(String data)
             throws SAXException

Helper method for generating SAX events

Throws:

SAXException

endTextRecording

public String endTextRecording()
                        throws SAXException

Stop recording of text and return the recorded information.

Overrides:

endTextRecording in class AbstractSAXTransformer

Returns:

The String, trimmed. NB. SQLTransformer needs to have a special version of this method It needs the TextRecorder to not trim whitespace from the queries it is building

Throws:

SAXException