--
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`),
--
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 `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;
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;
--
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_fer / blobdiff