From ca2aef36c8c280f61d14ba16f9efc03e014e3162 Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Tue, 22 Sep 2015 18:25:24 +0200 Subject: Add dozmod database upgrade script to restore feature --- data/dozmod-upgrade.sql | 129 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 129 insertions(+) create mode 100644 data/dozmod-upgrade.sql (limited to 'data') 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; + -- cgit v1.2.3-55-g7522