X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fupdatedatabase.pl;h=9c04a2587029d50be98f1355ce19db6f3590498f;hb=92433747b54ff3b91a0e301ea57e488ab26f74a3;hp=317680af231ec8461404e60b7ded1ccc83986a0a;hpb=2e5198ea47342e592ae437bdaf2dd79c96a9eaef;p=koha_fer

diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 317680af23..9c04a25870 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -5828,8 +5828,6 @@ if(C4::Context->preference("Version") < TransformToNum($DBversion) ) {
     SetVersion($DBversion);
 }
 
-
-
 $DBversion = "3.09.00.050";
 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     $dbh->do("ALTER TABLE authorised_values MODIFY category varchar(16) NOT NULL DEFAULT '';");
@@ -6336,7 +6334,6 @@ if ( CheckVersion($DBversion) ) {
    $dbh->do("INSERT INTO systempreferences ( variable, value, explanation, type ) VALUES ( 'SCOUserCSS', '', 'Add CSS to be included in the SCO module in an embedded <style> tag.', 'free' )");
    $dbh->do("INSERT INTO systempreferences ( variable, value, explanation, type ) VALUES ( 'SCOUserJS', '', 'Define custom javascript for inclusion in the SCO module', 'free' )");
    print "Upgrade to $DBversion done (Bug 9009: Add SCOUserCSS and SCOUserJS sysprefs)\n";
-   SetVersion ($DBversion);
 }
 
 $DBversion = "3.11.00.015";
@@ -6444,6 +6441,7 @@ if ( CheckVersion($DBversion) ) {
     SetVersion($DBversion);
 }
 
+
 $DBversion = "3.11.00.024";
 if ( CheckVersion($DBversion) ) {
     $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacItemLocation','callnum','Show the shelving location of items in the opac','callnum|ccode|location','Choice');");
@@ -6476,7 +6474,6 @@ if ( CheckVersion($DBversion) ) {
     SetVersion($DBversion);
 }
 
-
 $DBversion = "3.11.00.026";
 if ( CheckVersion($DBversion) ) {
     $dbh->do(qq{
@@ -6505,6 +6502,488 @@ if ( CheckVersion($DBversion) ) {
     SetVersion($DBversion);
 }
 
+$DBversion = "3.11.00.028";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do("INSERT IGNORE INTO systempreferences (variable,value,options,explanation,type) VALUES ('PatronSelfRegistrationAdditionalInstructions', '', NULL , 'A free text field to display additional instructions to newly self registered patrons.', 'free'    );");
+    print "Upgrade to $DBversion done (Bug 9756 - Patron self registration missing the system preference PatronSelfRegistrationAdditionalInstructions)\n";
+    SetVersion($DBversion);
+}
+
+$DBversion = "3.11.00.029";
+if (CheckVersion($DBversion)) {
+    $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('UseQueryParser', '0', 'If enabled, try to use QueryParser for queries.', NULL, 'YesNo')");
+    print "Upgrade to $DBversion done (Bug 9239: Make it possible for Koha to use QueryParser)\n";
+    SetVersion ($DBversion);
+}
+
+$DBversion = "3.11.00.030";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('FinesIncludeGracePeriod','1','If enabled, fines calculations will include the grace period.',NULL,'YesNo');");
+    print "Upgrade to $DBversion done (Add system preference FinesIncludeGracePeriod)\n";
+    SetVersion($DBversion);
+}
+
+$DBversion = "3.11.00.100";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+    print "Upgrade to $DBversion done (3.12-alpha release)\n";
+   SetVersion ($DBversion);
+}
+
+$DBversion = "3.11.00.101";
+if ( CheckVersion($DBversion) ) {
+   $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('UNIMARCAuthorsFacetsSeparator',', ', 'UNIMARC authors facets separator', NULL, 'short')");
+   print "Upgrade to $DBversion done (Bug 9341: Problem with UNIMARC authors facets)\n";
+   SetVersion ($DBversion);
+}
+
+$DBversion = "3.11.00.102";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do(q{
+        DELETE FROM systempreferences WHERE variable='NoZebra'
+    });
+    $dbh->do(q{
+        DELETE FROM systempreferences WHERE variable='QueryRemoveStopwords'
+    });
+    print "Upgrade to $DBversion done (Remove deprecated NoZebra and QueryRemoveStopwords sysprefs)\n";
+    SetVersion($DBversion);
+}
+
+$DBversion = "3.11.00.103";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do("DELETE FROM systempreferences WHERE variable = 'insecure';");
+    print "Upgrade to $DBversion done (Bug 9827 - Remove 'insecure' system preference)\n";
+    SetVersion($DBversion);
+}
+
+$DBversion = "3.11.00.104";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+    print "Upgrade to $DBversion done (3.12-alpha2 release)\n";
+    SetVersion ($DBversion);
+}
+
+$DBversion = "3.11.00.105";
+if ( CheckVersion($DBversion) ) {
+    if ( C4::Context->preference("marcflavour") eq 'MARC21' ) {
+        $sth = $dbh->prepare(
+"SELECT frameworkcode FROM marc_tag_structure WHERE tagfield = '029'"
+        );
+        $sth->execute;
+        my $frameworkcodes = $sth->fetchall_hashref('frameworkcode');
+
+        for my $frameworkcode ( keys %$frameworkcodes ) {
+            $dbh->do( "
+    INSERT IGNORE INTO marc_subfield_structure (tagfield, tagsubfield, liblibrarian,
+    libopac, repeatable, mandatory, kohafield, tab, authorised_value, authtypecode,
+    value_builder, isurl, hidden, frameworkcode, seealso, link, defaultvalue) VALUES
+    ('029', 'a', 'OCLC library identifier', 'OCLC library identifier', 0, 0, '', 0, '', '', '', 0, -6, '$frameworkcode', '', '', NULL),
+    ('029', 'b', 'System control number', 'System control number', 0, 0, '', 0, '', '', '', 0, -6, '$frameworkcode', '', '', NULL),
+    ('029', 'c', 'OAI set name', 'OAI set name', 0, 0, '', 0, '', '', '', 0, -6, '$frameworkcode', '', '', NULL),
+    ('029', 't', 'Content type identifier', 'Content type identifier', 0, 0, '', 0, '', '', '', 0, -6, '$frameworkcode', '', '', NULL)
+   " );
+        }
+
+        for my $tag ( '863', '864', '865' ) {
+            $sth = $dbh->prepare(
+"SELECT frameworkcode FROM marc_tag_structure WHERE tagfield = '$tag'"
+            );
+            $sth->execute;
+            my $frameworkcodes = $sth->fetchall_hashref('frameworkcode');
+
+            for my $frameworkcode ( keys %$frameworkcodes ) {
+                $dbh->do( "
+     INSERT IGNORE INTO marc_subfield_structure (tagfield, tagsubfield, liblibrarian,
+     libopac, repeatable, mandatory, kohafield, tab, authorised_value, authtypecode,
+     value_builder, isurl, hidden, frameworkcode, seealso, link, defaultvalue) VALUES
+     ('$tag', '6', 'Linkage', 'Linkage', 0, 0, '', 8, '', '', '', NULL, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', '8', 'Field link and sequence number', 'Field link and sequence number', 0, 0, '', 8, '', '', '', NULL, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'a', 'First level of enumeration', 'First level of enumeration', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'b', 'Second level of enumeration', 'Second level of enumeration', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'c', 'Third level of enumeration', 'Third level of enumeration', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'd', 'Fourth level of enumeration', 'Fourth level of enumeration', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'e', 'Fifth level of enumeration', 'Fifth level of enumeration', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'f', 'Sixth level of enumeration', 'Sixth level of enumeration', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'g', 'Alternative numbering scheme, first level of enumeration', 'Alternative numbering scheme, first level of enumeration', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'h', 'Alternative numbering scheme, second level of enumeration', 'Alternative numbering scheme, second level of enumeration', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'i', 'First level of chronology', 'First level of chronology', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'j', 'Second level of chronology', 'Second level of chronology', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'k', 'Third level of chronology', 'Third level of chronology', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'l', 'Fourth level of chronology', 'Fourth level of chronology', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'm', 'Alternative numbering scheme, chronology', 'Alternative numbering scheme, chronology', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'n', 'Converted Gregorian year', 'Converted Gregorian year', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'o', 'Type of unit', 'Type of unit', 1, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'p', 'Piece designation', 'Piece designation', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'q', 'Piece physical condition', 'Piece physical condition', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 's', 'Copyright article-fee code', 'Copyright article-fee code', 1, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 't', 'Copy number', 'Copy number', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'v', 'Issuing date', 'Issuing date', 1, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'w', 'Break indicator', 'Break indicator', 0, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'x', 'Nonpublic note', 'Nonpublic note', 1, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL),
+     ('$tag', 'z', 'Public note', 'Public note', 1, 0, '', 8, '', '', '', 0, 5, '$frameworkcode', '', '', NULL)
+    " );
+            }
+        }
+    }
+    print "Upgrade to $DBversion done (Bug 9353: Missing subfields on MARC21 frameworks)\n";
+    SetVersion($DBversion);
+}
+
+
+$DBversion = "3.11.00.106";
+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+    $dbh->do("INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES ('19', 'plugins', 'Koha plugins', '0')");
+    $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
+              ('19', 'manage', 'Manage plugins ( install / uninstall )'),
+              ('19', 'tool', 'Use tool plugins'),
+              ('19', 'report', 'Use report plugins'),
+              ('19', 'configure', 'Configure plugins')
+            ");
+    $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('UseKohaPlugins','0','Enable or disable the ability to use Koha Plugins.','','YesNo')");
+
+    $dbh->do("
+        CREATE TABLE IF NOT EXISTS plugin_data (
+            plugin_class varchar(255) NOT NULL,
+            plugin_key varchar(255) NOT NULL,
+            plugin_value text,
+            PRIMARY KEY (plugin_class,plugin_key)
+        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+    ");
+
+    print "Upgrade to $DBversion done (Bug 7804: Added plugin system.)\n";
+    SetVersion($DBversion);
+}
+
+$DBversion = "3.11.00.107";
+if ( CheckVersion($DBversion) ) {
+   $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type) VALUES ('TimeFormat','24hr','12hr|24hr','Defines the global time format for visual output.','Choice')");
+   print "Upgrade to $DBversion done (Bug 9014: Add syspref TimeFormat)\n";
+   SetVersion ($DBversion);
+}
+
+$DBversion = "3.11.00.108";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do("ALTER TABLE action_logs CHANGE timestamp timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;");
+    $dbh->do("UPDATE action_logs SET info=(SELECT itemnumber FROM items WHERE biblionumber= action_logs.info LIMIT 1) WHERE module='CIRCULATION' AND action in ('ISSUE','RETURN');");
+    $dbh->do("ALTER TABLE action_logs CHANGE timestamp timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;");
+    print "Upgrade to $DBversion done (Bug 7241: Fix on circulation logs)\n";
+    print "WARNING about bug 7241: to partially correct the broken logs, the log history is filled with the first found item for each biblio.\n";
+    SetVersion($DBversion);
+}
+
+$DBversion = "3.11.00.109";
+if ( CheckVersion($DBversion) ) {
+   $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type) VALUES('DisplayIconsXSLT', '1', '', 'If ON, displays the format, audience, and material type icons in XSLT MARC21 results and detail pages.', 'YesNo');");
+   print "Upgrade to $DBversion done (Bug 9403: Add DisplayIconsXSLT)\n";
+   SetVersion ($DBversion);
+}
+
+$DBversion = "3.11.00.110";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do("ALTER TABLE pending_offline_operations CHANGE barcode barcode VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
+    $dbh->do("ALTER TABLE pending_offline_operations ADD amount DECIMAL( 28, 6 ) NULL DEFAULT NULL");
+    print "Upgrade to $DBversion done (Bug 8220 - Allow koc uploads to go to process queue)\n";
+    SetVersion ($DBversion);
+}
+
+$DBversion = "3.11.00.111";
+if ( CheckVersion($DBversion) ) {
+    my $sth = $dbh->prepare("
+        SELECT module, code, branchcode, content
+        FROM letter
+        WHERE content LIKE '%<fine>%'
+    ");
+    $sth->execute;
+    my $sth_update = $dbh->prepare("UPDATE letter SET content = ? WHERE module = ? AND code = ? AND branchcode = ?");
+    while(my $row = $sth->fetchrow_hashref){
+        $row->{content} =~ s/<fine>\w+<\/fine>/<<items.fine>>/;
+        $sth_update->execute($row->{content}, $row->{module}, $row->{code}, $row->{branchcode});
+    }
+    print "Upgrade to $DBversion done (use new <<items.fine>> syntax in notices)\n";
+    SetVersion($DBversion);
+}
+
+$DBversion = "3.11.00.112";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do(qq{
+        ALTER TABLE issuingrules ADD COLUMN renewalperiod int(4) DEFAULT NULL AFTER renewalsallowed
+    });
+    $dbh->do(qq{
+        UPDATE issuingrules SET renewalperiod = issuelength
+    });
+    print "Upgrade to $DBversion done (Bug 8365: Add colum issuingrules.renewalperiod)\n";
+    SetVersion ($DBversion);
+}
+
+$DBversion = "3.11.00.113";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+    $dbh->do(q{
+        ALTER TABLE branchcategories ADD show_in_pulldown BOOLEAN NOT NULL DEFAULT '0',
+        ADD INDEX ( show_in_pulldown )
+    });
+    print "Upgrade to $DBversion done (Bug 9257 - Add groups to normal search pulldown)\n";
+    SetVersion ($DBversion);
+}
+
+$DBversion = "3.11.00.114";
+if ( CheckVersion($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 $check_numberpatterns_sth = $dbh->prepare(qq|
+        SELECT * FROM subscription_numberpatterns
+        WHERE add1 = ? AND add2 = ? AND add3 = ?
+          AND every1 = ? AND every2 = ? AND every3 = ?
+          AND whenmorethan1 = ? AND whenmorethan2 = ? AND whenmorethan3 = ?
+          AND setto1 = ? AND setto2 = ? AND setto3 = ?
+          AND numberingmethod = ?
+        LIMIT 1
+    |);
+    my $update_subscription_sth = $dbh->prepare(qq|
+        UPDATE subscription
+        SET numberpattern = ?,
+            periodicity = ?
+        WHERE subscriptionid = ?
+    |);
+
+    my $i = 1;
+    while(my $sub = $sth->fetchrow_hashref) {
+        $check_numberpatterns_sth->execute(
+            $sub->{add1}, $sub->{add2}, $sub->{add3},
+            $sub->{every1}, $sub->{every2}, $sub->{every3},
+            $sub->{whenmorethan1}, $sub->{whenmorethan2}, $sub->{whenmorethan3},
+            $sub->{setto1}, $sub->{setto2}, $sub->{setto3},
+            $sub->{numberingmethod}
+        );
+        my $p = $check_numberpatterns_sth->fetchrow_hashref;
+        if (defined $p) {
+            # Pattern already exists, link to it
+            $update_subscription_sth->execute($p->{id},
+                $frequencies_mapping->{$sub->{periodicity}},
+                $sub->{subscriptionid});
+        } else {
+            # Create a new numbering pattern for this 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 1 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) ON DELETE SET NULL ON UPDATE CASCADE,
+        ADD CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE
+    |);
+
+    # Set firstacquidate if not already set (firstacquidate is now mandatory)
+    my $get_first_planneddate_sth = $dbh->prepare(qq|
+        SELECT planneddate
+        FROM serial
+        WHERE subscriptionid = ?
+        ORDER BY serialid
+        LIMIT 1
+    |);
+    my $update_firstacquidate_sth = $dbh->prepare(qq|
+        UPDATE subscription
+        SET firstacquidate = ?
+        WHERE subscriptionid = ?
+    |);
+    my $get_subscriptions_sth = $dbh->prepare(qq|
+        SELECT subscriptionid, startdate
+        FROM subscription
+        WHERE firstacquidate IS NULL
+          OR firstacquidate = '0000-00-00'
+    |);
+    $get_subscriptions_sth->execute;
+    while ( my ($subscriptionid, $startdate) = $get_subscriptions_sth->fetchrow ) {
+        # Try to get the planned date of the first serial
+        $get_first_planneddate_sth->execute($subscriptionid);
+        my ($first_planneddate) = $get_first_planneddate_sth->fetchrow;
+        if ($first_planneddate and $first_planneddate =~ /^\d{4}-\d{2}-\d{2}$/) {
+            $update_firstacquidate_sth->execute($first_planneddate, $subscriptionid);
+        } else {
+            # Defaults to subscription start date
+            $update_firstacquidate_sth->execute($startdate, $subscriptionid);
+        }
+    }
+
+    print "Upgrade to $DBversion done (Bug 7688: Add subscription_frequencies and subscription_numberpatterns tables)\n";
+    SetVersion($DBversion);
+}
+
+$DBversion = "3.11.00.115";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type) VALUES ('HighlightOwnItemsOnOPAC','0','','If on, and a patron is logged into the OPAC, items from his or her home library will be emphasized and shown first in search results and item details.','YesNo')");
+    $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type) VALUES ('HighlightOwnItemsOnOPACWhich','PatronBranch','PatronBranch|OpacURLBranch','Decides which branch''s items to emphasize. If PatronBranch, emphasize the logged in user''s library''s items. If OpacURLBranch, highlight the items of the Apache var BRANCHCODE defined in Koha''s Apache configuration file.','Choice')");
+    print "Upgrade to $DBversion done (Bug 7740: Add syspref HighlightOwnItemsOnOPAC)\n";
+    SetVersion ($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
@@ -6527,6 +7006,7 @@ sub TableExists {
 Drop all foreign keys of the table $table
 
 =cut
+
 sub DropAllForeignKeys {
     my ($table) = @_;
     # get the table description