Menu
▾
▴
[WWcvs] webwork2: Add command-line utility to update sql and sql_single
|
From: Gavin L. v. a. <we...@ma...> - 2005-07-18 23:08:44
|
Log Message:
-----------
Add command-line utility to update sql and sql_single databases to includ=
e
gateway fields. Use: wwdb_addgw [-h] [sql|sql_single]
Adds fields to the set and set_user tables in the WeBWorK mysql databases=
=20
that are required for the gateway module. The script prompts for which=20
courses to modify. Adding gateway database fields to existing courses=20
should have no effect on those courses, even if they are running under a
non-gateway aware version of the WeBWorK system. (I haven't rigorously
tested this.)
If -h is supplied, the script hides the mysql password when it prompts fo=
r
it (this assumes that a Unix based stty -echo works). If sql or sql_sing=
le
are supplied, they become the default database format.
Note that this has been tested, but not rigorously. Please let me know
if you find that it does not perform as advertised. It might be wise to=20
back up the database tables before using, and to check that it does what
we expect (that is, adds columns to the set and set_user tables) after=20
running it.
Added Files:
-----------
webwork2/bin:
wwdb_addgw
Revision Data
-------------
--- /dev/null
+++ bin/wwdb_addgw
@@ -0,0 +1,391 @@
+#!/usr/bin/perl -w
+########################################################################=
########
+# WeBWorK Online Homework Delivery System
+# Copyright =A9 2000-2003 The WeBWorK Project, http://openwebwork.sf.net=
/
+# $CVSHeader: webwork2/bin/wwdb_addgw,v 1.1 2005/07/18 23:10:35 glarose =
Exp $
+#=20
+# This program is free software; you can redistribute it and/or modify i=
t under
+# the terms of either: (a) the GNU General Public License as published b=
y the
+# Free Software Foundation; either version 2, or (at your option) any la=
ter
+# version, or (b) the "Artistic License" which comes with this package.
+#=20
+# This program is distributed in the hope that it will be useful, but WI=
THOUT
+# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or =
FITNESS
+# FOR A PARTICULAR PURPOSE. See either the GNU General Public License o=
r the
+# Artistic License for more details.
+########################################################################=
########
+#=20
+# wwdb_addgw
+# update webwork database tables to add fields for the gateway module
+#
+# by Gavin LaRose <gl...@um...>
+#
+=3Dhead1 NAME
+
+wwdb_addgw - convert SQL databases for WeBWorK 2 to add gateway fields.
+
+=3Dhead1 SYNOPSIS
+
+ wwdb_addgw [-h] [sql|sql_single]
+
+=3Dhead1 DESCRIPTION
+
+Adds fields to the set and set_user tables in the WeBWorK mysql database=
s=20
+that are required for the gateway module. The script prompts for which=20
+courses to modify. Adding gateway database fields to existing courses=20
+should have no effect on those courses, even if they are running under a
+non-gateway aware version of the WeBWorK system.
+
+If C<-h> is provided, the script hides the mysql admin password.
+
+C<sql> or C<sql_single> gives the default WeBWorK database format. If=20
+omitted, the script assumes sql_single and prompts to be sure.
+
+=3Dcut
+
+use strict;
+use DBI;
+
+# this is necessary on some systems
+system("stty erase =08");
+
+my $source =3D 'DBI:mysql';
+
+# fields to add to the set and set_user tables
+my %addFields =3D ( 'assignment_type' =3D> 'text',
+ 'attempts_per_version' =3D> 'integer',
+ 'time_interval' =3D> 'integer',
+ 'versions_per_interval' =3D> 'integer',
+ 'version_time_limit' =3D> 'integer',
+ 'version_creation_time' =3D> 'bigint',
+ 'problem_randorder' =3D> 'integer',
+ 'version_last_attempt_time' =3D> 'bigint', );
+
+# process input data
+my $hidepw =3D 0;
+my $dbtype =3D 'sql_single';
+while ( $_ =3D shift(@ARGV) ) {
+ if ( /^-h$/ ) {
+ $hidepw =3D 1;
+ } elsif ( /^-/ ) {
+ die("Unknown input flag $_.\nUsage: wwdb_addgw [-h] sql|sql_single\n");
+ } else {
+ if ( $_ eq 'sql' || $_ eq 'sql_single' ) {
+ $dbtype =3D $_;
+ } else {
+ die("Unknown argument $_.\nUsage: wwdb_addgw [-h] " .
+ "sql|sql_single\n");
+ }
+ }
+}
+
+printHdr( $dbtype );
+
+# get database information
+my ( $admin, $adminpw );
+( $admin, $adminpw, $dbtype ) =3D getDBInfo( $hidepw, $dbtype );
+
+# connect to database, if we're in sql_single mode; this lets us easily
+# get a list of courses to work with. in sql mode, it's harder b/c I ca=
n't
+# get DBI->data_sources('mysql') to work on my system, so we prompt for=20
+# those separately. if we're in sql single mode, $dbh is a place holder=
,
+# because we have to do the database connects in the subroutines to conn=
ect
+# to each different database
+my $dbh =3D '';
+if ( $dbtype eq 'sql_single' ) {
+ $dbh =3D DBI->connect("$source:webwork", $admin, $adminpw) or
+ die( $DBI::errstr );
+}
+
+# get courses list
+my @courses =3D getCourses( $dbtype, $dbh );
+
+# now $course{coursename} =3D format (sql or sql_single)
+
+# do update
+my ( $doneRef, $skipRef ) =3D updateCourses( $dbtype, $dbh, \@courses,=20
+ $admin, $adminpw );
+$dbh->disconnect() if ( $dbh );
+
+# all done
+confirmUpdate( $dbtype, $doneRef, $skipRef );
+
+# end of main
+#-----------------------------------------------------------------------=
--------
+# subroutines
+
+sub printHdr {=20
+ print <<eoh;
+------------------------------------------------------------------------=
---
+wwdb_addgw: update WeBWorK SQL databases to include fields required for =
a=20
+ Gateway aware WeBWorK installation.
+
+set default WeBWorK database type to $dbtype.
+------------------------------------------------------------------------=
---
+eoh
+ return 1;
+}
+
+sub getDBInfo {
+ my $hide =3D shift();
+ my $type =3D shift();
+
+ print "mySQL administrator login name [root] > ";
+ my $admin =3D <STDIN>;
+ chomp( $admin );
+ $admin =3D 'root' if ( ! $admin );
+
+ print "mySQL login password for $admin > ";
+ system("stty -echo") if ( $hide );
+ my $passwd =3D <STDIN>;
+ if ( $hide ) { system("stty echo"); print "\n"; }
+ chomp( $passwd );
+ die("Error: no password provided\n") if ( ! $passwd );
+
+ print "WeBWorK database type (sql or sql_single) [$type] > ";
+ my $dbtype =3D <STDIN>;
+ chomp( $dbtype );
+ $dbtype =3D $type if ( ! $dbtype );
+
+ return( $admin, $passwd, $dbtype );
+}
+
+sub getCourses {
+ my ( $dbtype, $dbh ) =3D @_;
+
+ my %courses =3D ();
+
+# get a course list
+ if ( $dbtype eq 'sql' ) {
+ print "courses to update (enter comma separated) > ";
+ my $crslist =3D <STDIN>;
+ chomp($crslist);
+ my @crslist =3D split(/,\s*/, $crslist);
+ die("Error: no courses specified\n") if ( ! @crslist );
+ foreach ( @crslist ) { $courses{$_} =3D 1; }
+
+ } else {
+ my $cmd =3D 'show tables';
+ my $st =3D $dbh->prepare( $cmd ) or die( $dbh->errstr() );
+ $st->execute() or die( $st->errstr() );
+ my $rowRef =3D $st->fetchall_arrayref();
+ foreach my $r ( @$rowRef ) {
+ $_ =3D $r->[0];
+ my ($crs, $tbl) =3D ( /^([^_]+)_(.*)$/ );
+ $courses{$crs} =3D 1 if ( defined( $crs ) );
+ }
+ die("Error: found now sql_single WeBWorK courses\n") if ( ! %courses );
+ }
+
+# confirm this is correct
+ print "\nList of courses to update:\n";
+ my %nummap =3D orderedList( %courses );
+ printclist( sort keys( %courses ) );
+ print "Enter # to edit name, d# to delete from update list, or [cr] =
to " .
+ "continue.\n > ";
+ my $resp =3D <STDIN>;
+ chomp($resp);
+ while ( $resp ) {
+ if ( $resp =3D~ /^\d+$/ ) {
+ print " old course name $nummap{$resp}; new > ";
+ delete( $courses{$nummap{$resp}} );
+ my $newname =3D <STDIN>;
+ chomp($newname);
+ $courses{ $newname } =3D 1;
+ } elsif ( $resp =3D~ /^d(\d+)$/ ) {
+ $resp =3D $1;
+ delete( $courses{$nummap{$resp}} );
+ } else {
+ print "unrecognized response: $resp.\n";
+ }
+ %nummap =3D orderedList( %courses );
+ print "Current list of courses to update:\n";
+ printclist( sort keys( %courses ) );
+ print "Enter #, d# or [cr] > "; =20
+ chomp( $resp =3D <STDIN> );
+ }
+
+ my @courses =3D sort( keys %courses );
+ if ( @courses ) {
+ return @courses;
+ } else {
+ die("Error: no courses left to update.\n");
+ }
+}
+
+sub orderedList {
+ my %hash =3D @_;
+ my $i=3D1;
+ my %nummap =3D ();
+ foreach ( sort( keys( %hash ) ) ) {
+ $nummap{ $i } =3D $_;
+ $i++;
+ }
+ return %nummap;
+}
+
+sub printclist {
+ my @list =3D @_;
+
+# assumes a 75 column screen
+
+ my $i =3D 1;
+ if ( @list <=3D 3 ) {
+ foreach ( @list ) { print " $i. $_\n"; $i++ }
+ } else {
+ while ( @list >=3D $i ) {
+ printf(" %2d. %-19s", $i, $list[$i-1]);
+ printf(" %2d. %-19s", ($i+1), $list[$i]) if ( @list >=3D ($i+1) );
+ printf(" %2d. %-19s", ($i+2), $list[$i+1]) if ( @list >=3D ($i+2) =
);
+ print "\n";
+ $i+=3D3;
+ }
+ }
+ return 1;
+}
+
+sub updateCourses {
+ my ( $dbtype, $dbh, $crsRef, $admin, $adminpw ) =3D @_;
+
+ my @done =3D ();
+ my @skipped =3D ();
+
+# give some sense of progress
+ select STDOUT; $| =3D 1; # unbuffer output
+ print "doing update.";
+
+# list of added fields to check for classes that don't need updating
+ my @newFields =3D keys( %addFields );
+
+ foreach my $crs ( @$crsRef ) {
+ print ".";
+ my $colRef;
+
+ if ( $dbtype eq 'sql' ) {
+ # we need to get a database handle first
+ $dbh =3D DBI->connect("$source:webwork_$crs", $admin, $adminpw) or
+ die( $DBI::errstr );
+
+ # now get a list of columns from the set table to check to see if=20
+ # we need an update
+ my $cmd =3D "show columns from set_not_a_keyword";
+ my $st =3D $dbh->prepare( $cmd ) or die( $dbh->errstr() );
+ $st->execute();
+ $colRef =3D $st->fetchall_arrayref();
+
+ } else {
+ # for sql_single we already have a database handle; get the set tabl=
e
+ # columns and proceed
+ my $cmd =3D "show columns from ${crs}_set";
+ my $st =3D $dbh->prepare( $cmd ) or die( $dbh->errstr() );
+ $st->execute();
+ $colRef =3D $st->fetchall_arrayref();
+ }
+
+ # now, do we have the columns we need already?
+ my $doneAlready =3D 0;
+ foreach my $cols ( @$colRef ) {
+ if ( inList( $cols->[0], @newFields ) ) {
+ $doneAlready =3D 1;
+ last;
+ }
+ }
+ if ( $doneAlready ) {
+ push( @skipped, $crs );
+ next;
+ } else {
+
+ # do update for course
+ my ( $cmd1, $cmd2 );
+ if ( $dbtype eq 'sql' ) {
+ $cmd1 =3D 'alter table set_not_a_keyword add column';
+ $cmd2 =3D 'alter table set_user add column';
+ } else {
+ $cmd1 =3D "alter table ${crs}_set add column";
+ $cmd2 =3D "alter table ${crs}_set_user add column";
+ }
+
+ foreach my $f ( keys %addFields ) {
+ my $st =3D $dbh->prepare( "$cmd1 $f $addFields{$f}" ) or=20
+ die( $dbh->errstr() );
+ $st->execute() or die( $st->errstr() );
+ }
+
+ foreach my $f ( keys %addFields ) {
+ my $st =3D $dbh->prepare( "$cmd2 $f $addFields{$f}" ) or=20
+ die( $dbh->errstr() );
+ $st->execute() or die( $st->errstr() );
+ }
+
+ push( @done, $crs );
+ }
+ # if we're doing sql databases, disconnect from this courses' databa=
se
+ $dbh->disconnect() if ( $dbtype eq 'sql' );
+
+ } # end loop through courses
+ print "\n";
+
+ return( \@done, \@skipped );
+}
+
+sub inList {
+ my $v =3D shift();
+ foreach ( @_ ) { return 1 if ( $v eq $_ ); }
+ return 0;
+}
+
+sub confirmUpdate {
+ my ( $dbtype, $doneRef, $skipRef ) =3D @_;
+
+ my $s1 =3D "updated $dbtype courses: ";
+ my $s2 =3D "courses not needing updates were skipped: ";
+ my $l1 =3D length($s1);
+ my $l2 =3D length($s2);
+
+ my $crsList=3D (@$doneRef) ? join(', ', @$doneRef) : '';
+ my $skpList=3D (@$skipRef) ? join(', ', @$skipRef) : '';
+ my $crsString =3D ( $crsList ) ?=20
+ $s1 . hangIndent( $l1, 75, $l1, "$crsList.") . "\n" : '';
+ my $skpString =3D ( $skpList ) ?=20
+ $s2 . hangIndent( $l1, 75, $l2, "$skpList." ) : '';
+
+ print <<eot;
+------------------------------------------------------------------------=
---
+done.
+$crsString$skpString
+
+eot
+}
+
+sub hangIndent {
+ my ( $hang, $width, $shorten, $text ) =3D @_;
+# pre: $hang and $width are numbers, $hang < $width; $text is a string
+# if $shorten, the first line is shortened by $shorten
+# post: $text is reformatted to have maximum width $width and a hanging
+# indent of $hang each line after the first; the reformatted text
+# is returned
+ my $htext =3D '';
+ my $line =3D '';
+ my $indent =3D ($shorten ? $shorten : 0);
+ my $ldr =3D ' 'x$hang;
+
+ if ( $indent + length($text) < $width ) {
+ $htext =3D $text;
+ } else {
+ foreach ( split(/\s+/, $text ) ) {
+ if ( $indent + length($line) + length($_) >=3D $width ) {
+ $htext .=3D $line . "\n$ldr";
+ $line =3D "$_ ";
+ $indent =3D $hang;
+ } else {
+ $line .=3D "$_ ";
+ }
+ }
+ $htext .=3D $line if ( $line );
+ }
+ $htext =3D~ s/\n$ldr$//;
+ return $htext;
+}
+
+# end of script
+#-----------------------------------------------------------------------=
--------
|
