Updating Moodle table comments

Recently we updated Moodle to 2.6, after doing so I compared the SQL table structure of our upgraded site database against a clean install with the same addons. I found quite a few differences in table structure (smallint to tinyint, etc) for the 3rd party addons, and most core tables were missing their table comments.

To fix this I wrote the following perl script, it takes in a SQL structure dump from PhpMyAdmin and generates a list of alter commands to apply all the table comments in that file. This can then be run against your actual Moodle DB.


#!C:\Perl\bin\perl.exe

# Designed to read in SQL structure export and generate a list of alter commands to set comments from those tables

use strict; use warnings;

my $fileName = $ARGV[0];

open F, "< $fileName" or die "Can't open $fileName : $!"; my %comments = (); my $currentTable; while (my $line = ){
if ($line =~ /CREATE TABLE `(.+)`/) {
# Set current table name
# CREATE TABLE `mdl_assign`
$currentTable = $1;
}
if ($line =~ /COMMENT='(.+)'/) {
# Store table comment
$comments{ $currentTable } = $1;
}
}
close F;

foreach my $table ( sort keys %comments ) {
print "ALTER TABLE `" . $table . "` COMMENT = '" . $comments{$table} . "';\n";
}

About James Rudd

Network Administrator at Sydney Boys High School
This entry was posted in Moodle. Bookmark the permalink.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.