--- 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 */;
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 */;
--
`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 */;
--
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 */;
--
`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 */;
--
`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 */;
--
`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 */;
--
`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 */;
--
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 */;
--
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 */;
--
`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 */;
--
`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 */;
--
`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 */;
--
`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 */;
--
`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 */;
--
`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;\r
+ declare lLang varchar(255);\r
+ select min(lang_code) into lLang from lang_alias where alias=pLang;\r
+ if lLang is null then\r
+ SET lLang = pLang;\r
+ 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\r
+ DECLARE done INT DEFAULT FALSE;\r
+ DECLARE alias,lang,dummystr VARCHAR(255);\r
+ DECLARE cur CURSOR FOR
+ SELECT a.alias,a.lang_code FROM metadata.languages l,metadata.lang_alias a where l.lang_code=a.alias;\r
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;\r
+ OPEN cur;\r
+ langs: loop\r
+ FETCH cur into alias,lang;\r
+ IF done THEN\r
+ LEAVE langs;\r
+ END IF;\r
+ SELECT ProcessLang(alias,lang) INTO dummystr;\r
+ END loop;\r
+ CLOSE cur;\r
+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 */;
/*!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
projects / openlib.git / blobdiff