# The LearningOnline Network with CAPA # # $Id: loncoursedata.pm,v 1.126 2004/03/22 17:25:26 matthew Exp $ # # Copyright Michigan State University Board of Trustees # # This file is part of the LearningOnline Network with CAPA (LON-CAPA). # # LON-CAPA is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # LON-CAPA is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with LON-CAPA; if not, write to the Free Software # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA # # /home/httpd/html/adm/gpl.txt # # http://www.lon-capa.org/ # ### =pod =head1 NAME loncoursedata =head1 SYNOPSIS Set of functions that download and process student and course information. =head1 PACKAGES USED Apache::Constants qw(:common :http) Apache::lonnet() Apache::lonhtmlcommon HTML::TokeParser GDBM_File =cut package Apache::loncoursedata; use strict; use Apache::Constants qw(:common :http); use Apache::lonnet(); use Apache::lonhtmlcommon; use Time::HiRes; use Apache::lonmysql; use HTML::TokeParser; use GDBM_File; =pod =head1 DOWNLOAD INFORMATION This section contains all the functions that get data from other servers and/or itself. =cut #################################################### #################################################### =pod =item &get_sequence_assessment_data() Use lonnavmaps to build a data structure describing the order and assessment contents of each sequence in the current course. The returned structure is a hash reference. { title => 'title', symb => 'symb', src => '/s/o/u/r/c/e', type => (container|assessment), num_assess => 2, # only for container parts => [11,13,15], # only for assessment response_ids => [12,14,16], # only for assessment contents => [........] # only for container } $hash->{'contents'} is a reference to an array of hashes of the same structure. Also returned are array references to the sequences and assessments contained in the course. =cut #################################################### #################################################### sub get_sequence_assessment_data { my $fn=$ENV{'request.course.fn'}; ## ## use navmaps my $navmap = Apache::lonnavmaps::navmap->new(); if (!defined($navmap)) { return 'Can not open Coursemap'; } # We explicity grab the top level map because I am not sure we # are pulling it from the iterator. my $top_level_map = $navmap->getById('0.0'); # my $iterator = $navmap->getIterator(undef, undef, undef, 1); my $curRes = $iterator->next(); # Top level sequence ## ## Prime the pump ## ## We are going to loop until we run out of sequences/pages to explore for ## resources. This means we have to start out with something to look ## at. my $title = $ENV{'course.'.$ENV{'request.course.id'}.'.description'}; my $symb = $top_level_map->symb(); my $src = $top_level_map->src(); my $randompick = $top_level_map->randompick(); # my @Sequences; my @Assessments; my @Nested_Sequences = (); # Stack of sequences, keeps track of depth my $top = { title => $title, src => $src, symb => $symb, type => 'container', num_assess => 0, num_assess_parts => 0, contents => [], randompick => $randompick, }; push (@Sequences,$top); push (@Nested_Sequences, $top); # # We need to keep track of which sequences contain homework problems # my $previous_too; my $previous; while (scalar(@Nested_Sequences)) { $previous_too = $previous; $previous = $curRes; $curRes = $iterator->next(); my $currentmap = $Nested_Sequences[-1]; # Last one on the stack if ($curRes == $iterator->BEGIN_MAP()) { if (! ref($previous)) { $previous = $previous_too; } if (! ref($previous)) { next; } # get the map itself, instead of BEGIN_MAP $title = $previous->title(); $title =~ s/\:/\&\#058;/g; $symb = $previous->symb(); $src = $previous->src(); # pick up the filename if there is no title available if (! defined($title) || $title eq '') { ($title) = ($src=~/\/([^\/]*)$/); } $randompick = $previous->randompick(); my $newmap = { title => $title, src => $src, symb => $symb, type => 'container', num_assess => 0, randompick => $randompick, contents => [], }; push (@{$currentmap->{'contents'}},$newmap); # this is permanent push (@Sequences,$newmap); push (@Nested_Sequences, $newmap); # this is a stack next; } if ($curRes == $iterator->END_MAP()) { pop(@Nested_Sequences); next; } next if (! ref($curRes)); next if (! $curRes->is_problem() && $curRes->src() !~ /\.survey$/); # Okay, from here on out we only deal with assessments $title = $curRes->title(); $title =~ s/\:/\&\#058;/g; $symb = $curRes->symb(); $src = $curRes->src(); # Grab the filename if there is not title available if (! defined($title) || $title eq '') { ($title) = ($src=~ m:/([^/]*)$:); } my $parts = $curRes->parts(); my %partdata; foreach my $part (@$parts) { my @Responses = $curRes->responseType($part); my @Ids = $curRes->responseIds($part); $partdata{$part}->{'ResponseTypes'}= \@Responses; $partdata{$part}->{'ResponseIds'} = \@Ids; # Count how many responses of each type there are in this part foreach (@Responses) { $partdata{$part}->{$_}++; } } my $assessment = { title => $title, src => $src, symb => $symb, type => 'assessment', parts => $parts, num_parts => scalar(@$parts), partdata => \%partdata, }; push(@Assessments,$assessment); push(@{$currentmap->{'contents'}},$assessment); $currentmap->{'num_assess'}++; $currentmap->{'num_assess_parts'}+= scalar(@$parts); } $navmap->untieHashes(); return ($top,\@Sequences,\@Assessments); } sub LoadDiscussion { my ($courseID)=@_; my %Discuss=(); my %contrib=&Apache::lonnet::dump( $courseID, $ENV{'course.'.$courseID.'.domain'}, $ENV{'course.'.$courseID.'.num'}); #my %contrib=&DownloadCourseInformation($name, $courseID, 0); foreach my $temp(keys %contrib) { if ($temp=~/^version/) { my $ver=$contrib{$temp}; my ($dummy,$prb)=split(':',$temp); for (my $idx=1; $idx<=$ver; $idx++ ) { my $name=$contrib{"$idx:$prb:sendername"}; $Discuss{"$name:$prb"}=$idx; } } } return \%Discuss; } ################################################ ################################################ =pod =item &GetUserName(username,userdomain) Returns a hash with the following entries: 'firstname', 'middlename', 'lastname', 'generation', and 'fullname' 'fullname' is the result of &Apache::loncoursedata::ProcessFullName. =cut ################################################ ################################################ sub GetUserName { my ($username,$userdomain) = @_; $username = $ENV{'user.name'} if (! defined($username)); $userdomain = $ENV{'user.domain'} if (! defined($username)); my %userenv = &Apache::lonnet::get('environment', ['firstname','middlename','lastname','generation'], $userdomain,$username); $userenv{'fullname'} = &ProcessFullName($userenv{'lastname'}, $userenv{'generation'}, $userenv{'firstname'}, $userenv{'middlename'}); return %userenv; } ################################################ ################################################ =pod =item &ProcessFullName() Takes lastname, generation, firstname, and middlename (or some partial set of this data) and returns the full name version as a string. Format is Lastname generation, firstname middlename or a subset of this. =cut ################################################ ################################################ sub ProcessFullName { my ($lastname, $generation, $firstname, $middlename)=@_; my $Str = ''; # Strip whitespace preceeding & following name components. $lastname =~ s/(\s+$|^\s+)//g; $generation =~ s/(\s+$|^\s+)//g; $firstname =~ s/(\s+$|^\s+)//g; $middlename =~ s/(\s+$|^\s+)//g; if($lastname ne '') { $Str .= $lastname; $Str .= ' '.$generation if ($generation ne ''); $Str .= ','; $Str .= ' '.$firstname if ($firstname ne ''); $Str .= ' '.$middlename if ($middlename ne ''); } else { $Str .= $firstname if ($firstname ne ''); $Str .= ' '.$middlename if ($middlename ne ''); $Str .= ' '.$generation if ($generation ne ''); } return $Str; } ################################################ ################################################ =pod =item &make_into_hash($values); Returns a reference to a hash as described by $values. $values is assumed to be the result of join(':',map {&Apache::lonnet::escape($_)} %orighash); This is a helper function for get_current_state. =cut ################################################ ################################################ sub make_into_hash { my $values = shift; my %tmp = map { &Apache::lonnet::unescape($_); } split(':',$values); return \%tmp; } ################################################ ################################################ =pod =head1 LOCAL DATA CACHING SUBROUTINES The local caching is done using MySQL. There is no fall-back implementation if MySQL is not running. The programmers interface is to call &get_current_state() or some other primary interface subroutine (described below). The internals of this storage system are documented here. There are six tables used to store student performance data (the results of a dumpcurrent). Each of these tables is created in MySQL with a name of $courseid_*****, where ***** is 'symb', 'part', or whatever is appropriate for the table. The tables and their purposes are described below. Some notes before we get started. Each table must have a PRIMARY KEY, which is a column or set of columns which will serve to uniquely identify a row of data. NULL is not allowed! INDEXes work best on integer data. JOIN is used to combine data from many tables into one output. lonmysql.pm is used for some of the interface, specifically the table creation calls. The inserts are done in bulk by directly calling the database handler. The SELECT ... JOIN statement used to retrieve the data does not have an interface in lonmysql.pm and I shudder at the thought of writing one. =head3 Table Descriptions =over 4 =item Tables used to store meta information The following tables hold data required to keep track of the current status of a students data in the tables or to look up the students data in the tables. =over 4 =item $symb_table The symb_table has two columns. The first is a 'symb_id' and the second is the text name for the 'symb' (limited to 64k). The 'symb_id' is generated automatically by MySQL so inserts should be done on this table with an empty first element. This table has its PRIMARY KEY on the 'symb_id'. =item $part_table The part_table has two columns. The first is a 'part_id' and the second is the text name for the 'part' (limited to 100 characters). The 'part_id' is generated automatically by MySQL so inserts should be done on this table with an empty first element. This table has its PRIMARY KEY on the 'part' (100 characters) and a KEY on 'part_id'. =item $student_table The student_table has 7 columns. The first is a 'student_id' assigned by MySQL. The second is 'student' which is username:domain. The third through fifth are 'section', 'status' (enrollment status), and 'classification' (to be used in the future). The sixth and seventh ('updatetime' and 'fullupdatetime') contain the time of last update and full update of student data. This table has its PRIMARY KEY on the 'student_id' column and is indexed on 'student', 'section', and 'status'. =back =item Tables used to store current status data The following tables store data only about the students current status on a problem, meaning only the data related to the last attempt on a problem. =over 4 =item $performance_table The performance_table has 9 columns. The first three are 'symb_id', 'student_id', and 'part_id'. These comprise the PRIMARY KEY for this table and are directly related to the $symb_table, $student_table, and $part_table described above. MySQL does better indexing on numeric items than text, so we use these three "index tables". The remaining columns are 'solved', 'tries', 'awarded', 'award', 'awarddetail', and 'timestamp'. These are either the MySQL type TINYTEXT or various integers ('tries' and 'timestamp'). This table has KEYs of 'student_id' and 'symb_id'. For use of this table, see the functions described below. =item $parameters_table The parameters_table holds the data that does not fit neatly into the performance_table. The parameters table has four columns: 'symb_id', 'student_id', 'parameter', and 'value'. 'symb_id', 'student_id', and 'parameter' comprise the PRIMARY KEY for this table. 'parameter' is limited to 255 characters. 'value' is limited to 64k characters. =back =item Tables used for storing historic data The following tables are used to store almost all of the transactions a student has made on a homework problem. See loncapa/docs/homework/datastorage for specific information about each of the parameters stored. =over 4 =item $fulldump_response_table The response table holds data (documented in loncapa/docs/homework/datastorage) associated with a particular response id which is stored when a student attempts a problem. The following are the columns of the table, in order: 'symb_id','part_id','response_id','student_id','transaction','tries', 'awarddetail', 'response_specific' (data particular to the response type), 'response_specific_value', and 'submission (the text of the students submission). The primary key is based on the first five columns listed above. =item $fulldump_part_table The part table holds data (documented in loncapa/docs/homework/datastorage) associated with a particular part id which is stored when a student attempts a problem. The following are the columns of the table, in order: 'symb_id','part_id','student_id','transaction','tries','award','awarded', and 'previous'. The primary key is based on the first five columns listed above. =item $fulldump_timestamp_table The timestamp table holds the timestamps of the transactions which are stored in $fulldump_response_table and $fulldump_part_table. This data is about both the response and part data. Columns: 'symb_id','student_id', 'transaction', and 'timestamp'. The primary key is based on the first 3 columns. =back =back =head3 Important Subroutines Here is a brief overview of the subroutines which are likely to be of interest: =over 4 =item &get_current_state(): programmers interface. =item &init_dbs(): table creation =item &update_student_data(): data storage calls =item &get_student_data_from_performance_cache(): data retrieval =back =head3 Main Documentation =over 4 =cut ################################################ ################################################ ################################################ ################################################ { # Begin scope of table identifiers my $current_course =''; my $symb_table; my $part_table; my $student_table; my $performance_table; my $parameters_table; my $fulldump_response_table; my $fulldump_part_table; my $fulldump_timestamp_table; my @Tables; ################################################ ################################################ =pod =item &init_dbs() Input: course id Output: 0 on success, positive integer on error This routine issues the calls to lonmysql to create the tables used to store student data. =cut ################################################ ################################################ sub init_dbs { my $courseid = shift; &setup_table_names($courseid); # # Drop any of the existing tables foreach my $table (@Tables) { &Apache::lonmysql::drop_table($table); } # # Note - changes to this table must be reflected in the code that # stores the data (calls &Apache::lonmysql::store_row with this table # id my $symb_table_def = { id => $symb_table, permanent => 'no', columns => [{ name => 'symb_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL', auto_inc => 'yes', }, { name => 'symb', type => 'MEDIUMTEXT', restrictions => 'NOT NULL'}, ], 'PRIMARY KEY' => ['symb_id'], }; # my $part_table_def = { id => $part_table, permanent => 'no', columns => [{ name => 'part_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL', auto_inc => 'yes', }, { name => 'part', type => 'VARCHAR(100)', restrictions => 'NOT NULL'}, ], 'PRIMARY KEY' => ['part (100)'], 'KEY' => [{ columns => ['part_id']},], }; # my $student_table_def = { id => $student_table, permanent => 'no', columns => [{ name => 'student_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL', auto_inc => 'yes', }, { name => 'student', type => 'VARCHAR(100)', restrictions => 'NOT NULL UNIQUE'}, { name => 'section', type => 'VARCHAR(100)', restrictions => 'NOT NULL'}, { name => 'status', type => 'VARCHAR(15)', restrictions => 'NOT NULL'}, { name => 'classification', type => 'varchar(100)', }, { name => 'updatetime', type => 'INT UNSIGNED'}, { name => 'fullupdatetime', type => 'INT UNSIGNED'}, ], 'PRIMARY KEY' => ['student_id'], 'KEY' => [{ columns => ['student (100)', 'section (100)', 'status (15)',]},], }; # my $performance_table_def = { id => $performance_table, permanent => 'no', columns => [{ name => 'symb_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'student_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'part_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'part', type => 'VARCHAR(100)', restrictions => 'NOT NULL'}, { name => 'solved', type => 'TINYTEXT' }, { name => 'tries', type => 'SMALLINT UNSIGNED' }, { name => 'awarded', type => 'TINYTEXT' }, { name => 'award', type => 'TINYTEXT' }, { name => 'awarddetail', type => 'TINYTEXT' }, { name => 'timestamp', type => 'INT UNSIGNED'}, ], 'PRIMARY KEY' => ['symb_id','student_id','part_id'], 'KEY' => [{ columns=>['student_id'] }, { columns=>['symb_id'] },], }; # my $fulldump_part_table_def = { id => $fulldump_part_table, permanent => 'no', columns => [ { name => 'symb_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'part_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'student_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'transaction', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'tries', type => 'SMALLINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'award', type => 'TINYTEXT' }, { name => 'awarded', type => 'TINYTEXT' }, { name => 'previous', type => 'SMALLINT UNSIGNED' }, # { name => 'regrader', # type => 'TINYTEXT' }, # { name => 'afterduedate', # type => 'TINYTEXT' }, ], 'PRIMARY KEY' => ['symb_id','part_id','student_id','transaction'], 'KEY' => [ { columns=>['symb_id'] }, { columns=>['part_id'] }, { columns=>['student_id'] }, ], }; # my $fulldump_response_table_def = { id => $fulldump_response_table, permanent => 'no', columns => [ { name => 'symb_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'part_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'response_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'student_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'transaction', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'awarddetail', type => 'TINYTEXT' }, # { name => 'message', # type => 'CHAR' }, { name => 'response_specific', type => 'TINYTEXT' }, { name => 'response_specific_value', type => 'TINYTEXT' }, { name => 'submission', type => 'TEXT'}, ], 'PRIMARY KEY' => ['symb_id','part_id','response_id','student_id', 'transaction'], 'KEY' => [ { columns=>['symb_id'] }, { columns=>['part_id','response_id'] }, { columns=>['student_id'] }, ], }; my $fulldump_timestamp_table_def = { id => $fulldump_timestamp_table, permanent => 'no', columns => [ { name => 'symb_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'student_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'transaction', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'timestamp', type => 'INT UNSIGNED'}, ], 'PRIMARY KEY' => ['symb_id','student_id','transaction'], 'KEY' => [ { columns=>['symb_id'] }, { columns=>['student_id'] }, { columns=>['transaction'] }, ], }; # my $parameters_table_def = { id => $parameters_table, permanent => 'no', columns => [{ name => 'symb_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'student_id', type => 'MEDIUMINT UNSIGNED', restrictions => 'NOT NULL' }, { name => 'parameter', type => 'TINYTEXT', restrictions => 'NOT NULL' }, { name => 'value', type => 'MEDIUMTEXT' }, ], 'PRIMARY KEY' => ['symb_id','student_id','parameter (255)'], }; # # Create the tables my $tableid; $tableid = &Apache::lonmysql::create_table($symb_table_def); if (! defined($tableid)) { &Apache::lonnet::logthis("error creating symb_table: ". &Apache::lonmysql::get_error()); return 1; } # $tableid = &Apache::lonmysql::create_table($part_table_def); if (! defined($tableid)) { &Apache::lonnet::logthis("error creating part_table: ". &Apache::lonmysql::get_error()); return 2; } # $tableid = &Apache::lonmysql::create_table($student_table_def); if (! defined($tableid)) { &Apache::lonnet::logthis("error creating student_table: ". &Apache::lonmysql::get_error()); return 3; } # $tableid = &Apache::lonmysql::create_table($performance_table_def); if (! defined($tableid)) { &Apache::lonnet::logthis("error creating preformance_table: ". &Apache::lonmysql::get_error()); return 5; } # $tableid = &Apache::lonmysql::create_table($parameters_table_def); if (! defined($tableid)) { &Apache::lonnet::logthis("error creating parameters_table: ". &Apache::lonmysql::get_error()); return 6; } # $tableid = &Apache::lonmysql::create_table($fulldump_part_table_def); if (! defined($tableid)) { &Apache::lonnet::logthis("error creating fulldump_part_table: ". &Apache::lonmysql::get_error()); return 7; } # $tableid = &Apache::lonmysql::create_table($fulldump_response_table_def); if (! defined($tableid)) { &Apache::lonnet::logthis("error creating fulldump_response_table: ". &Apache::lonmysql::get_error()); return 8; } $tableid = &Apache::lonmysql::create_table($fulldump_timestamp_table_def); if (! defined($tableid)) { &Apache::lonnet::logthis("error creating fulldump_timestamp_table: ". &Apache::lonmysql::get_error()); return 9; } return 0; } ################################################ ################################################ =pod =item &delete_caches() =cut ################################################ ################################################ sub delete_caches { my $courseid = shift; $courseid = $ENV{'request.course.id'} if (! defined($courseid)); # &setup_table_names($courseid); # my $dbh = &Apache::lonmysql::get_dbh(); foreach my $table (@Tables) { my $command = 'DROP TABLE '.$table.';'; $dbh->do($command); if ($dbh->err) { &Apache::lonnet::logthis($command.' resulted in error: '.$dbh->errstr); } } return; } ################################################ ################################################ =pod =item &get_part_id() Get the MySQL id of a problem part string. Input: $part Output: undef on error, integer $part_id on success. =item &get_part() Get the string describing a part from the MySQL id of the problem part. Input: $part_id Output: undef on error, $part string on success. =cut ################################################ ################################################ my $have_read_part_table = 0; my %ids_by_part; my %parts_by_id; sub get_part_id { my ($part) = @_; $part = 0 if (! defined($part)); if (! $have_read_part_table) { my @Result = &Apache::lonmysql::get_rows($part_table); foreach (@Result) { $ids_by_part{$_->[1]}=$_->[0]; } $have_read_part_table = 1; } if (! exists($ids_by_part{$part})) { &Apache::lonmysql::store_row($part_table,[undef,$part]); undef(%ids_by_part); my @Result = &Apache::lonmysql::get_rows($part_table); foreach (@Result) { $ids_by_part{$_->[1]}=$_->[0]; } } return $ids_by_part{$part} if (exists($ids_by_part{$part})); return undef; # error } sub get_part { my ($part_id) = @_; if (! exists($parts_by_id{$part_id}) || ! defined($parts_by_id{$part_id}) || $parts_by_id{$part_id} eq '') { my @Result = &Apache::lonmysql::get_rows($part_table); foreach (@Result) { $parts_by_id{$_->[0]}=$_->[1]; } } return $parts_by_id{$part_id} if(exists($parts_by_id{$part_id})); return undef; # error } ################################################ ################################################ =pod =item &get_symb_id() Get the MySQL id of a symb. Input: $symb Output: undef on error, integer $symb_id on success. =item &get_symb() Get the symb associated with a MySQL symb_id. Input: $symb_id Output: undef on error, $symb on success. =cut ################################################ ################################################ my $have_read_symb_table = 0; my %ids_by_symb; my %symbs_by_id; sub get_symb_id { my ($symb) = @_; if (! $have_read_symb_table) { my @Result = &Apache::lonmysql::get_rows($symb_table); foreach (@Result) { $ids_by_symb{$_->[1]}=$_->[0]; } $have_read_symb_table = 1; } if (! exists($ids_by_symb{$symb})) { &Apache::lonmysql::store_row($symb_table,[undef,$symb]); undef(%ids_by_symb); my @Result = &Apache::lonmysql::get_rows($symb_table); foreach (@Result) { $ids_by_symb{$_->[1]}=$_->[0]; } } return $ids_by_symb{$symb} if(exists( $ids_by_symb{$symb})); return undef; # error } sub get_symb { my ($symb_id) = @_; if (! exists($symbs_by_id{$symb_id}) || ! defined($symbs_by_id{$symb_id}) || $symbs_by_id{$symb_id} eq '') { my @Result = &Apache::lonmysql::get_rows($symb_table); foreach (@Result) { $symbs_by_id{$_->[0]}=$_->[1]; } } return $symbs_by_id{$symb_id} if(exists( $symbs_by_id{$symb_id})); return undef; # error } ################################################ ################################################ =pod =item &get_student_id() Get the MySQL id of a student. Input: $sname, $dom Output: undef on error, integer $student_id on success. =item &get_student() Get student username:domain associated with the MySQL student_id. Input: $student_id Output: undef on error, string $student (username:domain) on success. =cut ################################################ ################################################ my $have_read_student_table = 0; my %ids_by_student; my %students_by_id; sub get_student_id { my ($sname,$sdom) = @_; my $student = $sname.':'.$sdom; if (! $have_read_student_table) { my @Result = &Apache::lonmysql::get_rows($student_table); foreach (@Result) { $ids_by_student{$_->[1]}=$_->[0]; } $have_read_student_table = 1; } if (! exists($ids_by_student{$student})) { &populate_student_table(); undef(%ids_by_student); undef(%students_by_id); my @Result = &Apache::lonmysql::get_rows($student_table); foreach (@Result) { $ids_by_student{$_->[1]}=$_->[0]; } } return $ids_by_student{$student} if(exists( $ids_by_student{$student})); return undef; # error } sub get_student { my ($student_id) = @_; if (! exists($students_by_id{$student_id}) || ! defined($students_by_id{$student_id}) || $students_by_id{$student_id} eq '') { my @Result = &Apache::lonmysql::get_rows($student_table); foreach (@Result) { $students_by_id{$_->[0]}=$_->[1]; } } return $students_by_id{$student_id} if(exists($students_by_id{$student_id})); return undef; # error } sub populate_student_table { my ($courseid) = @_; if (! defined($courseid)) { $courseid = $ENV{'request.course.id'}; } # &setup_table_names($courseid); my $dbh = &Apache::lonmysql::get_dbh(); my $request = 'INSERT IGNORE INTO '.$student_table. "(student,section,status) VALUES "; my $classlist = &get_classlist($courseid); my $student_count=0; while (my ($student,$data) = each %$classlist) { my ($section,$status) = ($data->[&CL_SECTION()], $data->[&CL_STATUS()]); if ($section eq '' || $section =~ /^\s*$/) { $section = 'none'; } $request .= "('".$student."','".$section."','".$status."'),"; $student_count++; } return if ($student_count == 0); chop($request); $dbh->do($request); if ($dbh->err()) { &Apache::lonnet::logthis("error ".$dbh->errstr(). " occured executing \n". $request); } return; } ################################################ ################################################ =pod =item &clear_internal_caches() Causes the internal caches used in get_student_id, get_student, get_symb_id, get_symb, get_part_id, and get_part to be undef'd. Needs to be called before the first operation with the MySQL database for a given Apache request. =cut ################################################ ################################################ sub clear_internal_caches { $have_read_part_table = 0; undef(%ids_by_part); undef(%parts_by_id); $have_read_symb_table = 0; undef(%ids_by_symb); undef(%symbs_by_id); $have_read_student_table = 0; undef(%ids_by_student); undef(%students_by_id); } ################################################ ################################################ =pod =item &update_full_student_data($sname,$sdom,$courseid) Does a lonnet::dump on a student to populate the courses tables. Input: $sname, $sdom, $courseid Output: $returnstatus $returnstatus is a string describing any errors that occured. 'okay' is the default. This subroutine loads a students data using lonnet::dump and inserts it into the MySQL database. The inserts are done on three tables, $fulldump_response_table, $fulldump_part_table, and $fulldump_timestamp_table. The INSERT calls are made directly by this subroutine, not through lonmysql because we do a 'bulk'insert which takes advantage of MySQLs non-SQL compliant INSERT command to insert multiple rows at a time. If anything has gone wrong during this process, $returnstatus is updated with a description of the error. Once the "fulldump" tables are updated, the tables used for chart and spreadsheet (which hold only the current state of the student on their homework, not historical data) are updated. If all updates have occured successfully, $student_table is updated to reflect the time of the update. Notice we do not insert the data and immediately query it. This means it is possible for there to be data returned this first time that is not available the second time. CYA. =cut ################################################ ################################################ sub update_full_student_data { my ($sname,$sdom,$courseid) = @_; # # Set up database names &setup_table_names($courseid); # my $student_id = &get_student_id($sname,$sdom); my $student = $sname.':'.$sdom; # my $returnstatus = 'okay'; # # Download students data my $time_of_retrieval = time; my @tmp = &Apache::lonnet::dump($courseid,$sdom,$sname); if (@tmp && $tmp[0] =~ /^error/) { $returnstatus = 'error retrieving full student data'; return $returnstatus; } elsif (! @tmp) { $returnstatus = 'okay: no student data'; return $returnstatus; } my %studentdata = @tmp; # # Get database handle and clean out the tables my $dbh = &Apache::lonmysql::get_dbh(); $dbh->do('DELETE FROM '.$fulldump_response_table.' WHERE student_id='. $student_id); $dbh->do('DELETE FROM '.$fulldump_part_table.' WHERE student_id='. $student_id); $dbh->do('DELETE FROM '.$fulldump_timestamp_table.' WHERE student_id='. $student_id); # # Parse and store the data into a form we can handle my $partdata; my $respdata; while (my ($key,$value) = each(%studentdata)) { next if ($key =~ /^(\d+):(resource$|subnum$|keys:)/); my ($transaction,$symb,$parameter) = split(':',$key); my $symb_id = &get_symb_id($symb); if ($parameter eq 'timestamp') { # We can deal with 'timestamp' right away my @timestamp_storage = ($symb_id,$student_id, $transaction,$value); my $store_command = 'INSERT IGNORE INTO '.$fulldump_timestamp_table. " VALUES ('".join("','",@timestamp_storage)."');"; $dbh->do($store_command); if ($dbh->err()) { &Apache::lonnet::logthis('unable to execute '.$store_command); &Apache::lonnet::logthis($dbh->errstr()); } next; } elsif ($parameter eq 'version') { next; } elsif ($parameter =~ /^resource\.(.*)\.(tries| award| awarded| previous| solved| awarddetail| submission| submissiongrading| molecule)\s*$/x){ # we do not have enough information to store an # entire row, so we save it up until later. my ($part_and_resp_id,$field) = ($1,$2); my ($part,$part_id,$resp,$resp_id); if ($part_and_resp_id =~ /\./) { ($part,$resp) = split(/\./,$part_and_resp_id); $part_id = &get_part_id($part); $resp_id = &get_part_id($resp); } else { $part_id = &get_part_id($part_and_resp_id); } # Deal with part specific data if ($field =~ /^(tries|award|awarded|previous)$/) { $partdata->{$symb_id}->{$part_id}->{$transaction}->{$field}=$value; } # deal with response specific data if (defined($resp_id) && $field =~ /^(awarddetail| submission| submissiongrading| molecule)$/x) { if ($field eq 'submission') { # We have to be careful with user supplied input. # most of the time we are okay because it is escaped. # However, there is one wrinkle: submissions which end in # and odd number of '\' cause insert errors to occur. # Best trap this somehow... $value = $dbh->quote($value); } if ($field eq 'submissiongrading' || $field eq 'molecule') { $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{'response_specific'}=$field; $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{'response_specific_value'}=$value; } else { $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{$field}=$value; } } } } ## ## Store the part data my $store_command = 'INSERT IGNORE INTO '.$fulldump_part_table. ' VALUES '."\n"; my $store_rows = 0; while (my ($symb_id,$hash1) = each (%$partdata)) { while (my ($part_id,$hash2) = each (%$hash1)) { while (my ($transaction,$data) = each (%$hash2)) { $store_command .= "('".join("','",$symb_id,$part_id, $student_id, $transaction, $data->{'tries'}, $data->{'award'}, $data->{'awarded'}, $data->{'previous'})."'),"; $store_rows++; } } } if ($store_rows) { chop($store_command); $dbh->do($store_command); if ($dbh->err) { $returnstatus = 'error storing part data'; &Apache::lonnet::logthis('insert error '.$dbh->errstr()); &Apache::lonnet::logthis("While attempting\n".$store_command); } } ## ## Store the response data $store_command = 'INSERT IGNORE INTO '.$fulldump_response_table. ' VALUES '."\n"; $store_rows = 0; while (my ($symb_id,$hash1) = each (%$respdata)) { while (my ($part_id,$hash2) = each (%$hash1)) { while (my ($resp_id,$hash3) = each (%$hash2)) { while (my ($transaction,$data) = each (%$hash3)) { my $submission = $data->{'submission'}; # We have to be careful with user supplied input. # most of the time we are okay because it is escaped. # However, there is one wrinkle: submissions which end in # and odd number of '\' cause insert errors to occur. # Best trap this somehow... $submission = $dbh->quote($submission); $store_command .= "('". join("','",$symb_id,$part_id, $resp_id,$student_id, $transaction, $data->{'awarddetail'}, $data->{'response_specific'}, $data->{'response_specific_value'}). "',".$submission."),"; $store_rows++; } } } } if ($store_rows) { chop($store_command); $dbh->do($store_command); if ($dbh->err) { $returnstatus = 'error storing response data'; &Apache::lonnet::logthis('insert error '.$dbh->errstr()); &Apache::lonnet::logthis("While attempting\n".$store_command); } } ## ## Update the students "current" data in the performance ## and parameters tables. my ($status,undef) = &store_student_data ($sname,$sdom,$courseid, &Apache::lonnet::convert_dump_to_currentdump(\%studentdata)); if ($returnstatus eq 'okay' && $status ne 'okay') { $returnstatus = 'error storing current data:'.$status; } elsif ($status ne 'okay') { $returnstatus .= ' error storing current data:'.$status; } ## ## Update the students time...... if ($returnstatus eq 'okay') { &store_updatetime($student_id,$time_of_retrieval,$time_of_retrieval); if ($dbh->err) { if ($returnstatus eq 'okay') { $returnstatus = 'error updating student time'; } else { $returnstatus = 'error updating student time'; } } } return $returnstatus; } ################################################ ################################################ =pod =item &update_student_data() Input: $sname, $sdom, $courseid Output: $returnstatus, \%student_data $returnstatus is a string describing any errors that occured. 'okay' is the default. \%student_data is the data returned by a call to lonnet::currentdump. This subroutine loads a students data using lonnet::currentdump and inserts it into the MySQL database. The inserts are done on two tables, $performance_table and $parameters_table. $parameters_table holds the data that is not included in $performance_table. See the description of $performance_table elsewhere in this file. The INSERT calls are made directly by this subroutine, not through lonmysql because we do a 'bulk' insert which takes advantage of MySQLs non-SQL compliant INSERT command to insert multiple rows at a time. If anything has gone wrong during this process, $returnstatus is updated with a description of the error and \%student_data is returned. Notice we do not insert the data and immediately query it. This means it is possible for there to be data returned this first time that is not available the second time. CYA. =cut ################################################ ################################################ sub update_student_data { my ($sname,$sdom,$courseid) = @_; # # Set up database names &setup_table_names($courseid); # my $student_id = &get_student_id($sname,$sdom); my $student = $sname.':'.$sdom; # my $returnstatus = 'okay'; # # Download students data my $time_of_retrieval = time; my @tmp = &Apache::lonnet::currentdump($courseid,$sdom,$sname); if ((scalar(@tmp) > 0) && ($tmp[0] =~ /^error:/)) { &Apache::lonnet::logthis('error getting data for '. $sname.':'.$sdom.' in course '.$courseid. ':'.$tmp[0]); $returnstatus = 'error getting data'; return ($returnstatus,undef); } if (scalar(@tmp) < 1) { return ('no data',undef); } my %student_data = @tmp; my @Results = &store_student_data($sname,$sdom,$courseid,\%student_data); # # Set the students update time if ($Results[0] eq 'okay') { &store_updatetime($student_id,$time_of_retrieval,$time_of_retrieval); } # return @Results; } sub store_updatetime { my ($student_id,$updatetime,$fullupdatetime)=@_; my $values = ''; if (defined($updatetime)) { $values = 'updatetime='.$updatetime.' '; } if (defined($fullupdatetime)) { if ($values ne '') { $values .= ','; } $values .= 'fullupdatetime='.$fullupdatetime.' '; } return if ($values eq ''); my $dbh = &Apache::lonmysql::get_dbh(); my $request = 'UPDATE '.$student_table.' SET '.$values. ' WHERE student_id='.$student_id.' LIMIT 1'; $dbh->do($request); } sub store_student_data { my ($sname,$sdom,$courseid,$student_data) = @_; # my $student_id = &get_student_id($sname,$sdom); my $student = $sname.':'.$sdom; # my $returnstatus = 'okay'; # # Remove all of the students data from the table my $dbh = &Apache::lonmysql::get_dbh(); $dbh->do('DELETE FROM '.$performance_table.' WHERE student_id='. $student_id); $dbh->do('DELETE FROM '.$parameters_table.' WHERE student_id='. $student_id); # # Store away the data # my $starttime = Time::HiRes::time; my $elapsed = 0; my $rows_stored; my $store_parameters_command = 'INSERT IGNORE INTO '.$parameters_table. ' VALUES '."\n"; my $num_parameters = 0; my $store_performance_command = 'INSERT IGNORE INTO '.$performance_table. ' VALUES '."\n"; return ('error',undef) if (! defined($dbh)); while (my ($current_symb,$param_hash) = each(%{$student_data})) { # # make sure the symb is set up properly my $symb_id = &get_symb_id($current_symb); # # Load data into the tables while (my ($parameter,$value) = each(%$param_hash)) { my $newstring; if ($parameter !~ /(timestamp|resource\.(.*)\.(solved|tries|awarded|award|awarddetail|previous))/) { $newstring = "('".join("','", $symb_id,$student_id, $parameter)."',". $dbh->quote($value)."),\n"; $num_parameters ++; if ($newstring !~ /''/) { $store_parameters_command .= $newstring; $rows_stored++; } } next if ($parameter !~ /^resource\.(.*)\.solved$/); # my $part = $1; my $part_id = &get_part_id($part); next if (!defined($part_id)); my $solved = $value; my $tries = $param_hash->{'resource.'.$part.'.tries'}; my $awarded = $param_hash->{'resource.'.$part.'.awarded'}; my $award = $param_hash->{'resource.'.$part.'.award'}; my $awarddetail = $param_hash->{'resource.'.$part.'.awarddetail'}; my $timestamp = $param_hash->{'timestamp'}; # $solved = '' if (! defined($solved)); $tries = '' if (! defined($tries)); $awarded = '' if (! defined($awarded)); $award = '' if (! defined($award)); $awarddetail = '' if (! defined($awarddetail)); $newstring = "('".join("','",$symb_id,$student_id,$part_id,$part, $solved,$tries,$awarded,$award, $awarddetail,$timestamp)."'),\n"; $store_performance_command .= $newstring; $rows_stored++; } } chop $store_parameters_command; chop $store_parameters_command; chop $store_performance_command; chop $store_performance_command; my $start = Time::HiRes::time; $dbh->do($store_performance_command); if ($dbh->err()) { &Apache::lonnet::logthis(' bigass insert error:'.$dbh->errstr()); &Apache::lonnet::logthis('command = '.$store_performance_command); $returnstatus = 'error: unable to insert performance into database'; return ($returnstatus,$student_data); } $dbh->do($store_parameters_command) if ($num_parameters>0); if ($dbh->err()) { &Apache::lonnet::logthis(' bigass insert error:'.$dbh->errstr()); &Apache::lonnet::logthis('command = '.$store_parameters_command); &Apache::lonnet::logthis('rows_stored = '.$rows_stored); &Apache::lonnet::logthis('student_id = '.$student_id); $returnstatus = 'error: unable to insert parameters into database'; return ($returnstatus,$student_data); } $elapsed += Time::HiRes::time - $start; return ($returnstatus,$student_data); } ###################################### ###################################### =pod =item &ensure_tables_are_set_up($courseid) Checks to be sure the MySQL tables for the given class are set up. If $courseid is omitted it will be obtained from the environment. Returns nothing on success and 'error' on failure =cut ###################################### ###################################### sub ensure_tables_are_set_up { my ($courseid) = @_; $courseid = $ENV{'request.course.id'} if (! defined($courseid)); # # Clean out package variables &setup_table_names($courseid); # # if the tables do not exist, make them my @CurrentTable = &Apache::lonmysql::tables_in_db(); my ($found_symb,$found_student,$found_part, $found_performance,$found_parameters,$found_fulldump_part, $found_fulldump_response,$found_fulldump_timestamp); foreach (@CurrentTable) { $found_symb = 1 if ($_ eq $symb_table); $found_student = 1 if ($_ eq $student_table); $found_part = 1 if ($_ eq $part_table); $found_performance = 1 if ($_ eq $performance_table); $found_parameters = 1 if ($_ eq $parameters_table); $found_fulldump_part = 1 if ($_ eq $fulldump_part_table); $found_fulldump_response = 1 if ($_ eq $fulldump_response_table); $found_fulldump_timestamp = 1 if ($_ eq $fulldump_timestamp_table); } if (!$found_symb || !$found_student || !$found_part || !$found_performance || !$found_parameters || !$found_fulldump_part || !$found_fulldump_response || !$found_fulldump_timestamp ) { if (&init_dbs($courseid)) { return 'error'; } } } ################################################ ################################################ =pod =item &ensure_current_data() Input: $sname, $sdom, $courseid Output: $status, $data This routine ensures the data for a given student is up to date. The $student_table is queried to determine the time of the last update. If the students data is out of date, &update_student_data() is called. The return values from the call to &update_student_data() are returned. =cut ################################################ ################################################ sub ensure_current_data { my ($sname,$sdom,$courseid) = @_; my $status = 'okay'; # return value # $courseid = $ENV{'request.course.id'} if (! defined($courseid)); &ensure_tables_are_set_up($courseid); # # Get the update time for the user my $updatetime = 0; my $modifiedtime = &Apache::lonnet::GetFileTimestamp ($sdom,$sname,$courseid.'.db', $Apache::lonnet::perlvar{'lonUsersDir'}); # my $student_id = &get_student_id($sname,$sdom); my @Result = &Apache::lonmysql::get_rows($student_table, "student_id ='$student_id'"); my $data = undef; if (@Result) { $updatetime = $Result[0]->[5]; # Ack! This is dumb! } if ($modifiedtime > $updatetime) { ($status,$data) = &update_student_data($sname,$sdom,$courseid); } return ($status,$data); } ################################################ ################################################ =pod =item &ensure_current_full_data($sname,$sdom,$courseid) Input: $sname, $sdom, $courseid Output: $status This routine ensures the fulldata (the data from a lonnet::dump, not a lonnet::currentdump) for a given student is up to date. The $student_table is queried to determine the time of the last update. If the students fulldata is out of date, &update_full_student_data() is called. The return value from the call to &update_full_student_data() is returned. =cut ################################################ ################################################ sub ensure_current_full_data { my ($sname,$sdom,$courseid) = @_; my $status = 'okay'; # return value # $courseid = $ENV{'request.course.id'} if (! defined($courseid)); &ensure_tables_are_set_up($courseid); # # Get the update time for the user my $modifiedtime = &Apache::lonnet::GetFileTimestamp ($sdom,$sname,$courseid.'.db', $Apache::lonnet::perlvar{'lonUsersDir'}); # my $student_id = &get_student_id($sname,$sdom); my @Result = &Apache::lonmysql::get_rows($student_table, "student_id ='$student_id'"); my $updatetime; if (@Result && ref($Result[0]) eq 'ARRAY') { $updatetime = $Result[0]->[6]; } if (! defined($updatetime) || $modifiedtime > $updatetime) { $status = &update_full_student_data($sname,$sdom,$courseid); } return $status; } ################################################ ################################################ =pod =item &get_student_data_from_performance_cache() Input: $sname, $sdom, $symb, $courseid Output: hash reference containing the data for the given student. If $symb is undef, all the students data is returned. This routine is the heart of the local caching system. See the description of $performance_table, $symb_table, $student_table, and $part_table. The main task is building the MySQL request. The tables appear in the request in the order in which they should be parsed by MySQL. When searching on a student the $student_table is used to locate the 'student_id'. All rows in $performance_table which have a matching 'student_id' are returned, with data from $part_table and $symb_table which match the entries in $performance_table, 'part_id' and 'symb_id'. When searching on a symb, the $symb_table is processed first, with matching rows grabbed from $performance_table and filled in from $part_table and $student_table in that order. Running 'EXPLAIN ' on the 'SELECT' statements generated can be quite interesting, especially if you play with the order the tables are listed. =cut ################################################ ################################################ sub get_student_data_from_performance_cache { my ($sname,$sdom,$symb,$courseid)=@_; my $student = $sname.':'.$sdom if (defined($sname) && defined($sdom)); &setup_table_names($courseid); # # Return hash my $studentdata; # my $dbh = &Apache::lonmysql::get_dbh(); my $request = "SELECT ". "d.symb,a.part,a.solved,a.tries,a.awarded,a.award,a.awarddetail,". "a.timestamp "; if (defined($student)) { $request .= "FROM $student_table AS b ". "LEFT JOIN $performance_table AS a ON b.student_id=a.student_id ". # "LEFT JOIN $part_table AS c ON c.part_id = a.part_id ". "LEFT JOIN $symb_table AS d ON d.symb_id = a.symb_id ". "WHERE student='$student'"; if (defined($symb) && $symb ne '') { $request .= " AND d.symb=".$dbh->quote($symb); } } elsif (defined($symb) && $symb ne '') { $request .= "FROM $symb_table as d ". "LEFT JOIN $performance_table AS a ON d.symb_id=a.symb_id ". # "LEFT JOIN $part_table AS c ON c.part_id = a.part_id ". "LEFT JOIN $student_table AS b ON b.student_id = a.student_id ". "WHERE symb='".$dbh->quote($symb)."'"; } my $starttime = Time::HiRes::time; my $rows_retrieved = 0; my $sth = $dbh->prepare($request); $sth->execute(); if ($sth->err()) { &Apache::lonnet::logthis("Unable to execute MySQL request:"); &Apache::lonnet::logthis("\n".$request."\n"); &Apache::lonnet::logthis("error is:".$sth->errstr()); return undef; } foreach my $row (@{$sth->fetchall_arrayref}) { $rows_retrieved++; my ($symb,$part,$solved,$tries,$awarded,$award,$awarddetail,$time) = (@$row); my $base = 'resource.'.$part; $studentdata->{$symb}->{$base.'.solved'} = $solved; $studentdata->{$symb}->{$base.'.tries'} = $tries; $studentdata->{$symb}->{$base.'.awarded'} = $awarded; $studentdata->{$symb}->{$base.'.award'} = $award; $studentdata->{$symb}->{$base.'.awarddetail'} = $awarddetail; $studentdata->{$symb}->{'timestamp'} = $time if (defined($time) && $time ne ''); } ## Get misc parameters $request = 'SELECT c.symb,a.parameter,a.value '. "FROM $student_table AS b ". "LEFT JOIN $parameters_table AS a ON b.student_id=a.student_id ". "LEFT JOIN $symb_table AS c ON c.symb_id = a.symb_id ". "WHERE student='$student'"; if (defined($symb) && $symb ne '') { $request .= " AND c.symb=".$dbh->quote($symb); } $sth = $dbh->prepare($request); $sth->execute(); if ($sth->err()) { &Apache::lonnet::logthis("Unable to execute MySQL request:"); &Apache::lonnet::logthis("\n".$request."\n"); &Apache::lonnet::logthis("error is:".$sth->errstr()); if (defined($symb) && $symb ne '') { $studentdata = $studentdata->{$symb}; } return $studentdata; } # foreach my $row (@{$sth->fetchall_arrayref}) { $rows_retrieved++; my ($symb,$parameter,$value) = (@$row); $studentdata->{$symb}->{$parameter} = $value; } # if (defined($symb) && $symb ne '') { $studentdata = $studentdata->{$symb}; } return $studentdata; } ################################################ ################################################ =pod =item &get_current_state() Input: $sname,$sdom,$symb,$courseid Output: Described below Retrieve the current status of a students performance. $sname and $sdom are the only required parameters. If $symb is undef the results of an &Apache::lonnet::currentdump() will be returned. If $courseid is undef it will be retrieved from the environment. The return structure is based on &Apache::lonnet::currentdump. If $symb is unspecified, all the students data is returned in a hash of the form: ( symb1 => { param1 => value1, param2 => value2 ... }, symb2 => { param1 => value1, param2 => value2 ... }, ) If $symb is specified, a hash of ( param1 => value1, param2 => value2, ) is returned. If no data is found for $symb, or if the student has no performance data, an empty list is returned. =cut ################################################ ################################################ sub get_current_state { my ($sname,$sdom,$symb,$courseid,$forcedownload)=@_; # $courseid = $ENV{'request.course.id'} if (! defined($courseid)); # return () if (! defined($sname) || ! defined($sdom)); # my ($status,$data) = &ensure_current_data($sname,$sdom,$courseid); # &Apache::lonnet::logthis # ('sname = '.$sname. # ' domain = '.$sdom. # ' status = '.$status. # ' data is '.(defined($data)?'defined':'undefined')); # while (my ($symb,$hash) = each(%$data)) { # &Apache::lonnet::logthis($symb."\n----------------------------------"); # while (my ($key,$value) = each (%$hash)) { # &Apache::lonnet::logthis(" ".$key." = ".$value); # } # } # if (defined($data) && defined($symb) && ref($data->{$symb})) { return %{$data->{$symb}}; } elsif (defined($data) && ! defined($symb) && ref($data)) { return %$data; } if ($status eq 'no data') { return (); } else { if ($status ne 'okay' && $status ne '') { &Apache::lonnet::logthis('status = '.$status); return (); } my $returnhash = &get_student_data_from_performance_cache($sname,$sdom, $symb,$courseid); return %$returnhash if (defined($returnhash)); } return (); } ################################################ ################################################ =pod =item &get_problem_statistics() Gather data on a given problem. The database is assumed to be populated and all local caching variables are assumed to be set properly. This means you need to call &ensure_current_data for the students you are concerned with prior to calling this routine. Inputs: $Sections, $status, $symb, $part, $courseid, $starttime, $endtime =over 4 =item $Sections Array ref containing section names for students. 'all' is allowed to be the first (and only) item in the array. =item $status String describing the status of students =item $symb is the symb for the problem. =item $part is the part id you need statistics for =item $courseid is the course id, of course! =item $starttime and $endtime are unix times which to use to limit the statistical data. =back Outputs: See the code for up to date information. A hash reference is returned. The hash has the following keys defined: =over 4 =item num_students The number of students attempting the problem =item tries The total number of tries for the students =item max_tries The maximum number of tries taken =item mean_tries The average number of tries =item num_solved The number of students able to solve the problem =item num_override The number of students whose answer is 'correct_by_override' =item deg_of_diff The degree of difficulty of the problem =item std_tries The standard deviation of the number of tries =item skew_tries The skew of the number of tries =item per_wrong The number of students attempting the problem who were not able to answer it correctly. =back =cut ################################################ ################################################ sub get_problem_statistics { my ($Sections,$status,$symb,$part,$courseid,$starttime,$endtime) = @_; return if (! defined($symb) || ! defined($part)); $courseid = $ENV{'request.course.id'} if (! defined($courseid)); # &setup_table_names($courseid); my $symb_id = &get_symb_id($symb); my $part_id = &get_part_id($part); my $stats_table = $courseid.'_problem_stats'; # my $dbh = &Apache::lonmysql::get_dbh(); return undef if (! defined($dbh)); # # Clean out the table $dbh->do('DROP TABLE '.$stats_table); # May return an error my $request = 'CREATE TEMPORARY TABLE '.$stats_table.' '. 'SELECT a.student_id,a.solved,a.award,a.awarded,a.tries '. 'FROM '.$performance_table.' AS a '; # # See if we need to include some requirements on the students if ((defined($Sections) && lc($Sections->[0]) ne 'all') || (defined($status) && lc($status) ne 'any')) { $request .= 'NATURAL LEFT JOIN '.$student_table.' AS b '; } $request .= ' WHERE a.symb_id='.$symb_id.' AND a.part_id='.$part_id; # # Limit the students included to those specified if (defined($Sections) && lc($Sections->[0]) ne 'all') { $request .= ' AND ('. join(' OR ', map { "b.section='".$_."'" } @$Sections ).')'; } if (defined($status) && lc($status) ne 'any') { $request .= " AND b.status='".$status."'"; } # # Limit by starttime and endtime my $time_requirements = undef; if (defined($starttime)) { $time_requirements .= 'a.timestamp>='.$starttime; if (defined($endtime)) { $time_requirements .= ' AND a.timestamp<='.$endtime; } } elsif (defined($endtime)) { $time_requirements .= 'a.timestamp<='.$endtime; } if (defined($time_requirements)) { $request .= ' AND '.$time_requirements; } # # Finally, execute the request to create the temporary table $dbh->do($request); # # Collect the first suite of statistics $request = 'SELECT COUNT(*),SUM(tries),MAX(tries),AVG(tries),STD(tries) '. 'FROM '.$stats_table; my ($num,$tries,$mod,$mean,$STD) = &execute_SQL_request ($dbh,$request); $request = 'SELECT SUM(awarded) FROM '.$stats_table; my ($Solved) = &execute_SQL_request($dbh,$request); $request = 'SELECT SUM(awarded) FROM '.$stats_table. " WHERE solved='correct_by_override'"; my ($solved) = &execute_SQL_request($dbh,$request); # $num = 0 if (! defined($num)); $tries = 0 if (! defined($tries)); $mod = 0 if (! defined($mod)); $STD = 0 if (! defined($STD)); $Solved = 0 if (! defined($Solved)); $solved = 0 if (! defined($solved)); # # Compute the more complicated statistics my $DegOfDiff = 'nan'; $DegOfDiff = 1-($Solved)/$tries if ($tries>0); # my $SKEW = 'nan'; my $wrongpercent = 0; if ($num > 0) { ($SKEW) = &execute_SQL_request($dbh,'SELECT SQRT(SUM('. 'POWER(tries - '.$STD.',3)'. '))/'.$num.' FROM '.$stats_table); $wrongpercent=int(10*100*($num-$Solved+$solved)/$num)/10; } # # Drop the temporary table $dbh->do('DROP TABLE '.$stats_table); # May return an error # # Store in metadata if ($num) { my %storestats=(); # my $urlres=(&Apache::lonnet::decode_symb($symb))[2]; # $storestats{$courseid.'___'.$urlres.'___timestamp'}=time; $storestats{$courseid.'___'.$urlres.'___stdno'}=$num; $storestats{$courseid.'___'.$urlres.'___avetries'}=$mean; $storestats{$courseid.'___'.$urlres.'___difficulty'}=$DegOfDiff; # $urlres=~/^(\w+)\/(\w+)/; &Apache::lonnet::put('nohist_resevaldata',\%storestats,$1,$2); } # # Return result return { num_students => $num, tries => $tries, max_tries => $mod, mean_tries => $mean, std_tries => $STD, skew_tries => $SKEW, num_solved => $Solved, num_override => $solved, per_wrong => $wrongpercent, deg_of_diff => $DegOfDiff }; } sub execute_SQL_request { my ($dbh,$request)=@_; # &Apache::lonnet::logthis($request); my $sth = $dbh->prepare($request); $sth->execute(); my $row = $sth->fetchrow_arrayref(); if (ref($row) eq 'ARRAY' && scalar(@$row)>0) { return @$row; } return (); } sub get_student_data { my ($students,$courseid) = @_; $courseid = $ENV{'request.course.id'} if (! defined($courseid)); &setup_table_names($courseid); my $dbh = &Apache::lonmysql::get_dbh(); return undef if (! defined($dbh)); my $request = 'SELECT '. 'student_id, student '. 'FROM '.$student_table; if (defined($students)) { $request .= ' WHERE ('. join(' OR ', map {'student_id='. &get_student_id($_->{'username'}, $_->{'domain'}) } @$students ).')'; } $request.= ' ORDER BY student_id'; my $sth = $dbh->prepare($request); $sth->execute(); if ($dbh->err) { &Apache::lonnet::logthis('error = '.$dbh->errstr()); return undef; } my $dataset = $sth->fetchall_arrayref(); if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) { return $dataset; } } sub RD_student_id { return 0; } sub RD_awarddetail { return 1; } sub RD_response_eval { return 2; } sub RD_submission { return 3; } sub RD_timestamp { return 4; } sub RD_tries { return 5; } sub RD_sname { return 6; } sub get_response_data { my ($Sections,$enrollment,$symb,$response,$courseid) = @_; return undef if (! defined($symb) || ! defined($response)); $courseid = $ENV{'request.course.id'} if (! defined($courseid)); # &setup_table_names($courseid); my $symb_id = &get_symb_id($symb); my $response_id = &get_part_id($response); # my $dbh = &Apache::lonmysql::get_dbh(); return undef if (! defined($dbh)); # my $student_requirements; if ( (defined($Sections) && $Sections->[0] ne 'all')) { $student_requirements = '('. join(' OR ', map { "d.section='".$_."'" } @$Sections ).')'; } # my $enrollment_requirements=undef; if (defined($enrollment) && $enrollment ne 'Any') { $enrollment_requirements = "d.status='".$enrollment."'"; } my $request = 'SELECT '. 'a.student_id, a.awarddetail, a.response_specific_value, '. 'a.submission, b.timestamp, c.tries, d.student '. 'FROM '.$fulldump_response_table.' AS a '. 'LEFT JOIN '.$fulldump_timestamp_table.' AS b '. 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '. 'a.transaction = b.transaction '. 'LEFT JOIN '.$fulldump_part_table.' AS c '. 'ON a.symb_id=c.symb_id AND a.student_id=c.student_id AND '. 'a.part_id=c.part_id AND a.transaction = c.transaction '. 'LEFT JOIN '.$student_table.' AS d '. 'ON a.student_id=d.student_id '. 'WHERE '. 'a.symb_id='.$symb_id.' AND a.response_id='.$response_id; if (defined($student_requirements) || defined($enrollment_requirements)) { $request .= ' AND '; if (defined($student_requirements)) { $request .= $student_requirements.' AND '; } if (defined($enrollment_requirements)) { $request .= $enrollment_requirements.' AND '; } $request =~ s/( AND )$//; } $request .= ' ORDER BY b.timestamp'; # &Apache::lonnet::logthis("request =\n".$request); my $sth = $dbh->prepare($request); $sth->execute(); if ($dbh->err) { &Apache::lonnet::logthis('error = '.$dbh->errstr()); return undef; } my $dataset = $sth->fetchall_arrayref(); if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) { # Clear the \'s from around the submission for (my $i =0;$i[$i]->[3] =~ s/(\'$|^\')//g; } return $dataset; } } sub RDs_awarddetail { return 3; } sub RDs_submission { return 2; } sub RDs_timestamp { return 1; } sub RDs_tries { return 0; } sub RDs_awarded { return 4; } sub get_response_data_by_student { my ($student,$symb,$response,$courseid) = @_; return undef if (! defined($symb) || ! defined($response)); $courseid = $ENV{'request.course.id'} if (! defined($courseid)); # &setup_table_names($courseid); my $symb_id = &get_symb_id($symb); my $response_id = &get_part_id($response); # my $student_id = &get_student_id($student->{'username'}, $student->{'domain'}); # my $dbh = &Apache::lonmysql::get_dbh(); return undef if (! defined($dbh)); my $request = 'SELECT '. 'c.tries, b.timestamp, a.submission, a.awarddetail, e.awarded '. 'FROM '.$fulldump_response_table.' AS a '. 'LEFT JOIN '.$fulldump_timestamp_table.' AS b '. 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '. 'a.transaction = b.transaction '. 'LEFT JOIN '.$fulldump_part_table.' AS c '. 'ON a.symb_id=c.symb_id AND a.student_id=c.student_id AND '. 'a.part_id=c.part_id AND a.transaction = c.transaction '. 'LEFT JOIN '.$student_table.' AS d '. 'ON a.student_id=d.student_id '. 'LEFT JOIN '.$performance_table.' AS e '. 'ON a.symb_id=e.symb_id AND a.part_id=e.part_id AND '. 'a.student_id=e.student_id AND c.tries=e.tries '. 'WHERE '. 'a.symb_id='.$symb_id.' AND a.response_id='.$response_id. ' AND a.student_id='.$student_id.' ORDER BY b.timestamp'; # &Apache::lonnet::logthis("request =\n".$request); my $sth = $dbh->prepare($request); $sth->execute(); if ($dbh->err) { &Apache::lonnet::logthis('error = '.$dbh->errstr()); return undef; } my $dataset = $sth->fetchall_arrayref(); if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) { # Clear the \'s from around the submission for (my $i =0;$i[$i]->[2] =~ s/(\'$|^\')//g; } return $dataset; } return undef; # error occurred } sub RT_student_id { return 0; } sub RT_awarded { return 1; } sub RT_tries { return 2; } sub RT_timestamp { return 3; } sub get_response_time_data { my ($students,$symb,$part,$courseid) = @_; return undef if (! defined($symb) || ! defined($part)); $courseid = $ENV{'request.course.id'} if (! defined($courseid)); # &setup_table_names($courseid); my $symb_id = &get_symb_id($symb); my $part_id = &get_part_id($part); # my $dbh = &Apache::lonmysql::get_dbh(); return undef if (! defined($dbh)); my $request = 'SELECT '. 'a.student_id, a.awarded, a.tries, b.timestamp '. 'FROM '.$fulldump_part_table.' AS a '. 'NATURAL LEFT JOIN '.$fulldump_timestamp_table.' AS b '. # 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '. # 'a.transaction = b.transaction '. 'WHERE '. 'a.symb_id='.$symb_id.' AND a.part_id='.$part_id; if (defined($students)) { $request .= ' AND ('. join(' OR ', map {'a.student_id='. &get_student_id($_->{'username'}, $_->{'domain'}) } @$students ).')'; } $request .= ' ORDER BY b.timestamp'; # &Apache::lonnet::logthis("request =\n".$request); my $sth = $dbh->prepare($request); $sth->execute(); if ($dbh->err) { &Apache::lonnet::logthis('error = '.$dbh->errstr()); return undef; } my $dataset = $sth->fetchall_arrayref(); if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) { return $dataset; } } ################################################ ################################################ =pod =item &get_student_scores($Sections,$Symbs,$enrollment,$courseid) =cut ################################################ ################################################ sub get_student_scores { my ($Sections,$Symbs,$enrollment,$courseid,$starttime,$endtime) = @_; $courseid = $ENV{'request.course.id'} if (! defined($courseid)); &setup_table_names($courseid); my $dbh = &Apache::lonmysql::get_dbh(); return (undef) if (! defined($dbh)); my $tmptable = $courseid.'_temp_'.time; # my $symb_requirements; if (defined($Symbs) && @$Symbs) { $symb_requirements = '('. join(' OR ', map{ "(a.symb_id='".&get_symb_id($_->{'symb'}). "' AND a.part_id='".&get_part_id($_->{'part'}). "')" } @$Symbs).')'; } # my $student_requirements; if ( (defined($Sections) && $Sections->[0] ne 'all')) { $student_requirements = '('. join(' OR ', map { "b.section='".$_."'" } @$Sections ).')'; } # my $enrollment_requirements=undef; if (defined($enrollment) && $enrollment ne 'Any') { $enrollment_requirements = "b.status='".$enrollment."'"; } # my $time_requirements = undef; if (defined($starttime)) { $time_requirements .= "a.timestamp>='".$starttime."'"; if (defined($endtime)) { $time_requirements .= " AND a.timestamp<='".$endtime."'"; } } elsif (defined($endtime)) { $time_requirements .= "a.timestamp<='".$endtime."'"; } ## ## my $request = 'CREATE TEMPORARY TABLE IF NOT EXISTS '.$tmptable. ' SELECT a.student_id,SUM(a.awarded) AS score FROM '. $performance_table.' AS a '; if (defined($student_requirements) || defined($enrollment_requirements)) { $request .= ' NATURAL LEFT JOIN '.$student_table.' AS b '; } if (defined($symb_requirements) || defined($student_requirements) || defined($enrollment_requirements) ) { $request .= ' WHERE '; } if (defined($symb_requirements)) { $request .= $symb_requirements.' AND '; } if (defined($student_requirements)) { $request .= $student_requirements.' AND '; } if (defined($enrollment_requirements)) { $request .= $enrollment_requirements.' AND '; } if (defined($time_requirements)) { $request .= $time_requirements.' AND '; } $request =~ s/ AND $//; # Strip of the trailing ' AND '. $request .= ' GROUP BY a.student_id'; # &Apache::lonnet::logthis("request = \n".$request); my $sth = $dbh->prepare($request); $sth->execute(); if ($dbh->err) { &Apache::lonnet::logthis('error = '.$dbh->errstr()); return undef; } $request = 'SELECT score,COUNT(*) FROM '.$tmptable.' GROUP BY score'; # &Apache::lonnet::logthis("request = \n".$request); $sth = $dbh->prepare($request); $sth->execute(); if ($dbh->err) { &Apache::lonnet::logthis('error = '.$dbh->errstr()); return undef; } my $dataset = $sth->fetchall_arrayref(); return $dataset; } ################################################ ################################################ =pod =item &setup_table_names() input: course id output: none Cleans up the package variables for local caching. =cut ################################################ ################################################ sub setup_table_names { my ($courseid) = @_; if (! defined($courseid)) { $courseid = $ENV{'request.course.id'}; } # if (! defined($current_course) || $current_course ne $courseid) { # Clear out variables $have_read_part_table = 0; undef(%ids_by_part); undef(%parts_by_id); $have_read_symb_table = 0; undef(%ids_by_symb); undef(%symbs_by_id); $have_read_student_table = 0; undef(%ids_by_student); undef(%students_by_id); # $current_course = $courseid; } # # Set up database names my $base_id = $courseid; $symb_table = $base_id.'_'.'symb'; $part_table = $base_id.'_'.'part'; $student_table = $base_id.'_'.'student'; $performance_table = $base_id.'_'.'performance'; $parameters_table = $base_id.'_'.'parameters'; $fulldump_part_table = $base_id.'_'.'partdata'; $fulldump_response_table = $base_id.'_'.'responsedata'; $fulldump_timestamp_table = $base_id.'_'.'timestampdata'; # @Tables = ( $symb_table, $part_table, $student_table, $performance_table, $parameters_table, $fulldump_part_table, $fulldump_response_table, $fulldump_timestamp_table, ); return; } ################################################ ################################################ =pod =back =item End of Local Data Caching Subroutines =cut ################################################ ################################################ } # End scope of table identifiers ################################################ ################################################ =pod =head3 Classlist Subroutines =item &get_classlist(); Retrieve the classist of a given class or of the current class. Student information is returned from the classlist.db file and, if needed, from the students environment. Optional arguments are $cid, $cdom, and $cnum (course id, course domain, and course number, respectively). Any omitted arguments will be taken from the current environment ($ENV{'request.course.id'}, $ENV{'course.'.$cid.'.domain'}, and $ENV{'course.'.$cid.'.num'}). Returns a reference to a hash which contains: keys '$sname:$sdom' values [$sdom,$sname,$end,$start,$id,$section,$fullname,$status,$type] The constant values CL_SDOM, CL_SNAME, CL_END, etc. can be used as indices into the returned list to future-proof clients against changes in the list order. =cut ################################################ ################################################ sub CL_SDOM { return 0; } sub CL_SNAME { return 1; } sub CL_END { return 2; } sub CL_START { return 3; } sub CL_ID { return 4; } sub CL_SECTION { return 5; } sub CL_FULLNAME { return 6; } sub CL_STATUS { return 7; } sub CL_TYPE { return 8; } sub get_classlist { my ($cid,$cdom,$cnum) = @_; $cid = $cid || $ENV{'request.course.id'}; $cdom = $cdom || $ENV{'course.'.$cid.'.domain'}; $cnum = $cnum || $ENV{'course.'.$cid.'.num'}; my $now = time; # my %classlist=&Apache::lonnet::dump('classlist',$cdom,$cnum); while (my ($student,$info) = each(%classlist)) { if ($student =~ /^(con_lost|error|no_such_host)/i) { &Apache::lonnet::logthis('get_classlist error for '.$cid.':'.$student); return undef; } my ($sname,$sdom) = split(/:/,$student); my @Values = split(/:/,$info); my ($end,$start,$id,$section,$fullname,$type); if (@Values > 2) { ($end,$start,$id,$section,$fullname,$type) = @Values; } else { # We have to get the data ourselves ($end,$start) = @Values; $section = &Apache::lonnet::getsection($sdom,$sname,$cid); my %info=&Apache::lonnet::get('environment', ['firstname','middlename', 'lastname','generation','id'], $sdom, $sname); my ($tmp) = keys(%info); if ($tmp =~/^(con_lost|error|no_such_host)/i) { $fullname = 'not available'; $id = 'not available'; &Apache::lonnet::logthis('unable to retrieve environment '. 'for '.$sname.':'.$sdom); } else { $fullname = &ProcessFullName(@info{qw/lastname generation firstname middlename/}); $id = $info{'id'}; } # Update the classlist with this students information if ($fullname ne 'not available') { my $enrolldata = join(':',$end,$start,$id,$section,$fullname); my $reply=&Apache::lonnet::cput('classlist', {$student => $enrolldata}, $cdom,$cnum); if ($reply !~ /^(ok|delayed)/) { &Apache::lonnet::logthis('Unable to update classlist for '. 'student '.$sname.':'.$sdom. ' error:'.$reply); } } } my $status='Expired'; if(((!$end) || $now < $end) && ((!$start) || ($now > $start))) { $status='Active'; } $classlist{$student} = [$sdom,$sname,$end,$start,$id,$section,$fullname,$status,$type]; } if (wantarray()) { return (\%classlist,['domain','username','end','start','id', 'section','fullname','status','type']); } else { return \%classlist; } } # ----- END HELPER FUNCTIONS -------------------------------------------- 1; __END__