Annotation of loncom/metadata_database/parse_activity_log.pl, revision 1.18
1.1 matthew 1: #!/usr/bin/perl
2: #
3: # The LearningOnline Network
4: #
1.18 ! matthew 5: # $Id: parse_activity_log.pl,v 1.17 2005/09/19 20:30:37 albertel Exp $
1.1 matthew 6: #
7: # Copyright Michigan State University Board of Trustees
8: #
9: # This file is part of the LearningOnline Network with CAPA (LON-CAPA).
10: #
11: # LON-CAPA is free software; you can redistribute it and/or modify
12: # it under the terms of the GNU General Public License as published by
13: # the Free Software Foundation; either version 2 of the License, or
14: # (at your option) any later version.
15: #
16: # LON-CAPA is distributed in the hope that it will be useful,
17: # but WITHOUT ANY WARRANTY; without even the implied warranty of
18: # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19: # GNU General Public License for more details.
20: #
21: # You should have received a copy of the GNU General Public License
22: # along with LON-CAPA; if not, write to the Free Software
23: # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
24: #
25: # /home/httpd/html/adm/gpl.txt
26: #
27: # http://www.lon-capa.org/
28: #
1.3 matthew 29: #--------------------------------------------------------------------
1.1 matthew 30: #
31: # Exit codes
32: # 0 Everything is okay
33: # 1 Another copy is running on this course
34: # 2 Activity log does not exist
35: # 3 Unable to connect to database
36: # 4 Unable to create database tables
1.9 matthew 37: # 5 Unable to open log file
38: # 6 Unable to get lock on activity log
1.1 matthew 39: #
40:
1.8 matthew 41: #
42: # Notes:
43: #
44: # Logging is done via the $logthis variable, which may be the result of
45: # overcleverness. log via $logthis->('logtext'); Those are parentheses,
46: # not curly braces. If the -log command line parameter is set, the $logthis
47: # routine is set to a routine which writes to a file. If the command line
48: # parameter is not set $logthis is set to ¬hing, which does what you
49: # would expect.
50: #
51:
1.1 matthew 52: use strict;
53: use DBI;
1.9 matthew 54: use lib '/home/httpd/lib/perl/Apache';
1.8 matthew 55: use lib '/home/httpd/lib/perl/';
56: use LONCAPA::Configuration();
57: use Apache::lonmysql();
1.1 matthew 58: use lonmysql();
59: use Time::HiRes();
60: use Getopt::Long();
1.3 matthew 61: use IO::File;
1.5 matthew 62: use File::Copy;
1.7 matthew 63: use Fcntl qw(:flock);
1.14 matthew 64: use HTML::TokeParser;
1.7 matthew 65:
1.1 matthew 66: #
67: # Determine parameters
1.15 matthew 68: my ($help,$course,$domain,$drop_when_done,$srcfile,$logfile,$time_run,$nocleanup,$log,$backup,$xmlfile);
1.1 matthew 69: &Getopt::Long::GetOptions( "course=s" => \$course,
70: "domain=s" => \$domain,
1.5 matthew 71: "backup" => \$backup,
1.1 matthew 72: "help" => \$help,
1.12 matthew 73: "logfile=s" => \$logfile,
74: "srcfile=s" => \$srcfile,
1.15 matthew 75: "justloadxml=s" => \$xmlfile,
1.1 matthew 76: "timerun" => \$time_run,
77: "nocleanup" => \$nocleanup,
1.12 matthew 78: "dropwhendone" => \$drop_when_done,
1.2 matthew 79: "log" => \$log);
1.1 matthew 80: if (! defined($course) || $help) {
81: print<<USAGE;
82: parse_activity_log.pl
83:
84: Process a lon-capa activity log into a database.
85: Parameters:
86: course Required
1.12 matthew 87: domain optional
1.5 matthew 88: backup optional if present, backup the activity log file
89: before processing it
1.12 matthew 90: dropwhendone optional if present, drop all course
91: specific activity log tables after processing.
92: srcfile optional Specify the file to parse, including path
1.1 matthew 93: time optional if present, print out timing data
94: nocleanup optional if present, do not remove old files
1.2 matthew 95: log optional if present, prepare log file of activity
1.12 matthew 96: logfile optional specifies the logfile to use
1.1 matthew 97: Examples:
98: $0 -course=123456abcdef -domain=msu
1.12 matthew 99: $0 -course=123456abcdef -srcfile=activity.log
100: $0 -course-123456abcdef -log -logfile=/tmp/logfile -dropwhendone
1.1 matthew 101: USAGE
102: exit;
103: }
104:
105: ##
106: ## Set up timing code
107: my $time_this = \¬hing;
108: if ($time_run) {
109: $time_this = \&time_action;
110: }
111: my $initial_time = Time::HiRes::time;
112:
113: ##
1.3 matthew 114: ## Read in configuration parameters
115: ##
1.8 matthew 116: my %perlvar = %{&LONCAPA::Configuration::read_conf('loncapa.conf')};
117:
1.3 matthew 118: if (! defined($domain) || $domain eq '') {
119: $domain = $perlvar{'lonDefDomain'};
120: }
121: &update_process_name($course.'@'.$domain);
122:
123: ##
1.2 matthew 124: ## Set up logging code
125: my $logthis = \¬hing;
1.12 matthew 126:
1.2 matthew 127: if ($log) {
1.12 matthew 128: if (! $logfile) {
129: $logfile = $perlvar{'lonDaemons'}.'/tmp/parse_activity_log.log.'.time;
130: }
1.2 matthew 131: print STDERR "$0: logging to $logfile".$/;
132: if (! open(LOGFILE,">$logfile")) {
1.9 matthew 133: warn("Unable to open $logfile for writing. Run aborted.");
1.17 albertel 134: &clean_up_and_exit(5);
1.2 matthew 135: } else {
136: $logthis = \&log_to_file;
137: }
138: }
1.3 matthew 139:
1.1 matthew 140:
141: ##
142: ## Determine filenames
143: ##
144: my $sourcefilename; # activity log data
145: my $newfilename; # $sourcefilename will be renamed to this
1.3 matthew 146: my $error_filename; # Errors in parsing the activity log will be written here
1.12 matthew 147: if ($srcfile) {
148: $sourcefilename = $srcfile;
1.1 matthew 149: } else {
150: $sourcefilename = &get_filename($course,$domain);
151: }
1.6 matthew 152: my $sql_filename = $sourcefilename;
1.2 matthew 153: $sql_filename =~ s|[^/]*$|activity.log.sql|;
1.14 matthew 154: my $gz_sql_filename = $sql_filename.'.gz';
155: #
156: my $xml_filename = $sourcefilename;
157: my $gz_xml_filename = $xml_filename.'.gz';
1.15 matthew 158: if (defined($xmlfile)) {
159: $xml_filename = $xmlfile;
160: if ($xml_filename =~ /\.gz$/) {
161: $gz_xml_filename = $xml_filename;
162: } else {
163: $gz_xml_filename = $xml_filename.'.gz';
164: }
165: } else {
166: my $xml_filename = $sourcefilename;
167: $xml_filename =~ s|[^/]*$|activity.log.xml|;
168: $gz_xml_filename = $xml_filename.'.gz';
169: }
1.14 matthew 170: #
1.3 matthew 171: $error_filename = $sourcefilename;
172: $error_filename =~ s|[^/]*$|activity.log.errors|;
173: $logthis->('Beginning logging '.time);
1.1 matthew 174:
1.7 matthew 175: #
176: # Wait for a lock on the lockfile to avoid collisions
177: my $lockfilename = $sourcefilename.'.lock';
1.16 matthew 178: $newfilename = $sourcefilename.'.processing';
1.15 matthew 179: if (! defined($xmlfile)) {
1.16 matthew 180: open(LOCKFILE,'>'.$lockfilename);
181: if (!flock(LOCKFILE,LOCK_EX|LOCK_NB)) {
182: warn("Unable to lock $lockfilename. Aborting".$/);
1.17 albertel 183: # don't call clean_up_and_exit another instance is running and
184: # we don't want to 'cleanup' there files
185: exit 6;
1.16 matthew 186: }
1.7 matthew 187:
1.16 matthew 188: if (! -e $newfilename && -e $sourcefilename) {
189: $logthis->('renaming '.$sourcefilename.' to '.$newfilename);
190: rename($sourcefilename,$newfilename);
191: Copy($newfilename,$newfilename.'.'.time) if ($backup);
192: $logthis->("renamed $sourcefilename to $newfilename");
193: } elsif (! -e $newfilename) {
194: utime(undef,undef,$newfilename);
195: }
1.1 matthew 196: }
197:
198: ##
199: ## Table definitions
200: ##
1.14 matthew 201: my %tables = &table_names($course,$domain);
1.1 matthew 202: my $student_table_def =
1.14 matthew 203: { id => $tables{'student'},
1.1 matthew 204: permanent => 'no',
205: columns => [
206: { name => 'student_id',
207: type => 'MEDIUMINT UNSIGNED',
208: restrictions => 'NOT NULL',
209: auto_inc => 'yes', },
210: { name => 'student',
211: type => 'VARCHAR(100) BINARY',
212: restrictions => 'NOT NULL', },
213: ],
214: 'PRIMARY KEY' => ['student_id',],
215: };
216:
217: my $res_table_def =
1.14 matthew 218: { id => $tables{'res'},
1.1 matthew 219: permanent => 'no',
220: columns => [{ name => 'res_id',
221: type => 'MEDIUMINT UNSIGNED',
222: restrictions => 'NOT NULL',
223: auto_inc => 'yes', },
224: { name => 'resource',
225: type => 'MEDIUMTEXT',
226: restrictions => 'NOT NULL'},
227: ],
228: 'PRIMARY KEY' => ['res_id'],
229: };
230:
1.4 matthew 231: #my $action_table_def =
232: #{ id => $action_table,
233: # permanent => 'no',
234: # columns => [{ name => 'action_id',
235: # type => 'MEDIUMINT UNSIGNED',
236: # restrictions => 'NOT NULL',
237: # auto_inc => 'yes', },
238: # { name => 'action',
239: # type => 'VARCHAR(100)',
240: # restrictions => 'NOT NULL'},
241: # ],
242: # 'PRIMARY KEY' => ['action_id',],
243: #};
1.1 matthew 244:
245: my $machine_table_def =
1.14 matthew 246: { id => $tables{'machine'},
1.1 matthew 247: permanent => 'no',
248: columns => [{ name => 'machine_id',
249: type => 'MEDIUMINT UNSIGNED',
250: restrictions => 'NOT NULL',
251: auto_inc => 'yes', },
252: { name => 'machine',
253: type => 'VARCHAR(100)',
254: restrictions => 'NOT NULL'},
255: ],
256: 'PRIMARY KEY' => ['machine_id',],
257: };
258:
259: my $activity_table_def =
1.14 matthew 260: { id => $tables{'activity'},
1.1 matthew 261: permanent => 'no',
262: columns => [
263: { name => 'res_id',
264: type => 'MEDIUMINT UNSIGNED',
265: restrictions => 'NOT NULL',},
266: { name => 'time',
267: type => 'DATETIME',
268: restrictions => 'NOT NULL',},
269: { name => 'student_id',
1.2 matthew 270: type => 'MEDIUMINT UNSIGNED',
1.1 matthew 271: restrictions => 'NOT NULL',},
1.4 matthew 272: { name => 'action',
273: type => 'VARCHAR(10)',
1.1 matthew 274: restrictions => 'NOT NULL',},
275: { name => 'idx', # This is here in case a student
276: type => 'MEDIUMINT UNSIGNED', # has multiple submissions during
277: restrictions => 'NOT NULL', # one second. It happens, trust
278: auto_inc => 'yes', }, # me.
279: { name => 'machine_id',
1.2 matthew 280: type => 'MEDIUMINT UNSIGNED',
1.1 matthew 281: restrictions => 'NOT NULL',},
282: { name => 'action_values',
283: type => 'MEDIUMTEXT', },
284: ],
1.4 matthew 285: 'PRIMARY KEY' => ['time','student_id','res_id','idx'],
286: 'KEY' => [{columns => ['student_id']},
287: {columns => ['time']},],
1.1 matthew 288: };
1.4 matthew 289:
1.8 matthew 290: my @Activity_Table = ($activity_table_def);
1.4 matthew 291: my @ID_Tables = ($student_table_def,$res_table_def,$machine_table_def);
1.14 matthew 292:
1.1 matthew 293: ##
1.13 matthew 294: ## End of table definitions
1.1 matthew 295: ##
1.14 matthew 296: $logthis->('tables = '.join(',',keys(%tables)));
1.1 matthew 297:
1.3 matthew 298: $logthis->('Connectiong to mysql');
1.8 matthew 299: &Apache::lonmysql::set_mysql_user_and_password('www',
1.1 matthew 300: $perlvar{'lonSqlAccess'});
301: if (!&Apache::lonmysql::verify_sql_connection()) {
302: warn "Unable to connect to MySQL database.";
1.2 matthew 303: $logthis->("Unable to connect to MySQL database.");
1.16 matthew 304: &clean_up_and_exit(3);
1.1 matthew 305: }
1.3 matthew 306: $logthis->('SQL connection is up');
307:
1.14 matthew 308: my $missing_table = &check_for_missing_tables(values(%tables));
309: if (-s $gz_sql_filename && ! -s $gz_xml_filename) {
1.8 matthew 310: my $backup_modification_time = (stat($gz_sql_filename))[9];
311: $logthis->($gz_sql_filename.' was last modified '.
312: localtime($backup_modification_time).
313: '('.$backup_modification_time.')');
1.14 matthew 314: if ($missing_table) {
315: # If the backup happened prior to the last table modification,
316: # we need to save the tables.
317: if (&latest_table_modification_time() > $backup_modification_time) {
318: # Save the current tables in case we need them another time.
319: $logthis->('Backing existing tables up');
320: &backup_tables_as_xml($gz_xml_filename.'.save_'.time,\%tables);
1.1 matthew 321: }
1.14 matthew 322: $time_this->();
323: &load_backup_sql_tables($gz_sql_filename);
324: &backup_tables_as_xml($gz_xml_filename,\%tables);
325: $time_this->('load backup tables');
1.1 matthew 326: }
1.14 matthew 327: } elsif (-s $gz_xml_filename) {
328: my $backup_modification_time = (stat($gz_xml_filename))[9];
329: $logthis->($gz_xml_filename.' was last modified '.
330: localtime($backup_modification_time).
331: '('.$backup_modification_time.')');
332: if ($missing_table) {
1.8 matthew 333: my $table_modification_time = $backup_modification_time;
334: # If the backup happened prior to the last table modification,
1.14 matthew 335: # we need to save the tables.
336: if (&latest_table_modification_time() > $backup_modification_time) {
1.8 matthew 337: # Save the current tables in case we need them another time.
1.14 matthew 338: $logthis->('Backing existing tables up');
339: &backup_tables_as_xml($gz_xml_filename.'.save_'.time,\%tables);
1.8 matthew 340: }
341: $time_this->();
1.14 matthew 342: # We have to make our own tables for the xml format
343: &drop_tables();
344: &create_tables();
345: &load_backup_xml_tables($gz_xml_filename,\%tables);
1.8 matthew 346: $time_this->('load backup tables');
1.14 matthew 347: }
1.1 matthew 348: }
349:
1.15 matthew 350: if (defined($xmlfile)) {
1.16 matthew 351: &clean_up_and_exit(0);
1.15 matthew 352: }
353:
1.3 matthew 354: ##
355: ## Ensure the tables we need exist
1.1 matthew 356: # create_tables does not complain if the tables already exist
1.3 matthew 357: $logthis->('creating tables');
1.1 matthew 358: if (! &create_tables()) {
359: warn "Unable to create tables";
1.2 matthew 360: $logthis->('Unable to create tables');
1.16 matthew 361: &clean_up_and_exit(4);
1.1 matthew 362: }
363:
1.3 matthew 364: ##
365: ## Read the ids used for various tables
1.2 matthew 366: $logthis->('reading id tables');
1.1 matthew 367: &read_id_tables();
1.2 matthew 368: $logthis->('finished reading id tables');
1.1 matthew 369:
370: ##
1.3 matthew 371: ## Set up the errors file
372: my $error_fh = IO::File->new(">>$error_filename");
373:
374: ##
375: ## Parse the course log
376: $logthis->('processing course log');
377: if (-s $newfilename) {
1.14 matthew 378: my $result = &process_courselog($newfilename,$error_fh,\%tables);
1.1 matthew 379: if (! defined($result)) {
380: # Something went wrong along the way...
1.2 matthew 381: $logthis->('process_courselog returned undef');
1.16 matthew 382: &clean_up_and_exit(5);
1.1 matthew 383: } elsif ($result > 0) {
384: $time_this->();
1.15 matthew 385: $logthis->('process_courselog returned '.$result.'.'.$/.
386: 'Backing up tables');
1.14 matthew 387: &backup_tables_as_xml($gz_xml_filename,\%tables);
1.1 matthew 388: $time_this->('write backup tables');
389: }
1.12 matthew 390: if ($drop_when_done) { &drop_tables(); $logthis->('dropped tables'); }
1.1 matthew 391: }
1.3 matthew 392: close($error_fh);
1.1 matthew 393:
394: ##
395: ## Clean up the filesystem
396: &Apache::lonmysql::disconnect_from_db();
1.3 matthew 397: unlink($newfilename) if (-e $newfilename && ! $nocleanup);
1.1 matthew 398:
1.3 matthew 399: ##
400: ## Print timing data
401: $logthis->('printing timing data');
1.1 matthew 402: if ($time_run) {
1.2 matthew 403: my $elapsed_time = Time::HiRes::time - $initial_time;
404: print "Overall time: ".$elapsed_time.$/;
1.1 matthew 405: print &outputtimes();
1.2 matthew 406: $logthis->("Overall time: ".$elapsed_time);
407: $logthis->(&outputtimes());
408: }
409:
1.16 matthew 410: &clean_up_and_exit(0);
411:
412: ########################################################
413: ########################################################
1.1 matthew 414:
1.16 matthew 415: sub clean_up_and_exit {
416: my ($exit_code) = @_;
417: # Close files
418: close(LOCKFILE);
419: close(LOGFILE);
420: # Remove zero length files
421: foreach my $file ($lockfilename, $error_filename,$logfile) {
422: if (defined($file) && -z $file) {
423: unlink($file);
424: }
1.12 matthew 425: }
1.16 matthew 426:
427: exit $exit_code;
1.12 matthew 428: }
429:
1.14 matthew 430: ########################################################
431: ########################################################
432: sub table_names {
433: my ($course,$domain) = @_;
434: my $prefix = $course.'_'.$domain.'_';
435: #
436: my %tables =
437: ( student =>&Apache::lonmysql::fix_table_name($prefix.'students'),
438: res =>&Apache::lonmysql::fix_table_name($prefix.'resource'),
439: machine =>&Apache::lonmysql::fix_table_name($prefix.'machine_table'),
440: activity=>&Apache::lonmysql::fix_table_name($prefix.'activity'),
441: );
442: return %tables;
443: }
1.1 matthew 444:
445: ########################################################
446: ########################################################
447: ##
448: ## Process Course Log
449: ##
450: ########################################################
451: ########################################################
452: #
453: # Returns the number of lines in the activity.log file that were processed.
454: sub process_courselog {
1.14 matthew 455: my ($inputfile,$error_fh,$tables) = @_;
1.1 matthew 456: if (! open(IN,$inputfile)) {
457: warn "Unable to open '$inputfile' for reading";
1.2 matthew 458: $logthis->("Unable to open '$inputfile' for reading");
1.1 matthew 459: return undef;
460: }
461: my ($linecount,$insertcount);
462: my $dbh = &Apache::lonmysql::get_dbh();
463: #
1.14 matthew 464: &store_entry();
1.1 matthew 465: while (my $line=<IN>){
466: # last if ($linecount > 1000);
467: #
468: # Bulk storage variables
469: $time_this->();
470: chomp($line);
471: $linecount++;
472: # print $linecount++.$/;
473: my ($timestamp,$host,$log)=split(/\:/,$line,3);
474: #
475: # $log has the actual log entries; currently still escaped, and
476: # %26(timestamp)%3a(url)%3a(user)%3a(domain)
477: # then additionally
478: # %3aPOST%3a(name)%3d(value)%3a(name)%3d(value)
479: # or
480: # %3aCSTORE%3a(name)%3d(value)%26(name)%3d(value)
481: #
482: # get delimiter between timestamped entries to be &&&
483: $log=~s/\%26(\d{9,10})\%3a/\&\&\&$1\%3a/g;
484: $log = &unescape($log);
485: # now go over all log entries
1.2 matthew 486: if (! defined($host)) { $host = 'unknown'; }
487: my $prevchunk = 'none';
488: foreach my $chunk (split(/\&\&\&/,$log)) {
489: my $warningflag = '';
490: my ($time,$res,$uname,$udom,$action,@values)= split(/:/,$chunk);
1.18 ! matthew 491: #
! 492: # Sometimes we get a file pasted into the activity.log from
! 493: # an upload form. Here we try to detect it and avoid inserting
! 494: # it into the database to avoid the quiet death of the database
! 495: # connection
! 496: my $i;
! 497: for ($i=0;$i<$#values;$i++) {
! 498: if ($values[$i] =~ /^HWVAL/) {
! 499: $#values = $i;
! 500: last;
! 501: }
! 502: }
! 503: #
1.1 matthew 504: if (! defined($res) || $res =~ /^\s*$/) {
505: $res = '/adm/roles';
1.2 matthew 506: $action = 'LOGIN';
1.1 matthew 507: }
508: if ($res =~ m|^/prtspool/|) {
509: $res = '/prtspool/';
510: }
511: if (! defined($action) || $action eq '') {
1.2 matthew 512: $action = 'VIEW';
1.1 matthew 513: }
1.2 matthew 514: if ($action !~ /^(LOGIN|VIEW|POST|CSTORE|STORE)$/) {
515: $warningflag .= 'action';
1.3 matthew 516: print $error_fh 'full log entry:'.$log.$/;
517: print $error_fh 'error on chunk:'.$chunk.$/;
518: $logthis->('(action) Unable to parse '.$/.$chunk.$/.
519: 'got '.
520: 'time = '.$time.$/.
521: 'res = '.$res.$/.
522: 'uname= '.$uname.$/.
523: 'udom = '.$udom.$/.
524: 'action='.$action.$/.
1.11 matthew 525: '@values = '.join('&',@values));
1.3 matthew 526: next; #skip it if we cannot understand what is happening.
1.2 matthew 527: }
528: #
1.14 matthew 529: my %data = (student => $uname.':'.$udom,
530: resource => $res,
531: machine => $host,
532: action => $action,
533: time => &Apache::lonmysql::sqltime($time));
1.11 matthew 534: if ($action eq 'POST') {
1.14 matthew 535: $data{'action_values'} =
1.11 matthew 536: $dbh->quote(join('&',map { &escape($_); } @values));
537: } else {
1.14 matthew 538: $data{'action_values'} = $dbh->quote(join('&',@values));
539: }
540: my $error = &store_entry($dbh,$tables,\%data);
541: if ($error) {
542: $logthis->('error store_entry:'.$error." on %data");
1.11 matthew 543: }
1.2 matthew 544: $prevchunk = $chunk;
1.1 matthew 545: }
546: }
1.14 matthew 547: my $result = &store_entry($dbh,$tables);
548: if (! defined($result)) {
549: my $error = &Apache::lonmysql::get_error();
550: warn "Error occured during insert.".$error;
551: $logthis->('error = '.$error);
1.1 matthew 552: }
553: close IN;
554: return $linecount;
555: }
556:
1.2 matthew 557:
558: ##
1.14 matthew 559: ## default value for $logthis and $time_this
1.2 matthew 560: sub nothing {
561: return;
562: }
563:
564: ##
1.14 matthew 565: ## Logging routine (look for $log)
1.2 matthew 566: ##
567: sub log_to_file {
568: my ($input)=@_;
569: print LOGFILE $input.$/;
570: }
571:
1.1 matthew 572: ##
573: ## Timing routines
574: ##
575: {
576: my %Timing;
577: my $starttime;
578:
579: sub time_action {
580: my ($key) = @_;
581: if (defined($key)) {
582: $Timing{$key}+=Time::HiRes::time-$starttime;
583: $Timing{'count_'.$key}++;
584: }
585: $starttime = Time::HiRes::time;
586: }
587:
588: sub outputtimes {
589: my $Str;
590: if ($time_run) {
591: $Str = "Timing Data:".$/;
592: while (my($k,$v) = each(%Timing)) {
593: next if ($k =~ /^count_/);
594: my $count = $Timing{'count_'.$k};
595: $Str .=
596: ' '.sprintf("%25.25s",$k).
597: ' '.sprintf('% 8d',$count).
598: ' '.sprintf('%12.5f',$v).$/;
599: }
600: }
601: return $Str;
602: }
603:
604: }
605:
1.14 matthew 606: sub latest_table_modification_time {
607: my $latest_time;
608: foreach my $table (@Activity_Table,@ID_Tables) {
609: my %tabledata = &Apache::lonmysql::table_information($table->{'id'});
610: next if (! scalar(keys(%tabledata))); # table does not exist
611: if (! defined($latest_time) ||
612: $latest_time < $tabledata{'Update_time'}) {
613: $latest_time = $tabledata{'Update_time'};
614: }
615: }
616: return $latest_time;
617: }
618:
619: sub check_for_missing_tables {
620: my @wanted_tables = @_;
621: # Check for missing tables
622: my @Current_Tables = &Apache::lonmysql::tables_in_db();
623: my %Found;
624: foreach my $tablename (@Current_Tables) {
625: foreach my $table (@wanted_tables) {
626: if ($tablename eq $table) {
627: $Found{$tablename}++;
628: }
629: }
630: }
631: $logthis->('Found tables '.join(',',keys(%Found)));
632: my $missing_a_table = 0;
633: foreach my $table (@wanted_tables) {
634: if (! $Found{$table}) {
635: $logthis->('Missing table '.$table);
636: $missing_a_table = 1;
637: last;
638: }
639: }
640: return $missing_a_table;
641: }
1.1 matthew 642:
643: ##
644: ## Use mysqldump to store backups of the tables
645: ##
1.14 matthew 646: sub backup_tables_as_sql {
1.6 matthew 647: my ($gz_sql_filename) = @_;
1.12 matthew 648: my $command = qq{mysqldump --quote-names --opt loncapa };
1.3 matthew 649: foreach my $table (@ID_Tables,@Activity_Table) {
1.1 matthew 650: my $tablename = $table->{'id'};
1.12 matthew 651: $tablename =~ s/\`//g;
1.1 matthew 652: $command .= $tablename.' ';
653: }
1.6 matthew 654: $command .= '| gzip >'.$gz_sql_filename;
1.2 matthew 655: $logthis->($command);
1.1 matthew 656: system($command);
657: }
658:
659: ##
660: ## Load in mysqldumped files
661: ##
1.14 matthew 662: sub load_backup_sql_tables {
1.6 matthew 663: my ($gz_sql_filename) = @_;
664: if (-s $gz_sql_filename) {
1.8 matthew 665: $logthis->('loading data from gzipped sql file');
666: my $command='gzip -dc '.$gz_sql_filename.' | mysql --database=loncapa';
1.6 matthew 667: system($command);
668: $logthis->('finished loading gzipped data');;
669: } else {
670: return undef;
671: }
1.1 matthew 672: }
673:
674: ##
675: ##
676: ##
677: sub update_process_name {
678: my ($text) = @_;
679: $0 = 'parse_activity_log.pl: '.$text;
680: }
681:
682: sub get_filename {
683: my ($course,$domain) = @_;
684: my ($a,$b,$c,undef) = split('',$course,4);
685: return "$perlvar{'lonUsersDir'}/$domain/$a/$b/$c/$course/activity.log";
686: }
687:
688: sub create_tables {
1.3 matthew 689: foreach my $table (@ID_Tables,@Activity_Table) {
1.1 matthew 690: my $table_id = &Apache::lonmysql::create_table($table);
691: if (! defined($table_id)) {
692: warn "Unable to create table ".$table->{'id'}.$/;
1.12 matthew 693: $logthis->('Unable to create table '.$table->{'id'});
694: $logthis->(join($/,&Apache::lonmysql::build_table_creation_request($table)));
1.1 matthew 695: return 0;
696: }
697: }
698: return 1;
699: }
700:
701: sub drop_tables {
1.3 matthew 702: foreach my $table (@ID_Tables,@Activity_Table) {
1.1 matthew 703: my $table_id = $table->{'id'};
704: &Apache::lonmysql::drop_table($table_id);
705: }
706: }
707:
708: #################################################################
709: #################################################################
710: ##
711: ## Database item id code
712: ##
713: #################################################################
714: #################################################################
715: { # Scoping for ID lookup code
716: my %IDs;
717:
718: sub read_id_tables {
1.3 matthew 719: foreach my $table (@ID_Tables) {
1.1 matthew 720: my @Data = &Apache::lonmysql::get_rows($table->{'id'});
1.3 matthew 721: my $count = 0;
1.1 matthew 722: foreach my $row (@Data) {
723: $IDs{$table->{'id'}}->{$row->[1]} = $row->[0];
724: }
725: }
1.3 matthew 726: return;
1.1 matthew 727: }
728:
729: sub get_id {
730: my ($table,$fieldname,$value) = @_;
1.16 matthew 731: if (exists($IDs{$table}->{$value}) && $IDs{$table}->{$value} =~ /^\d+$/) {
1.1 matthew 732: return $IDs{$table}->{$value};
733: } else {
734: # insert into the table - if the item already exists, that is
735: # okay.
736: my $result = &Apache::lonmysql::store_row($table,[undef,$value]);
737: if (! defined($result)) {
1.16 matthew 738: warn("Got error on id insert for $value\n".
739: &Apache::lonmysql::get_error());
1.1 matthew 740: }
741: # get the id
1.16 matthew 742: my $id = &Apache::lonmysql::get_dbh()->{'mysql_insertid'};
743: if (defined($id)) {
744: $IDs{$table}->{$value}=$id;
1.1 matthew 745: } else {
1.2 matthew 746: $logthis->("Unable to retrieve id for $table $fieldname $value");
1.1 matthew 747: return undef;
748: }
749: }
750: }
751:
752: } # End of ID scoping
753:
1.14 matthew 754: ###############################################################
755: ###############################################################
756: ##
757: ## Save as XML
758: ##
759: ###############################################################
760: ###############################################################
761: sub backup_tables_as_xml {
762: my ($filename,$tables) = @_;
763: open(XMLFILE,"|gzip - > $filename") || return ('error:unable to write '.$filename);
764: my $query = qq{
765: SELECT B.resource,
766: A.time,
767: A.idx,
768: C.student,
769: A.action,
770: E.machine,
771: A.action_values
772: FROM $tables->{'activity'} AS A
773: LEFT JOIN $tables->{'res'} AS B ON B.res_id=A.res_id
774: LEFT JOIN $tables->{'student'} AS C ON C.student_id=A.student_id
775: LEFT JOIN $tables->{'machine'} AS E ON E.machine_id=A.machine_id
776: ORDER BY A.time DESC
777: };
778: $query =~ s/\s+/ /g;
779: my $dbh = &Apache::lonmysql::get_dbh();
780: my $sth = $dbh->prepare($query);
781: if (! $sth->execute()) {
782: $logthis->('<font color="blue">'.
783: 'WARNING: Could not retrieve from database:'.
784: $sth->errstr().'</font>');
785: return undef;
786: } else {
787: my ($res,$sqltime,$idx,$student,$action,$machine,$action_values);
788: if ($sth->bind_columns(\$res,\$sqltime,\$idx,\$student,\$action,
789: \$machine,\$action_values)) {
790:
791: while ($sth->fetch) {
792: print XMLFILE '<row>'.
793: qq{<resource>$res</resource>}.
794: qq{<time>$sqltime</time>}.
795: qq{<idx>$idx</idx>}.
796: qq{<student>$student</student>}.
797: qq{<action>$action</action>}.
798: qq{<machine>$machine</machine>}.
799: qq{<action_values>$action_values</action_values>}.
800: '</row>'.$/;
801: }
802: } else {
803: warn "Unable to bind to columns.\n";
804: return undef;
805: }
806: }
807: close XMLFILE;
808: return;
809: }
810:
811: ###############################################################
812: ###############################################################
813: ##
814: ## load as xml
815: ##
816: ###############################################################
817: ###############################################################
1.15 matthew 818: {
819: my @fields = ('resource','time',
820: 'student','action','idx','machine','action_values');
821: my %ids = ();
1.14 matthew 822: sub load_backup_xml_tables {
823: my ($filename,$tables) = @_;
1.16 matthew 824: my $dbh = &Apache::lonmysql::get_dbh();
1.14 matthew 825: my $xmlfh;
826: open($xmlfh,"cat $filename | gzip -d - |");
827: if (! defined($xmlfh)) {
828: return ('error:unable to read '.$filename);
829: }
1.15 matthew 830: #
831: %ids = (resource=> {"\0count"=>1},
832: student=> {"\0count"=>1},
833: machine=> {"\0count"=>1});
834: #
1.14 matthew 835: my %data;
1.15 matthew 836: while (my $inputline = <$xmlfh>) {
837: my ($resource,$time,undef,$student,$action,$machine,$action_values) =
838: ($inputline =~ m{<row>
839: <resource>(.*)</resource>
840: <time>(.*)</time>
841: <idx>(.*)</idx>
842: <student>(.*)</student>
843: <action>(.*)</action>
844: <machine>(.*)</machine>
845: <action_values>(.*)</action_values>
846: </row>$
847: }x
848: );
849: my $resource_id = &xml_get_id('resource',$resource);
850: my $student_id = &xml_get_id('student',$student);
851: my $machine_id = &xml_get_id('machine',$machine);
1.16 matthew 852: &xml_store_activity_row(map { defined($_)?$dbh->quote($_):''
1.15 matthew 853: } ($resource_id,
854: $time,
855: $student_id,
856: $action,
857: 'NULL',
858: $machine_id,
859: $action_values));
860: }
861: &xml_store_activity_row();
862: close($xmlfh);
863: # Store id tables
864: while (my ($id_name,$id_data) = each(%ids)) {
865: if ($id_name eq 'resource') { $id_name = 'res'; }
866: delete($id_data->{"\0count"});
867: &xml_store_id_table($id_name,$id_data);
868: }
869: return;
870: }
871:
872: sub xml_get_id {
873: my ($table,$element) = @_;
874: if (! exists($ids{$table}->{$element})) {
875: $ids{$table}->{$element} = $ids{$table}->{"\0count"}++;
876: }
877: return $ids{$table}->{$element};
878: }
879:
880: {
881: my @data_rows;
882: sub xml_store_activity_row {
883: my @data = @_;
884: if (scalar(@data)) {
885: push(@data_rows,[@data]);
886: }
887: if (! scalar(@data) || scalar(@data_rows) > 500) {
888: if (! &Apache::lonmysql::bulk_store_rows($tables{'activity'},
889: scalar(@{$data_rows[0]}),
890: \@data_rows)) {
891: $logthis->("Error:".&Apache::lonmysql::get_error());
892: warn("Error:".&Apache::lonmysql::get_error());
893: } else {
894: undef(@data_rows);
1.14 matthew 895: }
896: }
897: return;
898: }
899:
1.15 matthew 900: }
901:
902: sub xml_store_id_table {
903: my ($table,$tabledata) =@_;
1.16 matthew 904: my $dbh = &Apache::lonmysql::get_dbh();
1.15 matthew 905: if (! &Apache::lonmysql::bulk_store_rows
906: ($tables{$table},2,
1.16 matthew 907: [map{[$tabledata->{$_},$dbh->quote($_)]} keys(%$tabledata)])) {
1.15 matthew 908: $logthis->("Error:".&Apache::lonmysql::get_error());
909: warn "Error:".&Apache::lonmysql::get_error().$/;
910: }
911: }
912:
913: } # End of load xml scoping
1.14 matthew 914:
915: #######################################################################
916: #######################################################################
917: ##
918: ## store_entry - accumulate data to be inserted into the database
1.15 matthew 919: ##
1.14 matthew 920: ## Pass no values in to clear accumulator
921: ## Pass ($dbh,\%tables) to initiate storage of values
922: ## Pass ($dbh,\%tables,\%data) to use normally
923: ##
924: #######################################################################
925: #######################################################################
926: {
927: my @rows;
928: my $max_row_count = 100;
929:
930: sub store_entry {
931: if (! @_) {
932: undef(@rows);
933: return '';
934: }
935: my ($dbh,$tables,$data) = @_;
936: return if (! defined($tables));
937: if (defined($data)) {
938: my $error;
939: foreach my $field ('student','resource','action','time') {
940: if (! defined($data->{$field}) || $data->{$field} eq ':' ||
941: $data->{$field}=~ /^\s*$/) {
942: $error.=$field.',';
943: }
944: }
945: if ($error) { $error=~s/,$//; return $error; }
946: #
947: my $student_id = &get_id($tables->{'student'},'student',
948: $data->{'student'});
949: my $res_id = &get_id($tables->{'res'},
950: 'resource',$data->{'resource'});
951: my $machine_id = &get_id($tables->{'machine'},
952: 'machine',$data->{'machine'});
953: my $idx = $data->{'idx'}; if (! $idx) { $idx = "''"; }
954: #
955: push(@rows,[$res_id,
956: qq{'$data->{'time'}'},
957: $student_id,
958: qq{'$data->{'action'}'},
959: $idx,
960: $machine_id,
961: $data->{'action_values'}]);
962: }
1.15 matthew 963: if (defined($tables) &&
1.14 matthew 964: ( (! defined($data) && scalar(@rows)) || scalar(@rows)>$max_row_count)
965: ){
966: # Store the rows
1.15 matthew 967: my $result =
1.14 matthew 968: &Apache::lonmysql::bulk_store_rows($tables->{'activity'},
969: undef,
970: \@rows);
971: if (! defined($result)) {
972: my $error = &Apache::lonmysql::get_error();
973: warn "Error occured during insert.".$error;
974: return $error;
975: }
976: undef(@rows);
977: return $result if (! defined($data));
978: }
979: return '';
980: }
981:
982: } # end of scope for &store_entry
1.1 matthew 983:
984: ###############################################################
985: ###############################################################
986: ##
987: ## The usual suspects
988: ##
989: ###############################################################
990: ###############################################################
991: sub escape {
992: my $str=shift;
993: $str =~ s/(\W)/"%".unpack('H2',$1)/eg;
994: return $str;
995: }
996:
997: sub unescape {
998: my $str=shift;
999: $str =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
1000: return $str;
1001: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/back.gif){kind=icon}
Return to parse_activity_log.pl CVS log ![[TXT]](/icons/text.gif){kind=icon}
![[DIR]](/icons/dir.gif){kind=icon}
Up to [LON-CAPA] / loncom / metadata_database