--
-- Host: localhost    Database: ztest
-- ------------------------------------------------------


--
-- Table structure for table `eq_csvImport`
--

CREATE TABLE `eq_csvImport` (
  `iid` int(10) unsigned NOT NULL auto_increment,
  `sid` int(10) unsigned default '0',
  `lid` int(10) unsigned default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `sCode` varchar(50) default NULL,
  `lCode` varchar(50) default NULL,
  `itemType` varchar(50) default NULL,
  `filename` varchar(255) default NULL,
  `data` mediumtext,
  `header` mediumtext,
  `mapHeader` mediumtext,
  `dataDup` mediumtext,
  `cMerge` tinyint(1) unsigned default '1',
  `countImported` int(10) unsigned default '0',
  `countMerged` int(10) unsigned default '0',
  `countProcessed` int(10) unsigned default '0',
  `countTotal` int(10) unsigned NOT NULL default '0',
  `countTotalHolding` int(10) default NULL,
  `dateImport` datetime default NULL,
  `dateUpload` datetime NOT NULL default '0000-00-00 00:00:00',
  `mesgDigest` varchar(128) default NULL,
  `status` enum('uploaded','rejected','processed','accepted','done') default NULL,
  `addToNewitemList` tinyint(1) unsigned default '0',
  `newItemExpDate` datetime default NULL,
  PRIMARY KEY  (`iid`),
  KEY `i_uid` (`uid`),
  KEY `i_lid` (`lid`),
  KEY `i_msgDigest` (`mesgDigest`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `eq_def`
--

CREATE TABLE `eq_def` (
  `id` int(10) NOT NULL auto_increment,
  `defType` varchar(100) NOT NULL,
  `fOrder` int(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `reqF` tinyint(3) unsigned NOT NULL default '0',
  `reqD` tinyint(3) unsigned NOT NULL default '0',
  `display` tinyint(3) NOT NULL default '0',
  `fieldType` tinyint(3) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_defRequired`
--

CREATE TABLE `eq_defRequired` (
  `id` int(10) NOT NULL auto_increment,
  `defType` varchar(100) NOT NULL,
  `fOrder` int(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `reqF` tinyint(3) NOT NULL default '1',
  `reqD` tinyint(3) unsigned NOT NULL default '0',
  `display` tinyint(3) NOT NULL default '0',
  `fieldType` tinyint(3) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_fieldDataType`
--

CREATE TABLE `eq_fieldDataType` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `dataType` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_hold`
--

CREATE TABLE `eq_hold` (
  `id` int(10) NOT NULL auto_increment,
  `idReserve` int(10) unsigned NOT NULL default '0',
  `barcode` varchar(50) NOT NULL default '',
  `dateHold` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateExpiry` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateLoan` datetime default NULL,
  `dateCancel` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `i_hold` (`idReserve`,`barcode`,`dateLoan`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_itemFields`
--

CREATE TABLE `eq_itemFields` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL,
  `iid` int(10) unsigned NOT NULL,
  `sfId` int(10) NOT NULL,
  `sfValue` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `u_risf` (`iid`,`rid`,`sfId`),
  FULLTEXT KEY `sfValue` (`sfValue`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_itemStatus`
--

CREATE TABLE `eq_itemStatus` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `barcode` varchar(50) NOT NULL default '',
  `ondate` datetime default NULL,
  `status` tinyint(4) default '0',
  `note` varchar(60) default NULL,
  PRIMARY KEY  (`id`),
  KEY `index1` (`barcode`,`ondate`,`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_items`
--

CREATE TABLE `eq_items` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `iid` int(10) unsigned NOT NULL,
  `rid` int(10) unsigned NOT NULL,
  `barcode` varchar(255) NOT NULL,
  `typeId` varchar(100) default NULL,
  `available` tinyint(3) default NULL,
  `createdDate` datetime default NULL,
  `modDate` datetime default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `u_rid_iid` (`iid`,`rid`),
  UNIQUE KEY `u_barcode` (`barcode`),
  FULLTEXT KEY `f_barcode` (`barcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_loan`
--

CREATE TABLE `eq_loan` (
  `id` int(10) NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL default '0',
  `barcode` varchar(50) NOT NULL default '',
  `maxRenewal` tinyint(3) unsigned default '0',
  `dateLoan` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateDue` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateRenewal` datetime default NULL,
  `dateReturn` datetime default NULL,
  `renewalCount` tinyint(4) default '0',
  `finerate` float NOT NULL default '0',
  `status` tinyint(4) default '0',
  `type` varchar(50) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `u_bcdl` (`barcode`,`dateLoan`),
  KEY `i_loan` (`uid`,`barcode`,`dateLoan`),
  KEY `i_barcode` (`barcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_odl`
--

CREATE TABLE `eq_odl` (
  `odl_id` int(10) unsigned NOT NULL auto_increment,
  `idloan` int(10) unsigned default '0',
  `type` set('overdue','lost','damaged') default 'overdue',
  `days_overdue` tinyint(3) unsigned default '0',
  `ondate` datetime NOT NULL default '0000-00-00 00:00:00',
  `settleDate` datetime default NULL,
  PRIMARY KEY  (`odl_id`),
  KEY `NewIndex` (`odl_id`,`idloan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_recordFields`
--

CREATE TABLE `eq_recordFields` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL,
  `fId` int(10) unsigned NOT NULL,
  `fValue` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `i_fValue` (`fValue`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_records`
--

CREATE TABLE `eq_records` (
  `rid` int(10) unsigned NOT NULL auto_increment,
  `rname` text NOT NULL,
  PRIMARY KEY  (`rid`),
  FULLTEXT KEY `rname` (`rname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `eq_reserve`
--

CREATE TABLE `eq_reserve` (
  `id` int(10) NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `numCopyReserve` tinyint(4) default '1',
  `dateReserve` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateExpiry` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateCancel` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `i_reserve` (`rid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_arl`
--

CREATE TABLE `opl_arl` (
  `rid` int(10) unsigned NOT NULL default '0',
  `program` varchar(100) NOT NULL default '',
  `interestLevel` varchar(50) NOT NULL default '',
  `readingLevel` varchar(50) NOT NULL default '',
  `pointValue` varchar(50) NOT NULL default '',
  `quizNum` varchar(50) NOT NULL default '',
  KEY `i_rid` (`rid`),
  KEY `i_rtilc` (`program`,`interestLevel`,`readingLevel`,`pointValue`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_bookingFor`
--

CREATE TABLE `opl_bookingFor` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `bookingId` int(10) NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `notes` varchar(150) default '',
  `deleted` tinyint(3) unsigned default '0',
  `createdDate` datetime default NULL,
  `modDate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `std_id` (`uid`),
  KEY `bookingId` (`bookingId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_bookingHold`
--

CREATE TABLE `opl_bookingHold` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `bookingId` int(10) unsigned NOT NULL,
  `barcode` varchar(50) default '',
  `rid` int(10) unsigned NOT NULL,
  `itemCategory` tinyint(3) unsigned NOT NULL default '1',
  `notes` varchar(150) default '',
  `holdDate` datetime default NULL,
  `idloan` int(10) unsigned NOT NULL default '0',
  `modDate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `deleted` tinyint(3) unsigned default '0',
  PRIMARY KEY  (`id`),
  KEY `bookingId` (`bookingId`),
  KEY `barcode` (`barcode`),
  KEY `idloan` (`idloan`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_bookingItem`
--

CREATE TABLE `opl_bookingItem` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `bookingId` int(10) NOT NULL default '0',
  `idReserve` int(10) unsigned default NULL,
  `rid` int(10) unsigned NOT NULL default '0',
  `itemCategory` tinyint(3) unsigned default '1',
  `numOfCopy` int(10) unsigned NOT NULL default '0',
  `notes` varchar(150) default '',
  `deleted` tinyint(3) unsigned default '0',
  `createdDate` datetime default NULL,
  `modDate` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `bookingId` (`bookingId`),
  KEY `itemCategory` (`itemCategory`),
  KEY `idReserve` (`idReserve`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_bookingRequest`
--

CREATE TABLE `opl_bookingRequest` (
  `bookingId` int(10) NOT NULL auto_increment,
  `responsible` int(10) unsigned default NULL,
  `achv_id` int(10) unsigned default '0',
  `reqUid` int(10) unsigned default '0',
  `reqFirstname` varchar(150) default NULL,
  `reqLastname` varchar(150) default NULL,
  `department` varchar(150) default NULL,
  `course` varchar(150) default NULL,
  `section` varchar(150) default NULL,
  `reserveFrom` datetime default NULL,
  `reserveTo` datetime default NULL,
  `status` enum('waiting','processing','ready','cancel','filled') default 'waiting',
  `notes` varchar(150) default '',
  `deleted` tinyint(3) unsigned default '0',
  `createdDate` datetime default NULL,
  `modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`bookingId`),
  KEY `requestorId` (`reqUid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_category`
--

CREATE TABLE `opl_category` (
  `catid` int(10) unsigned NOT NULL auto_increment,
  `cattype` varchar(30) NOT NULL,
  `catname` varchar(30) default NULL,
  `maxloans` int(10) unsigned NOT NULL default '1',
  `maxreserv` int(10) unsigned NOT NULL default '1',
  `ODexempt` smallint(3) unsigned DEFAULT '0',
  `required` enum('0','1') NOT NULL default '0',
  `defaultPerm` text,
  PRIMARY KEY  (`catid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_cmntRating`
--

CREATE TABLE `opl_cmntRating` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL default '0',
  `reviewerUid` int(10) unsigned NOT NULL default '0',
  `rate` smallint(5) unsigned default '0',
  `comment` text,
  `reviewed` int(2) unsigned default '0',
  `accepted` int(2) unsigned default '0',
  `firstname` varchar(50) default NULL,
  `lastname` varchar(50) default NULL,
  `note` text,
  `cmntDate`   datetime DEFAULT NULL,
  `reviewDate` datetime default NULL,
  `modDate` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted` tinyint(3) unsigned DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `ind_deleted` (`deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_collectionStats`
--

CREATE TABLE `opl_collectionStats` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `onDate` date NOT NULL,
  `status` enum('add','lost','delete','found') NOT NULL,
  `record_Holding` enum('record','holding') NOT NULL default 'holding',
  `print` int(10) unsigned default '0',
  `nonPrint` int(10) unsigned default '0',
  `fiction` int(10) unsigned default '0',
  `nonFiction` int(10) unsigned default '0',
  `pro` int(10) unsigned default '0',
  `ref` int(10) unsigned default '0',
  `serial` int(10) unsigned default '0',
  `shortStories` int(10) unsigned default '0',
  `video` int(10) unsigned default '0',
  `audio` int(10) unsigned default '0',
  `multiMedia` int(10) unsigned default '0',
  `cassette` int(10) unsigned default '0',
  `kit` int(10) unsigned default '0',
  `mixedMaterial` int(10) unsigned default '0',
  `notatedMusic` int(10) unsigned default '0',
  `map` int(10) unsigned default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_damage`
--

CREATE TABLE `opl_damage` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `idloan` int(10) unsigned NOT NULL default '0',
  `fine` float NOT NULL default '0',
  `amount` float NOT NULL default '0',
  `ondate` datetime NOT NULL default '0000-00-00 00:00:00',
  `final` char(1) default '0',
  `fdrtax` float NOT NULL default '0',
  `prvtax` float NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_dateClosed`
--

CREATE TABLE `opl_dateClosed` (
  `dClosed` date NOT NULL default '0000-00-00',
  `dLabel` varchar(250) default NULL,
  `dUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `note` text  DEFAULT '',
  `closed` enum('0','1') DEFAULT '1',
  `imgName` varchar(250) DEFAULT '',  
  PRIMARY KEY  (`dClosed`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_delUser`
--

CREATE TABLE `opl_delUser` (
  `sysCode` varchar(10) NOT NULL default '',
  `libCode` varchar(10) NOT NULL default '',
  `username` varchar(20) default NULL,
  `password` varchar(86) default NULL,
  `uid` int(10) unsigned NOT NULL auto_increment,
  `sid` varchar(18) default NULL,
  `userbarcode` varchar(18) default NULL,
  `title` varchar(5) default NULL,
  `firstname` varchar(50) default NULL,
  `lastname` varchar(50) default NULL,
  `addrLine1` varchar(80) default NULL,
  `addrLine2` varchar(80) default NULL,
  `city` varchar(50) default NULL,
  `zip` varchar(20) default NULL,
  `state` char(2) default NULL,
  `phone` varchar(50) default NULL,
  `cellphone` varchar(50) default NULL,
  `fax` varchar(50) default NULL,
  `email` varchar(60) default NULL,
  `birthday` date default NULL,
  `gender` tinyint(1) unsigned default NULL,
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `pw_timestamp` datetime default NULL,
  `permission` varchar(50) default NULL,
  `permissions` text,
  `expirydate` datetime default NULL,
  `status` tinyint(1) unsigned default '0',
  `categorycode` int(11) default NULL,
  `buildingcode` varchar(10) NOT NULL default '',
  `homeroom` varchar(100) NOT NULL,
  `teacher` varchar(100) NOT NULL default '',
  `grade` varchar(10) NOT NULL default '',
  `yeargraduation` smallint(5) unsigned default '0',
  `notes` text,
  `pref_lang` char(2) default NULL,
  `pref_theme` varchar(50) default NULL,
  `idimport` int(11) default '0',
  `incomplete` enum('true','false') default 'false',
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `u_sysLibUsername` (`sysCode`,`libCode`,`username`),
  UNIQUE KEY `u_sysLibBarcode` (`sysCode`,`libCode`,`userbarcode`),
  UNIQUE KEY `u_sysLibSid` (`sysCode`,`libCode`,`sid`),
  KEY `i_lastname` (`lastname`),
  KEY `i_homeroom` (`homeroom`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_deweyDesc`
--

CREATE TABLE `opl_deweyDesc` (
  `dewey` decimal(6,3) NOT NULL default '0.000',
  `dewey_100` smallint(3) default '0',
  `dewey_10` smallint(3) default '0',
  `dewey_int` smallint(3) default NULL,
  `dewey_decimal` decimal(3,3) default NULL,
  `description` text,
  PRIMARY KEY  (`dewey`),
  KEY `dIndex` (`dewey_100`,`dewey_10`,`dewey_int`,`dewey_decimal`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_emailDetails`
--

CREATE TABLE `opl_emailDetails` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `eid` int(10) unsigned NOT NULL default '0',
  `eContent` text,
  `eBarcodes` text,
  `subject` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_emailRequest`
--

CREATE TABLE `opl_emailRequest` (
  `eid` int(10) unsigned NOT NULL auto_increment,
  `email` varchar(255) default NULL,
  `uid` int(10) unsigned NOT NULL default '0',
  `sendDate` datetime default NULL,
  `status` enum('sent','waiting','rejected') NOT NULL default 'waiting',
  `retryCount` tinyint(5) unsigned NOT NULL default '0',
  `requestDate` datetime default NULL,
  `modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`eid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_extDatabase` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `page` varchar(100) DEFAULT 'searchPage',
  `name` varchar(50) NOT NULL default '---',
  `url` text NOT NULL,
  `param` varchar(50) NOT NULL default '',
  `rank` tinyint(3) unsigned default '0',
  `hidden` tinyint(3) unsigned default '0',
  `description` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_file` (
  `fileType` enum('image','audio') NOT NULL default 'image',
  `fileName` varchar(255) NOT NULL default '',
  `fRealName` varchar(255) default NULL,
  `mimeType` varchar(255) NOT NULL default '',
  `defaultMimeType` varchar(255) NOT NULL default '',
  `fileData` mediumblob NOT NULL,
  `defaultFile` mediumblob NOT NULL,
  PRIMARY KEY  (`fileType`,`fileName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_fineMaterials`
--

CREATE TABLE `opl_fineMaterials` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `material` varchar(128) default 'book',
  `def` enum('0','1') default '0',
  PRIMARY KEY  (`id`),
  KEY `material` (`material`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_fineRate`
--

CREATE TABLE `opl_fineRate` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `itemType` varchar(100) default '0',
  `overdue` float(10,2) default '0.00',
  `overduePerHour` float(10,2) default '0.00',
  `maxOverdue` float(10,2) default '0.00',
  `damage` float(10,2) default '0.00',
  `toDel` tinyint(3) unsigned default '0',
  `itemCategory` enum('1','2','3') NOT NULL default '1',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_found`
--

CREATE TABLE `opl_found` (
  `barcode` varchar(50) NOT NULL default '',
  `ondate` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_fullMarcRecord`
--

CREATE TABLE `opl_fullMarcRecord` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned default NULL,
  `tag_id` tinyint(3) unsigned default NULL,
  `tag` varchar(3) default NULL,
  `indicator1` char(1) default NULL,
  `indicator2` char(1) default NULL,
  `subfield` char(1) default NULL,
  `data` text,
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`,`rid`,`tag`,`tag_id`),
  FULLTEXT KEY `ftext` (`data`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_ge852record`
--

CREATE TABLE `opl_ge852record` (
  `req_id` int(10) unsigned default NULL,
  `rid` int(10) unsigned default NULL,
  `barcode` varchar(50) default NULL,
  `status` enum('waiting','done','read_permission_denied','write_permission_denied') NOT NULL default 'waiting',
  UNIQUE KEY `req_id` (`req_id`,`rid`,`barcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_ge852request`
--

CREATE TABLE `opl_ge852request` (
  `req_id` int(10) unsigned NOT NULL auto_increment,
  `uid` int(10) unsigned default NULL,
  `o_code` char(1) default NULL,
  `n_code` char(1) default NULL,
  `o_data` varchar(255) default NULL,
  `n_data` varchar(255) default NULL,
  `newRecType` enum('book','bookManuscript','ebook','journal','serial','electronicMedia','internet','compactDisc','map','mapManuscript','bookOnTape','musicCassette','CDMusic','manuscriptMusic','sheetMusic','artifact','photoPoster','libraryKit','mixedMaterial','movie','videocassette','DVD','blu_ray') default NULL,
  `action` enum('update','append','appendF','delete','copy','transfer','updateAddNew','changeRecType','moveHolding') default NULL,
  `frDate` datetime default NULL,
  `toDate` datetime default NULL,
  `reqDate` datetime default NULL,
  `finishDate` datetime default NULL,
  `status` enum('waiting','processing','done') NOT NULL default 'waiting',
  `digest` varchar(128) NOT NULL default '',
  `tag` char(3) default NULL,
  PRIMARY KEY  (`req_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_guardian`
--

CREATE TABLE `opl_guardian` (
  `gid` int(10) unsigned NOT NULL auto_increment,
  `gtitle` varchar(5) default NULL,
  `gfirstname` varchar(50) default NULL,
  `glastname` varchar(50) default NULL,
  `gaddrLine1` varchar(80) default NULL,
  `gaddrLine2` varchar(80) default NULL,
  `gcity` varchar(50) default NULL,
  `gzip` varchar(20) default NULL,
  `gstate` char(2) default NULL,
  `gphone` varchar(50) default NULL,
  `gcellphone` varchar(50) default NULL,
  `gfax` varchar(50) default NULL,
  `gemail` varchar(60) default NULL,
  `uid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`gid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_historyItem`
--

CREATE TABLE `opl_historyItem` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `barcode` varchar(15) NOT NULL default '',
  `ondate` datetime default NULL,
  `status` set('imported','deleted','damaged','lost','found') default NULL,
  `note` varchar(60) default NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`,`barcode`,`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_historyUser`
--

CREATE TABLE `opl_historyUser` (
  `id` int(3) unsigned NOT NULL auto_increment,
  `uid` int(10) unsigned default '0',
  `ondate` date default NULL,
  `status` set('added','deleted','modified','activated','deactivated','suspended') default NULL,
  `note` varchar(60) default NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`,`uid`,`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_hold`
--

CREATE TABLE `opl_hold` (
  `idReserve` int(10) unsigned NOT NULL default '0',
  `idloan` int(10) unsigned default '0',
  `barcode` varchar(50) NOT NULL default '',
  `dateHold` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateExpiry` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateLoan` datetime default NULL,
  `dateCancel` datetime default NULL,
  KEY `i_hold` (`idReserve`,`barcode`,`dateLoan`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_idGen`
--

CREATE TABLE `opl_idGen` (
  `type` varchar(255) NOT NULL default '0',
  `id` int(10) NOT NULL auto_increment,
  `checkSum` char(40) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

---- Table structure for table `opl_illAdmin`
--

CREATE TABLE `opl_illAdmin` (
  `uid` int(10) unsigned NOT NULL default '0',
  `sid` int(10) unsigned default NULL,
  `lid` int(10) unsigned default NULL,
  `adminType` tinyint(3) unsigned NOT NULL default '0',
  UNIQUE KEY `u_usl` (`uid`,`sid`,`lid`),
  KEY `i_sid` (`sid`),
  KEY `i_lid` (`lid`),
  KEY `i_uidType` (`uid`,`adminType`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_illNote`
--

CREATE TABLE `opl_illNote` (
  `illId` int(10) unsigned NOT NULL default '0',
  `sid` int(10) unsigned default NULL,
  `lid` int(10) unsigned default NULL,
  `uid` int(10) unsigned NOT NULL default '0',
  `notePublic` text,
  `notePrivate` text,
  `dateNote` datetime default NULL,
  KEY `i_illId` (`illId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_illReply`
--

CREATE TABLE `opl_illReply` (
  `illId` int(10) unsigned NOT NULL default '0',
  `sid` int(10) unsigned NOT NULL default '0',
  `lid` int(10) unsigned NOT NULL default '0',
  `uid` int(10) unsigned default NULL,
  `dateFill` datetime default NULL,
  `dateDue` datetime default NULL,
  `dateReturn` datetime default NULL,
  `dateReject` datetime default NULL,
  UNIQUE KEY `u_reply` (`illId`,`sid`,`lid`),
  KEY `i_illId` (`illId`),
  KEY `i_sid` (`sid`),
  KEY `i_lid` (`lid`),
  KEY `i_uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_illRequest` (
  `illId` int(10) unsigned NOT NULL auto_increment,
  `sid` int(10) unsigned NOT NULL default '0',
  `lid` int(10) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `rid` int(10) unsigned NOT NULL default '0',
  `description` text,
  `dateRequest` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateReturn` datetime default NULL,
  `dateCancel` datetime default NULL,
  `dateExpiry` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`illId`),
  UNIQUE KEY `u_request` (`uid`,`dateRequest`),
  KEY `i_sid` (`sid`),
  KEY `i_lid` (`lid`),
  KEY `i_uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_illUser`
--

CREATE TABLE `opl_illUser` (
  `uid` int(10) unsigned NOT NULL default '0',
  `sid` int(10) unsigned NOT NULL default '0',
  `lid` int(10) unsigned NOT NULL default '0',
  `illPlace` tinyint(3) unsigned NOT NULL default '0',
  `illFill` tinyint(3) unsigned NOT NULL default '0',
  UNIQUE KEY `u_usl` (`uid`,`sid`,`lid`),
  KEY `i_illPlace` (`uid`,`illPlace`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_index_map`
--

CREATE TABLE `opl_index_map` (
  `attrVal` varchar(8) default NULL,
  `attrName` varchar(128) default NULL,
  `tag` char(3) default NULL,
  `subfields` varchar(32) default NULL,
  `attrList` varchar(255) default NULL,
  KEY `attribute` (`attrVal`,`attrName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_inventory`
--

CREATE TABLE `opl_inventory` (
  `invId` int(10) unsigned NOT NULL default '0',
  `fid` int(10) unsigned NOT NULL default '0',
  `scanOrder` int(10) unsigned NOT NULL auto_increment,
  `sortOrder` int(10) NOT NULL default '0',
  `callNumber` varchar(40) NOT NULL default '',
  `barcode` varchar(50) NOT NULL default '',
  `status` set('active','damaged','found','missing','loan','invalid','unknown','noCallNumber','scanned','misplace') NOT NULL default 'active',
  `updated` tinyint(3) unsigned NOT NULL default '0',
  `updatedStatus` set('active','damaged','found','missing','loan','invalid','unknown','noCallNumber','scanned','misplace') NOT NULL default '',
  `noteDetail` varchar(100) default NULL,
  PRIMARY KEY  (`scanOrder`),
  UNIQUE KEY `bc_invId` (`barcode`,`invId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_inventoryFile`
--

CREATE TABLE `opl_inventoryFile` (
  `invId` int(10) unsigned NOT NULL default '0',
  `fid` int(10) unsigned NOT NULL auto_increment,
  `fname` varchar(255) NOT NULL default '',
  `uid` int(10) unsigned NOT NULL default '0',
  `mesgDigest` varchar(128) default NULL,
  `dateUpload` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`fid`),
  UNIQUE KEY `u_fileUser` (`fname`,`uid`,`invId`),
  UNIQUE KEY `u_mesgDigest` (`mesgDigest`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_inventoryInfo`
--

CREATE TABLE `opl_inventoryInfo` (
  `invId` int(10) NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL default '0',
  `callNoB` varchar(40) default '0',
  `callNoE` varchar(40) default '0',
  `invDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `status` set('scanning','done','waiting','processing','closed') default 'scanning',
  `orderidB` int(5) unsigned default NULL,
  `orderidE` int(5) unsigned default NULL,
  PRIMARY KEY  (`invId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_inventoryResult`
--

CREATE TABLE `opl_inventoryResult` (
  `invId` int(10) unsigned NOT NULL default '0',
  `varName` varchar(255) default NULL,
  `varValue` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_item` (
  `barcode` varchar(50) NOT NULL default '',
  `rid` int(10) unsigned NOT NULL default '0',
  `available` tinyint(4) default '1',
  `typeId` varchar(100) NOT NULL default '',
  `callNumber` varchar(40) default NULL,
  `dateImport` datetime default NULL,
  `price` varchar(50) default NULL,
  `location` varchar(100) default NULL,
  `importId` int(10) unsigned NOT NULL default '0',
  `modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `callNumSort_dewey` varchar(100) DEFAULT '',
  `callNumSort_lcc` varchar(100) DEFAULT '',
  PRIMARY KEY  (`barcode`),
  KEY `i_rid` (`rid`),
  KEY `i_location` (`location`),
  KEY `i_callNumber` (`callNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_itemCategory`
--

CREATE TABLE `opl_itemCategory` (
  `id` int(10) NOT NULL auto_increment,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_itemInfo` (
  `barcode` varchar(50) NOT NULL default '',
  `sf852Code` char(1) NOT NULL default '',
  `sf852Data` varchar(255) NOT NULL default '',
  UNIQUE KEY `u_bcd` (`barcode`,`sf852Code`,`sf852Data`),
  KEY `i_cd` (`sf852Code`,`sf852Data`),
  FULLTEXT KEY `f_sf852Data` (`sf852Data`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_itemStats`
--
CREATE TABLE `opl_itemStats` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `statId` int(10) unsigned default NULL,
  `rid` int(10) unsigned NOT NULL default '1',
  `barcode` varchar(50) NOT NULL default '',
  `prefix` varchar(16) default NULL,
  `import` int(5) unsigned NOT NULL default '0',
  `deleted` int(5) unsigned NOT NULL default '0',
  `lost` int(5) unsigned NOT NULL default '0',
  `damaged` int(5) unsigned NOT NULL default '0',
  `missing` int(5) unsigned NOT NULL default '0',
  `found` int(5) unsigned NOT NULL default '0',
  `validStats` enum('yes','no') default 'yes',
  PRIMARY KEY  (`id`),
  KEY `prefix` (`prefix`),
  KEY `status` (`import`,`deleted`,`lost`,`damaged`,`missing`,`found`),
  KEY `import` (`import`),
  KEY `deleted` (`deleted`),
  KEY `lost` (`lost`),
  KEY `rid` (`rid`,`statId`),
  KEY `barcode` (`barcode`),
  KEY `validRow` (`validStats`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

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

CREATE TABLE `opl_itemType` (
  `id` varchar(100) NOT NULL default '',
  `description` text,
  `defaultType` tinyint(3) unsigned default '0',
  `itemCategory` enum('1','2','3') NOT NULL default '1',
  PRIMARY KEY  (`id`,`itemCategory`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_itemTypeChange` (
  `cid` int(10) unsigned NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL default '0',
  `dateRequest` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateFinish` datetime default NULL,
  `oldTypeId` varchar(100) NOT NULL default '',
  `newTypeId` varchar(100) NOT NULL default '',
  `cReplaced` int(10) unsigned default '0',
  `cTotal` int(10) unsigned NOT NULL default '0',
  `status` enum('waiting','invalid','processing','done') NOT NULL default 'waiting',
  PRIMARY KEY  (`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_itemTypeParam` (
  `itemTypeId` varchar(100) NOT NULL default '',
  `userTypeId` int(10) unsigned NOT NULL default '0',
  `loanPeriod` int(10) unsigned NOT NULL default '0',
  `renewalPeriod` int(10) unsigned NOT NULL default '0',
  `reservePeriod` int(10) unsigned NOT NULL default '0',
  `holdPeriod` int(10) unsigned NOT NULL default '0',
  `gracePeriod` int(10) unsigned NOT NULL default '0',
  `gracePeriodUnit` enum('hour','minute') default 'hour',
  `maxRenewal` tinyint(3) unsigned default '0',
  `fine` float NOT NULL default '0',
  PRIMARY KEY  (`itemTypeId`,`userTypeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_itemstatus`
--

CREATE TABLE `opl_itemstatus` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `barcode` varchar(50) NOT NULL default '',
  `ondate` datetime default NULL,
  `status` tinyint(4) default '0',
  `note` varchar(60) default NULL,
  PRIMARY KEY  (`id`),
  KEY `index1` (`barcode`,`ondate`,`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_libSystem`
--

CREATE TABLE `opl_libSystem` (
  `sid` int(10) unsigned NOT NULL auto_increment,
  `sCode` varchar(10) NOT NULL default '',
  `sName` text NOT NULL,
  `sAddrLine1` varchar(255) default NULL,
  `sAddrLine2` varchar(255) default NULL,
  `sCity` varchar(255) default NULL,
  `sState` varchar(10) default NULL,
  `sZip` varchar(10) default NULL,
  `sPhone` varchar(50) default NULL,
  `sFax` varchar(50) default NULL,
  `sContact` varchar(255) default NULL,
  `sEmail` varchar(255) default NULL,
  PRIMARY KEY  (`sid`),
  UNIQUE KEY `u_sCode` (`sCode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_library`
--

CREATE TABLE `opl_library` (
  `lid` int(10) unsigned NOT NULL auto_increment,
  `sysCode` varchar(10) NOT NULL default '',
  `lCode` varchar(10) NOT NULL default '',
  `lName` text NOT NULL,
  `lAddrLine1` varchar(255) default NULL,
  `lAddrLine2` varchar(255) default NULL,
  `lCity` varchar(255) default NULL,
  `lState` varchar(10) default NULL,
  `lZip` varchar(10) default NULL,
  `lPhone` varchar(50) default NULL,
  `lFax` varchar(50) default NULL,
  `lContact` varchar(255) default NULL,
  `lEmail` varchar(255) default NULL,
  PRIMARY KEY  (`lid`),
  UNIQUE KEY `u_sysLib` (`sysCode`,`lCode`),
  KEY `i_lCode` (`lCode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_loan`
--

CREATE TABLE `opl_loan` (
  `idloan` int(10) unsigned NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL default '0',
  `barcode` varchar(50) NOT NULL default '',
  `maxRenewal` tinyint(3) unsigned default '0',
  `dateLoan` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateDue` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateRenewal` datetime default NULL,
  `dateReturn` datetime default NULL,
  `renewalCount` tinyint(4) default '0',
  `finerate` float NOT NULL default '0',
  `status` tinyint(4) default '0',
  `type` varchar(50) default NULL,
  PRIMARY KEY  (`idloan`),
  UNIQUE KEY `u_bcdl` (`barcode`,`dateLoan`),
  KEY `i_loan` (`uid`,`barcode`,`dateLoan`),
  KEY `i_barcode` (`barcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_lost`
--

CREATE TABLE `opl_lost` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `price` float default '0',
  `orderfee` float default '0',
  `shipfee` float default '0',
  `fdrtax` float default '0',
  `prvtax` float default '0',
  `amount` float default '0',
  `ondate` datetime default NULL,
  `final` char(1) default '0',
  `idloan` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_marcDelete` (
  `idmarc` int(11) NOT NULL auto_increment,
  `tsDeletion` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `content` mediumtext,
  PRIMARY KEY  (`idmarc`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_marcDuplicate` (
  `mid` int(10) unsigned NOT NULL auto_increment,
  `iid` int(10) unsigned NOT NULL default '0',
  `content` mediumtext,
  PRIMARY KEY  (`mid`),
  KEY `i_iid` (`iid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_marcExport`
--

CREATE TABLE `opl_marcExport` (
  `eid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `dateRequest` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `eType` enum('date','rid','all','modDate_r','modDate_h','ridList_r','ridList_h','hitlist') DEFAULT NULL,
  `eFrom` varchar(50) NOT NULL DEFAULT '',
  `eTo` varchar(50) NOT NULL DEFAULT '',
  `eEncoding` enum('UTF-8','MARC-8') NOT NULL DEFAULT 'UTF-8',
  `exclRecFormat` text,
  `dateExpiry` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dateFinish` 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','ready') NOT NULL DEFAULT 'waiting',
  `ridList` text,
  PRIMARY KEY (`eid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_marcImport`
--

CREATE TABLE `opl_marcImport` (
  `iid` int(10) unsigned NOT NULL auto_increment,
  `sid` int(10) unsigned default '0',
  `lid` int(10) unsigned default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `encoding` enum('UTF-8','MARC-8') NOT NULL default 'UTF-8',
  `sf852Code` char(1) default NULL,
  `sf852Data` varchar(255) default NULL,
  `sf856Code` char(1) DEFAULT '',
  `sf856Data` varchar(255) DEFAULT '',
  `type` enum('hardCopy','ebook') DEFAULT 'hardCopy',
  `mode` enum('autoMerge','overlay','manualMerge','new') NOT NULL default 'autoMerge',
  `mergeMode` enum('strict','loose') NOT NULL default 'strict',
  `countImported` int(10) unsigned default '0',
  `countMerged` int(10) unsigned default '0',
  `countMergedHolding` int(10) unsigned default '0',
  `countProcessed` int(10) unsigned default '0',
  `countTotal` int(10) unsigned NOT NULL default '0',
  `dateImport` datetime default NULL,
  `dateUpload` datetime NOT NULL default '0000-00-00 00:00:00',
  `mesgDigest` varchar(128) default '',
  `status` enum('uploaded','rejected','accepted','indexing','done') NOT NULL default 'uploaded',
  `addToNewItemList` tinyint(1) unsigned default '0',
  `newItemExpDate` datetime default NULL,
  `holding_op` enum('update','preserve') NOT NULL default 'update',
  `recordSource` enum('generic','OCoLC') NOT NULL default 'generic',
  PRIMARY KEY  (`iid`),
  KEY `i_uid` (`uid`),
  KEY `i_lid` (`lid`),
  KEY `i_mesgDigest` (`mesgDigest`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_marcRecord` (
  `rid` int(10) unsigned NOT NULL default '1',
  `title` text,
  `titleSort` varchar(255) default NULL,
  `author` text,
  `pubPlace` varchar(60) default NULL,
  `pubName` varchar(60) default NULL,
  `pubDate` varchar(20) default NULL,
  `pubDateSort` varchar(100) default NULL,
  `isbn` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zIndexed` tinyint(4) NOT NULL default '0',
  `lockExpire` datetime default NULL,
  `editUid` int(10) unsigned default NULL,
  `sessionid` varchar(40) default NULL,
  `leader` varchar(24) default NULL,
  `cf_008` varchar(40) default NULL,
  `modDate` datetime default '0000-00-00 00:00:00',
  `print` set('print','fiction','nonFiction','pro','ref','serial','shortStories') default NULL,
  `nonPrint` set('nonPrint','video','audio','multiMedia','cassette','kit','mixedMaterial','notatedMusic','map') default NULL,
  `deleted` tinyint(3) unsigned default '0',
  `incomplete` enum('true','false') default 'false',
  `tempIll` enum('temporary','ILL') default NULL,
  `recFormat` set('book','bookManuscript','ebook','journal','serial','electronicMedia','internet','compactDisc','map','mapManuscript','bookOnTape','musicCassette','CDMusic','manuscriptMusic','sheetMusic','artifact','photoPoster','libraryKit','mixedMaterial','movie','videocassette','DVD','blu_ray') default 'book',
  `literaryForm` varchar(100) default 'unknown',
  PRIMARY KEY  (`rid`),
  KEY `index1` (`leader`,`cf_008`,`modDate`),
  KEY `ind_titlesort` (`titleSort`),
  KEY `ind_pub` (`pubPlace`,`pubName`,`pubDate`,`pubDateSort`),
  KEY `ind_isbn` (`isbn`),
  FULLTEXT KEY `ind_auth` (`author`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_menuItem`
--

CREATE TABLE `opl_menuItem` (
  `MENU_ITEM_ID` bigint(20) NOT NULL auto_increment,
  `PARENT_ID` bigint(20) default NULL,
  `headerKey` varchar(255) default NULL,
  `orderValue` int(11) default NULL,
  `color` varchar(255) default NULL,
  `language` varchar(8) default 'en',
  `title` varchar(255) default NULL,
  `isJava` bit(1) default NULL,
  `path` varchar(255) default NULL,
  `permission` varchar(50) default NULL,
  `permissions` varchar(255) default '',
  PRIMARY KEY  (`MENU_ITEM_ID`),
  KEY `FK4CD5A1F36DE0266` (`PARENT_ID`),
  CONSTRAINT `FK4CD5A1F36DE0266` FOREIGN KEY (`PARENT_ID`) REFERENCES `opl_menuItem` (`MENU_ITEM_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_newsEvents`
--

CREATE TABLE `opl_newsEvents` (
  `idNE` mediumint(3) unsigned NOT NULL auto_increment,
  `title` varchar(254) default NULL,
  `description` text,
  `expDate` date default NULL,
  `status` enum('on','off') default 'on',
  PRIMARY KEY  (`idNE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_nystd`
--

CREATE TABLE `opl_nystd` (
  `NY_ID` varchar(20) default NULL,
  `grade` varchar(5) default NULL,
  `level` varchar(50) default NULL,
  `category` varchar(150) default NULL,
  `subject` varchar(150) default NULL,
  `NY_Std_No` int(3) unsigned default '0',
  `NY_Bmrk_No` int(3) unsigned default '0',
  `NYStandard` text,
  `NYBmrk` text,
  `subjectStandard` varchar(254) default NULL,
  `cerfcode` text,
  `subjectAlphabet` char(1) default NULL,
  FULLTEXT KEY `NYstandard` (`NYStandard`,`subjectStandard`,`NYBmrk`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_odl`
--

CREATE TABLE `opl_odl` (
  `odl_id` int(10) unsigned NOT NULL auto_increment,
  `idloan` int(10) unsigned default '0',
  `type` set('overdue','lost','damaged') default 'overdue',
  `days_overdue` tinyint(3) unsigned default '0',
  `hours_overdue` tinyint(3) unsigned default '0',
  `ondate` datetime NOT NULL default '0000-00-00 00:00:00',
  `settleDate` datetime default NULL,
  PRIMARY KEY  (`odl_id`),
  KEY `NewIndex` (`odl_id`,`idloan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_offlineLoan`
--

CREATE TABLE `opl_offlineLoan` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `idloan` int(10) unsigned default NULL,
  `itemBC` varchar(50) default NULL,
  `userBC` varchar(50) default NULL,
  `transDate` datetime default NULL,
  `note` text,
  `status` smallint(5) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_openHours`
--

CREATE TABLE `opl_openHours` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `weekday` tinyint(3) unsigned default '0',
  `open` varchar(32) default NULL,
  `close` varchar(32) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_overdue`
--

CREATE TABLE `opl_overdue` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `idloan` int(10) unsigned NOT NULL default '0',
  `ondate` datetime NOT NULL default '0000-00-00 00:00:00',
  `amount` float NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_payRefundInfo`
--

CREATE TABLE `opl_payRefundInfo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `tid` int(10) unsigned default NULL,
  `paid_tid` int(10) unsigned default NULL,
  `note` varchar(60) default NULL,
  `date` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_payment`
--

CREATE TABLE `opl_payment` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL default '0',
  `amount` float default '0',
  `ondate` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_permGroup`
--

CREATE TABLE `opl_permGroup` (
  `id` int(10) NOT NULL auto_increment,
  `ssId` int(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_permission` (
  `id` int(10) NOT NULL auto_increment,
  `grpId` int(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--

-- Table structure for table `opl_prefFormParam`
--

CREATE TABLE `opl_prefFormParam` (
  `var` varchar(150) NOT NULL,
  `listOrder` tinyint(2) unsigned default '0',
  `name` varchar(150) NOT NULL,
  `val` varchar(150) NOT NULL,
  UNIQUE KEY `var` (`var`,`val`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_prefGroup`
--

CREATE TABLE `opl_prefGroup` (
  `id` int(10) default '0',
  `title` varchar(128) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_preference` (
  `var` varchar(25) NOT NULL default '',
  `val` text,
  `valShow` text,
  `opt` varchar(10) default NULL,
  `varType` set('text','radio','checkbox','select','multipleSelect','timeLength') default 'text',
  `description` text,
  `hidden` tinyint(3) unsigned default '0',
  `gid` int(10) unsigned default NULL,
  `gOrder` int(10) unsigned default NULL,
  PRIMARY KEY  (`var`),
  KEY `i_hidden` (`hidden`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Table structure for table `opl_recordArchive`
--

CREATE TABLE `opl_recordArchive` (
  `archId` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL default '1',
  `onDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `marcXml` mediumblob,
  PRIMARY KEY  (`archId`),
  KEY `rid` (`rid`,`onDate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_recordIdentifier`
--

CREATE TABLE `opl_recordIdentifier` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL default '1',
  `tag` char(3) NOT NULL default '',
  `code` char(1) NOT NULL default '',
  `data` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `i_tcd` (`tag`,`code`,`data`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Table structure for table `opl_recordStats`
--
CREATE TABLE `opl_recordStats` (
  `statId` int(10) unsigned NOT NULL auto_increment,
  `archId` int(10) unsigned default NULL,
  `rid` int(10) unsigned NOT NULL default '1',
  `onDate` datetime default NULL,
  `status` enum('new','deleted','changed') default 'new',
  PRIMARY KEY  (`statId`),
  KEY `archId` (`archId`,`rid`,`onDate`,`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;


--
-- Table structure for table `opl_recordindex`
--

CREATE TABLE `opl_recordindex` (
  `rid` int(10) unsigned NOT NULL,
  `attr_1003` text,
  `attr_1018` text,
  `attr_12` text,
  `attr_13` text,
  `attr_16` text,
  `attr_21` text,
  `attr_31` text,
  `attr_4` text,
  `attr_5` text,
  `attr_5000` text,
  `attr_5001` text,
  `attr_5002` text,
  `attr_59` text,
  `attr_6` text,
  `attr_63` text,
  `attr_7` text,
  `attr_8` text,
  `attr_9` text,
  `attr_s1003` text,
  `attr_s31` text,
  `attr_s4` text,
  `attr_s5001` text,
  PRIMARY KEY  (`rid`),
  FULLTEXT KEY `index_a1003` (`attr_1003`),
  FULLTEXT KEY `index_a1016` (`attr_4`,`attr_5`,`attr_6`,`attr_21`,`attr_31`,`attr_59`,`attr_63`,`attr_1003`),
  FULLTEXT KEY `index_a1018` (`attr_1018`),
  FULLTEXT KEY `index_a12` (`attr_12`),
  FULLTEXT KEY `index_a13` (`attr_13`),
  FULLTEXT KEY `index_a16` (`attr_16`),
  FULLTEXT KEY `index_a21` (`attr_21`),
  FULLTEXT KEY `index_a31` (`attr_31`),
  FULLTEXT KEY `index_a4` (`attr_4`),
  FULLTEXT KEY `index_a5` (`attr_5`),
  FULLTEXT KEY `index_a5000` (`attr_5000`),
  FULLTEXT KEY `index_a5001` (`attr_5001`),
  FULLTEXT KEY `index_a5002` (`attr_5002`),
  FULLTEXT KEY `index_a59` (`attr_59`),
  FULLTEXT KEY `index_a6` (`attr_6`),
  FULLTEXT KEY `index_a63` (`attr_63`),
  FULLTEXT KEY `index_a7` (`attr_7`),
  FULLTEXT KEY `index_a8` (`attr_8`),
  FULLTEXT KEY `index_a9` (`attr_9`),
  FULLTEXT KEY `index_as1003` (`attr_s1003`),
  FULLTEXT KEY `index_as31` (`attr_s31`),
  FULLTEXT KEY `index_as4` (`attr_s4`),
  FULLTEXT KEY `index_as5001` (`attr_s5001`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_reserve`
--

CREATE TABLE `opl_reserve` (
  `idReserve` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `numCopyReserve` tinyint(4) default '1',
  `dateReserve` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateExpiry` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateCancel` datetime default NULL,
  PRIMARY KEY  (`idReserve`),
  KEY `i_reserve` (`rid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_ridBcdHitlistExport`
--

CREATE TABLE `opl_ridBcdHitlistExport` (
  `eid` int(10) unsigned default '0',
  `rid` int(10) unsigned default '0',
  `barcode` varchar(50) default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_schoolYear`
--

CREATE TABLE `opl_schoolYear` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `schoolYear` varchar(45) NOT NULL,
  `firstDate` datetime default NULL,
  `lastDate` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `Index_2` (`firstDate`,`lastDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_session` (
  `sessionid` varchar(40) NOT NULL default '',
  `uid` int(10) unsigned NOT NULL default '0',
  `ipaddress` varchar(63) default NULL,
  `lasttime` datetime default NULL,
  PRIMARY KEY  (`sessionid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_sessionVar`
--

CREATE TABLE `opl_sessionVar` (
  `ssid` varchar(40) default '0',
  `var` varchar(36) default NULL,
  `sOrder` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned default NULL,
  `barcode` varchar(50) default NULL,
  `note` text,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `u_svo` (`ssid`,`var`,`sOrder`),
  KEY `rid` (`rid`,`barcode`,`var`,`ssid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_studentILL`
--

CREATE TABLE `opl_studentILL` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL default '0',
  `zid` tinyint(3) unsigned NOT NULL default '0',
  `rid` int(10) unsigned default NULL,
  `title` text,
  `author` text,
  `pubDate` varchar(20) NOT NULL default '',
  `description` text,
  `dRequest` datetime NOT NULL default '0000-00-00 00:00:00',
  `dExpiry` datetime NOT NULL default '0000-00-00 00:00:00',
  `dApproval` datetime default NULL,
  `dRejection` datetime default NULL,
  `responderList` varchar(255) NOT NULL default '',
  `illAdmin` int(10) unsigned NOT NULL default '0',
  `illID` int(10) unsigned NOT NULL default '0',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_subSystem`
--

CREATE TABLE `opl_subSystem` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_subjects`
--

CREATE TABLE `opl_subjects` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `subject` text,
  `recCount` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `subject` (`subject`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_taxcalcdetail`
--

CREATE TABLE `opl_taxcalcdetail` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `tid` int(10) unsigned NOT NULL default '0',
  `taxName` varchar(64) NOT NULL default '0',
  `taxRate` float(10,2) NOT NULL default '0.00',
  `taxBase` set('basic','taxontax') NOT NULL default 'basic',
  `taxableAmount` float(10,2) NOT NULL default '0.00',
  `tax` float(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id`),
  KEY `NewIndex` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_taxes`
--

CREATE TABLE `opl_taxes` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `taxName` varchar(64) default '0',
  `rate` float(10,2) default '0.00',
  `type` set('flat','%') default '%',
  `calFormat` set('basic','taxontax') default 'basic',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_teacherSchedule`
--

CREATE TABLE `opl_teacherSchedule` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `sectionId` varchar(45) default NULL,
  `courseId` int(8) unsigned default NULL,
  `courseCode` varchar(45) default NULL,
  `teacherId` varchar(45) default NULL,
  `createdDate` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `Index_2` (`sectionId`,`courseCode`,`teacherId`,`courseId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_template` (
  `tid` tinyint(3) unsigned NOT NULL auto_increment,
  `rank` tinyint(3) unsigned NOT NULL default '1',
  `name` varchar(100) default NULL,
  `content` mediumtext,
  PRIMARY KEY  (`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_transCode`
--

CREATE TABLE `opl_transCode` (
  `code` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(150) NOT NULL,
  `note` varchar(255) NOT NULL,
  PRIMARY KEY  (`code`),
  KEY `Index_2` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_transactiondetail`
--

CREATE TABLE `opl_transactiondetail` (
  `tid` int(10) unsigned default '0',
  `odl_id` int(10) unsigned NOT NULL default '0',
  `responsible` varchar(64) default '',
  `materials` set('book','video','DVD','reserve material') default 'book',
  `rate` float(10,2) default '0.00',
  `hourlyRate` float(10,2) default '0.00',
  `overdueDays` tinyint(3) unsigned default '0',
  `overdueHours` tinyint(3) unsigned default '0',
  `fineAmount` float(10,2) default '0.00',
  `forgivenAmount` float(10,2) default '0.00',
  `amount` float(10,2) default '0.00',
  `taxes` float(10,2) default '0.00',
  `note` varchar(254) default '',
  KEY `NewIndex` (`tid`,`odl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_transactions`
--

CREATE TABLE `opl_transactions` (
  `tid` int(10) unsigned NOT NULL auto_increment,
  `code` smallint(6) default NULL,
  `uid` int(10) unsigned NOT NULL default '0',
  `amount` float(10,2) NOT NULL default '0.00',
  `balance` float(10,2) NOT NULL default '0.00',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `description` varchar(50) default NULL,
  `settleDate` datetime default NULL,
  PRIMARY KEY  (`tid`),
  UNIQUE KEY `tid` (`tid`),
  KEY `tid_2` (`tid`,`uid`,`date`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_unionRequest`
--

CREATE TABLE `opl_unionRequest` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `uid` tinyint(10) unsigned default NULL,
  `reqType` set('deletion','import') default 'deletion',
  `date` datetime default NULL,
  `status` enum('creating','pending','processing','sent') default 'creating',
  `unionSvrRespErr` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_unionRequestDetail`
--

CREATE TABLE `opl_unionRequestDetail` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `reqId` int(10) unsigned NOT NULL default '0',
  `rid` int(10) unsigned default '0',
  `barcode` varchar(50) default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_user`
--

CREATE TABLE `opl_user` (
  `sysCode` varchar(10) NOT NULL default '',
  `libCode` varchar(10) NOT NULL default '',
  `username` varchar(60) default NULL,
  `password` varchar(86) default NULL,
  `uid` int(10) unsigned NOT NULL auto_increment,
  `sid` varchar(18) default NULL,
  `userbarcode` varchar(18) default NULL,
  `title` varchar(5) default NULL,
  `firstname` varchar(50) default NULL,
  `lastname` varchar(50) default NULL,
  `addrLine1` varchar(80) default NULL,
  `addrLine2` varchar(80) default NULL,
  `city` varchar(50) default NULL,
  `zip` varchar(20) default NULL,
  `state` char(2) default NULL,
  `phone` varchar(50) default NULL,
  `cellphone` varchar(50) default NULL,
  `fax` varchar(50) default NULL,
  `email` varchar(60) default NULL,
  `birthday` date default NULL,
  `gender` tinyint(1) unsigned default NULL,
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `pw_timestamp` datetime default NULL,
  `permission` varchar(50) default NULL,
  `permissions` text,
  `expirydate` datetime default NULL,
  `status` tinyint(1) unsigned default '0',
  `categorycode` int(11) default NULL,
  `buildingcode` varchar(10) NOT NULL default '',
  `homeroom` varchar(100) NOT NULL,
  `teacher` varchar(100) NOT NULL default '',
  `grade` varchar(10) NOT NULL default '',
  `yeargraduation` smallint(5) unsigned default '0',
  `notes` text,
  `pref_lang` char(2) default NULL,
  `pref_theme` varchar(50) default NULL,
  `idimport` int(11) default '0',
  `incomplete` enum('true','false') default 'false',
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `u_sysLibUsername` (`sysCode`,`libCode`,`username`),
  UNIQUE KEY `u_sysLibBarcode` (`sysCode`,`libCode`,`userbarcode`),
  UNIQUE KEY `u_sysLibSid` (`sysCode`,`libCode`,`sid`),
  KEY `i_lastname` (`lastname`),
  KEY `i_homeroom` (`homeroom`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_userImg`
--

CREATE TABLE `opl_userImg` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL,
  `userbarcode` varchar(18) default NULL,
  `orgFile` mediumblob,
  `thumbnail` mediumblob,
  `mimeType` varchar(255) default NULL,
  `fileName` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `u_user` (`uid`,`userbarcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_userimport`
--

CREATE TABLE `opl_userimport` (
  `idimport` int(10) unsigned NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL default '0',
  `dateImport` datetime default NULL,
  `dateUpload` datetime NOT NULL default '0000-00-00 00:00:00',
  `data` mediumtext NOT NULL,
  `countProcessed` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`idimport`),
  KEY `i_uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_webLinks`
--

CREATE TABLE `opl_webLinks` (
  `title` varchar(254) default '0',
  `description` text,
  `url` varchar(254) default NULL,
  `subject` text,
  `grade` varchar(254) default 'All'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

CREATE TABLE `opl_zDatabase` (
  `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(20) NOT NULL default 'default',
  `syntax` tinyint(3) unsigned NOT NULL default '0',
  `encoding` enum('marc8','utf8') NOT NULL default 'utf8',
  `username` varchar(20) default NULL,
  `password` varchar(20) default NULL,
  `description` varchar(255) NOT NULL default '',
  `state` tinyint(3) unsigned default '1',
  `stateList` tinyint(3) unsigned default '1',
  `filter` varchar(255) NOT NULL default '',
  `circData` tinyint(3) unsigned NOT NULL default '0',
  `studentILL` tinyint(3) unsigned NOT NULL default '0',
  `locationId` varchar(50) default NULL,
  PRIMARY KEY  (`zid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_zResult`
--

CREATE TABLE `opl_zResult` (
  `uid` int(10) unsigned NOT NULL default '0',
  `resultId` int(10) unsigned NOT NULL default '0',
  `content` mediumtext,
  `holding` text,
  `syntax` varchar(10) default NULL,
  `encoding` enum('marc8','utf8') NOT NULL default 'utf8',
  KEY `i_zResult` (`resultId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



CREATE TABLE `pf_ridMap` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rid` int(10) unsigned NOT NULL DEFAULT '0',
  `pfId` int(10) unsigned NOT NULL,
  `modDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `notes` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_2` (`id`,`pfId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `pf_record`
--


CREATE TABLE `pf_record` (
  `pfId` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(255) default NULL,
  `author` varchar(255) default NULL,
  `authPosition` varchar(255) default NULL,
  `introduction` text,
  `subjectArea` varchar(255) default NULL,
  `createdDate` datetime default NULL,
  `expiryDate` datetime default NULL,
  `modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `audience` varchar(255) character set latin1 default NULL,
  `xml` text,
  PRIMARY KEY  (`pfId`),
  UNIQUE KEY `rid` (`pfId`),
  KEY `i_title` (`pfId`,`title`,`createdDate`,`modDate`),
  KEY `i_subjectArea` (`pfId`,`subjectArea`,`createdDate`,`modDate`),
  FULLTEXT KEY `Index_4` (`title`,`introduction`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `pf_sbjAreaList`
--

CREATE TABLE `pf_sbjAreaList` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `subjectArea` varchar(255) default NULL,
  UNIQUE KEY `id` (`id`),
  KEY `Index_4` USING BTREE (`subjectArea`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `pf_webRs`
--

CREATE TABLE `pf_webRs` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `pfId` int(10) unsigned NOT NULL,
  `title` varchar(255) default NULL,
  `author` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  `description` text,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`,`pfId`),
  KEY `Index_4` USING BTREE (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_courseList`
--

CREATE TABLE `tb_courseList` (
  `courseId` int(10) unsigned NOT NULL auto_increment,
  `courseCode` varchar(45) NOT NULL,
  `courseName` varchar(150) default NULL,
  `descriptions` text,
  `grade` varchar(45) default NULL,
  `schoolYear` varchar(45) default NULL,
  `level` varchar(45) default NULL,
  `instructionHours` varchar(45) default NULL,
  `prerequisite` varchar(45) default NULL,
  PRIMARY KEY  (`courseId`),
  KEY `Index_2` (`courseCode`,`courseName`,`grade`,`level`,`schoolYear`),
  FULLTEXT KEY `Index_3` (`courseName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `tb_csvDuplicate`
--

CREATE TABLE `tb_csvDuplicate` (
  `mid` int(10) NOT NULL auto_increment,
  `iid` int(10) unsigned NOT NULL default '0',
  `content` mediumtext NOT NULL,
  PRIMARY KEY  (`mid`),
  KEY `i_iid` (`iid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_csvImport`
--

CREATE TABLE `tb_csvImport` (
  `iid` int(10) unsigned NOT NULL auto_increment,
  `sid` int(10) unsigned default '0',
  `lid` int(10) unsigned default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `sCode` varchar(50) default NULL,
  `dCode` varchar(50) default NULL,
  `lCode` varchar(50) default NULL,
  `itemType` varchar(50) default NULL,
  `bcSelfGen` set('1','0') default '0',
  `bcStart` varchar(50) default NULL,
  `noHolding` enum('1','0') default '0',
  `filename` varchar(255) default NULL,
  `data` mediumtext,
  `header` mediumtext,
  `mapHeader` mediumtext,
  `dataDup` mediumtext,
  `cMerge` tinyint(1) unsigned default '1',
  `countImported` int(10) unsigned default '0',
  `countMerged` int(10) unsigned default '0',
  `countProcessed` int(10) unsigned default '0',
  `countTotal` int(10) unsigned NOT NULL default '0',
  `countTotalHolding` int(10) default NULL,
  `dateImport` datetime default NULL,
  `dateUpload` datetime NOT NULL default '0000-00-00 00:00:00',
  `mesgDigest` varchar(128) default NULL,
  `status` enum('uploaded','rejected','processed','accepted','done') default NULL,
  `addToNewItemList` tinyint(1) unsigned default '0',
  `newItemExpDate` datetime default NULL,
  PRIMARY KEY  (`iid`),
  KEY `i_uid` (`uid`),
  KEY `i_lid` (`lid`),
  KEY `i_msgDigest` (`mesgDigest`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_hold`
--

CREATE TABLE `tb_hold` (
  `id` int(10) NOT NULL auto_increment,
  `idReserve` int(10) unsigned NOT NULL default '0',
  `barcode` varchar(50) NOT NULL default '',
  `dateHold` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateExpiry` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateLoan` datetime default NULL,
  `dateCancel` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `i_hold` (`idReserve`,`barcode`,`dateLoan`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_index_map`
--

CREATE TABLE `tb_index_map` (
  `id` int(10) NOT NULL auto_increment,
  `fieldId` varchar(100) NOT NULL,
  `fieldName` varchar(255) NOT NULL,
  `subfield` char(1) NOT NULL,
  `repeatable` tinyint(3) unsigned NOT NULL default '0',
  `required` tinyint(3) unsigned NOT NULL default '0',
  `fieldType` tinyint(3) NOT NULL default '0',
  `fId` varchar(10) default NULL,
  `removable` enum('1','0') NOT NULL default '0',
  `selected` enum('1','0') NOT NULL default '0',
  `displayOrder` tinyint(3) NOT NULL,
  `showed` enum('1','0') NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `u_insf` (`fieldId`,`fieldType`,`subfield`),
  KEY `i_fId` (`fId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_itemStatus`
--

CREATE TABLE `tb_itemStatus` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `barcode` varchar(50) NOT NULL default '',
  `ondate` datetime default NULL,
  `status` tinyint(4) default '0',
  `note` varchar(60) default NULL,
  PRIMARY KEY  (`id`),
  KEY `index1` (`barcode`,`ondate`,`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_items`
--

CREATE TABLE `tb_items` (
  `id` int(10) NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL default '0',
  `barcode` varchar(50) NOT NULL default '',
  `available` tinyint(4) default '1',
  `typeId` varchar(100) NOT NULL default '',
  `locationCode` varchar(40) default NULL,
  `price` varchar(50) default NULL,
  `classNumber` varchar(40) default NULL,
  `acquisitionDate` date default NULL,
  `PONumber` varchar(100) default NULL,
  `distributor` varchar(100) default NULL,
  `regionCode` varchar(40) default NULL,
  `districtCode` varchar(40) default NULL,
  `buildingCode` varchar(40) default NULL,
  `budgetCategory` varchar(255) default NULL,
  `vendorCatalogNumber` varchar(255) default NULL,
  `importDate` datetime default NULL,
  `modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `updating` enum('1','0') NOT NULL default '0',
  `deleted` enum('1','0') NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `barcode` (`barcode`,`deleted`),
  KEY `r_index1` (`rid`,`modDate`,`importDate`,`available`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Table structure for table `tb_loan`
--

CREATE TABLE `tb_loan` (
  `id` int(10) NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL default '0',
  `barcode` varchar(50) NOT NULL default '',
  `maxRenewal` tinyint(3) unsigned default '0',
  `dateLoan` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateDue` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateRenewal` datetime default NULL,
  `dateReturn` datetime default NULL,
  `renewalCount` tinyint(4) default '0',
  `finerate` float NOT NULL default '0',
  `status` tinyint(4) default '0',
  `type` varchar(50) default NULL,
  `teacherScheduleId` int(10) unsigned default NULL,
  `teacherName` varchar(255) default NULL,
  `courseName` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `u_bcdl` (`barcode`,`dateLoan`),
  KEY `i_loan` (`uid`,`barcode`,`dateLoan`),
  KEY `i_barcode` (`barcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_marcDuplicate`
--

CREATE TABLE `tb_marcDuplicate` (
  `mid` int(10) unsigned NOT NULL auto_increment,
  `iid` int(10) unsigned NOT NULL default '0',
  `content` mediumtext,
  PRIMARY KEY  (`mid`),
  KEY `i_iid` (`iid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_marcImport`
--

CREATE TABLE `tb_marcImport` (
  `iid` int(10) unsigned NOT NULL auto_increment,
  `sid` int(10) unsigned default '0',
  `lid` int(10) unsigned default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `encoding` enum('UTF-8','MARC-8') NOT NULL default 'UTF-8',
  `sCode` varchar(50) default NULL,
  `lCode` varchar(50) default NULL,
  `itemType` varchar(50) default NULL,
  `cMerge` tinyint(1) unsigned default '1',
  `countImported` int(10) unsigned default '0',
  `countMerged` int(10) unsigned default '0',
  `countProcessed` int(10) unsigned default '0',
  `countTotal` int(10) unsigned NOT NULL default '0',
  `dateImport` datetime default NULL,
  `dateUpload` datetime NOT NULL default '0000-00-00 00:00:00',
  `mesgDigest` varchar(128) default '',
  `status` enum('uploaded','rejected','accepted','indexing','done') NOT NULL default 'uploaded',
  `addToNewItemList` tinyint(1) unsigned default '0',
  `newItemExpDate` datetime default NULL,
  PRIMARY KEY  (`iid`),
  KEY `i_uid` (`uid`),
  KEY `i_lid` (`lid`),
  KEY `i_mesgDigest` (`mesgDigest`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_odl`
--

CREATE TABLE `tb_odl` (
  `odl_id` int(10) unsigned NOT NULL auto_increment,
  `idloan` int(10) unsigned default '0',
  `type` set('overdue','lost','damaged') default 'overdue',
  `days_overdue` tinyint(3) unsigned default '0',
  `ondate` datetime NOT NULL default '0000-00-00 00:00:00',
  `settleDate` datetime default NULL,
  PRIMARY KEY  (`odl_id`),
  KEY `NewIndex` (`odl_id`,`idloan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_records`
--

CREATE TABLE `tb_records` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL,
  `fId` varchar(10) NOT NULL,
  `fVal` varchar(255) default NULL,
  `updating` enum('0','1') NOT NULL default '0',
  `deleted` enum('1','0') NOT NULL default '0',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `i_fVal` (`fVal`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_reserve`
--

CREATE TABLE `tb_reserve` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned NOT NULL default '0',
  `uid` int(10) unsigned NOT NULL default '0',
  `numCopyReserve` tinyint(4) default '1',
  `dateReserve` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateExpiry` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateCancel` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `i_reserve` (`rid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_teacherSchedule`
--

CREATE TABLE `tb_teacherSchedule` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `courseId` int(8) unsigned default NULL,
  `courseCode` varchar(45) default NULL,
  `teacherId` varchar(45) default NULL,
  `createdDate` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `Index_2` (`courseCode`,`teacherId`,`courseId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_teachers`
--

CREATE TABLE `tb_teachers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `teacherId` varchar(45) NOT NULL,
  `lastName` varchar(45) default NULL,
  `firstName` varchar(45) default NULL,
  `addrLine1` varchar(150) default NULL,
  `addrLine2` varchar(150) default NULL,
  `city` varchar(45) default NULL,
  `state` varchar(45) default NULL,
  `zip` varchar(25) default NULL,
  `phone` varchar(45) default NULL,
  `country` varchar(45) default 'USA',
  `email` varchar(150) default NULL,
  `birthday` date default NULL,
  `importId` int(11) default NULL,
  `gender` tinyint(1) default NULL,
  `status` tinyint(1) default NULL,
  `notes` text,
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `Index_2` USING BTREE (`teacherId`),
  FULLTEXT KEY `Index_3` (`lastName`,`firstName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `tb_transactiondetail`
--

CREATE TABLE `tb_transactiondetail` (
  `tid` int(10) NOT NULL,
  `odl_id` int(10) unsigned NOT NULL default '0',
  `responsible` varchar(64) default '',
  `materials` set('book','video','DVD','reserve material') default 'book',
  `rate` float(10,2) default '0.00',
  `overdueDays` tinyint(3) unsigned default '0',
  `fineAmount` float(10,2) default '0.00',
  `forgivenAmount` float(10,2) default '0.00',
  `amount` float(10,2) default '0.00',
  `taxes` float(10,2) default '0.00',
  `note` varchar(254) default '',
  PRIMARY KEY  (`tid`),
  KEY `NewIndex` (`tid`,`odl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_transactions`
--

CREATE TABLE `tb_transactions` (
  `tid` int(10) unsigned NOT NULL auto_increment,
  `ptid` int(10) unsigned NOT NULL default '0',
  `code` smallint(6) default NULL,
  `uid` int(10) unsigned NOT NULL default '0',
  `amount` float(10,2) default '0.00',
  `balance` float(10,2) NOT NULL default '0.00',
  `date` datetime default '0000-00-00 00:00:00',
  `description` varchar(50) default NULL,
  `settleDate` datetime default NULL,
  PRIMARY KEY  (`tid`),
  UNIQUE KEY `tid` (`tid`),
  KEY `tid_2` (`tid`,`uid`,`date`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `opl_emailRequest`
--

CREATE TABLE `opl_emailRequest` (
  `eid` int(10) unsigned NOT NULL auto_increment,
  `email` varchar(255) default NULL,
  `uid` int(10) unsigned NOT NULL default '0',
  `sendDate` datetime default NULL,
  `status` enum('sent','waiting','rejected') NOT NULL default 'waiting',
  `retryCount` tinyint(5) unsigned NOT NULL default '0',
  `requestDate` datetime default NULL,
  `modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`eid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `opl_emailDetails`
--

CREATE TABLE `opl_emailDetails` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `eid` int(10) unsigned NOT NULL default '0',
  `eContent` text,
  `eBarcodes` text,
  `subject` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `opl_bcmBc`
--

CREATE TABLE `opl_bcmBc` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `rid` int(10) unsigned default NULL,
  `barcode` varchar(50) NOT NULL default '',
  `status` varchar(25) NOT NULL default 'available',
  `pending` int(10) default '-1',
  `pendingFor` varchar(25) default '',
  `sessionid` varchar(40) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `rid_bc` (`rid`,`barcode`),
  KEY `barcode` (`barcode`),
  KEY `status` (`status`(1)),
  KEY `index_1` (`sessionid`,`pending`,`pendingFor`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

--
-- Table structure for table `opl_bcmRange`
--

CREATE TABLE `opl_bcmRange` (
  `rid` int(10) unsigned NOT NULL auto_increment,
  `vid` int(10) unsigned default NULL,
  `bcLen` tinyint(4) default NULL,
  `preFix` varchar(10) default NULL,
  `rangeFr` int(10) unsigned default NULL,
  `rangeTo` int(10) unsigned default NULL,
  PRIMARY KEY  (`rid`),
  UNIQUE KEY `range` (`vid`,`preFix`,`bcLen`,`rangeFr`,`rangeTo`),
  KEY `vid` (`vid`),
  KEY `preFix` (`preFix`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

--
-- Table structure for table `opl_bcmVendor`
--

CREATE TABLE `opl_bcmVendor` (
  `vid` int(10) unsigned NOT NULL auto_increment,
  `vendor` varchar(40) NOT NULL default 'Local',
  PRIMARY KEY  (`vid`),
  UNIQUE KEY `vendor` (`vendor`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

--
-- Table structure for table `opl_portalLayout`
--
CREATE TABLE `opl_portalLayout` (
  `tid` int(10) unsigned NOT NULL default '0',
  `cid` int(10) unsigned NOT NULL auto_increment,
  `colPos` tinyint(3) unsigned NOT NULL default '1',
  `rowPos` tinyint(3) unsigned NOT NULL default '1',
  `cWidth` smallint(4) default NULL,
  `cHeight` smallint(4) default NULL,
  UNIQUE KEY `cid` (`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_portlet`
--

CREATE TABLE `opl_portlet` (
  `pid` int(10) unsigned NOT NULL auto_increment,
  `cid` int(10) unsigned NOT NULL,
  `ordinal` tinyint(3) default NULL,
  `type` enum('aboutUs','rss','search','libraryHours','newItemList','newsEvents','image','webLinks','pathfinders','weather','reviewItemList','calendar','html','pfShowcase','libShowcase') default NULL,
  UNIQUE KEY `pid` (`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_portletProp`
--
CREATE TABLE `opl_portletProp` (
  `pid` int(10) unsigned NOT NULL,
  `var` varchar(255) default NULL,
  `val` text,
  UNIQUE KEY `propId` (`pid`,`var`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


--
-- Table structure for table `opl_portalTheme`
--
CREATE TABLE `opl_portalTheme` (
  `tid` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `css` varchar(255) default NULL,
  UNIQUE KEY `tid` (`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `pf_subject` (
  `pfId` int(10) unsigned NOT NULL,
  `subject` varchar(255) default NULL,
  KEY `index1` USING BTREE (`pfId`,`subject`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



CREATE TABLE `opl_featureItem` (   
    `id` int(10) unsigned NOT NULL auto_increment,   
    `rid` int(10) unsigned NOT NULL default '1',  
    `feature` varchar(40) NOT NULL default '',   
    `expiryDate` datetime default NULL,   
    `modDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
     PRIMARY KEY  (`id`),   
     UNIQUE KEY `rid_feature` (`rid`,`feature`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `sip_session`
--

CREATE TABLE `sip_session` (
  `sessionid` varchar(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `sc` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`sessionid`),
  KEY `i_sc` (`sc`),
  KEY `i_ip` (`ipaddress`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Table structure for table `opl_ridBid`
--

CREATE TABLE `opl_ridBid` (
  `rid` int(10) unsigned NOT NULL,
  `bid` int(10) unsigned NOT NULL,
  UNIQUE KEY `u_ridBid` (`bid`,`rid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


--
-- Table structure for table `opl_ebLoan`
--
CREATE TABLE `opl_ebLoan` (
  `idloan` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `bid` int(10) unsigned DEFAULT NULL,
  `uid` int(10) unsigned NOT NULL,
  `dateLoan` datetime DEFAULT NULL,
  `dateDue` datetime DEFAULT NULL,
  `dateReturn` datetime DEFAULT NULL,
  `note` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`idloan`),
  KEY `i_loan` (`bid`,`uid`,`dateLoan`,`dateDue`,`dateReturn`)
) ENGINE=MyISAM DEFAULT  CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Table structure for table `opl_ebReserve`
--

CREATE TABLE `opl_ebReserve` (
  `idReserve` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idloan` int(10) unsigned NOT NULL DEFAULT '0',
  `bid` int(10) unsigned DEFAULT NULL,
  `uid` int(10) unsigned NOT NULL,
  `dateReserve` datetime DEFAULT NULL,
  `dateExpiry` datetime DEFAULT NULL,
  `dateCancel` datetime DEFAULT NULL,
  `notified` tinyint(3) unsigned DEFAULT '0',
  PRIMARY KEY (`idReserve`),
  KEY `i_reserve` (`bid`,`uid`),
  KEY `idloan` (`idloan`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;




--
-- Table structure for table `eb_bookmark`
--

CREATE TABLE `eb_bookmark` (
  `bmId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `bid` int(10) unsigned DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `note` text COLLATE utf8_unicode_ci,
  `fid` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `percFr` varchar(25) COLLATE utf8_unicode_ci DEFAULT '0.00',
  `percTo` varchar(25) COLLATE utf8_unicode_ci DEFAULT '0.00',
  `bmDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bmId`),
  UNIQUE KEY `u_key` (`uid`,`bid`,`fid`,`percFr`,`percTo`),
  KEY `bmkKey` (`uid`,`bid`,`fid`),
  KEY `percent` (`percFr`,`percTo`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Table structure for table `eb_bookshelves`
--

CREATE TABLE `eb_bookshelves` (
  `bsId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `rid` int(10) unsigned DEFAULT NULL,
  `note` varchar(255) NOT NULL DEFAULT '',
  `addDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`bsId`),
  UNIQUE KEY `u_key` (`uid`,`rid`),
  KEY `uid` (`uid`),
  KEY `rid` (`rid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `eb_lastAccessPage`
--

CREATE TABLE `eb_lastAccessPage` (
  `placeId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `fid` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `percent` varchar(25) COLLATE utf8_unicode_ci DEFAULT '0.00',
  `lastAccess` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`placeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Table structure for table `eb_licMgnt`
--

CREATE TABLE `eb_licMgnt` (
  `licId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `bid` int(10) unsigned DEFAULT NULL,
  `licenseType` set('free','paid','rental') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'paid',
  `noLicense` int(10) unsigned NOT NULL DEFAULT '0',
  `datePurchase` datetime DEFAULT NULL,
  `modDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `poNum` varchar(50) NOT NULL DEFAULT '',
  `vendor` varchar(255) NOT NULL DEFAULT '',
  `deleted` smallint(3) DEFAULT '0',
  `startDate` datetime DEFAULT NULL,
  `endDate` datetime DEFAULT NULL,
  `note` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`licId`),
  KEY `bid` (`bid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Table structure for table `eb_licMgntLog`
--

CREATE TABLE `eb_licMgntLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `logDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `licId` int(10) unsigned NOT NULL,
  `uid` int(10) unsigned NOT NULL,
  `bid` int(10) unsigned DEFAULT NULL,
  `licenseType` set('free','paid','rental') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `noLicense` int(10) unsigned NOT NULL DEFAULT '0',
  `datePurchase` datetime DEFAULT NULL,
  `modDate` datetime NOT NULL,
  `poNum` varchar(50) NOT NULL DEFAULT '',
  `vendor` varchar(255) NOT NULL DEFAULT '',
  `startDate` datetime DEFAULT NULL,
  `endDate` datetime DEFAULT NULL,
  `note` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `id` (`licId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `opl_srchLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `page` varchar(100) DEFAULT '',
  `searchType` varchar(100) DEFAULT 'regular',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `query` text,
  `hits` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ind_1` (`page`,`hits`,`date`,`searchType`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `opl_reserveShelf` (
  `shelfId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `author` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `introduction` text CHARACTER SET latin1,
  `teacher` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `course` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `createdDate` datetime DEFAULT NULL,
  `reserveFrom` datetime DEFAULT '0000-00-00 00:00:00',
  `reserveTo` datetime DEFAULT '0000-00-00 00:00:00',
  `loanPeriod` int(10) unsigned NOT NULL DEFAULT '0',
  `loanType` set('hourly','daily','default') COLLATE utf8_unicode_ci DEFAULT 'daily',
  `modDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted` tinyint(3) unsigned DEFAULT '0',
  PRIMARY KEY (`shelfId`),
  KEY `key1` (`title`,`author`,`teacher`,`course`,`loanPeriod`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

CREATE TABLE `opl_reserveShelfUsers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shelfId` int(10) unsigned NOT NULL,
  `uid` int(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key1` (`shelfId`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE `opl_reserveShelfItems` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shelfId` int(10) unsigned NOT NULL,
  `groupId` int(10) unsigned NOT NULL DEFAULT '1',
  `rid` int(10) unsigned NOT NULL,
  `deleted` tinyint(3) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

CREATE TABLE `opl_reserveShelfSubject` (
  `shelfId` int(10) unsigned NOT NULL,
  `subject` varchar(255) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE `opl_reserveShelfGroup` (
  `groupId` int(10) unsigned NOT NULL DEFAULT '0',
  `shelfId` int(10) unsigned NOT NULL,
  `header` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `description` text CHARACTER SET utf8,
  `deleted` tinyint(3) unsigned DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE `opl_ILL_out` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned DEFAULT NULL,
  `ILL_id` int(10) unsigned NOT NULL DEFAULT '0',
  `ILL_loanId` varchar(20) NOT NULL DEFAULT '',
  `ILL_lid` int(10) unsigned NOT NULL,
  `ILL_lName` varchar(150) DEFAULT '',
  `barcode` varchar(20) NOT NULL DEFAULT '',
  `status` enum('loan','return','receive','lost','found') DEFAULT NULL,
  `dateLoan` datetime DEFAULT NULL,
  `dateDue` datetime DEFAULT NULL,
  `dateReturn` datetime DEFAULT NULL,
  `dateReqReceived` datetime DEFAULT NULL,
  `dateLost` datetime DEFAULT NULL,
  `dateFound` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `opl_ILL_in` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ILL_id` int(10) unsigned NOT NULL DEFAULT '0',
  `ILL_loanId` varchar(20) DEFAULT NULL,
  `ILL_lid` int(10) unsigned NOT NULL,
  `ILL_lName` varchar(150) DEFAULT '',
  `ILL_itemBc` varchar(20) DEFAULT '',
  `local_bc` varchar(50) NOT NULL DEFAULT '',
  `status` enum('loan','return','receive','lost','found') DEFAULT NULL,
  `dateLoan` datetime DEFAULT NULL,
  `dateDue` datetime DEFAULT NULL,
  `dateReturn` datetime DEFAULT NULL,
  `dateResReceived` datetime DEFAULT NULL,
  `dateLost` datetime DEFAULT NULL,
  `dateFound` datetime DEFAULT NULL,
  `title` text,
  `author` text,
  `isbn` varchar(100) DEFAULT NULL,
  `sf852h` varchar(255) NOT NULL DEFAULT '',
  `sf852i` varchar(255) NOT NULL DEFAULT '',
  `sf852k` varchar(255) NOT NULL DEFAULT '',
  `pubDate` varchar(50) DEFAULT NULL,
  `pubPlace` varchar(150) DEFAULT NULL,
  `pubName` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;





insert into opl_user 
    (username, password, firstname, lastname, created, pw_timestamp, permission,permissions, status) 
    values 
    ('admin', 'X6M5u7se6s7TtS5U9EV2qvDXfZY', 'Admin', 'Library', now(), now(), 'yyyyyyyyyyyyyyyyyyyy','user_add,user_edit,user_delete,marc_edit,pref_edit,circ_loan,circ_return,circ_rsrv,circ_rsrv_self,fine,notice,report,bibsrc,pf,rating,tb_record_edit,payment,tb_circ_loan,tb_circ_return,tb_circ_rsrv,tb_circ_rsrv_self,tb_fine,tb_notice,tb_report,eq_record_edit,eq_circ_loan,eq_circ_return,eq_circ_rsrv,eq_circ_rsrv_self,eq_report,tb_user_edit,tb_course_edit,circ_ill_filter,ebook_mgmt', 1);

insert into opl_zDatabase (name, description) 
    values
    (database(), 'My Library');
--    (database(), concat(upper(database()), ' Server'));
update opl_zDatabase set zid=zid-1;
insert into opl_zDatabase (name, description, host, port, syntax, encoding, state, stateList) 
    values
    ('union', 'SCOOLS Union (NYS)', 'scools.org', 210, 0, 'utf8', 0, 1),
    ('voyager', 'Library of Congress', 'z3950.loc.gov', 7090, 1, 'marc8', 0, 1),
    ('innopac', 'Access Pennsylvania', '205.247.101.11', 210, 1, 'marc8', 0, 1);

insert into opl_category (catname, maxloans, maxreserv) 
    values
    ('Student', 6, 0),
    ('Faculty', 40, 0),
    ('Staff', 20, 0),
    ('Inter Library Loan', 50, 0),
    ('Other', 2, 0),
    ('Non-circ', 99, 0);

INSERT INTO `opl_itemCategory` 
    VALUES 
    (1,'Library'),(2,'Textbook'),(3,'Equipment');

insert into opl_itemType (id, description, defaultType) 
    values
    ('G', 'General', 1),
    ('E-book', '', 1);

insert into opl_itemTypeParam (itemTypeId, userTypeId, loanPeriod, renewalPeriod, reservePeriod, holdPeriod, gracePeriod, maxRenewal) 
    values
    ('G', 1, 240, 240, 240, 240, 240, 10),
    ('G', 2, 240, 240, 240, 240, 240, 10),
    ('G', 3, 240, 240, 240, 240, 240, 10),
    ('G', 4, 240, 240, 240, 240, 240, 10),
    ('G', 5, 240, 240, 240, 240, 240, 10),
    ('G', 6, 240, 240, 240, 240, 240, 10),
    ('E-book', 1, 240, 240, 240, 240, 240, 10),
    ('E-book', 2, 240, 240, 240, 240, 240, 10),
    ('E-book', 3, 240, 240, 240, 240, 240, 10),
    ('E-book', 4, 240, 240, 240, 240, 240, 10),
    ('E-book', 5, 240, 240, 240, 240, 240, 10),
    ('E-book', 6, 240, 240, 240, 240, 240, 10);

INSERT INTO `opl_prefFormParam` 
    VALUES 
    ('allowOverdue',0,'override automatically without approval','2'),
    ('allowOverdue',0,'override with question','1'),
    ('allowOverdue',0,'no override','0'),
    ('autoAdd2NewItemList',0,'On','1'),
    ('autoAdd2NewItemList',1,'Off','0'),
    ('autoUpdateUnion',0,'Enable','1'),
    ('autoUpdateUnion',1,'Disable','0'),
    ('barcodeType',0,'default','0'),
    ('barcodeType',0,'Follett','1'),
    ('barcodeType',0,'Spectrum','2'),
    ('barcodeType',0,'Alexandria-Leading Zeros','3'),
    ('barcodeType',0,'Sagebrush','4'),
    ('bibformat',0,'APA','apa'),
    ('bibformat',0,'MLA','mla'),
    ('bibformat',0,'Shelf','shelf'),
    ('bibTimeclear',0,'minute','m'),
    ('bibTimeclear',1,'hour','h'),
    ('bibTimeclear',2,'day','d'),
    ('charge_damage',0,'On','1'),
    ('charge_damage',1,'Off','0'),
    ('charge_lost',0,'On','1'),
    ('charge_lost',1,'Off','0'),
    ('charge_overdue',0,'On','1'),
    ('charge_overdue',1,'Off','0'),
    ('circulationSound',0,'On','1'),
    ('circulationSound',1,'Off','0'),
    ('defaultSearchPage',0,'standard','0'),
    ('defaultSearchPage',0,'beginner','1'),
    ('defaultSearchPage',0,'advance','2'),
    ('defaultSearchPage',0,'OWL Beginner Interface','3'),
    ('equipmentMgmnt',0,'Enable','1'),
    ('equipmentMgmnt',1,'Disable','0'),
    ('fine',0,'On','1'),
    ('fine',1,'Off','0'),
    ('lang',0,'English','en'),
    ('lang',1,'French' ,'fr'),
    ('lang',2,'Spanish','es'),
    ('lang',3,'Portuguese','pt'),
    ('lang',4,'Arabic','ar'),
    ('lang',5,'Russian','ru'),
    ('multilingual',0,'English','en'),
    ('multilingual',1,'French' ,'fr'),
    ('multilingual',2,'Spanish','es'),
    ('multilingual',3,'Portuguese','pt'),
    ('multilingual',4,'Arabic','ar'),
    ('multilingual',5,'Russian','ru'),
    ('OWLBgSrchFields',2,'author','author'),
    ('OWLBgSrchFields',0,'keyword','keyword'),
    ('OWLBgSrchFields',1,'title','title'),
    ('patronItemPrivacy',0,'On','1'),
    ('patronItemPrivacy',1,'Off','0'),
    ('rating',0,'disable the reader rating item','0'),
    ('rating',0,'enable the reader rating item in expand record page','1'),
    ('rating',0,'enable the reader rating item in search result and expand record pages','2'),
    ('showARL',0,'On','1'),
    ('showARL',1,'Off','0'),
    ('showBeginnerSrch',0,'Hide all','0'),
    ('showBeginnerSrch',0,'Show all','1'),
    ('showBeginnerSrch',0,'show Beginner only','2'),
    ('showBeginnerSrch',0,'show OWL beginner only','3'),
    ('showLocation',0,'On','1'),
    ('showLocation',1,'Off','0'),
    ('showPathfinder',0,'On','1'),
    ('showPathfinder',1,'Off','0'),
    ('textbookChargeDamage',0,'On','1'),
    ('textbookChargeDamage',1,'Off','0'),
    ('textbookChargeLost',0,'On','1'),
    ('textbookChargeLost',1,'Off','0'),
    ('textbookChargeOverdue',0,'On','1'),
    ('textbookChargeOverdue',1,'Off','0'),
    ('textbookFine',0,'On','1'),
    ('textbookFine',1,'Off','0'),
    ('textbookMgmnt',0,'Enable','1'),
    ('textbookMgmnt',1,'Disable','0'),
    ('timeclear',0,'minute','m'),
    ('timeclear',1,'hour','h'),
    ('timeclear',2,'day','d'),
    ('timeout',0,'minute','m'),
    ('timeout',1,'hour','h'),
    ('timeout',2,'day','d'),
    ('unionCircUpdate',0,'Enable','1'),
    ('unionCircUpdate',1,'Disable','0'),
    ('ILL_Messaging',0,'Enable','1'),
    ('ILL_Messaging',1,'Disable','0'),
    ('useHolidays',0,'Extend loan period a day for each holiday','1'),
    ('useHolidays',1,'Count holiday as part of loan period','0'),
    ('validateBarcode',0,'Enable','1'),
    ('validateBarcode',1,'Disable','0'),
    ('timezone',13,'(GMT  0.0) Western Europe Time, London, Lisbon, Casablanca','Europe/London'),
    ('timezone',14,'(GMT +1.0) Brussels, Copenhagen, Madrid, Paris','Europe/Brussels'),
    ('timezone',24,'(GMT +10.0) Eastern Australia, Guam, Vladivostok','Australia/Sydney'),
    ('timezone',25,'(GMT +11.0) Magadan, Solomon Islands, New Caledonia','Asia/Magadan'),
    ('timezone',26,'(GMT +12.0) Auckland, Wellington, Fiji, Kamchatka','Pacific/Fiji'),
    ('timezone',15,'(GMT +2.0) Kaliningrad, South Africa','Europe/Kaliningrad'),
    ('timezone',16,'(GMT +3.0) Baghdad, Riyadh, Moscow, St. Petersburg','Europe/Moscow'),
    ('timezone',16,'(GMT +3.5) Tehran','Asia/Tehran'),
    ('timezone',17,'(GMT +4.0) Abu Dhabi, Muscat, Baku, Tbilisi','Asia/Baku'),
    ('timezone',17,'(GMT +4.5) Kabul','Asia/Kabul'),
    ('timezone',18,'(GMT +5.0) Ekaterinburg, Islamabad, Karachi, Tashkent','Asia/Karachi'),
    ('timezone',18,'(GMT +5.5) Bombay, Calcutta, Madras, New Delhi','Indian/Maldives'),
    ('timezone',19,'(GMT +6.0) Almaty, Dhaka, Colombo','Asia/Almaty'),
    ('timezone',20,'(GMT +7.0) Bangkok, Hanoi, Jakarta','Asia/Bangkok'),
    ('timezone',21,'(GMT +8.0) Beijing, Perth, Singapore, Hong Kong','Asia/Hong_Kong'),
    ('timezone',22,'(GMT +9.0) Tokyo, Seoul, Osaka, Sapporo, Yakutsk','Asia/Tokyo'),
    ('timezone',23,'(GMT +9.5) Adelaide, Darwin','Australia/Adelaide'),
    ('timezone',12,'(GMT -1.0) Azores, Cape Verde Islands','Atlantic/Azores'),
    ('timezone',2,'(GMT -10.0) Hawaii','Pacific/Honolulu'),
    ('timezone',1,'(GMT -11.0) Midway Island, Samoa','Pacific/Midway'),
    ('timezone',0,'(GMT -12.0) Eniwetok, Kwajalein','Pacific/Kwajalein'),
    ('timezone',11,'(GMT -2.0) Mid-Atlantic','America/Noronha'),
    ('timezone',9,'(GMT -3.0) Brazil, Buenos Aires, Georgetown','America/Sao_Paulo'),
    ('timezone',10,'(GMT -3.5) Newfoundland','America/St_Johns'),
    ('timezone',8,'(GMT -4.0) Atlantic Time (Canada), Caracas, La Paz','America/Caracas'),
    ('timezone',7,'(GMT -5.0) Eastern Time (US &amp; Canada), Bogota, Lima','America/Montreal'),
    ('timezone',7,'(GMT -5.0) Jamaica, Lima, Peru, Panama','America/Jamaica'),
    ('timezone',6,'(GMT -6.0) Central Time (US &amp; Canada), Mexico City','America/Chicago'),
    ('timezone',5,'(GMT -7.0) Mountain Time (US &amp; Canada)','America/Edmonton'),
    ('timezone',4,'(GMT -8.0) Pacific Time (US &amp; Canada)','America/Vancouver'),
    ('timezone',3,'(GMT -9.0) Alaska','America/Anchorage'),
    ('OWLBgSrchFields',3,'subject','subject'),
    ('defaultSearchResultSort',0,'Publication date','31'),
    ('defaultSearchResultSort',1,'Title','4'),
    ('defaultSearchResultSort',2,'Author','1003'),
    ('defaultSearchResultSort',3,'Callnumber','5001'),
    ('defaultSearchResultSort',4,'Location','8008'),
    ('defaultSearchResultSort',5,'Relevance','0'),
    ('bookingReserve',0,'On','1'),
    ('bookingReserve',1,'Off','0'),
    ('googlePreview',0,'On','1'),
    ('googlePreview',1,'Off','0'),
    ('selfcreateAccount',1,'Off','0'),
    ('selfcreateAccount',0,'On','1'),
    ('hit_counter',0,'On',1),
    ('hit_counter',1,'Off',0),
    ('classificationSystem',0,'Dewey Decimal System','dewey'),
    ('classificationSystem',1,'Library of Congress classification','LCC'),
    ('OpacLoginRequired',0,'No login required','0'),
    ('OpacLoginRequired',1,'Login required','1'),
    ('slipPrinter',0,'Disable','0'),
    ('slipPrinter',1,'Enable','1'),
    ('unionExportExcludeTypes',1,'book','book'),
    ('unionExportExcludeTypes',2,'bookManuscript','bookManuscript'),
    ('unionExportExcludeTypes',3,'ebook','ebook'),
    ('unionExportExcludeTypes',4,'journal','journal'),
    ('unionExportExcludeTypes',5,'serial','serial'),
    ('unionExportExcludeTypes',6,'electronicMedia','electronicMedia'),
    ('unionExportExcludeTypes',7,'internet','internet'),
    ('unionExportExcludeTypes',8,'compactDisc','compactDisc'),
    ('unionExportExcludeTypes',9,'map','map'),
    ('unionExportExcludeTypes',10,'mapManuscript','mapManuscript'),
    ('unionExportExcludeTypes',11,'bookOnTape','bookOnTape'),
    ('unionExportExcludeTypes',13,'musicCassette','musicCassette'),
    ('unionExportExcludeTypes',14,'CDMusic','CDMusic'),
    ('unionExportExcludeTypes',15,'manuscriptMusic','manuscriptMusic'),
    ('unionExportExcludeTypes',16,'sheetMusic','sheetMusic'),
    ('unionExportExcludeTypes',17,'artifact','artifact'),
    ('unionExportExcludeTypes',18,'photoPoster','photoPoster'),
    ('unionExportExcludeTypes',19,'libraryKit','libraryKit'),
    ('unionExportExcludeTypes',20,'mixedMaterial','mixedMaterial'),
    ('unionExportExcludeTypes',21,'movie','movie'),
    ('unionExportExcludeTypes',22,'videocassette','videocassette'),
    ('unionExportExcludeTypes',23,'DVD','DVD'),
    ('unionExportExcludeTypes',24,'blu_ray','blu_ray'),
    ('ebookModule',0,'Enable','1'),
    ('ebookModule',1,'Disable','0'),
    ('showLocationFilter',0,'Enable','1'),
    ('showLocationFilter',1,'Disable','0')

    ;


INSERT INTO `opl_prefGroup` 
    VALUES 
    (1,'General Preferences Settings'),
	(2,'Circulation Preferences Settings'),
	(3,'Overdue & Fine Management'),
	(4,'Data Import / Export Management'),
	(5,'OPAC Display Preferences Settings'),
	(6,'OPAC / Database Preferences Settings'),
	(7,'Reports Preferences Settings'),
	(8,'Local Catalog  Union Catalog Preferences Settings'),
	(9,'Additional Modules'),
    (10,'Ebook Settings');

INSERT INTO `opl_preference` 
    VALUES 
    ('theme','opals','opals','string','text','Theme directory',1,5,21),
    ('lang','en','English','string','select','Language directory',0,1,6),
    ('country','US','US','','text','',0,1,1),
--    ('libcode','zTest','zTest','string','text','Library code',0,1,7),
--    ('libname','OPALS Test site','OPALS Test site','String','text','',0,1,8),
    ('link1_name','OPALS School Demo','OPALS School Demo','string','text','1st primary link',0,5,14),
    ('link1_url','http://ztest.scoolaid.net/','http://ztest.scoolaid.net/','string','text','1st primary url',0,5,15),
    ('link2_name','Supported by OPALS-na.org','Supported by OPALS-na.org','string','text','2nd primary link',0,5,16),
    ('link2_url','http://opals-na.org/','http://opals-na.org/','string','text','2nd primary url',0,5,17),
    ('cssTheme','0','0','number','','CSS Overlay Theme',1,0,0),
    ('slogan','Thanks for looking at OPALS','Thanks for looking at OPALS','string','text','slogan of library',0,5,20),
    ('fdrtax','0.0','0.0','number','text','Goods and services tax (GST)',0,0,0),
    ('prvtax','0.0','0.0','number','text','Provincial/Harmonized sales tax (PST/HST)',0,0,0),
    ('fine','0','Off','integer','radio','Turn on/off fine system',0,3,2),
    ('cerf','0','0','string','text','Enable/disable CERF search',1,6,1),
    ('emailSmtp','','','','text','SMTP server',0,1,4),
    ('dateProfile','US-NY','US-NY','','text','',1,0,0),
    ('reportGroup','3,2','3,2','0,1,2,3','text','1:homeroom; 2: teacher;  3: grade; 4: phone; 5:student ID',0,7,1),
    ('odNoticeMsgTop','The following items that you borrowed from the library/media center are now past due:','','','text','',0,7,2),
    ('odNoticeMsgBottom','Please return any items that may be overdue. Thank you in advance for your cooperation.','','','text','',0,7,3),
    ('itemonloanMsgTop','Item on loan','','','text','',0,7,4),
    ('itemonloanMsgBottom','The following items are checked out.','','','text','',0,7,5),
--    ('dateFirst','2008-09-03','2008-09-03','','text','',1,0,0),
--    ('dateLast','2010-06-14','2012-06-29','','text','',1,0,0),
    ('timeout','200m','200m','string','timeLength','Session time out',0,1,9),
    ('timeclear','4h','4h','string','timeLength','Screen time out',0,5,22),
    ('searchAdv','4,and,1003,or,21,or,5,or,12','4,and,1003,or,21,or,5,or,12','see manual','text','Advance search configuration',0,5,2),
    ('useAttOrder','list:1016,9016,4,9004,5006,1003,21,5001,5000,13,12,1005,1004,31,7,8,16,9,5002,63,59,1018,5,6','list:1016,9016,4,9004,5006,1003,21,5001,5000,13,12,1005,1004,31,7,8,16,9,5002,63,59,1018,5,6','see manual','text','Use attribute order',0,5,3),
    ('emailAddress','noone@noreply.org','noone@noreply.org','','text','OPALS generic email account. Format: "Full Name" <someone@example.com>',0,1,2),
    ('validateBarcode','0','Disable','binary','radio','Enable/disable barcode validation',0,2,2),
    ('union852','',' ','integer','text','Union display: 0-code, 1-lib, 2-both',1,8,1),
    ('iuset','1','1','','text','',0,0,0),
    ('iumap','sid,Student Number,firstname,First Name,lastname,Last Name,addrLine1,Mailing Street,city,Mailing City,state,Mailing State,zip,Mailing Zip,phone,Home Phone,birthday,Birthday,gender,Gender,homeroom,Homeroom,teacher,HR Teacher Last Name,grade,Grade Level,yeargraduation,Graduation Year','sid,Student Number,firstname,First Name,lastname,Last Name,addrLine1,Mailing Street,city,Mailing City,state,Mailing State,zip,Mailing Zip,phone,Home Phone,birthday,Birthday,gender,Gender,homeroom,Homeroom,teacher,HR Teacher Last Name,grade,Grade Level,yeargraduation,Graduation Year','','text','',1,0,0),
    ('amazonId','','','string','text','Amazon Web Service ID',0,5,7),
    ('barcodeType','0','default','0|1|2|3','select','Barcode Type',0,2,1),
    ('warningDatetime','','','','text','Date and time to show warning message',1,0,0),
    ('allowOverdue','1','override with question','integer','select','Overdue allowance setting',0,3,1),
    ('bibformat','apa','APA','','select','Default bibliography format',0,5,12),
    ('defCategory','Student','Student','','text','Default user category',0,2,4),
    ('overwrittenByBarcode','0','0','0|1','text','To overwrite user data when importing users',1,0,0),
    ('recordLock','','','','text','Marc record lock/unlock',1,0,0),
    ('lockingDatetime','','','','text','Date and time to lock site',1,0,0),
    ('emailUser','','','','text','Email account username',0,1,5),
    ('emailPass','','','','text','Email account password',0,1,3),
    ('selfcreateAccount','0','Off','','radio','allow/disallow self register account.',0,1,10),
    ('hit_counter',1,'On','0|1','radio','enable/disable hit counter',0,1,11),
    ('defaultSearchPage','0','standard','0|1|2|3','select','Default search interface',0,5,1),
    ('bibTimeclear','10m','10m','string','timeLength','bibiography time clear',0,5,13),
    ('useHolidays','1','exclude holidays in circulation','0|1','radio','Include = Count holiday as part of loan period. Exclude = Extend loan period a day for each holiday',0,3,3),
    ('charge_damage','0','Off','1','radio','set system fine for damage.',0,3,4),
    ('charge_lost','0','Off','1','radio','set system fine for Lost',0,3,5),
    ('charge_overdue','0','Off','1','radio','set system fine for Overdue',0,3,6),
    ('patronItemPrivacy','0','Off','1|0','radio','set system allow to show or hide patron information',0,2,5),
    ('slipPrinter','0','Disable','1|0','radio','Enable/Disable use of slip printer.',0,2,5),
    ('syndeticsId','','','nysbsls','text','Syndetics client code \"ESUFS\"',0,5,9),
    ('circulationSound','1','On','0 | 1','radio','circulation sound system',0,2,3),
    ('showLocation','0','Off','0|1','radio','show item location($852 c)',0,5,11),
    ('textbookMgmnt','0','Disable','0|1','radio','Enable/Disable Textbook management module',0,9,2),
    ('showARL','0','Off','0|1','radio','Show accelerated readers/reading counts/lexile search.',0,5,24),
    
    ('learn360ClientKey','','','','text','learn360 URI mapping',0,5,26),
    ('learn360Location','','','','text','',0,5,27),

    ('libAddress','245 labrosse','245 labrosse','','text','',1,0,0),
    ('libCity','Montreal','Montreal','','text','',1,0,0),
    ('libState','QC','QC','','text','',1,0,0),
    ('libZip','H1H 1W1','H1H 1W1','','text','',1,0,0),
    ('libPhone','','','','text','',1,0,0),
    ('libFax','','','','text','',1,0,0),
    ('libEmail','','','','text','',1,0,0),
    ('showBeginnerSrch','0','Hide all','0|1|2|3','select','Beginner search interface setting',0,5,4),
    ('beginnerSrchLocation','','','','text','Beginner Search Location (#852 c)',0,5,4),
    ('multilingual','en','en','text','checkbox','Select supported languages',0,5,19),
    ('unionId','','','text','text','union authentication id used for updating union site(deletion/import)',0,8,2),
    ('autoUpdateUnion','0','Disable','0|1','radio','enable/disable auto update union',0,8,3),
    ('unionSvcUrl','','','url','text','union update web service url',0,8,4),
    ('autoAdd2NewItemList','1','On','0|1','radio',' Auto add to new item list in z-import,import or clone record;',0,5,10),
    ('equipmentMgmnt','0','Disable','0|1','radio','Enable/Disable Equipment management module',0,9,1),
    ('amazonSecreteKey','','','string','text',' Amazon Secret Access Key',0,5,8),
    ('rating','0','disable the reader rating item','0|1|2','select','Reader rating feature setting',0,5,23),
    ('OWLBgSrchFields','keyword,title,author','keyword,title,author','','checkbox','Search fields display in OWL Search Page',0,5,6),
    ('OWLsrchLocation','','','','text','OWL Search Location (#852 c)',0,5,5),
    ('showPathfinder','0','Off','0|1','radio','pathfinders setting',0,5,25),
    ('unionCircUpdate','0','Disable','0|1','radio','enable/disabled  auto update circulation data to union',0,8,5),
    ('ILL_Messaging','0','Disable','0|1','radio','enable/disabled  ILL messaging',0,8,8),
    ('unionCircUpdateDate','0000-00-00 00:00:00','0000-00-00 00:00:00','datetime','text','date and time in international format (YYYY-mm-dd HH:MM:SS)',1,8,6),
    ('textbookFine','0','Off','Integer','radio','Turn on/off Textbook fine system',0,9,3),
    ('textbookChargeLost','0','Off','Integer','radio','set Textbook fine system for Lost',0,9,4),
    ('textbookChargeDamage','0','Off','Integer','radio','set Textbook fine system for damage',0,9,5),
    ('textbookChargeOverdue','0','Off','Integer','radio','set Textbook fine system for overdue',0,9,6),
    ('timezone','America/Montreal','(GM4 -5.0) Eastern Time (US &amp; Canada), Bogota, Lima','','select','Time Zone',0,1,9),
    ('defaultSearchResultSort','31','Publication date','Integer','select','Default search result sort field',0,5,4),
    ('bookingReserve','1','On','0|1','radio','enable/disable Booking Reserve function',0,5,28),
    ('googlePreview','1','On','0|1','radio','Turn Google Preview Off/Off',0,5,29),
    ('classificationSystem','dewey', 'Dewey Decimal System','dewey|LCC','select','Dewey Decimal System',0,5,13),
    ('OpacLoginRequired','0','No','0|1','radio','Turn on/off OPAC Access login requirement',0,1,11),
    ('acs_uuid','','','string','text','ACS UUID',0,1,13),
    ('ebookModule','0','Disable','0|1','radio','enable / disable Ebook module',0,10,1),
    ('ebs_libCode','','','string','text','Opals eBook library code',0,10,2),
    ('ebs_host','','','string','text','Opals eBook server host',0,10,3),
    ('ebs_accessKey','','','string','text','Opals eBook access key',0,10,4),
    ('unionExportExcludeTypes','','','','checkbox','List of record types excluded when export',0,8,7),
    ('showLocationFilter','1','Enable','0|1','radio','enable / disable location search option',0,5,1)
   ;


insert into opl_extDatabase (page,name, url, param,rank,hidden,description) 
    values
    ('searchPage','C.E.R.F.','http://cerfinfo.com/cerfkoha.asp','keyword',0,0,NULL),
    ('searchPage','NOVEL-Databases','http://kbn.cerfinfo.com/novel.asp','term',0,1,NULL),
    ('searchPage','Quest-All','http://kbn.cerfinfo.com/questall.asp','src=Cerf&term',0,1,NULL),
    ('OWL','','','',1,0,''),
    ('OWL','','','',2,0,''),
    ('OWL','','','',3,0,''),
    ('OWL','','','',4,0,''),
    ('OWL','','','',5,0,'');
    
insert into opl_openHours 
    (weekday, open, close)
    values
    (1,'8:00','11:30'),
    (1,'13:30','15:30'),
    (2,'8:00','11:30'),
    (2,'13:30','15:30'),
    (3,'8:00','11:30'),
    (3,'13:30','15:30'),
    (4,'8:00','11:30'),
    (4,'13:30','15:30'),
    (5,'8:00','12:00'),
    (5,'13:00','15:00'),
    (6,'',''),
    (0,'','');

INSERT INTO `opl_index_map` 
    VALUES 
    ('12','Control-number-local','001','',NULL),
    ('9','Control-number-LC','010','a',NULL),
    ('7','Identifier-ISBN','020','a',NULL),
    ('8','Identifier-ISSN','022','a',NULL),
    ('8','Identifier-ISSN','440','x',NULL),
    ('8','Identifier-ISSN','490','x',NULL),
    ('8','Identifier-ISSN','700','x',NULL),
    ('8','Identifier-ISSN','710','x',NULL),
    ('8','Identifier-ISSN','711','x',NULL),
    ('8','Identifier-ISSN','730','x',NULL),
    ('13','Classification-Dewey','082','a',NULL),
    ('16','Classification-LC','050','a',NULL),
    ('16','Classification-LC','490','l',NULL),
    ('4','Title','130','a,t',NULL),
    ('4','Title','210','a',NULL),
    ('4','Title','222','a',NULL),
    ('4','Title','240','a',NULL),
    ('6','Title-uniform','240','a',NULL),
    ('4','Title','242','a,b',NULL),
    ('4','Title','243','a',NULL),
    ('4','Title','245','a,b,p',NULL),
    ('4','Title','246','a,b',NULL),
    ('4','Title','247','a,b',NULL),
    ('4','Title','440','a',NULL),
    ('4','Title','490','a',NULL),
    ('5','Title-series','440','a',NULL),
    ('5','Title-series','490','a',NULL),
    ('4','Title','505','t',NULL),
    ('4','Title','700','t',NULL),
    ('4','Title','710','t',NULL),
    ('4','Title','711','t',NULL),
    ('4','Title','730','a,t',NULL),
    ('4','Title','740','a',NULL),
    ('4','Title','800','c,t',NULL),
    ('4','Title','810','t',NULL),
    ('4','Title','811','t',NULL),
    ('4','Title','830','t',NULL),
    ('5002','Medium','245','h',NULL),
    ('59','Name-geographic-place-publication','260','a',NULL),
    ('1018','Name-publisher','260','b',NULL),
    ('31','Date-publication','260','c',NULL),
    ('63','Note','500','a',NULL),
    ('63','Note','501','a',NULL),
    ('63','Note','502','a',NULL),
    ('63','Note','503','a',NULL),
    ('63','Note','504','a',NULL),
    ('63','Note','505','a',NULL),
    ('63','Note','506','a',NULL),
    ('63','Note','507','a',NULL),
    ('63','Note','508','a',NULL),
    ('63','Note','509','a',NULL),
    ('63','Note','510','a',NULL),
    ('63','Note','511','a',NULL),
    ('63','Note','512','a',NULL),
    ('63','Note','513','a',NULL),
    ('63','Note','514','a',NULL),
    ('63','Note','515','a',NULL),
    ('63','Note','516','a',NULL),
    ('63','Note','517','a',NULL),
    ('63','Note','518','a',NULL),
    ('63','Note','519','a',NULL),
    ('63','Note','520','a',NULL),
    ('63','Note','521','a',NULL),
    ('63','Note','522','a',NULL),
    ('63','Note','523','a',NULL),
    ('63','Note','524','a',NULL),
    ('63','Note','525','a',NULL),
    ('63','Note','526','a',NULL),
    ('63','Note','527','a',NULL),
    ('63','Note','528','a',NULL),
    ('63','Note','529','a',NULL),
    ('63','Note','530','a',NULL),
    ('63','Note','531','a',NULL),
    ('63','Note','532','a',NULL),
    ('63','Note','533','a',NULL),
    ('63','Note','534','a',NULL),
    ('63','Note','535','a',NULL),
    ('63','Note','536','a',NULL),
    ('63','Note','537','a',NULL),
    ('63','Note','538','a',NULL),
    ('63','Note','539','a',NULL),
    ('63','Note','540','a',NULL),
    ('63','Note','541','a',NULL),
    ('63','Note','542','a',NULL),
    ('63','Note','543','a',NULL),
    ('63','Note','544','a',NULL),
    ('63','Note','545','a',NULL),
    ('63','Note','546','a',NULL),
    ('63','Note','547','a',NULL),
    ('63','Note','548','a',NULL),
    ('63','Note','549','a',NULL),
    ('63','Note','550','a',NULL),
    ('63','Note','551','a',NULL),
    ('63','Note','552','a',NULL),
    ('63','Note','553','a',NULL),
    ('63','Note','554','a',NULL),
    ('63','Note','555','a',NULL),
    ('63','Note','556','a',NULL),
    ('63','Note','557','a',NULL),
    ('63','Note','558','a',NULL),
    ('63','Note','559','a',NULL),
    ('63','Note','560','a',NULL),
    ('63','Note','561','a',NULL),
    ('63','Note','562','a',NULL),
    ('63','Note','563','a',NULL),
    ('63','Note','564','a',NULL),
    ('63','Note','565','a',NULL),
    ('63','Note','566','a',NULL),
    ('63','Note','567','a',NULL),
    ('63','Note','568','a',NULL),
    ('63','Note','569','a',NULL),
    ('63','Note','570','a',NULL),
    ('63','Note','571','a',NULL),
    ('63','Note','572','a',NULL),
    ('63','Note','573','a',NULL),
    ('63','Note','574','a',NULL),
    ('63','Note','575','a',NULL),
    ('63','Note','576','a',NULL),
    ('63','Note','577','a',NULL),
    ('63','Note','578','a',NULL),
    ('63','Note','579','a',NULL),
    ('63','Note','580','a',NULL),
    ('63','Note','581','a',NULL),
    ('63','Note','582','a',NULL),
    ('63','Note','583','a',NULL),
    ('63','Note','584','a',NULL),
    ('63','Note','585','a',NULL),
    ('63','Note','586','a',NULL),
    ('63','Note','587','a',NULL),
    ('63','Note','588','a',NULL),
    ('63','Note','589','a',NULL),
    ('63','Note','590','a',NULL),
    ('63','Note','591','a',NULL),
    ('63','Note','592','a',NULL),
    ('63','Note','593','a',NULL),
    ('63','Note','594','a',NULL),
    ('63','Note','595','a',NULL),
    ('63','Note','596','a',NULL),
    ('63','Note','597','a',NULL),
    ('63','Note','598','a',NULL),
    ('63','Note','599','a',NULL),
    ('21','Subject','600','',NULL),
    ('21','Subject','601','',NULL),
    ('21','Subject','602','',NULL),
    ('21','Subject','603','',NULL),
    ('21','Subject','604','',NULL),
    ('21','Subject','605','',NULL),
    ('21','Subject','606','',NULL),
    ('21','Subject','607','',NULL),
    ('21','Subject','608','',NULL),
    ('21','Subject','609','',NULL),
    ('21','Subject','610','',NULL),
    ('21','Subject','611','',NULL),
    ('21','Subject','612','',NULL),
    ('21','Subject','613','',NULL),
    ('21','Subject','614','',NULL),
    ('21','Subject','615','',NULL),
    ('21','Subject','616','',NULL),
    ('21','Subject','617','',NULL),
    ('21','Subject','618','',NULL),
    ('21','Subject','619','',NULL),
    ('21','Subject','620','',NULL),
    ('21','Subject','621','',NULL),
    ('21','Subject','622','',NULL),
    ('21','Subject','623','',NULL),
    ('21','Subject','624','',NULL),
    ('21','Subject','625','',NULL),
    ('21','Subject','626','',NULL),
    ('21','Subject','627','',NULL),
    ('21','Subject','628','',NULL),
    ('21','Subject','629','',NULL),
    ('21','Subject','630','',NULL),
    ('21','Subject','631','',NULL),
    ('21','Subject','632','',NULL),
    ('21','Subject','633','',NULL),
    ('21','Subject','634','',NULL),
    ('21','Subject','635','',NULL),
    ('21','Subject','636','',NULL),
    ('21','Subject','637','',NULL),
    ('21','Subject','638','',NULL),
    ('21','Subject','639','',NULL),
    ('21','Subject','640','',NULL),
    ('21','Subject','641','',NULL),
    ('21','Subject','642','',NULL),
    ('21','Subject','643','',NULL),
    ('21','Subject','644','',NULL),
    ('21','Subject','645','',NULL),
    ('21','Subject','646','',NULL),
    ('21','Subject','647','',NULL),
    ('21','Subject','648','',NULL),
    ('21','Subject','649','',NULL),
    ('21','Subject','650','',NULL),
    ('21','Subject','651','',NULL),
    ('21','Subject','652','',NULL),
    ('21','Subject','653','',NULL),
    ('21','Subject','654','',NULL),
    ('21','Subject','655','',NULL),
    ('21','Subject','656','',NULL),
    ('21','Subject','657','',NULL),
    ('21','Subject','658','',NULL),
    ('21','Subject','659','',NULL),
    ('21','Subject','660','',NULL),
    ('21','Subject','661','',NULL),
    ('21','Subject','662','',NULL),
    ('21','Subject','663','',NULL),
    ('21','Subject','664','',NULL),
    ('21','Subject','665','',NULL),
    ('21','Subject','666','',NULL),
    ('21','Subject','667','',NULL),
    ('21','Subject','668','',NULL),
    ('21','Subject','669','',NULL),
    ('21','Subject','670','',NULL),
    ('21','Subject','671','',NULL),
    ('21','Subject','672','',NULL),
    ('21','Subject','673','',NULL),
    ('21','Subject','674','',NULL),
    ('21','Subject','675','',NULL),
    ('21','Subject','676','',NULL),
    ('21','Subject','677','',NULL),
    ('21','Subject','678','',NULL),
    ('21','Subject','679','',NULL),
    ('21','Subject','680','',NULL),
    ('21','Subject','681','',NULL),
    ('21','Subject','682','',NULL),
    ('21','Subject','683','',NULL),
    ('21','Subject','684','',NULL),
    ('21','Subject','685','',NULL),
    ('21','Subject','686','',NULL),
    ('21','Subject','687','',NULL),
    ('21','Subject','688','',NULL),
    ('21','Subject','689','',NULL),
    ('21','Subject','690','',NULL),
    ('21','Subject','691','',NULL),
    ('21','Subject','692','',NULL),
    ('21','Subject','693','',NULL),
    ('21','Subject','694','',NULL),
    ('21','Subject','695','',NULL),
    ('21','Subject','696','',NULL),
    ('21','Subject','697','',NULL),
    ('21','Subject','698','',NULL),
    ('21','Subject','699','',NULL),
    ('1003','Author-name','100','a,q',NULL),
    ('1003','Author-name','110','a,b',NULL),
    ('1003','Author-name','111','a,q',NULL),
    ('1003','Author-name','700','a,q',NULL),
    ('1003','Author-name','710','a,d',NULL),
    ('1003','Author-name','711','a',NULL),
    ('1003','Author-name','800','a,q',NULL),
    ('1003','Author-name','810','a',NULL),
    ('1003','Author-name','811','a',NULL),
    ('13','Classification-Dewey','852','h',NULL),
    ('5000','Bar-code','852','p',NULL),
    ('5001','Call-number-local','852','k,h,i,m',NULL),
    ('s4','Title-sort value','245','-',NULL),
    ('s31','Date-publication-sort value','260','-',NULL),
    ('s5001','Call number-sort value','852','-',NULL),
    ('s1003','Author-sort value','100','a',NULL),
    ('1016','Anywhere search attribute list',NULL,NULL,'attr_4,attr_5,attr_6,attr_21,attr_31,attr_59,attr_63,attr_1003');


INSERT INTO `tb_index_map` 
    VALUES 
    (1,'010','LCCN','a',0,0,1,'010_a','0','1',15,'1'),
	(2,'020','ISBN','a',0,0,1,'020_a','0','1',3,'1'),
	(3,'022','ISSN','a',0,0,1,'022_a','0','1',20,'1'),
	(4,'100','Author','a',1,0,1,'100_a','0','1',4,'1'),
	(5,'110','Corporate Author','a',1,0,1,'110_a','0','0',5,'1'),
	(6,'245','Title','a',0,1,1,'245_a','0','1',1,'1'),
	(7,'245','Subtitle','b',0,0,1,'245_b','0','1',2,'1'),
	(8,'250','Edition','a',0,0,1,'250_a','0','1',11,'1'),
	(9,'260','Place of publication','a',0,0,1,'260_a','0','0',13,'1'),
	(10,'260','Publisher','b',0,0,1,'260_b','0','1',12,'1'),
	(11,'260','Year of publication','c',0,0,1,'260_c','0','1',14,'1'),
	(12,'300','Pages','a',0,0,1,'300_a','0','1',8,'1'),
	(13,'490','Series','a',0,0,1,'490_a','0','1',6,'1'),
	(14,'490','Volume #','v',0,0,1,'490_v','0','1',7,'1'),
	(15,'500','General Note','a',0,0,1,'500_a','0','1',16,'1'),
	(16,'505','Detailed Contents Page','g',0,0,1,'505_g','0','1',17,'1'),
	(17,'520','Content Summary','a',0,0,1,'520_a','0','1',18,'1'),
	(18,'521','Reading Level','a',0,0,1,'521_a','0','1',19,'1'),
	(19,'650','Topics','a',1,0,1,'650_a','0','1',10,'1'),
	(20,'856','Publication URL & Description','u',1,0,3,'856_u','0','1',21,'1'),
	(21,'900','Grade Level','a',0,0,1,'900_a','1','1',22,'1'),
	(22,'900','Course Number','b',0,0,1,'900_b','1','0',23,'1'),
	(23,'900','Course Name','c',0,0,1,'900_c','1','1',24,'1'),
	(24,'900','Course Description','d',0,0,1,'900_d','0','0',25,'1'),
	(29,'300','Number of words','w',0,0,4,'300_w','0','1',9,'1'),
	(30,'900','Kit Contains - Lesson Plan(s)','e',0,0,5,'900_e','1','0',30,'1'),
	(31,'900','Kit Contains - CD','f',0,0,5,'900_f','0','0',26,'1'),
	(32,'900','Kit Contains - DVD','g',0,0,5,'900_g','0','0',27,'1'),
	(33,'900','Kit Contains - Books','h',0,0,5,'900_h','0','0',28,'1'),
	(34,'900','Kit Contains - Other','k',0,0,5,'900_k','0','0',29,'1'),
	(35,'005','modDate','',0,0,1,'005','0','0',31,'0');


INSERT INTO `eq_defRequired` 
    VALUES 
    (1,'record',1,'Equipment Name','Equipment Name',1,1,1,0),
	(2,'item',1,'Bar Code','Bar Code',1,1,1,0),
	(3,'item',2,'Item Type','Item Category Type',1,1,0,0);

INSERT INTO `eq_fieldDataType` 
    VALUES 
    (1,'Text'),
	(2,'Date'),
	(3,'Url Link'),
	(4,'Number'),
	(5,'Checkbox');

INSERT INTO `opl_transCode` 
    VALUES 
    (1,'overdue charged',''),
	(2,'lost charged',''),
	(3,'damage chaged',''),
	(4,'payment',''),
	(5,'forgive',''),
	(6,'credit lost refund',''),
	(7,'rate adjustment',''),
	(8,'refund',''),
    (9,'outstanding fine carried over from previous system','');

INSERT INTO `opl_permission` 
    VALUES 
    (1,1,'User Add','user_add'),
    (2,1,'User Edit','user_edit'),
    (3,1,'User Delete','user_delete'),
    (4,2,'Marc Edit','marc_edit'),
    (5,3,'Pref Edit','pref_edit'),
    (6,4,'Circ Loan','circ_loan'),
    (7,4,'Circ Return','circ_return'),
    (8,4,'Circ Reserve','circ_rsrv'),
    (9,4,'Self-Reserve','circ_rsrv_self'),
    (10,4,'Fine','fine'),
    (12,4,'Notice','notice'),
    (13,4,'Report','report'),
    (14,4,'Email Bib','bibsrc'),
    (15,5,'Path Finder','pf'),
    (16,5,'Rating','rating'),
    (18,7,'Record Edit','tb_record_edit'),
    (11,4,'Payment','payment'),
    (19,9,'Circ Loan','tb_circ_loan'),
    (20,9,'Circ Return','tb_circ_return'),
    (21,9,'Circ Reserve','tb_circ_rsrv'),
    (22,9,'Self-Reserve','tb_circ_rsrv_self'),
    (23,9,'Fine','tb_fine'),
    (24,9,'Notice','tb_notice'),
    (25,9,'Report','tb_report'),
    (27,12,'Record Edit','eq_record_edit'),
    (28,14,'Circ Loan','eq_circ_loan'),
    (29,14,'Circ Return','eq_circ_return'),
    (30,14,'Circ Reserve','eq_circ_rsrv'),
    (31,14,'Self-Reserve','eq_circ_rsrv_self'),
    (32,14,'Report','eq_report'),
    (17,6,'User Add','tb_user_edit'),
    (26,10,'Course Edit','tb_course_edit'),
    (33,4,'ILL Filter','circ_ill_filter'),
    (34,1,'eBook Mgmt','ebook_mgmt');

INSERT INTO `opl_permGroup` 
    VALUES 
    (1,1,'User'),
    (2,1,'Marc'),
    (3,1,'System'),
    (4,1,'Circ'),
    (5,1,'Misc'),
    (6,2,'User'),
    (7,2,'Marc'),
    (8,2,'System'),
    (9,2,'Circ'),
    (10,2,'Misc'),
    (11,3,'User'),
    (12,3,'Marc'),
    (13,3,'System'),
    (14,3,'Circ'),
    (15,3,'Misc');

INSERT INTO `opl_subSystem` 
    VALUES 
    (1,'Library'),
    (2,'Textbook'),
    (3,'Equipment');

INSERT INTO `opl_portalLayout` 
    VALUES 
    (1,1,1,1,678,NULL),
    (1,2,2,1,NULL,NULL);

INSERT INTO `opl_portalTheme` 
    VALUES (1,'basic',' /htdosc/theme/css/portal.css');    

INSERT INTO `opl_portlet` 
    VALUES 
    (1,1,null,'aboutUs'),
    (2,1,1,'newsEvents'),
    (3,1,2,'newItemList'),
    (4,2,1,'libraryHours'),
    (5,2,2,'pathfinders');

INSERT INTO `opl_portletProp` 
    VALUES 
    (2,'nItemShow','3'),
    (2,'fieldsShow','title'),
    (3,'showBookCover','true'),
    (3,'fieldsShow','title,author,pubDate'),
    (3,'nItemShow','9');

INSERT INTO `eq_def` 
    VALUES 
    (1,'record',1,'Manufacturer','Manufacturer',0,0,1,0),
    (3,'record',2,'Model','Model',0,0,1,0),
    (26,'item',3,'District Code','District Code',0,0,0,0),
    (14,'record',4,'Warranty Notes','Warranty Notes',0,0,1,0),
    (18,'item',4,'Building Code','Building Code',0,0,0,0),
    (16,'record',6,'Manufacturer URL','http://',0,0,0,2),
    (38,'item',8,'Items Status notes','Items Status notes',0,0,0,0),
    (28,'record',8,'Online Manual','http://',0,0,0,2),
    (27,'record',7,'Supplier URL','http://',0,0,1,2),
    (6,'item',2,'Price','Price',0,0,1,0),
    (7,'item',7,'District inventory #','District inventory #',0,0,1,3),
    (11,'item',5,'Location in building','Location in building',0,0,0,0),
    (5,'item',1,'Serial#','Serial number',0,0,1,0),
    (31,'item',6,'Date Acquired','Date Acquired',0,0,0,1),
    (33,'record',3,'General notes','General notes',0,0,1,0),
    (36,'record',5,'Accessories Note','Accessories Note',0,0,1,0);       
insert into opl_preference values 
    ('libcode','borrower2','borrower2','string','text','Library code',0,1,7),
    ('libname','Borrower 2','Borrower 2','string','text','Library name',0,1,8),
    ('dateFirst','2013-09-01','2013-09-01','','text','',1,NULL,NULL),
    ('dateLast','2014-06-30','2014-06-30','','text','',1,NULL,NULL);

delete from opl_libSystem;
delete from opl_library;
insert into opl_libSystem (sCode, sName) values
    ('-N\/A-', 'Not Applicable'),
    ('', 'New System');
insert into opl_library (sysCode, lCode, lName) values
    ('-N\/A-', '-N\/A-', 'Not Applicable'),
    ('', '', 'New Library');
insert into opl_libSystem set sCode=upper('ill'),sName='ILL';
insert into opl_library set sysCode=upper('ill'),lCode=upper('borrower2'),lName='Borrower 2';
