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 ? ============================================================= Have fun !!