[task] Sql transformation

Description

Sqlt is an ant task that allows to:

• extract datas from database using ant sql task
• process datas using freemarker templates

Sqlt task parameters

Attribute	Description	Value	Required
template	freemarker template	.ftl	yes
tofile	output file	.	yes
overwrite	overwrite output file	true by default	no

Element	Description	Value	Required
sql	ant sql task		yes

Options

Mapping

<mapping groupby="model_id">
<column index="0" as="parameter_name"/>
<column index="1" as="parameter_type"/>
<column index="2" as="parameter_type_id"/>
<column index="3" as="model_name"/>
<column index="4" as="model_id"/>
</mapping>

Examples

Extract bugs from a mantis bugtracker and write them into csv file.

build.xml

ant file:

• set outputproperty

<project name="bidji-sqlt-project" xmlns:bj="antlib:org.bidji.taskdefs">
<target name="mantis" description="generate mantis2csv">		
	 <bj:sqlt template="mantis2csv.ftl" tofile="mantis.csv" overwrite="true">
		 <sql id="connection1"
			driver="com.mysql.jdbc.Driver"
			url="jdbc:mysql://$ {db.host}:3306/$ {db.name}"
			userid="readonly"
			password="readonly"
			print="true"
			encoding="UTF-8"
			delimiter="]["
			outputproperty="bugs"
			>
			 <classpath>
			  <pathelement path="lib/mysql-connector-java-5.1.22-bin.jar"/>
			 </classpath>
		 <![CDATA[
		SELECT b.id,b.category,b.fixed_in_version,b.summary,t.description,t.steps_to_reproduce,t.additional_information
		FROM mantis_bug_table AS b
		INNER JOIN mantis_bug_text_table AS t ON t.id = b.bug_text_id
		ORDER BY b.id DESC;				
		]] >	
		 </sql>
	 </bj:sqlt>
</target>
</project>

mantis2csv.ftl

• freemarker file

<#list bugs as bug>
$ {bug.id},$ {bug.summary},$ {bug.category},$ {bug.fixed_in_version}
</#list>