From df4b7427f6d9fb59bca2d254f13a2fbe2f86ab51 Mon Sep 17 00:00:00 2001
From: Benjamin Franzke <benjaminfranzke@googlemail.com>
Date: Sat, 26 Jan 2013 18:49:51 +0100
Subject: begin oracle conversion toolset

---
 oracle-conversion/export-kapv.sql    | 17 +++++++++++++++++
 oracle-conversion/export-lessons.sql |  8 ++++++++
 oracle-conversion/fixml.sh           |  7 +++++++
 oracle-conversion/grab.sh            |  7 +++++++
 oracle-conversion/rowset-to-sql.xsl  | 33 +++++++++++++++++++++++++++++++++
 5 files changed, 72 insertions(+)
 create mode 100644 oracle-conversion/export-kapv.sql
 create mode 100644 oracle-conversion/export-lessons.sql
 create mode 100755 oracle-conversion/fixml.sh
 create mode 100644 oracle-conversion/grab.sh
 create mode 100644 oracle-conversion/rowset-to-sql.xsl

diff --git a/oracle-conversion/export-kapv.sql b/oracle-conversion/export-kapv.sql
new file mode 100644
index 0000000..243936a
--- /dev/null
+++ b/oracle-conversion/export-kapv.sql
@@ -0,0 +1,17 @@
+set pages 0
+set long 9999999
+set head off
+
+spool /tmp/export-kapv.xml
+select dbms_xmlgen.getxml('select * from Adressen') from dual;
+select dbms_xmlgen.getxml('select * from Kunden') from dual;
+select dbms_xmlgen.getxml('select * from Produktlager') from dual;
+select dbms_xmlgen.getxml('select * from Kunden_Profil') from dual;
+select dbms_xmlgen.getxml('select * from Auftrag') from dual;
+select dbms_xmlgen.getxml('select * from Rechnungsdaten') from dual;
+select dbms_xmlgen.getxml('select * from Auftrag_hat_Positionen') from dual;
+select dbms_xmlgen.getxml('select * from Kunde_hat_Adresse') from dual;
+select dbms_xmlgen.getxml('select * from Vorlieben') from dual;
+select dbms_xmlgen.getxml('select * from Kunde_hat_Vorlieben') from dual;
+select dbms_xmlgen.getxml('select * from Zu_Vorlieben_gehoeren_Produkte') from dual;
+spool off
diff --git a/oracle-conversion/export-lessons.sql b/oracle-conversion/export-lessons.sql
new file mode 100644
index 0000000..934f08a
--- /dev/null
+++ b/oracle-conversion/export-lessons.sql
@@ -0,0 +1,8 @@
+set pages 0
+set long 9999999
+set head off
+
+spool /tmp/export-lesson.xml
+select dbms_xmlgen.getxml('select * from lessons') from dual;
+select dbms_xmlgen.getxml('select * from lesson_order') from dual;
+spool off
diff --git a/oracle-conversion/fixml.sh b/oracle-conversion/fixml.sh
new file mode 100755
index 0000000..75bbcf8
--- /dev/null
+++ b/oracle-conversion/fixml.sh
@@ -0,0 +1,7 @@
+sed  -e "s/ *$//" -e "s/^ *//" -e "/^$/d" $1 | \
+	sed 's/^SQL.*from \([^ ]*\)'"'"'.*/<ROWSET NAME="\1">/' | \
+	sed -e "/<ROWSET>/d" -e "/^SQL/d" -e '/^<?xml/d' | \
+	sed -e '1i <DATABASE>' -e '$a </DATABASE>' | \
+	sed  -e :a -e 'N;s/\n//g' -e ba | \
+	sed 's: *</:</:g' | \
+	xmllint --format -
diff --git a/oracle-conversion/grab.sh b/oracle-conversion/grab.sh
new file mode 100644
index 0000000..e54ba24
--- /dev/null
+++ b/oracle-conversion/grab.sh
@@ -0,0 +1,7 @@
+#!/bin/sh
+
+sqlplus nils/secret < export-lessons.sql
+sqlplus kapv/secret < export-kapv.sql
+
+./fixml.sh /tmp/export-lesson.xml > lesson.xml
+./fixml.sh /tmp/export-kapv.xml | xsltproc rowset-to-sql.xsl - > kapv.sql
diff --git a/oracle-conversion/rowset-to-sql.xsl b/oracle-conversion/rowset-to-sql.xsl
new file mode 100644
index 0000000..7b2a90a
--- /dev/null
+++ b/oracle-conversion/rowset-to-sql.xsl
@@ -0,0 +1,33 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
+
+<xsl:output method="text"/>
+
+
+<xsl:template match="ROWSET">
+	<xsl:text>insert into </xsl:text>
+	<xsl:value-of select="@NAME"/>
+	<xsl:text> values</xsl:text>
+	<xsl:for-each select="ROW">
+		<xsl:text>&#10;&#9;(</xsl:text>
+		<xsl:for-each select="*">
+			<xsl:text>'</xsl:text>
+			<xsl:value-of select="."/>
+			<xsl:text>'</xsl:text>
+			<xsl:if test="position() != last()">
+				<xsl:text>,</xsl:text>
+			</xsl:if>
+		</xsl:for-each>
+		<xsl:text>)</xsl:text>
+		<xsl:if test="position() != last()">
+			<xsl:text>,</xsl:text>
+		</xsl:if>
+	</xsl:for-each>
+	<xsl:text>;&#10;</xsl:text>
+</xsl:template>
+
+<xsl:template match="/">
+	<xsl:apply-templates select="DATABASE/ROWSET" />
+</xsl:template>
+</xsl:stylesheet>
+
-- 
cgit