-- MySQL dump 10.10
--
-- Host: localhost    Database: union_db
-- ------------------------------------------------------
-- Server version	5.0.22

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

/*
CREATE TABLE `opl_item` (
  `id` int(32) unsigned NOT NULL auto_increment,
  
`barcode` varchar(32) default NULL,
  
`lid` tinyint(3) unsigned default '0',
  
PRIMARY KEY  (`id`),
  
KEY `barcode` (`barcode`,`lid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


*/
--
-- Table structure for table `opl_itemStatusTracking`
--

DROP TABLE IF EXISTS `opl_itemStatusTracking`;
CREATE TABLE `opl_itemStatusTracking` (
  `id` int(32) unsigned NOT NULL auto_increment,
  `add` int(11)  default '0',
  `del` int(11)  default '0',
  `found` int(11)  default '0',
  `lost` int(11)  default '0',
  `missing` int(11)  default '0',
  `damaged` int(11)  default '0',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_marcRecStatusTracking`
--

DROP TABLE IF EXISTS `opl_marcRecStatusTracking`;
CREATE TABLE `opl_marcRecStatusTracking` (
  `id` int(32) unsigned NOT NULL auto_increment,
  `add` int(11)  default '0',
  `del` int(11)  default '0',
  `found` int(11)  default '0',
  `lost` int(11)  default '0',
  `missing` int(11)  default '0',
  `damaged` int(11)  default '0',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_item`
--

DROP TABLE IF EXISTS `opl_item`;
CREATE TABLE `opl_itemtest` (
  `itemid` bigint(20) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `barcode` varchar(50) NOT NULL default '',
  `tid` varchar(255) NOT NULL,
  `available` tinyint(4) default '1',
  `sf852_h` varchar(255) default NULL,
  `sf852_k` varchar(255) default NULL,
  `sf852_i` varchar(255) default NULL,
  `sf852_m` varchar(255) default NULL,
  `callNumber` varchar(255) default NULL,
  `price` varchar(255) default NULL,
  `status` enum('active','deleted','purged','damaged','lost') NOT NULL default 'active',
  `importId` int(10) unsigned NOT NULL default '0',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`itemid`),
  UNIQUE KEY `u_lb` (`lid`,`barcode`),
  KEY `i_rid` (`rid`,`itemid`),
  KEY `i_status` (`status`),
  KEY `i_ls` (`lid`,`status`),
  KEY `i_iid` (`importId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_closingDate`
--

DROP TABLE IF EXISTS `opl_closingDate`;
CREATE TABLE `opl_closingDate` (
  `lid` smallint(5) unsigned NOT NULL default '0',
  `dClosed` date NOT NULL default '0000-00-00',
  `dLabel` text NOT NULL,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`,`dClosed`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_deletedItem`
--

DROP TABLE IF EXISTS `opl_deletedItem`;
CREATE TABLE `opl_deletedItem`(
  `id` int(32) unsigned NOT NULL auto_increment,
  `req_id` int(11) NOT NULL default '0',
  `src_id` int(11) NOT NULL default '0',
  `barcode` varchar(50) NOT NULL default '',
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL,
  `data` text,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `u_rbl` (`req_id`,`barcode`,`lid`),
  KEY `i_rsbl` (`req_id`,`src_id`,`barcode`,`lid`),
  KEY `i_rd` (`req_id`,`data`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_deletedRecord`
--

DROP TABLE IF EXISTS `opl_deletedRecord`;
CREATE TABLE `opl_deletedRecord` (
  `id` int(32) unsigned NOT NULL auto_increment,
  `req_id` int(11) not null default '0',
  `rid` int(10) unsigned NOT NULL default '0',
  `data` text,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `u_rr` (`req_id`,`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_deletionRequest`
--

DROP TABLE IF EXISTS `opl_deletionRequest`;
CREATE TABLE `opl_deletionRequest` (
  `req_id` int(11) NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL,
  `dRequest` datetime NOT NULL,
  `dFinish` datetime default NULL,
  `req_group` enum('all','library','set','complement','none') NOT NULL default 'none',
  `status` enum('new','accepted','processing','done','aborted') NOT NULL default 'new',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`req_id`),
  KEY `i_lus` (`lid`,`uid`,`status`),
  KEY `i_ls` (`lid`,`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_deletionSource`
--

DROP TABLE IF EXISTS `opl_deletionSource`;
CREATE TABLE `opl_deletionSource` (
  `req_id` int(11) NOT NULL default '0',
  `src_id` int(11) NOT NULL auto_increment,
  `source` enum('none','scanner','text_file','marc21_file') NOT NULL default 'scanner',
  `file_name` varchar(255) default NULL,
  `status` enum('new','loaded') NOT NULL default 'new',
  `mesgDigest` varchar(128) default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `u_rs` (`req_id`,`src_id`),
  KEY `i_rs` (`req_id`,`status`),
  KEY `i_mesgDigest` (`mesgDigest`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_extDatabase`
--

DROP TABLE IF EXISTS `opl_extDatabase`;
CREATE TABLE `opl_extDatabase` (
  `lid` smallint(5) unsigned NOT NULL default '0',
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `url` text NOT NULL,
  `param` text NOT NULL,
  `rank` tinyint(3) unsigned default '0',
  `hidden` tinyint(3) unsigned default '0',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_file`
--

DROP TABLE IF EXISTS `opl_file`;
CREATE TABLE `opl_file` (
  `lid` smallint(5) unsigned NOT NULL default '0',
  `fileType` enum('image','audio') NOT NULL default 'image',
  `fileName` varchar(255) NOT NULL default '',
  `mimeType` varchar(255) NOT NULL default '',
  `fileData` mediumblob NOT NULL,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`,`fileType`,`fileName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_illFill`
--

DROP TABLE IF EXISTS `opl_illFill`;
CREATE TABLE `opl_illFill` (
  `illId` int(10) unsigned NOT NULL default '0',
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned default NULL,
  `status` enum('bOpen','lClosed','bCancelled','lRejected','lFilled','bReturned','lReceived','lost','bFound') default 'bOpen',
  `msgLid` int(10) unsigned NOT NULL default '0',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `u_reply` (`illId`,`lid`),
  KEY `i_illId` (`illId`),
  KEY `i_lid` (`lid`),
  KEY `i_uid` (`uid`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_illHistory`
--

DROP TABLE IF EXISTS `opl_illHistory`;
CREATE TABLE `opl_illHistory` (
  `hid` int(10) unsigned NOT NULL auto_increment,
  `illId` int(10) unsigned NOT NULL default '0',
  `lid` smallint(5) unsigned default NULL,
  `uid` int(10) unsigned NOT NULL default '0',
  `role` enum('requester','responder') default 'requester',
  `action` enum('request','note','cancel','return','fill','reject','reqRenewal','renew','receive','remind','reportLost','reportFound') default 'request',
  `public_note` text,
  `private_note` text,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`hid`),
  KEY `i_illId` (`illId`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_illRequest`
--

DROP TABLE IF EXISTS `opl_illRequest`;
CREATE TABLE `opl_illRequest` (
  `illId` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `rid` int(10) unsigned NOT NULL default '0',
  `description` text,
  `dRequest` datetime NOT NULL default '0000-00-00 00:00:00',
  `dExpiry` datetime NOT NULL default '0000-00-00 00:00:00',
  `dFill` datetime default NULL,
  `dDue` datetime default NULL,
  `status` enum('bOpen','bCancelled','lRejected','lFilled','bReturned','lReceived','lost','bFound') default 'bOpen',
  `msgLid` int(10) unsigned NOT NULL default '0',
  `idKey` varchar(128) NOT NULL default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`illId`),
  UNIQUE KEY `u_request` (`uid`,`dRequest`),
  KEY `i_lid` (`lid`),
  KEY `i_uid` (`uid`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_itemHistory`
--

DROP TABLE IF EXISTS `opl_itemHistory`;
CREATE TABLE `opl_itemHistory` (
  `itemid` bigint(20) unsigned NOT NULL default '0',
  `event` enum('imported','restored','deleted','damaged','lost','found','sf852abFixed','moved','updated') default NULL,
  `uid` int(10) unsigned NOT NULL default '0',
  `note` text,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY `i_ie` (`itemid`,`event`),
  KEY `i_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_itemInfo`
--

DROP TABLE IF EXISTS `opl_itemInfo`;
CREATE TABLE `opl_itemInfo` (
  `itemid` bigint(20) unsigned NOT NULL default '0',
  `sf852Code` char(1) NOT NULL,
  `sf852Data` varchar(255) NOT NULL,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`itemid`,`sf852Code`,`sf852Data`),
  FULLTEXT KEY `f_data` (`sf852Data`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_itemType`
--

DROP TABLE IF EXISTS `opl_itemType`;
CREATE TABLE `opl_itemType` (
  `lid` smallint(5) unsigned NOT NULL default '0',
  `tid` varchar(255) NOT NULL,
  `description` text,
  `isDefault` tinyint(3) unsigned default '0',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`,`tid`),
  KEY `i_default` (`lid`,`isDefault`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_itemTypeChange`
--

DROP TABLE IF EXISTS `opl_itemTypeChange`;
CREATE TABLE `opl_itemTypeChange` (
  `lid` smallint(5) unsigned NOT NULL default '0',
  `cid` int(10) unsigned NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL,
  `dRequest` datetime NOT NULL,
  `dFinish` datetime default NULL,
  `oldTypeId` varchar(100) NOT NULL,
  `newTypeId` varchar(100) NOT NULL,
  `cReplaced` int(10) unsigned default '0',
  `cTotal` int(10) unsigned NOT NULL,
  `status` enum('waiting','invalid','processing','done') NOT NULL default 'waiting',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`,`cid`),
   KEY `i_cid` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_itemTypeParam`
--

DROP TABLE IF EXISTS `opl_itemTypeParam`;
CREATE TABLE `opl_itemTypeParam` (
  `lid` smallint(5) unsigned NOT NULL default '0',
  `itemTypeId` varchar(255) NOT NULL,
  `userCategoryId` smallint(5) unsigned NOT NULL,
  `loanPeriod` int(10) unsigned NOT NULL,
  `renewalPeriod` int(10) unsigned NOT NULL,
  `reservePeriod` int(10) unsigned NOT NULL,
  `holdPeriod` int(10) unsigned NOT NULL,
  `gracePeriod` int(10) unsigned NOT NULL,
  `maxRenewal` tinyint(3) unsigned default '0',
  `fine` float NOT NULL default '0',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`,`itemTypeId`,`userCategoryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



--
-- Table structure for table `opl_libGroup`
--

DROP TABLE IF EXISTS `opl_libGroup`;
CREATE TABLE `opl_libGroup` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL ,
  `gid` int(10) unsigned NOT NULL ,
  `note` varchar(50)  default '', 
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
  KEY `i_id` (`id`),
  KEY `i_lid` (`lid`)
  KEY `i_gid` (`gid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_libInfo`
--

DROP TABLE IF EXISTS `opl_libInfo`;
CREATE TABLE `opl_libInfo` (
  `lid` smallint(5) unsigned NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL default '0',
  `level` smallint(5) unsigned NOT NULL default '0',
  `path` varchar(255) NOT NULL default '/',
  `code` varchar(15) NOT NULL default '',
  `name` text NOT NULL,
  `a` varchar(255) NOT NULL ,
  `b` varchar(255) NOT NULL ,
  `addrLine1` varchar(255)  default '',
  `addrLine2` varchar(255)  default '',
  `city` varchar(255)  default '',
  `state` varchar(255)  default '',
  `zip` varchar(50)  default '',
  `phone` varchar(50)  default '',
  `fax` varchar(50)  default '',
  `url` varchar(255) NOT NULL default '',
  `status` enum('active','disabled') NOT NULL default 'active',
  `contid` int(10) ,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`),
  UNIQUE KEY `u_pc` (`path`,`code`),
  KEY `i_code` (`code`),
  KEY `i_level` (`level`),
  KEY `i_a` (`a`),
  KEY `i_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_libContact`
--

DROP TABLE IF EXISTS `opl_libContact`;
CREATE TABLE `opl_libContact` (
  `contid` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL ,
  `jobtitle` varchar(50)  default '',
  `firstname` varchar(255)  default '',
  `lastname` varchar(255)  default '',
  `email` varchar(255)  default '',
  `phone` varchar(50)  default '',
  `fax` varchar(50)  default '', 
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
  KEY `i_contid` (`contid`),
  KEY `i_lid` (`lid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_libLog`
--

DROP TABLE IF EXISTS `opl_libLog`;
CREATE TABLE `opl_libLog` (
  `logid` int(10) unsigned NOT NULL auto_increment,
  `lidLogin` smallint(5) unsigned NOT NULL ,
  `lidTarget` smallint(5) unsigned NOT NULL ,
  `uid` int(10) unsigned NOT NULL,
  `action` text  NOT NULL,
  `url` varchar(255)  default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
  KEY `i_logid` (`logid`),
  KEY `i_lidLogin` (`lidLogin`),
  KEY `i_lidTarget` (`lidTarget`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




--
-- Table structure for table `opl_libHistory`
--

DROP TABLE IF EXISTS `opl_libHistory`;
CREATE TABLE `opl_libHistory` (
  `hid` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL ,
  `old_lid` smallint(5) unsigned NOT NULL ,  
  `pid` int(10) unsigned NOT NULL default '0',
  `level` smallint(5) unsigned NOT NULL default '0',
  `path` varchar(255) NOT NULL default '/',
  `code` varchar(15) NOT NULL default '',
  `name` text NOT NULL,
  `a` varchar(255) NOT NULL ,
  `b` varchar(255) NOT NULL ,
  `status` enum('active','disabled') NOT NULL default 'disabled',
  `uid` int(10) unsigned NOT NULL ,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`),
  UNIQUE KEY `u_pc` (`path`,`code`),
  KEY `i_hid` (`hid`),
  KEY `i_code` (`code`),
  KEY `i_level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_libHierachy`
--

DROP TABLE IF EXISTS `opl_libHierachy`;
CREATE TABLE `opl_libHierachy` (
  `hid` int(10) unsigned NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL default '0',
  `level` smallint(5) unsigned NOT NULL default '0',
  `name` varchar(255)  NOT NULL,
  `description` varchar(255) ,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
  PRIMARY KEY  (`pid`),
  KEY `i_hid` (`hid`),
  KEY `i_level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



--
-- Table structure for table `opl_marcDelete`
--

DROP TABLE IF EXISTS `opl_marcDelete`;
CREATE TABLE `opl_marcDelete` (
  `did` int(10) unsigned NOT NULL auto_increment,
   `lid` smallint(5) unsigned NOT NULL default '0',
  `rid` int(10) unsigned NOT NULL,
  `uid` int(10) unsigned NOT NULL,
  `content` mediumtext,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_marcDuplicate`
--

DROP TABLE IF EXISTS `opl_marcDuplicate`;
CREATE TABLE `opl_marcDuplicate` (
  `iid` int(10) unsigned NOT NULL default '0',
  `mid` int(10) unsigned NOT NULL auto_increment,
  `content` mediumtext,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY `i_mid` (`mid`),
  PRIMARY KEY  (`iid`,`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_message`
--

DROP TABLE IF EXISTS `opl_message`;
CREATE TABLE `opl_message` (
  `name` varchar(50) NOT NULL,
  `message` text,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_postImport`
--

DROP TABLE IF EXISTS `opl_postImport`;
CREATE TABLE `opl_postImport` (
  `iid` int(10) unsigned NOT NULL,
  `sf852a` varchar(20) NOT NULL default '',
  `sf852b` varchar(20) NOT NULL default '',
  `count` int(10) unsigned NOT NULL default '1',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY `i_iab` (`iid`,`sf852a`,`sf852b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_preference`
--

DROP TABLE IF EXISTS `opl_preference`;
CREATE TABLE `opl_preference` (
  `lid` smallint(5) unsigned NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `value` text NOT NULL,
  `valueSet` text NOT NULL,
  `description` text NOT NULL,
  `group_order` int(10) unsigned NOT NULL default '0',
  `hidden` tinyint(3) unsigned NOT NULL default '0',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`,`name`),
  UNIQUE KEY `u_name` (`lid`,`name`),
  KEY `i_hidden` (`lid`,`hidden`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



--
-- Table structure for table `opl_marcRecord`
--

DROP TABLE IF EXISTS `opl_marcRecord`;
CREATE TABLE `opl_marcRecord` (
  `rid` int(10) unsigned NOT NULL auto_increment,
  `title` text,
  `author` text,
  `isbn` varchar(255) default NULL,
  `pubPlace` varchar(255) default NULL,
  `pubName` varchar(255) default NULL,
  `pubDate` varchar(255) default NULL,
  `marc21` mediumtext,
  `zIndexed` tinyint(3) unsigned NOT NULL default '0',
  `status` enum('none','active','disabled','deleted','purged') NOT NULL default 'none',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`rid`),
  KEY `i_zIndexed` (`zIndexed`),
  KEY `i_status` (`status`),
  KEY `i_title` (`title`(100)),
  KEY `i_isbn` (`isbn`),
  KEY `i_marc21` (`marc21`(50000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_marcRecVerCtrl`
--

DROP TABLE IF EXISTS `opl_marcRecVerCtrl`;
CREATE TABLE `opl_marcRecVerCtrl` (
  `vid` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `version` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL,
  `diff` mediumtext,
  `orginal` mediumtext,
  `source` enum('globalEditor','import','marcEditor') NOT NULL default 'marcEditor',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`vid`),
  KEY `i_uid` (`lid`,`uid`),
  KEY `i_rid` (`rid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_marcRecordIn`
--

DROP TABLE IF EXISTS `opl_marcRecordIn`;
CREATE TABLE `opl_marcRecordIn` (
  `iid` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL,
  `library` varchar(255) NOT NULL default '',
  `sf852Code` char(1) default NULL,
  `sf852Data` varchar(255) default NULL,
  `mode` enum('autoMerge','manualMerge','new') NOT NULL default 'autoMerge',
  `to852` enum('yes','no') NOT NULL default 'yes',
  `remove949` enum('yes','no') NOT NULL default 'yes',
  `cImported` int(10) unsigned NOT NULL default '0',
  `cMerged` int(10) unsigned NOT NULL default '0',
  `cProcessed` int(10) unsigned NOT NULL default '0',
  `cBad` int(10) unsigned NOT NULL default '0',
  `cTotal` int(10) unsigned NOT NULL default '0',
  `dImport` datetime default NULL,
  `dUpload` datetime NOT NULL,
  `mesgDigest` varchar(128) default '',
  `status` enum('uploaded','validated','skipped','scanned','rejected','accepted','indexing','done') NOT NULL default 'uploaded',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`iid`),
  KEY `i_lu` (`uid`),
  KEY `i_library` (`library`),
  KEY `i_mesgDigest` (`mesgDigest`),
  KEY `i_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_marcRecordOut`
--

DROP TABLE IF EXISTS `opl_marcRecordOut`;
CREATE TABLE `opl_marcRecordOut` (
  `eid` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL,
  `library` varchar(255) NOT NULL default '',
  `dRequest` datetime NOT NULL,
  `eType` enum('date','rid','all') NOT NULL,
  `eFrom` varchar(50) NOT NULL,
  `eTo` varchar(50) NOT NULL,
  `dExpiry` datetime NOT NULL,
  `dFinish` datetime default NULL,
  `cRecord` int(10) unsigned default NULL,
  `cHolding` int(10) unsigned default NULL,
  `cFixedRec` int(10) unsigned default NULL,
  `status` enum('waiting','processing','done') NOT NULL default 'waiting',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`eid`),
  KEY `i_lu` (`lid`,`uid`),
  KEY `i_library` (`library`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_session`
--

DROP TABLE IF EXISTS `opl_session`;
CREATE TABLE `opl_session` (
  `id` bigint(20)  unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `sessionid` char(128) NOT NULL default '',
  `uid` int(10) unsigned NOT NULL default '0',
  `ipaddress` varchar(63) NOT NULL default '',
  `dt_in` datetime NOT NULL default '0000-00-00 00:00:00',
  `dt_out` datetime default NULL,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`sessionid`),
  KEY `i_id` (`id`),
  KEY `i_lid` (`lid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_template`
--

DROP TABLE IF EXISTS `opl_template`;
CREATE TABLE `opl_template` (
  `lid` smallint(5) unsigned NOT NULL default '0',
  `tid` tinyint(3) unsigned NOT NULL auto_increment,
  `rank` tinyint(3) unsigned NOT NULL default '1',
  `name` varchar(255) default NULL,
  `isDefault` tinyint(3) unsigned NOT NULL default '0',
  `content` mediumtext,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
   KEY `i_tid` (`tid`),
  PRIMARY KEY  (`lid`,`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_userCategory`
--

DROP TABLE IF EXISTS `opl_userCategory`;
CREATE TABLE `opl_userCategory` (
  `lid` smallint(5) unsigned NOT NULL default '0',
  `cid` smallint(5) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `cat_order` tinyint(3) unsigned NOT NULL default '0',
  `isDefault` tinyint(3) unsigned NOT NULL default '0',
  `permission` set('addUser','editUser','deleteUser','editPreference','bibliography','loan','return','reserve','selfReserve','editMarc','editHolding','deleteHolding','inventory','report','notice','setFine','illRequest','illFill') default NULL,
  `maxLoan` smallint(5) unsigned NOT NULL default '0',
  `maxReserve` smallint(5) unsigned NOT NULL default '0',
  `maxRenewal` smallint(5) unsigned NOT NULL default '0',
  `marcField` text,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`,`cid`),
  UNIQUE KEY `u_name` (`lid`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_userHistory`
--

DROP TABLE IF EXISTS `opl_userHistory`;
CREATE TABLE `opl_userHistory` (
  `id` bigint(20)  unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `action` enum('add','edit','delete') default NULL,
  `dataGroup` enum('info','login','reference') default NULL,
  `status` enum('active','suspended','deleted') default NULL,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY `i_id` (`id`),
  KEY `i_uid` (`uid`),
  KEY `i_action` (`action`),
  KEY `i_dataGroup` (`dataGroup`),
  KEY `i_status` (`status`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_userInfo`
--

DROP TABLE IF EXISTS `opl_userInfo`;
CREATE TABLE `opl_userInfo` (
  `uid` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `title` varchar(50) NOT NULL default '',
  `firstname` varchar(255) NOT NULL default '',
  `lastname` varchar(255) NOT NULL default '',
  `addrLine1` varchar(255) NOT NULL default '',
  `addrLine2` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `state` varchar(255) NOT NULL default '',
  `zip` varchar(50) NOT NULL default '',
  `phone` varchar(50) NOT NULL default '',
  `fax` varchar(50) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `birthday` date default NULL,
  `gender` enum('female','male') default NULL,
  `reference` varchar(255) NOT NULL default ',',
  `source` enum('file','user') default NULL,
  `sourceId` int(10) unsigned default '0',
  `digest` varchar(128) NOT NULL default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`uid`),
  KEY `i_firstname` (`firstname`),
  KEY `i_lastname` (`lastname`),
  KEY `i_reference` (`reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_userLogin`
--

DROP TABLE IF EXISTS `opl_userLogin`;
CREATE TABLE `opl_userLogin` (
  `id` bigint(20)  unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `sid` varchar(50) default NULL,
  `barcode` varchar(50) default NULL,
  `username` varchar(50) default NULL,
  `password` varchar(86) NOT NULL default '',
  `expiryDate` datetime default NULL,
  `status` enum('active','suspended','deleted') NOT NULL default 'active',
  `cid` smallint(5) unsigned NOT NULL default '0',
  `building` varchar(50) NOT NULL default '',
  `homeroom` varchar(50) NOT NULL default '',
  `teacher` varchar(255) NOT NULL default '',
  `grade` varchar(255) NOT NULL default '',
  `gradYear` smallint(5) unsigned default NULL,
  `notes` text NOT NULL,
  `prefLang` varchar(100) NOT NULL default 'en_US.UTF-8',
  `prefTheme` varchar(255) NOT NULL default 'opals',
  `permission` set('addUser','editUser','deleteUser','editPreference','bibliography','loan','return','reserve','selfReserve','editMarc','editHolding','deleteHolding','inventory','report','notice','setFine','illRequest','illFill') default NULL,
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `u_username` (`username`),
  UNIQUE KEY `u_sid` (`lid`,`sid`),
  UNIQUE KEY `u_barcode` (`lid`,`barcode`),
  KEY `i_id` (`id`),
  KEY `i_cid` (`lid`,`cid`),
  KEY `i_building` (`lid`,`building`),
  KEY `i_homeroom` (`lid`,`homeroom`),
  KEY `i_teacher` (`lid`,`teacher`),
  KEY `i_grade` (`lid`,`grade`),
  KEY `i_gradYear` (`lid`,`gradYear`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



--
-- Table structure for table `opl_userStaff`
--

DROP TABLE IF EXISTS `opl_userStaff`;
CREATE TABLE `opl_userStaff` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `sid` varchar(50) default NULL,
  `department` varchar(50)  default '',
  `jobtitle` varchar(50) default '',
  `notes` varchar(255) default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `u_sid` (`lid`,`sid`),
  UNIQUE KEY `u_uid` (`lid`,`uid`),
  KEY `i_id` (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_userLibrarian`
--

DROP TABLE IF EXISTS `opl_userLibrarian`;
CREATE TABLE `opl_userLibrarian` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `lnid` varchar(50) default NULL,
  `department` varchar(50)  default '',
  `jobtitle` varchar(50) default '',
  `notes` varchar(255) default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `u_sid` (`lid`,`lnid`),
  UNIQUE KEY `u_uid` (`lid`,`uid`),
  KEY `i_id` (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



--
-- Table structure for table `opl_userTeacher`
--

DROP TABLE IF EXISTS `opl_userTeacher`;
CREATE TABLE `opl_userTeacher` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `tid` varchar(50) default NULL,
  `department` varchar(50)  default '',
  `jobtitle` varchar(50) default '',
  `notes` varchar(255) default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `u_tid` (`lid`,`tid`),
  UNIQUE KEY `u_uid` (`lid`,`uid`),
  KEY `i_id` (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



--
-- Table structure for table `opl_userStudent`
--

DROP TABLE IF EXISTS `opl_userStudent`;
CREATE TABLE `opl_userStudent` (
  `id` bigint(20)  unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `sid` varchar(50) NOT NULL default '',
  `barcode` varchar(50) default NULL,
  `building` varchar(50) NOT NULL default '',
  `homeroom` varchar(50) NOT NULL default '',
  `teacher` varchar(255) NOT NULL default '',
  `grade` varchar(255) NOT NULL default '',
  `gradYear` smallint(5) unsigned default NULL,
  `notes` varchar(255) default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `u_sid` (`lid`,`sid`),
  UNIQUE KEY `u_uid` (`lid`,`uid`),
  UNIQUE KEY `u_barcode` (`lid`,`barcode`),
  KEY `i_id` (`id`),
  KEY `i_building` (`lid`,`building`),
  KEY `i_homeroom` (`lid`,`homeroom`),
  KEY `i_teacher` (`lid`,`teacher`),
  KEY `i_grade` (`lid`,`grade`),
  KEY `i_gradYear` (`lid`,`gradYear`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_userPatron`
--

DROP TABLE IF EXISTS `opl_userPatron`;
CREATE TABLE `opl_userPatron` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `gid` int(10) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `cid` smallint(5) unsigned NOT NULL ,
  `firstname` varchar(255) NOT NULL default '',
  `lastname` varchar(255) NOT NULL default '',
  `sid` varchar(50) NOT NULL default '',
  `barcode` varchar(50) default NULL,
  `notes` varchar(255) default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `u_sid` (`gid`,`sid`),
  UNIQUE KEY `u_uid` (`gid`,`uid`),
  UNIQUE KEY `u_barcode` (`gid`,`barcode`),
  KEY `i_id` (`id`),
  KEY `i_cid` (`cid`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_userPermission`
--

DROP TABLE IF EXISTS `opl_userPermission`;
CREATE TABLE `opl_userPermission` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `lid` smallint(5) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `pid` int(10)   default '0',
  `notes` varchar(255) default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `u_uid` (`lid`,`uid`),
  KEY `i_pid` (`pid`),
  KEY `i_id` (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_permission`
--

DROP TABLE IF EXISTS `opl_permission`;
CREATE TABLE `opl_permission` (
  `pid` int(10) unsigned NOT NULL auto_increment,
  `permission` varchar(50)  default '',
  `notes` varchar(255) default '',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY `i_permission` (`permission`),
  KEY `i_pid` (`pid`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_zDatabase`
--

DROP TABLE IF EXISTS `opl_zDatabase`;
CREATE TABLE `opl_zDatabase` (
  `lid` smallint(5) unsigned NOT NULL default '0',
  `zid` tinyint(3) unsigned NOT NULL auto_increment,
  `host` varchar(255) NOT NULL default 'localhost',
  `port` smallint(5) unsigned NOT NULL default '210',
  `name` varchar(255) NOT NULL default 'default',
  `syntax` enum('xml','marc21') NOT NULL default 'xml',
  `username` varchar(20) NOT NULL default '',
  `password` varchar(20) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `tabDisplay` tinyint(3) unsigned NOT NULL default '0',
  `curTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`lid`,`zid`)
) ENGINE=MyISAM 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 */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

