X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=9a94e0ffeac424dc0a23b5c03977be4dbd1bb217;hb=24213703d5906d4bfff1a8e1c9e079bd11523ba7;hp=4fd4106c20f2d0c59aefc769d4b0d832eefd8eb8;hpb=9c06057f21f1cdbc0a87ecbbdf84306e51e3d6a0;p=koha.git diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 4fd4106c20..9a94e0ffea 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -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; @@ -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; @@ -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", @@ -1045,6 +1053,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 +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`), @@ -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`), @@ -1399,6 +1462,8 @@ 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, 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` -- @@ -1574,6 +1658,8 @@ 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, 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; @@ -1979,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; -- @@ -1996,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; -- @@ -2018,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', @@ -2252,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; -- @@ -2495,6 +2608,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`), @@ -2632,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`), @@ -2668,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 */;