Hi all, back with some results....
We have been able to stream the data from mysql into a string representation of the expected CSV rewriting the functions like this:
declare function j:csv-export($table as xs:string) { let $connection := sql:connect(conf:get("jdbc.url"), conf:get("jdbc.user"), conf:get("jdbc.pass")) let $tmp-file := string-join(('"id","name","timestamp","value"', sql:execute($connection, "SELECT * FROM " || $table || $where) ! (out:nl() || j:to-cvs-row(.)))) return ($tmp-file, sql:close($connection)) };
declare function j:to-cvs-row($sql-result as node()) { $sql-result/sql:column[lower-case(@name)="id"]/string() || conf:get("csv.sep") || '"' || $sql-result/sql:column[lower-case(@name)="name"]/string() || '"' || conf:get("csv.sep") || '"' || $sql-result/sql:column[lower-case(@name)="timestamp"]/string() || '"' || conf:get("csv.sep") || '"' || $sql-result/sql:column[lower-case(@name)="value"]/string() || '"' };
Now, since we have to export actually three or four tables from the rdbms into a zip archive, the only bottleneck that remains is that still the whole string for each csv has to be kept in memory before zipping it to the archive together with some small metadata.
We use something like the following code:
let $metadata-entry := <archive:entry last-modified='{current-dateTime()}'compression-level='8'>metadata.json</archive:entry> let $table-entry := $tables ! <archive:entry last-modified='{current-dateTime()}' compression-level='8'>{ . || ".csv"}</archive:entry> let $table-data := $tables ! ji:csv-export(.) return archive:create(($metadata-entry, $table-entries), (serialize($metadata), $table-data)))
Are there suggestions to keep the very effective "streaming behaviour" also while adding the entries to the zip archive? Thanks a lot! Marco.
On 11/11/2016 16:11, Christian Grün wrote:
Thanks! This looks like a blocker:
<csv>{ $sql-results ! <record><id>{./sql:column[lower-case(@name)="id"]/string()}</id> <name>{./sql:column[lower-case(@name)="name"]/string()}</name> <timestamps>{./sql:column[lower-case(@name)="timestamp"]/string()}</timestamps> <value>{./sql:column[lower-case(@name)="value"]/string()}</value> </record> } </csv> };
As you are wrapping a root element around all results, everything needs to be cached in main memory and thus prevents streaming.