From 8f990b47361781b937ea36e14c4317b470ec0468 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Thu, 30 Mar 2023 09:58:30 +0200
Subject: [PATCH] Bug 33368: Extends borrowers.flags to bigint

it's a int(11) which means its limit is 2^31

We have currently 29 permissions, there is only one remaining, at 31 permissions
the permission system won't support a case where a patron has all permissions.

Test plan:
Don't apply this patch
Add another permission
INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (30, 'new permission 1', '', 0) ;
Go to the interface, tick all the boxes
select flags from borrowers where borrowernumber=5;
=> 2113928830
Remember:
2^31  2147483648
2^32  4294967296

Add another one
INSERT INTO userflags (bit, flag, flagdesc, defaulton) VALUES (31, 'new permission 2', '', 0) ;
Repeat the step before
=> boom
CGI::Compile::ROOT::kohadevbox_koha_members_member_2dflags_2epl::member_2dflags_2epl(): DBI Exception: DBD::mysql::st execute failed: Out of range value for column 'flags' at row 1 at /usr/share/perl5/CGI/Emulate/PSGI.pm line 30

Apply the patch, try again

QA note: That's ofc not the best solution, but the easiest for now

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net>
---
 .../data/mysql/atomicupdate/bug_33368.pl      | 22 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  6 ++---
 2 files changed, 25 insertions(+), 3 deletions(-)
 create mode 100755 installer/data/mysql/atomicupdate/bug_33368.pl

diff --git a/installer/data/mysql/atomicupdate/bug_33368.pl b/installer/data/mysql/atomicupdate/bug_33368.pl
new file mode 100755
index 0000000000..2fbdd8e209
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_33368.pl
@@ -0,0 +1,22 @@
+use Modern::Perl;
+
+return {
+    bug_number => "33368",
+    description => "Extend borrowers.flags to bigint",
+    up => sub {
+        my ($args) = @_;
+        my ($dbh, $out) = @$args{qw(dbh out)};
+        $dbh->do(q{
+            ALTER TABLE borrower_modifications
+            MODIFY COLUMN `flags` bigint(11)
+        });
+        $dbh->do(q{
+            ALTER TABLE borrowers
+            MODIFY COLUMN `flags` bigint(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions'
+        });
+        $dbh->do(q{
+            ALTER TABLE deletedborrowers
+            MODIFY COLUMN `flags` bigint(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions'
+        });
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 52e8ad2c5d..cba889d5e0 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1327,7 +1327,7 @@ CREATE TABLE `borrower_modifications` (
   `relationship` varchar(100) DEFAULT NULL,
   `sex` varchar(1) DEFAULT NULL,
   `password` varchar(30) DEFAULT NULL,
-  `flags` int(11) DEFAULT NULL,
+  `flags` bigint(11) DEFAULT NULL,
   `userid` varchar(75) DEFAULT NULL,
   `opacnote` longtext DEFAULT NULL,
   `contactnote` varchar(255) DEFAULT NULL,
@@ -1450,7 +1450,7 @@ CREATE TABLE `borrowers` (
   `password` varchar(60) DEFAULT NULL COMMENT 'patron/borrower''s Bcrypt encrypted password',
   `secret` mediumtext DEFAULT NULL COMMENT 'Secret for 2FA',
   `auth_method` enum('password','two-factor') NOT NULL DEFAULT 'password' COMMENT 'Authentication method',
-  `flags` int(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions',
+  `flags` bigint(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions',
   `userid` varchar(75) DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
   `opacnote` longtext DEFAULT NULL COMMENT 'a note on the patron/borrower''s account that is visible in the OPAC and staff interface',
   `contactnote` varchar(255) DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address',
@@ -2573,7 +2573,7 @@ CREATE TABLE `deletedborrowers` (
   `password` varchar(60) DEFAULT NULL COMMENT 'patron/borrower''s encrypted password',
   `secret` mediumtext DEFAULT NULL COMMENT 'Secret for 2FA',
   `auth_method` enum('password','two-factor') NOT NULL DEFAULT 'password' COMMENT 'Authentication method',
-  `flags` int(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions',
+  `flags` bigint(11) DEFAULT NULL COMMENT 'will include a number associated with the staff member''s permissions',
   `userid` varchar(75) DEFAULT NULL COMMENT 'patron/borrower''s opac and/or staff interface log in',
   `opacnote` longtext DEFAULT NULL COMMENT 'a note on the patron/borrower''s account that is visible in the OPAC and staff interface',
   `contactnote` varchar(255) DEFAULT NULL COMMENT 'a note related to the patron/borrower''s alternate address',
-- 
2.40.0