[*] Simplify DB scheme by removing version.isenabled and image.currentversionid

2 files changed, 6 insertions, 82 deletions

diff --git a/dozentenmodulserver/setup/sat-01-schema.sql b/dozentenmodulserver/setup/sat-01-schema.sql
index d671b104..2919ff8a 100644
--- a/dozentenmodulserver/setup/sat-01-schema.sql
+++ b/dozentenmodulserver/setup/sat-01-schema.sql
@@ -9,46 +9,8 @@ SET time_zone = "+00:00";
 CREATE DATABASE IF NOT EXISTS `sat` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 USE `sat`;
 
-DELIMITER $$
-CREATE 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;
-END$$
-
-DELIMITER ;
-
 CREATE TABLE IF NOT EXISTS `imagebase` (
   `imagebaseid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
-  `currentversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL COMMENT 'Reference to current version of this image. This is redundant as it can be determined from the imageversion table, but the query to get all images with their current version would be very slow then, which is not what we want, as it is fired quite often.',
   `latestversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
   `displayname` varchar(100) NOT NULL,
   `description` TEXT NULL DEFAULT NULL,
@@ -69,7 +31,6 @@ CREATE TABLE IF NOT EXISTS `imagebase` (
   KEY `fk_imagebase_1_idx` (`osid`),
   KEY `fk_imagebase_owner_idx` (`updaterid`),
   KEY `fk_imagebase_1_idx1` (`virtid`),
-  KEY `currentversion_idx` (`currentversionid`),
   KEY `latestversion_idx` (`latestversionid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
@@ -109,52 +70,16 @@ CREATE TABLE IF NOT EXISTS `imageversion` (
   `filesize` bigint(20) NOT NULL,
   `filepath` varchar(200) NOT NULL,
   `uploaderid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
-  `isenabled` tinyint(1) NOT NULL,
   `isrestricted` tinyint(1) NOT NULL,
   `isvalid` tinyint(1) NOT NULL,
   `isprocessed` tinyint(1) NOT NULL,
   `mastersha1` binary(20) DEFAULT NULL,
   `virtualizerconfig` text BINARY NULL DEFAULT NULL COMMENT 'Specific configuration of the virtualizer for this image. For vmware, this is basically a dump of the *.vmx.',
   PRIMARY KEY (`imageversionid`),
-  KEY `version_access` (`imagebaseid`,`isenabled`,`isvalid`,`createtime`),
+  KEY `version_access` (`imagebaseid`,`isvalid`,`createtime`),
   KEY `fk_imageversion_2_idx` (`uploaderid`),
   KEY `expire_index` (`expiretime`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-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);
-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;
-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);
-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;
-END
-//
-DELIMITER ;
 
 CREATE TABLE IF NOT EXISTS `imageversion_x_software` (
   `imageversionid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
@@ -272,7 +197,6 @@ ALTER TABLE `imagebase`
   ADD CONSTRAINT `fk_imagebase_owner` FOREIGN KEY (`ownerid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE,
   ADD CONSTRAINT `fk_imagebase_updater` FOREIGN KEY (`updaterid`) REFERENCES `user` (`userid`) ON UPDATE CASCADE,
   ADD CONSTRAINT `fk_imagebase_1` FOREIGN KEY (`virtid`) REFERENCES `virtualizer` (`virtid`) ON UPDATE CASCADE,
-  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 `imagetag`
diff --git a/dozentenmodulserver/setup/sat-01-testdata.sql b/dozentenmodulserver/setup/sat-01-testdata.sql
index a6819dc5..4817f401 100644
--- a/dozentenmodulserver/setup/sat-01-testdata.sql
+++ b/dozentenmodulserver/setup/sat-01-testdata.sql
@@ -61,15 +61,15 @@ INSERT INTO `organization` (`organizationid`, `displayname`, `canlogin`) VALUES
 INSERT INTO `user` (`userid`, `firstname`, `lastname`, `email`, `organizationid`, `lastlogin`, `canlogin`, `issuperuser`) VALUES
 ('2f2a7203-1035-11e5-97f5-001999f89615', 'Max', 'Mustermann', 'spam@aol.com', 'fr-test.de', 1434025631, 1, 1);
 
-INSERT INTO `imagebase` (`imagebaseid`, `currentversionid`, `latestversionid`, `displayname`, `description`, `osid`, `virtid`, `createtime`, `updatetime`, `ownerid`, `updaterid`, `sharemode`, `istemplate`, `canlinkdefault`, `candownloaddefault`, `caneditdefault`, `canadmindefault`) VALUES
-('ba9e1f3a-1035-11e5-97f5-001999f89615', NULL, NULL, 'Test Image', 'Bla bla bla', 1, 'vmware', 1434025865, 1434025865, '2f2a7203-1035-11e5-97f5-001999f89615', '2f2a7203-1035-11e5-97f5-001999f89615', 'LOCAL', 0, 1, 1, 0, 0);
+INSERT INTO `imagebase` (`imagebaseid`, `latestversionid`, `displayname`, `description`, `osid`, `virtid`, `createtime`, `updatetime`, `ownerid`, `updaterid`, `sharemode`, `istemplate`, `canlinkdefault`, `candownloaddefault`, `caneditdefault`, `canadmindefault`) VALUES
+('ba9e1f3a-1035-11e5-97f5-001999f89615', NULL, 'Test Image', 'Bla bla bla', 1, 'vmware', 1434025865, 1434025865, '2f2a7203-1035-11e5-97f5-001999f89615', '2f2a7203-1035-11e5-97f5-001999f89615', 'LOCAL', 0, 1, 1, 0, 0);
 
 INSERT INTO `imagepermission` (`imagebaseid`, `userid`, `canlink`, `candownload`, `canedit`, `canadmin`) VALUES
 ('ba9e1f3a-1035-11e5-97f5-001999f89615', '2f2a7203-1035-11e5-97f5-001999f89615', 1, 1, 1, 1);
 
-INSERT INTO `imageversion` (`imageversionid`, `imagebaseid`, `createtime`, `expiretime`, `filesize`, `filepath`, `uploaderid`, `isenabled`, `isrestricted`, `isvalid`, `isprocessed`, `mastersha1`, `virtualizerconfig`) VALUES
-('37613571-1036-11e5-97f5-001999f89615', 'ba9e1f3a-1035-11e5-97f5-001999f89615', 1434026074, 1434026074, 1234, 'y', '2f2a7203-1035-11e5-97f5-001999f89615', 0, 1, 1, 1, NULL, NULL),
-('721ab892-103e-11e5-97f5-001999f89615', 'ba9e1f3a-1035-11e5-97f5-001999f89615', 1434029609, 1434029609, 23452345, 'a', '2f2a7203-1035-11e5-97f5-001999f89615', 0, 1, 1, 1, NULL, NULL);
+INSERT INTO `imageversion` (`imageversionid`, `imagebaseid`, `createtime`, `expiretime`, `filesize`, `filepath`, `uploaderid`, `isrestricted`, `isvalid`, `isprocessed`, `mastersha1`, `virtualizerconfig`) VALUES
+('37613571-1036-11e5-97f5-001999f89615', 'ba9e1f3a-1035-11e5-97f5-001999f89615', 1434026074, 1434026074, 1234, 'y', '2f2a7203-1035-11e5-97f5-001999f89615', 1, 1, 1, NULL, NULL),
+('721ab892-103e-11e5-97f5-001999f89615', 'ba9e1f3a-1035-11e5-97f5-001999f89615', 1434029609, 1434029609, 23452345, 'a', '2f2a7203-1035-11e5-97f5-001999f89615', 1, 1, 1, NULL, NULL);
 
 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;