diff --git a/sql/000-baseline-20170612.sql b/sql/000-baseline-20170612.sql new file mode 100644 index 0000000..a2ae1f1 --- /dev/null +++ b/sql/000-baseline-20170612.sql @@ -0,0 +1,447 @@ +-- MySQL dump 10.13 Distrib 5.7.18, for Linux (x86_64) +-- +-- Host: localhost Database: mt_site +-- ------------------------------------------------------ +-- Server version 5.7.18-0ubuntu0.16.04.1 + +/*!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 */; +/*!40101 SET NAMES utf8 */; +/*!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 `actor` +-- + +DROP TABLE IF EXISTS `actor`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `actor` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `transcript_name` varchar(64) NOT NULL, + `visible` tinyint(1) NOT NULL DEFAULT '0', + `avatar` tinytext, + `family_name` tinytext, + `given_name` tinytext, + `description` text, + PRIMARY KEY (`id`), + KEY `transcript_name` (`transcript_name`), + KEY `actor_transcript_name` (`transcript_name`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `admin_log` +-- + +DROP TABLE IF EXISTS `admin_log`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `admin_log` ( + `logdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `contributor` int(10) unsigned DEFAULT NULL, + `section` int(10) unsigned DEFAULT NULL, + `action` enum('create','delete','update') DEFAULT NULL, + `level` int(11) NOT NULL, + `message` text, + KEY `contributor` (`contributor`), + KEY `section` (`section`), + CONSTRAINT `admin_log_ibfk_1` FOREIGN KEY (`contributor`) REFERENCES `contributor` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `admin_log_ibfk_2` FOREIGN KEY (`section`) REFERENCES `admin_section` (`id`) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `admin_section` +-- + +DROP TABLE IF EXISTS `admin_section`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `admin_section` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `name` tinytext, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `banner` +-- + +DROP TABLE IF EXISTS `banner`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `banner` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `sponsor` int(10) unsigned NOT NULL DEFAULT '0', + `media` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`id`), + KEY `sponsor` (`sponsor`), + KEY `media` (`media`), + CONSTRAINT `banner_ibfk_1` FOREIGN KEY (`sponsor`) REFERENCES `sponsor` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `banner_ibfk_2` FOREIGN KEY (`media`) REFERENCES `media_t` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `contributor` +-- + +DROP TABLE IF EXISTS `contributor`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `contributor` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `name` varchar(32) NOT NULL, + `password` char(40) NOT NULL DEFAULT '89d1bc57b4da2ae450e57898cf0f5ef80959458e', + `email` tinytext NOT NULL, + `nameplate` tinytext NOT NULL, + `default_image` tinytext, + `default_link` tinytext, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `fredart` +-- + +DROP TABLE IF EXISTS `fredart`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `fredart` ( + `pubdate` datetime NOT NULL, + `title` tinytext, + `link` varchar(255) DEFAULT NULL, + UNIQUE KEY `link` (`link`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `media_t` +-- + +DROP TABLE IF EXISTS `media_t`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `media_t` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `extension` char(3) NOT NULL, + `description` tinytext, + PRIMARY KEY (`id`), + UNIQUE KEY `extension` (`extension`) +) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `meta` +-- + +DROP TABLE IF EXISTS `meta`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `meta` ( + `type` int(10) unsigned NOT NULL, + `meta` int(10) unsigned NOT NULL, + KEY `type` (`type`), + KEY `meta` (`meta`), + CONSTRAINT `meta_ibfk_1` FOREIGN KEY (`type`) REFERENCES `strip_t` (`id`) ON UPDATE CASCADE, + CONSTRAINT `meta_ibfk_2` FOREIGN KEY (`meta`) REFERENCES `meta_t` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `meta_t` +-- + +DROP TABLE IF EXISTS `meta_t`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `meta_t` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `name` tinytext, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `rant` +-- + +DROP TABLE IF EXISTS `rant`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `rant` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `published` datetime NOT NULL, + `status` enum('published','draft') CHARACTER SET latin1 NOT NULL, + `side` enum('left','right') CHARACTER SET latin1 DEFAULT NULL, + `author` int(10) unsigned NOT NULL, + `title` tinytext CHARACTER SET latin1 NOT NULL, + `body` mediumtext CHARACTER SET latin1 NOT NULL, + `link` tinytext CHARACTER SET latin1, + `imagetype` int(10) unsigned NOT NULL, + `imagetext` tinytext CHARACTER SET latin1, + PRIMARY KEY (`id`), + KEY `author` (`author`), + KEY `imagetype` (`imagetype`), + KEY `published` (`published`), + KEY `halp` (`author`,`imagetype`,`published`), + CONSTRAINT `rant_ibfk_1` FOREIGN KEY (`author`) REFERENCES `contributor` (`id`) ON UPDATE CASCADE, + CONSTRAINT `rant_ibfk_2` FOREIGN KEY (`imagetype`) REFERENCES `media_t` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=1099 DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `rant_attachment` +-- + +DROP TABLE IF EXISTS `rant_attachment`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `rant_attachment` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `rant` int(10) unsigned NOT NULL, + `media` int(10) unsigned NOT NULL, + PRIMARY KEY (`id`), + KEY `rant` (`rant`), + KEY `media` (`media`), + CONSTRAINT `rant_attachment_ibfk_1` FOREIGN KEY (`rant`) REFERENCES `rant` (`id`) ON UPDATE CASCADE, + CONSTRAINT `rant_attachment_ibfk_2` FOREIGN KEY (`media`) REFERENCES `media_t` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `rss_comment` +-- + +DROP TABLE IF EXISTS `rss_comment`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `rss_comment` ( + `published` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `body` tinytext, + `url` tinytext +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `scratchpad` +-- + +DROP TABLE IF EXISTS `scratchpad`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `scratchpad` ( + `published` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `contributor` int(10) unsigned NOT NULL, + `message` tinytext, + KEY `contributor` (`contributor`), + CONSTRAINT `scratchpad_ibfk_1` FOREIGN KEY (`contributor`) REFERENCES `contributor` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `sponsor` +-- + +DROP TABLE IF EXISTS `sponsor`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sponsor` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `name` tinytext NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `static_page` +-- + +DROP TABLE IF EXISTS `static_page`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `static_page` ( + `url_name` varchar(32) NOT NULL, + `pubdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `status` enum('published','draft') NOT NULL, + `title` varchar(64) DEFAULT NULL, + `body` text, + `style` text, + PRIMARY KEY (`url_name`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `status` +-- + +DROP TABLE IF EXISTS `status`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `status` ( + `published` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `eta` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `percentage` tinyint(3) unsigned NOT NULL DEFAULT '0', + `text` tinytext CHARACTER SET latin1 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `strip` +-- + +DROP TABLE IF EXISTS `strip`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `strip` ( + `id` int(10) unsigned NOT NULL, + `published` datetime NOT NULL, + `media` int(10) unsigned NOT NULL, + `type` int(10) unsigned NOT NULL, + `title` tinytext CHARACTER SET latin1, + `book` tinyint(3) unsigned DEFAULT NULL, + `page` tinyint(3) unsigned DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `media` (`media`), + KEY `type` (`type`), + KEY `published` (`published`), + CONSTRAINT `strip_ibfk_1` FOREIGN KEY (`media`) REFERENCES `media_t` (`id`) ON UPDATE CASCADE, + CONSTRAINT `strip_ibfk_2` FOREIGN KEY (`type`) REFERENCES `strip_t` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `strip_t` +-- + +DROP TABLE IF EXISTS `strip_t`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `strip_t` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `name` varchar(8) NOT NULL, + `description` tinytext, + `meta` int(10) unsigned DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`), + KEY `meta` (`meta`), + CONSTRAINT `strip_t_ibfk_1` FOREIGN KEY (`meta`) REFERENCES `meta_t` (`id`) ON UPDATE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `support` +-- + +DROP TABLE IF EXISTS `support`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `support` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `sponsor` int(10) unsigned NOT NULL DEFAULT '0', + `published` date NOT NULL DEFAULT '0000-00-00', + `sunset` date NOT NULL DEFAULT '0000-00-00', + `link` tinytext, + `banner` int(10) unsigned DEFAULT NULL, + `body` tinytext, + PRIMARY KEY (`id`), + KEY `sponsor` (`sponsor`), + KEY `banner` (`banner`), + CONSTRAINT `support_ibfk_1` FOREIGN KEY (`sponsor`) REFERENCES `sponsor` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `support_ibfk_2` FOREIGN KEY (`banner`) REFERENCES `banner` (`id`) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `transcript` +-- + +DROP TABLE IF EXISTS `transcript`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `transcript` ( + `strip` int(10) unsigned NOT NULL, + `panel` int(10) unsigned NOT NULL, + `line` int(10) unsigned NOT NULL, + `speaker` varchar(64) NOT NULL, + `speech` text, + `search` text, + PRIMARY KEY (`strip`,`panel`,`line`), + KEY `speaker` (`speaker`), + KEY `transcript_speaker` (`speaker`), + CONSTRAINT `transcript_ibfk_1` FOREIGN KEY (`strip`) REFERENCES `strip` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `twitter_post` +-- + +DROP TABLE IF EXISTS `twitter_post`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `twitter_post` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `status` enum('scheduled','locked','error','success') NOT NULL, + `user` int(10) unsigned NOT NULL, + `time` datetime NOT NULL, + `text` varchar(140) NOT NULL, + PRIMARY KEY (`id`), + KEY `user` (`user`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `twitter_status` +-- + +DROP TABLE IF EXISTS `twitter_status`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `twitter_status` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `position` int(10) unsigned NOT NULL, + `message` varchar(140) DEFAULT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `twitter_user` +-- + +DROP TABLE IF EXISTS `twitter_user`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `twitter_user` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `username` tinytext NOT NULL, + `password` tinytext NOT NULL, + `oauth_token` tinytext, + `oauth_token_secret` tinytext, + `oauth_access_token` tinytext, + PRIMARY KEY (`id`), + UNIQUE KEY `username` (`username`(100)) +) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!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 2017-06-12 15:52:27