X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=installer%2Fdata%2Fmysql%2Fkohastructure.sql;h=7a416172bfa7b463dca2eed18d0a8d724f0c86d4;hb=88b3711ca918877796d8577c54efe3f0aba96673;hp=a4b138673c557d9bf5767e043ca9bd5c786ce212;hpb=96fc46e2e1621effac5f51ff8d6a9ac79fb0551b;p=koha.git diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index a4b138673c..7a416172bf 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -15,319 +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 `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 '', - `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, - `gstrate` decimal(6,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 `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_amount_sublevel` 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 `auth_header` -- @@ -368,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; @@ -413,11 +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, + `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; -- @@ -495,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; @@ -568,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; @@ -591,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; -- @@ -612,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, @@ -648,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, @@ -719,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, @@ -727,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` -- @@ -778,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, @@ -849,7 +564,7 @@ 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; @@ -928,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, @@ -989,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; @@ -1035,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`), @@ -1057,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` -- @@ -1070,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; @@ -1140,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; @@ -1160,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`), @@ -1181,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`) @@ -1223,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`), @@ -1277,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`), @@ -1298,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, @@ -1308,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; @@ -1544,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` @@ -1572,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; @@ -1597,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; @@ -1700,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; -- @@ -1722,7 +1497,7 @@ CREATE TABLE `repeatable_holidays` ( -- -- Table structure for table `reports_dictionary` --- +-- DROP TABLE IF EXISTS `reports_dictionary`; CREATE TABLE reports_dictionary ( @@ -1769,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`), @@ -1837,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` -- @@ -1863,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, @@ -1972,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; @@ -1984,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 '', @@ -2000,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; -- @@ -2014,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, @@ -2029,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`) @@ -2239,7 +2053,8 @@ CREATE TABLE language_descriptions ( description varchar(255), id int(11) NOT NULL auto_increment, PRIMARY KEY (`id`), - KEY `lang` (`lang`) + KEY `lang` (`lang`), + KEY `subtag_type_lang` (`subtag`, `type`, `lang`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- bi-directional support, keyed by script subcode @@ -2275,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, @@ -2313,150 +2129,478 @@ CREATE TABLE `tmp_holdsqueue` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- Table structure for table `message_queue` +-- 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(40) 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` +-- +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 `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 +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 `message_transport_types` +-- Table structure for table `aqbudgets` -- -DROP TABLE IF EXISTS `message_transport_types`; -CREATE TABLE `message_transport_types` ( - `message_transport_type` varchar(20) NOT NULL, - PRIMARY KEY (`message_transport_type`) +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 `message_attributes` +-- Table structure for table `aqbudgetperiods` -- -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`) + +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 `message_transports` +-- Table structure for table `aqbudgets_planning` -- -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 +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 `borrower_message_preferences` +-- Table structure for table 'aqcontract' -- -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; +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 `borrower_message_transport_preferences` +-- Table structure for table `aqorderdelivery` -- -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 +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 the table branch_transfer_limits +-- Table structure for table `aqorders` -- -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) +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 `item_circulation_alert_preferences` +-- Table structure for table `aqorders_items` -- -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`) +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 `messages` +-- Table structure for table `fieldmapping` -- -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`) +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 */;