--
-- Host: localhost    Database: metadata
-- ------------------------------------------------------


--
-- Table structure for table `authors`
--

CREATE TABLE `authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sort` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `NAMEIDX` (`name`) USING BTREE,
  KEY `SORTIDX` (`sort`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=140889 DEFAULT CHARSET=utf8;

--
-- Table structure for table `books`
--

CREATE TABLE `books` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(512) DEFAULT NULL,
  `sort` varchar(512) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `pubdate` timestamp NULL DEFAULT NULL,
  `series_index` decimal(10,2) DEFAULT '1.00',
  `isbn` varchar(45) DEFAULT NULL,
  `path` varchar(2000) DEFAULT NULL,
  `uuid` varchar(128) DEFAULT NULL,
  `has_cover` int(11) DEFAULT '0',
  `last_modified` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `SORTIDX` (`sort`(255)),
  KEY `TITLEIDX` (`title`(255)),
  KEY `TIMESTAMPIDX` (`timestamp`),
  KEY `ISBNIDX` (`isbn`)
) ENGINE=InnoDB AUTO_INCREMENT=1892665 DEFAULT CHARSET=utf8;

--
-- Table structure for table `books_authors_link`
--

CREATE TABLE `books_authors_link` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book` int(11) NOT NULL,
  `author` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `book` (`book`,`author`),
  KEY `books_authors_link_aidx` (`author`),
  KEY `books_authors_link_bidx` (`book`),
  CONSTRAINT `fk_books_authors_link_auth` FOREIGN KEY (`author`) REFERENCES `authors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_books_authors_link_books` FOREIGN KEY (`book`) REFERENCES `books_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2017231 DEFAULT CHARSET=utf8;

--
-- Table structure for table `books_languages_link`
--

CREATE TABLE `books_languages_link` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book` int(11) NOT NULL,
  `lang_code` int(11) NOT NULL,
  `item_order` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `book` (`book`,`lang_code`),
  KEY `books_languages_link_aidx` (`lang_code`),
  KEY `books_languages_link_bidx` (`book`),
  CONSTRAINT `fk_books_languages_link_book` FOREIGN KEY (`book`) REFERENCES `books_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_books_languages_link_lang` FOREIGN KEY (`lang_code`) REFERENCES `languages` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1667797 DEFAULT CHARSET=utf8;

--
-- Table structure for table `books_old`
--

CREATE TABLE `books_old` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(512) DEFAULT NULL,
  `sort` varchar(512) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `pubdate` timestamp NULL DEFAULT NULL,
  `series_index` decimal(4,2) DEFAULT '1.00',
  `isbn` varchar(45) DEFAULT NULL,
  `path` varchar(2000) DEFAULT NULL,
  `uuid` varchar(128) DEFAULT NULL,
  `has_cover` int(11) DEFAULT '0',
  `last_modified` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `SORTIDX` (`sort`(255)),
  KEY `TITLEIDX` (`title`(255)),
  KEY `TIMESTAMPIDX` (`timestamp`),
  KEY `ISBNIDX` (`isbn`)
) ENGINE=InnoDB AUTO_INCREMENT=1892532 DEFAULT CHARSET=utf8;

--
-- Table structure for table `books_publishers_link`
--

CREATE TABLE `books_publishers_link` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book` int(11) NOT NULL,
  `publisher` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `books_publishers_link_aidx` (`publisher`),
  KEY `books_publishers_link_bidx` (`book`),
  CONSTRAINT `fk_books_publishers_link_book` FOREIGN KEY (`book`) REFERENCES `books_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_books_publishers_link_pub` FOREIGN KEY (`publisher`) REFERENCES `publishers` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=981343 DEFAULT CHARSET=utf8;

--
-- Table structure for table `books_series_link`
--

CREATE TABLE `books_series_link` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book` int(11) NOT NULL,
  `series` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `books_series_link_aidx` (`series`),
  KEY `books_series_link_bidx` (`book`),
  CONSTRAINT `fk_books_series_link_book` FOREIGN KEY (`book`) REFERENCES `books_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_books_series_link_ser` FOREIGN KEY (`series`) REFERENCES `series` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=741933 DEFAULT CHARSET=utf8;

--
-- Table structure for table `books_tags_link`
--

CREATE TABLE `books_tags_link` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book` int(11) NOT NULL,
  `tag` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `book` (`book`,`tag`),
  KEY `books_tags_link_aidx` (`tag`),
  KEY `books_tags_link_bidx` (`book`),
  CONSTRAINT `fk_books_tags_link_book` FOREIGN KEY (`book`) REFERENCES `books_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_books_tags_link_tag` FOREIGN KEY (`tag`) REFERENCES `tags` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2015931 DEFAULT CHARSET=utf8;

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book` int(11) DEFAULT NULL,
  `text` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `book_UNIQUE` (`book`),
  CONSTRAINT `fk_comments_book` FOREIGN KEY (`book`) REFERENCES `books_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1457254 DEFAULT CHARSET=utf8;

--
-- Table structure for table `data`
--

CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `book` int(11) NOT NULL,
  `format` varchar(45) NOT NULL,
  `uncompressed_size` int(11) DEFAULT NULL,
  `name` varchar(2000) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_data_book_idx` (`book`),
  KEY `format_idx` (`format`),
  CONSTRAINT `fk_data_book` FOREIGN KEY (`book`) REFERENCES `books_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1892071 DEFAULT CHARSET=utf8;

--
-- Table structure for table `directories`
--

CREATE TABLE `directories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `path` varchar(256) NOT NULL,
  `descr` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `lang_alias`
--

CREATE TABLE `lang_alias` (
  `alias` varchar(32) NOT NULL,
  `lang_code` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`alias`),
  KEY `fk_lang_alias_1_idx` (`lang_code`),
  CONSTRAINT `fk_lang_alias_1` FOREIGN KEY (`lang_code`) REFERENCES `languages` (`lang_code`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `languages`
--

CREATE TABLE `languages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang_code` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lang_code_UNIQUE` (`lang_code`)
) ENGINE=InnoDB AUTO_INCREMENT=217 DEFAULT CHARSET=utf8;

--
-- Table structure for table `processed_archives`
--

CREATE TABLE `processed_archives` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `filename` (`filename`)
) ENGINE=InnoDB AUTO_INCREMENT=1035 DEFAULT CHARSET=utf8;

--
-- Table structure for table `publishers`
--

CREATE TABLE `publishers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` longtext NOT NULL,
  `sort` longtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31291 DEFAULT CHARSET=utf8;

--
-- Table structure for table `series`
--

CREATE TABLE `series` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` longtext NOT NULL,
  `sort` longtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46699 DEFAULT CHARSET=utf8;

--
-- Table structure for table `tags`
--

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `nameidx` (`name`(32))
) ENGINE=InnoDB AUTO_INCREMENT=548 DEFAULT CHARSET=utf8;

--
-- Table structure for table `tags_mapping`
--

CREATE TABLE `tags_mapping` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_mask` varchar(45) NOT NULL,
  `tag_result` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=utf8;

--
-- Dumping events for database 'metadata'
--

--
-- Dumping routines for database 'metadata'
--
/*!50003 DROP FUNCTION IF EXISTS `CreateBook` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `CreateBook`(title varchar(2000),pubdate datetime,series_index decimal(10,2),isbn varchar(45)) RETURNS int(11)
begin
  DECLARE lID INTEGER;
  INSERT INTO books(title,sort,pubdate,series_index,isbn) VALUES (title,SortStr(title),pubdate,series_index,isbn);
  select last_insert_id() into lID;
  RETURN lID;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `DeleteDoubleSpaces` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `DeleteDoubleSpaces`( title VARCHAR(250) ) RETURNS varchar(250) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(250);
    SET result = REPLACE( title, '  ', ' ' );
    WHILE (result <> title) DO 
        SET title = result;
        SET result = REPLACE( title, '  ', ' ' );
    END WHILE;
    RETURN result;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetOrCreateAuthor` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `GetOrCreateAuthor`(pAuthor varchar(255)) RETURNS int(11)
BEGIN
  declare lID integer;
  select min(ID) into lID from authors where name=pAuthor;
  if lID is null then
    insert into authors(name,sort)
	  values(pAuthor,SortAuthor(pAuthor));
    SET lID = last_insert_id();
  end if;
RETURN lID;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetOrCreateLang` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `GetOrCreateLang`(pLang varchar(255)) RETURNS int(11)
BEGIN
  declare lID integer;
  declare lLang varchar(255);
  select min(lang_code) into lLang from lang_alias where alias=pLang;
  if lLang is null then
    SET lLang = pLang;
  end if;
  select min(ID) into lID from languages where lang_code=lLang;
  if lID is null then
    insert into languages(lang_code)
	  values(lLang);
    SET lID = last_insert_id();
  end if;
RETURN lID;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetOrCreatePublisher` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `GetOrCreatePublisher`(pPub varchar(255)) RETURNS int(11)
BEGIN
  declare lID integer;
  select min(ID) into lID from publishers where name=pPub;
  if lID is null then
    insert into publishers(name,sort)
	  values(pPub,SortStr(pPub));
    SET lID = last_insert_id();
  end if;
RETURN lID;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetOrCreateSeries` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `GetOrCreateSeries`(pSeries varchar(255)) RETURNS int(11)
BEGIN
  declare lID integer;
  select min(ID) into lID from series where name=pSeries;
  if lID is null then
    insert into series(name,sort)
	  values(pSeries,SortStr(pSeries));
    SET lID = last_insert_id();
  end if;
RETURN lID;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GetOrCreateTag` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `GetOrCreateTag`(pTag varchar(255)) RETURNS int(11)
BEGIN
  declare lID integer;
  select min(ID) into lID from tags where name=pTag;
  if lID is null then
    insert into tags(name)
	  values(pTag);
    SET lID = last_insert_id();
  end if;
RETURN lID;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `ProcessLang` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `ProcessLang`(pOldLang varchar(64),pNewLang varchar(64)) RETURNS varchar(2000) CHARSET utf8
BEGIN
  declare Res varchar(2000);
  declare lOldID integer;
  declare lNewID integer;
  select id into lOldID from languages where lang_code=pOldLang;
  select id into lNewID from languages where lang_code=pNewLang;
  SET Res = CONCAT(lOldID ,' ',lNewID);
  insert into metadata.books_languages_link(book,lang_code)
  select book,lNewID from metadata.books_languages_link
    where lang_code=lOldID
     and book not in 
    (select book from metadata.books_languages_link
      where lang_code=lNewID);
  delete from metadata.books_languages_link
   where lang_code=lOldID;
  delete from metadata.languages where id=lOldID;
  return Res;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `ProcessMapping` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `ProcessMapping`() RETURNS int(11)
BEGIN
  DECLARE counter INTEGER;
  DECLARE done INT DEFAULT FALSE;
  DECLARE old_tag, new_tag,dummystr VARCHAR(255);
  DECLARE cur CURSOR FOR 
  SELECT t1.name,t2.name FROM metadata.tags t1,metadata.tags t2,metadata.tags_mapping m 
      where m.tag_result=t2.name and t1.name REGEXP m.tag_mask and t1.id<>t2.id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur;
  SET counter=0;
  tags: loop
    FETCH cur into old_tag,new_tag;
    IF done THEN
      LEAVE tags;
	END IF;
    SELECT ProcessTags(old_tag,new_tag) INTo dummystr;
    SET counter = counter+1;
  END loop;
  CLOSE cur;
  RETURN counter;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `ProcessTags` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `ProcessTags`(pOldTag varchar(64),pNewTag varchar(64)) RETURNS varchar(2000) CHARSET utf8
BEGIN
  declare Res varchar(2000);
  declare lOldID integer;
  declare lNewID integer;
  select id into lOldID from tags where name=pOldTag;
  select id into lNewID from tags where name=pNewTag;
  SET Res = CONCAT(lOldID ,' ',lNewID);
  insert into metadata.books_tags_link(book,tag)
  select book,lNewID from metadata.books_tags_link
    where tag=lOldID
     and book not in 
    (select book from metadata.books_tags_link
      where tag=lNewID);
  delete from metadata.books_tags_link
   where tag=lOldID;
  delete from metadata.tags where id=lOldID;
  return Res;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `SortAuthor` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `SortAuthor`(pStr varchar(2000)) RETURNS varchar(2000) CHARSET utf8
    DETERMINISTIC
BEGIN
  RETURN REPLACE(SplitAuthor(SortStr(pStr)),'NO ORIGINAL NAME FOR ','');
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `SortStr` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `SortStr`(pStr varchar(2000)) RETURNS varchar(2000) CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE v_char VARCHAR(1);
  DECLARE v_parseStr VARCHAR(2000) DEFAULT ' ';

SET pStr = UPPER(pStr);

IF pSTR LIKE 'A %' THEN
  SET pSTR=SUBSTR(pStr,3);
ELSEIF pSTR LIKE 'THE %' THEN
  SET pSTR=SUBSTR(pStr,5);
ELSEIF pSTR LIKE 'EL %' THEN
  SET pSTR=SUBSTR(pStr,4);
ELSEIF pSTR LIKE 'IL %' THEN
  SET pSTR=SUBSTR(pStr,4);
ELSEIF pSTR LIKE 'L\'%' THEN
  SET pSTR=SUBSTR(pStr,3);
ELSEIF pSTR LIKE 'LES %' THEN
  SET pSTR=SUBSTR(pStr,5);
ELSEIF pSTR LIKE 'DIE %' THEN
  SET pSTR=SUBSTR(pStr,5);
ELSEIF pSTR LIKE 'DER %' THEN
  SET pSTR=SUBSTR(pStr,5);
ELSEIF pSTR LIKE 'DAS %' THEN
  SET pSTR=SUBSTR(pStr,5);
ELSEIF pSTR LIKE 'LA %' THEN
  SET pSTR=SUBSTR(pStr,4);
ELSEIF pSTR LIKE 'LOS %' THEN
  SET pSTR=SUBSTR(pStr,5);
ELSEIF pSTR LIKE 'LÀ %' THEN
  SET pSTR=SUBSTR(pStr,4);
ELSEIF pSTR LIKE 'LE %' THEN
  SET pSTR=SUBSTR(pStr,4);
ELSEIF pSTR LIKE 'LAS %' THEN
  SET pSTR=SUBSTR(pStr,5);
ELSEIF pSTR LIKE 'UN %' THEN
  SET pSTR=SUBSTR(pStr,4);
ELSEIF pSTR LIKE 'UNA %' THEN
  SET pSTR=SUBSTR(pStr,5);
ELSEIF pSTR LIKE 'UNO %' THEN
  SET pSTR=SUBSTR(pStr,5);
END IF;

WHILE (i <= LENGTH(pStr) )  DO
 
  SET v_char = SUBSTR(pStr,i,1);
  
IF v_char REGEXP  '^[A-Z0-9АБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ ]+$' OR v_char = '-' THEN  #alphanumeric
        SET v_parseStr = CONCAT(v_parseStr,v_char);  
        SET v_char='';
  ELSEIF v_char='Š' collate 'utf8_bin' THEN
        SET v_parseStr = CONCAT(v_parseStr,'S');
        SET v_char='';
  ELSEIF v_char='Ð' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'Dj');
        SET v_char='';
  ELSEIF v_char='Ž' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'Z');
        SET v_char='';
  ELSEIF v_char='À' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'A');
        SET v_char='';
  ELSEIF v_char='Á' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'A');
        SET v_char='';
  ELSEIF v_char='Â' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'A');
        SET v_char='';
  ELSEIF v_char='Ã' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'A');
        SET v_char='';
  ELSEIF v_char='Ä' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'A');
        SET v_char='';
  ELSEIF v_char='Å' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'A');
        SET v_char='';
  ELSEIF v_char='Æ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'A');
        SET v_char='';
  ELSEIF v_char='Ç' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'C');
        SET v_char='';
  ELSEIF v_char='È' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'E');
        SET v_char='';
  ELSEIF v_char='É' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'E');
        SET v_char='';
  ELSEIF v_char='Ê' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'E');
        SET v_char='';
  ELSEIF v_char='Ë' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'E');
        SET v_char='';
  ELSEIF v_char='Ì' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'I');
        SET v_char='';
  ELSEIF v_char='Í' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'I');
        SET v_char='';
  ELSEIF v_char='Î' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'I');
        SET v_char='';
  ELSEIF v_char='Ï' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'I');
        SET v_char='';
  ELSEIF v_char='Ñ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'N');
        SET v_char='';
  ELSEIF v_char='Ò' THEN
        SET v_parseStr = CONCAT(v_parseStr,'O');
        SET v_char='';
  ELSEIF v_char='Ó' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'O');
        SET v_char='';
  ELSEIF v_char='Ô' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'O');
        SET v_char='';
  ELSEIF v_char='Õ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'O');
        SET v_char='';
  ELSEIF v_char='Ö' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'O');
        SET v_char='';
  ELSEIF v_char='Ø' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'O');
        SET v_char='';
  ELSEIF v_char='Ù' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'U');
        SET v_char='';
  ELSEIF v_char='Ú' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'U');
        SET v_char='';
  ELSEIF v_char='Û' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'U');
        SET v_char='';
  ELSEIF v_char='Ü' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'U');
        SET v_char='';
  ELSEIF v_char='Ý' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'Y');
        SET v_char='';
  ELSEIF v_char='Þ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'B');
        SET v_char='';
  ELSEIF v_char='ß' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'SS');
        SET v_char='';
  ELSEIF v_char='Ł' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'L');
        SET v_char='';
  ELSEIF v_char='Ĥ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'H');
        SET v_char='';
  ELSEIF v_char='Č' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'C');
        SET v_char='';
  END IF;

  IF v_char='Ґ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'Г');
        SET v_char='';
  ELSEIF v_char='І' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'И');  
        SET v_char='';
  ELSEIF v_char='Є' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'Е');  
        SET v_char='';
  ELSEIF v_char='Ї' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'Й');  
        SET v_char='';
  ELSEIF v_char='Ў' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'У');
        SET v_char='';
  ELSEIF v_char='Ђ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'ДЖ');
        SET v_char='';
  ELSEIF v_char='Џ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'ДЖ');
        SET v_char='';
  ELSEIF v_char='Ѓ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'Г');
        SET v_char='';
  ELSEIF v_char='Љ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'ЛЬ');
        SET v_char='';
  ELSEIF v_char='Њ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'HЬ');
        SET v_char='';
  ELSEIF v_char='Ћ' collate 'utf8_bin'  THEN
        SET v_parseStr = CONCAT(v_parseStr,'Ч');
        SET v_char='';
  END IF;

  IF v_char='$' THEN
        SET v_parseStr = CONCAT(v_parseStr,'S');
        SET v_char='';
  END IF;

  SET i = i + 1;
END WHILE;
SET v_parseStr=trim(DeleteDoubleSpaces(v_parseStr));
RETURN v_parseStr;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `SplitAuthor` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` FUNCTION `SplitAuthor`(pStr VARCHAR(2000)) RETURNS varchar(2000) CHARSET utf8
BEGIN
  DECLARE lLastName VARCHAR(2000);
  DECLARE lStr VARCHAR(2000);
  DECLARE lLastNameLen INTEGER;
  DECLARE lFirstName VARCHAR(2000);
  SET lStr=trim(pStr);
  SET lLastName=substring_index(lStr,' ',-1);
  IF lLastName<>lStr THEN
     SET lLastNameLen=char_length(lLastName);
     SET lFirstName=trim(substring(pStr,1,char_length(lStr)-lLastNameLen-1));
     RETURN concat(lLastName,' ',lFirstName);
  ELSE
    RETURN lStr;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DropTag` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `DropTag`(pOldTag varchar(64))
BEGIN
  declare lOldID integer;
  select id into lOldID from tags where name=pOldTag;
  delete from metadata.books_tags_link
    where tag=lOldID;
  delete from metadata.tags where id=lOldID;  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ProcessLangAliases` */;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `ProcessLangAliases`()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE alias,lang,dummystr VARCHAR(255);
  DECLARE cur CURSOR FOR 
    SELECT a.alias,a.lang_code FROM metadata.languages l,metadata.lang_alias a where l.lang_code=a.alias;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur;
  langs: loop
    FETCH cur into alias,lang;
    IF done THEN
      LEAVE langs;
    END IF;
    SELECT ProcessLang(alias,lang) INTO dummystr;
    END loop;
  CLOSE cur;
END ;;
DELIMITER ;


