-- This mode allows the use of 0 as a valid ID value in an AUTO_INCREMENT column SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; CREATE TABLE IF NOT EXISTS `language` ( `id` int unsigned NOT NULL, `shortcode` varchar(10) NOT NULL, `display_name` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO language VALUES(0,'None','None'); INSERT INTO language VALUES(1,'af','Afrikaans'); INSERT INTO language VALUES(2,'af-ZA','Afrikaans - South Africa'); INSERT INTO language VALUES(3,'sq','Albanian'); INSERT INTO language VALUES(4,'sq-AL','Albanian - Albania'); INSERT INTO language VALUES(5,'ar','Arabic'); INSERT INTO language VALUES(6,'ar-DZ','Arabic - Algeria'); INSERT INTO language VALUES(7,'ar-BH','Arabic - Bahrain'); INSERT INTO language VALUES(8,'ar-EG','Arabic - Egypt'); INSERT INTO language VALUES(9,'ar-IQ','Arabic - Iraq'); INSERT INTO language VALUES(10,'ar-JO','Arabic - Jordan'); INSERT INTO language VALUES(11,'ar-KW','Arabic - Kuwait'); INSERT INTO language VALUES(12,'ar-LB','Arabic - Lebanon'); INSERT INTO language VALUES(13,'ar-LY','Arabic - Libya'); INSERT INTO language VALUES(14,'ar-MA','Arabic - Morocco'); INSERT INTO language VALUES(15,'ar-OM','Arabic - Oman'); INSERT INTO language VALUES(16,'ar-QA','Arabic - Qatar'); INSERT INTO language VALUES(17,'ar-SA','Arabic - Saudi Arabia'); INSERT INTO language VALUES(18,'ar-SY','Arabic - Syria'); INSERT INTO language VALUES(19,'ar-TN','Arabic - Tunisia'); INSERT INTO language VALUES(20,'ar-AE','Arabic - United Arab Emirates'); INSERT INTO language VALUES(21,'ar-YE','Arabic - Yemen'); INSERT INTO language VALUES(22,'hy','Armenian'); INSERT INTO language VALUES(23,'hy-AM','Armenian - Armenia'); INSERT INTO language VALUES(24,'az','Azeri'); INSERT INTO language VALUES(25,'az-AZ-Cyrl','Azeri (Cyrillic) - Azerbaijan'); INSERT INTO language VALUES(26,'az-AZ-Latn','Azeri (Latin) - Azerbaijan'); INSERT INTO language VALUES(27,'eu','Basque'); INSERT INTO language VALUES(28,'eu-ES','Basque - Basque'); INSERT INTO language VALUES(29,'be','Belarusian'); INSERT INTO language VALUES(30,'be-BY','Belarusian - Belarus'); INSERT INTO language VALUES(31,'bg','Bulgarian'); INSERT INTO language VALUES(32,'bg-BG','Bulgarian - Bulgaria'); INSERT INTO language VALUES(33,'ca','Catalan'); INSERT INTO language VALUES(34,'ca-ES','Catalan - Catalan'); INSERT INTO language VALUES(35,'zh-CHS','Chinese (Simplified)'); INSERT INTO language VALUES(36,'zh-CHT','Chinese (Traditional)'); INSERT INTO language VALUES(37,'zh-CN','Chinese - China'); INSERT INTO language VALUES(38,'zh-HK','Chinese - Hong Kong SAR'); INSERT INTO language VALUES(39,'zh-MO','Chinese - Macao SAR'); INSERT INTO language VALUES(40,'zh-SG','Chinese - Singapore'); INSERT INTO language VALUES(41,'zh-TW','Chinese - Taiwan'); INSERT INTO language VALUES(42,'hr','Croatian'); INSERT INTO language VALUES(43,'hr-HR','Croatian - Croatia'); INSERT INTO language VALUES(44,'cs','Czech'); INSERT INTO language VALUES(45,'cs-CZ','Czech - Czech Republic'); INSERT INTO language VALUES(46,'da','Danish'); INSERT INTO language VALUES(47,'da-DK','Danish - Denmark'); INSERT INTO language VALUES(48,'div','Dhivehi'); INSERT INTO language VALUES(49,'div-MV','Dhivehi - Maldives'); INSERT INTO language VALUES(50,'nl','Dutch'); INSERT INTO language VALUES(51,'nl-BE','Dutch - Belgium'); INSERT INTO language VALUES(52,'nl-NL','Dutch - The Netherlands'); INSERT INTO language VALUES(53,'en','English'); INSERT INTO language VALUES(54,'en-AU','English - Australia'); INSERT INTO language VALUES(55,'en-BZ','English - Belize'); INSERT INTO language VALUES(56,'en-CA','English - Canada'); INSERT INTO language VALUES(57,'en-CB','English - Caribbean'); INSERT INTO language VALUES(58,'en-IE','English - Ireland'); INSERT INTO language VALUES(59,'en-JM','English - Jamaica'); INSERT INTO language VALUES(60,'en-NZ','English - New Zealand'); INSERT INTO language VALUES(61,'en-PH','English - Philippines'); INSERT INTO language VALUES(62,'en-ZA','English - South Africa'); INSERT INTO language VALUES(63,'en-TT','English - Trinidad and Tobago'); INSERT INTO language VALUES(64,'en-GB','English - United Kingdom'); INSERT INTO language VALUES(65,'en-US','English - United States'); INSERT INTO language VALUES(66,'en-ZW','English - Zimbabwe'); INSERT INTO language VALUES(67,'eo','Esperanto'); INSERT INTO language VALUES(68,'et','Estonian'); INSERT INTO language VALUES(69,'et-EE','Estonian - Estonia'); INSERT INTO language VALUES(70,'fo','Faroese'); INSERT INTO language VALUES(71,'fo-FO','Faroese - Faroe Islands'); INSERT INTO language VALUES(72,'fa','Farsi'); INSERT INTO language VALUES(73,'fa-IR','Farsi - Iran'); INSERT INTO language VALUES(74,'fi','Finnish'); INSERT INTO language VALUES(75,'fi-FI','Finnish - Finland'); INSERT INTO language VALUES(76,'fr','French'); INSERT INTO language VALUES(77,'fr-BE','French - Belgium'); INSERT INTO language VALUES(78,'fr-CA','French - Canada'); INSERT INTO language VALUES(79,'fr-FR','French - France'); INSERT INTO language VALUES(80,'fr-LU','French - Luxembourg'); INSERT INTO language VALUES(81,'fr-MC','French - Monaco'); INSERT INTO language VALUES(82,'fr-CH','French - Switzerland'); INSERT INTO language VALUES(83,'gl','Galician'); INSERT INTO language VALUES(84,'gl-ES','Galician - Galician'); INSERT INTO language VALUES(85,'ka','Georgian'); INSERT INTO language VALUES(86,'ka-GE','Georgian - Georgia'); INSERT INTO language VALUES(87,'de','German'); INSERT INTO language VALUES(88,'de-AT','German - Austria'); INSERT INTO language VALUES(89,'de-DE','German - Germany'); INSERT INTO language VALUES(90,'de-LI','German - Liechtenstein'); INSERT INTO language VALUES(91,'de-LU','German - Luxembourg'); INSERT INTO language VALUES(92,'de-CH','German - Switzerland'); INSERT INTO language VALUES(93,'el','Greek'); INSERT INTO language VALUES(94,'el-GR','Greek - Greece'); INSERT INTO language VALUES(95,'gu','Gujarati'); INSERT INTO language VALUES(96,'gu-IN','Gujarati - India'); INSERT INTO language VALUES(97,'he','Hebrew'); INSERT INTO language VALUES(98,'he-IL','Hebrew - Israel'); INSERT INTO language VALUES(99,'hi','Hindi'); INSERT INTO language VALUES(100,'hi-IN','Hindi - India'); INSERT INTO language VALUES(101,'hu','Hungarian'); INSERT INTO language VALUES(102,'hu-HU','Hungarian - Hungary'); INSERT INTO language VALUES(103,'is','Icelandic'); INSERT INTO language VALUES(104,'is-IS','Icelandic - Iceland'); INSERT INTO language VALUES(105,'id','Indonesian'); INSERT INTO language VALUES(106,'id-ID','Indonesian - Indonesia'); INSERT INTO language VALUES(107,'it','Italian'); INSERT INTO language VALUES(108,'it-IT','Italian - Italy'); INSERT INTO language VALUES(109,'it-CH','Italian - Switzerland'); INSERT INTO language VALUES(110,'ja','Japanese'); INSERT INTO language VALUES(111,'ja-JP','Japanese - Japan'); INSERT INTO language VALUES(112,'kn','Kannada'); INSERT INTO language VALUES(113,'kn-IN','Kannada - India'); INSERT INTO language VALUES(114,'kk','Kazakh'); INSERT INTO language VALUES(115,'kk-KZ','Kazakh - Kazakhstan'); INSERT INTO language VALUES(116,'kok','Konkani'); INSERT INTO language VALUES(117,'kok-IN','Konkani - India'); INSERT INTO language VALUES(118,'ko','Korean'); INSERT INTO language VALUES(119,'ko-KR','Korean - Korea'); INSERT INTO language VALUES(120,'ky','Kyrgyz'); INSERT INTO language VALUES(121,'ky-KG','Kyrgyz - Kyrgyzstan'); INSERT INTO language VALUES(122,'lv','Latvian'); INSERT INTO language VALUES(123,'lv-LV','Latvian - Latvia'); INSERT INTO language VALUES(124,'lt','Lithuanian'); INSERT INTO language VALUES(125,'lt-LT','Lithuanian - Lithuania'); INSERT INTO language VALUES(126,'lb','Luxembourgish'); INSERT INTO language VALUES(127,'mk','Macedonian'); INSERT INTO language VALUES(128,'mk-MK','Macedonian - Former Yugoslav Republic of Macedonia'); INSERT INTO language VALUES(129,'ms','Malay'); INSERT INTO language VALUES(130,'ms-BN','Malay - Brunei'); INSERT INTO language VALUES(131,'ms-MY','Malay - Malaysia'); INSERT INTO language VALUES(132,'mr','Marathi'); INSERT INTO language VALUES(133,'mr-IN','Marathi - India'); INSERT INTO language VALUES(134,'mn','Mongolian'); INSERT INTO language VALUES(135,'mn-MN','Mongolian - Mongolia'); INSERT INTO language VALUES(136,'no','Norwegian'); INSERT INTO language VALUES(137,'nb-NO','Norwegian (Bokmål) - Norway'); INSERT INTO language VALUES(138,'nn-NO','Norwegian (Nynorsk) - Norway'); INSERT INTO language VALUES(139,'pl','Polish'); INSERT INTO language VALUES(140,'pl-PL','Polish - Poland'); INSERT INTO language VALUES(141,'pt','Portuguese'); INSERT INTO language VALUES(142,'pt-BR','Portuguese - Brazil'); INSERT INTO language VALUES(143,'pt-PT','Portuguese - Portugal'); INSERT INTO language VALUES(144,'pa','Punjabi'); INSERT INTO language VALUES(145,'pa-IN','Punjabi - India'); INSERT INTO language VALUES(146,'ro','Romanian'); INSERT INTO language VALUES(147,'ro-RO','Romanian - Romania'); INSERT INTO language VALUES(148,'ru','Russian'); INSERT INTO language VALUES(149,'ru-RU','Russian - Russia'); INSERT INTO language VALUES(150,'sa','Sanskrit'); INSERT INTO language VALUES(151,'sa-IN','Sanskrit - India'); INSERT INTO language VALUES(152,'sr-SP-Cyrl','Serbian (Cyrillic) - Serbia'); INSERT INTO language VALUES(153,'sr-SP-Latn','Serbian (Latin) - Serbia'); INSERT INTO language VALUES(154,'sk','Slovak'); INSERT INTO language VALUES(155,'sk-SK','Slovak - Slovakia'); INSERT INTO language VALUES(156,'sl','Slovenian'); INSERT INTO language VALUES(157,'sl-SI','Slovenian - Slovenia'); INSERT INTO language VALUES(158,'es','Spanish'); INSERT INTO language VALUES(159,'es-AR','Spanish - Argentina'); INSERT INTO language VALUES(160,'es-BO','Spanish - Bolivia'); INSERT INTO language VALUES(161,'es-CL','Spanish - Chile'); INSERT INTO language VALUES(162,'es-CO','Spanish - Colombia'); INSERT INTO language VALUES(163,'es-CR','Spanish - Costa Rica'); INSERT INTO language VALUES(164,'es-DO','Spanish - Dominican Republic'); INSERT INTO language VALUES(165,'es-EC','Spanish - Ecuador'); INSERT INTO language VALUES(166,'es-SV','Spanish - El Salvador'); INSERT INTO language VALUES(167,'es-GT','Spanish - Guatemala'); INSERT INTO language VALUES(168,'es-HN','Spanish - Honduras'); INSERT INTO language VALUES(169,'es-MX','Spanish - Mexico'); INSERT INTO language VALUES(170,'es-NI','Spanish - Nicaragua'); INSERT INTO language VALUES(171,'es-PA','Spanish - Panama'); INSERT INTO language VALUES(172,'es-PY','Spanish - Paraguay'); INSERT INTO language VALUES(173,'es-PE','Spanish - Peru'); INSERT INTO language VALUES(174,'es-PR','Spanish - Puerto Rico'); INSERT INTO language VALUES(175,'es-ES','Spanish - Spain'); INSERT INTO language VALUES(176,'es-UY','Spanish - Uruguay'); INSERT INTO language VALUES(177,'es-VE','Spanish - Venezuela'); INSERT INTO language VALUES(178,'sw','Swahili'); INSERT INTO language VALUES(179,'sw-KE','Swahili - Kenya'); INSERT INTO language VALUES(180,'sv','Swedish'); INSERT INTO language VALUES(181,'sv-FI','Swedish - Finland'); INSERT INTO language VALUES(182,'sv-SE','Swedish - Sweden'); INSERT INTO language VALUES(183,'syr','Syriac'); INSERT INTO language VALUES(184,'syr-SY','Syriac - Syria'); INSERT INTO language VALUES(185,'ta','Tamil'); INSERT INTO language VALUES(186,'ta-IN','Tamil - India'); INSERT INTO language VALUES(187,'tt','Tatar'); INSERT INTO language VALUES(188,'tt-RU','Tatar - Russia'); INSERT INTO language VALUES(189,'te','Telugu'); INSERT INTO language VALUES(190,'te-IN','Telugu - India'); INSERT INTO language VALUES(191,'th','Thai'); INSERT INTO language VALUES(192,'th-TH','Thai - Thailand'); INSERT INTO language VALUES(193,'tr','Turkish'); INSERT INTO language VALUES(194,'tr-TR','Turkish - Turkey'); INSERT INTO language VALUES(195,'uk','Ukrainian'); INSERT INTO language VALUES(196,'uk-UA','Ukrainian - Ukraine'); INSERT INTO language VALUES(197,'ur','Urdu'); INSERT INTO language VALUES(198,'ur-PK','Urdu - Pakistan'); INSERT INTO language VALUES(199,'uz','Uzbek'); INSERT INTO language VALUES(200,'uz-UZ-Cyrl','Uzbek (Cyrillic) - Uzbekistan'); INSERT INTO language VALUES(201,'uz-UZ-Latn','Uzbek (Latin) - Uzbekistan'); INSERT INTO language VALUES(202,'vi','Vietnamese'); CREATE TABLE IF NOT EXISTS `itunes_category` ( `id` int unsigned NOT NULL, `language` varchar(10) NOT NULL, `category` varchar(64) NOT NULL, `subcategory` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `itunes_category` VALUES (0,'en', 'None','None'),(1,'en','Arts','Arts'),(2,'en','Arts','Books'),(3,'en','Arts','Design'),(4,'en','Arts','Fashion & Beauty'),(5,'en','Arts','Food'),(6,'en','Arts','Performing Arts'),(7,'en','Arts','Visual Arts'),(8,'en','Business','Business'),(9,'en','Business','Careers'),(10,'en','Business','Entrepreneurship'),(11,'en','Business','Investing'),(12,'en','Business','Management'),(13,'en','Business','Marketing'),(14,'en','Business','Non-Profit'),(15,'en','Comedy','Comedy'),(16,'en','Comedy','Comedy Interviews'),(17,'en','Comedy','Improv'),(18,'en','Comedy','Stand-Up'),(19,'en','Education','Education'),(20,'en','Education','Courses'),(21,'en','Education','How To'),(22,'en','Education','Language Learning'),(23,'en','Education','Self-Improvement'),(24,'en','Fiction','Fiction'),(25,'en','Fiction','Comedy Fiction'),(26,'en','Fiction','Drama'),(27,'en','Fiction','Science Fiction'),(28,'en','Government','Government'),(29,'en','Health & Fitness','Health & Fitness'),(30,'en','Health & Fitness','Alternative Health'),(31,'en','Health & Fitness','Fitness'),(32,'en','Health & Fitness','Medicine'),(33,'en','Health & Fitness','Mental Health'),(34,'en','Health & Fitness','Nutrition'),(35,'en','Health & Fitness','Sexuality'),(36,'en','History','History'),(37,'en','Kids & Family','Kids & Family'),(38,'en','Kids & Family','Education for Kids'),(39,'en','Kids & Family','Parenting'),(40,'en','Kids & Family','Pets & Animals'),(41,'en','Kids & Family','Stories for Kids'),(42,'en','Leisure','Leisure'),(43,'en','Leisure','Animation & Manga'),(44,'en','Leisure','Automotive'),(45,'en','Leisure','Aviation'),(46,'en','Leisure','Crafts'),(47,'en','Leisure','Games'),(48,'en','Leisure','Hobbies'),(49,'en','Leisure','Home & Garden'),(50,'en','Leisure','Video Games'),(51,'en','Music','Music'),(52,'en','Music','Music Commentary'),(53,'en','Music','Music History'),(54,'en','Music','Music Interviews'),(55,'en','News','News'),(56,'en','News','Business News'),(57,'en','News','Daily News'),(58,'en','News','Entertainment News'),(59,'en','News','News Commentary'),(60,'en','News','Politics'),(61,'en','News','Sports News'),(62,'en','News','Tech News'),(63,'en','Religion & Spirituality','Religion & Sprituality'),(64,'en','Religion & Spirituality','Buddhism'),(65,'en','Religion & Spiriuality','Christianity'),(66,'en','Religion & Spiriuality','Hinduism'),(67,'en','Religion & Spiriuality','Islam'),(68,'en','Religion & Spiriuality','Judaism'),(69,'en','Religion & Spiriuality','Religion'),(70,'en','Religion & Spiriuality','Spirituality'),(71,'en','Science','Science'),(72,'en','Science','Astronomy'),(73,'en','Science','Chemistry'),(74,'en','Science','Earth Sciences'),(75,'en','Science','Life Sciences'),(76,'en','Science','Mathematics'),(77,'en','Science','Natural Sciences'),(78,'en','Science','Nature'),(79,'en','Science','Physics'),(80,'en','Science','Social Sciences'),(81,'en','Society & Culture','Society & Culture'),(82,'en','Society & Culture','Documentary'),(83,'en','Society & Culture','Personal Journals'),(84,'en','Society & Culture','Philosophy'),(85,'en','Society & Culture','Places & Travel'),(86,'en','Society & Culture','Relationships'),(87,'en','Sports','Sports'),(88,'en','Sports','Baseball'),(89,'en','Sports','Basketball'),(90,'en','Sports','Cricket'),(91,'en','Sports','Fantasy Sports'),(92,'en','Sports','Football'),(93,'en','Sports','Golf'),(94,'en','Sports','Hockey'),(95,'en','Sports','Rugby'),(96,'en','Sports','Running'),(97,'en','Sports','Soccer'),(98,'en','Sports','Swimming'),(99,'en','Sports','Tennis'),(100,'en','Sports','Volleyball'),(101,'en','Sports','Wilderness'),(102,'en','Sports','Wrestling'),(103,'en','Technology','Technology'),(104,'en','True Crime','True Crime'),(105,'en','TV & Film','TV & Film'),(106,'en','TV & Film','After Shows'),(107,'en','TV & Film','Film History'),(108,'en','TV & Film','Film Interviews'),(109,'en','TV & Film','Film Reviews'),(110,'en','TV & Film','TV Reviews'); CREATE TABLE IF NOT EXISTS `useragents` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `http_header` text NOT NULL, `name` varchar(255) DEFAULT NULL, `device` varchar(64) DEFAULT NULL, `bot` BOOLEAN DEFAULT FALSE, PRIMARY KEY (`id`) ) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- INSERT INTO useragent VALUES(1, 'unknown', NULL, NULL, NULL); CREATE TABLE IF NOT EXISTS `downloads` ( `id` int unsigned NOT NULL AUTO_INCREMENT, -- podcast this HTTP request belongs to or 0 otherwise `podcast_id` int unsigned NOT NULL, `target` smallint unsigned NOT NULL DEFAULT 1, `reference_id` int unsigned NOT NULL, `user_agent_id` int unsigned NOT NULL, `request_id` varchar(32) NOT NULL, `access_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `access_date` date NOT NULL DEFAULT (CURRENT_DATE), PRIMARY KEY (`id`), KEY `user_agent_id` (`user_agent_id`), CONSTRAINT `downloads_ibfk_1` FOREIGN KEY (`user_agent_id`) REFERENCES `useragents` (`id`) ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS `jobs` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `guid` binary(16) NOT NULL, `podcast_id` int unsigned NOT NULL, `jobtype` smallint unsigned NOT NULL DEFAULT 1, `schedule` datetime NOT NULL, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS `users` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(256) NOT NULL, `pwdhash` varchar(256) NOT NULL, `role` smallint unsigned NOT NULL DEFAULT 1, PRIMARY KEY (`id`) ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO users VALUES(0, 'admin', 'admin', 0); -- -- Table structure for table `podcasts` -- CREATE TABLE IF NOT EXISTS `podcasts` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `user_id` int unsigned NOT NULL, `link` varchar(256) NOT NULL, `title` varchar(256) NOT NULL, `subtitle` text DEFAULT NULL, `image_file_name` varchar(128) DEFAULT NULL, `copyright` varchar(256) DEFAULT NULL, `author` varchar(256) DEFAULT NULL, `owner_name` varchar(128) DEFAULT NULL, `owner_email` varchar(128) DEFAULT NULL, `description` text NOT NULL, `meta` text NOT NULL, `last_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `http_hostname` varchar(128) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), CONSTRAINT `podcasts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Table structure for table `episodes` -- CREATE TABLE IF NOT EXISTS `episodes` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `user_id` int unsigned NOT NULL, `podcast_id` int unsigned NOT NULL, `guid` varchar(256) NOT NULL, `title` varchar(256) NOT NULL, `subtitle` varchar(256) DEFAULT NULL, `description` text, `content_encoded` text, `meta` text NOT NULL, `duration` varchar(12) DEFAULT NULL, `chapters` text, `state` tinyint unsigned NOT NULL DEFAULT '1', -- stored as UTC `publication_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, -- stored as UTC `last_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `podcast_id` (`podcast_id`), CONSTRAINT `episodes_ibfk_1` FOREIGN KEY (`podcast_id`) REFERENCES `podcasts` (`id`), CONSTRAINT `episodes_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), CONSTRAINT UC_EPISODE UNIQUE (podcast_id, guid) ) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS `feeds` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `path_name` varchar(36) NOT NULL DEFAULT 'mp3', `title` varchar(32) NOT NULL DEFAULT 'MP3', `user_id` int unsigned NOT NULL, `podcast_id` int unsigned NOT NULL, `mime_type` varchar(128) NOT NULL, `footer` text, `config` text, `last_published` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_pending` tinyint(1) NOT NULL DEFAULT '1', `last_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `podcast_id` (`podcast_id`), CONSTRAINT `feeds_ibfk_1` FOREIGN KEY (`podcast_id`) REFERENCES `podcasts` (`id`), CONSTRAINT `feeds_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS `transcripts` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `user_id` int unsigned NOT NULL, `episode_id` int unsigned NOT NULL, `podcast_id` int unsigned NOT NULL, `file_name` varchar(256) NOT NULL, `language` varchar(10) NOT NULL, `mime_type` varchar(128) NOT NULL, `captions` tinyint(1) NOT NULL DEFAULT '0', -- default location is local `location` tinyint unsigned NOT NULL DEFAULT '2', `last_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `episode_id` (`episode_id`), KEY `podcast_id` (`podcast_id`), KEY `user_id` (`user_id`), CONSTRAINT `transcripts_ibfk_1` FOREIGN KEY (`episode_id`) REFERENCES `episodes` (`id`), CONSTRAINT `transcripts_ibfk_2` FOREIGN KEY (`podcast_id`) REFERENCES `podcasts` (`id`), CONSTRAINT `transcripts_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), CONSTRAINT UC_TRANSCRIPT UNIQUE (episode_id, mime_type) ) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS `enclosures` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `user_id` int unsigned NOT NULL, `episode_id` int unsigned NOT NULL, `podcast_id` int unsigned NOT NULL, `file_name` varchar(256) NOT NULL, `size` varchar(12) NOT NULL, `title` varchar(512) NOT NULL, `mime_type` varchar(128) NOT NULL, -- default location is local `location` tinyint unsigned NOT NULL DEFAULT '2', `progress` tinyint unsigned DEFAULT NULL, `last_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `episode_id` (`episode_id`), KEY `podcast_id` (`podcast_id`), KEY `user_id` (`user_id`), CONSTRAINT `enclosures_ibfk_1` FOREIGN KEY (`episode_id`) REFERENCES `episodes` (`id`), CONSTRAINT `enclosures_ibfk_2` FOREIGN KEY (`podcast_id`) REFERENCES `podcasts` (`id`), CONSTRAINT `enclosures_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;