Retic EAI Server 0.3.2 Released - SQLTreeSource included.

[Alexander DEJANOVSKI]

I've released a new version of Retic with new components :

- Pipes : XPathPipe and FlatToXMLPipe
- Source : SQLTreeSource (permits to build complex XML
documents from several SQL requests).

The Designer is now fully working and supports all components
of the current Retic release.
It is available on sourceforge : http://sourceforge.net/projects/retic
================================================== ===========
WHAT IS RETIC ?
Retic is an EAI Server.
The aim is to permit applications to communicate, even if they
don't speak the same language (which means transport protocols
as well as data structures).
This is done by building adaptors. An adaptor is composed of :
- One source
- Several pipes (process data transformations)
- Several sinks (destination of data)
- Several loggers (using the logging module of python 2.3)

================================================== ===========
ABOUT THE SQLTreeSource COMPONENT:
Sometimes (well, might even be more than that) you need to get
more than a bunch of rows from a table translated to XML. You
need a whole structured XML document with a complex and deep
architecture. The SQLTreeSource is what you need then. It
permits to describe an XML tree, each node being the result of
an SQL statement. Here's an example :

<source name="SQLTreeSource" type="SQLTreeSource" dbType="odbc"
dsn="localhost_mysql" user="root" password="" rootTag="XPWeb"
encoding="ISO-8859-1" msgSize="0" polls="1" pollPeriod="0">
<treeQuery>
<query SQL="select * from iterations" recTag="iteration">
<query SQL="select * from stories"parentLink="id"
childLink="iteration_id" recTag="story">
<query SQL="select * from tasks"
parentLink="id,iteration_id" childLink="story_id,iteration_id"recTag="task"/>
</query>
</query>
</treeQuery>
</source>
Here is the XML output of the source described above :

<?xml version="1.0" encoding="ISO-8859-1"?>
<XPWeb>
<iteration>
<id>1</id>
<project_id>1</project_id>
<working_days_name>ite_1</working_days_name>
<load_factor>1.6</load_factor>
<name>07/04/2003</name>
<description>Mise en recette Valo
<date>2003-04-30</date>
<story>
<id>3</id>
<iteration_id>1</iteration_id>
<project_id>1</project_id>
<name>Evironnement de tests unitaires</name>
<description>Création d'un environnement de tests
unitaires et de non-régression.</description>
<validation_criteria></validation_criteria>
<priority>8</priority>
<risk>3</risk>
<weight>2</weight>
<task>
<id>15</id>
<story_id>3</story_id>
<iteration_id>1</iteration_id>
<project_id>1</project_id>
<responsible_team_member_id>5</responsible_team_member_id>
<working_days_name>ite_1</working_days_name>
<name>Scripts de création des données
(TERA)</name>
<description>Ecriture des scripts de
création de base</description>
<start_date>2003-04-10</start_date>
<weight>3</weight>
<done>4.8</done>
<todo>0</todo>
<end_date>2003-04-10</end_date>
</task>
<task>
<id>16</id>
<story_id>3</story_id>
<iteration_id>1</iteration_id>
<project_id>1</project_id>
<responsible_team_member_id>5</responsible_team_member_id>
<working_days_name>ite_1</working_days_name>
<name>Environnement $U - Session et
Uprocs</name>
<description>Création des sessions et
uprocs propres aux tests.</description>
<start_date>2003-04-16</start_date>
<weight>1</weight>
<done>0</done>
<todo>1</todo>
<end_date>2003-04-16</end_date>
</task>
</story>
<story>
......
......
......
</story>
</iteration>
</XPWeb>

This is an extraction of my XPWeb database (THE Extreme
Programming management tool - http://xpweb.sourceforge.net ).
Three tables are accessed here : iterations, stories and tasks.
Iterations may have several stories, which may have several
tasks. What I wanted here is to extract all my iterations with
their stories and tasks into a single XML document.

<treeQuery>
<query SQL="select * from iterations" recTag="iteration">

This permits to get all iterations at the second level (the
first being the root one whose name is defined in the rootTag
attribute of the source component) of the document. Each
iteration will be written to XML, embraced by an <iteration> tag
Providing a new subquery this way :

<treeQuery>
<query SQL="select * from iterations" recTag="iteration">
<query SQL="select * from stories" parentLink="id"
childLink="iteration_id" recTag="story"/>
</query>

Permits to define a new sublevel under iteration, writing the
stories inside <story> tags.
parentLink and childLink permit to assign the stories to their
iteration (we don't want all stories to be repeated under each
iteration). Here, we make a link between the fields :
iterations.id and stories.iteration_id
At execution time, the SQL statement is modified to include
the link.
(for example : select * from stories where iteration_id = 4)
Now, let's add a subquery to stories :

<treeQuery>
<query SQL="select * from iterations" recTag="iteration">
<query SQL="select * from stories" parentLink="id"
childLink="iteration_id" recTag="story">
<query SQL="select * from tasks" parentLink="id"
childLink="story_id" recTag="task"/>
</query>
</query>

Here, we've added a new sublevel, which will come under
stories. It will write all tasks for each stories (with a link
between stories.id and tasks.story_id).
Simple, isn't it ?
Note : Although it is not said yet in the doc, supported databases are :
mysql, odbc (not mxOdbc), sybase, dcoracle2, cx_oracle (dbtype in the
config file must take one of those values spelled exactly this way). Of
course, they'll only work if corresponding db modules are correctly
installed. I've tested mysql and odbc but couldn't yet test the others
(feedback is welcome !!).
Upcoming components are :

- xmlBlasterSource (already developped - will be shipped in the next
release this week)
- JabberSink (already developped - will be shipped in the next release this
week)
- httpSink
- SoapSource
- SoapSink
- xmlBlasterSink
- MQSeries source and sink (if I manage in compiling it with python 2.3 and
MinGw32 under win32 - help appreciated - Compile is ok but I get an error
at import : blablabla : Memory could not be read)
- Support for other popular databases (DB/2, PostgreSQL, etc...)

================================================== ===========
Have fun !!

PS : One last thing ==> Tell me if you're interested by Retic (good formy
morale).