Return to mkdb.pl CVS log

Up to [Local Repository] / db / prgsrc

added field

#!/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.11 2001/10/14 17:48:12 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 	       SMALLINT UNSIGNED NOT NULL,
			KEY NumberKey (Number),
		Type 		TEXT NOT NULL,
		Question 	TEXT,
		Answer 		TEXT,
		Authors 	TEXT,
		Sources 	TEXT,
		Comments 	TEXT,
                Rating          TEXT,
                ProcessedBySearch  INT
	)")
	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,
		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;
}