`authid` bigint(20) unsigned NOT NULL auto_increment,
`authtypecode` varchar(10) NOT NULL default '',
`datecreated` date default NULL,
- `datemodified` date default NULL,
+ `modification_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`origincode` varchar(20) default NULL,
`authtrees` mediumtext,
`marc` blob,
`frameworkcode` varchar(10) NOT NULL default '',
`defaultvalue` TEXT DEFAULT '',
PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
- KEY `tab` (`authtypecode`,`tab`)
+ KEY `tab` (`authtypecode`,`tab`),
+ CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
DROP TABLE IF EXISTS `biblio`;
CREATE TABLE `biblio` ( -- table that stores bibliographic information
`biblionumber` int(11) NOT NULL auto_increment, -- unique identifier assigned to each bibliographic record
- `frameworkcode` varchar(4) NOT NULL default '', -- foriegn key from the biblio_framework table to identify which framework was used in cataloging this record
+ `frameworkcode` varchar(4) NOT NULL default '', -- foreign key from the biblio_framework table to identify which framework was used in cataloging this record
`author` mediumtext, -- statement of responsibility from MARC record (100$a in MARC21)
`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)
`contactfirstname` text, -- used for children to include first name of guarentor
`contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor
`guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations
- `borrowernotes` mediumtext, -- a note on the patron/borroewr's account that is only visible in the staff client
+ `borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client
`relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor
- `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(60) default NULL, -- patron/borrower's encrypted password
`flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
`altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
+ `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
+ `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
UNIQUE KEY `cardnumber` (`cardnumber`),
PRIMARY KEY `borrowernumber` (`borrowernumber`),
KEY `categorycode` (`categorycode`),
KEY `surname_idx` (`surname`(255)),
KEY `firstname_idx` (`firstname`(255)),
KEY `othernames_idx` (`othernames`(255)),
+ KEY `sms_provider_id` (`sms_provider_id`),
CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`),
- CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
+ CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`),
+ CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`repeatable` tinyint(1) NOT NULL default 0, -- defines whether one patron/borrower can have multiple values for this custom field (1 for yes, 0 for no)
`unique_id` tinyint(1) NOT NULL default 0, -- defines if this value needs to be unique (1 for yes, 0 for no)
`opac_display` tinyint(1) NOT NULL default 0, -- defines if this field is visible to patrons on their account in the OPAC (1 for yes, 0 for no)
- `password_allowed` tinyint(1) NOT NULL default 0, -- defines if it is possible to associate a password with this custom field (1 for yes, 0 for no)
`staff_searchable` tinyint(1) NOT NULL default 0, -- defines if this field is searchable via the patron search in the staff client (1 for yes, 0 for no)
`authorised_value_category` varchar(32) default NULL, -- foreign key from authorised_values that links this custom field to an authorized value category
`display_checkout` tinyint(1) NOT NULL default 0,-- defines if this field displays in checkout screens
`borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines which patron/borrower has this attribute
`code` varchar(10) NOT NULL, -- foreign key from the borrower_attribute_types table, defines which custom field this value was entered for
`attribute` varchar(255) default NULL, -- custom patron field value
- `password` varchar(64) default NULL, -- password associated with this field
KEY `borrowernumber` (`borrowernumber`),
KEY `code_attribute` (`code`, `attribute`),
CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
--
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,
+CREATE TABLE borrower_debarments ( -- tracks restrictions on the patron's record
+ borrower_debarment_id int(11) NOT NULL AUTO_INCREMENT, -- unique key for the restriction
+ borrowernumber int(11) NOT NULL, -- foreign key for borrowers.borrowernumber for patron who is restricted
+ expiration date DEFAULT NULL, -- expiration date of the restriction
+ `type` enum('SUSPENSION','OVERDUES','MANUAL','DISCHARGE') NOT NULL DEFAULT 'MANUAL', -- type of restriction
+ `comment` text, -- comments about the restriction
+ manager_id int(11) DEFAULT NULL, -- foreign key for borrowers.borrowernumber for the librarian managing the restriction
+ created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date the restriction was added
+ updated timestamp NULL DEFAULT NULL, -- date the restriction was updated
PRIMARY KEY (borrower_debarment_id),
KEY borrowernumber (borrowernumber),
CONSTRAINT `borrower_debarments_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
`branchcode` varchar(10) NOT NULL, -- the branch this rule is for (branches.branchcode)
`itemtype` varchar(10) NOT NULL, -- the item type this rule applies to (items.itype)
`holdallowed` tinyint(1) default NULL, -- the number of holds allowed
+ hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode
`returnbranch` varchar(15) default NULL, -- the branch the item returns to (homebranch, holdingbranch, noreturn)
PRIMARY KEY (`itemtype`,`branchcode`),
KEY `branch_item_rules_ibfk_2` (`branchcode`),
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+--
+-- Table structure for table `borrower_password_recovery`
+--
+
+DROP TABLE IF EXISTS `borrower_password_recovery`;
+CREATE TABLE IF NOT EXISTS `borrower_password_recovery` ( -- holds information about password recovery attempts
+ `borrowernumber` int(11) NOT NULL, -- the user asking a password recovery
+ `uuid` varchar(128) NOT NULL, -- a unique string to identify a password recovery attempt
+ `valid_until` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- a time limit on the password recovery attempt
+ PRIMARY KEY (`borrowernumber`),
+ KEY borrowernumber (borrowernumber)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
--
-- Table structure for table borrower_sync
--
+DROP TABLE IF EXISTS `borrower_sync`;
CREATE TABLE borrower_sync (
borrowersyncid int(11) NOT NULL AUTO_INCREMENT, -- Primary key, unique identifier
borrowernumber int(11) NOT NULL, -- Connects data about synchronisations to a borrower
`enrolmentperiod` smallint(6) default NULL, -- number of months the patron is enrolled for (will be NULL if enrolmentperioddate is set)
`enrolmentperioddate` DATE NULL DEFAULT NULL, -- date the patron is enrolled until (will be NULL if enrolmentperiod is set)
`upperagelimit` smallint(6) default NULL, -- age limit for the patron
- `dateofbirthrequired` tinyint(1) default NULL,
+ `dateofbirthrequired` tinyint(1) default NULL, -- the minimum age required for the patron category
`finetype` varchar(30) default NULL, -- unused in Koha
`bulk` tinyint(1) default NULL,
`enrolmentfee` decimal(28,6) default NULL, -- enrollment fee for the patron
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
+ `course_id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the course
+ `department` varchar(80) DEFAULT NULL, -- the authorised value for the DEPARTMENT
+ `course_number` varchar(255) DEFAULT NULL, -- the "course number" assigned to a course
+ `section` varchar(255) DEFAULT NULL, -- the 'section' of a course
+ `course_name` varchar(255) DEFAULT NULL, -- the name of the course
+ `term` varchar(80) DEFAULT NULL, -- the authorised value for the TERM
+ `staff_note` mediumtext, -- the text of the staff only note
+ `public_note` mediumtext, -- the text of the public / opac note
+ `students_count` varchar(20) DEFAULT NULL, -- 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 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS course_instructors;
CREATE TABLE `course_instructors` (
- `course_id` int(11) NOT NULL,
- `borrowernumber` int(11) NOT NULL,
+ `course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id
+ `borrowernumber` int(11) NOT NULL, -- foreign key to link to borrowers.borrowernumber for instructor information
PRIMARY KEY (`course_id`,`borrowernumber`),
KEY `borrowernumber` (`borrowernumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS course_items;
CREATE TABLE `course_items` (
- `ci_id` int(11) NOT NULL AUTO_INCREMENT,
+ `ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id
`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'
+ `itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional)
+ `ccode` varchar(10) DEFAULT NULL, -- new category code for the item to have while on reserve (optional)
+ `holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional)
+ `location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional)
+ `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`),
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
+ `course_id` int(11) NOT NULL, -- foreign key to link to courses.course_id
+ `ci_id` int(11) NOT NULL, -- foreign key to link to courses_items.ci_id
+ `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`),
-- Constraints for table `course_reserves`
--
ALTER TABLE `course_reserves`
- ADD CONSTRAINT `course_reserves_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`);
+ ADD CONSTRAINT `course_reserves_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`),
+ ADD CONSTRAINT `course_reserves_ibfk_2` FOREIGN KEY (`ci_id`) REFERENCES `course_items` (`ci_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+
--
-- Table structure for table `branch_borrower_circ_rules`
`branchcode` VARCHAR(10) NOT NULL, -- the branch this rule applies to (branches.branchcode)
`categorycode` VARCHAR(10) NOT NULL, -- the patron category this rule applies to (categories.categorycode)
`maxissueqty` int(4) default NULL, -- the maximum number of checkouts this patron category can have at this branch
+ `maxonsiteissueqty` int(4) default NULL, -- the maximum number of on-site checkouts this patron category can have at this branch
PRIMARY KEY (`categorycode`, `branchcode`),
CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
ON DELETE CASCADE ON UPDATE CASCADE,
CREATE TABLE `default_borrower_circ_rules` ( -- default checkout rules found under "Default checkout, hold and return policy"
`categorycode` VARCHAR(10) NOT NULL, -- patron category this rul
`maxissueqty` int(4) default NULL,
+ `maxonsiteissueqty` int(4) default NULL,
PRIMARY KEY (`categorycode`),
CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
ON DELETE CASCADE ON UPDATE CASCADE
CREATE TABLE `default_branch_circ_rules` (
`branchcode` VARCHAR(10) NOT NULL,
`maxissueqty` int(4) default NULL,
+ `maxonsiteissueqty` int(4) default NULL,
`holdallowed` tinyint(1) default NULL,
+ hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode
`returnbranch` varchar(15) default NULL,
PRIMARY KEY (`branchcode`),
CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
CREATE TABLE `default_branch_item_rules` (
`itemtype` varchar(10) NOT NULL,
`holdallowed` tinyint(1) default NULL,
+ hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode
`returnbranch` varchar(15) default NULL,
PRIMARY KEY (`itemtype`),
CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
CREATE TABLE `default_circ_rules` (
`singleton` enum('singleton') NOT NULL default 'singleton',
`maxissueqty` int(4) default NULL,
+ `maxonsiteissueqty` int(4) default NULL,
`holdallowed` int(1) default NULL,
+ hold_fulfillment_policy ENUM('any', 'homebranch', 'holdingbranch') NOT NULL DEFAULT 'any', -- limit trapping of holds by branchcode
`returnbranch` varchar(15) default NULL,
PRIMARY KEY (`singleton`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`rate` float(15,5) default NULL,
`active` tinyint(1) default NULL,
+ `archived` tinyint(1) DEFAULT 0,
PRIMARY KEY (`currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
`contactfirstname` text, -- used for children to include first name of guarentor
`contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor
`guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations
- `borrowernotes` mediumtext, -- a note on the patron/borroewr's account that is only visible in the staff client
+ `borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client
`relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor
- `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(30) default NULL, -- patron/borrower's opac and/or staff client log in
+ `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
`contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
`sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
`altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
`smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
+ `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history KEY `borrowernumber` (`borrowernumber`),
+ `privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
KEY borrowernumber (borrowernumber),
- KEY `cardnumber` (`cardnumber`)
+ KEY `cardnumber` (`cardnumber`),
+ KEY `sms_provider_id` (`sms_provider_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
`restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
`itemnotes` mediumtext, -- public notes on this item (MARC21 952$x)
+ `itemnotes_nonpublic` mediumtext default NULL,
`holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
`paidfor` mediumtext,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
`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)
+ `new_status` VARCHAR(32) DEFAULT NULL, -- 'new' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.
PRIMARY KEY (`itemnumber`),
KEY `delitembarcodeidx` (`barcode`),
KEY `delitemstocknumberidx` (`stocknumber`),
KEY `itype_idx` (`itype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
---
--- Table structure for table `ethnicity`
---
-
-DROP TABLE IF EXISTS `ethnicity`;
-CREATE TABLE `ethnicity` (
- `code` varchar(10) NOT NULL default '',
- `name` varchar(255) default NULL,
- PRIMARY KEY (`code`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
--
-- Table structure for table `export_format`
--
DROP TABLE IF EXISTS `issues`;
CREATE TABLE `issues` ( -- information related to check outs or issues
+ `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
`borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
`itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
`date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
- `issuingbranch` varchar(18) default NULL,
`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
`return` varchar(4) default NULL,
`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
`onsite_checkout` int(1) NOT NULL default 0, -- in house use flag
+ PRIMARY KEY (`issue_id`),
+ UNIQUE KEY `itemnumber` (`itemnumber`),
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
`maxsuspensiondays` int(11) default NULL, -- max suspension days
`firstremind` int(11) default NULL, -- fine grace period
`chargeperiod` int(11) default NULL, -- how often the fine amount is charged
+ `chargeperiod_charge_at` tinyint(1) NOT NULL DEFAULT '0', -- Should fine be given at the start ( 1 ) or the end ( 0 ) of the period
`accountsent` int(11) default NULL, -- not used? always NULL
`chargename` varchar(100) default NULL, -- not used? always NULL
`maxissueqty` int(4) default NULL, -- total number of checkouts allowed
+ `maxonsiteissueqty` int(4) default NULL, -- total number of on-site checkouts allowed
`issuelength` int(4) default NULL, -- length of checkout in the unit set in issuingrules.lengthunit
`lengthunit` varchar(10) default 'days', -- unit of checkout length (days, hours)
`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
- `norenewalbefore` int(4) default NULL, -- no renewal allowed until X days or hours before due date. In the unit set in issuingrules.lengthunit
+ `norenewalbefore` int(4) default NULL, -- no renewal allowed until X days or hours before due date.
`auto_renew` BOOLEAN default FALSE, -- automatic renewal
`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
+ cap_fine_to_replacement_price BOOLEAN NOT NULL DEFAULT '0', -- cap the fine based on item's replacement price
+ onshelfholds tinyint(1) NOT NULL default 0, -- allow holds for items that are on shelf
+ opacitemholds char(1) NOT NULL default 'N', -- allow opac users to place specific items on hold
PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
KEY `categorycode` (`categorycode`),
KEY `itemtype` (`itemtype`)
`reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
`restricted` tinyint(1) default NULL, -- authorized value defining use restrictions for this item (MARC21 952$5)
`itemnotes` mediumtext, -- public notes on this item (MARC21 952$x)
+ `itemnotes_nonpublic` mediumtext default NULL,
`holdingbranch` varchar(10) default NULL, -- foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)
`paidfor` mediumtext,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
`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)
+ `new_status` VARCHAR(32) DEFAULT NULL, -- 'new' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.
PRIMARY KEY (`itemnumber`),
UNIQUE KEY `itembarcodeidx` (`barcode`),
KEY `itemstocknumberidx` (`stocknumber`),
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
+ CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
+ CONSTRAINT `items_ibfk_4` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table `items_last_borrower`
+--
+
+CREATE TABLE IF NOT EXISTS `items_last_borrower` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `itemnumber` int(11) NOT NULL,
+ `borrowernumber` int(11) NOT NULL,
+ `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `itemnumber` (`itemnumber`),
+ KEY `borrowernumber` (`borrowernumber`),
+ CONSTRAINT `items_last_borrower_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
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
+ hideinopac tinyint(1) NOT NULL DEFAULT 0, -- Hide the item type from the search options in OPAC
+ searchcategory varchar(80) default NULL, -- Group this item type with others with the same value on OPAC search options
PRIMARY KEY (`itemtype`),
UNIQUE KEY `itemtype` (`itemtype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
`printing_type` char(32) NOT NULL DEFAULT 'BAR',
`layout_name` char(20) NOT NULL DEFAULT 'DEFAULT',
`guidebox` int(1) DEFAULT '0',
+ `oblique_title` int(1) DEFAULT '1',
`font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR',
`font_size` int(4) NOT NULL DEFAULT '10',
`units` char(20) NOT NULL DEFAULT 'POINT',
`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 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS `old_issues`;
CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
+ `issue_id` int(11) NOT NULL, -- primary key for issues table
`borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to
`itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out
`date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd)
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
- `issuingbranch` varchar(18) default NULL,
`returndate` datetime default NULL, -- date the item was returned
`lastreneweddate` datetime default NULL, -- date the item was last renewed
`return` varchar(4) default NULL,
`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
`onsite_checkout` int(1) NOT NULL default 0, -- in house use flag
+ PRIMARY KEY (`issue_id`),
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,
`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for
`reservedate` date default NULL, -- the date the hold was places
`biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on
- `constrainttype` varchar(1) default NULL,
`branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at
`notificationdate` date default NULL, -- currently unused
`reminderdate` date default NULL, -- currently unused
`lowestPriority` tinyint(1) NOT NULL, -- has this hold been pinned to the lowest priority in the holds queue (1 for yes, 0 for no)
`suspend` BOOLEAN NOT NULL DEFAULT 0, -- in this hold suspended (1 for yes, 0 for no)
`suspend_until` DATETIME NULL DEFAULT NULL, -- the date this hold is suspended until (NULL for infinitely)
+ `itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting
PRIMARY KEY (`reserve_id`),
KEY `old_reserves_borrowernumber` (`borrowernumber`),
KEY `old_reserves_biblionumber` (`biblionumber`),
KEY `old_reserves_itemnumber` (`itemnumber`),
KEY `old_reserves_branchcode` (`branchcode`),
+ KEY `old_reserves_itemtype` (`itemtype`),
CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
ON DELETE SET NULL ON UPDATE SET NULL,
CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
ON DELETE SET NULL ON UPDATE SET NULL,
CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
+ ON DELETE SET NULL ON UPDATE SET NULL,
+ CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
`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
+ `borrowernumber` int(11) default NULL, -- The user who created the news article
PRIMARY KEY (`idnew`),
+ CONSTRAINT `borrowernumber_fk` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS `overduerules`;
CREATE TABLE `overduerules` ( -- overdue notice status and triggers
+ `overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules
`branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to define which branch this rule is for (if blank it's all libraries)
`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for
`delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent
`delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent
`letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice
`debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no)
- PRIMARY KEY (`branchcode`,`categorycode`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
---
--- Table structure for table `patroncards`
---
-
-DROP TABLE IF EXISTS `patroncards`;
-CREATE TABLE `patroncards` (
- `cardid` int(11) NOT NULL auto_increment,
- `batch_id` varchar(10) NOT NULL default '1',
- `borrowernumber` int(11) NOT NULL,
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
- PRIMARY KEY (`cardid`),
- KEY `patroncards_ibfk_1` (`borrowernumber`),
- CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
+ PRIMARY KEY (`overduerules_id`),
+ UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
KEY dictionary_area_idx (report_area)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
---
--- Table structure for table `reserveconstraints`
---
-
-DROP TABLE IF EXISTS `reserveconstraints`;
-CREATE TABLE `reserveconstraints` (
- `borrowernumber` int(11) NOT NULL default 0,
- `reservedate` date default NULL,
- `biblionumber` int(11) NOT NULL default 0,
- `biblioitemnumber` int(11) default NULL,
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
--
-- Table structure for table `reserves`
--
`borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for
`reservedate` date default NULL, -- the date the hold was places
`biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on
- `constrainttype` varchar(1) default NULL,
`branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at
`notificationdate` date default NULL, -- currently unused
`reminderdate` date default NULL, -- currently unused
`lowestPriority` tinyint(1) NOT NULL,
`suspend` BOOLEAN NOT NULL DEFAULT 0,
`suspend_until` DATETIME NULL DEFAULT NULL,
+ `itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting
PRIMARY KEY (`reserve_id`),
KEY priorityfoundidx (priority,found),
KEY `borrowernumber` (`borrowernumber`),
KEY `biblionumber` (`biblionumber`),
KEY `itemnumber` (`itemnumber`),
KEY `branchcode` (`branchcode`),
+ KEY `itemtype` (`itemtype`),
CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
+ CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `reserves_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+--
+-- Table structure for table 'search_field'
+--
+
+DROP TABLE IF EXISTS search_field;
+CREATE TABLE `search_field` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` varchar(255) NOT NULL COMMENT 'the name of the field as it will be stored in the search engine',
+ `label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display',
+ `type` ENUM('string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine',
+ PRIMARY KEY (`id`),
+ UNIQUE KEY (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `search_history`
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Opac search history results';
+--
+-- Table structure for table 'search_marc_map'
+--
+
+DROP TABLE IF EXISTS search_marc_map;
+CREATE TABLE `search_marc_map` (
+ 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',
+ PRIMARY KEY(`id`),
+ UNIQUE key(index_name, marc_field, marc_type),
+ INDEX (`index_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'search_marc_to_field'
+--
+
+DROP TABLE IF EXISTS search_marc_to_field;
+CREATE TABLE `search_marc_to_field` (
+ search_marc_map_id int(11) NOT NULL,
+ search_field_id int(11) NOT NULL,
+ facet boolean DEFAULT FALSE COMMENT 'true if a facet field should be generated for this',
+ suggestible boolean DEFAULT FALSE COMMENT 'true if this field can be used to generate suggestions for browse',
+ sort boolean DEFAULT NULL COMMENT 'true/false creates special sort handling, null doesn''t',
+ PRIMARY KEY(search_marc_map_id, search_field_id),
+ FOREIGN KEY(search_marc_map_id) REFERENCES search_marc_map(id) ON DELETE CASCADE ON UPDATE CASCADE,
+ FOREIGN KEY(search_field_id) REFERENCES search_field(id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Table structure for table `serial`
--
DROP TABLE IF EXISTS `serial`;
-CREATE TABLE `serial` (
- `serialid` int(11) NOT NULL auto_increment,
- `biblionumber` varchar(100) NOT NULL default '',
- `subscriptionid` varchar(100) NOT NULL default '',
- `serialseq` varchar(100) NOT NULL default '',
- `status` tinyint(4) NOT NULL default 0,
- `planneddate` date default NULL,
- `notes` text,
- `publisheddate` date default NULL,
- `claimdate` date default NULL,
- claims_count int(11) default 0,
- `routingnotes` text,
+CREATE TABLE `serial` ( -- issues related to subscriptions
+ `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
+ `biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to
+ `subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of
+ `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
+ `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
+ `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
+ `serialseq_z` varchar( 100 ) NULL DEFAULT NULL, -- third part of issue information
+ `status` tinyint(4) NOT NULL default 0, -- status code for this issue (see manual for full descriptions)
+ `planneddate` date default NULL, -- date expected
+ `notes` text, -- notes
+ `publisheddate` date default NULL, -- date published
+ publisheddatetext varchar(100) default NULL, -- date published (descriptive)
+ `claimdate` date default NULL, -- date claimed
+ claims_count int(11) default 0, -- number of claims made related to this issue
+ `routingnotes` text, -- notes from the routing list
PRIMARY KEY (`serialid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions (
`id` varchar(32) NOT NULL,
- `a_session` text NOT NULL,
+ `a_session` mediumtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+--
+-- Table structure for table `sms_providers`
+--
+
+DROP TABLE IF EXISTS sms_providers;
+CREATE TABLE `sms_providers` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` varchar(255) NOT NULL,
+ `domain` varchar(255) NOT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name` (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
--
-- Table structure for table `special_holidays`
--
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, -- proceedure code
+ `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 (locause, issue, return, renew, writeoff, payment, Credit*)
- `other` mediumtext,
- `usercode` varchar(10) default NULL,
+ `other` mediumtext, -- used by SIP
+ `usercode` varchar(10) default NULL, -- unused in Koha
`itemnumber` int(11) default NULL, -- foreign key from the items table, links transaction to a specific item
`itemtype` varchar(10) default NULL, -- foreign key from the itemtypes table, links transaction to a specific item type
`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower
- `associatedborrower` int(11) default NULL,
+ `associatedborrower` int(11) default NULL, -- unused in Koha
`ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code
KEY `timeidx` (`datetime`),
KEY `branch_idx` (`branch`),
KEY `ccode_idx` (`ccode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
---
--- Table structure for table `stopwords`
---
-
-DROP TABLE IF EXISTS `stopwords`;
- CREATE TABLE `stopwords` (
- `word` varchar(255) default NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
--
-- Table structure for table subscription_frequencies
--
--
DROP TABLE IF EXISTS `subscription`;
-CREATE TABLE `subscription` (
- `biblionumber` int(11) NOT NULL default 0,
- `subscriptionid` int(11) NOT NULL auto_increment,
- `librarian` varchar(100) default '',
- `startdate` date default NULL,
- `aqbooksellerid` int(11) default 0,
+CREATE TABLE `subscription` ( -- information related to the subscription
+ `biblionumber` int(11) NOT NULL default 0, -- foreign key for biblio.biblionumber that this subscription is attached to
+ `subscriptionid` int(11) NOT NULL auto_increment, -- unique key for this subscription
+ `librarian` varchar(100) default '', -- the librarian's username from borrowers.userid
+ `startdate` date default NULL, -- start date for this subscription
+ `aqbooksellerid` int(11) default 0, -- foreign key for aqbooksellers.id to link to the vendor
`cost` int(11) default 0,
`aqbudgetid` int(11) default 0,
- `weeklength` int(11) default 0,
- `monthlength` int(11) default 0,
- `numberlength` int(11) default 0,
- `periodicity` integer default null,
+ `weeklength` int(11) default 0, -- subscription length in weeks (will not be filled in if monthlength or numberlength is set)
+ `monthlength` int(11) default 0, -- subscription length in weeks (will not be filled in if weeklength or numberlength is set)
+ `numberlength` int(11) default 0, -- subscription length in weeks (will not be filled in if monthlength or weeklength is set)
+ `periodicity` integer default null, -- frequency type links to subscription_frequencies.id
countissuesperunit INTEGER NOT NULL DEFAULT 1,
- `notes` mediumtext,
- `status` varchar(100) NOT NULL default '',
+ `notes` mediumtext, -- notes
+ `status` varchar(100) NOT NULL default '', -- status of this subscription
`lastvalue1` int(11) default NULL,
`innerloop1` int(11) default 0,
`lastvalue2` int(11) default NULL,
`innerloop2` int(11) default 0,
`lastvalue3` int(11) default NULL,
`innerloop3` int(11) default 0,
- `firstacquidate` date default NULL,
- `manualhistory` tinyint(1) NOT NULL default 0,
- `irregularity` text,
+ `firstacquidate` date default NULL, -- first issue received date
+ `manualhistory` tinyint(1) NOT NULL default 0, -- yes or no to managing the history manually
+ `irregularity` text, -- any irregularities in the subscription
skip_serialseq BOOLEAN NOT NULL DEFAULT 0,
`letter` varchar(20) default NULL,
- `numberpattern` integer default null,
- locale VARCHAR(80) DEFAULT NULL,
+ `numberpattern` integer default null, -- the numbering pattern used links to subscription_numberpatterns.id
+ locale VARCHAR(80) DEFAULT NULL, -- for foreign language subscriptions to display months, seasons, etc correctly
`distributedto` text,
`internalnotes` longtext,
- `callnumber` text,
- `location` varchar(80) NULL default '',
- `branchcode` varchar(10) NOT NULL default '',
+ `callnumber` text, -- default call number
+ `location` varchar(80) NULL default '', -- default shelving location (items.location)
+ `branchcode` varchar(10) NOT NULL default '', -- default branches (items.homebranch)
`lastbranch` varchar(10),
- `serialsadditems` tinyint(1) NOT NULL default '0',
- `staffdisplaycount` VARCHAR(10) NULL,
- `opacdisplaycount` VARCHAR(10) NULL,
- `graceperiod` int(11) NOT NULL default '0',
- `enddate` date default NULL,
- `closed` INT(1) NOT NULL DEFAULT 0,
- `reneweddate` date default NULL,
+ `serialsadditems` tinyint(1) NOT NULL default '0', -- does receiving this serial create an item record
+ `staffdisplaycount` VARCHAR(10) NULL, -- how many issues to show to the staff
+ `opacdisplaycount` VARCHAR(10) NULL, -- how many issues to show to the public
+ `graceperiod` int(11) NOT NULL default '0', -- grace period in days
+ `enddate` date default NULL, -- subscription end date
+ `closed` INT(1) NOT NULL DEFAULT 0, -- yes / no if the subscription is closed
+ `reneweddate` date default NULL, -- date of last renewal for the subscription
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
`publicationyear` smallint(6) default 0,
`place` varchar(255) default NULL, -- publication place of the suggested item
`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 accepting or rejecting the suggestion
`patronreason` text, -- reason for making the suggestion
collectiontitle text default NULL, -- collection name for the suggested item
itemtype VARCHAR(30) default NULL, -- suggested item type
quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased
- currency VARCHAR(3) default NULL, -- suggested currency for the suggested price
+ currency VARCHAR(10) default NULL, -- suggested currency for the suggested price
price DECIMAL(28,6) default NULL, -- suggested price
total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency)
PRIMARY KEY (`suggestionid`),
KEY `suggestedby` (`suggestedby`),
KEY `managedby` (`managedby`),
+ KEY `status` (`STATUS`),
+ KEY `biblionumber` (`biblionumber`),
+ KEY `branchcode` (`branchcode`),
CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS `tags_all`;
CREATE TABLE `tags_all` ( -- all of the tags
`tag_id` int(11) NOT NULL auto_increment, -- unique id and primary key
- `borrowernumber` int(11) NOT NULL, -- the patron who added the tag (borrowers.borrowernumber)
+ `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
`language` int(4) default NULL, -- the language the tag was left in
KEY `tags_borrowers_fk_1` (`borrowernumber`),
KEY `tags_biblionumber_fk_1` (`biblionumber`),
CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
- REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+ REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
PRIMARY KEY (`term`),
KEY `tags_approval_borrowers_fk_1` (`approved_by`),
CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
- REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
+ REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`shelfname` varchar(255) default NULL, -- name of the list
`owner` int default NULL, -- foreign key linking to the borrowers table (using borrowernumber) for the creator of this list (changed from varchar(80) to int)
`category` varchar(1) default NULL, -- type of list (private [1], public [2])
- `sortfield` varchar(16) default NULL, -- the field this list is sorted on
+ `sortfield` varchar(16) default 'title', -- the field this list is sorted on
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the list was last modified
+ `created_on` TIMESTAMP NOT NULL, -- creation time
`allow_add` tinyint(1) default 0, -- permission for adding entries to list
`allow_delete_own` tinyint(1) default 1, -- permission for deleting entries frm list that you added yourself
`allow_delete_other` tinyint(1) default 0, -- permission for deleting entries from list that another person added
PRIMARY KEY (`shelfnumber`),
- CONSTRAINT `virtualshelves_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in VirtualShelves.pm
+ CONSTRAINT `virtualshelves_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in Members.pm
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
KEY `biblionumber` (`biblionumber`),
CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `shelfcontents_ibfk_3` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in VirtualShelves.pm
+ CONSTRAINT `shelfcontents_ibfk_3` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in Members.pm
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`invitekey` varchar(10), -- temporary string used in accepting the invitation to access thist list; not-empty means that the invitation has not been accepted yet
`sharedate` datetime, -- date of invitation or acceptance of invitation
CONSTRAINT `virtualshelfshares_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `virtualshelfshares_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in VirtualShelves.pm
+ CONSTRAINT `virtualshelfshares_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL -- no cascaded delete, please see HandleDelBorrower in Members.pm
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`to_address` mediumtext,
`from_address` mediumtext,
`content_type` text,
- KEY `message_id` (`message_id`),
+ PRIMARY KEY `message_id` (`message_id`),
KEY `borrowernumber` (`borrowernumber`),
KEY `message_transport_type` (`message_transport_type`),
CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
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',
+ `overduerules_id` INT(11) NOT NULL,
PRIMARY KEY (id),
- CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) 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 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS `accountlines`;
CREATE TABLE `accountlines` (
`accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
+ `issue_id` int(11) NULL DEFAULT NULL,
`borrowernumber` int(11) NOT NULL default 0,
`accountno` smallint(6) NOT NULL default 0,
`itemnumber` int(11) default NULL,
`deliveryplace` varchar(10) default NULL, -- basket delivery place
`billingplace` varchar(10) default NULL, -- basket billing place
branch varchar(10) default NULL, -- basket branch
+ is_standing TINYINT(1) NOT NULL DEFAULT 0, -- orders in this basket are standing
PRIMARY KEY (`basketno`),
KEY `booksellerid` (`booksellerid`),
KEY `basketgroupid` (`basketgroupid`),
KEY `contractnumber` (`contractnumber`),
+ KEY `authorisedby` (`authorisedby`),
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,
`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
+ `currency` varchar(10) NOT NULL default '', -- unused in Koha
`booksellerfax` mediumtext, -- vendor fax number
`notes` mediumtext, -- order notes
`bookselleremail` mediumtext, -- vendor email
PRIMARY KEY (`id`),
KEY `listprice` (`listprice`),
KEY `invoiceprice` (`invoiceprice`),
+ KEY `name` (`name`(255)),
CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
`sort2_authcat` varchar(80) default NULL, -- second statistical category for this fund
`budget_owner_id` int(11) default NULL, -- borrowernumber of the person who owns this fund (borrowers.borrowernumber)
`budget_permission` int(1) default '0', -- level of permission for this fund (used only by the owner, only by the library, or anyone)
- PRIMARY KEY (`budget_id`)
+ PRIMARY KEY (`budget_id`),
+ KEY `budget_parent_id` (`budget_parent_id`),
+ KEY `budget_code` (`budget_code`),
+ KEY `budget_branchcode` (`budget_branchcode`),
+ KEY `budget_period_id` (`budget_period_id`),
+ KEY `budget_owner_id` (`budget_owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`authvalue` varchar(30) NOT NULL,
`display` tinyint(1) DEFAULT 1,
PRIMARY KEY (`plan_id`),
+ KEY `budget_period_id` (`budget_period_id`),
CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
`biblionumber` int(11) default NULL, -- links the order to the biblio being ordered (biblio.biblionumber)
`entrydate` date default NULL, -- the date the bib was added to the basket
`quantity` smallint(6) default NULL, -- the quantity ordered
- `currency` varchar(3) default NULL, -- the currency used for the purchase
+ `currency` varchar(10) default NULL, -- the currency used for the purchase
`listprice` decimal(28,6) default NULL, -- the vendor price for this line item
- `totalamount` decimal(28,6) default NULL, -- not used? always NULL
`datereceived` date default NULL, -- the date this order was received
invoiceid int(11) default NULL, -- id of invoice
`freight` decimal(28,6) default NULL, -- shipping costs (not used)
`cancellationreason` text default NULL, -- reason of cancellation
`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)
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
`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'
+ line_item_id varchar(35) default NULL, -- Supplier's article id for Edifact orderline
+ suppliers_reference_number varchar(35) default NULL, -- Suppliers unique edifact quote ref
+ suppliers_reference_qualifier varchar(3) default NULL, -- Type of number above usually 'QLI'
+ `suppliers_report` text COLLATE utf8_unicode_ci, -- reports received from suppliers
PRIMARY KEY (`ordernumber`),
KEY `basketno` (`basketno`),
KEY `biblionumber` (`biblionumber`),
KEY `budget_id` (`budget_id`),
+ KEY `parent_ordernumber` (`parent_ordernumber`),
+ KEY `orderstatus` (`orderstatus`),
CONSTRAINT `aqorders_budget_id_fk` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE,
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_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE
+ CONSTRAINT `aqorders_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `aqorders_currency` FOREIGN KEY (`currency`) REFERENCES `currency` (`currency`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
CONSTRAINT aqorders_transfers_ordernumber_to FOREIGN KEY (ordernumber_to) REFERENCES aqorders (ordernumber) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+--
+-- Table structure for table vendor_edi_accounts
+--
+
+DROP TABLE IF EXISTS vendor_edi_accounts;
+CREATE TABLE IF NOT EXISTS vendor_edi_accounts (
+ id INT(11) NOT NULL auto_increment,
+ description TEXT NOT NULL,
+ host VARCHAR(40),
+ username VARCHAR(40),
+ password VARCHAR(40),
+ last_activity DATE,
+ vendor_id INT(11) REFERENCES aqbooksellers( id ),
+ download_directory TEXT,
+ upload_directory TEXT,
+ san VARCHAR(20),
+ id_code_qualifier VARCHAR(3) default '14',
+ transport VARCHAR(6) default 'FTP',
+ quotes_enabled TINYINT(1) not null default 0,
+ invoices_enabled TINYINT(1) not null default 0,
+ orders_enabled TINYINT(1) not null default 0,
+ responses_enabled TINYINT(1) not null default 0,
+ auto_orders TINYINT(1) not null default 0,
+ shipment_budget INTEGER(11) REFERENCES aqbudgets( budget_id ),
+ plugin varchar(256) NOT NULL DEFAULT "",
+ PRIMARY KEY (id),
+ KEY vendorid (vendor_id),
+ KEY shipmentbudget (shipment_budget),
+ CONSTRAINT vfk_vendor_id FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ),
+ CONSTRAINT vfk_shipment_budget FOREIGN KEY ( shipment_budget ) REFERENCES aqbudgets ( budget_id )
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table edifact_messages
+--
+
+DROP TABLE IF EXISTS edifact_messages;
+CREATE TABLE IF NOT EXISTS edifact_messages (
+ id INT(11) NOT NULL auto_increment,
+ message_type VARCHAR(10) NOT NULL,
+ transfer_date DATE,
+ vendor_id INT(11) REFERENCES aqbooksellers( id ),
+ edi_acct INTEGER REFERENCES vendor_edi_accounts( id ),
+ status TEXT,
+ basketno INT(11) REFERENCES aqbasket( basketno),
+ raw_msg MEDIUMTEXT,
+ filename TEXT,
+ deleted BOOLEAN NOT NULL DEFAULT 0,
+ PRIMARY KEY (id),
+ KEY vendorid ( vendor_id),
+ KEY ediacct (edi_acct),
+ KEY basketno ( basketno),
+ CONSTRAINT emfk_vendor FOREIGN KEY ( vendor_id ) REFERENCES aqbooksellers ( id ) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT emfk_edi_acct FOREIGN KEY ( edi_acct ) REFERENCES vendor_edi_accounts ( id ) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT emfk_basketno FOREIGN KEY ( basketno ) REFERENCES aqbasket ( basketno ) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
--
-- Table structure for table aqinvoices
--
closedate date default NULL, -- invoice close date, NULL means the invoice is open
shipmentcost decimal(28,6) default NULL, -- shipment cost
shipmentcost_budgetid int(11) default NULL, -- foreign key to aqbudgets, link the shipment cost to a budget
+ message_id int(11) default NULL, -- foreign key to edifact invoice message
PRIMARY KEY (invoiceid),
CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id ) REFERENCES edifact_messages ( id ) ON DELETE SET NULL,
CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS authorised_values_branches;
CREATE TABLE authorised_values_branches( -- association table between authorised_values and branches
- av_id INTEGER,
- branchcode VARCHAR(10),
+ av_id INT(11) NOT NULL,
+ branchcode VARCHAR(10) NOT NULL,
FOREIGN KEY (av_id) REFERENCES authorised_values(id) ON DELETE CASCADE,
FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
`guarantorid` int(11) DEFAULT NULL,
`borrowernotes` mediumtext,
`relationship` varchar(100) DEFAULT NULL,
- `ethnicity` varchar(50) DEFAULT NULL,
- `ethnotes` varchar(255) DEFAULT NULL,
`sex` varchar(1) DEFAULT NULL,
`password` varchar(30) DEFAULT NULL,
`flags` int(11) DEFAULT NULL,
KEY `borrowernumber` (`borrowernumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+--
+-- Table structure for table uploaded_files
+--
+
+DROP TABLE IF EXISTS uploaded_files;
+CREATE TABLE uploaded_files (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ hashvalue CHAR(40) NOT NULL,
+ filename TEXT NOT NULL,
+ dir TEXT NOT NULL,
+ filesize int(11),
+ dtcreated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ uploadcategorycode tinytext,
+ owner int(11),
+ public tinyint,
+ permanent tinyint,
+ PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
--
-- Table structure for table linktracker
-- This stores clicks to external links
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,
+ action ENUM('delete_field','update_field','move_field','copy_field','copy_and_replace_field') NOT NULL,
field_number smallint(6) NOT NULL DEFAULT '0',
from_field varchar(3) NOT NULL,
from_subfield varchar(1) DEFAULT NULL,
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+--
+-- Table structure for table 'discharges'
+--
+
+DROP TABLE IF EXISTS discharges;
+CREATE TABLE discharges (
+ discharge_id int(11) NOT NULL AUTO_INCREMENT,
+ borrower int(11) DEFAULT NULL,
+ needed timestamp NULL DEFAULT NULL,
+ validated timestamp NULL DEFAULT NULL,
+ PRIMARY KEY (discharge_id),
+ KEY borrower_discharges_ibfk1 (borrower),
+ CONSTRAINT borrower_discharges_ibfk1 FOREIGN KEY (borrower) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table additional_fields
+-- This table add the ability to add new fields for a record
+--
+
+CREATE TABLE `additional_fields` (
+ `id` int(11) NOT NULL AUTO_INCREMENT, -- primary key identifier
+ `tablename` varchar(255) NOT NULL DEFAULT '', -- tablename of the new field
+ `name` varchar(255) NOT NULL DEFAULT '', -- name of the field
+ `authorised_value_category` varchar(16) NOT NULL DEFAULT '', -- is an authorised value category
+ `marcfield` varchar(16) NOT NULL DEFAULT '', -- contains the marc field to copied into the record
+ `searchable` tinyint(1) NOT NULL DEFAULT '0', -- is the field searchable?
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `fields_uniq` (`tablename`,`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table additional_field_values
+-- This table store values for additional fields
+--
+
+CREATE TABLE `additional_field_values` (
+ `id` int(11) NOT NULL AUTO_INCREMENT, -- primary key identifier
+ `field_id` int(11) NOT NULL, -- foreign key references additional_fields(id)
+ `record_id` int(11) NOT NULL, -- record_id
+ `value` varchar(255) NOT NULL DEFAULT '', -- value for this field
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `field_record` (`field_id`,`record_id`),
+ CONSTRAINT `afv_fk` FOREIGN KEY (`field_id`) REFERENCES `additional_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'localization'
+--
+
+DROP TABLE IF EXISTS localization;
+CREATE TABLE `localization` (
+ localization_id int(11) NOT NULL AUTO_INCREMENT,
+ entity varchar(16) COLLATE utf8_unicode_ci NOT NULL,
+ code varchar(64) COLLATE utf8_unicode_ci NOT NULL,
+ lang varchar(25) COLLATE utf8_unicode_ci NOT NULL, -- could be a foreign key
+ translation text COLLATE utf8_unicode_ci,
+ PRIMARY KEY (localization_id),
+ UNIQUE KEY `entity_code_lang` (`entity`,`code`,`lang`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'audio_alerts'
+--
+
+DROP TABLE IF EXISTS audio_alerts;
+CREATE TABLE audio_alerts (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ precedence smallint(5) unsigned NOT NULL,
+ selector varchar(255) NOT NULL,
+ sound varchar(255) NOT NULL,
+ PRIMARY KEY (id),
+ KEY precedence (precedence)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'edifact_ean'
+--
+
+DROP TABLE IF EXISTS edifact_ean;
+CREATE TABLE IF NOT EXISTS edifact_ean (
+ ee_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ description VARCHAR(128) NULL DEFAULT NULL,
+ branchcode VARCHAR(10) NOT NULL REFERENCES branches (branchcode),
+ ean VARCHAR(15) NOT NULL,
+ id_code_qualifier VARCHAR(3) NOT NULL DEFAULT '14',
+ CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode )
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
/*!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.git / blobdiff