|
Lines 5146-5151
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
Link Here
|
| 5146 |
SetVersion($DBversion); |
5146 |
SetVersion($DBversion); |
| 5147 |
} |
5147 |
} |
| 5148 |
|
5148 |
|
|
|
5149 |
$DBversion = "XXX"; |
| 5150 |
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { |
| 5151 |
$dbh->do(qq| |
| 5152 |
DROP TABLE IF EXISTS subscription_frequencies |
| 5153 |
|); |
| 5154 |
$dbh->do(qq| |
| 5155 |
CREATE TABLE subscription_frequencies ( |
| 5156 |
id INTEGER NOT NULL AUTO_INCREMENT, |
| 5157 |
description TEXT NOT NULL, |
| 5158 |
displayorder INT DEFAULT NULL, |
| 5159 |
unit ENUM('day','week','month','year') DEFAULT NULL, |
| 5160 |
unitsperissue INTEGER NOT NULL DEFAULT '1', |
| 5161 |
issuesperunit INTEGER NOT NULL DEFAULT '1', |
| 5162 |
PRIMARY KEY (id) |
| 5163 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| 5164 |
|); |
| 5165 |
|
| 5166 |
$dbh->do(qq| |
| 5167 |
DROP TABLE IF EXISTS subscription_numberpatterns |
| 5168 |
|); |
| 5169 |
$dbh->do(qq| |
| 5170 |
CREATE TABLE subscription_numberpatterns ( |
| 5171 |
id INTEGER NOT NULL AUTO_INCREMENT, |
| 5172 |
label VARCHAR(255) NOT NULL, |
| 5173 |
displayorder INTEGER DEFAULT NULL, |
| 5174 |
description TEXT NOT NULL, |
| 5175 |
numberingmethod VARCHAR(255) NOT NULL, |
| 5176 |
label1 VARCHAR(255) DEFAULT NULL, |
| 5177 |
add1 INTEGER DEFAULT NULL, |
| 5178 |
every1 INTEGER DEFAULT NULL, |
| 5179 |
whenmorethan1 INTEGER DEFAULT NULL, |
| 5180 |
setto1 INTEGER DEFAULT NULL, |
| 5181 |
numbering1 VARCHAR(255) DEFAULT NULL, |
| 5182 |
label2 VARCHAR(255) DEFAULT NULL, |
| 5183 |
add2 INTEGER DEFAULT NULL, |
| 5184 |
every2 INTEGER DEFAULT NULL, |
| 5185 |
whenmorethan2 INTEGER DEFAULT NULL, |
| 5186 |
setto2 INTEGER DEFAULT NULL, |
| 5187 |
numbering2 VARCHAR(255) DEFAULT NULL, |
| 5188 |
label3 VARCHAR(255) DEFAULT NULL, |
| 5189 |
add3 INTEGER DEFAULT NULL, |
| 5190 |
every3 INTEGER DEFAULT NULL, |
| 5191 |
whenmorethan3 INTEGER DEFAULT NULL, |
| 5192 |
setto3 INTEGER DEFAULT NULL, |
| 5193 |
numbering3 VARCHAR(255) DEFAULT NULL, |
| 5194 |
PRIMARY KEY (id) |
| 5195 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| 5196 |
|); |
| 5197 |
|
| 5198 |
$dbh->do(qq| |
| 5199 |
INSERT INTO subscription_frequencies (description, unit, unitsperissue, issuesperunit, displayorder) |
| 5200 |
VALUES |
| 5201 |
('2/day', 'day', 1, 2, 1), |
| 5202 |
('1/day', 'day', 1, 1, 2), |
| 5203 |
('3/week', 'week', 1, 3, 3), |
| 5204 |
('1/week', 'week', 1, 1, 4), |
| 5205 |
('1/2 weeks', 'week', 2, 1, 5), |
| 5206 |
('1/3 weeks', 'week', 3, 1, 6), |
| 5207 |
('1/month', 'month', 1, 1, 7), |
| 5208 |
('1/2 months', 'month', 2, 1, 8), |
| 5209 |
('1/3 months', 'month', 3, 1, 9), |
| 5210 |
('2/year', 'month', 6, 1, 10), |
| 5211 |
('1/year', 'year', 1, 1, 11), |
| 5212 |
('1/2 year', 'year', 2, 1, 12), |
| 5213 |
('Irregular', NULL, 1, 1, 13) |
| 5214 |
|); |
| 5215 |
|
| 5216 |
# Used to link existing subscription to newly created frequencies |
| 5217 |
my $frequencies_mapping = { # keys are old frequency numbers, values are the new ones |
| 5218 |
1 => 2, # daily (n/week) |
| 5219 |
2 => 4, # 1/week |
| 5220 |
3 => 5, # 1/2 weeks |
| 5221 |
4 => 6, # 1/3 weeks |
| 5222 |
5 => 7, # 1/month |
| 5223 |
6 => 8, # 1/2 months (6/year) |
| 5224 |
7 => 9, # 1/3 months (1/quarter) |
| 5225 |
8 => 9, # 1/quarter (seasonal) |
| 5226 |
9 => 10, # 2/year |
| 5227 |
10 => 11, # 1/year |
| 5228 |
11 => 12, # 1/2 years |
| 5229 |
12 => 1, # 2/day |
| 5230 |
16 => 13, # Without periodicity |
| 5231 |
32 => 13, # Irregular |
| 5232 |
48 => 13 # Unknown |
| 5233 |
}; |
| 5234 |
|
| 5235 |
$dbh->do(qq| |
| 5236 |
INSERT INTO subscription_numberpatterns |
| 5237 |
(label, displayorder, description, numberingmethod, |
| 5238 |
label1, add1, every1, whenmorethan1, setto1, numbering1, |
| 5239 |
label2, add2, every2, whenmorethan2, setto2, numbering2, |
| 5240 |
label3, add3, every3, whenmorethan3, setto3, numbering3) |
| 5241 |
VALUES |
| 5242 |
('Number', 1, 'Simple Numbering method', 'No.{X}', |
| 5243 |
'Number', 1, 1, 99999, 1, NULL, |
| 5244 |
NULL, NULL, NULL, NULL, NULL, NULL, |
| 5245 |
NULL, NULL, NULL, NULL, NULL, NULL), |
| 5246 |
|
| 5247 |
('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', |
| 5248 |
'Volume', 1, 48, 99999, 1, NULL, |
| 5249 |
'Number', 1, 4, 12, 1, NULL, |
| 5250 |
'Issue', 1, 1, 4, 1, NULL), |
| 5251 |
|
| 5252 |
('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', |
| 5253 |
'Volume', 1, 12, 99999, 1, NULL, |
| 5254 |
'Number', 1, 1, 12, 1, NULL, |
| 5255 |
NULL, NULL, NULL, NULL, NULL, NULL), |
| 5256 |
|
| 5257 |
('Seasonal', 4, 'Season Year', '{X} {Y}', |
| 5258 |
'Season', 1, 1, 3, 0, 'season', |
| 5259 |
'Year', 1, 4, 99999, 1, NULL, |
| 5260 |
NULL, NULL, NULL, NULL, NULL, NULL) |
| 5261 |
|); |
| 5262 |
|
| 5263 |
$dbh->do(qq| |
| 5264 |
ALTER TABLE subscription |
| 5265 |
MODIFY COLUMN numberpattern INTEGER DEFAULT NULL, |
| 5266 |
MODIFY COLUMN periodicity INTEGER DEFAULT NULL |
| 5267 |
|); |
| 5268 |
|
| 5269 |
# Update existing subscriptions |
| 5270 |
|
| 5271 |
my $query = qq| |
| 5272 |
SELECT subscriptionid, periodicity, numberingmethod, |
| 5273 |
add1, every1, whenmorethan1, setto1, |
| 5274 |
add2, every2, whenmorethan2, setto2, |
| 5275 |
add3, every3, whenmorethan3, setto3 |
| 5276 |
FROM subscription |
| 5277 |
ORDER BY subscriptionid |
| 5278 |
|; |
| 5279 |
my $sth = $dbh->prepare($query); |
| 5280 |
$sth->execute; |
| 5281 |
my $insert_numberpatterns_sth = $dbh->prepare(qq| |
| 5282 |
INSERT INTO subscription_numberpatterns |
| 5283 |
(label, displayorder, description, numberingmethod, |
| 5284 |
label1, add1, every1, whenmorethan1, setto1, numbering1, |
| 5285 |
label2, add2, every2, whenmorethan2, setto2, numbering2, |
| 5286 |
label3, add3, every3, whenmorethan3, setto3, numbering3) |
| 5287 |
VALUES |
| 5288 |
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) |
| 5289 |
|); |
| 5290 |
my $update_subscription_sth = $dbh->prepare(qq| |
| 5291 |
UPDATE subscription |
| 5292 |
SET numberpattern = ?, |
| 5293 |
periodicity = ? |
| 5294 |
WHERE subscriptionid = ? |
| 5295 |
|); |
| 5296 |
|
| 5297 |
my $i = 1; |
| 5298 |
while(my $sub = $sth->fetchrow_hashref) { |
| 5299 |
# Create a new numbering pattern for each subscription |
| 5300 |
my $ok = $insert_numberpatterns_sth->execute( |
| 5301 |
"Backup pattern $i", 4+$i, "Automatically created pattern by updatedatabase", $sub->{numberingmethod}, |
| 5302 |
"X", $sub->{add1}, $sub->{every1}, $sub->{whenmorethan1}, $sub->{setto1}, undef, |
| 5303 |
"Y", $sub->{add2}, $sub->{every2}, $sub->{whenmorethan2}, $sub->{setto2}, undef, |
| 5304 |
"Z", $sub->{add3}, $sub->{every3}, $sub->{whenmorethan3}, $sub->{setto3}, undef |
| 5305 |
); |
| 5306 |
if($ok) { |
| 5307 |
my $id = $dbh->last_insert_id(undef, undef, 'subscription_numberpatterns', undef); |
| 5308 |
# Link to subscription_numberpatterns and subscription_frequencies |
| 5309 |
$update_subscription_sth->execute($id, |
| 5310 |
$frequencies_mapping->{$sub->{periodicity}}, $sub->{subscriptionid}); |
| 5311 |
} |
| 5312 |
$i++; |
| 5313 |
} |
| 5314 |
|
| 5315 |
# Remove now useless columns |
| 5316 |
$dbh->do(qq| |
| 5317 |
ALTER TABLE subscription |
| 5318 |
DROP COLUMN numberingmethod, |
| 5319 |
DROP COLUMN add1, |
| 5320 |
DROP COLUMN every1, |
| 5321 |
DROP COLUMN whenmorethan1, |
| 5322 |
DROP COLUMN setto1, |
| 5323 |
DROP COLUMN add2, |
| 5324 |
DROP COLUMN every2, |
| 5325 |
DROP COLUMN whenmorethan2, |
| 5326 |
DROP COLUMN setto2, |
| 5327 |
DROP COLUMN add3, |
| 5328 |
DROP COLUMN every3, |
| 5329 |
DROP COLUMN whenmorethan3, |
| 5330 |
DROP COLUMN setto3, |
| 5331 |
DROP COLUMN dow, |
| 5332 |
DROP COLUMN issuesatonce, |
| 5333 |
DROP COLUMN hemisphere, |
| 5334 |
ADD COLUMN countissuesperunit INTEGER NOT NULL DEFAULT 0 AFTER periodicity, |
| 5335 |
ADD COLUMN skip_serialseq BOOLEAN NOT NULL DEFAULT 0 AFTER irregularity, |
| 5336 |
ADD COLUMN locale VARCHAR(80) DEFAULT NULL AFTER numberpattern, |
| 5337 |
ADD CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id), |
| 5338 |
ADD CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) |
| 5339 |
|); |
| 5340 |
|
| 5341 |
print "Upgrade to $DBversion done (Add subscription_frequencies and subscription_numberpatterns tables)\n"; |
| 5342 |
SetVersion($DBversion); |
| 5343 |
} |
| 5344 |
|
| 5149 |
=head1 FUNCTIONS |
5345 |
=head1 FUNCTIONS |
| 5150 |
|
5346 |
|
| 5151 |
=head2 DropAllForeignKeys($table) |
5347 |
=head2 DropAllForeignKeys($table) |
| 5152 |
- |
|
|