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