Tracky/database.sql at main · Programie/Tracky · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
CREATE TABLE `users`
(
    `id`       int(11)      NOT NULL AUTO_INCREMENT,
    `username` varchar(100) NOT NULL,
    `password` varchar(200) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX (`username`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE `shows`
(
    `id`             int(11)      NOT NULL AUTO_INCREMENT,
    `title`          varchar(300) NOT NULL,
    `dataProvider`   enum ('tmdb', 'tvdb')                    DEFAULT NULL,
    `tmdbId`         int(11)                                  DEFAULT NULL,
    `tvdbId`         int(11)                                  DEFAULT NULL,
    `posterImageUrl` varchar(500)                             DEFAULT NULL,
    `language`       varchar(10)                              DEFAULT NULL,
    `status`         enum ('upcoming', 'continuing', 'ended') DEFAULT NULL,
    `lastUpdate`     datetime                                 DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE `seasons`
(
    `id`             int(11) NOT NULL AUTO_INCREMENT,
    `show`           int(11) NOT NULL,
    `number`         int(11) NOT NULL,
    `posterImageUrl` varchar(500) DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`show`) REFERENCES `shows` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE `episodes`
(
    `id`             int(11)      NOT NULL AUTO_INCREMENT,
    `season`         int(11)      NOT NULL,
    `number`         int(11)      NOT NULL,
    `title`          varchar(300) NOT NULL,
    `plot`           text         DEFAULT NULL,
    `firstAired`     date         DEFAULT NULL,
    `runtime`        int(11)      DEFAULT NULL,
    `posterImageUrl` varchar(500) DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`season`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE `movies`
(
    `id`             int(11)      NOT NULL AUTO_INCREMENT,
    `title`          varchar(300) NOT NULL,
    `tagline`        varchar(500)          DEFAULT NULL,
    `plot`           text                  DEFAULT NULL,
    `year`           int(11)               DEFAULT NULL,
    `runtime`        int(11)               DEFAULT NULL,
    `dataProvider`   enum ('tmdb', 'tvdb') DEFAULT NULL,
    `tmdbId`         int(11)               DEFAULT NULL,
    `tvdbId`         int(11)               DEFAULT NULL,
    `posterImageUrl` varchar(500)          DEFAULT NULL,
    `language`       varchar(10)           DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE `views`
(
    `id`       int(11)  NOT NULL AUTO_INCREMENT,
    `user`     int(11)  NOT NULL,
    `type`     enum ('episode', 'movie'),
    `item`     int(11)  NOT NULL,
    `datetime` datetime NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE `scrobblequeue`
(
    `id`       int(11)  NOT NULL AUTO_INCREMENT,
    `user`     int(11)  NOT NULL,
    `json`     json     NOT NULL,
    `datetime` datetime NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;