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: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>