SignUp/config/db.sql

90 lines
4.2 KiB
MySQL
Raw Permalink Normal View History

2019-08-08 13:40:15 +00:00
/**
* /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');