support some older Pg when upgrading tax rates and cust_bill_pkg_details

2 files changed, 75 insertions, 0 deletions

diff --git a/FS/FS/cust_bill_pkg_detail.pm b/FS/FS/cust_bill_pkg_detail.pm
index f40ce5869..9a90936ed 100644
--- a/FS/FS/cust_bill_pkg_detail.pm
+++ b/FS/FS/cust_bill_pkg_detail.pm
@@ -148,6 +148,37 @@ sub _upgrade_data { # class method
 
         }
 
+      } elsif ( $dbh->{pg_server_version} =~ /^704/ ) {  # earlier?
+
+        # ideally this would be supported in DBIx-DBSchema and friends
+
+        #  XXX_FIXME better locking
+
+        foreach my $table ( qw( cust_bill_pkg_detail h_cust_bill_pkg_detail ) ){
+
+          warn "updating $table column classnum to integer\n" if $DEBUG;
+
+          my $sql = "ALTER TABLE $table RENAME classnum TO old_classnum";
+          my $sth = $dbh->prepare($sql) or die $dbh->errstr;
+          $sth->execute or die $sth->errstr;
+
+          my $def = dbdef->table($table)->column('classnum');
+          $def->type('integer');
+          $def->length(''); 
+          $sql = "ALTER TABLE $table ADD COLUMN ". $def->line($dbh);
+          $sth = $dbh->prepare($sql) or die $dbh->errstr;
+          $sth->execute or die $sth->errstr;
+
+          $sql = "UPDATE $table SET classnum = int4( text( old_classnum ) )";
+          $sth = $dbh->prepare($sql) or die $dbh->errstr;
+          $sth->execute or die $sth->errstr;
+
+          $sql = "ALTER TABLE $table DROP old_classnum";
+          $sth = $dbh->prepare($sql) or die $dbh->errstr;
+          $sth->execute or die $sth->errstr;
+
+        }
+
       } else {
 
         die "cust_bill_pkg_detail classnum upgrade unsupported for this Pg version\n";
diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm
index 2808c6ef8..9a25f947b 100644
--- a/FS/FS/tax_rate.pm
+++ b/FS/FS/tax_rate.pm
@@ -1664,6 +1664,50 @@ sub _upgrade_data {  # class method
         }
       }
 
+    } elsif ( $dbh->{pg_server_version} =~ /^704/ ) {
+
+      # ideally this would be supported in DBIx-DBSchema and friends
+
+      foreach my $column ( @column ) {
+        my $columndef = dbdef->table($self->table)->column($column);
+        unless ($columndef->type eq 'numeric') {
+
+          warn "updating tax_rate column $column to numeric\n" if $DEBUG;
+
+          foreach my $table ( qw( tax_rate h_tax_rate ) ) {
+
+            my $sql = "ALTER TABLE $table RENAME $column TO old_$column";
+            my $sth = $dbh->prepare($sql) or die $dbh->errstr;
+            $sth->execute or die $sth->errstr;
+
+            my $def = dbdef->table($table)->column($column);
+            $def->type('numeric');
+            $def->length('14,8'); 
+            my $null = $def->null;
+            $def->null('NULL');
+
+            $sql = "ALTER TABLE $table ADD COLUMN ". $def->line($dbh);
+            $sth = $dbh->prepare($sql) or die $dbh->errstr;
+            $sth->execute or die $sth->errstr;
+
+            $sql = "UPDATE $table SET $column = CAST( old_$column AS numeric )";
+            $sth = $dbh->prepare($sql) or die $dbh->errstr;
+            $sth->execute or die $sth->errstr;
+
+            unless ( $null eq 'NULL' ) {
+              $sql = "ALTER TABLE $table ALTER $column SET NOT NULL";
+              $sth = $dbh->prepare($sql) or die $dbh->errstr;
+              $sth->execute or die $sth->errstr;
+            }
+
+            $sql = "ALTER TABLE $table DROP old_$column";
+            $sth = $dbh->prepare($sql) or die $dbh->errstr;
+            $sth->execute or die $sth->errstr;
+
+          }
+        }
+      }
+
     } else {
 
       warn "WARNING: tax_rate table upgrade unsupported for this Pg version\n";