[npl] / trunk / NationalProblemLibrary / loadDB2 Repository:
ViewVC logotype

Diff of /trunk/NationalProblemLibrary/loadDB2

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

Revision 441 Revision 442
… …
4use File::Find; 4use File::Find;
5use File::Basename; 5use File::Basename;
6use Cwd; 6use Cwd;
7use DBI; 7use DBI;
8 8
9if(! -e "create_tables2.sql") { 9if (! -e "create_tables2.sql") {
10 print "Cannot find file create_tables2.sql.\n"; 10 print "Cannot find file create_tables2.sql.\n";
11 print "This needs to be run from the directory it was unpacked to,\n"; 11 print "This needs to be run from the directory it was unpacked to,\n";
12 print "in which case \"create_tables2.sql\" should be in this directory.\n"; 12 print "in which case \"create_tables2.sql\" should be in this directory.\n";
13 exit; 13 exit;
14} 14}
15 15
16my $passwd; 16my $passwd;
17 17
18if(@ARGV != 1) { 18if (@ARGV != 1) {
19 print "What is the root password for mysql? "; 19 print "What is the root password for mysql? ";
20 $passwd = <STDIN>; 20 $passwd = <STDIN>;
21 chomp($passwd); 21 chomp($passwd);
22} else { 22} else {
23 $passwd = $ARGV[0]; 23 $passwd = $ARGV[0];
… …
53my ($pgfile,$pgpath,@keyword,$tagged,$chapter,$section,$institution,$author,$text,$edition,$date,$textauthor,$textsection,$textproblem); 53my ($pgfile,$pgpath,@keyword,$tagged,$chapter,$section,$institution,$author,$text,$edition,$date,$textauthor,$textsection,$textproblem);
54 54
55#recursive search for all pg files 55#recursive search for all pg files
56my $dbh = DBI->connect('dbi:mysql:ProblemLibrary', $user, $passwd); 56my $dbh = DBI->connect('dbi:mysql:ProblemLibrary', $user, $passwd);
57 57
58$dbh->{RaiseError} = 1; #bail if anything fails via die 58$dbh->{RaiseError} = 1; #bail if anything fails via die
59 59
60find({ wanted => \&pgfiles, follow_fast=> 1}, $libraryRoot); 60find({ wanted => \&pgfiles, follow_fast=> 1}, $libraryRoot);
61 61
62sub kwtidy { 62sub kwtidy {
63 my $s = shift; 63 my $s = shift;
… …
73 my @spl2 = map(kwtidy($_), @spl1); 73 my @spl2 = map(kwtidy($_), @spl1);
74 return(@spl2); 74 return(@spl2);
75} 75}
76 76
77sub dbug { 77sub dbug {
78 my $msg = shift; 78 my $msg = shift;
79 $cnt++; 79 $cnt++;
80 print $msg if($verbose>1); 80 print $msg if($verbose>1);
81# if($cnt==5000) { print "\n"; $cnt=0;} 81 # if($cnt==5000) { print "\n"; $cnt=0;}
82 print "." if($verbose==1 and ($cnt % 100 == 0)); 82 print "." if($verbose==1 and ($cnt % 100 == 0));
83} 83}
84 84
85# process each file returned by the find command. 85# process each file returned by the find command.
86sub pgfiles { 86sub pgfiles {
87 my $name = $File::Find::name; 87 my $name = $File::Find::name;
88 my ($subject, $chapter, $section, $date, $institution, $author, $text); 88 my ($subject, $chapter, $section, $date, $institution, $author, $text);
89 my ($edition, $textauthor, $textsection, $textproblem); 89 my ($edition, $textauthor, $textsection, $textproblem);
90 my @textproblems = (-1); 90 my @textproblems = (-1);
91 if($name =~ /pg$/) { 91 if ($name =~ /pg$/) {
92 $pgfile = basename($name); 92 $pgfile = basename($name);
93 $pgpath = dirname($name); 93 $pgpath = dirname($name);
94 $pgpath =~ s|^$libraryRoot/||; 94 $pgpath =~ s|^$libraryRoot/||;
95 open(IN,"$name") or die "can not open $name: $!"; 95 open(IN,"$name") or die "can not open $name: $!";
96 $tagged = 0; 96 $tagged = 0;
97 while(<IN>) { 97 while (<IN>) {
98 SWITCH: { 98 SWITCH: {
99 if(/KEYWORDS\((.*)\)/i) { 99 if (/KEYWORDS\((.*)\)/i) {
100 @keyword = keywordcleaner($1); 100 @keyword = keywordcleaner($1);
101 last SWITCH; 101 last SWITCH;
102 } 102 }
103 if(/DBsubject\(\s*'(.*?)'\s*\)/) { 103 if (/DBsubject\(\s*'(.*?)'\s*\)/) {
104 $subject = $1; 104 $subject = $1;
105 $subject =~ s/'/\'/g; 105 $subject =~ s/'/\'/g;
106 last SWITCH; 106 last SWITCH;
107 } 107 }
108 if(/DBchapter\(\s*'(.*?)'\s*\)/) { 108 if (/DBchapter\(\s*'(.*?)'\s*\)/) {
109 $chapter = $1; 109 $chapter = $1;
110 $chapter =~ s/'/\'/g; 110 $chapter =~ s/'/\'/g;
111 $tagged = 1; 111 $tagged = 1;
112 last SWITCH; 112 last SWITCH;
113 } 113 }
114 if(/DBsection\(\s*'(.*?)'\s*\)/) { 114 if (/DBsection\(\s*'(.*?)'\s*\)/) {
115 $section = $1; 115 $section = $1;
116 $section =~ s/'/\'/g; 116 $section =~ s/'/\'/g;
117 last SWITCH; 117 last SWITCH;
118 } 118 }
119 if(/Date\(\s*'(.*?)'\s*\)/) { 119 if (/Date\(\s*'(.*?)'\s*\)/) {
120 $date = $1; 120 $date = $1;
121 $date =~ s/'/\'/g; 121 $date =~ s/'/\'/g;
122 last SWITCH; 122 last SWITCH;
123 } 123 }
124 if(/Institution\(\s*'(.*?)'\s*\)/) { 124 if (/Institution\(\s*'(.*?)'\s*\)/) {
125 $institution = $1; 125 $institution = $1;
126 $institution =~ s/'/\'/g; 126 $institution =~ s/'/\'/g;
127 last SWITCH; 127 last SWITCH;
128 } 128 }
129 if(/Author\(\s*'(.*?)'\s*\)/) { 129 if (/Author\(\s*'(.*?)'\s*\)/) {
130 $author = $1; 130 $author = $1;
131 $author =~ s/'/\'/g; 131 $author =~ s/'/\'/g;
132 last SWITCH; 132 last SWITCH;
133 } 133 }
134 if(/TitleText1\(\s*'(.*?)'\s*\)/) { 134 if (/TitleText1\(\s*'(.*?)'\s*\)/) {
135 $text = $1; 135 $text = $1;
136 $text =~ s/'/\'/g; 136 $text =~ s/'/\'/g;
137 last SWITCH; 137 last SWITCH;
138 } 138 }
139 if(/EditionText1\(\s*'(.*?)'\s*\)/) 139 if (/EditionText1\(\s*'(.*?)'\s*\)/) {
140 {
141 $edition = $1; 140 $edition = $1;
142 $edition =~ s/'/\'/g; 141 $edition =~ s/'/\'/g;
143 last SWITCH; 142 last SWITCH;
144 } 143 }
145 if(/AuthorText1\(\s*'(.*?)'\s*\)/) 144 if (/AuthorText1\(\s*'(.*?)'\s*\)/) {
146 {
147 $textauthor = $1; 145 $textauthor = $1;
148 $textauthor =~ s/'/\'/g; 146 $textauthor =~ s/'/\'/g;
149 last SWITCH; 147 last SWITCH;
150 } 148 }
151 if(/Section1\(\s*'(.*?)'\s*\)/) 149 if (/Section1\(\s*'(.*?)'\s*\)/) {
152 {
153 $textsection = $1; 150 $textsection = $1;
154 $textsection =~ s/'/\'/g; 151 $textsection =~ s/'/\'/g;
155 last SWITCH; 152 last SWITCH;
156 } 153 }
157 if(/Problem1\(\s*'(.*?)'\s*\)/) 154 if (/Problem1\(\s*'(.*?)'\s*\)/) {
158 {
159 $textproblem = $1; 155 $textproblem = $1;
160 $textproblem =~ s/\D/ /g; 156 $textproblem =~ s/\D/ /g;
161 @textproblems = split /\s+/, $textproblem; 157 @textproblems = split /\s+/, $textproblem;
162 @textproblems = grep { $_ =~ /\S/ } $textproblem; 158 @textproblems = grep { $_ =~ /\S/ } $textproblem;
163 @textproblems = (-1) unless(scalar(@textproblems)); 159 @textproblems = (-1) unless(scalar(@textproblems));
164#print "$textproblem\n" if ($textproblem !~ /^[\s\d]+$/); 160 #print "$textproblem\n" if ($textproblem !~ /^[\s\d]+$/);
165# $textproblem =~ s/'/\'/g; 161 # $textproblem =~ s/'/\'/g;
166 last SWITCH; 162 last SWITCH;
163 }
167 } 164 }
168 }}#end of SWITCH and while 165 } #end of SWITCH and while
169 if($tagged and $chapter eq 'ZZZ-Inserted Text') { $tagged=0;} 166 if ($tagged and $chapter eq 'ZZZ-Inserted Text') {
167 $tagged=0;
168 }
170 if($tagged) { 169 if ($tagged) {
171 # 170 #
172 # kludge to fix the omission of a subject field 171 # kludge to fix the omission of a subject field
173 unless($subject) { 172 unless($subject) {
174 if($text =~ /precalculus/i) { $subject = "Precalculus" } 173 if ($text =~ /precalculus/i) {
175 elsif($text =~ /calculus/i) { $subject = "Calculus" } 174 $subject = "Precalculus";
176 elsif($text =~ /linear/i) { $subject = "Linear Algebra" } 175 } elsif ($text =~ /calculus/i) {
177 elsif($text =~ /algebra/i) { $subject = "Algebra" } 176 $subject = "Calculus";
178 elsif($text =~ /statistic/i) { $subject = "Statistics" } 177 } elsif ($text =~ /linear/i) {
179 elsif($text =~ /financial/i) { $subject = "Financial Mathematics" } 178 $subject = "Linear Algebra";
180 else { $subject = "Misc" } 179 } elsif ($text =~ /algebra/i) {
180 $subject = "Algebra";
181 } elsif ($text =~ /statistic/i) {
182 $subject = "Statistics";
183 } elsif ($text =~ /financial/i) {
184 $subject = "Financial Mathematics";
185 } else {
186 $subject = "Misc";
187 }
181 } 188 }
182 # From the pgfile we just looked at, 189 # From the pgfile we just looked at,
183 ## DBchapter('Limits and Derivatives') in $chapter 190 ## DBchapter('Limits and Derivatives') in $chapter
184 ## DBsection('Calculating Limits using the Limit Laws') in $section 191 ## DBsection('Calculating Limits using the Limit Laws') in $section
185 ## Date('6/3/2002') in $date 192 ## Date('6/3/2002') in $date
… …
193 # 200 #
194 # The database structure is in the file create_tables2.sql and its ER-graph 201 # The database structure is in the file create_tables2.sql and its ER-graph
195 # is the file wwdb_er_graph.pdf. Insert, in order, into the tables 202 # is the file wwdb_er_graph.pdf. Insert, in order, into the tables
196 # in that file. 203 # in that file.
197 # 204 #
198 if($textsection =~ /(\d*?)\.(\d*)/) { 205 if ($textsection =~ /(\d*?)\.(\d*)/) {
199 $chapnum = $1; 206 $chapnum = $1;
200 $secnum = $2; 207 $secnum = $2;
201 } else { 208 } else {
202 $chapnum = undef; #no valid number assigned 209 $chapnum = undef; #no valid number assigned
203 $secnum = $textsection; 210 $secnum = $textsection;
204 }; 211 }
212 ;
205 213
206 #selectrow_array returns first field of first row in scalar context or undef 214 #selectrow_array returns first field of first row in scalar context or undef
207 # undef for failure also, $dbh->{RaiseError} = 1 should catch that case. 215 # undef for failure also, $dbh->{RaiseError} = 1 should catch that case.
208 # 216 #
209 # DBsubject table 217 # DBsubject table
210 # 218 #
211 $query = "SELECT DBsubject_id FROM DBsubject WHERE name = \"$subject\""; 219 $query = "SELECT DBsubject_id FROM DBsubject WHERE name = \"$subject\"";
212 my $DBsubject_id = $dbh->selectrow_array($query); 220 my $DBsubject_id = $dbh->selectrow_array($query);
213 if(!defined($DBsubject_id)){ 221 if (!defined($DBsubject_id)) {
214 $dbh->do( 222 $dbh->do(
215 "INSERT INTO DBsubject 223 "INSERT INTO DBsubject
216 VALUES( 224 VALUES(
217 \"\", 225 \"\",
218 \"$subject\" 226 \"$subject\"
219 )" 227 )"
220 ); 228 );
221 dbug "INSERT INTO DBsubject VALUES(\"\",\"$subject\")\n"; 229 dbug "INSERT INTO DBsubject VALUES(\"\",\"$subject\")\n";
222 $DBsubject_id = $dbh->selectrow_array($query); 230 $DBsubject_id = $dbh->selectrow_array($query);
223 } 231 }
224 232
225 # DBchapter table 233 # DBchapter table
226 # 234 #
227 $query = "SELECT DBchapter_id FROM DBchapter WHERE name = \"$chapter\" and DBsubject_id = $DBsubject_id"; 235 $query = "SELECT DBchapter_id FROM DBchapter WHERE name = \"$chapter\" and DBsubject_id = $DBsubject_id";
228 my $DBchapter_id = $dbh->selectrow_array($query); 236 my $DBchapter_id = $dbh->selectrow_array($query);
229 if(!defined($DBchapter_id)){ 237 if (!defined($DBchapter_id)) {
230 $dbh->do("INSERT INTO DBchapter 238 $dbh->do("INSERT INTO DBchapter
231 VALUES( 239 VALUES(
232 \"\", 240 \"\",
233 \"$chapter\", 241 \"$chapter\",
234 \"$DBsubject_id\" 242 \"$DBsubject_id\"
235 )" 243 )"
236 ); 244 );
237 dbug "INSERT INTO DBchapter VALUES( \"\", \"$chapter\", \"$DBsubject_id\)\n"; 245 dbug "INSERT INTO DBchapter VALUES( \"\", \"$chapter\", \"$DBsubject_id\)\n";
238 $DBchapter_id = $dbh->selectrow_array($query); 246 $DBchapter_id = $dbh->selectrow_array($query);
239 } 247 }
240 248
241 # DBsection table 249 # DBsection table
242 # 250 #
243 $query = "SELECT DBsection_id FROM DBsection WHERE name = \"$section\" AND DBchapter_id = $DBchapter_id"; 251 $query = "SELECT DBsection_id FROM DBsection WHERE name = \"$section\" AND DBchapter_id = $DBchapter_id";
244 my $DBsection_id = $dbh->selectrow_array($query); 252 my $DBsection_id = $dbh->selectrow_array($query);
245 if(!defined($DBsection_id)){ 253 if (!defined($DBsection_id)) {
246 $dbh->do("INSERT INTO DBsection 254 $dbh->do("INSERT INTO DBsection
247 VALUES( 255 VALUES(
248 \"\", 256 \"\",
249 \"$section\", 257 \"$section\",
250 \"$DBchapter_id\" 258 \"$DBchapter_id\"
251 )" 259 )"
252 ); 260 );
253 dbug "INSERT INTO DBsection VALUES( \"\", \"$section\", \"$DBchapter_id\", \"$DBsubject_id\" )\n"; 261 dbug "INSERT INTO DBsection VALUES( \"\", \"$section\", \"$DBchapter_id\", \"$DBsubject_id\" )\n";
254 $DBsection_id = $dbh->selectrow_array($query); 262 $DBsection_id = $dbh->selectrow_array($query);
255 } 263 }
256 264
257 # institution table 265 # institution table
258 # 266 #
259 $query = "SELECT institution_id FROM institution WHERE name = \"$institution\""; 267 $query = "SELECT institution_id FROM institution WHERE name = \"$institution\"";
260 my $institution_id = $dbh->selectrow_array($query); 268 my $institution_id = $dbh->selectrow_array($query);
261 if(!defined($institution_id)){ 269 if (!defined($institution_id)) {
262 $dbh->do("INSERT INTO institution 270 $dbh->do("INSERT INTO institution
263 VALUES( 271 VALUES(
264 \"\", 272 \"\",
265 \"$institution\", 273 \"$institution\",
266 \"\", 274 \"\",
267 \"\", 275 \"\",
268 \"\", 276 \"\",
269 \"\", 277 \"\",
270 \"\", 278 \"\",
271 \"\" 279 \"\"
272 )" 280 )"
273 ); 281 );
274 dbug "INSERT INTO institution VALUES( \"\", \"$institution\", \"\", \"\", \"\", \"\", \"\", \"\")\n"; 282 dbug "INSERT INTO institution VALUES( \"\", \"$institution\", \"\", \"\", \"\", \"\", \"\", \"\")\n";
275 $institution_id = $dbh->selectrow_array($query); 283 $institution_id = $dbh->selectrow_array($query);
276 } 284 }
277 285
278 # author table 286 # author table
279 # 287 #
280 $author =~ /(.*?)\s(\w+)\s*$/; 288 $author =~ /(.*?)\s(\w+)\s*$/;
… …
283 #remove leading and trailing spaces from firstname, which includes any middle name too. 291 #remove leading and trailing spaces from firstname, which includes any middle name too.
284 $firstname =~ s/^\s*//; 292 $firstname =~ s/^\s*//;
285 $firstname =~ s/\s*$//; 293 $firstname =~ s/\s*$//;
286 $query = "SELECT author_id FROM author WHERE lastname = \"$lastname\" AND firstname=\"$firstname\""; 294 $query = "SELECT author_id FROM author WHERE lastname = \"$lastname\" AND firstname=\"$firstname\"";
287 my $author_id = $dbh->selectrow_array($query); 295 my $author_id = $dbh->selectrow_array($query);
288 if(!defined($author_id)){ 296 if (!defined($author_id)) {
289 $dbh->do("INSERT INTO author 297 $dbh->do("INSERT INTO author
290 VALUES( 298 VALUES(
291 \"\", 299 \"\",
292 \"$institution_id\", 300 \"$institution_id\",
293 \"$lastname\", 301 \"$lastname\",
294 \"$firstname\", 302 \"$firstname\",
295 \"\" 303 \"\"
296 )" 304 )"
297 ); 305 );
298 dbug "INSERT INTO author VALUES( \"\", \"$institution_id\", \"$lastname\", \"$firstname\", \"\" )\n"; 306 dbug "INSERT INTO author VALUES( \"\", \"$institution_id\", \"$lastname\", \"$firstname\", \"\" )\n";
299 $author_id = $dbh->selectrow_array($query); 307 $author_id = $dbh->selectrow_array($query);
300 } 308 }
301 309
302 # path table 310 # path table
303 # 311 #
304 $query = "SELECT path_id FROM path WHERE path = \"$pgpath\""; 312 $query = "SELECT path_id FROM path WHERE path = \"$pgpath\"";
305 my $path_id = $dbh->selectrow_array($query); 313 my $path_id = $dbh->selectrow_array($query);
306 if(!defined($path_id)){ 314 if (!defined($path_id)) {
307 $dbh->do("INSERT INTO path 315 $dbh->do("INSERT INTO path
308 VALUES( 316 VALUES(
309 \"\", 317 \"\",
310 \"$pgpath\", 318 \"$pgpath\",
311 \"\", 319 \"\",
312 \"\" 320 \"\"
313 )" 321 )"
314 ); 322 );
315 dbug "INSERT INTO path VALUES( \"\", \"$path\", \"\", \"\" )\n"; 323 dbug "INSERT INTO path VALUES( \"\", \"$path\", \"\", \"\" )\n";
316 $path_id = $dbh->selectrow_array($query); 324 $path_id = $dbh->selectrow_array($query);
317 } 325 }
318 326
319 # pgfile table 327 # pgfile table
320 # 328 #
321 my $pgfile_id; 329 my $pgfile_id;
… …
327 \"$institution_id\", 335 \"$institution_id\",
328 \"$path_id\", 336 \"$path_id\",
329 \"$pgfile\", 337 \"$pgfile\",
330 \"\" 338 \"\"
331 )" 339 )"
332 ); 340 );
333 dbug "INSERT INTO pgfile VALUES( \"\", \"$DBsection_id\", \"$author_id\", \"$institution_id\", \"$path_id\", \"$pgfile\", \"\" )\n"; 341 dbug "INSERT INTO pgfile VALUES( \"\", \"$DBsection_id\", \"$author_id\", \"$institution_id\", \"$path_id\", \"$pgfile\", \"\" )\n";
334 $query = "SELECT pgfile_id FROM pgfile WHERE filename = \"$pgfile\" and path_id=$path_id"; 342 $query = "SELECT pgfile_id FROM pgfile WHERE filename = \"$pgfile\" and path_id=$path_id";
335 $pgfile_id = $dbh->selectrow_array($query); 343 $pgfile_id = $dbh->selectrow_array($query);
336 344
337 # keyword table, and problem_keyword many-many table 345 # keyword table, and problem_keyword many-many table
338 # 346 #
339 foreach my $keyword (@keyword) 347 foreach my $keyword (@keyword) {
340 {
341 $keyword =~ s/[\'\"]//g; 348 $keyword =~ s/[\'\"]//g;
342 $query = "SELECT keyword_id FROM keyword WHERE keyword = \"$keyword\""; 349 $query = "SELECT keyword_id FROM keyword WHERE keyword = \"$keyword\"";
343 my $keyword_id = $dbh->selectrow_array($query); 350 my $keyword_id = $dbh->selectrow_array($query);
344 if(!defined($keyword_id)){ 351 if (!defined($keyword_id)) {
345 $dbh->do("INSERT INTO keyword 352 $dbh->do("INSERT INTO keyword
346 VALUES( 353 VALUES(
347 \"\", 354 \"\",
348 \"$keyword\" 355 \"$keyword\"
349 )" 356 )"
350 ); 357 );
351 dbug "INSERT INTO keyword VALUES( \"\", \"$keyword\")\n"; 358 dbug "INSERT INTO keyword VALUES( \"\", \"$keyword\")\n";
352 $keyword_id = $dbh->selectrow_array($query); 359 $keyword_id = $dbh->selectrow_array($query);
353 } 360 }
354 361
355 $query = "SELECT pgfile_id FROM pgfile_keyword WHERE keyword_id = \"$keyword_id\" and pgfile_id=\"$pgfile_id\""; 362 $query = "SELECT pgfile_id FROM pgfile_keyword WHERE keyword_id = \"$keyword_id\" and pgfile_id=\"$pgfile_id\"";
356 my $ok = $dbh->selectrow_array($query); 363 my $ok = $dbh->selectrow_array($query);
357 if(!defined($ok)){ 364 if (!defined($ok)) {
358 $dbh->do("INSERT INTO pgfile_keyword 365 $dbh->do("INSERT INTO pgfile_keyword
359 VALUES( 366 VALUES(
360 \"$pgfile_id\", 367 \"$pgfile_id\",
361 \"$keyword_id\" 368 \"$keyword_id\"
362 )" 369 )"
363 ); 370 );
364 dbug "INSERT INTO pgfile_keyword VALUES( \"$pgfile_id\", \"$keyword_id\" )\n"; 371 dbug "INSERT INTO pgfile_keyword VALUES( \"$pgfile_id\", \"$keyword_id\" )\n";
365 } 372 }
366 } #end foreach keyword 373 } #end foreach keyword
367 374
368 # pgfile_institution table 375 # pgfile_institution table
369 # 376 #
370 $query = "SELECT institution_id FROM pgfile_institution WHERE pgfile_id = \"$pgfile_id\""; 377 $query = "SELECT institution_id FROM pgfile_institution WHERE pgfile_id = \"$pgfile_id\"";
371 my $pg_inst_id = $dbh->selectrow_array($query); 378 my $pg_inst_id = $dbh->selectrow_array($query);
372 if(!defined($pg_inst_id)){ 379 if (!defined($pg_inst_id)) {
373 $dbh->do("INSERT INTO pgfile_institution 380 $dbh->do("INSERT INTO pgfile_institution
374 VALUES( 381 VALUES(
375 \"$pgfile_id\", 382 \"$pgfile_id\",
376 \"$institution_id\" 383 \"$institution_id\"
377 )" 384 )"
378 ); 385 );
379 dbug "INSERT INTO pgfile_institution VALUES( \"$pgfile_id\", \"$institution_id\" )\n"; 386 dbug "INSERT INTO pgfile_institution VALUES( \"$pgfile_id\", \"$institution_id\" )\n";
380 $pg_inst_id = $dbh->selectrow_array($query); 387 $pg_inst_id = $dbh->selectrow_array($query);
381 } 388 }
382 389
383 # textbook table 390 # textbook table
384 # 391 #
385if($text) { 392 if ($text) {
386 $query = "SELECT textbook_id FROM textbook WHERE title = \"$text\" AND edition = \"$edition\" AND author=\"$textauthor\""; 393 $query = "SELECT textbook_id FROM textbook WHERE title = \"$text\" AND edition = \"$edition\" AND author=\"$textauthor\"";
387 my $textbook_id = $dbh->selectrow_array($query); 394 my $textbook_id = $dbh->selectrow_array($query);
388 if(!defined($textbook_id)){ 395 if (!defined($textbook_id)) {
389 $dbh->do("INSERT INTO textbook 396 $dbh->do("INSERT INTO textbook
390 VALUES( 397 VALUES(
391 \"\", 398 \"\",
392 \"$text\", 399 \"$text\",
393 \"$edition\", 400 \"$edition\",
394 \"$textauthor\", 401 \"$textauthor\",
395 \"\", 402 \"\",
396 \"\", 403 \"\",
397 \"\" 404 \"\"
398 )" 405 )"
399 ); 406 );
400 dbug "INSERT INTO textbook VALUES( \"\", \"$text\", \"$edition\", \"$textauthor\", \"\", \"\", \"\" )\n"; 407 dbug "INSERT INTO textbook VALUES( \"\", \"$text\", \"$edition\", \"$textauthor\", \"\", \"\", \"\" )\n";
401 $textbook_id = $dbh->selectrow_array($query); 408 $textbook_id = $dbh->selectrow_array($query);
402 } 409 }
403 410
404 # chapter weak table of textbook 411 # chapter weak table of textbook
405 # 412 #
406 $query = "SELECT chapter_id FROM chapter WHERE textbook_id = \"$textbook_id\" AND name = \"$chapter\""; 413 $query = "SELECT chapter_id FROM chapter WHERE textbook_id = \"$textbook_id\" AND name = \"$chapter\"";
407 my $chapter_id = $dbh->selectrow_array($query); 414 my $chapter_id = $dbh->selectrow_array($query);
408 if(!defined($chapter_id)){ 415 if (!defined($chapter_id)) {
409 $dbh->do("INSERT INTO chapter 416 $dbh->do("INSERT INTO chapter
410 VALUES( 417 VALUES(
411 \"\", 418 \"\",
412 \"$textbook_id\", 419 \"$textbook_id\",
413 \"".$chapnum."\", 420 \"".$chapnum."\",
414 \"$chapter\", 421 \"$chapter\",
415 \"\" 422 \"\"
416 )" 423 )"
417 ); 424 );
418 dbug "INSERT INTO chapter VALUES(\"\", \"$textbook_id\", \"".$chapnum."\", \"$chapter\", \"\" )\n"; 425 dbug "INSERT INTO chapter VALUES(\"\", \"$textbook_id\", \"".$chapnum."\", \"$chapter\", \"\" )\n";
419 $chapter_id = $dbh->selectrow_array($query); 426 $chapter_id = $dbh->selectrow_array($query);
420 } 427 }
421 428
422 # section weak table of textbook 429 # section weak table of textbook
423 # 430 #
424 $query = "SELECT section_id FROM section WHERE chapter_id = \"$chapter_id\" AND name = \"$section\""; 431 $query = "SELECT section_id FROM section WHERE chapter_id = \"$chapter_id\" AND name = \"$section\"";
425 my $section_id = $dbh->selectrow_array($query); 432 my $section_id = $dbh->selectrow_array($query);
426 if(!defined($section_id)){ 433 if (!defined($section_id)) {
427 $dbh->do("INSERT INTO section 434 $dbh->do("INSERT INTO section
428 VALUES( 435 VALUES(
429 \"\", 436 \"\",
430 \"$chapter_id\", 437 \"$chapter_id\",
431 \"$secnum\", 438 \"$secnum\",
432 \"$section\", 439 \"$section\",
433 \"\" 440 \"\"
434 )" 441 )"
435 ); 442 );
436 dbug "INSERT INTO section VALUES(\"\", \"$textbook_id\", \"$secnum\", \"$section\", \"\" )\n"; 443 dbug "INSERT INTO section VALUES(\"\", \"$textbook_id\", \"$secnum\", \"$section\", \"\" )\n";
437 $section_id = $dbh->selectrow_array($query); 444 $section_id = $dbh->selectrow_array($query);
438 } 445 }
439 446
440 # problem table contains textbook problems 447 # problem table contains textbook problems
441 # 448 #
442 for my $tp (@textproblems) { 449 for my $tp (@textproblems) {
443 $query = "SELECT problem_id FROM problem WHERE section_id = \"$section_id\" AND number = \"$tp\""; 450 $query = "SELECT problem_id FROM problem WHERE section_id = \"$section_id\" AND number = \"$tp\"";
444 my $problem_id = $dbh->selectrow_array($query); 451 my $problem_id = $dbh->selectrow_array($query);
445 if(!defined($problem_id)){ 452 if (!defined($problem_id)) {
446 $dbh->do("INSERT INTO problem 453 $dbh->do("INSERT INTO problem
447 VALUES( 454 VALUES(
448 \"\", 455 \"\",
449 \"$section_id\", 456 \"$section_id\",
450 \"$tp\", 457 \"$tp\",
451 \"\" 458 \"\"
452 )" 459 )"
453 ); 460 );
454 dbug "INSERT INTO problem VALUES( \"\", \"$section_id\", \"$tp\", \"\" )\n"; 461 dbug "INSERT INTO problem VALUES( \"\", \"$section_id\", \"$tp\", \"\" )\n";
455 $problem_id = $dbh->selectrow_array($query); 462 $problem_id = $dbh->selectrow_array($query);
456 } 463 }
457 464
458 # pgfile_problem table associates pgfiles with textbook problems 465 # pgfile_problem table associates pgfiles with textbook problems
459 # 466 #
460 $query = "SELECT problem_id FROM pgfile_problem WHERE problem_id = \"$problem_id\" AND pgfile_id = \"$pgfile_id\""; 467 $query = "SELECT problem_id FROM pgfile_problem WHERE problem_id = \"$problem_id\" AND pgfile_id = \"$pgfile_id\"";
461 my $pg_problem_id = $dbh->selectrow_array($query); 468 my $pg_problem_id = $dbh->selectrow_array($query);
462 if(!defined($pg_problem_id)){ 469 if (!defined($pg_problem_id)) {
463 $dbh->do("INSERT INTO pgfile_problem 470 $dbh->do("INSERT INTO pgfile_problem
464 VALUES( 471 VALUES(
465 \"$pgfile_id\", 472 \"$pgfile_id\",
466 \"$problem_id\" 473 \"$problem_id\"
467 )" 474 )"
468 ); 475 );
469 dbug "INSERT INTO pgfile_problem VALUES( \"$pgfile_id\", \"$problem_id\" )\n"; 476 dbug "INSERT INTO pgfile_problem VALUES( \"$pgfile_id\", \"$problem_id\" )\n";
470 } 477 }
471 } 478 }
472 #reset tag vars, they may not match the next file 479 #reset tag vars, they may not match the next file
473 $chapter=""; $section=""; $date =""; $author=""; $institution=""; $text=""; $edition=""; $textauthor=""; $textsection=""; $textproblem=""; @textproblems=(); 480 $chapter=""; $section=""; $date =""; $author=""; $institution=""; $text=""; $edition=""; $textauthor=""; $textsection=""; $textproblem=""; @textproblems=();
481 }
482 }
483 close(IN) or die "can not close: $!";
474 } 484 }
475 }
476 close(IN) or die "can not close: $!";
477 }
478} 485}
479 486
480 487
481$dbh->disconnect; 488$dbh->disconnect;
482 489
Legend:
Removed from v.441  
changed lines
  Added in v.442
aubreyja at gmail dot com
 ViewVC Help
Powered by ViewVC 1.0.9