From 843ae6e8484028e931e69034733bd7ce47bd51b0 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Thu, 8 Dec 2011 16:10:57 +0100 Subject: [PATCH] Bug 7310: Improving list permissions: Changing Koha SQL structure Content-Type: text/plain; charset="UTF-8" Modifying Koha structure 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. Signed-off-by: Jared Camins-Esakov --- installer/data/mysql/kohastructure.sql | 22 ++++++++++++++++++++-- 1 files changed, 20 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 67149c9d..0917449 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1980,10 +1980,13 @@ DROP TABLE IF EXISTS `virtualshelves`; CREATE TABLE `virtualshelves` ( -- information about lists (or virtual shelves) `shelfnumber` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha `shelfname` varchar(255) default NULL, -- name of the list - `owner` varchar(80) default NULL, -- foriegn key linking to the borrowers table (using borrowernumber) for the creator of this list - `category` varchar(1) default NULL, -- type of list (public [2], private [1] or open [3]) + `owner` int default NULL, -- foreign key linking to the borrowers table (using borrowernumber) for the creator of this list (changed from varchar(80) to int) + `category` varchar(1) default NULL, -- type of list (private [1], public [2]) `sortfield` varchar(16) default NULL, -- 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 + `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 PRIMARY KEY (`shelfnumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1997,6 +2000,7 @@ CREATE TABLE `virtualshelfcontents` ( -- information about the titles in a list `biblionumber` int(11) NOT NULL default 0, -- foreign key linking to the biblio table, defines the bib record that has been added to the list `flags` int(11) default NULL, `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date and time this bib record was added to the list + `borrowernumber` int, -- borrower number that created this list entry (only the first one is saved: no need for use in/as key) KEY `shelfnumber` (`shelfnumber`), KEY `biblionumber` (`biblionumber`), CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE, @@ -2004,6 +2008,20 @@ CREATE TABLE `virtualshelfcontents` ( -- information about the titles in a list ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table structure for table `virtualshelfshares` +-- + +DROP TABLE IF EXISTS `virtualshelfshares`; +CREATE TABLE `virtualshelfshares` ( --shared private lists + `id` int AUTO_INCREMENT PRIMARY KEY, --unique key + `shelfnumber` int NOT NULL, -- foreign key for virtualshelves + `borrowernumber` int, -- borrower that accepted access to this list + `invitekey` varchar(10), -- temporary string used in accepting the invitation to access thist list; not-empty means that the invitation has not been accepted yet + `sharedate` datetime, -- date of invitation or acceptance of invitation + CONSTRAINT `virtualshelfshares_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE, +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table structure for table `z3950servers` -- -- 1.7.2.5