mt-site
RSS
Add SQL script to generate database structure.
darkmorford -
262881eb946e
Not Reviewed
Show More
Add another comment
Collapse all files
TODOs: 0 unresolved 0 Resolved
COMMENTS: 0 General 0 Inline
Show file before | Show file after | Show commentsHide comments
@@ -0,0 +1,447
1 -- MySQL dump 10.13 Distrib 5.7.18, for Linux (x86_64)
2 --
3 -- Host: localhost Database: mt_site
4 -- ------------------------------------------------------
5 -- Server version 5.7.18-0ubuntu0.16.04.1
6
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40101 SET NAMES utf8 */;
11 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18 --
19 -- Table structure for table `actor`
20 --
21
22 DROP TABLE IF EXISTS `actor`;
23 /*!40101 SET @saved_cs_client = @@character_set_client */;
24 /*!40101 SET character_set_client = utf8 */;
25 CREATE TABLE `actor` (
26 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
27 `transcript_name` varchar(64) NOT NULL,
28 `visible` tinyint(1) NOT NULL DEFAULT '0',
29 `avatar` tinytext,
30 `family_name` tinytext,
31 `given_name` tinytext,
32 `description` text,
33 PRIMARY KEY (`id`),
34 KEY `transcript_name` (`transcript_name`),
35 KEY `actor_transcript_name` (`transcript_name`)
36 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
37 /*!40101 SET character_set_client = @saved_cs_client */;
38
39 --
40 -- Table structure for table `admin_log`
41 --
42
43 DROP TABLE IF EXISTS `admin_log`;
44 /*!40101 SET @saved_cs_client = @@character_set_client */;
45 /*!40101 SET character_set_client = utf8 */;
46 CREATE TABLE `admin_log` (
47 `logdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
48 `contributor` int(10) unsigned DEFAULT NULL,
49 `section` int(10) unsigned DEFAULT NULL,
50 `action` enum('create','delete','update') DEFAULT NULL,
51 `level` int(11) NOT NULL,
52 `message` text,
53 KEY `contributor` (`contributor`),
54 KEY `section` (`section`),
55 CONSTRAINT `admin_log_ibfk_1` FOREIGN KEY (`contributor`) REFERENCES `contributor` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
56 CONSTRAINT `admin_log_ibfk_2` FOREIGN KEY (`section`) REFERENCES `admin_section` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
57 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
58 /*!40101 SET character_set_client = @saved_cs_client */;
59
60 --
61 -- Table structure for table `admin_section`
62 --
63
64 DROP TABLE IF EXISTS `admin_section`;
65 /*!40101 SET @saved_cs_client = @@character_set_client */;
66 /*!40101 SET character_set_client = utf8 */;
67 CREATE TABLE `admin_section` (
68 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
69 `name` tinytext,
70 PRIMARY KEY (`id`)
71 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
72 /*!40101 SET character_set_client = @saved_cs_client */;
73
74 --
75 -- Table structure for table `banner`
76 --
77
78 DROP TABLE IF EXISTS `banner`;
79 /*!40101 SET @saved_cs_client = @@character_set_client */;
80 /*!40101 SET character_set_client = utf8 */;
81 CREATE TABLE `banner` (
82 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
83 `sponsor` int(10) unsigned NOT NULL DEFAULT '0',
84 `media` int(10) unsigned NOT NULL DEFAULT '0',
85 PRIMARY KEY (`id`),
86 KEY `sponsor` (`sponsor`),
87 KEY `media` (`media`),
88 CONSTRAINT `banner_ibfk_1` FOREIGN KEY (`sponsor`) REFERENCES `sponsor` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
89 CONSTRAINT `banner_ibfk_2` FOREIGN KEY (`media`) REFERENCES `media_t` (`id`) ON UPDATE CASCADE
90 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
91 /*!40101 SET character_set_client = @saved_cs_client */;
92
93 --
94 -- Table structure for table `contributor`
95 --
96
97 DROP TABLE IF EXISTS `contributor`;
98 /*!40101 SET @saved_cs_client = @@character_set_client */;
99 /*!40101 SET character_set_client = utf8 */;
100 CREATE TABLE `contributor` (
101 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
102 `name` varchar(32) NOT NULL,
103 `password` char(40) NOT NULL DEFAULT '89d1bc57b4da2ae450e57898cf0f5ef80959458e',
104 `email` tinytext NOT NULL,
105 `nameplate` tinytext NOT NULL,
106 `default_image` tinytext,
107 `default_link` tinytext,
108 PRIMARY KEY (`id`),
109 UNIQUE KEY `name` (`name`)
110 ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;
111 /*!40101 SET character_set_client = @saved_cs_client */;
112
113 --
114 -- Table structure for table `fredart`
115 --
116
117 DROP TABLE IF EXISTS `fredart`;
118 /*!40101 SET @saved_cs_client = @@character_set_client */;
119 /*!40101 SET character_set_client = utf8 */;
120 CREATE TABLE `fredart` (
121 `pubdate` datetime NOT NULL,
122 `title` tinytext,
123 `link` varchar(255) DEFAULT NULL,
124 UNIQUE KEY `link` (`link`)
125 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
126 /*!40101 SET character_set_client = @saved_cs_client */;
127
128 --
129 -- Table structure for table `media_t`
130 --
131
132 DROP TABLE IF EXISTS `media_t`;
133 /*!40101 SET @saved_cs_client = @@character_set_client */;
134 /*!40101 SET character_set_client = utf8 */;
135 CREATE TABLE `media_t` (
136 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
137 `extension` char(3) NOT NULL,
138 `description` tinytext,
139 PRIMARY KEY (`id`),
140 UNIQUE KEY `extension` (`extension`)
141 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
142 /*!40101 SET character_set_client = @saved_cs_client */;
143
144 --
145 -- Table structure for table `meta`
146 --
147
148 DROP TABLE IF EXISTS `meta`;
149 /*!40101 SET @saved_cs_client = @@character_set_client */;
150 /*!40101 SET character_set_client = utf8 */;
151 CREATE TABLE `meta` (
152 `type` int(10) unsigned NOT NULL,
153 `meta` int(10) unsigned NOT NULL,
154 KEY `type` (`type`),
155 KEY `meta` (`meta`),
156 CONSTRAINT `meta_ibfk_1` FOREIGN KEY (`type`) REFERENCES `strip_t` (`id`) ON UPDATE CASCADE,
157 CONSTRAINT `meta_ibfk_2` FOREIGN KEY (`meta`) REFERENCES `meta_t` (`id`) ON UPDATE CASCADE
158 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
159 /*!40101 SET character_set_client = @saved_cs_client */;
160
161 --
162 -- Table structure for table `meta_t`
163 --
164
165 DROP TABLE IF EXISTS `meta_t`;
166 /*!40101 SET @saved_cs_client = @@character_set_client */;
167 /*!40101 SET character_set_client = utf8 */;
168 CREATE TABLE `meta_t` (
169 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
170 `name` tinytext,
171 PRIMARY KEY (`id`)
172 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
173 /*!40101 SET character_set_client = @saved_cs_client */;
174
175 --
176 -- Table structure for table `rant`
177 --
178
179 DROP TABLE IF EXISTS `rant`;
180 /*!40101 SET @saved_cs_client = @@character_set_client */;
181 /*!40101 SET character_set_client = utf8 */;
182 CREATE TABLE `rant` (
183 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
184 `published` datetime NOT NULL,
185 `status` enum('published','draft') CHARACTER SET latin1 NOT NULL,
186 `side` enum('left','right') CHARACTER SET latin1 DEFAULT NULL,
187 `author` int(10) unsigned NOT NULL,
188 `title` tinytext CHARACTER SET latin1 NOT NULL,
189 `body` mediumtext CHARACTER SET latin1 NOT NULL,
190 `link` tinytext CHARACTER SET latin1,
191 `imagetype` int(10) unsigned NOT NULL,
192 `imagetext` tinytext CHARACTER SET latin1,
193 PRIMARY KEY (`id`),
194 KEY `author` (`author`),
195 KEY `imagetype` (`imagetype`),
196 KEY `published` (`published`),
197 KEY `halp` (`author`,`imagetype`,`published`),
198 CONSTRAINT `rant_ibfk_1` FOREIGN KEY (`author`) REFERENCES `contributor` (`id`) ON UPDATE CASCADE,
199 CONSTRAINT `rant_ibfk_2` FOREIGN KEY (`imagetype`) REFERENCES `media_t` (`id`) ON UPDATE CASCADE
200 ) ENGINE=InnoDB AUTO_INCREMENT=1099 DEFAULT CHARSET=utf8;
201 /*!40101 SET character_set_client = @saved_cs_client */;
202
203 --
204 -- Table structure for table `rant_attachment`
205 --
206
207 DROP TABLE IF EXISTS `rant_attachment`;
208 /*!40101 SET @saved_cs_client = @@character_set_client */;
209 /*!40101 SET character_set_client = utf8 */;
210 CREATE TABLE `rant_attachment` (
211 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
212 `rant` int(10) unsigned NOT NULL,
213 `media` int(10) unsigned NOT NULL,
214 PRIMARY KEY (`id`),
215 KEY `rant` (`rant`),
216 KEY `media` (`media`),
217 CONSTRAINT `rant_attachment_ibfk_1` FOREIGN KEY (`rant`) REFERENCES `rant` (`id`) ON UPDATE CASCADE,
218 CONSTRAINT `rant_attachment_ibfk_2` FOREIGN KEY (`media`) REFERENCES `media_t` (`id`) ON UPDATE CASCADE
219 ) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin1;
220 /*!40101 SET character_set_client = @saved_cs_client */;
221
222 --
223 -- Table structure for table `rss_comment`
224 --
225
226 DROP TABLE IF EXISTS `rss_comment`;
227 /*!40101 SET @saved_cs_client = @@character_set_client */;
228 /*!40101 SET character_set_client = utf8 */;
229 CREATE TABLE `rss_comment` (
230 `published` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
231 `body` tinytext,
232 `url` tinytext
233 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
234 /*!40101 SET character_set_client = @saved_cs_client */;
235
236 --
237 -- Table structure for table `scratchpad`
238 --
239
240 DROP TABLE IF EXISTS `scratchpad`;
241 /*!40101 SET @saved_cs_client = @@character_set_client */;
242 /*!40101 SET character_set_client = utf8 */;
243 CREATE TABLE `scratchpad` (
244 `published` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
245 `contributor` int(10) unsigned NOT NULL,
246 `message` tinytext,
247 KEY `contributor` (`contributor`),
248 CONSTRAINT `scratchpad_ibfk_1` FOREIGN KEY (`contributor`) REFERENCES `contributor` (`id`) ON UPDATE CASCADE
249 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
250 /*!40101 SET character_set_client = @saved_cs_client */;
251
252 --
253 -- Table structure for table `sponsor`
254 --
255
256 DROP TABLE IF EXISTS `sponsor`;
257 /*!40101 SET @saved_cs_client = @@character_set_client */;
258 /*!40101 SET character_set_client = utf8 */;
259 CREATE TABLE `sponsor` (
260 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
261 `name` tinytext NOT NULL,
262 PRIMARY KEY (`id`)
263 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
264 /*!40101 SET character_set_client = @saved_cs_client */;
265
266 --
267 -- Table structure for table `static_page`
268 --
269
270 DROP TABLE IF EXISTS `static_page`;
271 /*!40101 SET @saved_cs_client = @@character_set_client */;
272 /*!40101 SET character_set_client = utf8 */;
273 CREATE TABLE `static_page` (
274 `url_name` varchar(32) NOT NULL,
275 `pubdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
276 `status` enum('published','draft') NOT NULL,
277 `title` varchar(64) DEFAULT NULL,
278 `body` text,
279 `style` text,
280 PRIMARY KEY (`url_name`)
281 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
282 /*!40101 SET character_set_client = @saved_cs_client */;
283
284 --
285 -- Table structure for table `status`
286 --
287
288 DROP TABLE IF EXISTS `status`;
289 /*!40101 SET @saved_cs_client = @@character_set_client */;
290 /*!40101 SET character_set_client = utf8 */;
291 CREATE TABLE `status` (
292 `published` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
293 `eta` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
294 `percentage` tinyint(3) unsigned NOT NULL DEFAULT '0',
295 `text` tinytext CHARACTER SET latin1
296 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
297 /*!40101 SET character_set_client = @saved_cs_client */;
298
299 --
300 -- Table structure for table `strip`
301 --
302
303 DROP TABLE IF EXISTS `strip`;
304 /*!40101 SET @saved_cs_client = @@character_set_client */;
305 /*!40101 SET character_set_client = utf8 */;
306 CREATE TABLE `strip` (
307 `id` int(10) unsigned NOT NULL,
308 `published` datetime NOT NULL,
309 `media` int(10) unsigned NOT NULL,
310 `type` int(10) unsigned NOT NULL,
311 `title` tinytext CHARACTER SET latin1,
312 `book` tinyint(3) unsigned DEFAULT NULL,
313 `page` tinyint(3) unsigned DEFAULT NULL,
314 PRIMARY KEY (`id`),
315 KEY `media` (`media`),
316 KEY `type` (`type`),
317 KEY `published` (`published`),
318 CONSTRAINT `strip_ibfk_1` FOREIGN KEY (`media`) REFERENCES `media_t` (`id`) ON UPDATE CASCADE,
319 CONSTRAINT `strip_ibfk_2` FOREIGN KEY (`type`) REFERENCES `strip_t` (`id`) ON UPDATE CASCADE
320 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
321 /*!40101 SET character_set_client = @saved_cs_client */;
322
323 --
324 -- Table structure for table `strip_t`
325 --
326
327 DROP TABLE IF EXISTS `strip_t`;
328 /*!40101 SET @saved_cs_client = @@character_set_client */;
329 /*!40101 SET character_set_client = utf8 */;
330 CREATE TABLE `strip_t` (
331 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
332 `name` varchar(8) NOT NULL,
333 `description` tinytext,
334 `meta` int(10) unsigned DEFAULT NULL,
335 PRIMARY KEY (`id`),
336 UNIQUE KEY `name` (`name`),
337 KEY `meta` (`meta`),
338 CONSTRAINT `strip_t_ibfk_1` FOREIGN KEY (`meta`) REFERENCES `meta_t` (`id`) ON UPDATE CASCADE
339 ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1;
340 /*!40101 SET character_set_client = @saved_cs_client */;
341
342 --
343 -- Table structure for table `support`
344 --
345
346 DROP TABLE IF EXISTS `support`;
347 /*!40101 SET @saved_cs_client = @@character_set_client */;
348 /*!40101 SET character_set_client = utf8 */;
349 CREATE TABLE `support` (
350 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
351 `sponsor` int(10) unsigned NOT NULL DEFAULT '0',
352 `published` date NOT NULL DEFAULT '0000-00-00',
353 `sunset` date NOT NULL DEFAULT '0000-00-00',
354 `link` tinytext,
355 `banner` int(10) unsigned DEFAULT NULL,
356 `body` tinytext,
357 PRIMARY KEY (`id`),
358 KEY `sponsor` (`sponsor`),
359 KEY `banner` (`banner`),
360 CONSTRAINT `support_ibfk_1` FOREIGN KEY (`sponsor`) REFERENCES `sponsor` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
361 CONSTRAINT `support_ibfk_2` FOREIGN KEY (`banner`) REFERENCES `banner` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
362 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
363 /*!40101 SET character_set_client = @saved_cs_client */;
364
365 --
366 -- Table structure for table `transcript`
367 --
368
369 DROP TABLE IF EXISTS `transcript`;
370 /*!40101 SET @saved_cs_client = @@character_set_client */;
371 /*!40101 SET character_set_client = utf8 */;
372 CREATE TABLE `transcript` (
373 `strip` int(10) unsigned NOT NULL,
374 `panel` int(10) unsigned NOT NULL,
375 `line` int(10) unsigned NOT NULL,
376 `speaker` varchar(64) NOT NULL,
377 `speech` text,
378 `search` text,
379 PRIMARY KEY (`strip`,`panel`,`line`),
380 KEY `speaker` (`speaker`),
381 KEY `transcript_speaker` (`speaker`),
382 CONSTRAINT `transcript_ibfk_1` FOREIGN KEY (`strip`) REFERENCES `strip` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
383 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
384 /*!40101 SET character_set_client = @saved_cs_client */;
385
386 --
387 -- Table structure for table `twitter_post`
388 --
389
390 DROP TABLE IF EXISTS `twitter_post`;
391 /*!40101 SET @saved_cs_client = @@character_set_client */;
392 /*!40101 SET character_set_client = utf8 */;
393 CREATE TABLE `twitter_post` (
394 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
395 `status` enum('scheduled','locked','error','success') NOT NULL,
396 `user` int(10) unsigned NOT NULL,
397 `time` datetime NOT NULL,
398 `text` varchar(140) NOT NULL,
399 PRIMARY KEY (`id`),
400 KEY `user` (`user`)
401 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
402 /*!40101 SET character_set_client = @saved_cs_client */;
403
404 --
405 -- Table structure for table `twitter_status`
406 --
407
408 DROP TABLE IF EXISTS `twitter_status`;
409 /*!40101 SET @saved_cs_client = @@character_set_client */;
410 /*!40101 SET character_set_client = utf8 */;
411 CREATE TABLE `twitter_status` (
412 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
413 `position` int(10) unsigned NOT NULL,
414 `message` varchar(140) DEFAULT NULL,
415 PRIMARY KEY (`id`)
416 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
417 /*!40101 SET character_set_client = @saved_cs_client */;
418
419 --
420 -- Table structure for table `twitter_user`
421 --
422
423 DROP TABLE IF EXISTS `twitter_user`;
424 /*!40101 SET @saved_cs_client = @@character_set_client */;
425 /*!40101 SET character_set_client = utf8 */;
426 CREATE TABLE `twitter_user` (
427 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
428 `username` tinytext NOT NULL,
429 `password` tinytext NOT NULL,
430 `oauth_token` tinytext,
431 `oauth_token_secret` tinytext,
432 `oauth_access_token` tinytext,
433 PRIMARY KEY (`id`),
434 UNIQUE KEY `username` (`username`(100))
435 ) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
436 /*!40101 SET character_set_client = @saved_cs_client */;
437 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
438
439 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
440 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
441 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
442 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
443 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
444 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
445 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
446
447 -- Dump completed on 2017-06-12 15:52:27
Comments 0
You need to be logged in to leave comments. Login now