diff options
Diffstat (limited to 'dozentenmodulserver')
3 files changed, 18 insertions, 24 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 */; diff --git a/dozentenmodulserver/setup/sat-01-testdata.sql b/dozentenmodulserver/setup/sat-01-testdata.sql index 3aac5b97..a6819dc5 100644 --- a/dozentenmodulserver/setup/sat-01-testdata.sql +++ b/dozentenmodulserver/setup/sat-01-testdata.sql @@ -9,11 +9,14 @@ SET time_zone = "+00:00"; USE `sat`; INSERT INTO `operatingsystem` (`osid`, `displayname`, `architecture`) VALUES -(1, 'WustOS', 'x86'); +(1,'Windows 7 (64 Bit)','AMD64'),(2,'Windows 8 (32 Bit)','x86'),(3,'Windows 8 (64 Bit)','AMD64'),(4,'Ubuntu (32 Bit)','x86'),(5,'Ubuntu (64 Bit)','AMD64'),(6,'OpenSUSE (32 Bit)','x86'),(7,'OpenSUSE (64 Bit)','AMD64'),(8,'Other Linux (32 Bit)','x86'),(9,'Other Linux (64 Bit)','AMD64'),(10,'Windows 7 (32 Bit)','x86'); INSERT INTO `virtualizer` (`virtid`, `virtname`) VALUES ('vmware', 'VMware'); +INSERT INTO `os_x_virt` VALUES +(6,'vmware','opensuse'),(7,'vmware','opensuse-64'),(8,'vmware','other3xlinux'),(9,'vmware','other3xlinux-64'),(4,'vmware','ubuntu'),(5,'vmware','ubuntu-64'),(10,'vmware','windows7'),(1,'vmware','windows7-64'),(2,'vmware','windows8'),(3,'vmware','windows8-64'); + INSERT INTO `organization` (`organizationid`, `displayname`, `canlogin`) VALUES ('blblogin.blb-karlsruhe.de', 'Badische Landesbibliothek', 0), ('bsz-bw.de', 'Bibliotheksservice-Zentrum Baden-Württemberg', 0), diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbSoftwareTag.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbSoftwareTag.java index 6cabd021..44e50090 100644 --- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbSoftwareTag.java +++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbSoftwareTag.java @@ -54,8 +54,7 @@ public class DbSoftwareTag { } public static List<String> getImageTags(MysqlConnection connection, String imageBaseId) throws SQLException { - MysqlStatement stmt = connection.prepareStatement("SELECT displayname FROM tag" - + " INNER JOIN imagebase_x_tag USING (tagid)" + MysqlStatement stmt = connection.prepareStatement("SELECT tagname FROM imagetag" + " WHERE imagebaseid = :imagebaseid"); stmt.setString("imagebaseid", imageBaseId); ResultSet rs = stmt.executeQuery(); |