Lines 5473-5478
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
Link Here
|
5473 |
SetVersion($DBversion); |
5473 |
SetVersion($DBversion); |
5474 |
} |
5474 |
} |
5475 |
|
5475 |
|
|
|
5476 |
$DBversion = "XXX"; |
5477 |
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { |
5478 |
$dbh->do(qq| |
5479 |
DROP TABLE IF EXISTS subscription_frequencies |
5480 |
|); |
5481 |
$dbh->do(qq| |
5482 |
CREATE TABLE subscription_frequencies ( |
5483 |
id INTEGER NOT NULL AUTO_INCREMENT, |
5484 |
description TEXT NOT NULL, |
5485 |
displayorder INT DEFAULT NULL, |
5486 |
unit ENUM('day','week','month','year') DEFAULT NULL, |
5487 |
unitsperissue INTEGER NOT NULL DEFAULT '1', |
5488 |
issuesperunit INTEGER NOT NULL DEFAULT '1', |
5489 |
PRIMARY KEY (id) |
5490 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
5491 |
|); |
5492 |
|
5493 |
$dbh->do(qq| |
5494 |
DROP TABLE IF EXISTS subscription_numberpatterns |
5495 |
|); |
5496 |
$dbh->do(qq| |
5497 |
CREATE TABLE subscription_numberpatterns ( |
5498 |
id INTEGER NOT NULL AUTO_INCREMENT, |
5499 |
label VARCHAR(255) NOT NULL, |
5500 |
displayorder INTEGER DEFAULT NULL, |
5501 |
description TEXT NOT NULL, |
5502 |
numberingmethod VARCHAR(255) NOT NULL, |
5503 |
label1 VARCHAR(255) DEFAULT NULL, |
5504 |
add1 INTEGER DEFAULT NULL, |
5505 |
every1 INTEGER DEFAULT NULL, |
5506 |
whenmorethan1 INTEGER DEFAULT NULL, |
5507 |
setto1 INTEGER DEFAULT NULL, |
5508 |
numbering1 VARCHAR(255) DEFAULT NULL, |
5509 |
label2 VARCHAR(255) DEFAULT NULL, |
5510 |
add2 INTEGER DEFAULT NULL, |
5511 |
every2 INTEGER DEFAULT NULL, |
5512 |
whenmorethan2 INTEGER DEFAULT NULL, |
5513 |
setto2 INTEGER DEFAULT NULL, |
5514 |
numbering2 VARCHAR(255) DEFAULT NULL, |
5515 |
label3 VARCHAR(255) DEFAULT NULL, |
5516 |
add3 INTEGER DEFAULT NULL, |
5517 |
every3 INTEGER DEFAULT NULL, |
5518 |
whenmorethan3 INTEGER DEFAULT NULL, |
5519 |
setto3 INTEGER DEFAULT NULL, |
5520 |
numbering3 VARCHAR(255) DEFAULT NULL, |
5521 |
PRIMARY KEY (id) |
5522 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
5523 |
|); |
5524 |
|
5525 |
$dbh->do(qq| |
5526 |
INSERT INTO subscription_frequencies (description, unit, unitsperissue, issuesperunit, displayorder) |
5527 |
VALUES |
5528 |
('2/day', 'day', 1, 2, 1), |
5529 |
('1/day', 'day', 1, 1, 2), |
5530 |
('3/week', 'week', 1, 3, 3), |
5531 |
('1/week', 'week', 1, 1, 4), |
5532 |
('1/2 weeks', 'week', 2, 1, 5), |
5533 |
('1/3 weeks', 'week', 3, 1, 6), |
5534 |
('1/month', 'month', 1, 1, 7), |
5535 |
('1/2 months', 'month', 2, 1, 8), |
5536 |
('1/3 months', 'month', 3, 1, 9), |
5537 |
('2/year', 'month', 6, 1, 10), |
5538 |
('1/year', 'year', 1, 1, 11), |
5539 |
('1/2 year', 'year', 2, 1, 12), |
5540 |
('Irregular', NULL, 1, 1, 13) |
5541 |
|); |
5542 |
|
5543 |
# Used to link existing subscription to newly created frequencies |
5544 |
my $frequencies_mapping = { # keys are old frequency numbers, values are the new ones |
5545 |
1 => 2, # daily (n/week) |
5546 |
2 => 4, # 1/week |
5547 |
3 => 5, # 1/2 weeks |
5548 |
4 => 6, # 1/3 weeks |
5549 |
5 => 7, # 1/month |
5550 |
6 => 8, # 1/2 months (6/year) |
5551 |
7 => 9, # 1/3 months (1/quarter) |
5552 |
8 => 9, # 1/quarter (seasonal) |
5553 |
9 => 10, # 2/year |
5554 |
10 => 11, # 1/year |
5555 |
11 => 12, # 1/2 years |
5556 |
12 => 1, # 2/day |
5557 |
16 => 13, # Without periodicity |
5558 |
32 => 13, # Irregular |
5559 |
48 => 13 # Unknown |
5560 |
}; |
5561 |
|
5562 |
$dbh->do(qq| |
5563 |
INSERT INTO subscription_numberpatterns |
5564 |
(label, displayorder, description, numberingmethod, |
5565 |
label1, add1, every1, whenmorethan1, setto1, numbering1, |
5566 |
label2, add2, every2, whenmorethan2, setto2, numbering2, |
5567 |
label3, add3, every3, whenmorethan3, setto3, numbering3) |
5568 |
VALUES |
5569 |
('Number', 1, 'Simple Numbering method', 'No.{X}', |
5570 |
'Number', 1, 1, 99999, 1, NULL, |
5571 |
NULL, NULL, NULL, NULL, NULL, NULL, |
5572 |
NULL, NULL, NULL, NULL, NULL, NULL), |
5573 |
|
5574 |
('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', |
5575 |
'Volume', 1, 48, 99999, 1, NULL, |
5576 |
'Number', 1, 4, 12, 1, NULL, |
5577 |
'Issue', 1, 1, 4, 1, NULL), |
5578 |
|
5579 |
('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', |
5580 |
'Volume', 1, 12, 99999, 1, NULL, |
5581 |
'Number', 1, 1, 12, 1, NULL, |
5582 |
NULL, NULL, NULL, NULL, NULL, NULL), |
5583 |
|
5584 |
('Seasonal', 4, 'Season Year', '{X} {Y}', |
5585 |
'Season', 1, 1, 3, 0, 'season', |
5586 |
'Year', 1, 4, 99999, 1, NULL, |
5587 |
NULL, NULL, NULL, NULL, NULL, NULL) |
5588 |
|); |
5589 |
|
5590 |
$dbh->do(qq| |
5591 |
ALTER TABLE subscription |
5592 |
MODIFY COLUMN numberpattern INTEGER DEFAULT NULL, |
5593 |
MODIFY COLUMN periodicity INTEGER DEFAULT NULL |
5594 |
|); |
5595 |
|
5596 |
# Update existing subscriptions |
5597 |
|
5598 |
my $query = qq| |
5599 |
SELECT subscriptionid, periodicity, numberingmethod, |
5600 |
add1, every1, whenmorethan1, setto1, |
5601 |
add2, every2, whenmorethan2, setto2, |
5602 |
add3, every3, whenmorethan3, setto3 |
5603 |
FROM subscription |
5604 |
ORDER BY subscriptionid |
5605 |
|; |
5606 |
my $sth = $dbh->prepare($query); |
5607 |
$sth->execute; |
5608 |
my $insert_numberpatterns_sth = $dbh->prepare(qq| |
5609 |
INSERT INTO subscription_numberpatterns |
5610 |
(label, displayorder, description, numberingmethod, |
5611 |
label1, add1, every1, whenmorethan1, setto1, numbering1, |
5612 |
label2, add2, every2, whenmorethan2, setto2, numbering2, |
5613 |
label3, add3, every3, whenmorethan3, setto3, numbering3) |
5614 |
VALUES |
5615 |
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) |
5616 |
|); |
5617 |
my $update_subscription_sth = $dbh->prepare(qq| |
5618 |
UPDATE subscription |
5619 |
SET numberpattern = ?, |
5620 |
periodicity = ? |
5621 |
WHERE subscriptionid = ? |
5622 |
|); |
5623 |
|
5624 |
my $i = 1; |
5625 |
while(my $sub = $sth->fetchrow_hashref) { |
5626 |
# Create a new numbering pattern for each subscription |
5627 |
my $ok = $insert_numberpatterns_sth->execute( |
5628 |
"Backup pattern $i", 4+$i, "Automatically created pattern by updatedatabase", $sub->{numberingmethod}, |
5629 |
"X", $sub->{add1}, $sub->{every1}, $sub->{whenmorethan1}, $sub->{setto1}, undef, |
5630 |
"Y", $sub->{add2}, $sub->{every2}, $sub->{whenmorethan2}, $sub->{setto2}, undef, |
5631 |
"Z", $sub->{add3}, $sub->{every3}, $sub->{whenmorethan3}, $sub->{setto3}, undef |
5632 |
); |
5633 |
if($ok) { |
5634 |
my $id = $dbh->last_insert_id(undef, undef, 'subscription_numberpatterns', undef); |
5635 |
# Link to subscription_numberpatterns and subscription_frequencies |
5636 |
$update_subscription_sth->execute($id, |
5637 |
$frequencies_mapping->{$sub->{periodicity}}, $sub->{subscriptionid}); |
5638 |
} |
5639 |
$i++; |
5640 |
} |
5641 |
|
5642 |
# Remove now useless columns |
5643 |
$dbh->do(qq| |
5644 |
ALTER TABLE subscription |
5645 |
DROP COLUMN numberingmethod, |
5646 |
DROP COLUMN add1, |
5647 |
DROP COLUMN every1, |
5648 |
DROP COLUMN whenmorethan1, |
5649 |
DROP COLUMN setto1, |
5650 |
DROP COLUMN add2, |
5651 |
DROP COLUMN every2, |
5652 |
DROP COLUMN whenmorethan2, |
5653 |
DROP COLUMN setto2, |
5654 |
DROP COLUMN add3, |
5655 |
DROP COLUMN every3, |
5656 |
DROP COLUMN whenmorethan3, |
5657 |
DROP COLUMN setto3, |
5658 |
DROP COLUMN dow, |
5659 |
DROP COLUMN issuesatonce, |
5660 |
DROP COLUMN hemisphere, |
5661 |
ADD COLUMN countissuesperunit INTEGER NOT NULL DEFAULT 0 AFTER periodicity, |
5662 |
ADD COLUMN skip_serialseq BOOLEAN NOT NULL DEFAULT 0 AFTER irregularity, |
5663 |
ADD COLUMN locale VARCHAR(80) DEFAULT NULL AFTER numberpattern, |
5664 |
ADD CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id), |
5665 |
ADD CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) |
5666 |
|); |
5667 |
|
5668 |
print "Upgrade to $DBversion done (Add subscription_frequencies and subscription_numberpatterns tables)\n"; |
5669 |
SetVersion($DBversion); |
5670 |
} |
5671 |
|
5476 |
=head1 FUNCTIONS |
5672 |
=head1 FUNCTIONS |
5477 |
|
5673 |
|
5478 |
=head2 TableExists($table) |
5674 |
=head2 TableExists($table) |
5479 |
- |
|
|