From 0f3d98ef3f71825b5c3b586c440f14bbeeb81034 Mon Sep 17 00:00:00 2001
From: Dobrica Pavlinusic <dpavlin@rot13.org>
Date: Wed, 2 Dec 2009 00:37:15 +0100
Subject: [PATCH] dump data in csv and load.xml for BlackRay

---
 sql2blackray.pl | 75 +++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 75 insertions(+)
 create mode 100755 sql2blackray.pl

diff --git a/sql2blackray.pl b/sql2blackray.pl
new file mode 100755
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;
-- 
2.20.1