Return to mkdb.pl CVS log

Up to [Local Repository] / db / prgsrc

Fulltext undex is added

    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.23 2009/12/12 13:15:08 roma7 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$/i, @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: if ($dbh->get_info( 18 )=~/^(5|(4.1))/)  {$dbh->do("SET NAMES 'koi8r'");}
   60: 	@tbl_list = $dbh->tables();
   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 	       SMALLINT UNSIGNED NOT NULL,
   69: 			KEY NumberKey (Number),
   70: 		`Type` 		CHAR(5) NOT NULL DEFAULT 'þ',
   71: 		        KEY TypeKey (Type),
   72: 		Question 	TEXT,
   73: 		Answer 		TEXT,
   74: 		PassCriteria	TEXT,
   75: 		Authors 	TEXT,
   76: 		Sources 	TEXT,
   77: 		Comments 	TEXT,
   78:                 Rating          TEXT,
   79:                 Topic           TEXT,
   80:                 ProcessedBySearch  INT,
   81: 		FULLTExt key fullindex(Question, Answer, PassCriteria, Comments)
   82: 	) TYPE=MyISAM"
   83: 	. (($dbh->get_info( 18 )=~/^(5|(4.1))/)?' DEFAULT CHARSET=koi8r':'')
   84: )
   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,
   95:                 Number    SMALLINT UNSIGNED,
   96: 		QuestionsNum INT UNSIGNED DEFAULT 0,
   97: 		Type 		ENUM('ç','ô','þ'),
   98: 		Copyright 	TEXT,
   99: 		Info 			TEXT,
  100: 		URL 			TINYTEXT,
  101: 		FileName 	CHAR(25),
  102:                 Editors         TEXT,
  103:                 EnteredBy       TEXT,
  104: 		PlayedAt 	DATE,
  105: 		PlayedAt2       DATE,		
  106: 		KandId          INT,		
  107: 		CreatedAt 	DATE NOT NULL
  108: 	) TYPE=MyISAM"
  109: 	. (($dbh->get_info( 18 )=~/^(5|(4.1))/)?' DEFAULT CHARSET=koi8r':'')
  110: 
  111: ) 
  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: }