[server] On mah way (lots of restructuring, some early db classes, sql dump of current schema)

1 files changed, 318 insertions, 0 deletions

diff --git a/dozentenmodulserver/setup/sat-01-schema.sql b/dozentenmodulserver/setup/sat-01-schema.sql
new file mode 100644
index 00000000..57e86e99
--- /dev/null
+++ b/dozentenmodulserver/setup/sat-01-schema.sql
@@ -0,0 +1,318 @@
+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 `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,
+  `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,
+  `tagid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
+  PRIMARY KEY (`softwareid`),
+  UNIQUE KEY `softwarestring_UNIQUE` (`softwarestring`),
+  KEY `index2` (`tagid`,`softwareid`)
+) 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,
+  PRIMARY KEY (`tagid`),
+  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 `software`
+  ADD CONSTRAINT `fk_software_1` FOREIGN KEY (`tagid`) REFERENCES `tag` (`tagid`) ON UPDATE CASCADE;
+
+ALTER TABLE `user`
+  ADD CONSTRAINT `fk_user_1` FOREIGN KEY (`organizationid`) REFERENCES `organization` (`organizationid`) 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 */;