#!/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.9 2003/04/14 20:06:59 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 ($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)
VALUES
(9999, 'Несортированные турниры', 0,'Г')");
$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)
VALUES
($title, $ParentId,$type)");
}
$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;
}
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>