-
Notifications
You must be signed in to change notification settings - Fork 1
/
schema.mysql.sql
40 lines (37 loc) · 1.62 KB
/
schema.mysql.sql
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
-- Create subjects table
CREATE TABLE IF NOT EXISTS `subjects` (
`subjectID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(90) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`abbreviation` varchar(90) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`subjectID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=343 ;
-- Create categories table
CREATE TABLE IF NOT EXISTS `categories` (
`categoryID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(90) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`categoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
-- Create files table
CREATE TABLE IF NOT EXISTS `files` (
`fileID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`subjectID` int(11) unsigned NOT NULL,
`categoryID` int(11) unsigned NOT NULL,
`fileName` text COLLATE utf8_unicode_ci NOT NULL,
`url` text COLLATE utf8_unicode_ci NOT NULL,
`downloadCount` int(10) unsigned NOT NULL,
PRIMARY KEY (`fileID`),
KEY `subjectID` (`subjectID`),
KEY `categoryID` (`categoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=166 ;
-- Create user table
CREATE TABLE IF NOT EXISTS `user` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`display_name` varchar(50) DEFAULT NULL,
`password` varchar(128) NOT NULL,
`state` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;