base.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


-- --------------------------------------------------------

--
-- Структура таблицы `accounts`
--

CREATE TABLE IF NOT EXISTS `accounts` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Login` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `Password` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `EMail` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `UserRoleID` int(11) NOT NULL,
  `ActivationCode` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  `IsEnabled` tinyint(1) NOT NULL DEFAULT '1',
  `UserAgent` text,
  `Notification` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Login` (`Login`),
  UNIQUE KEY `EMail` (`EMail`),
  KEY `UserRoleID` (`UserRoleID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


-- --------------------------------------------------------

--
-- Структура таблицы `job_positions`
--

CREATE TABLE IF NOT EXISTS `job_positions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `departments`
--

CREATE TABLE IF NOT EXISTS `departments` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) CHARACTER SET utf8 NULL,
  `FacultyID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`,`FacultyID`),
  KEY `FacultyID` (`FacultyID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `compound_disciplines`
--

-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `compound_disciplines` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) CHARACTER SET utf8 NULL DEFAULT 'Курс по выбору',
  `GradeID` int(11) NOT NULL,
  `SpecializationID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `disciplines`
--

-- --------------------------------------------------------


CREATE TABLE IF NOT EXISTS `disciplines` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `GradeID` int(11) NOT NULL,
  `SubjectID` int(11) NOT NULL,
  `AuthorID` int(11) NOT NULL,
  `ExamType` enum('exam','credit', 'grading_credit') NOT NULL,
  `SemesterID` int(11) NOT NULL,
  `PracticeCount` int(11) NOT NULL DEFAULT '0',
  `LectureCount` int(11) NOT NULL DEFAULT '0',
  `LabCount` int(11) NOT NULL DEFAULT '0',
  `FacultyID` int(11) NOT NULL,
  -- IsLocked (for editing structure)
  `IsLocked`  tinyint(1) NOT NULL DEFAULT '0',
  -- Milestone semester time (or is session time)
  `Milestone`  INT(1) NOT NULL DEFAULT '0',
  `MilestoneDate` DATE NULL DEFAULT NULL,
  `Subtype` enum('scientific_coursework', 'disciplinary_coursework' ) NULL DEFAULT NULL,
  `CompoundDiscID` INT(11) NULL DEFAULT NULL,

  `MaxRate` INT(11) NOT NULL DEFAULT 0,
  `CurRate` INT(11) NOT NULL DEFAULT 0,

  PRIMARY KEY (`ID`),
  KEY `GradeID`   (`GradeID`),
  KEY `SubjectID` (`SubjectID`),
  KEY `TeacherID` (`AuthorID`),
  KEY `SemesterID` (`SemesterID`),
  KEY `FacultyID` (`FacultyID`),
  KEY `CompoundDiscID` (`CompoundDiscID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `disciplines_groups`
--

CREATE TABLE IF NOT EXISTS `disciplines_groups` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DisciplineID` int(11) NOT NULL,
  `GroupID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `DisciplineID_2` (`DisciplineID`,`GroupID`),
  KEY `DisciplineID` (`DisciplineID`),
  KEY `GroupID` (`GroupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `disciplines_students`
--

CREATE TABLE IF NOT EXISTS `disciplines_students` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DisciplineID` int(11) NOT NULL,
  `StudentID` int(11) NOT NULL,
  `Type` enum('attach','detach') NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `DisciplineID_2` (`DisciplineID`,`StudentID`),
  KEY `DisciplineID` (`DisciplineID`),
  KEY `StudentID` (`StudentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `disciplines_teachers`
--

CREATE TABLE IF NOT EXISTS `disciplines_teachers` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DisciplineID` int(11) NOT NULL,
  `TeacherID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `DisciplineID_2` (`DisciplineID`,`TeacherID`),
  KEY `DisciplineID` (`DisciplineID`),
  KEY `AccountID` (`TeacherID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `faculties`
--

CREATE TABLE IF NOT EXISTS `faculties` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) CHARACTER SET utf8 NOT NULL,
  `Abbr` varchar(20) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;




-- --------------------------------------------------------

--
-- Структура таблицы `modules`
--

CREATE TABLE IF NOT EXISTS `modules` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) CHARACTER SET utf8 NOT NULL,
  `OrderNum` int(11) NOT NULL,
  `DisciplineID` int(11) NOT NULL,
  `Type` enum('regular','exam', 'bonus', 'extra') NOT NULL DEFAULT 'regular',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `OrderNum` (`OrderNum`,`DisciplineID`),
  KEY `DisciplineID` (`DisciplineID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



-- --------------------------------------------------------

--
-- Структура таблицы `rating_table`
--

CREATE TABLE IF NOT EXISTS `rating_table` (
  `StudentID` int(11) NOT NULL,
  `TeacherID` int(11) NOT NULL,
  `SubmoduleID` int(11) NOT NULL,
  `Rate` int(11) NOT NULL,
  `Date` date NOT NULL,
  PRIMARY KEY (`StudentID`,`SubmoduleID`),
  KEY `StudentID` (`StudentID`),
  KEY `SubmoduleID` (`SubmoduleID`),
  KEY `TeacherID` (`TeacherID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- CHANGE:
-- --------------------------------------------------------

--
-- Структура таблицы `requests`
--

CREATE TABLE IF NOT EXISTS `requests` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `AccountID` int(11) DEFAULT NULL,
  `Title` varchar(50) CHARACTER SET utf8 NULL DEFAULT NULL,
  `Description` text CHARACTER SET utf8 NULL DEFAULT NULL,
  `Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Status` enum('opened','processed','closed') NOT NULL DEFAULT 'opened',
  `HasImage` BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (`ID`),
  KEY `AccountID` (`AccountID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- --------------------------------------------------------

--
-- Структура таблицы `semesters`
--

CREATE TABLE IF NOT EXISTS `semesters` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Year` int(11) NOT NULL,
  `Num` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Year` (`Year`,`Num`),
  KEY `Year_2` (`Year`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


-- --------------------------------------------------------

--
-- Структура таблицы `years`
--
CREATE TABLE IF NOT EXISTS `years` (
  `Num` int(11) NOT NULL,
  PRIMARY KEY (`Num`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



-- --------------------------------------------------------

--
-- Структура таблицы `specializations`
--

CREATE TABLE IF NOT EXISTS `specializations` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) CHARACTER SET utf8 NULL,
  `Abbr` varchar(20) CHARACTER SET utf8 NULL,
  `Code` varchar(12) CHARACTER SET utf8 NULL,
  `FacultyID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`,`FacultyID`),
  KEY `FacultyID` (`FacultyID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `students`
--

CREATE TABLE IF NOT EXISTS `students` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `AccountID` int(11) NOT NULL,
  `LastName` varchar(30) CHARACTER SET utf8 NOT NULL,
  `FirstName` varchar(30) CHARACTER SET utf8 NOT NULL,
  `SecondName` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `AccountID` (`AccountID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `study_groups`
--

CREATE TABLE IF NOT EXISTS `study_groups` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `GradeID` int(11) NOT NULL,
  `GroupNum` int(11) NOT NULL,
  `FacultyID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `FacultyGroup`(`FacultyID`,`GradeID`,`GroupNum`),
  KEY `GradeID` (`GradeID`),
  KEY `FacultyID` (`FacultyID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


-- --------------------------------------------------------

--
-- Структура таблицы `groups_years`
--

CREATE TABLE IF NOT EXISTS `groups_years` (
  `GroupID` int(11) NOT NULL,
  `Year` int(11) NOT NULL,
  `SpecializationID` int(11) NOT NULL,
  `Name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`Year`, `GroupID`),
  KEY `GroupID` (`GroupID`),
  KEY `Year` (`Year`),
  KEY `SpecializationID` (`SpecializationID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `subjects`
--

CREATE TABLE IF NOT EXISTS `subjects` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) CHARACTER SET utf8 NOT NULL,
  `Abbr` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


-- --------------------------------------------------------

--
-- Структура таблицы `subjects`
--

CREATE TABLE IF NOT EXISTS `subjects_faculties` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `SubjectID` int(11) NOT NULL,
  `FacultyID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `SubjectID_2` (`SubjectID`,`FacultyID`),
  KEY `SubjectID` (`SubjectID`),
  KEY `FacultyID` (`FacultyID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `submodules`
--

CREATE TABLE IF NOT EXISTS `submodules` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ModuleID` int(11) NOT NULL,
  `MaxRate` int(11) NOT NULL,
  `OrderNum` int(11) NOT NULL,
  `Name` varchar(200) NOT NULL,
  `Description` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `IsUsed` tinyint(1) NOT NULL DEFAULT 0,
  `Type` enum('CurrentControl','LandmarkControl') NOT NULL DEFAULT 'CurrentControl',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ModuleID_2` (`ModuleID`,`OrderNum`),
  KEY `ModuleID` (`ModuleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- --------------------------------------------------------

--
-- Структура таблицы `grades`
--

CREATE TABLE IF NOT EXISTS `grades` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Num` int(11) NOT NULL,
  `Degree` enum('bachelor','master','specialist') NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Grade_2` (`Num`,`Degree`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- --------------------------------------------------------

--
-- Структура таблицы `teachers`
--

CREATE TABLE IF NOT EXISTS `teachers` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `LastName` varchar(30) CHARACTER SET utf8 NOT NULL,
  `FirstName` varchar(30) CHARACTER SET utf8 NOT NULL,
  `SecondName` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  `JobPositionID` int(11) NOT NULL,
  `DepartmentID` int(11) NULL,
  `AccountID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `FacultyID` (`DepartmentID`),
  KEY `AccountID` (`AccountID`),
  KEY `JobPositionID` (`JobPositionID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Структура таблицы `user_roles`
--

CREATE TABLE IF NOT EXISTS `user_roles` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Type` enum('student','teacher') NOT NULL,
  `RoleName` varchar(30) CHARACTER SET utf8 NOT NULL,
  `Mark` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


-- --------------------------------------------------------

--
-- Структура таблицы `recovery_tokens `
--

CREATE TABLE IF NOT EXISTS `recovery_tokens` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `AccountID` int(11) NOT NULL,
  `Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Token` varchar(100) CHARACTER SET utf8 NOT NULL,
  `IsUsed`  tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `AccountID` (`AccountID`),
  UNIQUE KEY `Token` (`Token`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



-- --------------------------------------------------------

--
-- Структура таблицы `students_groups`
--
CREATE TABLE IF NOT EXISTS `students_groups` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `StudentID` int(11) NOT NULL,
  `GroupID` int(11) NOT NULL,
  `SemesterID` int(11) NOT NULL,
  `State` enum('common', 'outlet', 'expulsion', 'leave') NOT NULL DEFAULT 'common',
  `Date` date NULL DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `StudentID_2` (`StudentID`, `SemesterID`),
  KEY `SemesterID` (`SemesterID`),
  KEY `StudentID` (`StudentID`),
  KEY `GroupID` (`GroupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- --------------------------------------------------------

--
-- Структура таблицы `exam_period_options`
--

CREATE TABLE IF NOT EXISTS `exam_period_options` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `SubmoduleID` int(11) NOT NULL,
  `StudentID` int(11) NOT NULL,
  `Type` enum('absence','pass'),
  PRIMARY KEY (`ID`),
  UNIQUE KEY `SubmoduleID` (`SubmoduleID`,`StudentID`),
  KEY `SubmoduleID_2` (`SubmoduleID`),
  KEY `StudentID` (`StudentID`),
  KEY `StudentID_2` (`StudentID`),
  KEY `StudentID_3` (`StudentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


-- --------------------------------------------------------

--
-- Структура таблицы `auth_tokens`
--

CREATE TABLE IF NOT EXISTS `auth_tokens` (
  `Token` char(40) charset ascii NOT NULL,
  `AccountID` int(11) NOT NULL,
  `Created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Accessed` TIMESTAMP NOT NULL DEFAULT 0,
  `Mask` int(11) NOT NULL DEFAULT 0,
  UNIQUE KEY `Token` (`Token`),
  KEY `AccountID` (`AccountID`)
);