Annotation of db/prgsrc/updateindex.pl, revision 1.6
1.1 boris 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:
1.6 ! boris 9: updateind.pl [B<-i> I<indexfile>] [B<-y>|B<-n>]
1.1 boris 10:
11:
12: =head1 DESCRIPTION
13:
1.6 ! boris 14: Upadets metainformation in the B<chgk> databse.
1.1 boris 15:
16: An example of the index file follows:
17:
1.3 boris 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:
1.1 boris 28:
1.6 ! boris 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:
1.1 boris 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:
1.6 ! boris 57: =head1 $Id: updateindex.pl,v 1.5 2000/10/19 03:05:18 boris Exp boris $
1.1 boris 58:
59: =cut
60:
61: use strict;
1.6 ! boris 62: use vars qw($opt_i $opt_h $opt_y $opt_n);
1.1 boris 63:
64: use Getopt::Std;
65: use DBI;
66:
67: MAIN:
68: {
1.6 ! boris 69: my $USAGE="Usage: updateindex.pl [-i indexfile] [-y|-n]\n";
! 70: getopts('hi:yn') or die $USAGE;
1.2 boris 71: if ($opt_h) {
72: print $USAGE;
73: exit 0;
74: }
1.6 ! boris 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;
1.2 boris 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:
1.6 ! boris 88: if ($source) {
! 89: open INFO, $source or die "Can't open input file: $!\n";
! 90: } else {
! 91: *INFO=*STDIN;
! 92: }
! 93: while (<INFO>) {
1.2 boris 94: chomp;
95: s/
//;
96: next if (/^\s*$/);
1.3 boris 97: if (s/^(\S+) *//) { # File found
1.2 boris 98: $filename = $1;
99: $depth = -1;
100: } else { # Group found
1.3 boris 101: $filename = '';
1.2 boris 102: $depth = -2;
103: }
104: s/^(\t*)//;
105: $depth += length($1);
106: if ($depth < 0) {
107: die "Wrong line $_\n";
1.1 boris 108: }
1.2 boris 109: s/^\s*//;
110: s/\s$//;
1.4 boris 111: my $title = $_;
1.2 boris 112: my $ParentId = ($depth) ? $depthId[$depth - 1] : 0;
1.6 ! boris 113: my $Id = CheckId($dbh,$title,$ParentId,$decision,$filename);
1.4 boris 114: if (!$Id || $filename) {
115: next;
1.2 boris 116: }
1.4 boris 117: $depthId[$depth] = $Id;
1.2 boris 118:
119: }
1.6 ! boris 120: print STDERR "Всего вопросов: ",
! 121: UpdateGroup($dbh,0),"\n";
1.2 boris 122: $dbh->disconnect;
1.4 boris 123: }
124:
125:
126: sub CheckId {
1.6 ! boris 127: my ($dbh,$title,$ParentId,$answer,$filename) = @_;
1.4 boris 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) {
1.6 ! boris 147: if ($answer eq 'askuser') {
! 148: print "$value is already in the DB!\n";
! 149: print "Заменить новым значением? [y/N]\n";
! 150: $answer = <STDIN>;
! 151: }
1.4 boris 152: if ($answer !~ /^[yY]/) {
1.6 ! boris 153: print STDERR "Не заменяем $value\n";
1.4 boris 154: return 0;
155: } else {
1.6 ! boris 156: print STDERR "Заменяем $value\n";
1.4 boris 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");
1.5 boris 181: $sth->execute or die $dbh->errstr;
1.4 boris 182: }
183: return $Id;
1.1 boris 184: }
1.6 ! boris 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:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>