90 lines
4.2 KiB
SQL
90 lines
4.2 KiB
SQL
/**
|
|
* /config/db.sql
|
|
* @version 1.0
|
|
* @desc SQL set up file
|
|
* @author Fándly Gergő Zoltán
|
|
* @copy 2017 Fándly Gergő Zoltán
|
|
*/
|
|
|
|
DROP TABLE IF EXISTS `users`, `login_history`, `login_bans`, `time_sequences`, `time_blocks`, `programs`, `registrations`, `registration_log`;
|
|
|
|
CREATE TABLE `users`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`name` varchar(65) NOT NULL default '',
|
|
`class` varchar(10) NOT NULL default '', /* format: ddC (ex: 05D) */
|
|
`accesslevel` tinyint(1) UNSIGNED NOT NULL default 0, /* 0:student; 1:head teacher; 2:manager; 3:administrator */
|
|
`password` varchar(255) NOT NULL default '',
|
|
`except_login` tinyint(1) UNSIGNED NOT NULL default 0, /* 0:no change; 1:always allow login; 2:never allow login - only takes effect for students */
|
|
`except_signup` tinyint(1) UNSIGNED NOT NULL default 0, /* 0:no change; 1:always allow sign up; 2:never allow sign up - only takes effect for students */
|
|
PRIMARY KEY (`id`)
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `login_history`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`user` int(4) UNSIGNED NOT NULL default 1, /* id of nouser */
|
|
`date` timestamp NOT NULL default current_timestamp,
|
|
`ip` varchar(45) NOT NULL default '0.0.0.0',
|
|
`auth_token` varchar(65) NOT NULL default '',
|
|
`user_agent` varchar(500) NOT NULL default '',
|
|
`success` tinyint(1) NOT NULL default 0,
|
|
PRIMARY KEY (`id`),
|
|
FOREIGN KEY (`user`) REFERENCES users(`id`) ON DELETE CASCADE
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `login_bans`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`ip` varchar(45) NOT NULL default '0.0.0.0',
|
|
`until` timestamp NOT NULL default current_timestamp,
|
|
PRIMARY KEY (`id`)
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `time_sequences`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`name` varchar(65) NOT NULL default '', /* ex: monday, tuesday, 1st week, etc */
|
|
`allow_signup` tinyint(1) UNSIGNED NOT NULL default 1, /* 0:forbid; 1:allow */
|
|
PRIMARY KEY (`id`)
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `time_blocks`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`name` varchar(65) NOT NULL default '', /* ex: 9-10, 8:00, etc */
|
|
`sequence` int(4) UNSIGNED NOT NULL default 0,
|
|
PRIMARY KEY (`id`),
|
|
FOREIGN KEY (`sequence`) REFERENCES time_sequences(`id`) ON DELETE CASCADE
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `programs`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`name` varchar(65) NOT NULL default '',
|
|
`description` text NOT NULL default '', /* as long, as wished! */
|
|
`instructor` varchar(150) NOT NULL default '',
|
|
`location` varchar(150) NOT NULL default '',
|
|
`category` tinyint(1) UNSIGNED NOT NULL default 0, /* 0:0th class; 1:1-2th class; 2:3-4th class; 3:5-6th class; 4:7-8th class; 5:9-10th class 6:11-12th class; 10:0-4th class; 11:5-8th class; 12:9-12th class; 20:0-12th class */
|
|
`time_block` int(4) UNSIGNED NOT NULL default 0,
|
|
`max_participants` int(4) UNSIGNED NOT NULL default 0,
|
|
PRIMARY KEY (`id`),
|
|
FOREIGN KEY (`time_block`) REFERENCES time_blocks(`id`) ON DELETE NO ACTION
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `registrations`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`user` int(4) UNSIGNED NOT NULL default 0,
|
|
`program` int(4) UNSIGNED NOT NULL default 0,
|
|
PRIMARY KEY (`id`),
|
|
FOREIGN KEY (`user`) REFERENCES users(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`program`) REFERENCES programs(`id`) ON DELETE CASCADE
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `registration_log`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`user` int(4) UNSIGNED NOT NULL default 0,
|
|
`date` timestamp NOT NULL default current_timestamp,
|
|
`action` tinyint(1) UNSIGNED NOT NULL default 0, /* 0:unsibscribe; 1:subscribe; 10:admin deleted; 11:admin added */
|
|
`program` int(4) UNSIGNED NOT NULL default 0,
|
|
PRIMARY KEY (`id`),
|
|
FOREIGN KEY (`user`) REFERENCES users(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`program`) REFERENCES programs(`id`) ON DELETE CASCADE
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
INSERT INTO users (`id`, `name`) VALUES (1, 'nouser');
|