X-Git-Url: https://git.rvb.name/openlib.git/blobdiff_plain/383d95da154278b563110813ded8f79ab9643efa..refs/heads/master:/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