File:  [Local Repository] / db / prgsrc / updatedb.pl
Revision 1.10: download - view: text, annotated - select for diffs - revision graph
Fri Oct 20 01:45:46 2000 UTC (23 years, 7 months ago) by boris
Branches: MAIN
CVS tags: HEAD
Added more correct deletion.

    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.10 2000/10/20 01:45:46 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("SELECT QuestionsNum, ParentId 
   52: FROM Tournaments WHERE Id = $ParentId");
   53: 	$sth1->execute;
   54: 	my ($q, $p) = ($sth1->fetchrow)[0, 1];
   55: 	$dbh->do("UPDATE Tournaments SET 
   56:                   QuestionsNum=$q + $all_qnum 
   57:                   WHERE Id = $ParentId");
   58: 	&UpdateParents($dbh, $p, $all_qnum);
   59:     }
   60: }
   61: 
   62: sub getField {
   63:     my($desc, $dbh) = @_;
   64:     my($key);
   65:     my($value) = ('');
   66:     while (<$desc>) {
   67: 	s/
//g;
   68: 	if ($key && /^\s*$/) {
   69: 	    chomp $value;
   70: 	    chomp $key;
   71: 	    if ($key eq 'Дата') {
   72: 		$value =~ s/^(.*)-(.*)-(.*)$/$3-$2-$1/;
   73: 		my($month) = $RevMonths{$2} || '000';
   74: 		$value =~ s/$2/$month/;
   75: 	    }
   76: 	    $value = $dbh->quote($value);
   77: 	    return ($key, $value);
   78: 	}
   79: 	next if (/^\s*$/);
   80: 	
   81: 	if (/^(.*)[:\.][\s]*$/ && !$key) {
   82: 	    $key = $1;
   83: 	    next;
   84: 	}
   85: 	if ($key) {
   86: 	    $value .= $_;
   87: 	    next;
   88: 	}
   89:     }
   90:     if ($key && $value) {
   91: 	$value = $dbh->quote($value);
   92: 	return ($key, $value);
   93:     }
   94:     return (0, 0);
   95: }
   96: 
   97: sub SelectGroup {
   98:     my ($dbh, $source, $TourName) = @_;
   99:     my ($sth, $ParentId, $i, @arr);
  100:     
  101:     $sth = $dbh->prepare("SELECT Id, Title FROM
  102: 		Tournaments WHERE Type = 'Г'");
  103:     $sth->execute;
  104:     print "Выберите группу для турнира:\n$TourName, файл $source\n\n";
  105:     while (@arr=$sth->fetchrow) {
  106: 	print "[$arr[0]] $arr[1]\n";
  107:     }
  108:     $ParentId = <STDIN>;
  109:     chomp $ParentId;
  110:     if (!$ParentId) {
  111: 	print "Пропускаем файл $source\n";
  112: 	print STDERR "Файл $source отвергнут оператором\n";
  113: 	return (0,0);
  114:     } else {
  115: 	print "Вы выбрали турнир: $ParentId\n";
  116: 	$sth = $dbh->prepare("INSERT INTO Tournaments
  117: 			      (Title, Type, ParentId, FileName) 
  118: 			       VALUES ($TourName, 'Ч', $ParentId, 
  119:                                        $source)");
  120: 	$sth->execute;
  121: 	my $TournamentId = $sth->{mysql_insertid};
  122: 	return ($TournamentId,$ParentId);
  123:     }
  124: 		
  125:     
  126: }
  127: 
  128: sub UpdateTournament {
  129:     my ($dbh, $TournamentId, $field, $value) = @_;
  130:     $dbh->do("UPDATE Tournaments SET $field=$value WHERE Id=$TournamentId")
  131: 	or die $dbh->errstr;
  132: }
  133: 
  134: sub UpdateQuestion {
  135:     my ($dbh, $QuestionId, $field, $value) = @_;
  136:     $dbh->do("UPDATE Questions SET $field=$value 
  137: 		WHERE QuestionId=$QuestionId")
  138: 	or die $dbh->errstr;
  139: }
  140: 
  141: sub CheckFile {
  142:     my ($dbh, $source, $title) = @_;
  143:     my $sth = $dbh->prepare("SELECT Id,ParentId,QuestionsNum FROM Tournaments
  144:                              WHERE FileName=$source AND Type='Ч'");
  145:     $sth->execute or die $dbh->errstr;
  146:     my @arr = $sth->fetchrow;
  147:     if (! scalar @arr) {
  148: 	return SelectGroup($dbh,$source,$title);
  149:     }
  150:     my($Id,$ParentId,$QuestionsNum)=@arr;
  151:     if($QuestionsNum) {	
  152: 	print "Файл $source с данными $title уже существует. ",
  153: 	"Заменить?[y/N]\n";
  154: 	my $answer = <STDIN>;
  155: 	if ($answer !~ /^[yY]/) {
  156: 	    return (0,0);
  157: 	} else {
  158: 	    DeleteTournament($dbh,$Id,$ParentId,$QuestionsNum,0);
  159: 	}
  160:     } 
  161:     return($Id,$ParentId);	
  162: }
  163: 
  164: 
  165: sub DeleteTournament {
  166:     my ($dbh,$Id,$ParentId,$QuestionsNum,$DeleteMyself) = @_;
  167:     if ($QuestionsNum) {
  168: 	UpdateParents($dbh,$ParentId,-$QuestionsNum);
  169:     }
  170:     my (@Tours) = &GetTours($dbh, $Id);
  171:     foreach my $Tour (@Tours) {
  172: 	DeleteTournament($dbh,$Tour,1);
  173:     }
  174:     my $sth = $dbh->prepare("DELETE FROM Questions
  175:                              WHERE ParentId=$Id");
  176:     $sth->execute or die $dbh->errstr;
  177:     if($DeleteMyself) {
  178: 	$sth = $dbh->prepare("DELETE FROM Tournaments
  179:                              WHERE Id=$Id");
  180: 	$sth->execute or die $dbh->errstr;
  181:     }
  182: }
  183: 
  184: sub GetTours {
  185: 	my ($dbh, $ParentId) = @_;
  186: 	my (@arr, @Tours);
  187: 
  188: 	my ($sth) = $dbh->prepare("SELECT Id FROM Tournaments
  189: 		WHERE ParentId=$ParentId ORDER BY Id");
  190: 
  191: 	$sth->execute;
  192: 
  193: 	while (@arr = $sth->fetchrow) {
  194: 		push @Tours, $arr[0];
  195: 	}
  196: 
  197: 	return @Tours;
  198: }
  199: 
  200: 
  201: MAIN: 
  202: {
  203:     my($key, $value, $addition);
  204:     
  205:     my($source);
  206:     
  207:     my($dbh) = DBI->connect("DBI:mysql:chgk", "piataev", "")
  208: 	or die "Can't connect to DB chgk\n";
  209:     
  210:     while ($source = shift) {
  211: 	my($PlayedAt) = '';
  212: 	my($QuestionId, $TourId, $TournamentId, $ParentId) = (0, 0, 0, 0);
  213: 	my($tournum, $qnum, $all_qnum, $qtype) = (0, 0, 0, 'Ч');
  214: 	my (@d) = (localtime((stat($source))[9]))[5,4,3];
  215: 	$d[1]++;
  216: 	$d[0]+=1900;
  217: 	my ($CreatedAt) = $dbh->quote( join('-', @d));
  218: 
  219: 	open INFD, $source 
  220: 	    or die "Can't open input file: $!\n";
  221: 	
  222: 	$source =~ s/^.*\/([^\/]*)$/$1/;
  223: 	$source = $dbh->quote($source);
  224: 	print STDERR "Файл: $source, дата: $CreatedAt ";
  225: 	while (($key, $value) = getField(\*INFD, $dbh)) {
  226: 	    last if (!$key);
  227: 	    
  228: 	    if ($key =~ /Мета/) {
  229: 		next;   # This is obsolete
  230: 	    }
  231: 	    if ($key =~ /Чемпионат/ || $key =~ /Пакет/) {		
  232: 		($TournamentId, $ParentId) = CheckFile($dbh,$source,$value);
  233: 		if (!$TournamentId)  {
  234: 		    last;
  235: 		}	
  236: 		$sth = $dbh->prepare("UPDATE Tournaments SET
  237: 			             Title=$value, Type='Ч', 
  238:                                      ParentId=$ParentId, 
  239:                                      FileName=$source, 
  240:                                      CreatedAt=$CreatedAt
  241:                                      WHERE
  242:                                      Id=$TournamentId");
  243: 		$sth->execute;
  244: 		next;
  245: 	    }
  246: 	    if ($key =~ /Тур/) {
  247: 		if ($TourId) {
  248: 		    $dbh->do("UPDATE Tournaments SET QuestionsNum=$qnum
  249: 			      WHERE Id=$TourId");
  250: 		}
  251: 		$qnum = 0;
  252: 		$qtype = 'Ч';
  253: 		$sth = $dbh->prepare("INSERT INTO Tournaments
  254: 				      (Title, Type, ParentId, CreatedAt) 
  255: 				      VALUES ($value, 'Т', $TournamentId, 
  256:                                       $CreatedAt)");
  257: 		$sth->execute;
  258: 		$TourId = $sth->{mysql_insertid};
  259: 		next;
  260: 	    }
  261: 	    if ($key =~ /Вид/ || $key =~ /Тип/) {
  262: 		$qtype = $value;
  263: 		$qtype =~ s/\'//g;
  264: 		next;
  265: 	    }
  266: 	    if ($key =~ /Вопрос/) {
  267: 		my $query = "INSERT INTO Questions 
  268: 			     (ParentId, Number, Type) 
  269: 			     VALUES ($TourId, $qnum+1, \'$qtype\')";
  270: 		$sth = $dbh->prepare($query);
  271: 		$sth->execute or print $query;;
  272: 		$QuestionId = $sth->{mysql_insertid};
  273: 		&UpdateQuestion($dbh, $QuestionId, "Question", $value);
  274: 		$qnum++;
  275: 		$all_qnum++;
  276: 		next;
  277: 	    }
  278: 
  279: 	    if ($key =~ /Ответ/) {
  280: 		&UpdateQuestion($dbh, $QuestionId, "Answer", $value);
  281: 		next;
  282: 	    }
  283: 
  284: 	    if ($key =~ /Рейтинг/) {
  285: 		&UpdateQuestion($dbh, $QuestionId, "Rating", $value);
  286: 		next;
  287: 	    }
  288: 	    
  289: 
  290: 	    if ($key =~ /Автор/) {
  291: 		&UpdateQuestion($dbh, $QuestionId, "Authors", $value);
  292: 		next;
  293: 	    }
  294: 	    
  295: 
  296: 	    if ($key =~ /Источник/) {
  297: 		&UpdateQuestion($dbh, $QuestionId, "Sources", $value);
  298: 		next;
  299: 	    }
  300: 	    
  301: 
  302: 	    if ($key =~ /Комментари/) {
  303: 		&UpdateQuestion($dbh, $QuestionId, "Comments", $value);
  304: 		next;
  305: 	    }
  306: 	    
  307: 
  308: 	    if ($key =~ /URL/ || $key =~ /Ссылка/) {
  309: 		&UpdateTournament($dbh, $TournamentId, "URL", $value);
  310: 		next;
  311: 	    }
  312: 	    
  313: 
  314: 	    if ($key =~ /Копирайт/) {
  315: 		&UpdateTournament($dbh, $TournamentId, "Copyright", $value);
  316: 		next;
  317: 	    }
  318: 	    
  319: 
  320: 	    if ($key =~ /Инфо/) {
  321: 		&UpdateTournament($dbh, $TournamentId, "Info", $value);
  322: 		next;
  323: 	    }
  324: 	    
  325: 
  326: 	    if ($key =~ /Редактор/) {
  327: 		&UpdateTournament($dbh, $TournamentId, "Editors", $value);
  328: 		next;
  329: 	    }
  330: 	    
  331: 
  332: 	    if ($key =~ /Обработан/) {
  333: 		&UpdateTournament($dbh, $TournamentId, "EnteredBy", $value);
  334: 		next;
  335: 			      }
  336: 	    
  337: 	    if ($key =~ /Дата/) {
  338: 		if ($TourId) {
  339: 		    &UpdateTournament($dbh, $TourId, "PlayedAt", $value);
  340: 		} else {
  341: 		    &UpdateTournament($dbh, $TournamentId, "PlayedAt", $value);
  342: 		}
  343: 		next;
  344: 	    }
  345: 	    
  346: 	    #
  347: 	    # If we are here, something got wrong!
  348: 	    #
  349: 	    print STDERR "\nЯ НЕ ПОНИМАЮ: $key, $value!\n";
  350: 	    
  351: 	}
  352: 	$dbh->do("UPDATE Tournaments SET QuestionsNum=$qnum
  353: 			WHERE Id=$TourId");
  354: 	$dbh->do("UPDATE Tournaments SET QuestionsNum=$all_qnum
  355: 			WHERE Id=$TournamentId");
  356: 	&UpdateParents($dbh, $ParentId, $all_qnum);		
  357: 	print STDERR "Всего вопросов: $all_qnum \n";
  358:     }
  359:     $dbh->disconnect;
  360: }

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>