X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=3045f13e4992cb7b7284c304b59537cb806e3391;hb=b40d4052b9ebb6139d43c6155e13c4e6497cac5d;hp=4854dffed54b21f61552dd04dcb2555672317538;hpb=122b26219efdb3542b1f7ea3fa456669e8c578f5;p=koha.git diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 4854dffed5..3045f13e49 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -15,244 +15,6 @@ /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; --- --- Table structure for table `accountlines` --- - -DROP TABLE IF EXISTS `accountlines`; -CREATE TABLE `accountlines` ( - `borrowernumber` int(11) NOT NULL default 0, - `accountno` smallint(6) NOT NULL default 0, - `itemnumber` int(11) default NULL, - `date` date default NULL, - `amount` decimal(28,6) default NULL, - `description` mediumtext, - `dispute` mediumtext, - `accounttype` varchar(5) default NULL, - `amountoutstanding` decimal(28,6) default NULL, - `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, - KEY `acctsborridx` (`borrowernumber`), - KEY `timeidx` (`timestamp`), - KEY `itemnumber` (`itemnumber`), - CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `accountoffsets` --- - -DROP TABLE IF EXISTS `accountoffsets`; -CREATE TABLE `accountoffsets` ( - `borrowernumber` int(11) NOT NULL default 0, - `accountno` smallint(6) NOT NULL default 0, - `offsetaccount` smallint(6) NOT NULL default 0, - `offsetamount` decimal(28,6) default NULL, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `action_logs` --- - -DROP TABLE IF EXISTS `action_logs`; -CREATE TABLE `action_logs` ( - `action_id` int(11) NOT NULL auto_increment, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `user` int(11) NOT NULL default 0, - `module` text, - `action` text, - `object` int(11) default NULL, - `info` text, - PRIMARY KEY (`action_id`), - KEY (`timestamp`,`user`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `alert` --- - -DROP TABLE IF EXISTS `alert`; -CREATE TABLE `alert` ( - `alertid` int(11) NOT NULL auto_increment, - `borrowernumber` int(11) NOT NULL default 0, - `type` varchar(10) NOT NULL default '', - `externalid` varchar(20) NOT NULL default '', - PRIMARY KEY (`alertid`), - KEY `borrowernumber` (`borrowernumber`), - KEY `type` (`type`,`externalid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `aqbasket` --- - -DROP TABLE IF EXISTS `aqbasket`; -CREATE TABLE `aqbasket` ( - `basketno` int(11) NOT NULL auto_increment, - `creationdate` date default NULL, - `closedate` date default NULL, - `booksellerid` int(11) NOT NULL default 1, - `authorisedby` varchar(10) default NULL, - `booksellerinvoicenumber` mediumtext, - PRIMARY KEY (`basketno`), - KEY `booksellerid` (`booksellerid`), - CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `aqbookfund` --- - -DROP TABLE IF EXISTS `aqbookfund`; -CREATE TABLE `aqbookfund` ( - `bookfundid` varchar(10) NOT NULL default '', - `bookfundname` mediumtext, - `bookfundgroup` varchar(5) default NULL, - `branchcode` varchar(10) NOT NULL default '', - PRIMARY KEY (`bookfundid`,`branchcode`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `aqbooksellers` --- - -DROP TABLE IF EXISTS `aqbooksellers`; -CREATE TABLE `aqbooksellers` ( - `id` int(11) NOT NULL auto_increment, - `name` mediumtext, - `address1` mediumtext, - `address2` mediumtext, - `address3` mediumtext, - `address4` mediumtext, - `phone` varchar(30) default NULL, - `accountnumber` mediumtext, - `othersupplier` mediumtext, - `currency` varchar(3) NOT NULL default '', - `deliverydays` smallint(6) default NULL, - `followupdays` smallint(6) default NULL, - `followupscancel` smallint(6) default NULL, - `specialty` mediumtext, - `booksellerfax` mediumtext, - `notes` mediumtext, - `bookselleremail` mediumtext, - `booksellerurl` mediumtext, - `contact` varchar(100) default NULL, - `postal` mediumtext, - `url` varchar(255) default NULL, - `contpos` varchar(100) default NULL, - `contphone` varchar(100) default NULL, - `contfax` varchar(100) default NULL, - `contaltphone` varchar(100) default NULL, - `contemail` varchar(100) default NULL, - `contnotes` mediumtext, - `active` tinyint(4) default NULL, - `listprice` varchar(10) default NULL, - `invoiceprice` varchar(10) default NULL, - `gstreg` tinyint(4) default NULL, - `listincgst` tinyint(4) default NULL, - `invoiceincgst` tinyint(4) default NULL, - `discount` float(6,4) default NULL, - `fax` varchar(50) default NULL, - `nocalc` int(11) default NULL, - `invoicedisc` float(6,4) default NULL, - PRIMARY KEY (`id`), - KEY `listprice` (`listprice`), - KEY `invoiceprice` (`invoiceprice`), - CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `aqbudget` --- - -DROP TABLE IF EXISTS `aqbudget`; -CREATE TABLE `aqbudget` ( - `bookfundid` varchar(10) NOT NULL default '', - `startdate` date NOT NULL default 0, - `enddate` date default NULL, - `budgetamount` decimal(13,2) default NULL, - `aqbudgetid` tinyint(4) NOT NULL auto_increment, - `branchcode` varchar(10) default NULL, - PRIMARY KEY (`aqbudgetid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `aqorderbreakdown` --- - -DROP TABLE IF EXISTS `aqorderbreakdown`; -CREATE TABLE `aqorderbreakdown` ( - `ordernumber` int(11) default NULL, - `linenumber` int(11) default NULL, - `branchcode` varchar(10) default NULL, - `bookfundid` varchar(10) NOT NULL default '', - `allocation` smallint(6) default NULL, - KEY `ordernumber` (`ordernumber`), - KEY `bookfundid` (`bookfundid`), - CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- 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` --- - -DROP TABLE IF EXISTS `aqorders`; -CREATE TABLE `aqorders` ( - `ordernumber` int(11) NOT NULL auto_increment, - `biblionumber` int(11) default NULL, - `title` mediumtext, - `entrydate` date default NULL, - `quantity` smallint(6) default NULL, - `currency` varchar(3) default NULL, - `listprice` decimal(28,6) default NULL, - `totalamount` decimal(28,6) default NULL, - `datereceived` date default NULL, - `booksellerinvoicenumber` mediumtext, - `freight` decimal(28,6) default NULL, - `unitprice` decimal(28,6) default NULL, - `quantityreceived` smallint(6) default NULL, - `cancelledby` varchar(10) default NULL, - `datecancellationprinted` date default NULL, - `notes` mediumtext, - `supplierreference` mediumtext, - `purchaseordernumber` mediumtext, - `subscription` tinyint(1) default NULL, - `serialid` varchar(30) default NULL, - `basketno` int(11) default NULL, - `biblioitemnumber` int(11) default NULL, - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `rrp` decimal(13,2) default NULL, - `ecost` decimal(13,2) default NULL, - `gst` decimal(13,2) default NULL, - `budgetdate` date default NULL, - `sort1` varchar(80) default NULL, - `sort2` varchar(80) default NULL, - PRIMARY KEY (`ordernumber`), - KEY `basketno` (`basketno`), - KEY `biblionumber` (`biblionumber`), - CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -- -- Table structure for table `auth_header` -- @@ -293,7 +55,7 @@ CREATE TABLE `auth_subfield_structure` ( `hidden` tinyint(3) NOT NULL default 0, `linkid` tinyint(1) NOT NULL default 0, `kohafield` varchar(45) NULL default '', - `frameworkcode` varchar(8) NOT NULL default '', + `frameworkcode` varchar(10) NOT NULL default '', PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`), KEY `tab` (`authtypecode`,`tab`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -338,8 +100,11 @@ CREATE TABLE `authorised_values` ( `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, PRIMARY KEY (`id`), - KEY `name` (`category`) + KEY `name` (`category`), + KEY `lib` (`lib`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -386,7 +151,7 @@ CREATE TABLE `biblioitems` ( `volume` mediumtext, `number` mediumtext, `itemtype` varchar(10) default NULL, - `isbn` varchar(14) default NULL, + `isbn` varchar(30) default NULL, `issn` varchar(9) default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, @@ -440,6 +205,7 @@ CREATE TABLE `borrowers` ( `address2` text, `city` mediumtext NOT NULL, `zipcode` varchar(25) default NULL, + `country` text, `email` mediumtext, `phone` text, `mobile` varchar(50) default NULL, @@ -449,8 +215,10 @@ CREATE TABLE `borrowers` ( `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, @@ -483,7 +251,9 @@ CREATE TABLE `borrowers` ( `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, UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), @@ -493,6 +263,53 @@ CREATE TABLE `borrowers` ( CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `borrower_attribute_types` +-- + +DROP TABLE IF EXISTS `borrower_attribute_types`; +CREATE TABLE `borrower_attribute_types` ( + `code` varchar(10) NOT NULL, + `description` varchar(255) NOT NULL, + `repeatable` tinyint(1) NOT NULL default 0, + `unique_id` tinyint(1) NOT NULL default 0, + `opac_display` tinyint(1) NOT NULL default 0, + `password_allowed` tinyint(1) NOT NULL default 0, + `staff_searchable` tinyint(1) NOT NULL default 0, + `authorised_value_category` varchar(10) default NULL, + PRIMARY KEY (`code`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `borrower_attributes` +-- + +DROP TABLE IF EXISTS `borrower_attributes`; +CREATE TABLE `borrower_attributes` ( + `borrowernumber` int(11) NOT NULL, + `code` varchar(10) NOT NULL, + `attribute` varchar(64) default NULL, + `password` varchar(64) default NULL, + KEY `borrowernumber` (`borrowernumber`), + KEY `code_attribute` (`code`, `attribute`), + CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `branch_item_rules` ( + `branchcode` varchar(10) NOT NULL, + `itemtype` varchar(10) NOT NULL, + `holdallowed` tinyint(1) default NULL, + PRIMARY KEY (`itemtype`,`branchcode`), + KEY `branch_item_rules_ibfk_2` (`branchcode`), + CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table `branchcategories` -- @@ -517,12 +334,17 @@ CREATE TABLE `branches` ( `branchaddress1` mediumtext, `branchaddress2` mediumtext, `branchaddress3` mediumtext, + `branchzip` varchar(25) default NULL, + `branchcity` mediumtext, + `branchcountry` text, `branchphone` mediumtext, `branchfax` mediumtext, `branchemail` mediumtext, + `branchurl` mediumtext, `issuing` tinyint(4) default NULL, `branchip` varchar(15) default NULL, `branchprinter` varchar(100) default NULL, + `branchnotes` mediumtext, UNIQUE KEY `branchcode` (`branchcode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -597,15 +419,70 @@ CREATE TABLE `categories` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table structure for table `categorytable` +-- Table structure for table `borrower_branch_circ_rules` -- -DROP TABLE IF EXISTS `categorytable`; -CREATE TABLE `categorytable` ( - `categorycode` varchar(5) NOT NULL default '', - `description` text, - `itemtypecodes` text, - PRIMARY KEY (`categorycode`) +DROP TABLE IF EXISTS `branch_borrower_circ_rules`; +CREATE TABLE `branch_borrower_circ_rules` ( + `branchcode` VARCHAR(10) NOT NULL, + `categorycode` VARCHAR(10) NOT NULL, + `maxissueqty` int(4) default NULL, + PRIMARY KEY (`categorycode`, `branchcode`), + CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `default_borrower_circ_rules` +-- + +DROP TABLE IF EXISTS `default_borrower_circ_rules`; +CREATE TABLE `default_borrower_circ_rules` ( + `categorycode` VARCHAR(10) NOT NULL, + `maxissueqty` int(4) default NULL, + PRIMARY KEY (`categorycode`), + CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `default_branch_circ_rules` +-- + +DROP TABLE IF EXISTS `default_branch_circ_rules`; +CREATE TABLE `default_branch_circ_rules` ( + `branchcode` VARCHAR(10) NOT NULL, + `maxissueqty` int(4) default NULL, + `holdallowed` tinyint(1) default NULL, + PRIMARY KEY (`branchcode`), + CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `default_branch_item_rules` +-- + +CREATE TABLE `default_branch_item_rules` ( + `itemtype` varchar(10) NOT NULL, + `holdallowed` tinyint(1) default NULL, + PRIMARY KEY (`itemtype`), + CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `default_circ_rules` +-- + +DROP TABLE IF EXISTS `default_circ_rules`; +CREATE TABLE `default_circ_rules` ( + `singleton` enum('singleton') NOT NULL default 'singleton', + `maxissueqty` int(4) default NULL, + `holdallowed` int(1) default NULL, + PRIMARY KEY (`singleton`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -659,6 +536,7 @@ CREATE TABLE `currency` ( `symbol` varchar(5) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `rate` float(7,5) default NULL, + `active` tinyint(1) default NULL, PRIMARY KEY (`currency`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -695,7 +573,7 @@ CREATE TABLE `deletedbiblioitems` ( `volume` mediumtext, `number` mediumtext, `itemtype` varchar(10) default NULL, - `isbn` varchar(14) default NULL, + `isbn` varchar(30) default NULL, `issn` varchar(9) default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, @@ -748,6 +626,7 @@ CREATE TABLE `deletedborrowers` ( `address2` text, `city` mediumtext NOT NULL, `zipcode` varchar(25) default NULL, + `country` text, `email` mediumtext, `phone` text, `mobile` varchar(50) default NULL, @@ -757,13 +636,15 @@ CREATE TABLE `deletedborrowers` ( `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(2) default NULL, + `categorycode` varchar(10) default NULL, `dateenrolled` date default NULL, `dateexpiry` date default NULL, `gonenoaddress` tinyint(1) default NULL, @@ -791,7 +672,9 @@ CREATE TABLE `deletedborrowers` ( `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, KEY `borrowernumber` (`borrowernumber`), KEY `cardnumber` (`cardnumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -819,7 +702,7 @@ CREATE TABLE `deleteditems` ( `damaged` tinyint(1) NOT NULL default 0, `itemlost` tinyint(1) NOT NULL default 0, `wthdrawn` tinyint(1) NOT NULL default 0, - `itemcallnumber` varchar(30) default NULL, + `itemcallnumber` varchar(255) default NULL, `issues` smallint(6) default NULL, `renewals` smallint(6) default NULL, `reserves` smallint(6) default NULL, @@ -829,6 +712,7 @@ CREATE TABLE `deleteditems` ( `paidfor` mediumtext, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `location` varchar(80) default NULL, + `permanent_location` varchar(80) default NULL, `onloan` date default NULL, `cn_source` varchar(10) default NULL, `cn_sort` varchar(30) default NULL, @@ -838,11 +722,12 @@ CREATE TABLE `deleteditems` ( `itype` varchar(10) default NULL, `more_subfields_xml` longtext default NULL, `enumchron` varchar(80) default NULL, - `copynumber` smallint(6) default NULL, + `copynumber` varchar(32) default NULL, + `stocknumber` varchar(32) default NULL, `marc` longblob, - `copynumber` smallint(6) default NULL, PRIMARY KEY (`itemnumber`), KEY `delitembarcodeidx` (`barcode`), + KEY `delitemstocknumberidx` (`stocknumber`), KEY `delitembinoidx` (`biblioitemnumber`), KEY `delitembibnoidx` (`biblionumber`), KEY `delhomebranch` (`homebranch`), @@ -860,6 +745,47 @@ CREATE TABLE `ethnicity` ( PRIMARY KEY (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `export_format` +-- + +DROP TABLE IF EXISTS `export_format`; +CREATE TABLE `export_format` ( + `export_format_id` int(11) NOT NULL auto_increment, + `profile` varchar(255) NOT NULL, + `description` mediumtext NOT NULL, + `marcfields` mediumtext NOT NULL, + `csv_separator` varchar(2) NOT NULL, + `field_separator` varchar(2) NOT NULL, + `subfield_separator` varchar(2) NOT NULL, + `encoding` varchar(255) NOT NULL, + PRIMARY KEY (`export_format_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export'; + + +-- +-- Table structure for table `hold_fill_targets` +-- + +DROP TABLE IF EXISTS `hold_fill_targets`; +CREATE TABLE hold_fill_targets ( + `borrowernumber` int(11) NOT NULL, + `biblionumber` int(11) NOT NULL, + `itemnumber` int(11) NOT NULL, + `source_branchcode` varchar(10) default NULL, + `item_level_request` tinyint(4) NOT NULL default 0, + PRIMARY KEY `itemnumber` (`itemnumber`), + KEY `bib_branch` (`biblionumber`, `source_branchcode`), + CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`) + REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`) + REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`) + REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`) + REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table `import_batches` -- @@ -873,7 +799,9 @@ CREATE TABLE `import_batches` ( `num_biblios` int(11) NOT NULL default 0, `num_items` int(11) NOT NULL default 0, `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, - `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new', + `overlay_action` enum('replace', 'create_new', 'use_template', 'ignore') NOT NULL default 'create_new', + `nomatch_action` enum('create_new', 'ignore') NOT NULL default 'create_new', + `item_action` enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore') NOT NULL default 'always_add', `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging', `batch_type` enum('batch', 'z3950') NOT NULL default 'batch', `file_name` varchar(100), @@ -899,7 +827,7 @@ CREATE TABLE `import_records` ( `marcxml_old` longtext NOT NULL, `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio', `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match', - `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged', + `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted', 'ignored') NOT NULL default 'staged', `import_error` mediumtext, `encoding` varchar(40) NOT NULL default '', `z3950random` varchar(40) default NULL, @@ -918,7 +846,7 @@ CREATE TABLE `import_record_matches` ( `import_record_id` int(11) NOT NULL, `candidate_match_id` int(11) NOT NULL, `score` int(11) NOT NULL default 0, - CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`) + CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`) REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE, KEY `record_score` (`import_record_id`, `score`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -935,10 +863,10 @@ CREATE TABLE `import_biblios` ( `original_source` varchar(25) default NULL, `title` varchar(128) default NULL, `author` varchar(80) default NULL, - `isbn` varchar(14) default NULL, + `isbn` varchar(30) default NULL, `issn` varchar(9) default NULL, `has_items` tinyint(1) NOT NULL default 0, - CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`) + CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`) REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE, KEY `matched_biblionumber` (`matched_biblionumber`), KEY `title` (`title`), @@ -955,11 +883,11 @@ CREATE TABLE `import_items` ( `import_record_id` int(11) NOT NULL, `itemnumber` int(11) default NULL, `branchcode` varchar(10) default NULL, - `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged', + `status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged', `marcxml` longtext NOT NULL, `import_error` mediumtext, PRIMARY KEY (`import_items_id`), - CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`) + CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`) REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE, KEY `itemnumber` (`itemnumber`), KEY `branchcode` (`branchcode`) @@ -1001,12 +929,15 @@ CREATE TABLE `issuingrules` ( `rentaldiscount` decimal(28,6) default NULL, `reservecharge` decimal(28,6) default NULL, `fine` decimal(28,6) default NULL, + `finedays` int(11) default NULL, `firstremind` int(11) default NULL, `chargeperiod` int(11) default NULL, `accountsent` int(11) default NULL, `chargename` varchar(100) default NULL, `maxissueqty` int(4) default NULL, `issuelength` int(4) default NULL, + `renewalsallowed` smallint(6) NOT NULL default "0", + `reservesallowed` smallint(6) NOT NULL default "0", `branchcode` varchar(10) NOT NULL default '', PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`), KEY `categorycode` (`categorycode`), @@ -1036,7 +967,7 @@ CREATE TABLE `items` ( `damaged` tinyint(1) NOT NULL default 0, `itemlost` tinyint(1) NOT NULL default 0, `wthdrawn` tinyint(1) NOT NULL default 0, - `itemcallnumber` varchar(30) default NULL, + `itemcallnumber` varchar(255) default NULL, `issues` smallint(6) default NULL, `renewals` smallint(6) default NULL, `reserves` smallint(6) default NULL, @@ -1046,6 +977,7 @@ CREATE TABLE `items` ( `paidfor` mediumtext, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `location` varchar(80) default NULL, + `permanent_location` varchar(80) default NULL, `onloan` date default NULL, `cn_source` varchar(10) default NULL, `cn_sort` varchar(30) default NULL, @@ -1055,9 +987,11 @@ CREATE TABLE `items` ( `itype` varchar(10) default NULL, `more_subfields_xml` longtext default NULL, `enumchron` varchar(80) default NULL, - `copynumber` smallint(6) default NULL, + `copynumber` varchar(32) default NULL, + `stocknumber` varchar(32) default NULL, PRIMARY KEY (`itemnumber`), UNIQUE KEY `itembarcodeidx` (`barcode`), + UNIQUE KEY `itemstocknumberidx` (`stocknumber`), KEY `itembinoidx` (`biblioitemnumber`), KEY `itembibnoidx` (`biblionumber`), KEY `homebranch` (`homebranch`), @@ -1075,7 +1009,6 @@ DROP TABLE IF EXISTS `itemtypes`; CREATE TABLE `itemtypes` ( `itemtype` varchar(10) NOT NULL default '', `description` mediumtext, - `renewalsallowed` smallint(6) default NULL, `rentalcharge` double(16,4) default NULL, `notforloan` smallint(6) default NULL, `imageurl` varchar(200) default NULL, @@ -1085,86 +1018,98 @@ CREATE TABLE `itemtypes` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table structure for table `labels` --- - -DROP TABLE IF EXISTS `labels`; -CREATE TABLE `labels` ( - `labelid` int(11) NOT NULL auto_increment, - `batch_id` varchar(10) NOT NULL default 1, - `itemnumber` varchar(100) NOT NULL default '', - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - PRIMARY KEY (`labelid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `labels_conf` --- - -DROP TABLE IF EXISTS `labels_conf`; -CREATE TABLE `labels_conf` ( - `id` int(4) NOT NULL auto_increment, - `barcodetype` char(100) default '', - `title` int(1) default '0', - `subtitle` int(1) default '0', - `itemtype` int(1) default '0', - `barcode` int(1) default '0', - `dewey` int(1) default '0', - `class` int(1) default NULL, - `subclass` int(1) default '0', - `itemcallnumber` int(1) default '0', - `author` int(1) default '0', - `issn` int(1) default '0', - `isbn` int(1) default '0', - `startlabel` int(2) NOT NULL default '1', - `printingtype` char(32) default 'BAR', - `layoutname` char(20) NOT NULL default 'TEST', - `guidebox` int(1) default '0', - `active` tinyint(1) default '1', - `fonttype` char(10) collate utf8_unicode_ci default NULL, - `ccode` char(4) collate utf8_unicode_ci default NULL, - `callnum_split` int(1) default NULL, - `text_justify` char(1) collate utf8_unicode_ci default NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `labels_profile` --- - -DROP TABLE IF EXISTS `labels_profile`; -CREATE TABLE `labels_profile` ( - `tmpl_id` int(4) NOT NULL, - `prof_id` int(4) NOT NULL, - UNIQUE KEY `tmpl_id` (`tmpl_id`), - UNIQUE KEY `prof_id` (`prof_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `labels_templates` --- - -DROP TABLE IF EXISTS `labels_templates`; -CREATE TABLE `labels_templates` ( -`tmpl_id` int(4) NOT NULL auto_increment, - `tmpl_code` char(100) default '', - `tmpl_desc` char(100) default '', - `page_width` float default '0', - `page_height` float default '0', - `label_width` float default '0', - `label_height` float default '0', - `topmargin` float default '0', - `leftmargin` float default '0', - `cols` int(2) default '0', - `rows` int(2) default '0', - `colgap` float default '0', - `rowgap` float default '0', - `active` int(1) default NULL, - `units` char(20) default 'PX', - `fontsize` int(4) NOT NULL default '3', - `font` char(10) NOT NULL default 'TR', - PRIMARY KEY (`tmpl_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- Table structure for table `creator_batches` +-- + +DROP TABLE IF EXISTS `creator_batches`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `creator_batches` ( + `label_id` int(11) NOT NULL AUTO_INCREMENT, + `batch_id` int(10) NOT NULL DEFAULT '1', + `item_number` int(11) DEFAULT NULL, + `borrower_number` int(11) DEFAULT NULL, + `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `branch_code` varchar(10) NOT NULL DEFAULT 'NB', + `creator` char(15) NOT NULL DEFAULT 'Labels', + PRIMARY KEY (`label_id`) USING BTREE, + KEY `branch_fk_constraint` (`branch_code`), + KEY `item_fk_constraint` (`item_number`), + KEY `borrower_fk_constraint` (`borrower_number`), + CONSTRAINT `creator_batches_ibfk_1` FOREIGN KEY (`borrower_number`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `creator_batches_ibfk_2` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE, + CONSTRAINT `creator_batches_ibfk_3` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `creator_images` +-- + +DROP TABLE IF EXISTS `creator_images`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `creator_images` ( + `image_id` int(4) NOT NULL AUTO_INCREMENT, + `imagefile` mediumblob, + `image_name` char(20) NOT NULL DEFAULT 'DEFAULT', + PRIMARY KEY (`image_id`) USING BTREE, + UNIQUE KEY `image_name_index` (`image_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `creator_layouts` +-- + +DROP TABLE IF EXISTS `creator_layouts`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `creator_layouts` ( + `layout_id` int(4) NOT NULL AUTO_INCREMENT, + `barcode_type` char(100) NOT NULL DEFAULT 'CODE39', + `start_label` int(2) NOT NULL DEFAULT '1', + `printing_type` char(32) NOT NULL DEFAULT 'BAR', + `layout_name` char(20) NOT NULL DEFAULT 'DEFAULT', + `guidebox` int(1) DEFAULT '0', + `font` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'TR', + `font_size` int(4) NOT NULL DEFAULT '10', + `units` char(20) NOT NULL DEFAULT 'POINT', + `callnum_split` int(1) DEFAULT '0', + `text_justify` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'L', + `format_string` varchar(210) NOT NULL DEFAULT 'barcode', + `layout_xml` text NOT NULL, + `creator` char(15) NOT NULL DEFAULT 'Labels', + PRIMARY KEY (`layout_id`) USING BTREE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `creator_templates` +-- + +DROP TABLE IF EXISTS `creator_templates`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `creator_templates` ( + `template_id` int(4) NOT NULL AUTO_INCREMENT, + `profile_id` int(4) DEFAULT NULL, + `template_code` char(100) NOT NULL DEFAULT 'DEFAULT TEMPLATE', + `template_desc` char(100) NOT NULL DEFAULT 'Default description', + `page_width` float NOT NULL DEFAULT '0', + `page_height` float NOT NULL DEFAULT '0', + `label_width` float NOT NULL DEFAULT '0', + `label_height` float NOT NULL DEFAULT '0', + `top_text_margin` float NOT NULL DEFAULT '0', + `left_text_margin` float NOT NULL DEFAULT '0', + `top_margin` float NOT NULL DEFAULT '0', + `left_margin` float NOT NULL DEFAULT '0', + `cols` int(2) NOT NULL DEFAULT '0', + `rows` int(2) NOT NULL DEFAULT '0', + `col_gap` float NOT NULL DEFAULT '0', + `row_gap` float NOT NULL DEFAULT '0', + `units` char(20) NOT NULL DEFAULT 'POINT', + `creator` char(15) NOT NULL DEFAULT 'Labels', + PRIMARY KEY (`template_id`), + KEY `template_profile_fk_constraint` (`profile_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Table structure for table `letter` @@ -1187,7 +1132,7 @@ CREATE TABLE `letter` ( DROP TABLE IF EXISTS `marc_subfield_structure`; CREATE TABLE `marc_subfield_structure` ( `tagfield` varchar(3) NOT NULL default '', - `tagsubfield` varchar(1) NOT NULL default '', + `tagsubfield` varchar(1) NOT NULL default '' COLLATE utf8_bin, `liblibrarian` varchar(255) NOT NULL default '', `libopac` varchar(255) NOT NULL default '', `repeatable` tinyint(4) NOT NULL default 0, @@ -1318,18 +1263,6 @@ CREATE TABLE `matchchecks` ( REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- --- Table structure for table `mediatypetable` --- - -DROP TABLE IF EXISTS `mediatypetable`; -CREATE TABLE `mediatypetable` ( - `mediatypecode` varchar(5) NOT NULL default '', - `description` text, - `itemtypecodes` text, - PRIMARY KEY (`mediatypecode`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -- -- Table structure for table `notifys` -- @@ -1351,13 +1284,13 @@ CREATE TABLE `notifys` ( DROP TABLE IF EXISTS `nozebra`; CREATE TABLE `nozebra` ( - `server` varchar(20) NOT NULL, - `indexname` varchar(40) NOT NULL, - `value` varchar(250) NOT NULL, - `biblionumbers` longtext NOT NULL, - KEY `indexname` (`server`,`indexname`), - KEY `value` (`server`,`value`)) - ENGINE=InnoDB DEFAULT CHARSET=utf8; + `server` varchar(20) NOT NULL, + `indexname` varchar(40) NOT NULL, + `value` varchar(250) NOT NULL, + `biblionumbers` longtext NOT NULL, + KEY `indexname` (`server`,`indexname`), + KEY `value` (`server`,`value`)) + ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Table structure for table `old_issues` @@ -1379,9 +1312,9 @@ CREATE TABLE `old_issues` ( KEY `old_issuesborridx` (`borrowernumber`), KEY `old_issuesitemidx` (`itemnumber`), KEY `old_bordate` (`borrowernumber`,`timestamp`), - CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) + CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1408,11 +1341,11 @@ CREATE TABLE `old_reserves` ( KEY `old_reserves_biblionumber` (`biblionumber`), KEY `old_reserves_itemnumber` (`itemnumber`), KEY `old_reserves_branchcode` (`branchcode`), - CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) + CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) + CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1439,7 +1372,7 @@ CREATE TABLE `opac_news` ( DROP TABLE IF EXISTS `overduerules`; CREATE TABLE `overduerules` ( `branchcode` varchar(10) NOT NULL default '', - `categorycode` varchar(2) NOT NULL default '', + `categorycode` varchar(10) NOT NULL default '', `delay1` int(4) default 0, `letter1` varchar(20) default NULL, `debarred1` varchar(1) default 0, @@ -1452,6 +1385,21 @@ CREATE TABLE `overduerules` ( PRIMARY KEY (`branchcode`,`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `patroncards` +-- + +DROP TABLE IF EXISTS `patroncards`; +CREATE TABLE `patroncards` ( + `cardid` int(11) NOT NULL auto_increment, + `batch_id` varchar(10) NOT NULL default '1', + `borrowernumber` int(11) NOT NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (`cardid`), + KEY `patroncards_ibfk_1` (`borrowernumber`), + CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table `patronimage` -- @@ -1483,18 +1431,18 @@ CREATE TABLE `printers` ( DROP TABLE IF EXISTS `printers_profile`; CREATE TABLE `printers_profile` ( - `prof_id` int(4) NOT NULL auto_increment, - `printername` varchar(40) NOT NULL, - `tmpl_id` int(4) NOT NULL, - `paper_bin` varchar(20) NOT NULL, - `offset_horz` float default NULL, - `offset_vert` float default NULL, - `creep_horz` float default NULL, - `creep_vert` float default NULL, - `unit` char(20) NOT NULL default 'POINT', - PRIMARY KEY (`prof_id`), - UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`), - CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE + `profile_id` int(4) NOT NULL auto_increment, + `printer_name` varchar(40) NOT NULL default 'Default Printer', + `template_id` int(4) NOT NULL default '0', + `paper_bin` varchar(20) NOT NULL default 'Bypass', + `offset_horz` float NOT NULL default '0', + `offset_vert` float NOT NULL default '0', + `creep_horz` float NOT NULL default '0', + `creep_vert` float NOT NULL default '0', + `units` char(20) NOT NULL default 'POINT', + `creator` char(15) NOT NULL DEFAULT 'Labels', + PRIMARY KEY (`profile_id`), + UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -1515,7 +1463,7 @@ CREATE TABLE `repeatable_holidays` ( -- -- Table structure for table `reports_dictionary` --- +-- DROP TABLE IF EXISTS `reports_dictionary`; CREATE TABLE reports_dictionary ( @@ -1630,8 +1578,25 @@ CREATE TABLE saved_reports ( `date_run` datetime default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; - - + + +-- +-- Table structure for table `search_history` +-- + +DROP TABLE IF EXISTS `search_history`; +CREATE TABLE IF NOT EXISTS `search_history` ( + `userid` int(11) NOT NULL, + `sessionid` varchar(32) NOT NULL, + `query_desc` varchar(255) NOT NULL, + `query_cgi` varchar(255) NOT NULL, + `total` int(11) NOT NULL, + `time` timestamp NOT NULL default CURRENT_TIMESTAMP, + KEY `userid` (`userid`), + KEY `sessionid` (`sessionid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results'; + + -- -- Table structure for table `serial` -- @@ -1656,7 +1621,7 @@ CREATE TABLE `serial` ( -- Table structure for table `sessions` -- -DROP TABLE IF EXISTS sessions; +DROP TABLE IF EXISTS sessions; CREATE TABLE sessions ( `id` varchar(32) NOT NULL, `a_session` text NOT NULL, @@ -1709,18 +1674,6 @@ DROP TABLE IF EXISTS `stopwords`; `word` varchar(255) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- --- Table structure for table `subcategorytable` --- - -DROP TABLE IF EXISTS `subcategorytable`; -CREATE TABLE `subcategorytable` ( - `subcategorycode` varchar(5) NOT NULL default '', - `description` text, - `itemtypecodes` text, - PRIMARY KEY (`subcategorycode`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -- -- Table structure for table `subscription` -- @@ -1769,9 +1722,14 @@ CREATE TABLE `subscription` ( `distributedto` text, `internalnotes` longtext, `callnumber` text, + `location` varchar(80) NULL default '', `branchcode` varchar(10) NOT NULL default '', `hemisphere` tinyint(3) default 0, `lastbranch` varchar(10), + `serialsadditems` tinyint(1) NOT NULL default '0', + `staffdisplaycount` VARCHAR(10) NULL, + `opacdisplaycount` VARCHAR(10) NULL, + `graceperiod` int(11) NOT NULL default '0', PRIMARY KEY (`subscriptionid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1784,7 +1742,7 @@ CREATE TABLE `subscriptionhistory` ( `biblionumber` int(11) NOT NULL default 0, `subscriptionid` int(11) NOT NULL default 0, `histstartdate` date default NULL, - `enddate` date default NULL, + `histenddate` date default NULL, `missinglist` longtext NOT NULL, `recievedlist` longtext NOT NULL, `opacnote` varchar(150) NOT NULL default '', @@ -1814,7 +1772,13 @@ DROP TABLE IF EXISTS `suggestions`; CREATE TABLE `suggestions` ( `suggestionid` int(8) NOT NULL auto_increment, `suggestedby` int(11) NOT NULL default 0, + `suggesteddate` date NOT NULL default 0, `managedby` int(11) default NULL, + `manageddate` date default NULL, + acceptedby INT(11) default NULL, + accepteddate date default NULL, + rejectedby INT(11) default NULL, + rejecteddate date default NULL, `STATUS` varchar(10) NOT NULL default '', `note` mediumtext, `author` varchar(80) default NULL, @@ -1825,10 +1789,14 @@ CREATE TABLE `suggestions` ( `volumedesc` varchar(255) default NULL, `publicationyear` smallint(6) default 0, `place` varchar(255) default NULL, - `isbn` varchar(10) default NULL, + `isbn` varchar(30) default NULL, `mailoverseeing` smallint(1) default 0, `biblionumber` int(11) default NULL, `reason` text, + budgetid INT(11), + branchcode VARCHAR(10) default NULL, + collectiontitle text default NULL, + itemtype VARCHAR(30) default NULL, PRIMARY KEY (`suggestionid`), KEY `suggestedby` (`suggestedby`), KEY `managedby` (`managedby`) @@ -1859,6 +1827,61 @@ CREATE TABLE `tags` ( PRIMARY KEY (`entry`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `tags_all` +-- + +DROP TABLE IF EXISTS `tags_all`; +CREATE TABLE `tags_all` ( + `tag_id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL, + `biblionumber` int(11) NOT NULL, + `term` varchar(255) NOT NULL, + `language` int(4) default NULL, + `date_created` datetime NOT NULL, + PRIMARY KEY (`tag_id`), + KEY `tags_borrowers_fk_1` (`borrowernumber`), + KEY `tags_biblionumber_fk_1` (`biblionumber`), + CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`) + REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`) + REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `tags_approval` +-- + +DROP TABLE IF EXISTS `tags_approval`; +CREATE TABLE `tags_approval` ( + `term` varchar(255) NOT NULL, + `approved` int(1) NOT NULL default '0', + `date_approved` datetime default NULL, + `approved_by` int(11) default NULL, + `weight_total` int(9) NOT NULL default '1', + PRIMARY KEY (`term`), + KEY `tags_approval_borrowers_fk_1` (`approved_by`), + CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`) + REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `tags_index` +-- + +DROP TABLE IF EXISTS `tags_index`; +CREATE TABLE `tags_index` ( + `term` varchar(255) NOT NULL, + `biblionumber` int(11) NOT NULL, + `weight` int(9) NOT NULL default '1', + PRIMARY KEY (`term`,`biblionumber`), + KEY `tags_index_biblionumber_fk_1` (`biblionumber`), + CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`) + REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`) + REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + -- -- Table structure for table `userflags` -- @@ -1883,6 +1906,7 @@ CREATE TABLE `virtualshelves` ( `owner` varchar(80) default NULL, `category` varchar(1) default NULL, `sortfield` varchar(16) default NULL, + `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`shelfnumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1895,7 +1919,7 @@ CREATE TABLE `virtualshelfcontents` ( `shelfnumber` int(11) NOT NULL default 0, `biblionumber` int(11) NOT NULL default 0, `flags` int(11) default NULL, - `dateadded` timestamp NULL default NULL, + `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY `shelfnumber` (`shelfnumber`), KEY `biblionumber` (`biblionumber`), CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE, @@ -1933,7 +1957,7 @@ CREATE TABLE `z3950servers` ( DROP TABLE IF EXISTS `zebraqueue`; CREATE TABLE `zebraqueue` ( `id` int(11) NOT NULL auto_increment, - `biblio_auth_number` int(11) NOT NULL default '0', + `biblio_auth_number` bigint(20) unsigned NOT NULL default '0', `operation` char(20) NOT NULL default '', `server` char(20) NOT NULL default '', `done` int(11) NOT NULL default '0', @@ -1958,6 +1982,8 @@ CREATE TABLE language_subtag_registry ( type varchar(25), -- language-script-region-variant-extension-privateuse description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list added date, + id int(11) NOT NULL auto_increment, + PRIMARY KEY (`id`), KEY `subtag` (`subtag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1968,6 +1994,8 @@ DROP TABLE IF EXISTS language_rfc4646_to_iso639; CREATE TABLE language_rfc4646_to_iso639 ( rfc4646_subtag varchar(25), iso639_2_code varchar(25), + id int(11) NOT NULL auto_increment, + PRIMARY KEY (`id`), KEY `rfc4646_subtag` (`rfc4646_subtag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1977,6 +2005,8 @@ CREATE TABLE language_descriptions ( type varchar(25), lang varchar(25), description varchar(255), + id int(11) NOT NULL auto_increment, + PRIMARY KEY (`id`), KEY `lang` (`lang`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1997,13 +2027,526 @@ CREATE TABLE language_script_mapping ( KEY `language_subtag` (`language_subtag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -DROP TABLE IF EXISTS serialitems; -CREATE TABLE serialitems ( - serialid int(11) NOT NULL, - itemnumber int(11) NOT NULL, - UNIQUE KEY `serialididx` (`serialid`) +DROP TABLE IF EXISTS `permissions`; +CREATE TABLE `permissions` ( + `module_bit` int(11) NOT NULL DEFAULT 0, + `code` varchar(64) DEFAULT NULL, + `description` varchar(255) DEFAULT NULL, + PRIMARY KEY (`module_bit`, `code`), + CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`) + ON DELETE CASCADE ON UPDATE CASCADE +) 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +DROP TABLE IF EXISTS `user_permissions`; +CREATE TABLE `user_permissions` ( + `borrowernumber` int(11) NOT NULL DEFAULT 0, + `module_bit` int(11) NOT NULL DEFAULT 0, + `code` varchar(64) DEFAULT NULL, + CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `tmp_holdsqueue` +-- + +DROP TABLE IF EXISTS `tmp_holdsqueue`; +CREATE TABLE `tmp_holdsqueue` ( + `biblionumber` int(11) default NULL, + `itemnumber` int(11) default NULL, + `barcode` varchar(20) default NULL, + `surname` mediumtext NOT NULL, + `firstname` text, + `phone` text, + `borrowernumber` int(11) NOT NULL, + `cardnumber` varchar(16) default NULL, + `reservedate` date default NULL, + `title` mediumtext, + `itemcallnumber` varchar(255) default NULL, + `holdingbranch` varchar(10) default NULL, + `pickbranch` varchar(10) default NULL, + `notes` text, + `item_level_request` tinyint(4) NOT NULL default 0 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_queue` +-- + +DROP TABLE IF EXISTS `message_queue`; +CREATE TABLE `message_queue` ( + `message_id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) default NULL, + `subject` text, + `content` text, + `metadata` text DEFAULT NULL, + `letter_code` varchar(64) DEFAULT NULL, + `message_transport_type` varchar(20) NOT NULL, + `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending', + `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `to_address` mediumtext, + `from_address` mediumtext, + `content_type` text, + KEY `message_id` (`message_id`), + KEY `borrowernumber` (`borrowernumber`), + KEY `message_transport_type` (`message_transport_type`), + CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_transport_types` +-- + +DROP TABLE IF EXISTS `message_transport_types`; +CREATE TABLE `message_transport_types` ( + `message_transport_type` varchar(20) NOT NULL, + PRIMARY KEY (`message_transport_type`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_attributes` +-- + +DROP TABLE IF EXISTS `message_attributes`; +CREATE TABLE `message_attributes` ( + `message_attribute_id` int(11) NOT NULL auto_increment, + `message_name` varchar(20) NOT NULL default '', + `takes_days` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`message_attribute_id`), + UNIQUE KEY `message_name` (`message_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `message_transports` +-- + +DROP TABLE IF EXISTS `message_transports`; +CREATE TABLE `message_transports` ( + `message_attribute_id` int(11) NOT NULL, + `message_transport_type` varchar(20) NOT NULL, + `is_digest` tinyint(1) NOT NULL default '0', + `letter_module` varchar(20) NOT NULL default '', + `letter_code` varchar(20) NOT NULL default '', + PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`), + 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`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `borrower_message_preferences` +-- + +DROP TABLE IF EXISTS `borrower_message_preferences`; +CREATE TABLE `borrower_message_preferences` ( + `borrower_message_preference_id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) default NULL, + `categorycode` varchar(10) default NULL, + `message_attribute_id` int(11) default '0', + `days_in_advance` int(11) default '0', + `wants_digest` tinyint(1) NOT NULL default '0', + PRIMARY KEY (`borrower_message_preference_id`), + KEY `borrowernumber` (`borrowernumber`), + KEY `categorycode` (`categorycode`), + KEY `message_attribute_id` (`message_attribute_id`), + CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `borrower_message_transport_preferences` +-- + +DROP TABLE IF EXISTS `borrower_message_transport_preferences`; +CREATE TABLE `borrower_message_transport_preferences` ( + `borrower_message_preference_id` int(11) NOT NULL default '0', + `message_transport_type` varchar(20) NOT NULL default '0', + PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`), + KEY `message_transport_type` (`message_transport_type`), + CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_message_transport_preferences_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; + +-- +-- Table structure for the table branch_transfer_limits +-- + +DROP TABLE IF EXISTS `branch_transfer_limits`; +CREATE TABLE branch_transfer_limits ( + limitId int(8) NOT NULL auto_increment, + toBranch varchar(10) NOT NULL, + fromBranch varchar(10) NOT NULL, + itemtype varchar(10) NULL, + ccode varchar(10) NULL, + PRIMARY KEY (limitId) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `item_circulation_alert_preferences` +-- + +DROP TABLE IF EXISTS `item_circulation_alert_preferences`; +CREATE TABLE `item_circulation_alert_preferences` ( + `id` int(11) NOT NULL auto_increment, + `branchcode` varchar(10) NOT NULL, + `categorycode` varchar(10) NOT NULL, + `item_type` varchar(10) NOT NULL, + `notification` varchar(16) NOT NULL, + PRIMARY KEY (`id`), + KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `messages` +-- + +CREATE TABLE `messages` ( + `message_id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL, + `branchcode` varchar(4) default NULL, + `message_type` varchar(1) NOT NULL, + `message` text NOT NULL, + `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`message_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `accountlines` +-- + +DROP TABLE IF EXISTS `accountlines`; +CREATE TABLE `accountlines` ( + `borrowernumber` int(11) NOT NULL default 0, + `accountno` smallint(6) NOT NULL default 0, + `itemnumber` int(11) default NULL, + `date` date default NULL, + `amount` decimal(28,6) default NULL, + `description` mediumtext, + `dispute` mediumtext, + `accounttype` varchar(5) default NULL, + `amountoutstanding` decimal(28,6) default NULL, + `lastincrement` decimal(28,6) default NULL, + `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, + KEY `acctsborridx` (`borrowernumber`), + KEY `timeidx` (`timestamp`), + KEY `itemnumber` (`itemnumber`), + CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `accountoffsets` +-- + +DROP TABLE IF EXISTS `accountoffsets`; +CREATE TABLE `accountoffsets` ( + `borrowernumber` int(11) NOT NULL default 0, + `accountno` smallint(6) NOT NULL default 0, + `offsetaccount` smallint(6) NOT NULL default 0, + `offsetamount` decimal(28,6) default NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `action_logs` +-- + +DROP TABLE IF EXISTS `action_logs`; +CREATE TABLE `action_logs` ( + `action_id` int(11) NOT NULL auto_increment, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `user` int(11) NOT NULL default 0, + `module` text, + `action` text, + `object` int(11) default NULL, + `info` text, + PRIMARY KEY (`action_id`), + KEY (`timestamp`,`user`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `alert` +-- + +DROP TABLE IF EXISTS `alert`; +CREATE TABLE `alert` ( + `alertid` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL default 0, + `type` varchar(10) NOT NULL default '', + `externalid` varchar(20) NOT NULL default '', + PRIMARY KEY (`alertid`), + KEY `borrowernumber` (`borrowernumber`), + KEY `type` (`type`,`externalid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `aqbasketgroups` +-- + +DROP TABLE IF EXISTS `aqbasketgroups`; +CREATE TABLE `aqbasketgroups` ( + `id` int(11) NOT NULL auto_increment, + `name` varchar(50) default NULL, + `closed` tinyint(1) default NULL, + `booksellerid` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `booksellerid` (`booksellerid`), + CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `aqbasket` +-- + +DROP TABLE IF EXISTS `aqbasket`; +CREATE TABLE `aqbasket` ( + `basketno` int(11) NOT NULL auto_increment, + `basketname` varchar(50) default NULL, + `note` mediumtext, + `booksellernote` mediumtext, + `contractnumber` int(11), + `creationdate` date default NULL, + `closedate` date default NULL, + `booksellerid` int(11) NOT NULL default 1, + `authorisedby` varchar(10) default NULL, + `booksellerinvoicenumber` mediumtext, + `basketgroupid` int(11), + PRIMARY KEY (`basketno`), + KEY `booksellerid` (`booksellerid`), + KEY `basketgroupid` (`basketgroupid`), + KEY `contractnumber` (`contractnumber`), + CONSTRAINT `aqbasket_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE, + CONSTRAINT `aqbasket_ibfk_2` FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`), + CONSTRAINT `aqbasket_ibfk_3` FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `aqbooksellers` +-- + +DROP TABLE IF EXISTS `aqbooksellers`; +CREATE TABLE `aqbooksellers` ( + `id` int(11) NOT NULL auto_increment, + `name` mediumtext NOT NULL, + `address1` mediumtext, + `address2` mediumtext, + `address3` mediumtext, + `address4` mediumtext, + `phone` varchar(30) default NULL, + `accountnumber` mediumtext, + `othersupplier` mediumtext, + `currency` varchar(3) NOT NULL default '', + `booksellerfax` mediumtext, + `notes` mediumtext, + `bookselleremail` mediumtext, + `booksellerurl` mediumtext, + `contact` varchar(100) default NULL, + `postal` mediumtext, + `url` varchar(255) default NULL, + `contpos` varchar(100) default NULL, + `contphone` varchar(100) default NULL, + `contfax` varchar(100) default NULL, + `contaltphone` varchar(100) default NULL, + `contemail` varchar(100) default NULL, + `contnotes` mediumtext, + `active` tinyint(4) default NULL, + `listprice` varchar(10) default NULL, + `invoiceprice` varchar(10) default NULL, + `gstreg` tinyint(4) default NULL, + `listincgst` tinyint(4) default NULL, + `invoiceincgst` tinyint(4) default NULL, + `gstrate` decimal(6,4) default NULL, + `discount` float(6,4) default NULL, + `fax` varchar(50) default NULL, + PRIMARY KEY (`id`), + KEY `listprice` (`listprice`), + KEY `invoiceprice` (`invoiceprice`), + CONSTRAINT `aqbooksellers_ibfk_1` FOREIGN KEY (`listprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `aqbooksellers_ibfk_2` FOREIGN KEY (`invoiceprice`) REFERENCES `currency` (`currency`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `aqbudgets` +-- + +DROP TABLE IF EXISTS `aqbudgets`; +CREATE TABLE `aqbudgets` ( + `budget_id` int(11) NOT NULL auto_increment, + `budget_parent_id` int(11) default NULL, + `budget_code` varchar(30) default NULL, + `budget_name` varchar(80) default NULL, + `budget_branchcode` varchar(10) default NULL, + `budget_amount` decimal(28,6) NULL default '0.00', + `budget_encumb` decimal(28,6) NULL default '0.00', + `budget_expend` decimal(28,6) NULL default '0.00', + `budget_notes` mediumtext, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `budget_period_id` int(11) default NULL, + `sort1_authcat` varchar(80) default NULL, + `sort2_authcat` varchar(80) default NULL, + `budget_owner_id` int(11) default NULL, + `budget_permission` int(1) default '0', + PRIMARY KEY (`budget_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + +-- +-- Table structure for table `aqbudgetperiods` +-- + + +DROP TABLE IF EXISTS `aqbudgetperiods`; +CREATE TABLE `aqbudgetperiods` ( + `budget_period_id` int(11) NOT NULL auto_increment, + `budget_period_startdate` date NOT NULL, + `budget_period_enddate` date NOT NULL, + `budget_period_active` tinyint(1) default '0', + `budget_period_description` mediumtext, + `budget_period_total` decimal(28,6), + `budget_period_locked` tinyint(1) default NULL, + `sort1_authcat` varchar(10) default NULL, + `sort2_authcat` varchar(10) default NULL, + PRIMARY KEY (`budget_period_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `aqbudgets_planning` +-- + +DROP TABLE IF EXISTS `aqbudgets_planning`; +CREATE TABLE `aqbudgets_planning` ( + `plan_id` int(11) NOT NULL auto_increment, + `budget_id` int(11) NOT NULL, + `budget_period_id` int(11) NOT NULL, + `estimated_amount` decimal(28,6) default NULL, + `authcat` varchar(30) NOT NULL, + `authvalue` varchar(30) NOT NULL, + PRIMARY KEY (`plan_id`), + 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 'aqcontract' +-- + +DROP TABLE IF EXISTS `aqcontract`; +CREATE TABLE `aqcontract` ( + `contractnumber` int(11) NOT NULL auto_increment, + `contractstartdate` date default NULL, + `contractenddate` date default NULL, + `contractname` varchar(50) default NULL, + `contractdescription` mediumtext, + `booksellerid` int(11) not NULL, + PRIMARY KEY (`contractnumber`), + CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`) + 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` +-- + +DROP TABLE IF EXISTS `aqorders`; +CREATE TABLE `aqorders` ( + `ordernumber` int(11) NOT NULL auto_increment, + `biblionumber` int(11) default NULL, + `entrydate` date default NULL, + `quantity` smallint(6) default NULL, + `currency` varchar(3) default NULL, + `listprice` decimal(28,6) default NULL, + `totalamount` decimal(28,6) default NULL, + `datereceived` date default NULL, + `booksellerinvoicenumber` mediumtext, + `freight` decimal(28,6) default NULL, + `unitprice` decimal(28,6) default NULL, + `quantityreceived` smallint(6) default NULL, + `cancelledby` varchar(10) default NULL, + `datecancellationprinted` date default NULL, + `notes` mediumtext, + `supplierreference` mediumtext, + `purchaseordernumber` mediumtext, + `subscription` tinyint(1) default NULL, + `serialid` varchar(30) default NULL, + `basketno` int(11) default NULL, + `biblioitemnumber` int(11) default NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + `rrp` decimal(13,2) default NULL, + `ecost` decimal(13,2) default NULL, + `gst` decimal(13,2) default NULL, + `budget_id` int(11) NOT NULL, + `budgetgroup_id` int(11) NOT NULL, + `budgetdate` date default NULL, + `sort1` varchar(80) default NULL, + `sort2` varchar(80) default NULL, + `sort1_authcat` varchar(10) default NULL, + `sort2_authcat` varchar(10) default NULL, + `uncertainprice` tinyint(1), + PRIMARY KEY (`ordernumber`), + KEY `basketno` (`basketno`), + KEY `biblionumber` (`biblionumber`), + CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `aqorders_items` +-- + +DROP TABLE IF EXISTS `aqorders_items`; +CREATE TABLE `aqorders_items` ( + `ordernumber` int(11) NOT NULL, + `itemnumber` int(11) NOT NULL, + `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (`itemnumber`), + KEY `ordernumber` (`ordernumber`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table `fieldmapping` +-- + +DROP TABLE IF EXISTS `fieldmapping`; +CREATE TABLE `fieldmapping` ( + `id` int(11) NOT NULL auto_increment, + `field` varchar(255) NOT NULL, + `frameworkcode` char(4) NOT NULL default '', + `fieldcode` char(3) NOT NULL, + `subfieldcode` char(1) NOT NULL, + PRIMARY KEY (`id`) +) 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 */;