--
DROP TABLE IF EXISTS `authorised_values`;
-CREATE TABLE `authorised_values` (
- `id` int(11) NOT NULL auto_increment,
- `category` varchar(10) NOT NULL default '',
- `authorised_value` varchar(80) NOT NULL default '',
- `lib` varchar(80) default NULL,
- `lib_opac` VARCHAR(80) default NULL,
- `imageurl` varchar(200) default NULL,
+CREATE TABLE `authorised_values` ( -- stores values for authorized values categories and values
+ `id` int(11) NOT NULL auto_increment, -- unique key, used to identify the authorized value
+ `category` varchar(10) NOT NULL default '', -- key used to identify the authorized value category
+ `authorised_value` varchar(80) NOT NULL default '', -- code use to identify the authorized value
+ `lib` varchar(80) default NULL, -- authorized value description as printed in the staff client
+ `lib_opac` VARCHAR(80) default NULL, -- authorized value description as printed in the OPAC
+ `imageurl` varchar(200) default NULL, -- authorized value URL
PRIMARY KEY (`id`),
KEY `name` (`category`),
KEY `lib` (`lib`),
--
DROP TABLE IF EXISTS `borrowers`;
-CREATE TABLE `borrowers` (
- `borrowernumber` int(11) NOT NULL auto_increment,
- `cardnumber` varchar(16) default NULL,
- `surname` mediumtext NOT NULL,
- `firstname` text,
- `title` mediumtext,
- `othernames` mediumtext,
- `initials` text,
- `streetnumber` varchar(10) default NULL,
- `streettype` varchar(50) default NULL,
- `address` mediumtext NOT NULL,
- `address2` text,
- `city` mediumtext NOT NULL,
- `zipcode` varchar(25) default NULL,
- `country` text,
- `email` mediumtext,
- `phone` text,
- `mobile` varchar(50) default NULL,
- `fax` mediumtext,
- `emailpro` text,
- `phonepro` text,
- `B_streetnumber` varchar(10) default NULL,
- `B_streettype` varchar(50) default NULL,
- `B_address` varchar(100) default NULL,
- `B_address2` text default NULL,
- `B_city` mediumtext,
- `B_zipcode` varchar(25) default NULL,
- `B_country` text,
- `B_email` text,
- `B_phone` mediumtext,
- `dateofbirth` date default NULL,
- `branchcode` varchar(10) NOT NULL default '',
- `categorycode` varchar(10) NOT NULL default '',
- `dateenrolled` date default NULL,
- `dateexpiry` date default NULL,
- `gonenoaddress` tinyint(1) default NULL,
- `lost` tinyint(1) default NULL,
- `debarred` tinyint(1) default NULL,
- `contactname` mediumtext,
- `contactfirstname` text,
- `contacttitle` text,
- `guarantorid` int(11) default NULL,
- `borrowernotes` mediumtext,
- `relationship` varchar(100) default NULL,
- `ethnicity` varchar(50) default NULL,
- `ethnotes` varchar(255) default NULL,
- `sex` varchar(1) default NULL,
- `password` varchar(30) default NULL,
- `flags` int(11) default NULL,
- `userid` varchar(30) default NULL,
- `opacnote` mediumtext,
- `contactnote` varchar(255) default NULL,
- `sort1` varchar(80) default NULL,
- `sort2` varchar(80) default NULL,
- `altcontactfirstname` varchar(255) default NULL,
- `altcontactsurname` varchar(255) default NULL,
- `altcontactaddress1` varchar(255) default NULL,
- `altcontactaddress2` varchar(255) default NULL,
- `altcontactaddress3` varchar(255) default NULL,
- `altcontactzipcode` varchar(50) default NULL,
- `altcontactcountry` text default NULL,
- `altcontactphone` varchar(50) default NULL,
- `smsalertnumber` varchar(50) default NULL,
- `privacy` integer(11) DEFAULT '1' NOT NULL,
+CREATE TABLE `borrowers` ( -- this table includes information about your patrons/borrowers/members
+ `borrowernumber` int(11) NOT NULL auto_increment, -- primary key, Koha assigned ID number for patrons/borrowers
+ `cardnumber` varchar(16) default NULL, -- unique key, library assigned ID number for patrons/borrowers
+ `surname` mediumtext NOT NULL, -- patron/borrower's last name (surname)
+ `firstname` text, -- patron/borrower's first name
+ `title` mediumtext, -- patron/borrower's title, for example: Mr. or Mrs.
+ `othernames` mediumtext, -- any other names associated with the patron/borrower
+ `initials` text, -- initials for your patron/borrower
+ `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
+ `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
+ `address` mediumtext NOT NULL, -- the first address line for your patron/borrower's primary address
+ `address2` text, -- the second address line for your patron/borrower's primary address
+ `city` mediumtext NOT NULL, -- the city or town for your patron/borrower's primary address
+ `state` text default NULL, -- the state or province for your patron/borrower's primary address
+ `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
+ `country` text, -- the country for your patron/borrower's primary address
+ `email` mediumtext, -- the primary email address for your patron/borrower's primary address
+ `phone` text, -- the primary phone number for your patron/borrower's primary address
+ `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
+ `fax` mediumtext, -- the fax number for your patron/borrower's primary address
+ `emailpro` text, -- the secondary email addres for your patron/borrower's primary address
+ `phonepro` text, -- the secondary phone number for your patron/borrower's primary address
+ `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
+ `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
+ `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
+ `B_address2` text default NULL, -- the second address line for your patron/borrower's alternate address
+ `B_city` mediumtext, -- the city or town for your patron/borrower's alternate address
+ `B_state` text default NULL, -- the state for your patron/borrower's alternate address
+ `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
+ `B_country` text, -- the country for your patron/borrower's alternate address
+ `B_email` text, -- the patron/borrower's alternate email address
+ `B_phone` mediumtext, -- the patron/borrower's alternate phone number
+ `dateofbirth` date default NULL, -- the patron/borrower's date of birth (YYYY-MM-DD)
+ `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, includes the code of the patron/borrower's home branch
+ `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)
+ `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` tinyint(1) default NULL, -- set to 1 for yes and 0 for no, flag to note that library marked this patron/borrower as being restricted
+ `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
+ `borrowernotes` mediumtext, -- a note on the patron/borroewr'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
+ `ethnicity` varchar(50) default NULL, -- unused in Koha
+ `ethnotes` varchar(255) default NULL, -- unused in Koha
+ `sex` varchar(1) default NULL, -- patron/borrower's gender
+ `password` varchar(30) default NULL, -- patron/borrower's encrypted password
+ `flags` int(11) default NULL, -- will include a number associated with the staff member's permissions
+ `userid` varchar(30) 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
+ `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
+ `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
+ `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
+ `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower
+ `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower
+ `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
+ `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
+ `altcontactaddress3` varchar(255) default NULL, -- the third address line for the alternate contact for the patron/borrower
+ `altcontactstate` text default NULL, -- the city and state for the alternate contact for the patron/borrower
+ `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)
+ `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
UNIQUE KEY `cardnumber` (`cardnumber`),
PRIMARY KEY `borrowernumber` (`borrowernumber`),
KEY `categorycode` (`categorycode`),
`branchaddress3` mediumtext,
`branchzip` varchar(25) default NULL,
`branchcity` mediumtext,
+ `branchstate` mediumtext,
`branchcountry` text,
`branchphone` mediumtext,
`branchfax` mediumtext,
CREATE TABLE `cities` (
`cityid` int(11) NOT NULL auto_increment,
`city_name` varchar(100) NOT NULL default '',
+ `city_state` VARCHAR( 100 ) NULL DEFAULT NULL,
+ `city_country` VARCHAR( 100 ) NULL DEFAULT NULL,
`city_zipcode` varchar(20) default NULL,
PRIMARY KEY (`cityid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`address` mediumtext NOT NULL,
`address2` text,
`city` mediumtext NOT NULL,
+ `state` text default NULL,
`zipcode` varchar(25) default NULL,
`country` text,
`email` mediumtext,
`B_address` varchar(100) default NULL,
`B_address2` text default NULL,
`B_city` mediumtext,
+ `B_state` text default NULL,
`B_zipcode` varchar(25) default NULL,
`B_country` text,
`B_email` text,
`altcontactaddress1` varchar(255) default NULL,
`altcontactaddress2` varchar(255) default NULL,
`altcontactaddress3` varchar(255) default NULL,
+ `altcontactstate` text default NULL,
`altcontactzipcode` varchar(50) default NULL,
`altcontactcountry` text default NULL,
`altcontactphone` varchar(50) default NULL,
DROP TABLE IF EXISTS `issues`;
CREATE TABLE `issues` (
- `borrowernumber` int(11) default NULL,
- `itemnumber` int(11) default NULL,
+ `borrowernumber` int(11),
+ `itemnumber` int(11),
`date_due` date default NULL,
`branchcode` varchar(10) default NULL,
`issuingbranch` varchar(18) default NULL,
`renewals` tinyint(4) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`issuedate` date default NULL,
+ PRIMARY KEY (`itemnumber`),
KEY `issuesborridx` (`borrowernumber`),
- KEY `issuesitemidx` (`itemnumber`),
KEY `bordate` (`borrowernumber`,`timestamp`),
- CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
- CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL
+ CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`stocknumber` varchar(32) default NULL,
PRIMARY KEY (`itemnumber`),
UNIQUE KEY `itembarcodeidx` (`barcode`),
- UNIQUE KEY `itemstocknumberidx` (`stocknumber`),
+ KEY `itemstocknumberidx` (`stocknumber`),
KEY `itembinoidx` (`biblioitemnumber`),
KEY `itembibnoidx` (`biblionumber`),
KEY `homebranch` (`homebranch`),
CREATE TABLE `overduerules` (
`branchcode` varchar(10) NOT NULL default '',
`categorycode` varchar(10) NOT NULL default '',
- `delay1` int(4) default 0,
+ `delay1` int(4) default NULL,
`letter1` varchar(20) default NULL,
`debarred1` varchar(1) default 0,
- `delay2` int(4) default 0,
+ `delay2` int(4) default NULL,
`debarred2` varchar(1) default 0,
`letter2` varchar(20) default NULL,
- `delay3` int(4) default 0,
+ `delay3` int(4) default NULL,
`letter3` varchar(20) default NULL,
`debarred3` int(1) default 0,
PRIMARY KEY (`branchcode`,`categorycode`)
`waitingdate` date default NULL,
`expirationdate` DATE DEFAULT NULL,
`lowestPriority` tinyint(1) NOT NULL,
+ KEY priorityfoundidx (priority,found),
KEY `borrowernumber` (`borrowernumber`),
KEY `biblionumber` (`biblionumber`),
KEY `itemnumber` (`itemnumber`),
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
+ CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `user_permissions`;
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`notify_id` int(11) NOT NULL default 0,
`notify_level` int(2) NOT NULL default 0,
+ `note` text NULL default NULL,
+ `manager_id` int(11) NULL,
KEY `acctsborridx` (`borrowernumber`),
KEY `timeidx` (`timestamp`),
KEY `itemnumber` (`itemnumber`),
`closed` tinyint(1) default NULL,
`booksellerid` int(11) NOT NULL,
`deliveryplace` varchar(10) default NULL,
+ `freedeliveryplace` text default NULL,
`deliverycomment` varchar(255) default NULL,
`billingplace` varchar(10) default NULL,
PRIMARY KEY (`id`),
projects / koha.git / blobdiff