X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=40f5969467d4583d1dc6b04e4c2dc9f4c59e2a55;hb=12ff7355bb21a30d472e987f05f1da35f00a53a5;hp=153e17b8e48bdee73409221f484f54cee77a7920;hpb=235a9dfb7d259b67f0c4167b56c7a0b04275046d;p=koha_gimpoz diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 153e17b8e4..40f5969467 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) NOT NULL DEFAULT '',-- 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, @@ -1200,6 +1207,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`), @@ -1356,6 +1364,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` -- @@ -1363,15 +1420,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`), @@ -1402,6 +1459,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`), @@ -1595,6 +1654,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`), @@ -2002,11 +2063,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; -- @@ -2019,10 +2084,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; -- @@ -2041,6 +2123,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', @@ -2519,6 +2602,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`), @@ -2710,6 +2794,22 @@ CREATE TABLE `biblioimages` ( 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; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;