Models

[Javimetro]

Each class represents a table in the database and the class attributes represents the columns of that table. Requires SQLAlchemy: SQLAlchemy uses Python classes to represent database tables and instances of those classes to represent rows in those tables. ORM (Object-Relational Mapping)

!pip install Flask-SQLAlchemy

Example of use:

from app import db  # Import the SQLAlchemy instance
from datetime import datetime

# Create an instance of the ESPDevice model
new_device = ESPDevice(DeviceID="1", 
                       RegistrationTime=datetime.utcnow(), 
                       LastActiveTime=datetime.utcnow())

# Add the new device to the session and commit it to the database
db.session.add(new_device)
db.session.commit()

[Javimetro]

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class ESPDevice(db.Model):
    __tablename__ = 'ESPDevices'
    DeviceID = db.Column(db.String(17), primary_key=True)
    RegistrationTime = db.Column(db.DateTime, nullable=False)
    LastActiveTime = db.Column(db.DateTime, nullable=False)

class User(db.Model):
    __tablename__ = 'Users'
    UserID = db.Column(db.Integer, primary_key=True)
    Username = db.Column(db.String(80), unique=True, nullable=False)
    DeviceID = db.Column(db.String(17), db.ForeignKey('ESPDevices.DeviceID'))
    RegistrationDate = db.Column(db.DateTime, nullable=False)

class Topic(db.Model):
    __tablename__ = 'Topics'
    TopicID = db.Column(db.Integer, primary_key=True)
    Title = db.Column(db.String(255), nullable=False)
    Description = db.Column(db.Text, nullable=False)
    StartTime = db.Column(db.DateTime, nullable=False)
    EndTime = db.Column(db.DateTime, nullable=False)

class Vote(db.Model):
    __tablename__ = 'Votes'
    VoteID = db.Column(db.Integer, primary_key=True)
    UserID = db.Column(db.Integer, db.ForeignKey('Users.UserID'))
    VoteType = db.Column(db.String(50), nullable=False)
    TopicID = db.Column(db.Integer, db.ForeignKey('Topics.TopicID'), nullable=False)
    VoteTime = db.Column(db.DateTime, nullable=False)

[MagnusLii]

The DB layout has been modified to better suite the needs of the projects, the updated SQLAlchemy classes are as follows. The new layout can be viewed at: https://dbdiagram.io/d/65941d20ac844320ae1b106b

db = SQLAlchemy()

class RegisteredESPs(db.Model):
    __tablename__ = 'registeredesps'
    DeviceIndex = db.Column(db.Integer, primary_key=True, unique=True, autoincrement=True)
    DeviceID = db.Column(db.String(255), unique=True, nullable=False)
    RegistrationTime = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp())
    LastActiveTime = db.Column(db.TIMESTAMP)
    Assigned = db.Column(db.Boolean, default=False)
    Registered = db.Column(db.Boolean, default=False)
    MacAddress = db.Column(db.String(255), unique=True)

class Users(db.Model):
    __tablename__ = 'users'
    UserID = db.Column(db.Integer, primary_key=True, autoincrement=True)
    Username = db.Column(db.Text, unique=True)
    DeviceIndex = db.Column(db.Integer, db.ForeignKey('registeredesps.DeviceIndex'))
    RegistrationDate = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp())
    registered_esp = db.relationship('RegisteredESPs', backref='users')

class Topics(db.Model):
    __tablename__ = 'topics'
    TopicID = db.Column(db.Integer, primary_key=True, autoincrement=True)
    Title = db.Column(db.Text, nullable=False)
    Description = db.Column(db.Text)
    StartTime = db.Column(db.TIMESTAMP)
    EndTime = db.Column(db.TIMESTAMP)

class Votes(db.Model):
    __tablename__ = 'votes'
    VoteID = db.Column(db.Integer, primary_key=True, autoincrement=True)
    UserID = db.Column(db.Integer, db.ForeignKey('users.UserID'), nullable=False)
    VoteType = db.Column(db.Text, nullable=False)
    TopicID = db.Column(db.Integer, db.ForeignKey('topics.TopicID', ondelete='CASCADE'), nullable=False)
    VoteTime = db.Column(db.TIMESTAMP, server_default=db.func.current_timestamp())