X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=7a416172bfa7b463dca2eed18d0a8d724f0c86d4;hb=88b3711ca918877796d8577c54efe3f0aba96673;hp=23e5b10b1126af44cbee027bbcda6051da174d56;hpb=c36098e3168165f01fd8784da82747df03a9ae97;p=koha.git diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 23e5b10b11..7a416172bf 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -15,245 +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, - `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 `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 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 '', - `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` -- @@ -294,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; @@ -339,10 +100,12 @@ 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 `lib` (`lib`) + KEY `lib` (`lib`), + KEY `auth_value_idx` (`authorised_value`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -420,8 +183,8 @@ CREATE TABLE `biblioitems` ( KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), KEY `isbn` (`isbn`), - KEY `publishercode` (`publishercode`), KEY `issn` (`issn`), + KEY `publishercode` (`publishercode`), CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -493,11 +256,13 @@ CREATE TABLE `borrowers` ( `altcontactcountry` text default NULL, `altcontactphone` varchar(50) default NULL, `smsalertnumber` varchar(50) default NULL, + `privacy` integer(11) DEFAULT '1' NOT NULL, UNIQUE KEY `cardnumber` (`cardnumber`), PRIMARY KEY `borrowernumber` (`borrowernumber`), KEY `categorycode` (`categorycode`), KEY `branchcode` (`branchcode`), KEY `userid` (`userid`), + KEY `guarantorid` (`guarantorid`), CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -516,7 +281,8 @@ CREATE TABLE `borrower_attribute_types` ( `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`) + PRIMARY KEY (`code`), + KEY `auth_val_cat_idx` (`authorised_value_category`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -537,6 +303,7 @@ CREATE TABLE `borrower_attributes` ( ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +DROP TABLE IF EXISTS `branch_item_rules`; CREATE TABLE `branch_item_rules` ( `branchcode` varchar(10) NOT NULL, `itemtype` varchar(10) NOT NULL, @@ -573,7 +340,7 @@ CREATE TABLE `branches` ( `branchaddress1` mediumtext, `branchaddress2` mediumtext, `branchaddress3` mediumtext, - `branchzip` varchar(25) default NULL, + `branchzip` varchar(25) default NULL, `branchcity` mediumtext, `branchcountry` text, `branchphone` mediumtext, @@ -644,6 +411,7 @@ CREATE TABLE `categories` ( `categorycode` varchar(10) NOT NULL default '', `description` mediumtext, `enrolmentperiod` smallint(6) default NULL, + `enrolmentperioddate` DATE NULL DEFAULT NULL, `upperagelimit` smallint(6) default NULL, `dateofbirthrequired` tinyint(1) default NULL, `finetype` varchar(30) default NULL, @@ -652,11 +420,33 @@ 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table: collections +-- +CREATE TABLE collections ( + colId integer(11) NOT NULL auto_increment, + colTitle varchar(100) NOT NULL DEFAULT '', + colDesc text NOT NULL, + colBranchcode varchar(4) DEFAULT NULL comment 'branchcode for branch where item should be held.', + PRIMARY KEY (colId) +) ENGINE=InnoDB DEFAULT CHARACTER SET utf8; + +-- +-- Table: collections_tracking +-- +CREATE TABLE collections_tracking ( + ctId integer(11) NOT NULL auto_increment, + colId integer(11) NOT NULL DEFAULT 0 comment 'collections.colId', + itemnumber integer(11) NOT NULL DEFAULT 0 comment 'items.itemnumber', + PRIMARY KEY (ctId) +) ENGINE=InnoDB DEFAULT CHARACTER SET utf8; + -- -- Table structure for table `borrower_branch_circ_rules` -- @@ -703,7 +493,7 @@ CREATE TABLE `default_branch_circ_rules` ( -- -- Table structure for table `default_branch_item_rules` -- - +DROP TABLE IF EXISTS `default_branch_item_rules`; CREATE TABLE `default_branch_item_rules` ( `itemtype` varchar(10) NOT NULL, `holdallowed` tinyint(1) default NULL, @@ -774,7 +564,8 @@ CREATE TABLE `currency` ( `currency` varchar(10) NOT NULL default '', `symbol` varchar(5) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `rate` float(7,5) default NULL, + `rate` float(15,5) default NULL, + `active` tinyint(1) default NULL, PRIMARY KEY (`currency`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -852,7 +643,7 @@ CREATE TABLE `deletedbiblioitems` ( DROP TABLE IF EXISTS `deletedborrowers`; CREATE TABLE `deletedborrowers` ( `borrowernumber` int(11) NOT NULL default 0, - `cardnumber` varchar(9) NOT NULL default '', + `cardnumber` varchar(16) NOT NULL default '', `surname` mediumtext NOT NULL, `firstname` text, `title` mediumtext, @@ -913,6 +704,7 @@ CREATE TABLE `deletedborrowers` ( `altcontactcountry` text default NULL, `altcontactphone` varchar(50) default NULL, `smsalertnumber` varchar(50) default NULL, + `privacy` integer(11) DEFAULT '1' NOT NULL, KEY `borrowernumber` (`borrowernumber`), KEY `cardnumber` (`cardnumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -959,11 +751,13 @@ CREATE TABLE `deleteditems` ( `uri` varchar(255) default NULL, `itype` varchar(10) default NULL, `more_subfields_xml` longtext default NULL, - `enumchron` varchar(80) default NULL, + `enumchron` text default NULL, `copynumber` varchar(32) default NULL, + `stocknumber` varchar(32) default NULL, `marc` longblob, PRIMARY KEY (`itemnumber`), KEY `delitembarcodeidx` (`barcode`), + KEY `delitemstocknumberidx` (`stocknumber`), KEY `delitembinoidx` (`biblioitemnumber`), KEY `delitembibnoidx` (`biblionumber`), KEY `delhomebranch` (`homebranch`), @@ -981,6 +775,24 @@ 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` -- @@ -994,13 +806,13 @@ CREATE TABLE hold_fill_targets ( `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`) + 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`) + 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`) + 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`) + CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1064,7 +876,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; @@ -1084,7 +896,7 @@ CREATE TABLE `import_biblios` ( `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`), @@ -1105,7 +917,7 @@ CREATE TABLE `import_items` ( `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`) @@ -1147,12 +959,17 @@ 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, + `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 '', PRIMARY KEY (`branchcode`,`categorycode`,`itemtype`), KEY `categorycode` (`categorycode`), @@ -1201,10 +1018,12 @@ CREATE TABLE `items` ( `uri` varchar(255) default NULL, `itype` varchar(10) default NULL, `more_subfields_xml` longtext default NULL, - `enumchron` varchar(80) default NULL, + `enumchron` text default NULL, `copynumber` varchar(32) default NULL, + `stocknumber` varchar(32) default NULL, PRIMARY KEY (`itemnumber`), UNIQUE KEY `itembarcodeidx` (`barcode`), + KEY `itemstocknumberidx` (`stocknumber`), KEY `itembinoidx` (`biblioitemnumber`), KEY `itembibnoidx` (`biblionumber`), KEY `homebranch` (`homebranch`), @@ -1222,7 +1041,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, @@ -1232,66 +1050,96 @@ CREATE TABLE `itemtypes` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table structure for table `labels_batches` --- - -DROP TABLE IF EXISTS `labels_batches`; -CREATE TABLE `labels_batches` ( - `label_id` int(11) NOT NULL auto_increment, - `batch_id` int(10) NOT NULL default '1', - `item_number` int(11) NOT NULL default '0', - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, - `branch_code` varchar(10) NOT NULL default 'NB', - PRIMARY KEY USING BTREE (`label_id`), - KEY `branch_fk` (`branch_code`), - KEY `item_fk` (`item_number`), - CONSTRAINT `item_fk_constraint` FOREIGN KEY (`item_number`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE, - CONSTRAINT `branch_fk_constraint` FOREIGN KEY (`branch_code`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `labels_layouts` --- - -DROP TABLE IF EXISTS `labels_layouts`; -CREATE TABLE `labels_layouts` ( - `layout_id` int(4) NOT NULL auto_increment, - `barcode_type` char(100) NOT NULL default 'CODE39', - `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', - `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', - PRIMARY KEY USING BTREE (`layout_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - --- --- Table structure for table `labels_templates` --- - -DROP TABLE IF EXISTS `labels_templates`; -CREATE TABLE `labels_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', - PRIMARY KEY (`template_id`), +-- 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`), + 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`), + 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`) +) 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; @@ -1468,13 +1316,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` @@ -1496,9 +1344,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; @@ -1521,15 +1369,17 @@ CREATE TABLE `old_reserves` ( `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `itemnumber` int(11) default NULL, `waitingdate` date default NULL, + `expirationdate` DATE DEFAULT NULL, + `lowestPriority` tinyint(1) NOT NULL, KEY `old_reserves_borrowernumber` (`borrowernumber`), 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; @@ -1624,8 +1474,9 @@ CREATE TABLE `printers_profile` ( `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`) + UNIQUE KEY `printername` (`printer_name`,`template_id`,`paper_bin`,`creator`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -1646,7 +1497,7 @@ CREATE TABLE `repeatable_holidays` ( -- -- Table structure for table `reports_dictionary` --- +-- DROP TABLE IF EXISTS `reports_dictionary`; CREATE TABLE reports_dictionary ( @@ -1693,6 +1544,8 @@ CREATE TABLE `reserves` ( `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `itemnumber` int(11) default NULL, `waitingdate` date default NULL, + `expirationdate` DATE DEFAULT NULL, + `lowestPriority` tinyint(1) NOT NULL, KEY `borrowernumber` (`borrowernumber`), KEY `biblionumber` (`biblionumber`), KEY `itemnumber` (`itemnumber`), @@ -1761,8 +1614,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` -- @@ -1787,7 +1657,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, @@ -1896,6 +1766,7 @@ CREATE TABLE `subscription` ( `staffdisplaycount` VARCHAR(10) NULL, `opacdisplaycount` VARCHAR(10) NULL, `graceperiod` int(11) NOT NULL default '0', + `enddate` date default NULL, PRIMARY KEY (`subscriptionid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1908,7 +1779,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 '', @@ -1924,10 +1795,15 @@ CREATE TABLE `subscriptionhistory` ( DROP TABLE IF EXISTS `subscriptionroutinglist`; CREATE TABLE `subscriptionroutinglist` ( `routingid` int(11) NOT NULL auto_increment, - `borrowernumber` int(11) default NULL, + `borrowernumber` int(11) NOT NULL, `ranking` int(11) default NULL, - `subscriptionid` int(11) default NULL, - PRIMARY KEY (`routingid`) + `subscriptionid` int(11) NOT NULL, + PRIMARY KEY (`routingid`), + UNIQUE (`subscriptionid`, `borrowernumber`), + CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) + ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -1938,7 +1814,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, @@ -1953,6 +1835,14 @@ CREATE TABLE `suggestions` ( `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, + quantity SMALLINT(6) default NULL, + currency VARCHAR(3) default NULL, + price DECIMAL(28,6) default NULL, + total DECIMAL(28,6) default NULL, PRIMARY KEY (`suggestionid`), KEY `suggestedby` (`suggestedby`), KEY `managedby` (`managedby`) @@ -2138,6 +2028,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; @@ -2148,6 +2040,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; @@ -2157,7 +2051,10 @@ CREATE TABLE language_descriptions ( type varchar(25), lang varchar(25), description varchar(255), - KEY `lang` (`lang`) + id int(11) NOT NULL auto_increment, + PRIMARY KEY (`id`), + KEY `lang` (`lang`), + KEY `subtag_type_lang` (`subtag`, `type`, `lang`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- bi-directional support, keyed by script subcode @@ -2193,9 +2090,10 @@ CREATE TABLE `serialitems` ( `serialid` int(11) NOT NULL, UNIQUE KEY `serialitemsidx` (`itemnumber`), KEY `serialitems_sfk_1` (`serialid`), - CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; - + DROP TABLE IF EXISTS `user_permissions`; CREATE TABLE `user_permissions` ( `borrowernumber` int(11) NOT NULL DEFAULT 0, @@ -2272,7 +2170,7 @@ CREATE TABLE `message_transport_types` ( 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 '', + `message_name` varchar(40) NOT NULL default '', `takes_days` tinyint(1) NOT NULL default '0', PRIMARY KEY (`message_attribute_id`), UNIQUE KEY `message_name` (`message_name`) @@ -2342,7 +2240,7 @@ CREATE TABLE branch_transfer_limits ( toBranch varchar(10) NOT NULL, fromBranch varchar(10) NOT NULL, itemtype varchar(10) NULL, - ccode varchar(10) NULL, + ccode varchar(10) NULL, PRIMARY KEY (limitId) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2361,6 +2259,348 @@ CREATE TABLE `item_circulation_alert_preferences` ( KEY `branchcode` (`branchcode`,`categorycode`,`item_type`, `notification`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `messages` +-- +DROP TABLE IF EXISTS `messages`; +CREATE TABLE `messages` ( + `message_id` int(11) NOT NULL auto_increment, + `borrowernumber` int(11) NOT NULL, + `branchcode` varchar(10) 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, + `deliveryplace` varchar(10) default NULL, + `deliverycomment` varchar(255) default NULL, + `billingplace` varchar(10) default 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, + `display` tinyint(1) DEFAULT 1, + 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) NOT NULL default 0, + `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`), + KEY `budget_id` (`budget_id`), + 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 */;