X-Git-Url: https://git.rvb.name/openlib.git/blobdiff_plain/a47401f5fac121db5eb44214530121ab14cba2f3..6b3a07a008979ee27733a2deae2ff4fc42f4a535:/metadata.sql diff --git a/metadata.sql b/metadata.sql index 3e078ad..113734d 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=140889 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -45,7 +45,7 @@ CREATE TABLE `books` ( `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, @@ -56,7 +56,7 @@ CREATE TABLE `books` ( KEY `TITLEIDX` (`title`(255)), KEY `TIMESTAMPIDX` (`timestamp`), KEY `ISBNIDX` (`isbn`) -) ENGINE=InnoDB AUTO_INCREMENT=728948 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=1892665 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -75,8 +75,8 @@ CREATE TABLE `books_authors_link` ( 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` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION -) ENGINE=InnoDB AUTO_INCREMENT=523239 DEFAULT CHARSET=utf8; + 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; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -95,9 +95,36 @@ CREATE TABLE `books_languages_link` ( 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_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=488274 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=1667797 DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `books_old` +-- + +DROP TABLE IF EXISTS `books_old`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +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; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -114,9 +141,9 @@ CREATE TABLE `books_publishers_link` ( 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` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, + 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=204595 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=981343 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -133,9 +160,9 @@ CREATE TABLE `books_series_link` ( 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, + 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=179786 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=741933 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -153,9 +180,9 @@ CREATE TABLE `books_tags_link` ( 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_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=544808 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=2015931 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -171,8 +198,8 @@ CREATE TABLE `comments` ( `text` varchar(4000) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `book_UNIQUE` (`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; + 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; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -191,8 +218,23 @@ CREATE TABLE `data` ( PRIMARY KEY (`id`), 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; + 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; +/*!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 +265,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=217 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -238,7 +280,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 +295,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=31291 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -268,7 +310,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=46699 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -283,7 +325,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 +340,632 @@ 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 = '' */ ; +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 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 +976,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 19:39:15