Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision a4d74009

Von Niclas Zimmermann vor mehr als 11 Jahren hinzugefügt

  • ID a4d740099476b4383a40a21ab5472e75806f3f4f
  • Vorgänger 714a28b1
  • Nachfolger 96dbc076

Verbesserungen in Upgrade-Scripten

In den Scripten currencies.pl rm_whitespaces wurden einige Ver-
besserungen durchgeführt:
1. Umbenennung von Spalten curr in currency_id in allen Tabellen
außer currencies.
2. Umbenennung von Spalte curr in name in Tabelle currencies.
3. Update in rm_whitespaces wird jetzt in einer Schleife erledigt
anstatt für jede Tabelle eigenen Code zu schreiben.
4. Tabelle currencies hat jetzt id als Primärschlüssel und nicht
mehr curr.

Die oben aufgeführten Änderungen führen natürlich auch zu notwendigen
Änderungen im Programm, welche dieses Commit ebenfalls enthält.

Unterschiede anzeigen:

sql/Pg-upgrade2/currencies.pl
122 122 
sub create_and_fill_table {
123 123 
  my $self = shift;
124 124 
  #Create an fill table currencies:
125 
  my $query = qq|CREATE TABLE currencies (id INTEGER DEFAULT nextval(('id'::text)::regclass) UNIQUE NOT NULL, curr TEXT PRIMARY KEY)|;
125 
  my $query = qq|CREATE TABLE currencies (id   SERIAL        PRIMARY KEY,
126 
                                          name TEXT NOT NULL UNIQUE)|;
126 127 
  $self->db_query($query);
127 128 
  foreach my $item ( @_ ) {
128 
    $query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|;
129 
    $query = qq|INSERT INTO currencies (name) VALUES ('| . $item . qq|')|;
129 130 
    $self->db_query($query);
130 131 
  }
131 132 

  		




  ......




  148
  149
  
    
    $self->db_query($query);


  		




  149
  150
  
    
  }


  		




  150
  151
  
    

  		




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


  		




  152
  
  
    
  $query = qq|ALTER TABLE ap ADD currency INTEGER;


  		




  153
  
  
    
              ALTER TABLE ar ADD currency INTEGER;


  		




  154
  
  
    
              ALTER TABLE oe ADD currency INTEGER;


  		




  155
  
  
    
              ALTER TABLE customer ADD currency INTEGER;


  		




  156
  
  
    
              ALTER TABLE delivery_orders ADD currency INTEGER;


  		




  157
  
  
    
              ALTER TABLE exchangerate ADD currency INTEGER;


  		




  158
  
  
    
              ALTER TABLE vendor ADD currency INTEGER;


  		




  159
  
  
    
              ALTER TABLE defaults ADD currency INTEGER;|;


  		




  
  152
  
    
  #Create a new columns currency_id and update with curr.id:


  		




  
  153
  
    
  $query = qq|ALTER TABLE ap ADD currency_id INTEGER;


  		




  
  154
  
    
              ALTER TABLE ar ADD currency_id INTEGER;


  		




  
  155
  
    
              ALTER TABLE oe ADD currency_id INTEGER;


  		




  
  156
  
    
              ALTER TABLE customer ADD currency_id INTEGER;


  		




  
  157
  
    
              ALTER TABLE delivery_orders ADD currency_id INTEGER;


  		




  
  158
  
    
              ALTER TABLE exchangerate ADD currency_id INTEGER;


  		




  
  159
  
    
              ALTER TABLE vendor ADD currency_id INTEGER;


  		




  
  160
  
    
              ALTER TABLE defaults ADD currency_id INTEGER;|;


  		




  160
  161
  
    
  $self->db_query($query);


  		




  161
  162
  
    
  #Set defaultcurrency:


  		




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


  		




  
  163
  
    
  $query = qq|UPDATE defaults SET currency_id= (SELECT id FROM currencies WHERE name = '| . $main::form->{defaultcurrency} . qq|')|;


  		




  163
  164
  
    
  $self->db_query($query);


  		




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


  		




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


  		




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


  		




  167
  
  
    
              UPDATE customer SET currency = (SELECT id FROM currencies c WHERE c.curr = customer.curr);


  		




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


  		




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


  		




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


  		




  
  165
  
    
  $query = qq|UPDATE ap SET currency_id = (SELECT id FROM currencies c WHERE c.name = ap.curr);


  		




  
  166
  
    
              UPDATE ar SET currency_id = (SELECT id FROM currencies c WHERE c.name = ar.curr);


  		




  
  167
  
    
              UPDATE oe SET currency_id = (SELECT id FROM currencies c WHERE c.name = oe.curr);


  		




  
  168
  
    
              UPDATE customer SET currency_id = (SELECT id FROM currencies c WHERE c.name = customer.curr);


  		




  
  169
  
    
              UPDATE delivery_orders SET currency_id = (SELECT id FROM currencies c WHERE c.name = delivery_orders.curr);


  		




  
  170
  
    
              UPDATE exchangerate SET currency_id = (SELECT id FROM currencies c WHERE c.name = exchangerate.curr);


  		




  
  171
  
    
              UPDATE vendor SET currency_id = (SELECT id FROM currencies c WHERE c.name = vendor.curr);|;


  		




  171
  172
  
    
  $self->db_query($query);


  		




  172
  173
  
    

  		




  173
  174
  
    
  #Drop column 'curr':


  		




  ......




  181
  182
  
    
              ALTER TABLE defaults DROP COLUMN curr;|;


  		




  182
  183
  
    
  $self->db_query($query);


  		




  183
  184
  
    

  		




  184
  
  
    
  #Rename currency to curr:


  		




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


  		




  186
  
  
    
              ALTER TABLE ap RENAME COLUMN currency TO curr;


  		




  187
  
  
    
              ALTER TABLE ar RENAME COLUMN currency TO curr;


  		




  188
  
  
    
              ALTER TABLE oe RENAME COLUMN currency TO curr;


  		




  189
  
  
    
              ALTER TABLE customer RENAME COLUMN currency TO curr;


  		




  190
  
  
    
              ALTER TABLE delivery_orders RENAME COLUMN currency TO curr;


  		




  191
  
  
    
              ALTER TABLE exchangerate RENAME COLUMN currency TO curr;


  		




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


  		




  193
  
  
    
  $self->db_query($query);


  		




  194
  
  
    

  		




  195
  185
  
    
  #Set NOT NULL constraints:


  		




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


  		




  197
  
  
    
              ALTER TABLE ar ALTER COLUMN curr SET NOT NULL;


  		




  198
  
  
    
              ALTER TABLE oe ALTER COLUMN curr SET NOT NULL;


  		




  199
  
  
    
              ALTER TABLE customer ALTER COLUMN curr SET NOT NULL;


  		




  200
  
  
    
              ALTER TABLE delivery_orders ALTER COLUMN curr SET NOT NULL;


  		




  201
  
  
    
              ALTER TABLE exchangerate ALTER COLUMN curr SET NOT NULL;


  		




  202
  
  
    
              ALTER TABLE vendor ALTER COLUMN curr SET NOT NULL;


  		




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


  		




  
  186
  
    
  $query = qq|ALTER TABLE ap ALTER COLUMN currency_id SET NOT NULL;


  		




  
  187
  
    
              ALTER TABLE ar ALTER COLUMN currency_id SET NOT NULL;


  		




  
  188
  
    
              ALTER TABLE oe ALTER COLUMN currency_id SET NOT NULL;


  		




  
  189
  
    
              ALTER TABLE customer ALTER COLUMN currency_id SET NOT NULL;


  		




  
  190
  
    
              ALTER TABLE delivery_orders ALTER COLUMN currency_id SET NOT NULL;


  		




  
  191
  
    
              ALTER TABLE exchangerate ALTER COLUMN currency_id SET NOT NULL;


  		




  
  192
  
    
              ALTER TABLE vendor ALTER COLUMN currency_id SET NOT NULL;


  		




  
  193
  
    
              ALTER TABLE defaults ALTER COLUMN currency_id SET NOT NULL;|;


  		




  204
  194
  
    
  $self->db_query($query);


  		




  205
  195
  
    

  		




  206
  196
  
    
  #Set foreign keys:


  		




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


  		




  208
  
  
    
              ALTER TABLE ar ADD FOREIGN KEY (curr) REFERENCES currencies(id);


  		




  209
  
  
    
              ALTER TABLE oe ADD FOREIGN KEY (curr) REFERENCES currencies(id);


  		




  210
  
  
    
              ALTER TABLE customer ADD FOREIGN KEY (curr) REFERENCES currencies(id);


  		




  211
  
  
    
              ALTER TABLE delivery_orders ADD FOREIGN KEY (curr) REFERENCES currencies(id);


  		




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


  		




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


  		




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


  		




  
  197
  
    
  $query = qq|ALTER TABLE ap ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);


  		




  
  198
  
    
              ALTER TABLE ar ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);


  		




  
  199
  
    
              ALTER TABLE oe ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);


  		




  
  200
  
    
              ALTER TABLE customer ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);


  		




  
  201
  
    
              ALTER TABLE delivery_orders ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);


  		




  
  202
  
    
              ALTER TABLE exchangerate ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);


  		




  
  203
  
    
              ALTER TABLE vendor ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);


  		




  
  204
  
    
              ALTER TABLE defaults ADD FOREIGN KEY (currency_id) REFERENCES currencies(id);|;


  		




  215
  205
  
    
  $self->db_query($query);


  		




  216
  206
  
    

  		




  217
  207
  
    
};


  		












Auch abrufbar als:  Unified diff