Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 32ddb71c

Von Niclas Zimmermann vor fast 12 Jahren hinzugefügt

  • ID 32ddb71c1b2a113b297e729817dda69a1afa203c
  • Vorgänger 18931692
  • Nachfolger b4359a89

Verbesserung Upgrade-Script

Dieser Commit enthält noch einige kleine Verbesserungen zum Update
tax_constraints.pl. Es wurden Einrückungen verändert, trailing white-
spaces entfernt und eine Abhängigkeit hinzugefügt.

Unterschiede anzeigen:

sql/Pg-upgrade2/tax_constraints.pl
1 1
# @tag: tax_constraints
2 2
# @description: Setzt Fremdschlüssel und andere constraints auf die Tabellen tax und taxkeys
3
# @depends: release_3_0_0
3
# @depends: release_3_0_0 charts_without_taxkey
4 4
# @charset: utf-8
5 5

  
6 6
use utf8;
......
25 25
}
26 26

  
27 27
sub do_update {
28
#CHECK CONSISTANCY OF tax
28
  #CHECK CONSISTANCY OF tax
29 29
  #update tax.rate and tax.taxdescription in order to set later NOT NULL constraints
30
my $query= <<SQL;
31
UPDATE tax SET rate=0 WHERE rate IS NULL;
32
UPDATE tax SET taxdescription='-' WHERE taxdescription IS NULL;
30
  my $query= <<SQL;
31
    UPDATE tax SET rate=0 WHERE rate IS NULL;
32
    UPDATE tax SET taxdescription='-' WHERE COALESCE(taxdescription, '') = '';
33 33
SQL
34 34

  
35 35
  do_query($query);
36 36

  
37 37
  #check automatic tax accounts
38 38
  $query= <<SQL;
39
SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
39
    SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
40 40
SQL
41
   
41

  
42 42
  my ($invalid_tax_account) = $dbh->selectrow_array($query);
43
  
43

  
44 44
  if ($invalid_tax_account > 0){
45 45
    #list all invalid tax accounts
46 46
    $query = <<SQL;
47
SELECT id,
48
  taxkey,
49
  taxdescription, 
50
  round(rate * 100, 2) AS rate 
51
FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
47
      SELECT id,
48
        taxkey,
49
        taxdescription,
50
        round(rate * 100, 2) AS rate
51
      FROM tax WHERE chart_id NOT IN (SELECT id FROM chart);
52 52
SQL
53 53

  
54 54
    my $sth = $dbh->prepare($query);
......
63 63
    $main::form->{invalid_tax_account} = 1;
64 64
    print_error_message();
65 65
    return 0;
66
  } 
66
  }
67 67

  
68 68
  #check entry tax.taxkey of NOT NULL
69 69
  $query= <<SQL;
70
SELECT count(*) FROM tax WHERE taxkey IS NULL;
70
    SELECT count(*) FROM tax WHERE taxkey IS NULL;
71 71
SQL
72
   
72

  
73 73
  my ($taxkey_is_null) = $dbh->selectrow_array($query);
74
  
74

  
75 75
  if ($taxkey_is_null > 0){
76 76
    #list all invalid tax accounts
77 77
    $query = <<SQL;
78
SELECT id, 
79
  taxdescription, 
80
  round(rate * 100, 2) AS rate, 
81
  (SELECT accno FROM chart WHERE id = chart_id) AS taxnumber, 
82
  (SELECT description FROM chart WHERE id = chart_id) AS account_description
83
FROM tax 
84
WHERE taxkey IS NULL;
78
      SELECT id,
79
        taxdescription,
80
        round(rate * 100, 2) AS rate,
81
        (SELECT accno FROM chart WHERE id = chart_id) AS taxnumber,
82
        (SELECT description FROM chart WHERE id = chart_id) AS account_description
83
      FROM tax
84
      WHERE taxkey IS NULL;
85 85
SQL
86 86

  
87 87
    my $sth = $dbh->prepare($query);
......
96 96
    $main::form->{taxkey_is_null} = 1;
97 97
    print_error_message();
98 98
    return 0;
99
  } 
100
#END CHECK OF tax
99
  }
100
  #END CHECK OF tax
101 101

  
102
#CHECK CONSISTANCY OF taxkeys
102
  #CHECK CONSISTANCY OF taxkeys
103 103
  #delete invalide entries in taxkeys
104 104
  $query= <<SQL;
105
DELETE FROM taxkeys 
106
WHERE chart_id IS NULL 
107
OR chart_id NOT IN (SELECT id FROM chart) 
108
OR startdate IS NULL;
105
    DELETE FROM taxkeys
106
    WHERE chart_id IS NULL
107
    OR chart_id NOT IN (SELECT id FROM chart)
108
    OR startdate IS NULL;
109 109
SQL
110 110

  
111 111
  do_query($query);
112 112

  
113
#There are 3 cases for taxkeys.tax_id and taxkeys.taxkey_id
114
#taxkeys.taxkey_id is NULL and taxkeys.tax_id is not NULL:
113
  #There are 3 cases for taxkeys.tax_id and taxkeys.taxkey_id
114
  #taxkeys.taxkey_id is NULL and taxkeys.tax_id is not NULL:
115 115

  
116 116
  #Update taxkeys.taxkey_id with tax.taxkey
117 117
  $query= <<SQL;
118
UPDATE taxkeys 
119
SET taxkey_id = (SELECT t.taxkey 
120
                        FROM tax t 
118
    UPDATE taxkeys
119
    SET taxkey_id = (SELECT t.taxkey
120
                        FROM tax t
121 121
                        WHERE t.id=tax_id)
122
WHERE taxkey_id IS NULL 
123
AND tax_id IS NOT NULL;
122
    WHERE taxkey_id IS NULL
123
    AND tax_id IS NOT NULL;
124 124
SQL
125
  
125

  
126 126
  do_query($query);
127 127

  
128
#taxkeys.taxkey_id and taxkeys.tax_id are NULL:
129
  
128
  #taxkeys.taxkey_id and taxkeys.tax_id are NULL:
129

  
130 130
  #Set taxkey 0 in this case:
131 131
  $query= <<SQL;
132
UPDATE taxkeys
133
SET taxkey_id = 0, tax_id = (SELECT id FROM tax WHERE taxkey=0)
134
WHERE taxkey_id IS NULL
135
AND tax_id IS NULL;
132
    UPDATE taxkeys
133
    SET taxkey_id = 0, tax_id = (SELECT id FROM tax WHERE taxkey=0)
134
    WHERE taxkey_id IS NULL
135
    AND tax_id IS NULL;
136 136
SQL
137
   
137

  
138 138
  do_query($query);
139 139

  
140
#Last case where taxkeys.taxkey_id is not null and taxkeys.tax_id is null
140
  #Last case where taxkeys.taxkey_id is not null and taxkeys.tax_id is null
141 141

  
142 142
  #If such entries exist we update with an entry in tax where tax.rate=0
143 143
  #and tax.taxkey corresponds to taxkeys.taxkey_id.
144
  #If no entry in tax with rate 0 and taxkey taxkeys.taxkey_id exists 
144
  #If no entry in tax with rate 0 and taxkey taxkeys.taxkey_id exists
145 145
  #we create one.
146 146
  $query= <<SQL;
147
SELECT DISTINCT taxkey_id
148
FROM taxkeys 
149
WHERE taxkey_id IS NOT NULL 
150
AND tax_id IS NULL;
147
    SELECT DISTINCT taxkey_id
148
    FROM taxkeys
149
    WHERE taxkey_id IS NOT NULL
150
    AND tax_id IS NULL;
151 151
SQL
152 152

  
153 153
  my $sth = $dbh->prepare($query);
......
160 160
    $rowcount++;
161 161
  }
162 162
  $sth->finish;
163
  
163

  
164 164
  my $insertquery;
165 165
  my $updatequery;
166 166
  my $tax_id;
167 167
  for my $i (0 .. $rowcount-1){
168 168
    $query= qq|
169
SELECT id FROM tax WHERE rate = 0 and taxkey=| . $main::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1
170
|;  
171
    ($tax_id) = $dbh->selectrow_array($query); 
169
      SELECT id FROM tax WHERE rate = 0 and taxkey=| . $main::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1
170
|;
171
    ($tax_id) = $dbh->selectrow_array($query);
172 172
    if ( not $tax_id ){
173 173
      $insertquery=qq|
174
INSERT INTO tax (rate, taxdescription, taxkey) VALUES (0, '| . $::locale->text('0% tax with taxkey') . $main::form->{TAXID}[$i]->{taxkey_id} .  $::locale->text('. Automatically generated.') . 
175
qq|', | . $main::form->{TAXID}[$i]->{taxkey_id} . qq|);
174
        INSERT INTO tax (rate, taxdescription, taxkey) VALUES (0, '| . $::locale->text('0% tax with taxkey') . $main::form->{TAXID}[$i]->{taxkey_id} .  $::locale->text('. Automatically generated.') .
175
        qq|', | . $main::form->{TAXID}[$i]->{taxkey_id} . qq|);
176 176
|;
177 177
      do_query($insertquery);
178 178
      ($tax_id) = $dbh->selectrow_array($query);
179
      $tax_id || $main::form->dberror($query); 
179
      $tax_id || $main::form->dberror($query);
180 180
    }
181 181
    $updatequery = qq|
182
UPDATE taxkeys SET tax_id= | . $tax_id . qq| WHERE taxkey_id = | . $main::form->{TAXID}[$i]->{taxkey_id} . qq| AND tax_id IS NULL
182
      UPDATE taxkeys SET tax_id= | . $tax_id . qq| WHERE taxkey_id = | . $main::form->{TAXID}[$i]->{taxkey_id} . qq| AND tax_id IS NULL
183 183
|;
184 184
    do_query($updatequery);
185 185
  }
186 186

  
187
#The triple taxkey_id, chart_id, startdate in taxkeys has to be unique
187
  #The triple taxkey_id, chart_id, startdate in taxkeys has to be unique
188 188
  #Select these entries:
189 189
  $query= <<SQL;
190
SELECT DISTINCT tk1.chart_id AS chart_id, tk1.startdate AS startdate
191
FROM taxkeys tk1 
192
WHERE (SELECT count(*) 
193
       FROM taxkeys tk2 
194
       WHERE tk2.chart_id  = tk1.chart_id 
195
       AND   tk2.startdate = tk1.startdate) > 1;
190
    SELECT DISTINCT tk1.chart_id AS chart_id, tk1.startdate AS startdate
191
    FROM taxkeys tk1
192
    WHERE (SELECT count(*)
193
           FROM taxkeys tk2
194
           WHERE tk2.chart_id  = tk1.chart_id
195
           AND   tk2.startdate = tk1.startdate) > 1;
196 196
SQL
197 197

  
198 198
  $sth = $dbh->prepare($query);
......
207 207
  $sth->finish;
208 208

  
209 209
  for my $i (0 .. $rowcount-1){
210
    $query= qq| 
211
DELETE FROM taxkeys tk1 
212
WHERE (SELECT count(*) 
213
       FROM taxkeys tk2 
214
       WHERE tk2.chart_id  = tk1.chart_id 
215
       AND   tk2.startdate = tk1.startdate) > 1 
216
AND NOT tk1.id = (SELECT id 
217
                  FROM taxkeys 
218
                  WHERE chart_id  = | . $main::form->{TAXKEYS}[$i]->{chart_id} . qq|
219
                  AND   startdate = '| . $main::form->{TAXKEYS}[$i]->{startdate} . qq|'
220
                  LIMIT 1)
210
    $query= qq|
211
      DELETE FROM taxkeys tk1
212
      WHERE (SELECT count(*)
213
            FROM taxkeys tk2
214
            WHERE tk2.chart_id  = tk1.chart_id
215
            AND   tk2.startdate = tk1.startdate) > 1
216
      AND NOT tk1.id = (SELECT id
217
                        FROM taxkeys
218
                        WHERE chart_id  = | . $main::form->{TAXKEYS}[$i]->{chart_id} . qq|
219
                        AND   startdate = '| . $main::form->{TAXKEYS}[$i]->{startdate} . qq|'
220
                        LIMIT 1)
221 221
|;
222 222

  
223 223
    do_query($query);
224 224
  }
225 225

  
226
#END CHECK OF taxkeys
226
  #END CHECK OF taxkeys
227 227

  
228
#ADD CONSTRAINTS:
229
#Now the database is consistent, so we can add constraints:
228
  #ADD CONSTRAINTS:
229
  #Now the database is consistent, so we can add constraints:
230 230
  #Crate NOT NULL constraint for tax.rate with default value 0
231 231
  $query= <<SQL;
232
ALTER TABLE tax ALTER COLUMN rate SET NOT NULL;
233
ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0;
232
    ALTER TABLE tax ALTER COLUMN rate SET NOT NULL;
233
    ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0;
234 234
SQL
235 235

  
236 236
  do_query($query);
237 237

  
238 238
  #Create NOT NULL constraint for tax.description
239 239
  $query= <<SQL;
240
ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL;
240
    ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL;
241 241
SQL
242 242

  
243 243
  do_query($query);
244 244

  
245 245
  #Create foreign key for tax.chart_id to chart.id
246 246
  $query= <<SQL;
247
ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
247
    ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
248 248
SQL
249
  
249

  
250 250
  do_query($query);
251
  
251

  
252 252
  #Create NOT NULL constraint for tax.taxkey
253 253
  $query= <<SQL;
254
ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL;
254
    ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL;
255 255
SQL
256 256

  
257 257
  do_query($query);
258 258

  
259 259
  #Create NOT NULL constraint for taxkey.chart_id and foreign key for taxkey.chart_id
260 260
  $query= <<SQL;
261
ALTER TABLE taxkeys ALTER COLUMN chart_id SET NOT NULL;
262
ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
261
    ALTER TABLE taxkeys ALTER COLUMN chart_id SET NOT NULL;
262
    ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id);
263 263
SQL
264
  
264

  
265 265
  do_query($query);
266 266

  
267 267
  #Create NOT NULL constraint for taxkey.startdate
268 268
  $query= <<SQL;
269
ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL;
269
    ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL;
270 270
SQL
271
  
271

  
272 272
  do_query($query);
273 273

  
274 274
  #Create NOT NULL constraint for taxkey.taxkey_id
275 275
  $query= <<SQL;
276
ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL;
276
    ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL;
277 277
SQL
278
   
278

  
279 279
  do_query($query);
280 280

  
281 281
  #Create NOT NULL constraint for taxkey.tax_id
282 282
  $query= <<SQL;
283
ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL;
283
    ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL;
284 284
SQL
285
   
285

  
286 286
  do_query($query);
287 287

  
288 288
  #The triple chart_id, taxkey_id, startdate should be unique:
289 289
  $query= <<SQL;
290
CREATE UNIQUE INDEX taxkeys_chartid_startdate ON taxkeys(chart_id, startdate);
290
    CREATE UNIQUE INDEX taxkeys_chartid_startdate ON taxkeys(chart_id, startdate);
291 291
SQL
292
  
292

  
293 293
  do_query($query);
294
#ALL CONSTRAINTS WERE ADDED
294
  #ALL CONSTRAINTS WERE ADDED
295 295

  
296 296
  return 1;
297 297
}; # end do_update

Auch abrufbar als: Unified diff