Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 41276623

Von Niclas Zimmermann vor mehr als 11 Jahren hinzugefügt

  • ID 41276623722df9317163274551400fc42a2722e0
  • Vorgänger 014161b8
  • Nachfolger 88cddb02

Anpassung Upgrade-Script an neue Synthax

Passt zwei alte Upgrade-Scripte an die neue Synthax an.

Unterschiede anzeigen:

sql/Pg-upgrade2/currencies.pl
1 1 
# @tag: currencies
2 2 
# @description: Erstellt neue Tabelle currencies. Währungen können dann einfacher eingegeben und unkritisch geändert werden.
3 3 
# @depends: release_3_0_0 rm_whitespaces
4 
# @charset: utf-8
4 

  		




  
  5
  
    
package SL::DBUpgrade2::currencies;


  		




  5
  6
  
    

  		




  6
  7
  
    
use utf8;


  		




  7
  8
  
    
use strict;


  		




  8
  9
  
    

  		




  9
  
  
    
die("This script cannot be run from the command line.") unless ($main::form);


  		




  10
  
  
    

  		




  11
  
  
    
sub mydberror {


  		




  12
  
  
    
  my ($msg) = @_;


  		




  13
  
  
    
  die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr);


  		




  14
  
  
    
}


  		




  15
  
  
    

  		




  16
  
  
    
sub do_query {


  		




  17
  
  
    
  my ($query, $may_fail) = @_;


  		




  18
  
  
    

  		




  19
  
  
    
  if (!$dbh->do($query)) {


  		




  20
  
  
    
    mydberror($query) unless ($may_fail);


  		




  21
  
  
    
    $dbh->rollback();


  		




  22
  
  
    
    $dbh->begin_work();


  		




  23
  
  
    
  }


  		




  24
  
  
    
}


  		




  
  10
  
    
use parent qw(SL::DBUpgrade2::Base);


  		




  25
  11
  
    

  		




  26
  12
  
    

  		




  27
  
  
    
sub do_update {


  		




  
  13
  
    
sub run {


  		




  
  14
  
    
  my ($self) = @_;


  		




  28
  15
  
    
  #Check wheather default currency exists


  		




  29
  16
  
    
  my $query = qq|SELECT curr FROM defaults|;


  		




  30
  
  
    
  my ($currencies) = $dbh->selectrow_array($query);


  		




  
  17
  
    
  my ($currencies) = $self->dbh->selectrow_array($query);


  		




  31
  18
  
    

  		




  32
  19
  
    
  if (length($currencies) == 0 and length($main::form->{defaultcurrency}) == 0){


  		




  33
  20
  
    
    print_no_default_currency();


  		




  ......




  53
  40
  
    
              UNION


  		




  54
  41
  
    
              SELECT DISTINCT curr FROM vendor|;


  		




  55
  42
  
    

  		




  56
  
  
    
  my $sth = $dbh->prepare($query);


  		




  
  43
  
    
  my $sth = $self->dbh->prepare($query);


  		




  57
  44
  
    
  $sth->execute || $self->dberror($query);


  		




  58
  45
  
    

  		




  59
  46
  
    
  $main::form->{ORPHANED_CURRENCIES} = [];


  		




  ......




  88
  75
  
    

  		




  89
  76
  
    
  if ($main::form->{continue_options} eq 'insert') {


  		




  90
  77
  
    
    for my $i (0..($rowcount-1)){


  		




  91
  
  
    
      push @currency_array, $form->{"curr_$i"};


  		




  
  78
  
    
      push @currency_array, $main::form->{"curr_$i"};


  		




  92
  79
  
    
    }


  		




  93
  
  
    
    create_and_fill_table(@currency_array);


  		




  
  80
  
    
    create_and_fill_table($self, @currency_array);


  		




  94
  81
  
    
    return 1;


  		




  95
  82
  
    
  }


  		




  96
  83
  
    

  		




  ......




  102
  89
  
    
        if ($main::form->{"curr_$i"} eq $item){


  		




  103
  90
  
    
          $still_orphaned = 0;


  		




  104
  91
  
    
          $query = qq|DELETE FROM exchangerate WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  105
  
  
    
          do_query($query);


  		




  
  92
  
    
          $self->db_query($query);


  		




  106
  93
  
    
          $query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  107
  
  
    
          do_query($query);


  		




  
  94
  
    
          $self->db_query($query);


  		




  108
  95
  
    
          $query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  109
  
  
    
          do_query($query);


  		




  
  96
  
    
          $self->db_query($query);


  		




  110
  97
  
    
          $query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  111
  
  
    
          do_query($query);


  		




  
  98
  
    
          $self->db_query($query);


  		




  112
  99
  
    
          $query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  113
  
  
    
          do_query($query);


  		




  
  100
  
    
          $self->db_query($query);


  		




  114
  101
  
    
          $query = qq|UPDATE delivery_orders SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  115
  
  
    
          do_query($query);


  		




  
  102
  
    
          $self->db_query($query);


  		




  116
  103
  
    
          $query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|;


  		




  117
  
  
    
          do_query($query);


  		




  
  104
  
    
          $self->db_query($query);


  		




  118
  105
  
    
          last;


  		




  119
  106
  
    
        }


  		




  120
  107
  
    
      }


  		




  ......




  123
  110
  
    
        return do_update();


  		




  124
  111
  
    
      }


  		




  125
  112
  
    
    }


  		




  126
  
  
    
    create_and_fill_table(@currency_array);


  		




  
  113
  
    
    create_and_fill_table($self, @currency_array);


  		




  127
  114
  
    
    return 1;


  		




  128
  115
  
    
  }


  		




  129
  116
  
    

  		




  130
  117
  
    
  #No orphaned currencies, so create table:


  		




  131
  
  
    
  create_and_fill_table(@currency_array);


  		




  
  118
  
    
  create_and_fill_table($self, @currency_array);


  		




  132
  119
  
    
  return 1;


  		




  133
  120
  
    
}; # end do_update


  		




  134
  121
  
    

  		




  135
  122
  
    
sub create_and_fill_table {


  		




  
  123
  
    
  my $self = shift;


  		




  136
  124
  
    
  #Create an fill table currencies:


  		




  137
  125
  
    
  my $query = qq|CREATE TABLE currencies (id INTEGER DEFAULT nextval(('id'::text)::regclass) UNIQUE NOT NULL, curr TEXT PRIMARY KEY)|;


  		




  138
  
  
    
  do_query($query);


  		




  
  126
  
    
  $self->db_query($query);


  		




  139
  127
  
    
  foreach my $item ( @_ ) {


  		




  140
  128
  
    
    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|;


  		




  141
  
  
    
    do_query($query);


  		




  
  129
  
    
    $self->db_query($query);


  		




  142
  130
  
    
  }


  		




  143
  131
  
    

  		




  144
  132
  
    
  #Set default currency if no currency was chosen:


  		




  ......




  149
  137
  
    
  $query .= qq|UPDATE delivery_orders SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;


  		




  150
  138
  
    
  $query .= qq|UPDATE vendor SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|;


  		




  151
  139
  
    
  $query .= qq|DELETE FROM exchangerate WHERE curr IS NULL or curr='';|;


  		




  152
  
  
    
  do_query($query);


  		




  
  140
  
    
  $self->db_query($query);


  		




  153
  141
  
    

  		




  154
  142
  
    
  #Check wheather defaultcurrency is already in table currencies:


  		




  155
  143
  
    
  $query = qq|SELECT curr FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|'|;


  		




  156
  
  
    
  my ($insert_default) = $dbh->selectrow_array($query);


  		




  
  144
  
    
  my ($insert_default) = $self->dbh->selectrow_array($query);


  		




  157
  145
  
    

  		




  158
  146
  
    
  if (!$insert_default) {


  		




  159
  147
  
    
    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $main::form->{defaultcurrency} . qq|')|;


  		




  160
  
  
    
    do_query($query);


  		




  
  148
  
    
    $self->db_query($query);


  		




  161
  149
  
    
  }


  		




  162
  150
  
    

  		




  163
  151
  
    
  #Create a new columns currency and update with curr.id:


  		




  ......




  169
  157
  
    
              ALTER TABLE exchangerate ADD currency INTEGER;


  		




  170
  158
  
    
              ALTER TABLE vendor ADD currency INTEGER;


  		




  171
  159
  
    
              ALTER TABLE defaults ADD currency INTEGER;|;


  		




  172
  
  
    
  do_query($query);


  		




  
  160
  
    
  $self->db_query($query);


  		




  173
  161
  
    
  #Set defaultcurrency:


  		




  174
  162
  
    
  $query = qq|UPDATE defaults SET currency= (SELECT id FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|')|;


  		




  175
  
  
    
  do_query($query);


  		




  
  163
  
    
  $self->db_query($query);


  		




  176
  164
  
    
  $query = qq|UPDATE ap SET currency = (SELECT id FROM currencies c WHERE c.curr = ap.curr);


  		




  177
  165
  
    
              UPDATE ar SET currency = (SELECT id FROM currencies c WHERE c.curr = ar.curr);


  		




  178
  166
  
    
              UPDATE oe SET currency = (SELECT id FROM currencies c WHERE c.curr = oe.curr);


  		




  ......




  180
  168
  
    
              UPDATE delivery_orders SET currency = (SELECT id FROM currencies c WHERE c.curr = delivery_orders.curr);


  		




  181
  169
  
    
              UPDATE exchangerate SET currency = (SELECT id FROM currencies c WHERE c.curr = exchangerate.curr);


  		




  182
  170
  
    
              UPDATE vendor SET currency = (SELECT id FROM currencies c WHERE c.curr = vendor.curr);|;


  		




  183
  
  
    
  do_query($query);


  		




  
  171
  
    
  $self->db_query($query);


  		




  184
  172
  
    

  		




  185
  173
  
    
  #Drop column 'curr':


  		




  186
  174
  
    
  $query = qq|ALTER TABLE ap DROP COLUMN curr;


  		




  ......




  191
  179
  
    
              ALTER TABLE exchangerate DROP COLUMN curr;


  		




  192
  180
  
    
              ALTER TABLE vendor DROP COLUMN curr;


  		




  193
  181
  
    
              ALTER TABLE defaults DROP COLUMN curr;|;


  		




  194
  
  
    
  do_query($query);


  		




  
  182
  
    
  $self->db_query($query);


  		




  195
  183
  
    

  		




  196
  184
  
    
  #Rename currency to curr:


  		




  197
  185
  
    
  $query = qq|ALTER TABLE defaults RENAME COLUMN currency TO curr;


  		




  ......




  202
  190
  
    
              ALTER TABLE delivery_orders RENAME COLUMN currency TO curr;


  		




  203
  191
  
    
              ALTER TABLE exchangerate RENAME COLUMN currency TO curr;


  		




  204
  192
  
    
              ALTER TABLE vendor RENAME COLUMN currency TO curr;|;


  		




  205
  
  
    
  do_query($query);


  		




  
  193
  
    
  $self->db_query($query);


  		




  206
  194
  
    

  		




  207
  195
  
    
  #Set NOT NULL constraints:


  		




  208
  196
  
    
  $query = qq|ALTER TABLE ap ALTER COLUMN curr SET NOT NULL;


  		




  ......




  213
  201
  
    
              ALTER TABLE exchangerate ALTER COLUMN curr SET NOT NULL;


  		




  214
  202
  
    
              ALTER TABLE vendor ALTER COLUMN curr SET NOT NULL;


  		




  215
  203
  
    
              ALTER TABLE defaults ALTER COLUMN curr SET NOT NULL;|;


  		




  216
  
  
    
  do_query($query);


  		




  
  204
  
    
  $self->db_query($query);


  		




  217
  205
  
    

  		




  218
  206
  
    
  #Set foreign keys:


  		




  219
  207
  
    
  $query = qq|ALTER TABLE ap ADD FOREIGN KEY (curr) REFERENCES currencies(id);


  		




  ......




  224
  212
  
    
              ALTER TABLE exchangerate ADD FOREIGN KEY (curr) REFERENCES currencies(id);


  		




  225
  213
  
    
              ALTER TABLE vendor ADD FOREIGN KEY (curr) REFERENCES currencies(id);


  		




  226
  214
  
    
              ALTER TABLE defaults ADD FOREIGN KEY (curr) REFERENCES currencies(id);|;


  		




  227
  
  
    
  do_query($query);


  		




  
  215
  
    
  $self->db_query($query);


  		




  228
  216
  
    

  		




  229
  217
  
    
};


  		




  230
  218
  
    

  		




  ......




  236
  224
  
    
  print $main::form->parse_html_template("dbupgrade/orphaned_currencies");


  		




  237
  225
  
    
};


  		




  238
  226
  
    

  		




  239
  
  
    
return do_update();


  		




  
  227
  
    
1;


  		












Auch abrufbar als:  Unified diff