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