Revision 347f2cff
Von Moritz Bunkus vor mehr als 11 Jahren hinzugefügt
| 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 | 3 |
# @depends: release_3_0_0 charts_without_taxkey |
| 4 |
# @charset: utf-8
|
|
| 4 |
package SL::DBUpgrade2::tax_constraints;
|
|
| 5 | 5 |
|
| 6 |
use utf8; |
|
| 7 | 6 |
use strict; |
| 8 |
use SL::Locale; |
|
| 9 |
|
|
| 10 |
die("This script cannot be run from the command line.") unless ($main::form);
|
|
| 11 |
|
|
| 12 |
sub mydberror {
|
|
| 13 |
my ($msg) = @_; |
|
| 14 |
die($dbup_locale->text("Database update error:") . "<br>$msg<br>" . $DBI::errstr);
|
|
| 15 |
} |
|
| 7 |
use utf8; |
|
| 16 | 8 |
|
| 17 |
sub do_query {
|
|
| 18 |
my ($query, $may_fail) = @_; |
|
| 9 |
use parent qw(SL::DBUpgrade2::Base); |
|
| 19 | 10 |
|
| 20 |
if (!$dbh->do($query)) {
|
|
| 21 |
mydberror($query) unless ($may_fail); |
|
| 22 |
$dbh->rollback(); |
|
| 23 |
$dbh->begin_work(); |
|
| 24 |
} |
|
| 25 |
} |
|
| 11 |
sub run {
|
|
| 12 |
my ($self) = @_; |
|
| 26 | 13 |
|
| 27 |
sub do_update {
|
|
| 28 | 14 |
#CHECK CONSISTANCY OF tax |
| 29 | 15 |
#update tax.rate and tax.taxdescription in order to set later NOT NULL constraints |
| 30 | 16 |
my $query= <<SQL; |
| ... | ... | |
| 32 | 18 |
UPDATE tax SET taxdescription='-' WHERE COALESCE(taxdescription, '') = ''; |
| 33 | 19 |
SQL |
| 34 | 20 |
|
| 35 |
do_query($query);
|
|
| 21 |
$self->db_query($query);
|
|
| 36 | 22 |
|
| 37 | 23 |
#check automatic tax accounts |
| 38 | 24 |
$query= <<SQL; |
| 39 | 25 |
SELECT count(*) FROM tax WHERE chart_id NOT IN (SELECT id FROM chart); |
| 40 | 26 |
SQL |
| 41 | 27 |
|
| 42 |
my ($invalid_tax_account) = $dbh->selectrow_array($query); |
|
| 28 |
my ($invalid_tax_account) = $self->dbh->selectrow_array($query);
|
|
| 43 | 29 |
|
| 44 | 30 |
if ($invalid_tax_account > 0){
|
| 45 | 31 |
#list all invalid tax accounts |
| ... | ... | |
| 51 | 37 |
FROM tax WHERE chart_id NOT IN (SELECT id FROM chart); |
| 52 | 38 |
SQL |
| 53 | 39 |
|
| 54 |
my $sth = $dbh->prepare($query); |
|
| 55 |
$sth->execute || $main::form->dberror($query);
|
|
| 40 |
my $sth = $self->dbh->prepare($query);
|
|
| 41 |
$sth->execute || $::form->dberror($query); |
|
| 56 | 42 |
|
| 57 |
$main::form->{TAX} = [];
|
|
| 43 |
$::form->{TAX} = [];
|
|
| 58 | 44 |
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
| 59 |
push @{ $main::form->{TAX} }, $ref;
|
|
| 45 |
push @{ $::form->{TAX} }, $ref;
|
|
| 60 | 46 |
} |
| 61 | 47 |
$sth->finish; |
| 62 | 48 |
|
| 63 |
$main::form->{invalid_tax_account} = 1;
|
|
| 49 |
$::form->{invalid_tax_account} = 1;
|
|
| 64 | 50 |
print_error_message(); |
| 65 | 51 |
return 0; |
| 66 | 52 |
} |
| ... | ... | |
| 70 | 56 |
SELECT count(*) FROM tax WHERE taxkey IS NULL; |
| 71 | 57 |
SQL |
| 72 | 58 |
|
| 73 |
my ($taxkey_is_null) = $dbh->selectrow_array($query); |
|
| 59 |
my ($taxkey_is_null) = $self->dbh->selectrow_array($query);
|
|
| 74 | 60 |
|
| 75 | 61 |
if ($taxkey_is_null > 0){
|
| 76 | 62 |
#list all invalid tax accounts |
| ... | ... | |
| 84 | 70 |
WHERE taxkey IS NULL; |
| 85 | 71 |
SQL |
| 86 | 72 |
|
| 87 |
my $sth = $dbh->prepare($query); |
|
| 88 |
$sth->execute || $main::form->dberror($query);
|
|
| 73 |
my $sth = $self->dbh->prepare($query);
|
|
| 74 |
$sth->execute || $::form->dberror($query); |
|
| 89 | 75 |
|
| 90 |
$main::form->{TAX} = [];
|
|
| 76 |
$::form->{TAX} = [];
|
|
| 91 | 77 |
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
| 92 |
push @{ $main::form->{TAX} }, $ref;
|
|
| 78 |
push @{ $::form->{TAX} }, $ref;
|
|
| 93 | 79 |
} |
| 94 | 80 |
$sth->finish; |
| 95 | 81 |
|
| 96 |
$main::form->{taxkey_is_null} = 1;
|
|
| 82 |
$::form->{taxkey_is_null} = 1;
|
|
| 97 | 83 |
print_error_message(); |
| 98 | 84 |
return 0; |
| 99 | 85 |
} |
| ... | ... | |
| 108 | 94 |
OR startdate IS NULL; |
| 109 | 95 |
SQL |
| 110 | 96 |
|
| 111 |
do_query($query);
|
|
| 97 |
$self->db_query($query);
|
|
| 112 | 98 |
|
| 113 | 99 |
#There are 3 cases for taxkeys.tax_id and taxkeys.taxkey_id |
| 114 | 100 |
#taxkeys.taxkey_id is NULL and taxkeys.tax_id is not NULL: |
| ... | ... | |
| 123 | 109 |
AND tax_id IS NOT NULL; |
| 124 | 110 |
SQL |
| 125 | 111 |
|
| 126 |
do_query($query);
|
|
| 112 |
$self->db_query($query);
|
|
| 127 | 113 |
|
| 128 | 114 |
#taxkeys.taxkey_id and taxkeys.tax_id are NULL: |
| 129 | 115 |
|
| ... | ... | |
| 135 | 121 |
AND tax_id IS NULL; |
| 136 | 122 |
SQL |
| 137 | 123 |
|
| 138 |
do_query($query);
|
|
| 124 |
$self->db_query($query);
|
|
| 139 | 125 |
|
| 140 | 126 |
#Last case where taxkeys.taxkey_id is not null and taxkeys.tax_id is null |
| 141 | 127 |
|
| ... | ... | |
| 150 | 136 |
AND tax_id IS NULL; |
| 151 | 137 |
SQL |
| 152 | 138 |
|
| 153 |
my $sth = $dbh->prepare($query); |
|
| 154 |
$sth->execute || $main::form->dberror($query);
|
|
| 139 |
my $sth = $self->dbh->prepare($query);
|
|
| 140 |
$sth->execute || $::form->dberror($query); |
|
| 155 | 141 |
|
| 156 |
$main::form->{TAXID} = [];
|
|
| 142 |
$::form->{TAXID} = [];
|
|
| 157 | 143 |
my $rowcount = 0; |
| 158 | 144 |
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
| 159 |
push @{ $main::form->{TAXID} }, $ref;
|
|
| 145 |
push @{ $::form->{TAXID} }, $ref;
|
|
| 160 | 146 |
$rowcount++; |
| 161 | 147 |
} |
| 162 | 148 |
$sth->finish; |
| ... | ... | |
| 166 | 152 |
my $tax_id; |
| 167 | 153 |
for my $i (0 .. $rowcount-1){
|
| 168 | 154 |
$query= qq| |
| 169 |
SELECT id FROM tax WHERE rate = 0 and taxkey=| . $main::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1
|
|
| 155 |
SELECT id FROM tax WHERE rate = 0 and taxkey=| . $::form->{TAXID}[$i]->{taxkey_id} . qq| LIMIT 1
|
|
| 170 | 156 |
|; |
| 171 |
($tax_id) = $dbh->selectrow_array($query); |
|
| 157 |
($tax_id) = $self->dbh->selectrow_array($query);
|
|
| 172 | 158 |
if ( not $tax_id ){
|
| 173 | 159 |
$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|);
|
|
| 160 |
INSERT INTO tax (rate, taxdescription, taxkey) VALUES (0, '| . $::locale->text('0% tax with taxkey') . $::form->{TAXID}[$i]->{taxkey_id} . $::locale->text('. Automatically generated.') .
|
|
| 161 |
qq|', | . $::form->{TAXID}[$i]->{taxkey_id} . qq|);
|
|
| 176 | 162 |
|; |
| 177 |
do_query($insertquery);
|
|
| 178 |
($tax_id) = $dbh->selectrow_array($query); |
|
| 179 |
$tax_id || $main::form->dberror($query);
|
|
| 163 |
$self->db_query($insertquery);
|
|
| 164 |
($tax_id) = $self->dbh->selectrow_array($query);
|
|
| 165 |
$tax_id || $::form->dberror($query); |
|
| 180 | 166 |
} |
| 181 | 167 |
$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
|
|
| 168 |
UPDATE taxkeys SET tax_id= | . $tax_id . qq| WHERE taxkey_id = | . $::form->{TAXID}[$i]->{taxkey_id} . qq| AND tax_id IS NULL
|
|
| 183 | 169 |
|; |
| 184 |
do_query($updatequery);
|
|
| 170 |
$self->db_query($updatequery);
|
|
| 185 | 171 |
} |
| 186 | 172 |
|
| 187 | 173 |
#The triple taxkey_id, chart_id, startdate in taxkeys has to be unique |
| ... | ... | |
| 195 | 181 |
AND tk2.startdate = tk1.startdate) > 1; |
| 196 | 182 |
SQL |
| 197 | 183 |
|
| 198 |
$sth = $dbh->prepare($query); |
|
| 199 |
$sth->execute || $main::form->dberror($query);
|
|
| 184 |
$sth = $self->dbh->prepare($query);
|
|
| 185 |
$sth->execute || $::form->dberror($query); |
|
| 200 | 186 |
|
| 201 |
$main::form->{TAXKEYS} = [];
|
|
| 187 |
$::form->{TAXKEYS} = [];
|
|
| 202 | 188 |
$rowcount = 0; |
| 203 | 189 |
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
| 204 |
push @{ $main::form->{TAXKEYS} }, $ref;
|
|
| 190 |
push @{ $::form->{TAXKEYS} }, $ref;
|
|
| 205 | 191 |
$rowcount++; |
| 206 | 192 |
} |
| 207 | 193 |
$sth->finish; |
| ... | ... | |
| 215 | 201 |
AND tk2.startdate = tk1.startdate) > 1 |
| 216 | 202 |
AND NOT tk1.id = (SELECT id |
| 217 | 203 |
FROM taxkeys |
| 218 |
WHERE chart_id = | . $main::form->{TAXKEYS}[$i]->{chart_id} . qq|
|
|
| 219 |
AND startdate = '| . $main::form->{TAXKEYS}[$i]->{startdate} . qq|'
|
|
| 204 |
WHERE chart_id = | . $::form->{TAXKEYS}[$i]->{chart_id} . qq|
|
|
| 205 |
AND startdate = '| . $::form->{TAXKEYS}[$i]->{startdate} . qq|'
|
|
| 220 | 206 |
LIMIT 1) |
| 221 | 207 |
|; |
| 222 | 208 |
|
| 223 |
do_query($query);
|
|
| 209 |
$self->db_query($query);
|
|
| 224 | 210 |
} |
| 225 | 211 |
|
| 226 | 212 |
#END CHECK OF taxkeys |
| ... | ... | |
| 233 | 219 |
ALTER TABLE tax ALTER COLUMN rate SET DEFAULT 0; |
| 234 | 220 |
SQL |
| 235 | 221 |
|
| 236 |
do_query($query);
|
|
| 222 |
$self->db_query($query);
|
|
| 237 | 223 |
|
| 238 | 224 |
#Create NOT NULL constraint for tax.description |
| 239 | 225 |
$query= <<SQL; |
| 240 | 226 |
ALTER TABLE tax ALTER COLUMN taxdescription SET NOT NULL; |
| 241 | 227 |
SQL |
| 242 | 228 |
|
| 243 |
do_query($query);
|
|
| 229 |
$self->db_query($query);
|
|
| 244 | 230 |
|
| 245 | 231 |
#Create foreign key for tax.chart_id to chart.id |
| 246 | 232 |
$query= <<SQL; |
| 247 | 233 |
ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart(id); |
| 248 | 234 |
SQL |
| 249 | 235 |
|
| 250 |
do_query($query);
|
|
| 236 |
$self->db_query($query);
|
|
| 251 | 237 |
|
| 252 | 238 |
#Create NOT NULL constraint for tax.taxkey |
| 253 | 239 |
$query= <<SQL; |
| 254 | 240 |
ALTER TABLE tax ALTER COLUMN taxkey SET NOT NULL; |
| 255 | 241 |
SQL |
| 256 | 242 |
|
| 257 |
do_query($query);
|
|
| 243 |
$self->db_query($query);
|
|
| 258 | 244 |
|
| 259 | 245 |
#Create NOT NULL constraint for taxkey.chart_id and foreign key for taxkey.chart_id |
| 260 | 246 |
$query= <<SQL; |
| ... | ... | |
| 262 | 248 |
ALTER TABLE taxkeys ADD FOREIGN KEY (chart_id) REFERENCES chart(id); |
| 263 | 249 |
SQL |
| 264 | 250 |
|
| 265 |
do_query($query);
|
|
| 251 |
$self->db_query($query);
|
|
| 266 | 252 |
|
| 267 | 253 |
#Create NOT NULL constraint for taxkey.startdate |
| 268 | 254 |
$query= <<SQL; |
| 269 | 255 |
ALTER TABLE taxkeys ALTER COLUMN startdate SET NOT NULL; |
| 270 | 256 |
SQL |
| 271 | 257 |
|
| 272 |
do_query($query);
|
|
| 258 |
$self->db_query($query);
|
|
| 273 | 259 |
|
| 274 | 260 |
#Create NOT NULL constraint for taxkey.taxkey_id |
| 275 | 261 |
$query= <<SQL; |
| 276 | 262 |
ALTER TABLE taxkeys ALTER COLUMN taxkey_id SET NOT NULL; |
| 277 | 263 |
SQL |
| 278 | 264 |
|
| 279 |
do_query($query);
|
|
| 265 |
$self->db_query($query);
|
|
| 280 | 266 |
|
| 281 | 267 |
#Create NOT NULL constraint for taxkey.tax_id |
| 282 | 268 |
$query= <<SQL; |
| 283 | 269 |
ALTER TABLE taxkeys ALTER COLUMN tax_id SET NOT NULL; |
| 284 | 270 |
SQL |
| 285 | 271 |
|
| 286 |
do_query($query);
|
|
| 272 |
$self->db_query($query);
|
|
| 287 | 273 |
|
| 288 | 274 |
#The triple chart_id, taxkey_id, startdate should be unique: |
| 289 | 275 |
$query= <<SQL; |
| 290 | 276 |
CREATE UNIQUE INDEX taxkeys_chartid_startdate ON taxkeys(chart_id, startdate); |
| 291 | 277 |
SQL |
| 292 | 278 |
|
| 293 |
do_query($query);
|
|
| 279 |
$self->db_query($query);
|
|
| 294 | 280 |
#ALL CONSTRAINTS WERE ADDED |
| 295 | 281 |
|
| 296 | 282 |
return 1; |
| 297 |
}; # end do_update
|
|
| 283 |
} # end run
|
|
| 298 | 284 |
|
| 299 | 285 |
|
| 300 | 286 |
sub print_error_message {
|
| 301 |
print $main::form->parse_html_template("dbupgrade/tax_constraints");
|
|
| 287 |
print $::form->parse_html_template("dbupgrade/tax_constraints");
|
|
| 302 | 288 |
} |
| 303 | 289 |
|
| 304 |
return do_update(); |
|
| 290 |
1; |
|
Auch abrufbar als: Unified diff
Perl-Datenbank-Upgradescripte auf Objektorientierung & strict umgestellt