View of /trunk/webwork-modperl/bin/sql2sql_single

    1 #!/usr/bin/env perl
    2 ################################################################################
    3 # WeBWorK Online Homework Delivery System
    4 # Copyright © 2000-2006 The WeBWorK Project, http://openwebwork.sf.net/
    5 # $CVSHeader: webwork2/bin/sql2sql_single,v 1.2 2004/10/22 22:59:46 sh002i Exp $
    6 # 
    7 # This program is free software; you can redistribute it and/or modify it under
    8 # the terms of either: (a) the GNU General Public License as published by the
    9 # Free Software Foundation; either version 2, or (at your option) any later
   10 # version, or (b) the "Artistic License" which comes with this package.
   11 # 
   12 # This program is distributed in the hope that it will be useful, but WITHOUT
   13 # ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
   14 # FOR A PARTICULAR PURPOSE.  See either the GNU General Public License or the
   15 # Artistic License for more details.
   16 ################################################################################
   17 
   18 =head1 NAME
   19 
   20 sql2sql_single - convert a course from the C<sql> to the C<sql_single> database
   21 layout.
   22 
   23 =head1 SYNOPSIS
   24 
   25  sql2sql_single COURSEID NEWDBNAME DBADMIN DBPASS
   26 
   27 =head1 DESCRIPTION
   28 
   29 Convert a course database using the C<sql> database layout to use the
   30 <sql_single> database layout instead. This is done by moving the tables of the
   31 course database into the single database used by the C<sql_single> database
   32 layout. For this to work, both layouts must be using the same database server.
   33 
   34 NEWDBNAME gives the name of the database used by C<sql_single>. This is usually
   35 "webwork". DBADMIN and DBPASS are respectively the username and password of an
   36 administrative account on the SQL server.
   37 
   38 After running this script, you must edit the course's F<course.conf> file,
   39 chainging the database layout from C<sql> to C<sql_single>.
   40 
   41 =cut
   42 
   43 BEGIN {
   44 	# hide arguments (there could be passwords there!)
   45 	$0 = "$0";
   46 }
   47 
   48 use strict;
   49 use warnings;
   50 use DBI;
   51 use Data::Dumper;
   52 
   53 BEGIN {
   54 	die "WEBWORK_ROOT not found in environment.\n"
   55 		unless exists $ENV{WEBWORK_ROOT};
   56 }
   57 
   58 use lib "$ENV{WEBWORK_ROOT}/lib";
   59 use WeBWorK::CourseEnvironment;
   60 use WeBWorK::DB;
   61 use WeBWorK::Utils::CourseManagement qw/dbLayoutSQLSources/;
   62 
   63 sub usage {
   64 	print STDERR "usage: $0 COURSEID NEWDBNAME ADMINUSER ADMINPASSWORD\n";
   65 	exit;
   66 }
   67 
   68 sub usage_error {
   69 	print STDERR "$0: @_\n";
   70 	usage();
   71 }
   72 
   73 # get command-line options
   74 my ($courseID, $newDBName, $dbUsername, $dbPassword) = @ARGV;
   75 
   76 # perform sanity check
   77 usage_error("must specify COURSEID.") unless $courseID and $courseID ne "";
   78 usage_error("must specify NEWDBNAME.") unless defined $newDBName and $newDBName ne "";
   79 usage_error("must specify DBADMIN.") unless defined $dbUsername;
   80 usage_error("must specify DBPASS.") unless defined $dbPassword;
   81 
   82 # bring up a minimal course environment
   83 my $ce = WeBWorK::CourseEnvironment->new({
   84 	webwork_dir => $ENV{WEBWORK_ROOT},
   85 	courseName => $courseID,
   86 });
   87 
   88 # make sure the course actually uses the 'sql' layout
   89 usage_error("$courseID: does not use 'sql' database layout.") unless $ce->{dbLayoutName} eq "sql";
   90 
   91 # get database layout source data
   92 my %sql_sources = dbLayoutSQLSources($ce->{dbLayouts}{sql});
   93 
   94 foreach my $source (keys %sql_sources) {
   95 	my %source = %{$sql_sources{$source}};
   96 	my @tables = @{$source{tables}};
   97 	
   98 	my $dbh = DBI->connect($source, $dbUsername, $dbPassword);
   99 	
  100 	my $stmt = "RENAME TABLE";
  101 	
  102 	foreach my $table (@tables) {
  103 		my $curr_table_name = $ce->{dbLayouts}{sql}{$table}{params}{tableOverride} || $table;
  104 		my $new_table_name = $ce->{dbLayouts}{sql_single}{$table}{params}{tableOverride} || $table;
  105 		$stmt .= " `$curr_table_name` TO `$newDBName`.`$new_table_name`,";
  106 	}
  107 	
  108 	substr($stmt, -1, 1, ";");
  109 	unless ($dbh->do($stmt)) {
  110 		die "An error occured while trying to convert the course database.\n",
  111 			"It is possible that the course database was partially transferred.\n",
  112 			"The DBI error message was:\n\n",
  113 			$dbh->errstr, "\n";
  114 	}
  115 	
  116 	$dbh->disconnect;
  117 }
  118 
  119 # warn about modifying course.conf
  120 my $course_dot_conf = $ce->{courseFiles}{environment};
  121 
  122 print "\nThe course database for course '$courseID' has been moved to the 'sql_single' database '$newDBName'. To complete the transition, you must edit the course's course.conf file, located at: $course_dot_conf\n\n";
  123 
  124 print <<'EOT';
  125 In that file, there should be lines like:
  126 	$dbLayoutName = 'sql';
  127 	*dbLayout = $dbLayouts{$dbLayoutName};
  128 You must change these likes to:
  129 	$dbLayoutName = 'sql_single';
  130 	*dbLayout = $dbLayouts{$dbLayoutName};
  131 The following sed(1) command may be of use:
  132 	sed 's/sql/sql_single/' < course.conf > course.conf.new
  133 
  134 Until you do this, the course will not work.
  135 EOT
  136 
  137 =head1 AUTHOR
  138 
  139 Written by Sam Hathaway, hathaway at users.sourceforge.net.
  140 
  141 =cut