`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;
`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
`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
) 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`
--
`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
`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
`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';
`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
`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
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;
`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;
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`
) 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`
--
`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,
`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;
--
`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;
`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`),
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_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;
--
`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`),
--
+-- 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
--
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 */;
projects / koha-ffzg.git / blobdiff