X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=e0ce0343c75740e1efa0b5df5af5209678890f39;hb=00bd409a9a4e7576a2d5f613e7e86e26758ee287;hp=a03bd23f91b6ef4b21f7a6a19f6ead651f4d908c;hpb=e12fca0eb8330adbe230c435a55e88696409bfbd;p=koha.git diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index a03bd23f91..e0ce0343c7 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -317,6 +317,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`) @@ -497,6 +498,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 +511,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 +526,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; @@ -1031,7 +1035,7 @@ CREATE TABLE `items` ( -- holdings/item information `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) + `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 @@ -1045,6 +1049,7 @@ CREATE TABLE `items` ( -- holdings/item information KEY `itembibnoidx` (`biblionumber`), KEY `homebranch` (`homebranch`), KEY `holdingbranch` (`holdingbranch`), + KEY `itemcallnumber` (`itemcallnumber`), 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 +1173,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; -- @@ -1317,10 +1324,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 +1359,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` -- @@ -1393,7 +1449,7 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b `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 the status is of the hold is after it has been confirmed + `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 @@ -1475,6 +1531,24 @@ 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 + +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` -- @@ -1568,7 +1642,7 @@ CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha `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 the status is of the hold is after it has been confirmed + `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 @@ -1597,7 +1671,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; -- @@ -1863,7 +1939,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 @@ -2251,12 +2328,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 +2539,39 @@ 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 PRIMARY KEY (`id`), KEY `listprice` (`listprice`), KEY `invoiceprice` (`invoiceprice`), @@ -2631,6 +2709,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 +2747,21 @@ 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; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;