[server] Simplified db schema a bit

1 files changed, 13 insertions, 21 deletions

diff --git a/dozentenmodulserver/setup/sat-01-schema.sql b/dozentenmodulserver/setup/sat-01-schema.sql
index bb4a4e5c..d671b104 100644
--- a/dozentenmodulserver/setup/sat-01-schema.sql
+++ b/dozentenmodulserver/setup/sat-01-schema.sql
@@ -73,11 +73,11 @@ CREATE TABLE IF NOT EXISTS `imagebase` (
   KEY `latestversion_idx` (`latestversionid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
-CREATE TABLE IF NOT EXISTS `imagebase_x_tag` (
+CREATE TABLE IF NOT EXISTS `imagetag` (
   `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`)
+  `tagname` CHAR(32) NOT NULL,
+  PRIMARY KEY (`imagebaseid`,`tagname`),
+  KEY `tag_image` (`tagname`,`imagebaseid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 CREATE TABLE IF NOT EXISTS `imageblock` (
@@ -165,9 +165,9 @@ CREATE TABLE IF NOT EXISTS `imageversion_x_software` (
 
 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`)
+  `tagname` CHAR(32) NOT NULL,
+  PRIMARY KEY (`softwareid`,`tagname`),
+  KEY `fk_software_x_tag_2_idx` (`tagname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 CREATE TABLE IF NOT EXISTS `lecture` (
@@ -231,20 +231,14 @@ CREATE TABLE IF NOT EXISTS `os_x_virt` (
 ) 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.',
+  `softwareid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'This ID is used internally only, it never leaves the satellite.',
   `softwarestring` varchar(120) NOT NULL,
+  `isrestricted` TINYINT(1) NOT NULL,
+  `isrestrictedoverride` TINYINT(1) 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,
@@ -281,9 +275,8 @@ ALTER TABLE `imagebase`
   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 ON DELETE CASCADE,
-  ADD CONSTRAINT `fk_imagebase_x_tag_2` FOREIGN KEY (`tagid`) REFERENCES `tag` (`tagid`) ON UPDATE CASCADE ON DELETE CASCADE;
+ALTER TABLE `imagetag`
+  ADD CONSTRAINT `fk_imagetag_1` FOREIGN KEY (`imagebaseid`) REFERENCES `imagebase` (`imagebaseid`) ON UPDATE CASCADE ON DELETE CASCADE;
 
 ALTER TABLE `imageblock`
   ADD CONSTRAINT `fk_imageblocksha1_1` FOREIGN KEY (`imageversionid`) REFERENCES `imageversion` (`imageversionid`) ON UPDATE CASCADE ON DELETE CASCADE;
@@ -317,8 +310,7 @@ 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 ON DELETE CASCADE,
-  ADD CONSTRAINT `fk_software_x_tag_2` FOREIGN KEY (`tagid`) REFERENCES `tag` (`tagid`) ON UPDATE CASCADE ON DELETE CASCADE;
+  ADD CONSTRAINT `fk_software_x_tag_1` FOREIGN KEY (`softwareid`) REFERENCES `software` (`softwareid`) ON UPDATE CASCADE ON DELETE CASCADE;
 
 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;