Skip to content
Snippets Groups Projects

Add Quarter table to the database

In this commit, the following functionality is changed:

  • The database now includes a Quarter table. This table has booleans interest (for gathering interest) and availability (for gathering availability). The first boolean is moved from Schedule.
  • The code is edited to comply with the specified new database schema.

In order to update your database, I have supplied the following update script:

-- Create Quarter Database
CREATE TABLE Quarter (
    year int NOT NULL,
    quarter int NOT NULL,
    interest boolean NOT NULL DEFAULT false,
    availability boolean NOT NULL DEFAULT false,
    CONSTRAINT Quarter_pk PRIMARY KEY (year,quarter)
);

-- Duplicate current quarters in the database from both CourseEdition and Schedule
INSERT INTO Quarter (year, quarter) SELECT year, quarter FROM CourseEdition;
INSERT INTO Quarter (year, quarter, interest) SELECT year, quarter, interest FROM Schedule ON DUPLICATE KEY UPDATE Quarter.interest = Schedule.interest;

-- Only use the commented out commands if the altering tables does not work. In this case, please check whether the year and quarter columns of the erroring class are
-- equal to the Quarter columns by running:
-- SHOW CREATE TABLE ERRORTABLE;
-- SHOW CREATE TABLE Quarter;
-- and comparing the year and quarter column of the two.

-- Add Foreign key constraints
-- SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE CourseEdition ADD CONSTRAINT CourseEdition_Quarter FOREIGN KEY CourseEdition_Quarter (year,quarter) REFERENCES Quarter (year,quarter);
ALTER TABLE Note ADD CONSTRAINT Note_Quarter FOREIGN KEY Note_Quarter (year,quarter) REFERENCES Quarter (year,quarter);
ALTER TABLE Schedule ADD CONSTRAINT Schedule_Quarter FOREIGN KEY Schedule_Quarter (year,quarter) REFERENCES Quarter (year,quarter);
ALTER TABLE Timeslot ADD CONSTRAINT Timeslot_Quarter FOREIGN KEY Timeslot_Quarter (year,quarter) REFERENCES Quarter (year,quarter);
-- SET FOREIGN_KEY_CHECKS = 1

-- The commented command below is only to be used if the CourseEdition has a UNIQUE called course_code. You can check this by running SHOW CREATE TABLE CourseEdition
-- Miscellaneous updates
-- ALTER TABLE CourseEdition DROP INDEX course_code;
ALTER TABLE CourseEdition ADD UNIQUE INDEX CourseEdition_course_code (course_code,year,quarter);
-- The next line breaks backwards compatibility.
-- ALTER TABLE Schedule DROP COLUMN interest;

Please read through the comments in the script as they give additional information on running the script.

Edited by Otto Visser

Merge request reports

Checking pipeline status.

Merged by Otto VisserOtto Visser May 30, 2018 (May 30, 2018 12:41pm UTC)

Loading

Pipeline #75441 passed

Pipeline passed for e2fe4575 on master

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
  • Otto Visser marked as a Work In Progress

    marked as a Work In Progress

    By Max van Deursen on 2018-05-30T10:03:38 (imported from GitLab project)

  • Otto Visser changed the description

    changed the description

    By Max van Deursen on 2018-05-30T10:03:38 (imported from GitLab project)

  • Otto Visser added 2 commits

    added 2 commits

    • 43647a04 - 1 commit from branch master
    • ca81b80c - Add Quarter table to the database

    Compare with previous version

    By Max van Deursen on 2018-05-30T10:12:07 (imported from GitLab project)

  • Otto Visser unmarked as a Work In Progress

    unmarked as a Work In Progress

    By Max van Deursen on 2018-05-30T10:12:15 (imported from GitLab project)

  • Otto Visser marked as a Work In Progress

    marked as a Work In Progress

    By Max van Deursen on 2018-05-30T10:18:18 (imported from GitLab project)

  • Otto Visser added 1 commit

    added 1 commit

    • 3a598923 - Add Quarter table to the database

    Compare with previous version

    By Max van Deursen on 2018-05-30T10:35:32 (imported from GitLab project)

  • Otto Visser unmarked as a Work In Progress

    unmarked as a Work In Progress

    By Max van Deursen on 2018-05-30T10:36:46 (imported from GitLab project)

  • Otto Visser changed the description

    changed the description

    By Max van Deursen on 2018-05-30T12:00:21 (imported from GitLab project)

  • assigned to @mpigmans

    By Max van Deursen on 2018-05-30T12:00:54 (imported from GitLab project)

  • What is this for now, since we are specifying all the information in the table in this query (year=?, quarter=?, interest=true); what information do we get from doing this query now?

    By Max Pigmans on 2018-05-30T12:13:07 (imported from GitLab project)

  • Thinking about this more doing this differently would require much more code for no gain. nvm

    By Max Pigmans on 2018-05-30T12:14:54 (imported from GitLab project)

  • Otto Visser resolved all discussions

    resolved all discussions

    By Max Pigmans on 2018-05-30T12:14:55 (imported from GitLab project)

  • merged

    By Max Pigmans on 2018-05-30T12:41:21 (imported from GitLab project)

  • Otto Visser mentioned in commit e2fe4575

    mentioned in commit e2fe4575

    By Max Pigmans on 2018-05-30T12:41:21 (imported from GitLab project)

  • Otto Visser changed the description

    changed the description

    By Geert Habben Jansen on 2018-06-01T13:43:57 (imported from GitLab project)

Please register or sign in to reply
Loading