[LON-CAPA-cvs] cvs: loncom /metadata_database searchcat.pl
www
lon-capa-cvs@mail.lon-capa.org
Thu, 25 Dec 2003 04:06:52 -0000
www Wed Dec 24 23:06:52 2003 EDT
Modified files:
/loncom/metadata_database searchcat.pl
Log:
Saving my work - this produces and SQL error on Insert.
Index: loncom/metadata_database/searchcat.pl
diff -u loncom/metadata_database/searchcat.pl:1.46 loncom/metadata_database/searchcat.pl:1.47
--- loncom/metadata_database/searchcat.pl:1.46 Wed Dec 24 15:41:32 2003
+++ loncom/metadata_database/searchcat.pl Wed Dec 24 23:06:52 2003
@@ -2,7 +2,7 @@
# The LearningOnline Network
# searchcat.pl "Search Catalog" batch script
#
-# $Id: searchcat.pl,v 1.46 2003/12/24 20:41:32 www Exp $
+# $Id: searchcat.pl,v 1.47 2003/12/25 04:06:52 www Exp $
#
# Copyright Michigan State University Board of Trustees
#
@@ -249,35 +249,34 @@
exit;
}
-# Create table for static metadata, unless exists
- my $make_metadata_table = "CREATE TABLE IF NOT EXISTS metadata (".
+# Make temporary table
+ $dbh->do("DROP TABLE IF EXISTS newmetadata");
+ my $make_metadata_table = "CREATE TABLE IF NOT EXISTS newmetadata (".
"title TEXT, author TEXT, subject TEXT, url TEXT, keywords TEXT, ".
"version TEXT, notes TEXT, abstract TEXT, mime TEXT, language TEXT, ".
"creationdate DATETIME, lastrevisiondate DATETIME, owner TEXT, ".
- "copyright TEXT, FULLTEXT idx_title (title), ".
- "FULLTEXT idx_author (author), FULLTEXT idx_subject (subject), ".
- "FULLTEXT idx_url (url), FULLTEXT idx_keywords (keywords), ".
- "FULLTEXT idx_version (version), FULLTEXT idx_notes (notes), ".
- "FULLTEXT idx_abstract (abstract), FULLTEXT idx_mime (mime), ".
- "FULLTEXT idx_language (language), FULLTEXT idx_owner (owner), ".
- "FULLTEXT idx_copyright (copyright)) TYPE=MYISAM";
- # It would sure be nice to have some logging mechanism.
- $dbh->do($make_metadata_table);
-
-# Create table for dynamic metadata, unless exists
- my $make_dynmetadata_table = "CREATE TABLE IF NOT EXISTS dynmetadata (".
- "url TEXT, count INTEGER UNSIGNED, ".
+ "copyright TEXT, ".
+ "count INTEGER UNSIGNED, ".
"course INTEGER UNSIGNED, course_list TEXT, ".
"goto INTEGER UNSIGNED, goto_list TEXT, ".
"comefrom INTEGER UNSIGNED, comefrom_list TEXT, ".
- "usage INTEGER UNSIGNED, usage_list TEXT, ".
+ "fusage INTEGER UNSIGNED, fusage_list TEXT, ".
"stdno INTEGER UNSIGNED, stdno_list TEXT, ".
"avetries FLOAT, avetries_list TEXT, ".
- "difficulty FLOAT, difficulty_list TEXT ".
- "TYPE=MYISAM";
+ "difficulty FLOAT, difficulty_list TEXT, ".
+ "FULLTEXT idx_title (title), ".
+ "FULLTEXT idx_author (author), FULLTEXT idx_subject (subject), ".
+ "FULLTEXT idx_url (url), FULLTEXT idx_keywords (keywords), ".
+ "FULLTEXT idx_version (version), FULLTEXT idx_notes (notes), ".
+ "FULLTEXT idx_abstract (abstract), FULLTEXT idx_mime (mime), ".
+ "FULLTEXT idx_language (language), FULLTEXT idx_owner (owner), ".
+ "FULLTEXT idx_copyright (copyright)) ".
+ "TYPE=MyISAM";
# It would sure be nice to have some logging mechanism.
-#### $dbh->do($make_dynmetadata_table);
-
+ unless ($dbh->do($make_metadata_table)) {
+ print LOG "\nMySQL Error Create: ".$dbh->errstr."\n";
+ die $dbh->errstr;
+ }
}
# ------------------------------------------------------------- get .meta files
@@ -289,15 +288,13 @@
#
# Create the statement handlers we need
-my $delete_sth = $dbh->prepare
- ("DELETE FROM metadata WHERE url LIKE BINARY ?");
my $insert_sth = $dbh->prepare
- ("INSERT INTO metadata VALUES (".
+ ("INSERT INTO newmetadata VALUES (".
"?,". # title
"?,". # author
"?,". # subject
- "?,". # m2???
+ "?,". # declutter url
"?,". # version
"?,". # current
"?,". # notes
@@ -307,7 +304,22 @@
"?,". # creationdate
"?,". # revisiondate
"?,". # owner
- "?)" # copyright
+ "?,". # copyright
+ "?,". # count
+ "?,". # course
+ "?,". # course_list
+ "?,". # goto
+ "?,". # goto_list
+ "?,". # comefrom
+ "?,". # comefrom_list
+ "?,". # usage
+ "?,". # usage_list
+ "?,". # stdno
+ "?,". # stdno_list
+ "?,". # avetries
+ "?,". # avetries_list
+ "?,". # difficulty
+ "?," # difficulty_list
);
foreach my $user (@homeusers) {
@@ -328,10 +340,10 @@
$m2=~s/\.meta$//;
if ($ref->{'obsolete'}) { print LOG "obsolete\n"; next; }
if ($ref->{'copyright'} eq 'private') { print LOG "private\n"; next; }
- &dynamicmeta($m2);
+ my %dyn=&dynamicmeta($m2);
&count($m2);
- $delete_sth->execute($m2);
- $insert_sth->execute($ref->{'title'},
+ unless ($insert_sth->execute(
+ $ref->{'title'},
$ref->{'author'},
$ref->{'subject'},
$m2,
@@ -344,20 +356,39 @@
sqltime($ref->{'creationdate'}),
sqltime($ref->{'lastrevisiondate'}),
$ref->{'owner'},
- $ref->{'copyright'});
-# if ($dbh->err()) {
-# print STDERR "Error:".$dbh->errstr()."\n";
-# }
+ $ref->{'copyright'},
+ $dyn{'count'},
+ $dyn{'course'},
+ $dyn{'course_list'},
+ $dyn{'goto'},
+ $dyn{'goto_list'},
+ $dyn{'comefrom'},
+ $dyn{'comefrom_list'},
+ $dyn{'usage'},
+ $dyn{'usage_list'},
+ $dyn{'stdno'},
+ $dyn{'stdno_list'},
+ $dyn{'avetries'},
+ $dyn{'avetries_list'},
+ $dyn{'difficulty'},
+ $dyn{'difficulty_list'}
+ )) {
+ print LOG "\nMySQL Error Insert: ".$dbh->errstr."\n";
+ die $dbh->errstr;
+ }
$ref = undef;
}
-
- # --------------------------------------------------- Clean up database
- # Need to, perhaps, remove stale SQL database records.
- # ... not yet implemented
-
}
# --------------------------------------------------- Close database connection
-$dbh->disconnect;
+$dbh->do("DROP TABLE IF EXISTS metadata");
+unless ($dbh->do("RENAME TABLE newmetadata TO metadata")) {
+ print LOG "\nMySQL Error Rename: ".$dbh->errstr."\n";
+ die $dbh->errstr;
+}
+unless ($dbh->disconnect) {
+ print LOG "\nMySQL Error Disconnect: ".$dbh->errstr."\n";
+ die $dbh->errstr;
+}
print LOG "\n==== Searchcat completed ".localtime()." ====\n";
close(LOG);
&writesimple();
@@ -372,7 +403,7 @@
# significantly altered from subroutine present in lonnet
sub metadata {
my ($uri,$what)=@_;
- my %metacache;
+ my %metacache=();
$uri=&declutter($uri);
my $filename=$uri;
$uri=~s/\.meta$//;