Return to updateindex.pl CVS log

Up to [Local Repository] / db / prgsrc

Sync with live

#!/usr/local/bin/perl -w

=head1 NAME

updateindex.pl - a script for creation of new database. 

=head1 SYNOPSIS

updateind.pl [B<-i> I<indexfile>] [B<-y>|B<-n>] [B<-r>]


=head1 DESCRIPTION

Upadets metainformation in the B<chgk> databse. 

An example of the index file follows:

		 Авторские вопросы
			 Виктор Байрак
 bayrak.txt  			Вопросы В.Байрака
			 Борис Бурда
 burda.txt   			Вопросы Бориса Бурды
 burda1.txt  			Тренировки Бориса Бурды 1
 burda10.txt 			Тренировки Бориса Бурды 10
 burda11.txt 			Тренировки Бориса Бурды 11
 burda12.txt 			Тренировки Бориса Бурды 12


=head1 OPTIONS

=over 4

=item B<-i> I<indexfile>

The index file to read (Standard input by default)

=item B<-y>

Answer 'yes' to all questions

=item B<-n>

Answer 'no' to all questions

=item B<-r>

Remove all entries with zero QuestionNum

=head1 BUGS

The database, user and password are hardcoded. 

=head1 SEE ALSO

createindex.pl(1)

=head1 AUTHOR

Boris Veytsman

=head1 $Id: updateindex.pl,v 1.12 2008/11/14 11:23:03 roma7 Exp $

=cut

    use strict;
use vars qw($opt_i $opt_h $opt_y $opt_n $opt_r);

use Getopt::Std;
use DBI;

MAIN: 
{
    my $USAGE="Usage: updateindex.pl [-i indexfile] [-y|-n][-r]\n";
    my $REMOVE=0;
    getopts('hi:ynr') or die $USAGE;
    if ($opt_h) {
	print $USAGE;
	exit 0;
    }
    my $decision='askuser';
    if ($opt_y) {
	$decision = 'yes';
    } 
    if ($opt_n ) {
	$decision = 'no';
    }
    if ($opt_r) {
	$REMOVE=1;
    }
    my($source) = $opt_i;
    my $champ;
    my($depth, @depthId);
    my $filename;
    my($dbh) = DBI->connect("DBI:mysql:chgk", "piataev", "") 
	or die "Can't connect to DB chgk\n";
if ($dbh->get_info( 18 )=~/^(5|(4.1))/)  {$dbh->do("SET NAMES 'koi8r'");}
    if ($source) {
	open INFO, $source or die "Can't open input file: $!\n";
    } else {
	*INFO=*STDIN;
    }

    while (<INFO>) {
	chomp;
	s/
//;
	next if (/^\s*$/);
	if (s/^(\S+\.txt) *//) { # File found
	    $filename = $1;
	    $depth = -1;
	    $champ=1;
	} else {  # Group found
	    if (s/^(\S+)//)
	    	{ $filename = $1;}	
	    else 
		{$filename = ''}
	    $depth = -2;
	    $champ=0;
	}
	s/^(\t*)//;
	$depth += length($1);
	if ($depth < 0) {
	    die "Wrong line $_\n";
	}
	s/^\s*//;
	s/\s$//;
	my $title = $_;
	my $ParentId = ($depth) ? $depthId[$depth - 1] : 0;
	my $Id = CheckId($dbh,$title,$ParentId,$decision,$filename);
	if (!$Id  || $champ) {
	    next;
	}
	$depthId[$depth] = $Id;

    }
    print STDERR "Всего вопросов: ",
    UpdateGroup($dbh,0),"\n";
    if ($REMOVE) {
	print STDERR "Removing empty tours.";
	$dbh->do("DELETE FROM Tournaments WHERE QuestionsNum=0");
    }
    $dbh->do("INSERT INTO Tournaments
                             (Id, Title, ParentId, Type,CreatedAt) 
                             VALUES
                             (9999, 'Несортированные турниры', 0,'Г',NOW())");

    $dbh->disconnect;
}


sub CheckId {
    my ($dbh,$title,$ParentId,$answer,$filename) = @_;
    my $type;
    my $key;
    my $value;
    my $Id = 0;	
    if ($filename && $filename=~/\.txt/) {
	$type=$dbh->quote('Ч');
    }	else {$type=$dbh->quote('Г');}
    if ($filename)
    {
    	$key = "FileName";
    	$value = $dbh->quote($filename);
    } else {
    	$key = "Title";
    	$value = $dbh->quote($title);
    }

    $title=$dbh->quote($title);    
    my $sth = $dbh->prepare("SELECT Id FROM Tournaments 
                             WHERE $key=$value");
    $sth->execute or die $dbh->errstr;
    my @arr = $sth->fetchrow;
    if (scalar @arr) {
	if ($answer eq 'askuser') {
	    print "$value is already in the DB!\n";
	    print "Заменить новым значением? [y/N]\n";
	    $answer = <STDIN>;
	}
	if ($answer !~ /^[yY]/) {
	    print STDERR  "Не заменяем $value\n";
	    return 0;
	} else {
	    print STDERR  "Заменяем $value\n";
	    $Id = $arr[0];
	} 
    }
    if ($Id) {
	$sth = $dbh->prepare("UPDATE Tournaments
                             SET Title=$title, ParentId=$ParentId, 
                             Type=$type
                             WHERE Id=$Id");

    } else {
	$sth = $dbh->prepare("INSERT INTO Tournaments
                             (Title, ParentId, Type,CreatedAt) 
                             VALUES
                             ($title, $ParentId,$type,NOW())");
    }
    $sth->execute or die $dbh->errstr;
    if (!$Id) {
	$Id = $sth->{'mysql_insertid'};
    }
    if ($filename) {
	$filename=$dbh->quote($filename);
	$sth = $dbh->prepare("UPDATE Tournaments
                             SET FileName=$filename
                             WHERE Id=$Id");
	$sth->execute or die $dbh->errstr;
    }	
    return $Id;
}

sub UpdateGroup {
    my ($dbh,$Id) = @_;
    my $sth = $dbh->prepare("SELECT COUNT(*) FROM Questions
			     WHERE ParentId=$Id");
    $sth->execute;
    my @arr=$sth->fetchrow;
    my $result=$arr[0];
    my @Tours = GetTours($dbh,$Id);
    foreach my $TourId (@Tours) {
	$result += UpdateGroup($dbh,$TourId);
    }
    $sth=$dbh->prepare("UPDATE Tournaments SET
                   QuestionsNum=$result 
                   WHERE Id=$Id");
    $sth->execute;
    return $result;
}

sub GetTours {
	my ($dbh, $ParentId) = @_;
	my (@arr, @Tours);

	my ($sth) = $dbh->prepare("SELECT Id FROM Tournaments
		WHERE ParentId=$ParentId ORDER BY Id");

	$sth->execute;

	while (@arr = $sth->fetchrow) {
		push @Tours, $arr[0];
	}

	return @Tours;
}