db/migrations/1.310_add_seminare_semester_table.php


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

<?php
class AddSeminareSemesterTable extends Migration
{

    public function description()
    {
        return 'Creates a better performing connection between courses and semesters.';
    }

    public function up()
    {
        DBManager::get()->exec(
            "CREATE TABLE IF NOT EXISTS `semester_courses` (
                `semester_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
                `course_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
                `mkdate` INT(10) NOT NULL DEFAULT '0',
                `chdate` INT(10) NOT NULL DEFAULT '0',
                PRIMARY KEY (`semester_id`, `course_id`)
            );"
        );

        DBManager::get()->exec("
            INSERT IGNORE INTO `semester_courses`
            (`semester_id`, `course_id`, `mkdate`, `chdate`)
            SELECT `semester_data`.`semester_id`, `seminare`.`Seminar_id`, `seminare`.`mkdate`, `seminare`.`chdate`
            FROM `seminare`
            INNER JOIN `semester_data` ON 
                `seminare`.`start_time` <= `semester_data`.`beginn` AND
                `semester_data`.`beginn` <= `seminare`.`start_time` + `seminare`.`duration_time` AND
                `seminare`.`duration_time` >= 0
        ");
    }

    public function down()
    {
        DBManager::get()->exec("DROP TABLE IF EXISTS `semester_courses`;");
    }
}