From 76e85fb5ebb72b09caacd02b6a8b33c4beea1a0e Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Thu, 8 Dec 2011 16:10:57 +0100 Subject: [PATCH 1/9] 7310a: DB revision for Improving list permissions DB revision for improving list permissions. Adds new table virtualshelfshares for maintaining shared private lists. Adds three columns to virtualshelves for permissions per list. Adds column borrowernumber to virtualshelfcontents. Feb 16, 2012: Rebased. Signed-off-by: Jared Camins-Esakov Signed-off-by: Marcel de Rooy Feb 28, 2012: Added three FK constraints for QA (for owner/borrowernumber in virtualshelves, shelfcontents and shelfshares). Tested update again. Also removed the note about public lists, made obsolete by earlier adjustments for comments from Owen. Feb 29, 2012: Rebased. Added an update statement to replace invalid borrowernumbers with null in virtualshelves before adding constraint and added a delete statement for private lists without owner now (as a result of the first step). Tested it again. March 5, 2012: Rebased. Signed-off-by: Ian Walls --- installer/data/mysql/updatedatabase.pl | 27 +++++++++++++++++++++++++++ 1 files changed, 27 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 082d5ca..14b9bc9 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4924,6 +4924,33 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { } +$DBversion = "3.07.00.XXX"; #FIXME +if ( C4::Context->preference("Version") lt TransformToNum($DBversion) ) { + $dbh->do("ALTER TABLE virtualshelves MODIFY COLUMN owner int"); #should have been int already (fk to borrowers) + $dbh->do("UPDATE virtualshelves vi LEFT JOIN borrowers bo ON bo.borrowernumber=vi.owner SET vi.owner=NULL where bo.borrowernumber IS NULL"); #before adding the constraint on borrowernumber, we need to get rid of deleted owners + $dbh->do("DELETE FROM virtualshelves WHERE owner IS NULL and category=1"); #delete private lists without owner (cascades to shelfcontents) + $dbh->do("ALTER TABLE virtualshelves ADD COLUMN allow_add tinyint(1) DEFAULT 0, ADD COLUMN allow_delete_own tinyint(1) DEFAULT 1, ADD COLUMN allow_delete_other tinyint(1) DEFAULT 0, ADD CONSTRAINT `virtualshelves_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL"); + $dbh->do("UPDATE virtualshelves SET allow_add=0, allow_delete_own=1, allow_delete_other=0 WHERE category=1"); + $dbh->do("UPDATE virtualshelves SET allow_add=0, allow_delete_own=1, allow_delete_other=0 WHERE category=2"); + $dbh->do("UPDATE virtualshelves SET allow_add=1, allow_delete_own=1, allow_delete_other=1 WHERE category=3"); + $dbh->do("UPDATE virtualshelves SET category=2 WHERE category=3"); + + $dbh->do("ALTER TABLE virtualshelfcontents ADD COLUMN borrowernumber int, ADD CONSTRAINT `shelfcontents_ibfk_3` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL"); + $dbh->do("UPDATE virtualshelfcontents co LEFT JOIN virtualshelves sh USING (shelfnumber) SET co.borrowernumber=sh.owner"); + + $dbh->do("CREATE TABLE virtualshelfshares + (id int AUTO_INCREMENT PRIMARY KEY, shelfnumber int NOT NULL, + borrowernumber int, invitekey varchar(10), sharedate datetime, + CONSTRAINT `virtualshelfshares_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `virtualshelfshares_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8"); + + $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacAllowPublicListCreation',1,'If set, allows opac users to create public lists',NULL,'YesNo');"); + $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacAllowSharingPrivateLists',0,'If set, allows opac users to share private lists with other patrons',NULL,'YesNo');"); + + print "Upgrade to $DBversion done (BZ7310: Improving list permissions)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) -- 1.7.0.4