#!/usr/bin/perl
#
# tr2sql - t(ab) r(eturn) to (insert) sql (statements)
# A simple tool to convert tab-return formated data to sql INSERT
# statements.
#
# Version: 0.0.6
#
#    Copyright (C) 1999-2007 by Gregor Retti
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 1, or (at your option)
#    any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program; if not, write to the Free Software
#    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#
# Usage:
#	tr2sql.pl <tab-return-file>
#
# Set the enviroment variable TR2SQL to a number to insert a COMMIT
# statement every TR2SQL record.
#
# The sql formated output will be written to STDOUT.
#
# Format of tab-return data
#
# The tab-return-file must consist of two parts: header and body.
#
# Header:
# The header holds a description of the table and the columns.
# It may optionally contain a comment. Comments start with
# -- followed by a white space character (space, tab).
# 
# The table and column information must be written as follows:
#
# tablename
# columnname1 <tab> operation
# [columnname2 <tab> operation]
# and so on ...
#
# The order of columnnames must be the same as the data is ordered
#
# operation may be (operations are case sensitive):
# Q for "quote": data for that column will be quoted
# N for "do nothing": leave data as is, useful for numbers
# <function_start>VALUE<function_end>: VALUE will be replaced with the
# data
# <constant>: a constant expression will be used (instead of data)
# <constants> should be used at the end of the columns, otherwise empty
# rows must be in the tab-return-data.
#
# To complete ignore one columne insert IGNORE for the columnname and
# IGNORE for the operation
#
# Body:
#
# The body consist of tab-return data. It must be in the order described
# in the header.
#
# Header and body are separated by an empty line. (Empty means empty!).
# Their may be an additional empty line between a comment and the <tablename>.
#
# If tr2sql.pl runs into troubles it will produce garbage, terminate or
# not work in another way. There is no error checking implemented, so if
# you are lazy while formating the tab-return-file, you will regret it.
#
# v 0.0.2
# only cosmetic changes
#
# v 0.0.3
# check environment variable TR2SQL for COMMIT counter and insert
# a commit line every COMMIT counter line
# change vars and run under strict
#
# v 0.0.4
# change comments to --
# this is SQL92 and works w/ oracle and postgreSQL
#
# v 0.0.5
# change value to NULL for empty values of numbers
#
# v 0.0.6
# add a backward compatibility condition for REM
# add IGNORE IGNORE option

use strict;

require "ctime.pl";
my $version = "tr2sql -- 0.0.6 (c) 1999-2007 by Gregor Retti";
my $timestamp = &ctime(time);
print "-- Created with $version -- $timestamp\n";
print "-- Attention: COMMIT every $ENV{'TR2SQL'} records!\n" if $ENV{'TR2SQL'};

my $in_header = 1;

my ($data, @data, $tname, $insert, $col, @cols, @oper, $i);

my $cnt = 0;

while (<>) {
	chop;
  	if ((/^--[\s]*/)  && ($in_header)) {
  		print "$_\n";
  		next;
  		}
  	if ((/^REM[\s]*/)  && ($in_header)) {
  		print "$_\n";
  		next;
  		}
	$data = $_;
	unless ($data) {
		next unless $tname;
		$in_header = 0;
		&make_insert;
		next;
		}
	@data = split('\t',$data);
	if ($in_header) { 
		$tname = $data[0] unless $data[1];
		if ($data[1]) {
			$cols[$#cols+1] = $data[0];
			$oper[$#oper+1] = $data[1];
			}
		}
	else {
		print "\n$insert";
		for ($i=0;$i<=$#cols;$i++) {
			next if $oper[$i] eq 'IGNORE';
			&print_data($i);
			print "," unless $i == $#cols;
			}
		print ");\n";
		if ($ENV{'TR2SQL'}) {
			$cnt++;
			print "\nCOMMIT;\n\n" if $cnt % $ENV{'TR2SQL'} == 0;
			};
		}
	}
print "\nCOMMIT;\n\n";
	
sub make_insert {
	$insert = "INSERT INTO $tname (";
	foreach $col (@cols) {
		next if $col eq 'IGNORE';
		$insert .= $col;
		$insert .= "," unless $col eq $cols[$#cols];
		}
	$insert .= ")\n\tVALUES (";
	}

sub print_data {
	my($pos) = shift;
	my($val) = $data[$pos];
	my($format) = $oper[$pos];
	if ($format =~ /VALUE/) {
		$format =~ s/VALUE/$val/;
		$val = $format;
		}
	elsif ($format eq "Q") {
		$val =~ s/\'/''/g;
		$val = "'".$val."'";
		}
	elsif ($format ne "N") {
		$val = $format;
		}
	elsif ($format eq "N") {
		$val = 'NULL' unless $val || $val eq '0';
		}
	print $val;
	}