Add dozmod database upgrade script to restore feature

1 files changed, 129 insertions, 0 deletions

diff --git a/data/dozmod-upgrade.sql b/data/dozmod-upgrade.sql
new file mode 100644
index 0000000..3636d72
--- /dev/null
+++ b/data/dozmod-upgrade.sql
@@ -0,0 +1,129 @@
+-- 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)

+	SELECT bw.userID, bw.Vorname, bw.Nachname, bw.mail, 'hs-offenburg.de', 1

+	FROM bwLehrpool.m_user AS bw;

+

+-- Make myself a superuser, so I can edit every image/lecture

+--UPDATE sat.user SET issuperuser=1 WHERE email='steffen.ritter@hs-offenburg.de';

+

+-- 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 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

+		IF(bw.image_isTemplate=1, '1', '0'),	-- 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;

+

+-- 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;

+