From 9085dcdcb35ae1f9e3a592c8cd5dfecdd4e9bde1 Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Tue, 16 Jun 2015 18:22:54 +0200 Subject: [server] Add script field to lecture table; implement getImageDetails method to get detailed information about an image from the database --- dozentenmodulserver/setup/sat-01-schema.sql | 107 +++++++++++++++------------- 1 file changed, 57 insertions(+), 50 deletions(-) (limited to 'dozentenmodulserver/setup') 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 */; -- cgit v1.2.3-55-g7522