class MessageLog(db.Model):
__tablename__ = 'message_log'
id = db.Column(db.BigInteger, primary_key=True)
email = db.Column(db.String(255), index=True)
msg_purpose = db.Column(db.String(128), index=True)
msg_subject = db.Column(db.String(128))
msg_text = db.Column(db.Text())
msg_html = db.Column(db.Text())
msg_sent = db.Column(db.DateTime(timezone=True), server_default=db.func.now())
class Page(CMSModel):
"""Model representing a single CMS page"""
columns = ('title', 'address', 'content')
address = db.Column(db.String(256),
unique=True,
index=True,
nullable=False,
default='index')
title = db.Column(db.String(256))
content = db.Column(db.Text())
@validates('address')
def validate_address(self, key, address):
if '//' in address or address.endswith('/') or address.startswith('/'):
raise ValidationError(
'Address cannot contain neither consecutive nor trailing slashes'
)
pages = Page.query.filter_by(address=address).all()
if pages:
# ignore if it is the current page:
if len(pages) == 1 and pages[0] == self:
return address
raise ValidationError('Page with address: "' + address +
'" already exists.')
return address
@property
def url(self):
"""A URL-like identifier ready to be used in HTML <a> tag"""
return '/' + self.address + '/'
def __repr__(self):
return '<Page /{0} with id {1}>'.format(self.address, self.id)
class ProductModel(db.Model):
__tablename__ = 'products'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), nullable=False, unique=True)
description = db.Column(db.Text(), nullable=False)
price = db.Column(db.Float(precision=2), nullable=False)
categories = db.relationship('ProductCategory', lazy=True)
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_name(cls, name):
return cls.query.filter_by(name=name).first()
@classmethod
def get_all_products(cls):
return cls.query.all()
def __repr__(self):
return f"<Product {self.name} >"
class Ticket(db.Model):
ticket_id = db.Column(db.Integer, primary_key=True)
movie_name = db.Column(db.Text(), nullable=False)
ticket_number = db.Column(db.Integer, nullable=False)
def __repr__(self):
return "<Ticket {} {} {}>".format(self.ticket_id, self.movie_name, self.ticket_number)
class Touch_store(db.Model):
__tablename__ = 'touchs'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(300))
message = db.Column(db.Text())
hsize = db.Column(db.String(100))
hcolor = db.Column(db.String(100))
hbg = db.Column(db.String(100))
tsize = db.Column(db.String(100))
tcolor = db.Column(db.String(100))
msize = db.Column(db.String(100))
mcolor = db.Column(db.String(100))
mbg = db.Column(db.String(100))
audio = db.Column(db.String(100))
hfont = db.Column(db.String(100))
mfont = db.Column(db.String(100))
tfont = db.Column(db.String(100))
mduration = db.Column(db.String(100))
bgcolor = db.Column(db.String(100))
p = db.Column(db.Boolean)
n = db.Column(db.Boolean)
tmp = db.Column(db.Integer)
akey = db.Column(db.Integer, db.ForeignKey("media.id",
ondelete='cascade'),
nullable=True)
ikey = db.Column(db.Integer, db.ForeignKey("media.id",
ondelete='cascade'),
nullable=True)
def __init__(self, id=0, title="Please select a task to pull a tick for",
hsize="500%", hcolor="rgb(129, 200, 139)",
hbg="rgba(0, 0, 0, 0.50)", tsize="400%",
mbg="rgba(0, 0, 0, 0.50)", msize="400%",
mcolor="rgb(255, 255, 0)", tcolor="btn-danger",
message="Ticket has been issued, pleas wait your turn",
audio="bell_sound.wav", hfont="El Messiri", mfont="Mada",
tfont="Amiri", ikey=1, tmp=2, akey=5,
mduration="3000", bgcolor="bg_dark.jpg", p=False, n=True):
self.id = 0
self.hfont = hfont
self.mfont = mfont
self.tfont = tfont
self.mduration = mduration
self.title = title
self.message = message
self.hsize = hsize
self.hcolor = hcolor
self.hbg = hbg
self.tsize = tsize
self.tcolor = tcolor
self.msize = msize
self.mcolor = mcolor
self.mbg = mbg
self.audio = audio
self.bgcolor = bgcolor
self.ikey = ikey
self.akey = akey
self.p = p
self.n = n
self.tmp = tmp
class Article(CustomModelMixin, Model):
__tablename__ = 'articles'
title = Column(db.String(100), nullable=False)
slug = Column(db.String(100), nullable=False, unique=True)
summary = Column(db.Text(), nullable=False)
image = Column(db.Text, nullable=True)
content = Column(db.Text, nullable=False)
is_published = Column(db.Boolean, default=True)
view_count = Column(db.Integer, default=0)
created_by_id = reference_col("user", nullable=True)
date_created = Column(db.DateTime,
nullable=False,
default=dt.datetime.utcnow)
tags = relationship("Tag", secondary=articles_tags, backref='posts')
created_by = relationship("User")
def __repr__(self):
"""Represent instance as a string."""
return self.title
def publish(self):
self.is_published = True
return self.save()
def archive(self):
self.is_published = False
return self.save()
class UploadedFiles(db.Model):
__tablename__ = 'uploaded_files'
id = db.Column(db.Integer, primary_key=True)
original_name = db.Column(db.Text(), nullable=False)
extension = db.Column(db.String(64), nullable=False)
hash = db.Column(db.String(128), unique=True)
filename = db.Column(db.String(140), nullable=False)
size = db.Column(db.String(20))
original_uploader = db.Column(db.Integer, nullable=False)
#user = db.relationship('Users', secondary='file_access', lazy='subquery', backref=db.backref('files', lazy=True)),
upload_timestamp = db.Column(db.DateTime(timezone=True))
parameters = db.Column(db.Text())
remark = db.Column(db.Text(), nullable=False)
def __init__(self,
original_name,
hash,
original_uploader,
extension='',
size='',
parameters='',
remark='',
upload_timestamp=None):
self.original_name = original_name
self.hash = hash
self.extension = extension
self.original_uploader = original_uploader
self.parameters = parameters
self.remark = remark
self.size = size
tz = get_localzone()
if upload_timestamp is None:
upload_timestamp = tz.localize(datetime.now(), is_dst=None)
upload_timestamp = upload_timestamp.astimezone(pytz.UTC)
if upload_timestamp.tzinfo is None:
upload_timestamp = pytz.utc.localize(upload_timestamp, is_dst=None)
self.upload_timestamp = upload_timestamp
self.filename = hash + extension
def __repr__(self):
return '<Upload {id}>'.format(id=self.id)
class Setting(CMSModel):
name = db.Column(db.String(256), nullable=False, unique=True, index=True)
value = db.Column(db.Text())
@property
def int_value(self):
return int(self.value)
class TaskModel(db.Model):
__tablename__ = 'task'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
date = db.Column(db.Text())
title = db.Column(db.Text())
content = db.Column(db.Text())
commit = db.Column(db.Integer())
createTime = db.Column(db.DateTime, nullable=False, default=datetime.now)
updateTime = db.Column(db.DateTime, nullable=False, default=datetime.now, onupdate=datetime.now)
def __init__(self, title, content):
self.title = title
self.content = content
def __repr__(self):
return '<TaskModel ' + str(self.id) + ':' + self.title + '>'
class API3(db.Model):
id = db.Column(db.Integer, primary_key=True)
updated = db.Column(db.String(256))
cases = db.Column(db.String(256))
todayCases = db.Column(db.String(256))
deaths = db.Column(db.String(256))
todayDeaths = db.Column(db.String(256))
recovered = db.Column(db.String(256))
todayRecovered = db.Column(db.String(256))
active = db.Column(db.String(256))
critical = db.Column(db.String(256))
casesPerOneMillion = db.Column(db.String(256))
deathsPerOneMillion = db.Column(db.String(256))
tests = db.Column(db.String(256))
testsPerOneMillion = db.Column(db.String(256))
population = db.Column(db.String(256))
continent = db.Column(db.String(40), unique=True)
activePerOneMillion = db.Column(db.String(256))
recoveredPerOneMillion = db.Column(db.String(256))
criticalPerOneMillion = db.Column(db.String(256))
countries = db.Column(db.Text(20000))
def __init__(self, updated, cases, todayCases, deaths, todayDeaths,
recovered, todayRecovered, active, critical,
casesPerOneMillion, deathsPerOneMillion, tests,
testsPerOneMillion, population, continent,
activePerOneMillion, recoveredPerOneMillion,
criticalPerOneMillion, countries):
self.updated = updated
self.cases = cases
self.todayCases = todayCases
self.deaths = deaths
self.todayDeaths = todayDeaths
self.recovered = recovered
self.todayRecovered = todayRecovered
self.active = active
self.critical = critical
self.casesPerOneMillion = casesPerOneMillion
self.deathsPerOneMillion = deathsPerOneMillion
self.tests = tests
self.testsPerOneMillion = testsPerOneMillion
self.population = population
self.continent = continent
self.activePerOneMillion = activePerOneMillion
self.recoveredPerOneMillion = recoveredPerOneMillion
self.criticalPerOneMillion = criticalPerOneMillion
self.countries = countries
def __repr__(self):
return '<Continent %r>' % self.continent
def as_dict(self):
dictionary = {
c.name: getattr(self, c.name)
for c in self.__table__.columns
}
return dictionary
class Person(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text(), nullable=False)
# 'Payment' is upper cased because we are looking for the class
payments = db.relationship('Payment', backref='payer')
bookings = db.relationship('Ticket', secondary=booking_table, backref='buyer', lazy='dynamic')
def __repr__(self):
return "<Person {}: {}>".format(self.id, self.name)
class GoodNumber(db.Model):
__tablename__ = "t_good_number"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
poster_id = db.Column(db.Integer,
db.ForeignKey('t_post.id'),
nullable=False)
remote_host = db.Column(db.Text(), nullable=False)
def __init__(self, poster_id, remote_host):
self.poster_id = poster_id
self.remote_host = remote_host
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(256, collation="utf8_general_ci"), nullable=False)
content = db.Column(db.Text(collation="utf8_general_ci"), nullable=False)
def __init__(self, title=None, content=None):
self.title = title
self.content = content
def __repr__(self):
return '<Title %r>' % self.title
class Interest(db.Model):
__tablename__ = 'interest'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255))
company_name = db.Column(db.String(255))
email = db.Column(db.String(255))
website = db.Column(db.String(255))
note = db.Column(db.Text())
def __str__(self):
return '%s' % (self.email)
class Interest(db.Model): __tablename__ = 'interest' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255)) company_name = db.Column(db.String(255)) email = db.Column(db.String(255)) website = db.Column(db.String(255)) note = db.Column(db.Text()) created_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now()) def __str__(self): return '%s' % (self.email)
class Post(db.Model):
__tablename__ = "t_post"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
post_time = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
poster_id = db.Column(db.Text())
poster_name = db.Column(db.Text())
post_contents = db.Column(db.Text())
parent_post_id = db.Column(db.Integer)
ke_id = db.Column(db.Integer, db.ForeignKey('m_thread.id'))
password = db.Column(db.Text())
is_delete = db.Column(db.Integer)
def __init__(self, post_time, poster_id, poster_name, post_contents,
parent_post_id, ke_id, password, is_delete):
self.post_time = post_time
self.poster_id = poster_id
self.poster_name = poster_name
self.post_contents = post_contents
self.parent_post_id = parent_post_id
self.ke_id = ke_id
self.password = password
self.is_delete = is_delete
class Users(db.Model):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text())
uses_full_drink_date = db.Column(db.Boolean())
beers = db.relationship("Beers", backref="users", lazy="joined")
default_drink_location_city = db.Column(db.Text())
default_drink_location_country = db.Column(db.Text())
default_drink_date = db.Column(db.DateTime)
creation_datetime = db.Column(db.DateTime)
last_updated = db.Column(db.DateTime)
def __init__(
self,
name,
uses_full_drink_date,
default_drink_location_city,
default_drink_location_country,
default_drink_date,
creation_datetime=None,
last_updated=None,
):
self.name = name
self.uses_full_drink_date = uses_full_drink_date
self.default_drink_location_city = default_drink_location_city
self.default_drink_location_country = default_drink_location_country
self.default_drink_date = default_drink_date
if creation_datetime is None:
creation_datetime = datetime.utcnow()
if last_updated is None:
last_updated = datetime.utcnow()
self.creation_datetime = creation_datetime
self.last_updated = last_updated
def __repr__(self):
return "<User %s>" % (self.name)
class Tags(db.Model):
__tablename__ = "tags"
id = db.Column(db.Integer, primary_key=True)
user = db.Column(db.Integer, db.ForeignKey("users.id"))
beer = db.Column(db.Integer, db.ForeignKey("beers.id"))
tag = db.Column(db.Text())
def __init__(self, user, beer, tag):
self.user = user
self.beer = beer
self.tag = tag
def __repr__(self):
return "<%r %r - %r>" % (self.user, self.beer, self.tag)
class Presale(db.Model):
__tablename__ = 'presale'
id = db.Column(db.Integer, primary_key=True)
full_name = db.Column(db.String(255))
email = db.Column(db.String(255))
accredited = db.Column(db.Boolean())
entity_type = db.Column(db.String(255))
desired_allocation = db.Column(db.String(255))
desired_allocation_currency = db.Column(db.String(3))
citizenship = db.Column(db.String(2))
sending_addr = db.Column(db.String(255))
note = db.Column(db.Text())
def __str__(self):
return '%s' % (self.email)
class TraceFile(db.Model):
__tablename__ = 'tracefiles'
id = db.Column(db.String(8), primary_key=True)
name = db.Column(db.String(128), index=True)
description = db.Column(db.Text())
filename = db.Column(db.String(128))
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
username = db.relationship('User')
filesize = db.Column(db.Integer) #Bytes
filetype = db.Column(db.String(64))
packet_count = db.Column(db.Integer)
date_added = db.Column(db.DateTime)
date_deleted = db.Column(db.DateTime)
status = db.Column(db.Integer(1))
def __repr__(self):
return '<TraceFile %r, filename: %r>\n' % (self.name, self.filename)
class QuestionData(db.Model):
""" Feedback-form question-data for all experiments """
q_id = db.Column(db.Integer, primary_key=True)
gfg_id = db.Column(db.String(32), nullable=False)
session_id = db.Column(db.Integer, db.ForeignKey('session.session_id'))
exp_name = db.Column(db.String(32), nullable=False)
rating = db.Column(db.String(32))
difficulty = db.Column(db.String(32))
distraction = db.Column(db.String(32)) #informative
#extrahelp = db.Column(db.String) #added new
openended = db.Column(db.Text())
def __repr__(self):
pass
def add_json_data(self, json_event):
""" Parse and add backbone.js json data for a questionnaire """
self.rating = json_event['rating']
if json_event['difficulty'] == "Not difficult":
json_event['difficulty'] = "1"
elif json_event['difficulty'] == "Somewhat difficult":
json_event['difficulty'] = "5"
elif json_event['difficulty'] == "Very difficult":
json_event['difficulty'] = "10"
elif json_event['difficulty'] == "Not rated":
json_event['difficulty'] = "0"
self.difficulty = json_event['difficulty']
if json_event['distraction'] == "No distraction":
json_event['distraction'] = "1"
elif json_event['distraction'] == "Some distractions":
json_event['distraction'] = "5"
elif json_event['distraction'] == "Frequent interruptions":
json_event['distraction'] = "10"
elif json_event['distraction'] == "Not rated":
json_event['distraction'] = "0"
self.distraction = json_event['distraction']
#self.extrahelp = json_event['extrahelp']
self.openended = clean_db_string(json_event['openended'])
current_app.logger.info("%s added to QuestionData for session id %s " %
(self.q_id, self.session_id))
class LogEntry(db.Model):
id = db.Column(db.Integer, primary_key=True)
timestamp = db.Column(db.DateTime, index=True)
server = db.Column(db.String(100), index=True)
log_name = db.Column(db.String(255), index=True)
message = db.Column(db.Text(), nullable=True)
def __init__(self, timestamp, server, log_name, message):
self.timestamp = datetime_from_str(timestamp)
self.server = server
self.log_name = log_name
self.message = message
def to_dict(self):
return {
'timestamp': self.timestamp,
'server': self.server,
'log_name': self.log_name,
'message': self.message,
}
class Post(db.Model):
__tablename__ = 'posts'
id = db.Column(db.String(36),
primary_key=True,
default=lambda: str(uuid.uuid4()))
user_id = db.Column(db.String(36), db.ForeignKey('users.id'))
title = db.Column(db.String(50), nullable=False)
text = db.Column(db.Text(), nullable=False)
created_at = db.Column(db.DateTime(timezone=True),
server_default=func.now())
updated_at = db.Column(db.DateTime(timezone=True),
server_default=func.now(),
onupdate=func.now())
user = db.relationship('User', back_populates='posts', uselist=False)
categories = db.relationship('Category', secondary=posts_categories)
def serialize(self, detail=False):
author = "{} {}".format(self.user.profile.first_name,
self.user.profile.last_name)
response = {
'id': self.id,
'author': author,
'title': self.title,
'text': self.text
}
if detail:
categories_list = [
category.serialize() for category in self.categories
]
response.update({'categories': categories_list})
return response
class ShortURL(CMSModel):
"""Model for URL shortening entries"""
address = db.Column(db.Text())
alphabet = (
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')
base = len(alphabet)
@property
def shorthand(self):
if self.id <= 0:
raise ValueError('ShortURL id has to be greater than 0')
shorthand = ''
id_number = self.id - 1
remainder = id_number % self.base
id_number //= self.base
shorthand += self.alphabet[remainder]
while id_number:
remainder = id_number % self.base
id_number //= self.base
shorthand += self.alphabet[remainder]
return shorthand
@staticmethod
def shorthand_to_id(shorthand):
id_number = 0
for pos, letter in enumerate(shorthand):
weight = pow(ShortURL.base, pos)
id_number += weight * ShortURL.alphabet.index(letter)
return id_number + 1
class UserModel(db.Model):
"""
User Model class with table column definition
"""
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
display_name = db.Column(db.String(), nullable=False)
username = db.Column(db.String(), unique=True, nullable=False)
email = db.Column(db.String(), unique=True, nullable=False)
hashed_password = db.Column(db.Text(), nullable=True)
role = db.Column(db.Enum("sub_admin", "donator", "admin", name="role"))
country = db.Column(db.String(), nullable=True)
donation_active = db.Column(db.Boolean, default=False)
address_id = db.Column(db.Integer,
db.ForeignKey("addresses.id"),
nullable=False)
address = relationship("AddressModel", foreign_keys=[address_id])
def __init__(self,
display_name: str,
username: str,
email: str,
address_id: int,
role: str,
country: str,
hashed_password: str = None,
donation_active: bool = False) -> None:
self.display_name = display_name
self.username = username
self.email = email
self.address_id = address_id
self.hashed_password = hashed_password
self.role = role
self.country = country
self.donation_active = donation_active
def __repr__(self):
return f"<User {self.username}>"
def as_dict(self) -> Dict[str, Any]:
"""
Return object data in easily serializable format
"""
return {
"id": self.id,
"display_name": self.display_name,
"username": self.username,
"email": self.email,
"formatted_address": self.address.format_address(),
"address": self.address.as_dict(),
"role": self.role,
"country": self.country,
"donation_active": self.donation_active
}
@staticmethod
def create_user(new_user: UserModel) -> int:
"""
create new users
:param new_user:
:return: created user id
"""
try:
db.session.add(new_user)
db.session.commit()
return new_user.id
except SQLAlchemyError as error:
db.session.rollback()
raise error
@staticmethod
def update_user(user_id: int, user: UserModel) -> bool:
"""
update user info by id
:param user_id:
:param user:
:return: bool
"""
try:
target_user = db.session.query(UserModel).get(user_id)
if not target_user:
raise SQLCustomError("No record for requested school")
target_user.display_name = user.display_name
target_user.username = user.username
target_user.email = user.email
target_user.country = user.country
target_user.donation_active = user.donation_active
db.session.commit()
return True
except SQLAlchemyError as error:
db.session.rollback()
raise error
@staticmethod
def delete_user(user_id: int) -> bool:
"""
delete user by id
:param user_id:
:return: bool
"""
try:
if not db.session.query(UserModel).filter(
UserModel.id == user_id).delete():
return False
db.session.commit()
return True
except SQLAlchemyError as error:
db.session.rollback()
raise error
@staticmethod
def get_user_by_id(user_id: int) -> UserModel:
"""
get user by id
:param user_id:
:return: user info
"""
try:
return db.session.query(UserModel).get(user_id)
except SQLAlchemyError as error:
raise error
@staticmethod
def get_users_by_address_ids(address_ids: tuple) -> List[UserModel]:
"""
get user by ids
:param address_ids:
:return: user infos
"""
try:
return db.session.query(UserModel).filter(
UserModel.address_id.in_(address_ids)).all()
except SQLAlchemyError as error:
raise error
@staticmethod
def get_user_by_email(email: str) -> UserModel:
"""
get user by email
:param email:
:return: user info
"""
try:
return db.session.query(UserModel).filter(
UserModel.email == email).first()
except SQLAlchemyError as error:
raise error
@staticmethod
def get_user_by_username(username: str) -> UserModel:
"""
get user by username
:param username:
:return: user info
"""
try:
return db.session.query(UserModel).filter(
UserModel.username == username).first()
except SQLAlchemyError as error:
raise error
@staticmethod
def get_users_by_query(page: int,
query: str,
per_page: int = 20) -> Pagination:
"""
get users by name (as name is not unique, multiple records can be returned)
:param page:
:param query:
:param per_page int
:return: user info list
"""
try:
return db.session.query(UserModel).\
join(AddressModel).filter(or_(UserModel.display_name.ilike('%' + query + '%'),
UserModel.email.ilike('%' + query + '%'))).paginate(
page=page,
per_page=per_page,
error_out=False)
except SQLAlchemyError as error:
raise error
@staticmethod
def get_all_users(page: int = 1, per_page: int = 20) -> Pagination:
"""
get all users
:page integer
:per_page int
:return: users list of dict
"""
try:
return db.session.query(UserModel).join(AddressModel).paginate(
page=page, per_page=per_page, error_out=False)
except SQLAlchemyError as error:
raise error
@staticmethod
def get_users_by_role(page: int,
role: str,
per_page: int = 20) -> Pagination:
"""
get all users by role
:params integer
:role str
:per_page int
:return: users list of Pagination
"""
try:
return db.session.query(UserModel).join(AddressModel).filter(
UserModel.role == role).paginate(page=page,
per_page=per_page,
error_out=False)
except SQLAlchemyError as error:
raise error
@staticmethod
def get_users_by_country(page: int,
country: str,
per_page: int = 20) -> Pagination:
"""
get all users by country
:params integer
:country str
:per_page int
:return: users list of dict
"""
try:
return db.session.query(UserModel).join(AddressModel).filter(
UserModel.country == country).paginate(page=page,
per_page=per_page,
error_out=False)
except SQLAlchemyError as error:
raise error
@staticmethod
def get_users_by_role_country(page: int,
role: str,
country: str,
per_page: int = 20) -> Pagination:
"""
get all users by role and country
:params page
:params role
:country role
:per_page int
:return: user info list
"""
try:
return db.session.query(UserModel).join(AddressModel).filter(
and_(UserModel.country == country,
UserModel.role == role)).paginate(page=page,
per_page=per_page,
error_out=False)
except SQLAlchemyError as error:
raise error
@staticmethod
def get_all_user_address(page: int = 1, per_page: int = 20) -> Pagination:
"""
get all user address for get all address API
:params page
:return Pagination Object
"""
try:
return db.session.query(AddressModel, UserModel). \
filter(AddressModel.id == UserModel.address_id).filter(
AddressModel.type == "user").paginate(page=page, per_page=per_page, error_out=False)
except SQLAlchemyError as error:
raise error
@staticmethod
def change_password(user_id: int, new_pwd: str) -> bool:
"""
change password by userid
:param user_id:
:param new_pwd:
:return: bool
"""
try:
db.session.query(UserModel).filter(UserModel.id == user_id).\
update({UserModel.hashed_password: new_pwd})
db.session.commit()
return True
except SQLAlchemyError as error:
db.session.rollback()
raise error
@staticmethod
def change_password(user_id, new_pwd) -> bool:
"""
change password by userid
:param user_id:
:param new_pwd:
:return: bool
"""
try:
db.session.query(UserModel).filter(UserModel.id == user_id).\
update({UserModel.hashed_password: new_pwd})
db.session.commit()
return True
except SQLAlchemyError as error:
db.session.rollback()
raise error
class StudentModel(db.Model):
__tablename__ = "students"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String())
deactivated_at = db.Column(db.DateTime(), nullable=True)
birth_date = db.Column(db.Date(), nullable=True)
father_name = db.Column(db.UnicodeText())
mother_name = db.Column(db.UnicodeText())
parents_occupation = db.Column(db.Text())
address_id = db.Column(db.Integer, db.ForeignKey("addresses.id"), nullable=False)
address = relationship("AddressModel", foreign_keys=[address_id])
def __init__(self, name: str,
deactivated_at: datetime, birth_date: date, father_name: str, mother_name: str,
parents_occupation: str, address_id: int):
self.name = name
self.deactivated_at = deactivated_at
self.birth_date = birth_date
self.father_name = father_name
self.mother_name = mother_name
self.parents_occupation = parents_occupation
self.address_id = address_id
def __repr__(self):
return f"<Student {self.name}>"
def student_dict(self) -> Dict[str, Any]:
"""
Return object data in easily serializable format
"""
return {
"id": self.id,
"name": self.name,
"deactivated_at": self.deactivated_at.strftime("%d-%m-%Y") if self.deactivated_at else "",
"birth_date": self.birth_date.strftime("%d-%m-%Y") if self.birth_date else "",
"father_name": self.father_name,
"mother_name": self.mother_name,
"parents_occupation": self.parents_occupation,
"address": {
"id": self.address_id,
"division": self.address.division,
"district": self.address.district,
"township": self.address.township,
"street_address": self.address.street_address
}
}
@staticmethod
def create_student(new_student):
"""
create new student
:param new_student:
:return: bool
"""
try:
db.session.add(new_student)
db.session.commit()
return True
except SQLAlchemyError as e:
# to put log
return False
class TextEntry(CMSModel):
name = db.Column(db.String(256), nullable=False, unique=True, index=True)
content = db.Column(db.Text())
class BadWord(CMSModel):
"""Model for words which should be filtered out"""
word = db.Column(db.Text())
class StudentModel(db.Model):
__tablename__ = "students"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String())
deactivated_at = db.Column(db.DateTime(), nullable=True)
birth_date = db.Column(db.Date(), nullable=True)
father_name = db.Column(db.UnicodeText())
mother_name = db.Column(db.UnicodeText())
parents_occupation = db.Column(db.Text())
photo = db.Column(db.Text())
address_id = db.Column(db.Integer,
db.ForeignKey("addresses.id"),
nullable=False)
address = relationship("AddressModel", foreign_keys=[address_id])
def __init__(self, name: str, deactivated_at: datetime, birth_date: date,
father_name: str, mother_name: str, parents_occupation: str,
photo: str, address_id: int):
self.name = name
self.deactivated_at = deactivated_at
self.birth_date = birth_date
self.father_name = father_name
self.mother_name = mother_name
self.parents_occupation = parents_occupation
self.photo = photo
self.address_id = address_id
def __repr__(self):
return f"<Student {self.name}>"
def student_dict(self) -> Dict[str, Any]:
"""
Return object data in easily serializable format
"""
return {
"id":
self.id,
"name":
self.name,
"deactivated_at":
self.deactivated_at.strftime("%d-%m-%Y")
if self.deactivated_at else "",
"birth_date":
self.birth_date.strftime("%d-%m-%Y") if self.birth_date else "",
"father_name":
self.father_name,
"mother_name":
self.mother_name,
"parents_occupation":
self.parents_occupation,
"photo":
self.photo,
"address": {
"id": self.address_id,
"division": self.address.division,
"district": self.address.district,
"township": self.address.township,
"street_address": self.address.street_address
}
}
@staticmethod
def get_student_by_id(student_id: int) -> Optional[StudentModel]:
"""
get student by id
:param student_id:
:return: student info
"""
try:
return db.session.query(StudentModel). \
join(AddressModel). \
filter(StudentModel.id == student_id).first()
except SQLAlchemyError as error:
raise error
@staticmethod
def get_students_by_name(name) -> List[StudentModel]:
"""
get students by name (as name is not unique, multiple records can be returned)
:param name:
:return: student info list
"""
try:
return db.session.query(StudentModel).join(AddressModel).filter(
StudentModel.name == name)
except SQLAlchemyError as error:
raise error
@staticmethod
def get_students_by_birth_date(birth_date) -> List[StudentModel]:
"""
get students by birth_date (as birth_date is not unique, multiple records can be returned)
:param birth_date:
:return: student info list
"""
try:
return db.session.query(StudentModel).join(AddressModel).filter(
StudentModel.birth_date == birth_date)
except SQLAlchemyError as error:
raise error
@staticmethod
def get_all_students(page) -> Pagination:
"""
get all students
:return: students list of dict
"""
try:
return db.session.query(StudentModel).join(AddressModel).\
paginate(page=page, error_out=False)
except SQLAlchemyError as error:
raise error
@staticmethod
def get_all_student_address(page) -> Pagination:
"""
get all school address for get all address API
:params page
:return
"""
try:
return db.session.query(AddressModel, StudentModel). \
filter(AddressModel.id == StudentModel.address_id).filter(
AddressModel.type == "student").paginate(page=page, error_out=False)
except SQLAlchemyError as error:
raise error
@staticmethod
def create_student(new_student):
"""
create new student
:param new_student:
:return: bool
"""
try:
db.session.add(new_student)
db.session.commit()
return new_student.id
except SQLAlchemyError as error:
db.session.rollback()
raise error
@staticmethod
def update_student(student_id, student) -> bool:
"""
update student info by id
:param student_id:
:param student:
:return: bool
"""
try:
target_student = db.session.query(StudentModel).filter(
StudentModel.id == student_id).first()
if not target_student:
raise SQLCustomError("No record for requested student")
target_student.name = student.name
target_student.deactivated_at = student.deactivated_at
target_student.birth_date = student.birth_date
target_student.father_name = student.father_name
target_student.mother_name = student.mother_name
target_student.parents_occupation = student.parents_occupation
target_student.photo = student.photo
target_student.address_id = student.address_id
db.session.commit()
return True
except SQLAlchemyError as error:
db.session.rollback()
raise error
@staticmethod
def delete_student(student_id) -> bool:
"""
delete student by id
:param student_id:
:return: bool
"""
try:
if not db.session.query(StudentModel).filter(
StudentModel.id == student_id).delete():
return False
db.session.commit()
return True
except SQLAlchemyError as error:
db.session.rollback()
raise error
class User(CMSModel):
"""Model for use with Flask-Login"""
# http://www.rfc-editor.org/errata_search.php?rfc=3696&eid=1690
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(254), unique=True)
access_level = db.Column(db.Integer, default=0)
is_verified = db.Column(db.Boolean, default=False)
verification_token = db.Column(db.Text,
default=security.generate_random_token)
pass_hash = db.Column(db.Text())
# only datasets
datasets = db.relationship(
'UsersMutationsDataset',
# beware: the expression will fail if put into quotation marks;
# it is somehow related to late evaluation of hybrid attributes
primaryjoin=and_(id == UsersMutationsDataset.owner_id,
not_(UsersMutationsDataset.is_expired)))
all_datasets = db.relationship('UsersMutationsDataset', backref='owner')
def __init__(self, email, password, access_level=0):
if not self.is_mail_correct(email):
raise ValidationError('This email address seems to be incorrect')
if not self.is_password_strong(password):
raise ValidationError('The password is not strong enough')
self.email = email
self.access_level = access_level
self.pass_hash = security.generate_secret_hash(password)
@classmethod
def user_loader(cls, user_id):
return cls.query.get(int(user_id))
def datasets_names_by_uri(self):
return {d.uri: d.name for d in self.datasets}
@staticmethod
def is_mail_correct(email):
if len(email) > 254:
return False
if '@' not in email:
return False
# both parts required
try:
local, domain = email.split('@')
except ValueError:
return False
# no consecutive dots allowed in domain
if '..' in domain:
return False
return True
@staticmethod
def is_password_strong(password):
# count of different characters used
if len(set(password)) <= 2:
return False
# overall length
return len(password) >= 5
@property
def is_admin(self):
return self.access_level == 10
@property
def is_moderator(self):
return self.access_level >= 5
@property
def is_authenticated(self):
return True
@property
def is_active(self):
return True
@property
def is_anonymous(self):
return False
def authenticate(self, password):
return self.is_verified and security.verify_secret(
password, str(self.pass_hash))
@cached_property
def username(self):
return self.email.split('@')[0].replace('.', ' ').title()
def __repr__(self):
return '<User {0} with id {1}>'.format(self.email, self.id)
def get_id(self):
return self.id
