`BlockExpiredPatronOpacActions` tinyint(1) NOT NULL default '-1', -- wheither or not a patron of this category can renew books or place holds once their card has expired. 0 means they can, 1 means they cannot, -1 means use syspref BlockExpiredPatronOpacActions
`default_privacy` ENUM( 'default', 'never', 'forever' ) NOT NULL DEFAULT 'default', -- Default privacy setting for this patron category
`checkprevcheckout` varchar(7) NOT NULL default 'inherit', -- produce a warning for this patron category if this item has previously been checked out to this patron if 'yes', not if 'no', defer to syspref setting if 'inherit'.
+ `reset_password` TINYINT(1) NULL DEFAULT NULL, -- if patrons of this category can do the password reset flow,
+ `change_password` TINYINT(1) NULL DEFAULT NULL, -- if patrons of this category can change their passwords in the OAPC
PRIMARY KEY (`categorycode`),
UNIQUE KEY `categorycode` (`categorycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
`lang` varchar(25) NOT NULL default 'default', -- lang to use to send notices to this patron
`login_attempts` int(4) default 0, -- number of failed login attemps
`overdrive_auth_token` MEDIUMTEXT default NULL, -- persist OverDrive auth token
+ `flgAnonymized` tinyint DEFAULT 0, -- flag for data anonymization
KEY borrowernumber (borrowernumber),
KEY `cardnumber` (`cardnumber`),
KEY `sms_provider_id` (`sms_provider_id`)
itemtype varchar(10) NOT NULL default '', -- unique key, a code associated with the item type
description LONGTEXT, -- a plain text explanation of the item type
rentalcharge decimal(28,6) default NULL, -- the amount charged when this item is checked out/issued
- rentalcharge_daily decimal(28,6) default NULL, -- the amount charged for each increment (day/hour) between checkout date and due date
+ rentalcharge_daily decimal(28,6) default NULL, -- the amount charged for each day between checkout date and due date
+ rentalcharge_hourly decimal(28,6) default NULL, -- the amount charged for each hour between checkout date and due date
defaultreplacecost decimal(28,6) default NULL, -- default replacement cost
processfee decimal(28,6) default NULL, -- default text be recorded in the column note when the processing fee is applied
notforloan smallint(6) default NULL, -- 1 if the item is not for loan, 0 if the item is available for loan
`label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display',
`type` ENUM('', 'string', 'date', 'number', 'boolean', 'sum', 'isbn', 'stdno') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine',
`weight` decimal(5,2) DEFAULT NULL,
+ `facet_order` TINYINT(4) DEFAULT NULL COMMENT 'the order place of the field in facet list if faceted',
PRIMARY KEY (`id`),
UNIQUE KEY (`name` (191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
id int(11) NOT NULL AUTO_INCREMENT,
index_name ENUM('biblios','authorities') NOT NULL COMMENT 'what storage index this map is for',
marc_type ENUM('marc21', 'unimarc', 'normarc') NOT NULL COMMENT 'what MARC type this map is for',
- marc_field VARCHAR(255) NOT NULL COMMENT 'the MARC specifier for this field',
+ marc_field VARCHAR(255) NOT NULL COLLATE utf8mb4_bin COMMENT 'the MARC specifier for this field',
PRIMARY KEY(`id`),
UNIQUE key `index_name` (`index_name`, `marc_field` (191), `marc_type`),
INDEX (`index_name`)
`lang` varchar(25) NOT NULL default 'default', -- lang to use to send notices to this patron
`login_attempts` int(4) default 0, -- number of failed login attemps
`overdrive_auth_token` MEDIUMTEXT default NULL, -- persist OverDrive auth token
+ `flgAnonymized` tinyint DEFAULT 0, -- flag for data anonymization
UNIQUE KEY `cardnumber` (`cardnumber`),
PRIMARY KEY `borrowernumber` (`borrowernumber`),
KEY `categorycode` (`categorycode`),
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
`returndate` datetime default NULL, -- date the item was returned, will be NULL until moved to old_issues
`lastreneweddate` datetime default NULL, -- date the item was last renewed
- `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
+ `renewals` tinyint(4) NOT NULL default 0, -- lists the number of times the item was renewed
`auto_renew` BOOLEAN default FALSE, -- automatic renewal
`auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
`returndate` datetime default NULL, -- date the item was returned
`lastreneweddate` datetime default NULL, -- date the item was last renewed
- `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
+ `renewals` tinyint(4) NOT NULL default 0, -- lists the number of times the item was renewed
`auto_renew` BOOLEAN default FALSE, -- automatic renewal
`auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- automatic renewal error
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
CREATE TABLE `statistics` ( -- information related to transactions (circulation and fines) in Koha
`datetime` datetime default NULL, -- date and time of the transaction
`branch` varchar(10) default NULL, -- foreign key, branch where the transaction occurred
- `proccode` varchar(4) default NULL, -- type of procedure used when making payments (does not appear in the code)
`value` double(16,4) default NULL, -- monetary value associated with the transaction
`type` varchar(16) default NULL, -- transaction type (localuse, issue, return, renew, writeoff, payment)
`other` LONGTEXT, -- used by SIP
`ccode` varchar(80) default NULL, -- foreign key from the items table, links transaction to a specific collection code
KEY `timeidx` (`datetime`),
KEY `branch_idx` (`branch`),
- KEY `proccode_idx` (`proccode`),
KEY `type_idx` (`type`),
KEY `usercode_idx` (`usercode`),
KEY `itemnumber_idx` (`itemnumber`),
`tag_id` int(11) NOT NULL auto_increment, -- unique id and primary key
`borrowernumber` int(11) DEFAULT NULL, -- the patron who added the tag (borrowers.borrowernumber)
`biblionumber` int(11) NOT NULL, -- the bib record this tag was left on (biblio.biblionumber)
- `term` varchar(255) NOT NULL, -- the tag
+ `term` varchar(191) NOT NULL COLLATE utf8mb4_bin, -- the tag
`language` int(4) default NULL, -- the language the tag was left in
`date_created` datetime NOT NULL, -- the date the tag was added
PRIMARY KEY (`tag_id`),
DROP TABLE IF EXISTS `tags_approval`;
CREATE TABLE `tags_approval` ( -- approved tags
- `term` varchar(191) NOT NULL, -- the tag
+ `term` varchar(191) NOT NULL COLLATE utf8mb4_bin, -- the tag
`approved` int(1) NOT NULL default '0', -- whether the tag is approved or not (1=yes, 0=pending, -1=rejected)
`date_approved` datetime default NULL, -- the date this tag was approved
`approved_by` int(11) default NULL, -- the librarian who approved the tag (borrowers.borrowernumber)
DROP TABLE IF EXISTS `tags_index`;
CREATE TABLE `tags_index` ( -- a weighted list of all tags and where they are used
- `term` varchar(191) NOT NULL, -- the tag
+ `term` varchar(191) NOT NULL COLLATE utf8mb4_bin, -- the tag
`biblionumber` int(11) NOT NULL, -- the bib record this tag was used on (biblio.biblionumber)
`weight` int(9) NOT NULL default '1', -- the number of times this term was used on this bib record
PRIMARY KEY (`term`,`biblionumber`),
`accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
`issue_id` int(11) NULL DEFAULT NULL,
`borrowernumber` int(11) DEFAULT NULL,
- `accountno` smallint(6) NOT NULL default 0,
`itemnumber` int(11) default NULL,
`date` date default NULL,
`amount` decimal(28,6) default NULL,
`description` LONGTEXT,
- `accounttype` varchar(5) default NULL,
+ `accounttype` varchar(16) default NULL,
+ `status` varchar(16) default NULL,
`payment_type` varchar(80) default NULL, -- optional authorised value PAYMENT_TYPE
`amountoutstanding` decimal(28,6) default NULL,
- `lastincrement` decimal(28,6) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`note` MEDIUMTEXT NULL default NULL,
- `manager_id` int(11) NULL,
+ `manager_id` int(11) NULL DEFAULT NULL,
+ `interface` VARCHAR(16) NOT NULL,
`branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc.
PRIMARY KEY (`accountlines_id`),
KEY `acctsborridx` (`borrowernumber`),
KEY `timeidx` (`timestamp`),
KEY `itemnumber` (`itemnumber`),
KEY `branchcode` (`branchcode`),
+ KEY `manager_id` (`manager_id`),
CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
- CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
+ CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
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
+ `suggestedby` int(11) DEFAULT NULL, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table
`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
PRIMARY KEY (`suggestionid`),
KEY `suggestedby` (`suggestedby`),
KEY `managedby` (`managedby`),
- KEY `status` (`STATUS`),
+ KEY `acceptedby` (`acceptedby`),
+ KEY `rejectedby` (`rejectedby`),
KEY `biblionumber` (`biblionumber`),
+ KEY `budgetid` (`budgetid`),
KEY `branchcode` (`branchcode`),
- CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE
+ KEY `status` (`STATUS`),
+ CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_ibfk_managedby` FOREIGN KEY (`managedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_ibfk_acceptedby` FOREIGN KEY (`acceptedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_ibfk_rejectedby` FOREIGN KEY (`rejectedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_ibfk_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `suggestions_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `stockrotationrotas`
--
-CREATE TABLE IF NOT EXISTS stockrotationrotas (
+DROP TABLE IF EXISTS stockrotationrotas;
+CREATE TABLE stockrotationrotas (
rota_id int(11) auto_increment, -- Stockrotation rota ID
title varchar(100) NOT NULL, -- Title for this rota
description text NOT NULL, -- Description for this rota
-- Table structure for table `stockrotationstages`
--
-CREATE TABLE IF NOT EXISTS stockrotationstages (
+DROP TABLE IF EXISTS stockrotationstages;
+CREATE TABLE stockrotationstages (
stage_id int(11) auto_increment, -- Unique stage ID
position int(11) NOT NULL, -- The position of this stage within its rota
rota_id int(11) NOT NULL, -- The rota this stage belongs to
-- Table structure for table `stockrotationitems`
--
-CREATE TABLE IF NOT EXISTS stockrotationitems (
+DROP TABLE IF EXISTS stockrotationitems;
+CREATE TABLE stockrotationitems (
itemnumber_id int(11) NOT NULL, -- Itemnumber to link to a stage & rota
stage_id int(11) NOT NULL, -- stage ID to link the item to
indemand tinyint(1) NOT NULL default 0, -- Should this item be skipped for rotation?
projects / koha-equinox.git / blobdiff