-- MySQL dump 10.13 Distrib 8.0.46, for Linux (x86_64) -- -- Host: localhost Database: websofia -- ------------------------------------------------------ -- Server version 8.0.46 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `autor` -- DROP TABLE IF EXISTS `autor`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `autor` ( `id` int NOT NULL AUTO_INCREMENT, `nome` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `nome_sanitizado` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'temp', `nome_biblio` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `nome_wiki` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `url_imagem` varchar(350) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `conteudo` text COLLATE utf8mb4_unicode_ci, `publicar` tinyint(1) NOT NULL DEFAULT '1', `destaque` tinyint(1) NOT NULL DEFAULT '0', `dthr_criacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `dthr_alteracao` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `ix_autor` (`publicar`,`nome_sanitizado`) ) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `autor_livro` -- DROP TABLE IF EXISTS `autor_livro`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `autor_livro` ( `autor_id` int NOT NULL, `livro_id` int NOT NULL, PRIMARY KEY (`autor_id`,`livro_id`), KEY `fk_autor_livro_livro` (`livro_id`), CONSTRAINT `fk_autor_livro_autor` FOREIGN KEY (`autor_id`) REFERENCES `autor` (`id`), CONSTRAINT `fk_autor_livro_livro` FOREIGN KEY (`livro_id`) REFERENCES `livro` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `autor_video` -- DROP TABLE IF EXISTS `autor_video`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `autor_video` ( `autor_id` int NOT NULL, `video_id` int NOT NULL, PRIMARY KEY (`autor_id`,`video_id`), KEY `fk_autor_video_video` (`video_id`), CONSTRAINT `fk_autor_video_autor` FOREIGN KEY (`autor_id`) REFERENCES `autor` (`id`), CONSTRAINT `fk_autor_video_video` FOREIGN KEY (`video_id`) REFERENCES `video` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `frase` -- DROP TABLE IF EXISTS `frase`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `frase` ( `id` int NOT NULL AUTO_INCREMENT, `token` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `autor_id` int DEFAULT NULL, `livro_id` int DEFAULT NULL, `publicar` tinyint(1) NOT NULL DEFAULT '1', `frase` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL, `dthr_criacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `token` (`token`), KEY `fk_frase_autor` (`autor_id`), KEY `fk_frase_livro` (`livro_id`), CONSTRAINT `fk_frase_autor` FOREIGN KEY (`autor_id`) REFERENCES `autor` (`id`), CONSTRAINT `fk_frase_livro` FOREIGN KEY (`livro_id`) REFERENCES `livro` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=275 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_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_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `trg_frase_token` BEFORE INSERT ON `frase` FOR EACH ROW BEGIN IF NEW.token IS NULL OR NEW.token = '' THEN SET NEW.token = LOWER(HEX(RANDOM_BYTES(8))); 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 */ ; -- -- Table structure for table `idioma` -- DROP TABLE IF EXISTS `idioma`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `idioma` ( `id` int NOT NULL AUTO_INCREMENT, `nome` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `link` -- DROP TABLE IF EXISTS `link`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `link` ( `id` int NOT NULL AUTO_INCREMENT, `token` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `url` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL, `views` int NOT NULL DEFAULT '0', `tamanho` int DEFAULT NULL, `dthr_criacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `livro_id` int NOT NULL, `idioma_id` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `token` (`token`), KEY `fk_link_livro` (`livro_id`), KEY `fk_link_idioma` (`idioma_id`), CONSTRAINT `fk_link_idioma` FOREIGN KEY (`idioma_id`) REFERENCES `idioma` (`id`), CONSTRAINT `fk_link_livro` FOREIGN KEY (`livro_id`) REFERENCES `livro` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1398 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!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 = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_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_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `trg_link_token` BEFORE INSERT ON `link` FOR EACH ROW BEGIN IF NEW.token IS NULL OR NEW.token = '' THEN SET NEW.token = LOWER(HEX(RANDOM_BYTES(8))); 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 */ ; -- -- Table structure for table `livro` -- DROP TABLE IF EXISTS `livro`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `livro` ( `id` int NOT NULL AUTO_INCREMENT, `titulo` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL, `titulo_sanitizado` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'temp', `titulo_wiki` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `subtitulo` varchar(600) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `url_capa` varchar(350) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `publicar` tinyint(1) NOT NULL DEFAULT '1', `destaque` tinyint(1) NOT NULL DEFAULT '0', `dthr_criacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `dthr_alteracao` datetime DEFAULT NULL, `sinopse` longtext COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`), KEY `ix_livro` (`publicar`,`titulo_sanitizado`) ) ENGINE=InnoDB AUTO_INCREMENT=789 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `livro_link` -- DROP TABLE IF EXISTS `livro_link`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `livro_link` ( `livro_id` int NOT NULL, `link_id` int NOT NULL, `idioma_id` int DEFAULT NULL, PRIMARY KEY (`livro_id`,`link_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `livro_tag` -- DROP TABLE IF EXISTS `livro_tag`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `livro_tag` ( `livro_id` int NOT NULL, `tag_id` int NOT NULL, PRIMARY KEY (`livro_id`,`tag_id`), KEY `fk_livro_tag_tag` (`tag_id`), CONSTRAINT `fk_livro_tag_livro` FOREIGN KEY (`livro_id`) REFERENCES `livro` (`id`), CONSTRAINT `fk_livro_tag_tag` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `tag` -- DROP TABLE IF EXISTS `tag`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `tag` ( `id` int NOT NULL AUTO_INCREMENT, `tag` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `tag_sanitizado` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `publicar` tinyint(1) NOT NULL DEFAULT '0', `dthr_criacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_tag_sanitizado` (`tag_sanitizado`) ) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `video` -- DROP TABLE IF EXISTS `video`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `video` ( `id` int NOT NULL AUTO_INCREMENT, `livro_id` int DEFAULT NULL, `youtube_id` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, `titulo` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `publicar` tinyint(1) NOT NULL DEFAULT '0', `destaque` tinyint(1) NOT NULL DEFAULT '0', `dthr_criacao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `fk_video_livro` (`livro_id`), CONSTRAINT `fk_video_livro` FOREIGN KEY (`livro_id`) REFERENCES `livro` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary view structure for view `vw_livro_downloads` -- DROP TABLE IF EXISTS `vw_livro_downloads`; /*!50001 DROP VIEW IF EXISTS `vw_livro_downloads`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `vw_livro_downloads` AS SELECT 1 AS `id`, 1 AS `total_views`*/; SET character_set_client = @saved_cs_client; -- -- Final view structure for view `vw_livro_downloads` -- /*!50001 DROP VIEW IF EXISTS `vw_livro_downloads`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = latin1 */; /*!50001 SET character_set_results = latin1 */; /*!50001 SET collation_connection = latin1_swedish_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `vw_livro_downloads` AS select `l`.`id` AS `id`,sum(`lk`.`views`) AS `total_views` from (`livro` `l` join `link` `lk` on((`lk`.`livro_id` = `l`.`id`))) where (`l`.`publicar` = 1) group by `l`.`id` order by `total_views` desc limit 10 */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2026-07-10 2:15:57