

CREATE TABLE  IF not EXISTS `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;

CREATE TABLE  IF not EXISTS `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  IF not EXISTS  `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  IF not EXISTS `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  IF not EXISTS `opl_reserveShelfItems` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shelfId` int(10) unsigned NOT NULL,
  `groupId` int(10) unsigned NOT NULL DEFAULT '1',
  `iOrder` 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  IF not EXISTS `opl_reserveShelfSubject` (
  `shelfId` int(10) unsigned NOT NULL,
  `subject` varchar(255) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE  IF not EXISTS `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 if not exists `opl_ILL_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned DEFAULT NULL,
  `lid` int(10) unsigned DEFAULT NULL,
  `lName` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE if not exists `opl_authCtrl` (
  `tag` varchar(3) NOT NULL DEFAULT '',
  `sfCode` char(1) NOT NULL DEFAULT '',
  `sfData` varchar(255) NOT NULL DEFAULT '',
  UNIQUE KEY `authCtrKey` (`tag`,`sfCode`,`sfData`),
  KEY `authCtr` (`sfData`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into opl_authCtrl select distinct '852',sf852Code,sf852Data from opl_itemInfo where sf852Code='3'||sf852Code='c' || sf852Code='k' && barcode not regexp '^___' order by sf852Code;

replace INTO opl_permission VALUES (34,1,'eBook Mgmt','ebook_mgmt');

alter table opl_category add `allowAccessMyFile` enum('0','1') NOT NULL DEFAULT '1' after `required` , add `authenticateBySID` enum('0','1') NOT NULL DEFAULT '0' after `allowAccessMyFile` ;
