From 41276623722df9317163274551400fc42a2722e0 Mon Sep 17 00:00:00 2001 From: Niclas Zimmermann Date: Thu, 2 May 2013 12:40:28 +0200 Subject: [PATCH] Anpassung Upgrade-Script an neue Synthax Passt zwei alte Upgrade-Scripte an die neue Synthax an. --- sql/Pg-upgrade2/currencies.pl | 76 +++++++++++++------------------ sql/Pg-upgrade2/rm_whitespaces.pl | 46 +++++++------------ 2 files changed, 48 insertions(+), 74 deletions(-) diff --git a/sql/Pg-upgrade2/currencies.pl b/sql/Pg-upgrade2/currencies.pl index 2497be983..a2c498ec0 100644 --- a/sql/Pg-upgrade2/currencies.pl +++ b/sql/Pg-upgrade2/currencies.pl @@ -1,33 +1,20 @@ # @tag: currencies # @description: Erstellt neue Tabelle currencies. Währungen können dann einfacher eingegeben und unkritisch geändert werden. # @depends: release_3_0_0 rm_whitespaces -# @charset: utf-8 + +package SL::DBUpgrade2::currencies; use utf8; use strict; -die("This script cannot be run from the command line.") unless ($main::form); - -sub mydberror { - my ($msg) = @_; - die($dbup_locale->text("Database update error:") . "
$msg
" . $DBI::errstr); -} - -sub do_query { - my ($query, $may_fail) = @_; - - if (!$dbh->do($query)) { - mydberror($query) unless ($may_fail); - $dbh->rollback(); - $dbh->begin_work(); - } -} +use parent qw(SL::DBUpgrade2::Base); -sub do_update { +sub run { + my ($self) = @_; #Check wheather default currency exists my $query = qq|SELECT curr FROM defaults|; - my ($currencies) = $dbh->selectrow_array($query); + my ($currencies) = $self->dbh->selectrow_array($query); if (length($currencies) == 0 and length($main::form->{defaultcurrency}) == 0){ print_no_default_currency(); @@ -53,7 +40,7 @@ sub do_update { UNION SELECT DISTINCT curr FROM vendor|; - my $sth = $dbh->prepare($query); + my $sth = $self->dbh->prepare($query); $sth->execute || $self->dberror($query); $main::form->{ORPHANED_CURRENCIES} = []; @@ -88,9 +75,9 @@ sub do_update { if ($main::form->{continue_options} eq 'insert') { for my $i (0..($rowcount-1)){ - push @currency_array, $form->{"curr_$i"}; + push @currency_array, $main::form->{"curr_$i"}; } - create_and_fill_table(@currency_array); + create_and_fill_table($self, @currency_array); return 1; } @@ -102,19 +89,19 @@ sub do_update { if ($main::form->{"curr_$i"} eq $item){ $still_orphaned = 0; $query = qq|DELETE FROM exchangerate WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE ap SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE ar SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE oe SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE customer SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE delivery_orders SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE vendor SET curr = '| . $main::form->{"curr_$i"} . qq|' WHERE curr = '| . $main::form->{"old_curr_$i"} . qq|'|; - do_query($query); + $self->db_query($query); last; } } @@ -123,22 +110,23 @@ sub do_update { return do_update(); } } - create_and_fill_table(@currency_array); + create_and_fill_table($self, @currency_array); return 1; } #No orphaned currencies, so create table: - create_and_fill_table(@currency_array); + create_and_fill_table($self, @currency_array); return 1; }; # end do_update sub create_and_fill_table { + my $self = shift; #Create an fill table currencies: my $query = qq|CREATE TABLE currencies (id INTEGER DEFAULT nextval(('id'::text)::regclass) UNIQUE NOT NULL, curr TEXT PRIMARY KEY)|; - do_query($query); + $self->db_query($query); foreach my $item ( @_ ) { $query = qq|INSERT INTO currencies (curr) VALUES ('| . $item . qq|')|; - do_query($query); + $self->db_query($query); } #Set default currency if no currency was chosen: @@ -149,15 +137,15 @@ sub create_and_fill_table { $query .= qq|UPDATE delivery_orders SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|; $query .= qq|UPDATE vendor SET curr = '| . $main::form->{"defaultcurrency"} . qq|' WHERE curr IS NULL or curr='';|; $query .= qq|DELETE FROM exchangerate WHERE curr IS NULL or curr='';|; - do_query($query); + $self->db_query($query); #Check wheather defaultcurrency is already in table currencies: $query = qq|SELECT curr FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|'|; - my ($insert_default) = $dbh->selectrow_array($query); + my ($insert_default) = $self->dbh->selectrow_array($query); if (!$insert_default) { $query = qq|INSERT INTO currencies (curr) VALUES ('| . $main::form->{defaultcurrency} . qq|')|; - do_query($query); + $self->db_query($query); } #Create a new columns currency and update with curr.id: @@ -169,10 +157,10 @@ sub create_and_fill_table { ALTER TABLE exchangerate ADD currency INTEGER; ALTER TABLE vendor ADD currency INTEGER; ALTER TABLE defaults ADD currency INTEGER;|; - do_query($query); + $self->db_query($query); #Set defaultcurrency: $query = qq|UPDATE defaults SET currency= (SELECT id FROM currencies WHERE curr = '| . $main::form->{defaultcurrency} . qq|')|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE ap SET currency = (SELECT id FROM currencies c WHERE c.curr = ap.curr); UPDATE ar SET currency = (SELECT id FROM currencies c WHERE c.curr = ar.curr); UPDATE oe SET currency = (SELECT id FROM currencies c WHERE c.curr = oe.curr); @@ -180,7 +168,7 @@ sub create_and_fill_table { UPDATE delivery_orders SET currency = (SELECT id FROM currencies c WHERE c.curr = delivery_orders.curr); UPDATE exchangerate SET currency = (SELECT id FROM currencies c WHERE c.curr = exchangerate.curr); UPDATE vendor SET currency = (SELECT id FROM currencies c WHERE c.curr = vendor.curr);|; - do_query($query); + $self->db_query($query); #Drop column 'curr': $query = qq|ALTER TABLE ap DROP COLUMN curr; @@ -191,7 +179,7 @@ sub create_and_fill_table { ALTER TABLE exchangerate DROP COLUMN curr; ALTER TABLE vendor DROP COLUMN curr; ALTER TABLE defaults DROP COLUMN curr;|; - do_query($query); + $self->db_query($query); #Rename currency to curr: $query = qq|ALTER TABLE defaults RENAME COLUMN currency TO curr; @@ -202,7 +190,7 @@ sub create_and_fill_table { ALTER TABLE delivery_orders RENAME COLUMN currency TO curr; ALTER TABLE exchangerate RENAME COLUMN currency TO curr; ALTER TABLE vendor RENAME COLUMN currency TO curr;|; - do_query($query); + $self->db_query($query); #Set NOT NULL constraints: $query = qq|ALTER TABLE ap ALTER COLUMN curr SET NOT NULL; @@ -213,7 +201,7 @@ sub create_and_fill_table { ALTER TABLE exchangerate ALTER COLUMN curr SET NOT NULL; ALTER TABLE vendor ALTER COLUMN curr SET NOT NULL; ALTER TABLE defaults ALTER COLUMN curr SET NOT NULL;|; - do_query($query); + $self->db_query($query); #Set foreign keys: $query = qq|ALTER TABLE ap ADD FOREIGN KEY (curr) REFERENCES currencies(id); @@ -224,7 +212,7 @@ sub create_and_fill_table { ALTER TABLE exchangerate ADD FOREIGN KEY (curr) REFERENCES currencies(id); ALTER TABLE vendor ADD FOREIGN KEY (curr) REFERENCES currencies(id); ALTER TABLE defaults ADD FOREIGN KEY (curr) REFERENCES currencies(id);|; - do_query($query); + $self->db_query($query); }; @@ -236,4 +224,4 @@ sub print_orphaned_currencies { print $main::form->parse_html_template("dbupgrade/orphaned_currencies"); }; -return do_update(); +1; diff --git a/sql/Pg-upgrade2/rm_whitespaces.pl b/sql/Pg-upgrade2/rm_whitespaces.pl index 51d2395ef..c23489751 100644 --- a/sql/Pg-upgrade2/rm_whitespaces.pl +++ b/sql/Pg-upgrade2/rm_whitespaces.pl @@ -1,53 +1,39 @@ # @tag: rm_whitespaces # @description: Entfernt mögliche Leerzeichen am Anfang und Ende jeder Währung # @depends: release_3_0_0 -# @charset: utf-8 -use utf8; -use strict; - -die("This script cannot be run from the command line.") unless ($main::form); - -sub mydberror { - my ($msg) = @_; - die($dbup_locale->text("Database update error:") . "
$msg
" . $DBI::errstr); -} +package SL::DBUpgrade2::rm_whitespaces; -sub do_query { - my ($query, $may_fail) = @_; - - if (!$dbh->do($query)) { - mydberror($query) unless ($may_fail); - $dbh->rollback(); - $dbh->begin_work(); - } -} +use parent qw(SL::DBUpgrade2::Base); +use utf8; +use strict; -sub do_update { +sub run { + my ($self) = @_; my $query = qq|UPDATE ar SET curr = RTRIM(LTRIM(curr))|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE ap SET curr = RTRIM(LTRIM(curr))|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE oe SET curr = RTRIM(LTRIM(curr))|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE customer SET curr = RTRIM(LTRIM(curr))|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE delivery_orders SET curr = RTRIM(LTRIM(curr))|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE exchangerate SET curr = RTRIM(LTRIM(curr))|; - do_query($query); + $self->db_query($query); $query = qq|UPDATE vendor SET curr = RTRIM(LTRIM(curr))|; - do_query($query); + $self->db_query($query); $query = qq|SELECT curr FROM defaults|; - my ($curr) = selectrow_query($self, $dbh, $query); + my ($curr) = $self->dbh->selectrow_array($query); $curr =~ s/ //g; $query = qq|UPDATE defaults SET curr = '$curr'|; - do_query($query); + $self->db_query($query); return 1; }; -return do_update(); +1; -- 2.20.1