X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=46db97fcbef9426a0dcdda4fa3f98dbf6baa48c9;hb=7ae384a0b1ca177d76e841bf67a5bb27bd4a6488;hp=b22134bbd292ecf003dd25edc6eba5974e2cffac;hpb=905306efb191d684e1470b1279be32230e2258eb;p=koha_fer diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index b22134bbd2..46db97fcbe 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -56,6 +56,7 @@ CREATE TABLE `auth_subfield_structure` ( `linkid` tinyint(1) NOT NULL default 0, `kohafield` varchar(45) NULL default '', `frameworkcode` varchar(10) NOT NULL default '', + `defaultvalue` TEXT DEFAULT '', PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`), KEY `tab` (`authtypecode`,`tab`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -120,7 +121,7 @@ CREATE TABLE `biblio` ( -- table that stores bibliographic information `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 + `serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial `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 @@ -172,7 +173,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in `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) + `url` text 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, @@ -246,7 +247,7 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons `ethnicity` varchar(50) default NULL, -- unused in Koha `ethnotes` varchar(255) default NULL, -- unused in Koha `sex` varchar(1) default NULL, -- patron/borrower's gender - `password` varchar(30) default NULL, -- patron/borrower's encrypted password + `password` varchar(60) default NULL, -- patron/borrower's encrypted password `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions `userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in `opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client @@ -270,6 +271,9 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons KEY `branchcode` (`branchcode`), KEY `userid` (`userid`), KEY `guarantorid` (`guarantorid`), + KEY `surname_idx` (`surname`(255)), + KEY `firstname_idx` (`firstname`(255)), + KEY `othernames_idx` (`othernames`(255)), CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -314,6 +318,27 @@ CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table structure for table `borrower_debarments` +-- + +DROP TABLE IF EXISTS `borrower_debarments`; +CREATE TABLE borrower_debarments ( + borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, + borrowernumber int(11) NOT NULL, + expiration date DEFAULT NULL, + `type` enum('SUSPENSION','OVERDUES','MANUAL') NOT NULL DEFAULT 'MANUAL', + `comment` text, + manager_id int(11) DEFAULT NULL, + created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + updated timestamp NULL DEFAULT NULL, + PRIMARY KEY (borrower_debarment_id), + KEY borrowernumber (borrowernumber), + CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + +-- -- Table structure for table `branch_item_rules` -- @@ -462,10 +487,10 @@ CREATE TABLE collections ( -- DROP TABLE IF EXISTS collections_tracking; CREATE TABLE collections_tracking ( - ctId integer(11) NOT NULL auto_increment, + collections_tracking_id integer(11) NOT NULL auto_increment, colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId', itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber', - PRIMARY KEY (ctId) + PRIMARY KEY (collections_tracking_id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8; -- @@ -709,7 +734,7 @@ CREATE TABLE `deletedbiblio` ( -- stores information about bibliographic records `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 + `serial` tinyint(1) default NULL, -- Boolean indicating whether biblio is for a serial `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 @@ -750,7 +775,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t `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) + `url` text 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, @@ -866,7 +891,9 @@ CREATE TABLE `deleteditems` ( `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) + `itemlost_on` datetime DEFAULT NULL, -- the date and time an item was last marked as lost, NULL if not lost `withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0) + `withdrawn_on` datetime DEFAULT NULL, -- the date and time an item was last marked as withdrawn, NULL if not withdrawn `itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o) `coded_location_qualifier` varchar(10) default NULL, -- coded location qualifier(MARC21 952$f) `issues` smallint(6) default NULL, -- number of times this item has been checked out @@ -883,14 +910,13 @@ CREATE TABLE `deleteditems` ( `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 `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`), @@ -921,11 +947,12 @@ CREATE TABLE `export_format` ( `export_format_id` int(11) NOT NULL auto_increment, `profile` varchar(255) NOT NULL, `description` mediumtext NOT NULL, - `marcfields` mediumtext NOT NULL, + `content` mediumtext NOT NULL, `csv_separator` varchar(2) NOT NULL, `field_separator` varchar(2) NOT NULL, `subfield_separator` varchar(2) NOT NULL, `encoding` varchar(255) NOT NULL, + `type` varchar(255) DEFAULT 'marc', PRIMARY KEY (`export_format_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export'; @@ -968,7 +995,7 @@ CREATE TABLE `import_batches` ( -- information about batches of marc records tha `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- date and time the file was uploaded `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new', -- how to handle duplicate records `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new', -- how to handle records where no match is found - `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add', -- what to do with item records + `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore', 'replace') NOT NULL default 'always_add', -- what to do with item records `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging', -- the status of the imported file `batch_type` enum('batch', 'z3950', 'webservice') NOT NULL default 'batch', -- where this batch has come from `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio', -- type of record in the batch @@ -1128,6 +1155,7 @@ CREATE TABLE `issuingrules` ( -- circulation and fine rules `hardduedatecompare` tinyint NOT NULL default "0", -- type of hard due date (1 = after, 0 = on, -1 = before) `renewalsallowed` smallint(6) NOT NULL default "0", -- how many renewals are allowed `renewalperiod` int(4) default NULL, -- renewal period in the unit set in issuingrules.lengthunit + `norenewalbefore` int(4) default NULL, -- no renewal allowed until X days or hours before due date. In the unit set in issuingrules.lengthunit `reservesallowed` smallint(6) NOT NULL default "0", -- how many holds are allowed `branchcode` varchar(10) NOT NULL default '', -- the branch this rule is for (branches.branchcode) overduefinescap decimal(28,6) default NULL, -- the maximum amount of an overdue fine @@ -1158,7 +1186,9 @@ CREATE TABLE `items` ( -- holdings/item information `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) + `itemlost_on` datetime DEFAULT NULL, -- the date and time an item was last marked as lost, NULL if not lost `withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0) + `withdrawn_on` datetime DEFAULT NULL, -- the date and time an item was last marked as withdrawn, NULL if not withdrawn `itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o) `coded_location_qualifier` varchar(10) default NULL, -- coded location qualifier(MARC21 952$f) `issues` smallint(6) default NULL, -- number of times this item has been checked out/issued @@ -1204,12 +1234,15 @@ CREATE TABLE `items` ( -- holdings/item information DROP TABLE IF EXISTS `itemtypes`; CREATE TABLE `itemtypes` ( -- defines the item types - `itemtype` varchar(10) NOT NULL default '', -- unique key, a code associated with the item type - `description` mediumtext, -- a plain text explanation of the item type - `rentalcharge` double(16,4) default NULL, -- the amount charged when this item is checked out/issued - `notforloan` smallint(6) default NULL, -- 1 if the item is not for loan, 0 if the item is available for loan - `imageurl` varchar(200) default NULL, -- URL for the item type icon - `summary` text, -- information from the summary field, may include HTML + itemtype varchar(10) NOT NULL default '', -- unique key, a code associated with the item type + description mediumtext, -- a plain text explanation of the item type + rentalcharge double(16,4) default NULL, -- the amount charged when this item is checked out/issued + notforloan smallint(6) default NULL, -- 1 if the item is not for loan, 0 if the item is available for loan + imageurl varchar(200) default NULL, -- URL for the item type icon + summary text, -- information from the summary field, may include HTML + checkinmsg VARCHAR(255), -- message that is displayed when an item with the given item type is checked in + checkinmsgtype CHAR(16) DEFAULT 'message' NOT NULL, -- type (CSS class) for the checkinmsg, can be "alert" or "message" + sip_media_type VARCHAR(3) DEFAULT NULL, -- SIP2 protocol media type for this itemtype PRIMARY KEY (`itemtype`), UNIQUE KEY `itemtype` (`itemtype`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1321,7 +1354,10 @@ CREATE TABLE `letter` ( -- table for all notice templates in Koha `is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no) `title` varchar(200) NOT NULL default '', -- subject line of the notice `content` text, -- body text for the notice or slip - PRIMARY KEY (`module`,`code`, `branchcode`) + `message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice + PRIMARY KEY (`module`,`code`, `branchcode`, `message_transport_type`), + CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`) + REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -1521,6 +1557,7 @@ CREATE TABLE `oai_sets_mappings` ( `set_id` int(11) NOT NULL, `marcfield` char(3) NOT NULL, `marcsubfield` char(1) NOT NULL, + `operator` varchar(8) NOT NULL default 'equal', `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; @@ -1610,13 +1647,16 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b DROP TABLE IF EXISTS `opac_news`; CREATE TABLE `opac_news` ( -- data from the news tool `idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article + `branchcode` varchar(10) default NULL, -- branch code users to create branch specific news, NULL is every branch. `title` varchar(250) NOT NULL default '', -- title of the news article `new` text NOT NULL, -- the body of your news article `lang` varchar(25) NOT NULL default '', -- location for the article (koha is the staff client, slip is the circulation receipt and language codes are for the opac) `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- pulibcation date and time `expirationdate` date default NULL, -- date the article is set to expire or no longer be visible `number` int(11) default NULL, -- the order in which this article appears in that specific location - PRIMARY KEY (`idnew`) + PRIMARY KEY (`idnew`), + CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode) + ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -1660,11 +1700,11 @@ CREATE TABLE `patroncards` ( DROP TABLE IF EXISTS `patronimage`; CREATE TABLE `patronimage` ( -- information related to patron images - `cardnumber` varchar(16) NOT NULL, -- the cardnumber of the patron this image is attached to (borrowers.cardnumber) + `borrowernumber` int(11) NOT NULL, -- the borrowernumber of the patron this image is attached to (borrowers.borrowernumber) `mimetype` varchar(15) NOT NULL, -- the format of the image (png, jpg, etc) `imagefile` mediumblob NOT NULL, -- the image - PRIMARY KEY (`cardnumber`), - CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE + PRIMARY KEY (`borrowernumber`), + CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Table structure for table `pending_offline_operations` @@ -1819,17 +1859,6 @@ CREATE TABLE `reviews` ( -- patron opac comments ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table structure for table `roadtype` --- - -DROP TABLE IF EXISTS `roadtype`; -CREATE TABLE `roadtype` ( -- road types defined in administration and used in patron management - `roadtypeid` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha for each road type - `road_type` varchar(100) NOT NULL default '', -- text for road type - PRIMARY KEY (`roadtypeid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- -- Table structure for table `saved_sql` -- @@ -1973,6 +2002,53 @@ DROP TABLE IF EXISTS `stopwords`; ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table structure for table subscription_frequencies +-- + +DROP TABLE IF EXISTS subscription_frequencies; +CREATE TABLE subscription_frequencies ( + id INTEGER NOT NULL AUTO_INCREMENT, + description TEXT NOT NULL, + displayorder INT DEFAULT NULL, + unit ENUM('day','week','month','year') DEFAULT NULL, + unitsperissue INTEGER NOT NULL DEFAULT '1', + issuesperunit INTEGER NOT NULL DEFAULT '1', + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table subscription_numberpatterns +-- + +DROP TABLE IF EXISTS subscription_numberpatterns; +CREATE TABLE subscription_numberpatterns ( + id INTEGER NOT NULL AUTO_INCREMENT, + label VARCHAR(255) NOT NULL, + displayorder INTEGER DEFAULT NULL, + description TEXT NOT NULL, + numberingmethod VARCHAR(255) NOT NULL, + label1 VARCHAR(255) DEFAULT NULL, + add1 INTEGER DEFAULT NULL, + every1 INTEGER DEFAULT NULL, + whenmorethan1 INTEGER DEFAULT NULL, + setto1 INTEGER DEFAULT NULL, + numbering1 VARCHAR(255) DEFAULT NULL, + label2 VARCHAR(255) DEFAULT NULL, + add2 INTEGER DEFAULT NULL, + every2 INTEGER DEFAULT NULL, + whenmorethan2 INTEGER DEFAULT NULL, + setto2 INTEGER DEFAULT NULL, + numbering2 VARCHAR(255) DEFAULT NULL, + label3 VARCHAR(255) DEFAULT NULL, + add3 INTEGER DEFAULT NULL, + every3 INTEGER DEFAULT NULL, + whenmorethan3 INTEGER DEFAULT NULL, + setto3 INTEGER DEFAULT NULL, + numbering3 VARCHAR(255) DEFAULT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table structure for table `subscription` -- @@ -1988,41 +2064,28 @@ CREATE TABLE `subscription` ( `weeklength` int(11) default 0, `monthlength` int(11) default 0, `numberlength` int(11) default 0, - `periodicity` tinyint(4) default 0, - `dow` varchar(100) default '', - `numberingmethod` varchar(100) default '', + `periodicity` integer default null, + countissuesperunit INTEGER NOT NULL DEFAULT 1, `notes` mediumtext, `status` varchar(100) NOT NULL default '', - `add1` int(11) default 0, - `every1` int(11) default 0, - `whenmorethan1` int(11) default 0, - `setto1` int(11) default NULL, `lastvalue1` int(11) default NULL, - `add2` int(11) default 0, - `every2` int(11) default 0, - `whenmorethan2` int(11) default 0, - `setto2` int(11) default NULL, - `lastvalue2` int(11) default NULL, - `add3` int(11) default 0, - `every3` int(11) default 0, `innerloop1` int(11) default 0, + `lastvalue2` int(11) default NULL, `innerloop2` int(11) default 0, - `innerloop3` int(11) default 0, - `whenmorethan3` int(11) default 0, - `setto3` int(11) default NULL, `lastvalue3` int(11) default NULL, - `issuesatonce` tinyint(3) NOT NULL default 1, + `innerloop3` int(11) default 0, `firstacquidate` date default NULL, `manualhistory` tinyint(1) NOT NULL default 0, `irregularity` text, + skip_serialseq BOOLEAN NOT NULL DEFAULT 0, `letter` varchar(20) default NULL, - `numberpattern` tinyint(3) default 0, + `numberpattern` integer default null, + locale VARCHAR(80) DEFAULT NULL, `distributedto` text, `internalnotes` longtext, `callnumber` text, `location` varchar(80) NULL default '', `branchcode` varchar(10) NOT NULL default '', - `hemisphere` tinyint(3) default 0, `lastbranch` varchar(10), `serialsadditems` tinyint(1) NOT NULL default '0', `staffdisplaycount` VARCHAR(10) NULL, @@ -2031,7 +2094,9 @@ CREATE TABLE `subscription` ( `enddate` date default NULL, `closed` INT(1) NOT NULL DEFAULT 0, `reneweddate` date default NULL, - PRIMARY KEY (`subscriptionid`) + PRIMARY KEY (`subscriptionid`), + CONSTRAINT subscription_ibfk_1 FOREIGN KEY (periodicity) REFERENCES subscription_frequencies (id) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT subscription_ibfk_2 FOREIGN KEY (numberpattern) REFERENCES subscription_numberpatterns (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -2078,7 +2143,7 @@ DROP TABLE IF EXISTS `suggestions`; CREATE TABLE `suggestions` ( -- purchase suggestions `suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha `suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table - `suggesteddate` date NOT NULL default 0, -- date the suggestion was submitted + `suggesteddate` date NOT NULL, -- date the suggestion was submitted `managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table `manageddate` date default NULL, -- date the suggestion was updated acceptedby INT(11) default NULL, -- borrowernumber for the librarian who accepted the suggestion, foreign key linking to the borrowers table @@ -2280,6 +2345,7 @@ CREATE TABLE `z3950servers` ( -- connection information for the Z39.50 targets u `type` enum('zed','opensearch') NOT NULL default 'zed', `encoding` text default NULL, -- characters encoding provided by this target `description` text NOT NULL, -- unused in Koha + `recordtype` varchar(45) NOT NULL default 'biblio', -- server contains bibliographic or authority records PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2486,6 +2552,22 @@ CREATE TABLE `message_transport_types` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table structure for table `overduerules_transport_types` +-- + +DROP TABLE IF EXISTS `overduerules_transport_types`; +CREATE TABLE overduerules_transport_types( + `id` INT(11) NOT NULL AUTO_INCREMENT, + `branchcode` varchar(10) NOT NULL DEFAULT '', + `categorycode` VARCHAR(10) NOT NULL DEFAULT '', + `letternumber` INT(1) NOT NULL DEFAULT 1, + `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email', + PRIMARY KEY (id), + CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table structure for table `message_attributes` -- @@ -2672,7 +2754,12 @@ CREATE TABLE `action_logs` ( -- logs of actions taken in Koha (requires that the `object` int(11) default NULL, -- the object that the action was taken against (could be a borrowernumber, itemnumber, etc) `info` text, -- information about the action (usually includes SQL statement) PRIMARY KEY (`action_id`), - KEY (`timestamp`,`user`) + KEY `timestamp_idx` (`timestamp`), + KEY `user_idx` (`user`), + KEY `module_idx` (`module`(255)), + KEY `action_idx` (`action`(255)), + KEY `object_idx` (`object`), + KEY `info_idx` (`info`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -2728,13 +2815,28 @@ CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions `basketgroupid` int(11), -- links this basket to its group (aqbasketgroups.id) `deliveryplace` varchar(10) default NULL, -- basket delivery place `billingplace` varchar(10) default NULL, -- basket billing place + branch varchar(10) default NULL, -- basket branch PRIMARY KEY (`basketno`), KEY `booksellerid` (`booksellerid`), KEY `basketgroupid` (`basketgroupid`), KEY `contractnumber` (`contractnumber`), CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE, CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`), - CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE + CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE, + CONSTRAINT aqbasket_ibfk_4 FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE SET NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table aqbasketusers +-- + +DROP TABLE IF EXISTS aqbasketusers; +CREATE TABLE aqbasketusers ( + basketno int(11) NOT NULL, + borrowernumber int(11) NOT NULL, + PRIMARY KEY (basketno,borrowernumber), + CONSTRAINT aqbasketusers_ibfk_1 FOREIGN KEY (basketno) REFERENCES aqbasket (basketno) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT aqbasketusers_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -2878,19 +2980,6 @@ CREATE TABLE `aqcontract` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- --- Table structure for table `aqorderdelivery` --- - -DROP TABLE IF EXISTS `aqorderdelivery`; -CREATE TABLE `aqorderdelivery` ( - `ordernumber` date default NULL, - `deliverynumber` smallint(6) NOT NULL default 0, - `deliverydate` varchar(18) default NULL, - `qtydelivered` smallint(6) default NULL, - `deliverycomments` mediumtext -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- -- Table structure for table `aqorders` -- @@ -2910,7 +2999,8 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far `cancelledby` varchar(10) default NULL, -- not used? always NULL `datecancellationprinted` date default NULL, -- the date the line item was deleted - `notes` mediumtext, -- notes related to this order line + `order_internalnote` mediumtext, -- notes related to this order line, made for staff + `order_vendornote` mediumtext, -- notes related to this order line, made for vendor `supplierreference` mediumtext, -- not used? always NULL `purchaseordernumber` mediumtext, -- not used? always NULL `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno) @@ -2931,6 +3021,7 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `claimed_date` date default NULL, -- last date a claim was generated `subscriptionid` int(11) default NULL, -- links this order line to a subscription (subscription.subscriptionid) parent_ordernumber int(11) default NULL, -- ordernumber of parent order line, or same as ordernumber if no parent + `orderstatus` varchar(16) default 'new', -- the current status for this line item. Can be 'new', 'ordered', 'partial', 'complete' or 'cancelled' PRIMARY KEY (`ordernumber`), KEY `basketno` (`basketno`), KEY `biblionumber` (`biblionumber`), @@ -3229,6 +3320,85 @@ CREATE TABLE IF NOT EXISTS plugin_data ( PRIMARY KEY (plugin_class,plugin_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `patron_lists` +-- + +DROP TABLE IF EXISTS patron_lists; +CREATE TABLE patron_lists ( + patron_list_id int(11) NOT NULL AUTO_INCREMENT, -- unique identifier + name varchar(255) CHARACTER SET utf8 NOT NULL, -- the list's name + owner int(11) NOT NULL, -- borrowernumber of the list creator + PRIMARY KEY (patron_list_id), + KEY owner (owner) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Constraints for table `patron_lists` +-- +ALTER TABLE `patron_lists` + ADD CONSTRAINT patron_lists_ibfk_1 FOREIGN KEY (`owner`) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Table structure for table 'patron_list_patrons' +-- + +DROP TABLE IF EXISTS patron_list_patrons; +CREATE TABLE patron_list_patrons ( + patron_list_patron_id int(11) NOT NULL AUTO_INCREMENT, -- unique identifier + patron_list_id int(11) NOT NULL, -- the list this entry is part of + borrowernumber int(11) NOT NULL, -- the borrower that is part of this list + PRIMARY KEY (patron_list_patron_id), + KEY patron_list_id (patron_list_id), + KEY borrowernumber (borrowernumber) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Constraints for table `patron_list_patrons` +-- +ALTER TABLE `patron_list_patrons` + ADD CONSTRAINT patron_list_patrons_ibfk_1 FOREIGN KEY (patron_list_id) REFERENCES patron_lists (patron_list_id) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT patron_list_patrons_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Table structure for table 'marc_modification_templates' +-- + +CREATE TABLE IF NOT EXISTS marc_modification_templates ( + template_id int(11) NOT NULL AUTO_INCREMENT, + name text NOT NULL, + PRIMARY KEY (template_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table 'marc_modification_template_actions' +-- + +CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( + mmta_id int(11) NOT NULL AUTO_INCREMENT, + template_id int(11) NOT NULL, + ordering int(3) NOT NULL, + action enum('delete_field','update_field','move_field','copy_field') NOT NULL, + field_number smallint(6) NOT NULL DEFAULT '0', + from_field varchar(3) NOT NULL, + from_subfield varchar(1) DEFAULT NULL, + field_value varchar(100) DEFAULT NULL, + to_field varchar(3) DEFAULT NULL, + to_subfield varchar(1) DEFAULT NULL, + to_regex_search text, + to_regex_replace text, + to_regex_modifiers varchar(8) DEFAULT '', + conditional enum('if','unless') DEFAULT NULL, + conditional_field varchar(3) DEFAULT NULL, + conditional_subfield varchar(1) DEFAULT NULL, + conditional_comparison enum('exists','not_exists','equals','not_equals') DEFAULT NULL, + conditional_value text, + conditional_regex tinyint(1) NOT NULL DEFAULT '0', + description text, + PRIMARY KEY (mmta_id), + CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) 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 */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;