[BACK]Return to updatedb.pl CVS log [TXT] [DIR] Up to [Local Repository] / db / prgsrc
File:  [Local Repository] / db / prgsrc / updatedb.pl
Revision 1.2: download - view: text, annotated - select for diffs - revision graph
Tue Oct 17 01:54:16 2000 UTC (23 years, 7 months ago) by boris
Branches: MAIN
CVS tags: HEAD
Changed insertid to mysql_insertid

    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.2 2000/10/17 01:54:16 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: 	 'Янв', '0', 'Фев', 1, 'Мар', 2, 'Апр', 3, 'Май', '4',
   34: 	 'Июн', '5', 'Июл', 6, 'Авг', '7', 'Сен', '8', 
   35: 	 'Окт', '9', 'Ноя', '19', 'Дек', '11');
   36: my ($sth);
   37: 
   38: use DBI;
   39: use strict;
   40: 
   41: sub UpdateParents {
   42: 	my ($dbh, $ParentId, $all_qnum) = @_;
   43: 	if ($ParentId) {
   44: 		my ($sth1) = $dbh->prepare("
   45: 			SELECT QuestionsNum, ParentId FROM Tournaments WHERE Id = $ParentId
   46: 		");
   47: 		$sth1->execute;
   48: 		my ($q, $p) = ($sth1->fetchrow)[0, 1];
   49: 		$dbh->do("
   50: 			UPDATE Tournaments SET QuestionsNum=$q + $all_qnum WHERE Id =
   51: 			$ParentId");
   52: 		&UpdateParents($dbh, $p, $all_qnum);
   53: 	}
   54: }
   55: 
   56: sub getField {
   57: 	my($desc, $dbh) = @_;
   58: 	my($key);
   59: 	my($value) = ('');
   60: 	while (<$desc>) {
   61: 		s/
//;
   62: 		if ($key && /^\s*$/) {
   63: 			chomp $value;
   64: 			chomp $key;
   65: 			if ($key eq 'Дата') {
   66: 				$value =~ s/^(.*)-(.*)-(.*)$/$3-$2-$1/;
   67: 				my($month) = $RevMonths{$2};
   68: 				$value =~ s/$2/$month/;
   69: 			}
   70: 			$value = $dbh->quote($value);
   71: 			return ($key, $value);
   72: 		}
   73: 		next if (/^\s*$/);
   74: 
   75: 		if (/^(.*):\s*$/ && !$key) {
   76: 			$key = $1;
   77: 			next;
   78: 		}
   79: 		if ($key) {
   80: 			$value .= $_;
   81: 			next;
   82: 		}
   83: 	}
   84: 	if ($key && $value) {
   85: 		$value = $dbh->quote($value);
   86: 		return ($key, $value);
   87: 	}
   88: 	return (0, 0);
   89: }
   90: 
   91: sub SelectGroup {
   92: 	my ($dbh, $TourName, $sth, $ParentId, $i, @arr) = @_;
   93: 
   94: 	$sth = $dbh->prepare("SELECT Id, Title FROM
   95: 		Tournaments WHERE Type = 'Г'");
   96: 	$sth->execute;
   97: 	print "Выберите группу для турнира:\n$TourName\n\n";
   98: 	for ($i = 0; $i < $sth->numrows; $i++) {
   99: 		@arr = $sth->fetchrow;
  100: 		print "[$arr[0]] $arr[1]\n";
  101: 	}
  102: 	$ParentId = <STDIN>;
  103: 	print "Вы выбрали турнир: $ParentId\n";
  104: 	return $ParentId;
  105: }
  106: 
  107: sub UpdateTournament {
  108: 	my ($dbh, $TournamentId, $field, $value) = @_;
  109: 	$dbh->do("UPDATE Tournaments SET $field=$value WHERE Id=$TournamentId")
  110: 		or die $dbh->errstr;
  111: }
  112: 
  113: sub UpdateQuestion {
  114: 	my ($dbh, $QuestionId, $field, $value) = @_;
  115: 	$dbh->do("UPDATE Questions SET $field=$value 
  116: 		WHERE QuestionId=$QuestionId")
  117: 			or die $dbh->errstr;
  118: }
  119: 
  120: MAIN: 
  121: {
  122: 	my($key, $value, $addition);
  123: 
  124: 	my($source);
  125: 
  126: 	my($dbh) = DBI->connect("DBI:mysql:chgk", "piataev", "")
  127: 		or die "Can't connect to DB chgk\n";
  128: 
  129: 	while ($source = shift) {
  130: 		my($PlayedAt) = '';
  131: 		my($QuestionId, $TourId, $TournamentId, $ParentId) = (0, 0, 0, 0);
  132: 		my($tournum, $qnum, $all_qnum, $qtype) = (0, 0, 0, 'Ч');
  133: 		my (@d) = (localtime((stat($source))[9]))[5,4,3];
  134: 		$d[1]++;
  135: 		my ($CreatedAt) = $dbh->quote( join('-', @d));
  136: 		print "File created on: $CreatedAt\n";
  137: 	
  138: 		open INFD, $source 
  139: 			or die "Can't open input file: $!\n";
  140: 	
  141: 		$source =~ s/^.*\/([^\/]*)$/$1/;
  142: 		$source = $dbh->quote($source);
  143: 		print "Processing file: $source \n";
  144: 
  145: 		while (($key, $value) = getField(\*INFD, $dbh)) {
  146: 			last if (!$key);
  147: 	
  148: 			if ($key =~ /Мета/) {
  149: 				$value =~ s/[^\d]*//g;
  150: 				$sth = $dbh->prepare("SELECT Id FROM Tournaments WHERE
  151: 					MetaId=$value");
  152: 				$sth->execute 
  153: 					or die "Invalid Meta field: $value";
  154: 				$ParentId = ($sth->fetchrow)[0];
  155: 				next;
  156: 			}
  157: 			if ($key =~ /Чемпионат/) {
  158: 				$ParentId = &SelectGroup($dbh, $value)
  159: 					unless ($ParentId);
  160: 				$sth = $dbh->prepare("INSERT INTO Tournaments
  161: 					(Title, Type, ParentId, FileName, CreatedAt) 
  162: 					VALUES ($value, 'Ч', $ParentId, $source,
  163: 					$CreatedAt)");
  164: 				$sth->execute;
  165: 				$TournamentId = $sth->{mysql_insertid};
  166: 				next;
  167: 			}
  168: 			if ($key =~ /Тур/) {
  169: 				if ($TourId) {
  170: 					$dbh->do("UPDATE Tournaments SET QuestionsNum=$qnum
  171: 						WHERE Id=$TourId");
  172: 				}
  173: 				$qnum = 0;
  174: 				$qtype = "'Ч'";
  175: 				$sth = $dbh->prepare("INSERT INTO Tournaments
  176: 					(Title, Type, ParentId, CreatedAt) 
  177: 					VALUES ($value, 'Т', $TournamentId, $CreatedAt)");
  178: 				$sth->execute;
  179: 				$TourId = $sth->{mysql_insertid};
  180: 				next;
  181: 			}
  182: 			if ($key =~ /Вид/) {
  183: 				$qtype = $value;
  184: 				next;
  185: 			}
  186: 			if ($key =~ /Вопрос/) {
  187: 				$sth = $dbh->prepare("INSERT INTO Questions 
  188: 					(ParentId, Number, Type) 
  189: 					VALUES ($TourId, $qnum+1, $qtype)");
  190: 				$sth->execute;
  191: 				$QuestionId = $sth->{mysql_insertid};
  192: 				&UpdateQuestion($dbh, $QuestionId, "Question", $value);
  193: 				$qnum++;
  194: 				$all_qnum++;
  195: 				next;
  196: 			}
  197: 			&UpdateQuestion($dbh, $QuestionId, "Answer", $value) 
  198: 				if ($key =~ /Ответ/);
  199: 	
  200: 			&UpdateQuestion($dbh, $QuestionId, "Authors", $value) 
  201: 				if ($key =~ /Автор/);
  202: 	
  203: 			&UpdateQuestion($dbh, $QuestionId, "Sources", $value) 
  204: 				if ($key =~ /Источник/);
  205: 	
  206: 			&UpdateQuestion($dbh, $QuestionId, "Comments", $value) 
  207: 				if ($key =~ /Комментарий/);
  208: 	
  209: 			&UpdateTournament($dbh, $TournamentId, "URL", $value)
  210: 				if ($key =~ /URL/);
  211: 
  212: 			&UpdateTournament($dbh, $TournamentId, "Copyright", $value)
  213: 				if ($key =~ /Копирайт/);
  214: 
  215: 			&UpdateTournament($dbh, $TournamentId, "Info", $value)
  216: 				if ($key =~ /Инфо/);
  217: 
  218: 			if ($key =~ /Дата/) {
  219: 				if ($TourId) {
  220: 					&UpdateTournament($dbh, $TourId, "PlayedAt", $value);
  221: 				} else {
  222: 					&UpdateTournament($dbh, $TournamentId, "PlayedAt", $value);
  223: 				}
  224: 			}
  225: 		}
  226: 		$dbh->do("UPDATE Tournaments SET QuestionsNum=$qnum
  227: 			WHERE Id=$TourId");
  228: 		$dbh->do("UPDATE Tournaments SET QuestionsNum=$all_qnum
  229: 			WHERE Id=$TournamentId");
  230: 		&UpdateParents($dbh, $ParentId, $all_qnum);		
  231: 	}
  232: 	$dbh->disconnect;
  233: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>