· 8 years ago · May 20, 2018, 07:54 PM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5CREATE SCHEMA IF NOT EXISTS `nerdstar` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
6USE `nerdstar` ;
7
8-- -----------------------------------------------------
9-- Table `nerdstar`.`user_position`
10-- -----------------------------------------------------
11DROP TABLE IF EXISTS `nerdstar`.`user_position` ;
12
13CREATE TABLE IF NOT EXISTS `nerdstar`.`user_position` (
14 `id` INT NOT NULL ,
15 `name` VARCHAR(45) NULL ,
16 `description` TEXT NULL ,
17 PRIMARY KEY (`id`) )
18ENGINE = InnoDB;
19
20
21-- -----------------------------------------------------
22-- Table `nerdstar`.`user`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `nerdstar`.`user` ;
25
26CREATE TABLE IF NOT EXISTS `nerdstar`.`user` (
27 `id` INT NOT NULL AUTO_INCREMENT ,
28 `user_name` VARCHAR(45) NULL ,
29 `first_name` VARCHAR(45) NULL ,
30 `last_name` VARCHAR(45) NULL ,
31 `email` VARCHAR(45) NULL ,
32 `slogan` VARCHAR(500) NULL ,
33 `password` VARCHAR(32) NULL ,
34 `permissions` VARCHAR(26) NULL ,
35 `join_date` INT NULL ,
36 `active` TINYINT(1) NULL ,
37 `position_id` INT NULL ,
38 PRIMARY KEY (`id`) ,
39 INDEX `position_id` (`position_id` ASC) ,
40 CONSTRAINT `position_id`
41 FOREIGN KEY (`position_id` )
42 REFERENCES `nerdstar`.`user_position` (`id` )
43 ON DELETE NO ACTION
44 ON UPDATE NO ACTION)
45ENGINE = InnoDB;
46
47
48-- -----------------------------------------------------
49-- Table `nerdstar`.`image_cat`
50-- -----------------------------------------------------
51DROP TABLE IF EXISTS `nerdstar`.`image_cat` ;
52
53CREATE TABLE IF NOT EXISTS `nerdstar`.`image_cat` (
54 `id` INT NOT NULL ,
55 `name` VARCHAR(45) NULL ,
56 `description` TEXT NULL ,
57 PRIMARY KEY (`id`) )
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table `nerdstar`.`image_album`
63-- -----------------------------------------------------
64DROP TABLE IF EXISTS `nerdstar`.`image_album` ;
65
66CREATE TABLE IF NOT EXISTS `nerdstar`.`image_album` (
67 `id` INT NOT NULL ,
68 `name` VARCHAR(45) NULL ,
69 `description` TEXT NULL ,
70 PRIMARY KEY (`id`) )
71ENGINE = InnoDB;
72
73
74-- -----------------------------------------------------
75-- Table `nerdstar`.`image`
76-- -----------------------------------------------------
77DROP TABLE IF EXISTS `nerdstar`.`image` ;
78
79CREATE TABLE IF NOT EXISTS `nerdstar`.`image` (
80 `id` INT NOT NULL ,
81 `name` VARCHAR(45) NULL ,
82 `link` VARCHAR(128) NULL ,
83 `description` VARCHAR(500) NULL ,
84 `album_id` INT NULL ,
85 `cat_id` INT NULL ,
86 PRIMARY KEY (`id`) ,
87 INDEX `cat_id` (`cat_id` ASC) ,
88 INDEX `album_id` (`album_id` ASC) ,
89 CONSTRAINT `cat_id`
90 FOREIGN KEY (`cat_id` )
91 REFERENCES `nerdstar`.`image_cat` (`id` )
92 ON DELETE NO ACTION
93 ON UPDATE NO ACTION,
94 CONSTRAINT `album_id`
95 FOREIGN KEY (`album_id` )
96 REFERENCES `nerdstar`.`image_album` (`id` )
97 ON DELETE NO ACTION
98 ON UPDATE NO ACTION)
99ENGINE = InnoDB;
100
101
102-- -----------------------------------------------------
103-- Table `nerdstar`.`slideshow`
104-- -----------------------------------------------------
105DROP TABLE IF EXISTS `nerdstar`.`slideshow` ;
106
107CREATE TABLE IF NOT EXISTS `nerdstar`.`slideshow` (
108 `id` INT NOT NULL ,
109 `image_id` INT NULL ,
110 `url` VARCHAR(45) NULL ,
111 PRIMARY KEY (`id`) ,
112 INDEX `image_id` (`image_id` ASC) ,
113 CONSTRAINT `image_id`
114 FOREIGN KEY (`image_id` )
115 REFERENCES `nerdstar`.`image` (`id` )
116 ON DELETE NO ACTION
117 ON UPDATE NO ACTION)
118ENGINE = InnoDB;
119
120
121-- -----------------------------------------------------
122-- Table `nerdstar`.`news`
123-- -----------------------------------------------------
124DROP TABLE IF EXISTS `nerdstar`.`news` ;
125
126CREATE TABLE IF NOT EXISTS `nerdstar`.`news` (
127 `id` INT NOT NULL AUTO_INCREMENT ,
128 `title` VARCHAR(45) NULL ,
129 `description` TEXT NULL ,
130 `content` TEXT NULL ,
131 `timestamp` INT NULL ,
132 `active` TINYINT(1) NULL ,
133 `cat_id` INT NULL ,
134 `writer_id` INT NULL ,
135 PRIMARY KEY (`id`) ,
136 INDEX `writer_id` (`writer_id` ASC) ,
137 CONSTRAINT `writer_id`
138 FOREIGN KEY (`writer_id` )
139 REFERENCES `nerdstar`.`user` (`id` )
140 ON DELETE NO ACTION
141 ON UPDATE NO ACTION)
142ENGINE = InnoDB;
143
144
145-- -----------------------------------------------------
146-- Table `nerdstar`.`show_cat`
147-- -----------------------------------------------------
148DROP TABLE IF EXISTS `nerdstar`.`show_cat` ;
149
150CREATE TABLE IF NOT EXISTS `nerdstar`.`show_cat` (
151 `id` INT NOT NULL AUTO_INCREMENT ,
152 `name` VARCHAR(45) NULL ,
153 `description` TEXT NULL ,
154 PRIMARY KEY (`id`) )
155ENGINE = InnoDB;
156
157
158-- -----------------------------------------------------
159-- Table `nerdstar`.`show`
160-- -----------------------------------------------------
161DROP TABLE IF EXISTS `nerdstar`.`show` ;
162
163CREATE TABLE IF NOT EXISTS `nerdstar`.`show` (
164 `id` INT NOT NULL AUTO_INCREMENT ,
165 `title` VARCHAR(45) NULL ,
166 `description` TEXT NULL ,
167 `thumbnail` VARCHAR(45) NULL ,
168 `videohash` VARCHAR(32) NULL ,
169 `timestamp` INT NULL ,
170 `youtube` TINYINT(1) NULL ,
171 `allow_comments` TINYINT(1) NULL ,
172 `cat_id` INT NULL ,
173 `thumbnail_id` INT NULL ,
174 PRIMARY KEY (`id`) ,
175 INDEX `cat_id` (`cat_id` ASC) ,
176 INDEX `thumbnail_id` (`thumbnail_id` ASC) ,
177 CONSTRAINT `cat_id`
178 FOREIGN KEY (`cat_id` )
179 REFERENCES `nerdstar`.`show_cat` (`id` )
180 ON DELETE NO ACTION
181 ON UPDATE NO ACTION,
182 CONSTRAINT `thumbnail_id`
183 FOREIGN KEY (`thumbnail_id` )
184 REFERENCES `nerdstar`.`image` (`id` )
185 ON DELETE NO ACTION
186 ON UPDATE NO ACTION)
187ENGINE = InnoDB;
188
189
190-- -----------------------------------------------------
191-- Table `nerdstar`.`show_comment`
192-- -----------------------------------------------------
193DROP TABLE IF EXISTS `nerdstar`.`show_comment` ;
194
195CREATE TABLE IF NOT EXISTS `nerdstar`.`show_comment` (
196 `id` INT NOT NULL AUTO_INCREMENT ,
197 `comment` TEXT NULL ,
198 `name` VARCHAR(45) NULL ,
199 `timestamp` INT NULL ,
200 `active` TINYINT(1) NULL ,
201 `show_id` INT NULL ,
202 PRIMARY KEY (`id`) ,
203 INDEX `show_id` (`show_id` ASC) ,
204 CONSTRAINT `show_id`
205 FOREIGN KEY (`show_id` )
206 REFERENCES `nerdstar`.`show` (`id` )
207 ON DELETE NO ACTION
208 ON UPDATE NO ACTION)
209ENGINE = InnoDB;
210
211
212-- -----------------------------------------------------
213-- Table `nerdstar`.`blog_cat`
214-- -----------------------------------------------------
215DROP TABLE IF EXISTS `nerdstar`.`blog_cat` ;
216
217CREATE TABLE IF NOT EXISTS `nerdstar`.`blog_cat` (
218 `id` INT NOT NULL AUTO_INCREMENT ,
219 `name` VARCHAR(45) NULL ,
220 `description` TEXT NULL ,
221 `icon_id` INT NULL ,
222 PRIMARY KEY (`id`) ,
223 INDEX `thumbnail_id` (`icon_id` ASC) ,
224 CONSTRAINT `thumbnail_id`
225 FOREIGN KEY (`icon_id` )
226 REFERENCES `nerdstar`.`image` (`id` )
227 ON DELETE NO ACTION
228 ON UPDATE NO ACTION)
229ENGINE = InnoDB;
230
231
232-- -----------------------------------------------------
233-- Table `nerdstar`.`blog`
234-- -----------------------------------------------------
235DROP TABLE IF EXISTS `nerdstar`.`blog` ;
236
237CREATE TABLE IF NOT EXISTS `nerdstar`.`blog` (
238 `id` INT NOT NULL AUTO_INCREMENT ,
239 `title` VARCHAR(45) NULL ,
240 `description` TEXT NULL ,
241 `content` TEXT NULL ,
242 `releasedate` INT NULL ,
243 `timestamp` INT NULL ,
244 `active` TINYINT(1) NULL ,
245 `writer_id` INT NULL ,
246 `cat_id` INT NULL ,
247 `thumbnail_id` INT NULL ,
248 PRIMARY KEY (`id`) ,
249 INDEX `writer_id` (`writer_id` ASC) ,
250 INDEX `cat_id` (`cat_id` ASC) ,
251 INDEX `thumbnail_id` (`thumbnail_id` ASC) ,
252 CONSTRAINT `writer_id`
253 FOREIGN KEY (`writer_id` )
254 REFERENCES `nerdstar`.`user` (`id` )
255 ON DELETE NO ACTION
256 ON UPDATE NO ACTION,
257 CONSTRAINT `cat_id`
258 FOREIGN KEY (`cat_id` )
259 REFERENCES `nerdstar`.`blog_cat` (`id` )
260 ON DELETE NO ACTION
261 ON UPDATE NO ACTION,
262 CONSTRAINT `thumbnail_id`
263 FOREIGN KEY (`thumbnail_id` )
264 REFERENCES `nerdstar`.`image` (`id` )
265 ON DELETE NO ACTION
266 ON UPDATE NO ACTION)
267ENGINE = InnoDB;
268
269
270-- -----------------------------------------------------
271-- Table `nerdstar`.`blog_comment`
272-- -----------------------------------------------------
273DROP TABLE IF EXISTS `nerdstar`.`blog_comment` ;
274
275CREATE TABLE IF NOT EXISTS `nerdstar`.`blog_comment` (
276 `id` INT NOT NULL AUTO_INCREMENT ,
277 `comment` TEXT NULL ,
278 `name` VARCHAR(45) NULL ,
279 `timestamp` INT NULL ,
280 `active` TINYINT(1) NULL ,
281 `show_id` INT NULL ,
282 PRIMARY KEY (`id`) ,
283 INDEX `show_id` (`show_id` ASC) ,
284 CONSTRAINT `blog_id`
285 FOREIGN KEY (`show_id` )
286 REFERENCES `nerdstar`.`blog` (`id` )
287 ON DELETE NO ACTION
288 ON UPDATE NO ACTION)
289ENGINE = InnoDB;
290
291
292-- -----------------------------------------------------
293-- Table `nerdstar`.`poll`
294-- -----------------------------------------------------
295DROP TABLE IF EXISTS `nerdstar`.`poll` ;
296
297CREATE TABLE IF NOT EXISTS `nerdstar`.`poll` (
298 `id` INT NOT NULL AUTO_INCREMENT ,
299 `question` VARCHAR(45) NULL ,
300 `start_date` INT NULL ,
301 `end_date` INT NULL ,
302 `active` TINYINT(1) NULL ,
303 `image_id` INT NULL ,
304 PRIMARY KEY (`id`) ,
305 INDEX `image_id` (`image_id` ASC) ,
306 CONSTRAINT `image_id`
307 FOREIGN KEY (`image_id` )
308 REFERENCES `nerdstar`.`image` (`id` )
309 ON DELETE NO ACTION
310 ON UPDATE NO ACTION)
311ENGINE = InnoDB;
312
313
314-- -----------------------------------------------------
315-- Table `nerdstar`.`poll_options`
316-- -----------------------------------------------------
317DROP TABLE IF EXISTS `nerdstar`.`poll_options` ;
318
319CREATE TABLE IF NOT EXISTS `nerdstar`.`poll_options` (
320 `id` INT NOT NULL AUTO_INCREMENT ,
321 `option` VARCHAR(45) NULL ,
322 `votes` INT NULL ,
323 `active` TINYINT(1) NULL ,
324 `poll_id` INT NULL ,
325 PRIMARY KEY (`id`) ,
326 INDEX `poll_id` (`poll_id` ASC) ,
327 CONSTRAINT `poll_id`
328 FOREIGN KEY (`poll_id` )
329 REFERENCES `nerdstar`.`poll` (`id` )
330 ON DELETE NO ACTION
331 ON UPDATE NO ACTION)
332ENGINE = InnoDB;
333
334
335-- -----------------------------------------------------
336-- Table `nerdstar`.`newsletter_user`
337-- -----------------------------------------------------
338DROP TABLE IF EXISTS `nerdstar`.`newsletter_user` ;
339
340CREATE TABLE IF NOT EXISTS `nerdstar`.`newsletter_user` (
341 `id` INT NOT NULL AUTO_INCREMENT ,
342 `email` VARCHAR(45) NULL ,
343 `active` TINYINT(1) NULL ,
344 PRIMARY KEY (`id`) )
345ENGINE = InnoDB;
346
347
348-- -----------------------------------------------------
349-- Table `nerdstar`.`newsletter`
350-- -----------------------------------------------------
351DROP TABLE IF EXISTS `nerdstar`.`newsletter` ;
352
353CREATE TABLE IF NOT EXISTS `nerdstar`.`newsletter` (
354 `id` INT NOT NULL ,
355 `title` VARCHAR(128) NULL ,
356 `content` TEXT NULL ,
357 `sent` TINYINT(1) NULL ,
358 PRIMARY KEY (`id`) )
359ENGINE = InnoDB;
360
361
362
363SET SQL_MODE=@OLD_SQL_MODE;
364SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
365SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;