diff options
Diffstat (limited to 'data')
| -rw-r--r-- | data/dozmod-upgrade.sql | 129 |
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;
+
|