blob: 88f50e4ecc52d24c718e3a7782c5ff6933657b1c (
plain)
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
101
102
103
|
<?php
# refers to https://develop.studip.de/trac/ticket/3973
class AddSeminarIdToFolder extends Migration
{
/**
* short description of this migration
*/
function description()
{
return 'add field `seminar_id` to table `folder`';
}
/**
* perform this migration
*/
function up()
{
$this->addSeminarIdField();
$this->updateHeads();
$this->updateTails();
}
function addSeminarIdField()
{
DBManager::get()->exec('ALTER TABLE `folder` ADD `seminar_id` VARCHAR(32) NOT NULL AFTER `range_id`');
}
function updateHeads()
{
$this->updateFirstLevelFolders();
$this->updateSpecialFolders();
}
function updateTails()
{
do {
$updated = $this->updateSeminarIds("SELECT f1.folder_id, f2.seminar_id FROM `folder` f1 INNER JOIN folder f2 ON f1.range_id = f2.folder_id WHERE f1.seminar_id = ''");
} while ($updated > 0);
}
function updateFirstLevelFolders()
{
$this->updateSeminarIds("SELECT f.folder_id, s.Seminar_id AS seminar_id ".
"FROM `folder` f ".
"INNER JOIN `seminare` s ON s.Seminar_id = f.range_id");
$this->updateSeminarIds("SELECT f.folder_id, s.Seminar_id AS seminar_id ".
"FROM `folder` f ".
"INNER JOIN `seminare` s ON MD5(CONCAT(s.Seminar_id, 'top_folder')) = f.range_id");
$this->updateSeminarIds("SELECT f.folder_id, i.Institut_id AS seminar_id ".
"FROM `folder` f ".
"INNER JOIN `Institute` i ON i.Institut_id = f.range_id");
$this->updateSeminarIds("SELECT f.folder_id, i.Institut_id AS seminar_id ".
"FROM `folder` f ".
"INNER JOIN `Institute` i ON MD5(CONCAT(i.Institut_id, 'top_folder')) = f.range_id");
}
function updateSpecialFolders()
{
$this->updateSeminarIds("SELECT f.folder_id, s.range_id AS seminar_id ".
"FROM `folder` f ".
"INNER JOIN `statusgruppen` s ON s.statusgruppe_id = f.range_id");
$this->updateSeminarIds("SELECT f.folder_id, t.seminar_id AS seminar_id ".
"FROM `folder` f ".
"INNER JOIN `themen` t ON t.issue_id = f.range_id");
$this->updateSeminarIds("SELECT f.folder_id, a.user_id AS seminar_id
FROM `folder` f
INNER JOIN `auth_user_md5` a ON a.user_id = f.range_id");
}
function updateSeminarIds($sql)
{
$db = DBManager::get();
$stmt = $db->prepare($sql);
$stmt->execute([]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$updated = 0;
$stmt = $db->prepare("UPDATE folder SET seminar_id = ? WHERE folder_id = ?");
foreach ($rows as $row) {
$stmt->execute([$row['seminar_id'], $row['folder_id']]);
$updated += $stmt->rowCount();
}
return $updated;
}
/**
* revert this migration
*/
function down()
{
$db = DBManager::get();
try {
$db->exec("ALTER TABLE `folder` DROP COLUMN `seminar_id`");
} catch (Exception $e) { }
}
}
|
