`cn_class` varchar(30) default NULL,
`cn_item` varchar(10) default NULL,
`cn_suffix` varchar(10) default NULL,
- `cn_sort` varchar(30) default NULL,
- `agerestriction` varchar(255) default NULL,
+ `cn_sort` varchar(30) default NULL, -- normalized version of the call number used for sorting
+ `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
`totalissues` int(10),
`marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
PRIMARY KEY (`biblioitemnumber`),
`cn_class` varchar(30) default NULL,
`cn_item` varchar(10) default NULL,
`cn_suffix` varchar(10) default NULL,
- `cn_sort` varchar(30) default NULL,
- `agerestriction` varchar(255) default NULL,
+ `cn_sort` varchar(30) default NULL, -- normalized version of the call number used for sorting
+ `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a)
`totalissues` int(10),
`marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML
PRIMARY KEY (`biblioitemnumber`),
`itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
`wthdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
`itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o)
+ `coded_location_qualifier` varchar(10) default NULL, -- coded location qualifier(MARC21 952$f)
`issues` smallint(6) default NULL, -- number of times this item has been checked out
`renewals` smallint(6) default NULL, -- number of times this item has been renewed
`reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `branchcode` (`branchcode`),
- KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
+ KEY `batch_sequence` (`import_batch_id`, `record_sequence`),
+ KEY `batch_id_record_type` (`import_batch_id`,`record_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
`itemlost` tinyint(1) NOT NULL default 0, -- authorized value defining this item as lost (MARC21 952$1)
`wthdrawn` tinyint(1) NOT NULL default 0, -- authorized value defining this item as withdrawn (MARC21 952$0)
`itemcallnumber` varchar(255) default NULL, -- call number for this item (MARC21 952$o)
+ `coded_location_qualifier` varchar(10) default NULL, -- coded location qualifier(MARC21 952$f)
`issues` smallint(6) default NULL, -- number of times this item has been checked out/issued
`renewals` smallint(6) default NULL, -- number of times this item has been renewed
`reserves` smallint(6) default NULL, -- number of times this item has been placed on hold/reserved
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
--- Table structure for table `nozebra`
---
-
-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;
-
---
-- Table structure for table `oai_sets`
--
-- so MyISAM is better in this case
DROP TABLE IF EXISTS `pending_offline_operations`;
-CREATE TABLE `pending_offline_operations` (
- `operationid` int(11) NOT NULL AUTO_INCREMENT,
- `userid` varchar(30) NOT NULL,
- `branchcode` varchar(10) NOT NULL,
+CREATE TABLE pending_offline_operations (
+ operationid int(11) NOT NULL AUTO_INCREMENT,
+ userid varchar(30) NOT NULL,
+ branchcode varchar(10) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`action` varchar(10) NOT NULL,
- `barcode` varchar(20) NOT NULL,
- `cardnumber` varchar(16) DEFAULT NULL,
- PRIMARY KEY (`operationid`)
-) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-
+ barcode varchar(20) DEFAULT NULL,
+ cardnumber varchar(16) DEFAULT NULL,
+ amount decimal(28,6) DEFAULT NULL,
+ PRIMARY KEY (operationid)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
`itemtype` varchar(10) default NULL, -- foreign key from the itemtypes table, links transaction to a specific item type
`borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower
`associatedborrower` int(11) default NULL,
+ `ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code
KEY `timeidx` (`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`opacdisplaycount` VARCHAR(10) NULL,
`graceperiod` int(11) NOT NULL default '0',
`enddate` date default NULL,
+ `closed` INT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`subscriptionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`itemnumber` int(11) NOT NULL, -- the item number for this item (items.itemnumber)
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order item was last touched
PRIMARY KEY (`itemnumber`),
- KEY `ordernumber` (`ordernumber`)
+ KEY `ordernumber` (`ordernumber`),
+ CONSTRAINT aqorders_items_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+--
+-- Table structure for table categories_branches
+--
+
+DROP TABLE IF EXISTS categories_branches;
+CREATE TABLE categories_branches( -- association table between categories and branches
+ categorycode VARCHAR(10),
+ branchcode VARCHAR(10),
+ FOREIGN KEY (categorycode) REFERENCES categories(categorycode) ON DELETE CASCADE,
+ FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
+) ENGINE=INNODB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table authorised_values_branches
+--
+
+DROP TABLE IF EXISTS authorised_values_branches;
+CREATE TABLE authorised_values_branches( -- association table between authorised_values and branches
+ av_id INTEGER,
+ branchcode VARCHAR(10),
+ FOREIGN KEY (av_id) REFERENCES authorised_values(id) ON DELETE CASCADE,
+ FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
+) ENGINE=INNODB DEFAULT CHARSET=utf8;
+
+
+--
+-- Table structure for table borrower_attribute_types_branches
+--
+
+DROP TABLE IF EXISTS borrower_attribute_types_branches;
+CREATE TABLE borrower_attribute_types_branches( -- association table between borrower_attribute_types and branches
+ bat_code VARCHAR(10),
+ b_branchcode VARCHAR(10),
+ FOREIGN KEY (bat_code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE,
+ FOREIGN KEY (b_branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE
+) ENGINE=INNODB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table `borrower_modifications`
+--
+
+CREATE TABLE IF NOT EXISTS `borrower_modifications` (
+ `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `verification_token` varchar(255) NOT NULL DEFAULT '',
+ `borrowernumber` int(11) NOT NULL DEFAULT '0',
+ `cardnumber` varchar(16) DEFAULT NULL,
+ `surname` mediumtext,
+ `firstname` text,
+ `title` mediumtext,
+ `othernames` mediumtext,
+ `initials` text,
+ `streetnumber` varchar(10) DEFAULT NULL,
+ `streettype` varchar(50) DEFAULT NULL,
+ `address` mediumtext,
+ `address2` text,
+ `city` mediumtext,
+ `state` text,
+ `zipcode` varchar(25) DEFAULT NULL,
+ `country` text,
+ `email` mediumtext,
+ `phone` text,
+ `mobile` varchar(50) DEFAULT NULL,
+ `fax` mediumtext,
+ `emailpro` text,
+ `phonepro` text,
+ `B_streetnumber` varchar(10) DEFAULT NULL,
+ `B_streettype` varchar(50) DEFAULT NULL,
+ `B_address` varchar(100) DEFAULT NULL,
+ `B_address2` text,
+ `B_city` mediumtext,
+ `B_state` text,
+ `B_zipcode` varchar(25) DEFAULT NULL,
+ `B_country` text,
+ `B_email` text,
+ `B_phone` mediumtext,
+ `dateofbirth` date DEFAULT NULL,
+ `branchcode` varchar(10) DEFAULT NULL,
+ `categorycode` varchar(10) DEFAULT NULL,
+ `dateenrolled` date DEFAULT NULL,
+ `dateexpiry` date DEFAULT NULL,
+ `gonenoaddress` tinyint(1) DEFAULT NULL,
+ `lost` tinyint(1) DEFAULT NULL,
+ `debarred` date DEFAULT NULL,
+ `debarredcomment` varchar(255) DEFAULT NULL,
+ `contactname` mediumtext,
+ `contactfirstname` text,
+ `contacttitle` text,
+ `guarantorid` int(11) DEFAULT NULL,
+ `borrowernotes` mediumtext,
+ `relationship` varchar(100) DEFAULT NULL,
+ `ethnicity` varchar(50) DEFAULT NULL,
+ `ethnotes` varchar(255) DEFAULT NULL,
+ `sex` varchar(1) DEFAULT NULL,
+ `password` varchar(30) DEFAULT NULL,
+ `flags` int(11) DEFAULT NULL,
+ `userid` varchar(75) DEFAULT NULL,
+ `opacnote` mediumtext,
+ `contactnote` varchar(255) DEFAULT NULL,
+ `sort1` varchar(80) DEFAULT NULL,
+ `sort2` varchar(80) DEFAULT NULL,
+ `altcontactfirstname` varchar(255) DEFAULT NULL,
+ `altcontactsurname` varchar(255) DEFAULT NULL,
+ `altcontactaddress1` varchar(255) DEFAULT NULL,
+ `altcontactaddress2` varchar(255) DEFAULT NULL,
+ `altcontactaddress3` varchar(255) DEFAULT NULL,
+ `altcontactstate` text,
+ `altcontactzipcode` varchar(50) DEFAULT NULL,
+ `altcontactcountry` text,
+ `altcontactphone` varchar(50) DEFAULT NULL,
+ `smsalertnumber` varchar(50) DEFAULT NULL,
+ `privacy` int(11) DEFAULT NULL,
+ PRIMARY KEY (`verification_token`,`borrowernumber`),
+ KEY `verification_token` (`verification_token`),
+ KEY `borrowernumber` (`borrowernumber`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table linktracker
+-- This stores clicks to external links
+--
+
+DROP TABLE IF EXISTS linktracker;
+CREATE TABLE linktracker (
+ id int(11) NOT NULL AUTO_INCREMENT, -- primary key identifier
+ biblionumber int(11) DEFAULT NULL, -- biblionumber of the record the link is from
+ itemnumber int(11) DEFAULT NULL, -- itemnumber if applicable that the link was from
+ borrowernumber int(11) DEFAULT NULL, -- borrowernumber who clicked the link
+ url text, -- the link itself
+ timeclicked datetime DEFAULT NULL, -- the date and time the link was clicked
+ PRIMARY KEY (id),
+ KEY bibidx (biblionumber),
+ KEY itemidx (itemnumber),
+ KEY borridx (borrowernumber),
+ KEY dateidx (timeclicked)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Table structure for table 'plugin_data'
+--
+
+CREATE TABLE IF NOT EXISTS plugin_data (
+ plugin_class varchar(255) NOT NULL,
+ plugin_key varchar(255) NOT NULL,
+ plugin_value text,
+ PRIMARY KEY (plugin_class,plugin_key)
+) 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 */;
projects / koha-ffzg.git / blobdiff