dump data in csv and load.xml for BlackRay

diff --git a/sql2blackray.pl b/sql2blackray.pl
new file mode 100755 (executable)
index 0000000..b1c6aa5
--- /dev/null
+++ b/sql2blackray.pl
@@ -0,0 +1,75 @@
+#!/usr/bin/perl
+
+use warnings;
+use strict;
+
+use DBI;
+use Data::Dump qw(dump);
+
+our $dsn      = 'DBI:mysql:dbname=';
+our $database = '';
+our $user     = '';
+our $passwd   = '';
+
+require 'config.pl' if -e 'config.pl';
+
+my $table = 'borrowers';
+my $schema = $database;
+
+my $dbh = DBI->connect($dsn . $database, $user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
+
+open(my $load, '>', 'load.xml');
+
+sub dump_table {
+       my $table = shift;
+
+my $sql = qq|
+       select *
+       from $table
+|;
+
+my $sth = $dbh->prepare( $sql );
+$sth->execute();
+
+warn "got ", $sth->rows, " rows\n";
+
+my @columns = @{ $sth->{NAME} };
+
+warn "columns ", dump @columns;
+
+print $load qq|<table-def name="$table" schema="$schema">\n|;
+print $load qq| <column-def name="$_" tyle="string" tokenizing=" "
+                       searchable="yes" compress="no" wildcards="yes" />
+| foreach @columns;
+print $load qq|<table-def>|;
+
+my $file = "$table.csv";
+
+print $load qq|<load-data table="$table" schema="$schema" file="$file">|;
+foreach my $i ( 0 .. $#columns ) {
+       print $load qq|
+       <column name="$columns[$i]" csv-column="$i">
+        <function type="trim" />
+    </column>
+       |;
+}
+print $load qq|</load-data>|;
+
+open(my $fh, '>', $file) || die "$file: $!";
+
+while (my $row = $sth->fetchrow_arrayref) {
+       print $fh join(',', map { ! defined $_ ? '' : m/^\d+$/ ? $_ : "\"$_\"" } @$row), "\n";
+}
+
+close($fh);
+
+warn "$file ", -s $file, " bytes\n";
+
+}
+
+print $load qq|<load-script>\n|;
+dump_table 'borrowers';
+dump_table 'borrower_attributes';
+print $load qq|</load-script>\n|;
+
+#$dbh->commit;