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