Compare/Pg.pm untested

filter: Statementstmt 0.0% 0 / 100 Branchbran 0.0% 0 / 16 Conditioncond 0.0% 0 / 3 MC/DCmcdc 0.0% 0 / 2 Subroutinesub 0.0% 0 / 11 Podpod 0.0% 0 / 7 totaltotal 0.0% 0 / 139 CC 9 SCAR 31.6
CC
9
Cov
0%
deep_compare
56.0
get_tables
12.0
compare_string_field
2.0
CRAP 23.5
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__