Return to updateindex.pl CVS log

Up to [Local Repository] / db / prgsrc

Added updating tournaments.

    1: #!/usr/local/bin/perl -w
    2: 
    3: =head1 NAME
    4: 
    5: updateindex.pl - a script for creation of new database. 
    6: 
    7: =head1 SYNOPSIS
    8: 
    9: updateind.pl [B<-i> I<indexfile>] [B<-y>|B<-n>] 
   10: 
   11: 
   12: =head1 DESCRIPTION
   13: 
   14: Upadets metainformation in the B<chgk> databse. 
   15: 
   16: An example of the index file follows:
   17: 
   18: 		 Авторские вопросы
   19: 			 Виктор Байрак
   20:  bayrak.txt  			Вопросы В.Байрака
   21: 			 Борис Бурда
   22:  burda.txt   			Вопросы Бориса Бурды
   23:  burda1.txt  			Тренировки Бориса Бурды 1
   24:  burda10.txt 			Тренировки Бориса Бурды 10
   25:  burda11.txt 			Тренировки Бориса Бурды 11
   26:  burda12.txt 			Тренировки Бориса Бурды 12
   27: 
   28: 
   29: =head1 OPTIONS
   30: 
   31: =over 4
   32: 
   33: =item B<-i> I<indexfile>
   34: 
   35: The index file to read (Standard input by default)
   36: 
   37: =item B<-y>
   38: 
   39: Answer 'yes' to all questions
   40: 
   41: =item B<-n>
   42: 
   43: Answer 'no' to all questions
   44: 
   45: =head1 BUGS
   46: 
   47: The database, user and password are hardcoded. 
   48: 
   49: =head1 SEE ALSO
   50: 
   51: createindex.pl(1)
   52: 
   53: =head1 AUTHOR
   54: 
   55: Boris Veytsman
   56: 
   57: =head1 $Id: updateindex.pl,v 1.6 2000/10/22 01:25:25 boris Exp $
   58: 
   59: =cut
   60: 
   61:     use strict;
   62: use vars qw($opt_i $opt_h $opt_y $opt_n);
   63: 
   64: use Getopt::Std;
   65: use DBI;
   66: 
   67: MAIN: 
   68: {
   69:     my $USAGE="Usage: updateindex.pl [-i indexfile] [-y|-n]\n";
   70:     getopts('hi:yn') or die $USAGE;
   71:     if ($opt_h) {
   72: 	print $USAGE;
   73: 	exit 0;
   74:     }
   75:     my $decision='askuser';
   76:     if ($opt_y) {
   77: 	$decision = 'yes';
   78:     } 
   79:     if ($opt_n ) {
   80: 	$decision = 'no';
   81:     }
   82:     my($source) = $opt_i;
   83:     my($depth, @depthId);
   84:     my $filename;
   85:     my($dbh) = DBI->connect("DBI:mysql:chgk", "piataev", "") 
   86: 	or die "Can't connect to DB chgk\n";
   87: 
   88:     if ($source) {
   89: 	open INFO, $source or die "Can't open input file: $!\n";
   90:     } else {
   91: 	*INFO=*STDIN;
   92:     }
   93:     while (<INFO>) {
   94: 	chomp;
   95: 	s/
//;
   96: 	next if (/^\s*$/);
   97: 	if (s/^(\S+) *//) { # File found
   98: 	    $filename = $1;
   99: 	    $depth = -1;
  100: 	} else {  # Group found
  101: 	    $filename = '';
  102: 	    $depth = -2;
  103: 	}
  104: 	s/^(\t*)//;
  105: 	$depth += length($1);
  106: 	if ($depth < 0) {
  107: 	    die "Wrong line $_\n";
  108: 	}
  109: 	s/^\s*//;
  110: 	s/\s$//;
  111: 	my $title = $_;
  112: 	my $ParentId = ($depth) ? $depthId[$depth - 1] : 0;
  113: 	my $Id = CheckId($dbh,$title,$ParentId,$decision,$filename);
  114: 	if (!$Id  || $filename) {
  115: 	    next;
  116: 	}
  117: 	$depthId[$depth] = $Id;
  118: 
  119:     }
  120:     print STDERR "Всего вопросов: ",
  121:     UpdateGroup($dbh,0),"\n";
  122:     $dbh->disconnect;
  123: }
  124: 
  125: 
  126: sub CheckId {
  127:     my ($dbh,$title,$ParentId,$answer,$filename) = @_;
  128:     my $type;
  129:     my $key;
  130:     my $value;
  131:     my $Id = 0;
  132:     if ($filename) {
  133: 	$type=$dbh->quote('Ч');
  134: 	$key = "FileName";
  135: 	$value = $dbh->quote($filename);
  136:     } else {
  137: 	$type=$dbh->quote('Г');
  138: 	$key = "Title";
  139: 	$value = $dbh->quote($title);
  140:     }
  141:     $title=$dbh->quote($title);    
  142:     my $sth = $dbh->prepare("SELECT Id FROM Tournaments 
  143:                              WHERE $key=$value");
  144:     $sth->execute or die $dbh->errstr;
  145:     my @arr = $sth->fetchrow;
  146:     if (scalar @arr) {
  147: 	if ($answer eq 'askuser') {
  148: 	    print "$value is already in the DB!\n";
  149: 	    print "Заменить новым значением? [y/N]\n";
  150: 	    $answer = <STDIN>;
  151: 	}
  152: 	if ($answer !~ /^[yY]/) {
  153: 	    print STDERR  "Не заменяем $value\n";
  154: 	    return 0;
  155: 	} else {
  156: 	    print STDERR  "Заменяем $value\n";
  157: 	    $Id = $arr[0];
  158: 	} 
  159:     }
  160:     if ($Id) {
  161: 	$sth = $dbh->prepare("UPDATE Tournaments
  162:                              SET Title=$title, ParentId=$ParentId, 
  163:                              Type=$type
  164:                              WHERE Id=$Id");
  165: 
  166:     } else {
  167: 	$sth = $dbh->prepare("INSERT INTO Tournaments
  168:                              (Title, ParentId, Type) 
  169:                              VALUES
  170:                              ($title, $ParentId,$type)");
  171:     }
  172:     $sth->execute or die $dbh->errstr;
  173:     if (!$Id) {
  174: 	$Id = $sth->{'mysql_insertid'};
  175:     }
  176:     if ($filename) {
  177: 	$filename=$dbh->quote($filename);
  178: 	$sth = $dbh->prepare("UPDATE Tournaments
  179:                              SET FileName=$filename
  180:                              WHERE Id=$Id");
  181: 	$sth->execute or die $dbh->errstr;
  182:     }	
  183:     return $Id;
  184: }
  185: 
  186: sub UpdateGroup {
  187:     my ($dbh,$Id) = @_;
  188:     my $sth = $dbh->prepare("SELECT COUNT(*) FROM Questions
  189: 			     WHERE ParentId=$Id");
  190:     $sth->execute;
  191:     my @arr=$sth->fetchrow;
  192:     my $result=$arr[0];
  193:     my @Tours = GetTours($dbh,$Id);
  194:     foreach my $TourId (@Tours) {
  195: 	$result += UpdateGroup($dbh,$TourId);
  196:     }
  197:     $sth=$dbh->prepare("UPDATE Tournaments SET
  198:                    QuestionsNum=$result 
  199:                    WHERE Id=$Id");
  200:     $sth->execute;
  201:     return $result;
  202: }
  203: 
  204: sub GetTours {
  205: 	my ($dbh, $ParentId) = @_;
  206: 	my (@arr, @Tours);
  207: 
  208: 	my ($sth) = $dbh->prepare("SELECT Id FROM Tournaments
  209: 		WHERE ParentId=$ParentId ORDER BY Id");
  210: 
  211: 	$sth->execute;
  212: 
  213: 	while (@arr = $sth->fetchrow) {
  214: 		push @Tours, $arr[0];
  215: 	}
  216: 
  217: 	return @Tours;
  218: }
  219: