Skip to content

DatabaseSchema

Jump to bottom
JForden edited this page Nov 7, 2023 · 3 revisions

Table of contents:

  1. Introduction
  2. Deployment and changes
  3. Connecting to the database
  4. Database Schema

DockerMySQL

The database for TA-Bot is a self-contained Docker container. The benefits of this are:

  • Isolation: Docker ensures that the MySQL instance doesn't interfere with any other services on your system.
  • Portability: You can easily move the Docker container to another machine without worrying about dependencies.
  • Ease of Setup: Docker can automatically handle the setup and configuration of the MySQL instance.

Deployment and changes

  • Deployment: The Docker container is deployed using the docker-compose.yml file in the root directory of the repository.
 db:
    image: mysql
    restart: always
    environment:
      MYSQL_DATABASE: `example_database_name`
      MYSQL_USER: 'example_user'
      MYSQL_PASSWORD: 'example_password'
      MYSQL_ROOT_PASSWORD: 'example_password'
    ports:
      - '127.0.0.1:3306:3306'
    expose:
      - '3306'
    volumes:
      - ./database:/var/lib/mysql
      - ./init-db:/docker-entrypoint-initdb.d
  • Changes: If you want to change the database name, user, or password, you can do so by changing the environment variables in the docker-compose.yml file. If you want to change the port that the database is exposed on, you can do so by changing the port in the ports section of the docker-compose.yml file.

The docker image is built via an init.sql file that is located in the init-db directory. This file is run when the container is first created. If you want to make changes to the database schema, you can do so by modifying this file. If you want to make changes to the database schema after the container has been created, you can do so by connecting to the database image and running SQL commands manually, or by using MySQL Workbench. - Editing the init.sql file will not change the database schema of an existing container. Changes made to this will only have an effect if you create a new database container.

Connecting to the database

The simplest way to view the database directly is by using MYSQL Workbench

  • This can be downloaded here

Once created, you can add a new connection, using the following settings:

  • Change the connection name to whatever you want
  • Change the connection method to Standard TCP/IP over SSH
  • The machine name is whatever server the docker container is running on
  • Specifying the port as 3306, and setting the username and password credentials accordingly

Once you have connected to the database, you can view the database schema by clicking on the Schemas tab on the left side of the screen. You can then view the tables by clicking on the Tables tab on the left side of the screen.

Database-Schema

Describing a database schema is a non-trivial task, while we hope this section provides some insight into the database schema, it is not a complete description.

Schema Visualization

tabotdbschema

Schema Model

Perhaps the easiest way to walk through the foreign key relations is to start with the models.py file. This maintains an internal structure of the database, which is used in multiple API repo's that leverage SQLAlchemy to interact with the database. If a change is made to the schema, it must be reflected in this file. For more detail and example of leveraging SQLAlchemy refer to the development guide

class Projects(db.Model):
    __tablename__ = "Projects"
    Id = Column(Integer, primary_key=True, autoincrement=True)
    ClassId = Column(Integer, ForeignKey('Classes.Id'))
    Name = Column(String)
    Start = Column(Date)
    End = Column(Date)
    Language = Column(String)

    Submissions=relationship('Submissions') 
    Levels=relationship('Levels')
    StudentProgress=relationship('StudentProgress')
    StudentUnlocks=relationship('StudentUnlocks') 
    solutionpath=Column(String)
    AsnDescriptionPath = Column(String)

class Users(db.Model):
    __tablename__ = "Users"
    Id = Column(Integer, primary_key=True, autoincrement=True)
    Username = Column(String)
    Firstname = Column(String)
    Lastname = Column(String)
    Email = Column(String)
    StudentNumber = Column(String)
    Role = Column(Integer)
    IsLocked = Column(Boolean)
    ResearchGroup = Column(Integer)
    Submissions=relationship('Submissions')
    ClassAssignments=relationship('ClassAssignments')
    LoginAttempts=relationship('LoginAttempts')
    StudentProgress=relationship('StudentProgress')
    StudentUnlocks=relationship('StudentUnlocks') 

class Submissions(db.Model):
    __tablename__ = "Submissions"
    Id = Column(Integer, primary_key=True)
    OutputFilepath = Column(String)
    PylintFilepath = Column(String)
    CodeFilepath = Column(String)
    IsPassing = Column(Boolean)
    NumberOfPylintErrors = Column(String)
    Time = Column(Date)
    User = Column(Integer, ForeignKey('Users.Id'))
    Project = Column(Integer, ForeignKey('Projects.Id'))
    SubmissionLevel =Column(String)
    Points = Column(Integer)
    StudentProgress=relationship('StudentProgress')
    visible = Column(Integer)

class LoginAttempts(db.Model):
    __tablename__ = "LoginAttempts"
    Id = Column(Integer, primary_key=True)
    Time = Column(Date)
    IPAddress = Column(String)
    Username = Column(String, ForeignKey('Users.Username'))

class Classes(db.Model):
    __tablename__ = "Classes"
    Id = Column(Integer, primary_key=True)
    Name = Column(String)
    Tid = Column(String)
    

class Labs(db.Model):
    __tablename__ = "Labs"
    Id = Column(Integer, primary_key=True)
    Name = Column(String)
    ClassId = Column(Integer, ForeignKey('Classes.Id'))
    ClassAssignments=relationship('ClassAssignments')

class LectureSections(db.Model):
    __tablename__ = "LectureSections"
    Id = Column(Integer, primary_key=True)
    Name = Column(String)
    ClassId = Column(Integer, ForeignKey('Classes.Id'), primary_key=True)
    ClassAssignments=relationship('ClassAssignments')

class ClassAssignments(db.Model):
    __tablename__ = "ClassAssignments"
    UserId = Column(Integer, ForeignKey('Users.Id'), primary_key=True)
    ClassId = Column(Integer, ForeignKey('Classes.Id'), primary_key=True)
    LabId = Column(Integer, ForeignKey('Labs.Id'))
    LectureId = Column(Integer, ForeignKey('LectureSections.Id'))

class StudentProgress(db.Model):
    __tablename__ = "StudentProgress"
    UserId = Column(Integer, ForeignKey('Users.Id'), primary_key=True)
    ProjectId = Column(Integer, ForeignKey('Projects.Id'), primary_key=True)
    SubmissionId = Column(Integer, ForeignKey('Submissions.Id'), primary_key=True)
    LatestLevel = Column(String)

class StudentUnlocks(db.Model):
    __tablename__ = "StudentUnlocks"
    UserId = Column(Integer, ForeignKey('Users.Id'), primary_key=True)
    ProjectId = Column(Integer, ForeignKey('Projects.Id'), primary_key=True)
    Time = Column(DateTime)

class Config(db.Model):
    __tablename__ = "Config"
    Name  = Column(String, primary_key=True)
    Value = Column(String)

class Levels(db.Model):
    __tablename__ = "Levels"
    Id = Column(Integer, primary_key=True)
    ProjectId = Column(Integer, ForeignKey('Projects.Id'))
    Name=Column(String)
    Order=Column(Integer)
    Points=Column(Integer)

class Testcases(db.Model):
    __tablename__ = "Testcases"
    Id = Column(Integer, primary_key=True, autoincrement=True)
    ProjectId = Column(Integer, ForeignKey('Projects.Id'))
    LevelId = Column(Integer, ForeignKey("Levels.Id"))
    Name = Column(String)
    Description = Column(String)
    input = Column(String)
    Output = Column(String)
    IsHidden = Column(Boolean)
    additionalfilepath = Column(String)
class GPTLogs(db.Model):
    __tablename__ = "GPTLogs"
    Qid = Column(Integer, primary_key=True, autoincrement=True)
    SubmissionId = Column(Integer, ForeignKey('Submissions.Id'))
    GPTResponse = Column(String)
    StudentFeedback = Column(Integer)
    Type = Column(Integer)

class ChatGPTkeys(db.Model):
    __tablename__ = "ChatGPTkeys"
    idChatGPTkeys = Column(Integer, primary_key=True, autoincrement=True)
    ChatGPTkeyscol = Column(String)
    LastUsed = Column(DateTime)

class StudentQuestions(db.Model):
    __tablename__ = "StudentQuestions"
    Sqid = Column(Integer, primary_key=True, autoincrement=True)
    StudentQuestionscol = Column(String)
    ruling = Column(Integer)
    dismissed = Column(Integer)
    StudentId = Column(Integer, ForeignKey('Users.Id'))
    TimeSubmitted = Column(DateTime)
    projectId = Column(Integer, ForeignKey('Projects.Id'))
    TimeAccepted = Column(DateTime)
    TimeCompleted = Column(DateTime)

class StudentGrades(db.Model):
    __tablename__ = "StudentGrades"
    Sid = Column(Integer, ForeignKey('Users.Id'), primary_key=True)
    Pid = Column(Integer, ForeignKey('Projects.Id'), primary_key=True)
    Grade = Column(Integer)

Clone this wiki locally