Annotation of db/prgsrc/mkdb.pl, revision 1.1
1.1 ! boris 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:$
! 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: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>