How to support a new database or a new programming language

SPWrapper code can be arranged into two layers. On the top we have the ant tasks and the code generators, these classes knows how to build source code but don't know very much of the underlaying database. On the bottom layer there are introspectors: these classes knows all the database related details about datatypes, driver classes, etc.

For instance while running SqlJaTor, the translator detects input parameters, looks for a suitable introspector wich in turn execute sql code and detects output parameter types and driver details. With this information the translator is finally able to assemble java code.

This page explains how to add support for a new database and explains how to write a new translator to generate source files in other languages.

Introspectors

Since version 0.7 there are two types of introspectors: hardcoded introspectors and fallback introspectors. Hardcoded introspectors are java classes that extends the net.sourceforge.spwrapper.Introspector class, are hard to be build, but can be used from both the stored procedure and the sql statement code generators. Fallback introspector is a class that can be configured with text files and enables SqlJaTor to quickly work with a new db

This tutorial covers only the FallbackIntrospector configuration, because it can be done quickly and can even be used to fix bugs with less tested databases. If you are interested into Hardcoded instropectors look at net.sourceforge.spwrapper.oracle.OracleIntrospector source code.

Fallback Introspectors

The net.sourceforge.spwrapper.fallback.FallbackIntrospector is instantiated many times with various configuration files, one for each database. Actually at SPWrapper startup the FallbackIntrospector is registered three times: one for Postgresql, one for Mysql and then for Sql Server.

The good new is that at startup it is possible to register your own configuration without recompiling SPWrapper. Simply add this option to your java vm -Dnet.sourceforge.spwrapper.custom-introspector=/my/new/configuration.properties This option takes the precedence over other standard introspector, so you will also be able to override default spwrapper behaviours. Obviously the file configuration.properties must be placed into the directory my/new into your current CLASSPATH

The configuration files

The version 0.9 introduced new configuration files, extending what was possible to achieve with previous releases.

This table descibes these files; one note: these files can have wathever name you decide.

The first configuration file must be provided for each database, usually this file is named as the database. For instance for Oracle the configuration file will be named oracle.properties

This file must contain these keys:

example

#defines the configuration for the OracleIntrospector
#  
#    Copyright (C) 2007  Giovanni Bricconi
#
#    This library is free software; you can redistribute it and/or
#    modify it under the terms of the GNU Lesser General Public
#    License as published by the Free Software Foundation; either
#    version 2 of the License, or (at your option) any later version.
#
#    This library is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
#    Lesser General Public License for more details.
#
#    You should have received a copy of the GNU Lesser General Public
#    License along with this library; if not, write to the Free Software
#    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
# 

supported.driver.classes=oracle.jdbc.OracleDriver,oracle.jdbc.driver.OracleDriver
supported.connection.classes=oracle.jdbc.OracleConnectionWrapper,oracle.jdbc.driver.T4CConnection
#Driver name for error messages, language independent
driver.name=OracleDriver
#language bindings
language-supported1=java
lang-class-java=net.sourceforge.spwrapper.JBinding
lang-conf-java=/net/sourceforge/spwrapper/oracle/java.properties

language-supported2=python
lang-class-python=net.sourceforge.spwrapper.python.PyBinding
lang-conf-python=/net/sourceforge/spwrapper/oracle/python.properties

Language binding configuration

Each language binding must be properly configured, and each language binding has a different set of configuration needed. Let's look at Java configuration and later to Python configuration.

Java binding

example

# java bindings for oracle
#  
#    Copyright (C) 2007  Giovanni Bricconi
#
#    This library is free software; you can redistribute it and/or
#    modify it under the terms of the GNU Lesser General Public
#    License as published by the Free Software Foundation; either
#    version 2 of the License, or (at your option) any later version.
#
#    This library is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
#    Lesser General Public License for more details.
#
#    You should have received a copy of the GNU Lesser General Public
#    License along with this library; if not, write to the Free Software
#    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
# 
language=java
additional.imports=oracle.jdbc, oracle.sql,net.sourceforge.spwrapper.oracle

driver.specific.mappings=/net/sourceforge/spwrapper/oracle/OracleMappings.xml
#currently not needed
#type.alias=
forname.class=oracle.jdbc.OracleDriver
callable.statement.class=oracle.jdbc.OracleCallableStatement
prepared.statement.class=oracle.jdbc.OraclePreparedStatement
result.set.class=oracle.jdbc.OracleResultSet

Python binding

example

# python bindings for oracle
#  
#    Copyright (C) 2007  Giovanni Bricconi
#
#    This library is free software; you can redistribute it and/or
#    modify it under the terms of the GNU Lesser General Public
#    License as published by the Free Software Foundation; either
#    version 2 of the License, or (at your option) any later version.
#
#    This library is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
#    Lesser General Public License for more details.
#
#    You should have received a copy of the GNU Lesser General Public
#    License along with this library; if not, write to the Free Software
#    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
# 
language=python
additional.imports=cx_Oracle

#driver.specific.mappings=/net/sourceforge/spwrapper/oracle/OracleMappings.xml
#currently not needed
type.alias=/net/sourceforge/spwrapper/oracle/python-alias.properties

module.name=cx_Oracle
parameter.style=named

mappings.xml

The exact name of this file must be reported into the language binding configuration files. Simply call it MysqlMappings,xml, SqlServerMappings.xml or wathever else is appropriate.

To discover what can be written into this file we will analyze the OracleMappings.xml file provided with spwrapper sources.

<?xml version="1.0" encoding="UTF-8"?>
<!--
    OracleMappings.xml - bridge between sql data types and java code

    Copyright (C) 2006  Giovanni Bricconi

    This library is free software; you can redistribute it and/or
    modify it under the terms of the GNU Lesser General Public
    License as published by the Free Software Foundation; either
    version 2 of the License, or (at your option) any later version.

    This library is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    Lesser General Public License for more details.

    You should have received a copy of the GNU Lesser General Public
    License along with this library; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

-->
<!DOCTYPE SPWrapperMappings [
<!ENTITY stdInVarDecl "<![CDATA[protected boolean {1}_setNull=false;
				public void set_{1}_null(boolean b) '{'{1}_setNull = b; }
				protected {3} {1};
				public void set_{1}({3} in) '{'{1} = in; }
                ]]>" >
    <!ENTITY stdOutVarDecl "<![CDATA[protected boolean {1}_wasNull=false;
        		public boolean was_{1}_null() '{'  return {1}_wasNull; }
        		protected {3} {1};
        		public {3} get_{1}() '{' return {1}; }
        ]]>">
    <!ENTITY stdInOutVarDecl "<![CDATA[protected boolean {1}_setNull=false;
				public void set_{1}_null(boolean b) '{' {1}_setNull = b; }
				protected {3} {1};
				public void set_{1}({3} in) '{' {1} = in; }
				protected boolean {1}_wasNull=false;
                                public boolean was_{1}_null() '{' return {1}_wasNull; }
                                public {3} get_{1}() '{' return {1}; }
		]]>" >
	
]>
<Mappings>
	<Mapping sqlType="OBJECT" composite="true">
		<!-- JavaType>java.sql.Struct</JavaType -->
		<JavaType>java.lang.Object</JavaType>
		<InVarDecl>&stdInVarDecl;<![CDATA[
			protected String {1}_sqlType{4};
			public void set_{1}_sqlType(String in) '{' {1}_sqlType=in;}
		]]></InVarDecl>
		<OutVarDecl>&stdOutVarDecl;<![CDATA[
			protected String {1}_sqlType{4};
			public void set_{1}_sqlType(String in) '{' {1}_sqlType=in;}
			public java.sql.Struct get_{1}_Struct() '{' 
				return (java.sql.Struct) {1};
				} 
		]]></OutVarDecl>
		<InOutVarDecl>&stdInOutVarDecl;<![CDATA[
			protected String {1}_sqlType{4};
			public void set_{1}_sqlType(String in) '{' {1}_sqlType=in;}
			public java.sql.Struct get_{1}_Struct() '{' 
				return (java.sql.Struct) {1};
				} 
			]]>
		</InOutVarDecl>
		<Getter>
			<![CDATA[
			{1}=statement.getObject({2});//({3})statement.getObject({2});
			{1}_wasNull=statement.wasNull();
                ]]>
		</Getter>
		<Setter>
			<![CDATA[
			if ({1}_setNull) statement.setNull({2},Types.STRUCT,{1}_sqlType);
			else statement.setObject({2},{1});
                ]]>
		</Setter>
		<Register>
			<![CDATA[
			(narrowedStatement).registerOutParameter({2},OracleTypes.STRUCT,
				{1}_sqlType);
			]]>
		</Register>
	</Mapping>
	...
</Mappings>					
				

After the usual xml declaration some useful entities are declared, their purpose will be explained later. Until then consider them as macro code that will be used to avoid cut and paste.

The root element of this file is always Mappings, it can contain only Mapping elements. Each mapping defines a database type.

Mapping

The Mapping element has one mandatory attribute, sqlType; it specifies the new database specific data type.

Other Mapping attributes are composite, createCallback, quickCall. Composite must be set to true if the SQL type is structured, say has sub fields such as a C struct. CreateCallback must be set to true if you whant that a callback method is created and invoked each time a colum of this type is fetched out the result set, it is meant for BLOB like types. QuickCall must be set to false if it is ambiguous or not possible to pass this argument as java function call argument; an example is Oracle's RefCursor type. None of these attributes is mandatory.

Mapping has 7 children elements:

• JavaType - the name of the target java class/scalar type
• InVarDecl
• OutVarDecl
• InOutVarDecl
• Getter
• Setter
• Register

JavaType

This element is very simple, has no attirbute and should contains only the java type to wich you are mapping the current SQL type. It is a good idea to use fully qualified names (my.package.Class instead of Class)

InVarDecl, OutVarDecl and InOutVarDecl

These elements contains the java template to be used when the current type is used as input, output or as input-output argument. I decided not to use XSL templates because of their complexity and verbosity, the standard java MessageFormat was good enough for this task.

Remember to write '{' if you want to be an open brace in your java code, otherwise there will be a exception. The variables that will be used inside the templates are:

• {0} - the original SQL name of the parameter, can be empty if concering the functions return parameter. Probably useless.
• {1} - the java/python argument name, as detected or specified by the user.
• {2} - a number, the argument position in prepared statements/callable statement
• {3} - the java/python type, actually taken form the JavaType element
• {4} - the sql composite type name, useful only with composite types like structs
• {5} - dbapi module name

Now that you know the meaning of the parameter we can examine what you should place in the source templates

Within InVarDecl you should provide a member variable of the correct type, the corresponding setter, and a boolean varible and its setter to allow the user to pass NULL values as input parameters. Conventionally the boolean variable should be called {1}_setNull and its setter set_{1}_null(). As you can guess if we are dealing with the parameter "salary" these snippets will be transated into salaty_setNull and set_salary_null()

Within OutVarDecl you should declare the member variable and a getter, to allow the stub user to retrieve the actual value. Again you should add a boolean to detect NULL output values. Again the boolean variables should be named {1}_wasNull and its getter was_{1}_null()

The InOutVarDecl combines both needs, and is used only when the variable is used for input and output purposes.

Now the purpose of the XML entities can be explained, if those code snippet are correct for your datatype you can consider using them in this way:

 
<InVarDecl>&stdInVarDecl;</InVarDecl>
<OutVarDecl>&stdOutVarDecl;</OutVarDecl>
<InOutVarDecl>&stdInOutVarDecl;</InOutVarDecl>
			

Getter, Setter and Register

The getter is the code fragment used to read the parameter value out of the result set/callable statement. It should also detect the _wasNull value. Use statement as callable statement or result set variable, it will correctly replaced by the translator. Yow can also use narrowedStatement if you need driver specific functions.

The Setter obviously is the getter counterpart, should pass in the value or set it to null according to user request

Finally the Register element is needed to register this parameter as output parameter, with the correct type

Note for Python programmers

A lot of parameter described above have no meaning for Python programs. Usually you should only provide the sqlType for the Mapping element. Then the InVarDecl, OutVarDecl, InOutVarDecl and Getter. Refer to StandardMappings.xml in the net.sourceforge.spwrapper.python package.

This is an excerpt

<!DOCTYPE SPWrapperMappings [
    <!ENTITY stdInVarDecl "<![CDATA[    
    def set_{1}(self,_arg):
        self.{1}=_arg
        
]]>" >
...
	<Mapping sqlType="VARCHAR">
		<JavaType>notNeeded</JavaType>
		<InVarDecl>&stdInVarDecl;</InVarDecl>
		<OutVarDecl>&stdOutVarDecl;</OutVarDecl>
		<InOutVarDecl>&stdInOutVarDecl;</InOutVarDecl>
		<Getter>&stdGet;</Getter>
		<Setter>notNeeded</Setter>
		<Register>notNeeded</Register>
	</Mapping>

aliases.properties

This is a very simple property file, the key value should be your database type, and its value should be the standard SQL type to which you whant to map it. This is the list of the available target types for Java

If you do not find the suitable type in this table you have to add your own mappings.xml file. Python users should look into net.sourceforge.spwrapper.python.StandarMappings.xml.

# aliases.properties - aliases to support postgresql types
#  
#    Copyright (C) 2007  Giovanni Bricconi
#
#    This library is free software; you can redistribute it and/or
#    modify it under the terms of the GNU Lesser General Public
#    License as published by the Free Software Foundation; either
#    version 2 of the License, or (at your option) any later version.
#
#    This library is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
#    Lesser General Public License for more details.
#
#    You should have received a copy of the GNU Lesser General Public
#    License along with this library; if not, write to the Free Software
#    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
# 
# This file contains a set of aliases to map 
# database specific types to sql generic types
# Each row shoul contain an original type (case 
# sensitive) and a destination type, case sensitive too
# This file should be used only for types that 
# do not require special code handling. 
# The other types should stay in files 
# like net.sourceforge.spwrapper.oracle.OracleMappings.xml

int8=BIGINT
serial8=BIGINT
float8=DOUBLE
int4=INTEGER
int=INTEGER
float4=REAL
int2=SMALLINT
serial4=INTEGER
timestamp=TIMESTAMP
text=LONGVARCHAR
bpchar=CHAR
timestamptz=TIMESTAMP
timetz=TIME