#!/bin/sh

# The contents of this file are subject to the MonetDB Public License
# Version 1.1 (the "License"); you may not use this file except in
# compliance with the License. You may obtain a copy of the License at
# http://www.monetdb.org/Legal/MonetDBLicense
#
# Software distributed under the License is distributed on an "AS IS"
# basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
# License for the specific language governing rights and limitations
# under the License.
#
# The Original Code is the MonetDB Database System.
#
# The Initial Developer of the Original Code is CWI.
# Portions created by CWI are Copyright (C) 1997-July 2008 CWI.
# Copyright August 2008-2015 MonetDB B.V.
# All Rights Reserved.

DISCLAIMER='
-- NOTE:
-- "MySQL-2-MonetDB.sh" is a very first very simple approach to
-- automatically convert a MySQL SQL dump into a MonetDB SQL dump. 
-- There is no documentation other than the code itself.
-- The script comes without any ambitions, let alone guarantees, of
-- completeness or correctness.  Some MySQL-specific features that
-- diverge from or go beyond the pure SQL standard are not taken
-- care of, yet, other than being recognized and dropped/ignored.
-- So far, the script has been successfully tested and used with a single
-- MySQL dump, only. Any feedback is more than welcome. Please share your
-- feedback with users-list@monetdb.org .
-- Please also be aware of the fact that loading a large DB dump via mclient
-- is not the fastest/most efficient way to load large amounts of data into
-- MonetDB.  Alternatively, consider dumping only the DDL from MySQL as SQL,
-- while dumping the data into CSV files.  Then use (a variation of) this
-- script to turn the MySQL DDL dump into a MonetDB DDL dump, use that to
-- create your tables, etc.  in MonetDB, and then load your data from the CSV
-- files using MonetDB COPY INTO bulk data load; cf.,
-- https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto
-- Thanks & Enjoy,
-- Stefan.Manegold@CWI.nl (http://www.cwi.nl/~manegold/)
'

if [ ! "$2" ] ; then
	echo
	echo "Usage:  MySQL-2-MonetDB.sh <input-file> <output-file>"
	echo
	echo "<input-file>:  given MySQL SQL dump"
	echo "<output-file>: to be created MonetDB SQL dump"
	echo
	echo "$DISCLAIMER"
	echo
	exit 1
fi >&2

(
echo '-- Created by "MySQL-2-MonetDB.sh".'
echo "$DISCLAIMER"
cat $1 \
| tr -d '\r' \
| grep -v '\\0' \
| egrep -v \
'^(SET (SQL_MODE=|time_zone = )".+";|  (FULLTEXT )?KEY `.+` \(.+\),?|DROP TABLE IF EXISTS .*;|(UN)?LOCK TABLES.*;)$' \
| sed \
-e 's:`:":g' \
-e 's:CREATE TABLE IF NOT EXISTS:CREATE TABLE:g' \
-e 's: \(medium\)\?int([0-2])\([ ,]\): tinyint\2:g' \
-e 's: \(medium\)\?int([34])\([ ,]\): smallint\2:g' \
-e 's: \(medium\)\?int([5-9])\([ ,]\): integer\2:g' \
-e 's: \(medium\)\?int(1[0-8])\([ ,]\): bigint\2:g' \
-e 's: \(medium\)\?int(\(19\|[2-9][0-9]\|[0-9][0-9][0-9]\+\))\([ ,]\): varchar(\2)\3:g' \
-e 's:\([ylg]int\)([0-9]\+)\([ ,]\):\1\2:g' \
-e 's: year(4)\([ ,]\): smallint\1:g' \
-e 's: datetime\([ ,]\): timestamp\1:g' \
-e 's: \(medium\|long\)text\([ ,]\): string\2:g' \
-e 's: longblob\([ ,]\): clob\1:g' \
-e 's: unsigned\([ ,]\):\1:g' \
-e "s: \(set\|enum\)\(('[^)]*')\)\(.*\)$: string\3 -- \1\2:g" \
-e "s:\( COMMENT '[^']*'\)\(.*\)$:\2 --\1:g" \
-e 's: UNIQUE KEY "\([^"]\+\)" : UNIQUE :g' \
-e 's: ON UPDATE CURRENT_TIMESTAMP,$:,:g' \
-e 's:)\( \+\(ENGINE=[^ ;]\+\|DEFAULT\|CHARSET=[^ ;]\+\|AUTO_INCREMENT=[0-9]\+\)\)* *;$:);:g' \
-e 's:^\(INSERT INTO .* VALUES\) (:\1\n(:g' \
-e 's:),(:),\n(:g' \
-e "s: '0000-00-00',: '1970-01-01',:g" \
-e "s: '\([12][0-9][0-9][0-9]\)-00-00',: '\1-01-01',:g" \
-e "s: '\([12][0-9][0-9][0-9]-[01][0-9]\)-00',: '\1-01',:g" \
-e 's:Subsidie/opdrachtgever:Subsidie_opdrachtgever:g' \
| awk '
/^CREATE TABLE ".+" \($/ {x="";}
/^  [^ ].+ .+$/ {printf "%s%s",x,gensub(",$","",1,$0); x=",\n"}
/^\);$/ {printf "\n";}
/^INSERT INTO .+ VALUES$/ {n=0; s=""; t=$3;}
/^\(.+\)[,;]$/ {n++; s=s gensub("^\\((.+)\\)[,;]$","\\1",1,$0) "\n";}
/^\(.+\);$/ {printf "COPY %d RECORDS INTO %s FROM stdin USING DELIMITERS '\'','\'','\''\\n'\'','\''\\'\'\'';\n%s",n,t,s;}
!/^(  [^ ].+ .+|INSERT INTO .+ VALUES|\(.+\)[,;])$/ {print $0;}
' \
) \
> $2 \
|| exit 2

c="`grep 'CHARSET=' $1 | sed 's:^.*CHARSET=\([^ ;]\+\)[ ;].*$:\1:' | sort -u`"
echo
echo "load dump into MonetDB via"
echo
echo "mclient --encoding=$c $2"
echo
echo "$DISCLAIMER"
echo
