TVGuide/schema.sql at master · Sopsy/TVGuide · 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

CREATETABLE `tvguide_channel` (
`id`int unsigned NOT NULL AUTO_INCREMENT,
`origin_id`varchar(100) NOT NULL,
`name`varchar(100) NOT NULL,
`slug`varchar(100) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`is_visible`bit(1) NOT NULL DEFAULT b'0',
`position` tinyint unsigned NOT NULL DEFAULT '255',
PRIMARY KEY (`id`),
 KEY `origin_id` (`origin_id`),
 UNIQUE `slug` (`name`)
);

CREATETABLE `tvguide_program` (
`id`int unsigned NOT NULL AUTO_INCREMENT,
`title`varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`description`varchar(6000) NOT NULL,
`start_time`timestampNOT NULL,
`end_time`timestampNOT NULL,
`channel_id`int unsigned NOT NULL,
`season`int unsigned DEFAULT NULL,
`episode`int unsigned DEFAULT NULL,
`episodes`int unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
 UNIQUE KEY `title` (`title`,`start_time`,`end_time`,`channel_id`),
 KEY `channel_id` (`channel_id`,`start_time`,`end_time`) USING BTREE,
 KEY `start_time` (`start_time`,`channel_id`) USING BTREE,
 KEY `end_time` (`end_time`,`channel_id`) USING BTREE,
CONSTRAINT`tvguide_program_ibfk_1`FOREIGN KEY (`channel_id`) REFERENCES`tvguide_channel` (`id`) ON DELETE CASCADEONUPDATE CASCADE
);

CREATETABLEtvguide_channel_group (
`id`INT UNSIGNED NOT NULL AUTO_INCREMENT,
`slug`VARCHAR(100) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`name`VARCHAR(100) NOT NULL,
`user_id`INT UNSIGNED DEFAULT NULL,
`position` TINYINT UNSIGNED NOT NULL DEFAULT 128,
`is_public`BITNOT NULL DEFAULT 0,
`is_default`BITNOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
 UNIQUE `slug` (`slug`, `user_id`),
CONSTRAINT`tvguide_channel_group_ibfk_1`FOREIGN KEY (`user_id`) REFERENCES user(id) ON DELETE CASCADEONUPDATE CASCADE
);

CREATETABLEtvguide_channel_group_channel (
`channel_group_id`int unsigned NOT NULL,
`channel_id`int unsigned,
`position` TINYINT UNSIGNED NOT NULL DEFAULT 128,
 KEY (`position`),
CONSTRAINT`tvguide_channel_group_channel_ibfk_1`FOREIGN KEY (`channel_group_id`) REFERENCES tvguide_channel_group(id) ON DELETE CASCADEONUPDATE CASCADE,
CONSTRAINT`tvguide_channel_group_channel_ibfk_2`FOREIGN KEY (`channel_id`) REFERENCES tvguide_channel(id) ON DELETE CASCADEONUPDATE CASCADE
);