From: Roman Bazalevsky Date: Thu, 19 May 2016 20:04:21 +0000 (+0300) Subject: Обработка "неприводимых к латинице и кириллице" имен авторов с возможностью ручного... X-Git-Url: https://git.rvb.name/openlib.git/commitdiff_plain/bb5933a6e7cbc75001b8de80b4dce4078ac5e76b?ds=inline;hp=bf89140dad76eb57b5acc0c0391a9481098e0849 Обработка "неприводимых к латинице и кириллице" имен авторов с возможностью ручного задания имени в таблице. --- diff --git a/metadata.sql b/metadata.sql index 113734d..ac0d5e3 100644 --- a/metadata.sql +++ b/metadata.sql @@ -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=140889 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(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=1892665 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=1893115 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_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION -) ENGINE=InnoDB AUTO_INCREMENT=2017231 DEFAULT CHARSET=utf8; + CONSTRAINT `fk_books_authors_link_books` FOREIGN KEY (`book`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=2017688 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -92,39 +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_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, + 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=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; +) ENGINE=InnoDB AUTO_INCREMENT=1668113 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -140,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`), - CONSTRAINT `fk_books_publishers_link_book` FOREIGN KEY (`book`) REFERENCES `books_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, + 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=981343 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=981535 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -159,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_old` (`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=741933 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=742109 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -177,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_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, + 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=2015931 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=2016387 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -195,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`), - 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; + 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=1457394 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -218,8 +189,8 @@ 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_old` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION -) ENGINE=InnoDB AUTO_INCREMENT=1892071 DEFAULT CHARSET=utf8; + CONSTRAINT `fk_data_book` FOREIGN KEY (`book`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=1892214 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -265,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=217 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=232 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -295,7 +266,7 @@ CREATE TABLE `publishers` ( `name` longtext NOT NULL, `sort` longtext, PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=31291 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=31351 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -310,7 +281,7 @@ CREATE TABLE `series` ( `name` longtext NOT NULL, `sort` longtext, PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=46699 DEFAULT CHARSET=utf8; +) ENGINE=InnoDB AUTO_INCREMENT=46776 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -646,12 +617,24 @@ DELIMITER ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET 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 - RETURN REPLACE(SplitAuthor(SortStr(pStr)),'NO ORIGINAL NAME FOR ',''); + 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 */ ; @@ -976,4 +959,4 @@ DELIMITER ; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2016-05-19 19:39:15 +-- Dump completed on 2016-05-19 23:04:13