#!/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.2 2000/10/17 01:31:27 boris Exp $

=cut


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

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

	if (scalar(grep(/^$TabName$/, @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";

	@tbl_list = $dbh->func( '_ListTables' );

 	&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 		TINYINT UNSIGNED NOT NULL,
			KEY NumberKey (Number),
		# Currently there are 2 types of questions: 'Chto? Gde? Kogda?'
		# and 'Brain ring'.
		Type 			ENUM('þ','â') NOT NULL,
		Question 	TEXT,
		Answer 		TINYTEXT,
		Authors 		TINYTEXT,
		Sources 		TINYTEXT,
		Comments 	TEXT
	)")
	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,
		QuestionsNum INT UNSIGNED DEFAULT 0,
		Type 			ENUM('ç','ô','þ'),
		MetaId 		INT UNSIGNED,
		Copyright 	TEXT,
		Info 			TEXT,
		URL 			TINYTEXT,
		FileName 	CHAR(25),
		PlayedAt 	DATE,
		CreatedAt 	DATE NOT NULL
	)") 
	or die "Can't create Tournaments table: $!\n";

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