File:
[LON-CAPA] /
loncom /
interface /
loncoursedata.pm
Revision
1.121:
download - view:
text,
annotated -
select for diffs
Sun Mar 7 20:53:40 2004 UTC (20 years, 4 months ago) by
matthew
Branches:
MAIN
CVS tags:
HEAD
loncoursedata.pm: &get_student_scores modified to limit by time.
loncorrectproblemplot.pm: Added time limits, corrected "bin"ing bug which
caused the last data set to be missed, plot title is now the sequence
being analyzed or "Multiple Sequences", added "N", the number of students,
to the title. Added the maximum possible number of correct problems to
the x-axis label.
1: # The LearningOnline Network with CAPA
2: #
3: # $Id: loncoursedata.pm,v 1.121 2004/03/07 20:53:40 matthew Exp $
4: #
5: # Copyright Michigan State University Board of Trustees
6: #
7: # This file is part of the LearningOnline Network with CAPA (LON-CAPA).
8: #
9: # LON-CAPA is free software; you can redistribute it and/or modify
10: # it under the terms of the GNU General Public License as published by
11: # the Free Software Foundation; either version 2 of the License, or
12: # (at your option) any later version.
13: #
14: # LON-CAPA is distributed in the hope that it will be useful,
15: # but WITHOUT ANY WARRANTY; without even the implied warranty of
16: # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17: # GNU General Public License for more details.
18: #
19: # You should have received a copy of the GNU General Public License
20: # along with LON-CAPA; if not, write to the Free Software
21: # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
22: #
23: # /home/httpd/html/adm/gpl.txt
24: #
25: # http://www.lon-capa.org/
26: #
27: ###
28:
29: =pod
30:
31: =head1 NAME
32:
33: loncoursedata
34:
35: =head1 SYNOPSIS
36:
37: Set of functions that download and process student and course information.
38:
39: =head1 PACKAGES USED
40:
41: Apache::Constants qw(:common :http)
42: Apache::lonnet()
43: Apache::lonhtmlcommon
44: HTML::TokeParser
45: GDBM_File
46:
47: =cut
48:
49: package Apache::loncoursedata;
50:
51: use strict;
52: use Apache::Constants qw(:common :http);
53: use Apache::lonnet();
54: use Apache::lonhtmlcommon;
55: use Time::HiRes;
56: use Apache::lonmysql;
57: use HTML::TokeParser;
58: use GDBM_File;
59:
60: =pod
61:
62: =head1 DOWNLOAD INFORMATION
63:
64: This section contains all the functions that get data from other servers
65: and/or itself.
66:
67: =cut
68:
69: ####################################################
70: ####################################################
71:
72: =pod
73:
74: =item &get_sequence_assessment_data()
75:
76: Use lonnavmaps to build a data structure describing the order and
77: assessment contents of each sequence in the current course.
78:
79: The returned structure is a hash reference.
80:
81: { title => 'title',
82: symb => 'symb',
83: src => '/s/o/u/r/c/e',
84: type => (container|assessment),
85: num_assess => 2, # only for container
86: parts => [11,13,15], # only for assessment
87: response_ids => [12,14,16], # only for assessment
88: contents => [........] # only for container
89: }
90:
91: $hash->{'contents'} is a reference to an array of hashes of the same structure.
92:
93: Also returned are array references to the sequences and assessments contained
94: in the course.
95:
96:
97: =cut
98:
99: ####################################################
100: ####################################################
101: sub get_sequence_assessment_data {
102: my $fn=$ENV{'request.course.fn'};
103: ##
104: ## use navmaps
105: my $navmap = Apache::lonnavmaps::navmap->new();
106: if (!defined($navmap)) {
107: return 'Can not open Coursemap';
108: }
109: # We explicity grab the top level map because I am not sure we
110: # are pulling it from the iterator.
111: my $top_level_map = $navmap->getById('0.0');
112: #
113: my $iterator = $navmap->getIterator(undef, undef, undef, 1);
114: my $curRes = $iterator->next(); # Top level sequence
115: ##
116: ## Prime the pump
117: ##
118: ## We are going to loop until we run out of sequences/pages to explore for
119: ## resources. This means we have to start out with something to look
120: ## at.
121: my $title = $ENV{'course.'.$ENV{'request.course.id'}.'.description'};
122: my $symb = $top_level_map->symb();
123: my $src = $top_level_map->src();
124: my $randompick = $top_level_map->randompick();
125: #
126: my @Sequences;
127: my @Assessments;
128: my @Nested_Sequences = (); # Stack of sequences, keeps track of depth
129: my $top = { title => $title,
130: src => $src,
131: symb => $symb,
132: type => 'container',
133: num_assess => 0,
134: num_assess_parts => 0,
135: contents => [],
136: randompick => $randompick,
137: };
138: push (@Sequences,$top);
139: push (@Nested_Sequences, $top);
140: #
141: # We need to keep track of which sequences contain homework problems
142: #
143: my $previous_too;
144: my $previous;
145: while (scalar(@Nested_Sequences)) {
146: $previous_too = $previous;
147: $previous = $curRes;
148: $curRes = $iterator->next();
149: my $currentmap = $Nested_Sequences[-1]; # Last one on the stack
150: if ($curRes == $iterator->BEGIN_MAP()) {
151: if (! ref($previous)) {
152: $previous = $previous_too;
153: }
154: if (! ref($previous)) {
155: next;
156: }
157: # get the map itself, instead of BEGIN_MAP
158: $title = $previous->title();
159: $title =~ s/\:/\&\#058;/g;
160: $symb = $previous->symb();
161: $src = $previous->src();
162: # pick up the filename if there is no title available
163: if (! defined($title) || $title eq '') {
164: ($title) = ($src=~/\/([^\/]*)$/);
165: }
166: $randompick = $previous->randompick();
167: my $newmap = { title => $title,
168: src => $src,
169: symb => $symb,
170: type => 'container',
171: num_assess => 0,
172: randompick => $randompick,
173: contents => [],
174: };
175: push (@{$currentmap->{'contents'}},$newmap); # this is permanent
176: push (@Sequences,$newmap);
177: push (@Nested_Sequences, $newmap); # this is a stack
178: next;
179: }
180: if ($curRes == $iterator->END_MAP()) {
181: pop(@Nested_Sequences);
182: next;
183: }
184: next if (! ref($curRes));
185: next if (! $curRes->is_problem() && $curRes->src() !~ /\.survey$/);
186: # Okay, from here on out we only deal with assessments
187: $title = $curRes->title();
188: $title =~ s/\:/\&\#058;/g;
189: $symb = $curRes->symb();
190: $src = $curRes->src();
191: # Grab the filename if there is not title available
192: if (! defined($title) || $title eq '') {
193: ($title) = ($src=~ m:/([^/]*)$:);
194: }
195: my $parts = $curRes->parts();
196: my %partdata;
197: foreach my $part (@$parts) {
198: my @Responses = $curRes->responseType($part);
199: my @Ids = $curRes->responseIds($part);
200: $partdata{$part}->{'ResponseTypes'}= \@Responses;
201: $partdata{$part}->{'ResponseIds'} = \@Ids;
202: # Count how many responses of each type there are in this part
203: foreach (@Responses) {
204: $partdata{$part}->{$_}++;
205: }
206: }
207: my $assessment = { title => $title,
208: src => $src,
209: symb => $symb,
210: type => 'assessment',
211: parts => $parts,
212: num_parts => scalar(@$parts),
213: partdata => \%partdata,
214: };
215: push(@Assessments,$assessment);
216: push(@{$currentmap->{'contents'}},$assessment);
217: $currentmap->{'num_assess'}++;
218: $currentmap->{'num_assess_parts'}+= scalar(@$parts);
219: }
220: $navmap->untieHashes();
221: return ($top,\@Sequences,\@Assessments);
222: }
223:
224: sub LoadDiscussion {
225: my ($courseID)=@_;
226: my %Discuss=();
227: my %contrib=&Apache::lonnet::dump(
228: $courseID,
229: $ENV{'course.'.$courseID.'.domain'},
230: $ENV{'course.'.$courseID.'.num'});
231:
232: #my %contrib=&DownloadCourseInformation($name, $courseID, 0);
233:
234: foreach my $temp(keys %contrib) {
235: if ($temp=~/^version/) {
236: my $ver=$contrib{$temp};
237: my ($dummy,$prb)=split(':',$temp);
238: for (my $idx=1; $idx<=$ver; $idx++ ) {
239: my $name=$contrib{"$idx:$prb:sendername"};
240: $Discuss{"$name:$prb"}=$idx;
241: }
242: }
243: }
244:
245: return \%Discuss;
246: }
247:
248: ################################################
249: ################################################
250:
251: =pod
252:
253: =item &GetUserName(username,userdomain)
254:
255: Returns a hash with the following entries:
256: 'firstname', 'middlename', 'lastname', 'generation', and 'fullname'
257:
258: 'fullname' is the result of &Apache::loncoursedata::ProcessFullName.
259:
260: =cut
261:
262: ################################################
263: ################################################
264: sub GetUserName {
265: my ($username,$userdomain) = @_;
266: $username = $ENV{'user.name'} if (! defined($username));
267: $userdomain = $ENV{'user.domain'} if (! defined($username));
268: my %userenv = &Apache::lonnet::get('environment',
269: ['firstname','middlename','lastname','generation'],
270: $userdomain,$username);
271: $userenv{'fullname'} = &ProcessFullName($userenv{'lastname'},
272: $userenv{'generation'},
273: $userenv{'firstname'},
274: $userenv{'middlename'});
275: return %userenv;
276: }
277:
278: ################################################
279: ################################################
280:
281: =pod
282:
283: =item &ProcessFullName()
284:
285: Takes lastname, generation, firstname, and middlename (or some partial
286: set of this data) and returns the full name version as a string. Format
287: is Lastname generation, firstname middlename or a subset of this.
288:
289: =cut
290:
291: ################################################
292: ################################################
293: sub ProcessFullName {
294: my ($lastname, $generation, $firstname, $middlename)=@_;
295: my $Str = '';
296:
297: # Strip whitespace preceeding & following name components.
298: $lastname =~ s/(\s+$|^\s+)//g;
299: $generation =~ s/(\s+$|^\s+)//g;
300: $firstname =~ s/(\s+$|^\s+)//g;
301: $middlename =~ s/(\s+$|^\s+)//g;
302:
303: if($lastname ne '') {
304: $Str .= $lastname;
305: $Str .= ' '.$generation if ($generation ne '');
306: $Str .= ',';
307: $Str .= ' '.$firstname if ($firstname ne '');
308: $Str .= ' '.$middlename if ($middlename ne '');
309: } else {
310: $Str .= $firstname if ($firstname ne '');
311: $Str .= ' '.$middlename if ($middlename ne '');
312: $Str .= ' '.$generation if ($generation ne '');
313: }
314:
315: return $Str;
316: }
317:
318: ################################################
319: ################################################
320:
321: =pod
322:
323: =item &make_into_hash($values);
324:
325: Returns a reference to a hash as described by $values. $values is
326: assumed to be the result of
327: join(':',map {&Apache::lonnet::escape($_)} %orighash);
328:
329: This is a helper function for get_current_state.
330:
331: =cut
332:
333: ################################################
334: ################################################
335: sub make_into_hash {
336: my $values = shift;
337: my %tmp = map { &Apache::lonnet::unescape($_); }
338: split(':',$values);
339: return \%tmp;
340: }
341:
342:
343: ################################################
344: ################################################
345:
346: =pod
347:
348: =head1 LOCAL DATA CACHING SUBROUTINES
349:
350: The local caching is done using MySQL. There is no fall-back implementation
351: if MySQL is not running.
352:
353: The programmers interface is to call &get_current_state() or some other
354: primary interface subroutine (described below). The internals of this
355: storage system are documented here.
356:
357: There are six tables used to store student performance data (the results of
358: a dumpcurrent). Each of these tables is created in MySQL with a name of
359: $courseid_*****, where ***** is 'symb', 'part', or whatever is appropriate
360: for the table. The tables and their purposes are described below.
361:
362: Some notes before we get started.
363:
364: Each table must have a PRIMARY KEY, which is a column or set of columns which
365: will serve to uniquely identify a row of data. NULL is not allowed!
366:
367: INDEXes work best on integer data.
368:
369: JOIN is used to combine data from many tables into one output.
370:
371: lonmysql.pm is used for some of the interface, specifically the table creation
372: calls. The inserts are done in bulk by directly calling the database handler.
373: The SELECT ... JOIN statement used to retrieve the data does not have an
374: interface in lonmysql.pm and I shudder at the thought of writing one.
375:
376: =head3 Table Descriptions
377:
378: =over 4
379:
380: =item Tables used to store meta information
381:
382: The following tables hold data required to keep track of the current status
383: of a students data in the tables or to look up the students data in the tables.
384:
385: =over 4
386:
387: =item $symb_table
388:
389: The symb_table has two columns. The first is a 'symb_id' and the second
390: is the text name for the 'symb' (limited to 64k). The 'symb_id' is generated
391: automatically by MySQL so inserts should be done on this table with an
392: empty first element. This table has its PRIMARY KEY on the 'symb_id'.
393:
394: =item $part_table
395:
396: The part_table has two columns. The first is a 'part_id' and the second
397: is the text name for the 'part' (limited to 100 characters). The 'part_id' is
398: generated automatically by MySQL so inserts should be done on this table with
399: an empty first element. This table has its PRIMARY KEY on the 'part' (100
400: characters) and a KEY on 'part_id'.
401:
402: =item $student_table
403:
404: The student_table has 7 columns. The first is a 'student_id' assigned by
405: MySQL. The second is 'student' which is username:domain. The third through
406: fifth are 'section', 'status' (enrollment status), and 'classification'
407: (to be used in the future). The sixth and seventh ('updatetime' and
408: 'fullupdatetime') contain the time of last update and full update of student
409: data. This table has its PRIMARY KEY on the 'student_id' column and is indexed
410: on 'student', 'section', and 'status'.
411:
412: =back
413:
414: =item Tables used to store current status data
415:
416: The following tables store data only about the students current status on
417: a problem, meaning only the data related to the last attempt on a problem.
418:
419: =over 4
420:
421: =item $performance_table
422:
423: The performance_table has 9 columns. The first three are 'symb_id',
424: 'student_id', and 'part_id'. These comprise the PRIMARY KEY for this table
425: and are directly related to the $symb_table, $student_table, and $part_table
426: described above. MySQL does better indexing on numeric items than text,
427: so we use these three "index tables". The remaining columns are
428: 'solved', 'tries', 'awarded', 'award', 'awarddetail', and 'timestamp'.
429: These are either the MySQL type TINYTEXT or various integers ('tries' and
430: 'timestamp'). This table has KEYs of 'student_id' and 'symb_id'.
431: For use of this table, see the functions described below.
432:
433: =item $parameters_table
434:
435: The parameters_table holds the data that does not fit neatly into the
436: performance_table. The parameters table has four columns: 'symb_id',
437: 'student_id', 'parameter', and 'value'. 'symb_id', 'student_id', and
438: 'parameter' comprise the PRIMARY KEY for this table. 'parameter' is
439: limited to 255 characters. 'value' is limited to 64k characters.
440:
441: =back
442:
443: =item Tables used for storing historic data
444:
445: The following tables are used to store almost all of the transactions a student
446: has made on a homework problem. See loncapa/docs/homework/datastorage for
447: specific information about each of the parameters stored.
448:
449: =over 4
450:
451: =item $fulldump_response_table
452:
453: The response table holds data (documented in loncapa/docs/homework/datastorage)
454: associated with a particular response id which is stored when a student
455: attempts a problem. The following are the columns of the table, in order:
456: 'symb_id','part_id','response_id','student_id','transaction','tries',
457: 'awarddetail', 'response_specific' (data particular to the response
458: type), 'response_specific_value', and 'submission (the text of the students
459: submission). The primary key is based on the first five columns listed above.
460:
461: =item $fulldump_part_table
462:
463: The part table holds data (documented in loncapa/docs/homework/datastorage)
464: associated with a particular part id which is stored when a student attempts
465: a problem. The following are the columns of the table, in order:
466: 'symb_id','part_id','student_id','transaction','tries','award','awarded',
467: and 'previous'. The primary key is based on the first five columns listed
468: above.
469:
470: =item $fulldump_timestamp_table
471:
472: The timestamp table holds the timestamps of the transactions which are
473: stored in $fulldump_response_table and $fulldump_part_table. This data is
474: about both the response and part data. Columns: 'symb_id','student_id',
475: 'transaction', and 'timestamp'.
476: The primary key is based on the first 3 columns.
477:
478: =back
479:
480: =back
481:
482: =head3 Important Subroutines
483:
484: Here is a brief overview of the subroutines which are likely to be of
485: interest:
486:
487: =over 4
488:
489: =item &get_current_state(): programmers interface.
490:
491: =item &init_dbs(): table creation
492:
493: =item &update_student_data(): data storage calls
494:
495: =item &get_student_data_from_performance_cache(): data retrieval
496:
497: =back
498:
499: =head3 Main Documentation
500:
501: =over 4
502:
503: =cut
504:
505: ################################################
506: ################################################
507:
508: ################################################
509: ################################################
510: { # Begin scope of table identifiers
511:
512: my $current_course ='';
513: my $symb_table;
514: my $part_table;
515: my $student_table;
516: my $performance_table;
517: my $parameters_table;
518: my $fulldump_response_table;
519: my $fulldump_part_table;
520: my $fulldump_timestamp_table;
521:
522: my @Tables;
523: ################################################
524: ################################################
525:
526: =pod
527:
528: =item &init_dbs()
529:
530: Input: course id
531:
532: Output: 0 on success, positive integer on error
533:
534: This routine issues the calls to lonmysql to create the tables used to
535: store student data.
536:
537: =cut
538:
539: ################################################
540: ################################################
541: sub init_dbs {
542: my $courseid = shift;
543: &setup_table_names($courseid);
544: #
545: # Drop any of the existing tables
546: foreach my $table (@Tables) {
547: &Apache::lonmysql::drop_table($table);
548: }
549: #
550: # Note - changes to this table must be reflected in the code that
551: # stores the data (calls &Apache::lonmysql::store_row with this table
552: # id
553: my $symb_table_def = {
554: id => $symb_table,
555: permanent => 'no',
556: columns => [{ name => 'symb_id',
557: type => 'MEDIUMINT UNSIGNED',
558: restrictions => 'NOT NULL',
559: auto_inc => 'yes', },
560: { name => 'symb',
561: type => 'MEDIUMTEXT',
562: restrictions => 'NOT NULL'},
563: ],
564: 'PRIMARY KEY' => ['symb_id'],
565: };
566: #
567: my $part_table_def = {
568: id => $part_table,
569: permanent => 'no',
570: columns => [{ name => 'part_id',
571: type => 'MEDIUMINT UNSIGNED',
572: restrictions => 'NOT NULL',
573: auto_inc => 'yes', },
574: { name => 'part',
575: type => 'VARCHAR(100)',
576: restrictions => 'NOT NULL'},
577: ],
578: 'PRIMARY KEY' => ['part (100)'],
579: 'KEY' => [{ columns => ['part_id']},],
580: };
581: #
582: my $student_table_def = {
583: id => $student_table,
584: permanent => 'no',
585: columns => [{ name => 'student_id',
586: type => 'MEDIUMINT UNSIGNED',
587: restrictions => 'NOT NULL',
588: auto_inc => 'yes', },
589: { name => 'student',
590: type => 'VARCHAR(100)',
591: restrictions => 'NOT NULL UNIQUE'},
592: { name => 'section',
593: type => 'VARCHAR(100)',
594: restrictions => 'NOT NULL'},
595: { name => 'status',
596: type => 'VARCHAR(15)',
597: restrictions => 'NOT NULL'},
598: { name => 'classification',
599: type => 'varchar(100)', },
600: { name => 'updatetime',
601: type => 'INT UNSIGNED'},
602: { name => 'fullupdatetime',
603: type => 'INT UNSIGNED'},
604: ],
605: 'PRIMARY KEY' => ['student_id'],
606: 'KEY' => [{ columns => ['student (100)',
607: 'section (100)',
608: 'status (15)',]},],
609: };
610: #
611: my $performance_table_def = {
612: id => $performance_table,
613: permanent => 'no',
614: columns => [{ name => 'symb_id',
615: type => 'MEDIUMINT UNSIGNED',
616: restrictions => 'NOT NULL' },
617: { name => 'student_id',
618: type => 'MEDIUMINT UNSIGNED',
619: restrictions => 'NOT NULL' },
620: { name => 'part_id',
621: type => 'MEDIUMINT UNSIGNED',
622: restrictions => 'NOT NULL' },
623: { name => 'part',
624: type => 'VARCHAR(100)',
625: restrictions => 'NOT NULL'},
626: { name => 'solved',
627: type => 'TINYTEXT' },
628: { name => 'tries',
629: type => 'SMALLINT UNSIGNED' },
630: { name => 'awarded',
631: type => 'TINYTEXT' },
632: { name => 'award',
633: type => 'TINYTEXT' },
634: { name => 'awarddetail',
635: type => 'TINYTEXT' },
636: { name => 'timestamp',
637: type => 'INT UNSIGNED'},
638: ],
639: 'PRIMARY KEY' => ['symb_id','student_id','part_id'],
640: 'KEY' => [{ columns=>['student_id'] },
641: { columns=>['symb_id'] },],
642: };
643: #
644: my $fulldump_part_table_def = {
645: id => $fulldump_part_table,
646: permanent => 'no',
647: columns => [
648: { name => 'symb_id',
649: type => 'MEDIUMINT UNSIGNED',
650: restrictions => 'NOT NULL' },
651: { name => 'part_id',
652: type => 'MEDIUMINT UNSIGNED',
653: restrictions => 'NOT NULL' },
654: { name => 'student_id',
655: type => 'MEDIUMINT UNSIGNED',
656: restrictions => 'NOT NULL' },
657: { name => 'transaction',
658: type => 'MEDIUMINT UNSIGNED',
659: restrictions => 'NOT NULL' },
660: { name => 'tries',
661: type => 'SMALLINT UNSIGNED',
662: restrictions => 'NOT NULL' },
663: { name => 'award',
664: type => 'TINYTEXT' },
665: { name => 'awarded',
666: type => 'TINYTEXT' },
667: { name => 'previous',
668: type => 'SMALLINT UNSIGNED' },
669: # { name => 'regrader',
670: # type => 'TINYTEXT' },
671: # { name => 'afterduedate',
672: # type => 'TINYTEXT' },
673: ],
674: 'PRIMARY KEY' => ['symb_id','part_id','student_id','transaction'],
675: 'KEY' => [
676: { columns=>['symb_id'] },
677: { columns=>['part_id'] },
678: { columns=>['student_id'] },
679: ],
680: };
681: #
682: my $fulldump_response_table_def = {
683: id => $fulldump_response_table,
684: permanent => 'no',
685: columns => [
686: { name => 'symb_id',
687: type => 'MEDIUMINT UNSIGNED',
688: restrictions => 'NOT NULL' },
689: { name => 'part_id',
690: type => 'MEDIUMINT UNSIGNED',
691: restrictions => 'NOT NULL' },
692: { name => 'response_id',
693: type => 'MEDIUMINT UNSIGNED',
694: restrictions => 'NOT NULL' },
695: { name => 'student_id',
696: type => 'MEDIUMINT UNSIGNED',
697: restrictions => 'NOT NULL' },
698: { name => 'transaction',
699: type => 'MEDIUMINT UNSIGNED',
700: restrictions => 'NOT NULL' },
701: { name => 'awarddetail',
702: type => 'TINYTEXT' },
703: # { name => 'message',
704: # type => 'CHAR' },
705: { name => 'response_specific',
706: type => 'TINYTEXT' },
707: { name => 'response_specific_value',
708: type => 'TINYTEXT' },
709: { name => 'submission',
710: type => 'TEXT'},
711: ],
712: 'PRIMARY KEY' => ['symb_id','part_id','response_id','student_id',
713: 'transaction'],
714: 'KEY' => [
715: { columns=>['symb_id'] },
716: { columns=>['part_id','response_id'] },
717: { columns=>['student_id'] },
718: ],
719: };
720: my $fulldump_timestamp_table_def = {
721: id => $fulldump_timestamp_table,
722: permanent => 'no',
723: columns => [
724: { name => 'symb_id',
725: type => 'MEDIUMINT UNSIGNED',
726: restrictions => 'NOT NULL' },
727: { name => 'student_id',
728: type => 'MEDIUMINT UNSIGNED',
729: restrictions => 'NOT NULL' },
730: { name => 'transaction',
731: type => 'MEDIUMINT UNSIGNED',
732: restrictions => 'NOT NULL' },
733: { name => 'timestamp',
734: type => 'INT UNSIGNED'},
735: ],
736: 'PRIMARY KEY' => ['symb_id','student_id','transaction'],
737: 'KEY' => [
738: { columns=>['symb_id'] },
739: { columns=>['student_id'] },
740: { columns=>['transaction'] },
741: ],
742: };
743:
744: #
745: my $parameters_table_def = {
746: id => $parameters_table,
747: permanent => 'no',
748: columns => [{ name => 'symb_id',
749: type => 'MEDIUMINT UNSIGNED',
750: restrictions => 'NOT NULL' },
751: { name => 'student_id',
752: type => 'MEDIUMINT UNSIGNED',
753: restrictions => 'NOT NULL' },
754: { name => 'parameter',
755: type => 'TINYTEXT',
756: restrictions => 'NOT NULL' },
757: { name => 'value',
758: type => 'MEDIUMTEXT' },
759: ],
760: 'PRIMARY KEY' => ['symb_id','student_id','parameter (255)'],
761: };
762: #
763: # Create the tables
764: my $tableid;
765: $tableid = &Apache::lonmysql::create_table($symb_table_def);
766: if (! defined($tableid)) {
767: &Apache::lonnet::logthis("error creating symb_table: ".
768: &Apache::lonmysql::get_error());
769: return 1;
770: }
771: #
772: $tableid = &Apache::lonmysql::create_table($part_table_def);
773: if (! defined($tableid)) {
774: &Apache::lonnet::logthis("error creating part_table: ".
775: &Apache::lonmysql::get_error());
776: return 2;
777: }
778: #
779: $tableid = &Apache::lonmysql::create_table($student_table_def);
780: if (! defined($tableid)) {
781: &Apache::lonnet::logthis("error creating student_table: ".
782: &Apache::lonmysql::get_error());
783: return 3;
784: }
785: #
786: $tableid = &Apache::lonmysql::create_table($performance_table_def);
787: if (! defined($tableid)) {
788: &Apache::lonnet::logthis("error creating preformance_table: ".
789: &Apache::lonmysql::get_error());
790: return 5;
791: }
792: #
793: $tableid = &Apache::lonmysql::create_table($parameters_table_def);
794: if (! defined($tableid)) {
795: &Apache::lonnet::logthis("error creating parameters_table: ".
796: &Apache::lonmysql::get_error());
797: return 6;
798: }
799: #
800: $tableid = &Apache::lonmysql::create_table($fulldump_part_table_def);
801: if (! defined($tableid)) {
802: &Apache::lonnet::logthis("error creating fulldump_part_table: ".
803: &Apache::lonmysql::get_error());
804: return 7;
805: }
806: #
807: $tableid = &Apache::lonmysql::create_table($fulldump_response_table_def);
808: if (! defined($tableid)) {
809: &Apache::lonnet::logthis("error creating fulldump_response_table: ".
810: &Apache::lonmysql::get_error());
811: return 8;
812: }
813: $tableid = &Apache::lonmysql::create_table($fulldump_timestamp_table_def);
814: if (! defined($tableid)) {
815: &Apache::lonnet::logthis("error creating fulldump_timestamp_table: ".
816: &Apache::lonmysql::get_error());
817: return 9;
818: }
819: return 0;
820: }
821:
822: ################################################
823: ################################################
824:
825: =pod
826:
827: =item &delete_caches()
828:
829: =cut
830:
831: ################################################
832: ################################################
833: sub delete_caches {
834: my $courseid = shift;
835: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
836: #
837: &setup_table_names($courseid);
838: #
839: my $dbh = &Apache::lonmysql::get_dbh();
840: foreach my $table (@Tables) {
841: my $command = 'DROP TABLE '.$table.';';
842: $dbh->do($command);
843: if ($dbh->err) {
844: &Apache::lonnet::logthis($command.' resulted in error: '.$dbh->errstr);
845: }
846: }
847: return;
848: }
849:
850: ################################################
851: ################################################
852:
853: =pod
854:
855: =item &get_part_id()
856:
857: Get the MySQL id of a problem part string.
858:
859: Input: $part
860:
861: Output: undef on error, integer $part_id on success.
862:
863: =item &get_part()
864:
865: Get the string describing a part from the MySQL id of the problem part.
866:
867: Input: $part_id
868:
869: Output: undef on error, $part string on success.
870:
871: =cut
872:
873: ################################################
874: ################################################
875:
876: my $have_read_part_table = 0;
877: my %ids_by_part;
878: my %parts_by_id;
879:
880: sub get_part_id {
881: my ($part) = @_;
882: $part = 0 if (! defined($part));
883: if (! $have_read_part_table) {
884: my @Result = &Apache::lonmysql::get_rows($part_table);
885: foreach (@Result) {
886: $ids_by_part{$_->[1]}=$_->[0];
887: }
888: $have_read_part_table = 1;
889: }
890: if (! exists($ids_by_part{$part})) {
891: &Apache::lonmysql::store_row($part_table,[undef,$part]);
892: undef(%ids_by_part);
893: my @Result = &Apache::lonmysql::get_rows($part_table);
894: foreach (@Result) {
895: $ids_by_part{$_->[1]}=$_->[0];
896: }
897: }
898: return $ids_by_part{$part} if (exists($ids_by_part{$part}));
899: return undef; # error
900: }
901:
902: sub get_part {
903: my ($part_id) = @_;
904: if (! exists($parts_by_id{$part_id}) ||
905: ! defined($parts_by_id{$part_id}) ||
906: $parts_by_id{$part_id} eq '') {
907: my @Result = &Apache::lonmysql::get_rows($part_table);
908: foreach (@Result) {
909: $parts_by_id{$_->[0]}=$_->[1];
910: }
911: }
912: return $parts_by_id{$part_id} if(exists($parts_by_id{$part_id}));
913: return undef; # error
914: }
915:
916: ################################################
917: ################################################
918:
919: =pod
920:
921: =item &get_symb_id()
922:
923: Get the MySQL id of a symb.
924:
925: Input: $symb
926:
927: Output: undef on error, integer $symb_id on success.
928:
929: =item &get_symb()
930:
931: Get the symb associated with a MySQL symb_id.
932:
933: Input: $symb_id
934:
935: Output: undef on error, $symb on success.
936:
937: =cut
938:
939: ################################################
940: ################################################
941:
942: my $have_read_symb_table = 0;
943: my %ids_by_symb;
944: my %symbs_by_id;
945:
946: sub get_symb_id {
947: my ($symb) = @_;
948: if (! $have_read_symb_table) {
949: my @Result = &Apache::lonmysql::get_rows($symb_table);
950: foreach (@Result) {
951: $ids_by_symb{$_->[1]}=$_->[0];
952: }
953: $have_read_symb_table = 1;
954: }
955: if (! exists($ids_by_symb{$symb})) {
956: &Apache::lonmysql::store_row($symb_table,[undef,$symb]);
957: undef(%ids_by_symb);
958: my @Result = &Apache::lonmysql::get_rows($symb_table);
959: foreach (@Result) {
960: $ids_by_symb{$_->[1]}=$_->[0];
961: }
962: }
963: return $ids_by_symb{$symb} if(exists( $ids_by_symb{$symb}));
964: return undef; # error
965: }
966:
967: sub get_symb {
968: my ($symb_id) = @_;
969: if (! exists($symbs_by_id{$symb_id}) ||
970: ! defined($symbs_by_id{$symb_id}) ||
971: $symbs_by_id{$symb_id} eq '') {
972: my @Result = &Apache::lonmysql::get_rows($symb_table);
973: foreach (@Result) {
974: $symbs_by_id{$_->[0]}=$_->[1];
975: }
976: }
977: return $symbs_by_id{$symb_id} if(exists( $symbs_by_id{$symb_id}));
978: return undef; # error
979: }
980:
981: ################################################
982: ################################################
983:
984: =pod
985:
986: =item &get_student_id()
987:
988: Get the MySQL id of a student.
989:
990: Input: $sname, $dom
991:
992: Output: undef on error, integer $student_id on success.
993:
994: =item &get_student()
995:
996: Get student username:domain associated with the MySQL student_id.
997:
998: Input: $student_id
999:
1000: Output: undef on error, string $student (username:domain) on success.
1001:
1002: =cut
1003:
1004: ################################################
1005: ################################################
1006:
1007: my $have_read_student_table = 0;
1008: my %ids_by_student;
1009: my %students_by_id;
1010:
1011: sub get_student_id {
1012: my ($sname,$sdom) = @_;
1013: my $student = $sname.':'.$sdom;
1014: if (! $have_read_student_table) {
1015: my @Result = &Apache::lonmysql::get_rows($student_table);
1016: foreach (@Result) {
1017: $ids_by_student{$_->[1]}=$_->[0];
1018: }
1019: $have_read_student_table = 1;
1020: }
1021: if (! exists($ids_by_student{$student})) {
1022: &populate_student_table();
1023: undef(%ids_by_student);
1024: undef(%students_by_id);
1025: my @Result = &Apache::lonmysql::get_rows($student_table);
1026: foreach (@Result) {
1027: $ids_by_student{$_->[1]}=$_->[0];
1028: }
1029: }
1030: return $ids_by_student{$student} if(exists( $ids_by_student{$student}));
1031: return undef; # error
1032: }
1033:
1034: sub get_student {
1035: my ($student_id) = @_;
1036: if (! exists($students_by_id{$student_id}) ||
1037: ! defined($students_by_id{$student_id}) ||
1038: $students_by_id{$student_id} eq '') {
1039: my @Result = &Apache::lonmysql::get_rows($student_table);
1040: foreach (@Result) {
1041: $students_by_id{$_->[0]}=$_->[1];
1042: }
1043: }
1044: return $students_by_id{$student_id} if(exists($students_by_id{$student_id}));
1045: return undef; # error
1046: }
1047:
1048: sub populate_student_table {
1049: my ($courseid) = @_;
1050: if (! defined($courseid)) {
1051: $courseid = $ENV{'request.course.id'};
1052: }
1053: #
1054: &setup_table_names($courseid);
1055: my $dbh = &Apache::lonmysql::get_dbh();
1056: my $request = 'INSERT IGNORE INTO '.$student_table.
1057: "(student,section,status) VALUES ";
1058: my $classlist = &get_classlist($courseid);
1059: my $student_count=0;
1060: while (my ($student,$data) = each %$classlist) {
1061: my ($section,$status) = ($data->[&CL_SECTION()],
1062: $data->[&CL_STATUS()]);
1063: if ($section eq '' || $section =~ /^\s*$/) {
1064: $section = 'none';
1065: }
1066: $request .= "('".$student."','".$section."','".$status."'),";
1067: $student_count++;
1068: }
1069: return if ($student_count == 0);
1070: chop($request);
1071: $dbh->do($request);
1072: if ($dbh->err()) {
1073: &Apache::lonnet::logthis("error ".$dbh->errstr().
1074: " occured executing \n".
1075: $request);
1076: }
1077: return;
1078: }
1079:
1080:
1081: ################################################
1082: ################################################
1083:
1084: =pod
1085:
1086: =item &clear_internal_caches()
1087:
1088: Causes the internal caches used in get_student_id, get_student,
1089: get_symb_id, get_symb, get_part_id, and get_part to be undef'd.
1090:
1091: Needs to be called before the first operation with the MySQL database
1092: for a given Apache request.
1093:
1094: =cut
1095:
1096: ################################################
1097: ################################################
1098: sub clear_internal_caches {
1099: $have_read_part_table = 0;
1100: undef(%ids_by_part);
1101: undef(%parts_by_id);
1102: $have_read_symb_table = 0;
1103: undef(%ids_by_symb);
1104: undef(%symbs_by_id);
1105: $have_read_student_table = 0;
1106: undef(%ids_by_student);
1107: undef(%students_by_id);
1108: }
1109:
1110:
1111: ################################################
1112: ################################################
1113:
1114: =pod
1115:
1116: =item &update_full_student_data($sname,$sdom,$courseid)
1117:
1118: Does a lonnet::dump on a student to populate the courses tables.
1119:
1120: Input: $sname, $sdom, $courseid
1121:
1122: Output: $returnstatus
1123:
1124: $returnstatus is a string describing any errors that occured. 'okay' is the
1125: default.
1126:
1127: This subroutine loads a students data using lonnet::dump and inserts
1128: it into the MySQL database. The inserts are done on three tables,
1129: $fulldump_response_table, $fulldump_part_table, and $fulldump_timestamp_table.
1130: The INSERT calls are made directly by this subroutine, not through lonmysql
1131: because we do a 'bulk'insert which takes advantage of MySQLs non-SQL
1132: compliant INSERT command to insert multiple rows at a time.
1133: If anything has gone wrong during this process, $returnstatus is updated with
1134: a description of the error.
1135:
1136: Once the "fulldump" tables are updated, the tables used for chart and
1137: spreadsheet (which hold only the current state of the student on their
1138: homework, not historical data) are updated. If all updates have occured
1139: successfully, $student_table is updated to reflect the time of the update.
1140:
1141: Notice we do not insert the data and immediately query it. This means it
1142: is possible for there to be data returned this first time that is not
1143: available the second time. CYA.
1144:
1145: =cut
1146:
1147: ################################################
1148: ################################################
1149: sub update_full_student_data {
1150: my ($sname,$sdom,$courseid) = @_;
1151: #
1152: # Set up database names
1153: &setup_table_names($courseid);
1154: #
1155: my $student_id = &get_student_id($sname,$sdom);
1156: my $student = $sname.':'.$sdom;
1157: #
1158: my $returnstatus = 'okay';
1159: #
1160: # Download students data
1161: my $time_of_retrieval = time;
1162: my @tmp = &Apache::lonnet::dump($courseid,$sdom,$sname);
1163: if (@tmp && $tmp[0] =~ /^error/) {
1164: $returnstatus = 'error retrieving full student data';
1165: return $returnstatus;
1166: } elsif (! @tmp) {
1167: $returnstatus = 'okay: no student data';
1168: return $returnstatus;
1169: }
1170: my %studentdata = @tmp;
1171: #
1172: # Get database handle and clean out the tables
1173: my $dbh = &Apache::lonmysql::get_dbh();
1174: $dbh->do('DELETE FROM '.$fulldump_response_table.' WHERE student_id='.
1175: $student_id);
1176: $dbh->do('DELETE FROM '.$fulldump_part_table.' WHERE student_id='.
1177: $student_id);
1178: $dbh->do('DELETE FROM '.$fulldump_timestamp_table.' WHERE student_id='.
1179: $student_id);
1180: #
1181: # Parse and store the data into a form we can handle
1182: my $partdata;
1183: my $respdata;
1184: while (my ($key,$value) = each(%studentdata)) {
1185: next if ($key =~ /^(\d+):(resource$|subnum$|keys:)/);
1186: my ($transaction,$symb,$parameter) = split(':',$key);
1187: my $symb_id = &get_symb_id($symb);
1188: if ($parameter eq 'timestamp') {
1189: # We can deal with 'timestamp' right away
1190: my @timestamp_storage = ($symb_id,$student_id,
1191: $transaction,$value);
1192: my $store_command = 'INSERT IGNORE INTO '.$fulldump_timestamp_table.
1193: " VALUES ('".join("','",@timestamp_storage)."');";
1194: $dbh->do($store_command);
1195: if ($dbh->err()) {
1196: &Apache::lonnet::logthis('unable to execute '.$store_command);
1197: &Apache::lonnet::logthis($dbh->errstr());
1198: }
1199: next;
1200: } elsif ($parameter eq 'version') {
1201: next;
1202: } elsif ($parameter =~ /^resource\.(.*)\.(tries|
1203: award|
1204: awarded|
1205: previous|
1206: solved|
1207: awarddetail|
1208: submission|
1209: submissiongrading|
1210: molecule)\s*$/x){
1211: # we do not have enough information to store an
1212: # entire row, so we save it up until later.
1213: my ($part_and_resp_id,$field) = ($1,$2);
1214: my ($part,$part_id,$resp,$resp_id);
1215: if ($part_and_resp_id =~ /\./) {
1216: ($part,$resp) = split(/\./,$part_and_resp_id);
1217: $part_id = &get_part_id($part);
1218: $resp_id = &get_part_id($resp);
1219: } else {
1220: $part_id = &get_part_id($part_and_resp_id);
1221: }
1222: # Deal with part specific data
1223: if ($field =~ /^(tries|award|awarded|previous)$/) {
1224: $partdata->{$symb_id}->{$part_id}->{$transaction}->{$field}=$value;
1225: }
1226: # deal with response specific data
1227: if (defined($resp_id) &&
1228: $field =~ /^(awarddetail|
1229: submission|
1230: submissiongrading|
1231: molecule)$/x) {
1232: if ($field eq 'submission') {
1233: # We have to be careful with user supplied input.
1234: # most of the time we are okay because it is escaped.
1235: # However, there is one wrinkle: submissions which end in
1236: # and odd number of '\' cause insert errors to occur.
1237: # Best trap this somehow...
1238: $value = $dbh->quote($value);
1239: }
1240: if ($field eq 'submissiongrading' ||
1241: $field eq 'molecule') {
1242: $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{'response_specific'}=$field;
1243: $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{'response_specific_value'}=$value;
1244: } else {
1245: $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{$field}=$value;
1246: }
1247: }
1248: }
1249: }
1250: ##
1251: ## Store the part data
1252: my $store_command = 'INSERT IGNORE INTO '.$fulldump_part_table.
1253: ' VALUES '."\n";
1254: my $store_rows = 0;
1255: while (my ($symb_id,$hash1) = each (%$partdata)) {
1256: while (my ($part_id,$hash2) = each (%$hash1)) {
1257: while (my ($transaction,$data) = each (%$hash2)) {
1258: $store_command .= "('".join("','",$symb_id,$part_id,
1259: $student_id,
1260: $transaction,
1261: $data->{'tries'},
1262: $data->{'award'},
1263: $data->{'awarded'},
1264: $data->{'previous'})."'),";
1265: $store_rows++;
1266: }
1267: }
1268: }
1269: if ($store_rows) {
1270: chop($store_command);
1271: $dbh->do($store_command);
1272: if ($dbh->err) {
1273: $returnstatus = 'error storing part data';
1274: &Apache::lonnet::logthis('insert error '.$dbh->errstr());
1275: &Apache::lonnet::logthis("While attempting\n".$store_command);
1276: }
1277: }
1278: ##
1279: ## Store the response data
1280: $store_command = 'INSERT IGNORE INTO '.$fulldump_response_table.
1281: ' VALUES '."\n";
1282: $store_rows = 0;
1283: while (my ($symb_id,$hash1) = each (%$respdata)) {
1284: while (my ($part_id,$hash2) = each (%$hash1)) {
1285: while (my ($resp_id,$hash3) = each (%$hash2)) {
1286: while (my ($transaction,$data) = each (%$hash3)) {
1287: my $submission = $data->{'submission'};
1288: # We have to be careful with user supplied input.
1289: # most of the time we are okay because it is escaped.
1290: # However, there is one wrinkle: submissions which end in
1291: # and odd number of '\' cause insert errors to occur.
1292: # Best trap this somehow...
1293: $submission = $dbh->quote($submission);
1294: $store_command .= "('".
1295: join("','",$symb_id,$part_id,
1296: $resp_id,$student_id,
1297: $transaction,
1298: $data->{'awarddetail'},
1299: $data->{'response_specific'},
1300: $data->{'response_specific_value'}).
1301: "',".$submission."),";
1302: $store_rows++;
1303: }
1304: }
1305: }
1306: }
1307: if ($store_rows) {
1308: chop($store_command);
1309: $dbh->do($store_command);
1310: if ($dbh->err) {
1311: $returnstatus = 'error storing response data';
1312: &Apache::lonnet::logthis('insert error '.$dbh->errstr());
1313: &Apache::lonnet::logthis("While attempting\n".$store_command);
1314: }
1315: }
1316: ##
1317: ## Update the students "current" data in the performance
1318: ## and parameters tables.
1319: my ($status,undef) = &store_student_data
1320: ($sname,$sdom,$courseid,
1321: &Apache::lonnet::convert_dump_to_currentdump(\%studentdata));
1322: if ($returnstatus eq 'okay' && $status ne 'okay') {
1323: $returnstatus = 'error storing current data:'.$status;
1324: } elsif ($status ne 'okay') {
1325: $returnstatus .= ' error storing current data:'.$status;
1326: }
1327: ##
1328: ## Update the students time......
1329: if ($returnstatus eq 'okay') {
1330: &store_updatetime($student_id,$time_of_retrieval,$time_of_retrieval);
1331: if ($dbh->err) {
1332: if ($returnstatus eq 'okay') {
1333: $returnstatus = 'error updating student time';
1334: } else {
1335: $returnstatus = 'error updating student time';
1336: }
1337: }
1338: }
1339: return $returnstatus;
1340: }
1341:
1342: ################################################
1343: ################################################
1344:
1345: =pod
1346:
1347: =item &update_student_data()
1348:
1349: Input: $sname, $sdom, $courseid
1350:
1351: Output: $returnstatus, \%student_data
1352:
1353: $returnstatus is a string describing any errors that occured. 'okay' is the
1354: default.
1355: \%student_data is the data returned by a call to lonnet::currentdump.
1356:
1357: This subroutine loads a students data using lonnet::currentdump and inserts
1358: it into the MySQL database. The inserts are done on two tables,
1359: $performance_table and $parameters_table. $parameters_table holds the data
1360: that is not included in $performance_table. See the description of
1361: $performance_table elsewhere in this file. The INSERT calls are made
1362: directly by this subroutine, not through lonmysql because we do a 'bulk'
1363: insert which takes advantage of MySQLs non-SQL compliant INSERT command to
1364: insert multiple rows at a time. If anything has gone wrong during this
1365: process, $returnstatus is updated with a description of the error and
1366: \%student_data is returned.
1367:
1368: Notice we do not insert the data and immediately query it. This means it
1369: is possible for there to be data returned this first time that is not
1370: available the second time. CYA.
1371:
1372: =cut
1373:
1374: ################################################
1375: ################################################
1376: sub update_student_data {
1377: my ($sname,$sdom,$courseid) = @_;
1378: #
1379: # Set up database names
1380: &setup_table_names($courseid);
1381: #
1382: my $student_id = &get_student_id($sname,$sdom);
1383: my $student = $sname.':'.$sdom;
1384: #
1385: my $returnstatus = 'okay';
1386: #
1387: # Download students data
1388: my $time_of_retrieval = time;
1389: my @tmp = &Apache::lonnet::currentdump($courseid,$sdom,$sname);
1390: if ((scalar(@tmp) > 0) && ($tmp[0] =~ /^error:/)) {
1391: &Apache::lonnet::logthis('error getting data for '.
1392: $sname.':'.$sdom.' in course '.$courseid.
1393: ':'.$tmp[0]);
1394: $returnstatus = 'error getting data';
1395: return ($returnstatus,undef);
1396: }
1397: if (scalar(@tmp) < 1) {
1398: return ('no data',undef);
1399: }
1400: my %student_data = @tmp;
1401: my @Results = &store_student_data($sname,$sdom,$courseid,\%student_data);
1402: #
1403: # Set the students update time
1404: if ($Results[0] eq 'okay') {
1405: &store_updatetime($student_id,$time_of_retrieval,$time_of_retrieval);
1406: }
1407: #
1408: return @Results;
1409: }
1410:
1411: sub store_updatetime {
1412: my ($student_id,$updatetime,$fullupdatetime)=@_;
1413: my $values = '';
1414: if (defined($updatetime)) {
1415: $values = 'updatetime='.$updatetime.' ';
1416: }
1417: if (defined($fullupdatetime)) {
1418: if ($values ne '') {
1419: $values .= ',';
1420: }
1421: $values .= 'fullupdatetime='.$fullupdatetime.' ';
1422: }
1423: return if ($values eq '');
1424: my $dbh = &Apache::lonmysql::get_dbh();
1425: my $request = 'UPDATE '.$student_table.' SET '.$values.
1426: ' WHERE student_id='.$student_id.' LIMIT 1';
1427: $dbh->do($request);
1428: }
1429:
1430: sub store_student_data {
1431: my ($sname,$sdom,$courseid,$student_data) = @_;
1432: #
1433: my $student_id = &get_student_id($sname,$sdom);
1434: my $student = $sname.':'.$sdom;
1435: #
1436: my $returnstatus = 'okay';
1437: #
1438: # Remove all of the students data from the table
1439: my $dbh = &Apache::lonmysql::get_dbh();
1440: $dbh->do('DELETE FROM '.$performance_table.' WHERE student_id='.
1441: $student_id);
1442: $dbh->do('DELETE FROM '.$parameters_table.' WHERE student_id='.
1443: $student_id);
1444: #
1445: # Store away the data
1446: #
1447: my $starttime = Time::HiRes::time;
1448: my $elapsed = 0;
1449: my $rows_stored;
1450: my $store_parameters_command = 'INSERT IGNORE INTO '.$parameters_table.
1451: ' VALUES '."\n";
1452: my $num_parameters = 0;
1453: my $store_performance_command = 'INSERT IGNORE INTO '.$performance_table.
1454: ' VALUES '."\n";
1455: return ('error',undef) if (! defined($dbh));
1456: while (my ($current_symb,$param_hash) = each(%{$student_data})) {
1457: #
1458: # make sure the symb is set up properly
1459: my $symb_id = &get_symb_id($current_symb);
1460: #
1461: # Load data into the tables
1462: while (my ($parameter,$value) = each(%$param_hash)) {
1463: my $newstring;
1464: if ($parameter !~ /(timestamp|resource\.(.*)\.(solved|tries|awarded|award|awarddetail|previous))/) {
1465: $newstring = "('".join("','",
1466: $symb_id,$student_id,
1467: $parameter)."',".
1468: $dbh->quote($value)."),\n";
1469: $num_parameters ++;
1470: if ($newstring !~ /''/) {
1471: $store_parameters_command .= $newstring;
1472: $rows_stored++;
1473: }
1474: }
1475: next if ($parameter !~ /^resource\.(.*)\.solved$/);
1476: #
1477: my $part = $1;
1478: my $part_id = &get_part_id($part);
1479: next if (!defined($part_id));
1480: my $solved = $value;
1481: my $tries = $param_hash->{'resource.'.$part.'.tries'};
1482: my $awarded = $param_hash->{'resource.'.$part.'.awarded'};
1483: my $award = $param_hash->{'resource.'.$part.'.award'};
1484: my $awarddetail = $param_hash->{'resource.'.$part.'.awarddetail'};
1485: my $timestamp = $param_hash->{'timestamp'};
1486: #
1487: $solved = '' if (! defined($solved));
1488: $tries = '' if (! defined($tries));
1489: $awarded = '' if (! defined($awarded));
1490: $award = '' if (! defined($award));
1491: $awarddetail = '' if (! defined($awarddetail));
1492: $newstring = "('".join("','",$symb_id,$student_id,$part_id,$part,
1493: $solved,$tries,$awarded,$award,
1494: $awarddetail,$timestamp)."'),\n";
1495: $store_performance_command .= $newstring;
1496: $rows_stored++;
1497: }
1498: }
1499: chop $store_parameters_command;
1500: chop $store_parameters_command;
1501: chop $store_performance_command;
1502: chop $store_performance_command;
1503: my $start = Time::HiRes::time;
1504: $dbh->do($store_performance_command);
1505: if ($dbh->err()) {
1506: &Apache::lonnet::logthis(' bigass insert error:'.$dbh->errstr());
1507: &Apache::lonnet::logthis('command = '.$store_performance_command);
1508: $returnstatus = 'error: unable to insert performance into database';
1509: return ($returnstatus,$student_data);
1510: }
1511: $dbh->do($store_parameters_command) if ($num_parameters>0);
1512: if ($dbh->err()) {
1513: &Apache::lonnet::logthis(' bigass insert error:'.$dbh->errstr());
1514: &Apache::lonnet::logthis('command = '.$store_parameters_command);
1515: &Apache::lonnet::logthis('rows_stored = '.$rows_stored);
1516: &Apache::lonnet::logthis('student_id = '.$student_id);
1517: $returnstatus = 'error: unable to insert parameters into database';
1518: return ($returnstatus,$student_data);
1519: }
1520: $elapsed += Time::HiRes::time - $start;
1521: return ($returnstatus,$student_data);
1522: }
1523:
1524: ######################################
1525: ######################################
1526:
1527: =pod
1528:
1529: =item &ensure_tables_are_set_up($courseid)
1530:
1531: Checks to be sure the MySQL tables for the given class are set up.
1532: If $courseid is omitted it will be obtained from the environment.
1533:
1534: Returns nothing on success and 'error' on failure
1535:
1536: =cut
1537:
1538: ######################################
1539: ######################################
1540: sub ensure_tables_are_set_up {
1541: my ($courseid) = @_;
1542: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1543: #
1544: # Clean out package variables
1545: &setup_table_names($courseid);
1546: #
1547: # if the tables do not exist, make them
1548: my @CurrentTable = &Apache::lonmysql::tables_in_db();
1549: my ($found_symb,$found_student,$found_part,
1550: $found_performance,$found_parameters,$found_fulldump_part,
1551: $found_fulldump_response,$found_fulldump_timestamp);
1552: foreach (@CurrentTable) {
1553: $found_symb = 1 if ($_ eq $symb_table);
1554: $found_student = 1 if ($_ eq $student_table);
1555: $found_part = 1 if ($_ eq $part_table);
1556: $found_performance = 1 if ($_ eq $performance_table);
1557: $found_parameters = 1 if ($_ eq $parameters_table);
1558: $found_fulldump_part = 1 if ($_ eq $fulldump_part_table);
1559: $found_fulldump_response = 1 if ($_ eq $fulldump_response_table);
1560: $found_fulldump_timestamp = 1 if ($_ eq $fulldump_timestamp_table);
1561: }
1562: if (!$found_symb ||
1563: !$found_student || !$found_part ||
1564: !$found_performance || !$found_parameters ||
1565: !$found_fulldump_part || !$found_fulldump_response ||
1566: !$found_fulldump_timestamp ) {
1567: if (&init_dbs($courseid)) {
1568: return 'error';
1569: }
1570: }
1571: }
1572:
1573: ################################################
1574: ################################################
1575:
1576: =pod
1577:
1578: =item &ensure_current_data()
1579:
1580: Input: $sname, $sdom, $courseid
1581:
1582: Output: $status, $data
1583:
1584: This routine ensures the data for a given student is up to date.
1585: The $student_table is queried to determine the time of the last update.
1586: If the students data is out of date, &update_student_data() is called.
1587: The return values from the call to &update_student_data() are returned.
1588:
1589: =cut
1590:
1591: ################################################
1592: ################################################
1593: sub ensure_current_data {
1594: my ($sname,$sdom,$courseid) = @_;
1595: my $status = 'okay'; # return value
1596: #
1597: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1598: &ensure_tables_are_set_up($courseid);
1599: #
1600: # Get the update time for the user
1601: my $updatetime = 0;
1602: my $modifiedtime = &Apache::lonnet::GetFileTimestamp
1603: ($sdom,$sname,$courseid.'.db',
1604: $Apache::lonnet::perlvar{'lonUsersDir'});
1605: #
1606: my $student_id = &get_student_id($sname,$sdom);
1607: my @Result = &Apache::lonmysql::get_rows($student_table,
1608: "student_id ='$student_id'");
1609: my $data = undef;
1610: if (@Result) {
1611: $updatetime = $Result[0]->[5]; # Ack! This is dumb!
1612: }
1613: if ($modifiedtime > $updatetime) {
1614: ($status,$data) = &update_student_data($sname,$sdom,$courseid);
1615: }
1616: return ($status,$data);
1617: }
1618:
1619: ################################################
1620: ################################################
1621:
1622: =pod
1623:
1624: =item &ensure_current_full_data($sname,$sdom,$courseid)
1625:
1626: Input: $sname, $sdom, $courseid
1627:
1628: Output: $status
1629:
1630: This routine ensures the fulldata (the data from a lonnet::dump, not a
1631: lonnet::currentdump) for a given student is up to date.
1632: The $student_table is queried to determine the time of the last update.
1633: If the students fulldata is out of date, &update_full_student_data() is
1634: called.
1635:
1636: The return value from the call to &update_full_student_data() is returned.
1637:
1638: =cut
1639:
1640: ################################################
1641: ################################################
1642: sub ensure_current_full_data {
1643: my ($sname,$sdom,$courseid) = @_;
1644: my $status = 'okay'; # return value
1645: #
1646: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1647: &ensure_tables_are_set_up($courseid);
1648: #
1649: # Get the update time for the user
1650: my $modifiedtime = &Apache::lonnet::GetFileTimestamp
1651: ($sdom,$sname,$courseid.'.db',
1652: $Apache::lonnet::perlvar{'lonUsersDir'});
1653: #
1654: my $student_id = &get_student_id($sname,$sdom);
1655: my @Result = &Apache::lonmysql::get_rows($student_table,
1656: "student_id ='$student_id'");
1657: my $updatetime;
1658: if (@Result && ref($Result[0]) eq 'ARRAY') {
1659: $updatetime = $Result[0]->[6];
1660: }
1661: if (! defined($updatetime) || $modifiedtime > $updatetime) {
1662: $status = &update_full_student_data($sname,$sdom,$courseid);
1663: }
1664: return $status;
1665: }
1666:
1667: ################################################
1668: ################################################
1669:
1670: =pod
1671:
1672: =item &get_student_data_from_performance_cache()
1673:
1674: Input: $sname, $sdom, $symb, $courseid
1675:
1676: Output: hash reference containing the data for the given student.
1677: If $symb is undef, all the students data is returned.
1678:
1679: This routine is the heart of the local caching system. See the description
1680: of $performance_table, $symb_table, $student_table, and $part_table. The
1681: main task is building the MySQL request. The tables appear in the request
1682: in the order in which they should be parsed by MySQL. When searching
1683: on a student the $student_table is used to locate the 'student_id'. All
1684: rows in $performance_table which have a matching 'student_id' are returned,
1685: with data from $part_table and $symb_table which match the entries in
1686: $performance_table, 'part_id' and 'symb_id'. When searching on a symb,
1687: the $symb_table is processed first, with matching rows grabbed from
1688: $performance_table and filled in from $part_table and $student_table in
1689: that order.
1690:
1691: Running 'EXPLAIN ' on the 'SELECT' statements generated can be quite
1692: interesting, especially if you play with the order the tables are listed.
1693:
1694: =cut
1695:
1696: ################################################
1697: ################################################
1698: sub get_student_data_from_performance_cache {
1699: my ($sname,$sdom,$symb,$courseid)=@_;
1700: my $student = $sname.':'.$sdom if (defined($sname) && defined($sdom));
1701: &setup_table_names($courseid);
1702: #
1703: # Return hash
1704: my $studentdata;
1705: #
1706: my $dbh = &Apache::lonmysql::get_dbh();
1707: my $request = "SELECT ".
1708: "d.symb,a.part,a.solved,a.tries,a.awarded,a.award,a.awarddetail,".
1709: "a.timestamp ";
1710: if (defined($student)) {
1711: $request .= "FROM $student_table AS b ".
1712: "LEFT JOIN $performance_table AS a ON b.student_id=a.student_id ".
1713: # "LEFT JOIN $part_table AS c ON c.part_id = a.part_id ".
1714: "LEFT JOIN $symb_table AS d ON d.symb_id = a.symb_id ".
1715: "WHERE student='$student'";
1716: if (defined($symb) && $symb ne '') {
1717: $request .= " AND d.symb=".$dbh->quote($symb);
1718: }
1719: } elsif (defined($symb) && $symb ne '') {
1720: $request .= "FROM $symb_table as d ".
1721: "LEFT JOIN $performance_table AS a ON d.symb_id=a.symb_id ".
1722: # "LEFT JOIN $part_table AS c ON c.part_id = a.part_id ".
1723: "LEFT JOIN $student_table AS b ON b.student_id = a.student_id ".
1724: "WHERE symb='".$dbh->quote($symb)."'";
1725: }
1726: my $starttime = Time::HiRes::time;
1727: my $rows_retrieved = 0;
1728: my $sth = $dbh->prepare($request);
1729: $sth->execute();
1730: if ($sth->err()) {
1731: &Apache::lonnet::logthis("Unable to execute MySQL request:");
1732: &Apache::lonnet::logthis("\n".$request."\n");
1733: &Apache::lonnet::logthis("error is:".$sth->errstr());
1734: return undef;
1735: }
1736: foreach my $row (@{$sth->fetchall_arrayref}) {
1737: $rows_retrieved++;
1738: my ($symb,$part,$solved,$tries,$awarded,$award,$awarddetail,$time) =
1739: (@$row);
1740: my $base = 'resource.'.$part;
1741: $studentdata->{$symb}->{$base.'.solved'} = $solved;
1742: $studentdata->{$symb}->{$base.'.tries'} = $tries;
1743: $studentdata->{$symb}->{$base.'.awarded'} = $awarded;
1744: $studentdata->{$symb}->{$base.'.award'} = $award;
1745: $studentdata->{$symb}->{$base.'.awarddetail'} = $awarddetail;
1746: $studentdata->{$symb}->{'timestamp'} = $time if (defined($time) && $time ne '');
1747: }
1748: ## Get misc parameters
1749: $request = 'SELECT c.symb,a.parameter,a.value '.
1750: "FROM $student_table AS b ".
1751: "LEFT JOIN $parameters_table AS a ON b.student_id=a.student_id ".
1752: "LEFT JOIN $symb_table AS c ON c.symb_id = a.symb_id ".
1753: "WHERE student='$student'";
1754: if (defined($symb) && $symb ne '') {
1755: $request .= " AND c.symb=".$dbh->quote($symb);
1756: }
1757: $sth = $dbh->prepare($request);
1758: $sth->execute();
1759: if ($sth->err()) {
1760: &Apache::lonnet::logthis("Unable to execute MySQL request:");
1761: &Apache::lonnet::logthis("\n".$request."\n");
1762: &Apache::lonnet::logthis("error is:".$sth->errstr());
1763: if (defined($symb) && $symb ne '') {
1764: $studentdata = $studentdata->{$symb};
1765: }
1766: return $studentdata;
1767: }
1768: #
1769: foreach my $row (@{$sth->fetchall_arrayref}) {
1770: $rows_retrieved++;
1771: my ($symb,$parameter,$value) = (@$row);
1772: $studentdata->{$symb}->{$parameter} = $value;
1773: }
1774: #
1775: if (defined($symb) && $symb ne '') {
1776: $studentdata = $studentdata->{$symb};
1777: }
1778: return $studentdata;
1779: }
1780:
1781: ################################################
1782: ################################################
1783:
1784: =pod
1785:
1786: =item &get_current_state()
1787:
1788: Input: $sname,$sdom,$symb,$courseid
1789:
1790: Output: Described below
1791:
1792: Retrieve the current status of a students performance. $sname and
1793: $sdom are the only required parameters. If $symb is undef the results
1794: of an &Apache::lonnet::currentdump() will be returned.
1795: If $courseid is undef it will be retrieved from the environment.
1796:
1797: The return structure is based on &Apache::lonnet::currentdump. If
1798: $symb is unspecified, all the students data is returned in a hash of
1799: the form:
1800: (
1801: symb1 => { param1 => value1, param2 => value2 ... },
1802: symb2 => { param1 => value1, param2 => value2 ... },
1803: )
1804:
1805: If $symb is specified, a hash of
1806: (
1807: param1 => value1,
1808: param2 => value2,
1809: )
1810: is returned.
1811:
1812: If no data is found for $symb, or if the student has no performance data,
1813: an empty list is returned.
1814:
1815: =cut
1816:
1817: ################################################
1818: ################################################
1819: sub get_current_state {
1820: my ($sname,$sdom,$symb,$courseid,$forcedownload)=@_;
1821: #
1822: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1823: #
1824: return () if (! defined($sname) || ! defined($sdom));
1825: #
1826: my ($status,$data) = &ensure_current_data($sname,$sdom,$courseid);
1827: # &Apache::lonnet::logthis
1828: # ('sname = '.$sname.
1829: # ' domain = '.$sdom.
1830: # ' status = '.$status.
1831: # ' data is '.(defined($data)?'defined':'undefined'));
1832: # while (my ($symb,$hash) = each(%$data)) {
1833: # &Apache::lonnet::logthis($symb."\n----------------------------------");
1834: # while (my ($key,$value) = each (%$hash)) {
1835: # &Apache::lonnet::logthis(" ".$key." = ".$value);
1836: # }
1837: # }
1838: #
1839: if (defined($data) && defined($symb) && ref($data->{$symb})) {
1840: return %{$data->{$symb}};
1841: } elsif (defined($data) && ! defined($symb) && ref($data)) {
1842: return %$data;
1843: }
1844: if ($status eq 'no data') {
1845: return ();
1846: } else {
1847: if ($status ne 'okay' && $status ne '') {
1848: &Apache::lonnet::logthis('status = '.$status);
1849: return ();
1850: }
1851: my $returnhash = &get_student_data_from_performance_cache($sname,$sdom,
1852: $symb,$courseid);
1853: return %$returnhash if (defined($returnhash));
1854: }
1855: return ();
1856: }
1857:
1858: ################################################
1859: ################################################
1860:
1861: =pod
1862:
1863: =item &get_problem_statistics()
1864:
1865: Gather data on a given problem. The database is assumed to be
1866: populated and all local caching variables are assumed to be set
1867: properly. This means you need to call &ensure_current_data for
1868: the students you are concerned with prior to calling this routine.
1869:
1870: Inputs: $students, $symb, $part, $courseid
1871:
1872: =over 4
1873:
1874: =item $students is an array of hash references.
1875: Each hash must contain at least the 'username' and 'domain' of a student.
1876:
1877: =item $symb is the symb for the problem.
1878:
1879: =item $part is the part id you need statistics for
1880:
1881: =item $courseid is the course id, of course!
1882:
1883: =back
1884:
1885: Outputs: See the code for up to date information. A hash reference is
1886: returned. The hash has the following keys defined:
1887:
1888: =over 4
1889:
1890: =item num_students The number of students attempting the problem
1891:
1892: =item tries The total number of tries for the students
1893:
1894: =item max_tries The maximum number of tries taken
1895:
1896: =item mean_tries The average number of tries
1897:
1898: =item num_solved The number of students able to solve the problem
1899:
1900: =item num_override The number of students whose answer is 'correct_by_override'
1901:
1902: =item deg_of_diff The degree of difficulty of the problem
1903:
1904: =item std_tries The standard deviation of the number of tries
1905:
1906: =item skew_tries The skew of the number of tries
1907:
1908: =item per_wrong The number of students attempting the problem who were not
1909: able to answer it correctly.
1910:
1911: =back
1912:
1913: =cut
1914:
1915: ################################################
1916: ################################################
1917: sub get_problem_statistics {
1918: my ($Sections,$status,$symb,$part,$courseid) = @_;
1919: return if (! defined($symb) || ! defined($part));
1920: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
1921: #
1922: &setup_table_names($courseid);
1923: my $symb_id = &get_symb_id($symb);
1924: my $part_id = &get_part_id($part);
1925: my $stats_table = $courseid.'_problem_stats';
1926: #
1927: my $dbh = &Apache::lonmysql::get_dbh();
1928: return undef if (! defined($dbh));
1929: #
1930: $dbh->do('DROP TABLE '.$stats_table); # May return an error
1931: my $request =
1932: 'CREATE TEMPORARY TABLE '.$stats_table.' '.
1933: 'SELECT a.student_id,a.solved,a.award,a.awarded,a.tries '.
1934: 'FROM '.$performance_table.' AS a ';
1935: if ((defined($Sections) && lc($Sections->[0]) ne 'all') ||
1936: (defined($status) && lc($status) ne 'any')) {
1937: $request .= 'NATURAL LEFT JOIN '.$student_table.' AS b ';
1938: }
1939: $request .= ' WHERE a.symb_id='.$symb_id.' AND a.part_id='.$part_id;
1940: if (defined($Sections) && lc($Sections->[0]) ne 'all') {
1941: $request .= ' AND ('.
1942: join(' OR ', map { "b.section='".$_."'" } @$Sections
1943: ).')';
1944: }
1945: if (defined($status) && lc($status) ne 'any') {
1946: $request .= " AND b.status='".$status."'";
1947: }
1948: $dbh->do($request);
1949: # &Apache::lonnet::logthis('request = '.$/.$request);
1950: $request = 'SELECT COUNT(*),SUM(tries),MAX(tries),AVG(tries),STD(tries) '.
1951: 'FROM '.$stats_table;
1952: my ($num,$tries,$mod,$mean,$STD) = &execute_SQL_request
1953: ($dbh,$request);
1954: # &Apache::lonnet::logthis('request = '.$/.$request);
1955: $request = 'SELECT SUM(awarded) FROM '.$stats_table;
1956: my ($Solved) = &execute_SQL_request($dbh,$request);
1957: # &Apache::lonnet::logthis('request = '.$/.$request);
1958: $request = 'SELECT SUM(awarded) FROM '.$stats_table.
1959: " WHERE solved='correct_by_override'";
1960: # &Apache::lonnet::logthis('request = '.$/.$request);
1961: my ($solved) = &execute_SQL_request($dbh,$request);
1962: # $Solved = int($Solved);
1963: # $solved = int($solved);
1964: #
1965: $num = 0 if (! defined($num));
1966: $tries = 0 if (! defined($tries));
1967: $mod = 0 if (! defined($mod));
1968: $STD = 0 if (! defined($STD));
1969: $Solved = 0 if (! defined($Solved));
1970: $solved = 0 if (! defined($solved));
1971: #
1972: my $DegOfDiff = 'nan';
1973: $DegOfDiff = 1-($Solved)/$tries if ($tries>0);
1974:
1975: my $SKEW = 'nan';
1976: my $wrongpercent = 0;
1977: if ($num > 0) {
1978: ($SKEW) = &execute_SQL_request($dbh,'SELECT SQRT(SUM('.
1979: 'POWER(tries - '.$STD.',3)'.
1980: '))/'.$num.' FROM '.$stats_table);
1981: $wrongpercent=int(10*100*($num-$Solved+$solved)/$num)/10;
1982: }
1983: #
1984: # $dbh->do('DROP TABLE '.$stats_table); # May return an error
1985: #
1986: # Store in metadata
1987: #
1988: if ($num) {
1989: my %storestats=();
1990:
1991: my $urlres=(&Apache::lonnet::decode_symb($symb))[2];
1992:
1993: $storestats{$courseid.'___'.$urlres.'___timestamp'}=time;
1994: $storestats{$courseid.'___'.$urlres.'___stdno'}=$num;
1995: $storestats{$courseid.'___'.$urlres.'___avetries'}=$mean;
1996: $storestats{$courseid.'___'.$urlres.'___difficulty'}=$DegOfDiff;
1997:
1998: $urlres=~/^(\w+)\/(\w+)/;
1999: &Apache::lonnet::put('nohist_resevaldata',\%storestats,$1,$2);
2000: }
2001: #
2002: # Return result
2003: #
2004: return { num_students => $num,
2005: tries => $tries,
2006: max_tries => $mod,
2007: mean_tries => $mean,
2008: std_tries => $STD,
2009: skew_tries => $SKEW,
2010: num_solved => $Solved,
2011: num_override => $solved,
2012: per_wrong => $wrongpercent,
2013: deg_of_diff => $DegOfDiff };
2014: }
2015:
2016: sub execute_SQL_request {
2017: my ($dbh,$request)=@_;
2018: # &Apache::lonnet::logthis($request);
2019: my $sth = $dbh->prepare($request);
2020: $sth->execute();
2021: my $row = $sth->fetchrow_arrayref();
2022: if (ref($row) eq 'ARRAY' && scalar(@$row)>0) {
2023: return @$row;
2024: }
2025: return ();
2026: }
2027:
2028: sub get_student_data {
2029: my ($students,$courseid) = @_;
2030: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
2031: &setup_table_names($courseid);
2032: my $dbh = &Apache::lonmysql::get_dbh();
2033: return undef if (! defined($dbh));
2034: my $request = 'SELECT '.
2035: 'student_id, student '.
2036: 'FROM '.$student_table;
2037: if (defined($students)) {
2038: $request .= ' WHERE ('.
2039: join(' OR ', map {'student_id='.
2040: &get_student_id($_->{'username'},
2041: $_->{'domain'})
2042: } @$students
2043: ).')';
2044: }
2045: $request.= ' ORDER BY student_id';
2046: my $sth = $dbh->prepare($request);
2047: $sth->execute();
2048: if ($dbh->err) {
2049: &Apache::lonnet::logthis('error = '.$dbh->errstr());
2050: return undef;
2051: }
2052: my $dataset = $sth->fetchall_arrayref();
2053: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) {
2054: return $dataset;
2055: }
2056: }
2057:
2058: sub RD_student_id { return 0; }
2059: sub RD_awarddetail { return 1; }
2060: sub RD_response_eval { return 2; }
2061: sub RD_submission { return 3; }
2062: sub RD_timestamp { return 4; }
2063: sub RD_tries { return 5; }
2064: sub RD_sname { return 6; }
2065:
2066: sub get_response_data {
2067: my ($students,$symb,$response,$courseid) = @_;
2068: return undef if (! defined($symb) ||
2069: ! defined($response));
2070: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
2071: #
2072: &setup_table_names($courseid);
2073: my $symb_id = &get_symb_id($symb);
2074: my $response_id = &get_part_id($response);
2075: #
2076: my $dbh = &Apache::lonmysql::get_dbh();
2077: return undef if (! defined($dbh));
2078: my $request = 'SELECT '.
2079: 'a.student_id, a.awarddetail, a.response_specific_value, '.
2080: 'a.submission, b.timestamp, c.tries, d.student '.
2081: 'FROM '.$fulldump_response_table.' AS a '.
2082: 'LEFT JOIN '.$fulldump_timestamp_table.' AS b '.
2083: 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '.
2084: 'a.transaction = b.transaction '.
2085: 'LEFT JOIN '.$fulldump_part_table.' AS c '.
2086: 'ON a.symb_id=c.symb_id AND a.student_id=c.student_id AND '.
2087: 'a.part_id=c.part_id AND a.transaction = c.transaction '.
2088: 'LEFT JOIN '.$student_table.' AS d '.
2089: 'ON a.student_id=d.student_id '.
2090: 'WHERE '.
2091: 'a.symb_id='.$symb_id.' AND a.response_id='.$response_id;
2092: if (defined($students)) {
2093: $request .= ' AND ('.
2094: join(' OR ', map {'a.student_id='.
2095: &get_student_id($_->{'username'},
2096: $_->{'domain'})
2097: } @$students
2098: ).')';
2099: }
2100: $request .= ' ORDER BY b.timestamp';
2101: # &Apache::lonnet::logthis("request =\n".$request);
2102: my $sth = $dbh->prepare($request);
2103: $sth->execute();
2104: if ($dbh->err) {
2105: &Apache::lonnet::logthis('error = '.$dbh->errstr());
2106: return undef;
2107: }
2108: my $dataset = $sth->fetchall_arrayref();
2109: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) {
2110: # Clear the \'s from around the submission
2111: for (my $i =0;$i<scalar(@$dataset);$i++) {
2112: $dataset->[$i]->[3] =~ s/(\'$|^\')//g;
2113: }
2114: return $dataset;
2115: }
2116: }
2117:
2118:
2119: sub RDs_awarddetail { return 3; }
2120: sub RDs_submission { return 2; }
2121: sub RDs_timestamp { return 1; }
2122: sub RDs_tries { return 0; }
2123: sub RDs_awarded { return 4; }
2124:
2125: sub get_response_data_by_student {
2126: my ($student,$symb,$response,$courseid) = @_;
2127: return undef if (! defined($symb) ||
2128: ! defined($response));
2129: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
2130: #
2131: &setup_table_names($courseid);
2132: my $symb_id = &get_symb_id($symb);
2133: my $response_id = &get_part_id($response);
2134: #
2135: my $student_id = &get_student_id($student->{'username'},
2136: $student->{'domain'});
2137: #
2138: my $dbh = &Apache::lonmysql::get_dbh();
2139: return undef if (! defined($dbh));
2140: my $request = 'SELECT '.
2141: 'c.tries, b.timestamp, a.submission, a.awarddetail, e.awarded '.
2142: 'FROM '.$fulldump_response_table.' AS a '.
2143: 'LEFT JOIN '.$fulldump_timestamp_table.' AS b '.
2144: 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '.
2145: 'a.transaction = b.transaction '.
2146: 'LEFT JOIN '.$fulldump_part_table.' AS c '.
2147: 'ON a.symb_id=c.symb_id AND a.student_id=c.student_id AND '.
2148: 'a.part_id=c.part_id AND a.transaction = c.transaction '.
2149: 'LEFT JOIN '.$student_table.' AS d '.
2150: 'ON a.student_id=d.student_id '.
2151: 'LEFT JOIN '.$performance_table.' AS e '.
2152: 'ON a.symb_id=e.symb_id AND a.part_id=e.part_id AND '.
2153: 'a.student_id=e.student_id AND c.tries=e.tries '.
2154: 'WHERE '.
2155: 'a.symb_id='.$symb_id.' AND a.response_id='.$response_id.
2156: ' AND a.student_id='.$student_id.' ORDER BY b.timestamp';
2157: # &Apache::lonnet::logthis("request =\n".$request);
2158: my $sth = $dbh->prepare($request);
2159: $sth->execute();
2160: if ($dbh->err) {
2161: &Apache::lonnet::logthis('error = '.$dbh->errstr());
2162: return undef;
2163: }
2164: my $dataset = $sth->fetchall_arrayref();
2165: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) {
2166: # Clear the \'s from around the submission
2167: for (my $i =0;$i<scalar(@$dataset);$i++) {
2168: $dataset->[$i]->[2] =~ s/(\'$|^\')//g;
2169: }
2170: return $dataset;
2171: }
2172: return undef; # error occurred
2173: }
2174:
2175: sub RT_student_id { return 0; }
2176: sub RT_awarded { return 1; }
2177: sub RT_tries { return 2; }
2178: sub RT_timestamp { return 3; }
2179:
2180: sub get_response_time_data {
2181: my ($students,$symb,$part,$courseid) = @_;
2182: return undef if (! defined($symb) ||
2183: ! defined($part));
2184: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
2185: #
2186: &setup_table_names($courseid);
2187: my $symb_id = &get_symb_id($symb);
2188: my $part_id = &get_part_id($part);
2189: #
2190: my $dbh = &Apache::lonmysql::get_dbh();
2191: return undef if (! defined($dbh));
2192: my $request = 'SELECT '.
2193: 'a.student_id, a.awarded, a.tries, b.timestamp '.
2194: 'FROM '.$fulldump_part_table.' AS a '.
2195: 'NATURAL LEFT JOIN '.$fulldump_timestamp_table.' AS b '.
2196: # 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '.
2197: # 'a.transaction = b.transaction '.
2198: 'WHERE '.
2199: 'a.symb_id='.$symb_id.' AND a.part_id='.$part_id;
2200: if (defined($students)) {
2201: $request .= ' AND ('.
2202: join(' OR ', map {'a.student_id='.
2203: &get_student_id($_->{'username'},
2204: $_->{'domain'})
2205: } @$students
2206: ).')';
2207: }
2208: $request .= ' ORDER BY b.timestamp';
2209: # &Apache::lonnet::logthis("request =\n".$request);
2210: my $sth = $dbh->prepare($request);
2211: $sth->execute();
2212: if ($dbh->err) {
2213: &Apache::lonnet::logthis('error = '.$dbh->errstr());
2214: return undef;
2215: }
2216: my $dataset = $sth->fetchall_arrayref();
2217: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) {
2218: return $dataset;
2219: }
2220:
2221: }
2222:
2223: ################################################
2224: ################################################
2225:
2226: =pod
2227:
2228: =item &get_student_scores($Sections,$Symbs,$enrollment,$courseid)
2229:
2230: =cut
2231:
2232: ################################################
2233: ################################################
2234: sub get_student_scores {
2235: my ($Sections,$Symbs,$enrollment,$courseid,$starttime,$endtime) = @_;
2236: $courseid = $ENV{'request.course.id'} if (! defined($courseid));
2237: &setup_table_names($courseid);
2238: my $dbh = &Apache::lonmysql::get_dbh();
2239: return (undef) if (! defined($dbh));
2240: my $tmptable = $courseid.'_temp_'.time;
2241: #
2242: my $symb_requirements;
2243: if (defined($Symbs) && @$Symbs) {
2244: $symb_requirements = '('.
2245: join(' OR ', map{ "(a.symb_id='".&get_symb_id($_->{'symb'}).
2246: "' AND a.part_id='".&get_part_id($_->{'part'}).
2247: "')"
2248: } @$Symbs).')';
2249: }
2250: #
2251: my $student_requirements;
2252: if ( (defined($Sections) && $Sections->[0] ne 'all')) {
2253: $student_requirements = '('.
2254: join(' OR ', map { "b.section='".$_."'" } @$Sections
2255: ).')';
2256: }
2257: #
2258: my $enrollment_requirements=undef;
2259: if (defined($enrollment) && $enrollment ne 'Any') {
2260: $enrollment_requirements = "b.status='".$enrollment."'";
2261: }
2262: #
2263: my $time_requirements = undef;
2264: if (defined($starttime)) {
2265: $time_requirements .= "a.timestamp>='".$starttime."'";
2266: if (defined($endtime)) {
2267: $time_requirements .= " AND a.timestamp<='".$endtime."'";
2268: }
2269: } elsif (defined($endtime)) {
2270: $time_requirements .= "a.timestamp<='".$endtime."'";
2271: }
2272: ##
2273: ##
2274: my $request = 'CREATE TEMPORARY TABLE IF NOT EXISTS '.$tmptable.
2275: ' SELECT a.student_id,SUM(a.awarded) AS score FROM '.
2276: $performance_table.' AS a ';
2277: if (defined($student_requirements) || defined($enrollment_requirements)) {
2278: $request .= ' NATURAL LEFT JOIN '.$student_table.' AS b ';
2279: }
2280: if (defined($symb_requirements) ||
2281: defined($student_requirements) ||
2282: defined($enrollment_requirements) ) {
2283: $request .= ' WHERE ';
2284: }
2285: if (defined($symb_requirements)) {
2286: $request .= $symb_requirements.' AND ';
2287: }
2288: if (defined($student_requirements)) {
2289: $request .= $student_requirements.' AND ';
2290: }
2291: if (defined($enrollment_requirements)) {
2292: $request .= $enrollment_requirements.' AND ';
2293: }
2294: if (defined($time_requirements)) {
2295: $request .= $time_requirements.' AND ';
2296: }
2297: $request =~ s/ AND $//; # Strip of the trailing ' AND '.
2298: $request .= ' GROUP BY a.student_id';
2299: # &Apache::lonnet::logthis("request = \n".$request);
2300: my $sth = $dbh->prepare($request);
2301: $sth->execute();
2302: if ($dbh->err) {
2303: &Apache::lonnet::logthis('error = '.$dbh->errstr());
2304: return undef;
2305: }
2306: $request = 'SELECT score,COUNT(*) FROM '.$tmptable.' GROUP BY score';
2307: # &Apache::lonnet::logthis("request = \n".$request);
2308: $sth = $dbh->prepare($request);
2309: $sth->execute();
2310: if ($dbh->err) {
2311: &Apache::lonnet::logthis('error = '.$dbh->errstr());
2312: return undef;
2313: }
2314: my $dataset = $sth->fetchall_arrayref();
2315: return $dataset;
2316: }
2317:
2318: ################################################
2319: ################################################
2320:
2321: =pod
2322:
2323: =item &setup_table_names()
2324:
2325: input: course id
2326:
2327: output: none
2328:
2329: Cleans up the package variables for local caching.
2330:
2331: =cut
2332:
2333: ################################################
2334: ################################################
2335: sub setup_table_names {
2336: my ($courseid) = @_;
2337: if (! defined($courseid)) {
2338: $courseid = $ENV{'request.course.id'};
2339: }
2340: #
2341: if (! defined($current_course) || $current_course ne $courseid) {
2342: # Clear out variables
2343: $have_read_part_table = 0;
2344: undef(%ids_by_part);
2345: undef(%parts_by_id);
2346: $have_read_symb_table = 0;
2347: undef(%ids_by_symb);
2348: undef(%symbs_by_id);
2349: $have_read_student_table = 0;
2350: undef(%ids_by_student);
2351: undef(%students_by_id);
2352: #
2353: $current_course = $courseid;
2354: }
2355: #
2356: # Set up database names
2357: my $base_id = $courseid;
2358: $symb_table = $base_id.'_'.'symb';
2359: $part_table = $base_id.'_'.'part';
2360: $student_table = $base_id.'_'.'student';
2361: $performance_table = $base_id.'_'.'performance';
2362: $parameters_table = $base_id.'_'.'parameters';
2363: $fulldump_part_table = $base_id.'_'.'partdata';
2364: $fulldump_response_table = $base_id.'_'.'responsedata';
2365: $fulldump_timestamp_table = $base_id.'_'.'timestampdata';
2366: #
2367: @Tables = (
2368: $symb_table,
2369: $part_table,
2370: $student_table,
2371: $performance_table,
2372: $parameters_table,
2373: $fulldump_part_table,
2374: $fulldump_response_table,
2375: $fulldump_timestamp_table,
2376: );
2377: return;
2378: }
2379:
2380: ################################################
2381: ################################################
2382:
2383: =pod
2384:
2385: =back
2386:
2387: =item End of Local Data Caching Subroutines
2388:
2389: =cut
2390:
2391: ################################################
2392: ################################################
2393:
2394: } # End scope of table identifiers
2395:
2396: ################################################
2397: ################################################
2398:
2399: =pod
2400:
2401: =head3 Classlist Subroutines
2402:
2403: =item &get_classlist();
2404:
2405: Retrieve the classist of a given class or of the current class. Student
2406: information is returned from the classlist.db file and, if needed,
2407: from the students environment.
2408:
2409: Optional arguments are $cid, $cdom, and $cnum (course id, course domain,
2410: and course number, respectively). Any omitted arguments will be taken
2411: from the current environment ($ENV{'request.course.id'},
2412: $ENV{'course.'.$cid.'.domain'}, and $ENV{'course.'.$cid.'.num'}).
2413:
2414: Returns a reference to a hash which contains:
2415: keys '$sname:$sdom'
2416: values [$sdom,$sname,$end,$start,$id,$section,$fullname,$status,$type]
2417:
2418: The constant values CL_SDOM, CL_SNAME, CL_END, etc. can be used
2419: as indices into the returned list to future-proof clients against
2420: changes in the list order.
2421:
2422: =cut
2423:
2424: ################################################
2425: ################################################
2426:
2427: sub CL_SDOM { return 0; }
2428: sub CL_SNAME { return 1; }
2429: sub CL_END { return 2; }
2430: sub CL_START { return 3; }
2431: sub CL_ID { return 4; }
2432: sub CL_SECTION { return 5; }
2433: sub CL_FULLNAME { return 6; }
2434: sub CL_STATUS { return 7; }
2435: sub CL_TYPE { return 8; }
2436:
2437: sub get_classlist {
2438: my ($cid,$cdom,$cnum) = @_;
2439: $cid = $cid || $ENV{'request.course.id'};
2440: $cdom = $cdom || $ENV{'course.'.$cid.'.domain'};
2441: $cnum = $cnum || $ENV{'course.'.$cid.'.num'};
2442: my $now = time;
2443: #
2444: my %classlist=&Apache::lonnet::dump('classlist',$cdom,$cnum);
2445: while (my ($student,$info) = each(%classlist)) {
2446: if ($student =~ /^(con_lost|error|no_such_host)/i) {
2447: &Apache::lonnet::logthis('get_classlist error for '.$cid.':'.$student);
2448: return undef;
2449: }
2450: my ($sname,$sdom) = split(/:/,$student);
2451: my @Values = split(/:/,$info);
2452: my ($end,$start,$id,$section,$fullname,$type);
2453: if (@Values > 2) {
2454: ($end,$start,$id,$section,$fullname,$type) = @Values;
2455: } else { # We have to get the data ourselves
2456: ($end,$start) = @Values;
2457: $section = &Apache::lonnet::getsection($sdom,$sname,$cid);
2458: my %info=&Apache::lonnet::get('environment',
2459: ['firstname','middlename',
2460: 'lastname','generation','id'],
2461: $sdom, $sname);
2462: my ($tmp) = keys(%info);
2463: if ($tmp =~/^(con_lost|error|no_such_host)/i) {
2464: $fullname = 'not available';
2465: $id = 'not available';
2466: &Apache::lonnet::logthis('unable to retrieve environment '.
2467: 'for '.$sname.':'.$sdom);
2468: } else {
2469: $fullname = &ProcessFullName(@info{qw/lastname generation
2470: firstname middlename/});
2471: $id = $info{'id'};
2472: }
2473: # Update the classlist with this students information
2474: if ($fullname ne 'not available') {
2475: my $enrolldata = join(':',$end,$start,$id,$section,$fullname);
2476: my $reply=&Apache::lonnet::cput('classlist',
2477: {$student => $enrolldata},
2478: $cdom,$cnum);
2479: if ($reply !~ /^(ok|delayed)/) {
2480: &Apache::lonnet::logthis('Unable to update classlist for '.
2481: 'student '.$sname.':'.$sdom.
2482: ' error:'.$reply);
2483: }
2484: }
2485: }
2486: my $status='Expired';
2487: if(((!$end) || $now < $end) && ((!$start) || ($now > $start))) {
2488: $status='Active';
2489: }
2490: $classlist{$student} =
2491: [$sdom,$sname,$end,$start,$id,$section,$fullname,$status,$type];
2492: }
2493: if (wantarray()) {
2494: return (\%classlist,['domain','username','end','start','id',
2495: 'section','fullname','status','type']);
2496: } else {
2497: return \%classlist;
2498: }
2499: }
2500:
2501: # ----- END HELPER FUNCTIONS --------------------------------------------
2502:
2503: 1;
2504: __END__
2505:
2506:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>