X-Git-Url: https://git.rvb.name/openlib.git/blobdiff_plain/383d95da154278b563110813ded8f79ab9643efa..de4a5d3664fd9de7c8fd560745668c320c018b45:/metadata.sql?ds=sidebyside

diff --git a/metadata.sql b/metadata.sql
index 3e078ad..ac0d5e3 100644
--- a/metadata.sql
+++ b/metadata.sql
@@ -1,8 +1,8 @@
--- MySQL dump 10.13  Distrib 5.5.34, for debian-linux-gnu (x86_64)
+-- MySQL dump 10.13  Distrib 5.7.12, for Linux (x86_64)
 --
 -- Host: localhost    Database: metadata
 -- ------------------------------------------------------
--- Server version	5.5.34-0ubuntu0.13.10.1
+-- Server version	5.7.12-0ubuntu1
 
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
@@ -29,7 +29,7 @@ CREATE TABLE `authors` (
   PRIMARY KEY (`id`),
   UNIQUE KEY `NAMEIDX` (`name`) USING BTREE,
   KEY `SORTIDX` (`sort`) USING BTREE
-) ENGINE=InnoDB AUTO_INCREMENT=118680 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=141003 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -44,19 +44,19 @@ CREATE TABLE `books` (
   `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',
+  `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,
+  `pubdate` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `SORTIDX` (`sort`(255)),
   KEY `TITLEIDX` (`title`(255)),
   KEY `TIMESTAMPIDX` (`timestamp`),
   KEY `ISBNIDX` (`isbn`)
-) ENGINE=InnoDB AUTO_INCREMENT=728948 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=1893115 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -76,7 +76,7 @@ CREATE TABLE `books_authors_link` (
   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` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
-) ENGINE=InnoDB AUTO_INCREMENT=523239 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=2017688 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -92,12 +92,11 @@ CREATE TABLE `books_languages_link` (
   `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` (`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=488274 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=1668113 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -113,10 +112,10 @@ CREATE TABLE `books_publishers_link` (
   `publisher` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `books_publishers_link_aidx` (`publisher`),
-  KEY `books_publishers_link_bidx` (`book`),
+  KEY `fk_books_publishers_link_book_idx` (`book`),
   CONSTRAINT `fk_books_publishers_link_book` FOREIGN KEY (`book`) REFERENCES `books` (`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=204595 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=981535 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -132,10 +131,10 @@ CREATE TABLE `books_series_link` (
   `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` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
+  KEY `fk_books_series_link_book_idx` (`book`),
+  CONSTRAINT `fk_books_series_link_book` FOREIGN KEY (`book`) REFERENCES `books` (`id`) ON DELETE NO ACTION 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=179786 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=742109 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -150,12 +149,11 @@ CREATE TABLE `books_tags_link` (
   `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` (`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=544808 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=2016387 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -168,11 +166,11 @@ DROP TABLE IF EXISTS `comments`;
 CREATE TABLE `comments` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `book` int(11) DEFAULT NULL,
-  `text` varchar(4000) DEFAULT NULL,
+  `text` varchar(21000) DEFAULT NULL,
   PRIMARY KEY (`id`),
-  UNIQUE KEY `book_UNIQUE` (`book`),
+  KEY `fk_comments_book_idx` (`book`),
   CONSTRAINT `fk_comments_book` FOREIGN KEY (`book`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
-) ENGINE=InnoDB AUTO_INCREMENT=414407 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=1457394 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -192,7 +190,22 @@ CREATE TABLE `data` (
   KEY `fk_data_book_idx` (`book`),
   KEY `format_idx` (`format`),
   CONSTRAINT `fk_data_book` FOREIGN KEY (`book`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
-) ENGINE=InnoDB AUTO_INCREMENT=728543 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=1892214 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `directories`
+--
+
+DROP TABLE IF EXISTS `directories`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+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;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -223,7 +236,7 @@ CREATE TABLE `languages` (
   `lang_code` varchar(10) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `lang_code_UNIQUE` (`lang_code`)
-) ENGINE=InnoDB AUTO_INCREMENT=204 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=232 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -238,7 +251,7 @@ CREATE TABLE `processed_archives` (
   `filename` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `filename` (`filename`)
-) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=1035 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -253,7 +266,7 @@ CREATE TABLE `publishers` (
   `name` longtext NOT NULL,
   `sort` longtext,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=26395 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=31351 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -268,7 +281,7 @@ CREATE TABLE `series` (
   `name` longtext NOT NULL,
   `sort` longtext,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=39266 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=46776 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -283,7 +296,7 @@ CREATE TABLE `tags` (
   `name` longtext NOT NULL,
   PRIMARY KEY (`id`),
   KEY `nameidx` (`name`(32))
-) ENGINE=InnoDB AUTO_INCREMENT=478 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=548 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -298,8 +311,644 @@ CREATE TABLE `tags_mapping` (
   `tag_mask` varchar(45) NOT NULL,
   `tag_result` varchar(45) NOT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=316 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping events for database 'metadata'
+--
+
+--
+-- Dumping routines for database 'metadata'
+--
+/*!50003 DROP FUNCTION IF EXISTS `CreateBook` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `DeleteDoubleSpaces` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `GetOrCreateAuthor` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `GetOrCreateLang` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `GetOrCreatePublisher` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `GetOrCreateSeries` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `GetOrCreateTag` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `ProcessLang` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `ProcessMapping` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `ProcessTags` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `SortAuthor` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+DELIMITER ;;
+CREATE DEFINER=`admin`@`%` FUNCTION `SortAuthor`(pStr varchar(2000)) RETURNS varchar(2000) CHARSET utf8
+    DETERMINISTIC
+BEGIN
+  DECLARE lRes VARCHAR(2000);
+  SELECT MAX(SORT) INTO lRes FROM authors WHERE Name=pStr;
+  IF lRes is null THEN
+    SET lRes=SortStr(pStr);
+  ELSE
+    RETURN lRes;
+  END IF;  
+  IF REPLACE(lRes,' ','')='' THEN
+    SET lRes=UUID();
+  ELSE  
+    SET lRes=REPLACE(SplitAuthor(SortStr(pStr)),'NO ORIGINAL NAME FOR ','');
+  END IF;
+  RETURN lRes;
+END ;;
+DELIMITER ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `SortStr` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP FUNCTION IF EXISTS `SplitAuthor` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `DropTag` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
+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 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
+/*!50003 DROP PROCEDURE IF EXISTS `ProcessLangAliases` */;
+/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
+/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
+/*!50003 SET @saved_col_connection = @@collation_connection */ ;
+/*!50003 SET character_set_client  = utf8 */ ;
+/*!50003 SET character_set_results = utf8 */ ;
+/*!50003 SET collation_connection  = utf8_general_ci */ ;
+/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
+/*!50003 SET sql_mode              = '' */ ;
+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 ;
+/*!50003 SET sql_mode              = @saved_sql_mode */ ;
+/*!50003 SET character_set_client  = @saved_cs_client */ ;
+/*!50003 SET character_set_results = @saved_cs_results */ ;
+/*!50003 SET collation_connection  = @saved_col_connection */ ;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -310,4 +959,4 @@ CREATE TABLE `tags_mapping` (
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2013-11-30 18:05:09
+-- Dump completed on 2016-05-19 23:04:13
