X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=ea3167732ab103eaa85aebe2fa68c8b1a860013a;hb=990e9dbd9dfcb00e10f8feb9272be93b57f5763e;hp=a62e07c9fb9b946fdf09948cdd764d3b9962b1b7;hpb=554c77569d5afe52912c085d0603b9d87a062d9b;p=koha.git diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index a62e07c9fb..ea3167732a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -95,13 +95,13 @@ CREATE TABLE `auth_types` ( -- 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`), @@ -193,70 +193,73 @@ CREATE TABLE `biblioitems` ( -- 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`), @@ -342,6 +345,7 @@ CREATE TABLE `branches` ( `branchaddress3` mediumtext, `branchzip` varchar(25) default NULL, `branchcity` mediumtext, + `branchstate` mediumtext, `branchcountry` text, `branchphone` mediumtext, `branchfax` mediumtext, @@ -420,6 +424,7 @@ CREATE TABLE `categories` ( `overduenoticerequired` tinyint(1) default NULL, `issuelimit` smallint(6) default NULL, `reservefee` decimal(28,6) default NULL, + `hidelostitems` tinyint(1) NOT NULL default '0', `category_type` varchar(1) NOT NULL default 'A', PRIMARY KEY (`categorycode`), UNIQUE KEY `categorycode` (`categorycode`) @@ -521,6 +526,8 @@ DROP TABLE IF EXISTS `cities`; 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; @@ -653,6 +660,7 @@ CREATE TABLE `deletedborrowers` ( `address` mediumtext NOT NULL, `address2` text, `city` mediumtext NOT NULL, + `state` text default NULL, `zipcode` varchar(25) default NULL, `country` text, `email` mediumtext, @@ -666,6 +674,7 @@ CREATE TABLE `deletedborrowers` ( `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, @@ -699,6 +708,7 @@ CREATE TABLE `deletedborrowers` ( `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, @@ -928,8 +938,8 @@ CREATE TABLE `import_items` ( 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, @@ -939,11 +949,11 @@ CREATE TABLE `issues` ( `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; -- @@ -965,6 +975,8 @@ CREATE TABLE `issuingrules` ( `chargename` varchar(100) default NULL, `maxissueqty` int(4) default NULL, `issuelength` int(4) default NULL, + `hardduedate` date default NULL, + `hardduedatecompare` tinyint NOT NULL default "0", `renewalsallowed` smallint(6) NOT NULL default "0", `reservesallowed` smallint(6) NOT NULL default "0", `branchcode` varchar(10) NOT NULL default '', @@ -1020,7 +1032,7 @@ CREATE TABLE `items` ( `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`), @@ -1404,13 +1416,13 @@ DROP TABLE IF EXISTS `overduerules`; 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`) @@ -1543,6 +1555,7 @@ CREATE TABLE `reserves` ( `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`), @@ -2088,7 +2101,7 @@ CREATE TABLE `serialitems` ( 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`; @@ -2289,6 +2302,8 @@ CREATE TABLE `accountlines` ( `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`), @@ -2353,6 +2368,7 @@ CREATE TABLE `aqbasketgroups` ( `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`),