Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 714a28b1

Von Niclas Zimmermann vor mehr als 11 Jahren hinzugefügt

  • ID 714a28b162b86362a7e5d150211f0d85bf4ef1e4
  • Vorgänger a23454bb
  • Nachfolger a4d74009

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;
sql/Pg-upgrade2/rm_whitespaces.pl
1 1
# @tag: rm_whitespaces
2 2
# @description: Entfernt mögliche Leerzeichen am Anfang und Ende jeder Währung
3 3
# @depends: release_3_0_0
4
# @charset: utf-8
5 4

  
6
use utf8;
7
use strict;
8

  
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
}
5
package SL::DBUpgrade2::rm_whitespaces;
15 6

  
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
}
7
use parent qw(SL::DBUpgrade2::Base);
25 8

  
9
use utf8;
10
use strict;
26 11

  
27
sub do_update {
12
sub run {
13
  my ($self) = @_;
28 14
  my $query = qq|UPDATE ar SET curr = RTRIM(LTRIM(curr))|;
29
  do_query($query);
15
  $self->db_query($query);
30 16
  $query = qq|UPDATE ap SET curr = RTRIM(LTRIM(curr))|;
31
  do_query($query);
17
  $self->db_query($query);
32 18
  $query = qq|UPDATE oe SET curr = RTRIM(LTRIM(curr))|;
33
  do_query($query);
19
  $self->db_query($query);
34 20
  $query = qq|UPDATE customer SET curr = RTRIM(LTRIM(curr))|;
35
  do_query($query);
21
  $self->db_query($query);
36 22
  $query = qq|UPDATE delivery_orders SET curr = RTRIM(LTRIM(curr))|;
37
  do_query($query);
23
  $self->db_query($query);
38 24
  $query = qq|UPDATE exchangerate SET curr = RTRIM(LTRIM(curr))|;
39
  do_query($query);
25
  $self->db_query($query);
40 26
  $query = qq|UPDATE vendor SET curr = RTRIM(LTRIM(curr))|;
41
  do_query($query);
27
  $self->db_query($query);
42 28

  
43 29
  $query = qq|SELECT curr FROM defaults|;
44
  my ($curr)     = selectrow_query($self, $dbh, $query);
30
  my ($curr)     = $self->dbh->selectrow_array($query);
45 31

  
46 32
  $curr  =~ s/ //g;
47 33

  
48 34
  $query = qq|UPDATE defaults SET curr = '$curr'|;
49
  do_query($query);
35
  $self->db_query($query);
50 36
  return 1;
51 37
};
52 38

  
53
return do_update();
39
1;

Auch abrufbar als: Unified diff