| 1 | package DBIx::Compare::Pg; | ||
| 2 | |||
| 3 | use 5.006; | ||
| 4 | use strict; | ||
| 5 | use warnings; | ||
| 6 | require DBIx::Compare; | ||
| 7 | |||
| 8 | our $VERSION = '1.4'; | ||
| 9 | |||
| 10 | { package pg_comparison; | ||
| 11 | |||
| 12 | our @ISA = qw( db_comparison ); | ||
| 13 | |||
| 14 | sub compare_string_field { | ||
| 15 | my ($self,$table,$field) = @_; | ||
| 16 | my $statement = " | ||
| 17 | SELECT AVG(OCTET_LENGTH($field)), STDDEV(OCTET_LENGTH($field)), MIN(OCTET_LENGTH($field)), MAX(OCTET_LENGTH($field)) | ||
| 18 | FROM $table | ||
| 19 | "; | ||
| 20 | return $self->do_compare_field($statement); | ||
| 21 | } | ||
| 22 | sub compare_numeric_field { | ||
| 23 | my ($self,$table,$field) = @_; | ||
| 24 | my $statement = " | ||
| 25 | SELECT AVG($field), STDDEV($field), MIN($field), MAX($field) | ||
| 26 | FROM $table | ||
| 27 | "; | ||
| 28 | return $self->do_compare_field($statement); | ||
| 29 | } | ||
| 30 | sub compare_datetime_field { | ||
| 31 | my ($self,$table,$field) = @_; | ||
| 32 | my $statement = " | ||
| 33 | SELECT AVG(DATE_PART('epoch', $field)::numeric), STDDEV(DATE_PART('epoch', $field)::numeric), MIN(DATE_PART('epoch', $field)::numeric), MAX(DATE_PART('epoch', $field)::numeric) | ||
| 34 | FROM $table | ||
| 35 | "; | ||
| 36 | return $self->do_compare_field($statement); | ||
| 37 | } | ||
| 38 | # implement SHOW TABLES but avoid system schema, slony ancillary ones | ||
| 39 | # and the information schema | ||
| 40 | sub get_tables { | ||
| 41 | my $self = shift; | ||
| 42 | unless (defined $self->{ _db1 }{ _Tables } && $self->{ _db2 }{ _Tables }){ | ||
| 43 | my ($dbh1,$dbh2) = $self->get_dbh; | ||
| 44 | my $statement = " | ||
| 45 | SELECT schemaname||'.'||tablename | ||
| 46 | FROM pg_catalog.pg_tables | ||
| 47 | WHERE substr(schemaname,1,3) != 'pg_' | ||
| 48 | AND substr(schemaname,1,1) != '_' | ||
| 49 | AND schemaname != 'information_schema' | ||
| 50 | ORDER BY schemaname, tablename | ||
| 51 | "; | ||
| 52 | $self->{ _db1 }{ _Tables } = $self->fetch_multisinglefield($statement,$dbh1); | ||
| 53 | $self->{ _db2 }{ _Tables } = $self->fetch_multisinglefield($statement,$dbh2); | ||
| 54 | } | ||
| 55 | if (wantarray()){ | ||
| 56 | return ( $self->{ _db1 }{ _Tables },$self->{ _db2 }{ _Tables } ); | ||
| 57 | } else { | ||
| 58 | return $self->{ _db1 }{ _Tables }; | ||
| 59 | } | ||
| 60 | } | ||
| 61 | # Separate schema and table name for primary_key_info call. | ||
| 62 | sub set_primary_keys { | ||
| 63 | my ($self,$table,$dbh) = @_; | ||
| 64 | my $db_name = $dbh->{ Name }; | ||
| 65 | my ($schema, $tableshort) = split(/\./, $table); | ||
| 66 | my @aKeys = $dbh->primary_key(undef, $schema, $tableshort ); | ||
| 67 | |||
| 68 | $self->{ $db_name }{ $table }{ _primary_keys } = \@aKeys; | ||
| 69 | } | ||
| 70 | # implement DESCRIBE | ||
| 71 | sub set_field_info { | ||
| 72 | my ($self,$table) = @_; | ||
| 73 | my @aDBH = $self->get_dbh; | ||
| 74 | my ($schema, $tableshort) = split(/\./, $table); | ||
| 75 | for my $dbh (@aDBH){ | ||
| 76 | my $db_name = $dbh->{ Name }; | ||
| 77 | my $sth = $dbh->column_info(undef, $schema, $tableshort, undef); | ||
| 78 | |||
| 79 | my @aCols = @{$sth->fetchall_arrayref}; | ||
| 80 | my @aFields = (); | ||
| 81 | for my $aRef (@aCols) { | ||
| 82 | my $field = @$aRef[3]; | ||
| 83 | my $type = @$aRef[5]; | ||
| 84 | push (@aFields, $field); | ||
| 85 | $self->{ $db_name }{ $table }{ Fields }{ $field } = $type; | ||
| 86 | } | ||
| 87 | my @aSorted_Fields = sort @aFields; | ||
| 88 | $self->{ $db_name }{ $table }{ Sorted_Fields } = \@aSorted_Fields; | ||
| 89 | } | ||
| 90 | } | ||
| 91 | # do the same logic as super, but explicitly use a CURSOR to avoid libpq | ||
| 92 | # trying to buffer both tables in memory (a huge trap for the unwary I must | ||
| 93 | # say) | ||
| 94 | sub deep_compare { | ||
| 95 | my $self = shift; | ||
| 96 | |||
| 97 | my ($dbh1,$dbh2) = $self->get_dbh; | ||
| 98 | my $same = 1; | ||
| 99 | |||
| 100 | my @aTables; | ||
| 101 | if (@_){ | ||
| 102 | @aTables = @_; | ||
| 103 | } else { | ||
| 104 | $same = $self->compare; # sets minimal similar tables | ||
| 105 | warn "Only running deep_compare() on similar tables\n" unless ($same); | ||
| 106 | @aTables = @{ $self->similar_tables }; | ||
| 107 | } | ||
| 108 | |||
| 109 | TABLE:for my $table (@aTables){ | ||
| 110 | my $primary_key = $self->get_primary_keys($table,$dbh1); | ||
| 111 | # recursively calls compare_field_lists() and common_tables() | ||
| 112 | # if relevant $self fields are not already filled | ||
| 113 | my $fields = $self->field_list($table); # common fields | ||
| 114 | my $statement = "select $fields from $table order by $primary_key"; | ||
| 115 | |||
| 116 | # declare cursor for each table | ||
| 117 | $dbh1->do("DECLARE cur CURSOR WITH HOLD FOR $statement"); | ||
| 118 | $dbh2->do("DECLARE cur CURSOR WITH HOLD FOR $statement"); | ||
| 119 | |||
| 120 | my $sth1 = $dbh1->prepare("FETCH 100 FROM cur"); | ||
| 121 | my $sth2 = $dbh2->prepare("FETCH 100 FROM cur"); | ||
| 122 | |||
| 123 | my $row = 0; | ||
| 124 | |||
| 125 | # fetch each row. | ||
| 126 | ROW:while(1) { | ||
| 127 | |||
| 128 | $sth1->execute; | ||
| 129 | $sth2->execute; | ||
| 130 | last if ($sth1->rows == 0); | ||
| 131 | |||
| 132 | while (my $aResult_Row1 = $sth1->fetchrow_arrayref()) { | ||
| 133 | $row++; | ||
| 134 | my $aResult_Row2 = $sth2->fetchrow_arrayref(); | ||
| 135 | my @aResult_Row1 = map { $_ ? $_ : '' } @$aResult_Row1; | ||
| 136 | my @aResult_Row2 = map { $_ ? $_ : '' } @$aResult_Row2; | ||
| 137 | unless (join(',',@aResult_Row1) eq join(',',@aResult_Row2)){ | ||
| 138 | warn "Discrepancy in table '$table' at row $row\n"; | ||
| 139 | $self->add_errors("Discrepancy in table $table",$row); | ||
| 140 | $same = undef; | ||
| 141 | $sth1->finish; # finish with statements | ||
| 142 | $sth2->finish; | ||
| 143 | $dbh1->do("CLOSE cur"); # ...and cursors | ||
| 144 | $dbh2->do("CLOSE cur"); | ||
| 145 | next TABLE; | ||
| 146 | } | ||
| 147 | } | ||
| 148 | |||
| 149 | } | ||
| 150 | $sth1->finish; # finish with statements | ||
| 151 | $sth2->finish; | ||
| 152 | |||
| 153 | $dbh1->do("CLOSE cur"); | ||
| 154 | $dbh2->do("CLOSE cur"); | ||
| 155 | } | ||
| 156 | return $same; | ||
| 157 | } | ||
| 158 | } | ||
| 159 | |||
| 160 | 1; | ||
| 161 | |||
| 162 | __END__ |