From 0412eab2658146978fa06730ff27210a27de3a57 Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Tue, 29 Sep 2015 18:28:18 +0200 Subject: Update dozmod-upgrade.sql --- data/dozmod-upgrade.sql | 19 +++++++++++-------- 1 file changed, 11 insertions(+), 8 deletions(-) diff --git a/data/dozmod-upgrade.sql b/data/dozmod-upgrade.sql index 4307543..abca7b3 100644 --- a/data/dozmod-upgrade.sql +++ b/data/dozmod-upgrade.sql @@ -10,12 +10,12 @@ 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 + SELECT bw.userID, bw.Vorname, bw.Nachname, Concat('@', bw.mail, '@'), 'hs-offenburg.de', 1 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 INTO sat.user (userid, firstname, lastname, email, organizationid, canlogin) +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 @@ -35,16 +35,16 @@ INSERT INTO sat.imagebase 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', + ELSE bw.content_operatingSystem END, + 'vmware', UNIX_TIMESTAMP(bw.image_create_time), - UNIX_TIMESTAMP(bw.image_update_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, + 'LOCAL', + bw.image_isTemplate, '0', -- canlinkdefault - IF(bw.image_isTemplate=1, '1', '0'), -- If the image is an template, everybody can download it + bw.image_isTemplate, -- If the image is an template, everybody can download it '0', -- caneditdefault '0' -- canadmindefault FROM bwLehrpool.m_VLData_imageInfo AS bw; @@ -124,3 +124,6 @@ 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'; + -- cgit v1.2.3-55-g7522