X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=53c1c71b78f9ab2e393c1964ad9299cc27b8d50a;hb=73def1a24a7b35f25580cc698b513384263ef07a;hp=37113c2f099bef4ac1836f216bb06ecec5f7e275;hpb=e93126834cddfb5e0f33f0f8df77e67ee2a68b48;p=koha_fer

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 37113c2f09..53c1c71b78 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -154,6 +154,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in
   `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
   `isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a)
   `issn` varchar(9) default NULL, -- ISSN (MARC21 022$a)
+  `ean` varchar(13) default NULL,
   `publicationyear` text,
   `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
   `volumedate` date default NULL,
@@ -286,6 +287,8 @@ CREATE TABLE `borrower_attribute_types` ( -- definitions for custom patron field
   `staff_searchable` tinyint(1) NOT NULL default 0, -- defines if this field is searchable via the patron search in the staff client (1 for yes, 0 for no)
   `authorised_value_category` varchar(10) default NULL, -- foreign key from authorised_values that links this custom field to an authorized value category
   `display_checkout` tinyint(1) NOT NULL default 0,-- defines if this field displays in checkout screens
+  `category_code` VARCHAR(1) NULL DEFAULT NULL,-- defines a category for an attribute_type
+  `class` VARCHAR(255) NOT NULL DEFAULT '',-- defines a class for an attribute_type
   PRIMARY KEY  (`code`),
   KEY `auth_val_cat_idx` (`authorised_value_category`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -317,6 +320,7 @@ CREATE TABLE `branch_item_rules` (
   `branchcode` varchar(10) NOT NULL,
   `itemtype` varchar(10) NOT NULL,
   `holdallowed` tinyint(1) default NULL,
+  `returnbranch` varchar(15) default NULL,
   PRIMARY KEY  (`itemtype`,`branchcode`),
   KEY `branch_item_rules_ibfk_2` (`branchcode`),
   CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
@@ -361,7 +365,8 @@ CREATE TABLE `branches` ( -- information about your libraries or branches are st
   `branchip` varchar(15) default NULL, -- the IP address for your library or branch
   `branchprinter` varchar(100) default NULL, -- unused in Koha
   `branchnotes` mediumtext, -- notes related to your library or branch
-  UNIQUE KEY `branchcode` (`branchcode`)
+  opac_info text, -- HTML that displays in OPAC
+  PRIMARY KEY (`branchcode`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -384,13 +389,13 @@ CREATE TABLE `branchrelations` ( -- this table links libraries/branches to group
 --
 
 DROP TABLE IF EXISTS `branchtransfers`;
-CREATE TABLE `branchtransfers` (
-  `itemnumber` int(11) NOT NULL default 0,
-  `datesent` datetime default NULL,
-  `frombranch` varchar(10) NOT NULL default '',
-  `datearrived` datetime default NULL,
-  `tobranch` varchar(10) NOT NULL default '',
-  `comments` mediumtext,
+CREATE TABLE `branchtransfers` ( -- information for items that are in transit between branches
+  `itemnumber` int(11) NOT NULL default 0, -- the itemnumber that it is in transit (items.itemnumber)
+  `datesent` datetime default NULL, -- the date the transfer was initialized
+  `frombranch` varchar(10) NOT NULL default '', -- the branch the transfer is coming from
+  `datearrived` datetime default NULL, -- the date the transfer arrived at its destination
+  `tobranch` varchar(10) NOT NULL default '', -- the branch the transfer was going to
+  `comments` mediumtext, -- any comments related to the transfer
   KEY `frombranch` (`frombranch`),
   KEY `tobranch` (`tobranch`),
   KEY `itemnumber` (`itemnumber`),
@@ -497,6 +502,7 @@ CREATE TABLE `default_branch_circ_rules` (
   `branchcode` VARCHAR(10) NOT NULL,
   `maxissueqty` int(4) default NULL,
   `holdallowed` tinyint(1) default NULL,
+  `returnbranch` varchar(15) default NULL,
   PRIMARY KEY (`branchcode`),
   CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
     ON DELETE CASCADE ON UPDATE CASCADE
@@ -509,6 +515,7 @@ DROP TABLE IF EXISTS `default_branch_item_rules`;
 CREATE TABLE `default_branch_item_rules` (
   `itemtype` varchar(10) NOT NULL,
   `holdallowed` tinyint(1) default NULL,
+  `returnbranch` varchar(15) default NULL,
   PRIMARY KEY  (`itemtype`),
   CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
     ON DELETE CASCADE ON UPDATE CASCADE
@@ -523,6 +530,7 @@ CREATE TABLE `default_circ_rules` (
     `singleton` enum('singleton') NOT NULL default 'singleton',
     `maxissueqty` int(4) default NULL,
     `holdallowed` int(1) default NULL,
+    `returnbranch` varchar(15) default NULL,
     PRIMARY KEY (`singleton`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
@@ -618,6 +626,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t
   `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
   `isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a)
   `issn` varchar(9) default NULL, -- ISSN (MARC21 022$a)
+  `ean` varchar(13) default NULL,
   `publicationyear` text,
   `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
   `volumedate` date default NULL,
@@ -851,7 +860,7 @@ CREATE TABLE `import_batches` (
   `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
   `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
   `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
-  `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
+  `batch_type` enum('batch', 'z3950', 'webservice') NOT NULL default 'batch',
   `file_name` varchar(100),
   `comments` mediumtext,
   PRIMARY KEY (`import_batch_id`),
@@ -949,15 +958,15 @@ DROP TABLE IF EXISTS `issues`;
 CREATE TABLE `issues` ( -- information related to check outs or issues
   `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
   `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
-  `date_due` date default NULL, -- date the item is due (yyyy-mm-dd)
+  `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)
   `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
   `issuingbranch` varchar(18) default NULL,
-  `returndate` date default NULL, -- date the item was returned, will be NULL until moved to old_issues
-  `lastreneweddate` date default NULL, -- date the item was last renewed
+  `returndate` datetime default NULL, -- date the item was returned, will be NULL until moved to old_issues
+  `lastreneweddate` datetime default NULL, -- date the item was last renewed
   `return` varchar(4) default NULL,
   `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
-  `issuedate` date default NULL, -- date the item was checked out or issued
+  `issuedate` datetime default NULL, -- date the item was checked out or issued
   KEY `issuesborridx` (`borrowernumber`),
   KEY `bordate` (`borrowernumber`,`timestamp`),
   CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
@@ -983,6 +992,7 @@ CREATE TABLE `issuingrules` (
   `chargename` varchar(100) default NULL,
   `maxissueqty` int(4) default NULL,
   `issuelength` int(4) default NULL,
+  `lengthunit` varchar(10) default 'days',
   `hardduedate` date default NULL,
   `hardduedatecompare` tinyint NOT NULL default "0",
   `renewalsallowed` smallint(6) NOT NULL default "0",
@@ -1045,6 +1055,9 @@ CREATE TABLE `items` ( -- holdings/item information
   KEY `itembibnoidx` (`biblionumber`),
   KEY `homebranch` (`homebranch`),
   KEY `holdingbranch` (`holdingbranch`),
+  KEY `itemcallnumber` (`itemcallnumber`),
+  KEY `items_location` (`location`),
+  KEY `items_ccode` (`ccode`),
   CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
   CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
@@ -1168,10 +1181,12 @@ DROP TABLE IF EXISTS `letter`;
 CREATE TABLE `letter` ( -- table for all notice templates in Koha
   `module` varchar(20) NOT NULL default '', -- Koha module that triggers this notice
   `code` varchar(20) NOT NULL default '', -- unique identifier for this notice
+  `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
   `name` varchar(100) NOT NULL default '', -- plain text name for this notice
+  `is_html` tinyint(1) default 0,
   `title` varchar(200) NOT NULL default '', -- subject line of the notice
   `content` text, -- body text for the notice
-  PRIMARY KEY  (`module`,`code`)
+  PRIMARY KEY  (`module`,`code`, `branchcode`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -1197,6 +1212,7 @@ CREATE TABLE `marc_subfield_structure` (
   `seealso` varchar(1100) default NULL,
   `link` varchar(80) default NULL,
   `defaultvalue` text default NULL,
+  `maxlength` int(4) NOT NULL DEFAULT '9999',
   PRIMARY KEY  (`frameworkcode`,`tagfield`,`tagsubfield`),
   KEY `kohafield_2` (`kohafield`),
   KEY `tab` (`frameworkcode`,`tab`),
@@ -1353,6 +1369,55 @@ CREATE TABLE `nozebra` (
   ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
+-- Table structure for table `oai_sets`
+--
+
+DROP TABLE IF EXISTS `oai_sets`;
+CREATE TABLE `oai_sets` (
+  `id` int(11) NOT NULL auto_increment,
+  `spec` varchar(80) NOT NULL UNIQUE,
+  `name` varchar(80) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `oai_sets_descriptions`
+--
+
+DROP TABLE IF EXISTS `oai_sets_descriptions`;
+CREATE TABLE `oai_sets_descriptions` (
+  `set_id` int(11) NOT NULL,
+  `description` varchar(255) NOT NULL,
+  CONSTRAINT `oai_sets_descriptions_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `oai_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `oai_sets_mappings`
+--
+
+DROP TABLE IF EXISTS `oai_sets_mappings`;
+CREATE TABLE `oai_sets_mappings` (
+  `set_id` int(11) NOT NULL,
+  `marcfield` char(3) NOT NULL,
+  `marcsubfield` char(1) NOT NULL,
+  `marcvalue` varchar(80) NOT NULL,
+  CONSTRAINT `oai_sets_mappings_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `oai_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `oai_sets_biblios`
+--
+
+DROP TABLE IF EXISTS `oai_sets_biblios`;
+CREATE TABLE `oai_sets_biblios` (
+  `biblionumber` int(11) NOT NULL,
+  `set_id` int(11) NOT NULL,
+  PRIMARY KEY (`biblionumber`, `set_id`),
+  CONSTRAINT `oai_sets_biblios_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `oai_sets_biblios_ibfk_2` FOREIGN KEY (`set_id`) REFERENCES `oai_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
 -- Table structure for table `old_issues`
 --
 
@@ -1360,15 +1425,15 @@ DROP TABLE IF EXISTS `old_issues`;
 CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
   `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to
   `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out
-  `date_due` date default NULL, -- date the item is due (yyyy-mm-dd)
+  `date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)
   `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
   `issuingbranch` varchar(18) default NULL,
-  `returndate` date default NULL, -- date the item was returned
-  `lastreneweddate` date default NULL, -- date the item was last renewed
+  `returndate` datetime default NULL, -- date the item was returned
+  `lastreneweddate` datetime default NULL, -- date the item was last renewed
   `return` varchar(4) default NULL,
   `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
-  `issuedate` date default NULL, -- date the item was checked out or issued
+  `issuedate` datetime default NULL, -- date the item was checked out or issued
   KEY `old_issuesborridx` (`borrowernumber`),
   KEY `old_issuesitemidx` (`itemnumber`),
   KEY `old_bordate` (`borrowernumber`,`timestamp`),
@@ -1383,6 +1448,7 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r
 --
 DROP TABLE IF EXISTS `old_reserves`;
 CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
+  `reserve_id` int(11) NOT NULL, -- primary key
   `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for
   `reservedate` date default NULL, -- the date the hold was places
   `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on
@@ -1399,6 +1465,9 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b
   `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
   `expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)
   `lowestPriority` tinyint(1) NOT NULL,
+  `suspend` BOOLEAN NOT NULL DEFAULT 0,
+  `suspend_until` DATETIME NULL DEFAULT NULL,
+  PRIMARY KEY (`reserve_id`),
   KEY `old_reserves_borrowernumber` (`borrowernumber`),
   KEY `old_reserves_biblionumber` (`biblionumber`),
   KEY `old_reserves_itemnumber` (`itemnumber`),
@@ -1475,6 +1544,25 @@ CREATE TABLE `patronimage` (
   CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
+-- Table structure for table `pending_offline_operations`
+--
+-- this table is MyISAM, InnoDB tables are growing only and this table is filled/emptied/filled/emptied...
+-- so MyISAM is better in this case
+
+DROP TABLE IF EXISTS `pending_offline_operations`;
+CREATE TABLE `pending_offline_operations` (
+  `operationid` int(11) NOT NULL AUTO_INCREMENT,
+  `userid` varchar(30) NOT NULL,
+  `branchcode` varchar(10) NOT NULL,
+  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `action` varchar(10) NOT NULL,
+  `barcode` varchar(20) NOT NULL,
+  `cardnumber` varchar(16) DEFAULT NULL,
+  PRIMARY KEY (`operationid`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+
+
 --
 -- Table structure for table `printers`
 --
@@ -1558,6 +1646,7 @@ CREATE TABLE `reserveconstraints` (
 
 DROP TABLE IF EXISTS `reserves`;
 CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
+  `reserve_id` int(11) NOT NULL auto_increment, -- primary key
   `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for
   `reservedate` date default NULL, -- the date the hold was places
   `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on
@@ -1574,6 +1663,9 @@ CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
   `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
   `expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)
   `lowestPriority` tinyint(1) NOT NULL,
+  `suspend` BOOLEAN NOT NULL DEFAULT 0,
+  `suspend_until` DATETIME NULL DEFAULT NULL,
+  PRIMARY KEY (`reserve_id`),
   KEY priorityfoundidx (priority,found),
   KEY `borrowernumber` (`borrowernumber`),
   KEY `biblionumber` (`biblionumber`),
@@ -1597,7 +1689,9 @@ CREATE TABLE `reviews` ( -- patron opac comments
   `review` text, -- the body of the comment
   `approved` tinyint(4) default NULL, -- whether this comment has been approved by a librarian (1 for yes, 0 for no)
   `datereviewed` datetime default NULL, -- the date the comment was left
-  PRIMARY KEY  (`reviewid`)
+  PRIMARY KEY  (`reviewid`),
+  CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+  CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -1626,6 +1720,8 @@ CREATE TABLE saved_sql (
    `report_name` varchar(255) default NULL,
    `type` varchar(255) default NULL,
    `notes` text,
+   `cache_expiry` int NOT NULL default 300,
+   `public` boolean NOT NULL default FALSE,
    PRIMARY KEY  (`id`),
    KEY boridx (`borrowernumber`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -1654,7 +1750,7 @@ CREATE TABLE IF NOT EXISTS `search_history` (
   `userid` int(11) NOT NULL,
   `sessionid` varchar(32) NOT NULL,
   `query_desc` varchar(255) NOT NULL,
-  `query_cgi` varchar(255) NOT NULL,
+  `query_cgi` text NOT NULL,
   `total` int(11) NOT NULL,
   `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
   KEY `userid` (`userid`),
@@ -1690,7 +1786,7 @@ DROP TABLE IF EXISTS sessions;
 CREATE TABLE sessions (
   `id` varchar(32) NOT NULL,
   `a_session` text NOT NULL,
-  UNIQUE KEY id (id)
+  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -1979,11 +2075,15 @@ 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
-  PRIMARY KEY  (`shelfnumber`)
+  `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`),
+  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 VirtualShelves.pm
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -1996,10 +2096,27 @@ 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,
-  CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `shelfcontents_ibfk_3` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in VirtualShelves.pm
+) 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,
+  CONSTRAINT `virtualshelfshares_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in VirtualShelves.pm
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -2018,6 +2135,7 @@ CREATE TABLE `z3950servers` ( -- connection information for the Z39.50 targets u
   `checked` smallint(6) default NULL, -- whether this target is checked by default  (1 for yes, 0 for no)
   `rank` int(11) default NULL, -- where this target appears in the list of targets
   `syntax` varchar(80) default NULL, -- marc format provided by this target
+  `timeout` int(11) NOT NULL DEFAULT '0',
   `icon` text, -- unused in Koha
   `position` enum('primary','secondary','') NOT NULL default 'primary',
   `type` enum('zed','opensearch') NOT NULL default 'zed',
@@ -2252,12 +2370,13 @@ CREATE TABLE `message_transports` (
   `is_digest` tinyint(1) NOT NULL default '0',
   `letter_module` varchar(20) NOT NULL default '',
   `letter_code` varchar(20) NOT NULL default '',
+  `branchcode` varchar(10) NOT NULL default '',
   PRIMARY KEY  (`message_attribute_id`,`message_transport_type`,`is_digest`),
   KEY `message_transport_type` (`message_transport_type`),
   KEY `letter_module` (`letter_module`,`letter_code`),
   CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
-  CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`, `branchcode`) REFERENCES `letter` (`module`, `code`, `branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -2385,14 +2504,14 @@ CREATE TABLE `accountoffsets` (
 --
 
 DROP TABLE IF EXISTS `action_logs`;
-CREATE TABLE `action_logs` (
-  `action_id` int(11) NOT NULL auto_increment,
-  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  `user` int(11) NOT NULL default 0,
-  `module` text,
-  `action` text,
-  `object` int(11) default NULL,
-  `info` text,
+CREATE TABLE `action_logs` ( -- logs of actions taken in Koha (requires that the logs be turned on)
+  `action_id` int(11) NOT NULL auto_increment, -- unique identifier for each action
+  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time the action took place
+  `user` int(11) NOT NULL default 0, -- the staff member who performed the action (borrowers.borrowernumber)
+  `module` text, -- the module this action was taken against
+  `action` text, -- the action (includes things like DELETED, ADDED, MODIFY, etc)
+  `object` int(11) default NULL, -- the object that the action was taken against (could be a borrowernumber, itemnumber, etc)
+  `info` text, -- information about the action (usually includes SQL statement)
   PRIMARY KEY (`action_id`),
   KEY  (`timestamp`,`user`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -2495,6 +2614,7 @@ CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis
   `gstrate` decimal(6,4) default NULL, -- the tax rate the library is charged
   `discount` float(6,4) default NULL, -- discount offered on all items ordered from this vendor
   `fax` varchar(50) default NULL, -- vendor fax number
+  deliverytime int(11) default NULL, -- vendor delivery time
   PRIMARY KEY  (`id`),
   KEY `listprice` (`listprice`),
   KEY `invoiceprice` (`invoiceprice`),
@@ -2507,25 +2627,41 @@ CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis
 --
 
 DROP TABLE IF EXISTS `aqbudgets`;
-CREATE TABLE `aqbudgets` (
-  `budget_id` int(11) NOT NULL auto_increment,
-  `budget_parent_id` int(11) default NULL,
-  `budget_code` varchar(30) default NULL,
-  `budget_name` varchar(80) default NULL,
-  `budget_branchcode` varchar(10) default NULL,
-  `budget_amount` decimal(28,6) NULL default '0.00',
-  `budget_encumb` decimal(28,6) NULL default '0.00',
-  `budget_expend` decimal(28,6) NULL default '0.00',
-  `budget_notes` mediumtext,
-  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  `budget_period_id` int(11) default NULL,
-  `sort1_authcat` varchar(80) default NULL,
-  `sort2_authcat` varchar(80) default NULL,
-  `budget_owner_id` int(11) default NULL,
-  `budget_permission` int(1) default '0',
+CREATE TABLE `aqbudgets` ( -- information related to Funds
+  `budget_id` int(11) NOT NULL auto_increment, -- primary key and unique number assigned to each fund by Koha
+  `budget_parent_id` int(11) default NULL, -- if this fund is a child of another this will include the parent id (aqbudgets.budget_id)
+  `budget_code` varchar(30) default NULL, -- code assigned to the fund by the user
+  `budget_name` varchar(80) default NULL, -- name assigned to the fund by the user
+  `budget_branchcode` varchar(10) default NULL, -- branch that this fund belongs to (branches.branchcode)
+  `budget_amount` decimal(28,6) NULL default '0.00', -- total amount for this fund
+  `budget_encumb` decimal(28,6) NULL default '0.00', -- not used in the code
+  `budget_expend` decimal(28,6) NULL default '0.00', -- not used in the code
+  `budget_notes` mediumtext, -- notes related to this fund
+  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this fund was last touched (created or modified)
+  `budget_period_id` int(11) default NULL, -- id of the budget that this fund belongs to (aqbudgetperiods.budget_period_id)
+  `sort1_authcat` varchar(80) default NULL, -- statistical category for this fund
+  `sort2_authcat` varchar(80) default NULL, -- second statistical category for this fund
+  `budget_owner_id` int(11) default NULL, -- borrowernumber of the person who owns this fund (borrowers.borrowernumber)
+  `budget_permission` int(1) default '0', -- level of permission for this fund (used only by the owner, only by the library, or anyone)
   PRIMARY KEY  (`budget_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
+--
+-- Table structure for table aqbudgetborrowers
+--
+
+DROP TABLE IF EXISTS aqbudgetborrowers;
+CREATE TABLE aqbudgetborrowers (
+  budget_id int(11) NOT NULL,
+  borrowernumber int(11) NOT NULL,
+  PRIMARY KEY (budget_id, borrowernumber),
+  CONSTRAINT aqbudgetborrowers_ibfk_1 FOREIGN KEY (budget_id)
+    REFERENCES aqbudgets (budget_id)
+    ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT aqbudgetborrowers_ibfk_2 FOREIGN KEY (borrowernumber)
+    REFERENCES borrowers (borrowernumber)
+    ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
 -- Table structure for table `aqbudgetperiods`
@@ -2533,16 +2669,16 @@ CREATE TABLE `aqbudgets` (
 
 
 DROP TABLE IF EXISTS `aqbudgetperiods`;
-CREATE TABLE `aqbudgetperiods` (
-  `budget_period_id` int(11) NOT NULL auto_increment,
-  `budget_period_startdate` date NOT NULL,
-  `budget_period_enddate` date NOT NULL,
-  `budget_period_active` tinyint(1) default '0',
-  `budget_period_description` mediumtext,
-  `budget_period_total` decimal(28,6),
-  `budget_period_locked` tinyint(1) default NULL,
-  `sort1_authcat` varchar(10) default NULL,
-  `sort2_authcat` varchar(10) default NULL,
+CREATE TABLE `aqbudgetperiods` ( -- information related to Budgets
+  `budget_period_id` int(11) NOT NULL auto_increment, -- primary key and unique number assigned by Koha
+  `budget_period_startdate` date NOT NULL, -- date when the budget starts
+  `budget_period_enddate` date NOT NULL, -- date when the budget ends
+  `budget_period_active` tinyint(1) default '0', -- whether this budget is active or not (1 for yes, 0 for no)
+  `budget_period_description` mediumtext, -- description assigned to this budget
+  `budget_period_total` decimal(28,6), -- total amount available in this budget
+  `budget_period_locked` tinyint(1) default NULL, -- whether this budget is locked or not (1 for yes, 0 for no)
+  `sort1_authcat` varchar(10) default NULL, -- statistical category for this budget
+  `sort2_authcat` varchar(10) default NULL, -- second statistical category for this budget
   PRIMARY KEY  (`budget_period_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
@@ -2598,42 +2734,42 @@ CREATE TABLE `aqorderdelivery` (
 --
 
 DROP TABLE IF EXISTS `aqorders`;
-CREATE TABLE `aqorders` (
-  `ordernumber` int(11) NOT NULL auto_increment,
-  `biblionumber` int(11) default NULL,
-  `entrydate` date default NULL,
-  `quantity` smallint(6) default NULL,
-  `currency` varchar(3) default NULL,
-  `listprice` decimal(28,6) default NULL,
-  `totalamount` decimal(28,6) default NULL,
-  `datereceived` date default NULL,
-  `booksellerinvoicenumber` mediumtext,
-  `freight` decimal(28,6) default NULL,
-  `unitprice` decimal(28,6) default NULL,
-  `quantityreceived` smallint(6) NOT NULL default 0,
-  `cancelledby` varchar(10) default NULL,
-  `datecancellationprinted` date default NULL,
-  `notes` mediumtext,
-  `supplierreference` mediumtext,
-  `purchaseordernumber` mediumtext,
-  `subscription` tinyint(1) default NULL,
-  `serialid` varchar(30) default NULL,
-  `basketno` int(11) default NULL,
-  `biblioitemnumber` int(11) default NULL,
-  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  `rrp` decimal(13,2) default NULL,
-  `ecost` decimal(13,2) default NULL,
-  `gst` decimal(13,2) default NULL,
-  `budget_id` int(11) NOT NULL,
-  `budgetgroup_id` int(11) NOT NULL,
-  `budgetdate` date default NULL,
-  `sort1` varchar(80) default NULL,
-  `sort2` varchar(80) default NULL,
+CREATE TABLE `aqorders` ( --information related to the basket line items
+  `ordernumber` int(11) NOT NULL auto_increment, -- primary key and unique identifier assigned by Koha to each line
+  `biblionumber` int(11) default NULL, -- links the order to the biblio being ordered (biblio.biblionumber)
+  `entrydate` date default NULL, -- the date the bib was added to the basket
+  `quantity` smallint(6) default NULL, -- the quantity ordered
+  `currency` varchar(3) default NULL, -- the currency used for the purchase
+  `listprice` decimal(28,6) default NULL, -- the vendor price for this line item
+  `totalamount` decimal(28,6) default NULL, -- not used? always NULL
+  `datereceived` date default NULL, -- the date this order was received
+  `booksellerinvoicenumber` mediumtext, -- the invoice number this line item was received on
+  `freight` decimal(28,6) default NULL, -- shipping costs (not used)
+  `unitprice` decimal(28,6) default NULL, -- the actual cost entered when receiving this line item
+  `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
+  `cancelledby` varchar(10) default NULL, -- not used? always NULL
+  `datecancellationprinted` date default NULL, -- the date the line item was deleted
+  `notes` mediumtext, -- notes related to this order line
+  `supplierreference` mediumtext, -- not used? always NULL
+  `purchaseordernumber` mediumtext, -- not used? always NULL
+  `subscription` tinyint(1) default NULL, -- not used? always NULL
+  `serialid` varchar(30) default NULL, -- not used? always NULL
+  `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
+  `biblioitemnumber` int(11) default NULL, -- links this order line the biblioitems table (biblioitems.biblioitemnumber)
+  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
+  `rrp` decimal(13,2) default NULL, -- the replacement cost for this line item
+  `ecost` decimal(13,2) default NULL, -- the estimated cost for this line item
+  `gst` decimal(13,2) default NULL, -- the tax rate for this line item
+  `budget_id` int(11) NOT NULL, -- the fund this order goes against (aqbudgets.budget_id)
+  `budgetgroup_id` int(11) NOT NULL, -- not used? always zero
+  `budgetdate` date default NULL, -- not used? always NULL
+  `sort1` varchar(80) default NULL, -- statistical field
+  `sort2` varchar(80) default NULL, -- second statistical field
   `sort1_authcat` varchar(10) default NULL,
   `sort2_authcat` varchar(10) default NULL,
-  `uncertainprice` tinyint(1),
-  `claims_count` int(11) default 0,
-  `claimed_date` date default NULL,
+  `uncertainprice` tinyint(1), -- was this price uncertain (1 for yes, 0 for no)
+  `claims_count` int(11) default 0, -- count of claim letters generated
+  `claimed_date` date default NULL, -- last date a claim was generated
   PRIMARY KEY  (`ordernumber`),
   KEY `basketno` (`basketno`),
   KEY `biblionumber` (`biblionumber`),
@@ -2648,10 +2784,10 @@ CREATE TABLE `aqorders` (
 --
 
 DROP TABLE IF EXISTS `aqorders_items`;
-CREATE TABLE `aqorders_items` (
-  `ordernumber` int(11) NOT NULL,
-  `itemnumber` int(11) NOT NULL,
-  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisitions process
+  `ordernumber` int(11) NOT NULL, -- the order this item is attached to (aqorders.ordernumber)
+  `itemnumber` int(11) NOT NULL, -- the item number for this item (items.itemnumber)
+  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order item was last touched
   PRIMARY KEY  (`itemnumber`),
   KEY `ordernumber` (`ordernumber`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -2671,12 +2807,12 @@ CREATE TABLE `fieldmapping` ( -- koha to keyword mapping
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
--- Table structure for table `bibliocoverimage`
+-- Table structure for table `biblioimages`
 --
 
-DROP TABLE IF EXISTS `bibliocoverimage`;
+DROP TABLE IF EXISTS `biblioimages`;
 
-CREATE TABLE `bibliocoverimage` (
+CREATE TABLE `biblioimages` (
  `imagenumber` int(11) NOT NULL AUTO_INCREMENT,
  `biblionumber` int(11) NOT NULL,
  `mimetype` varchar(15) NOT NULL,
@@ -2686,6 +2822,50 @@ CREATE TABLE `bibliocoverimage` (
  CONSTRAINT `bibliocoverimage_fk1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
+--
+-- Table structure for table `social_data`
+--
+
+DROP TABLE IF EXISTS `social_data`;
+CREATE TABLE IF NOT EXISTS `social_data` (
+  `isbn` VARCHAR(30),
+  `num_critics` INT,
+  `num_critics_pro` INT,
+  `num_quotations` INT,
+  `num_videos` INT,
+  `score_avg` DECIMAL(5,2),
+  `num_scores` INT,
+  PRIMARY KEY  (`isbn`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- 'Ratings' table. This tracks the star ratings set by borrowers.
+--
+
+DROP TABLE IF EXISTS ratings;
+CREATE TABLE ratings ( -- information related to the star ratings in the OPAC
+    borrowernumber int(11) NOT NULL, -- the borrowernumber of the patron who left this rating (borrowers.borrowernumber)
+    biblionumber int(11) NOT NULL, -- the biblio this rating is for (biblio.biblionumber)
+    rating_value tinyint(1) NOT NULL, -- the rating, from 1 to 5
+    timestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
+    PRIMARY KEY  (borrowernumber,biblionumber),
+    CONSTRAINT ratings_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
+    CONSTRAINT ratings_ibfk_2 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `quotes`
+--
+
+DROP TABLE IF EXISTS quotes;
+CREATE TABLE `quotes` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `source` text DEFAULT NULL,
+  `text` mediumtext NOT NULL,
+  `timestamp` datetime NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;