X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=9a94e0ffeac424dc0a23b5c03977be4dbd1bb217;hb=24213703d5906d4bfff1a8e1c9e079bd11523ba7;hp=452173de92db1fc4d5a1eb547e0871b46deccb73;hpb=aef1dd15fbe37a8a9c30ba4b38f7ecd6c1fea54d;p=koha.git

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 452173de92..9a94e0ffea 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -116,16 +116,16 @@ DROP TABLE IF EXISTS `biblio`;
 CREATE TABLE `biblio` ( -- table that stores bibliographic information
   `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
   `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record
-  `author` mediumtext, -- statement of responsibility from MARC record (100 in MARC21)
-  `title` mediumtext, -- title (without the subtitle) from the MARC record (245 in MARC21)
-  `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240 in MARC21)
-  `notes` mediumtext, -- values from the general notes field in the MARC record (500 in MARC21) split by bar (|)
+  `author` mediumtext, -- statement of responsibility from MARC record (100$a in MARC21)
+  `title` mediumtext, -- title (without the subtitle) from the MARC record (245$a in MARC21)
+  `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
+  `notes` mediumtext, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
   `serial` tinyint(1) default NULL, -- foreign key, linking to the subscriptionid in the serial table
   `seriestitle` mediumtext,
   `copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
   `datecreated` DATE NOT NULL, -- the date this record was added to Koha
-  `abstract` mediumtext, -- summary from the MARC record (520 in MARC21)
+  `abstract` mediumtext, -- summary from the MARC record (520$a in MARC21)
   PRIMARY KEY  (`biblionumber`),
   KEY `blbnoidx` (`biblionumber`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -146,39 +146,39 @@ CREATE TABLE `biblio_framework` (
 --
 
 DROP TABLE IF EXISTS `biblioitems`;
-CREATE TABLE `biblioitems` (
-  `biblioitemnumber` int(11) NOT NULL auto_increment,
-  `biblionumber` int(11) NOT NULL default 0,
+CREATE TABLE `biblioitems` ( -- information related to bibliographic records in Koha
+  `biblioitemnumber` int(11) NOT NULL auto_increment, -- primary key, unique identifier assigned by Koha
+  `biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table
   `volume` mediumtext,
   `number` mediumtext,
-  `itemtype` varchar(10) default NULL,
-  `isbn` varchar(30) default NULL,
-  `issn` varchar(9) default NULL,
+  `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)
   `publicationyear` text,
-  `publishercode` varchar(255) default NULL,
+  `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
   `volumedate` date default NULL,
-  `volumedesc` text,
+  `volumedesc` text, -- volume information (MARC21 362$a)
   `collectiontitle` mediumtext default NULL,
   `collectionissn` text default NULL,
   `collectionvolume` mediumtext default NULL,
   `editionstatement` text default NULL,
   `editionresponsibility` text default NULL,
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  `illus` varchar(255) default NULL,
-  `pages` varchar(255) default NULL,
+  `illus` varchar(255) default NULL, -- illustrations (MARC21 300$b)
+  `pages` varchar(255) default NULL, -- number of pages (MARC21 300$c)
   `notes` mediumtext,
-  `size` varchar(255) default NULL,
-  `place` varchar(255) default NULL,
-  `lccn` varchar(25) default NULL,
-  `marc` longblob,
-  `url` varchar(255) default NULL,
-  `cn_source` varchar(10) default NULL,
+  `size` varchar(255) default NULL, -- material size (MARC21 300$c)
+  `place` varchar(255) default NULL, -- publication place (MARC21 260$a)
+  `lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
+  `marc` longblob, -- full bibliographic MARC record
+  `url` varchar(255) default NULL, -- url (MARC21 856$u)
+  `cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
   `cn_class` varchar(30) default NULL,
   `cn_item` varchar(10) default NULL,
   `cn_suffix` varchar(10) default NULL,
   `cn_sort` varchar(30) default NULL,
   `totalissues` int(10),
-  `marcxml` longtext NOT NULL,
+  `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
   PRIMARY KEY  (`biblioitemnumber`),
   KEY `bibinoidx` (`biblioitemnumber`),
   KEY `bibnoidx` (`biblionumber`),
@@ -286,6 +286,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 +319,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,6 +364,7 @@ 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
+  opac_info text, -- HTML that displays in OPAC
   UNIQUE KEY `branchcode` (`branchcode`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
@@ -417,21 +421,21 @@ CREATE TABLE `browser` (
 --
 
 DROP TABLE IF EXISTS `categories`;
-CREATE TABLE `categories` (
-  `categorycode` varchar(10) NOT NULL default '',
-  `description` mediumtext,
-  `enrolmentperiod` smallint(6) default NULL,
-  `enrolmentperioddate` DATE NULL DEFAULT NULL,
-  `upperagelimit` smallint(6) default NULL,
+CREATE TABLE `categories` ( -- this table shows information related to Koha patron categories
+  `categorycode` varchar(10) NOT NULL default '', -- unique primary key used to idenfity the patron category
+  `description` mediumtext, -- description of the patron category
+  `enrolmentperiod` smallint(6) default NULL, -- number of months the patron is enrolled for (will be NULL if enrolmentperioddate is set)
+  `enrolmentperioddate` DATE NULL DEFAULT NULL, -- date the patron is enrolled until (will be NULL if enrolmentperiod is set)
+  `upperagelimit` smallint(6) default NULL, -- age limit for the patron
   `dateofbirthrequired` tinyint(1) default NULL,
-  `finetype` varchar(30) default NULL,
+  `finetype` varchar(30) default NULL, -- unused in Koha
   `bulk` tinyint(1) default NULL,
-  `enrolmentfee` decimal(28,6) default NULL,
-  `overduenoticerequired` tinyint(1) default NULL,
-  `issuelimit` smallint(6) default NULL,
-  `reservefee` decimal(28,6) default NULL,
-  `hidelostitems` tinyint(1) NOT NULL default '0',
-  `category_type` varchar(1) NOT NULL default 'A',
+  `enrolmentfee` decimal(28,6) default NULL, -- enrollment fee for the patron
+  `overduenoticerequired` tinyint(1) default NULL, -- are overdue notices sent to this patron category (1 for yes, 0 for no)
+  `issuelimit` smallint(6) default NULL, -- unused in Koha
+  `reservefee` decimal(28,6) default NULL, -- cost to place holds
+  `hidelostitems` tinyint(1) NOT NULL default '0', -- are lost items shown to this category (1 for yes, 0 for no)
+  `category_type` varchar(1) NOT NULL default 'A', -- type of Koha patron (Adult, Child, Professional, Organizational, Statistical, Staff)
   PRIMARY KEY  (`categorycode`),
   UNIQUE KEY `categorycode` (`categorycode`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -497,6 +501,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 +514,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 +529,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;
 
@@ -591,16 +598,16 @@ DROP TABLE IF EXISTS `deletedbiblio`;
 CREATE TABLE `deletedbiblio` ( -- stores information about bibliographic records that have been deleted
   `biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
   `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record
-  `author` mediumtext, -- statement of responsibility from MARC record (100 in MARC21)
-  `title` mediumtext, -- title (without the subtitle) from the MARC record (245 in MARC21)
-  `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240 in MARC21)
-  `notes` mediumtext, -- values from the general notes field in the MARC record (500 in MARC21) split by bar (|)
+  `author` mediumtext, -- statement of responsibility from MARC record (100$a in MARC21)
+  `title` mediumtext, -- title (without the subtitle) from the MARC record (245$a in MARC21)
+  `unititle` mediumtext, -- uniform title (without the subtitle) from the MARC record (240$a in MARC21)
+  `notes` mediumtext, -- values from the general notes field in the MARC record (500$a in MARC21) split by bar (|)
   `serial` tinyint(1) default NULL, -- foreign key, linking to the subscriptionid in the serial table
   `seriestitle` mediumtext,
   `copyrightdate` smallint(6) default NULL, -- publication or copyright date from the MARC record
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this record was last touched
   `datecreated` DATE NOT NULL, -- the date this record was added to Koha
-  `abstract` mediumtext, -- summary from the MARC record (520 in MARC21)
+  `abstract` mediumtext, -- summary from the MARC record (520$a in MARC21)
   PRIMARY KEY  (`biblionumber`),
   KEY `blbnoidx` (`biblionumber`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -610,39 +617,39 @@ CREATE TABLE `deletedbiblio` ( -- stores information about bibliographic records
 --
 
 DROP TABLE IF EXISTS `deletedbiblioitems`;
-CREATE TABLE `deletedbiblioitems` (
-  `biblioitemnumber` int(11) NOT NULL default 0,
-  `biblionumber` int(11) NOT NULL default 0,
+CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records that have been deleted
+  `biblioitemnumber` int(11) NOT NULL default 0, -- primary key, unique identifier assigned by Koha
+  `biblionumber` int(11) NOT NULL default 0, -- foreign key linking this table to the biblio table
   `volume` mediumtext,
   `number` mediumtext,
-  `itemtype` varchar(10) default NULL,
-  `isbn` varchar(30) default NULL,
-  `issn` varchar(9) default NULL,
+  `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)
   `publicationyear` text,
-  `publishercode` varchar(255) default NULL,
+  `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
   `volumedate` date default NULL,
-  `volumedesc` text,
+  `volumedesc` text, -- volume information (MARC21 362$a)
   `collectiontitle` mediumtext default NULL,
   `collectionissn` text default NULL,
   `collectionvolume` mediumtext default NULL,
   `editionstatement` text default NULL,
   `editionresponsibility` text default NULL,
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  `illus` varchar(255) default NULL,
-  `pages` varchar(255) default NULL,
+  `illus` varchar(255) default NULL, -- illustrations (MARC21 300$b)
+  `pages` varchar(255) default NULL, -- number of pages (MARC21 300$c)
   `notes` mediumtext,
-  `size` varchar(255) default NULL,
-  `place` varchar(255) default NULL,
-  `lccn` varchar(25) default NULL,
-  `marc` longblob,
-  `url` varchar(255) default NULL,
-  `cn_source` varchar(10) default NULL,
+  `size` varchar(255) default NULL, -- material size (MARC21 300$c)
+  `place` varchar(255) default NULL, -- publication place (MARC21 260$a)
+  `lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
+  `marc` longblob, -- full bibliographic MARC record
+  `url` varchar(255) default NULL, -- url (MARC21 856$u)
+  `cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
   `cn_class` varchar(30) default NULL,
   `cn_item` varchar(10) default NULL,
   `cn_suffix` varchar(10) default NULL,
   `cn_sort` varchar(30) default NULL,
   `totalissues` int(10),
-  `marcxml` longtext NOT NULL,
+  `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
   PRIMARY KEY  (`biblioitemnumber`),
   KEY `bibinoidx` (`biblioitemnumber`),
   KEY `bibnoidx` (`biblionumber`),
@@ -733,46 +740,46 @@ CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower
 
 DROP TABLE IF EXISTS `deleteditems`;
 CREATE TABLE `deleteditems` (
-  `itemnumber` int(11) NOT NULL default 0,
-  `biblionumber` int(11) NOT NULL default 0,
-  `biblioitemnumber` int(11) NOT NULL default 0,
-  `barcode` varchar(20) default NULL,
-  `dateaccessioned` date default NULL,
-  `booksellerid` mediumtext default NULL,
-  `homebranch` varchar(10) default NULL,
-  `price` decimal(8,2) default NULL,
-  `replacementprice` decimal(8,2) default NULL,
-  `replacementpricedate` date default NULL,
-  `datelastborrowed` date default NULL,
-  `datelastseen` date default NULL,
+  `itemnumber` int(11) NOT NULL default 0, -- primary key and unique identifier added by Koha
+  `biblionumber` int(11) NOT NULL default 0, -- foreign key from biblio table used to link this item to the right bib record
+  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
+  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
+  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
+  `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e)
+  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
+  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
+  `replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v)
+  `replacementpricedate` date default NULL, -- the date the price is effective from (MARC21 952$w)
+  `datelastborrowed` date default NULL, -- the date the item was last checked out
+  `datelastseen` date default NULL, -- the date the item was last see (usually the last time the barcode was scanned or inventory was done)
   `stack` tinyint(1) default NULL,
-  `notforloan` tinyint(1) NOT NULL default 0,
-  `damaged` tinyint(1) NOT NULL default 0,
-  `itemlost` tinyint(1) NOT NULL default 0,
-  `wthdrawn` tinyint(1) NOT NULL default 0,
-  `itemcallnumber` varchar(255) default NULL,
-  `issues` smallint(6) default NULL,
-  `renewals` smallint(6) default NULL,
-  `reserves` smallint(6) default NULL,
-  `restricted` tinyint(1) default NULL,
-  `itemnotes` mediumtext,
-  `holdingbranch` varchar(10) default NULL,
+  `notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7)
+  `damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4)
+  `itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
+  `wthdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
+  `itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o)
+  `issues` smallint(6) default NULL, -- number of times this item has been checked out
+  `renewals` smallint(6) default NULL, -- number of times this item has been renewed
+  `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
+  `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
+  `itemnotes` mediumtext, -- public notes on this item (MARC21 952$x)
+  `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
   `paidfor` mediumtext,
-  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  `location` varchar(80) default NULL,
-  `permanent_location` varchar(80) default NULL,
-  `onloan` date default NULL,
-  `cn_source` varchar(10) default NULL,
-  `cn_sort` varchar(30) default NULL,
-  `ccode` varchar(10) default NULL,
-  `materials` varchar(10) default NULL,
-  `uri` varchar(255) default NULL,
-  `itype` varchar(10) default NULL,
-  `more_subfields_xml` longtext default NULL,
-  `enumchron` text default NULL,
-  `copynumber` varchar(32) default NULL,
-  `stocknumber` varchar(32) default NULL,
-  `marc` longblob,
+  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
+  `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
+  `permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
+  `onloan` date default NULL, -- defines if this item is currently checked out (1 for yes, 0 for no)
+  `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
+  `cn_sort` varchar(30) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting
+  `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
+  `materials` varchar(10) default NULL, -- materials specified (MARC21 952$3)
+  `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
+  `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y)
+  `more_subfields_xml` longtext default NULL, -- additional 952 subfields in XML format
+  `enumchron` text default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
+  `copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
+  `stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
+  `marc` longblob, -- unused in Koha
   PRIMARY KEY  (`itemnumber`),
   KEY `delitembarcodeidx` (`barcode`),
   KEY `delitemstocknumberidx` (`stocknumber`),
@@ -851,7 +858,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 +956,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 +990,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",
@@ -998,46 +1006,46 @@ CREATE TABLE `issuingrules` (
 --
 
 DROP TABLE IF EXISTS `items`;
-CREATE TABLE `items` (
-  `itemnumber` int(11) NOT NULL auto_increment,
-  `biblionumber` int(11) NOT NULL default 0,
-  `biblioitemnumber` int(11) NOT NULL default 0,
-  `barcode` varchar(20) default NULL,
-  `dateaccessioned` date default NULL,
-  `booksellerid` mediumtext default NULL,
-  `homebranch` varchar(10) default NULL,
-  `price` decimal(8,2) default NULL,
-  `replacementprice` decimal(8,2) default NULL,
-  `replacementpricedate` date default NULL,
-  `datelastborrowed` date default NULL,
-  `datelastseen` date default NULL,
+CREATE TABLE `items` ( -- holdings/item information 
+  `itemnumber` int(11) NOT NULL auto_increment, -- primary key and unique identifier added by Koha
+  `biblionumber` int(11) NOT NULL default 0, -- foreign key from biblio table used to link this item to the right bib record
+  `biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
+  `barcode` varchar(20) default NULL, -- item barcode (MARC21 952$p)
+  `dateaccessioned` date default NULL, -- date the item was acquired or added to Koha (MARC21 952$d)
+  `booksellerid` mediumtext default NULL, -- where the item was purchased (MARC21 952$e)
+  `homebranch` varchar(10) default NULL, -- foreign key from the branches table for the library that owns this item (MARC21 952$a)
+  `price` decimal(8,2) default NULL, -- purchase price (MARC21 952$g)
+  `replacementprice` decimal(8,2) default NULL, -- cost the library charges to replace the item if it has been marked lost (MARC21 952$v)
+  `replacementpricedate` date default NULL, -- the date the price is effective from (MARC21 952$w)
+  `datelastborrowed` date default NULL, -- the date the item was last checked out/issued
+  `datelastseen` date default NULL, -- the date the item was last see (usually the last time the barcode was scanned or inventory was done)
   `stack` tinyint(1) default NULL,
-  `notforloan` tinyint(1) NOT NULL default 0,
-  `damaged` tinyint(1) NOT NULL default 0,
-  `itemlost` tinyint(1) NOT NULL default 0,
-  `wthdrawn` tinyint(1) NOT NULL default 0,
-  `itemcallnumber` varchar(255) default NULL,
-  `issues` smallint(6) default NULL,
-  `renewals` smallint(6) default NULL,
-  `reserves` smallint(6) default NULL,
-  `restricted` tinyint(1) default NULL,
-  `itemnotes` mediumtext,
-  `holdingbranch` varchar(10) default NULL,
+  `notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7)
+  `damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4)
+  `itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
+  `wthdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
+  `itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o)
+  `issues` smallint(6) default NULL, -- number of times this item has been checked out/issued
+  `renewals` smallint(6) default NULL, -- number of times this item has been renewed
+  `reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
+  `restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
+  `itemnotes` mediumtext, -- public notes on this item (MARC21 952$x)
+  `holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
   `paidfor` mediumtext,
-  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  `location` varchar(80) default NULL,
-  `permanent_location` varchar(80) default NULL,
-  `onloan` date default NULL,
-  `cn_source` varchar(10) default NULL,
-  `cn_sort` varchar(30) default NULL,
-  `ccode` varchar(10) default NULL,
-  `materials` varchar(10) default NULL,
-  `uri` varchar(255) default NULL,
-  `itype` varchar(10) default NULL,
-  `more_subfields_xml` longtext default NULL,
-  `enumchron` text default NULL,
-  `copynumber` varchar(32) default NULL,
-  `stocknumber` varchar(32) default NULL,
+  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
+  `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
+  `permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
+  `onloan` date default NULL, -- defines if this item is currently checked out (1 for yes, 0 for no)
+  `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
+  `cn_sort` varchar(30) default NULL,  -- normalized form of the call number (MARC21 952$o) used for sorting
+  `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
+  `materials` text default NULL, -- materials specified (MARC21 952$3)
+  `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
+  `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y)
+  `more_subfields_xml` longtext default NULL, -- additional 952 subfields in XML format
+  `enumchron` text default NULL, -- serial enumeration/chronology for the item (MARC21 952$h)
+  `copynumber` varchar(32) default NULL, -- copy number (MARC21 952$t)
+  `stocknumber` varchar(32) default NULL, -- inventory number (MARC21 952$i)
   PRIMARY KEY  (`itemnumber`),
   UNIQUE KEY `itembarcodeidx` (`barcode`),
   KEY `itemstocknumberidx` (`stocknumber`),
@@ -1045,6 +1053,9 @@ CREATE TABLE `items` (
   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 +1179,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 +1210,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`),
@@ -1317,10 +1331,10 @@ CREATE TABLE `matchchecks` (
 --
 
 DROP TABLE IF EXISTS `need_merge_authorities`;
-CREATE TABLE `need_merge_authorities` (
-  `id` int NOT NULL auto_increment PRIMARY KEY,
-  `authid` bigint NOT NULL,
-  `done` tinyint DEFAULT 0
+CREATE TABLE `need_merge_authorities` ( -- keeping track of authority records still to be merged by merge_authority cron job (used only if pref dontmerge is ON)
+  `id` int NOT NULL auto_increment PRIMARY KEY, -- unique id
+  `authid` bigint NOT NULL, -- reference to authority record
+  `done` tinyint DEFAULT 0  -- indication whether merge has been executed (0=not done, 1= done, 2= in progress)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -1352,6 +1366,55 @@ CREATE TABLE `nozebra` (
   KEY `value` (`server`,`value`))
   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 +1423,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`),
@@ -1382,23 +1445,25 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r
 -- Table structure for table `old_reserves`
 --
 DROP TABLE IF EXISTS `old_reserves`;
-CREATE TABLE `old_reserves` (
-  `borrowernumber` int(11) default NULL,
-  `reservedate` date default NULL,
-  `biblionumber` int(11) default NULL,
+CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
+  `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
   `constrainttype` varchar(1) default NULL,
-  `branchcode` varchar(10) default NULL,
-  `notificationdate` date default NULL,
-  `reminderdate` date default NULL,
-  `cancellationdate` date default NULL,
-  `reservenotes` mediumtext,
-  `priority` smallint(6) default NULL,
-  `found` varchar(1) default NULL,
-  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  `itemnumber` int(11) default NULL,
-  `waitingdate` date default NULL,
-  `expirationdate` DATE DEFAULT NULL,
+  `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at
+  `notificationdate` date default NULL, -- currently unused
+  `reminderdate` date default NULL, -- currently unused
+  `cancellationdate` date default NULL, -- the date this hold was cancelled
+  `reservenotes` mediumtext, -- notes related to this hold
+  `priority` smallint(6) default NULL, -- where in the queue the patron sits
+  `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed
+  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated
+  `itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with
+  `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,
   KEY `old_reserves_borrowernumber` (`borrowernumber`),
   KEY `old_reserves_biblionumber` (`biblionumber`),
   KEY `old_reserves_itemnumber` (`itemnumber`),
@@ -1475,6 +1540,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`
 --
@@ -1557,23 +1641,25 @@ CREATE TABLE `reserveconstraints` (
 --
 
 DROP TABLE IF EXISTS `reserves`;
-CREATE TABLE `reserves` (
-  `borrowernumber` int(11) NOT NULL default 0,
-  `reservedate` date default NULL,
-  `biblionumber` int(11) NOT NULL default 0,
+CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
+  `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
   `constrainttype` varchar(1) default NULL,
-  `branchcode` varchar(10) default NULL,
-  `notificationdate` date default NULL,
-  `reminderdate` date default NULL,
-  `cancellationdate` date default NULL,
-  `reservenotes` mediumtext,
-  `priority` smallint(6) default NULL,
-  `found` varchar(1) default NULL,
-  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-  `itemnumber` int(11) default NULL,
-  `waitingdate` date default NULL,
-  `expirationdate` DATE DEFAULT NULL,
+  `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at
+  `notificationdate` date default NULL, -- currently unused
+  `reminderdate` date default NULL, -- currently unused
+  `cancellationdate` date default NULL, -- the date this hold was cancelled
+  `reservenotes` mediumtext, -- notes related to this hold
+  `priority` smallint(6) default NULL, -- where in the queue the patron sits
+  `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed
+  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated
+  `itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with
+  `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,
   KEY priorityfoundidx (priority,found),
   KEY `borrowernumber` (`borrowernumber`),
   KEY `biblionumber` (`biblionumber`),
@@ -1597,7 +1683,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 +1714,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;
@@ -1863,7 +1953,8 @@ CREATE TABLE `suggestions` ( -- purchase suggestions
   `isbn` varchar(30) default NULL, -- isbn of the suggested item
   `mailoverseeing` smallint(1) default 0,
   `biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered
-  `reason` text, -- reason for making the suggestion
+  `reason` text, -- reason for accepting or rejecting the suggestion
+  `patronreason` text, -- reason for making the suggestion
    budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets table
    branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table
    collectiontitle text default NULL, -- collection name for the suggested item
@@ -1978,11 +2069,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;
 
 --
@@ -1995,10 +2090,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;
 
 --
@@ -2017,6 +2129,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',
@@ -2251,12 +2364,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;
 
 --
@@ -2461,39 +2575,40 @@ CREATE TABLE `aqbasket` (
 --
 
 DROP TABLE IF EXISTS `aqbooksellers`;
-CREATE TABLE `aqbooksellers` (
-  `id` int(11) NOT NULL auto_increment,
-  `name` mediumtext NOT NULL,
-  `address1` mediumtext,
-  `address2` mediumtext,
-  `address3` mediumtext,
-  `address4` mediumtext,
-  `phone` varchar(30) default NULL,
-  `accountnumber` mediumtext,
-  `othersupplier` mediumtext,
-  `currency` varchar(3) NOT NULL default '',
-  `booksellerfax` mediumtext,
-  `notes` mediumtext,
-  `bookselleremail` mediumtext,
-  `booksellerurl` mediumtext,
-  `contact` varchar(100) default NULL,
-  `postal` mediumtext,
-  `url` varchar(255) default NULL,
-  `contpos` varchar(100) default NULL,
-  `contphone` varchar(100) default NULL,
-  `contfax` varchar(100) default NULL,
-  `contaltphone` varchar(100) default NULL,
-  `contemail` varchar(100) default NULL,
-  `contnotes` mediumtext,
-  `active` tinyint(4) default NULL,
-  `listprice` varchar(10) default NULL,
-  `invoiceprice` varchar(10) default NULL,
-  `gstreg` tinyint(4) default NULL,
-  `listincgst` tinyint(4) default NULL,
-  `invoiceincgst` tinyint(4) default NULL,
-  `gstrate` decimal(6,4) default NULL,
-  `discount` float(6,4) default NULL,
-  `fax` varchar(50) default NULL,
+CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquisitions
+  `id` int(11) NOT NULL auto_increment, -- primary key and unique identifier assigned by Koha
+  `name` mediumtext NOT NULL, -- vendor name
+  `address1` mediumtext, -- first line of vendor physical address
+  `address2` mediumtext, -- second line of vendor physical address
+  `address3` mediumtext, -- third line of vendor physical address
+  `address4` mediumtext, -- fourth line of vendor physical address
+  `phone` varchar(30) default NULL, -- vendor phone number
+  `accountnumber` mediumtext, -- unused in Koha
+  `othersupplier` mediumtext,  -- unused in Koha
+  `currency` varchar(3) NOT NULL default '', -- unused in Koha
+  `booksellerfax` mediumtext, -- vendor fax number
+  `notes` mediumtext, -- order notes
+  `bookselleremail` mediumtext, -- vendor email
+  `booksellerurl` mediumtext, -- unused in Koha
+  `contact` varchar(100) default NULL, -- name of contact at vendor
+  `postal` mediumtext, -- vendor postal address (all lines)
+  `url` varchar(255) default NULL, -- vendor web address
+  `contpos` varchar(100) default NULL, -- contact person's position
+  `contphone` varchar(100) default NULL, -- contact's phone number
+  `contfax` varchar(100) default NULL,  -- contact's fax number
+  `contaltphone` varchar(100) default NULL, -- contact's alternate phone number
+  `contemail` varchar(100) default NULL, -- contact's email address
+  `contnotes` mediumtext, -- notes related to the contact
+  `active` tinyint(4) default NULL, -- is this vendor active (1 for yes, 0 for no)
+  `listprice` varchar(10) default NULL, -- currency code for list prices
+  `invoiceprice` varchar(10) default NULL, -- currency code for invoice prices
+  `gstreg` tinyint(4) default NULL, -- is your library charged tax (1 for yes, 0 for no)
+  `listincgst` tinyint(4) default NULL, -- is tax included in list prices (1 for yes, 0 for no)
+  `invoiceincgst` tinyint(4) default NULL, -- is tax included in invoice prices (1 for yes, 0 for no)
+  `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`),
@@ -2631,6 +2746,8 @@ CREATE TABLE `aqorders` (
   `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,
   PRIMARY KEY  (`ordernumber`),
   KEY `basketno` (`basketno`),
   KEY `biblionumber` (`biblionumber`),
@@ -2667,6 +2784,52 @@ CREATE TABLE `fieldmapping` ( -- koha to keyword mapping
   PRIMARY KEY  (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
+--
+-- Table structure for table `biblioimages`
+--
+
+DROP TABLE IF EXISTS `biblioimages`;
+
+CREATE TABLE `biblioimages` (
+ `imagenumber` int(11) NOT NULL AUTO_INCREMENT,
+ `biblionumber` int(11) NOT NULL,
+ `mimetype` varchar(15) NOT NULL,
+ `imagefile` mediumblob NOT NULL,
+ `thumbnail` mediumblob NOT NULL,
+ PRIMARY KEY (`imagenumber`),
+ 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 (
+    borrowernumber int(11) NOT NULL, -- the borrower this rating is for
+    biblionumber int(11) NOT NULL, -- the biblio it's for
+    rating_value tinyint(1) NOT NULL, -- the rating, from 1-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;
 
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;