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.
