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:
9: updateind.pl [B<-i> I<indexfile>] [B<-y>|B<-n>] [B<-r>]
10:
11:
12: =head1 DESCRIPTION
13:
14: Upadets metainformation in the B<chgk> databse.
15:
16: An example of the index file follows:
17:
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:
28:
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:
45: =item B<-r>
46:
47: Remove all entries with zero QuestionNum
48:
49: =head1 BUGS
50:
51: The database, user and password are hardcoded.
52:
53: =head1 SEE ALSO
54:
55: createindex.pl(1)
56:
57: =head1 AUTHOR
58:
59: Boris Veytsman
60:
61: =head1 $Id: updateindex.pl,v 1.8 2002/11/09 01:46:24 roma7 Exp $
62:
63: =cut
64:
65: use strict;
66: use vars qw($opt_i $opt_h $opt_y $opt_n $opt_r);
67:
68: use Getopt::Std;
69: use DBI;
70:
71: MAIN:
72: {
73: my $USAGE="Usage: updateindex.pl [-i indexfile] [-y|-n][-r]\n";
74: my $REMOVE=0;
75: getopts('hi:ynr') or die $USAGE;
76: if ($opt_h) {
77: print $USAGE;
78: exit 0;
79: }
80: my $decision='askuser';
81: if ($opt_y) {
82: $decision = 'yes';
83: }
84: if ($opt_n ) {
85: $decision = 'no';
86: }
87: if ($opt_r) {
88: $REMOVE=1;
89: }
90: my($source) = $opt_i;
91: my($depth, @depthId);
92: my $filename;
93: my($dbh) = DBI->connect("DBI:mysql:chgk", "piataev", "")
94: or die "Can't connect to DB chgk\n";
95:
96: if ($source) {
97: open INFO, $source or die "Can't open input file: $!\n";
98: } else {
99: *INFO=*STDIN;
100: }
101:
102: while (<INFO>) {
103: chomp;
104: s/
//;
105: next if (/^\s*$/);
106: if (s/^(\S+) *//) { # File found
107: $filename = $1;
108: $depth = -1;
109: } else { # Group found
110: $filename = '';
111: $depth = -2;
112: }
113: s/^(\t*)//;
114: $depth += length($1);
115: if ($depth < 0) {
116: die "Wrong line $_\n";
117: }
118: s/^\s*//;
119: s/\s$//;
120: my $title = $_;
121: my $ParentId = ($depth) ? $depthId[$depth - 1] : 0;
122: my $Id = CheckId($dbh,$title,$ParentId,$decision,$filename);
123: if (!$Id || $filename) {
124: next;
125: }
126: $depthId[$depth] = $Id;
127:
128: }
129: print STDERR "Всего вопросов: ",
130: UpdateGroup($dbh,0),"\n";
131: if ($REMOVE) {
132: print STDERR "Removing empty tours.";
133: $dbh->do("DELETE FROM Tournaments WHERE QuestionsNum=0");
134: }
135: $dbh->do("INSERT INTO Tournaments
136: (Id, Title, ParentId, Type)
137: VALUES
138: (9999, 'Несортированные турниры', 0,'Г')");
139:
140: $dbh->disconnect;
141: }
142:
143:
144: sub CheckId {
145: my ($dbh,$title,$ParentId,$answer,$filename) = @_;
146: my $type;
147: my $key;
148: my $value;
149: my $Id = 0;
150: if ($filename) {
151: $type=$dbh->quote('Ч');
152: $key = "FileName";
153: $value = $dbh->quote($filename);
154: } else {
155: $type=$dbh->quote('Г');
156: $key = "Title";
157: $value = $dbh->quote($title);
158: }
159: $title=$dbh->quote($title);
160: my $sth = $dbh->prepare("SELECT Id FROM Tournaments
161: WHERE $key=$value");
162: $sth->execute or die $dbh->errstr;
163: my @arr = $sth->fetchrow;
164: if (scalar @arr) {
165: if ($answer eq 'askuser') {
166: print "$value is already in the DB!\n";
167: print "Заменить новым значением? [y/N]\n";
168: $answer = <STDIN>;
169: }
170: if ($answer !~ /^[yY]/) {
171: print STDERR "Не заменяем $value\n";
172: return 0;
173: } else {
174: print STDERR "Заменяем $value\n";
175: $Id = $arr[0];
176: }
177: }
178: if ($Id) {
179: $sth = $dbh->prepare("UPDATE Tournaments
180: SET Title=$title, ParentId=$ParentId,
181: Type=$type
182: WHERE Id=$Id");
183:
184: } else {
185: $sth = $dbh->prepare("INSERT INTO Tournaments
186: (Title, ParentId, Type)
187: VALUES
188: ($title, $ParentId,$type)");
189: }
190: $sth->execute or die $dbh->errstr;
191: if (!$Id) {
192: $Id = $sth->{'mysql_insertid'};
193: }
194: if ($filename) {
195: $filename=$dbh->quote($filename);
196: $sth = $dbh->prepare("UPDATE Tournaments
197: SET FileName=$filename
198: WHERE Id=$Id");
199: $sth->execute or die $dbh->errstr;
200: }
201: return $Id;
202: }
203:
204: sub UpdateGroup {
205: my ($dbh,$Id) = @_;
206: my $sth = $dbh->prepare("SELECT COUNT(*) FROM Questions
207: WHERE ParentId=$Id");
208: $sth->execute;
209: my @arr=$sth->fetchrow;
210: my $result=$arr[0];
211: my @Tours = GetTours($dbh,$Id);
212: foreach my $TourId (@Tours) {
213: $result += UpdateGroup($dbh,$TourId);
214: }
215: $sth=$dbh->prepare("UPDATE Tournaments SET
216: QuestionsNum=$result
217: WHERE Id=$Id");
218: $sth->execute;
219: return $result;
220: }
221:
222: sub GetTours {
223: my ($dbh, $ParentId) = @_;
224: my (@arr, @Tours);
225:
226: my ($sth) = $dbh->prepare("SELECT Id FROM Tournaments
227: WHERE ParentId=$ParentId ORDER BY Id");
228:
229: $sth->execute;
230:
231: while (@arr = $sth->fetchrow) {
232: push @Tours, $arr[0];
233: }
234:
235: return @Tours;
236: }
237:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/cvsweb/icons/back.gif){kind=icon}
Return to updateindex.pl CVS log ![[TXT]](/cvsweb/icons/text.gif){kind=icon}
![[DIR]](/cvsweb/icons/dir.gif){kind=icon}
Up to [Local Repository] / db / prgsrc