#!/usr/local/bin/perl -w

=head1 NAME

mkdb.pl - a script for creation of new database. 

=head1 SYNOPSIS

mkdb.pl


=head1 DESCRIPTION

This script will create tables Questions and Tournaments
in the B<chgk> databse. If the tables exist, it will ask user whether
new tables should be created.

=head1 BUGS

The database, user and password are hardcoded. 

=head1 AUTHOR

Dmitry Rubinstein

=head1 $Id: mkdb.pl,v 1.24 2009/12/12 16:55:21 roma7 Exp $

=cut


use DBI;
use strict;
my (@tbl_list, $dbh);

sub CheckTable
{
	my ($TabName) = @_;
	my ($ans);

	if (scalar(grep(/^$TabName$/i, @tbl_list))) {
		print "Table $TabName exists. Do you want to delete it? ";
		$ans = <STDIN>;
		if ($ans =~ /[yY]/) {
			$dbh->do("DROP TABLE $TabName");
			print "deleted table $TabName\n";
		} else {
			exit;
		}
	}
}

MAIN:
{
print "Before connecting to the DB\n";

	$dbh = DBI->connect("DBI:mysql:chgk", "piataev", "")
		or die "Can't connect to DB chgk\n" . $dbh->errstr;
print "Connected successfully\n";
if ($dbh->get_info( 18 )=~/^(5|(4.1))/)  {$dbh->do("SET NAMES 'koi8r'");}
	@tbl_list = $dbh->tables();

 	&CheckTable("Questions");
	$dbh->do("CREATE TABLE Questions (
		QuestionId 	INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
			KEY QuestionIdKey (QuestionId),
		ParentId 	SMALLINT UNSIGNED NOT NULL,
			KEY ParentIdKey (ParentId),
		Number 	       SMALLINT UNSIGNED NOT NULL,
			KEY NumberKey (Number),
		`Type` 		CHAR(5) NOT NULL DEFAULT 'þ',
		        KEY TypeKey (Type),
		Question 	TEXT,
		Answer 		TEXT,
		PassCriteria	TEXT,
		Authors 	TEXT,
		Sources 	TEXT,
		Comments 	TEXT,
                Rating          TEXT,
                Topic           TEXT,
                ProcessedBySearch  INT
	) TYPE=MyISAM"
	. (($dbh->get_info( 18 )=~/^(5|(4.1))/)?' DEFAULT CHARSET=koi8r':'')
)
	or die "Can't create Questions table: $!\n";
	
	&CheckTable("Tournaments");

	$dbh->do("CREATE TABLE Tournaments (
		Id 			INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
			KEY IdKey (Id),
		ParentId 	INT UNSIGNED NOT NULL,
			KEY ParentIdKey (ParentId),
		Title 		TINYTEXT NOT NULL,
                Number    SMALLINT UNSIGNED,
		QuestionsNum INT UNSIGNED DEFAULT 0,
		Type 		ENUM('ç','ô','þ'),
		Copyright 	TEXT,
		Info 			TEXT,
		URL 			TINYTEXT,
		FileName 	CHAR(25),
                Editors         TEXT,
                EnteredBy       TEXT,
		PlayedAt 	DATE,
		PlayedAt2       DATE,		
		KandId          INT,		
		CreatedAt 	DATE NOT NULL
	) TYPE=MyISAM"
	. (($dbh->get_info( 18 )=~/^(5|(4.1))/)?' DEFAULT CHARSET=koi8r':'')

) 
	or die "Can't create Tournaments table: $!\n";

#	CREATE INDEX ParentInd ON Tournaments (ParentId)
#	CREATE UNIQUE INDEX IdInd ON Tournaments (Id)
	
	$dbh->disconnect;
}