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

(-)a/C4/Installer.pm (-1 / +23 lines)
Lines 38-44 BEGIN { Link Here
38
    require Exporter;
38
    require Exporter;
39
    @ISA = qw( Exporter );
39
    @ISA = qw( Exporter );
40
    push @EXPORT,
40
    push @EXPORT,
41
        qw( primary_key_exists unique_key_exists foreign_key_exists index_exists column_exists TableExists marc_framework_sql_list TransformToNum CheckVersion NewVersion SetVersion sanitize_zero_date update get_db_entries get_atomic_updates run_atomic_updates has_non_dynamic_row_format );
41
        qw( primary_key_exists unique_key_exists foreign_key_exists index_exists trigger_exists column_exists TableExists marc_framework_sql_list TransformToNum CheckVersion NewVersion SetVersion sanitize_zero_date update get_db_entries get_atomic_updates run_atomic_updates has_non_dynamic_row_format );
42
}
42
}
43
43
44
=head1 NAME
44
=head1 NAME
Lines 727-732 sub index_exists { Link Here
727
    return $exists;
727
    return $exists;
728
}
728
}
729
729
730
=head2 trigger_exists
731
732
    my $exists = trigger_exists( $trigger_name );
733
734
Checks if a database trigger named I<$trigger_name> exists in the current database schema.
735
736
=cut
737
738
sub trigger_exists {
739
    my ($trigger_name) = @_;
740
    my $dbh            = C4::Context->dbh;
741
    my ($count)        = $dbh->selectrow_array(
742
        q{
743
        SELECT COUNT(*)
744
        FROM information_schema.triggers
745
        WHERE trigger_schema = DATABASE()
746
        AND trigger_name = ?
747
        }, undef, $trigger_name
748
    );
749
    return $count > 0 ? 1 : 0;
750
}
751
730
=head2 column_exists
752
=head2 column_exists
731
753
732
Missing POD for column_exists.
754
Missing POD for column_exists.
(-)a/installer/data/mysql/atomicupdate/bug_33905_cardnumber_userid_constraint.pl (+78 lines)
Line 0 Link Here
1
use Modern::Perl;
2
use C4::Installer           qw( trigger_exists );
3
use Koha::Installer::Output qw(say_warning say_success say_info);
4
5
return {
6
    bug_number  => "33905",
7
    description => "Add triggers to prevent cardnumber/userid interchange between users",
8
    up          => sub {
9
        my ($args) = @_;
10
        my ( $dbh, $out ) = @$args{qw(dbh out)};
11
12
        # Create INSERT trigger if it doesn't exist
13
        unless ( trigger_exists('trg_borrowers_cardnumber_userid_insert') ) {
14
            $dbh->do(
15
                q{
16
                CREATE TRIGGER trg_borrowers_cardnumber_userid_insert
17
                    BEFORE INSERT ON borrowers
18
                    FOR EACH ROW
19
                BEGIN
20
                    -- Check if new cardnumber exists as userid in other records
21
                    IF NEW.cardnumber IS NOT NULL AND EXISTS (
22
                        SELECT 1 FROM borrowers
23
                        WHERE userid = NEW.cardnumber
24
                        AND borrowernumber != COALESCE(NEW.borrowernumber, 0)
25
                    ) THEN
26
                        SIGNAL SQLSTATE '45000'
27
                        SET MESSAGE_TEXT = 'Koha::Exceptions::Patron::CardnumberMatchesUserID';
28
                    END IF;
29
30
                    -- Check if new userid exists as cardnumber in other records
31
                    IF NEW.userid IS NOT NULL AND EXISTS (
32
                        SELECT 1 FROM borrowers
33
                        WHERE cardnumber = NEW.userid
34
                        AND borrowernumber != COALESCE(NEW.borrowernumber, 0)
35
                    ) THEN
36
                        SIGNAL SQLSTATE '45000'
37
                        SET MESSAGE_TEXT = 'Koha::Exceptions::Patron::UserIDMatchesCardnumber';
38
                    END IF;
39
                END
40
            }
41
            );
42
            say_success( $out, "Created INSERT trigger to prevent cardnumber/userid conflicts" );
43
        }
44
45
        # Create UPDATE trigger if it doesn't exist
46
        unless ( trigger_exists('trg_borrowers_cardnumber_userid_update') ) {
47
            $dbh->do(
48
                q{
49
                CREATE TRIGGER trg_borrowers_cardnumber_userid_update
50
                    BEFORE UPDATE ON borrowers
51
                    FOR EACH ROW
52
                BEGIN
53
                    -- Check if updated cardnumber exists as userid in other records
54
                    IF NEW.cardnumber IS NOT NULL AND EXISTS (
55
                        SELECT 1 FROM borrowers
56
                        WHERE userid = NEW.cardnumber
57
                        AND borrowernumber != NEW.borrowernumber
58
                    ) THEN
59
                        SIGNAL SQLSTATE '45000'
60
                        SET MESSAGE_TEXT = 'Koha::Exceptions::Patron::CardnumberMatchesUserID';
61
                    END IF;
62
63
                    -- Check if updated userid exists as cardnumber in other records
64
                    IF NEW.userid IS NOT NULL AND EXISTS (
65
                        SELECT 1 FROM borrowers
66
                        WHERE cardnumber = NEW.userid
67
                        AND borrowernumber != NEW.borrowernumber
68
                    ) THEN
69
                        SIGNAL SQLSTATE '45000'
70
                        SET MESSAGE_TEXT = 'Koha::Exceptions::Patron::UserIDMatchesCardnumber';
71
                    END IF;
72
                END
73
            }
74
            );
75
            say_success( $out, "Created UPDATE trigger to prevent cardnumber/userid conflicts" );
76
        }
77
    },
78
};
(-)a/installer/data/mysql/kohastructure.sql (+58 lines)
Lines 1627-1632 CREATE TABLE `borrowers` ( Link Here
1627
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1627
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1628
/*!40101 SET character_set_client = @saved_cs_client */;
1628
/*!40101 SET character_set_client = @saved_cs_client */;
1629
1629
1630
--
1631
-- Triggers for table `borrowers`
1632
--
1633
1634
DELIMITER $$
1635
1636
CREATE TRIGGER trg_borrowers_cardnumber_userid_insert
1637
    BEFORE INSERT ON borrowers
1638
    FOR EACH ROW
1639
BEGIN
1640
    -- Check if new cardnumber exists as userid in other records
1641
    IF NEW.cardnumber IS NOT NULL AND EXISTS (
1642
        SELECT 1 FROM borrowers
1643
        WHERE userid = NEW.cardnumber
1644
        AND borrowernumber != COALESCE(NEW.borrowernumber, 0)
1645
    ) THEN
1646
        SIGNAL SQLSTATE '45000'
1647
        SET MESSAGE_TEXT = 'Koha::Exceptions::Patron::CardnumberMatchesUserID';
1648
    END IF;
1649
1650
    -- Check if new userid exists as cardnumber in other records
1651
    IF NEW.userid IS NOT NULL AND EXISTS (
1652
        SELECT 1 FROM borrowers
1653
        WHERE cardnumber = NEW.userid
1654
        AND borrowernumber != COALESCE(NEW.borrowernumber, 0)
1655
    ) THEN
1656
        SIGNAL SQLSTATE '45000'
1657
        SET MESSAGE_TEXT = 'Koha::Exceptions::Patron::UserIDMatchesCardnumber';
1658
    END IF;
1659
END$$
1660
1661
CREATE TRIGGER trg_borrowers_cardnumber_userid_update
1662
    BEFORE UPDATE ON borrowers
1663
    FOR EACH ROW
1664
BEGIN
1665
    -- Check if updated cardnumber exists as userid in other records
1666
    IF NEW.cardnumber IS NOT NULL AND EXISTS (
1667
        SELECT 1 FROM borrowers
1668
        WHERE userid = NEW.cardnumber
1669
        AND borrowernumber != NEW.borrowernumber
1670
    ) THEN
1671
        SIGNAL SQLSTATE '45000'
1672
        SET MESSAGE_TEXT = 'Koha::Exceptions::Patron::CardnumberMatchesUserID';
1673
    END IF;
1674
1675
    -- Check if updated userid exists as cardnumber in other records
1676
    IF NEW.userid IS NOT NULL AND EXISTS (
1677
        SELECT 1 FROM borrowers
1678
        WHERE cardnumber = NEW.userid
1679
        AND borrowernumber != NEW.borrowernumber
1680
    ) THEN
1681
        SIGNAL SQLSTATE '45000'
1682
        SET MESSAGE_TEXT = 'Koha::Exceptions::Patron::UserIDMatchesCardnumber';
1683
    END IF;
1684
END$$
1685
1686
DELIMITER ;
1687
1630
--
1688
--
1631
-- Table structure for table `branch_transfer_limits`
1689
-- Table structure for table `branch_transfer_limits`
1632
--
1690
--
(-)a/t/db_dependent/Installer_trigger_exists.t (-1 / +91 lines)
Line 0 Link Here
0
- 
1
#!/usr/bin/perl
2
3
# This file is part of Koha.
4
#
5
# Koha is free software; you can redistribute it and/or modify it
6
# under the terms of the GNU General Public License as published by
7
# the Free Software Foundation; either version 3 of the License, or
8
# (at your option) any later version.
9
#
10
# Koha is distributed in the hope that it will be useful, but
11
# WITHOUT ANY WARRANTY; without even the implied warranty of
12
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13
# GNU General Public License for more details.
14
#
15
# You should have received a copy of the GNU General Public License
16
# along with Koha; if not, see <http://www.gnu.org/licenses>.
17
18
use Modern::Perl;
19
20
use Test::More tests => 4;
21
22
use C4::Context;
23
use C4::Installer qw( trigger_exists );
24
25
my $dbh = C4::Context->dbh;
26
27
# Start transaction for cleanup
28
$dbh->begin_work;
29
30
subtest 'trigger_exists function availability' => sub {
31
    plan tests => 1;
32
33
    can_ok( 'C4::Installer', 'trigger_exists' );
34
};
35
36
subtest 'trigger_exists with non-existent trigger' => sub {
37
    plan tests => 1;
38
39
    my $exists = trigger_exists('non_existent_trigger_name_12345');
40
    is( $exists, 0, 'Non-existent trigger returns 0' );
41
};
42
43
subtest 'trigger_exists with existing trigger' => sub {
44
    plan tests => 3;
45
46
    # Create a test trigger
47
    my $test_trigger_name = 'test_trigger_' . time();
48
49
    # First verify it doesn't exist
50
    my $exists_before = trigger_exists($test_trigger_name);
51
    is( $exists_before, 0, 'Test trigger does not exist initially' );
52
53
    # Create the trigger (using a simple trigger that does nothing)
54
    $dbh->do(
55
        qq{
56
        CREATE TRIGGER $test_trigger_name
57
            BEFORE INSERT ON borrowers
58
            FOR EACH ROW
59
        BEGIN
60
            -- This is a test trigger that does nothing
61
            SET NEW.borrowernumber = NEW.borrowernumber;
62
        END
63
    }
64
    );
65
66
    # Now verify it exists
67
    my $exists_after = trigger_exists($test_trigger_name);
68
    is( $exists_after, 1, 'Test trigger exists after creation' );
69
70
    # Clean up - drop the trigger
71
    $dbh->do("DROP TRIGGER IF EXISTS $test_trigger_name");
72
73
    # Verify it's gone
74
    my $exists_after_drop = trigger_exists($test_trigger_name);
75
    is( $exists_after_drop, 0, 'Test trigger does not exist after dropping' );
76
};
77
78
subtest 'trigger_exists parameter validation' => sub {
79
    plan tests => 2;
80
81
    # Test with undefined parameter
82
    my $exists_undef = trigger_exists(undef);
83
    is( $exists_undef, 0, 'trigger_exists with undef parameter returns 0' );
84
85
    # Test with empty string
86
    my $exists_empty = trigger_exists('');
87
    is( $exists_empty, 0, 'trigger_exists with empty string returns 0' );
88
};
89
90
# Rollback transaction to clean up any test data
91
$dbh->rollback;

Return to bug 33905