Annotation of db/prgsrc/updatedb.pl, revision 1.1
1.1 ! boris 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: updatem.pl,v 1.2 2000/10/17 01:47:34 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: 'Янв', '0', 'Фев', 1, 'Мар', 2, 'Апр', 3, 'Май', '4',
! 34: 'Июн', '5', 'Июл', 6, 'Авг', '7', 'Сен', '8',
! 35: 'Окт', '9', 'Ноя', '19', 'Дек', '11');
! 36: my ($sth);
! 37:
! 38: use DBI;
! 39: use strict;
! 40:
! 41: sub UpdateParents {
! 42: my ($dbh, $ParentId, $all_qnum) = @_;
! 43: if ($ParentId) {
! 44: my ($sth1) = $dbh->prepare("
! 45: SELECT QuestionsNum, ParentId FROM Tournaments WHERE Id = $ParentId
! 46: ");
! 47: $sth1->execute;
! 48: my ($q, $p) = ($sth1->fetchrow)[0, 1];
! 49: $dbh->do("
! 50: UPDATE Tournaments SET QuestionsNum=$q + $all_qnum WHERE Id =
! 51: $ParentId");
! 52: &UpdateParents($dbh, $p, $all_qnum);
! 53: }
! 54: }
! 55:
! 56: sub getField {
! 57: my($desc, $dbh) = @_;
! 58: my($key);
! 59: my($value) = ('');
! 60: while (<$desc>) {
! 61: s/
//;
! 62: if ($key && /^\s*$/) {
! 63: chomp $value;
! 64: chomp $key;
! 65: if ($key eq 'Дата') {
! 66: $value =~ s/^(.*)-(.*)-(.*)$/$3-$2-$1/;
! 67: my($month) = $RevMonths{$2};
! 68: $value =~ s/$2/$month/;
! 69: }
! 70: $value = $dbh->quote($value);
! 71: return ($key, $value);
! 72: }
! 73: next if (/^\s*$/);
! 74:
! 75: if (/^(.*):\s*$/ && !$key) {
! 76: $key = $1;
! 77: next;
! 78: }
! 79: if ($key) {
! 80: $value .= $_;
! 81: next;
! 82: }
! 83: }
! 84: if ($key && $value) {
! 85: $value = $dbh->quote($value);
! 86: return ($key, $value);
! 87: }
! 88: return (0, 0);
! 89: }
! 90:
! 91: sub SelectGroup {
! 92: my ($dbh, $TourName, $sth, $ParentId, $i, @arr) = @_;
! 93:
! 94: $sth = $dbh->prepare("SELECT Id, Title FROM
! 95: Tournaments WHERE Type = 'Г'");
! 96: $sth->execute;
! 97: print "Выберите группу для турнира:\n$TourName\n\n";
! 98: for ($i = 0; $i < $sth->numrows; $i++) {
! 99: @arr = $sth->fetchrow;
! 100: print "[$arr[0]] $arr[1]\n";
! 101: }
! 102: $ParentId = <STDIN>;
! 103: print "Вы выбрали турнир: $ParentId\n";
! 104: return $ParentId;
! 105: }
! 106:
! 107: sub UpdateTournament {
! 108: my ($dbh, $TournamentId, $field, $value) = @_;
! 109: $dbh->do("UPDATE Tournaments SET $field=$value WHERE Id=$TournamentId")
! 110: or die $dbh->errstr;
! 111: }
! 112:
! 113: sub UpdateQuestion {
! 114: my ($dbh, $QuestionId, $field, $value) = @_;
! 115: $dbh->do("UPDATE Questions SET $field=$value
! 116: WHERE QuestionId=$QuestionId")
! 117: or die $dbh->errstr;
! 118: }
! 119:
! 120: MAIN:
! 121: {
! 122: my($key, $value, $addition);
! 123:
! 124: my($source);
! 125:
! 126: my($dbh) = DBI->connect("DBI:mysql:chgk", "piataev", "")
! 127: or die "Can't connect to DB chgk\n";
! 128:
! 129: while ($source = shift) {
! 130: my($PlayedAt) = '';
! 131: my($QuestionId, $TourId, $TournamentId, $ParentId) = (0, 0, 0, 0);
! 132: my($tournum, $qnum, $all_qnum, $qtype) = (0, 0, 0, 'Ч');
! 133: my (@d) = (localtime((stat($source))[9]))[5,4,3];
! 134: $d[1]++;
! 135: my ($CreatedAt) = $dbh->quote( join('-', @d));
! 136: print "File created on: $CreatedAt\n";
! 137:
! 138: open INFD, $source
! 139: or die "Can't open input file: $!\n";
! 140:
! 141: $source =~ s/^.*\/([^\/]*)$/$1/;
! 142: $source = $dbh->quote($source);
! 143: print "Processing file: $source \n";
! 144:
! 145: while (($key, $value) = getField(\*INFD, $dbh)) {
! 146: last if (!$key);
! 147:
! 148: if ($key =~ /Мета/) {
! 149: $value =~ s/[^\d]*//g;
! 150: $sth = $dbh->prepare("SELECT Id FROM Tournaments WHERE
! 151: MetaId=$value");
! 152: $sth->execute
! 153: or die "Invalid Meta field: $value";
! 154: $ParentId = ($sth->fetchrow)[0];
! 155: next;
! 156: }
! 157: if ($key =~ /Чемпионат/) {
! 158: $ParentId = &SelectGroup($dbh, $value)
! 159: unless ($ParentId);
! 160: $sth = $dbh->prepare("INSERT INTO Tournaments
! 161: (Title, Type, ParentId, FileName, CreatedAt)
! 162: VALUES ($value, 'Ч', $ParentId, $source,
! 163: $CreatedAt)");
! 164: $sth->execute;
! 165: $TournamentId = $sth->{insertid};
! 166: next;
! 167: }
! 168: if ($key =~ /Тур/) {
! 169: if ($TourId) {
! 170: $dbh->do("UPDATE Tournaments SET QuestionsNum=$qnum
! 171: WHERE Id=$TourId");
! 172: }
! 173: $qnum = 0;
! 174: $qtype = "'Ч'";
! 175: $sth = $dbh->prepare("INSERT INTO Tournaments
! 176: (Title, Type, ParentId, CreatedAt)
! 177: VALUES ($value, 'Т', $TournamentId, $CreatedAt)");
! 178: $sth->execute;
! 179: $TourId = $sth->{insertid};
! 180: next;
! 181: }
! 182: if ($key =~ /Вид/) {
! 183: $qtype = $value;
! 184: next;
! 185: }
! 186: if ($key =~ /Вопрос/) {
! 187: $sth = $dbh->prepare("INSERT INTO Questions
! 188: (ParentId, Number, Type)
! 189: VALUES ($TourId, $qnum+1, $qtype)");
! 190: $sth->execute;
! 191: $QuestionId = $sth->{insertid};
! 192: &UpdateQuestion($dbh, $QuestionId, "Question", $value);
! 193: $qnum++;
! 194: $all_qnum++;
! 195: next;
! 196: }
! 197: &UpdateQuestion($dbh, $QuestionId, "Answer", $value)
! 198: if ($key =~ /Ответ/);
! 199:
! 200: &UpdateQuestion($dbh, $QuestionId, "Authors", $value)
! 201: if ($key =~ /Автор/);
! 202:
! 203: &UpdateQuestion($dbh, $QuestionId, "Sources", $value)
! 204: if ($key =~ /Источник/);
! 205:
! 206: &UpdateQuestion($dbh, $QuestionId, "Comments", $value)
! 207: if ($key =~ /Комментарий/);
! 208:
! 209: &UpdateTournament($dbh, $TournamentId, "URL", $value)
! 210: if ($key =~ /URL/);
! 211:
! 212: &UpdateTournament($dbh, $TournamentId, "Copyright", $value)
! 213: if ($key =~ /Копирайт/);
! 214:
! 215: &UpdateTournament($dbh, $TournamentId, "Info", $value)
! 216: if ($key =~ /Инфо/);
! 217:
! 218: if ($key =~ /Дата/) {
! 219: if ($TourId) {
! 220: &UpdateTournament($dbh, $TourId, "PlayedAt", $value);
! 221: } else {
! 222: &UpdateTournament($dbh, $TournamentId, "PlayedAt", $value);
! 223: }
! 224: }
! 225: }
! 226: $dbh->do("UPDATE Tournaments SET QuestionsNum=$qnum
! 227: WHERE Id=$TourId");
! 228: $dbh->do("UPDATE Tournaments SET QuestionsNum=$all_qnum
! 229: WHERE Id=$TournamentId");
! 230: &UpdateParents($dbh, $ParentId, $all_qnum);
! 231: }
! 232: $dbh->disconnect;
! 233: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/cvsweb/icons/back.gif){kind=icon}
Return to updatedb.pl CVS log ![[TXT]](/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvsweb/icons/dir.gif){kind=icon}
Up to [Local Repository] / db / prgsrc