--
-- Host: localhost Database: koha30test
-- ------------------------------------------------------
--- Server version 4.1.22
+-- Server version 4.1.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
`volume` mediumtext,
`number` mediumtext,
`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)
+ `isbn` mediumtext, -- ISBN (MARC21 020$a)
+ `issn` mediumtext, -- ISSN (MARC21 022$a)
`ean` varchar(13) default NULL,
`publicationyear` text,
`publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
`place` varchar(255) default NULL, -- publication place (MARC21 260$a)
`lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
`marc` longblob, -- full bibliographic MARC record
- `url` varchar(255) default NULL, -- url (MARC21 856$u)
+ `url` text default NULL, -- url (MARC21 856$u)
`cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
`cn_class` varchar(30) default NULL,
`cn_item` varchar(10) default NULL,
`cn_suffix` varchar(10) default NULL,
- `cn_sort` varchar(30) default NULL, -- normalized version of the call number used for sorting
+ `cn_sort` varchar(255) default NULL, -- normalized version of the call number used for sorting
`agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
`totalissues` int(10),
`marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
KEY `bibinoidx` (`biblioitemnumber`),
KEY `bibnoidx` (`biblionumber`),
KEY `itemtype_idx` (`itemtype`),
- KEY `isbn` (`isbn`),
- KEY `issn` (`issn`),
+ KEY `isbn` (`isbn`(255)),
+ KEY `issn` (`issn`(255)),
KEY `publishercode` (`publishercode`),
CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table 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
+ synctype varchar(32) NOT NULL, -- There could potentially be more than one kind of syncing going on, a text string here can be used to tell them apart. E.g.: The Norwegian national patron database uses 'norwegianpatrondb' in this column
+ sync tinyint(1) NOT NULL DEFAULT '0', -- A boolean (1/0) for turning syncing off and on for individual borrowers
+ syncstatus varchar(10) DEFAULT NULL, -- The sync status for any given borrower. Could be text strings like 'new', 'edited', 'synced' etc. The values used here will depend on the actual syncing being done.
+ lastsync varchar(50) DEFAULT NULL, -- Date of the last successfull sync. The date format might be different depending on the service that is being used, so no special date format is being enforced here.
+ hashed_pin varchar(64) DEFAULT NULL, -- Perhaps specific to The Norwegian national patron database, this column holds a hashed PIN code
+ PRIMARY KEY (borrowersyncid),
+ KEY borrowernumber (borrowernumber),
+ CONSTRAINT borrower_sync_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+
--
-- Table structure for table `branchcategories`
--
`branchphone` mediumtext, -- the primary phone for your library or branch
`branchfax` mediumtext, -- the fax number for your library or branch
`branchemail` mediumtext, -- the primary email address for your library or branch
+ `branchreplyto` mediumtext, -- the email to be used as a Reply-To
+ `branchreturnpath` mediumtext, -- the email to be used as Return-Path
`branchurl` mediumtext, -- the URL for your library or branch's website
`issuing` tinyint(4) default NULL, -- unused in Koha
`branchip` varchar(15) default NULL, -- the IP address for your library or branch
`reservefee` decimal(28,6) default NULL, -- cost to place holds
`hidelostitems` tinyint(1) NOT NULL default '0', -- are lost items shown to this category (1 for yes, 0 for no)
`category_type` varchar(1) NOT NULL default 'A', -- type of Koha patron (Adult, Child, Professional, Organizational, Statistical, Staff)
+ `BlockExpiredPatronOpacActions` tinyint(1) NOT NULL default '-1', -- wheither or not a patron of this category can renew books or place holds once their card has expired. 0 means they can, 1 means they cannot, -1 means use syspref BlockExpiredPatronOpacActions
+ `default_privacy` ENUM( 'default', 'never', 'forever' ) NOT NULL DEFAULT 'default', -- Default privacy setting for this patron category
PRIMARY KEY (`categorycode`),
UNIQUE KEY `categorycode` (`categorycode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
colId integer(11) NOT NULL auto_increment,
colTitle varchar(100) NOT NULL DEFAULT '',
colDesc text NOT NULL,
- colBranchcode varchar(4) DEFAULT NULL comment 'branchcode for branch where item should be held.',
+ colBranchcode varchar(10) DEFAULT NULL, -- 'branchcode for branch where item should be held.'
PRIMARY KEY (colId)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
+--
+-- Constraints for table `collections`
+--
+ALTER TABLE `collections`
+ ADD CONSTRAINT `collections_ibfk_1` FOREIGN KEY (`colBranchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE;
+
--
-- Table: collections_tracking
--
DROP TABLE IF EXISTS collections_tracking;
CREATE TABLE collections_tracking (
- ctId integer(11) NOT NULL auto_increment,
+ collections_tracking_id integer(11) NOT NULL auto_increment,
colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId',
itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber',
- PRIMARY KEY (ctId)
+ PRIMARY KEY (collections_tracking_id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
--
CREATE TABLE `currency` (
`currency` varchar(10) NOT NULL default '',
`symbol` varchar(5) default NULL,
+ `isocode` varchar(5) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`rate` float(15,5) default NULL,
`active` tinyint(1) default NULL,
`volume` mediumtext,
`number` mediumtext,
`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)
+ `isbn` mediumtext default NULL, -- ISBN (MARC21 020$a)
+ `issn` mediumtext default NULL, -- ISSN (MARC21 022$a)
`ean` varchar(13) default NULL,
`publicationyear` text,
`publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
`place` varchar(255) default NULL, -- publication place (MARC21 260$a)
`lccn` varchar(25) default NULL, -- library of congress control number (MARC21 010$a)
`marc` longblob, -- full bibliographic MARC record
- `url` varchar(255) default NULL, -- url (MARC21 856$u)
+ `url` text default NULL, -- url (MARC21 856$u)
`cn_source` varchar(10) default NULL, -- classification source (MARC21 942$2)
`cn_class` varchar(30) default NULL,
`cn_item` varchar(10) default NULL,
`cn_suffix` varchar(10) default NULL,
- `cn_sort` varchar(30) default NULL, -- normalized version of the call number used for sorting
+ `cn_sort` varchar(255) default NULL, -- normalized version of the call number used for sorting
`agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
`totalissues` int(10),
`marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
KEY `bibinoidx` (`biblioitemnumber`),
KEY `bibnoidx` (`biblionumber`),
KEY `itemtype_idx` (`itemtype`),
- KEY `isbn` (`isbn`),
+ KEY `isbn` (`isbn`(255)),
KEY `publishercode` (`publishercode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7)
`damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4)
`itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
+ `itemlost_on` datetime DEFAULT NULL, -- the date and time an item was last marked as lost, NULL if not lost
`withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
+ `withdrawn_on` datetime DEFAULT NULL, -- the date and time an item was last marked as withdrawn, NULL if not withdrawn
`itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o)
`coded_location_qualifier` varchar(10) default NULL, -- coded location qualifier(MARC21 952$f)
`issues` smallint(6) default NULL, -- number of times this item has been checked out
`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 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
+ `cn_sort` varchar(255) 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)
`materials` text default NULL, -- materials specified (MARC21 952$3)
`uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
`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)
- `marc` longblob, -- unused in Koha
PRIMARY KEY (`itemnumber`),
KEY `delitembarcodeidx` (`barcode`),
KEY `delitemstocknumberidx` (`stocknumber`),
`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
+ `auto_renew` BOOLEAN default FALSE, -- automatic renewal
`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
KEY `issuesborridx` (`borrowernumber`),
KEY `itemnumber_idx` (`itemnumber`),
KEY `branchcode_idx` (`branchcode`),
`reservecharge` decimal(28,6) default NULL,
`fine` decimal(28,6) default NULL, -- fine amount
`finedays` int(11) default NULL, -- suspension in days
+ `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
`accountsent` int(11) default NULL, -- not used? always NULL
`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
+ `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
`notforloan` tinyint(1) NOT NULL default 0, -- authorized value defining why this item is not for loan (MARC21 952$7)
`damaged` tinyint(1) NOT NULL default 0, -- authorized value defining this item as damaged (MARC21 952$4)
`itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
+ `itemlost_on` datetime DEFAULT NULL, -- the date and time an item was last marked as lost, NULL if not lost
`withdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
+ `withdrawn_on` datetime DEFAULT NULL, -- the date and time an item was last marked as withdrawn, NULL if not withdrawn
`itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o)
`coded_location_qualifier` varchar(10) default NULL, -- coded location qualifier(MARC21 952$f)
`issues` smallint(6) default NULL, -- number of times this item has been checked out/issued
`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 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
+ `cn_sort` varchar(255) 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)
`materials` text default NULL, -- materials specified (MARC21 952$3)
`uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u)
summary text, -- information from the summary field, may include HTML
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
PRIMARY KEY (`itemtype`),
UNIQUE KEY `itemtype` (`itemtype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`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 or slip
- PRIMARY KEY (`module`,`code`, `branchcode`)
+ `message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice
+ PRIMARY KEY (`module`,`code`, `branchcode`, `message_transport_type`),
+ CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`)
+ REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`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
+ `auto_renew` BOOLEAN default FALSE, -- automatic renewal
`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
KEY `old_issuesborridx` (`borrowernumber`),
KEY `old_issuesitemidx` (`itemnumber`),
KEY `branchcode_idx` (`branchcode`),
DROP TABLE IF EXISTS `opac_news`;
CREATE TABLE `opac_news` ( -- data from the news tool
`idnew` int(10) unsigned NOT NULL auto_increment, -- unique identifier for the news article
+ `branchcode` varchar(10) default NULL, -- branch code users to create branch specific news, NULL is every branch.
`title` varchar(250) NOT NULL default '', -- title of the news article
`new` text NOT NULL, -- the body of your news article
`lang` varchar(25) NOT NULL default '', -- location for the article (koha is the staff client, slip is the circulation receipt and language codes are for the opac)
`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
- PRIMARY KEY (`idnew`)
+ PRIMARY KEY (`idnew`),
+ CONSTRAINT opac_news_branchcode_ibfk FOREIGN KEY (branchcode) REFERENCES branches (branchcode)
+ ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
---
--- Table structure for table `roadtype`
---
-
-DROP TABLE IF EXISTS `roadtype`;
-CREATE TABLE `roadtype` ( -- road types defined in administration and used in patron management
- `roadtypeid` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha for each road type
- `road_type` varchar(100) NOT NULL default '', -- text for road type
- PRIMARY KEY (`roadtypeid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
--
-- Table structure for table `saved_sql`
--
`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, -- the name of this report
+ `report_name` varchar(255) NOT NULL default '', -- 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,
`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
+ `type` varchar(16) NOT NULL DEFAULT 'biblio', -- search type, must be 'biblio' or 'authority'
`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`),
`notes` text,
`publisheddate` date default NULL,
`claimdate` date default NULL,
+ claims_count int(11) default 0,
`routingnotes` text,
PRIMARY KEY (`serialid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `suggestions` ( -- purchase suggestions
`suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha
`suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table
- `suggesteddate` date NOT NULL default 0, -- date the suggestion was submitted
+ `suggesteddate` date NOT NULL, -- date the suggestion was submitted
`managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table
`manageddate` date default NULL, -- date the suggestion was updated
acceptedby INT(11) default NULL, -- borrowernumber for the librarian who accepted the suggestion, foreign key linking to the borrowers table
branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table
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
- price DECIMAL(28,6) default NULL, -- suggested price
- total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency)
+ quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased
+ currency VARCHAR(3) 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`)
DROP TABLE IF EXISTS `z3950servers`;
CREATE TABLE `z3950servers` ( -- connection information for the Z39.50 targets used in cataloging
+ `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
`host` varchar(255) default NULL, -- target's host name
`port` int(11) default NULL, -- port number used to connect to target
`db` varchar(255) default NULL, -- target's database name
`userid` varchar(255) default NULL, -- username needed to log in to target
`password` varchar(255) default NULL, -- password needed to log in to target
- `name` mediumtext, -- name given to the target by the library
- `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
+ `servername` mediumtext NOT NULL, -- name given to the target by the library
`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', -- 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',
+ `servertype` enum('zed','sru') NOT NULL default 'zed', -- zed means z39.50 server
`encoding` text default NULL, -- characters encoding provided by this target
- `description` text NOT NULL, -- unused in Koha
- `recordtype` varchar(45) NOT NULL default 'biblio', -- server contains bibliographic or authority records
+ `recordtype` enum('authority','biblio') NOT NULL default 'biblio', -- server contains bibliographic or authority records
+ `sru_options` varchar(255) default NULL, -- options like sru=get, sru_version=1.1; will be passed to the server via ZOOM
+ `sru_fields` mediumtext default NULL, -- contains the mapping between the Z3950 search fields and the specific SRU server indexes
+ `add_xslt` mediumtext default NULL, -- zero or more paths to XSLT files to be processed on the search results
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `serialitems`;
CREATE TABLE `serialitems` (
- `itemnumber` int(11) NOT NULL,
- `serialid` int(11) NOT NULL,
- UNIQUE KEY `serialitemsidx` (`itemnumber`),
- KEY `serialitems_sfk_1` (`serialid`),
- CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
+ `itemnumber` int(11) NOT NULL,
+ `serialid` int(11) NOT NULL,
+ UNIQUE KEY `serialitemsidx` (`itemnumber`),
+ KEY `serialitems_sfk_1` (`serialid`),
+ CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
PRIMARY KEY (`message_transport_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `overduerules_transport_types`
+--
+
+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',
+ PRIMARY KEY (id),
+ CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) 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;
+
--
-- Table structure for table `message_attributes`
--
`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`)
+ KEY `timestamp_idx` (`timestamp`),
+ KEY `user_idx` (`user`),
+ KEY `module_idx` (`module`(255)),
+ KEY `action_idx` (`action`(255)),
+ KEY `object_idx` (`object`),
+ KEY `info_idx` (`info`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`notes` mediumtext, -- order notes
`bookselleremail` mediumtext, -- vendor email
`booksellerurl` mediumtext, -- unused in Koha
- `contact` varchar(100) default NULL, -- name of contact at vendor
`postal` mediumtext, -- vendor postal address (all lines)
`url` varchar(255) default NULL, -- vendor web address
- `contpos` varchar(100) default NULL, -- contact person's position
- `contphone` varchar(100) default NULL, -- contact's phone number
- `contfax` varchar(100) default NULL, -- contact's fax number
- `contaltphone` varchar(100) default NULL, -- contact's alternate phone number
- `contemail` varchar(100) default NULL, -- contact's email address
- `contnotes` mediumtext, -- notes related to the contact
`active` tinyint(4) default NULL, -- is this vendor active (1 for yes, 0 for no)
`listprice` varchar(10) default NULL, -- currency code for list prices
`invoiceprice` varchar(10) default NULL, -- currency code for invoice prices
CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table 'aqcontacts'
+--
+
+DROP TABLE IF EXISTS aqcontacts;
+CREATE TABLE aqcontacts (
+ id int(11) NOT NULL auto_increment, -- primary key and unique number assigned by Koha
+ name varchar(100) default NULL, -- name of contact at vendor
+ position varchar(100) default NULL, -- contact person's position
+ phone varchar(100) default NULL, -- contact's phone number
+ altphone varchar(100) default NULL, -- contact's alternate phone number
+ fax varchar(100) default NULL, -- contact's fax number
+ email varchar(100) default NULL, -- contact's email address
+ notes mediumtext, -- notes related to the contact
+ claimacquisition BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive acquisitions claims
+ claimissues BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive serial claims
+ acqprimary BOOLEAN NOT NULL DEFAULT 0, -- is this the primary contact for acquisitions messages
+ serialsprimary BOOLEAN NOT NULL DEFAULT 0, -- is this the primary contact for serials messages
+ booksellerid int(11) not NULL,
+ PRIMARY KEY (id),
+ CONSTRAINT booksellerid_aqcontacts_fk FOREIGN KEY (booksellerid)
+ REFERENCES aqbooksellers (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+
--
-- Table structure for table 'aqcontract'
--
REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
---
--- Table structure for table `aqorderdelivery`
---
-
-DROP TABLE IF EXISTS `aqorderdelivery`;
-CREATE TABLE `aqorderdelivery` (
- `ordernumber` date default NULL,
- `deliverynumber` smallint(6) NOT NULL default 0,
- `deliverydate` varchar(18) default NULL,
- `qtydelivered` smallint(6) default NULL,
- `deliverycomments` mediumtext
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
--
-- Table structure for table `aqorders`
--
`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
+ `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)
CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table `misc_files`
+--
+
+CREATE TABLE IF NOT EXISTS `misc_files` ( -- miscellaneous files attached to records from various tables
+ `file_id` int(11) NOT NULL AUTO_INCREMENT, -- unique id for the file record
+ `table_tag` varchar(255) NOT NULL, -- usually table name, or arbitrary unique tag
+ `record_id` int(11) NOT NULL, -- record id from the table this file is associated to
+ `file_name` varchar(255) NOT NULL, -- file name
+ `file_type` varchar(255) NOT NULL, -- MIME type of the file
+ `file_description` varchar(255) DEFAULT NULL, -- description given to the file
+ `file_content` longblob NOT NULL, -- file content
+ `date_uploaded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- date and time the file was added
+ PRIMARY KEY (`file_id`),
+ KEY `table_tag` (`table_tag`),
+ KEY `record_id` (`record_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `columns_settings`
+--
+
+CREATE TABLE IF NOT EXISTS columns_settings (
+ module varchar(255) NOT NULL,
+ page varchar(255) NOT NULL,
+ tablename varchar(255) NOT NULL,
+ columnname varchar(255) NOT NULL,
+ cannot_be_toggled int(1) NOT NULL DEFAULT 0,
+ is_hidden int(1) NOT NULL DEFAULT 0,
+ PRIMARY KEY(module, page, tablename, columnname)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table 'items_search_fields'
+--
+
+DROP TABLE IF EXISTS items_search_fields;
+CREATE TABLE items_search_fields (
+ name VARCHAR(255) NOT NULL,
+ label VARCHAR(255) NOT NULL,
+ tagfield CHAR(3) NOT NULL,
+ tagsubfield CHAR(1) NULL DEFAULT NULL,
+ authorised_values_category VARCHAR(16) NULL DEFAULT NULL,
+ PRIMARY KEY(name),
+ CONSTRAINT items_search_fields_authorised_values_category
+ FOREIGN KEY (authorised_values_category) REFERENCES authorised_values (category)
+ ON DELETE SET NULL ON UPDATE CASCADE
+) 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 */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-
projects / koha.git / blobdiff