path: root/dozentenmodulserver/setup/sat-01-schema.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 */;

CREATE DATABASE IF NOT EXISTS `sat` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `sat`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `setversionbase`(IN baseid CHAR(36) CHARACTER SET ascii)
BEGIN
	-- Variables
	DECLARE l_current, l_latest VARCHAR(36) CHARACTER SET ascii;
	DECLARE done INT DEFAULT FALSE;
	-- Our two cursors
	DECLARE cur_current CURSOR FOR
		SELECT imageversionid FROM imageversion
		WHERE imagebaseid = baseid AND isenabled = 1 AND isvalid = 1
		ORDER BY createtime DESC LIMIT 1;
	DECLARE cur_latest CURSOR FOR
		SELECT imageversionid FROM imageversion
		WHERE imagebaseid = baseid
		ORDER BY createtime DESC LIMIT 1;
	-- Handler
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	-- Get the current version id
	OPEN cur_current;
	cur_loop: LOOP
		FETCH FROM cur_current INTO l_current;
		LEAVE cur_loop;
	END LOOP;
	CLOSE cur_current;
	-- Get the latest version id
	OPEN cur_latest;
	lat_loop: LOOP
		FETCH FROM cur_latest INTO l_latest;
		LEAVE lat_loop;
	END LOOP;
	CLOSE cur_latest;
	-- Update image table
	UPDATE imagebase SET currentversionid = l_current, latestversionid = l_latest WHERE imagebaseid = baseid LIMIT 1;
END$$

DELIMITER ;

CREATE TABLE IF NOT EXISTS `imagebase` (
  `imagebaseid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `currentversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL COMMENT 'Reference to current version of this image. This is redundant as it can be determined from the imageversion table, but the query to get all images with their current version would be very slow then, which is not what we want, as it is fired quite often.',
  `latestversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `displayname` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `osid` int(11) NOT NULL,
  `virtid` varchar(10) NOT NULL,
  `createtime` bigint(20) NOT NULL,
  `updatetime` bigint(20) NOT NULL,
  `ownerid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `updaterid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `sharemode` enum('LOCAL','PUBLISH','DOWNLOAD') NOT NULL,
  `istemplate` tinyint(1) NOT NULL,
  `canlinkdefault` tinyint(1) NOT NULL,
  `candownloaddefault` tinyint(1) NOT NULL,
  `caneditdefault` tinyint(1) NOT NULL,
  `canadmindefault` tinyint(1) NOT NULL,
  PRIMARY KEY (`imagebaseid`),
  KEY `owner` (`ownerid`),
  KEY `fk_imagebase_1_idx` (`osid`),
  KEY `fk_imagebase_owner_idx` (`updaterid`),
  KEY `fk_imagebase_1_idx1` (`virtid`),
  KEY `currentversion_idx` (`currentversionid`),
  KEY `latestversion_idx` (`latestversionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `imagebase_x_tag` (
  `imagebaseid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `tagid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  PRIMARY KEY (`imagebaseid`,`tagid`),
  KEY `tag_image` (`tagid`,`imagebaseid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `imageblock` (
  `imageversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `startbyte` bigint(20) NOT NULL,
  `blocksize` int(11) NOT NULL,
  `blocksha1` binary(20) DEFAULT NULL,
  `ismissing` tinyint(1) NOT NULL COMMENT 'true if this block is missing from the file, either because it was not transferred to the server yet, or because it failed an integrity check.',
  PRIMARY KEY (`imageversionid`,`startbyte`,`blocksize`),
  KEY `checksums` (`blocksha1`,`blocksize`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `imagepermission` (
  `imagebaseid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `canlink` tinyint(1) NOT NULL,
  `candownload` tinyint(1) NOT NULL,
  `canedit` tinyint(1) NOT NULL,
  `canadmin` tinyint(1) NOT NULL,
  PRIMARY KEY (`imagebaseid`,`userid`),
  KEY `fk_imagepermission_2_idx` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `imageversion` (
  `imageversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `imagebaseid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `createtime` bigint(20) NOT NULL,
  `expiretime` bigint(20) NOT NULL,
  `filesize` bigint(20) NOT NULL,
  `filepath` varchar(200) NOT NULL,
  `uploaderid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `isenabled` tinyint(1) NOT NULL,
  `isrestricted` tinyint(1) NOT NULL,
  `isvalid` tinyint(1) NOT NULL,
  `isprocessed` tinyint(1) NOT NULL,
  `mastersha1` binary(20) DEFAULT NULL,
  `virtualizerconfig` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT 'Specific configuration of the virtualizer for this image. For vmware, this is basically a dump of the *.vmx.',
  PRIMARY KEY (`imageversionid`),
  KEY `version_access` (`imagebaseid`,`isenabled`,`isvalid`,`createtime`),
  KEY `fk_imageversion_2_idx` (`uploaderid`),
  KEY `expire_index` (`expiretime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TRIGGER IF EXISTS `version_delete_post`;
DELIMITER //
CREATE TRIGGER `version_delete_post` AFTER DELETE ON `imageversion`
 FOR EACH ROW BEGIN
	CALL setversionbase(OLD.imagebaseid);
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `version_delete_pre`;
DELIMITER //
CREATE TRIGGER `version_delete_pre` BEFORE DELETE ON `imageversion`
 FOR EACH ROW BEGIN
	UPDATE imagebase SET currentversionid = NULL WHERE currentversionid = OLD.imageversionid;
	UPDATE imagebase SET latestversionid = NULL WHERE latestversionid = OLD.imageversionid;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `version_insert`;
DELIMITER //
CREATE TRIGGER `version_insert` AFTER INSERT ON `imageversion`
 FOR EACH ROW BEGIN
	CALL setversionbase(NEW.imagebaseid);
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `version_update`;
DELIMITER //
CREATE TRIGGER `version_update` AFTER UPDATE ON `imageversion`
 FOR EACH ROW BEGIN
	IF NEW.isenabled <> OLD.isenabled OR NEW.isvalid <> OLD.isvalid THEN
		CALL setversionbase(NEW.imagebaseid);
	END IF;
END
//
DELIMITER ;

CREATE TABLE IF NOT EXISTS `imageversion_x_software` (
  `imageversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `softwareid` int(11) NOT NULL,
  PRIMARY KEY (`imageversionid`,`softwareid`),
  KEY `fk_imageversion_x_software_2_idx` (`softwareid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `software_x_tag` (
  `softwareid` int(11) NOT NULL,
  `tagid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  PRIMARY KEY (`softwareid`,`tagid`),
  KEY `fk_software_x_tag_2_idx` (`tagid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lecture` (
  `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `displayname` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `imageversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT 'We reference a specific image version here, not the base image.\nOn update of an image, we update the lecture table for all matching lectures that used the current image version.\nThis way, a tutor can explicitly switch back to an older version of an image.',
  `isenabled` tinyint(1) NOT NULL,
  `starttime` bigint(20) NOT NULL,
  `endtime` bigint(20) NOT NULL,
  `lastused` bigint(20) NOT NULL DEFAULT '0',
  `usecount` int(11) NOT NULL DEFAULT '0',
  `createtime` bigint(20) NOT NULL,
  `updatetime` bigint(20) NOT NULL,
  `ownerid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `updaterid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `runscript` text DEFAULT NULL,
  `isexam` tinyint(1) NOT NULL,
  `hasinternetaccess` tinyint(1) NOT NULL,
  `caneditdefault` tinyint(1) NOT NULL,
  `canadmindefault` tinyint(1) NOT NULL,
  PRIMARY KEY (`lectureid`),
  KEY `fk_lecture_1_idx` (`imageversionid`),
  KEY `fk_lecture_2_idx` (`ownerid`),
  KEY `fk_lecture_3_idx` (`updaterid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lecturenetrule` (
  `ruleid` int(11) NOT NULL AUTO_INCREMENT,
  `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `direction` enum('IN','OUT') NOT NULL,
  `host` varchar(45) NOT NULL,
  PRIMARY KEY (`ruleid`),
  KEY `fk_lecturenetrule_1_idx` (`lectureid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `lecturepermission` (
  `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `canedit` tinyint(1) NOT NULL,
  `canadmin` tinyint(1) NOT NULL,
  PRIMARY KEY (`lectureid`,`userid`),
  KEY `fk_lecturepermission_2_idx` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `operatingsystem` (
  `osid` int(11) NOT NULL COMMENT 'Defined on the master server, so no auto_increment!',
  `displayname` varchar(100) NOT NULL,
  `architecture` varchar(14) NOT NULL,
  PRIMARY KEY (`osid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `organization` (
  `organizationid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `displayname` varchar(255) NOT NULL,
  `canlogin` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`organizationid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `os_x_virt` (
  `osid` int(11) NOT NULL,
  `virtid` varchar(10) NOT NULL,
  `virtoskeyword` varchar(30) NOT NULL,
  PRIMARY KEY (`osid`,`virtid`),
  KEY `fk_os_x_virt_2_idx` (`virtid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `software` (
  `softwareid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'This ID is used internally only, this never leaves the satellite.',
  `softwarestring` varchar(120) NOT NULL,
  PRIMARY KEY (`softwareid`),
  UNIQUE KEY `softwarestring_UNIQUE` (`softwarestring`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tag` (
  `tagid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `displayname` varchar(32) NOT NULL,
  `isrestricting` tinyint(1) NOT NULL COMMENT 'True if there is no Landeslizenz for this software, meaning it should not be downloadable by students.',
  PRIMARY KEY (`tagid`),
  UNIQUE KEY `tagindex` (`displayname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user` (
  `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `organizationid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `lastlogin` bigint(20) DEFAULT NULL,
  `canlogin` tinyint(1) NOT NULL DEFAULT '0',
  `issuperuser` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`userid`),
  KEY `fk_user_1_idx` (`organizationid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `virtualizer` (
  `virtid` varchar(10) NOT NULL,
  `virtname` varchar(20) NOT NULL,
  PRIMARY KEY (`virtid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE `imagebase`
  ADD CONSTRAINT `fk_imagebase_os` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`),
  ADD CONSTRAINT `fk_imagebase_owner` FOREIGN KEY (`ownerid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_imagebase_updater` FOREIGN KEY (`updaterid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_imagebase_1` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `currentversion` FOREIGN KEY (`currentversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `latestversion` FOREIGN KEY (`latestversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE;

ALTER TABLE `imagebase_x_tag`
  ADD CONSTRAINT `fk_imagebase_x_tag_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_imagebase_x_tag_2` FOREIGN KEY (`tagid`) REFERENCES `tag` (`tagid`) ON UPDATE CASCADE;

ALTER TABLE `imageblock`
  ADD CONSTRAINT `fk_imageblocksha1_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE;

ALTER TABLE `imagepermission`
  ADD CONSTRAINT `fk_imagepermission_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_imagepermission_2` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE;

ALTER TABLE `imageversion`
  ADD CONSTRAINT `fk_imageversion_base` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_imageversion_creator` FOREIGN KEY (`uploaderid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE;

ALTER TABLE `imageversion_x_software`
  ADD CONSTRAINT `fk_imageversion_x_software_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_imageversion_x_software_2` FOREIGN KEY (`softwareid`) REFERENCES `software` (`softwareid`) ON UPDATE CASCADE;

ALTER TABLE `lecture`
  ADD CONSTRAINT `fk_lecture_image` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_lecture_owner` FOREIGN KEY (`ownerid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_lecture_updater` FOREIGN KEY (`updaterid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE;

ALTER TABLE `lecturenetrule`
  ADD CONSTRAINT `fk_lecturenetrule_1` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON UPDATE CASCADE;

ALTER TABLE `lecturepermission`
  ADD CONSTRAINT `fk_lecturepermission_1` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_lecturepermission_2` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE;

ALTER TABLE `os_x_virt`
  ADD CONSTRAINT `fk_os_x_virt_1` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`),
  ADD CONSTRAINT `fk_os_x_virt_2` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`);

ALTER TABLE `user`
  ADD CONSTRAINT `fk_user_1` FOREIGN KEY (`organizationid`) REFERENCES `organization` (`organizationid`) ON UPDATE CASCADE;

ALTER TABLE `software_x_tag`
  ADD CONSTRAINT `fk_software_x_tag_1` FOREIGN KEY (`softwareid`) REFERENCES `software` (`softwareid`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_software_x_tag_2` FOREIGN KEY (`tagid`) REFERENCES `tag` (`tagid`) ON UPDATE CASCADE;

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