[Koha-patches] [PATCH] 7310a: DB revision for Improving list permissions

[Marcel de Rooy]

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.
---
 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 0ef0b1f..9a21bef 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -4684,6 +4684,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 1, ADD COLUMN allow_delete_own tinyint(1) DEFAULT 1, ADD COLUMN allow_delete_other tinyint(1) DEFAULT 0");
+    $dbh->do("UPDATE virtualshelves SET allow_add=1, allow_delete_own=1, allow_delete_other=0 WHERE category=1");
+    $dbh->do("UPDATE virtualshelves SET allow_add=1, allow_delete_own=1, allow_delete_other=1 WHERE category=2"); #former behavior
+    $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");
+    $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) 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";
+    print "NOTE: If you want to make your public lists read-only, please check the permissions on list level or run a sql update statement on the virtualshelves table just like the ones in the updatedatabase script.\n";
+    SetVersion($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 DropAllForeignKeys($table)
-- 
1.6.0.6