1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
<?php
/**
* @see https://gitlab.studip.de/studip/studip/-/issues/132
*/
final class ConsultationMultipleResponsibleRanges extends Migration
{
public function description()
{
return 'Adjust database to allow multiple responsible ranges for consultations';
}
protected function up()
{
$query = "CREATE TABLE IF NOT EXISTS `consultation_responsibilities` (
`block_id` INT(11) UNSIGNED NOT NULL,
`range_id` CHAR(32) CHARSET latin1 COLLATE latin1_bin NOT NULL,
`range_type` ENUM('user', 'institute', 'statusgroup') CHARSET latin1 COLLATE latin1_bin NOT NULL,
`mkdate` INT(11) UNSIGNED NOT NULL,
PRIMARY KEY (`block_id`, `range_id`, `range_type`)
)";
DBManager::get()->exec($query);
$query = "CREATE TABLE IF NOT EXISTS `consultation_events` (
`slot_id` INT(11) UNSIGNED NOT NULL,
`user_id` CHAR(32) CHARSET latin1 COLLATE latin1_bin NOT NULL,
`event_id` CHAR(32) CHARSET latin1 COLLATE latin1_bin NOT NULL,
`mkdate` INT(11) UNSIGNED NOT NULL,
PRIMARY KEY (`slot_id`, `user_id`)
)";
DBManager::get()->exec($query);
$query = "INSERT IGNORE INTO `consultation_responsibilities` (
`block_id`, `range_id`, `range_type`, `mkdate`
)
SELECT `block_id`, `teacher_id`, 'user', UNIX_TIMESTAMP()
FROM `consultation_blocks`
WHERE `teacher_id` IS NOT NULL";
DBManager::get()->exec($query);
$query = "INSERT IGNORE INTO `consultation_events` (
`slot_id`, `user_id`, `event_id`, `mkdate`
)
SELECT `slot_id`, `teacher_id`, `teacher_event_id`, UNIX_TIMESTAMP()
FROM `consultation_blocks`
JOIN `consultation_slots` USING (`block_id`)
WHERE `teacher_event_id` IS NOT NULL";
DBManager::get()->exec($query);
$query = "ALTER TABLE `consultation_blocks`
DROP COLUMN `teacher_id`";
DBManager::get()->exec($query);
$query = "ALTER TABLE `consultation_slots`
DROP COLUMN `teacher_event_id`";
DBManager::get()->exec($query);
}
protected function down()
{
$query = "ALTER TABLE `consultation_slots`
ADD COLUMN `teacher_event_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL AFTER `note`";
DBManager::get()->exec($query);
$query = "ALTER TABLE `consultation_blocks`
ADD COLUMN `teacher_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL";
DBManager::get()->exec($query);
$query = "UPDATE `consultation_slots` AS cs
JOIN `consultation_events` AS ce USING (`slot_id`)
JOIN `consultation_blocks` AS cb USING (`block_id`)
SET cs.`teacher_event_id` = ce.`event_id`
WHERE cb.`range_type` = 'user'
AND cs.`slot_id` IN (
SELECT `slot_id`
FROM `consultation_events`
GROUP BY `slot_id`
HAVING COUNT(*) = 1
)";
DBManager::get()->exec($query);
$query = "UPDATE `consultation_blocks` AS cb
JOIN `consultation_responsibilities` AS cr USING (`block_id`)
SET cb.`teacher_id` = cr.`range_id`
WHERE cb.`block_id` IN (
SELECT `block_id`
FROM `consultation_responsibilities` AS cr2
JOIN `consultation_blocks` AS cb USING (`block_id`)
WHERE cr2.`range_type` = 'user'
GROUP BY `block_id`
HAVING COUNT(DISTINCT cr.`range_id`) = 1
)";
DBManager::get()->exec($query);
$query = "DROP TABLE IF EXISTS `consultation_events`";
DBManager::get()->exec($query);
$query = "DROP TABLE IF EXISTS `consultation_responsibilities`";
DBManager::get()->exec($query);
}
}
|
