|One message to multiple SQL actions.|
So are you back to having to write custom code? No. With OpenGroupware Coils 0.1.48 OIE's SQL related actions will support operation filtering. With operation filtering each row element in a result set can be tagged with an operation attribute indicating what SQL related actions it is a candidate for; so sqlInsertAction, for example, will only process tagged rows whose tag is "insert". Utilizing this method of tagging a single transformation with an XSLT stylesheet can produce a single message that can subsequently be processed by multiple actions - each of which will only processes the intended rows.
This change will not break existing work-flows as untagged rows are treated as if they are tagged with "any", all untagged rows will be processed by every action they pass through.
But if tagged the sqlUpdateAction will only process row elements with an operation attribute of "update", sqlInsertAction will only process row elements tagged as "insert", and sqlDeleteAction will only process row elements tagged as "delete". Row elements may also be explicitly tagged for operation "any" as well; doing so will yield the same behavior as if the row was not tagged with an operation.
Stanza 000060The sqlUpsertAction is a special case. Since upsert is actually a hybrid operation the tagging doesn't make sense in that context and the operation attributes are ignored by sqlUpsertAction.
debug:logic command is "action::sql-insert"
info:Table is "workorder"
info:3 records inserted to SQL connection
debug:logic command is "action::sql-update"
error:Update target is table "workorder"
1876 records processed.
Text 1: What a process log might look like when using operations filters; in this process both the sqlInsertAction in stanza 000060 and the sqlUpdateAction in stanza 000065 processed the same message as input.
Using the format classes in conjunction with the power of OIE's XSLT extentions such as lookup tables as well as operation filtering it is possibly to efficiently perform almost any to-RDBMS type ETL work-flow.