From 4fe1724e306e9063243d8bda0f71d9aad091a154 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Wed, 8 Mar 2017 14:10:32 +0100 Subject: [PATCH] Bug 18228: Table revision of virtualshelves In order to make the permissions easier, we will replace the columns allow_add, allow_delete_own and allow_delete_other by two new columns allow_change_from_owner and allow_change_from_others. The distinction between adding or deleting an entry is no longer made. If you have change permission, you can do both. Also deleting an entry does no longer depend on who added the entry. Formerly, the owner could always add entries. It is now possible to make a list readonly. We will not use the combination of owner=no and other=yes. This will leave us three possibilities: [1] owner=no, other=no: The list is read-only. No one can change contents of the list. Naturally, the owner can edit permissions. [2] owner=yes, other=no: Only the owner can change contents. [3] owner=yes, other=yes: Anyone seeing the list can change contents. This especially applies to shared lists and public lists. The two database columns will be presented in the interface as one permission field offering the three abovementioned options. Test plan: [1] Run the db rev. Signed-off-by: Marcel de Rooy Signed-off-by: Jesse Maseto Signed-off-by: Nick Clemens --- .../data/mysql/atomicupdate/bug18228.perl | 30 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 5 ++-- 2 files changed, 32 insertions(+), 3 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug18228.perl diff --git a/installer/data/mysql/atomicupdate/bug18228.perl b/installer/data/mysql/atomicupdate/bug18228.perl new file mode 100644 index 0000000000..3f94609009 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug18228.perl @@ -0,0 +1,30 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + # Add the new columns + $dbh->do( +q|ALTER TABLE virtualshelves +ADD COLUMN allow_change_from_owner tinyint(1) default 1, +ADD COLUMN allow_change_from_others tinyint(1) default 0| + ); + + # Conversion: + # Since we had no readonly lists, change_from_owner is set to true. + # When adding or delete_other was granted, change_from_others is true. + # Note: In my opinion the best choice; there is no exact match. + $dbh->do( +q|UPDATE virtualshelves +SET allow_change_from_owner = 1, +allow_change_from_others = CASE WHEN allow_add=1 OR allow_delete_other=1 THEN 1 ELSE 0 END| + ); + + # Remove the old columns + $dbh->do( +q|ALTER TABLE virtualshelves +DROP COLUMN allow_add, +DROP COLUMN allow_delete_own, +DROP COLUMN allow_delete_other| + ); + + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 18228 - Alter table virtualshelves)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e9c60aa850..dfd5934857 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2268,9 +2268,8 @@ CREATE TABLE `virtualshelves` ( -- information about lists (or virtual shelves) `sortfield` varchar(16) default 'title', -- the field this list is sorted on `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the list was last modified `created_on` datetime NOT NULL, -- creation time - `allow_add` tinyint(1) default 0, -- permission for adding entries to list - `allow_delete_own` tinyint(1) default 1, -- permission for deleting entries frm list that you added yourself - `allow_delete_other` tinyint(1) default 0, -- permission for deleting entries from list that another person added + `allow_change_from_owner` tinyint(1) default 1, -- can owner change contents? + `allow_change_from_others` tinyint(1) default 0 -- can others change contents? PRIMARY KEY (`shelfnumber`), CONSTRAINT `virtualshelves_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in Members.pm ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- 2.39.5