View | Details | Raw Unified | Return to bug 17234
Collapse All | Expand All

(-)a/installer/data/mysql/updatedatabase.pl (-74 / +146 lines)
Lines 10420-10434 if ( CheckVersion($DBversion) ) { Link Here
10420
10420
10421
$DBversion = "3.19.00.041";
10421
$DBversion = "3.19.00.041";
10422
if ( CheckVersion($DBversion) ) {
10422
if ( CheckVersion($DBversion) ) {
10423
    $dbh->do(q|
10423
    unless ( constraint_exists( 'suggestions', 'status' ) ) {
10424
        ALTER TABLE suggestions ADD KEY status (STATUS)
10424
        $dbh->do(q|
10425
    |);
10425
            ALTER TABLE suggestions ADD KEY status (STATUS)
10426
    $dbh->do(q|
10426
        |);
10427
        ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)
10427
    }
10428
    |);
10428
    unless ( constraint_exists( 'suggestions', 'biblionumber' ) ) {
10429
    $dbh->do(q|
10429
        $dbh->do(q|
10430
        ALTER TABLE suggestions ADD KEY branchcode (branchcode)
10430
            ALTER TABLE suggestions ADD KEY biblionumber (biblionumber)
10431
    |);
10431
        |);
10432
    }
10433
    unless ( constraint_exists( 'suggestions', 'branchcode' ) ) {
10434
        $dbh->do(q|
10435
            ALTER TABLE suggestions ADD KEY branchcode (branchcode)
10436
        |);
10437
    }
10432
    print "Upgrade to $DBversion done (Bug 14132: suggestions table is missing indexes)\n";
10438
    print "Upgrade to $DBversion done (Bug 14132: suggestions table is missing indexes)\n";
10433
    SetVersion ($DBversion);
10439
    SetVersion ($DBversion);
10434
}
10440
}
Lines 10442-10453 if ( CheckVersion($DBversion) ) { Link Here
10442
        WHERE auth_types.authtypecode IS NULL
10448
        WHERE auth_types.authtypecode IS NULL
10443
    });
10449
    });
10444
10450
10445
    $dbh->do(q{
10451
    unless ( constraint_exists( 'auth_subfield_structure', 'auth_subfield_structure_ibfk_1' ) ) {
10446
        ALTER TABLE auth_subfield_structure
10452
        $dbh->do(q{
10447
        ADD CONSTRAINT auth_subfield_structure_ibfk_1
10453
            ALTER TABLE auth_subfield_structure
10448
        FOREIGN KEY (authtypecode) REFERENCES auth_types(authtypecode)
10454
            ADD CONSTRAINT auth_subfield_structure_ibfk_1
10449
        ON DELETE CASCADE ON UPDATE CASCADE
10455
            FOREIGN KEY (authtypecode) REFERENCES auth_types(authtypecode)
10450
    });
10456
            ON DELETE CASCADE ON UPDATE CASCADE
10457
        });
10458
    }
10451
10459
10452
    print "Upgrade to $DBversion done (Bug 8480: Add foreign key on auth_subfield_structure.authtypecode)\n";
10460
    print "Upgrade to $DBversion done (Bug 8480: Add foreign key on auth_subfield_structure.authtypecode)\n";
10453
    SetVersion($DBversion);
10461
    SetVersion($DBversion);
Lines 10563-10593 if ( CheckVersion($DBversion) ) { Link Here
10563
10571
10564
$DBversion = "3.21.00.007";
10572
$DBversion = "3.21.00.007";
10565
if ( CheckVersion($DBversion) ) {
10573
if ( CheckVersion($DBversion) ) {
10566
    $dbh->do(q|
10574
    unless ( constraint_exists( 'aqbasket', 'authorisedby' ) ) {
10567
        ALTER TABLE aqbasket
10575
        $dbh->do(q|
10568
            ADD KEY authorisedby (authorisedby)
10576
            ALTER TABLE aqbasket
10569
    |);
10577
                ADD KEY authorisedby (authorisedby)
10570
    $dbh->do(q|
10578
        |);
10571
        ALTER TABLE aqbooksellers
10579
    }
10572
            ADD KEY name (name(255))
10580
    unless ( constraint_exists( 'aqbooksellers', 'name' ) ) {
10573
    |);
10581
        $dbh->do(q|
10574
    $dbh->do(q|
10582
            ALTER TABLE aqbooksellers
10575
        ALTER TABLE aqbudgets
10583
                ADD KEY name (name(255))
10576
            ADD KEY budget_parent_id (budget_parent_id),
10584
        |);
10577
            ADD KEY budget_code (budget_code),
10585
    }
10578
            ADD KEY budget_branchcode (budget_branchcode),
10586
    unless ( constraint_exists( 'aqbudgets', 'budget_parent_id' ) ) {
10579
            ADD KEY budget_period_id (budget_period_id),
10587
        $dbh->do(q|
10580
            ADD KEY budget_owner_id (budget_owner_id)
10588
            ALTER TABLE aqbudgets
10581
    |);
10589
                ADD KEY budget_parent_id (budget_parent_id)|);
10582
    $dbh->do(q|
10590
        }
10583
        ALTER TABLE aqbudgets_planning
10591
    unless ( constraint_exists( 'aqbudgets', 'budget_code' ) ) {
10584
            ADD KEY budget_period_id (budget_period_id)
10592
        $dbh->do(q|
10585
    |);
10593
            ALTER TABLE aqbudgets
10586
    $dbh->do(q|
10594
                ADD KEY budget_code (budget_code)|);
10587
        ALTER TABLE aqorders
10595
    }
10588
            ADD KEY parent_ordernumber (parent_ordernumber),
10596
    unless ( constraint_exists( 'aqbudgets', 'budget_branchcode' ) ) {
10589
            ADD KEY orderstatus (orderstatus)
10597
        $dbh->do(q|
10590
    |);
10598
            ALTER TABLE aqbudgets
10599
                ADD KEY budget_branchcode (budget_branchcode)|);
10600
    }
10601
    unless ( constraint_exists( 'aqbudgets', 'budget_period_id' ) ) {
10602
        $dbh->do(q|
10603
            ALTER TABLE aqbudgets
10604
                ADD KEY budget_period_id (budget_period_id)|);
10605
    }
10606
    unless ( constraint_exists( 'aqbudgets', 'budget_owner_id' ) ) {
10607
        $dbh->do(q|
10608
            ALTER TABLE aqbudgets
10609
                ADD KEY budget_owner_id (budget_owner_id)|);
10610
    }
10611
    unless ( constraint_exists( 'aqbudgets_planning', 'budget_period_id' ) ) {
10612
        $dbh->do(q|
10613
            ALTER TABLE aqbudgets_planning
10614
                ADD KEY budget_period_id (budget_period_id)|);
10615
    }
10616
    unless ( constraint_exists( 'aqorders', 'parent_ordernumber' ) ) {
10617
        $dbh->do(q|
10618
            ALTER TABLE aqorders
10619
                ADD KEY parent_ordernumber (parent_ordernumber)|);
10620
    }
10621
    unless ( constraint_exists( 'aqorders', 'orderstatus' ) ) {
10622
        $dbh->do(q|
10623
            ALTER TABLE aqorders
10624
                ADD KEY orderstatus (orderstatus)|);
10625
    }
10591
    print "Upgrade to $DBversion done (Bug 14053: Acquisition db tables are missing indexes)\n";
10626
    print "Upgrade to $DBversion done (Bug 14053: Acquisition db tables are missing indexes)\n";
10592
    SetVersion ($DBversion);
10627
    SetVersion ($DBversion);
10593
}
10628
}
Lines 10688-10696 if ( CheckVersion($DBversion) ) { Link Here
10688
        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
10723
        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)
10689
        VALUES ('OAI-PMH:DeletedRecord','persistent','Koha\'s deletedbiblio table will never be deleted (persistent) or might be deleted (transient)','transient|persistent','Choice')
10724
        VALUES ('OAI-PMH:DeletedRecord','persistent','Koha\'s deletedbiblio table will never be deleted (persistent) or might be deleted (transient)','transient|persistent','Choice')
10690
    });
10725
    });
10691
    $dbh->do(q|
10726
10692
        ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1
10727
    if ( constraint_exists( 'oai_sets_biblios', 'oai_sets_biblios_ibfk_1' ) ) {
10693
    |);
10728
        $dbh->do(q|
10729
            ALTER TABLE oai_sets_biblios DROP FOREIGN KEY oai_sets_biblios_ibfk_1
10730
        |);
10731
    }
10694
    print "Upgrade to $DBversion done (Bug 3206: OAI repository deleted record support)\n";
10732
    print "Upgrade to $DBversion done (Bug 3206: OAI repository deleted record support)\n";
10695
    SetVersion ($DBversion);
10733
    SetVersion ($DBversion);
10696
}
10734
}
Lines 10771-10782 if ( CheckVersion($DBversion) ) { Link Here
10771
10809
10772
$DBversion = "3.21.00.019";
10810
$DBversion = "3.21.00.019";
10773
if ( CheckVersion($DBversion) ) {
10811
if ( CheckVersion($DBversion) ) {
10774
    $dbh->do(q{
10812
    if ( column_exists( 'reserves', 'constrainttype' ) ) {
10775
        ALTER TABLE reserves DROP constrainttype
10813
        $dbh->do(q{
10776
    });
10814
            ALTER TABLE reserves DROP constrainttype
10777
    $dbh->do(q{
10815
        });
10778
        ALTER TABLE old_reserves DROP constrainttype
10816
        $dbh->do(q{
10779
    });
10817
            ALTER TABLE old_reserves DROP constrainttype
10818
        });
10819
    }
10780
    $dbh->do(q{
10820
    $dbh->do(q{
10781
        DROP TABLE IF EXISTS reserveconstraints
10821
        DROP TABLE IF EXISTS reserveconstraints
10782
    });
10822
    });
Lines 10816-10823 if ( CheckVersion($DBversion) ) { Link Here
10816
    my ($print_error) = $dbh->{PrintError};
10856
    my ($print_error) = $dbh->{PrintError};
10817
    $dbh->{RaiseError} = 0;
10857
    $dbh->{RaiseError} = 0;
10818
    $dbh->{PrintError} = 0;
10858
    $dbh->{PrintError} = 0;
10819
    $dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});
10859
    if ( constraint_exists('course_reserves', 'course_reserves_ibfk_2') ) {
10820
    $dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});
10860
        $dbh->do(q{ALTER TABLE course_reserves DROP FOREIGN KEY course_reserves_ibfk_2});
10861
        $dbh->do(q{ALTER TABLE course_reserves DROP INDEX course_reserves_ibfk_2});
10862
    }
10821
    $dbh->{PrintError} = $print_error;
10863
    $dbh->{PrintError} = $print_error;
10822
10864
10823
    $dbh->do(q{
10865
    $dbh->do(q{
Lines 10915-10942 if ( CheckVersion($DBversion) ) { Link Here
10915
10957
10916
$DBversion = "3.21.00.028";
10958
$DBversion = "3.21.00.028";
10917
if ( CheckVersion($DBversion) ) {
10959
if ( CheckVersion($DBversion) ) {
10918
    $dbh->do(q{
10960
    unless ( column_exists('uploaded_files', 'public') ) {
10919
        ALTER TABLE uploaded_files
10961
        $dbh->do(q{
10920
            ADD COLUMN public tinyint,
10962
            ALTER TABLE uploaded_files
10921
            ADD COLUMN permanent tinyint
10963
                ADD COLUMN public tinyint,
10922
    });
10964
                ADD COLUMN permanent tinyint
10923
    $dbh->do(q{
10965
        });
10924
        UPDATE uploaded_files SET public=1, permanent=1
10966
        $dbh->do(q{
10925
    });
10967
            UPDATE uploaded_files SET public=1, permanent=1
10926
    $dbh->do(q{
10968
        });
10927
        ALTER TABLE uploaded_files
10969
        $dbh->do(q{
10928
            CHANGE COLUMN categorycode uploadcategorycode tinytext
10970
            ALTER TABLE uploaded_files
10929
    });
10971
                CHANGE COLUMN categorycode uploadcategorycode tinytext
10972
        });
10973
    }
10930
    print "Upgrade to $DBversion done (Bug 14321: Merge UploadedFile and UploadedFiles into Koha::Upload)\n";
10974
    print "Upgrade to $DBversion done (Bug 14321: Merge UploadedFile and UploadedFiles into Koha::Upload)\n";
10931
    SetVersion($DBversion);
10975
    SetVersion($DBversion);
10932
}
10976
}
10933
10977
10934
$DBversion = "3.21.00.029";
10978
$DBversion = "3.21.00.029";
10935
if ( CheckVersion($DBversion) ) {
10979
if ( CheckVersion($DBversion) ) {
10936
    $dbh->do(q{
10980
    unless ( column_exists('discharges', 'discharge_id') ) {
10937
        ALTER TABLE discharges
10981
        $dbh->do(q{
10938
            ADD COLUMN discharge_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
10982
            ALTER TABLE discharges
10939
    });
10983
                ADD COLUMN discharge_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
10984
        });
10985
    }
10940
    print "Upgrade to $DBversion done (Bug 14368: Add discharges history)\n";
10986
    print "Upgrade to $DBversion done (Bug 14368: Add discharges history)\n";
10941
    SetVersion($DBversion);
10987
    SetVersion($DBversion);
10942
}
10988
}
Lines 12983-12994 if ( CheckVersion($DBversion) ) { Link Here
12983
13029
12984
$DBversion = "16.06.00.027";
13030
$DBversion = "16.06.00.027";
12985
if ( CheckVersion($DBversion) ) {
13031
if ( CheckVersion($DBversion) ) {
12986
    $dbh->do(q{
13032
    unless ( column_exists('borrowers', 'lastseen') ) {
12987
        ALTER TABLE borrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
13033
        $dbh->do(q{
12988
    });
13034
            ALTER TABLE borrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
12989
    $dbh->do(q{
13035
        });
12990
        ALTER TABLE deletedborrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
13036
        $dbh->do(q{
12991
    });
13037
            ALTER TABLE deletedborrowers ADD COLUMN lastseen datetime default NULL AFTER updated_on;
13038
        });
13039
    }
12992
    $dbh->do(q{
13040
    $dbh->do(q{
12993
        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('TrackLastPatronActivity', '0', 'If set, the field borrowers.lastseen will be updated everytime a patron is seen', NULL, 'YesNo');
13041
        INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES ('TrackLastPatronActivity', '0', 'If set, the field borrowers.lastseen will be updated everytime a patron is seen', NULL, 'YesNo');
12994
    });
13042
    });
Lines 13144-13147 sub CheckVersion { Link Here
13144
    }
13192
    }
13145
}
13193
}
13146
13194
13195
sub constraint_exists {
13196
    my ( $table_name, $key_name ) = @_;
13197
    my $dbh = C4::Context->dbh;
13198
    my ($exists) = $dbh->selectrow_array(
13199
        qq|
13200
        SHOW INDEX FROM $table_name
13201
        WHERE key_name = ?
13202
        |, undef, $key_name
13203
    );
13204
    return $exists;
13205
}
13206
13207
sub column_exists {
13208
    my ( $table_name, $column_name ) = @_;
13209
    my $dbh = C4::Context->dbh;
13210
    my ($exists) = $dbh->selectrow_array(
13211
        qq|
13212
        SHOW COLUMNS FROM $table_name
13213
        WHERE Field = ?
13214
        |, undef, $column_name
13215
    );
13216
    return $exists;
13217
13218
}
13219
13147
exit;
13220
exit;
13148
- 

Return to bug 17234