Обработка "неприводимых к латинице и кириллице" имен авторов с возможностью ручного...

diff --git a/metadata.sql b/metadata.sql
index 113734d9a7ead58406e388cf7a3604b003038398..ac0d5e3be1e1dfc68fd871593ee095b5f19b1656 100644 (file)
--- 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
   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 */;
 
 --
 /*!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,
   `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,
   `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`)
   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 */;
 
 --
 /*!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,
   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 */;
 
 --
 /*!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`),
   `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`),
   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
   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 */;
 
 --
 /*!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`),
   `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
   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 */;
 
 --
 /*!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`),
   `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
   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 */;
 
 --
 /*!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`),
   `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`),
   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
   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 */;
 
 --
 /*!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,
 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`),
   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 */;
 
 --
 /*!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`),
   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 */;
 
 --
 /*!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`)
   `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 */;
 
 --
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --


@@ -295,7 +266,7 @@ CREATE TABLE `publishers` (
   `name` longtext NOT NULL,
   `sort` longtext,
   PRIMARY KEY (`id`)
   `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 */;
 
 --
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --


@@ -310,7 +281,7 @@ CREATE TABLE `series` (
   `name` longtext NOT NULL,
   `sort` longtext,
   PRIMARY KEY (`id`)
   `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 */;
 
 --
 /*!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 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
 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 */ ;
 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 */;
 
 /*!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