Return to mkdb.pl CVS log

Up to [Local Repository] / db / prgsrc

Initial revision

    1: #!/usr/local/bin/perl -w
    2: 
    3: =head1 NAME
    4: 
    5: mkdb.pl - a script for creation of new database. 
    6: 
    7: =head1 DESCRIPTION
    8: 
    9: This script will create tables Questions and Tournaments
   10: in the B<chgk> databse. If the tables exist, it will ask user whether
   11: new tables should be created.
   12: 
   13: =head1 BUGS
   14: 
   15: The database, user and password are hardcoded. 
   16: 
   17: =head1 AUTHOR
   18: 
   19: Dmitry Rubinstein
   20: 
   21: =head1 $Id: mkdb.pl,v 1.1 2000/10/16 23:20:49 boris Exp $
   22: 
   23: =cut
   24: 
   25: 
   26: use DBI;
   27: use strict;
   28: my (@tbl_list, $dbh);
   29: 
   30: sub CheckTable
   31: {
   32: 	my ($TabName) = @_;
   33: 	my ($ans);
   34: 
   35: 	if (scalar(grep(/^$TabName$/, @tbl_list))) {
   36: 		print "Table $TabName exists. Do you want to delete it? ";
   37: 		$ans = <STDIN>;
   38: 		if ($ans =~ /[yY]/) {
   39: 			$dbh->do("DROP TABLE $TabName");
   40: 			print "deleted table $TabName\n";
   41: 		} else {
   42: 			exit;
   43: 		}
   44: 	}
   45: }
   46: 
   47: MAIN:
   48: {
   49: print "Before connecting to the DB\n";
   50: 
   51: 	$dbh = DBI->connect("DBI:mysql:chgk", "piataev", "")
   52: 		or die "Can't connect to DB chgk\n" . $dbh->errstr;
   53: print "Connected successfully\n";
   54: 
   55: 	@tbl_list = $dbh->func( '_ListTables' );
   56: 
   57:  	&CheckTable("Questions");
   58: 	$dbh->do("CREATE TABLE Questions (
   59: 		QuestionId 	INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   60: 			KEY QuestionIdKey (QuestionId),
   61: 		ParentId 	SMALLINT UNSIGNED NOT NULL,
   62: 			KEY ParentIdKey (ParentId),
   63: 		Number 		TINYINT UNSIGNED NOT NULL,
   64: 			KEY NumberKey (Number),
   65: 		# Currently there are 2 types of questions: 'Chto? Gde? Kogda?'
   66: 		# and 'Brain ring'.
   67: 		Type 			ENUM('þ','â') NOT NULL,
   68: 		Question 	TEXT,
   69: 		Answer 		TINYTEXT,
   70: 		Authors 		TINYTEXT,
   71: 		Sources 		TINYTEXT,
   72: 		Comments 	TEXT
   73: 	)")
   74: 	or die "Can't create Questions table: $!\n";
   75: 	
   76: 	&CheckTable("Tournaments");
   77: 
   78: 	$dbh->do("CREATE TABLE Tournaments (
   79: 		Id 			INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   80: 			KEY IdKey (Id),
   81: 		ParentId 	INT UNSIGNED NOT NULL,
   82: 			KEY ParentIdKey (ParentId),
   83: 		Title 		TINYTEXT NOT NULL,
   84: 		QuestionsNum INT UNSIGNED DEFAULT 0,
   85: 		Type 			ENUM('ç','ô','þ'),
   86: 		MetaId 		INT UNSIGNED,
   87: 		Copyright 	TEXT,
   88: 		Info 			TEXT,
   89: 		URL 			TINYTEXT,
   90: 		FileName 	CHAR(25),
   91: 		PlayedAt 	DATE,
   92: 		CreatedAt 	DATE NOT NULL
   93: 	)") 
   94: 	or die "Can't create Tournaments table: $!\n";
   95: 
   96: #	CREATE INDEX ParentInd ON Tournaments (ParentId)
   97: #	CREATE UNIQUE INDEX IdInd ON Tournaments (Id)
   98: 	
   99: 	$dbh->disconnect;
  100: }