class Employee(db.Model):
__tablename__ = 'employee'
__table_args__ = {'mysql_engine': 'InnoDB'}
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(64), nullable=False)
last_name = db.Column(db.String(64), nullable=False)
salary = db.Column(db.Integer, nullable=True)
department = db.Column(db.String(64),
db.ForeignKey('department.name',
ondelete='SET NULL',
onupdate='CASCADE'),
nullable=True)
birth_date = db.Column(db.Date, nullable=False)
hire_date = db.Column(db.Date, nullable=False, default=date.today())
dept = db.relationship('Department',
backref=db.backref('employee',
passive_deletes=True,
passive_updates=True))
head = db.relationship('Head',
backref='employee',
passive_deletes=True,
uselist=False)
def serialize(self):
return {
'id': self.id,
'first_name': self.first_name,
'last_name': self.last_name,
'salary': self.salary,
'department': self.department,
'birth_date': dump_date(self.birth_date),
'hire_date': dump_date(self.hire_date)
}
class Rule(db.Model):
__tablename__ = 'rule'
__table_args = (db.UniqueConstraint('text', 'condominium_id'),
db.Index('rule_idx', 'text'))
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.String(500), nullable=False)
employee_id = db.Column(db.Integer,
db.ForeignKey('employee.id', ondelete='CASCADE'),
nullable=False)
condominium_id = db.Column(db.Integer,
db.ForeignKey('condominium.id',
ondelete='CASCADE'),
nullable=False)
author = db.relationship('Employee',
backref=db.backref('rules',
lazy=True,
cascade='all, delete'),
lazy=False)
condominium = db.relationship(
'Condominium',
backref=db.backref('rules', lazy=True, cascade='all, delete'),
lazy=True,
primaryjoin=
'and_(Rule.condominium_id == Condominium.id, Condominium.active == 1)')
def __repr__(self):
return f'Rule(text={self.text})'
class Movie(db.Model):
__tablename__ = 'movie'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(50), nullable=False)
date_posted = db.Column(db.DateTime,
nullable=False,
default=datetime.datetime.now)
release_year = db.Column(db.Integer, nullable=False)
director = db.Column(db.String(30), nullable=False)
duration_hrs = db.Column(db.Integer, nullable=False)
duration_mins = db.Column(db.Integer, nullable=False)
rate = db.Column(db.Integer, nullable=False)
storyline = db.Column(db.String(1000), nullable=False)
poster = db.Column(db.String(1000),
nullable=False,
default='movie_poster_default.jpg')
genre_id = db.Column(db.Integer, db.ForeignKey('genre.id'), nullable=False)
genre = db.relationship('Genre')
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
user = db.relationship('User')
def __repr__(self):
return ("Movie"
f"('{self.title}',"
f" '{self.director}',"
f" '{self.rate}',"
f" '{self.duration_hrs}:{self.duration_mins}',"
f" '{self.release_year}',"
f" '{self.genre.name}'"
f" '{self.author.username}'"
f" '{self.date_posted.strftime('%Y-%m-%d %I:%M%p')}')")
@property
def serialize(self):
return {
'id': self.id,
'title': self.title,
'date_posted': self.date_posted,
'release_year': self.release_year,
'director': self.director,
'duration_hrs': self.duration_hrs,
'duration_mins': self.duration_mins,
'rate': self.rate,
'poster': self.poster,
'genre': self.genre.name,
'genre_id': self.genre_id,
'user': self.user.username,
'user_id': self.user_id,
'poster': self.poster
}
class Resident(db.Model):
__tablename__ = 'resident'
__table_args__ = (db.UniqueConstraint('cpf', 'apartment_id'),
db.Index('resident_idx', 'cpf', 'apartment_id'))
id = db.Column(db.Integer, primary_key=True)
cpf = db.Column(db.String(11), nullable=False)
name = db.Column(db.String(50), nullable=False)
birthday = db.Column(db.Date, nullable=False)
photo_location = db.Column(db.String(200),
nullable=True,
default='data/photos/default/resident.png')
apartment_id = db.Column(db.Integer,
db.ForeignKey('apartment.id', ondelete='CASCADE'),
nullable=False)
apartment = db.relationship(
'Apartment',
backref=db.backref('residents', lazy=True, cascade='all, delete'),
lazy=True,
primaryjoin=
'and_(Resident.apartment_id == Apartment.id, Apartment.active == 1)')
def __repr__(self):
return f'Resident(id={self.id}, ' \
f'name={self.name}, ' \
f'birthday={self.birthday})'
class Product(db.Model):
__tablename__ = 'Product'
SKU = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(24), index=True, unique=True, nullable=False)
category = db.Column(db.String(16), nullable=False)
price = db.Column(db.Numeric(4,2), nullable=False)
reorder_point = db.Column(db.Integer, nullable=False)
stock_quantity = db.Column(db.Integer, default=0, nullable=False)
storage_location = db.Column(db.String(24), nullable=False)
batches = db.relationship('ProductBatch', backref='product', lazy=True)
def __init__(self, SKU, name, category, price, reorder_point, storage_location):
self.SKU = SKU
self.name = name
self.category = category
self.price = price
self.reorder_point = reorder_point
self.storage_location = storage_location
def __repr__(self):
return '<Product {}, {}>'.format(self.SKU, self.name)
def update_stock_quantity(self):
total = 0
for batch in self.batches:
total += batch.batch_quantity
self.stock_quantity = int(total)
class Customer(db.Model):
__tablename__ = 'Customer'
customerID = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(64), index=True, unique=True, nullable=False)
name = db.Column(db.String(64), nullable=False)
phoneNum = db.Column(db.String(64))
creditCardID = db.relationship('CreditCard', backref='customer', lazy=True)
addressID = db.relationship('Address', backref='customer', lazy=True)
def __init__(self, email, name):
self.email = email
self.name = name
def __repr__(self):
return '<Customer: {}, customer: {}>'.format(self.customerID,
self.email)
class Guest(db.Model):
__tablename__ = 'guest'
__table_args__ = (db.UniqueConstraint('name', 'arrival', 'apartment_id'),
db.Index('guest_idx', 'name', 'arrival', 'apartment_id'))
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), nullable=False)
arrival = db.Column(db.DateTime, nullable=False)
active = db.Column(db.SmallInteger, default=1)
apartment_id = db.Column(db.Integer,
db.ForeignKey('apartment.id', ondelete='CASCADE'),
nullable=False)
apartment = db.relationship(
'Apartment',
backref=db.backref(
'guests',
lazy=True,
cascade='all, delete',
primaryjoin=
'and_(Guest.apartment_id == Apartment.id, Guest.active == 1)'),
lazy=True,
primaryjoin=
'and_(Guest.apartment_id == Apartment.id, Apartment.active == 1)')
def __repr__(self):
return f'Guest(id={self.id}, name={self.name}, arrival={self.arrival})'
class Employee(db.Model):
__tablename__ = 'employee'
__table_args__ = (
db.UniqueConstraint('cpf', 'role', 'condominium_id'),
db.Index('employee_idx', 'cpf', 'role', 'condominium_id')
)
id = db.Column(db.Integer, primary_key=True)
cpf = db.Column(db.String(11), nullable=False)
name = db.Column(db.String(50), nullable=False)
birthday = db.Column(db.Date, nullable=False)
photo_location = db.Column(db.String(200), nullable=True, default='data/photos/default/employee.png')
role = db.Column(db.String(50), nullable=False)
type = db.Column(db.SmallInteger, nullable=False)
condominium_id = db.Column(db.Integer, db.ForeignKey('condominium.id', ondelete='CASCADE'), nullable=False)
active = db.Column(db.SmallInteger, default=1)
condominium = db.relationship('Condominium',
backref=db.backref('employees', lazy=True, cascade='all, delete'),
lazy=True,
primaryjoin='and_(Employee.condominium_id == Condominium.id, Condominium.active == 1)')
def __repr__(self):
return f'Employee(id={self.id}, ' \
f'cpf={self.cpf}, ' \
f'name={self.name}, ' \
f'birthday={self.birthday}, ' \
f'role={self.role})'
class Department(db.Model):
__tablename__ = 'department'
__table_args__ = {'mysql_engine': 'InnoDB'}
name = db.Column(db.String(64), primary_key=True)
head = db.relationship('Head',
backref='department',
passive_deletes=True,
uselist=False)
class Event(db.Model):
__tablename__ = 'event'
__table_args__ = (db.UniqueConstraint('event_type_id', 'start_datetime',
'end_datetime'),
db.Index('event_idx', 'start_datetime', 'end_datetime'))
id = db.Column(db.Integer, primary_key=True)
start_datetime = db.Column(db.DateTime, nullable=False)
end_datetime = db.Column(db.DateTime, nullable=False)
active = db.Column(db.SmallInteger, default=1)
event_type_id = db.Column(db.Integer,
db.ForeignKey('event_type.id',
ondelete='CASCADE'),
nullable=False)
apartment_id = db.Column(db.Integer,
db.ForeignKey('apartment.id', ondelete='CASCADE'),
nullable=False)
apartment = db.relationship(
'Apartment',
backref=db.backref(
'events',
lazy=True,
cascade='all, delete',
primaryjoin=
'and_(Event.apartment_id == Apartment.id, Event.active == 1)'),
lazy=True,
primaryjoin=
'and_(Event.apartment_id == Apartment.id, Apartment.active == 1)')
event_type = db.relationship(
'EventType',
backref=db.backref(
'events',
lazy=True,
cascade='all, delete',
primaryjoin=
'and_(Event.event_type_id == EventType.id, Event.active == 1)'),
lazy=False)
def __repr__(self):
return f'Event(id={self.id}, ' \
f'start_datetime={self.start_datetime}, ' \
f'end_datetime={self.end_datetime})'
class Room(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(120), unique=True)
description = db.Column(db.String(500))
reservable = db.Column(db.Boolean, nullable=False)
reservations = db.relationship('Reservation',
backref='room',
lazy='dynamic')
def __repr__(self):
return '<Room: %s>' % self.name
class Genre(db.Model):
__tablename__ = 'genre'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(1000), unique=True, nullable=False)
movies = db.relationship('Movie')
def __repr__(self):
return f"Genre('{self.name}')"
@property
def serialize(self):
return {'id': self.id, 'name': self.name}
class Notification(db.Model):
__tablename__ = 'notification'
__table_args__ = (
db.UniqueConstraint('title', 'condominium_id'),
db.Index('date_idx', 'finish_date'),
db.Index('notification_idx', 'title', 'condominium_id')
)
id = db.Column(db.Integer, primary_key=True)
type = db.Column(db.Integer, nullable=False)
title = db.Column(db.String(50), nullable=False)
text = db.Column(db.String(300), nullable=False)
finish_date = db.Column(db.Date, nullable=True)
active = db.Column(db.SmallInteger, default=1)
employee_id = db.Column(db.Integer, db.ForeignKey('employee.id', ondelete='CASCADE'), nullable=False)
condominium_id = db.Column(db.Integer, db.ForeignKey('condominium.id', ondelete='CASCADE'), nullable=False)
author = db.relationship('Employee',
backref=db.backref('notifications',
lazy=True,
cascade='all, delete',
primaryjoin='and_(Notification.employee_id == Employee.id, Notification.active == 1)'),
lazy=True)
condominium = db.relationship('Condominium',
backref=db.backref('notifications',
lazy=True,
cascade='all, delete',
primaryjoin='and_(Notification.condominium_id == Condominium.id, Notification.active == 1)'),
lazy=True,
primaryjoin='and_(Notification.condominium_id == Condominium.id, Condominium.active == 1)')
def __repr__(self):
return f'Notification(id={self.id}, ' \
f'type={self.type}, ' \
f'title={self.title}, ' \
f'text={self.text}, ' \
f'finish_date={self.finish_date})'
class ResidentUser(db.Model):
__tablename__ = 'resident_user'
__table_args__ = (db.UniqueConstraint('apartment_id'),
db.UniqueConstraint('username'))
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(100),
db.ForeignKey('username.username',
ondelete='CASCADE'),
nullable=False)
password = db.Column(db.String(200), nullable=False)
apartment_id = db.Column(db.Integer,
db.ForeignKey('apartment.id', ondelete='CASCADE'),
nullable=False)
apartment = db.relationship(
'Apartment',
backref=db.backref('user',
lazy=True,
cascade='all, delete',
uselist=False),
lazy=True,
uselist=False,
primaryjoin=
'and_(ResidentUser.apartment_id == Apartment.id, Apartment.active == 1)'
)
_username = db.relationship('Username',
backref=db.backref('resident_user',
lazy=True,
cascade='all, delete',
uselist=False),
lazy=True,
uselist=False)
def __repr__(self):
return f'ResidentUser(username={self.username})'
class Actual(db.Model):
#__tablename__ = 'actuals'
id = db.Column(db.Integer, primary_key=True)
temp = db.Column(db.Integer)
rain = db.Column(db.Integer)
date = db.Column(db.Date)
crag_id = db.Column(db.Integer, db.ForeignKey('crag.id'))
crag = db.relationship("Crag", backref=db.backref("actuals", lazy='dynamic'))
def __repr__(self):
return "<Actual(crag=%s, temp=%d, rain=%s, date=%s>" % (
self.crag_id, self.temp, self.rain,
self.date.strftime("%b %d %y"))
class Address(db.Model):
__tablename__ = 'address'
__table_args__ = (db.UniqueConstraint('street_name', 'city_id'), db.Index('address_idx', 'street_name', 'city_id'))
id = db.Column(db.Integer, primary_key=True)
street_name = db.Column(db.String(150), nullable=False)
neighbourhood = db.Column(db.String(150), nullable=False)
city_id = db.Column(db.Integer, db.ForeignKey('city.id', ondelete='CASCADE'), nullable=False)
city = db.relationship('City', backref=db.backref('addresses', lazy=True, cascade='all, delete'), lazy=False)
def __repr__(self):
return f'Address(id={self.id}, ' \
f'street_name={self.street_name}, ' \
f'neighbourhood={self.neighbourhood}, ' \
f'city_name={self.city.name})'
class Tower(db.Model):
__tablename__ = 'tower'
__table_args__ = (db.UniqueConstraint('name', 'condominium_id'), db.Index('tower_idx', 'name', 'condominium_id'))
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(10), nullable=False)
condominium_id = db.Column(db.Integer, db.ForeignKey('condominium.id', ondelete='CASCADE'), nullable=False)
active = db.Column(db.SmallInteger, default=1)
condominium = db.relationship('Condominium',
backref=db.backref('towers', lazy=True, cascade='all, delete'),
lazy=True,
primaryjoin='and_(Tower.condominium_id == Condominium.id, Condominium.active == 1)')
def __repr__(self):
return f'Tower(id={self.id}, ' \
f'tower_name={self.name})'
class Company(db.Model):
__tablename__ = 'company'
c_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
c_name = db.Column(db.String(32))
c_legent = db.Column(db.String(32))
c_employed = db.Column(db.Integer)
c_shacap = db.Column(db.Integer)
c_other = db.Column(db.Text)
c_cdate = db.Column(db.DateTime,
default=datetime.utcnow,
onupdate=datetime.utcnow)
notes = db.relationship('Note',
backref='company',
cascade='all, delete, delete-orphan',
single_parent=True,
order_by='desc(Note.timestamp)')
class Forecast(db.Model):
#__tablename__ = 'forecasts'
id = db.Column(db.Integer, primary_key=True)
service = db.Column(db.String, nullable=False)
crag_id = db.Column(db.Integer, db.ForeignKey('crag.id'))
temp = db.Column(db.Integer)
rain = db.Column(db.Integer)
pred_time = db.Column(db.DateTime)
pred_for = db.Column(db.Date)
crag = db.relationship("Crag", backref=db.backref("forecasts", lazy='dynamic'))
def __repr__(self):
return "<Forecast(service=%s, crag=%s, temp=%d, rain=%d, pred_time=%s, pred_for=%s)>" % (
self.service, self.crag_id, self.temp,
self.rain, self.pred_time.strftime("%b %d %y, %H:%M"),
self.pred_for.strftime("%b %d %y"))
class State(db.Model):
__tablename__ = 'state'
__table_args__ = (db.UniqueConstraint('name', 'country_id'),
db.Index('state_idx', 'name', 'country_id'))
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60), nullable=False)
country_id = db.Column(db.Integer,
db.ForeignKey('country.id', ondelete='CASCADE'),
nullable=False)
country = db.relationship('Country',
backref=db.backref('states',
lazy=True,
cascade='all, delete'),
lazy=True)
def __repr__(self):
return f'State(id={self.id}, name={self.name})'
class User(db.Model, UserMixin):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(50), nullable=False)
email = db.Column(db.String(50), unique=True, nullable=False)
profile_pic = db.Column(db.String(1000),
nullable=False,
default='op_profile_default.png')
movies = db.relationship('Movie', backref='author', lazy=True)
def __repr__(self):
return f"User('{self.username}', '{self.email}', '{self.movies}')"
@property
def serialize(self):
return {
'id': self.id,
'uesrname': self.username,
'email': self.email,
}
class User(db.Model):
# columns
# __tablename__ = "users" # to get old users
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
role_id = db.Column(
db.Integer,
nullable=False) # 1 for user, 2 for user manager and 3 for admin
expected_calories = db.Column(db.Integer, nullable=False)
password = db.Column(db.String(60), nullable=False)
# password = db.Column(db.String(60), nullable=True)
# hashing algorithm will make this string as 60 char long
records = db.relationship('Record', backref='owner', lazy=True)
# backref is similar to adding another column
# Magic Method: How object is printed when we print it (also __scr__)
def __repr__(self):
return f"User('{self.id}, {self.name}, {self.email}, {self.role_id}')"
class EventType(db.Model):
__tablename__ = 'event_type'
__table_args__ = (db.UniqueConstraint('name', 'condominium_id'), )
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), nullable=False, index=True)
condominium_id = db.Column(db.Integer,
db.ForeignKey('condominium.id',
ondelete='CASCADE'),
nullable=False)
condominium = db.relationship(
'Condominium',
backref=db.backref('event_types', lazy=True, cascade='all, delete'),
lazy=True,
primaryjoin=
'and_(EventType.condominium_id == Condominium.id, Condominium.active == 1)'
)
def __repr__(self):
return f'EventType(id={self.id}, name={self.name})'
class Apartment(db.Model):
__tablename__ = 'apartment'
__table_args__ = (db.UniqueConstraint('apt_number', 'tower_id'),
db.Index('apt_idx', 'apt_number', 'tower_id'))
id = db.Column(db.Integer, primary_key=True)
apt_number = db.Column(db.Integer, nullable=False)
tower_id = db.Column(db.Integer,
db.ForeignKey('tower.id', ondelete='CASCADE'),
nullable=False)
active = db.Column(db.SmallInteger, default=1)
tower = db.relationship(
'Tower',
backref=db.backref('apartments', lazy=True, cascade='all, delete'),
lazy=True,
primaryjoin='and_(Apartment.tower_id == Tower.id, Tower.active == 1)')
def __repr__(self):
return f'Apartment(id={self.id},' \
f'apt_number={self.apt_number})'
class Condominium(db.Model):
__tablename__ = 'condominium'
__table_args__ = (db.UniqueConstraint('street_number', 'address_id'), db.Index('cond_idx', 'street_number', 'address_id'))
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60), nullable=False)
street_number = db.Column(db.Integer, nullable=False)
photo_location = db.Column(db.String(200), nullable=True, default='data/photos/default/condominium.png')
address_id = db.Column(db.Integer, db.ForeignKey('address.id', ondelete='CASCADE'), nullable=False)
active = db.Column(db.SmallInteger, default=1)
address = db.relationship('Address',
backref=db.backref('condominiums',
lazy=True,
cascade='all, delete',
primaryjoin='and_(Address.id == Condominium.address_id, Condominium.active == 1)'),
lazy=True,
primaryjoin='and_(Condominium.address_id == Address.id, Condominium.active == 1)')
def __repr__(self):
return f'Condominium(id={self.id}, ' \
f'condominium_name={self.name})'
class User(db.Model, UserMixin):
id = db.Column(db.String(8), primary_key=True) # Andrew ID
username = db.Column(db.String(100),
nullable=False) # required for Social Flask
admin = db.Column(db.Boolean, default=False, nullable=False)
banned = db.Column(db.Boolean, default=False, nullable=False)
last_login = db.Column(db.DateTime, default=func.now(), nullable=False)
reservations = db.relationship('Reservation',
backref='user',
lazy='dynamic')
def __init__(self, *args, **kwargs):
self.id = kwargs.get('id', kwargs.get('email', '').split('@')[0])
self.username = self.id
self.admin = kwargs.get('admin', self.id in admins)
self.banned = kwargs.get('banned', False)
def email(self, template='*****@*****.**'):
return template % id
def __repr__(self):
return '<User: %s>' % self.id
class Artist(db.Model):
__tablename__ = 'artist'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
city = db.Column(db.String(120))
state = db.Column(db.String(120))
phone = db.Column(db.String(120))
genres = db.Column(db.String(120))
image_link = db.Column(db.String(500))
facebook_link = db.Column(db.String(120))
website = db.Column(db.String(120))
seeking_venue = db.Column(db.Boolean, default=False)
seeking_description = db.Column(db.String)
# TODO: implement any missing fields, as a database migration using Flask-Migrate
shows = db.relationship('Show',
backref=db.backref('artist', lazy='select'),
cascade="all, delete",
lazy='dynamic')
def __repr__(self):
return f'<Artist [{ self.id }]: { self.name }, { self.city }, { self.state }, { self.phone }, { self.facebook_link }>'
