Return to updatedb.pl CVS log

Up to [Local Repository] / db / prgsrc

Added uppercase months.

    1: #!/usr/local/bin/perl 
    2: 
    3: =head1 NAME
    4: 
    5: updatedb.pl - a script for creation of new database. 
    6: 
    7: =head1 SYNOPSIS
    8: 
    9: updatedb.pl I<file1> I<file2>....
   10: 
   11: 
   12: =head1 DESCRIPTION
   13: 
   14: Updates information in the B<chgk> databse. Uses file
   15: 
   16: 
   17: =head1 BUGS
   18: 
   19: The database, user and password are hardcoded. 
   20: 
   21: =head1 AUTHOR
   22: 
   23: Dmitry Rubinstein
   24: 
   25: =head1 $Id: updatedb.pl,v 1.4 2000/10/17 16:56:09 boris Exp $
   26: 
   27: =cut
   28: 
   29: my (%RevMonths) = 
   30:  	('Jan', '1', 'Feb', '2', 'Mar', '3', 'Apr', '4', 'May', '5', 'Jun', '6',
   31: 	'Jul', '7', 'Aug', '8', 'Sep', '9', 'Oct', '10', 'Nov', '11',
   32: 	'Dec', '12', 
   33: 	 'JAN', '1', 'FEB', '2', 'MAR', '3', 'APR', '4', 'MAY', '5', 'JUN', '6',
   34: 	'JUL', '7', 'AUG', '8', 'SEP', '9', 'OCT', '10', 'NOV', '11',
   35: 	'DEC', '12', 
   36: 	 'Янв', '0', 'Фев', 1, 'Мар', 2, 'Апр', 3, 'Май', '4',
   37: 	 'Июн', '5', 'Июл', 6, 'Авг', '7', 'Сен', '8', 
   38: 	 'Окт', '9', 'Ноя', '19', 'Дек', '11');
   39: my ($sth);
   40: 
   41: use vars qw($/);
   42: 
   43: 
   44: 
   45: use DBI;
   46: use strict;
   47: 
   48: sub UpdateParents {
   49: 	my ($dbh, $ParentId, $all_qnum) = @_;
   50: 	if ($ParentId) {
   51: 		my ($sth1) = $dbh->prepare("
   52: 			SELECT QuestionsNum, ParentId FROM Tournaments WHERE Id = $ParentId
   53: 		");
   54: 		$sth1->execute;
   55: 		my ($q, $p) = ($sth1->fetchrow)[0, 1];
   56: 		$dbh->do("
   57: 			UPDATE Tournaments SET QuestionsNum=$q + $all_qnum WHERE Id =
   58: 			$ParentId");
   59: 		&UpdateParents($dbh, $p, $all_qnum);
   60: 	}
   61: }
   62: 
   63: sub getField {
   64: 	my($desc, $dbh) = @_;
   65: 	my($key);
   66: 	my($value) = ('');
   67: 	while (<$desc>) {
   68: 		s/
//;
   69: 		if ($key && /^\s*$/) {
   70: 			chomp $value;
   71: 			chomp $key;
   72: 			if ($key eq 'Дата') {
   73: 				$value =~ s/^(.*)-(.*)-(.*)$/$3-$2-$1/;
   74: 				my($month) = $RevMonths{$2};
   75: 				$value =~ s/$2/$month/;
   76: 			}
   77: 			$value = $dbh->quote($value);
   78: 			return ($key, $value);
   79: 		}
   80: 		next if (/^\s*$/);
   81: 
   82: 		if (/^(.*):\s*$/ && !$key) {
   83: 			$key = $1;
   84: 			next;
   85: 		}
   86: 		if ($key) {
   87: 			$value .= $_;
   88: 			next;
   89: 		}
   90: 	}
   91: 	if ($key && $value) {
   92: 		$value = $dbh->quote($value);
   93: 		return ($key, $value);
   94: 	}
   95: 	return (0, 0);
   96: }
   97: 
   98: sub SelectGroup {
   99: 	my ($dbh, $TourName, $sth, $ParentId, $i, @arr) = @_;
  100: 
  101: 	$sth = $dbh->prepare("SELECT Id, Title FROM
  102: 		Tournaments WHERE Type = 'Г'");
  103: 	$sth->execute;
  104: 	print "Выберите группу для турнира:\n$TourName\n\n";
  105: 	while (@arr=$sth->fetchrow) {
  106: 		print "[$arr[0]] $arr[1]\n";
  107: 	}
  108: 	$ParentId = <STDIN>;
  109: 	print "Вы выбрали турнир: $ParentId\n";
  110: 	return $ParentId;
  111: }
  112: 
  113: sub UpdateTournament {
  114: 	my ($dbh, $TournamentId, $field, $value) = @_;
  115: 	$dbh->do("UPDATE Tournaments SET $field=$value WHERE Id=$TournamentId")
  116: 		or die $dbh->errstr;
  117: }
  118: 
  119: sub UpdateQuestion {
  120: 	my ($dbh, $QuestionId, $field, $value) = @_;
  121: 	$dbh->do("UPDATE Questions SET $field=$value 
  122: 		WHERE QuestionId=$QuestionId")
  123: 			or die $dbh->errstr;
  124: }
  125: 
  126: MAIN: 
  127: {
  128: 	my($key, $value, $addition);
  129: 
  130: 	my($source);
  131: 
  132: 	my($dbh) = DBI->connect("DBI:mysql:chgk", "piataev", "")
  133: 		or die "Can't connect to DB chgk\n";
  134: 
  135: 	while ($source = shift) {
  136: 		my($PlayedAt) = '';
  137: 		my($QuestionId, $TourId, $TournamentId, $ParentId) = (0, 0, 0, 0);
  138: 		my($tournum, $qnum, $all_qnum, $qtype) = (0, 0, 0, 'Ч');
  139: 		my (@d) = (localtime((stat($source))[9]))[5,4,3];
  140: 		$d[1]++;
  141: 		$d[0]+=1900;
  142: 		my ($CreatedAt) = $dbh->quote( join('-', @d));
  143: 		print "File created on: $CreatedAt\n";
  144: 	
  145: 		open INFD, $source 
  146: 			or die "Can't open input file: $!\n";
  147: 	
  148: 		$source =~ s/^.*\/([^\/]*)$/$1/;
  149: 		$source = $dbh->quote($source);
  150: 		print "Processing file: $source \n";
  151: 
  152: 		while (($key, $value) = getField(\*INFD, $dbh)) {
  153: 			last if (!$key);
  154: 	
  155: 			if ($key =~ /Мета/) {
  156: 				$value =~ s/[^\d]*//g;
  157: 				$sth = $dbh->prepare("SELECT Id FROM Tournaments WHERE
  158: 					MetaId=$value");
  159: 				$sth->execute 
  160: 					or die "Invalid Meta field: $value";
  161: 				$ParentId = ($sth->fetchrow)[0];
  162: 				next;
  163: 			}
  164: 			if ($key =~ /Чемпионат/) {
  165: 				$ParentId = &SelectGroup($dbh, $value)
  166: 					unless ($ParentId);
  167: 				$sth = $dbh->prepare("INSERT INTO Tournaments
  168: 					(Title, Type, ParentId, FileName, CreatedAt) 
  169: 					VALUES ($value, 'Ч', $ParentId, $source,
  170: 					$CreatedAt)");
  171: 				$sth->execute;
  172: 				$TournamentId = $sth->{mysql_insertid};
  173: 				next;
  174: 			}
  175: 			if ($key =~ /Тур/) {
  176: 				if ($TourId) {
  177: 					$dbh->do("UPDATE Tournaments SET QuestionsNum=$qnum
  178: 						WHERE Id=$TourId");
  179: 				}
  180: 				$qnum = 0;
  181: 				$qtype = 'Ч';
  182: 				$sth = $dbh->prepare("INSERT INTO Tournaments
  183: 					(Title, Type, ParentId, CreatedAt) 
  184: 					VALUES ($value, 'Т', $TournamentId, $CreatedAt)");
  185: 				$sth->execute;
  186: 				$TourId = $sth->{mysql_insertid};
  187: 				next;
  188: 			}
  189: 			if ($key =~ /Вид/) {
  190: 				$qtype = $value;
  191: 				$qtype =~ s/'//g;
  192: 				next;
  193: 			}
  194: 			if ($key =~ /Вопрос/) {
  195: 			    my $query = "INSERT INTO Questions 
  196: 					(ParentId, Number, Type) 
  197: 					VALUES ($TourId, $qnum+1, \'$qtype\')";
  198: 				$sth = $dbh->prepare($query);
  199: 				$sth->execute or print $query;;
  200: 				$QuestionId = $sth->{mysql_insertid};
  201: 				&UpdateQuestion($dbh, $QuestionId, "Question", $value);
  202: 				$qnum++;
  203: 				$all_qnum++;
  204: 				next;
  205: 			}
  206: 			&UpdateQuestion($dbh, $QuestionId, "Answer", $value) 
  207: 				if ($key =~ /Ответ/);
  208: 	
  209: 			&UpdateQuestion($dbh, $QuestionId, "Authors", $value) 
  210: 				if ($key =~ /Автор/);
  211: 	
  212: 			&UpdateQuestion($dbh, $QuestionId, "Sources", $value) 
  213: 				if ($key =~ /Источник/);
  214: 	
  215: 			&UpdateQuestion($dbh, $QuestionId, "Comments", $value) 
  216: 				if ($key =~ /Комментарий/);
  217: 	
  218: 			&UpdateTournament($dbh, $TournamentId, "URL", $value)
  219: 				if ($key =~ /URL/);
  220: 
  221: 			&UpdateTournament($dbh, $TournamentId, "Copyright", $value)
  222: 				if ($key =~ /Копирайт/);
  223: 
  224: 			&UpdateTournament($dbh, $TournamentId, "Info", $value)
  225: 				if ($key =~ /Инфо/);
  226: 
  227: 			if ($key =~ /Дата/) {
  228: 				if ($TourId) {
  229: 					&UpdateTournament($dbh, $TourId, "PlayedAt", $value);
  230: 				} else {
  231: 					&UpdateTournament($dbh, $TournamentId, "PlayedAt", $value);
  232: 				}
  233: 			}
  234: 		}
  235: 		$dbh->do("UPDATE Tournaments SET QuestionsNum=$qnum
  236: 			WHERE Id=$TourId");
  237: 		$dbh->do("UPDATE Tournaments SET QuestionsNum=$all_qnum
  238: 			WHERE Id=$TournamentId");
  239: 		&UpdateParents($dbh, $ParentId, $all_qnum);		
  240: 	}
  241: 	$dbh->disconnect;
  242: }