path: root/data/dozmod-upgrade.sql

-- Delete images, lectures, permissions AND users
USE sat;
DELETE FROM lecturepermission;
DELETE FROM imagepermission;
DELETE FROM lecture;
UPDATE imagebase SET latestversionid=NULL;
DELETE FROM imageversion;
DELETE FROM imagebase;
DELETE FROM user;

-- Migrate users (set all to specific organisation)
INSERT INTO sat.user (userid, firstname, lastname, email, organizationid, canlogin, emailnotifications)
	SELECT bw.userID, bw.Vorname, bw.Nachname, Concat('@', bw.mail, '@'), 'hs-offenburg.de', 1, 0
	FROM bwLehrpool.m_user AS bw;

-- Insert dummy account to which we link all images/lectures without existing real user.
-- A SuperAdmin can later change the permissions accordingly or delete the images/lectures
INSERT IGNORE INTO sat.user (userid, firstname, lastname, email, organizationid, canlogin)
	VALUES('dummy@dummy.de', 'Dummy', 'Dummy', 'dummy@dummy.de', 'hs-offenburg.de', 0);

-- Migrate images
INSERT INTO sat.imagebase
	(imagebaseid, latestversionid, displayname, description, osid, virtid, createtime, updatetime,
		ownerid, updaterid, sharemode, istemplate, canlinkdefault, candownloaddefault, caneditdefault, canadmindefault)
	SELECT
		bw.GUID_imageID, 
		NULL, 	-- 'latestversionid' will be filled later with an update
		bw.image_name, 
		bw.image_description, 
			CASE
			WHEN bw.content_operatingSystem=0 THEN 10		-- Win7x86	-> Win7x86
			WHEN bw.content_operatingSystem=10 THEN 13		-- Deb6x86	-> OtherLinuxx86
			WHEN bw.content_operatingSystem=12 THEN 13		-- Deb7x86	-> OtherLinuxx86
			WHEN bw.content_operatingSystem=11 THEN 14		-- Deb6x64	-> OtherLinuxx64
			WHEN bw.content_operatingSystem=13 THEN 14		-- Deb7x64	-> OtherLinuxx64
			WHEN bw.content_operatingSystem=14 THEN 2		-- Win10x86	-> Win8x86
			WHEN bw.content_operatingSystem=15 THEN 3		-- Win10x64	-> Win8x64
			ELSE bw.content_operatingSystem END,
		'vmware',
		UNIX_TIMESTAMP(bw.image_create_time),
		UNIX_TIMESTAMP(bw.image_update_time),
		IF(bw.image_owner IN (SELECT userid FROM sat.user), bw.image_owner, 'dummy@dummy.de'),			-- If an image belongs to an old user which wasn't present at the time of user migration
		IF(bw.image_change_by IN (SELECT userid FROM sat.user), bw.image_change_by, 'dummy@dummy.de'),	-- set the dummy account as image owner/updater
		'LOCAL',
		bw.image_isTemplate,
		'0',	-- canlinkdefault
		bw.image_isTemplate,	-- If the image is an template, everybody can download it
		'0', 	-- caneditdefault
		'0'		-- canadmindefault
	FROM bwLehrpool.m_VLData_imageInfo AS bw;

-- Create imageversions from migrated images
INSERT INTO sat.imageversion (imageversionid, imagebaseid, createtime, expiretime, filesize, filepath, uploaderid, isrestricted, isvalid, isprocessed)
	SELECT 
		UUID(),
		b.imagebaseid, 
		b.createtime, 
		UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL +8 MONTH)),	-- expiretime is in 8 months
		(SELECT image_filesize FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID=b.imagebaseid), 
		(SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID=b.imagebaseid),
		b.ownerid,
		1,	-- isrestricted
		1,	-- isvalid
		0	-- isprocessed
	FROM sat.imagebase AS b;

-- Update 'latestversionid' in the imagebase with the just created imageversion
UPDATE sat.imagebase b, sat.imageversion v 
	SET b.latestversionid = v.imageversionid
	WHERE b.imagebaseid = v.imagebaseid;

-- Delete image permissions which link to a nonexisting image
USE bwLehrpool;

DELETE pm
	FROM pm_VLData_image AS pm
	LEFT JOIN m_VLData_imageInfo AS img 
		ON pm.GUID_imageID=img.GUID_imageID
	WHERE img.GUID_imageID IS NULL;

-- Migrate image permissions
INSERT IGNORE INTO sat.imagepermission(imagebaseid, userid, canlink, candownload, canedit, canadmin)
	SELECT pm.GUID_imageID, pm.userID, pm.link_allowed, pm.image_read, pm.image_write, pm.image_admin
	FROM bwLehrpool.pm_VLData_image AS pm WHERE pm.userID IN (SELECT userid FROM sat.user);

-- Delete lectures which link to a non existing image
DELETE lec
	FROM m_VLData_lecture AS lec
	LEFT JOIN m_VLData_imageInfo AS img 
		ON lec.imageID=img.GUID_imageID
	WHERE img.GUID_imageID IS NULL;

-- Migrate lectures
INSERT IGNORE INTO sat.lecture (lectureid, displayname, description, imageversionid, autoupdate, isenabled, starttime,
	endtime, createtime, updatetime, ownerid, updaterid, isexam, hasinternetaccess, caneditdefault, canadmindefault)
	SELECT 
		l.lectureID, 
		l.name, 
		l.description, 
		(SELECT latestversionid FROM sat.imagebase WHERE imagebase.imagebaseid=l.imageID),
		1, 	-- autoupdate
		l.isActive, 
		UNIX_TIMESTAMP(l.startTime), 
		UNIX_TIMESTAMP(l.endTime), 
		UNIX_TIMESTAMP(l.admin_createTime), 
		UNIX_TIMESTAMP(l.admin_changeTime), 
		IF(l.admin_owner IN (SELECT userid FROM sat.user), l.admin_owner, 'dummy@dummy.de'),
		IF(l.admin_change_by IN (SELECT userid FROM sat.user), l.admin_change_by, 'dummy@dummy.de'),
		0, 	-- isexam
		1, 	-- hasinternetaccess
		0, 	-- caneditdefault
		0	-- canadmindefault
	FROM bwLehrpool.m_VLData_lecture AS l;

-- Delete lecture permissions which link to a nonexisting lecture
DELETE pm
	FROM pm_VLData_lecture AS pm
	LEFT JOIN m_VLData_lecture AS lec 
		ON pm.lectureID=lec.lectureID
	WHERE lec.lectureID IS NULL;

-- Migrate lecture permissions
INSERT IGNORE INTO sat.lecturepermission (lectureid, userid, canedit, canadmin)
	SELECT pm.lectureID, pm.userID, pm.rec_write, pm.rec_admin
	FROM bwLehrpool.pm_VLData_lecture AS pm;

-- Try to get rid of dummy user. If it's needed this will fail thanks to constraints
DELETE IGNORE FROM sat.user WHERE userid = 'dummy@dummy.de';