[server] Add script field to lecture table; implement getImageDetails method to get detailed information about an image from the database

1 files changed, 57 insertions, 50 deletions

diff --git a/dozentenmodulserver/setup/sat-01-schema.sql b/dozentenmodulserver/setup/sat-01-schema.sql
index 57e86e99..0c52f68b 100644
--- a/dozentenmodulserver/setup/sat-01-schema.sql
+++ b/dozentenmodulserver/setup/sat-01-schema.sql
@@ -11,37 +11,37 @@ 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;

+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 ;
@@ -123,35 +123,35 @@ CREATE TABLE IF NOT EXISTS `imageversion` (
 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);

+ 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;

+ 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);

+ 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;

+ FOR EACH ROW BEGIN
+	IF NEW.isenabled <> OLD.isenabled OR NEW.isvalid <> OLD.isvalid THEN
+		CALL setversionbase(NEW.imagebaseid);
+	END IF;
 END
 //
 DELIMITER ;
@@ -163,6 +163,13 @@ CREATE TABLE IF NOT EXISTS `imageversion_x_software` (
   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,
@@ -177,6 +184,7 @@ CREATE TABLE IF NOT EXISTS `lecture` (
   `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,
@@ -230,18 +238,16 @@ CREATE TABLE IF NOT EXISTS `os_x_virt` (
 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`)
+  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,
+  `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`),
-  KEY `tagindex` (`displayname`)
+  UNIQUE KEY `tagindex` (`displayname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 CREATE TABLE IF NOT EXISTS `user` (
@@ -307,12 +313,13 @@ 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;
 
+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 */;