Annotation of db/prgsrc/mkdb.pl, revision 1.23
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:
1.2 boris 7: =head1 SYNOPSIS
8:
9: mkdb.pl
10:
11:
1.1 boris 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:
1.23 ! roma7 26: =head1 $Id: mkdb.pl,v 1.22 2009-05-24 10:10:17 roma7 Exp $
1.1 boris 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:
1.15 roma7 40: if (scalar(grep(/^$TabName$/i, @tbl_list))) {
1.1 boris 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";
1.20 roma7 59: if ($dbh->get_info( 18 )=~/^(5|(4.1))/) {$dbh->do("SET NAMES 'koi8r'");}
1.17 boris 60: @tbl_list = $dbh->tables();
1.1 boris 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),
1.8 boris 68: Number SMALLINT UNSIGNED NOT NULL,
1.1 boris 69: KEY NumberKey (Number),
1.19 roma7 70: `Type` CHAR(5) NOT NULL DEFAULT 'þ',
1.12 roma7 71: KEY TypeKey (Type),
1.1 boris 72: Question TEXT,
1.7 boris 73: Answer TEXT,
1.16 roma7 74: PassCriteria TEXT,
1.7 boris 75: Authors TEXT,
76: Sources TEXT,
1.4 boris 77: Comments TEXT,
1.11 boris 78: Rating TEXT,
1.13 boris 79: Topic TEXT,
1.23 ! roma7 80: ProcessedBySearch INT,
! 81: FULLTExt key fullindex(Question, Answer, PassCriteria, Comments)
1.19 roma7 82: ) TYPE=MyISAM"
83: . (($dbh->get_info( 18 )=~/^(5|(4.1))/)?' DEFAULT CHARSET=koi8r':'')
84: )
1.1 boris 85: or die "Can't create Questions table: $!\n";
86:
87: &CheckTable("Tournaments");
88:
89: $dbh->do("CREATE TABLE Tournaments (
90: Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
91: KEY IdKey (Id),
92: ParentId INT UNSIGNED NOT NULL,
93: KEY ParentIdKey (ParentId),
94: Title TINYTEXT NOT NULL,
1.10 boris 95: Number SMALLINT UNSIGNED,
1.1 boris 96: QuestionsNum INT UNSIGNED DEFAULT 0,
1.6 boris 97: Type ENUM('ç','ô','þ'),
1.1 boris 98: Copyright TEXT,
99: Info TEXT,
100: URL TINYTEXT,
101: FileName CHAR(25),
1.7 boris 102: Editors TEXT,
103: EnteredBy TEXT,
1.1 boris 104: PlayedAt DATE,
1.22 roma7 105: PlayedAt2 DATE,
106: KandId INT,
1.1 boris 107: CreatedAt DATE NOT NULL
1.19 roma7 108: ) TYPE=MyISAM"
109: . (($dbh->get_info( 18 )=~/^(5|(4.1))/)?' DEFAULT CHARSET=koi8r':'')
110:
111: )
1.1 boris 112: or die "Can't create Tournaments table: $!\n";
113:
114: # CREATE INDEX ParentInd ON Tournaments (ParentId)
115: # CREATE UNIQUE INDEX IdInd ON Tournaments (Id)
116:
117: $dbh->disconnect;
118: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>