Initial commit

1 files changed, 113 insertions, 0 deletions

diff --git a/schema/schema.sql b/schema/schema.sql
new file mode 100644
index 0000000..b643cc7
--- /dev/null
+++ b/schema/schema.sql
@@ -0,0 +1,113 @@
+SET NAMES utf8;
+SET time_zone = '+00:00';
+SET foreign_key_checks = 0;
+SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+
+SET NAMES utf8mb4;
+
+CREATE TABLE `genre` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `imdb_id` int(11) NOT NULL,
+  `name` varchar(255) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `imdb_id` (`imdb_id`),
+  CONSTRAINT `genre_ibfk_1` FOREIGN KEY (`imdb_id`) REFERENCES `imdb` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+
+
+CREATE TABLE `imdb` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `links_id` int(11) NOT NULL,
+  `imdb_id` varchar(255) NOT NULL,
+  `wiki_article` varchar(255) DEFAULT NULL,
+  `synopsis` text DEFAULT NULL,
+  `year` int(11) DEFAULT NULL,
+  `poster_url` text DEFAULT NULL,
+  `title_type` varchar(255) DEFAULT NULL,
+  `primary_title` varchar(255) DEFAULT NULL,
+  `original_title` varchar(255) DEFAULT NULL,
+  `start_year` int(11) DEFAULT NULL,
+  `runtime_minutes` int(11) DEFAULT NULL,
+  `average_rating` decimal(3,1) unsigned DEFAULT NULL,
+  `num_votes` int(11) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `links_id` (`links_id`),
+  CONSTRAINT `imdb_ibfk_1` FOREIGN KEY (`links_id`) REFERENCES `links` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+
+
+CREATE TABLE `links` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `story_id` int(11) NOT NULL,
+  `url` varchar(1000) DEFAULT NULL,
+  `field` int(11) NOT NULL,
+  `host` varchar(255) DEFAULT NULL,
+  `param` varchar(255) DEFAULT NULL,
+  `type` varchar(255) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `story_id` (`story_id`),
+  CONSTRAINT `links_ibfk_1` FOREIGN KEY (`story_id`) REFERENCES `story` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=407448 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+
+
+CREATE TABLE `max_item` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `max_story_id` int(11) NOT NULL,
+  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `max_story_id` (`max_story_id`),
+  CONSTRAINT `max_item_ibfk_1` FOREIGN KEY (`max_story_id`) REFERENCES `story` (`story_id`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+
+
+CREATE TABLE `people` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `name` varchar(255) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+
+
+CREATE TABLE `profession` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `name` varchar(255) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+
+INSERT INTO `profession` (`id`, `name`) VALUES
+(1,	'actor'),
+(2,	'director'),
+(3,	'screenwriter');
+
+CREATE TABLE `story` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `story_id` int(11) NOT NULL,
+  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `type` varchar(255) NOT NULL,
+  `title` varchar(255) NOT NULL,
+  `text` text DEFAULT NULL,
+  `score` int(11) NOT NULL,
+  `descendants` int(11) NOT NULL,
+  `time` int(11) NOT NULL,
+  `poster` varchar(255) NOT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `story_id` (`story_id`)
+) ENGINE=InnoDB AUTO_INCREMENT=366974 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+
+
+CREATE TABLE `who` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `imdb_id` int(11) NOT NULL,
+  `people_id` int(11) NOT NULL,
+  `profession_id` int(11) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `imdb_id` (`imdb_id`),
+  KEY `people_id` (`people_id`),
+  KEY `profession_id` (`profession_id`),
+  CONSTRAINT `who_ibfk_1` FOREIGN KEY (`imdb_id`) REFERENCES `imdb` (`id`),
+  CONSTRAINT `who_ibfk_2` FOREIGN KEY (`people_id`) REFERENCES `people` (`id`),
+  CONSTRAINT `who_ibfk_3` FOREIGN KEY (`profession_id`) REFERENCES `profession` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;