119 lines
5.2 KiB
SQL
119 lines
5.2 KiB
SQL
/**
|
|
* /config/setup.sql
|
|
* @version 1.3
|
|
* @desc sql structure file
|
|
* @author Fándly Gergő Zoltán (fandlygergo@gmail.hu, systemtest.tk)
|
|
* @copy 2017 Fándly Gergő Zoltán
|
|
* License:
|
|
Result Manager for managing results of students in bilingual school systems.
|
|
Copyright (C) 2017 Fándly Gergő Zoltán
|
|
|
|
This program is free software: you can redistribute it and/or modify
|
|
it under the terms of the GNU General Public License as published by
|
|
the Free Software Foundation, either version 3 of the License, or
|
|
(at your option) any later version.
|
|
|
|
This program is distributed in the hope that it will be useful,
|
|
but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
GNU General Public License for more details.
|
|
|
|
You should have received a copy of the GNU General Public License
|
|
along with this program. If not, see <https://www.gnu.org/licenses/>.
|
|
**/
|
|
|
|
CREATE TABLE `users`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`username` varchar(65) NOT NULL default '',
|
|
`fullname` varchar(65) NOT NULL default '',
|
|
`accesslevel` tinyint(1) UNSIGNED NOT NULL default 0, /* 0:student, 1:teacher; 2:head teacher; 3:manager; 4:admin */
|
|
`class` varchar(10) NOT NULL default '', /* format: {G,L}{Year when school started}[AF] */
|
|
`password` varchar(255) NOT NULL default '',
|
|
`perm_message` tinyint(1) NOT NULL default 1, /* 0:don't allow messaging; 1:allow messaging */
|
|
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,
|
|
`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_remember`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`user` int(4) UNSIGNED NOT NULL default 0,
|
|
`remember_token` varchar(65) NOT NULL default '',
|
|
`until` timestamp NOT NULL default current_timestamp,
|
|
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 `subjects`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`name_1` varchar(65) NOT NULL default '',
|
|
`name_2` varchar(65) NOT NULL default '',
|
|
PRIMARY KEY (`id`)
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `contests`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`name_1` varchar(65) NOT NULL default '',
|
|
`name_2` varchar(65) NOT NULL default '',
|
|
`subject` int(4) UNSIGNED NOT NULl,
|
|
`description` text NOT NULL default '',
|
|
`ministry_support` tinyint(1) UNSIGNED NOT NULL default 0, /* 0:not listed; 1:not supported; 2:supported */
|
|
`ministry_place` int(4) UNSIGNED NOT NULL default 0, /* place on the list of contests */
|
|
`schoolyear` varchar(16) NOT NULL default '0000-0001',
|
|
PRIMARY KEY (`id`),
|
|
FOREIGN KEY (`subject`) REFERENCES subjects(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `phases`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`name_1` varchar(65) NOT NULl default '',
|
|
`name_2` varchar(65) NOT NULL default '',
|
|
PRIMARY KEY (`id`)
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `register`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`student` int(4) UNSIGNED NOT NULL default 1,
|
|
`contest` int(4) UNSIGNED NOT NULL,
|
|
`phase` int(4) UNSIGNED NOT NULL,
|
|
`teacher` int(4) UNSIGNED NOT NULL default 1,
|
|
`place` tinyint(1) NOT NULL default 0, /* -1:dicseret; -2:kulondij; -3:reszvetel */
|
|
`mention` text NOT NULL default '',
|
|
`schoolyear` varchar(16) NOT NULL default '0000-0001',
|
|
PRIMARY KEY (`id`),
|
|
FOREIGN KEY (`student`) REFERENCES users(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
FOREIGN KEY (`contest`) REFERENCES contests(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
FOREIGN KEY (`phase`) REFERENCES phases(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
FOREIGN KEY (`teacher`) REFERENCES users(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE `messages`(
|
|
`id` int(4) UNSIGNED NOT NULL auto_increment,
|
|
`sender` int(4) UNSIGNED NOT NULL default 1,
|
|
`recipient` int(4) UNSIGNED NOT NULL default 1,
|
|
`content` text NOT NULL default '',
|
|
`dismissed` tinyint(1) UNSIGNED NOT NULL default 0,
|
|
PRIMARY KEY (`id`),
|
|
FOREIGN KEY (`sender`) REFERENCES users(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
FOREIGN KEY (`recipient`) REFERENCES users(`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
) CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
INSERT INTO users (`id`, `username`) VALUES (1, 'nouser');
|