class Space(db.Model):
__tablename__ = 'space'
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(100), nullable=False)
capacity = db.Column(db.Integer(), nullable=False)
created_at = db.Column(db.DateTime(), nullable=False, server_default=db.func.now())
updated_at = db.Column(db.DateTime(), nullable=False, server_default=db.func.now(), onupdate=db.func.now())
@classmethod
def get_by_id(cls, space_id):
return cls.query.filter_by(id=space_id).first()
@classmethod
def get_by_capacity(cls, space_capacity):
return cls.query.filter_by(capacity=space_capacity).all()
def save(self):
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
@classmethod
def get_all_spaces(cls):
return cls.query.all()
class User(db.Model):
__tablename__ = 'userdetails'
__bind_key__ = 'db1'
userId = db.Column(db.Integer, primary_key=True)
password = db.Column(db.String(154))
username = db.Column(db.String(154))
name = db.Column(db.String(154))
usertype = db.Column(db.String(54))
#is_staff = db.Column(db.Boolean, nullable=False, default=False)
isAdmin = db.Column(db.Boolean, nullable=False, default=False)
confirmed = db.Column(db.Boolean, nullable=False, default=True)
created_time = db.Column(db.String(154))
usertype_id = db.Column(db.Integer())
city = db.Column(db.String(154))
state = db.Column(db.String(154))
country = db.Column(db.String(154))
zipcode = db.Column(db.String(154))
trailexpired = db.Column(db.Integer())
def is_authenticated(self):
return True
def is_active(self):
return True
def is_anonymous(self):
return False
def get_id(self):
try:
return unicode(self.userId) # python 2
except NameError:
return str(self.userId) # python 3
class Sentence(db.Model):
__tablename__ = 'sentence'
id = db.Column(db.Integer(), primary_key=True)
text = db.Column(db.String())
sentiment = db.Column(db.Float())
status = db.Column(db.String()) #CONTEXT, SENTIMENT, PRED, DONE
date = db.Column(db.String())
time = db.Column(db.String())
article_id = db.Column(db.Integer(), db.ForeignKey('article.id'))
context = db.Column(db.String(), db.ForeignKey("company.stock_code"))
def __init__(self, text, article_id, date, time):
#self.id = id
self.text = text
self.article_id = article_id
self.status = "CONTEXT"
self.date = self.time = datetime.strptime(date, '%m/%d/%Y')
self.time = datetime.strptime(time, '%H:%M:%S')
def __repr__(self):
return '<id {}>'.format(self.id)
def serialize(self):
return {
'id': self.id,
'text': self.text,
'sentiment': self.sentiment,
'status': self.status,
'article_id': self.article_id,
'date': self.date.strftime("%m/%d/%Y"),
'time': self.time.strftime("%H:%M:%S"),
'context': self.context
}
class Notification(db.Model):
__tablename__ = 'notifications'
id = db.Column(db.Integer(), primary_key=True)
idUser = db.Column(db.Integer(), db.ForeignKey('users.id', ondelete='CASCADE'))
message = db.Column(db.String(255), nullable=False)
msgType = db.Column(db.INTEGER(), nullable=False)
msgDate = db.Column(db.DATE(), nullable=False)
class Reservation(db.Model):
__tablename__ = 'reservation'
id = db.Column(db.Integer(), primary_key=True)
time = db.Column(db.Date(), nullable=False)
user_id = db.Column(db.Integer())
space_id = db.Column(db.Integer(), nullable=False)
created_at = db.Column(db.DateTime(),
nullable=False,
server_default=db.func.now())
@classmethod
def get_all_reservations(cls):
return cls.query.all()
@classmethod
def get_by_id(cls, reservation_id):
return cls.query.filter_by(id=reservation_id).first()
def save(self):
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
@classmethod
def get_all_by_user_id(cls, user_id):
return cls.query.filter_by(user_id=user_id).all()
@classmethod
def get_all_by_space_id(cls, space_id):
return cls.query.filter_by(space_id=space_id).all()
class Reservation(db.Model):
__tablename__ = 'reservation'
id = db.Column(db.Integer, primary_key=True)
client_id = db.Column(db.Integer(), db.ForeignKey("client.id"))
workspace_id = db.Column(db.Integer(), db.ForeignKey("workspace.id"))
created_at = db.Column(db.DateTime(), nullable=False, server_default=db.func.now())
updated_at = db.Column(db.DateTime(), nullable=False, server_default=db.func.now(), onupdate=db.func.now())
@classmethod
def get_all_by_client(cls, client_id):
return cls.query.filter_by(client_id=client_id).all()
@classmethod
def get_by_id(cls, reservation_id):
return cls.query.filter_by(id=reservation_id).first()
@classmethod
def get_by_client(cls, client_id):
return cls.query.filter_by(client_id=client_id).first()
@classmethod
def get_by_workspace(cls, workspace_id):
return cls.query.filter_by(workspace_id=workspace_id).first()
def save(self):
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
class FeatureParticipation(ModelBase):
__tablename__ = 'feature_participation'
user_id = db.Column(db.Integer(), db.ForeignKey('app_user.id', onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True)
feature_id = db.Column(db.Integer(), db.ForeignKey('feature.id', onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True)
def __init__(self, user_id: int, feature_id: int):
if not isinstance(user_id, int):
raise expected('user_id', user_id, int)
if not isinstance(feature_id, int):
raise expected('feature_id', feature_id, int)
self.user_id = user_id
self.feature_id = feature_id
@staticmethod
def create(user: AppUser, feature: Feature) -> 'Optional[FeatureParticipation]':
participation = FeatureParticipation(user.id, feature.id)
db.session.add(participation)
return participation
@staticmethod
def get_for_user(user: AppUser, feature: Feature) -> 'Optional[FeatureParticipation]':
return FeatureParticipation.query.filter_by(user_id=user.id, feature_id=feature.id).first()
def __hash__(self):
return hash((self.user_id, self.feature_id))
class Reservation(db.Model):
__tablename__ = 'reservation'
id = db.Column(db.Integer, primary_key=True) # generoitu
user_id = db.Column(db.Integer(), db.ForeignKey("user.id")) # varauksen tekijän id
workspace_id = db.Column(db.Integer(), db.ForeignKey("workspace.id")) # työtilan id
# default lähinnä testinä, todellisuudessa ei tietty voi varata tilaa ilman aikaa
start_time = db.Column(db.DateTime(), nullable=False, server_default=db.func.now()) # aloitusaika
# defaultend = db.func.now() + timedelta(hours=1)
# server_default= defaultend
# ilmesesti sqalkemian timestamp-hässäkkä ei ole yhteensopiva pythonin oman timestamp-hässäkän kanssa
end_time = db.Column(db.DateTime(), nullable=False, server_default=db.func.now()) # lopetusaika
created_at = db.Column(db.DateTime(), nullable=False, server_default=db.func.now())
updated_at = db.Column(db.DateTime(), nullable=False, server_default=db.func.now(), onupdate=db.func.now())
@classmethod
def get_by_id(cls, id):
return cls.query.filter_by(username=id).first()
@classmethod
def get_by_user_id(cls, user_id):
return cls.query.filter_by(email=user_id).first()
def save(self):
db.session.add(self)
db.session.commit()
class ClosingDays(ModelBase):
__tablename__ = 'closing_days'
id = db.Column(db.Integer(), primary_key=True, autoincrement=True)
campus_id = db.Column(db.Integer(), db.ForeignKey('campus.id'), nullable=False)
first_day = db.Column(db.Date(), nullable=False)
last_day = db.Column(db.Date(), nullable=True, server_default=None)
translatable_id = db.Column(db.Integer(), db.ForeignKey('translatable.id', onupdate='CASCADE', ondelete='RESTRICT'),
nullable=False)
def __init__(self, campus_id: int, first_day: datetime.date, last_day: datetime.date,
translatable_id: int):
if not isinstance(campus_id, int):
raise expected('campus_id', campus_id, int)
if not isinstance(first_day, datetime.date):
raise expected('first_day', first_day, datetime.date)
if last_day is not None and not isinstance(last_day, datetime.date):
raise expected_or_none('last_day', last_day, datetime.date)
if not isinstance(translatable_id, int):
raise expected('translatable_id', translatable_id, int)
self.campus_id = campus_id
self.first_day = first_day
self.last_day = last_day
self.translatable_id = translatable_id
@staticmethod
def create(campus: Campus, first_day: datetime.date, last_day: Optional[datetime.date], reason: str, language: str,
add_to_db=True) -> 'ClosingDays':
translatable, translation = Translatable.get_or_create(reason, language)
result = ClosingDays(campus.id, first_day, last_day, translatable.id)
if add_to_db:
db.session.add(result)
return result
@staticmethod
def find_is_closed(campus: Campus, day: datetime.date) -> 'Optional[ClosingDays]':
return ClosingDays.query.filter(db.and_(ClosingDays.campus_id == campus.id,
ClosingDays.first_day <= day,
db.or_(
ClosingDays.last_day == None,
ClosingDays.last_day >= day
)
)).first()
@staticmethod
def find_closing_days_including(campus: Campus,
start_date: datetime.date,
end_date: datetime.date) -> 'List[ClosingDays]':
return ClosingDays.query.filter(db.and_(ClosingDays.campus_id == campus.id,
ClosingDays.first_day <= end_date,
db.or_(
ClosingDays.last_day == None,
ClosingDays.last_day >= start_date
)
)).all()
class Product(db.Model):
__tablename__ = 'products'
productID = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(64), index=True)
description = db.Column(db.String(120))
price = db.Column(db.Numeric(scale=2))
is_available = db.Column(db.Boolean(), default=True)
supplier_ID = db.Column(db.Integer(), ForeignKey('suppliers.supplierID'))
class Supplier(db.Model):
__tablename__ = 'suppliers'
supplierID = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(128), index=True)
phone = db.Column(db.BigInteger(), nullable=False)
email = db.Column(db.String(255), unique=True, index=True)
customer_ID = db.Column(db.Integer(),
db.ForeignKey('customers.customerID'))
class Sale(BaseDataModel):
__tablename__ = 'sale'
description = db.Column(db.String(128))
price = db.Column(db.Float(), nullable=False)
currency = db.Column(db.Integer(),
db.ForeignKey('currency.id', ondelete='NO ACTION'),
default=1)
reservationid = db.Column(
db.Integer(), db.ForeignKey('reservation.id', ondelete='NO ACTION'))
class HotelEmployee(BaseModel):
__tablename__ = 'hotel_employee'
hotelrefno = db.Column(db.Integer(),
db.ForeignKey('hotel.hotelrefno',
ondelete='CASCADE'),
nullable=False)
employeeid = db.Column(db.Integer(),
db.ForeignKey('employee.id', ondelete='CASCADE'),
nullable=False)
class ParkingSpot(db.Model):
__tablename__ = 'parking_spots'
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255), nullable=False)
city = db.Column(db.String(255), nullable=False)
address = db.Column(db.String(255), nullable=False)
available = db.Column(db.Boolean(), nullable=False, default=False)
idUser = db.Column(db.Integer(),
db.ForeignKey('users.id', ondelete='CASCADE'))
class TimeOff(db.Model):
__tablename__ = 'time_offs'
id = db.Column(db.Integer(), primary_key=True)
startDate = db.Column(db.Date(), nullable=False,
default=datetime.today().strftime('%Y-%m-%d'))
endDate = db.Column(db.Date(), nullable=False,
default=datetime.today().strftime('%Y-%m-%d'))
idParkingSpot = db.Column(db.Integer(),
db.ForeignKey('parking_spots.id', ondelete='CASCADE'))
class AuthorizationCode(db.Model, OAuth2AuthorizationCodeMixin):
__tablename__ = 'oauth2_code'
id = db.Column(db.Integer(), primary_key=True)
client_id = db.Column(
db.String(128),
db.ForeignKey('oauth2_client.client_id', ondelete='CASCADE'))
user_id = db.Column(db.Integer(),
db.ForeignKey('users.id', ondelete='CASCADE'))
class Client(db.Model, OAuth2ClientMixin):
__tablename__ = 'oauth2_client'
id = db.Column(db.Integer(), primary_key=True)
user_id = db.Column(db.Integer(),
db.ForeignKey('users.id', ondelete='CASCADE'))
payment_agreements = db.relationship('PaymentAgreement',
backref='client',
lazy='dynamic')
class QuestionTags(Dictifiable, db.Model):
question_id = db.Column(db.Integer(),
db.ForeignKey('question.id', ondelete='CASCADE'),
primary_key=True)
tag_id = db.Column(db.Integer(),
db.ForeignKey('tag.id', ondelete='CASCADE'),
primary_key=True)
question = db.relationship('Question', backref='question_tags')
tag = db.relationship('Tag', backref='question_tags')
class RoomType(BaseDataModel):
__tablename__ = 'roomtype'
description = db.Column(db.String(128))
bedcount = db.Column(db.Integer())
# bedtype
# roomstate
currency = db.Column(db.Integer(),
db.ForeignKey('currency.id', ondelete='NO ACTION'),
default=1)
price = db.Column(db.Float(), nullable=False)
pricechd = db.Column(db.Float(), nullable=False)
class QuestionAllowedLanguages(Dictifiable, db.Model):
language_id = db.Column(db.Integer(),
db.ForeignKey('coding_language.id'),
primary_key=True)
question_id = db.Column(db.Integer(),
db.ForeignKey('question.id'),
primary_key=True)
question = db.relationship('Question',
backref='question_allowed_languages')
class Forum(DB.Model):
__tablename__ = "forums"
__table_args__ = {'extend_existing': True}
id = DB.Column(DB.Integer(), primary_key=True)
title = DB.Column(DB.String(255), nullable=False)
creator_id = DB.Column(DB.Integer(), DB.ForeignKey("users.id"))
date = DB.Column(DB.DateTime(),
default=datetime.datetime.now(),
onupdate=datetime.datetime.now(),
nullable=False)
def __repr__(self):
return f"forum called {self.title}"
class Reservation_A(db.Model):
__tablename__ = 'RoomA_Booking'
Room_A_id = db.Column(db.Integer, primary_key=True)
Room_A_name = db.Column(db.String(100), default='Room A')
Room_A_is_reserved = db.Column(db.Boolean(), default=False)
#Room_A_date = db.Column(db.DateTime(), nullable=False)
#ToDo : Check the type of the data in the following statement
Room_A_start_time = db.Column(db.String(20), nullable=False)
Room_A_end_time = db.Column(db.String(20), nullable=False)
Room_A_teacher_id = db.Column(db.Integer(), db.ForeignKey("teacher.teacher_id"))
Room_A_student_id = db.Column(db.Integer(), db.ForeignKey("student.student_id"))
def data(self):
return{
'Room_A_id': self.Room_A_id,
'Room_A_name': self.Room_A_name,
'Room_A_is_reserved': self.Room_A_is_reserved,
## TODO: CHeck if we can overcome date field serialization
#'Room_A_date': self.Room_A_date,
'Room_A_start_time': self.Room_A_start_time,
'Room_A_end_time': self.Room_A_end_time,
'Room_A_teacher_id': self.Room_A_teacher_id,
'Room_A_student_id': self.Room_A_student_id
}
@classmethod
### checking all the reservations of the rooms A done by the user
### checking all the reservations of the rooms A done by the user
def get_all_reserved(cls):
return cls.query.filter_by(Room_A_is_reserved=True).all()
@classmethod
##checking all the rooms with no reservation
def get_all_not_reserved(cls):
return cls.query.filter_by(Room_A_is_reserved=False).all()
@classmethod
### getting the reservations of the room by id
def get_by_id(cls, Room_A_id):
return cls.query.filter_by(Room_A_id=Room_A_id).first()
def save(self):
db.session.add(self)
db.session.commit()
class ForumComment(DB.Model):
__tablename__ = "forum_comments"
__table_args__ = {'extend_existing': True}
id = DB.Column(DB.Integer(), primary_key=True)
text = DB.Column(DB.Text(), nullable=False)
likes = DB.Column(DB.Integer())
user_id = DB.Column(DB.Integer(), DB.ForeignKey("users.id"))
forum_id = DB.Column(DB.Integer(), DB.ForeignKey("articles.id"))
date = DB.Column(DB.DateTime(),
default=datetime.datetime.now(),
onupdate=datetime.datetime.now(),
nullable=False)
def __repr__(self):
return f"ArticleComment with author_id: {self.user_id}, article_id: {self.article_id}, text: {self.text}"
class Transaction(db.Model):
__tablename__ = 'transactions'
transaction_id = db.Column(db.String(128), primary_key=True)
user_identifier = db.Column(
db.String(128), db.ForeignKey('users.identifier', ondelete='CASCADE'))
payment_amount = db.Column(db.Integer(), default=0)
payment_address = db.Column(db.String(128), nullable=True)
timestamp = db.Column(db.Integer(), nullable=False)
payment_agreement_id = db.Column(db.Integer(),
db.ForeignKey('payment_agreement.id'))
def __repr__(self):
return '<Transaction ID: {}>'.format(self.transaction_id)
class OrderDetails(db.Model):
__tablename__ = 'order_details'
orderDetailsID = db.Column(db.Integer(), primary_key=True)
item = db.Column(db.String(64))
name = db.Column(db.String(215), index=True)
dob = db.Column(db.String(32), index=True)
height = db.Column(db.Numeric(scale=2))
weight = db.Column(db.Numeric(scale=2))
message = db.Column(db.String(300))
is_message = db.Column(db.Boolean(), default=False)
order_ID = db.Column(db.Integer(), db.ForeignKey('orders.orderID'))
product_ID = db.Column(db.Integer(), db.ForeignKey('products.productID'))
shipper_ID = db.Column(db.Integer(), db.ForeignKey('shippers.shipperID'))
customer_ID = db.Column(db.Integer(),
db.ForeignKey('customers.customerID'))
class UserDayCampusPreference(ModelBase):
__tablename__ = 'user_day_campus_preference'
user_id = db.Column(db.Integer(), db.ForeignKey('app_user.id', onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True)
day = db.Column(db.Enum(Day), primary_key=True)
campus_id = db.Column(db.Integer(), db.ForeignKey('campus.id', onupdate='CASCADE', ondelete='CASCADE'),
nullable=False)
# FIXME: Move this out of this table and instead store this in some subscription info table for daily_menu channel
active = db.Column(db.Boolean(), default=True, nullable=False)
def __init__(self, user_id: int, day: Day, campus_id: int, active=True) -> None:
if not isinstance(user_id, int):
raise expected('user_id', user_id, int)
if not isinstance(day, Day):
raise expected('day', day, Day)
if not isinstance(campus_id, int):
raise expected('campus_id', campus_id, int)
if not isinstance(active, bool):
raise expected('active', active, bool)
self.user_id = user_id
self.day = day
self.campus_id = campus_id
self.active = active
@staticmethod
def get_all_for_user(user: '******') -> 'List[UserDayCampusPreference]':
return UserDayCampusPreference.query.filter_by(user_id=user.id).all()
@staticmethod
def get_for_user(user: '******', day: Day) -> 'Optional[UserDayCampusPreference]':
return UserDayCampusPreference.query.filter_by(user_id=user.id, day=day).first()
@staticmethod
def create(user: '******', day: Day, campus: Campus, active=True) -> 'Optional[UserDayCampusPreference]':
if day in [Day.SATURDAY, Day.SUNDAY]:
raise ValueError('Day cannot be SATURDAY or SUNDAY')
subscription = UserDayCampusPreference(user.id, day, campus.id, active)
db.session.add(subscription)
return subscription
def __hash__(self):
return hash((self.user_id, self.day))
class Workspace(db.Model):
__tablename__ = 'workspace'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String())
size = db.Column(db.Integer())
created_at = db.Column(db.DateTime(),
nullable=False,
server_default=db.func.now())
updated_at = db.Column(db.DateTime(),
nullable=False,
server_default=db.func.now(),
onupdate=db.func.now())
def save(self):
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
@classmethod
def get_by_name(cls, name):
return cls.query.filter_by(name=name).first()
@classmethod
def get_by_id(cls, id):
return cls.query.filter_by(id=id).first()
@classmethod
def get_all(cls):
return cls.query.all()
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer(), primary_key=True)
username = db.Column(db.String(80), nullable=False, unique=True)
email = db.Column(db.String(200), nullable=False, unique=True)
password = db.Column(db.String())
role = db.Column(db.String(200), nullable=False)
is_active = db.Column(db.Boolean(), default=False)
created_at = db.Column(db.DateTime(),
nullable=False,
server_default=db.func.now())
updated_at = db.Column(db.DateTime(),
nullable=False,
server_default=db.func.now(),
onupdate=db.func.now())
@classmethod
def get_by_username(cls, username):
return cls.query.filter_by(username=username).first()
@classmethod
def get_by_email(cls, email):
return cls.query.filter_by(email=email).first()
@classmethod
def get_by_id(cls, id):
return cls.query.filter_by(id=id).first()
def save(self):
db.session.add(self)
db.session.commit()
class Article(DB.Model):
__tablename__ = "articles"
__table_args__ = {'extend_existing': True}
id = DB.Column(DB.Integer(), primary_key=True)
title = DB.Column(DB.String(255), nullable=False)
user_id = DB.Column(DB.Integer(), DB.ForeignKey("users.id"))
author_name = DB.Column(DB.String(255), nullable=False)
date = DB.Column(DB.DateTime(),
default=datetime.datetime.now(),
onupdate=datetime.datetime.now(),
nullable=False)
text = DB.Column(DB.Text(), nullable=False)
likes = DB.Column(DB.Integer(), default=0)
def __repr__(self):
return f"Article \"{self.title}\" ..."
class Translation(ModelBase):
__tablename__ = 'translation'
translatable_id = db.Column(db.Integer(), db.ForeignKey('translatable.id', onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True)
language = db.Column(db.String(5), primary_key=True)
translation = db.Column(db.String(256), nullable=False)
provider = db.Column(db.String(16))
def __init__(self, translatable_id: int, language: str, translation: str, provider: str = None):
if not isinstance(translatable_id, int):
raise expected('translatable_id', translatable_id, int)
if not isinstance(language, str):
raise expected('language', language, str)
if not isinstance(translation, str):
raise expected('translation', translation, str)
if provider is not None and not isinstance(provider, str):
raise expected_or_none('provider', provider, str)
self.translatable_id = translatable_id
self.language = language
self.translation = translation
self.provider = provider
def __eq__(self, other: 'Translation'):
if self.translatable_id != other.translatable_id:
return False
if self.language != other.language:
return False
if self.translation != other.translation:
return False
return True
def __hash__(self):
return hash((self.translatable_id, self.language))