#----script follows
#!/usr/bin/perl -w
use strict;
# use: ./alog2sql logfile_name > sqlfile_name
my $wd = `pwd`;
my $cl = $wd;
chomp($cl);
$cl =~ s/\/logs$//;
$cl =~ s/.*(ma\d\d\d.+)/$1/;
die(" * unable to determine class\n") if ( ! $cl );
my $update = 'update `' . $cl . '_problem_user` set status=SCORE where ' .
'user_id=\'USER\' and set_id=\'SET\' and problem_id=PROBLEM;' . "\n";
while ( ) {
my @fields = split( /\|/ );
my $user_id = $fields[1];
my $set_id = $fields[2];
my $prob_id = $fields[3];
my $scorestring = $fields[4];
if ( $set_id !~ /GW/ ||
( $set_id =~ /GW/ && $scorestring =~ /\[submit\]/ ) ) {
my ( $right, @timeans ) = split( /\t/, $scorestring );
my @parts = split(//, $right);
my $score = 0;
foreach my $s ( @parts ) { $score += $s; };
$score = sprintf("%7.6f", $score/scalar(@parts));
$score =~ s/0+$//;
my $tmpcmd = $update;
$tmpcmd =~ s/SCORE/$score/;
$tmpcmd =~ s/USER/$user_id/;
$tmpcmd =~ s/SET/$set_id/;
$tmpcmd =~ s/PROBLEM/$prob_id/;
print $tmpcmd;
}
}
#----end script
Hi all,
For what it's worth, I just ended up doing much of what Bruce asked about on account of a small typo in a mysql command. My verdict is that it isn't hard to reconstruct student scores from the answer_log. I used a simple Perl script to suck in the answer_log and generate a mysql command file that reset the answers for the course, which I append below. Note that the logging for gateway/quiz assignments is slightly different than that for homeworks; in my system all gateway/quiz assignments are named with a "GW" at the end of the set_id, which motivates the conditional about the set_id ending with "GW. " I also name all of my classes "maCCC-SSS-tYY" (CCC-SSS is the course-section number, and tYY the term).
Note that this script assumes that the sets all still exist and the scores were just changed; e.g., if a student were deleted from the course and then added back in with all of the same set assignments.
One other note: this obviously sets the scores for all students in the course. Add the appropriate user_id conditional in the while loop to restrict to one or more users. Oh, and the script assumes that it's being run in the logfiles directory for the course.
Gavin