`itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
`isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a)
`issn` varchar(9) default NULL, -- ISSN (MARC21 022$a)
+ `ean` varchar(13) default NULL,
`publicationyear` text,
`publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
`volumedate` date default NULL,
`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(10) 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
+ `category_code` VARCHAR(1) NULL DEFAULT NULL,-- defines a category for an attribute_type
+ `class` VARCHAR(255) NOT NULL DEFAULT '',-- defines a class for an attribute_type
PRIMARY KEY (`code`),
KEY `auth_val_cat_idx` (`authorised_value_category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`branchcode` varchar(10) NOT NULL,
`itemtype` varchar(10) NOT NULL,
`holdallowed` tinyint(1) default NULL,
+ `returnbranch` varchar(15) default NULL,
PRIMARY KEY (`itemtype`,`branchcode`),
KEY `branch_item_rules_ibfk_2` (`branchcode`),
CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
`branchip` varchar(15) default NULL, -- the IP address for your library or branch
`branchprinter` varchar(100) default NULL, -- unused in Koha
`branchnotes` mediumtext, -- notes related to your library or branch
- UNIQUE KEY `branchcode` (`branchcode`)
+ opac_info text, -- HTML that displays in OPAC
+ PRIMARY KEY (`branchcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
--
DROP TABLE IF EXISTS `branchtransfers`;
-CREATE TABLE `branchtransfers` (
- `itemnumber` int(11) NOT NULL default 0,
- `datesent` datetime default NULL,
- `frombranch` varchar(10) NOT NULL default '',
- `datearrived` datetime default NULL,
- `tobranch` varchar(10) NOT NULL default '',
- `comments` mediumtext,
+CREATE TABLE `branchtransfers` ( -- information for items that are in transit between branches
+ `itemnumber` int(11) NOT NULL default 0, -- the itemnumber that it is in transit (items.itemnumber)
+ `datesent` datetime default NULL, -- the date the transfer was initialized
+ `frombranch` varchar(10) NOT NULL default '', -- the branch the transfer is coming from
+ `datearrived` datetime default NULL, -- the date the transfer arrived at its destination
+ `tobranch` varchar(10) NOT NULL default '', -- the branch the transfer was going to
+ `comments` mediumtext, -- any comments related to the transfer
KEY `frombranch` (`frombranch`),
KEY `tobranch` (`tobranch`),
KEY `itemnumber` (`itemnumber`),
`branchcode` VARCHAR(10) NOT NULL,
`maxissueqty` int(4) default NULL,
`holdallowed` tinyint(1) default NULL,
+ `returnbranch` varchar(15) default NULL,
PRIMARY KEY (`branchcode`),
CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
ON DELETE CASCADE ON UPDATE CASCADE
CREATE TABLE `default_branch_item_rules` (
`itemtype` varchar(10) NOT NULL,
`holdallowed` tinyint(1) default NULL,
+ `returnbranch` varchar(15) default NULL,
PRIMARY KEY (`itemtype`),
CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
ON DELETE CASCADE ON UPDATE CASCADE
`singleton` enum('singleton') NOT NULL default 'singleton',
`maxissueqty` int(4) default NULL,
`holdallowed` int(1) default NULL,
+ `returnbranch` varchar(15) default NULL,
PRIMARY KEY (`singleton`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
`isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a)
`issn` varchar(9) default NULL, -- ISSN (MARC21 022$a)
+ `ean` varchar(13) default NULL,
`publicationyear` text,
`publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
`volumedate` date default NULL,
`nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new',
`item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add',
`import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
- `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
+ `batch_type` enum('batch', 'z3950', 'webservice') NOT NULL default 'batch',
`file_name` varchar(100),
`comments` mediumtext,
PRIMARY KEY (`import_batch_id`),
CREATE TABLE `issues` ( -- information related to check outs or issues
`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` date default NULL, -- date the item is due (yyyy-mm-dd)
+ `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` date default NULL, -- date the item was returned, will be NULL until moved to old_issues
- `lastreneweddate` date default NULL, -- date the item was last renewed
+ `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,
`renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
- `issuedate` date default NULL, -- date the item was checked out or issued
+ `issuedate` datetime default NULL, -- date the item was checked out or issued
KEY `issuesborridx` (`borrowernumber`),
KEY `bordate` (`borrowernumber`,`timestamp`),
CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
`chargename` varchar(100) default NULL,
`maxissueqty` int(4) default NULL,
`issuelength` int(4) default NULL,
+ `lengthunit` varchar(10) default 'days',
`hardduedate` date default NULL,
`hardduedatecompare` tinyint NOT NULL default "0",
`renewalsallowed` smallint(6) NOT NULL default "0",
KEY `itembibnoidx` (`biblionumber`),
KEY `homebranch` (`homebranch`),
KEY `holdingbranch` (`holdingbranch`),
+ KEY `itemcallnumber` (`itemcallnumber`),
+ KEY `items_location` (`location`),
+ KEY `items_ccode` (`ccode`),
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
CREATE TABLE `letter` ( -- table for all notice templates in Koha
`module` varchar(20) NOT NULL default '', -- Koha module that triggers this notice
`code` varchar(20) NOT NULL default '', -- unique identifier for this notice
+ `branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
`name` varchar(100) NOT NULL default '', -- plain text name for this notice
+ `is_html` tinyint(1) default 0,
`title` varchar(200) NOT NULL default '', -- subject line of the notice
`content` text, -- body text for the notice
- PRIMARY KEY (`module`,`code`)
+ PRIMARY KEY (`module`,`code`, `branchcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`seealso` varchar(1100) default NULL,
`link` varchar(80) default NULL,
`defaultvalue` text default NULL,
+ `maxlength` int(4) NOT NULL DEFAULT '9999',
PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
KEY `kohafield_2` (`kohafield`),
KEY `tab` (`frameworkcode`,`tab`),
ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
+-- Table structure for table `oai_sets`
+--
+
+DROP TABLE IF EXISTS `oai_sets`;
+CREATE TABLE `oai_sets` (
+ `id` int(11) NOT NULL auto_increment,
+ `spec` varchar(80) NOT NULL UNIQUE,
+ `name` varchar(80) NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `oai_sets_descriptions`
+--
+
+DROP TABLE IF EXISTS `oai_sets_descriptions`;
+CREATE TABLE `oai_sets_descriptions` (
+ `set_id` int(11) NOT NULL,
+ `description` varchar(255) NOT NULL,
+ CONSTRAINT `oai_sets_descriptions_ibfk_1` FOREIGN KEY (`set_id`) REFERENCES `oai_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `oai_sets_mappings`
+--
+
+DROP TABLE IF EXISTS `oai_sets_mappings`;
+CREATE TABLE `oai_sets_mappings` (
+ `set_id` int(11) NOT NULL,
+ `marcfield` char(3) NOT NULL,
+ `marcsubfield` char(1) NOT NULL,
+ `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;
+
+--
+-- Table structure for table `oai_sets_biblios`
+--
+
+DROP TABLE IF EXISTS `oai_sets_biblios`;
+CREATE TABLE `oai_sets_biblios` (
+ `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;
+
+--
-- Table structure for table `old_issues`
--
CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned
`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` date default NULL, -- date the item is due (yyyy-mm-dd)
+ `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` date default NULL, -- date the item was returned
- `lastreneweddate` date default NULL, -- date the item was last renewed
+ `returndate` datetime default NULL, -- date the item was returned
+ `lastreneweddate` datetime default NULL, -- date the item was last renewed
`return` varchar(4) default NULL,
`renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
- `issuedate` date default NULL, -- date the item was checked out or issued
+ `issuedate` datetime default NULL, -- date the item was checked out or issued
KEY `old_issuesborridx` (`borrowernumber`),
KEY `old_issuesitemidx` (`itemnumber`),
KEY `old_bordate` (`borrowernumber`,`timestamp`),
--
DROP TABLE IF EXISTS `old_reserves`;
CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled)
+ `reserve_id` int(11) NOT NULL, -- primary key
`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
`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
`expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)
`lowestPriority` tinyint(1) NOT NULL,
+ `suspend` BOOLEAN NOT NULL DEFAULT 0,
+ `suspend_until` DATETIME NULL DEFAULT NULL,
+ PRIMARY KEY (`reserve_id`),
KEY `old_reserves_borrowernumber` (`borrowernumber`),
KEY `old_reserves_biblionumber` (`biblionumber`),
KEY `old_reserves_itemnumber` (`itemnumber`),
CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+-- Table structure for table `pending_offline_operations`
+--
+-- this table is MyISAM, InnoDB tables are growing only and this table is filled/emptied/filled/emptied...
+-- so MyISAM is better in this case
+
+DROP TABLE IF EXISTS `pending_offline_operations`;
+CREATE TABLE `pending_offline_operations` (
+ `operationid` int(11) NOT NULL AUTO_INCREMENT,
+ `userid` varchar(30) NOT NULL,
+ `branchcode` varchar(10) NOT NULL,
+ `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ `action` varchar(10) NOT NULL,
+ `barcode` varchar(20) NOT NULL,
+ `cardnumber` varchar(16) DEFAULT NULL,
+ PRIMARY KEY (`operationid`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+
+
--
-- Table structure for table `printers`
--
DROP TABLE IF EXISTS `reserves`;
CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha
+ `reserve_id` int(11) NOT NULL auto_increment, -- primary key
`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
`waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
`expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)
`lowestPriority` tinyint(1) NOT NULL,
+ `suspend` BOOLEAN NOT NULL DEFAULT 0,
+ `suspend_until` DATETIME NULL DEFAULT NULL,
+ PRIMARY KEY (`reserve_id`),
KEY priorityfoundidx (priority,found),
KEY `borrowernumber` (`borrowernumber`),
KEY `biblionumber` (`biblionumber`),
`review` text, -- the body of the comment
`approved` tinyint(4) default NULL, -- whether this comment has been approved by a librarian (1 for yes, 0 for no)
`datereviewed` datetime default NULL, -- the date the comment was left
- PRIMARY KEY (`reviewid`)
+ PRIMARY KEY (`reviewid`),
+ CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`report_name` varchar(255) default NULL,
`type` varchar(255) default NULL,
`notes` text,
+ `cache_expiry` int NOT NULL default 300,
+ `public` boolean NOT NULL default FALSE,
PRIMARY KEY (`id`),
KEY boridx (`borrowernumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`userid` int(11) NOT NULL,
`sessionid` varchar(32) NOT NULL,
`query_desc` varchar(255) NOT NULL,
- `query_cgi` varchar(255) NOT NULL,
+ `query_cgi` text NOT NULL,
`total` int(11) NOT NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP,
KEY `userid` (`userid`),
CREATE TABLE sessions (
`id` varchar(32) NOT NULL,
`a_session` text NOT NULL,
- UNIQUE KEY id (id)
+ PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
CREATE TABLE `virtualshelves` ( -- information about lists (or virtual shelves)
`shelfnumber` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
`shelfname` varchar(255) default NULL, -- name of the list
- `owner` varchar(80) default NULL, -- foriegn key linking to the borrowers table (using borrowernumber) for the creator of this list
- `category` varchar(1) default NULL, -- type of list (public [2], private [1] or open [3])
+ `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
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the list was last modified
- PRIMARY KEY (`shelfnumber`)
+ `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`biblionumber` int(11) NOT NULL default 0, -- foreign key linking to the biblio table, defines the bib record that has been added to the list
`flags` int(11) default NULL,
`dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date and time this bib record was added to the list
+ `borrowernumber` int, -- borrower number that created this list entry (only the first one is saved: no need for use in/as key)
KEY `shelfnumber` (`shelfnumber`),
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_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
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `virtualshelfshares`
+--
+
+DROP TABLE IF EXISTS `virtualshelfshares`;
+CREATE TABLE `virtualshelfshares` ( -- shared private lists
+ `id` int AUTO_INCREMENT PRIMARY KEY, -- unique key
+ `shelfnumber` int NOT NULL, -- foreign key for virtualshelves
+ `borrowernumber` int, -- borrower that accepted access to this list
+ `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`checked` smallint(6) default NULL, -- whether this target is checked by default (1 for yes, 0 for no)
`rank` int(11) default NULL, -- where this target appears in the list of targets
`syntax` varchar(80) default NULL, -- marc format provided by this target
+ `timeout` int(11) NOT NULL DEFAULT '0',
`icon` text, -- unused in Koha
`position` enum('primary','secondary','') NOT NULL default 'primary',
`type` enum('zed','opensearch') NOT NULL default 'zed',
`is_digest` tinyint(1) NOT NULL default '0',
`letter_module` varchar(20) NOT NULL default '',
`letter_code` varchar(20) NOT NULL default '',
+ `branchcode` varchar(10) NOT NULL default '',
PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
KEY `message_transport_type` (`message_transport_type`),
KEY `letter_module` (`letter_module`,`letter_code`),
CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
+ CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`, `branchcode`) REFERENCES `letter` (`module`, `code`, `branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
--
DROP TABLE IF EXISTS `action_logs`;
-CREATE TABLE `action_logs` (
- `action_id` int(11) NOT NULL auto_increment,
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
- `user` int(11) NOT NULL default 0,
- `module` text,
- `action` text,
- `object` int(11) default NULL,
- `info` text,
+CREATE TABLE `action_logs` ( -- logs of actions taken in Koha (requires that the logs be turned on)
+ `action_id` int(11) NOT NULL auto_increment, -- unique identifier for each action
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time the action took place
+ `user` int(11) NOT NULL default 0, -- the staff member who performed the action (borrowers.borrowernumber)
+ `module` text, -- the module this action was taken against
+ `action` text, -- the action (includes things like DELETED, ADDED, MODIFY, etc)
+ `object` int(11) default NULL, -- the object that the action was taken against (could be a borrowernumber, itemnumber, etc)
+ `info` text, -- information about the action (usually includes SQL statement)
PRIMARY KEY (`action_id`),
KEY (`timestamp`,`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`gstrate` decimal(6,4) default NULL, -- the tax rate the library is charged
`discount` float(6,4) default NULL, -- discount offered on all items ordered from this vendor
`fax` varchar(50) default NULL, -- vendor fax number
+ deliverytime int(11) default NULL, -- vendor delivery time
PRIMARY KEY (`id`),
KEY `listprice` (`listprice`),
KEY `invoiceprice` (`invoiceprice`),
--
DROP TABLE IF EXISTS `aqbudgets`;
-CREATE TABLE `aqbudgets` (
- `budget_id` int(11) NOT NULL auto_increment,
- `budget_parent_id` int(11) default NULL,
- `budget_code` varchar(30) default NULL,
- `budget_name` varchar(80) default NULL,
- `budget_branchcode` varchar(10) default NULL,
- `budget_amount` decimal(28,6) NULL default '0.00',
- `budget_encumb` decimal(28,6) NULL default '0.00',
- `budget_expend` decimal(28,6) NULL default '0.00',
- `budget_notes` mediumtext,
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
- `budget_period_id` int(11) default NULL,
- `sort1_authcat` varchar(80) default NULL,
- `sort2_authcat` varchar(80) default NULL,
- `budget_owner_id` int(11) default NULL,
- `budget_permission` int(1) default '0',
+CREATE TABLE `aqbudgets` ( -- information related to Funds
+ `budget_id` int(11) NOT NULL auto_increment, -- primary key and unique number assigned to each fund by Koha
+ `budget_parent_id` int(11) default NULL, -- if this fund is a child of another this will include the parent id (aqbudgets.budget_id)
+ `budget_code` varchar(30) default NULL, -- code assigned to the fund by the user
+ `budget_name` varchar(80) default NULL, -- name assigned to the fund by the user
+ `budget_branchcode` varchar(10) default NULL, -- branch that this fund belongs to (branches.branchcode)
+ `budget_amount` decimal(28,6) NULL default '0.00', -- total amount for this fund
+ `budget_encumb` decimal(28,6) NULL default '0.00', -- not used in the code
+ `budget_expend` decimal(28,6) NULL default '0.00', -- not used in the code
+ `budget_notes` mediumtext, -- notes related to this fund
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this fund was last touched (created or modified)
+ `budget_period_id` int(11) default NULL, -- id of the budget that this fund belongs to (aqbudgetperiods.budget_period_id)
+ `sort1_authcat` varchar(80) default NULL, -- statistical category for this fund
+ `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table aqbudgetborrowers
+--
+
+DROP TABLE IF EXISTS aqbudgetborrowers;
+CREATE TABLE aqbudgetborrowers (
+ budget_id int(11) NOT NULL,
+ borrowernumber int(11) NOT NULL,
+ PRIMARY KEY (budget_id, borrowernumber),
+ CONSTRAINT aqbudgetborrowers_ibfk_1 FOREIGN KEY (budget_id)
+ REFERENCES aqbudgets (budget_id)
+ ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT aqbudgetborrowers_ibfk_2 FOREIGN KEY (borrowernumber)
+ REFERENCES borrowers (borrowernumber)
+ ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `aqbudgetperiods`
DROP TABLE IF EXISTS `aqbudgetperiods`;
-CREATE TABLE `aqbudgetperiods` (
- `budget_period_id` int(11) NOT NULL auto_increment,
- `budget_period_startdate` date NOT NULL,
- `budget_period_enddate` date NOT NULL,
- `budget_period_active` tinyint(1) default '0',
- `budget_period_description` mediumtext,
- `budget_period_total` decimal(28,6),
- `budget_period_locked` tinyint(1) default NULL,
- `sort1_authcat` varchar(10) default NULL,
- `sort2_authcat` varchar(10) default NULL,
+CREATE TABLE `aqbudgetperiods` ( -- information related to Budgets
+ `budget_period_id` int(11) NOT NULL auto_increment, -- primary key and unique number assigned by Koha
+ `budget_period_startdate` date NOT NULL, -- date when the budget starts
+ `budget_period_enddate` date NOT NULL, -- date when the budget ends
+ `budget_period_active` tinyint(1) default '0', -- whether this budget is active or not (1 for yes, 0 for no)
+ `budget_period_description` mediumtext, -- description assigned to this budget
+ `budget_period_total` decimal(28,6), -- total amount available in this budget
+ `budget_period_locked` tinyint(1) default NULL, -- whether this budget is locked or not (1 for yes, 0 for no)
+ `sort1_authcat` varchar(10) default NULL, -- statistical category for this budget
+ `sort2_authcat` varchar(10) default NULL, -- second statistical category for this budget
PRIMARY KEY (`budget_period_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `aqorders`;
-CREATE TABLE `aqorders` (
- `ordernumber` int(11) NOT NULL auto_increment,
- `biblionumber` int(11) default NULL,
- `entrydate` date default NULL,
- `quantity` smallint(6) default NULL,
- `currency` varchar(3) default NULL,
- `listprice` decimal(28,6) default NULL,
- `totalamount` decimal(28,6) default NULL,
- `datereceived` date default NULL,
- `booksellerinvoicenumber` mediumtext,
- `freight` decimal(28,6) default NULL,
- `unitprice` decimal(28,6) default NULL,
- `quantityreceived` smallint(6) NOT NULL default 0,
- `cancelledby` varchar(10) default NULL,
- `datecancellationprinted` date default NULL,
- `notes` mediumtext,
- `supplierreference` mediumtext,
- `purchaseordernumber` mediumtext,
- `subscription` tinyint(1) default NULL,
- `serialid` varchar(30) default NULL,
- `basketno` int(11) default NULL,
- `biblioitemnumber` int(11) default NULL,
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
- `rrp` decimal(13,2) default NULL,
- `ecost` decimal(13,2) default NULL,
- `gst` decimal(13,2) default NULL,
- `budget_id` int(11) NOT NULL,
- `budgetgroup_id` int(11) NOT NULL,
- `budgetdate` date default NULL,
- `sort1` varchar(80) default NULL,
- `sort2` varchar(80) default NULL,
+CREATE TABLE `aqorders` ( --information related to the basket line items
+ `ordernumber` int(11) NOT NULL auto_increment, -- primary key and unique identifier assigned by Koha to each line
+ `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
+ `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
+ `booksellerinvoicenumber` mediumtext, -- the invoice number this line item was received on
+ `freight` decimal(28,6) default NULL, -- shipping costs (not used)
+ `unitprice` decimal(28,6) default NULL, -- the actual cost entered when receiving this line item
+ `quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
+ `cancelledby` varchar(10) default NULL, -- not used? always NULL
+ `datecancellationprinted` date default NULL, -- the date the line item was deleted
+ `notes` mediumtext, -- notes related to this order line
+ `supplierreference` mediumtext, -- not used? always NULL
+ `purchaseordernumber` mediumtext, -- not used? always NULL
+ `subscription` tinyint(1) default NULL, -- not used? always NULL
+ `serialid` varchar(30) default NULL, -- not used? always NULL
+ `basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
+ `biblioitemnumber` int(11) default NULL, -- links this order line the biblioitems table (biblioitems.biblioitemnumber)
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
+ `rrp` decimal(13,2) default NULL, -- the replacement cost for this line item
+ `ecost` decimal(13,2) default NULL, -- the estimated cost for this line item
+ `gst` decimal(13,2) default NULL, -- the tax rate for this line item
+ `budget_id` int(11) NOT NULL, -- the fund this order goes against (aqbudgets.budget_id)
+ `budgetgroup_id` int(11) NOT NULL, -- not used? always zero
+ `budgetdate` date default NULL, -- not used? always NULL
+ `sort1` varchar(80) default NULL, -- statistical field
+ `sort2` varchar(80) default NULL, -- second statistical field
`sort1_authcat` varchar(10) default NULL,
`sort2_authcat` varchar(10) default NULL,
- `uncertainprice` tinyint(1),
- `claims_count` int(11) default 0,
- `claimed_date` date default NULL,
+ `uncertainprice` tinyint(1), -- was this price uncertain (1 for yes, 0 for no)
+ `claims_count` int(11) default 0, -- count of claim letters generated
+ `claimed_date` date default NULL, -- last date a claim was generated
PRIMARY KEY (`ordernumber`),
KEY `basketno` (`basketno`),
KEY `biblionumber` (`biblionumber`),
--
DROP TABLE IF EXISTS `aqorders_items`;
-CREATE TABLE `aqorders_items` (
- `ordernumber` int(11) NOT NULL,
- `itemnumber` int(11) NOT NULL,
- `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisitions process
+ `ordernumber` int(11) NOT NULL, -- the order this item is attached to (aqorders.ordernumber)
+ `itemnumber` int(11) NOT NULL, -- the item number for this item (items.itemnumber)
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order item was last touched
PRIMARY KEY (`itemnumber`),
KEY `ordernumber` (`ordernumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
--- Table structure for table `bibliocoverimage`
+-- Table structure for table `biblioimages`
--
-DROP TABLE IF EXISTS `bibliocoverimage`;
+DROP TABLE IF EXISTS `biblioimages`;
-CREATE TABLE `bibliocoverimage` (
+CREATE TABLE `biblioimages` (
`imagenumber` int(11) NOT NULL AUTO_INCREMENT,
`biblionumber` int(11) NOT NULL,
`mimetype` varchar(15) NOT NULL,
CONSTRAINT `bibliocoverimage_fk1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `social_data`
+--
+
+DROP TABLE IF EXISTS `social_data`;
+CREATE TABLE IF NOT EXISTS `social_data` (
+ `isbn` VARCHAR(30),
+ `num_critics` INT,
+ `num_critics_pro` INT,
+ `num_quotations` INT,
+ `num_videos` INT,
+ `score_avg` DECIMAL(5,2),
+ `num_scores` INT,
+ PRIMARY KEY (`isbn`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- 'Ratings' table. This tracks the star ratings set by borrowers.
+--
+
+DROP TABLE IF EXISTS ratings;
+CREATE TABLE ratings ( -- information related to the star ratings in the OPAC
+ borrowernumber int(11) NOT NULL, -- the borrowernumber of the patron who left this rating (borrowers.borrowernumber)
+ biblionumber int(11) NOT NULL, -- the biblio this rating is for (biblio.biblionumber)
+ rating_value tinyint(1) NOT NULL, -- the rating, from 1 to 5
+ timestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
+ PRIMARY KEY (borrowernumber,biblionumber),
+ CONSTRAINT ratings_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT ratings_ibfk_2 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `quotes`
+--
+
+DROP TABLE IF EXISTS quotes;
+CREATE TABLE `quotes` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `source` text DEFAULT NULL,
+ `text` mediumtext NOT NULL,
+ `timestamp` datetime NOT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
projects / koha_fer / blobdiff