CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table `authorised_value_categories`
+--
+
+DROP TABLE IF EXISTS `authorised_value_categories`;
+CREATE TABLE `authorised_value_categories` (
+ `category_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
+ PRIMARY KEY (`category_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
--
-- Table structure for table `authorised_values`
--
PRIMARY KEY (`id`),
KEY `name` (`category`),
KEY `lib` (`lib`),
- KEY `auth_value_idx` (`authorised_value`)
+ KEY `auth_value_idx` (`authorised_value`),
+ CONSTRAINT `authorised_values_authorised_values_category` FOREIGN KEY (`category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
`isbn` mediumtext, -- ISBN (MARC21 020$a)
`issn` mediumtext, -- ISSN (MARC21 022$a)
- `ean` varchar(13) default NULL,
+ `ean` mediumtext default NULL,
`publicationyear` text,
`publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
`volumedate` date default NULL,
`size` varchar(255) default NULL, -- material size (MARC21 300$c)
`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` 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_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, -- full bibliographic MARC record in MARCXML
PRIMARY KEY (`biblioitemnumber`),
KEY `bibinoidx` (`biblioitemnumber`),
KEY `bibnoidx` (`biblionumber`),
KEY `itemtype_idx` (`itemtype`),
KEY `isbn` (`isbn`(255)),
KEY `issn` (`issn`(255)),
+ KEY `ean` (`ean`(255)),
KEY `publishercode` (`publishercode`),
+ KEY `timestamp` (`timestamp`),
CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
`repeatable` tinyint(1) NOT NULL default 0, -- defines whether one patron/borrower can have multiple values for this custom field (1 for yes, 0 for no)
`unique_id` tinyint(1) NOT NULL default 0, -- defines if this value needs to be unique (1 for yes, 0 for no)
`opac_display` tinyint(1) NOT NULL default 0, -- defines if this field is visible to patrons on their account in the OPAC (1 for yes, 0 for no)
+ `opac_editable` tinyint(1) NOT NULL default 0, -- defines if this field is editable by patrons on their account in the OPAC (1 for yes, 0 for no)
`staff_searchable` tinyint(1) NOT NULL default 0, -- defines if this field is searchable via the patron search in the staff client (1 for yes, 0 for no)
`authorised_value_category` varchar(32) default NULL, -- foreign key from authorised_values that links this custom field to an authorized value category
`display_checkout` tinyint(1) NOT NULL default 0,-- defines if this field displays in checkout screens
`branchprinter` varchar(100) default NULL, -- unused in Koha
`branchnotes` mediumtext, -- notes related to your library or branch
opac_info text, -- HTML that displays in OPAC
+ `geolocation` VARCHAR(255) default NULL, -- geolocation of your library
+ `marcorgcode` VARCHAR(16) default NULL, -- MARC Organization Code, see http://www.loc.gov/marc/organizations/orgshome.html, when empty defaults to syspref MARCOrgCode
PRIMARY KEY (`branchcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
`itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c)
`isbn` mediumtext default NULL, -- ISBN (MARC21 020$a)
`issn` mediumtext default NULL, -- ISSN (MARC21 022$a)
- `ean` varchar(13) default NULL,
+ `ean` mediumtext default NULL,
`publicationyear` text,
`publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b)
`volumedate` date default NULL,
`size` varchar(255) default NULL, -- material size (MARC21 300$c)
`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` 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_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, -- full bibliographic MARC record in MARCXML
PRIMARY KEY (`biblioitemnumber`),
KEY `bibinoidx` (`biblioitemnumber`),
KEY `bibnoidx` (`biblionumber`),
KEY `itemtype_idx` (`itemtype`),
KEY `isbn` (`isbn`(255)),
- KEY `publishercode` (`publishercode`)
+ KEY `ean` (`ean`(255)),
+ KEY `publishercode` (`publishercode`),
+ KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
`dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD)
`dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD)
+ `date_renewed` date default NULL, -- date the patron/borrower's card was last renewed
`gonenoaddress` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address
`lost` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card
- `debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYY-MM-DD)
+ `debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYYY-MM-DD)
`debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of patron
- `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name
- `contactfirstname` text, -- used for children to include first name of guarentor
- `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor
- `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations
+ `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarantor or organization name
+ `contactfirstname` text, -- used for children to include first name of guarantor
+ `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarantor
+ `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarantors or organizations
`borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client
- `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor
+ `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor
`sex` varchar(1) default NULL, -- patron/borrower's gender
`password` varchar(60) default NULL, -- patron/borrower's encrypted password
`flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
`altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
`altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
- `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
+ `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on)
`sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history KEY `borrowernumber` (`borrowernumber`),
`privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
`checkprevcheckout` varchar(7) NOT NULL default 'inherit', -- produce a warning for this patron if this item has previously been checked out to this patron if 'yes', not if 'no', defer to category setting if 'inherit'.
`updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- time of last change could be useful for synchronization with external systems (among others)
+ `lastseen` datetime default NULL, -- last time a patron has been seen (connected at the OPAC or staff interface)
+ `lang` varchar(25) NOT NULL default 'default', -- lang to use to send notices to this patron
+ `login_attempts` int(4) default 0, -- number of failed login attemps
+ `overdrive_auth_token` text default NULL, -- persist OverDrive auth token
KEY borrowernumber (borrowernumber),
KEY `cardnumber` (`cardnumber`),
KEY `sms_provider_id` (`sms_provider_id`)
`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 item is checked out (NULL for not checked out, and checkout date for checked out)
+ `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and due date for checked out)
`cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
`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)
KEY `delitembibnoidx` (`biblionumber`),
KEY `delhomebranch` (`homebranch`),
KEY `delholdingbranch` (`holdingbranch`),
- KEY `itype_idx` (`itype`)
+ KEY `itype_idx` (`itype`),
+ KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`subfield_separator` varchar(2) NOT NULL,
`encoding` varchar(255) NOT NULL,
`type` varchar(255) DEFAULT 'marc',
+ `used_for` varchar(255) DEFAULT 'export_records',
PRIMARY KEY (`export_format_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Used for CSV export';
`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.
`auto_renew` BOOLEAN default FALSE, -- automatic renewal
+ `no_auto_renewal_after` int(4) default NULL, -- no auto renewal allowed after X days or hours after the issue date
+ `no_auto_renewal_after_hard_limit` date default NULL, -- no auto renewal allowed after a given date
`reservesallowed` smallint(6) NOT NULL default "0", -- how many holds are allowed
`holds_per_record` SMALLINT(6) NOT NULL DEFAULT 1, -- How many holds a patron can have on a given bib
`branchcode` varchar(10) NOT NULL default '', -- the branch this rule is for (branches.branchcode)
cap_fine_to_replacement_price BOOLEAN NOT NULL DEFAULT '0', -- cap the fine based on item's replacement price
onshelfholds tinyint(1) NOT NULL default 0, -- allow holds for items that are on shelf
opacitemholds char(1) NOT NULL default 'N', -- allow opac users to place specific items on hold
+ article_requests enum('no','yes','bib_only','item_only') NOT NULL DEFAULT 'no', -- allow article requests to be placed,
PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`),
KEY `categorycode` (`categorycode`),
KEY `itemtype` (`itemtype`)
--
DROP TABLE IF EXISTS `items`;
-CREATE TABLE `items` ( -- holdings/item information
+CREATE TABLE `items` ( -- holdings/item information
`itemnumber` int(11) NOT NULL auto_increment, -- primary key and unique identifier added by Koha
`biblionumber` int(11) NOT NULL default 0, -- foreign key from biblio table used to link this item to the right bib record
`biblioitemnumber` int(11) NOT NULL default 0, -- foreign key from the biblioitems table to link to item to additional information
`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 item is checked out (NULL for not checked out, and checkout date for checked out)
+ `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and due date for checked out)
`cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2)
`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)
KEY `items_location` (`location`),
KEY `items_ccode` (`ccode`),
KEY `itype_idx` (`itype`),
+ KEY `timestamp` (`timestamp`),
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,
DROP TABLE IF EXISTS `branchtransfers`;
CREATE TABLE `branchtransfers` ( -- information for items that are in transit between branches
+ `branchtransfer_id` int(12) NOT NULL auto_increment, -- primary key
`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
+ PRIMARY KEY (`branchtransfer_id`),
KEY `frombranch` (`frombranch`),
KEY `tobranch` (`tobranch`),
KEY `itemnumber` (`itemnumber`),
`mandatory` tinyint(4) NOT NULL default 0,
`kohafield` varchar(40) default NULL,
`tab` tinyint(1) default NULL,
- `authorised_value` varchar(20) default NULL,
+ `authorised_value` varchar(32) default NULL,
`authtypecode` varchar(20) default NULL,
`value_builder` varchar(80) default NULL,
`isurl` tinyint(1) default NULL,
PRIMARY KEY (`frameworkcode`,`tagfield`,`tagsubfield`),
KEY `kohafield_2` (`kohafield`),
KEY `tab` (`frameworkcode`,`tab`),
- KEY `kohafield` (`frameworkcode`,`kohafield`)
+ KEY `kohafield` (`frameworkcode`,`kohafield`),
+ CONSTRAINT `marc_subfield_structure_ibfk_1` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
--
DROP TABLE IF EXISTS `need_merge_authorities`;
-CREATE TABLE `need_merge_authorities` ( -- keeping track of authority records still to be merged by merge_authority cron job (used only if pref dontmerge is ON)
+CREATE TABLE `need_merge_authorities` ( -- keeping track of authority records still to be merged by merge_authority cron job
`id` int NOT NULL auto_increment PRIMARY KEY, -- unique id
- `authid` bigint NOT NULL, -- reference to authority record
- `done` tinyint DEFAULT 0 -- indication whether merge has been executed (0=not done, 1= done, 2= in progress)
+ `authid` bigint NOT NULL, -- reference to original authority record
+ `authid_new` bigint, -- reference to optional new authority record
+ `reportxml` text, -- xml showing original reporting tag
+ `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- date and time last modified
+ `done` tinyint DEFAULT 0 -- indication whether merge has been executed (0=not done, 1=done, 2=in progress)
+-- Note: authid and authid_new should NOT be FOREIGN keys !
+-- authid may have been deleted; authid_new may be zero
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL COMMENT 'the name of the field as it will be stored in the search engine',
`label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display',
- `type` ENUM('string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine',
+ `type` ENUM('', 'string', 'date', 'number', 'boolean', 'sum') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine',
PRIMARY KEY (`id`),
UNIQUE KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
`categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table, includes the code of the patron category
`dateenrolled` date default NULL, -- date the patron was added to Koha (YYYY-MM-DD)
`dateexpiry` date default NULL, -- date the patron/borrower's card is set to expire (YYYY-MM-DD)
+ `date_renewed` date default NULL, -- date the patron/borrower's card was last renewed
`gonenoaddress` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having an unconfirmed address
`lost` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as having lost their card
- `debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYY-MM-DD)
+ `debarred` date default NULL, -- until this date the patron can only check-in (no loans, no holds, etc.), is a fine based on days instead of money (YYYY-MM-DD)
`debarredcomment` VARCHAR(255) DEFAULT NULL, -- comment on the stop of the patron
- `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarentor or organization name
- `contactfirstname` text, -- used for children to include first name of guarentor
- `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarentor
- `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarentors or organizations
+ `contactname` mediumtext, -- used for children and profesionals to include surname or last name of guarantor or organization name
+ `contactfirstname` text, -- used for children to include first name of guarantor
+ `contacttitle` text, -- used for children to include title (Mr., Mrs., etc) of guarantor
+ `guarantorid` int(11) default NULL, -- borrowernumber used for children or professionals to link them to guarantors or organizations
`borrowernotes` mediumtext, -- a note on the patron/borrower's account that is only visible in the staff client
- `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarentor
+ `relationship` varchar(100) default NULL, -- used for children to include the relationship to their guarantor
`sex` varchar(1) default NULL, -- patron/borrower's gender
- `password` varchar(60) default NULL, -- patron/borrower's encrypted password
+ `password` varchar(60) default NULL, -- patron/borrower's Bcrypt encrypted password
`flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
`userid` varchar(75) default NULL, -- patron/borrower's opac and/or staff client log in
`opacnote` mediumtext, -- a note on the patron/borrower's account that is visible in the OPAC and staff client
`altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
`altcontactcountry` text default NULL, -- the country for the alternate contact for the patron/borrower
`altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
- `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
+ `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on)
`sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
`privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
`privacy_guarantor_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
`checkprevcheckout` varchar(7) NOT NULL default 'inherit', -- produce a warning for this patron if this item has previously been checked out to this patron if 'yes', not if 'no', defer to category setting if 'inherit'.
`updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- time of last change could be useful for synchronization with external systems (among others)
+ `lastseen` datetime default NULL, -- last time a patron has been seen (connected at the OPAC or staff interface)
+ `lang` varchar(25) NOT NULL default 'default', -- lang to use to send notices to this patron
+ `login_attempts` int(4) default 0, -- number of failed login attemps
+ `overdrive_auth_token` text default NULL, -- persist OverDrive auth token
UNIQUE KEY `cardnumber` (`cardnumber`),
PRIMARY KEY `borrowernumber` (`borrowernumber`),
KEY `categorycode` (`categorycode`),
DROP TABLE IF EXISTS `borrower_attributes`;
CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as extended patron attributes linked to patrons/borrowers
+ `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, -- Row id field
`borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines which patron/borrower has this attribute
`code` varchar(10) NOT NULL, -- foreign key from the borrower_attribute_types table, defines which custom field this value was entered for
`attribute` varchar(255) default NULL, -- custom patron field value
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
`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
`auto_renew` BOOLEAN default FALSE, -- automatic renewal
+ `auto_renew_error` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, -- automatic renewal error
`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
+ `note` mediumtext default NULL, -- issue note text
+ `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)
PRIMARY KEY (`issue_id`),
UNIQUE KEY `itemnumber` (`itemnumber`),
KEY `issuesborridx` (`borrowernumber`),
`branchcode` varchar(10) default NULL, -- foreign key, linking to the branches table for the location the item was checked out
`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
`auto_renew` BOOLEAN default FALSE, -- automatic renewal
+ `auto_renew_error` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, -- automatic renewal error
`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
+ `note` mediumtext default NULL, -- issue note text
+ `notedate` datetime default NULL, -- datetime of issue note (yyyy-mm-dd hh:mm::ss)
PRIMARY KEY (`issue_id`),
KEY `old_issuesborridx` (`borrowernumber`),
KEY `old_issuesitemidx` (`itemnumber`),
`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
+ `content` 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
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`)
ON DELETE CASCADE ON UPDATE CASCADE
-<<<<<<< 85adee702147e7112ed41a440ad4f13d2ed9fd36
-=======
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-
---
--- Table structure for table `suggestions`
---
-
-DROP TABLE IF EXISTS `suggestions`;
-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, -- 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
- accepteddate date default NULL, -- date the suggestion was marked as accepted
- rejectedby INT(11) default NULL, -- borrowernumber for the librarian who rejected the suggestion, foreign key linking to the borrowers table
- rejecteddate date default NULL, -- date the suggestion was marked as rejected
- `STATUS` varchar(10) NOT NULL default '', -- suggestion status (ASKED, CHECKED, ACCEPTED, or REJECTED)
- `note` mediumtext, -- note entered on the suggestion
- `author` varchar(80) default NULL, -- author of the suggested item
- `title` varchar(255) default NULL, -- title of the suggested item
- `copyrightdate` smallint(6) default NULL, -- copyright date of the suggested item
- `publishercode` varchar(255) default NULL, -- publisher of the suggested item
- `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the suggestion was updated
- `volumedesc` varchar(255) default NULL,
- `publicationyear` smallint(6) default 0,
- `place` varchar(255) default NULL, -- publication place of the suggested item
- `isbn` varchar(30) default NULL, -- isbn of the suggested item
- `biblionumber` int(11) default NULL, -- foreign key linking the suggestion to the biblio table after the suggestion has been ordered
- `reason` text, -- reason for accepting or rejecting the suggestion
- `patronreason` text, -- reason for making the suggestion
- budgetid INT(11), -- foreign key linking the suggested budget to the aqbudgets 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)
- PRIMARY KEY (`suggestionid`),
- KEY `suggestedby` (`suggestedby`),
- KEY `managedby` (`managedby`),
- KEY `status` (`STATUS`),
- KEY `biblionumber` (`biblionumber`),
- KEY `branchcode` (`branchcode`),
- CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE
->>>>>>> BZ7677: New areas in subscriptions and new functions when receiving.
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
--
DROP TABLE IF EXISTS `virtualshelves`;
-CREATE TABLE `virtualshelves` ( -- information about lists (or virtual shelves)
+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` int default NULL, -- foreign key linking to the borrowers table (using borrowernumber) for the creator of this list (changed from varchar(80) to int)
`sortfield` varchar(16) default 'title', -- the field this list is sorted on
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the list was last modified
`created_on` datetime NOT NULL, -- creation time
- `allow_add` tinyint(1) default 0, -- permission for adding entries to list
- `allow_delete_own` tinyint(1) default 1, -- permission for deleting entries frm list that you added yourself
- `allow_delete_other` tinyint(1) default 0, -- permission for deleting entries from list that another person added
+ `allow_change_from_owner` tinyint(1) default 1, -- can owner change contents?
+ `allow_change_from_others` tinyint(1) default 0, -- can others change contents?
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 Members.pm
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `serialitems` (
`itemnumber` int(11) NOT NULL,
`serialid` int(11) NOT NULL,
- UNIQUE KEY `serialitemsidx` (`itemnumber`),
+ PRIMARY KEY (`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
`title` varchar(200) NOT NULL default '', -- subject line of the notice
`content` text, -- body text for the notice or slip
`message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice
- PRIMARY KEY (`module`,`code`, `branchcode`, `message_transport_type`),
+ `lang` varchar(25) NOT NULL DEFAULT 'default', -- lang of the notice
+ PRIMARY KEY (`module`,`code`, `branchcode`, `message_transport_type`, `lang`),
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 COLLATE=utf8_unicode_ci;
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`, `branchcode`) REFERENCES `letter` (`module`, `code`, `branchcode`) 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`message_type` varchar(1) NOT NULL, -- whether the message is for the librarians (L) or the patron (B)
`message` text NOT NULL, -- the text of the message
`message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written
- PRIMARY KEY (`message_id`)
+ `manager_id` int(11) default NULL, -- creator of message
+ PRIMARY KEY (`message_id`),
+ CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
`gstreg` tinyint(4) default NULL, -- is your library charged tax (1 for yes, 0 for no)
`listincgst` tinyint(4) default NULL, -- is tax included in list prices (1 for yes, 0 for no)
`invoiceincgst` tinyint(4) default NULL, -- is tax included in invoice prices (1 for yes, 0 for no)
- `gstrate` decimal(6,4) default NULL, -- the tax rate the library is charged
+ `tax_rate` 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
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
+ orderacquisition BOOLEAN NOT NULL DEFAULT 0, -- should this contact receive acquisition orders
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
`basketgroupid` int(11), -- links this basket to its group (aqbasketgroups.id)
`deliveryplace` varchar(10) default NULL, -- basket delivery place
`billingplace` varchar(10) default NULL, -- basket billing place
+ create_items ENUM('ordering', 'receiving', 'cataloguing') default NULL; -- when items should be created for orders in this basket
branch varchar(10) default NULL, -- basket branch
is_standing TINYINT(1) NOT NULL DEFAULT 0, -- orders in this basket are standing
PRIMARY KEY (`basketno`),
`listprice` decimal(28,6) default NULL, -- the vendor price for this line item
`datereceived` date default NULL, -- the date this order was received
invoiceid int(11) default NULL, -- id of invoice
- `freight` decimal(28,6) default NULL, -- shipping costs (not used)
- `unitprice` decimal(28,6) default NULL, -- the actual cost entered when receiving this line item
+ `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
+ `unitprice_tax_excluded` decimal(28,6) default NULL, -- the unit price excluding tax (on receiving)
+ `unitprice_tax_included` decimal(28,6) default NULL, -- the unit price including tax (on receiving)
`quantityreceived` smallint(6) NOT NULL default 0, -- the quantity that have been received so far
`datecancellationprinted` date default NULL, -- the date the line item was deleted
`cancellationreason` text default NULL, -- reason of cancellation
`purchaseordernumber` mediumtext, -- not used? always NULL
`basketno` int(11) default NULL, -- links this order line to a specific basket (aqbasket.basketno)
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order line was last modified
- `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
- `gstrate` decimal(6,4) default NULL, -- the tax rate for this line item
- `discount` float(6,4) default NULL, -- the discount for this line item
+ `rrp` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item
+ `rrp_tax_excluded` decimal(28,6) default NULL, -- the replacement cost excluding tax
+ `rrp_tax_included` decimal(28,6) default NULL, -- the replacement cost including tax
+ `ecost` decimal(13,2) DEFAULT NULL, -- the replacement cost for this line item
+ `ecost_tax_excluded` decimal(28,6) default NULL, -- the estimated cost excluding tax
+ `ecost_tax_included` decimal(28,6) default NULL, -- the estimated cost including tax
+ `tax_rate_bak` decimal(6,4) DEFAULT NULL, -- the tax rate for this line item (%)
+ `tax_rate_on_ordering` decimal(6,4) DEFAULT NULL, -- the tax rate on ordering for this line item (%)
+ `tax_rate_on_receiving` decimal(6,4) DEFAULT NULL, -- the tax rate on receiving for this line item (%)
+ `tax_value_bak` decimal(28,6) default NULL, -- the tax value for this line item
+ `tax_value_on_ordering` decimal(28,6) DEFAULT NULL, -- the tax value on ordering for this line item
+ `tax_value_on_receiving` decimal(28,6) DEFAULT NULL, -- the tax value on receiving for this line item
+ `discount` float(6,4) default NULL, -- the discount for this line item (%)
`budget_id` int(11) NOT NULL, -- the fund this order goes against (aqbudgets.budget_id)
`budgetdate` date default NULL, -- not used? always NULL
`sort1` varchar(80) default NULL, -- statistical field
`categorycode` varchar(10) DEFAULT NULL,
`dateenrolled` date DEFAULT NULL,
`dateexpiry` date DEFAULT NULL,
+ `date_renewed` date default NULL,
`gonenoaddress` tinyint(1) DEFAULT NULL,
`lost` tinyint(1) DEFAULT NULL,
`debarred` date DEFAULT NULL,
`altcontactphone` varchar(50) DEFAULT NULL,
`smsalertnumber` varchar(50) DEFAULT NULL,
`privacy` int(11) DEFAULT NULL,
+ `extended_attributes` text DEFAULT NULL,
PRIMARY KEY (`verification_token`,`borrowernumber`),
KEY `verification_token` (`verification_token`),
KEY `borrowernumber` (`borrowernumber`)
authorised_values_category VARCHAR(32) NULL DEFAULT NULL,
PRIMARY KEY(name),
CONSTRAINT items_search_fields_authorised_values_category
- FOREIGN KEY (authorised_values_category) REFERENCES authorised_values (category)
+ FOREIGN KEY (authorised_values_category) REFERENCES authorised_value_categories (category_name)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+--
+-- Table structure for table `housebound_profile`
+--
+
+DROP TABLE IF EXISTS `housebound_profile`;
+CREATE TABLE `housebound_profile` (
+ `borrowernumber` int(11) NOT NULL, -- Number of the borrower associated with this profile.
+ `day` text NOT NULL, -- The preferred day of the week for delivery.
+ `frequency` text NOT NULL, -- The Authorised_Value definining the pattern for delivery.
+ `fav_itemtypes` text default NULL, -- Free text describing preferred itemtypes.
+ `fav_subjects` text default NULL, -- Free text describing preferred subjects.
+ `fav_authors` text default NULL, -- Free text describing preferred authors.
+ `referral` text default NULL, -- Free text indicating how the borrower was added to the service.
+ `notes` text default NULL, -- Free text for additional notes.
+ PRIMARY KEY (`borrowernumber`),
+ CONSTRAINT `housebound_profile_bnfk`
+ FOREIGN KEY (`borrowernumber`)
+ REFERENCES `borrowers` (`borrowernumber`)
+ ON UPDATE CASCADE ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table `housebound_visit`
+--
+
+DROP TABLE IF EXISTS `housebound_visit`;
+CREATE TABLE `housebound_visit` (
+ `id` int(11) NOT NULL auto_increment, -- ID of the visit.
+ `borrowernumber` int(11) NOT NULL, -- Number of the borrower, & the profile, linked to this visit.
+ `appointment_date` date default NULL, -- Date of visit.
+ `day_segment` varchar(10), -- Rough time frame: 'morning', 'afternoon' 'evening'
+ `chooser_brwnumber` int(11) default NULL, -- Number of the borrower to choose items for delivery.
+ `deliverer_brwnumber` int(11) default NULL, -- Number of the borrower to deliver items.
+ PRIMARY KEY (`id`),
+ CONSTRAINT `houseboundvisit_bnfk`
+ FOREIGN KEY (`borrowernumber`)
+ REFERENCES `housebound_profile` (`borrowernumber`)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT `houseboundvisit_bnfk_1`
+ FOREIGN KEY (`chooser_brwnumber`)
+ REFERENCES `borrowers` (`borrowernumber`)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT `houseboundvisit_bnfk_2`
+ FOREIGN KEY (`deliverer_brwnumber`)
+ REFERENCES `borrowers` (`borrowernumber`)
+ ON UPDATE CASCADE ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table `housebound_role`
+--
+
+DROP TABLE IF EXISTS `housebound_role`;
+CREATE TABLE IF NOT EXISTS `housebound_role` (
+ `borrowernumber_id` int(11) NOT NULL, -- borrowernumber link
+ `housebound_chooser` tinyint(1) NOT NULL DEFAULT 0, -- set to 1 to indicate this patron is a housebound chooser volunteer
+ `housebound_deliverer` tinyint(1) NOT NULL DEFAULT 0, -- set to 1 to indicate this patron is a housebound deliverer volunteer
+ PRIMARY KEY (`borrowernumber_id`),
+ CONSTRAINT `houseboundrole_bnfk`
+ FOREIGN KEY (`borrowernumber_id`)
+ REFERENCES `borrowers` (`borrowernumber`)
+ ON UPDATE CASCADE ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'article_requests'
+--
+
+CREATE TABLE `article_requests` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `borrowernumber` int(11) NOT NULL,
+ `biblionumber` int(11) NOT NULL,
+ `itemnumber` int(11) DEFAULT NULL,
+ `branchcode` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
+ `title` text,
+ `author` text,
+ `volume` text,
+ `issue` text,
+ `date` text,
+ `pages` text,
+ `chapters` text,
+ `patron_notes` text,
+ `status` enum('PENDING','PROCESSING','COMPLETED','CANCELED') NOT NULL DEFAULT 'PENDING',
+ `notes` text,
+ `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `updated_on` timestamp NULL DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ KEY `borrowernumber` (`borrowernumber`),
+ KEY `biblionumber` (`biblionumber`),
+ KEY `itemnumber` (`itemnumber`),
+ KEY `branchcode` (`branchcode`),
+ CONSTRAINT `article_requests_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `article_requests_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `article_requests_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
+ CONSTRAINT `article_requests_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table `biblio_metadata`
+--
+
+CREATE TABLE biblio_metadata (
+ `id` INT(11) NOT NULL AUTO_INCREMENT,
+ `biblionumber` INT(11) NOT NULL,
+ `format` VARCHAR(16) NOT NULL,
+ `marcflavour` VARCHAR(16) NOT NULL,
+ `metadata` LONGTEXT NOT NULL,
+ PRIMARY KEY(id),
+ UNIQUE KEY `biblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
+ CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table `deletedbiblio_metadata`
+--
+
+CREATE TABLE deletedbiblio_metadata (
+ `id` INT(11) NOT NULL AUTO_INCREMENT,
+ `biblionumber` INT(11) NOT NULL,
+ `format` VARCHAR(16) NOT NULL,
+ `marcflavour` VARCHAR(16) NOT NULL,
+ `metadata` LONGTEXT NOT NULL,
+ PRIMARY KEY(id),
+ UNIQUE KEY `deletedbiblio_metadata_uniq_key` (`biblionumber`,`format`,`marcflavour`),
+ CONSTRAINT `deletedrecord_metadata_fk_1` FOREIGN KEY (biblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'club_templates'
+--
+
+CREATE TABLE IF NOT EXISTS club_templates (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ `name` tinytext NOT NULL,
+ description text,
+ is_enrollable_from_opac tinyint(1) NOT NULL DEFAULT '0',
+ is_email_required tinyint(1) NOT NULL DEFAULT '0',
+ branchcode varchar(10) NULL DEFAULT NULL,
+ date_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ date_updated timestamp NULL DEFAULT NULL,
+ is_deletable tinyint(1) NOT NULL DEFAULT '1',
+ PRIMARY KEY (id),
+ KEY ct_branchcode (branchcode),
+ CONSTRAINT `club_templates_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'clubs'
+--
+
+CREATE TABLE IF NOT EXISTS clubs (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ club_template_id int(11) NOT NULL,
+ `name` tinytext NOT NULL,
+ description text,
+ date_start date DEFAULT NULL,
+ date_end date DEFAULT NULL,
+ branchcode varchar(10) NULL DEFAULT NULL,
+ date_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ date_updated timestamp NULL DEFAULT NULL,
+ PRIMARY KEY (id),
+ KEY club_template_id (club_template_id),
+ KEY branchcode (branchcode),
+ CONSTRAINT clubs_ibfk_1 FOREIGN KEY (club_template_id) REFERENCES club_templates (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT clubs_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'club_enrollments'
+--
+
+CREATE TABLE IF NOT EXISTS club_enrollments (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ club_id int(11) NOT NULL,
+ borrowernumber int(11) NOT NULL,
+ date_enrolled timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ date_canceled timestamp NULL DEFAULT NULL,
+ date_created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+ date_updated timestamp NULL DEFAULT NULL,
+ branchcode varchar(10) NULL DEFAULT NULL,
+ PRIMARY KEY (id),
+ KEY club_id (club_id),
+ KEY borrowernumber (borrowernumber),
+ KEY branchcode (branchcode),
+ CONSTRAINT club_enrollments_ibfk_1 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT club_enrollments_ibfk_2 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT club_enrollments_ibfk_3 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ON DELETE SET NULL ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'club_template_enrollment_fields'
+--
+
+CREATE TABLE IF NOT EXISTS club_template_enrollment_fields (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ club_template_id int(11) NOT NULL,
+ `name` tinytext NOT NULL,
+ description text,
+ authorised_value_category varchar(16) DEFAULT NULL,
+ PRIMARY KEY (id),
+ KEY club_template_id (club_template_id),
+ CONSTRAINT club_template_enrollment_fields_ibfk_1 FOREIGN KEY (club_template_id) REFERENCES club_templates (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'club_enrollment_fields'
+--
+
+CREATE TABLE IF NOT EXISTS club_enrollment_fields (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ club_enrollment_id int(11) NOT NULL,
+ club_template_enrollment_field_id int(11) NOT NULL,
+ `value` text NOT NULL,
+ PRIMARY KEY (id),
+ KEY club_enrollment_id (club_enrollment_id),
+ KEY club_template_enrollment_field_id (club_template_enrollment_field_id),
+ CONSTRAINT club_enrollment_fields_ibfk_1 FOREIGN KEY (club_enrollment_id) REFERENCES club_enrollments (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT club_enrollment_fields_ibfk_2 FOREIGN KEY (club_template_enrollment_field_id) REFERENCES club_template_enrollment_fields (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'club_template_fields'
+--
+
+CREATE TABLE IF NOT EXISTS club_template_fields (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ club_template_id int(11) NOT NULL,
+ `name` tinytext NOT NULL,
+ description text,
+ authorised_value_category varchar(16) DEFAULT NULL,
+ PRIMARY KEY (id),
+ KEY club_template_id (club_template_id),
+ CONSTRAINT club_template_fields_ibfk_1 FOREIGN KEY (club_template_id) REFERENCES club_templates (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table 'club_fields'
+--
+
+CREATE TABLE IF NOT EXISTS club_fields (
+ id int(11) NOT NULL AUTO_INCREMENT,
+ club_template_field_id int(11) NOT NULL,
+ club_id int(11) NOT NULL,
+ `value` text,
+ PRIMARY KEY (id),
+ KEY club_template_field_id (club_template_field_id),
+ KEY club_id (club_id),
+ CONSTRAINT club_fields_ibfk_3 FOREIGN KEY (club_template_field_id) REFERENCES club_template_fields (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT club_fields_ibfk_4 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
projects / koha.git / blobdiff