X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=e1e3e96611419419afe519600d2148b2dac6c78f;hb=a10773dc7775e33a10a31f1b4d1cd9ee0696c73d;hp=d004fe1d6453d21536a0613d549ac09731b5f76c;hpb=2bd3c85961ca878376f524c448d7ffb48bd7dfc3;p=koha_fer diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index d004fe1d64..e1e3e96611 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; @@ -184,6 +185,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in PRIMARY KEY (`biblioitemnumber`), KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), + KEY `itemtype_idx` (`itemtype`), KEY `isbn` (`isbn`), KEY `issn` (`issn`), KEY `publishercode` (`publishercode`), @@ -245,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 @@ -340,7 +342,9 @@ CREATE TABLE `branchcategories` ( -- information related to library/branch group `categoryname` varchar(32), -- name of the library/branch group `codedescription` mediumtext, -- longer description of the library/branch group `categorytype` varchar(16), -- says whether this is a search group or a properties group - PRIMARY KEY (`categorycode`) + `show_in_pulldown` tinyint(1) NOT NULL DEFAULT '0', -- says this group should be in the opac libararies pulldown if it is enabled + PRIMARY KEY (`categorycode`), + KEY `show_in_pulldown` (`show_in_pulldown`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -466,7 +470,109 @@ CREATE TABLE collections_tracking ( ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8; -- --- Table structure for table `borrower_branch_circ_rules` +-- Table structure for table `courses` +-- + +-- The courses table stores the courses created for the +-- course reserves feature. + +DROP TABLE IF EXISTS courses; +CREATE TABLE `courses` ( + `course_id` int(11) NOT NULL AUTO_INCREMENT, + `department` varchar(80) DEFAULT NULL, -- Stores the authorised value DEPT + `course_number` varchar(255) DEFAULT NULL, -- An arbitrary field meant to store the "course number" assigned to a course + `section` varchar(255) DEFAULT NULL, -- Also arbitrary, but for the 'section' of a course. + `course_name` varchar(255) DEFAULT NULL, + `term` varchar(80) DEFAULT NULL, -- Stores the authorised value TERM + `staff_note` mediumtext, + `public_note` mediumtext, + `students_count` varchar(20) DEFAULT NULL, -- Meant to be just an estimate of how many students will be taking this course/section + `enabled` enum('yes','no') NOT NULL DEFAULT 'yes', -- Determines whether the course is active + `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`course_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `course_instructors` +-- + +-- The course instructors table links Koha borrowers to the +-- courses they are teaching. Many instructors can teach many +-- courses. course_instructors is just a many-to-many join table. + +DROP TABLE IF EXISTS course_instructors; +CREATE TABLE `course_instructors` ( + `course_id` int(11) NOT NULL, + `borrowernumber` int(11) NOT NULL, + PRIMARY KEY (`course_id`,`borrowernumber`), + KEY `borrowernumber` (`borrowernumber`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Constraints for table `course_instructors` +-- +ALTER TABLE `course_instructors` + ADD CONSTRAINT `course_instructors_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`), + ADD CONSTRAINT `course_instructors_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Table structure for table `course_items` +-- + +-- If an item is placed on course reserve for one or more courses +-- it will have an entry in this table. No matter how many courses an item +-- is part of, it will only have one row in this table. + +DROP TABLE IF EXISTS course_items; +CREATE TABLE `course_items` ( + `ci_id` int(11) NOT NULL AUTO_INCREMENT, + `itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve + `itype` varchar(10) DEFAULT NULL, -- an optional new itemtype for the item to have while on reserve + `ccode` varchar(10) DEFAULT NULL, -- an optional new category code for the item to have while on reserve + `holdingbranch` varchar(10) DEFAULT NULL, -- an optional new holding branch for the item to have while on reserve + `location` varchar(80) DEFAULT NULL, -- an optional new shelving location for the item to have while on reseve + `enabled` enum('yes','no') NOT NULL DEFAULT 'no', -- If at least one enabled course has this item on reseve, this field will be 'yes', otherwise it will be 'no' + `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`ci_id`), + UNIQUE KEY `itemnumber` (`itemnumber`), + KEY `holdingbranch` (`holdingbranch`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Constraints for table `course_items` +-- +ALTER TABLE `course_items` + ADD CONSTRAINT `course_items_ibfk_2` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `course_items_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE; + +-- +-- Table structure for table `course_reserves` +-- + +-- This table connects an item placed on course reserve to a course it is on reserve for. +-- There will be a row in this table for each course an item is on reserve for. + +DROP TABLE IF EXISTS course_reserves; +CREATE TABLE `course_reserves` ( + `cr_id` int(11) NOT NULL AUTO_INCREMENT, + `course_id` int(11) NOT NULL, -- Foreign key to the courses table + `ci_id` int(11) NOT NULL, -- Foreign key to the course_items table + `staff_note` mediumtext, -- Staff only note + `public_note` mediumtext, -- Public, OPAC visible note + `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`cr_id`), + UNIQUE KEY `pseudo_key` (`course_id`,`ci_id`), + KEY `course_id` (`course_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Constraints for table `course_reserves` +-- +ALTER TABLE `course_reserves` + ADD CONSTRAINT `course_reserves_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`); + +-- +-- Table structure for table `branch_borrower_circ_rules` -- DROP TABLE IF EXISTS `branch_borrower_circ_rules`; @@ -657,6 +763,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t PRIMARY KEY (`biblioitemnumber`), KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), + KEY `itemtype_idx` (`itemtype`), KEY `isbn` (`isbn`), KEY `publishercode` (`publishercode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -760,7 +867,7 @@ 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) - `wthdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0) + `withdrawn` 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) `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 @@ -791,7 +898,8 @@ CREATE TABLE `deleteditems` ( KEY `delitembinoidx` (`biblioitemnumber`), KEY `delitembibnoidx` (`biblionumber`), KEY `delhomebranch` (`homebranch`), - KEY `delholdingbranch` (`holdingbranch`) + KEY `delholdingbranch` (`holdingbranch`), + KEY `itype_idx` (`itype`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -814,11 +922,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'; @@ -896,7 +1005,8 @@ CREATE TABLE `import_records` ( CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`) REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE, KEY `branchcode` (`branchcode`), - KEY `batch_sequence` (`import_batch_id`, `record_sequence`) + KEY `batch_sequence` (`import_batch_id`, `record_sequence`), + KEY `batch_id_record_type` (`import_batch_id`,`record_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -988,6 +1098,9 @@ CREATE TABLE `issues` ( -- information related to check outs or issues `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched `issuedate` datetime default NULL, -- date the item was checked out or issued KEY `issuesborridx` (`borrowernumber`), + KEY `itemnumber_idx` (`itemnumber`), + KEY `branchcode_idx` (`branchcode`), + KEY `issuingbranch_idx` (`issuingbranch`), KEY `bordate` (`borrowernumber`,`timestamp`), CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE @@ -1016,9 +1129,10 @@ CREATE TABLE `issuingrules` ( -- circulation and fine rules `hardduedate` date default NULL, -- hard due date `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 `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 default NULL, -- the maximum amount of an overdue fine + overduefinescap decimal(28,6) default NULL, -- the maximum amount of an overdue fine PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`), KEY `categorycode` (`categorycode`), KEY `itemtype` (`itemtype`) @@ -1046,7 +1160,7 @@ 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) - `wthdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0) + `withdrawn` 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) `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 @@ -1080,6 +1194,7 @@ CREATE TABLE `items` ( -- holdings/item information KEY `itemcallnumber` (`itemcallnumber`), KEY `items_location` (`location`), KEY `items_ccode` (`ccode`), + KEY `itype_idx` (`itype`), 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 @@ -1091,12 +1206,14 @@ 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" PRIMARY KEY (`itemtype`), UNIQUE KEY `itemtype` (`itemtype`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1377,20 +1494,6 @@ CREATE TABLE `notifys` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table structure for table `nozebra` --- - -DROP TABLE IF EXISTS `nozebra`; -CREATE TABLE `nozebra` ( - `server` varchar(20) NOT NULL, - `indexname` varchar(40) NOT NULL, - `value` varchar(250) NOT NULL, - `biblionumbers` longtext NOT NULL, - KEY `indexname` (`server`,`indexname`), - KEY `value` (`server`,`value`)) - ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- -- Table structure for table `oai_sets` -- @@ -1422,6 +1525,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; @@ -1458,6 +1562,8 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r `issuedate` datetime default NULL, -- date the item was checked out or issued KEY `old_issuesborridx` (`borrowernumber`), KEY `old_issuesitemidx` (`itemnumber`), + KEY `branchcode_idx` (`branchcode`), + KEY `issuingbranch_idx` (`issuingbranch`), KEY `old_bordate` (`borrowernumber`,`timestamp`), CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, @@ -1559,11 +1665,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` @@ -1572,17 +1678,17 @@ CREATE TABLE `patronimage` ( -- information related to patron images -- 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, +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; - + barcode varchar(20) DEFAULT NULL, + cardnumber varchar(16) DEFAULT NULL, + amount decimal(28,6) DEFAULT NULL, + PRIMARY KEY (operationid) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- @@ -1799,7 +1905,6 @@ CREATE TABLE `serial` ( `planneddate` date default NULL, `notes` text, `publisheddate` date default NULL, - `itemnumber` text default NULL, `claimdate` date default NULL, `routingnotes` text, PRIMARY KEY (`serialid`) @@ -1851,7 +1956,16 @@ CREATE TABLE `statistics` ( -- information related to transactions (circulation `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower `associatedborrower` int(11) default NULL, `ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code - KEY `timeidx` (`datetime`) + KEY `timeidx` (`datetime`), + KEY `branch_idx` (`branch`), + KEY `proccode_idx` (`proccode`), + KEY `type_idx` (`type`), + KEY `usercode_idx` (`usercode`), + KEY `itemnumber_idx` (`itemnumber`), + KEY `itemtype_idx` (`itemtype`), + KEY `borrowernumber_idx` (`borrowernumber`), + KEY `associatedborrower_idx` (`associatedborrower`), + KEY `ccode_idx` (`ccode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -1921,6 +2035,7 @@ CREATE TABLE `subscription` ( `graceperiod` int(11) NOT NULL default '0', `enddate` date default NULL, `closed` INT(1) NOT NULL DEFAULT 0, + `reneweddate` date default NULL, PRIMARY KEY (`subscriptionid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1978,7 +2093,7 @@ CREATE TABLE `suggestions` ( -- purchase suggestions `STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED) `note` mediumtext, -- note entered on the suggestion `author` varchar(80) default NULL, -- author of the suggested item - `title` varchar(80) default NULL, -- title of the suggested item + `title` varchar(255) default NULL, -- title of the suggested item `copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item `publishercode` varchar(255) default NULL, -- publisher of the suggested item `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the suggestion was updated @@ -2170,6 +2285,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; @@ -2409,6 +2525,24 @@ CREATE TABLE `message_transports` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- +-- Table structure for table `borrower_files` +-- + +DROP TABLE IF EXISTS `borrower_files`; +CREATE TABLE IF NOT EXISTS `borrower_files` ( -- files attached to the patron/borrower record + `file_id` int(11) NOT NULL AUTO_INCREMENT, -- unique key + `borrowernumber` int(11) NOT NULL, -- foreign key linking to the patron via the borrowernumber + `file_name` varchar(255) NOT NULL, -- file name + `file_type` varchar(255) NOT NULL, -- type of file + `file_description` varchar(255) DEFAULT NULL, -- description given to the file + `file_content` longblob NOT NULL, -- the file + `date_uploaded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- date and time the file was added + PRIMARY KEY (`file_id`), + KEY `borrowernumber` (`borrowernumber`), + CONSTRAINT borrower_files_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table structure for table `borrower_message_preferences` -- @@ -2785,10 +2919,7 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `notes` mediumtext, -- notes related to this order line `supplierreference` mediumtext, -- not used? always NULL `purchaseordernumber` mediumtext, -- not used? always NULL - `subscription` tinyint(1) default NULL, -- not used? always NULL - `serialid` varchar(30) default NULL, -- not used? always NULL `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno) - `biblioitemnumber` int(11) default NULL, -- links this order line the biblioitems table (biblioitems.biblioitemnumber) `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified `rrp` decimal(13,2) default NULL, -- the replacement cost for this line item `ecost` decimal(13,2) default NULL, -- the estimated cost for this line item @@ -2804,6 +2935,7 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items `uncertainprice` tinyint(1), -- was this price uncertain (1 for yes, 0 for no) `claims_count` int(11) default 0, -- count of claim letters generated `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 PRIMARY KEY (`ordernumber`), KEY `basketno` (`basketno`), @@ -2811,7 +2943,8 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items KEY `budget_id` (`budget_id`), CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `aqorders_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2831,6 +2964,21 @@ CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisiti -- +-- Table structure for table aqorders_transfers +-- + +DROP TABLE IF EXISTS aqorders_transfers; +CREATE TABLE aqorders_transfers ( + ordernumber_from int(11) NULL, + ordernumber_to int(11) NULL, + timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE KEY ordernumber_from (ordernumber_from), + UNIQUE KEY ordernumber_to (ordernumber_to), + CONSTRAINT aqorders_transfers_ordernumber_from FOREIGN KEY (ordernumber_from) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT aqorders_transfers_ordernumber_to FOREIGN KEY (ordernumber_to) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- -- Table structure for table aqinvoices -- @@ -2886,12 +3034,12 @@ CREATE TABLE transport_cost ( 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, +CREATE TABLE `biblioimages` ( -- local cover images + `imagenumber` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the image + `biblionumber` int(11) NOT NULL, -- foreign key from biblio table to link to biblionumber + `mimetype` varchar(15) NOT NULL, -- image type + `imagefile` mediumblob NOT NULL, -- image file contents + `thumbnail` mediumblob NOT NULL, -- thumbnail file contents PRIMARY KEY (`imagenumber`), CONSTRAINT `bibliocoverimage_fk1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2932,11 +3080,11 @@ CREATE TABLE ratings ( -- information related to the star ratings in the OPAC -- DROP TABLE IF EXISTS quotes; -CREATE TABLE `quotes` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `source` text DEFAULT NULL, - `text` mediumtext NOT NULL, - `timestamp` datetime NOT NULL, +CREATE TABLE `quotes` ( -- data for the quote of the day feature + `id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the quote + `source` text DEFAULT NULL, -- source/credit for the quote + `text` mediumtext NOT NULL, -- text of the quote + `timestamp` datetime NOT NULL, -- date and time that the quote last appeared in the opac PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -3076,6 +3224,58 @@ CREATE TABLE linktracker ( KEY dateidx (timeclicked) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table 'plugin_data' +-- + +CREATE TABLE IF NOT EXISTS plugin_data ( + plugin_class varchar(255) NOT NULL, + plugin_key varchar(255) NOT NULL, + plugin_value text, + 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; + + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;