[server] Implemented getUsers call, more permission checking methods

1 files changed, 25 insertions, 15 deletions

diff --git a/dozentenmodulserver/setup/sat-01-schema.sql b/dozentenmodulserver/setup/sat-01-schema.sql
index 0c52f68b..1d800901 100644
--- a/dozentenmodulserver/setup/sat-01-schema.sql
+++ b/dozentenmodulserver/setup/sat-01-schema.sql
@@ -10,7 +10,7 @@ CREATE DATABASE IF NOT EXISTS `sat` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb
 USE `sat`;
 
 DELIMITER $$
-CREATE DEFINER=`root`@`localhost` PROCEDURE `setversionbase`(IN baseid CHAR(36) CHARACTER SET ascii)
+CREATE PROCEDURE `setversionbase`(IN baseid CHAR(36) CHARACTER SET ascii)
 BEGIN
 	-- Variables
 	DECLARE l_current, l_latest VARCHAR(36) CHARACTER SET ascii;
@@ -185,6 +185,7 @@ CREATE TABLE IF NOT EXISTS `lecture` (
   `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,
+  `nics` VARCHAR(200) CHARACTER SET ascii COLLATE ascii_bin NULL COMMENT 'Freeform text field for future extendability. Format is specified at application layer.',
   `isexam` tinyint(1) NOT NULL,
   `hasinternetaccess` tinyint(1) NOT NULL,
   `caneditdefault` tinyint(1) NOT NULL,
@@ -200,6 +201,7 @@ CREATE TABLE IF NOT EXISTS `lecturenetrule` (
   `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
   `direction` enum('IN','OUT') NOT NULL,
   `host` varchar(45) NOT NULL,
+  `port` INT NULL,
   PRIMARY KEY (`ruleid`),
   KEY `fk_lecturenetrule_1_idx` (`lectureid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
@@ -269,6 +271,14 @@ CREATE TABLE IF NOT EXISTS `virtualizer` (
   PRIMARY KEY (`virtid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
+CREATE TABLE IF NOT EXISTS `lectureuser` (
+  `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
+  `userlogin` VARCHAR(45) NOT NULL,
+  PRIMARY KEY (`lectureid`, `userlogin`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+ALTER TABLE `lectureuser`
+  ADD CONSTRAINT `fk_lectureuser_1` FOREIGN KEY (`lectureid`) REFERENCES `sat`.`lecture` (`lectureid`) ON DELETE CASCADE ON UPDATE CASCADE;
 
 ALTER TABLE `imagebase`
   ADD CONSTRAINT `fk_imagebase_os` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`),
@@ -279,23 +289,23 @@ ALTER TABLE `imagebase`
   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;
+  ADD CONSTRAINT `fk_imagebase_x_tag_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON UPDATE CASCADE ON DELETE CASCADE,
+  ADD CONSTRAINT `fk_imagebase_x_tag_2` FOREIGN KEY (`tagid`) REFERENCES `tag` (`tagid`) ON UPDATE CASCADE ON DELETE CASCADE;
 
 ALTER TABLE `imageblock`
-  ADD CONSTRAINT `fk_imageblocksha1_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE;
+  ADD CONSTRAINT `fk_imageblocksha1_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE ON DELETE 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;
+  ADD CONSTRAINT `fk_imagepermission_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON UPDATE CASCADE ON DELETE CASCADE,
+  ADD CONSTRAINT `fk_imagepermission_2` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE ON DELETE 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;
+  ADD CONSTRAINT `fk_imageversion_x_software_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE ON DELETE CASCADE,
+  ADD CONSTRAINT `fk_imageversion_x_software_2` FOREIGN KEY (`softwareid`) REFERENCES `software` (`softwareid`) ON UPDATE CASCADE ON DELETE CASCADE;
 
 ALTER TABLE `lecture`
   ADD CONSTRAINT `fk_lecture_image` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE,
@@ -303,22 +313,22 @@ ALTER TABLE `lecture`
   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;
+  ADD CONSTRAINT `fk_lecturenetrule_1` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON UPDATE CASCADE ON DELETE 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;
+  ADD CONSTRAINT `fk_lecturepermission_1` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`) ON UPDATE CASCADE ON DELETE CASCADE,
+  ADD CONSTRAINT `fk_lecturepermission_2` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE ON DELETE 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`);
+  ADD CONSTRAINT `fk_os_x_virt_1` FOREIGN KEY (`osid`) REFERENCES `operatingsystem` (`osid`) ON DELETE CASCADE ON DELETE CASCADE,
+  ADD CONSTRAINT `fk_os_x_virt_2` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`) ON DELETE CASCADE ON DELETE 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;
+  ADD CONSTRAINT `fk_software_x_tag_1` FOREIGN KEY (`softwareid`) REFERENCES `software` (`softwareid`) ON UPDATE CASCADE ON DELETE CASCADE,
+  ADD CONSTRAINT `fk_software_x_tag_2` FOREIGN KEY (`tagid`) REFERENCES `tag` (`tagid`) ON UPDATE CASCADE ON DELETE CASCADE;
 
 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;