Menu
▾
▴
[WWcvs] webwork2: Added some of the database functions -- in particular dump_tables from head back to rel-2-4-5
|
From: Mike G. v. a. <we...@ma...> - 2008-06-29 14:38:22
|
Log Message:
-----------
Added some of the database functions -- in particular dump_tables
from head back to rel-2-4-5
Tags:
----
rel-2-4-patches
Modified Files:
--------------
webwork2/lib/WeBWorK:
DB.pm
webwork2/lib/WeBWorK/ContentGenerator/Instructor:
PGProblemEditor.pm
webwork2/lib/WeBWorK/DB/Record:
LocationAddresses.pm
webwork2/lib/WeBWorK/DB/Schema:
NewSQL.pm
webwork2/lib/WeBWorK/DB/Schema/NewSQL:
Std.pm
Revision Data
-------------
Index: DB.pm
===================================================================
RCS file: /webwork/cvs/system/webwork2/lib/WeBWorK/DB.pm,v
retrieving revision 1.104.2.2
retrieving revision 1.104.2.2.2.1
diff -Llib/WeBWorK/DB.pm -Llib/WeBWorK/DB.pm -u -r1.104.2.2 -r1.104.2.2.2.1
--- lib/WeBWorK/DB.pm
+++ lib/WeBWorK/DB.pm
@@ -97,12 +97,62 @@
use warnings;
use Carp;
use Data::Dumper;
+use Scalar::Util qw/blessed/;
use WeBWorK::DB::Schema;
use WeBWorK::DB::Utils qw/make_vsetID grok_vsetID grok_setID_from_vsetID_sql
grok_versionID_from_vsetID_sql/;
use WeBWorK::Debug;
use WeBWorK::Utils qw(runtime_use);
+=for comment
+
+These exceptions will replace the ones in WeBWorK::DB::Schema and will be
+allowed to propagate out to calling code. The following callers will have to be
+changed to catch these exceptions instead of doing string matching:
+
+lib/WebworkSOAP.pm: if ($@ =~ m/user set exists/) {
+lib/WeBWorK/ContentGenerator/Instructor.pm: if ($@ =~ m/user set exists/) {
+lib/WeBWorK/ContentGenerator/Instructor.pm: if ( $@ =~ m/user set exists/ ) {
+lib/WeBWorK/ContentGenerator/Instructor.pm: if ($@ =~ m/user problem exists/) {
+lib/WeBWorK/ContentGenerator/Instructor.pm: if ($@ =~ m/user problem exists/) {
+lib/WeBWorK/ContentGenerator/Instructor.pm: next if $@ =~ m/user set exists/;
+lib/WeBWorK/Utils/DBImportExport.pm: if ($@ =~ m/exists/) {
+lib/WeBWorK/DB.pm: if ($@ and $@ !~ m/password exists/) {
+lib/WeBWorK/DB.pm: if ($@ and $@ !~ m/permission level exists/) {
+
+How these exceptions should be used:
+
+* RecordExists is thrown by the DBI error handler (handle_error in
+Schema::NewSQL::Std) when in INSERT fails because a record exists. Thus it can
+be thrown via addUser, addPassword, etc.
+
+* RecordNotFound should be thrown when we try to UPDATE and zero rows were
+affected. Problem: Frank Wolfs (UofR PAS) may have a MySQL server that returns 0
+when updating even when a record was modified. What's up with that? There's some
+question as to where we should throw this: in this file's put* methods? In
+Std.pm's put method? Or in update_fields and update_fields_i?
+
+* DependencyNotFound should be throws when we check for a record that is needed
+to insert another record (e.g. password depends on user). These checks are done
+in this file, so we'll throw this exception from there.
+
+=cut
+
+use Exception::Class (
+ 'WeBWorK::DB::Ex' => {},
+ 'WeBWorK::DB::Ex::RecordExists' => {
+ isa => 'WeBWorK::DB::Ex',
+ fields => ['type', 'key'],
+ },
+ 'WeBWorK::DB::Ex::RecordNotFound' => {
+ isa => 'WeBWorK::DB::Ex',
+ fields => ['type', 'key'],
+ },
+ 'WeBWorK::DB::Ex::DependencyNotFound' => {
+ isa => 'WeBWorK::DB::Ex::RecordNotFound',
+ },
+);
+
################################################################################
# constructor
################################################################################
@@ -271,8 +321,40 @@
};
}
+sub gen_insert_records {
+ my $table = shift;
+ return sub {
+ my ($self, @records) = @_;
+ if (@records == 1 and blessed $records[0] and $records[0]->isa("Iterator")) {
+ return $self->{$table}->insert_records_i($records[0]);
+ } else {
+ return $self->{$table}->insert_records(@records);
+ }
+ };
+}
+
+sub gen_update_records {
+ my $table = shift;
+ return sub {
+ my ($self, @records) = @_;
+ if (@records == 1 and blessed $records[0] and $records[0]->isa("Iterator")) {
+ return $self->{$table}->update_records_i($records[0]);
+ } else {
+ return $self->{$table}->update_records(@records);
+ }
+ };
+}
+
+sub gen_delete_where {
+ my $table = shift;
+ return sub {
+ my ($self, $where) = @_;
+ return $self->{$table}->delete_where($where);
+ };
+}
+
################################################################################
-# create/rename/delete tables
+# create/rename/delete/dump/restore tables
################################################################################
sub create_tables {
@@ -334,6 +416,42 @@
return 1;
}
+sub dump_tables {
+ my ($self, $dump_dir) = @_;
+
+ foreach my $table (keys %$self) {
+ next if $table =~ /^_/; # skip non-table self fields (none yet)
+ next if $self->{$table}{params}{non_native}; # skip non-native tables
+ my $schema_obj = $self->{$table};
+ if ($schema_obj->can("dump_table")) {
+ my $dump_file = "$dump_dir/$table.sql";
+ $schema_obj->dump_table($dump_file);
+ } else {
+ warn "skipping dump of '$table' table: no dump_table method\n";
+ }
+ }
+
+ return 1;
+}
+
+sub restore_tables {
+ my ($self, $dump_dir) = @_;
+
+ foreach my $table (keys %$self) {
+ next if $table =~ /^_/; # skip non-table self fields (none yet)
+ next if $self->{$table}{params}{non_native}; # skip non-native tables
+ my $schema_obj = $self->{$table};
+ if ($schema_obj->can("restore_table")) {
+ my $dump_file = "$dump_dir/$table.sql";
+ $schema_obj->restore_table($dump_file);
+ } else {
+ warn "skipping restore of '$table' table: no restore_table method\n";
+ }
+ }
+
+ return 1;
+}
+
################################################################################
# user functions
################################################################################
@@ -693,6 +811,55 @@
}
################################################################################
+# setting functions
+################################################################################
+
+BEGIN {
+ *Setting = gen_schema_accessor("setting");
+ *newSetting = gen_new("setting");
+ *countSettingsWhere = gen_count_where("setting");
+ *existsSettingWhere = gen_exists_where("setting");
+ *listSettingsWhere = gen_list_where("setting");
+ *getSettingsWhere = gen_get_records_where("setting");
+ *addSettings = gen_insert_records("setting");
+ *putSettings = gen_update_records("setting");
+ *deleteSettingsWhere = gen_delete_where("setting");
+}
+
+# minimal set of routines for basic setting operation
+# we don't need a full set, since the usage of settings is somewhat limited
+# we also don't want to bother with records, since a setting is just a pair
+
+sub settingExists {
+ my ($self, $name) = @_;
+ return $self->{setting}->exists_where([name_eq=>$name]);
+}
+
+sub getSettingValue {
+ my ($self, $name) = @_;
+ return map { @$_ } $self->{setting}->get_fields_where(['value'], [name_eq=>$name]);
+}
+
+# we totally don't care if a setting already exists (and in fact i find that
+# whole distinction somewhat annoying lately) so we hide the fact that we're
+# either calling insert or update. at some point we could stand to add a
+# method to Std.pm that used REPLACE INTO and then we'd be able to not care
+# at all whether a setting was already there
+sub setSettingValue {
+ my ($self, $name, $value) = @_;
+ if ($self->settingExists($name)) {
+ return $self->{setting}->update_where({value=>$value}, [name_eq=>$name]);
+ } else {
+ return $self->{setting}->insert_fields(['name','value'], [[$name,$value]]);
+ }
+}
+
+sub deleteSetting {
+ my ($self, $name) = shift->checkArgs(\@_, qw/name/);
+ return $self->{setting}->delete_where([name_eq=>$name]);
+}
+
+################################################################################
# locations functions
################################################################################
# this database table is for ip restrictions by assignment
@@ -941,6 +1108,7 @@
my ($self, $setID) = shift->checkArgs(\@_, "set_id$U");
$self->deleteUserSet(undef, $setID);
$self->deleteGlobalProblem($setID, undef);
+ $self->deleteGlobalSetLocation($setID, undef);
return $self->{set}->delete($setID);
}
Index: PGProblemEditor.pm
===================================================================
RCS file: /webwork/cvs/system/webwork2/lib/WeBWorK/ContentGenerator/Instructor/PGProblemEditor.pm,v
retrieving revision 1.90.4.1
retrieving revision 1.90.4.1.2.1
diff -Llib/WeBWorK/ContentGenerator/Instructor/PGProblemEditor.pm -Llib/WeBWorK/ContentGenerator/Instructor/PGProblemEditor.pm -u -r1.90.4.1 -r1.90.4.1.2.1
--- lib/WeBWorK/ContentGenerator/Instructor/PGProblemEditor.pm
+++ lib/WeBWorK/ContentGenerator/Instructor/PGProblemEditor.pm
@@ -76,11 +76,11 @@
# But it is used instead of set_header when producing a hardcopy of the problem set in the TeX format, instead of producing HTML
# formatted version for use on the computer screen.
#
-# file_type eq 'course_info
+# file_type eq 'course_info'
# This allows editing of the course_info.txt file which gives general information about the course. It is called from the
# ProblemSets.pm module.
#
-# file_type eq 'options_info
+# file_type eq 'options_info'
# This allows editing of the options_info.txt file which gives general information about the course. It is called from the
# Options.pm module.
#
@@ -1540,14 +1540,6 @@
}
-
-
-
-
-
-
-
-
sub make_local_copy_handler {
my ($self, $genericParams, $actionParams, $tableParams) = @_;
foreach my $key (qw(target_file file_type saveMode source_file)) {
Index: LocationAddresses.pm
===================================================================
RCS file: /webwork/cvs/system/webwork2/lib/WeBWorK/DB/Record/LocationAddresses.pm,v
retrieving revision 1.1.2.1
retrieving revision 1.1.2.1.2.1
diff -Llib/WeBWorK/DB/Record/LocationAddresses.pm -Llib/WeBWorK/DB/Record/LocationAddresses.pm -u -r1.1.2.1 -r1.1.2.1.2.1
--- lib/WeBWorK/DB/Record/LocationAddresses.pm
+++ lib/WeBWorK/DB/Record/LocationAddresses.pm
@@ -1,4 +1,5 @@
-################################################################################# WeBWorK Online Homework Delivery System
+################################################################################
+# WeBWorK Online Homework Delivery System
# Copyright � 2000-2007 The WeBWorK Project, http://openwebwork.sf.net/
# $CVSHeader$
#
Index: NewSQL.pm
===================================================================
RCS file: /webwork/cvs/system/webwork2/lib/WeBWorK/DB/Schema/NewSQL.pm,v
retrieving revision 1.21.2.1
retrieving revision 1.21.2.1.2.1
diff -Llib/WeBWorK/DB/Schema/NewSQL.pm -Llib/WeBWorK/DB/Schema/NewSQL.pm -u -r1.21.2.1 -r1.21.2.1.2.1
--- lib/WeBWorK/DB/Schema/NewSQL.pm
+++ lib/WeBWorK/DB/Schema/NewSQL.pm
@@ -149,6 +149,11 @@
return {ip_mask=>$ip_mask};
}
+sub where_name_eq {
+ my ($self, $flags, $name) = @_;
+ return {name=>$name};
+} # gotta get rid of this stupid way of specifying where clauses...
+
################################################################################
# utility methods
################################################################################
@@ -177,6 +182,15 @@
return shift->{record}->FIELD_DATA;
}
+sub initial_records {
+ my ($self) = @_;
+ if ($self->{record}->can("INITIAL_RECORDS")) {
+ return $self->{record}->INITIAL_RECORDS;
+ } else {
+ return ();
+ }
+}
+
sub box {
my ($self, $values) = @_;
# promote undef values to empty strings. eventually we'd like to stop doing this (FIXME)
@@ -320,7 +334,8 @@
if (exists $API{$2}) {
croak sprintf("%s does not implement &%s", $1, $2);
} else {
- croak sprintf("Undefined subroutine &%s called", $AUTOLOAD);
+ warn caller();
+ croak sprintf("Undefined subroutine &%s called ", $AUTOLOAD);
}
}
Index: Std.pm
===================================================================
RCS file: /webwork/cvs/system/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm,v
retrieving revision 1.10.2.2
retrieving revision 1.10.2.2.2.1
diff -Llib/WeBWorK/DB/Schema/NewSQL/Std.pm -Llib/WeBWorK/DB/Schema/NewSQL/Std.pm -u -r1.10.2.2 -r1.10.2.2.2.1
--- lib/WeBWorK/DB/Schema/NewSQL/Std.pm
+++ lib/WeBWorK/DB/Schema/NewSQL/Std.pm
@@ -28,6 +28,8 @@
use Carp qw(croak);
use Iterator;
use Iterator::Util;
+use File::Temp;
+use String::ShellQuote;
use WeBWorK::DB::Utils::SQLAbstractIdentTrans;
use WeBWorK::Debug;
@@ -111,7 +113,10 @@
my ($self) = @_;
my $stmt = $self->_create_table_stmt;
- return $self->dbh->do($stmt);
+ $self->dbh->do($stmt);
+ my @fields = $self->fields;
+ my @rows = map { [ @$_{@fields} ] } $self->initial_records;
+ return $self->insert_fields(\@fields, \@rows);
}
# this is mostly ripped off from wwdb_check, which is pretty much a per-table
@@ -192,7 +197,90 @@
my ($self) = @_;
my $sql_table_name = $self->sql_table_name;
- return "DROP TABLE `$sql_table_name`";
+ return "DROP TABLE IF EXISTS `$sql_table_name`";
+}
+
+################################################################################
+# table dumping and restoring
+################################################################################
+
+# These are limited to mysql, since they use the mysql monitor and mysqldump.
+# An exception will be thrown if the table in question doesn't use mysql.
+# It also requires some additions to the params:
+# mysqldump_path - path to mysqldump(1)
+# mysql_path - path to mysql(1)
+
+sub dump_table {
+ my ($self, $dumpfile_path) = @_;
+
+ my ($my_cnf, $database) = $self->_get_db_info;
+ my $mysqldump = $self->{params}{mysqldump_path};
+
+ # 2>&1 is specified first, which apparently makes stderr go to stdout
+ # and stdout (not including stderr) go to the dumpfile. see bash(1).
+ my $dump_cmd = "2>&1 " . shell_quote($mysqldump)
+ . " --defaults-extra-file=" . shell_quote($my_cnf->filename)
+ . " " . shell_quote($database)
+ . " " . shell_quote($self->sql_table_name)
+ . " > " . shell_quote($dumpfile_path);
+ my $dump_out = readpipe $dump_cmd;
+ if ($?) {
+ my $exit = $? >> 8;
+ my $signal = $? & 127;
+ my $core = $? & 128;
+ die "Failed to dump table '".$self->sql_table_name."' with command '$dump_cmd' (exit=$exit signal=$signal core=$core): $dump_out\n";
+ }
+
+ return 1;
+}
+
+sub restore_table {
+ my ($self, $dumpfile_path) = @_;
+
+ my ($my_cnf, $database) = $self->_get_db_info;
+ my $mysql = $self->{params}{mysql_path};
+
+ my $restore_cmd = "2>&1 " . shell_quote($mysql)
+ . " --defaults-extra-file=" . shell_quote($my_cnf->filename)
+ . " " . shell_quote($database)
+ . " < " . shell_quote($dumpfile_path);
+ my $restore_out = readpipe $restore_cmd;
+ if ($?) {
+ my $exit = $? >> 8;
+ my $signal = $? & 127;
+ my $core = $? & 128;
+ die "Failed to restore table '".$self->sql_table_name."' with command '$restore_cmd' (exit=$exit signal=$signal core=$core): $restore_out\n";
+ }
+
+ return 1;
+}
+
+sub _get_db_info {
+ my ($self) = @_;
+ my $dsn = $self->{driver}{source};
+ my $username = $self->{params}{username};
+ my $password = $self->{params}{password};
+
+ die "Can't call dump_table or restore_table on a table with a non-MySQL source"
+ unless $dsn =~ s/^dbi:mysql://i;
+
+ # this is an internal function which we probably shouldn't be using here
+ # but it's quick and gets us what we want (FIXME what about sockets, etc?)
+ my %dsn;
+ DBD::mysql->_OdbcParse($dsn, \%dsn, ['database', 'host', 'port']);
+ die "no database specified in DSN!" unless defined $dsn{database};
+
+ # doing this securely is kind of a hassle...
+ my $my_cnf = new File::Temp;
+ $my_cnf->unlink_on_destroy(1);
+ chmod 0600, $my_cnf or die "failed to chmod 0600 $my_cnf: $!"; # File::Temp objects stringify with ->filename
+ print $my_cnf "[client]\n";
+ print $my_cnf "user=$username\n" if defined $username and length($username) > 0;
+ print $my_cnf "password=$password\n" if defined $password and length($password) > 0;
+ print $my_cnf "host=$dsn{host}\n" if defined $dsn{host} and length($dsn{host}) > 0;
+ print $my_cnf "port=$dsn{port}\n" if defined $dsn{port} and length($dsn{port}) > 0;
+
+ return ($my_cnf, $dsn{database});
}
################################################################################
|
