DROP TABLE IF EXISTS `authorised_values`;
CREATE TABLE `authorised_values` ( -- stores values for authorized values categories and values
`id` int(11) NOT NULL auto_increment, -- unique key, used to identify the authorized value
- `category` varchar(10) NOT NULL default '', -- key used to identify the authorized value category
+ `category` varchar(16) NOT NULL default '', -- key used to identify the authorized value category
`authorised_value` varchar(80) NOT NULL default '', -- code use to identify the authorized value
- `lib` varchar(80) default NULL, -- authorized value description as printed in the staff client
- `lib_opac` VARCHAR(80) default NULL, -- authorized value description as printed in the OPAC
+ `lib` varchar(200) default NULL, -- authorized value description as printed in the staff client
+ `lib_opac` varchar(200) default NULL, -- authorized value description as printed in the OPAC
`imageurl` varchar(200) default NULL, -- authorized value URL
PRIMARY KEY (`id`),
KEY `name` (`category`),
--
DROP TABLE IF EXISTS `biblio_framework`;
-CREATE TABLE `biblio_framework` (
- `frameworkcode` varchar(4) NOT NULL default '',
- `frameworktext` varchar(255) NOT NULL default '',
+CREATE TABLE `biblio_framework` ( -- information about MARC frameworks
+ `frameworkcode` varchar(4) NOT NULL default '', -- the unique code assigned to the framework
+ `frameworktext` varchar(255) NOT NULL default '', -- the description/name given to the framework
PRIMARY KEY (`frameworkcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`cn_item` varchar(10) default NULL,
`cn_suffix` varchar(10) default NULL,
`cn_sort` varchar(30) default NULL,
+ `agerestriction` varchar(255) default NULL,
`totalissues` int(10),
`marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
PRIMARY KEY (`biblioitemnumber`),
`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
+ `category_code` VARCHAR(10) 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`)
CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as extended patron attributes linked to patrons/borrowers
`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(64) default NULL, -- custom patron field value
+ `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`),
--
DROP TABLE IF EXISTS `branch_item_rules`;
-CREATE TABLE `branch_item_rules` (
- `branchcode` varchar(10) NOT NULL,
- `itemtype` varchar(10) NOT NULL,
- `holdallowed` tinyint(1) default NULL,
- `returnbranch` varchar(15) default NULL,
+CREATE TABLE `branch_item_rules` ( -- information entered in the circulation and fine rules under 'Holds policy by item type'
+ `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
+ `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`),
CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`)
--
DROP TABLE IF EXISTS `branch_borrower_circ_rules`;
-CREATE TABLE `branch_borrower_circ_rules` (
- `branchcode` VARCHAR(10) NOT NULL,
- `categorycode` VARCHAR(10) NOT NULL,
- `maxissueqty` int(4) default NULL,
+CREATE TABLE `branch_borrower_circ_rules` ( -- includes default circulation rules for patron categories found under "Checkout limit by patron category"
+ `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
PRIMARY KEY (`categorycode`, `branchcode`),
CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
ON DELETE CASCADE ON UPDATE CASCADE,
--
DROP TABLE IF EXISTS `default_borrower_circ_rules`;
-CREATE TABLE `default_borrower_circ_rules` (
- `categorycode` VARCHAR(10) NOT NULL,
+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,
PRIMARY KEY (`categorycode`),
CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
`cn_item` varchar(10) default NULL,
`cn_suffix` varchar(10) default NULL,
`cn_sort` varchar(30) default NULL,
+ `agerestriction` varchar(255) default NULL,
`totalissues` int(10),
`marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
PRIMARY KEY (`biblioitemnumber`),
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
`location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
`permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
- `onloan` date default NULL, -- defines if this item is currently checked out (1 for yes, 0 for no)
+ `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and checkout date for checked out)
`cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
`cn_sort` varchar(30) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting
`ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
--
DROP TABLE IF EXISTS `import_batches`;
-CREATE TABLE `import_batches` (
- `import_batch_id` int(11) NOT NULL auto_increment,
- `matcher_id` int(11) default NULL,
+CREATE TABLE `import_batches` ( -- information about batches of marc records that have been imported
+ `import_batch_id` int(11) NOT NULL auto_increment, -- unique identifier and primary key
+ `matcher_id` int(11) default NULL, -- the id of the match rule used (matchpoints.matcher_id)
`template_id` int(11) default NULL,
`branchcode` varchar(10) default NULL,
- `num_biblios` int(11) NOT NULL default 0,
- `num_items` int(11) NOT NULL default 0,
- `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
- `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new',
- `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', 'webservice') NOT NULL default 'batch',
- `file_name` varchar(100),
- `comments` mediumtext,
+ `num_records` int(11) NOT NULL default 0, -- number of records in the file
+ `num_items` int(11) NOT NULL default 0, -- number of items in the file
+ `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, -- date and time the file was uploaded
+ `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new', -- how to handle duplicate records
+ `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new', -- how to handle records where no match is found
+ `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add', -- what to do with item records
+ `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging', -- the status of the imported file
+ `batch_type` enum('batch', 'z3950', 'webservice') NOT NULL default 'batch', -- where this batch has come from
+ `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio', -- type of record in the batch
+ `file_name` varchar(100), -- the name of the file uploaded
+ `comments` mediumtext, -- any comments added when the file was uploaded
PRIMARY KEY (`import_batch_id`),
KEY `branchcode` (`branchcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Table structure for `import_record_matches`
--
DROP TABLE IF EXISTS `import_record_matches`;
-CREATE TABLE `import_record_matches` (
- `import_record_id` int(11) NOT NULL,
- `candidate_match_id` int(11) NOT NULL,
- `score` int(11) NOT NULL default 0,
+CREATE TABLE `import_record_matches` ( -- matches found when importing a batch of records
+ `import_record_id` int(11) NOT NULL, -- the id given to the imported bib record (import_records.import_record_id)
+ `candidate_match_id` int(11) NOT NULL, -- the biblio the imported record matches (biblio.biblionumber)
+ `score` int(11) NOT NULL default 0, -- the match score
CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `record_score` (`import_record_id`, `score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `import_auths`
+--
+
+DROP TABLE IF EXISTS `import_auths`;
+CREATE TABLE `import_auths` (
+ `import_record_id` int(11) NOT NULL,
+ `matched_authid` int(11) default NULL,
+ `control_number` varchar(25) default NULL,
+ `authorized_heading` varchar(128) default NULL,
+ `original_source` varchar(25) default NULL,
+ CONSTRAINT `import_auths_ibfk_1` FOREIGN KEY (`import_record_id`)
+ REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ KEY `matched_authid` (`matched_authid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
--
-- Table structure for table `import_biblios`
--
--
DROP TABLE IF EXISTS `issuingrules`;
-CREATE TABLE `issuingrules` (
- `categorycode` varchar(10) NOT NULL default '',
- `itemtype` varchar(10) NOT NULL default '',
- `restrictedtype` tinyint(1) default NULL,
- `rentaldiscount` decimal(28,6) default NULL,
+CREATE TABLE `issuingrules` ( -- circulation and fine rules
+ `categorycode` varchar(10) NOT NULL default '', -- patron category this rule is for (categories.categorycode)
+ `itemtype` varchar(10) NOT NULL default '', -- item type this rule is for (itemtypes.itemtype)
+ `restrictedtype` tinyint(1) default NULL, -- not used? always NULL
+ `rentaldiscount` decimal(28,6) default NULL, -- percent discount on the rental charge for this item
`reservecharge` decimal(28,6) default NULL,
- `fine` decimal(28,6) default NULL,
- `finedays` int(11) default NULL,
- `firstremind` int(11) default NULL,
- `chargeperiod` int(11) default NULL,
- `accountsent` int(11) default NULL,
- `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",
- `reservesallowed` smallint(6) NOT NULL default "0",
- `branchcode` varchar(10) NOT NULL default '',
+ `fine` decimal(28,6) default NULL, -- fine amount
+ `finedays` int(11) default NULL, -- suspension in days
+ `firstremind` int(11) default NULL, -- fine grace period
+ `chargeperiod` int(11) default NULL, -- how often the fine amount is charged
+ `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
+ `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
+ `reservesallowed` smallint(6) NOT NULL default "0", -- how many holds are allowed
+ `branchcode` varchar(10) NOT NULL default '', -- the branch this rule is for (branches.branchcode)
+ overduefinescap decimal default NULL, -- the maximum amount of an overdue fine
PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
KEY `categorycode` (`categorycode`),
KEY `itemtype` (`itemtype`)
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time this item was last altered
`location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c)
`permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location
- `onloan` date default NULL, -- defines if this item is currently checked out (1 for yes, 0 for no)
+ `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and checkout date for checked out)
`cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
`cn_sort` varchar(30) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting
`ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8)
DROP TABLE IF EXISTS `letter`;
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,
+ `module` varchar(20) NOT NULL default '', -- Koha module that triggers this notice or slip
+ `code` varchar(20) NOT NULL default '', -- unique identifier for this notice or slip
+ `branchcode` varchar(10) default NULL, -- the branch this notice or slip is used at (branches.branchcode)
+ `name` varchar(100) NOT NULL default '', -- plain text name for this notice or slip
+ `is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no)
`title` varchar(200) NOT NULL default '', -- subject line of the notice
- `content` text, -- body text for the notice
+ `content` text, -- body text for the notice or slip
PRIMARY KEY (`module`,`code`, `branchcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with
`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,
+ `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)
PRIMARY KEY (`reserve_id`),
KEY `old_reserves_borrowernumber` (`borrowernumber`),
KEY `old_reserves_biblionumber` (`biblionumber`),
--
DROP TABLE IF EXISTS `patronimage`;
-CREATE TABLE `patronimage` (
- `cardnumber` varchar(16) NOT NULL,
- `mimetype` varchar(15) NOT NULL,
- `imagefile` mediumblob NOT NULL,
+CREATE TABLE `patronimage` ( -- information related to patron images
+ `cardnumber` varchar(16) NOT NULL, -- the cardnumber of the patron this image is attached to (borrowers.cardnumber)
+ `mimetype` varchar(15) NOT NULL, -- the format of the image (png, jpg, etc)
+ `imagefile` mediumblob NOT NULL, -- the image
PRIMARY KEY (`cardnumber`),
CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`date_created` datetime default NULL, -- date and time this definition was created
`date_modified` datetime default NULL, -- date and time this definition was last modified
`saved_sql` text, -- SQL snippet for us in reports
- `area` int(11) default NULL, -- Koha module this definition is for (1 = Circulation, 2 = Catalog, 3 = Patrons, 4 = Acquistions, 5 = Accounts)
- PRIMARY KEY (`id`)
+ report_area varchar(6) DEFAULT NULL, -- Koha module this definition is for Circulation, Catalog, Patrons, Acquistions, Accounts)
+ PRIMARY KEY (id),
+ KEY dictionary_area_idx (report_area)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
--
DROP TABLE IF EXISTS `saved_sql`;
-CREATE TABLE saved_sql (
- `id` int(11) NOT NULL auto_increment,
- `borrowernumber` int(11) default NULL,
- `date_created` datetime default NULL,
- `last_modified` datetime default NULL,
- `savedsql` text,
+CREATE TABLE saved_sql ( -- saved sql reports
+ `id` int(11) NOT NULL auto_increment, -- unique id and primary key assigned by Koha
+ `borrowernumber` int(11) default NULL, -- the staff member who created this report (borrowers.borrowernumber)
+ `date_created` datetime default NULL, -- the date this report was created
+ `last_modified` datetime default NULL, -- the date this report was last edited
+ `savedsql` text, -- the SQL for this report
`last_run` datetime default NULL,
- `report_name` varchar(255) default NULL,
- `type` varchar(255) default NULL,
- `notes` text,
+ `report_name` varchar(255) default NULL, -- the name of this report
+ `type` varchar(255) default NULL, -- always 1 for tabular
+ `notes` text, -- the notes or description given to this report
`cache_expiry` int NOT NULL default 300,
`public` boolean NOT NULL default FALSE,
+ report_area varchar(6) default NULL,
+ report_group varchar(80) default NULL,
+ report_subgroup varchar(80) default NULL,
PRIMARY KEY (`id`),
+ KEY sql_area_group_idx (report_group, report_subgroup),
KEY boridx (`borrowernumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
DROP TABLE IF EXISTS `search_history`;
-CREATE TABLE IF NOT EXISTS `search_history` (
- `userid` int(11) NOT NULL,
- `sessionid` varchar(32) NOT NULL,
- `query_desc` varchar(255) NOT NULL,
- `query_cgi` text NOT NULL,
- `total` int(11) NOT NULL,
- `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
+CREATE TABLE IF NOT EXISTS `search_history` ( -- patron's opac search history
+ `userid` int(11) NOT NULL, -- the patron who performed the search (borrowers.borrowernumber)
+ `sessionid` varchar(32) NOT NULL, -- a system generated session id
+ `query_desc` varchar(255) NOT NULL, -- the search that was performed
+ `query_cgi` text NOT NULL, -- the string to append to the search url to rerun the search
+ `total` int(11) NOT NULL, -- the total of results found
+ `time` timestamp NOT NULL default CURRENT_TIMESTAMP, -- the date and time the search was run
KEY `userid` (`userid`),
KEY `sessionid` (`sessionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
--
DROP TABLE IF EXISTS `tags_all`;
-CREATE TABLE `tags_all` (
- `tag_id` int(11) NOT NULL auto_increment,
- `borrowernumber` int(11) NOT NULL,
- `biblionumber` int(11) NOT NULL,
- `term` varchar(255) NOT NULL,
- `language` int(4) default NULL,
- `date_created` datetime NOT NULL,
+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)
+ `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
+ `date_created` datetime NOT NULL, -- the date the tag was added
PRIMARY KEY (`tag_id`),
KEY `tags_borrowers_fk_1` (`borrowernumber`),
KEY `tags_biblionumber_fk_1` (`biblionumber`),
--
DROP TABLE IF EXISTS `tags_approval`;
-CREATE TABLE `tags_approval` (
- `term` varchar(255) NOT NULL,
- `approved` int(1) NOT NULL default '0',
- `date_approved` datetime default NULL,
- `approved_by` int(11) default NULL,
- `weight_total` int(9) NOT NULL default '1',
+CREATE TABLE `tags_approval` ( -- approved tags
+ `term` varchar(255) NOT NULL, -- the tag
+ `approved` int(1) NOT NULL default '0', -- whether the tag is approved or not (1=yes, 0=pending, -1=rejected)
+ `date_approved` datetime default NULL, -- the date this tag was approved
+ `approved_by` int(11) default NULL, -- the librarian who approved the tag (borrowers.borrowernumber)
+ `weight_total` int(9) NOT NULL default '1', -- the total number of times this tag was used
PRIMARY KEY (`term`),
KEY `tags_approval_borrowers_fk_1` (`approved_by`),
CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
--
DROP TABLE IF EXISTS `tags_index`;
-CREATE TABLE `tags_index` (
- `term` varchar(255) NOT NULL,
- `biblionumber` int(11) NOT NULL,
- `weight` int(9) NOT NULL default '1',
+CREATE TABLE `tags_index` ( -- a weighted list of all tags and where they are used
+ `term` varchar(255) NOT NULL, -- the tag
+ `biblionumber` int(11) NOT NULL, -- the bib record this tag was used on (biblio.biblionumber)
+ `weight` int(9) NOT NULL default '1', -- the number of times this term was used on this bib record
PRIMARY KEY (`term`,`biblionumber`),
KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
`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',
+ `timeout` int(11) NOT NULL DEFAULT '0', -- number of seconds before Koha stops trying to access this server
`icon` text, -- unused in Koha
`position` enum('primary','secondary','') NOT NULL default 'primary',
`type` enum('zed','opensearch') NOT NULL default 'zed',
DROP TABLE IF EXISTS `accountlines`;
CREATE TABLE `accountlines` (
+ `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
`borrowernumber` int(11) NOT NULL default 0,
`accountno` smallint(6) NOT NULL default 0,
`itemnumber` int(11) default NULL,
`notify_level` int(2) NOT NULL default 0,
`note` text NULL default NULL,
`manager_id` int(11) NULL,
+ PRIMARY KEY (`accountlines_id`),
KEY `acctsborridx` (`borrowernumber`),
KEY `timeidx` (`timestamp`),
KEY `itemnumber` (`itemnumber`),
--
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;
--
DROP TABLE IF EXISTS `aqbasket`;
-CREATE TABLE `aqbasket` (
- `basketno` int(11) NOT NULL auto_increment,
- `basketname` varchar(50) default NULL,
- `note` mediumtext,
- `booksellernote` mediumtext,
- `contractnumber` int(11),
- `creationdate` date default NULL,
- `closedate` date default NULL,
- `booksellerid` int(11) NOT NULL default 1,
- `authorisedby` varchar(10) default NULL,
- `booksellerinvoicenumber` mediumtext,
- `basketgroupid` int(11),
+CREATE TABLE `aqbasket` ( -- stores data about baskets in acquisitions
+ `basketno` int(11) NOT NULL auto_increment, -- primary key, Koha defined number
+ `basketname` varchar(50) default NULL, -- name given to the basket at creation
+ `note` mediumtext, -- the internal note added at basket creation
+ `booksellernote` mediumtext, -- the vendor note added at basket creation
+ `contractnumber` int(11), -- links this basket to the aqcontract table (aqcontract.contractnumber)
+ `creationdate` date default NULL, -- the date the basket was created
+ `closedate` date default NULL, -- the date the basket was closed
+ `booksellerid` int(11) NOT NULL default 1, -- the Koha assigned ID for the vendor (aqbooksellers.id)
+ `authorisedby` varchar(10) default NULL, -- the borrowernumber of the person who created the basket
+ `booksellerinvoicenumber` mediumtext, -- appears to always be NULL
+ `basketgroupid` int(11), -- links this basket to its group (aqbasketgroups.id)
PRIMARY KEY (`basketno`),
KEY `booksellerid` (`booksellerid`),
KEY `basketgroupid` (`basketgroupid`),
--
DROP TABLE IF EXISTS `aqorders`;
-CREATE TABLE `aqorders` ( --information related to the basket line items
+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
`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
+ invoiceid int(11) default NULL, -- id of invoice
`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
`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
+ parent_ordernumber int(11) default NULL, -- ordernumber of parent order line, or same as ordernumber if no parent
PRIMARY KEY (`ordernumber`),
KEY `basketno` (`basketno`),
KEY `biblionumber` (`biblionumber`),
KEY `budget_id` (`budget_id`),
CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE
+ CONSTRAINT `aqorders_ibfk_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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
KEY `ordernumber` (`ordernumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table aqinvoices
+--
+
+DROP TABLE IF EXISTS aqinvoices;
+CREATE TABLE aqinvoices (
+ invoiceid int(11) NOT NULL AUTO_INCREMENT, -- ID of the invoice, primary key
+ invoicenumber mediumtext NOT NULL, -- Name of invoice
+ booksellerid int(11) NOT NULL, -- foreign key to aqbooksellers
+ shipmentdate date default NULL, -- date of shipment
+ billingdate date default NULL, -- date of billing
+ 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
+ PRIMARY KEY (invoiceid),
+ CONSTRAINT aqinvoices_fk_aqbooksellerid FOREIGN KEY (booksellerid) REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ 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;
+
+
--
-- Table structure for table `fieldmapping`
--
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `transport_cost`
+--
+
+DROP TABLE IF EXISTS transport_cost;
+CREATE TABLE transport_cost (
+ frombranch varchar(10) NOT NULL,
+ tobranch varchar(10) NOT NULL,
+ cost decimal(6,2) NOT NULL,
+ disable_transfer tinyint(1) NOT NULL DEFAULT 0,
+ CHECK ( frombranch <> tobranch ), -- a dud check, mysql does not support that
+ PRIMARY KEY (frombranch, tobranch),
+ CONSTRAINT transport_cost_ibfk_1 FOREIGN KEY (frombranch) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT transport_cost_ibfk_2 FOREIGN KEY (tobranch) REFERENCES branches (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
--
-- Table structure for table `biblioimages`
--
--
DROP TABLE IF EXISTS ratings;
-CREATE TABLE ratings (
- borrowernumber int(11) NOT NULL, -- the borrower this rating is for
- biblionumber int(11) NOT NULL, -- the biblio it's for
- rating_value tinyint(1) NOT NULL, -- the rating, from 1-5
+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,
projects / koha.git / blobdiff