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

View of /trunk/NationalProblemLibrary/loadDB2-deprecated

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1625 - (download) (annotate)
Wed Jan 12 22:43:44 2011 UTC (3 years, 3 months ago) by jj
File size: 18667 byte(s)
Testing commit access.

    1 #!/usr/bin/perl
    2 
    3 #use strict;
    4 use File::Find;
    5 use File::Basename;
    6 use Cwd;
    7 use DBI;
    8 
    9 if (! -e "create_tables2.sql") {
   10 	print "Cannot find file create_tables2.sql.\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";
   13 	exit;
   14 }
   15 
   16 my $passwd;
   17 
   18 # Innocuous change to check on commit
   19 
   20 if (@ARGV != 1) {
   21 	print "What is the root password for mysql? ";
   22 	$passwd = <STDIN>;
   23 	chomp($passwd);
   24 } else {
   25 	$passwd = $ARGV[0];
   26 }
   27 
   28 my $user = 'root';
   29 my $libraryRoot = Cwd::cwd();
   30 my $verbose =0;
   31 my $cnt = 0;
   32 my $cnt2 = 0;
   33 
   34 $| = 1; # autoflush output
   35 
   36 sub dbug {
   37 	my $msg = shift;
   38 	$cnt++;
   39 	print $msg if($verbose>1);
   40 	#    if($cnt==5000) { print "\n"; $cnt=0;}
   41 	print "." if($verbose==1 and ($cnt % 100 == 0));
   42 }
   43 
   44 system("mysql -uroot -p$passwd -B ProblemLibrary <./create_tables2.sql");
   45 print "Mysql database reinitialized.\n";
   46 
   47 my $mydb =  "dbi:mysql:ProblemLibrary";
   48 
   49 # From pgfile
   50 ## DBchapter('Limits and Derivatives')
   51 ## DBsection('Calculating Limits using the Limit Laws')
   52 ## Date('6/3/2002')
   53 ## Author('Tangan Gao')
   54 ## Institution('csulb')
   55 ## TitleText1('Calculus Early Transcendentals')
   56 ## EditionText1('4')
   57 ## AuthorText1('Stewart')
   58 ## Section1('2.3')
   59 ## Problem1('7')
   60 #
   61 # The database structure is in the file create_tables2.sql and its ER-graph is 
   62 #    the file wwdb_er_graph.pdf. 
   63 
   64 my ($name,$pgfile,$pgpath);
   65 
   66 #recursive search for all pg files
   67 my $dbh = DBI->connect('dbi:mysql:ProblemLibrary', $user, $passwd);
   68 
   69 $dbh->{RaiseError} = 1;			#bail if anything fails via die
   70 
   71 #### First read in textbook information
   72 
   73 if(open(IN, "Textbooks")) {
   74 	print "Reading in textbook data.\n";
   75 	my %textinfo = ( TitleText => '', EditionText =>'', AuthorText=>'');
   76 	my $bookid = undef;
   77 	while (my $line = <IN>) {
   78 		$line =~ s|#*$||;
   79 		if($line =~ /^\s*(.*?)\s*>>>\s*(.*?)\s*$/) { # Should have chapter or section information
   80 			my $chapsec = $1;
   81 			my $title = $2;
   82 			if($chapsec=~ /(\d+)\.(\d+)/) { # We have a section
   83 				if(defined($bookid)) {
   84 					my $query = "SELECT chapter_id FROM chapter WHERE textbook_id = \"$bookid\" AND number = \"$1\"";
   85 					my $chapid = $dbh->selectrow_array($query);
   86 					if(defined($chapid)) {
   87 						$query = "SELECT section_id FROM section WHERE chapter_id = \"$chapid\" AND name = \"$title\"";
   88 						my $sectid = $dbh->selectrow_array($query);
   89 						if (!defined($sectid)) {
   90 							$dbh->do("INSERT INTO section
   91 					VALUES(
   92 						\"\",
   93 						\"$chapid\",
   94 						\"$2\",
   95 						\"$title\",
   96 						\"\"
   97 					)"
   98 								);
   99 							dbug "INSERT INTO section VALUES(\"\", \"$chapid\", \"$2\", \"$title\", \"\" )\n";
  100 						}
  101 					} else {
  102 						print "Cannot enter section $chapsec because textbook information is missing the chapter entry\n";
  103 					}
  104 				} else {
  105 					print "Cannot enter section $chapsec because textbook information is incomplete\n";
  106 				}
  107 			} else { # We have a chapter entry
  108 				if(defined($bookid)) {
  109 					my $query = "SELECT chapter_id FROM chapter WHERE textbook_id = \"$bookid\" AND name = \"$chapsec\"";
  110 					my $chapid = $dbh->selectrow_array($query);
  111 					if (!defined($chapid)) {
  112 						$dbh->do("INSERT INTO chapter
  113 					VALUES(
  114 						\"\",
  115 						\"$bookid\",
  116 						\"".$chapsec."\",
  117 						\"$title\",
  118 						\"\"
  119 					)"
  120 							);
  121 					dbug "INSERT INTO chapter VALUES(\"\", \"$bookid\", \"".$chapsec."\", \"$title\", \"\" )\n";
  122 					}
  123 				} else {
  124 					print "Cannot enter chapter $chapsec because textbook information is incomplete\n";
  125 				}
  126 			}
  127 		} elsif($line =~ /^\s*(TitleText|EditionText|AuthorText)\(\s*'(.*?)'\s*\)/) {
  128 			# Textbook information, maybe new
  129 			my $type = $1;
  130 			if(defined($textinfo{$type})) { # signals new text
  131 				%textinfo = ( TitleText => undef, 
  132 							  EditionText =>undef, 
  133 							  AuthorText=> undef);
  134 				$textinfo{$type} = $2;
  135 				$bookid = undef;
  136 			} else {
  137 				$textinfo{$type} = $2;
  138 				if(defined($textinfo{TitleText}) and 
  139 				   defined($textinfo{AuthorText}) and 
  140 				   defined($textinfo{EditionText})) {
  141 					my $query = "SELECT textbook_id FROM textbook WHERE title = \"$textinfo{TitleText}\" AND edition = \"$textinfo{EditionText}\" AND author=\"$textinfo{AuthorText}\"";
  142 					$bookid = $dbh->selectrow_array($query);
  143 					if (!defined($bookid)) {
  144 						$dbh->do("INSERT INTO textbook
  145 					VALUES(
  146 						\"\",
  147 						\"$textinfo{TitleText}\",
  148 						\"$textinfo{EditionText}\",
  149 						\"$textinfo{AuthorText}\",
  150 						\"\",
  151 						\"\",
  152 						\"\"
  153 					)"
  154 							);
  155 						dbug "INSERT INTO textbook VALUES( \"\", \"$textinfo{TitleText}\", \"$textinfo{EditionText}\", \"$textinfo{AuthorText}\", \"\", \"\", \"\" )\n";
  156 						$bookid = $dbh->selectrow_array($query);
  157 					}
  158 				}
  159 			}
  160 		}
  161 	}
  162 	close(IN);
  163 } else{
  164 	print "Textbooks file was not found.  Updating from cvs should fix this problem.\n";
  165 }
  166 
  167 print "Converting data from tagged pgfiles into mysql.\n";
  168 print "Number of files processed:\n";
  169 
  170 #### Now search for tagged problems
  171 
  172 find({ wanted => \&pgfiles, follow_fast=> 1}, $libraryRoot);
  173 
  174 sub kwtidy {
  175 	my $s = shift;
  176 	$s =~ s/\W//g;
  177 	$s =~ s/_//g;
  178 	$s = lc($s);
  179 	return($s);
  180 }
  181 
  182 sub keywordcleaner {
  183 	my $string = shift;
  184 	my @spl1 = split /,/, $string;
  185 	my @spl2 = map(kwtidy($_), @spl1);
  186 	return(@spl2);
  187 }
  188 
  189 # Save on passing these values around
  190 my %textinfo;
  191 
  192 # Initialize, if needed more text-info information;
  193 sub maybenewtext {
  194 	my $textno = shift;
  195 	return if defined($textinfo{$textno});
  196 	# So, not defined yet
  197 	$textinfo{$textno} = { title => '', author =>'', edition =>'',
  198 						   section => '', chapter =>'', problems => [] };
  199 }
  200 
  201 # process each file returned by the find command.
  202 sub pgfiles {
  203 	my $name = $File::Find::name;
  204 	my ($subject, $chapter, $section, $date, $institution, $author, $text);
  205 	my ($edition, $textauthor, $textsection, $textproblem, $tagged);
  206 	%textinfo=();
  207 	my @textproblems = (-1);
  208 	if ($name =~ /pg$/) {
  209 		$pgfile = basename($name);
  210 		$pgpath = dirname($name);
  211 		$cnt2++;
  212 		printf("%6d", $cnt2) if(($cnt2 % 100) == 0);
  213 		print "\n" if(($cnt2 % 1000) == 0);
  214 		$pgpath =~ s|^$libraryRoot/||;
  215 		open(IN,"$name") or die "can not open $name: $!";
  216 		$tagged = 0;
  217 		while (<IN>) {
  218 		SWITCH: {
  219 				if (/KEYWORDS\((.*)\)/i) {
  220 					@keyword = keywordcleaner($1);
  221 					last SWITCH;
  222 				}
  223 				if (/DBsubject\(\s*'(.*?)'\s*\)/) {
  224 					$subject = $1;
  225 					$subject =~ s/'/\'/g;
  226 					last SWITCH;
  227 				}
  228 				if (/DBchapter\(\s*'(.*?)'\s*\)/) {
  229 					$chapter = $1;
  230 					$chapter =~ s/'/\'/g;
  231 					$tagged = 1;
  232 					last SWITCH;
  233 				}
  234 				if (/DBsection\(\s*'(.*?)'\s*\)/) {
  235 					$section = $1;
  236 					$section =~ s/'/\'/g;
  237 					last SWITCH;
  238 				}
  239 				if (/Date\(\s*'(.*?)'\s*\)/) {
  240 					$date = $1;
  241 					$date =~ s/'/\'/g;
  242 					last SWITCH;
  243 				}
  244 				if (/Institution\(\s*'(.*?)'\s*\)/) {
  245 					$institution = $1;
  246 					$institution =~ s/'/\'/g;
  247 					last SWITCH;
  248 				}
  249 				if (/Author\(\s*'(.*?)'\s*\)/) {
  250 					$author = $1;
  251 					$author =~ s/'/\'/g;
  252 					last SWITCH;
  253 				}
  254 				if (/TitleText(\d+)\(\s*'(.*?)'\s*\)/) {
  255 					$textno = $1;
  256 					$text = $2;
  257 					$text =~ s/'/\'/g;
  258 					if ($text =~ /\S/) {
  259 						maybenewtext($textno);
  260 						$textinfo{$textno}->{title} = $text;
  261 					}
  262 					last SWITCH;
  263 				}
  264 				if (/EditionText(\d+)\(\s*'(.*?)'\s*\)/) {
  265 					$textno = $1;	
  266 					$edition = $2;
  267 					$edition =~ s/'/\'/g;
  268 					if ($edition =~ /\S/) {
  269 						maybenewtext($textno);
  270 						$textinfo{$textno}->{edition} = $edition;
  271 					}
  272 					last SWITCH;
  273 				}
  274 				if (/AuthorText(\d+)\(\s*'(.*?)'\s*\)/) {
  275 					$textno = $1;
  276 					$textauthor = $2;
  277 					$textauthor =~ s/'/\'/g;
  278 					if ($textauthor =~ /\S/) {
  279 						maybenewtext($textno);
  280 						$textinfo{$textno}->{author} = $textauthor;
  281 					}
  282 					last SWITCH;
  283 				}
  284 				if (/Section(\d+)\(\s*'(.*?)'\s*\)/) {
  285 					$textno = $1;
  286 					$textsection = $2;
  287 					$textsection =~ s/'/\'/g;
  288 					if ($textsection =~ /\S/) {
  289 						maybenewtext($textno);
  290 						if ($textsection =~ /(\d*?)\.(\d*)/) {
  291 							$textinfo{$textno}->{chapter} = $1;
  292 							$textinfo{$textno}->{section} = $2;
  293 						} else {
  294 							$textinfo{$textno}->{chapter} = $textsection;
  295 							$textinfo{$textno}->{section} = '';
  296 							# used to be: $chapnum = undef;	#no valid number assigned
  297 						}
  298 
  299 					}
  300 					last SWITCH;
  301 				}
  302 				if (/Problem(\d+)\(\s*(.*?)\s*\)/) {
  303 					$textno = $1;
  304 					$textproblem = $2;
  305 					$textproblem =~ s/\D/ /g;
  306 					@textproblems = split /\s+/, $textproblem;
  307 					@textproblems = grep { $_ =~ /\S/ } @textproblems;
  308 					if (scalar(@textproblems) or defined($textinfo{$textno})) {
  309 						@textproblems = (-1) unless(scalar(@textproblems));
  310 						maybenewtext($textno);
  311 						$textinfo{$textno}->{problems} = \@textproblems;
  312 					}
  313 					#print "$textproblem\n" if ($textproblem !~ /^[\s\d]+$/);
  314 					#		$textproblem =~ s/'/\'/g;
  315 					last SWITCH;
  316 				}
  317 			}
  318 		}						#end of SWITCH and while
  319 		if ($tagged and $chapter eq 'ZZZ-Inserted Text') {
  320 			$tagged=0;
  321 		}
  322 		if ($tagged) {
  323 			#
  324 			# kludge to fix the omission of a subject field
  325 			unless($subject) {
  326 				if ($text =~ /precalculus/i) {
  327 					$subject = "Precalculus";
  328 				} elsif ($text =~ /calculus/i) {
  329 					$subject = "Calculus";
  330 				} elsif ($text =~ /linear/i) {
  331 					$subject = "Linear Algebra";
  332 				} elsif ($text =~ /algebra/i) {
  333 					$subject = "Algebra";
  334 				} elsif ($text =~ /statistic/i) {
  335 					$subject = "Statistics";
  336 				} elsif ($text =~ /financial/i) {
  337 					$subject = "Financial Mathematics";
  338 				} else {
  339 					$subject = "Misc";
  340 				}
  341 			}
  342 			# From the pgfile we just looked at,
  343 			## DBchapter('Limits and Derivatives') 		in $chapter
  344 			## DBsection('Calculating Limits using the Limit Laws') in $section
  345 			## Date('6/3/2002') 				in $date
  346 			## Author('Tangan Gao') 				in $author
  347 			## Institution('csulb') 				in $institution
  348 			## TitleText1('Calculus Early Transcendentals')	in $text
  349 			## EditionText1('4') 				in $edition
  350 			## AuthorText1('Stewart') 				in $textauthor
  351 			## Section1('2.3') 					in $textsection
  352 			## Problem1('7') 					in $textproblem
  353 			#
  354 			# The database structure is in the file create_tables2.sql and its ER-graph
  355 			#    is the file wwdb_er_graph.pdf. Insert, in order, into the tables
  356 			#    in that file.
  357 			#
  358 
  359 			#selectrow_array returns first field of first row in scalar context or undef 
  360 			# undef for failure also, $dbh->{RaiseError} = 1 should catch that case.
  361 			#
  362 			# DBsubject table
  363 			#
  364 			$query = "SELECT DBsubject_id FROM DBsubject WHERE name = \"$subject\"";
  365 			my $DBsubject_id = $dbh->selectrow_array($query);
  366 			if (!defined($DBsubject_id)) {
  367 				$dbh->do(
  368 						 "INSERT INTO DBsubject
  369 					VALUES(
  370 						\"\",
  371 						\"$subject\"
  372 					)"
  373 						);
  374 				dbug "INSERT INTO DBsubject VALUES(\"\",\"$subject\")\n";
  375 				$DBsubject_id = $dbh->selectrow_array($query);
  376 			}
  377 
  378 			# DBchapter table
  379 			#
  380 			$query = "SELECT DBchapter_id FROM DBchapter WHERE name = \"$chapter\" and DBsubject_id = $DBsubject_id";
  381 			my $DBchapter_id = $dbh->selectrow_array($query);  
  382 			if (!defined($DBchapter_id)) {
  383 				$dbh->do("INSERT INTO DBchapter 
  384 					VALUES(
  385 						\"\",
  386 						\"$chapter\",
  387 						\"$DBsubject_id\"
  388 					)"
  389 						);
  390 				dbug "INSERT INTO DBchapter VALUES(  \"\", \"$chapter\", \"$DBsubject_id\)\n";
  391 				$DBchapter_id = $dbh->selectrow_array($query);
  392 			}
  393 
  394 			# DBsection table
  395 			#
  396 			$query = "SELECT DBsection_id FROM DBsection WHERE name = \"$section\" AND DBchapter_id = $DBchapter_id";
  397 			my $DBsection_id = $dbh->selectrow_array($query);
  398 			if (!defined($DBsection_id)) {
  399 				$dbh->do("INSERT INTO DBsection
  400 					VALUES(
  401 						\"\",
  402 						\"$section\",
  403 						\"$DBchapter_id\"
  404 					)"
  405 						);
  406 				dbug "INSERT INTO DBsection VALUES( \"\", \"$section\", \"$DBchapter_id\", \"$DBsubject_id\" )\n";
  407 				$DBsection_id = $dbh->selectrow_array($query);
  408 			}
  409 
  410 			# institution table
  411 			#
  412 			$query = "SELECT institution_id FROM institution WHERE name = \"$institution\"";
  413 			my $institution_id = $dbh->selectrow_array($query);  
  414 			if (!defined($institution_id)) {
  415 				$dbh->do("INSERT INTO institution 
  416 					VALUES(
  417 						\"\",
  418 						\"$institution\",
  419 						\"\",
  420 						\"\",
  421 						\"\",
  422 						\"\",
  423 						\"\",
  424 						\"\"
  425 					)"
  426 						);
  427 				dbug "INSERT INTO institution VALUES( \"\", \"$institution\", \"\", \"\", \"\", \"\", \"\", \"\")\n";
  428 				$institution_id = $dbh->selectrow_array($query);
  429 			}
  430 
  431 			# author table
  432 			#
  433 			$author =~ /(.*?)\s(\w+)\s*$/;
  434 			my $firstname = $1;
  435 			my $lastname = $2;
  436 			#remove leading and trailing spaces from firstname, which includes any middle name too.
  437 			$firstname =~ s/^\s*//;
  438 			$firstname =~ s/\s*$//;
  439 			$query = "SELECT author_id FROM author WHERE lastname = \"$lastname\" AND firstname=\"$firstname\"";
  440 			my $author_id = $dbh->selectrow_array($query);
  441 			if (!defined($author_id)) {
  442 				$dbh->do("INSERT INTO author
  443 					VALUES(
  444 						\"\",
  445 						\"$institution_id\",
  446 						\"$lastname\",
  447 						\"$firstname\",
  448 						\"\"
  449 					)"
  450 						);
  451 				dbug "INSERT INTO author VALUES( \"\", \"$institution_id\", \"$lastname\", \"$firstname\", \"\" )\n";
  452 				$author_id = $dbh->selectrow_array($query);
  453 			}
  454 
  455 			# path table
  456 			#
  457 			$query = "SELECT path_id FROM path WHERE path = \"$pgpath\"";
  458 			my $path_id = $dbh->selectrow_array($query);
  459 			if (!defined($path_id)) {
  460 				$dbh->do("INSERT INTO path 
  461 					VALUES(
  462 						\"\",
  463 						\"$pgpath\",
  464 						\"\",
  465 						\"\"
  466 					)"
  467 						);
  468 				dbug "INSERT INTO path VALUES( \"\", \"$path\", \"\", \"\" )\n";
  469 				$path_id = $dbh->selectrow_array($query);
  470 			}
  471 
  472 			# pgfile table
  473 			#
  474 			my $pgfile_id;
  475 			$dbh->do("INSERT INTO pgfile
  476 					VALUES(
  477 						\"\",
  478 						\"$DBsection_id\",
  479 						\"$author_id\",
  480 						\"$institution_id\",
  481 						\"$path_id\",
  482 						\"$pgfile\",
  483 						\"\"
  484 					)"
  485 					);
  486 			dbug "INSERT INTO pgfile VALUES( \"\", \"$DBsection_id\", \"$author_id\", \"$institution_id\", \"$path_id\", \"$pgfile\", \"\" )\n";
  487 			$query = "SELECT pgfile_id FROM pgfile WHERE filename = \"$pgfile\" and path_id=$path_id";
  488 			$pgfile_id = $dbh->selectrow_array($query);
  489 
  490 			# keyword table, and problem_keyword many-many table
  491 			#
  492 			foreach my $keyword (@keyword) {
  493 				$keyword =~ s/[\'\"]//g;
  494 				$query = "SELECT keyword_id FROM keyword WHERE keyword = \"$keyword\"";
  495 				my $keyword_id = $dbh->selectrow_array($query);
  496 				if (!defined($keyword_id)) {
  497 					$dbh->do("INSERT INTO keyword
  498 						VALUES(
  499 							\"\",
  500 							\"$keyword\"
  501 						)"
  502 							);
  503 					dbug "INSERT INTO keyword VALUES( \"\", \"$keyword\")\n";
  504 					$keyword_id = $dbh->selectrow_array($query);
  505 				}
  506 
  507 				$query = "SELECT pgfile_id FROM pgfile_keyword WHERE keyword_id = \"$keyword_id\" and pgfile_id=\"$pgfile_id\"";
  508 				my $ok = $dbh->selectrow_array($query);
  509 				if (!defined($ok)) {
  510 					$dbh->do("INSERT INTO pgfile_keyword
  511 						VALUES(
  512 							\"$pgfile_id\",
  513 							\"$keyword_id\"
  514 						)"
  515 							);
  516 					dbug "INSERT INTO pgfile_keyword VALUES( \"$pgfile_id\", \"$keyword_id\" )\n";
  517 				}
  518 			}					#end foreach keyword
  519 
  520 			# pgfile_institution table
  521 			#
  522 			$query = "SELECT institution_id FROM pgfile_institution WHERE pgfile_id = \"$pgfile_id\"";
  523 			my $pg_inst_id = $dbh->selectrow_array($query);
  524 			if (!defined($pg_inst_id)) {
  525 				$dbh->do("INSERT INTO pgfile_institution
  526 					VALUES(
  527 						\"$pgfile_id\",
  528 						\"$institution_id\"
  529 					)"
  530 						);
  531 				dbug "INSERT INTO pgfile_institution VALUES( \"$pgfile_id\", \"$institution_id\" )\n";
  532 				$pg_inst_id = $dbh->selectrow_array($query);
  533 			}
  534 
  535 			# Textbook section
  536 			# problem table contains textbook problems
  537 			#
  538 			for my $textno (keys %textinfo) {
  539 				my $texthashref = $textinfo{$textno};
  540 
  541 				# textbook table
  542 				#
  543 				$text = $texthashref->{title};
  544 				$edition = $texthashref->{edition};
  545 				$textauthor = $texthashref->{author};
  546 				my $chapnum = $texthashref->{chapter};
  547 				my $secnum = $texthashref->{section};
  548 				$query = "SELECT textbook_id FROM textbook WHERE title = \"$text\" AND edition = \"$edition\" AND author=\"$textauthor\"";
  549 				my $textbook_id = $dbh->selectrow_array($query);
  550 				if (!defined($textbook_id)) {
  551 					$dbh->do("INSERT INTO textbook
  552 					VALUES(
  553 						\"\",
  554 						\"$text\",
  555 						\"$edition\",
  556 						\"$textauthor\",
  557 						\"\",
  558 						\"\",
  559 						\"\"
  560 					)"
  561 							);
  562 					dbug "INSERT INTO textbook VALUES( \"\", \"$text\", \"$edition\", \"$textauthor\", \"\", \"\", \"\" )\n";
  563 					print "Late add into textbook \"$text\", \"$edition\", \"$textauthor\"\n";
  564 					$textbook_id = $dbh->selectrow_array($query);
  565 				}
  566 
  567 				# chapter weak table of textbook
  568 				#
  569 				$query = "SELECT chapter_id FROM chapter WHERE textbook_id = \"$textbook_id\" AND name = \"$chapter\"";
  570 				my $chapter_id = $dbh->selectrow_array($query);
  571 				if (!defined($chapter_id)) {
  572 					$dbh->do("INSERT INTO chapter
  573 					VALUES(
  574 						\"\",
  575 						\"$textbook_id\",
  576 						\"".$chapnum."\",
  577 						\"$chapter\",
  578 						\"\"
  579 					)"
  580 							);
  581 					dbug "INSERT INTO chapter VALUES(\"\", \"$textbook_id\", \"".$chapnum."\", \"$chapter\", \"\" )\n";
  582 					$chapter_id = $dbh->selectrow_array($query);
  583 				}
  584 
  585 				# section weak table of textbook
  586 				#
  587 				$query = "SELECT section_id FROM section WHERE chapter_id = \"$chapter_id\" AND name = \"$section\"";
  588 				my $section_id = $dbh->selectrow_array($query);
  589 				if (!defined($section_id)) {
  590 					$dbh->do("INSERT INTO section
  591 					VALUES(
  592 						\"\",
  593 						\"$chapter_id\",
  594 						\"$secnum\",
  595 						\"$section\",
  596 						\"\"
  597 					)"
  598 							);
  599 					dbug "INSERT INTO section VALUES(\"\", \"$textbook_id\", \"$secnum\", \"$section\", \"\" )\n";
  600 					$section_id = $dbh->selectrow_array($query);
  601 				}
  602 
  603 				@textproblems = @{$texthashref->{problems}};
  604 				for my $tp (@textproblems) {
  605 					$query = "SELECT problem_id FROM problem WHERE section_id = \"$section_id\" AND number = \"$tp\"";
  606 					my $problem_id = $dbh->selectrow_array($query);
  607 					if (!defined($problem_id)) {
  608 						$dbh->do("INSERT INTO problem
  609 					VALUES(
  610 						\"\",
  611 						\"$section_id\",
  612 						\"$tp\",
  613 						\"\"
  614 					)"
  615 								);
  616 						dbug "INSERT INTO problem VALUES( \"\", \"$section_id\", \"$tp\", \"\" )\n";
  617 						$problem_id = $dbh->selectrow_array($query);
  618 					}
  619 
  620 					# pgfile_problem table associates pgfiles with textbook problems
  621 					#
  622 					$query = "SELECT problem_id FROM pgfile_problem WHERE problem_id = \"$problem_id\" AND pgfile_id = \"$pgfile_id\"";
  623 					my $pg_problem_id = $dbh->selectrow_array($query);  
  624 					if (!defined($pg_problem_id)) {
  625 						$dbh->do("INSERT INTO pgfile_problem 
  626 					VALUES(
  627 						\"$pgfile_id\",
  628 						\"$problem_id\"
  629 					)"
  630 								);
  631 						dbug "INSERT INTO pgfile_problem VALUES( \"$pgfile_id\", \"$problem_id\" )\n";
  632 					}
  633 				}
  634 
  635 			}
  636 			#reset tag vars, they may not match the next file
  637 			$date =""; $textauthor=""; $textsection="";
  638 			$chapter=""; $section="";
  639 		}
  640 		close(IN) or die "can not close: $!";
  641 	}
  642 }
  643 
  644 
  645 $dbh->disconnect;
  646 
  647 print "\nDone.\n";
  648 

aubreyja at gmail dot com
ViewVC Help
Powered by ViewVC 1.0.9