Sql transformation

Description

Sqlt is an ant task that allows to:

Sqlt task parameters


|Attribute  | Description           | Value                         | Required  |
|-----------|-----------------------|-------------------------------|-----------|
|template   | freemarker template   | <filename>.ftl                | yes       |
|tofile     | output file           | <filename>.<mime>             | 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>