· 8 years ago · Mar 08, 2018, 11:02 PM
1
2# This is a fix for InnoDB in MySQL >= 4.1.x
3# It "suspends judgement" for fkey relationships until are tables are set.
4SET FOREIGN_KEY_CHECKS = 0;
5
6#-----------------------------------------------------------------------------
7#-- client
8#-----------------------------------------------------------------------------
9
10DROP TABLE IF EXISTS `client`;
11
12
13CREATE TABLE `client`
14(
15 `torrent_id` INTEGER,
16 `created_at` DATETIME,
17 `updated_at` DATETIME,
18 `port` INTEGER,
19 `bytes_uploaded` INTEGER,
20 `bytes_downloaded` INTEGER,
21 `bytes_left` INTEGER,
22 `ip` VARCHAR(40),
23 `client_key` VARCHAR(60),
24 `tracker_id` VARCHAR(60),
25 `peer_id` VARCHAR(60),
26 `id` INTEGER NOT NULL AUTO_INCREMENT,
27 PRIMARY KEY (`id`),
28 INDEX `client_FI_1` (`torrent_id`),
29 CONSTRAINT `client_FK_1`
30 FOREIGN KEY (`torrent_id`)
31 REFERENCES `torrent` (`id`)
32 ON DELETE CASCADE
33)Type=InnoDB;
34
35#-----------------------------------------------------------------------------
36#-- torrent
37#-----------------------------------------------------------------------------
38
39DROP TABLE IF EXISTS `torrent`;
40
41
42CREATE TABLE `torrent`
43(
44 `episode_id` INTEGER default null,
45 `feed_id` INTEGER default null,
46 `created_at` DATETIME,
47 `updated_at` DATETIME,
48 `file` VARCHAR(255),
49 `size` INTEGER,
50 `mime_type` VARCHAR(40),
51 `web_url` TEXT,
52 `file_sha1` VARCHAR(40),
53 `guid_hash` VARCHAR(40),
54 `info_hash` VARCHAR(40),
55 `downloads` INTEGER,
56 `seeders` INTEGER,
57 `peers` INTEGER,
58 `id` INTEGER NOT NULL AUTO_INCREMENT,
59 PRIMARY KEY (`id`),
60 UNIQUE KEY `torrent_U_1` (`guid_hash`),
61 UNIQUE KEY `unique_file_name` (`file`, `episode_id`),
62 UNIQUE KEY `unique_episode_feed` (`episode_id`, `feed_id`),
63 CONSTRAINT `torrent_FK_1`
64 FOREIGN KEY (`episode_id`)
65 REFERENCES `episode` (`id`)
66 ON DELETE CASCADE,
67 INDEX `torrent_FI_2` (`feed_id`),
68 CONSTRAINT `torrent_FK_2`
69 FOREIGN KEY (`feed_id`)
70 REFERENCES `feed` (`id`)
71 ON DELETE CASCADE
72)Type=InnoDB;
73
74#-----------------------------------------------------------------------------
75#-- episode
76#-----------------------------------------------------------------------------
77
78DROP TABLE IF EXISTS `episode`;
79
80
81CREATE TABLE `episode`
82(
83 `podcast_id` INTEGER default null,
84 `created_at` DATETIME,
85 `updated_at` DATETIME,
86 `title` TEXT,
87 `slug` VARCHAR(255),
88 `description` TEXT,
89 `length` INTEGER,
90 `downloads` INTEGER,
91 `guid_hash` VARCHAR(40),
92 `id` INTEGER NOT NULL AUTO_INCREMENT,
93 PRIMARY KEY (`id`),
94 UNIQUE KEY `episode_U_1` (`guid_hash`),
95 UNIQUE KEY `unique_slug` (`slug`, `podcast_id`),
96 INDEX `episode_FI_1` (`podcast_id`),
97 CONSTRAINT `episode_FK_1`
98 FOREIGN KEY (`podcast_id`)
99 REFERENCES `podcast` (`id`)
100 ON DELETE CASCADE
101)Type=InnoDB;
102
103#-----------------------------------------------------------------------------
104#-- feed
105#-----------------------------------------------------------------------------
106
107DROP TABLE IF EXISTS `feed`;
108
109
110CREATE TABLE `feed`
111(
112 `podcast_id` INTEGER default null,
113 `created_at` DATETIME,
114 `updated_at` DATETIME,
115 `last_fetched` DATETIME,
116 `title` TEXT,
117 `slug` VARCHAR(255),
118 `rss_url` TEXT,
119 `rss_url_hash` VARCHAR(40),
120 `id` INTEGER NOT NULL AUTO_INCREMENT,
121 PRIMARY KEY (`id`),
122 UNIQUE KEY `feed_U_1` (`rss_url_hash`),
123 INDEX `feed_FI_1` (`podcast_id`),
124 CONSTRAINT `feed_FK_1`
125 FOREIGN KEY (`podcast_id`)
126 REFERENCES `podcast` (`id`)
127 ON DELETE CASCADE
128)Type=InnoDB;
129
130#-----------------------------------------------------------------------------
131#-- podcast
132#-----------------------------------------------------------------------------
133
134DROP TABLE IF EXISTS `podcast`;
135
136
137CREATE TABLE `podcast`
138(
139 `default_feed_id` INTEGER default null,
140 `slug` VARCHAR(255),
141 `created_at` DATETIME,
142 `updated_at` DATETIME,
143 `title` TEXT,
144 `description` TEXT,
145 `author` TEXT,
146 `email` TEXT,
147 `link` TEXT,
148 `itunes_id` TEXT,
149 `image_url` TEXT,
150 `id` INTEGER NOT NULL AUTO_INCREMENT,
151 PRIMARY KEY (`id`),
152 UNIQUE KEY `podcast_U_1` (`slug`),
153 INDEX `podcast_FI_1` (`default_feed_id`),
154 CONSTRAINT `podcast_FK_1`
155 FOREIGN KEY (`default_feed_id`)
156 REFERENCES `feed` (`id`)
157)Type=InnoDB;
158
159#-----------------------------------------------------------------------------
160#-- setting
161#-----------------------------------------------------------------------------
162
163DROP TABLE IF EXISTS `setting`;
164
165
166CREATE TABLE `setting`
167(
168 `key` VARCHAR(255),
169 `value` VARCHAR(255),
170 `vendor` VARCHAR(255),
171 `id` INTEGER NOT NULL AUTO_INCREMENT,
172 PRIMARY KEY (`id`),
173 UNIQUE KEY `setting_U_1` (`key`)
174)Type=InnoDB;
175
176# This restores the fkey checks, after having unset them earlier
177SET FOREIGN_KEY_CHECKS = 1;