maintenance/postgres/compare_schemas.pl

   1 #!/usr/bin/perl
   2
   3 ## Rough check that the base and postgres "tables.sql" are in sync
   4 ## Should be run from maintenance/postgres
   5
   6 use strict;
   7 use warnings;
   8 use Data::Dumper;
   9
  10 my @old = ("../tables.sql");
  11 my $new = "tables.sql";
  12
  13 ## Read in exceptions and other metadata
  14 my %ok;
  15 while (<DATA>) {
  16         next unless /^(\w+)\s*:\s*([^#]+)/;
  17         my ($name,$val) = ($1,$2);
  18         chomp $val;
  19         if ($name eq 'RENAME') {
  20                 die "Invalid rename\n" unless $val =~ /(\w+)\s+(\w+)/;
  21                 $ok{OLD}{$1} = $2;
  22                 $ok{NEW}{$2} = $1;
  23                 next;
  24         }
  25         if ($name eq 'XFILE') {
  26                 push @old, $val;
  27                 next;
  28         }
  29         for (split(/\s+/ => $val)) {
  30                 $ok{$name}{$_} = 0;
  31         }
  32 }
  33
  34 open my $newfh, "<", $new or die qq{Could not open $new: $!\n};
  35
  36 my $datatype = join '|' => qw(
  37 bool
  38 tinyint int bigint real float
  39 tinytext mediumtext text char varchar
  40 timestamp datetime
  41 tinyblob mediumblob blob
  42 );
  43 $datatype .= q{|ENUM\([\"\w, ]+\)};
  44 $datatype = qr{($datatype)};
  45
  46 my $typeval = qr{(\(\d+\))?};
  47
  48 my $typeval2 = qr{ unsigned| binary| NOT NULL| NULL| auto_increment| default ['\-\d\w"]+| REFERENCES .+CASCADE};
  49
  50 my $indextype = join '|' => qw(INDEX KEY FULLTEXT), "PRIMARY KEY", "UNIQUE INDEX", "UNIQUE KEY";
  51 $indextype = qr{$indextype};
  52
  53 my $tabletype = qr{InnoDB|MyISAM|HEAP|HEAP MAX_ROWS=\d+};
  54
  55 my ($table,%old);
  56 for my $old (@old) {
  57         open my $oldfh, "<", $old or die qq{Could not open $old: $!\n};
  58
  59         while (<$oldfh>) {
  60                 next if /^\s*\-\-/ or /^\s+$/;
  61                 s/\s*\-\- [\w ]+$//;
  62                 chomp;
  63
  64                 if (/CREATE\s*TABLE/i) {
  65                         m{^CREATE TABLE /\*\$wgDBprefix\*/(\w+) \($}
  66                                 or die qq{Invalid CREATE TABLE at line $. of $old\n};
  67                         $table = $1;
  68                         $old{$table}{name}=$table;
  69                 }
  70                 elsif (/^\) TYPE=($tabletype);$/) {
  71                         $old{$table}{type}=$1;
  72                 }
  73                 elsif (/^  (\w+) $datatype$typeval$typeval2{0,3},?$/) {
  74                         $old{$table}{column}{$1} = $2;
  75                 }
  76                 elsif (/^  ($indextype)(?: (\w+))? \(([\w, \(\)]+)\),?$/) {
  77                         $old{$table}{lc $1."_name"} = $2 ? $2 : "";
  78                         $old{$table}{lc $1."pk_target"} = $3;
  79                 }
  80                 else {
  81                         die "Cannot parse line $. of $old:\n$_\n";
  82                 }
  83         }
  84         close $oldfh;
  85 }
  86
  87 $datatype = join '|' => qw(
  88 SMALLINT INTEGER BIGINT NUMERIC SERIAL
  89 TEXT CHAR VARCHAR
  90 BYTEA
  91 TIMESTAMPTZ
  92 CIDR
  93 );
  94 $datatype = qr{($datatype)};
  95 my %new;
  96 my ($infunction,$inview,$inrule) = (0,0,0);
  97 while (<$newfh>) {
  98         next if /^\s*\-\-/ or /^\s*$/;
  99         s/\s*\-\- [\w ']+$//;
 100         next if /^BEGIN;/ or /^SET / or /^COMMIT;/;
 101         next if /^CREATE SEQUENCE/;
 102         next if /^CREATE(?: UNIQUE)? INDEX/;
 103         next if /^CREATE FUNCTION/;
 104         next if /^CREATE TRIGGER/ or /^  FOR EACH ROW/;
 105         next if /^INSERT INTO/ or /^  VALUES \(/;
 106         next if /^ALTER TABLE/;
 107         chomp;
 108
 109         if (/^\$mw\$;?$/) {
 110                 $infunction = $infunction ? 0 : 1;
 111                 next;
 112         }
 113         next if $infunction;
 114
 115         next if /^CREATE VIEW/ and $inview = 1;
 116         if ($inview) {
 117                 /;$/ and $inview = 0;
 118                 next;
 119         }
 120
 121         next if /^CREATE RULE/ and $inrule = 1;
 122         if ($inrule) {
 123                 /;$/ and $inrule = 0;
 124                 next;
 125         }
 126
 127         if (/^CREATE TABLE "?(\w+)"? \($/) {
 128                 $table = $1;
 129                 $new{$table}{name}=$table;
 130         }
 131         elsif (/^\);$/) {
 132         }
 133         elsif (/^  (\w+) +$datatype/) {
 134                 $new{$table}{column}{$1} = $2;
 135         }
 136         else {
 137                 die "Cannot parse line $. of $new:\n$_\n";
 138         }
 139 }
 140 close $newfh;
 141
 142 ## Old but not new
 143 for my $t (sort keys %old) {
 144         if (!exists $new{$t} and !exists $ok{OLD}{$t}) {
 145                 print "Table not in $new: $t\n";
 146                 next;
 147         }
 148         next if exists $ok{OLD}{$t} and !$ok{OLD}{$t};
 149         my $newt = exists $ok{OLD}{$t} ? $ok{OLD}{$t} : $t;
 150         my $oldcol = $old{$t}{column};
 151         my $newcol = $new{$newt}{column};
 152         for my $c (keys %$oldcol) {
 153                 if (!exists $newcol->{$c}) {
 154                         print "Column $t.$c not in new\n";
 155                         next;
 156                 }
 157         }
 158         for my $c (keys %$newcol) {
 159                 if (!exists $oldcol->{$c}) {
 160                         print "Column $t.$c not in old\n";
 161                         next;
 162                 }
 163         }
 164 }
 165 ## New but not old:
 166 for (sort keys %new) {
 167         if (!exists $old{$_} and !exists $ok{NEW}{$_}) {
 168                 print "Not in old: $_\n";
 169                 next;
 170         }
 171 }
 172
 173 __DATA__
 174 ## Known exceptions
 175 OLD: searchindex          ## We use tsearch2 directly on the page table instead
 176 OLD: archive              ## This is a view due to the char(14) timestamp hack
 177 RENAME: user mwuser       ## Reserved word causing lots of problems
 178 RENAME: text pagecontent  ## Reserved word
 179 NEW: archive2             ## The real archive table
 180 NEW: mediawiki_version    ## Just us, for now
 181 XFILE: ../archives/patch-profiling.sql