loncom/interface/loncoursedata.pm - view

File: [LON-CAPA] / loncom / interface / loncoursedata.pm
Revision 1.111: download - view: text, annotated - select for diffs
Tue Dec 16 16:47:16 2003 UTC (20 years, 5 months ago) by raeburn
Branches: MAIN
CVS tags: version_1_1_1, version_1_1_0, version_1_0_99_3, HEAD

Added CL_TYPE subroutine to retrieve user type.
User type is either 'auto' (if added via automated enrollment or via the
'Update roster now' function in lonpopulate) or '' otherwise.

1: # The LearningOnline Network with CAPA 2: # 3: # $Id: loncoursedata.pm,v 1.111 2003/12/16 16:47:16 raeburn 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/\&colon;/\&\#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->randomout); 186: # Okay, from here on out we only deal with assessments 187: $title = $curRes->title(); 188: $title =~ s/\&colon;/\&\#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 two columns. The first is a 'student_id' and the second 405: is the text description of the 'student' (typically username:domain) (less 406: than 100 characters). The 'student_id' is automatically generated by MySQL. 407: The use of the name 'student_id' is loaded, I know, but this ID is used ONLY 408: internally to the MySQL database and is not the same as the students ID 409: (stored in the students environment). This table has its PRIMARY KEY on the 410: 'student' (100 characters). 411: 412: =item $studentdata_table 413: 414: The studentdata_table has four columns: 'student_id' (the unique id of 415: the student), 'updatetime' (the time the students data was last updated), 416: 'fullupdatetime' (the time the students full data was last updated), 417: 'section', and 'classification'( the students current classification). 418: This table has its PRIMARY KEY on 'student_id'. 419: 420: =back 421: 422: =item Tables used to store current status data 423: 424: The following tables store data only about the students current status on 425: a problem, meaning only the data related to the last attempt on a problem. 426: 427: =over 4 428: 429: =item $performance_table 430: 431: The performance_table has 9 columns. The first three are 'symb_id', 432: 'student_id', and 'part_id'. These comprise the PRIMARY KEY for this table 433: and are directly related to the $symb_table, $student_table, and $part_table 434: described above. MySQL does better indexing on numeric items than text, 435: so we use these three "index tables". The remaining columns are 436: 'solved', 'tries', 'awarded', 'award', 'awarddetail', and 'timestamp'. 437: These are either the MySQL type TINYTEXT or various integers ('tries' and 438: 'timestamp'). This table has KEYs of 'student_id' and 'symb_id'. 439: For use of this table, see the functions described below. 440: 441: =item $parameters_table 442: 443: The parameters_table holds the data that does not fit neatly into the 444: performance_table. The parameters table has four columns: 'symb_id', 445: 'student_id', 'parameter', and 'value'. 'symb_id', 'student_id', and 446: 'parameter' comprise the PRIMARY KEY for this table. 'parameter' is 447: limited to 255 characters. 'value' is limited to 64k characters. 448: 449: =back 450: 451: =item Tables used for storing historic data 452: 453: The following tables are used to store almost all of the transactions a student 454: has made on a homework problem. See loncapa/docs/homework/datastorage for 455: specific information about each of the parameters stored. 456: 457: =over 4 458: 459: =item $fulldump_response_table 460: 461: The response table holds data (documented in loncapa/docs/homework/datastorage) 462: associated with a particular response id which is stored when a student 463: attempts a problem. The following are the columns of the table, in order: 464: 'symb_id','part_id','response_id','student_id','transaction','tries', 465: 'awarddetail', 'response_specific' (data particular to the response 466: type), 'response_specific_value', and 'submission (the text of the students 467: submission). The primary key is based on the first five columns listed above. 468: 469: =item $fulldump_part_table 470: 471: The part table holds data (documented in loncapa/docs/homework/datastorage) 472: associated with a particular part id which is stored when a student attempts 473: a problem. The following are the columns of the table, in order: 474: 'symb_id','part_id','student_id','transaction','tries','award','awarded', 475: and 'previous'. The primary key is based on the first five columns listed 476: above. 477: 478: =item $fulldump_timestamp_table 479: 480: The timestamp table holds the timestamps of the transactions which are 481: stored in $fulldump_response_table and $fulldump_part_table. This data is 482: about both the response and part data. Columns: 'symb_id','student_id', 483: 'transaction', and 'timestamp'. 484: The primary key is based on the first 3 columns. 485: 486: =back 487: 488: =back 489: 490: =head3 Important Subroutines 491: 492: Here is a brief overview of the subroutines which are likely to be of 493: interest: 494: 495: =over 4 496: 497: =item &get_current_state(): programmers interface. 498: 499: =item &init_dbs(): table creation 500: 501: =item &update_student_data(): data storage calls 502: 503: =item &get_student_data_from_performance_cache(): data retrieval 504: 505: =back 506: 507: =head3 Main Documentation 508: 509: =over 4 510: 511: =cut 512: 513: ################################################ 514: ################################################ 515: 516: ################################################ 517: ################################################ 518: { # Begin scope of table identifiers 519: 520: my $current_course =''; 521: my $symb_table; 522: my $part_table; 523: my $student_table; 524: my $studentdata_table; 525: my $performance_table; 526: my $parameters_table; 527: my $fulldump_response_table; 528: my $fulldump_part_table; 529: my $fulldump_timestamp_table; 530: 531: my @Tables; 532: ################################################ 533: ################################################ 534: 535: =pod 536: 537: =item &init_dbs() 538: 539: Input: course id 540: 541: Output: 0 on success, positive integer on error 542: 543: This routine issues the calls to lonmysql to create the tables used to 544: store student data. 545: 546: =cut 547: 548: ################################################ 549: ################################################ 550: sub init_dbs { 551: my $courseid = shift; 552: &setup_table_names($courseid); 553: # 554: # Drop any of the existing tables 555: foreach my $table (@Tables) { 556: &Apache::lonmysql::drop_table($table); 557: } 558: # 559: # Note - changes to this table must be reflected in the code that 560: # stores the data (calls &Apache::lonmysql::store_row with this table 561: # id 562: my $symb_table_def = { 563: id => $symb_table, 564: permanent => 'no', 565: columns => [{ name => 'symb_id', 566: type => 'MEDIUMINT UNSIGNED', 567: restrictions => 'NOT NULL', 568: auto_inc => 'yes', }, 569: { name => 'symb', 570: type => 'MEDIUMTEXT', 571: restrictions => 'NOT NULL'}, 572: ], 573: 'PRIMARY KEY' => ['symb_id'], 574: }; 575: # 576: my $part_table_def = { 577: id => $part_table, 578: permanent => 'no', 579: columns => [{ name => 'part_id', 580: type => 'MEDIUMINT UNSIGNED', 581: restrictions => 'NOT NULL', 582: auto_inc => 'yes', }, 583: { name => 'part', 584: type => 'VARCHAR(100)', 585: restrictions => 'NOT NULL'}, 586: ], 587: 'PRIMARY KEY' => ['part (100)'], 588: 'KEY' => [{ columns => ['part_id']},], 589: }; 590: # 591: my $student_table_def = { 592: id => $student_table, 593: permanent => 'no', 594: columns => [{ name => 'student_id', 595: type => 'MEDIUMINT UNSIGNED', 596: restrictions => 'NOT NULL', 597: auto_inc => 'yes', }, 598: { name => 'student', 599: type => 'VARCHAR(100)', 600: restrictions => 'NOT NULL'}, 601: { name => 'classification', 602: type => 'varchar(100)', }, 603: ], 604: 'PRIMARY KEY' => ['student (100)'], 605: 'KEY' => [{ columns => ['student_id']},], 606: }; 607: # 608: my $studentdata_table_def = { 609: id => $studentdata_table, 610: permanent => 'no', 611: columns => [{ name => 'student_id', 612: type => 'MEDIUMINT UNSIGNED', 613: restrictions => 'NOT NULL UNIQUE',}, 614: { name => 'updatetime', 615: type => 'INT UNSIGNED'}, 616: { name => 'fullupdatetime', 617: type => 'INT UNSIGNED'}, 618: { name => 'section', 619: type => 'VARCHAR(100)'}, 620: { name => 'classification', 621: type => 'VARCHAR(100)', }, 622: ], 623: 'PRIMARY KEY' => ['student_id'], 624: }; 625: # 626: my $performance_table_def = { 627: id => $performance_table, 628: permanent => 'no', 629: columns => [{ name => 'symb_id', 630: type => 'MEDIUMINT UNSIGNED', 631: restrictions => 'NOT NULL' }, 632: { name => 'student_id', 633: type => 'MEDIUMINT UNSIGNED', 634: restrictions => 'NOT NULL' }, 635: { name => 'part_id', 636: type => 'MEDIUMINT UNSIGNED', 637: restrictions => 'NOT NULL' }, 638: { name => 'part', 639: type => 'VARCHAR(100)', 640: restrictions => 'NOT NULL'}, 641: { name => 'solved', 642: type => 'TINYTEXT' }, 643: { name => 'tries', 644: type => 'SMALLINT UNSIGNED' }, 645: { name => 'awarded', 646: type => 'TINYTEXT' }, 647: { name => 'award', 648: type => 'TINYTEXT' }, 649: { name => 'awarddetail', 650: type => 'TINYTEXT' }, 651: { name => 'timestamp', 652: type => 'INT UNSIGNED'}, 653: ], 654: 'PRIMARY KEY' => ['symb_id','student_id','part_id'], 655: 'KEY' => [{ columns=>['student_id'] }, 656: { columns=>['symb_id'] },], 657: }; 658: # 659: my $fulldump_part_table_def = { 660: id => $fulldump_part_table, 661: permanent => 'no', 662: columns => [ 663: { name => 'symb_id', 664: type => 'MEDIUMINT UNSIGNED', 665: restrictions => 'NOT NULL' }, 666: { name => 'part_id', 667: type => 'MEDIUMINT UNSIGNED', 668: restrictions => 'NOT NULL' }, 669: { name => 'student_id', 670: type => 'MEDIUMINT UNSIGNED', 671: restrictions => 'NOT NULL' }, 672: { name => 'transaction', 673: type => 'MEDIUMINT UNSIGNED', 674: restrictions => 'NOT NULL' }, 675: { name => 'tries', 676: type => 'SMALLINT UNSIGNED', 677: restrictions => 'NOT NULL' }, 678: { name => 'award', 679: type => 'TINYTEXT' }, 680: { name => 'awarded', 681: type => 'TINYTEXT' }, 682: { name => 'previous', 683: type => 'SMALLINT UNSIGNED' }, 684: # { name => 'regrader', 685: # type => 'TINYTEXT' }, 686: # { name => 'afterduedate', 687: # type => 'TINYTEXT' }, 688: ], 689: 'PRIMARY KEY' => ['symb_id','part_id','student_id','transaction'], 690: 'KEY' => [ 691: { columns=>['symb_id'] }, 692: { columns=>['part_id'] }, 693: { columns=>['student_id'] }, 694: ], 695: }; 696: # 697: my $fulldump_response_table_def = { 698: id => $fulldump_response_table, 699: permanent => 'no', 700: columns => [ 701: { name => 'symb_id', 702: type => 'MEDIUMINT UNSIGNED', 703: restrictions => 'NOT NULL' }, 704: { name => 'part_id', 705: type => 'MEDIUMINT UNSIGNED', 706: restrictions => 'NOT NULL' }, 707: { name => 'response_id', 708: type => 'MEDIUMINT UNSIGNED', 709: restrictions => 'NOT NULL' }, 710: { name => 'student_id', 711: type => 'MEDIUMINT UNSIGNED', 712: restrictions => 'NOT NULL' }, 713: { name => 'transaction', 714: type => 'MEDIUMINT UNSIGNED', 715: restrictions => 'NOT NULL' }, 716: { name => 'awarddetail', 717: type => 'TINYTEXT' }, 718: # { name => 'message', 719: # type => 'CHAR' }, 720: { name => 'response_specific', 721: type => 'TINYTEXT' }, 722: { name => 'response_specific_value', 723: type => 'TINYTEXT' }, 724: { name => 'submission', 725: type => 'TEXT'}, 726: ], 727: 'PRIMARY KEY' => ['symb_id','part_id','response_id','student_id', 728: 'transaction'], 729: 'KEY' => [ 730: { columns=>['symb_id'] }, 731: { columns=>['part_id','response_id'] }, 732: { columns=>['student_id'] }, 733: ], 734: }; 735: my $fulldump_timestamp_table_def = { 736: id => $fulldump_timestamp_table, 737: permanent => 'no', 738: columns => [ 739: { name => 'symb_id', 740: type => 'MEDIUMINT UNSIGNED', 741: restrictions => 'NOT NULL' }, 742: { name => 'student_id', 743: type => 'MEDIUMINT UNSIGNED', 744: restrictions => 'NOT NULL' }, 745: { name => 'transaction', 746: type => 'MEDIUMINT UNSIGNED', 747: restrictions => 'NOT NULL' }, 748: { name => 'timestamp', 749: type => 'INT UNSIGNED'}, 750: ], 751: 'PRIMARY KEY' => ['symb_id','student_id','transaction'], 752: 'KEY' => [ 753: { columns=>['symb_id'] }, 754: { columns=>['student_id'] }, 755: { columns=>['transaction'] }, 756: ], 757: }; 758: 759: # 760: my $parameters_table_def = { 761: id => $parameters_table, 762: permanent => 'no', 763: columns => [{ name => 'symb_id', 764: type => 'MEDIUMINT UNSIGNED', 765: restrictions => 'NOT NULL' }, 766: { name => 'student_id', 767: type => 'MEDIUMINT UNSIGNED', 768: restrictions => 'NOT NULL' }, 769: { name => 'parameter', 770: type => 'TINYTEXT', 771: restrictions => 'NOT NULL' }, 772: { name => 'value', 773: type => 'MEDIUMTEXT' }, 774: ], 775: 'PRIMARY KEY' => ['symb_id','student_id','parameter (255)'], 776: }; 777: # 778: # Create the tables 779: my $tableid; 780: $tableid = &Apache::lonmysql::create_table($symb_table_def); 781: if (! defined($tableid)) { 782: &Apache::lonnet::logthis("error creating symb_table: ". 783: &Apache::lonmysql::get_error()); 784: return 1; 785: } 786: # 787: $tableid = &Apache::lonmysql::create_table($part_table_def); 788: if (! defined($tableid)) { 789: &Apache::lonnet::logthis("error creating part_table: ". 790: &Apache::lonmysql::get_error()); 791: return 2; 792: } 793: # 794: $tableid = &Apache::lonmysql::create_table($student_table_def); 795: if (! defined($tableid)) { 796: &Apache::lonnet::logthis("error creating student_table: ". 797: &Apache::lonmysql::get_error()); 798: return 3; 799: } 800: # 801: $tableid = &Apache::lonmysql::create_table($studentdata_table_def); 802: if (! defined($tableid)) { 803: &Apache::lonnet::logthis("error creating studentdata_table: ". 804: &Apache::lonmysql::get_error()); 805: return 4; 806: } 807: # 808: $tableid = &Apache::lonmysql::create_table($performance_table_def); 809: if (! defined($tableid)) { 810: &Apache::lonnet::logthis("error creating preformance_table: ". 811: &Apache::lonmysql::get_error()); 812: return 5; 813: } 814: # 815: $tableid = &Apache::lonmysql::create_table($parameters_table_def); 816: if (! defined($tableid)) { 817: &Apache::lonnet::logthis("error creating parameters_table: ". 818: &Apache::lonmysql::get_error()); 819: return 6; 820: } 821: # 822: $tableid = &Apache::lonmysql::create_table($fulldump_part_table_def); 823: if (! defined($tableid)) { 824: &Apache::lonnet::logthis("error creating fulldump_part_table: ". 825: &Apache::lonmysql::get_error()); 826: return 7; 827: } 828: # 829: $tableid = &Apache::lonmysql::create_table($fulldump_response_table_def); 830: if (! defined($tableid)) { 831: &Apache::lonnet::logthis("error creating fulldump_response_table: ". 832: &Apache::lonmysql::get_error()); 833: return 8; 834: } 835: $tableid = &Apache::lonmysql::create_table($fulldump_timestamp_table_def); 836: if (! defined($tableid)) { 837: &Apache::lonnet::logthis("error creating fulldump_timestamp_table: ". 838: &Apache::lonmysql::get_error()); 839: return 9; 840: } 841: return 0; 842: } 843: 844: ################################################ 845: ################################################ 846: 847: =pod 848: 849: =item &delete_caches() 850: 851: =cut 852: 853: ################################################ 854: ################################################ 855: sub delete_caches { 856: my $courseid = shift; 857: $courseid = $ENV{'request.course.id'} if (! defined($courseid)); 858: # 859: &setup_table_names($courseid); 860: # 861: my $dbh = &Apache::lonmysql::get_dbh(); 862: foreach my $table (@Tables) { 863: my $command = 'DROP TABLE '.$table.';'; 864: $dbh->do($command); 865: if ($dbh->err) { 866: &Apache::lonnet::logthis($command.' resulted in error: '.$dbh->errstr); 867: } 868: } 869: return; 870: } 871: 872: ################################################ 873: ################################################ 874: 875: =pod 876: 877: =item &get_part_id() 878: 879: Get the MySQL id of a problem part string. 880: 881: Input: $part 882: 883: Output: undef on error, integer $part_id on success. 884: 885: =item &get_part() 886: 887: Get the string describing a part from the MySQL id of the problem part. 888: 889: Input: $part_id 890: 891: Output: undef on error, $part string on success. 892: 893: =cut 894: 895: ################################################ 896: ################################################ 897: 898: my $have_read_part_table = 0; 899: my %ids_by_part; 900: my %parts_by_id; 901: 902: sub get_part_id { 903: my ($part) = @_; 904: $part = 0 if (! defined($part)); 905: if (! $have_read_part_table) { 906: my @Result = &Apache::lonmysql::get_rows($part_table); 907: foreach (@Result) { 908: $ids_by_part{$_->[1]}=$_->[0]; 909: } 910: $have_read_part_table = 1; 911: } 912: if (! exists($ids_by_part{$part})) { 913: &Apache::lonmysql::store_row($part_table,[undef,$part]); 914: undef(%ids_by_part); 915: my @Result = &Apache::lonmysql::get_rows($part_table); 916: foreach (@Result) { 917: $ids_by_part{$_->[1]}=$_->[0]; 918: } 919: } 920: return $ids_by_part{$part} if (exists($ids_by_part{$part})); 921: return undef; # error 922: } 923: 924: sub get_part { 925: my ($part_id) = @_; 926: if (! exists($parts_by_id{$part_id}) || 927: ! defined($parts_by_id{$part_id}) || 928: $parts_by_id{$part_id} eq '') { 929: my @Result = &Apache::lonmysql::get_rows($part_table); 930: foreach (@Result) { 931: $parts_by_id{$_->[0]}=$_->[1]; 932: } 933: } 934: return $parts_by_id{$part_id} if(exists($parts_by_id{$part_id})); 935: return undef; # error 936: } 937: 938: ################################################ 939: ################################################ 940: 941: =pod 942: 943: =item &get_symb_id() 944: 945: Get the MySQL id of a symb. 946: 947: Input: $symb 948: 949: Output: undef on error, integer $symb_id on success. 950: 951: =item &get_symb() 952: 953: Get the symb associated with a MySQL symb_id. 954: 955: Input: $symb_id 956: 957: Output: undef on error, $symb on success. 958: 959: =cut 960: 961: ################################################ 962: ################################################ 963: 964: my $have_read_symb_table = 0; 965: my %ids_by_symb; 966: my %symbs_by_id; 967: 968: sub get_symb_id { 969: my ($symb) = @_; 970: if (! $have_read_symb_table) { 971: my @Result = &Apache::lonmysql::get_rows($symb_table); 972: foreach (@Result) { 973: $ids_by_symb{$_->[1]}=$_->[0]; 974: } 975: $have_read_symb_table = 1; 976: } 977: if (! exists($ids_by_symb{$symb})) { 978: &Apache::lonmysql::store_row($symb_table,[undef,$symb]); 979: undef(%ids_by_symb); 980: my @Result = &Apache::lonmysql::get_rows($symb_table); 981: foreach (@Result) { 982: $ids_by_symb{$_->[1]}=$_->[0]; 983: } 984: } 985: return $ids_by_symb{$symb} if(exists( $ids_by_symb{$symb})); 986: return undef; # error 987: } 988: 989: sub get_symb { 990: my ($symb_id) = @_; 991: if (! exists($symbs_by_id{$symb_id}) || 992: ! defined($symbs_by_id{$symb_id}) || 993: $symbs_by_id{$symb_id} eq '') { 994: my @Result = &Apache::lonmysql::get_rows($symb_table); 995: foreach (@Result) { 996: $symbs_by_id{$_->[0]}=$_->[1]; 997: } 998: } 999: return $symbs_by_id{$symb_id} if(exists( $symbs_by_id{$symb_id})); 1000: return undef; # error 1001: } 1002: 1003: ################################################ 1004: ################################################ 1005: 1006: =pod 1007: 1008: =item &get_student_id() 1009: 1010: Get the MySQL id of a student. 1011: 1012: Input: $sname, $dom 1013: 1014: Output: undef on error, integer $student_id on success. 1015: 1016: =item &get_student() 1017: 1018: Get student username:domain associated with the MySQL student_id. 1019: 1020: Input: $student_id 1021: 1022: Output: undef on error, string $student (username:domain) on success. 1023: 1024: =cut 1025: 1026: ################################################ 1027: ################################################ 1028: 1029: my $have_read_student_table = 0; 1030: my %ids_by_student; 1031: my %students_by_id; 1032: 1033: sub get_student_id { 1034: my ($sname,$sdom) = @_; 1035: my $student = $sname.':'.$sdom; 1036: if (! $have_read_student_table) { 1037: my @Result = &Apache::lonmysql::get_rows($student_table); 1038: foreach (@Result) { 1039: $ids_by_student{$_->[1]}=$_->[0]; 1040: } 1041: $have_read_student_table = 1; 1042: } 1043: if (! exists($ids_by_student{$student})) { 1044: &Apache::lonmysql::store_row($student_table, 1045: [undef,$student,undef]); 1046: undef(%ids_by_student); 1047: my @Result = &Apache::lonmysql::get_rows($student_table); 1048: foreach (@Result) { 1049: $ids_by_student{$_->[1]}=$_->[0]; 1050: } 1051: } 1052: return $ids_by_student{$student} if(exists( $ids_by_student{$student})); 1053: return undef; # error 1054: } 1055: 1056: sub get_student { 1057: my ($student_id) = @_; 1058: if (! exists($students_by_id{$student_id}) || 1059: ! defined($students_by_id{$student_id}) || 1060: $students_by_id{$student_id} eq '') { 1061: my @Result = &Apache::lonmysql::get_rows($student_table); 1062: foreach (@Result) { 1063: $students_by_id{$_->[0]}=$_->[1]; 1064: } 1065: } 1066: return $students_by_id{$student_id} if(exists($students_by_id{$student_id})); 1067: return undef; # error 1068: } 1069: 1070: ################################################ 1071: ################################################ 1072: 1073: =pod 1074: 1075: =item &clear_internal_caches() 1076: 1077: Causes the internal caches used in get_student_id, get_student, 1078: get_symb_id, get_symb, get_part_id, and get_part to be undef'd. 1079: 1080: Needs to be called before the first operation with the MySQL database 1081: for a given Apache request. 1082: 1083: =cut 1084: 1085: ################################################ 1086: ################################################ 1087: sub clear_internal_caches { 1088: $have_read_part_table = 0; 1089: undef(%ids_by_part); 1090: undef(%parts_by_id); 1091: $have_read_symb_table = 0; 1092: undef(%ids_by_symb); 1093: undef(%symbs_by_id); 1094: $have_read_student_table = 0; 1095: undef(%ids_by_student); 1096: undef(%students_by_id); 1097: } 1098: 1099: 1100: ################################################ 1101: ################################################ 1102: 1103: =pod 1104: 1105: =item &update_full_student_data($sname,$sdom,$courseid) 1106: 1107: Does a lonnet::dump on a student to populate the courses tables. 1108: 1109: Input: $sname, $sdom, $courseid 1110: 1111: Output: $returnstatus 1112: 1113: $returnstatus is a string describing any errors that occured. 'okay' is the 1114: default. 1115: 1116: This subroutine loads a students data using lonnet::dump and inserts 1117: it into the MySQL database. The inserts are done on three tables, 1118: $fulldump_response_table, $fulldump_part_table, and $fulldump_timestamp_table. 1119: The INSERT calls are made directly by this subroutine, not through lonmysql 1120: because we do a 'bulk'insert which takes advantage of MySQLs non-SQL 1121: compliant INSERT command to insert multiple rows at a time. 1122: If anything has gone wrong during this process, $returnstatus is updated with 1123: a description of the error. 1124: 1125: Once the "fulldump" tables are updated, the tables used for chart and 1126: spreadsheet (which hold only the current state of the student on their 1127: homework, not historical data) are updated. If all updates have occured 1128: successfully, the studentdata table is updated to reflect the time of the 1129: update. 1130: 1131: Notice we do not insert the data and immediately query it. This means it 1132: is possible for there to be data returned this first time that is not 1133: available the second time. CYA. 1134: 1135: =cut 1136: 1137: ################################################ 1138: ################################################ 1139: sub update_full_student_data { 1140: my ($sname,$sdom,$courseid) = @_; 1141: # 1142: # Set up database names 1143: &setup_table_names($courseid); 1144: # 1145: my $student_id = &get_student_id($sname,$sdom); 1146: my $student = $sname.':'.$sdom; 1147: # 1148: my $returnstatus = 'okay'; 1149: # 1150: # Download students data 1151: my $time_of_retrieval = time; 1152: my @tmp = &Apache::lonnet::dump($courseid,$sdom,$sname); 1153: if (@tmp && $tmp[0] =~ /^error/) { 1154: $returnstatus = 'error retrieving full student data'; 1155: return $returnstatus; 1156: } elsif (! @tmp) { 1157: $returnstatus = 'okay: no student data'; 1158: return $returnstatus; 1159: } 1160: my %studentdata = @tmp; 1161: # 1162: # Get database handle and clean out the tables 1163: my $dbh = &Apache::lonmysql::get_dbh(); 1164: $dbh->do('DELETE FROM '.$fulldump_response_table.' WHERE student_id='. 1165: $student_id); 1166: $dbh->do('DELETE FROM '.$fulldump_part_table.' WHERE student_id='. 1167: $student_id); 1168: $dbh->do('DELETE FROM '.$fulldump_timestamp_table.' WHERE student_id='. 1169: $student_id); 1170: # 1171: # Parse and store the data into a form we can handle 1172: my $partdata; 1173: my $respdata; 1174: while (my ($key,$value) = each(%studentdata)) { 1175: next if ($key =~ /^(\d+):(resource$|subnum$|keys:)/); 1176: my ($transaction,$symb,$parameter) = split(':',$key); 1177: my $symb_id = &get_symb_id($symb); 1178: if ($parameter eq 'timestamp') { 1179: # We can deal with 'timestamp' right away 1180: my @timestamp_storage = ($symb_id,$student_id, 1181: $transaction,$value); 1182: my $store_command = 'INSERT IGNORE INTO '.$fulldump_timestamp_table. 1183: " VALUES ('".join("','",@timestamp_storage)."');"; 1184: $dbh->do($store_command); 1185: if ($dbh->err()) { 1186: &Apache::lonnet::logthis('unable to execute '.$store_command); 1187: &Apache::lonnet::logthis($dbh->errstr()); 1188: } 1189: next; 1190: } elsif ($parameter eq 'version') { 1191: next; 1192: } elsif ($parameter =~ /^resource\.(.*)\.(tries| 1193: award| 1194: awarded| 1195: previous| 1196: solved| 1197: awarddetail| 1198: submission| 1199: submissiongrading| 1200: molecule)\s*$/x){ 1201: # we do not have enough information to store an 1202: # entire row, so we save it up until later. 1203: my ($part_and_resp_id,$field) = ($1,$2); 1204: my ($part,$part_id,$resp,$resp_id); 1205: if ($part_and_resp_id =~ /\./) { 1206: ($part,$resp) = split(/\./,$part_and_resp_id); 1207: $part_id = &get_part_id($part); 1208: $resp_id = &get_part_id($resp); 1209: } else { 1210: $part_id = &get_part_id($part_and_resp_id); 1211: } 1212: # Deal with part specific data 1213: if ($field =~ /^(tries|award|awarded|previous)$/) { 1214: $partdata->{$symb_id}->{$part_id}->{$transaction}->{$field}=$value; 1215: } 1216: # deal with response specific data 1217: if (defined($resp_id) && 1218: $field =~ /^(awarddetail| 1219: submission| 1220: submissiongrading| 1221: molecule)$/x) { 1222: if ($field eq 'submission') { 1223: # We have to be careful with user supplied input. 1224: # most of the time we are okay because it is escaped. 1225: # However, there is one wrinkle: submissions which end in 1226: # and odd number of '\' cause insert errors to occur. 1227: # Best trap this somehow... 1228: $value =~ s/\'/\\\'/g; 1229: my ($offensive_string) = ($value =~ /(\\+)$/); 1230: if (length($offensive_string) % 2) { 1231: $value =~ s/\\$/\\\\/; 1232: } 1233: } 1234: if ($field eq 'submissiongrading' || 1235: $field eq 'molecule') { 1236: $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{'response_specific'}=$field; 1237: $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{'response_specific_value'}=$value; 1238: } else { 1239: $respdata->{$symb_id}->{$part_id}->{$resp_id}->{$transaction}->{$field}=$value; 1240: } 1241: } 1242: } 1243: } 1244: ## 1245: ## Store the part data 1246: my $store_command = 'INSERT IGNORE INTO '.$fulldump_part_table. 1247: ' VALUES '."\n"; 1248: my $store_rows = 0; 1249: while (my ($symb_id,$hash1) = each (%$partdata)) { 1250: while (my ($part_id,$hash2) = each (%$hash1)) { 1251: while (my ($transaction,$data) = each (%$hash2)) { 1252: $store_command .= "('".join("','",$symb_id,$part_id, 1253: $student_id, 1254: $transaction, 1255: $data->{'tries'}, 1256: $data->{'award'}, 1257: $data->{'awarded'}, 1258: $data->{'previous'})."'),"; 1259: $store_rows++; 1260: } 1261: } 1262: } 1263: if ($store_rows) { 1264: chop($store_command); 1265: $dbh->do($store_command); 1266: if ($dbh->err) { 1267: $returnstatus = 'error storing part data'; 1268: &Apache::lonnet::logthis('insert error '.$dbh->errstr()); 1269: &Apache::lonnet::logthis("While attempting\n".$store_command); 1270: } 1271: } 1272: ## 1273: ## Store the response data 1274: $store_command = 'INSERT IGNORE INTO '.$fulldump_response_table. 1275: ' VALUES '."\n"; 1276: $store_rows = 0; 1277: while (my ($symb_id,$hash1) = each (%$respdata)) { 1278: while (my ($part_id,$hash2) = each (%$hash1)) { 1279: while (my ($resp_id,$hash3) = each (%$hash2)) { 1280: while (my ($transaction,$data) = each (%$hash3)) { 1281: $store_command .= "('".join("','",$symb_id,$part_id, 1282: $resp_id,$student_id, 1283: $transaction, 1284: $data->{'awarddetail'}, 1285: $data->{'response_specific'}, 1286: $data->{'response_specific_value'}, 1287: $data->{'submission'})."'),"; 1288: $store_rows++; 1289: } 1290: } 1291: } 1292: } 1293: if ($store_rows) { 1294: chop($store_command); 1295: $dbh->do($store_command); 1296: if ($dbh->err) { 1297: $returnstatus = 'error storing response data'; 1298: &Apache::lonnet::logthis('insert error '.$dbh->errstr()); 1299: &Apache::lonnet::logthis("While attempting\n".$store_command); 1300: } 1301: } 1302: ## 1303: ## Update the students "current" data in the performance 1304: ## and parameters tables. 1305: my ($status,undef) = &store_student_data 1306: ($sname,$sdom,$courseid, 1307: &Apache::lonnet::convert_dump_to_currentdump(\%studentdata)); 1308: if ($returnstatus eq 'okay' && $status ne 'okay') { 1309: $returnstatus = 'error storing current data:'.$status; 1310: } elsif ($status ne 'okay') { 1311: $returnstatus .= ' error storing current data:'.$status; 1312: } 1313: ## 1314: ## Update the students time...... 1315: if ($returnstatus eq 'okay') { 1316: &Apache::lonmysql::replace_row 1317: ($studentdata_table, 1318: [$student_id,$time_of_retrieval,$time_of_retrieval,undef,undef]); 1319: } 1320: return $returnstatus; 1321: } 1322: 1323: ################################################ 1324: ################################################ 1325: 1326: =pod 1327: 1328: =item &update_student_data() 1329: 1330: Input: $sname, $sdom, $courseid 1331: 1332: Output: $returnstatus, \%student_data 1333: 1334: $returnstatus is a string describing any errors that occured. 'okay' is the 1335: default. 1336: \%student_data is the data returned by a call to lonnet::currentdump. 1337: 1338: This subroutine loads a students data using lonnet::currentdump and inserts 1339: it into the MySQL database. The inserts are done on two tables, 1340: $performance_table and $parameters_table. $parameters_table holds the data 1341: that is not included in $performance_table. See the description of 1342: $performance_table elsewhere in this file. The INSERT calls are made 1343: directly by this subroutine, not through lonmysql because we do a 'bulk' 1344: insert which takes advantage of MySQLs non-SQL compliant INSERT command to 1345: insert multiple rows at a time. If anything has gone wrong during this 1346: process, $returnstatus is updated with a description of the error and 1347: \%student_data is returned. 1348: 1349: Notice we do not insert the data and immediately query it. This means it 1350: is possible for there to be data returned this first time that is not 1351: available the second time. CYA. 1352: 1353: =cut 1354: 1355: ################################################ 1356: ################################################ 1357: sub update_student_data { 1358: my ($sname,$sdom,$courseid) = @_; 1359: # 1360: # Set up database names 1361: &setup_table_names($courseid); 1362: # 1363: my $student_id = &get_student_id($sname,$sdom); 1364: my $student = $sname.':'.$sdom; 1365: # 1366: my $returnstatus = 'okay'; 1367: # 1368: # Download students data 1369: my $time_of_retrieval = time; 1370: my @tmp = &Apache::lonnet::currentdump($courseid,$sdom,$sname); 1371: if ((scalar(@tmp) > 0) && ($tmp[0] =~ /^error:/)) { 1372: &Apache::lonnet::logthis('error getting data for '. 1373: $sname.':'.$sdom.' in course '.$courseid. 1374: ':'.$tmp[0]); 1375: $returnstatus = 'error getting data'; 1376: return ($returnstatus,undef); 1377: } 1378: if (scalar(@tmp) < 1) { 1379: return ('no data',undef); 1380: } 1381: my %student_data = @tmp; 1382: my @Results = &store_student_data($sname,$sdom,$courseid,\%student_data); 1383: # 1384: # Set the students update time 1385: if ($Results[0] eq 'okay') { 1386: &Apache::lonmysql::replace_row($studentdata_table, 1387: [$student_id,$time_of_retrieval,undef,undef,undef]); 1388: } 1389: # 1390: return @Results; 1391: } 1392: 1393: sub store_student_data { 1394: my ($sname,$sdom,$courseid,$student_data) = @_; 1395: # 1396: my $student_id = &get_student_id($sname,$sdom); 1397: my $student = $sname.':'.$sdom; 1398: # 1399: my $returnstatus = 'okay'; 1400: # 1401: # Remove all of the students data from the table 1402: my $dbh = &Apache::lonmysql::get_dbh(); 1403: $dbh->do('DELETE FROM '.$performance_table.' WHERE student_id='. 1404: $student_id); 1405: $dbh->do('DELETE FROM '.$parameters_table.' WHERE student_id='. 1406: $student_id); 1407: # 1408: # Store away the data 1409: # 1410: my $starttime = Time::HiRes::time; 1411: my $elapsed = 0; 1412: my $rows_stored; 1413: my $store_parameters_command = 'INSERT IGNORE INTO '.$parameters_table. 1414: ' VALUES '."\n"; 1415: my $num_parameters = 0; 1416: my $store_performance_command = 'INSERT IGNORE INTO '.$performance_table. 1417: ' VALUES '."\n"; 1418: return ('error',undef) if (! defined($dbh)); 1419: while (my ($current_symb,$param_hash) = each(%{$student_data})) { 1420: # 1421: # make sure the symb is set up properly 1422: my $symb_id = &get_symb_id($current_symb); 1423: # 1424: # Load data into the tables 1425: while (my ($parameter,$value) = each(%$param_hash)) { 1426: my $newstring; 1427: if ($parameter !~ /(timestamp|resource\.(.*)\.(solved|tries|awarded|award|awarddetail|previous))/) { 1428: $newstring = "('".join("','", 1429: $symb_id,$student_id, 1430: $parameter)."',". 1431: $dbh->quote($value)."),\n"; 1432: $num_parameters ++; 1433: if ($newstring !~ /''/) { 1434: $store_parameters_command .= $newstring; 1435: $rows_stored++; 1436: } 1437: } 1438: next if ($parameter !~ /^resource\.(.*)\.solved$/); 1439: # 1440: my $part = $1; 1441: my $part_id = &get_part_id($part); 1442: next if (!defined($part_id)); 1443: my $solved = $value; 1444: my $tries = $param_hash->{'resource.'.$part.'.tries'}; 1445: my $awarded = $param_hash->{'resource.'.$part.'.awarded'}; 1446: my $award = $param_hash->{'resource.'.$part.'.award'}; 1447: my $awarddetail = $param_hash->{'resource.'.$part.'.awarddetail'}; 1448: my $timestamp = $param_hash->{'timestamp'}; 1449: # 1450: $solved = '' if (! defined($solved)); 1451: $tries = '' if (! defined($tries)); 1452: $awarded = '' if (! defined($awarded)); 1453: $award = '' if (! defined($award)); 1454: $awarddetail = '' if (! defined($awarddetail)); 1455: $newstring = "('".join("','",$symb_id,$student_id,$part_id,$part, 1456: $solved,$tries,$awarded,$award, 1457: $awarddetail,$timestamp)."'),\n"; 1458: $store_performance_command .= $newstring; 1459: $rows_stored++; 1460: } 1461: } 1462: chop $store_parameters_command; 1463: chop $store_parameters_command; 1464: chop $store_performance_command; 1465: chop $store_performance_command; 1466: my $start = Time::HiRes::time; 1467: $dbh->do($store_performance_command); 1468: if ($dbh->err()) { 1469: &Apache::lonnet::logthis(' bigass insert error:'.$dbh->errstr()); 1470: &Apache::lonnet::logthis('command = '.$store_performance_command); 1471: $returnstatus = 'error: unable to insert performance into database'; 1472: return ($returnstatus,$student_data); 1473: } 1474: $dbh->do($store_parameters_command) if ($num_parameters>0); 1475: if ($dbh->err()) { 1476: &Apache::lonnet::logthis(' bigass insert error:'.$dbh->errstr()); 1477: &Apache::lonnet::logthis('command = '.$store_parameters_command); 1478: &Apache::lonnet::logthis('rows_stored = '.$rows_stored); 1479: &Apache::lonnet::logthis('student_id = '.$student_id); 1480: $returnstatus = 'error: unable to insert parameters into database'; 1481: return ($returnstatus,$student_data); 1482: } 1483: $elapsed += Time::HiRes::time - $start; 1484: return ($returnstatus,$student_data); 1485: } 1486: 1487: ###################################### 1488: ###################################### 1489: 1490: =pod 1491: 1492: =item &ensure_tables_are_set_up($courseid) 1493: 1494: Checks to be sure the MySQL tables for the given class are set up. 1495: If $courseid is omitted it will be obtained from the environment. 1496: 1497: Returns nothing on success and 'error' on failure 1498: 1499: =cut 1500: 1501: ###################################### 1502: ###################################### 1503: sub ensure_tables_are_set_up { 1504: my ($courseid) = @_; 1505: $courseid = $ENV{'request.course.id'} if (! defined($courseid)); 1506: # 1507: # Clean out package variables 1508: &setup_table_names($courseid); 1509: # 1510: # if the tables do not exist, make them 1511: my @CurrentTable = &Apache::lonmysql::tables_in_db(); 1512: my ($found_symb,$found_student,$found_part,$found_studentdata, 1513: $found_performance,$found_parameters,$found_fulldump_part, 1514: $found_fulldump_response,$found_fulldump_timestamp); 1515: foreach (@CurrentTable) { 1516: $found_symb = 1 if ($_ eq $symb_table); 1517: $found_student = 1 if ($_ eq $student_table); 1518: $found_part = 1 if ($_ eq $part_table); 1519: $found_studentdata = 1 if ($_ eq $studentdata_table); 1520: $found_performance = 1 if ($_ eq $performance_table); 1521: $found_parameters = 1 if ($_ eq $parameters_table); 1522: $found_fulldump_part = 1 if ($_ eq $fulldump_part_table); 1523: $found_fulldump_response = 1 if ($_ eq $fulldump_response_table); 1524: $found_fulldump_timestamp = 1 if ($_ eq $fulldump_timestamp_table); 1525: } 1526: if (!$found_symb || !$found_studentdata || 1527: !$found_student || !$found_part || 1528: !$found_performance || !$found_parameters || 1529: !$found_fulldump_part || !$found_fulldump_response || 1530: !$found_fulldump_timestamp ) { 1531: if (&init_dbs($courseid)) { 1532: return 'error'; 1533: } 1534: } 1535: } 1536: 1537: ################################################ 1538: ################################################ 1539: 1540: =pod 1541: 1542: =item &ensure_current_data() 1543: 1544: Input: $sname, $sdom, $courseid 1545: 1546: Output: $status, $data 1547: 1548: This routine ensures the data for a given student is up to date. 1549: The $studentdata_table is queried to determine the time of the last update. 1550: If the students data is out of date, &update_student_data() is called. 1551: The return values from the call to &update_student_data() are returned. 1552: 1553: =cut 1554: 1555: ################################################ 1556: ################################################ 1557: sub ensure_current_data { 1558: my ($sname,$sdom,$courseid) = @_; 1559: my $status = 'okay'; # return value 1560: # 1561: $courseid = $ENV{'request.course.id'} if (! defined($courseid)); 1562: &ensure_tables_are_set_up($courseid); 1563: # 1564: # Get the update time for the user 1565: my $updatetime = 0; 1566: my $modifiedtime = &Apache::lonnet::GetFileTimestamp 1567: ($sdom,$sname,$courseid.'.db', 1568: $Apache::lonnet::perlvar{'lonUsersDir'}); 1569: # 1570: my $student_id = &get_student_id($sname,$sdom); 1571: my @Result = &Apache::lonmysql::get_rows($studentdata_table, 1572: "student_id ='$student_id'"); 1573: my $data = undef; 1574: if (@Result) { 1575: $updatetime = $Result[0]->[1]; 1576: } 1577: if ($modifiedtime > $updatetime) { 1578: ($status,$data) = &update_student_data($sname,$sdom,$courseid); 1579: } 1580: return ($status,$data); 1581: } 1582: 1583: ################################################ 1584: ################################################ 1585: 1586: =pod 1587: 1588: =item &ensure_current_full_data($sname,$sdom,$courseid) 1589: 1590: Input: $sname, $sdom, $courseid 1591: 1592: Output: $status 1593: 1594: This routine ensures the fulldata (the data from a lonnet::dump, not a 1595: lonnet::currentdump) for a given student is up to date. 1596: The $studentdata_table is queried to determine the time of the last update. 1597: If the students fulldata is out of date, &update_full_student_data() is 1598: called. 1599: 1600: The return value from the call to &update_full_student_data() is returned. 1601: 1602: =cut 1603: 1604: ################################################ 1605: ################################################ 1606: sub ensure_current_full_data { 1607: my ($sname,$sdom,$courseid) = @_; 1608: my $status = 'okay'; # return value 1609: # 1610: $courseid = $ENV{'request.course.id'} if (! defined($courseid)); 1611: &ensure_tables_are_set_up($courseid); 1612: # 1613: # Get the update time for the user 1614: my $modifiedtime = &Apache::lonnet::GetFileTimestamp 1615: ($sdom,$sname,$courseid.'.db', 1616: $Apache::lonnet::perlvar{'lonUsersDir'}); 1617: # 1618: my $student_id = &get_student_id($sname,$sdom); 1619: my @Result = &Apache::lonmysql::get_rows($studentdata_table, 1620: "student_id ='$student_id'"); 1621: my $updatetime; 1622: if (@Result && ref($Result[0]) eq 'ARRAY') { 1623: $updatetime = $Result[0]->[2]; 1624: } 1625: if (! defined($updatetime) || $modifiedtime > $updatetime) { 1626: $status = &update_full_student_data($sname,$sdom,$courseid); 1627: } 1628: return $status; 1629: } 1630: 1631: ################################################ 1632: ################################################ 1633: 1634: =pod 1635: 1636: =item &get_student_data_from_performance_cache() 1637: 1638: Input: $sname, $sdom, $symb, $courseid 1639: 1640: Output: hash reference containing the data for the given student. 1641: If $symb is undef, all the students data is returned. 1642: 1643: This routine is the heart of the local caching system. See the description 1644: of $performance_table, $symb_table, $student_table, and $part_table. The 1645: main task is building the MySQL request. The tables appear in the request 1646: in the order in which they should be parsed by MySQL. When searching 1647: on a student the $student_table is used to locate the 'student_id'. All 1648: rows in $performance_table which have a matching 'student_id' are returned, 1649: with data from $part_table and $symb_table which match the entries in 1650: $performance_table, 'part_id' and 'symb_id'. When searching on a symb, 1651: the $symb_table is processed first, with matching rows grabbed from 1652: $performance_table and filled in from $part_table and $student_table in 1653: that order. 1654: 1655: Running 'EXPLAIN ' on the 'SELECT' statements generated can be quite 1656: interesting, especially if you play with the order the tables are listed. 1657: 1658: =cut 1659: 1660: ################################################ 1661: ################################################ 1662: sub get_student_data_from_performance_cache { 1663: my ($sname,$sdom,$symb,$courseid)=@_; 1664: my $student = $sname.':'.$sdom if (defined($sname) && defined($sdom)); 1665: &setup_table_names($courseid); 1666: # 1667: # Return hash 1668: my $studentdata; 1669: # 1670: my $dbh = &Apache::lonmysql::get_dbh(); 1671: my $request = "SELECT ". 1672: "d.symb,a.part,a.solved,a.tries,a.awarded,a.award,a.awarddetail,". 1673: "a.timestamp "; 1674: if (defined($student)) { 1675: $request .= "FROM $student_table AS b ". 1676: "LEFT JOIN $performance_table AS a ON b.student_id=a.student_id ". 1677: # "LEFT JOIN $part_table AS c ON c.part_id = a.part_id ". 1678: "LEFT JOIN $symb_table AS d ON d.symb_id = a.symb_id ". 1679: "WHERE student='$student'"; 1680: if (defined($symb) && $symb ne '') { 1681: $request .= " AND d.symb=".$dbh->quote($symb); 1682: } 1683: } elsif (defined($symb) && $symb ne '') { 1684: $request .= "FROM $symb_table as d ". 1685: "LEFT JOIN $performance_table AS a ON d.symb_id=a.symb_id ". 1686: # "LEFT JOIN $part_table AS c ON c.part_id = a.part_id ". 1687: "LEFT JOIN $student_table AS b ON b.student_id = a.student_id ". 1688: "WHERE symb='".$dbh->quote($symb)."'"; 1689: } 1690: my $starttime = Time::HiRes::time; 1691: my $rows_retrieved = 0; 1692: my $sth = $dbh->prepare($request); 1693: $sth->execute(); 1694: if ($sth->err()) { 1695: &Apache::lonnet::logthis("Unable to execute MySQL request:"); 1696: &Apache::lonnet::logthis("\n".$request."\n"); 1697: &Apache::lonnet::logthis("error is:".$sth->errstr()); 1698: return undef; 1699: } 1700: foreach my $row (@{$sth->fetchall_arrayref}) { 1701: $rows_retrieved++; 1702: my ($symb,$part,$solved,$tries,$awarded,$award,$awarddetail,$time) = 1703: (@$row); 1704: my $base = 'resource.'.$part; 1705: $studentdata->{$symb}->{$base.'.solved'} = $solved; 1706: $studentdata->{$symb}->{$base.'.tries'} = $tries; 1707: $studentdata->{$symb}->{$base.'.awarded'} = $awarded; 1708: $studentdata->{$symb}->{$base.'.award'} = $award; 1709: $studentdata->{$symb}->{$base.'.awarddetail'} = $awarddetail; 1710: $studentdata->{$symb}->{'timestamp'} = $time if (defined($time) && $time ne ''); 1711: } 1712: ## Get misc parameters 1713: $request = 'SELECT c.symb,a.parameter,a.value '. 1714: "FROM $student_table AS b ". 1715: "LEFT JOIN $parameters_table AS a ON b.student_id=a.student_id ". 1716: "LEFT JOIN $symb_table AS c ON c.symb_id = a.symb_id ". 1717: "WHERE student='$student'"; 1718: if (defined($symb) && $symb ne '') { 1719: $request .= " AND c.symb=".$dbh->quote($symb); 1720: } 1721: $sth = $dbh->prepare($request); 1722: $sth->execute(); 1723: if ($sth->err()) { 1724: &Apache::lonnet::logthis("Unable to execute MySQL request:"); 1725: &Apache::lonnet::logthis("\n".$request."\n"); 1726: &Apache::lonnet::logthis("error is:".$sth->errstr()); 1727: if (defined($symb) && $symb ne '') { 1728: $studentdata = $studentdata->{$symb}; 1729: } 1730: return $studentdata; 1731: } 1732: # 1733: foreach my $row (@{$sth->fetchall_arrayref}) { 1734: $rows_retrieved++; 1735: my ($symb,$parameter,$value) = (@$row); 1736: $studentdata->{$symb}->{$parameter} = $value; 1737: } 1738: # 1739: if (defined($symb) && $symb ne '') { 1740: $studentdata = $studentdata->{$symb}; 1741: } 1742: return $studentdata; 1743: } 1744: 1745: ################################################ 1746: ################################################ 1747: 1748: =pod 1749: 1750: =item &get_current_state() 1751: 1752: Input: $sname,$sdom,$symb,$courseid 1753: 1754: Output: Described below 1755: 1756: Retrieve the current status of a students performance. $sname and 1757: $sdom are the only required parameters. If $symb is undef the results 1758: of an &Apache::lonnet::currentdump() will be returned. 1759: If $courseid is undef it will be retrieved from the environment. 1760: 1761: The return structure is based on &Apache::lonnet::currentdump. If 1762: $symb is unspecified, all the students data is returned in a hash of 1763: the form: 1764: ( 1765: symb1 => { param1 => value1, param2 => value2 ... }, 1766: symb2 => { param1 => value1, param2 => value2 ... }, 1767: ) 1768: 1769: If $symb is specified, a hash of 1770: ( 1771: param1 => value1, 1772: param2 => value2, 1773: ) 1774: is returned. 1775: 1776: If no data is found for $symb, or if the student has no performance data, 1777: an empty list is returned. 1778: 1779: =cut 1780: 1781: ################################################ 1782: ################################################ 1783: sub get_current_state { 1784: my ($sname,$sdom,$symb,$courseid,$forcedownload)=@_; 1785: # 1786: $courseid = $ENV{'request.course.id'} if (! defined($courseid)); 1787: # 1788: return () if (! defined($sname) || ! defined($sdom)); 1789: # 1790: my ($status,$data) = &ensure_current_data($sname,$sdom,$courseid); 1791: # &Apache::lonnet::logthis 1792: # ('sname = '.$sname. 1793: # ' domain = '.$sdom. 1794: # ' status = '.$status. 1795: # ' data is '.(defined($data)?'defined':'undefined')); 1796: # while (my ($symb,$hash) = each(%$data)) { 1797: # &Apache::lonnet::logthis($symb."\n----------------------------------"); 1798: # while (my ($key,$value) = each (%$hash)) { 1799: # &Apache::lonnet::logthis(" ".$key." = ".$value); 1800: # } 1801: # } 1802: # 1803: if (defined($data) && defined($symb) && ref($data->{$symb})) { 1804: return %{$data->{$symb}}; 1805: } elsif (defined($data) && ! defined($symb) && ref($data)) { 1806: return %$data; 1807: } 1808: if ($status eq 'no data') { 1809: return (); 1810: } else { 1811: if ($status ne 'okay' && $status ne '') { 1812: &Apache::lonnet::logthis('status = '.$status); 1813: return (); 1814: } 1815: my $returnhash = &get_student_data_from_performance_cache($sname,$sdom, 1816: $symb,$courseid); 1817: return %$returnhash if (defined($returnhash)); 1818: } 1819: return (); 1820: } 1821: 1822: ################################################ 1823: ################################################ 1824: 1825: =pod 1826: 1827: =item &get_problem_statistics() 1828: 1829: Gather data on a given problem. The database is assumed to be 1830: populated and all local caching variables are assumed to be set 1831: properly. This means you need to call &ensure_current_data for 1832: the students you are concerned with prior to calling this routine. 1833: 1834: Inputs: $students, $symb, $part, $courseid 1835: 1836: =over 4 1837: 1838: =item $students is an array of hash references. 1839: Each hash must contain at least the 'username' and 'domain' of a student. 1840: 1841: =item $symb is the symb for the problem. 1842: 1843: =item $part is the part id you need statistics for 1844: 1845: =item $courseid is the course id, of course! 1846: 1847: =back 1848: 1849: Outputs: See the code for up to date information. A hash reference is 1850: returned. The hash has the following keys defined: 1851: 1852: =over 4 1853: 1854: =item num_students The number of students attempting the problem 1855: 1856: =item tries The total number of tries for the students 1857: 1858: =item max_tries The maximum number of tries taken 1859: 1860: =item mean_tries The average number of tries 1861: 1862: =item num_solved The number of students able to solve the problem 1863: 1864: =item num_override The number of students whose answer is 'correct_by_override' 1865: 1866: =item deg_of_diff The degree of difficulty of the problem 1867: 1868: =item std_tries The standard deviation of the number of tries 1869: 1870: =item skew_tries The skew of the number of tries 1871: 1872: =item per_wrong The number of students attempting the problem who were not 1873: able to answer it correctly. 1874: 1875: =back 1876: 1877: =cut 1878: 1879: ################################################ 1880: ################################################ 1881: sub get_problem_statistics { 1882: my ($students,$symb,$part,$courseid) = @_; 1883: return if (! defined($symb) || ! defined($part)); 1884: $courseid = $ENV{'request.course.id'} if (! defined($courseid)); 1885: # 1886: &setup_table_names($courseid); 1887: my $symb_id = &get_symb_id($symb); 1888: my $part_id = &get_part_id($part); 1889: my $stats_table = $courseid.'_problem_stats'; 1890: # 1891: my $dbh = &Apache::lonmysql::get_dbh(); 1892: return undef if (! defined($dbh)); 1893: # 1894: $dbh->do('DROP TABLE '.$stats_table); # May return an error 1895: my $request = 1896: 'CREATE TEMPORARY TABLE '.$stats_table. 1897: ' SELECT student_id,solved,award,awarded,tries FROM '.$performance_table. 1898: ' WHERE symb_id='.$symb_id.' AND part_id='.$part_id; 1899: if (defined($students)) { 1900: $request .= ' AND ('. 1901: join(' OR ', map {'student_id='. 1902: &get_student_id($_->{'username'}, 1903: $_->{'domain'}) 1904: } @$students 1905: ).')'; 1906: } 1907: # &Apache::lonnet::logthis($request); 1908: $dbh->do($request); 1909: # &Apache::lonnet::logthis('request = '.$/.$request); 1910: $request = 'SELECT COUNT(*),SUM(tries),MAX(tries),AVG(tries),STD(tries) '. 1911: 'FROM '.$stats_table; 1912: my ($num,$tries,$mod,$mean,$STD) = &execute_SQL_request 1913: ($dbh,$request); 1914: # &Apache::lonnet::logthis('request = '.$/.$request); 1915: $request = 'SELECT SUM(awarded) FROM '.$stats_table; 1916: my ($Solved) = &execute_SQL_request($dbh,$request); 1917: # &Apache::lonnet::logthis('request = '.$/.$request); 1918: $request = 'SELECT SUM(awarded) FROM '.$stats_table. 1919: " WHERE solved='correct_by_override'"; 1920: # &Apache::lonnet::logthis('request = '.$/.$request); 1921: my ($solved) = &execute_SQL_request($dbh,$request); 1922: # $Solved = int($Solved); 1923: # $solved = int($solved); 1924: # 1925: $num = 0 if (! defined($num)); 1926: $tries = 0 if (! defined($tries)); 1927: $mod = 0 if (! defined($mod)); 1928: $STD = 0 if (! defined($STD)); 1929: $Solved = 0 if (! defined($Solved)); 1930: $solved = 0 if (! defined($solved)); 1931: # 1932: my $DegOfDiff = 'nan'; 1933: $DegOfDiff = 1-($Solved)/$tries if ($tries>0); 1934: 1935: my $SKEW = 'nan'; 1936: my $wrongpercent = 0; 1937: if ($num > 0) { 1938: ($SKEW) = &execute_SQL_request($dbh,'SELECT SQRT(SUM('. 1939: 'POWER(tries - '.$STD.',3)'. 1940: '))/'.$num.' FROM '.$stats_table); 1941: $wrongpercent=int(10*100*($num-$Solved+$solved)/$num)/10; 1942: } 1943: # 1944: # $dbh->do('DROP TABLE '.$stats_table); # May return an error 1945: # 1946: # Store in metadata 1947: # 1948: if ($num) { 1949: my %storestats=(); 1950: 1951: my $urlres=(&Apache::lonnet::decode_symb($symb))[2]; 1952: 1953: $storestats{$courseid.'___'.$urlres.'___timestamp'}=time; 1954: $storestats{$courseid.'___'.$urlres.'___stdno'}=$num; 1955: $storestats{$courseid.'___'.$urlres.'___avetries'}=$mean; 1956: $storestats{$courseid.'___'.$urlres.'___difficulty'}=$DegOfDiff; 1957: 1958: $urlres=~/^(\w+)\/(\w+)/; 1959: &Apache::lonnet::put('nohist_resevaldata',\%storestats,$1,$2); 1960: } 1961: # 1962: # Return result 1963: # 1964: return { num_students => $num, 1965: tries => $tries, 1966: max_tries => $mod, 1967: mean_tries => $mean, 1968: std_tries => $STD, 1969: skew_tries => $SKEW, 1970: num_solved => $Solved, 1971: num_override => $solved, 1972: per_wrong => $wrongpercent, 1973: deg_of_diff => $DegOfDiff }; 1974: } 1975: 1976: sub execute_SQL_request { 1977: my ($dbh,$request)=@_; 1978: # &Apache::lonnet::logthis($request); 1979: my $sth = $dbh->prepare($request); 1980: $sth->execute(); 1981: my $row = $sth->fetchrow_arrayref(); 1982: if (ref($row) eq 'ARRAY' && scalar(@$row)>0) { 1983: return @$row; 1984: } 1985: return (); 1986: } 1987: 1988: sub get_student_data { 1989: my ($students,$courseid) = @_; 1990: $courseid = $ENV{'request.course.id'} if (! defined($courseid)); 1991: &setup_table_names($courseid); 1992: my $dbh = &Apache::lonmysql::get_dbh(); 1993: return undef if (! defined($dbh)); 1994: my $request = 'SELECT '. 1995: 'student_id, student '. 1996: 'FROM '.$student_table; 1997: if (defined($students)) { 1998: $request .= ' WHERE ('. 1999: join(' OR ', map {'student_id='. 2000: &get_student_id($_->{'username'}, 2001: $_->{'domain'}) 2002: } @$students 2003: ).')'; 2004: } 2005: $request.= ' ORDER BY student_id'; 2006: my $sth = $dbh->prepare($request); 2007: $sth->execute(); 2008: if ($dbh->err) { 2009: &Apache::lonnet::logthis('error = '.$dbh->errstr()); 2010: return undef; 2011: } 2012: my $dataset = $sth->fetchall_arrayref(); 2013: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) { 2014: return $dataset; 2015: } 2016: } 2017: 2018: sub RD_student_id { return 0; } 2019: sub RD_awarddetail { return 1; } 2020: sub RD_response_eval { return 2; } 2021: sub RD_submission { return 3; } 2022: sub RD_timestamp { return 4; } 2023: sub RD_tries { return 5; } 2024: sub RD_sname { return 6; } 2025: 2026: sub get_response_data { 2027: my ($students,$symb,$response,$courseid) = @_; 2028: return undef if (! defined($symb) || 2029: ! defined($response)); 2030: $courseid = $ENV{'request.course.id'} if (! defined($courseid)); 2031: # 2032: &setup_table_names($courseid); 2033: my $symb_id = &get_symb_id($symb); 2034: my $response_id = &get_part_id($response); 2035: # 2036: my $dbh = &Apache::lonmysql::get_dbh(); 2037: return undef if (! defined($dbh)); 2038: my $request = 'SELECT '. 2039: 'a.student_id, a.awarddetail, a.response_specific_value, '. 2040: 'a.submission, b.timestamp, c.tries, d.student '. 2041: 'FROM '.$fulldump_response_table.' AS a '. 2042: 'LEFT JOIN '.$fulldump_timestamp_table.' AS b '. 2043: 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '. 2044: 'a.transaction = b.transaction '. 2045: 'LEFT JOIN '.$fulldump_part_table.' AS c '. 2046: 'ON a.symb_id=c.symb_id AND a.student_id=c.student_id AND '. 2047: 'a.part_id=c.part_id AND a.transaction = c.transaction '. 2048: 'LEFT JOIN '.$student_table.' AS d '. 2049: 'ON a.student_id=d.student_id '. 2050: 'WHERE '. 2051: 'a.symb_id='.$symb_id.' AND a.response_id='.$response_id; 2052: if (defined($students)) { 2053: $request .= ' AND ('. 2054: join(' OR ', map {'a.student_id='. 2055: &get_student_id($_->{'username'}, 2056: $_->{'domain'}) 2057: } @$students 2058: ).')'; 2059: } 2060: $request .= ' ORDER BY b.timestamp'; 2061: # &Apache::lonnet::logthis("request =\n".$request); 2062: my $sth = $dbh->prepare($request); 2063: $sth->execute(); 2064: if ($dbh->err) { 2065: &Apache::lonnet::logthis('error = '.$dbh->errstr()); 2066: return undef; 2067: } 2068: my $dataset = $sth->fetchall_arrayref(); 2069: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) { 2070: return $dataset; 2071: } 2072: } 2073: 2074: sub RT_student_id { return 0; } 2075: sub RT_awarded { return 1; } 2076: sub RT_tries { return 2; } 2077: sub RT_timestamp { return 3; } 2078: 2079: sub get_response_time_data { 2080: my ($students,$symb,$part,$courseid) = @_; 2081: return undef if (! defined($symb) || 2082: ! defined($part)); 2083: $courseid = $ENV{'request.course.id'} if (! defined($courseid)); 2084: # 2085: &setup_table_names($courseid); 2086: my $symb_id = &get_symb_id($symb); 2087: my $part_id = &get_part_id($part); 2088: # 2089: my $dbh = &Apache::lonmysql::get_dbh(); 2090: return undef if (! defined($dbh)); 2091: my $request = 'SELECT '. 2092: 'a.student_id, a.awarded, a.tries, b.timestamp '. 2093: 'FROM '.$fulldump_part_table.' AS a '. 2094: 'NATURAL LEFT JOIN '.$fulldump_timestamp_table.' AS b '. 2095: # 'ON a.symb_id=b.symb_id AND a.student_id=b.student_id AND '. 2096: # 'a.transaction = b.transaction '. 2097: 'WHERE '. 2098: 'a.symb_id='.$symb_id.' AND a.part_id='.$part_id; 2099: if (defined($students)) { 2100: $request .= ' AND ('. 2101: join(' OR ', map {'a.student_id='. 2102: &get_student_id($_->{'username'}, 2103: $_->{'domain'}) 2104: } @$students 2105: ).')'; 2106: } 2107: $request .= ' ORDER BY b.timestamp'; 2108: # &Apache::lonnet::logthis("request =\n".$request); 2109: my $sth = $dbh->prepare($request); 2110: $sth->execute(); 2111: if ($dbh->err) { 2112: &Apache::lonnet::logthis('error = '.$dbh->errstr()); 2113: return undef; 2114: } 2115: my $dataset = $sth->fetchall_arrayref(); 2116: if (ref($dataset) eq 'ARRAY' && scalar(@$dataset)>0) { 2117: return $dataset; 2118: } 2119: 2120: } 2121: 2122: ################################################ 2123: ################################################ 2124: 2125: =pod 2126: 2127: =item &setup_table_names() 2128: 2129: input: course id 2130: 2131: output: none 2132: 2133: Cleans up the package variables for local caching. 2134: 2135: =cut 2136: 2137: ################################################ 2138: ################################################ 2139: sub setup_table_names { 2140: my ($courseid) = @_; 2141: if (! defined($courseid)) { 2142: $courseid = $ENV{'request.course.id'}; 2143: } 2144: # 2145: if (! defined($current_course) || $current_course ne $courseid) { 2146: # Clear out variables 2147: $have_read_part_table = 0; 2148: undef(%ids_by_part); 2149: undef(%parts_by_id); 2150: $have_read_symb_table = 0; 2151: undef(%ids_by_symb); 2152: undef(%symbs_by_id); 2153: $have_read_student_table = 0; 2154: undef(%ids_by_student); 2155: undef(%students_by_id); 2156: # 2157: $current_course = $courseid; 2158: } 2159: # 2160: # Set up database names 2161: my $base_id = $courseid; 2162: $symb_table = $base_id.'_'.'symb'; 2163: $part_table = $base_id.'_'.'part'; 2164: $student_table = $base_id.'_'.'student'; 2165: $studentdata_table = $base_id.'_'.'studentdata'; 2166: $performance_table = $base_id.'_'.'performance'; 2167: $parameters_table = $base_id.'_'.'parameters'; 2168: $fulldump_part_table = $base_id.'_'.'partdata'; 2169: $fulldump_response_table = $base_id.'_'.'responsedata'; 2170: $fulldump_timestamp_table = $base_id.'_'.'timestampdata'; 2171: # 2172: @Tables = ( 2173: $symb_table, 2174: $part_table, 2175: $student_table, 2176: $studentdata_table, 2177: $performance_table, 2178: $parameters_table, 2179: $fulldump_part_table, 2180: $fulldump_response_table, 2181: $fulldump_timestamp_table, 2182: ); 2183: return; 2184: } 2185: 2186: ################################################ 2187: ################################################ 2188: 2189: =pod 2190: 2191: =back 2192: 2193: =item End of Local Data Caching Subroutines 2194: 2195: =cut 2196: 2197: ################################################ 2198: ################################################ 2199: 2200: } # End scope of table identifiers 2201: 2202: ################################################ 2203: ################################################ 2204: 2205: =pod 2206: 2207: =head3 Classlist Subroutines 2208: 2209: =item &get_classlist(); 2210: 2211: Retrieve the classist of a given class or of the current class. Student 2212: information is returned from the classlist.db file and, if needed, 2213: from the students environment. 2214: 2215: Optional arguments are $cid, $cdom, and $cnum (course id, course domain, 2216: and course number, respectively). Any omitted arguments will be taken 2217: from the current environment ($ENV{'request.course.id'}, 2218: $ENV{'course.'.$cid.'.domain'}, and $ENV{'course.'.$cid.'.num'}). 2219: 2220: Returns a reference to a hash which contains: 2221: keys '$sname:$sdom' 2222: values [$sdom,$sname,$end,$start,$id,$section,$fullname,$status,$type] 2223: 2224: The constant values CL_SDOM, CL_SNAME, CL_END, etc. can be used 2225: as indices into the returned list to future-proof clients against 2226: changes in the list order. 2227: 2228: =cut 2229: 2230: ################################################ 2231: ################################################ 2232: 2233: sub CL_SDOM { return 0; } 2234: sub CL_SNAME { return 1; } 2235: sub CL_END { return 2; } 2236: sub CL_START { return 3; } 2237: sub CL_ID { return 4; } 2238: sub CL_SECTION { return 5; } 2239: sub CL_FULLNAME { return 6; } 2240: sub CL_STATUS { return 7; } 2241: sub CL_TYPE { return 8; } 2242: 2243: sub get_classlist { 2244: my ($cid,$cdom,$cnum) = @_; 2245: $cid = $cid || $ENV{'request.course.id'}; 2246: $cdom = $cdom || $ENV{'course.'.$cid.'.domain'}; 2247: $cnum = $cnum || $ENV{'course.'.$cid.'.num'}; 2248: my $now = time; 2249: # 2250: my %classlist=&Apache::lonnet::dump('classlist',$cdom,$cnum); 2251: while (my ($student,$info) = each(%classlist)) { 2252: if ($student =~ /^(con_lost|error|no_such_host)/i) { 2253: &Apache::lonnet::logthis('get_classlist error for '.$cid.':'.$student); 2254: return undef; 2255: } 2256: my ($sname,$sdom) = split(/:/,$student); 2257: my @Values = split(/:/,$info); 2258: my ($end,$start,$id,$section,$fullname,$type); 2259: if (@Values > 2) { 2260: ($end,$start,$id,$section,$fullname,$type) = @Values; 2261: } else { # We have to get the data ourselves 2262: ($end,$start) = @Values; 2263: $section = &Apache::lonnet::getsection($sdom,$sname,$cid); 2264: my %info=&Apache::lonnet::get('environment', 2265: ['firstname','middlename', 2266: 'lastname','generation','id'], 2267: $sdom, $sname); 2268: my ($tmp) = keys(%info); 2269: if ($tmp =~/^(con_lost|error|no_such_host)/i) { 2270: $fullname = 'not available'; 2271: $id = 'not available'; 2272: &Apache::lonnet::logthis('unable to retrieve environment '. 2273: 'for '.$sname.':'.$sdom); 2274: } else { 2275: $fullname = &ProcessFullName(@info{qw/lastname generation 2276: firstname middlename/}); 2277: $id = $info{'id'}; 2278: } 2279: # Update the classlist with this students information 2280: if ($fullname ne 'not available') { 2281: my $enrolldata = join(':',$end,$start,$id,$section,$fullname); 2282: my $reply=&Apache::lonnet::cput('classlist', 2283: {$student => $enrolldata}, 2284: $cdom,$cnum); 2285: if ($reply !~ /^(ok|delayed)/) { 2286: &Apache::lonnet::logthis('Unable to update classlist for '. 2287: 'student '.$sname.':'.$sdom. 2288: ' error:'.$reply); 2289: } 2290: } 2291: } 2292: my $status='Expired'; 2293: if(((!$end) || $now < $end) && ((!$start) || ($now > $start))) { 2294: $status='Active'; 2295: } 2296: $classlist{$student} = 2297: [$sdom,$sname,$end,$start,$id,$section,$fullname,$status,$type]; 2298: } 2299: if (wantarray()) { 2300: return (\%classlist,['domain','username','end','start','id', 2301: 'section','fullname','status','type']); 2302: } else { 2303: return \%classlist; 2304: } 2305: } 2306: 2307: # ----- END HELPER FUNCTIONS -------------------------------------------- 2308: 2309: 1; 2310: __END__ 2311: 2312: