From 33adca3f541232cbb854f0911e9656c99880c417 Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Thu, 8 Mar 2012 17:12:55 +0100 Subject: [PATCH 1/2] Bug 7688: Database files kohastructure.sql and updatedatabase.pl changes + sample frequencies data and sample numberpatterns data for fresh installs (sample data is included in updatedatabase.pl) --- .../mysql/de-DE/optional/sample_frequencies.sql | 16 ++ .../mysql/de-DE/optional/sample_frequencies.txt | 1 + .../mysql/de-DE/optional/sample_numberpatterns.sql | 25 +++ .../mysql/de-DE/optional/sample_numberpatterns.txt | 1 + .../data/mysql/en/optional/sample_frequencies.sql | 16 ++ .../data/mysql/en/optional/sample_frequencies.txt | 1 + .../mysql/en/optional/sample_numberpatterns.sql | 25 +++ .../mysql/en/optional/sample_numberpatterns.txt | 1 + .../mysql/es-ES/optional/sample_frequencies.sql | 16 ++ .../mysql/es-ES/optional/sample_frequencies.txt | 1 + .../mysql/es-ES/optional/sample_numberpatterns.sql | 25 +++ .../mysql/es-ES/optional/sample_numberpatterns.txt | 1 + .../mysql/fr-FR/2-Optionel/sample_frequencies.sql | 16 ++ .../mysql/fr-FR/2-Optionel/sample_frequencies.txt | 1 + .../fr-FR/2-Optionel/sample_numberpatterns.sql | 25 +++ .../fr-FR/2-Optionel/sample_numberpatterns.txt | 1 + .../mysql/it-IT/optional/sample_frequencies.sql | 16 ++ .../mysql/it-IT/optional/sample_frequencies.txt | 1 + .../mysql/it-IT/optional/sample_numberpatterns.sql | 25 +++ .../mysql/it-IT/optional/sample_numberpatterns.txt | 1 + installer/data/mysql/kohastructure.sql | 74 ++++++-- .../mysql/nb-NO/2-Valgfritt/sample_frequencies.sql | 16 ++ .../mysql/nb-NO/2-Valgfritt/sample_frequencies.txt | 1 + .../nb-NO/2-Valgfritt/sample_numberpatterns.sql | 25 +++ .../nb-NO/2-Valgfritt/sample_numberpatterns.txt | 1 + .../mysql/pl-PL/optional/sample_frequencies.sql | 16 ++ .../mysql/pl-PL/optional/sample_frequencies.txt | 1 + .../mysql/pl-PL/optional/sample_numberpatterns.sql | 25 +++ .../mysql/pl-PL/optional/sample_numberpatterns.txt | 1 + .../mysql/ru-RU/optional/sample_frequencies.sql | 16 ++ .../mysql/ru-RU/optional/sample_frequencies.txt | 1 + .../mysql/ru-RU/optional/sample_numberpatterns.sql | 25 +++ .../mysql/ru-RU/optional/sample_numberpatterns.txt | 1 + .../mysql/uk-UA/optional/sample_frequencies.sql | 16 ++ .../mysql/uk-UA/optional/sample_frequencies.txt | 1 + .../mysql/uk-UA/optional/sample_numberpatterns.sql | 25 +++ .../mysql/uk-UA/optional/sample_numberpatterns.txt | 1 + installer/data/mysql/updatedatabase.pl | 196 ++++++++++++++++++++ 38 files changed, 638 insertions(+), 19 deletions(-) create mode 100644 installer/data/mysql/de-DE/optional/sample_frequencies.sql create mode 100644 installer/data/mysql/de-DE/optional/sample_frequencies.txt create mode 100644 installer/data/mysql/de-DE/optional/sample_numberpatterns.sql create mode 100644 installer/data/mysql/de-DE/optional/sample_numberpatterns.txt create mode 100644 installer/data/mysql/en/optional/sample_frequencies.sql create mode 100644 installer/data/mysql/en/optional/sample_frequencies.txt create mode 100644 installer/data/mysql/en/optional/sample_numberpatterns.sql create mode 100644 installer/data/mysql/en/optional/sample_numberpatterns.txt create mode 100644 installer/data/mysql/es-ES/optional/sample_frequencies.sql create mode 100644 installer/data/mysql/es-ES/optional/sample_frequencies.txt create mode 100644 installer/data/mysql/es-ES/optional/sample_numberpatterns.sql create mode 100644 installer/data/mysql/es-ES/optional/sample_numberpatterns.txt create mode 100644 installer/data/mysql/fr-FR/2-Optionel/sample_frequencies.sql create mode 100644 installer/data/mysql/fr-FR/2-Optionel/sample_frequencies.txt create mode 100644 installer/data/mysql/fr-FR/2-Optionel/sample_numberpatterns.sql create mode 100644 installer/data/mysql/fr-FR/2-Optionel/sample_numberpatterns.txt create mode 100644 installer/data/mysql/it-IT/optional/sample_frequencies.sql create mode 100644 installer/data/mysql/it-IT/optional/sample_frequencies.txt create mode 100644 installer/data/mysql/it-IT/optional/sample_numberpatterns.sql create mode 100644 installer/data/mysql/it-IT/optional/sample_numberpatterns.txt create mode 100644 installer/data/mysql/nb-NO/2-Valgfritt/sample_frequencies.sql create mode 100644 installer/data/mysql/nb-NO/2-Valgfritt/sample_frequencies.txt create mode 100644 installer/data/mysql/nb-NO/2-Valgfritt/sample_numberpatterns.sql create mode 100644 installer/data/mysql/nb-NO/2-Valgfritt/sample_numberpatterns.txt create mode 100644 installer/data/mysql/pl-PL/optional/sample_frequencies.sql create mode 100644 installer/data/mysql/pl-PL/optional/sample_frequencies.txt create mode 100644 installer/data/mysql/pl-PL/optional/sample_numberpatterns.sql create mode 100644 installer/data/mysql/pl-PL/optional/sample_numberpatterns.txt create mode 100644 installer/data/mysql/ru-RU/optional/sample_frequencies.sql create mode 100644 installer/data/mysql/ru-RU/optional/sample_frequencies.txt create mode 100644 installer/data/mysql/ru-RU/optional/sample_numberpatterns.sql create mode 100644 installer/data/mysql/ru-RU/optional/sample_numberpatterns.txt create mode 100644 installer/data/mysql/uk-UA/optional/sample_frequencies.sql create mode 100644 installer/data/mysql/uk-UA/optional/sample_frequencies.txt create mode 100644 installer/data/mysql/uk-UA/optional/sample_numberpatterns.sql create mode 100644 installer/data/mysql/uk-UA/optional/sample_numberpatterns.txt diff --git a/installer/data/mysql/de-DE/optional/sample_frequencies.sql b/installer/data/mysql/de-DE/optional/sample_frequencies.sql new file mode 100644 index 0000000..f09c8a2 --- /dev/null +++ b/installer/data/mysql/de-DE/optional/sample_frequencies.sql @@ -0,0 +1,16 @@ +INSERT INTO subscription_frequencies + (description, unit, unitsperissue, issuesperunit, displayorder) +VALUES + ('2/day', 'day', 1, 2, 1), + ('1/day', 'day', 1, 1, 2), + ('3/week', 'week', 1, 3, 3), + ('1/week', 'week', 1, 1, 4), + ('1/2 weeks', 'week', 2, 1, 5), + ('1/3 weeks', 'week', 3, 1, 6), + ('1/month', 'month', 1, 1, 7), + ('1/2 months', 'month', 2, 1, 8), + ('1/3 months', 'month', 3, 1, 9), + ('2/year', 'month', 6, 1, 10), + ('1/year', 'year', 1, 1, 11), + ('1/2 year', 'year', 2, 1, 12), + ('Irregular', NULL, 1, 1, 13); diff --git a/installer/data/mysql/de-DE/optional/sample_frequencies.txt b/installer/data/mysql/de-DE/optional/sample_frequencies.txt new file mode 100644 index 0000000..8754f46 --- /dev/null +++ b/installer/data/mysql/de-DE/optional/sample_frequencies.txt @@ -0,0 +1 @@ +Sample frequencies for subscriptions diff --git a/installer/data/mysql/de-DE/optional/sample_numberpatterns.sql b/installer/data/mysql/de-DE/optional/sample_numberpatterns.sql new file mode 100644 index 0000000..6b59300 --- /dev/null +++ b/installer/data/mysql/de-DE/optional/sample_numberpatterns.sql @@ -0,0 +1,25 @@ +INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) +VALUES + ('Number', 1, 'Simple Numbering method', 'No.{X}', + 'Number', 1, 1, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', + 'Volume', 1, 48, 99999, 1, NULL, + 'Number', 1, 4, 12, 1, NULL, + 'Issue', 1, 1, 4, 1, NULL), + + ('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', + 'Volume', 1, 12, 99999, 1, NULL, + 'Number', 1, 1, 12, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Seasonal', 4, 'Season Year', '{X} {Y}', + 'Season', 1, 1, 3, 0, 'season', + 'Year', 1, 4, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL); diff --git a/installer/data/mysql/de-DE/optional/sample_numberpatterns.txt b/installer/data/mysql/de-DE/optional/sample_numberpatterns.txt new file mode 100644 index 0000000..853db0f --- /dev/null +++ b/installer/data/mysql/de-DE/optional/sample_numberpatterns.txt @@ -0,0 +1 @@ +Sample numbering patterns for subscriptions diff --git a/installer/data/mysql/en/optional/sample_frequencies.sql b/installer/data/mysql/en/optional/sample_frequencies.sql new file mode 100644 index 0000000..f09c8a2 --- /dev/null +++ b/installer/data/mysql/en/optional/sample_frequencies.sql @@ -0,0 +1,16 @@ +INSERT INTO subscription_frequencies + (description, unit, unitsperissue, issuesperunit, displayorder) +VALUES + ('2/day', 'day', 1, 2, 1), + ('1/day', 'day', 1, 1, 2), + ('3/week', 'week', 1, 3, 3), + ('1/week', 'week', 1, 1, 4), + ('1/2 weeks', 'week', 2, 1, 5), + ('1/3 weeks', 'week', 3, 1, 6), + ('1/month', 'month', 1, 1, 7), + ('1/2 months', 'month', 2, 1, 8), + ('1/3 months', 'month', 3, 1, 9), + ('2/year', 'month', 6, 1, 10), + ('1/year', 'year', 1, 1, 11), + ('1/2 year', 'year', 2, 1, 12), + ('Irregular', NULL, 1, 1, 13); diff --git a/installer/data/mysql/en/optional/sample_frequencies.txt b/installer/data/mysql/en/optional/sample_frequencies.txt new file mode 100644 index 0000000..8754f46 --- /dev/null +++ b/installer/data/mysql/en/optional/sample_frequencies.txt @@ -0,0 +1 @@ +Sample frequencies for subscriptions diff --git a/installer/data/mysql/en/optional/sample_numberpatterns.sql b/installer/data/mysql/en/optional/sample_numberpatterns.sql new file mode 100644 index 0000000..6b59300 --- /dev/null +++ b/installer/data/mysql/en/optional/sample_numberpatterns.sql @@ -0,0 +1,25 @@ +INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) +VALUES + ('Number', 1, 'Simple Numbering method', 'No.{X}', + 'Number', 1, 1, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', + 'Volume', 1, 48, 99999, 1, NULL, + 'Number', 1, 4, 12, 1, NULL, + 'Issue', 1, 1, 4, 1, NULL), + + ('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', + 'Volume', 1, 12, 99999, 1, NULL, + 'Number', 1, 1, 12, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Seasonal', 4, 'Season Year', '{X} {Y}', + 'Season', 1, 1, 3, 0, 'season', + 'Year', 1, 4, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL); diff --git a/installer/data/mysql/en/optional/sample_numberpatterns.txt b/installer/data/mysql/en/optional/sample_numberpatterns.txt new file mode 100644 index 0000000..853db0f --- /dev/null +++ b/installer/data/mysql/en/optional/sample_numberpatterns.txt @@ -0,0 +1 @@ +Sample numbering patterns for subscriptions diff --git a/installer/data/mysql/es-ES/optional/sample_frequencies.sql b/installer/data/mysql/es-ES/optional/sample_frequencies.sql new file mode 100644 index 0000000..f09c8a2 --- /dev/null +++ b/installer/data/mysql/es-ES/optional/sample_frequencies.sql @@ -0,0 +1,16 @@ +INSERT INTO subscription_frequencies + (description, unit, unitsperissue, issuesperunit, displayorder) +VALUES + ('2/day', 'day', 1, 2, 1), + ('1/day', 'day', 1, 1, 2), + ('3/week', 'week', 1, 3, 3), + ('1/week', 'week', 1, 1, 4), + ('1/2 weeks', 'week', 2, 1, 5), + ('1/3 weeks', 'week', 3, 1, 6), + ('1/month', 'month', 1, 1, 7), + ('1/2 months', 'month', 2, 1, 8), + ('1/3 months', 'month', 3, 1, 9), + ('2/year', 'month', 6, 1, 10), + ('1/year', 'year', 1, 1, 11), + ('1/2 year', 'year', 2, 1, 12), + ('Irregular', NULL, 1, 1, 13); diff --git a/installer/data/mysql/es-ES/optional/sample_frequencies.txt b/installer/data/mysql/es-ES/optional/sample_frequencies.txt new file mode 100644 index 0000000..8754f46 --- /dev/null +++ b/installer/data/mysql/es-ES/optional/sample_frequencies.txt @@ -0,0 +1 @@ +Sample frequencies for subscriptions diff --git a/installer/data/mysql/es-ES/optional/sample_numberpatterns.sql b/installer/data/mysql/es-ES/optional/sample_numberpatterns.sql new file mode 100644 index 0000000..6b59300 --- /dev/null +++ b/installer/data/mysql/es-ES/optional/sample_numberpatterns.sql @@ -0,0 +1,25 @@ +INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) +VALUES + ('Number', 1, 'Simple Numbering method', 'No.{X}', + 'Number', 1, 1, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', + 'Volume', 1, 48, 99999, 1, NULL, + 'Number', 1, 4, 12, 1, NULL, + 'Issue', 1, 1, 4, 1, NULL), + + ('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', + 'Volume', 1, 12, 99999, 1, NULL, + 'Number', 1, 1, 12, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Seasonal', 4, 'Season Year', '{X} {Y}', + 'Season', 1, 1, 3, 0, 'season', + 'Year', 1, 4, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL); diff --git a/installer/data/mysql/es-ES/optional/sample_numberpatterns.txt b/installer/data/mysql/es-ES/optional/sample_numberpatterns.txt new file mode 100644 index 0000000..853db0f --- /dev/null +++ b/installer/data/mysql/es-ES/optional/sample_numberpatterns.txt @@ -0,0 +1 @@ +Sample numbering patterns for subscriptions diff --git a/installer/data/mysql/fr-FR/2-Optionel/sample_frequencies.sql b/installer/data/mysql/fr-FR/2-Optionel/sample_frequencies.sql new file mode 100644 index 0000000..f09c8a2 --- /dev/null +++ b/installer/data/mysql/fr-FR/2-Optionel/sample_frequencies.sql @@ -0,0 +1,16 @@ +INSERT INTO subscription_frequencies + (description, unit, unitsperissue, issuesperunit, displayorder) +VALUES + ('2/day', 'day', 1, 2, 1), + ('1/day', 'day', 1, 1, 2), + ('3/week', 'week', 1, 3, 3), + ('1/week', 'week', 1, 1, 4), + ('1/2 weeks', 'week', 2, 1, 5), + ('1/3 weeks', 'week', 3, 1, 6), + ('1/month', 'month', 1, 1, 7), + ('1/2 months', 'month', 2, 1, 8), + ('1/3 months', 'month', 3, 1, 9), + ('2/year', 'month', 6, 1, 10), + ('1/year', 'year', 1, 1, 11), + ('1/2 year', 'year', 2, 1, 12), + ('Irregular', NULL, 1, 1, 13); diff --git a/installer/data/mysql/fr-FR/2-Optionel/sample_frequencies.txt b/installer/data/mysql/fr-FR/2-Optionel/sample_frequencies.txt new file mode 100644 index 0000000..8754f46 --- /dev/null +++ b/installer/data/mysql/fr-FR/2-Optionel/sample_frequencies.txt @@ -0,0 +1 @@ +Sample frequencies for subscriptions diff --git a/installer/data/mysql/fr-FR/2-Optionel/sample_numberpatterns.sql b/installer/data/mysql/fr-FR/2-Optionel/sample_numberpatterns.sql new file mode 100644 index 0000000..6b59300 --- /dev/null +++ b/installer/data/mysql/fr-FR/2-Optionel/sample_numberpatterns.sql @@ -0,0 +1,25 @@ +INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) +VALUES + ('Number', 1, 'Simple Numbering method', 'No.{X}', + 'Number', 1, 1, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', + 'Volume', 1, 48, 99999, 1, NULL, + 'Number', 1, 4, 12, 1, NULL, + 'Issue', 1, 1, 4, 1, NULL), + + ('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', + 'Volume', 1, 12, 99999, 1, NULL, + 'Number', 1, 1, 12, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Seasonal', 4, 'Season Year', '{X} {Y}', + 'Season', 1, 1, 3, 0, 'season', + 'Year', 1, 4, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL); diff --git a/installer/data/mysql/fr-FR/2-Optionel/sample_numberpatterns.txt b/installer/data/mysql/fr-FR/2-Optionel/sample_numberpatterns.txt new file mode 100644 index 0000000..853db0f --- /dev/null +++ b/installer/data/mysql/fr-FR/2-Optionel/sample_numberpatterns.txt @@ -0,0 +1 @@ +Sample numbering patterns for subscriptions diff --git a/installer/data/mysql/it-IT/optional/sample_frequencies.sql b/installer/data/mysql/it-IT/optional/sample_frequencies.sql new file mode 100644 index 0000000..f09c8a2 --- /dev/null +++ b/installer/data/mysql/it-IT/optional/sample_frequencies.sql @@ -0,0 +1,16 @@ +INSERT INTO subscription_frequencies + (description, unit, unitsperissue, issuesperunit, displayorder) +VALUES + ('2/day', 'day', 1, 2, 1), + ('1/day', 'day', 1, 1, 2), + ('3/week', 'week', 1, 3, 3), + ('1/week', 'week', 1, 1, 4), + ('1/2 weeks', 'week', 2, 1, 5), + ('1/3 weeks', 'week', 3, 1, 6), + ('1/month', 'month', 1, 1, 7), + ('1/2 months', 'month', 2, 1, 8), + ('1/3 months', 'month', 3, 1, 9), + ('2/year', 'month', 6, 1, 10), + ('1/year', 'year', 1, 1, 11), + ('1/2 year', 'year', 2, 1, 12), + ('Irregular', NULL, 1, 1, 13); diff --git a/installer/data/mysql/it-IT/optional/sample_frequencies.txt b/installer/data/mysql/it-IT/optional/sample_frequencies.txt new file mode 100644 index 0000000..8754f46 --- /dev/null +++ b/installer/data/mysql/it-IT/optional/sample_frequencies.txt @@ -0,0 +1 @@ +Sample frequencies for subscriptions diff --git a/installer/data/mysql/it-IT/optional/sample_numberpatterns.sql b/installer/data/mysql/it-IT/optional/sample_numberpatterns.sql new file mode 100644 index 0000000..6b59300 --- /dev/null +++ b/installer/data/mysql/it-IT/optional/sample_numberpatterns.sql @@ -0,0 +1,25 @@ +INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) +VALUES + ('Number', 1, 'Simple Numbering method', 'No.{X}', + 'Number', 1, 1, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', + 'Volume', 1, 48, 99999, 1, NULL, + 'Number', 1, 4, 12, 1, NULL, + 'Issue', 1, 1, 4, 1, NULL), + + ('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', + 'Volume', 1, 12, 99999, 1, NULL, + 'Number', 1, 1, 12, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Seasonal', 4, 'Season Year', '{X} {Y}', + 'Season', 1, 1, 3, 0, 'season', + 'Year', 1, 4, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL); diff --git a/installer/data/mysql/it-IT/optional/sample_numberpatterns.txt b/installer/data/mysql/it-IT/optional/sample_numberpatterns.txt new file mode 100644 index 0000000..853db0f --- /dev/null +++ b/installer/data/mysql/it-IT/optional/sample_numberpatterns.txt @@ -0,0 +1 @@ +Sample numbering patterns for subscriptions diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 4912e19..146e0cf 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1824,6 +1824,53 @@ DROP TABLE IF EXISTS `stopwords`; ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table structure for table subscription_frequencies +-- + +DROP TABLE IF EXISTS subscription_frequencies; +CREATE TABLE subscription_frequencies ( + id INTEGER NOT NULL AUTO_INCREMENT, + description TEXT NOT NULL, + displayorder INT DEFAULT NULL, + unit ENUM('day','week','month','year') DEFAULT NULL, + unitsperissue INTEGER NOT NULL DEFAULT '1', + issuesperunit INTEGER NOT NULL DEFAULT '1', + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table subscription_numberpatterns +-- + +DROP TABLE IF EXISTS subscription_numberpatterns; +CREATE TABLE subscription_numberpatterns ( + id INTEGER NOT NULL AUTO_INCREMENT, + label VARCHAR(255) NOT NULL, + displayorder INTEGER DEFAULT NULL, + description TEXT NOT NULL, + numberingmethod VARCHAR(255) NOT NULL, + label1 VARCHAR(255) DEFAULT NULL, + add1 INTEGER DEFAULT NULL, + every1 INTEGER DEFAULT NULL, + whenmorethan1 INTEGER DEFAULT NULL, + setto1 INTEGER DEFAULT NULL, + numbering1 VARCHAR(255) DEFAULT NULL, + label2 VARCHAR(255) DEFAULT NULL, + add2 INTEGER DEFAULT NULL, + every2 INTEGER DEFAULT NULL, + whenmorethan2 INTEGER DEFAULT NULL, + setto2 INTEGER DEFAULT NULL, + numbering2 VARCHAR(255) DEFAULT NULL, + label3 VARCHAR(255) DEFAULT NULL, + add3 INTEGER DEFAULT NULL, + every3 INTEGER DEFAULT NULL, + whenmorethan3 INTEGER DEFAULT NULL, + setto3 INTEGER DEFAULT NULL, + numbering3 VARCHAR(255) DEFAULT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table structure for table `subscription` -- @@ -1840,47 +1887,36 @@ CREATE TABLE `subscription` ( `monthlength` int(11) default 0, `numberlength` int(11) default 0, `periodicity` tinyint(4) default 0, - `dow` varchar(100) default '', - `numberingmethod` varchar(100) default '', + countissuesperunit INTEGER NOT NULL DEFAULT 0, `notes` mediumtext, `status` varchar(100) NOT NULL default '', - `add1` int(11) default 0, - `every1` int(11) default 0, - `whenmorethan1` int(11) default 0, - `setto1` int(11) default NULL, `lastvalue1` int(11) default NULL, - `add2` int(11) default 0, - `every2` int(11) default 0, - `whenmorethan2` int(11) default 0, - `setto2` int(11) default NULL, - `lastvalue2` int(11) default NULL, - `add3` int(11) default 0, - `every3` int(11) default 0, `innerloop1` int(11) default 0, + `lastvalue2` int(11) default NULL, `innerloop2` int(11) default 0, - `innerloop3` int(11) default 0, - `whenmorethan3` int(11) default 0, - `setto3` int(11) default NULL, `lastvalue3` int(11) default NULL, - `issuesatonce` tinyint(3) NOT NULL default 1, + `innerloop3` int(11) default 0, `firstacquidate` date default NULL, `manualhistory` tinyint(1) NOT NULL default 0, `irregularity` text, + skip_serialseq BOOLEAN NOT NULL DEFAULT 0, `letter` varchar(20) default NULL, `numberpattern` tinyint(3) default 0, + locale VARCHAR(80) DEFAULT NULL, `distributedto` text, `internalnotes` longtext, `callnumber` text, `location` varchar(80) NULL default '', `branchcode` varchar(10) NOT NULL default '', - `hemisphere` tinyint(3) default 0, `lastbranch` varchar(10), `serialsadditems` tinyint(1) NOT NULL default '0', `staffdisplaycount` VARCHAR(10) NULL, `opacdisplaycount` VARCHAR(10) NULL, `graceperiod` int(11) NOT NULL default '0', `enddate` date default NULL, - PRIMARY KEY (`subscriptionid`) + PRIMARY KEY (`subscriptionid`), + CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id), + CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- diff --git a/installer/data/mysql/nb-NO/2-Valgfritt/sample_frequencies.sql b/installer/data/mysql/nb-NO/2-Valgfritt/sample_frequencies.sql new file mode 100644 index 0000000..f09c8a2 --- /dev/null +++ b/installer/data/mysql/nb-NO/2-Valgfritt/sample_frequencies.sql @@ -0,0 +1,16 @@ +INSERT INTO subscription_frequencies + (description, unit, unitsperissue, issuesperunit, displayorder) +VALUES + ('2/day', 'day', 1, 2, 1), + ('1/day', 'day', 1, 1, 2), + ('3/week', 'week', 1, 3, 3), + ('1/week', 'week', 1, 1, 4), + ('1/2 weeks', 'week', 2, 1, 5), + ('1/3 weeks', 'week', 3, 1, 6), + ('1/month', 'month', 1, 1, 7), + ('1/2 months', 'month', 2, 1, 8), + ('1/3 months', 'month', 3, 1, 9), + ('2/year', 'month', 6, 1, 10), + ('1/year', 'year', 1, 1, 11), + ('1/2 year', 'year', 2, 1, 12), + ('Irregular', NULL, 1, 1, 13); diff --git a/installer/data/mysql/nb-NO/2-Valgfritt/sample_frequencies.txt b/installer/data/mysql/nb-NO/2-Valgfritt/sample_frequencies.txt new file mode 100644 index 0000000..8754f46 --- /dev/null +++ b/installer/data/mysql/nb-NO/2-Valgfritt/sample_frequencies.txt @@ -0,0 +1 @@ +Sample frequencies for subscriptions diff --git a/installer/data/mysql/nb-NO/2-Valgfritt/sample_numberpatterns.sql b/installer/data/mysql/nb-NO/2-Valgfritt/sample_numberpatterns.sql new file mode 100644 index 0000000..6b59300 --- /dev/null +++ b/installer/data/mysql/nb-NO/2-Valgfritt/sample_numberpatterns.sql @@ -0,0 +1,25 @@ +INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) +VALUES + ('Number', 1, 'Simple Numbering method', 'No.{X}', + 'Number', 1, 1, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', + 'Volume', 1, 48, 99999, 1, NULL, + 'Number', 1, 4, 12, 1, NULL, + 'Issue', 1, 1, 4, 1, NULL), + + ('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', + 'Volume', 1, 12, 99999, 1, NULL, + 'Number', 1, 1, 12, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Seasonal', 4, 'Season Year', '{X} {Y}', + 'Season', 1, 1, 3, 0, 'season', + 'Year', 1, 4, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL); diff --git a/installer/data/mysql/nb-NO/2-Valgfritt/sample_numberpatterns.txt b/installer/data/mysql/nb-NO/2-Valgfritt/sample_numberpatterns.txt new file mode 100644 index 0000000..853db0f --- /dev/null +++ b/installer/data/mysql/nb-NO/2-Valgfritt/sample_numberpatterns.txt @@ -0,0 +1 @@ +Sample numbering patterns for subscriptions diff --git a/installer/data/mysql/pl-PL/optional/sample_frequencies.sql b/installer/data/mysql/pl-PL/optional/sample_frequencies.sql new file mode 100644 index 0000000..f09c8a2 --- /dev/null +++ b/installer/data/mysql/pl-PL/optional/sample_frequencies.sql @@ -0,0 +1,16 @@ +INSERT INTO subscription_frequencies + (description, unit, unitsperissue, issuesperunit, displayorder) +VALUES + ('2/day', 'day', 1, 2, 1), + ('1/day', 'day', 1, 1, 2), + ('3/week', 'week', 1, 3, 3), + ('1/week', 'week', 1, 1, 4), + ('1/2 weeks', 'week', 2, 1, 5), + ('1/3 weeks', 'week', 3, 1, 6), + ('1/month', 'month', 1, 1, 7), + ('1/2 months', 'month', 2, 1, 8), + ('1/3 months', 'month', 3, 1, 9), + ('2/year', 'month', 6, 1, 10), + ('1/year', 'year', 1, 1, 11), + ('1/2 year', 'year', 2, 1, 12), + ('Irregular', NULL, 1, 1, 13); diff --git a/installer/data/mysql/pl-PL/optional/sample_frequencies.txt b/installer/data/mysql/pl-PL/optional/sample_frequencies.txt new file mode 100644 index 0000000..8754f46 --- /dev/null +++ b/installer/data/mysql/pl-PL/optional/sample_frequencies.txt @@ -0,0 +1 @@ +Sample frequencies for subscriptions diff --git a/installer/data/mysql/pl-PL/optional/sample_numberpatterns.sql b/installer/data/mysql/pl-PL/optional/sample_numberpatterns.sql new file mode 100644 index 0000000..6b59300 --- /dev/null +++ b/installer/data/mysql/pl-PL/optional/sample_numberpatterns.sql @@ -0,0 +1,25 @@ +INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) +VALUES + ('Number', 1, 'Simple Numbering method', 'No.{X}', + 'Number', 1, 1, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', + 'Volume', 1, 48, 99999, 1, NULL, + 'Number', 1, 4, 12, 1, NULL, + 'Issue', 1, 1, 4, 1, NULL), + + ('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', + 'Volume', 1, 12, 99999, 1, NULL, + 'Number', 1, 1, 12, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Seasonal', 4, 'Season Year', '{X} {Y}', + 'Season', 1, 1, 3, 0, 'season', + 'Year', 1, 4, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL); diff --git a/installer/data/mysql/pl-PL/optional/sample_numberpatterns.txt b/installer/data/mysql/pl-PL/optional/sample_numberpatterns.txt new file mode 100644 index 0000000..853db0f --- /dev/null +++ b/installer/data/mysql/pl-PL/optional/sample_numberpatterns.txt @@ -0,0 +1 @@ +Sample numbering patterns for subscriptions diff --git a/installer/data/mysql/ru-RU/optional/sample_frequencies.sql b/installer/data/mysql/ru-RU/optional/sample_frequencies.sql new file mode 100644 index 0000000..f09c8a2 --- /dev/null +++ b/installer/data/mysql/ru-RU/optional/sample_frequencies.sql @@ -0,0 +1,16 @@ +INSERT INTO subscription_frequencies + (description, unit, unitsperissue, issuesperunit, displayorder) +VALUES + ('2/day', 'day', 1, 2, 1), + ('1/day', 'day', 1, 1, 2), + ('3/week', 'week', 1, 3, 3), + ('1/week', 'week', 1, 1, 4), + ('1/2 weeks', 'week', 2, 1, 5), + ('1/3 weeks', 'week', 3, 1, 6), + ('1/month', 'month', 1, 1, 7), + ('1/2 months', 'month', 2, 1, 8), + ('1/3 months', 'month', 3, 1, 9), + ('2/year', 'month', 6, 1, 10), + ('1/year', 'year', 1, 1, 11), + ('1/2 year', 'year', 2, 1, 12), + ('Irregular', NULL, 1, 1, 13); diff --git a/installer/data/mysql/ru-RU/optional/sample_frequencies.txt b/installer/data/mysql/ru-RU/optional/sample_frequencies.txt new file mode 100644 index 0000000..8754f46 --- /dev/null +++ b/installer/data/mysql/ru-RU/optional/sample_frequencies.txt @@ -0,0 +1 @@ +Sample frequencies for subscriptions diff --git a/installer/data/mysql/ru-RU/optional/sample_numberpatterns.sql b/installer/data/mysql/ru-RU/optional/sample_numberpatterns.sql new file mode 100644 index 0000000..6b59300 --- /dev/null +++ b/installer/data/mysql/ru-RU/optional/sample_numberpatterns.sql @@ -0,0 +1,25 @@ +INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) +VALUES + ('Number', 1, 'Simple Numbering method', 'No.{X}', + 'Number', 1, 1, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', + 'Volume', 1, 48, 99999, 1, NULL, + 'Number', 1, 4, 12, 1, NULL, + 'Issue', 1, 1, 4, 1, NULL), + + ('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', + 'Volume', 1, 12, 99999, 1, NULL, + 'Number', 1, 1, 12, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Seasonal', 4, 'Season Year', '{X} {Y}', + 'Season', 1, 1, 3, 0, 'season', + 'Year', 1, 4, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL); diff --git a/installer/data/mysql/ru-RU/optional/sample_numberpatterns.txt b/installer/data/mysql/ru-RU/optional/sample_numberpatterns.txt new file mode 100644 index 0000000..853db0f --- /dev/null +++ b/installer/data/mysql/ru-RU/optional/sample_numberpatterns.txt @@ -0,0 +1 @@ +Sample numbering patterns for subscriptions diff --git a/installer/data/mysql/uk-UA/optional/sample_frequencies.sql b/installer/data/mysql/uk-UA/optional/sample_frequencies.sql new file mode 100644 index 0000000..f09c8a2 --- /dev/null +++ b/installer/data/mysql/uk-UA/optional/sample_frequencies.sql @@ -0,0 +1,16 @@ +INSERT INTO subscription_frequencies + (description, unit, unitsperissue, issuesperunit, displayorder) +VALUES + ('2/day', 'day', 1, 2, 1), + ('1/day', 'day', 1, 1, 2), + ('3/week', 'week', 1, 3, 3), + ('1/week', 'week', 1, 1, 4), + ('1/2 weeks', 'week', 2, 1, 5), + ('1/3 weeks', 'week', 3, 1, 6), + ('1/month', 'month', 1, 1, 7), + ('1/2 months', 'month', 2, 1, 8), + ('1/3 months', 'month', 3, 1, 9), + ('2/year', 'month', 6, 1, 10), + ('1/year', 'year', 1, 1, 11), + ('1/2 year', 'year', 2, 1, 12), + ('Irregular', NULL, 1, 1, 13); diff --git a/installer/data/mysql/uk-UA/optional/sample_frequencies.txt b/installer/data/mysql/uk-UA/optional/sample_frequencies.txt new file mode 100644 index 0000000..8754f46 --- /dev/null +++ b/installer/data/mysql/uk-UA/optional/sample_frequencies.txt @@ -0,0 +1 @@ +Sample frequencies for subscriptions diff --git a/installer/data/mysql/uk-UA/optional/sample_numberpatterns.sql b/installer/data/mysql/uk-UA/optional/sample_numberpatterns.sql new file mode 100644 index 0000000..6b59300 --- /dev/null +++ b/installer/data/mysql/uk-UA/optional/sample_numberpatterns.sql @@ -0,0 +1,25 @@ +INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) +VALUES + ('Number', 1, 'Simple Numbering method', 'No.{X}', + 'Number', 1, 1, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', + 'Volume', 1, 48, 99999, 1, NULL, + 'Number', 1, 4, 12, 1, NULL, + 'Issue', 1, 1, 4, 1, NULL), + + ('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', + 'Volume', 1, 12, 99999, 1, NULL, + 'Number', 1, 1, 12, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Seasonal', 4, 'Season Year', '{X} {Y}', + 'Season', 1, 1, 3, 0, 'season', + 'Year', 1, 4, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL); diff --git a/installer/data/mysql/uk-UA/optional/sample_numberpatterns.txt b/installer/data/mysql/uk-UA/optional/sample_numberpatterns.txt new file mode 100644 index 0000000..853db0f --- /dev/null +++ b/installer/data/mysql/uk-UA/optional/sample_numberpatterns.txt @@ -0,0 +1 @@ +Sample numbering patterns for subscriptions diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 503c8ee..5809722 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5146,6 +5146,202 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do(qq| + DROP TABLE IF EXISTS subscription_frequencies + |); + $dbh->do(qq| + CREATE TABLE subscription_frequencies ( + id INTEGER NOT NULL AUTO_INCREMENT, + description TEXT NOT NULL, + displayorder INT DEFAULT NULL, + unit ENUM('day','week','month','year') DEFAULT NULL, + unitsperissue INTEGER NOT NULL DEFAULT '1', + issuesperunit INTEGER NOT NULL DEFAULT '1', + PRIMARY KEY (id) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 + |); + + $dbh->do(qq| + DROP TABLE IF EXISTS subscription_numberpatterns + |); + $dbh->do(qq| + CREATE TABLE subscription_numberpatterns ( + id INTEGER NOT NULL AUTO_INCREMENT, + label VARCHAR(255) NOT NULL, + displayorder INTEGER DEFAULT NULL, + description TEXT NOT NULL, + numberingmethod VARCHAR(255) NOT NULL, + label1 VARCHAR(255) DEFAULT NULL, + add1 INTEGER DEFAULT NULL, + every1 INTEGER DEFAULT NULL, + whenmorethan1 INTEGER DEFAULT NULL, + setto1 INTEGER DEFAULT NULL, + numbering1 VARCHAR(255) DEFAULT NULL, + label2 VARCHAR(255) DEFAULT NULL, + add2 INTEGER DEFAULT NULL, + every2 INTEGER DEFAULT NULL, + whenmorethan2 INTEGER DEFAULT NULL, + setto2 INTEGER DEFAULT NULL, + numbering2 VARCHAR(255) DEFAULT NULL, + label3 VARCHAR(255) DEFAULT NULL, + add3 INTEGER DEFAULT NULL, + every3 INTEGER DEFAULT NULL, + whenmorethan3 INTEGER DEFAULT NULL, + setto3 INTEGER DEFAULT NULL, + numbering3 VARCHAR(255) DEFAULT NULL, + PRIMARY KEY (id) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 + |); + + $dbh->do(qq| + INSERT INTO subscription_frequencies (description, unit, unitsperissue, issuesperunit, displayorder) + VALUES + ('2/day', 'day', 1, 2, 1), + ('1/day', 'day', 1, 1, 2), + ('3/week', 'week', 1, 3, 3), + ('1/week', 'week', 1, 1, 4), + ('1/2 weeks', 'week', 2, 1, 5), + ('1/3 weeks', 'week', 3, 1, 6), + ('1/month', 'month', 1, 1, 7), + ('1/2 months', 'month', 2, 1, 8), + ('1/3 months', 'month', 3, 1, 9), + ('2/year', 'month', 6, 1, 10), + ('1/year', 'year', 1, 1, 11), + ('1/2 year', 'year', 2, 1, 12), + ('Irregular', NULL, 1, 1, 13) + |); + + # Used to link existing subscription to newly created frequencies + my $frequencies_mapping = { # keys are old frequency numbers, values are the new ones + 1 => 2, # daily (n/week) + 2 => 4, # 1/week + 3 => 5, # 1/2 weeks + 4 => 6, # 1/3 weeks + 5 => 7, # 1/month + 6 => 8, # 1/2 months (6/year) + 7 => 9, # 1/3 months (1/quarter) + 8 => 9, # 1/quarter (seasonal) + 9 => 10, # 2/year + 10 => 11, # 1/year + 11 => 12, # 1/2 years + 12 => 1, # 2/day + 16 => 13, # Without periodicity + 32 => 13, # Irregular + 48 => 13 # Unknown + }; + + $dbh->do(qq| + INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) + VALUES + ('Number', 1, 'Simple Numbering method', 'No.{X}', + 'Number', 1, 1, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Volume, Number, Issue', 2, 'Volume Number Issue 1', 'Vol.{X}, Number {Y}, Issue {Z}', + 'Volume', 1, 48, 99999, 1, NULL, + 'Number', 1, 4, 12, 1, NULL, + 'Issue', 1, 1, 4, 1, NULL), + + ('Volume, Number', 3, 'Volume Number 1', 'Vol {X}, No {Y}', + 'Volume', 1, 12, 99999, 1, NULL, + 'Number', 1, 1, 12, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL), + + ('Seasonal', 4, 'Season Year', '{X} {Y}', + 'Season', 1, 1, 3, 0, 'season', + 'Year', 1, 4, 99999, 1, NULL, + NULL, NULL, NULL, NULL, NULL, NULL) + |); + + $dbh->do(qq| + ALTER TABLE subscription + MODIFY COLUMN numberpattern INTEGER DEFAULT NULL, + MODIFY COLUMN periodicity INTEGER DEFAULT NULL + |); + + # Update existing subscriptions + + my $query = qq| + SELECT subscriptionid, periodicity, numberingmethod, + add1, every1, whenmorethan1, setto1, + add2, every2, whenmorethan2, setto2, + add3, every3, whenmorethan3, setto3 + FROM subscription + ORDER BY subscriptionid + |; + my $sth = $dbh->prepare($query); + $sth->execute; + my $insert_numberpatterns_sth = $dbh->prepare(qq| + INSERT INTO subscription_numberpatterns + (label, displayorder, description, numberingmethod, + label1, add1, every1, whenmorethan1, setto1, numbering1, + label2, add2, every2, whenmorethan2, setto2, numbering2, + label3, add3, every3, whenmorethan3, setto3, numbering3) + VALUES + (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + |); + my $update_subscription_sth = $dbh->prepare(qq| + UPDATE subscription + SET numberpattern = ?, + periodicity = ? + WHERE subscriptionid = ? + |); + + my $i = 1; + while(my $sub = $sth->fetchrow_hashref) { + # Create a new numbering pattern for each subscription + my $ok = $insert_numberpatterns_sth->execute( + "Backup pattern $i", 4+$i, "Automatically created pattern by updatedatabase", $sub->{numberingmethod}, + "X", $sub->{add1}, $sub->{every1}, $sub->{whenmorethan1}, $sub->{setto1}, undef, + "Y", $sub->{add2}, $sub->{every2}, $sub->{whenmorethan2}, $sub->{setto2}, undef, + "Z", $sub->{add3}, $sub->{every3}, $sub->{whenmorethan3}, $sub->{setto3}, undef + ); + if($ok) { + my $id = $dbh->last_insert_id(undef, undef, 'subscription_numberpatterns', undef); + # Link to subscription_numberpatterns and subscription_frequencies + $update_subscription_sth->execute($id, + $frequencies_mapping->{$sub->{periodicity}}, $sub->{subscriptionid}); + } + $i++; + } + + # Remove now useless columns + $dbh->do(qq| + ALTER TABLE subscription + DROP COLUMN numberingmethod, + DROP COLUMN add1, + DROP COLUMN every1, + DROP COLUMN whenmorethan1, + DROP COLUMN setto1, + DROP COLUMN add2, + DROP COLUMN every2, + DROP COLUMN whenmorethan2, + DROP COLUMN setto2, + DROP COLUMN add3, + DROP COLUMN every3, + DROP COLUMN whenmorethan3, + DROP COLUMN setto3, + DROP COLUMN dow, + DROP COLUMN issuesatonce, + DROP COLUMN hemisphere, + ADD COLUMN countissuesperunit INTEGER NOT NULL DEFAULT 0 AFTER periodicity, + ADD COLUMN skip_serialseq BOOLEAN NOT NULL DEFAULT 0 AFTER irregularity, + ADD COLUMN locale VARCHAR(80) DEFAULT NULL AFTER numberpattern, + ADD CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id), + ADD CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) + |); + + print "Upgrade to $DBversion done (Add subscription_frequencies and subscription_numberpatterns tables)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) -- 1.7.9.5