From e7822a029b27b30826cd351b0053feecbcbcccbf Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Thu, 8 Dec 2011 16:10:57 +0100 Subject: [PATCH] 7310a: DB revision for Improving list permissions Content-Type: text/plain; charset="utf-8" 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. --- installer/data/mysql/updatedatabase.pl | 25 +++++++++++++++++++++++++ 1 files changed, 25 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index d2d9d97..bf9b426 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4712,6 +4712,31 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.07.99.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("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.6.0.6