class Issue(db.Model):
"""Issues table in the database."""
__tablename__ = 'issues'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.Text)
user_id = db.Column(db.ForeignKey(u'users.id'), index=True)
category_id = db.Column(db.ForeignKey(u'category.id'),
nullable=False,
index=True)
location_lat = db.Column(db.Float)
location_lon = db.Column(db.Float)
status = db.Column(db.Text)
description = db.Column(db.Text)
open_date = db.Column(db.TIMESTAMP(timezone=True))
close_date = db.Column(db.TIMESTAMP(timezone=True))
delete_date = db.Column(db.TIMESTAMP(timezone=True))
category = db.relationship(u'Category')
user = db.relationship(u'User')
def delete(self):
"""Setting deleting date for issue"""
if not self.delete_date:
self.delete_date = func.current_timestamp()
return True
return False
def restore(self):
"""Restoring issue from deletion"""
if self.delete_date:
self.delete_date = None
return True
return False
class Ticket(db.Model):
__tablename__ = 'ticket'
# attributes
tid = db.Column(db.Integer, primary_key=True, autoincrement=True)
cust_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
time_of_movie = db.Column(db.DateTime,
db.ForeignKey('movieShow.timing'),
nullable=False)
hasexpired = db.Column(db.Boolean, default=0)
class IssueHistory(db.Model):
"""IssueHistory table in the database."""
__tablename__ = 'issue_History'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.ForeignKey(u'users.id'))
issue_id = db.Column(db.ForeignKey(u'issues.id'), index=True)
status_id = db.Column(db.ForeignKey(u'statuses.id'), index=True)
transaction_date = db.Column(db.TIMESTAMP(timezone=True))
issue = db.relationship(u'Issue')
status = db.relationship(u'Status')
user = db.relationship(u'User')
class Attachment(db.Model):
"""Attachment table in the database."""
__tablename__ = 'attachments'
id = db.Column(db.Integer, primary_key=True)
issue_id = db.Column(db.ForeignKey(u'issues.id'), index=True)
image_url = db.Column(db.Text)
issue = db.relationship(u'Issue')
def get_thumbnail_url(self):
head, tail = os.path.split(self.image_url)
thumb_name = "thumb-{}".format(tail)
return "{}/{}".format(head, thumb_name)
def delete(self):
db.session.delete(self)
db.session.commit()
delete_file(self.image_url)
delete_file(self.get_thumbnail_url())
directory_path = os.path.abspath(
os.path.join(current_app.config['MEDIA_FOLDER'], self.image_url,
os.pardir))
if os.path.exists(directory_path) and not os.listdir(directory_path):
os.rmdir(directory_path)
def get_full_thumbnail_url(self):
url = self.get_thumbnail_url()
if current_app.config.get('MEDIA_URL'):
return current_app.config['MEDIA_URL'] + url
return '/media/' + url
class Label(db.Model):
__tablename__ = 'labels'
label_id = db.Column(db.String(32), primary_key=True)
user_id = db.Column(db.String(32), db.ForeignKey('users.user_id'))
songs = db.relationship('Song', backref='label')
label_name = db.Column(db.String(50), nullable=False)
class SqlQuery(db.Model):
id = db.Column(db.Integer, primary_key=True)
label = db.Column(db.String(64), index=True, unique=True)
raw_sql = db.Column(db.Text)
creator_user_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
index=True,
nullable=False)
charts = db.relationship('Chart', backref='sql_query', lazy='dynamic')
usergroups = db.relationship("Usergroup",
secondary=query_perms,
backref="queries")
@validates('label')
def validate_label(self, key, label):
if not label:
raise AssertionError('No label provided')
if Usergroup.query.filter(
func.lower(Usergroup.label) == func.lower(label)).first():
raise AssertionError('Provided label is already in use')
return label
@validates('raw_sql')
def validate_raw_sql(self, key, raw_sql):
if not raw_sql:
raise AssertionError('No raw_sql provided')
if not isinstance(raw_sql, str):
raise AssertionError('raw_sql must be a string')
return raw_sql
@validates('usergroups')
def validate_usergroups(self, key, usergroups):
if not isinstance(usergroups, Usergroup):
raise AssertionError('Provided usergroup is not recognized')
return usergroups
def get_dict(self):
dict_format = {
'query_id': self.id,
'label': self.label,
'raw_sql': self.raw_sql,
'creator': self.creator.get_dict(),
}
return dict_format
def get_usergroups(self):
usergroups = helpers.get_dicts_from_usergroups(self.usergroups)
return usergroups
def get_authorized_users(self):
users = dict(users=helpers.get_users_from_usergroups(self.usergroups))
return users
def __repr__(self):
return '<SqlQuery {}:{}'.format(self.id, self.label)
class Comments(db.Model):
"""
Issues table in the database.
"""
__tablename__ = 'comments'
id = db.Column(db.Integer, primary_key=True)
comment = db.Column(db.Text)
date_public = db.Column(db.TIMESTAMP(timezone=True))
user_id = db.Column(db.ForeignKey(u'users.id'))
issue_id = db.Column(db.ForeignKey(u'issues.id'), index=True)
status = db.Column(db.Text)
pre_deletion_status = db.Column(db.Text)
issue = db.relationship(u'Issue')
user = db.relationship(u'User')
class Meta:
"""..."""
app_label = 'city_issues'
managed = False
db_table = 'comments'
class Identity(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
provider = db.Column(db.String(255), nullable=False)
provider_user_id = db.Column(db.String(255), nullable=False)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
last_used_at = db.Column(db.DateTime)
__table_args__ = (db.UniqueConstraint('provider',
'provider_user_id',
name='uniq_provider_user_id'), )
class Session(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
token = db.Column(db.String(255), default=generate_token)
user_agent = db.Column(db.String(255))
ip_address = db.Column(db.String(255))
platform = db.Column(db.String(255))
browser = db.Column(db.String(255))
created_at = db.Column(db.DateTime, default=datetime.utcnow)
last_active_at = db.Column(db.DateTime, default=datetime.utcnow)
class UserTasks(db.Model):
__tablename__ = 'user_tasks'
id = db.Column(db.Integer, primary_key=True)
uuid = db.Column(db.String(200), nullable=False)
user_id = db.Column(db.Integer,
db.ForeignKey(User.id, ondelete='CASCADE'),
nullable=False)
def __init__(self, uuid: str, user_id: int):
self.uuid = uuid
self.user_id = user_id
def __repr__(self):
return f'<id: {self.id}><{self.uuid}><user_id: {self.user_id}>'
class Song(db.Model):
__tablename__ = 'songs'
song_id = db.Column(db.String(32), primary_key=True)
label_id = db.Column(db.String(32), db.ForeignKey('labels.label_id'))
song_name = db.Column(db.String(80), nullable=True)
song_artist = db.Column(db.String(80), nullable=True)
song_type = db.Column(db.String(80), nullable=True)
rating = db.Column(db.Float, nullable=True)
author = db.Column(db.String(80), nullable=True)
author_id = db.Column(db.Integer, nullable=True)
key = db.Column(db.String(80), nullable=True)
tuning_name = db.Column(db.String(80), nullable=True)
tutning_value = db.Column(db.String(80), nullable=True)
difficulty = db.Column(db.String(80), nullable=True)
content = db.Column(db.Text, nullable=True)
strummings = db.Column(db.Text, nullable=True)
class Log(BaseModel, db.Model):
"""Model for Log table"""
__tablename__ = "Logs"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
timestamp = db.Column(db.String, nullable="False")
log_message = db.Column(db.String, nullable="False")
type_ = db.Column(db.String(20), nullable="False")
activity_id = db.Column(db.Integer,
db.ForeignKey('Activities.id', ondelete="CASCADE"),
nullable=False)
activity = db.relationship("Activity", back_populates="logs")
def __init__(self, timestamp, log_message, type_, activity_id):
super().__init__()
self.timestamp = timestamp
self.log_message = log_message
self.type_ = type_
self.activity_id = activity_id
from datetime import datetime, time
import re
from werkzeug.security import generate_password_hash, check_password_hash
from sqlalchemy.orm import validates
from sqlalchemy import func
from backend.app import db, helper_functions as helpers
from backend.app.encrypt import encrypt_with_aws, decrypt_with_aws
user_perms = db.Table(
'user_perms',
db.Column('user_id',
db.Integer,
db.ForeignKey('user.id'),
primary_key=True),
db.Column('usergroup_id',
db.Integer,
db.ForeignKey('usergroup.id'),
primary_key=True),
db.UniqueConstraint('user_id',
'usergroup_id',
name='UC_user_id_usergroup_id'),
)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64),
index=True,
unique=True,
nullable=False)
email = db.Column(db.String(120), index=True, nullable=False)
class Contact(db.Model):
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(64))
last_name = db.Column(db.String(64))
email = db.Column(db.String(128))
public = db.Column(db.Boolean)
creator_user_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
index=True)
publication_ids = db.relationship("Publication",
secondary=publication_recipients,
backref="recipients")
@validates('first_name')
def validate_first_name(self, key, first_name):
if not first_name:
return None
if not isinstance(first_name, str):
raise AssertionError('First name must be string')
if len(first_name) > 50:
raise AssertionError('First names must be less than 50 characters')
return first_name
@validates('last_name')
def validate_last_name(self, key, last_name):
if not last_name:
return None
if not isinstance(last_name, str):
raise AssertionError('Last name must be string')
if len(last_name) > 50:
raise AssertionError('Last names must be less than 50 characters')
return last_name
@validates('email')
def validate_email(self, key, email):
if not email:
raise AssertionError('No email provided')
if not isinstance(email, str):
raise AssertionError('Email must be string')
if not re.match("[^@]+@[^@]+\.[^@]+", email):
raise AssertionError('Provided email is not an email address')
return email.lower()
@validates('public')
def validates_public(self, key, public):
if not public:
return None
if isinstance(public, bool):
return public
if isinstance(public, str):
if public.lower() == 'true':
return True
if public.lower() == 'false':
return False
else:
raise AssertionError('Public flag must be boolean value')
@validates('publications')
def validate_publications(self, key, publication):
if not publication:
raise AssertionError('No publication provided')
if not isinstance(publication, Publication):
raise AssertionError('Publication not recognized')
return publication
def get_dict(self):
dict_format = {
'contact_id': self.id,
'first_name': self.first_name,
'last_name': self.last_name,
'email': self.email,
'public': self.public,
'creator': self.creator.get_dict(),
}
return dict_format
def __repr__(self):
return '<Recipient {}, {}>'.format(self.last_name, self.first_name)
class Publication(db.Model):
id = db.Column(db.Integer, primary_key=True)
type = db.Column(db.Enum('email_attachment',
'email_embedded',
'dashboard',
name='pub_type'),
default='dashboard')
creator_user_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
index=True)
frequency = db.Column(db.Enum('manual',
'days_of_week',
'day_of_month',
'daily',
'hourly',
'every_ten_min',
name='pub_frequency'),
default='manual')
monday = db.Column(db.Boolean(), default=False)
tuesday = db.Column(db.Boolean(), default=False)
wednesday = db.Column(db.Boolean(), default=False)
thursday = db.Column(db.Boolean(), default=False)
friday = db.Column(db.Boolean(), default=False)
saturday = db.Column(db.Boolean(), default=False)
sunday = db.Column(db.Boolean(), default=False)
day_of_month = db.Column(db.Integer)
pub_time = db.Column(db.Time, default=time())
report_id = db.Column(db.Integer, db.ForeignKey('report.id'), index=True)
# contact_ids = db.relationship("Contact", secondary=publication_recipients, backref="publications")
@validates('recipients')
def validate_recipients(self, key, contact):
if not contact:
raise AssertionError('No contact id provided')
if not isinstance(contact, Contact):
raise AssertionError('Contact ID not recognized')
return contact
@validates('type')
def validate_type(self, key, type):
if not type:
raise AssertionError('Publication type not provided')
if type not in ['email_attachment', 'email_embedded', 'dashboard']:
raise AssertionError('Publication type not recognized')
return type
@validates('frequency')
def validate_type(self, key, frequency):
if not frequency:
raise AssertionError('Publication frequency not provided')
if frequency not in [
'manual', 'days_of_week', 'day_of_month', 'daily', 'hourly',
'every_ten_min'
]:
raise AssertionError('Publication frequency not recognized')
return frequency
@validates('publication_report')
def validate_contact_ids(self, key, report):
if not report:
raise AssertionError('No report provided')
if not isinstance(report, Report):
raise AssertionError('Report ID not recognized')
return report
def get_dict(self):
dict_format = {
'publication_id': self.id,
'type': self.type,
'creator': self.creator.get_dict(),
'frequency': self.frequency,
'monday': self.monday,
'tuesday': self.tuesday,
'wednesday': self.wednesday,
'thursday': self.thursday,
'friday': self.friday,
'saturday': self.saturday,
'sunday': self.sunday,
'day_of_month': self.day_of_month,
'publication_time': self.pub_time.strftime('%l:%M%p'),
'report_id': self.report_id,
'recipients': self.get_recipients()
}
return dict_format
def get_recipients(self):
return list(map(lambda obj: obj.get_dict(), self.recipients))
def __repr__(self):
return '<Publication {} for report {}'.format(self.type,
self.report_id)
class Report(db.Model):
id = db.Column(db.Integer, primary_key=True)
label = db.Column(db.String(64), index=True, unique=True)
creator_user_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
index=True)
created_on = db.Column(db.DateTime, default=datetime.utcnow)
last_published = db.Column(db.DateTime)
parameters = db.Column(db.Text)
publications = db.relationship('Publication',
backref='publication_report',
lazy='dynamic')
usergroups = db.relationship("Usergroup",
secondary=report_perms,
backref="reports")
@validates('label')
def validate_label(self, key, label):
if not label:
raise AssertionError('No label provided')
if Usergroup.query.filter(
func.lower(Usergroup.label) == func.lower(label)).first():
raise AssertionError('Provided label is already in use')
return label
@validates('parameters')
def validate_parameters(self, key, parameters):
if not parameters:
raise AssertionError('No parameters provided')
if not isinstance(parameters, str):
raise AssertionError('Provided parameters is wrong data type')
return parameters
@validates('usergroups')
def validate_usergroups(self, key, usergroups):
if not isinstance(usergroups, Usergroup):
raise AssertionError('Provided usergroup is not recognized')
return usergroups
def get_dict(self):
dict_format = {
'report_id': self.id,
'label': self.label,
'creator': self.creator.get_dict(),
'created_on': self.created_on,
'last_published': self.last_published,
'parameters': self.parameters,
'publications': self.get_publications(),
}
return dict_format
def get_usergroups(self):
usergroups = helpers.get_dicts_from_usergroups(self.usergroups)
return usergroups
def get_authorized_users(self):
users = dict(users=helpers.get_users_from_usergroups(self.usergroups))
return users
def get_publications(self):
return list(map(lambda obj: obj.get_dict(), self.publications))
def __repr__(self):
return '<Report {}>'.format(self.label)
class Chart(db.Model):
id = db.Column(db.Integer, primary_key=True)
label = db.Column(db.String(64), index=True, unique=True)
type = db.Column(db.String(128))
parameters = db.Column(db.Text)
creator_user_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
index=True)
sql_query_id = db.Column(db.Integer, db.ForeignKey('sql_query.id'))
connection_id = db.Column(db.Integer, db.ForeignKey('connection.id'))
usergroups = db.relationship("Usergroup",
secondary=chart_perms,
backref="charts")
@validates('label')
def validate_label(self, key, label):
if not label:
raise AssertionError('No label provided')
if Usergroup.query.filter(
func.lower(Usergroup.label) == func.lower(label)).first():
raise AssertionError('Provided label is already in use')
return label
@validates('type')
def validate_type(self, key, type):
if not type:
raise AssertionError('No chart type provided')
if not isinstance(type, str):
raise AssertionError('chart type must be a string')
return type
@validates('parameters')
def validate_parameters(self, key, parameters):
if not parameters:
raise AssertionError('No parameters provided')
if not isinstance(parameters, str):
raise AssertionError('Provided parameters is wrong data type')
return parameters
@validates('sql_query_id')
def validate_sql_query_id(self, key, sql_query_id):
if not sql_query_id:
raise AssertionError('sql_query_id not provided')
if not helpers.get_record_from_id(SqlQuery, sql_query_id):
raise AssertionError('sql_query_id not recognized')
return sql_query_id
@validates('connection_id')
def validate_connection_id(self, key, connection_id):
if not connection_id:
raise AssertionError('connection_id not provided')
if not helpers.get_record_from_id(Connection, connection_id):
raise AssertionError('connection_id not recognized')
return connection_id
@validates('usergroups')
def validate_usergroups(self, key, usergroups):
if not isinstance(usergroups, Usergroup):
raise AssertionError('Provided usergroup is not recognized')
return usergroups
def get_dict(self):
dict_format = {
'chart_id': self.id,
'label': self.label,
'creator': self.creator.get_dict(),
'type': self.type,
'parameters': self.parameters,
'sql_query': self.sql_query.get_dict(),
'connection': self.chart_connection.get_dict(),
}
return dict_format
def get_usergroups(self):
usergroups = helpers.get_dicts_from_usergroups(self.usergroups)
return usergroups
def get_authorized_users(self):
users = dict(users=helpers.get_users_from_usergroups(self.usergroups))
return users
def __repr__(self):
return '<Chart label: {}>'.format(self.label)
class User(db.Model):
"""User table in the database"""
ROLE_ADMIN = 1
ROLE_MODERATOR = 2
ROLE_USER = 3
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text)
alias = db.Column(db.Text)
email = db.Column(db.Text)
hashed_password = db.Column(db.Text)
role_id = db.Column(db.ForeignKey(u'roles.id'), index=True)
avatar = db.Column(db.Text)
delete_date = db.Column(db.TIMESTAMP)
last_login = db.Column(db.TIMESTAMP)
role = db.relationship(u'Role')
@hybrid_property
def password(self):
"""Getting the password."""
return self.hashed_password
@password.setter
def password(self, raw_password):
"""Hashing password before being stored."""
self.hashed_password = django_bcrypt.hash(raw_password)
def check_password(self, raw_password):
"""Checking the password form database."""
return django_bcrypt.verify(raw_password, self.hashed_password)
# pylint: disable=no-self-use
# This needs to be checked because no self is used in function
def is_last_admin(self):
"""Looking for the last admin"""
count = User.query.filter_by(role_id=User.ROLE_ADMIN,
delete_date=None).count()
if count > 1:
return False
return True
def delete(self):
"""Setting deleting date for user"""
if self.role_id == User.ROLE_ADMIN:
if not self.is_last_admin():
self.delete_date = func.current_timestamp()
return True
else:
self.delete_date = func.current_timestamp()
return True
return False
def restore(self):
"""Restoring user from deletion"""
if self.delete_date:
self.delete_date = None
return True
return False
class Connection(db.Model):
id = db.Column(db.Integer, primary_key=True)
label = db.Column(db.String(64), index=True, unique=True, nullable=False)
db_type = db.Column(db.String(64))
host = db.Column(db.String(256))
port = db.Column(db.Integer)
username = db.Column(db.String(128))
password = db.Column(db.String(128))
database_name = db.Column(db.String(256))
charts = db.relationship('Chart',
backref='chart_connection',
lazy='dynamic')
creator_user_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
index=True,
nullable=False)
usergroups = db.relationship("Usergroup",
secondary=connection_perms,
backref="connections")
@validates('label')
def validate_label(self, key, label):
if not label:
raise AssertionError('No label provided')
if not isinstance(label, str):
raise AssertionError('Label must be string')
if Usergroup.query.filter(
func.lower(Usergroup.label) == func.lower(label)).first():
raise AssertionError('Provided label is already in use')
return label.lower()
@validates('db_type')
def validate_db_type(self, key, db_type):
if not db_type:
raise AssertionError('No db_type provided')
if not isinstance(db_type, str):
raise AssertionError('Provided db_type not valid')
return db_type
@validates('host')
def validate_host(self, key, host):
if not host:
raise AssertionError('No host provided')
if not isinstance(host, str):
raise AssertionError('Provided host not valid')
return host
@validates('port')
def validate_host(self, key, port):
if not port:
raise AssertionError('No port provided')
if not isinstance(port, (str, int)):
raise AssertionError('Provided port not valid')
return port
@validates('username')
def validate_username(self, key, username):
if not username:
raise AssertionError('No username provided')
if not isinstance(username, str):
raise AssertionError('Provided username not valid')
return username
@validates('password')
def validate_password(self, key, password):
if not password:
raise AssertionError('No password provided')
if not isinstance(password, str):
raise AssertionError('Provided password not valid')
return encrypt_with_aws(password)
@validates('database_name')
def validate_database_name(self, key, database_name):
if not database_name:
raise AssertionError('No database_name provided')
if not isinstance(database_name, str):
raise AssertionError('Provided database name not valid')
return database_name
@validates('creator')
def validate_creator(self, key, creator):
if not isinstance(creator, User):
raise AssertionError('Provided creator is not recognized')
return creator
@validates('usergroups')
def validate_usergroups(self, key, usergroups):
if not isinstance(usergroups, Usergroup):
raise AssertionError('Provided usergroup is not recognized')
return usergroups
def get_dict(self):
dict_format = {
'connection_id':
self.id,
'label':
self.label,
'db_type':
self.db_type,
'host':
self.host,
'port':
self.port,
'username':
self.username,
'password':
decrypt_with_aws(self.password)
if self.password else self.password,
'db_name':
self.database_name,
'creator':
self.creator.get_dict(),
}
return dict_format
def get_usergroups(self):
usergroups = helpers.get_dicts_from_usergroups(self.usergroups)
return usergroups
def get_authorized_users(self):
users = helpers.get_users_from_usergroups(self.usergroups)
return users
def __repr__(self):
return '<Connection label: {}>'.format(self.label)