Return to updatedb.pl CVS log

Up to [Local Repository] / db / prgsrc

Changed insertid to mysql_insertid

#!/usr/local/bin/perl 

=head1 NAME

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

=head1 SYNOPSIS

updatedb.pl I<file1> I<file2>....


=head1 DESCRIPTION

Updates information in the B<chgk> databse. Uses file


=head1 BUGS

The database, user and password are hardcoded. 

=head1 AUTHOR

Dmitry Rubinstein

=head1 $Id: updatedb.pl,v 1.2 2000/10/17 01:54:16 boris Exp $

=cut

my (%RevMonths) = 
	('Jan', '1', 'Feb', '2', 'Mar', '3', 'Apr', '4', 'May', '5', 'Jun', '6',
	'Jul', '7', 'Aug', '8', 'Sep', '9', 'Oct', '10', 'Nov', '11',
	'Dec', '12', 
	 'Янв', '0', 'Фев', 1, 'Мар', 2, 'Апр', 3, 'Май', '4',
	 'Июн', '5', 'Июл', 6, 'Авг', '7', 'Сен', '8', 
	 'Окт', '9', 'Ноя', '19', 'Дек', '11');
my ($sth);

use DBI;
use strict;

sub UpdateParents {
	my ($dbh, $ParentId, $all_qnum) = @_;
	if ($ParentId) {
		my ($sth1) = $dbh->prepare("
			SELECT QuestionsNum, ParentId FROM Tournaments WHERE Id = $ParentId
		");
		$sth1->execute;
		my ($q, $p) = ($sth1->fetchrow)[0, 1];
		$dbh->do("
			UPDATE Tournaments SET QuestionsNum=$q + $all_qnum WHERE Id =
			$ParentId");
		&UpdateParents($dbh, $p, $all_qnum);
	}
}

sub getField {
	my($desc, $dbh) = @_;
	my($key);
	my($value) = ('');
	while (<$desc>) {
		s/
//;
		if ($key && /^\s*$/) {
			chomp $value;
			chomp $key;
			if ($key eq 'Дата') {
				$value =~ s/^(.*)-(.*)-(.*)$/$3-$2-$1/;
				my($month) = $RevMonths{$2};
				$value =~ s/$2/$month/;
			}
			$value = $dbh->quote($value);
			return ($key, $value);
		}
		next if (/^\s*$/);

		if (/^(.*):\s*$/ && !$key) {
			$key = $1;
			next;
		}
		if ($key) {
			$value .= $_;
			next;
		}
	}
	if ($key && $value) {
		$value = $dbh->quote($value);
		return ($key, $value);
	}
	return (0, 0);
}

sub SelectGroup {
	my ($dbh, $TourName, $sth, $ParentId, $i, @arr) = @_;

	$sth = $dbh->prepare("SELECT Id, Title FROM
		Tournaments WHERE Type = 'Г'");
	$sth->execute;
	print "Выберите группу для турнира:\n$TourName\n\n";
	for ($i = 0; $i < $sth->numrows; $i++) {
		@arr = $sth->fetchrow;
		print "[$arr[0]] $arr[1]\n";
	}
	$ParentId = <STDIN>;
	print "Вы выбрали турнир: $ParentId\n";
	return $ParentId;
}

sub UpdateTournament {
	my ($dbh, $TournamentId, $field, $value) = @_;
	$dbh->do("UPDATE Tournaments SET $field=$value WHERE Id=$TournamentId")
		or die $dbh->errstr;
}

sub UpdateQuestion {
	my ($dbh, $QuestionId, $field, $value) = @_;
	$dbh->do("UPDATE Questions SET $field=$value 
		WHERE QuestionId=$QuestionId")
			or die $dbh->errstr;
}

MAIN: 
{
	my($key, $value, $addition);

	my($source);

	my($dbh) = DBI->connect("DBI:mysql:chgk", "piataev", "")
		or die "Can't connect to DB chgk\n";

	while ($source = shift) {
		my($PlayedAt) = '';
		my($QuestionId, $TourId, $TournamentId, $ParentId) = (0, 0, 0, 0);
		my($tournum, $qnum, $all_qnum, $qtype) = (0, 0, 0, 'Ч');
		my (@d) = (localtime((stat($source))[9]))[5,4,3];
		$d[1]++;
		my ($CreatedAt) = $dbh->quote( join('-', @d));
		print "File created on: $CreatedAt\n";
	
		open INFD, $source 
			or die "Can't open input file: $!\n";
	
		$source =~ s/^.*\/([^\/]*)$/$1/;
		$source = $dbh->quote($source);
		print "Processing file: $source \n";

		while (($key, $value) = getField(\*INFD, $dbh)) {
			last if (!$key);
	
			if ($key =~ /Мета/) {
				$value =~ s/[^\d]*//g;
				$sth = $dbh->prepare("SELECT Id FROM Tournaments WHERE
					MetaId=$value");
				$sth->execute 
					or die "Invalid Meta field: $value";
				$ParentId = ($sth->fetchrow)[0];
				next;
			}
			if ($key =~ /Чемпионат/) {
				$ParentId = &SelectGroup($dbh, $value)
					unless ($ParentId);
				$sth = $dbh->prepare("INSERT INTO Tournaments
					(Title, Type, ParentId, FileName, CreatedAt) 
					VALUES ($value, 'Ч', $ParentId, $source,
					$CreatedAt)");
				$sth->execute;
				$TournamentId = $sth->{mysql_insertid};
				next;
			}
			if ($key =~ /Тур/) {
				if ($TourId) {
					$dbh->do("UPDATE Tournaments SET QuestionsNum=$qnum
						WHERE Id=$TourId");
				}
				$qnum = 0;
				$qtype = "'Ч'";
				$sth = $dbh->prepare("INSERT INTO Tournaments
					(Title, Type, ParentId, CreatedAt) 
					VALUES ($value, 'Т', $TournamentId, $CreatedAt)");
				$sth->execute;
				$TourId = $sth->{mysql_insertid};
				next;
			}
			if ($key =~ /Вид/) {
				$qtype = $value;
				next;
			}
			if ($key =~ /Вопрос/) {
				$sth = $dbh->prepare("INSERT INTO Questions 
					(ParentId, Number, Type) 
					VALUES ($TourId, $qnum+1, $qtype)");
				$sth->execute;
				$QuestionId = $sth->{mysql_insertid};
				&UpdateQuestion($dbh, $QuestionId, "Question", $value);
				$qnum++;
				$all_qnum++;
				next;
			}
			&UpdateQuestion($dbh, $QuestionId, "Answer", $value) 
				if ($key =~ /Ответ/);
	
			&UpdateQuestion($dbh, $QuestionId, "Authors", $value) 
				if ($key =~ /Автор/);
	
			&UpdateQuestion($dbh, $QuestionId, "Sources", $value) 
				if ($key =~ /Источник/);
	
			&UpdateQuestion($dbh, $QuestionId, "Comments", $value) 
				if ($key =~ /Комментарий/);
	
			&UpdateTournament($dbh, $TournamentId, "URL", $value)
				if ($key =~ /URL/);

			&UpdateTournament($dbh, $TournamentId, "Copyright", $value)
				if ($key =~ /Копирайт/);

			&UpdateTournament($dbh, $TournamentId, "Info", $value)
				if ($key =~ /Инфо/);

			if ($key =~ /Дата/) {
				if ($TourId) {
					&UpdateTournament($dbh, $TourId, "PlayedAt", $value);
				} else {
					&UpdateTournament($dbh, $TournamentId, "PlayedAt", $value);
				}
			}
		}
		$dbh->do("UPDATE Tournaments SET QuestionsNum=$qnum
			WHERE Id=$TourId");
		$dbh->do("UPDATE Tournaments SET QuestionsNum=$all_qnum
			WHERE Id=$TournamentId");
		&UpdateParents($dbh, $ParentId, $all_qnum);		
	}
	$dbh->disconnect;
}