Menu
▾
▴
[WWcvs] webwork2: Streamlined the course integrity checking
|
From: Mike G. v. a. <we...@ma...> - 2009-02-02 03:27:56
|
Log Message:
-----------
Streamlined the course integrity checking
Modified Files:
--------------
webwork2/lib/WeBWorK/ContentGenerator:
CourseAdmin.pm
webwork2/lib/WeBWorK/DB/Schema/NewSQL:
Std.pm
webwork2/lib/WeBWorK/Utils:
CourseIntegrityCheck.pm
CourseManagement.pm
Revision Data
-------------
Index: CourseAdmin.pm
===================================================================
RCS file: /webwork/cvs/system/webwork2/lib/WeBWorK/ContentGenerator/CourseAdmin.pm,v
retrieving revision 1.80
retrieving revision 1.81
diff -Llib/WeBWorK/ContentGenerator/CourseAdmin.pm -Llib/WeBWorK/ContentGenerator/CourseAdmin.pm -u -r1.80 -r1.81
--- lib/WeBWorK/ContentGenerator/CourseAdmin.pm
+++ lib/WeBWorK/ContentGenerator/CourseAdmin.pm
@@ -381,11 +381,15 @@
%WeBWorK::SeedCE,
courseName => $courseID,
});
+
+ my $CIchecker = new WeBWorK::Utils::CourseIntegrityCheck(ce=>$tempCE);
+ my ($tables_ok,$dbStatus) = $CIchecker->checkCourseTables($courseID);
print CGI::li(CGI::a({href=>$self->systemLink($urlpath, authen => 0)}, $courseID),
CGI::code(
$tempCE->{dbLayoutName},
),
(-r $tempCE->{courseFiles}->{environment}) ? "" : CGI::i(", missing course.conf"),
+ ($courseID eq "modelCourse" or $tables_ok ) ? CGI::span({style=>"color:green"},"Database tables ok") : CGI::span({style=>"color:red"},"Database tables need updating"),
);
@@ -905,68 +909,89 @@
courseName => $rename_oldCourseID,
});
- my ($tables_ok,$ok_tables,$schema_only,$database_only,$update_fields);
+ my ($tables_ok,$dbStatus);
my %missing_fields;
if ($ce2->{dbLayoutName} ) {
- my $CIchecker = new WeBWorK::Utils::CourseIntegrityCheck(ce=>$ce2);
- if ($r->param("missing_database_tables")) {
- my @table_names = split(/\s+/, $r->param("missing_database_tables") );
- my $msg = $CIchecker->updateCourseTables($rename_oldCourseID, [@table_names]);
- print CGI::p({-style=>'color:green; font-weight:bold'}, $msg);
- }
- ($tables_ok,$ok_tables,$schema_only,$database_only,$update_fields) = $CIchecker->checkCourseTables($rename_oldCourseID);
- print CGI::p("Are you sure you want to rename the course " . CGI::b($rename_oldCourseID). " to ".CGI::b($rename_newCourseID)
- . "? ");
-
- print CGI::p({-style=>'color:black; font-weight:bold'},"These schema tables agree with those found in the database:");
- my $str = '';
- foreach my $table (sort keys %$ok_tables) {
- $str .= CGI::b($table).CGI::br();
- #$str .= CGI::span( {-style=>'color:gray; font-weight:lighter'},$both->{$table} );
- }
- print CGI::p($str);
-
- # print tables with mismatched fields
- my $all_fields_ok = 1;
- if (%$update_fields) {
- print CGI::p({-style=>'color:black; font-weight:bold'},"The field names for these tables don't
- agree with those found in the database. <br/>These fields will need to be repaired by hand by
- accessing the database directly.");
- $str='';
- foreach my $table (sort keys %$update_fields) {
- my ($field_ok, $fields_both, $fields_schema_only, $fields_database_only) = @{$update_fields->{$table}};
- $str .= " missing fields from database table <b>$table</b>: "
- . join(", ", map { "<br/> $_ => $$fields_schema_only{$_}" } keys %$fields_schema_only )
- . CGI::br();
- $all_fields_ok = 0 unless $field_ok;
+ my $CIchecker = new WeBWorK::Utils::CourseIntegrityCheck(ce=>$ce2);
+ ($tables_ok,$dbStatus) = $CIchecker->checkCourseTables($rename_oldCourseID);
+ if ($r->param("upgrade_course_tables")) {
+ my @schema_table_names = keys %$dbStatus; # update tables missing from database;
+ my @tables_to_create = grep {$dbStatus->{$_}->[0] == WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_A} @schema_table_names;
+ my @tables_to_alter = grep {$dbStatus->{$_}->[0] == WeBWorK::Utils::CourseIntegrityCheck::DIFFER_IN_A_AND_B} @schema_table_names;
+ my $msg = $CIchecker->updateCourseTables($rename_oldCourseID, [@tables_to_create]);
+ foreach my $table_name (@tables_to_alter) {
+ $msg .= $CIchecker->updateTableFields($rename_oldCourseID, $table_name);
}
- print CGI::p($str);
-
- }
-
- # print tables missing from database
- if (%$schema_only) {
- print CGI::p({-style=>'color:red; font-weight:bold'}, "These schema tables are missing from the database.
- Upgrading the database will create these tables." );
- $str = '';
- foreach my $table (sort keys %$schema_only) {
- $str .= CGI::b($table)." missing from database".CGI::br();
- }
- print CGI::p($str);
+ print CGI::p({-style=>'color:green; font-weight:bold'}, $msg);
}
-
- # print tables missing from schema
- if (%$database_only) {
- print CGI::p({-style=>'color:red; font-weight:bold'}, "These database tables are missing from the schema.
- These tables will be created in the database before archiving this course." );
- $str = '';
- foreach my $table (sort keys %$database_only) {
- $str .= CGI::b($table)." exists in database but is missing from schema".CGI::br();
+ ($tables_ok,$dbStatus) = $CIchecker->checkCourseTables($rename_oldCourseID);
+
+
+ # print db status
+
+ my %msg =( WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_A => CGI::span({style=>"color:red"}," Table defined in schema but missing in database"),
+ WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_B => CGI::span({style=>"color:red"}," Table defined in database but missing in schema"),
+ WeBWorK::Utils::CourseIntegrityCheck::SAME_IN_A_AND_B => CGI::span({style=>"color:green"}," Table is ok "),
+ WeBWorK::Utils::CourseIntegrityCheck::DIFFER_IN_A_AND_B => CGI::span({style=>"color:red"}," Schema and database table definitions do not agree "),
+ );
+ my %msg2 =( WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_A => CGI::span({style=>"color:red"}," missing in database"),
+ WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_B => CGI::span({style=>"color:red"}," missing in schema"),
+ WeBWorK::Utils::CourseIntegrityCheck::SAME_IN_A_AND_B => CGI::span({style=>"color:green"}," is ok "),
+ WeBWorK::Utils::CourseIntegrityCheck::DIFFER_IN_A_AND_B => CGI::span({style=>"color:red"}," Schema and database field definitions do not agree "),
+ );
+ my $all_tables_ok=1;
+ my $extra_database_tables=0;
+ my $extra_database_fields=0;
+ my $str=CGI::h4("Report on database structure for course $rename_oldCourseID:").CGI::br();
+ foreach my $table (sort keys %$dbStatus) {
+ my $table_status = $dbStatus->{$table}->[0];
+ $str .= CGI::b($table) . $msg{ $table_status } . CGI::br();
+
+ CASE: {
+ $table_status == WeBWorK::Utils::CourseIntegrityCheck::SAME_IN_A_AND_B
+ && do{ last CASE;
+ };
+ $table_status == WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_A
+ && do{
+ $all_tables_ok = 0; last CASE;
+ };
+ $table_status == WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_B
+ && do{
+ $extra_database_tables = 1; last CASE;
+ };
+ $table_status == WeBWorK::Utils::CourseIntegrityCheck::DIFFER_IN_A_AND_B
+ && do{
+ my %fieldInfo = %{ $dbStatus->{$table}->[1] };
+ foreach my $key (keys %fieldInfo) {
+ my $field_status = $fieldInfo{$key}->[0];
+ CASE2: {
+ $field_status == WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_B
+ && do{
+ $extra_database_fields = 1; last CASE2;
+ };
+ $field_status == WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_A
+ && do{
+ $all_tables_ok=0; last CASE2;
+ };
+ }
+ $str .= CGI::br()."\n Field $key => ". $msg2{$field_status };
+ }
+ };
}
- print CGI::p($str);
+ $str.=CGI::br();
+
}
- if ($tables_ok) {
- print CGI::p({-style=>'color:black; font-weight:bold'},"Course $rename_oldCourseID database is in order");
+ print CGI::p($str);
+ if ($extra_database_tables) {
+ print CGI::p({-style=>'color:red; font-weight:bold'},"There are extra database tables which are not defined in the schema.
+ They can only be removed manually from the database. They will not be renamed.");
+ }
+ if ($extra_database_fields) {
+ print CGI::p({-style=>'color:red; font-weight:bold'},"There are extra database fields which are not defined in the schema for at least one table.
+ They can only be removed manually from the database.");
+ }
+ if ($all_tables_ok) {
+ print CGI::p({-style=>'color:green; font-weight:bold'},"Course $rename_oldCourseID database is in order");
} else {
print CGI::p({-style=>'color:red; font-weight:bold'}, "Course $rename_oldCourseID databases must be updated before renaming this course.");
}
@@ -979,31 +1004,19 @@
- if ($tables_ok and $all_fields_ok ) { # no missing fields
+ if ($all_tables_ok ) { # no missing tables or missing fields
print CGI::p({style=>"text-align: center"},
CGI::submit(-name=>"decline_rename_course", -value=>"Don't rename"),
" ",
CGI::submit(-name=>"confirm_rename_course", -value=>"Rename") ,
);
- } elsif ($all_fields_ok) {
+ } else {
print CGI::p({style=>"text-align: center"},
- CGI::hidden(-name => 'missing_database_tables',-value => join(" ",keys %$schema_only)),
- CGI::hidden(-name => 'extra_database_tables', -value => join(" ",keys %$database_only) ),
- CGI::hidden(-name => 'missing_fields', -value => join(" ", %missing_fields) ),
CGI::submit(-name => "decline_rename_course", -value => "Don't rename"),
" ",
CGI::submit(-name=>"upgrade_course_tables", -value=>"upgrade course tables"),
);
- } else {
- print CGI::p({style=>"text-align: center"},
- CGI::hidden(-name => 'missing_database_tables',-value => join(" ",keys %$schema_only)),
- CGI::hidden(-name => 'extra_database_tables', -value => join(" ",keys %$database_only) ),
- CGI::hidden(-name => 'missing_fields', -value => join(" ", %missing_fields) ),
- CGI::submit(-name => "decline_rename_course", -value => "Don't rename"),
- " ",
- # CGI::submit(-name=>"upgrade_course_tables", -value=>"upgrade course tables"),
- );
- }
+ }
}
}
sub rename_course_validate {
@@ -1784,73 +1797,97 @@
});
- my ($tables_ok,$ok_tables,$schema_only,$database_only,$update_fields);
+ my ($tables_ok,$dbStatus);
+
my %missing_fields;
if ($ce2->{dbLayoutName} ) {
my $CIchecker = new WeBWorK::Utils::CourseIntegrityCheck(ce=>$ce2);
- if ($r->param("missing_database_tables")) {
- my @table_names = split(/\s+/, $r->param("missing_database_tables") );
- my $msg = $CIchecker->updateCourseTables($archive_courseID, [@table_names]);
- print CGI::p({-style=>'color:green; font-weight:bold'}, $msg);
- }
- ($tables_ok,$ok_tables,$schema_only,$database_only,$update_fields) = $CIchecker->checkCourseTables($archive_courseID);
- print CGI::p("Are you sure you want to archive the course " . CGI::b($archive_courseID)
- . "? ");
-
- print CGI::p({-style=>'color:black; font-weight:bold'},"These schema tables agree with those found in the database:");
- my $str = '';
- foreach my $table (sort keys %$ok_tables) {
- $str .= CGI::b($table).CGI::br();
- #$str .= CGI::span( {-style=>'color:gray; font-weight:lighter'},$both->{$table} );
- }
- print CGI::p($str);
-
- # print tables with mismatched fields
- my $all_fields_ok = 1;
- if (%$update_fields) {
- print CGI::p({-style=>'color:black; font-weight:bold'},"The field names for these tables don't
- agree with those found in the database. <br/>These fields will need to be repaired by hand by
- accessing the database directly.");
- $str='';
- foreach my $table (sort keys %$update_fields) {
- my ($field_ok, $fields_both, $fields_schema_only, $fields_database_only) = @{$update_fields->{$table}};
- $str .= " missing fields from database table <b>$table</b>: "
- . join(", ", map { "<br/> $_ => $$fields_schema_only{$_}" } keys %$fields_schema_only )
- . CGI::br();
- $all_fields_ok = 0 unless $field_ok;
+ ($tables_ok,$dbStatus) = $CIchecker->checkCourseTables($archive_courseID);
+ if ($r->param("upgrade_course_tables")) {
+ my @schema_table_names = keys %$dbStatus; # update tables missing from database;
+ my @tables_to_create = grep {$dbStatus->{$_}->[0] == WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_A} @schema_table_names;
+ my @tables_to_alter = grep {$dbStatus->{$_}->[0] == WeBWorK::Utils::CourseIntegrityCheck::DIFFER_IN_A_AND_B} @schema_table_names;
+ my $msg = $CIchecker->updateCourseTables($archive_courseID, [@tables_to_create]);
+ foreach my $table_name (@tables_to_alter) {
+ $msg .= $CIchecker->updateTableFields($archive_courseID, $table_name);
}
- print CGI::p($str);
-
- }
-
- # print tables missing from database
- if (%$schema_only) {
- print CGI::p({-style=>'color:red; font-weight:bold'}, "These schema tables are missing from the database.
- Upgrading the database will create these tables." );
- $str = '';
- foreach my $table (sort keys %$schema_only) {
- $str .= CGI::b($table)." missing from database".CGI::br();
- }
- print CGI::p($str);
+ print CGI::p({-style=>'color:green; font-weight:bold'}, $msg);
}
-
- # print tables missing from schema
- if (%$database_only) {
- print CGI::p({-style=>'color:red; font-weight:bold'}, "These database tables are missing from the schema.
- These tables will be created in the database before archiving this course." );
- $str = '';
- foreach my $table (sort keys %$database_only) {
- $str .= CGI::b($table)." exists in database but is missing from schema".CGI::br();
+ ($tables_ok,$dbStatus) = $CIchecker->checkCourseTables($archive_courseID);
+
+
+ # print db status
+
+ my %msg =( WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_A => CGI::span({style=>"color:red"}," Table defined in schema but missing in database"),
+ WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_B => CGI::span({style=>"color:red"}," Table defined in database but missing in schema"),
+ WeBWorK::Utils::CourseIntegrityCheck::SAME_IN_A_AND_B => CGI::span({style=>"color:green"}," Table is ok "),
+ WeBWorK::Utils::CourseIntegrityCheck::DIFFER_IN_A_AND_B => CGI::span({style=>"color:red"}," Schema and database table definitions do not agree "),
+ );
+ my %msg2 =( WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_A => CGI::span({style=>"color:red"}," missing in database"),
+ WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_B => CGI::span({style=>"color:red"}," missing in schema"),
+ WeBWorK::Utils::CourseIntegrityCheck::SAME_IN_A_AND_B => CGI::span({style=>"color:green"}," is ok "),
+ WeBWorK::Utils::CourseIntegrityCheck::DIFFER_IN_A_AND_B => CGI::span({style=>"color:red"}," Schema and database field definitions do not agree "),
+ );
+ my $all_tables_ok=1;
+ my $extra_database_tables=0;
+ my $extra_database_fields=0;
+ my $str=CGI::h4("Report on database structure for course $archive_courseID:").CGI::br();
+ foreach my $table (sort keys %$dbStatus) {
+ my $table_status = $dbStatus->{$table}->[0];
+ $str .= CGI::b($table) . $msg{ $table_status } . CGI::br();
+
+ CASE: {
+ $table_status == WeBWorK::Utils::CourseIntegrityCheck::SAME_IN_A_AND_B
+ && do{ last CASE;
+ };
+ $table_status == WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_A
+ && do{
+ $all_tables_ok = 0; last CASE;
+ };
+ $table_status == WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_B
+ && do{
+ $extra_database_tables = 1; last CASE;
+ };
+ $table_status == WeBWorK::Utils::CourseIntegrityCheck::DIFFER_IN_A_AND_B
+ && do{
+ my %fieldInfo = %{ $dbStatus->{$table}->[1] };
+ foreach my $key (keys %fieldInfo) {
+ my $field_status = $fieldInfo{$key}->[0];
+ CASE2: {
+ $field_status == WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_B
+ && do{
+ $extra_database_fields = 1; last CASE2;
+ };
+ $field_status == WeBWorK::Utils::CourseIntegrityCheck::ONLY_IN_A
+ && do{
+ $all_tables_ok=0; last CASE2;
+ };
+ }
+ $str .= CGI::br()."\n Field $key => ". $msg2{$field_status };
+ }
+ };
}
- print CGI::p($str);
+ $str.=CGI::br();
+
}
-
- if ($tables_ok) {
- print CGI::p({-style=>'color:black; font-weight:bold'},"Course $archive_courseID database is in order");
+ print CGI::p($str);
+ if ($extra_database_tables) {
+ print CGI::p({-style=>'color:red; font-weight:bold'},"There are extra database tables which are not defined in the schema.
+ They can only be removed manually from the database.");
+ }
+ if ($extra_database_fields) {
+ print CGI::p({-style=>'color:red; font-weight:bold'},"There are extra database fields which are not defined in the schema for at least one table.
+ They can only be removed manually from the database.");
+ }
+ if ($all_tables_ok) {
+ print CGI::p({-style=>'color:green; font-weight:bold'},"Course $archive_courseID database is in order");
print(CGI::p({-style=>'color:red; font-weight:bold'}, "Are you sure that you want to delete the course ".
- CGI::b($archive_courseID). " after archiving? This cannot be undone!")) if $delete_course_flag;
+ CGI::b($archive_courseID). " after archiving? This cannot be undone!")) if $delete_course_flag;
} else {
- print CGI::p({-style=>'color:red; font-weight:bold'}, "Course $archive_courseID databases must be updated before archiving this course.");
+ print CGI::p({-style=>'color:red; font-weight:bold'}, "There are tables or fields missing from the
+ database. The database
+ must be upgraded before archiving this course."
+ );
}
print CGI::start_form(-method=>"POST", -action=>$r->uri);
print $self->hidden_authen_fields;
@@ -1864,39 +1901,25 @@
print CGI::p( "$archive_courseID: The directory for the course not found.");
}
- # fail if a course archive already exists
- # FIXME there could be an option to overwrite an existing archive
- if (-e $archive_path and -w $archive_path) {
- print CGI::p({-style=>'color:red; font-weight:bold'},"The course '$archive_courseID' has already been archived at '$archive_path'.
- This earlier archive will be erased. This cannot be undone.");
- }
-
-
- if ($tables_ok and $all_fields_ok ) { # no missing fields
+ if ($all_tables_ok ) { # no missing fields
+ # Warn about overwriting an existing archive
+ if (-e $archive_path and -w $archive_path) {
+ print CGI::p({-style=>'color:red; font-weight:bold'},"The course '$archive_courseID' has already been archived at '$archive_path'.
+ This earlier archive will be erased. This cannot be undone.");
+ }
+ # archive execute button
print CGI::p({style=>"text-align: center"},
CGI::submit(-name=>"decline_archive_course", -value=>"Don't archive"),
" ",
CGI::submit(-name=>"confirm_archive_course", -value=>"archive") ,
);
- } elsif ($all_fields_ok) {
+ } else {
print CGI::p({style=>"text-align: center"},
- CGI::hidden(-name => 'missing_database_tables',-value => join(" ",keys %$schema_only)),
- CGI::hidden(-name => 'extra_database_tables', -value => join(" ",keys %$database_only) ),
- CGI::hidden(-name => 'missing_fields', -value => join(" ", %missing_fields) ),
- CGI::submit(-name => "decline_archive_course", -value => "Don't archive"),
+ CGI::submit(-name => "decline_archive_course", -value => "Don't archive"),
" ",
CGI::submit(-name=>"upgrade_course_tables", -value=>"upgrade course tables"),
);
- } else {
- print CGI::p({style=>"text-align: center"},
- CGI::hidden(-name => 'missing_database_tables',-value => join(" ",keys %$schema_only)),
- CGI::hidden(-name => 'extra_database_tables', -value => join(" ",keys %$database_only) ),
- CGI::hidden(-name => 'missing_fields', -value => join(" ", %missing_fields) ),
- CGI::submit(-name => "decline_archive_course", -value => "Don't archive"),
- " ",
- # CGI::submit(-name=>"upgrade_course_tables", -value=>"upgrade course tables"),
- );
- }
+ }
print CGI::end_form();
} else {
print CGI::p({-style=>'color:red; font-weight:bold'},"Unable to find database layout for $archive_courseID");
Index: Std.pm
===================================================================
RCS file: /webwork/cvs/system/webwork2/lib/WeBWorK/DB/Schema/NewSQL/Std.pm,v
retrieving revision 1.21
retrieving revision 1.22
diff -Llib/WeBWorK/DB/Schema/NewSQL/Std.pm -Llib/WeBWorK/DB/Schema/NewSQL/Std.pm -u -r1.21 -r1.22
--- lib/WeBWorK/DB/Schema/NewSQL/Std.pm
+++ lib/WeBWorK/DB/Schema/NewSQL/Std.pm
@@ -302,6 +302,29 @@
return "Describe `$sql_table_name` `$field_name`";
}
####################################################
+# adding Field column
+####################################################
+
+sub add_column_field {
+ my $self = shift;
+ my $field_name = shift;
+ my $stmt = $self->_add_column_field_stmt($field_name);
+ #warn "database command $stmt";
+ my $result = $self->dbh->do($stmt);
+ #warn "result of add column is $result";
+ #return ($result eq "0E0") ? 0 : 1; # failed result is 0E0
+ return 1; #FIXME how to determine if database update was successful???
+}
+
+sub _add_column_field_stmt {
+ my $self = shift;
+ my $field_name=shift;
+ my $sql_table_name = $self->sql_table_name;
+ my $sql_field_name = $self->sql_field_name($field_name);
+ my $sql_field_type = $self->field_data->{$field_name}{type};
+ return "Alter table `$sql_table_name` add column `$sql_field_name` $sql_field_type";
+}
+####################################################
# checking Tables
####################################################
sub tableExists {
Index: CourseManagement.pm
===================================================================
RCS file: /webwork/cvs/system/webwork2/lib/WeBWorK/Utils/CourseManagement.pm,v
retrieving revision 1.45
retrieving revision 1.46
diff -Llib/WeBWorK/Utils/CourseManagement.pm -Llib/WeBWorK/Utils/CourseManagement.pm -u -r1.45 -r1.46
--- lib/WeBWorK/Utils/CourseManagement.pm
+++ lib/WeBWorK/Utils/CourseManagement.pm
@@ -1169,154 +1169,5 @@
-#
-#
-# =item checkCourseDirectories($courseName)
-#
-# Checks the course files and directories to make sure they exist and have the correct permissions.
-#
-# =cut
-#
-#
-#
-# =item checkCourseTables($courseName, $dbLayoutName, $ce);
-#
-# Checks the course tables in the mysql database and ensures that they are the
-# same as the ones specified by the databaseLayout
-#
-#
-# =cut
-#
-# sub checkCourseTables {
-# my ($courseName, $dbLayoutName, $ce) = @_;
-# my $str='';
-# my %both = ();
-# my %schema_only = ();
-# my %database_only = ();
-# ##########################################################
-# # fetch schema from course environment and search database
-# # for corresponding tables.
-# ##########################################################
-# my $db = new WeBWorK::DB($ce->{dbLayouts}->{$dbLayoutName});
-# foreach my $table (sort keys %$db) {
-# next if $db->{$table}{params}{non_native}; # skip non-native tables
-# my $table_name = (exists $db->{$table}->{params}->{tableOverride})? $db->{$table}->{params}->{tableOverride}:$table;
-# my $database_table_exists = ($db->{$table}->can("delete_table")) ? 1:0;
-# if ($database_table_exists ) { # exists means the table could be deleted.
-# $both{$table_name} = checkTableFields($courseName, $dbLayoutName, $ce, $table);
-# } else {
-# $schema_only{$table_name} = 1;
-# }
-# }
-# ##########################################################
-# # fetch fetch corresponding tables in the database and
-# # search for corresponding schema entries.
-# ##########################################################
-#
-# my $dbh =$db->{key}->dbh; # grab any database handle
-# my $stmt = "show tables like '$courseName%'"; # mysql request
-# my $result = $dbh->selectall_arrayref($stmt) ;
-# my @tableNames = map {@$_} @$result; # drill down in the result to the table name level
-# foreach my $table (sort @tableNames) {
-# $table =~/${courseName}_(.*)/;
-# my $schema_name = $1;
-# my $exists = exists($db->{$schema_name});
-# $database_only{$table}=1 unless $exists;
-# }
-# print CGI::p($str);
-# my $tables_ok = not ( %schema_only || %database_only ); # count number of extraneous tables; 0 means ok
-# return ($tables_ok,\%both, \%schema_only, \%database_only); # table in both schema & database; found in schema only; found in database only
-# }
-#
-# =item updateCourseTables($courseName, $dbLayoutName, $ce, $table_names);
-#
-# Adds schema tables to the database that had been missing from the database.
-#
-# =cut
-#
-# sub updateCourseTables {
-# my ($courseName, $dbLayoutName, $ce, $table_names) = @_;
-# my $db = new WeBWorK::DB($ce->{dbLayouts}->{$dbLayoutName});
-# warn "Programmers: Pass reference to the array of table names to be updated." unless ref($table_names)=~/ARRAY/;
-# #warn "table names are ".join(" ", @$table_names);
-# my $str='';
-# foreach my $table (sort @$table_names) { # remainder copied from db->create_table
-# #warn "processing $table";
-# next if $table =~ /^_/; # skip non-table self fields (none yet)
-# #warn "not a non-table self field";
-# next if $db->{$table}{params}{non_native}; # skip non-native tables
-# #warn "not a non_native table";
-# my $schema_obj = $db->{$table};
-#
-# if ($schema_obj->can("create_table")) {
-# # warn "creating table $schema_obj";
-# $schema_obj->create_table;
-# $str .= "Table $table created".CGI::br();
-# } else {
-# warn "Skipping creation of '$table' table: no create_table method\n";
-# }
-# }
-# $str;
-#
-# }
-#
-# =cut
-#
-#
-#
-# =item checkTableFields($courseName, $dbLayoutName, $ce, $table);
-#
-# Checks the course tables in the mysql database and insures that they are the same as the ones specified by the databaseLayout
-#
-#
-# =cut
-#
-#
-# sub checkTableFields {
-# my ($courseName, $dbLayoutName, $ce,$table) = @_;
-# my $str=' ';
-# my %both = ();
-# my %schema_only = ();
-# my %database_only = ();
-# ##########################################################
-# # fetch schema from course environment and search database
-# # for corresponding tables.
-# ##########################################################
-# my $db = new WeBWorK::DB($ce->{dbLayouts}->{$dbLayoutName});
-# my $table_name = (exists $db->{$table}->{params}->{tableOverride})? $db->{$table}->{params}->{tableOverride}:$table;
-# warn "$table_name is a non native table" if $db->{$table}{params}{non_native}; # skip non-native tables
-# my @fields = $db->{$table}->{record}->FIELDS;
-# foreach my $field (sort @fields) {
-# #my $database_table_exists = ($db->{$table}->can("delete_table")) ? 1:0;
-# my $field_name = $db->{$table}->{params}->{fieldOverride}->{$field} ||$field;
-# my $database_field_exists = $db->{$table}->tableFieldExists($field_name);
-# if ($database_field_exists) {
-# $str.="$field =>$field_name, ";
-# $both{$field}=1;
-# } else {
-# $str.="$field =>MISSING, ";
-# $schema_only{$field}=1;
-# }
-#
-# }
-# ##########################################################
-# # fetch fetch corresponding tables in the database and
-# # search for corresponding schema entries.
-# ##########################################################
-#
-# # my $dbh =$db->{key}->dbh; # grab any database handle
-# # my $stmt = "show tables like '$courseName%'"; # mysql request
-# # my $result = $dbh->selectall_arrayref($stmt) ;
-# # my @tableNames = map {@$_} @$result; # drill down in the result to the table name level
-# # foreach my $table (sort @tableNames) {
-# # $table =~/${courseName}_(.*)/;
-# # my $schema_name = $1;
-# # my $exists = exists($db->{$schema_name});
-# # $database_only{$table}=1 unless $exists;
-# # }
-# # return (\%both, \%schema_only, \%database_only); # table in both schema & database; found in schema only; found in database only
-# return $str."<br/>";
-# }
-
1;
Index: CourseIntegrityCheck.pm
===================================================================
RCS file: /webwork/cvs/system/webwork2/lib/WeBWorK/Utils/CourseIntegrityCheck.pm,v
retrieving revision 1.1
retrieving revision 1.2
diff -Llib/WeBWorK/Utils/CourseIntegrityCheck.pm -Llib/WeBWorK/Utils/CourseIntegrityCheck.pm -u -r1.1 -r1.2
--- lib/WeBWorK/Utils/CourseIntegrityCheck.pm
+++ lib/WeBWorK/Utils/CourseIntegrityCheck.pm
@@ -28,6 +28,12 @@
use WeBWorK::Debug;
use WeBWorK::Utils::CourseManagement qw/listCourses/;
+use constant { # constants describing the comparison of two hashes.
+ ONLY_IN_A=>0,
+ ONLY_IN_B=>1,
+ DIFFER_IN_A_AND_B=>2,
+ SAME_IN_A_AND_B=>3
+};
################################################################################
sub new {
@@ -71,7 +77,7 @@
}
################################################################################
-=item checkCourseDirectories($courseName)
+=item $CIchecker->checkCourseDirectories($courseName)
Checks the course files and directories to make sure they exist and have the correct permissions.
@@ -79,7 +85,7 @@
-=item checkCourseTables($courseName, $dbLayoutName, $ce);
+=item $CIchecker->checkCourseTables($courseName);
Checks the course tables in the mysql database and ensures that they are the
same as the ones specified by the databaseLayout
@@ -90,11 +96,9 @@
sub checkCourseTables {
my ($self, $courseName) = @_;
my $str='';
- my %ok_tables = ();
- my %schema_only = ();
- my %database_only = ();
- my %update_fields = ();
- ##########################################################
+ my $tables_ok = 1;
+ my %dbStatus = ();
+ #################################
# fetch schema from course environment and search database
# for corresponding tables.
##########################################################
@@ -105,14 +109,16 @@
my $table_name = (exists $db->{$table}->{params}->{tableOverride})? $db->{$table}->{params}->{tableOverride}:$table;
my $database_table_exists = ($db->{$table}->tableExists) ? 1:0;
if ($database_table_exists ) { # exists means the table can be described;
- my( $fields_ok, $field_str,$fields_both, $fields_schema_only, $fields_database_only) = $self->checkTableFields($courseName, $table);
+ my( $fields_ok, $fieldStatus) = $self->checkTableFields($courseName, $table);
if ($fields_ok) {
- $ok_tables{$table_name} = 1;
+ $dbStatus{$table} = [SAME_IN_A_AND_B()];
} else {
- $update_fields{$table_name}=[$fields_ok,$fields_both,$fields_schema_only,$fields_database_only];
+ $dbStatus{$table} = [DIFFER_IN_A_AND_B(),$fieldStatus];
+ $tables_ok=0;
}
} else {
- $schema_only{$table_name} = 1;
+ $tables_ok=0;
+ $dbStatus{$table}=[ONLY_IN_A(),];
}
}
##########################################################
@@ -128,40 +134,40 @@
next unless $table =~/^${courseName}\_(.*)/; #double check that we only have our course tables
my $schema_name = $1;
my $exists = exists($db->{$schema_name});
- $database_only{$table}=1 unless $exists;
+ $tables_ok = 0 unless exists($db->{$schema_name});
+ $dbStatus{$schema_name} =[ONLY_IN_B] unless $exists;
}
- my $tables_ok = ( scalar(%schema_only) || scalar(%database_only) ||scalar(%update_fields) ) ?0 :1; # count number of extraneous tables; no such tables makes $tables_ok true
$self->unlock_database;
- return ($tables_ok,\%ok_tables, \%schema_only, \%database_only, \%update_fields); # table in both schema & database; found in schema only; found in database only
+ return ($tables_ok,\%dbStatus); # table in both schema & database; found in schema only; found in database only
}
-=item updateCourseTables($courseName, $dbLayoutName, $ce, $table_names);
+=item $CIchecker-> updateCourseTables($courseName, $table_names);
Adds schema tables to the database that had been missing from the database.
=cut
sub updateCourseTables {
- my ($self, $courseName, $table_names) = @_;
+ my ($self, $courseName, $schema_table_names) = @_;
my $db = $self->db;
$self->lock_database;
- warn "Programmers: Pass reference to the array of table names to be updated." unless ref($table_names)=~/ARRAY/;
- #warn "table names are ".join(" ", @$table_names);
+ warn "Programmers: Pass reference to the array of table names to be updated." unless ref($schema_table_names)=~/ARRAY/;
+ # warn "table names are ".join(" ", @$schema_table_names);
my $str='';
- foreach my $table (sort @$table_names) { # remainder copied from db->create_table
- next if $table =~ /^_/; # skip non-table self fields (none yet)
- #warn "not a non-table self field";
- $table =~ /${courseName}_(.*)/;
- my $schema_table_name = $1;
+ foreach my $schema_table_name (sort @$schema_table_names) { # remainder copied from db->create_table
+ # next if $table =~ /^_/; # skip non-table self fields (none yet)
+ # warn "not a non-table self field";
next if $db->{$schema_table_name}{params}{non_native}; # skip non-native tables
#warn "not a non_native table";
my $schema_obj = $db->{$schema_table_name};
+ my $database_table_name = (exists $db->{$schema_table_name}->{params}->{tableOverride})?
+ $db->{$schema_table_name}->{params}->{tableOverride}:$schema_table_name;
if ($schema_obj->can("create_table")) {
# warn "creating table $schema_obj";
$schema_obj->create_table;
- $str .= "Table $table created".CGI::br();
+ $str .= "Table $schema_table_name created as $database_table_name in database.".CGI::br();
} else {
- warn "Skipping creation of '$table' table: no create_table method\n";
+ warn "Skipping creation of '$schema_table_name' table: no create_table method\n";
}
}
$self->unlock_database;
@@ -173,7 +179,7 @@
-=item checkTableFields($courseName, $dbLayoutName, $ce, $table);
+=item $CIchecker->checkTableFields($courseName, $table);
Checks the course tables in the mysql database and insures that they are the same as the ones specified by the databaseLayout
@@ -183,10 +189,8 @@
sub checkTableFields {
my ($self,$courseName, $table) = @_;
- my $str=' ';
- my %both = ();
- my %schema_only = ();
- my %database_only = ();
+ my $fields_ok = 1;
+ my %fieldStatus = ();
##########################################################
# fetch schema from course environment and search database
# for corresponding tables.
@@ -200,12 +204,11 @@
my $field_name = $db->{$table}->{params}->{fieldOverride}->{$field} ||$field;
$schema_override_field_names{$field_name}=$field;
my $database_field_exists = $db->{$table}->tableFieldExists($field_name);
- if ($database_field_exists) {
- $str.="$field =>$field_name, ";
- $both{$field}=1;
+ if ($database_field_exists) {
+ $fieldStatus{$field} =[SAME_IN_A_AND_B]
} else {
- $str.="$field =>MISSING, ";
- $schema_only{$field}=1;
+ $fields_ok = 0;
+ $fieldStatus{$field} =[ONLY_IN_A];
}
}
@@ -215,16 +218,50 @@
##########################################################
my $dbh =$self->dbh; # grab any database handle
- my $stmt = "SHOW COLUMNS FROM $table_name"; # mysql request
+ my $stmt = "SHOW COLUMNS FROM `$table_name`"; # mysql request
my $result = $dbh->selectall_arrayref($stmt) ;
my %database_field_names = map {${$_}[0]=>[$_]} @$result; # drill down in the result to the field name level
# result is array: Field | Type | Null | Key | Default | Extra
foreach my $field_name (sort keys %database_field_names) {
my $exists = exists($schema_override_field_names{$field_name} );
- $database_only{$table}=1 unless $exists;
+ $fields_ok=0 unless $exists;
+ $fieldStatus{$field_name} = [ONLY_IN_B] unless $exists;
}
- my $fields_ok = not ( %schema_only || %database_only ); # count number of extraneous tables; no such tables makes $fields_ok true
- return ($fields_ok, $str."<br/>",\%both, \%schema_only, \%database_only); # table in both schema & database; found in schema only; found in database only
+
+
+ return ($fields_ok, \%fieldStatus); # table in both schema & database; found in schema only; found in database only
+}
+
+
+=item $CIchecker->updateTableFields($courseName, $table);
+
+Checks the fields in the table in the mysql database and insures that they are the same as the ones specified by the databaseLayout
+
+
+=cut
+
+
+sub updateTableFields {
+ my ($self,$courseName, $table) = @_;
+ my $msg='';
+ ##########################################################
+ # fetch schema from course environment and search database
+ # for corresponding tables.
+ ##########################################################
+ my $db = $self->db;
+ my $table_name = (exists $db->{$table}->{params}->{tableOverride})? $db->{$table}->{params}->{tableOverride}:$table;
+ warn "$table_name is a non native table" if $db->{$table}{params}{non_native}; # skip non-native tables
+ my ($fields_ok, $fieldStatus) = $self->checkTableFields($courseName,$table);
+ # add fields
+ foreach my $field_name (keys %$fieldStatus) {
+ next unless $fieldStatus->{$field_name}->[0] == ONLY_IN_A;
+ my $schema_obj = $db->{$table};
+ if ( $schema_obj->can("add_column_field") ) {
+ $msg.= "Added column '$field_name' to table '$table'".CGI::br() if $schema_obj->add_column_field($field_name);
+ }
+ }
+ return $msg;
+
}
##############################################################################
@@ -308,4 +345,17 @@
}
+#
+#
+# =item checkCourseDirectories($courseName)
+#
+# Checks the course files and directories to make sure they exist and have the correct permissions.
+#
+# =cut
+#
+#
+#
+
+
+
1;
\ No newline at end of file
|
