#!/usr/bin/perl -w

use strict;

print <<MYSQL;
CREATE DATABASE IF NOT EXISTS chess;
USE chess;
DROP TABLE IF EXISTS eco_class;
# create table statement
CREATE TABLE eco_class (
	eco_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	eco CHAR(3) NOT NULL,
	opening VARCHAR(50) NOT NULL,
	variation VARCHAR(50),
	moves VARCHAR(200) NOT NULL,
	KEY eco(eco), KEY opening(opening),
	KEY variation (variation), KEY moves(moves));
# create the header for the insert statement
INSERT INTO eco_class VALUES 
MYSQL

my @line;	# input fields
my $should_print =1;	# flag for first line

with_while(); # comment to test the alternative
#with_map();  # uncomment to test the alternative 
print ";\n";

sub with_while {
	while (<DATA>) {
		chomp;			# removes trailing newline
		next if /^\s*$/;	# skips blank lines
		# skips the first line
		print ",\n" if ($should_print? --$should_print : 1); 
		@line = split /;/;	# gets individual records
		print "(NULL, ", quotrim($line[0]), 
			", ", quotrim($line[1]), ", "; 
		# the third field may be missing
		print scalar @line < 4 ? 
			"NULL, " . quotrim($line[2]) 
			: quotrim($line[2]) . ", " . quotrim($line[3]), ")";
		}
}

sub with_map {
	print join ",\n" , grep { defined $_ } map { 
		chomp;			# removes trailing newline
		if (/^\s*$/ ) {		# skips blank lines
			$_ = undef;
		}
		else {
			@line = split /;/;	# gets individual records
			$_ =  "(NULL, " 
			  . quotrim($line[0]) 
			  .  ", " . quotrim($line[1]) 
			  . ", ";
			$_ .= scalar @line < 4 ? 
			   "NULL, " . quotrim($line[2]) 
			   : quotrim($line[2]) . ", " 
			     . quotrim($line[3]), ")";
			$_ .= ")";
		}
	} <DATA>;
}

sub quotrim { # quote and trim :->
	$_[0] =~ s/\"/\\"/g;
   	$_[0] =~ s/^\s+//;
   	$_[0] =~ s/\s+$//;
	return '"' . $_[0] . '"';
}

#sample data follows
__DATA__

A00;Polish (Sokolsky) opening;1.b4
A00;Polish;Tuebingen Variation ;1.b4 Nh6
A00;Polish;Outflank Variation ;1.b4 c6
A00;Benko's Opening;1.g3
A00;Lasker simul special;1.g3 h5
A00;Benko's Opening;reversed Alekhine;1.g3 e5 2.Nf3
A00;Grob's attack;1.g4
A00;Grob;Spike attack;1.g4 d5 2.Bg2 c6 3.g5
A00;Grob;Fritz gambit;1.g4 d5 2.Bg2 Bxg4 3.c4
A00;Grob;Romford counter-gambit;1.g4 d5 2.Bg2 Bxg4 3.c4 d4
A00;Clemenz (Mead's, Basman's or de Klerk's) Opening;1.h3
A00;Global Opening;1.h3 e5 2.a3
A00;Amar (Paris) Opening;1.Nh3
A00;Amar gambit;1.Nh3 d5 2.g3 e5 3.f4 Bxh3 4.Bxh3 exf4