3 Persistence

Jonas Jødestøl Haugland edited this page 2022-04-28 15:00:47 +02:00

Table of Contents

• ER-Diagram
• The SQL calls to create the database tables needed:

All data is stored in the MySQL-database. User sessions are stored in server memory.

ER-Diagram

The SQL calls to create the database tables needed:

-- WARNING: Will delete EVERYTHING in the database!

DROP TABLE IF EXISTS matches;
DROP TABLE IF EXISTS teams;
DROP TABLE IF EXISTS tournaments;
DROP TABLE IF EXISTS users;

-- Create the tables
CREATE TABLE tournaments (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    prize TEXT,
    teamLimit INTEGER NOT NULL,
    startTime DATETIME NOT NULL,
    endTime DATETIME NOT NULL
);

CREATE TABLE teams (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    tournamentId INTEGER NOT NULL,
    name TEXT NOT NULL,

    FOREIGN KEY (tournamentId) REFERENCES tournaments (id) ON DELETE CASCADE
);

CREATE TABLE matches (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    tournamentId INTEGER NOT NULL,
    parentMatchId INTEGER,
    team1Id INTEGER,
    team2Id INTEGER,
    winnerId INTEGER,
    tier INTEGER,

    FOREIGN KEY (tournamentId) REFERENCES tournaments (id) ON DELETE CASCADE,
    FOREIGN KEY (team1Id) REFERENCES teams (id) ON DELETE SET NULL,
    FOREIGN KEY (team2Id) REFERENCES teams (id) ON DELETE SET NULL,
    FOREIGN KEY (winnerId) REFERENCES teams (id) ON DELETE SET NULL
);

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    googleId TEXT,
    name TEXT,
    email TEXT NOT NULL,
    isManager BOOLEAN NOT NULL
);