class UserRoleEntity(db.Model, CRUDMixin):
""" Stores the user-role mapping """
__tablename__ = 'UserRole'
id = db.Column("urID", db.Integer, primary_key=True)
user_id = db.Column("usrID",
db.Integer,
db.ForeignKey('User.usrID', ondelete='CASCADE'),
nullable=False)
role_id = db.Column("rolID",
db.Integer,
db.ForeignKey('Role.rolID', ondelete='CASCADE'),
nullable=False)
added_at = db.Column('urAddedAt',
db.DateTime(),
nullable=False,
server_default='0000-00-00 00:00:00')
role = db.relationship('RoleEntity', uselist=False)
user = db.relationship('UserEntity', uselist=False)
def get_id(self):
""" return the unicode of the primary key value """
return unicode(self.id)
def __repr__(self):
return "<UserRoleEntity (\n\t" \
"urID: {0.id}, \n\t" \
" {0.user!r}, \n\t" \
" {0.role!r}, \n" \
" {0.added_at}, \n" \
")>".format(self)
class SubjectFileEntity(db.Model, CRUDMixin):
""" Stores the file metadata """
__tablename__ = 'SubjectFile'
id = db.Column("sfID", db.Integer, primary_key=True)
subject_id = db.Column("sbjID", db.Integer, db.ForeignKey('Subject.sbjID'),
nullable=False)
event_id = db.Column("evtID", db.Integer, db.ForeignKey('Event.evtID'),
nullable=False)
file_name = db.Column("sfFileName", db.String(255), nullable=False)
file_check_sum = db.Column("sfFileCheckSum", db.String(32), nullable=False)
file_size = db.Column("sfFileSize", db.String(255), nullable=False)
uploaded_at = db.Column("sfUploadedAt", db.DateTime(), nullable=False,
server_default='0000-00-00 00:00:00')
user_id = db.Column("usrID", db.Integer, db.ForeignKey('User.usrID'),
nullable=False)
# @OneToOne
subject = db.relationship('SubjectEntity', uselist=False, lazy='joined')
event = db.relationship('EventEntity', uselist=False, lazy='joined')
user = db.relationship('UserEntity', uselist=False, lazy='joined')
def get_full_path(self, prefix):
"""
Build the full path using the database info and the prefix
@TODO: implement the naming convention
"""
return os.path.join(prefix, self.file_name)
def __repr__(self):
return "<SubjectFileEntity (sfID: {0.id}, sbjID: {0.subject_id})>" \
"usrID: {0.user_id}".format(self)
def serialize(self):
"""Return object data for jsonification """
return {
'id': self.id,
'file_name': self.file_name,
'file_check_sum': self.file_check_sum,
'file_size': self.file_size,
'uploaded_at': dump_datetime(self.uploaded_at),
'subject_id': self.subject_id,
'event_id': self.event_id,
'user_id': self.user_id,
'user_name': self.user.get_name(),
# 'subject': self.subject.serialize(),
# 'event': self.event.serialize(),
# 'user': self.user.serialize(),
}
class WebSessionEntity(db.Model, CRUDMixin):
"""Store web session details"""
__tablename__ = 'WebSession'
id = db.Column('webID', db.Integer, primary_key=True)
session_id = db.Column('webSessID',
db.String(255),
nullable=False,
default='')
user_id = db.Column('usrID',
db.Integer,
db.ForeignKey('User.usrID'),
nullable=False,
default=0)
ip = db.Column('webIP', db.String(15), nullable=False, default='')
date_time = db.Column('webDateTime',
db.DateTime,
nullable=False,
default=datetime.datetime(datetime.MINYEAR, 1, 1))
user_agent_id = db.Column('uaID',
db.Integer,
db.ForeignKey('UserAgent.uaID'),
nullable=False)
# @OneToMany
user_agent = db.relationship(UserAgentEntity, lazy='joined')
user = db.relationship(UserEntity, lazy='joined')
@staticmethod
def get_by_session_id(session_id):
""" Search helper: WHERE webSessID = ???"""
return WebSessionEntity.query.filter_by(session_id=session_id).first()
def __repr__(self):
""" Return a friendly object representation """
return "<WebSessionEntity (webID: '{0.id}', webSessID: {0.session_id},"\
" usrID: {0.user_id}, webIP: {0.ip})>".format(self)
class SubjectFileEntity(db.Model, CRUDMixin):
""" Stores the uploaded file metadata """
__tablename__ = 'SubjectFile'
id = db.Column("sfID", db.Integer, primary_key=True)
subject_id = db.Column("sbjID",
db.Integer,
db.ForeignKey('Subject.sbjID'),
nullable=False)
event_id = db.Column("evtID",
db.Integer,
db.ForeignKey('Event.evtID'),
nullable=False)
file_name = db.Column("sfFileName", db.String(255), nullable=False)
file_check_sum = db.Column("sfFileCheckSum", db.String(32), nullable=False)
file_size = db.Column("sfFileSize", db.String(255), nullable=False)
uploaded_at = db.Column("sfUploadedAt",
db.DateTime,
nullable=False,
server_default='0000-00-00 00:00:00')
user_id = db.Column("usrID",
db.Integer,
db.ForeignKey('User.usrID'),
nullable=False)
# @OneToOne
subject = db.relationship('SubjectEntity', uselist=False, lazy='joined')
event = db.relationship('EventEntity', uselist=False, lazy='joined')
user = db.relationship('UserEntity', uselist=False, lazy='joined')
def create_folder(self, directory):
"""
Create folder if it does not exist
"""
success = True
if not os.path.exists(directory):
try:
os.makedirs(directory)
except Exception as exc:
print "Failed due: {}".format(exc)
success = False
return success
@classmethod
def get_convention_file_name(cls, date_and_time, subject_id, file_name):
"""
Concatenate the pieces to obtain a fiendly file name.
@TODO: check if we need to need the "site ID" and
how to obtain it.
Original convention:
20120101_0123_SiteID_A_SubjectID_B_Sequence123_xyz.jpg
Actual implementation (does not keep track of sequences):
20120101_0123_site_subject_B_xyz.jpg
"""
date_part = date_and_time.strftime("%Y%m%d")
time_part = date_and_time.strftime("%H%M")
file_convention = "{}_{}_site_subject_{}_{}".format(
date_part, time_part, subject_id, file_name)
return file_convention
def get_full_path(self, prefix):
"""
Build the full path using the database info and the prefix
@TODO: implement the naming convention
20120101_0123_SiteIDA_SubjectIDB_Sequence123_xyz.jpg
"""
subject_dir = os.path.join(prefix,
"subject_{}".format(self.subject.redcap_id))
success = self.create_folder(subject_dir)
assert success
file_convention = SubjectFileEntity.get_convention_file_name(
self.uploaded_at, self.subject.redcap_id, self.file_name)
full_path = os.path.join(subject_dir, file_convention)
return full_path
def __repr__(self):
""" Return a friendly object representation """
return "<SubjectFileEntity (sfID: {0.id}, sbjID: {0.subject_id}, " \
"usrID: {0.user_id}, sfFileName: {0.file_name}>)".format(self)
def serialize(self):
"""Return object data for jsonification """
# @TODO: add information about download counts
return {
'id': self.id,
'file_name': self.file_name,
'file_check_sum': self.file_check_sum,
'file_size': self.file_size,
'uploaded_at': utils.localize_est_datetime(self.uploaded_at),
'subject_id': self.subject_id,
'event_id': self.event_id,
'user_id': self.user_id,
'user_name': self.user.get_name(),
# 'subject': self.subject.serialize(),
# 'event': self.event.serialize(),
# 'user': self.user.serialize(),
}
class LogEntity(db.Model, CRUDMixin):
""" Keep track of important user actions """
__tablename__ = 'Log'
id = db.Column('logID', db.Integer, primary_key=True)
type_id = db.Column('logtID', db.Integer,
db.ForeignKey('LogType.logtID'),
nullable=False)
web_session_id = db.Column('webID', db.Integer,
db.ForeignKey('WebSession.webID'),
nullable=False)
date_time = db.Column('logDateTime', db.DateTime, nullable=False,
server_default='0000-00-00 00:00:00')
details = db.Column('logDetails', db.Text, nullable=False)
# @OneToOne
log_type = db.relationship(LogTypeEntity, uselist=False, lazy='joined')
web_session = db.relationship(WebSessionEntity, uselist=False,
lazy='joined')
@staticmethod
def get_logs(per_page=25, page_num=1):
"""
Helper for formating the event details
"""
def item_from_entity(entity):
return {
'id': entity.id,
'user_email': entity.web_session.user.email
if entity.web_session.user is not None else '',
'type': entity.log_type.type,
'details': entity.details,
'web_session_ip': entity.web_session.ip,
'date_time': utils.localize_est_datetime(entity.date_time),
}
pagination = LogEntity.query.paginate(page_num, per_page, False)
items = map(item_from_entity, pagination.items)
return items, pagination.pages
@staticmethod
def _log(log_type, session_id, details=''):
""" Helper for logging """
logt = LogTypeEntity.query.filter_by(type=log_type).first()
if logt is None:
app.logger.error("Developer error. Invalid log type: {}"
.format(log_type))
return
web_session = WebSessionEntity.get_by_session_id(session_id)
if web_session is None:
app.logger.error("Developer error. Invalid session id: {}"
.format(session_id))
return
LogEntity.create(log_type=logt,
date_time=datetime.datetime.now(),
details=details,
web_session=web_session)
@staticmethod
def account_created(session_id, details=''):
""" Log account creation """
LogEntity._log(LOG_TYPE_ACCOUNT_CREATED, session_id, details)
@staticmethod
def login(session_id, details=''):
""" Log successful login """
LogEntity._log(LOG_TYPE_LOGIN, session_id, details)
@staticmethod
def logout(session_id, details=''):
""" Log logout click """
LogEntity._log(LOG_TYPE_LOGOUT, session_id, details)
@staticmethod
def login_error(session_id, details=''):
""" Log failed login """
LogEntity._log(LOG_TYPE_LOGIN_ERROR, session_id, details)
@staticmethod
def file_uploaded(session_id, details=''):
""" Log file upload """
LogEntity._log(LOG_TYPE_FILE_UPLOADED, session_id, details)
@staticmethod
def file_downloaded(session_id, details=''):
""" Log file download """
LogEntity._log(LOG_TYPE_FILE_DOWNLOADED, session_id, details)
@staticmethod
def account_modified(session_id, details=''):
""" Log account changes """
LogEntity._log(LOG_TYPE_ACCOUNT_MODIFIED, session_id, details)
@staticmethod
def redcap_subjects_imported(session_id, details=''):
""" Log it """
LogEntity._log(LOG_TYPE_REDCAP_SUBJECTS_IMPORTED, session_id, details)
@staticmethod
def redcap_events_imported(session_id, details=''):
""" Log it """
LogEntity._log(LOG_TYPE_REDCAP_EVENTS_IMPORTED, session_id, details)
def __repr__(self):
""" Return a friendly object representation """
return "<LogEntity(logID: {0.id}, "\
"logtID: {0.type_id}" \
"webID: {0.web_session_id}, "\
"date_time: {0.date_time})>".format(self)
class UserEntity(db.Model, UserMixin, CRUDMixin):
""" Stores the basic information about the user.
Implements the functions as required by:
https://flask-login.readthedocs.org/en/latest/
"""
__tablename__ = 'User'
id = db.Column("usrID", db.Integer, primary_key=True)
email = db.Column("usrEmail", db.String(255), nullable=False, unique=True)
first = db.Column("usrFirst", db.String(255), nullable=False)
last = db.Column("usrLast", db.String(255), nullable=False)
minitial = db.Column("usrMI", db.String(1), nullable=False)
added_at = db.Column("usrAddedAt",
db.DateTime(),
nullable=False,
server_default='0000-00-00 00:00:00')
modified_at = db.Column("usrModifiedAt", db.TIMESTAMP(), nullable=False)
# server_default='CURRENT_TIMESTAMP')
email_confirmed_at = db.Column("usrEmailConfirmedAt",
db.DateTime(),
nullable=False,
server_default='0000-00-00 00:00:00')
active = db.Column("usrIsActive",
db.Boolean(),
nullable=False,
server_default='1')
access_expires_at = db.Column("usrAccessExpiresAt",
db.DateTime(),
nullable=False,
server_default='0000-00-00 00:00:00')
password_hash = db.Column("usrPasswordHash",
db.String(255),
nullable=False,
server_default='')
# @OneToMany
roles = db.relationship(RoleEntity,
secondary=UserRoleEntity.__tablename__,
backref=db.backref('users'),
lazy='dynamic')
"""
`lazy` defines when SQLAlchemy will load the data from the database:
'select' (which is the default) means that SQLAlchemy will load the
data as necessary in one go using a standard select statement.
'joined' tells SQLAlchemy to load the relationship in the same query as the
parent using a JOIN statement.
'subquery' works like 'joined' but instead SQLAlchemy will use a subquery.
'dynamic' is special and useful if you have may items. Instead of loading
the items SQLAlchemy will return another query object which you can
further refine before loading them items. This is usually what you want
if you expect more than a handful of items for this relationship.
"""
def is_active(self):
""" An user can be blocked by setting a flag in the database """
return self.active
def is_expired(self):
""" An user can be blocked by setting expiration date to yesterday"""
return self.access_expires_at < datetime.today()
def is_anonymous(self):
""" Flag instances of valid users """
return False
def is_authenticated(self):
""" Returns True if the user is authenticated, i.e. they have provided
valid credentials.
(Only authenticated users will fulfill the criteria of login_required.)
"""
return True
def get_id(self):
""" The id encrypted in the session """
return unicode(self.id)
def get_roles(self):
""" Return text representation of user roles """
return [role.name for role in self.roles]
"""
@property
def to_visible(self):
visible_props = ['id', 'email', 'first', 'last', 'minitial',
'added_at', 'modified_at', 'email_confirmed_at',
'is_active', 'access_expires_at']
return dict([(key, val) for key, val in self.__dict__.items()
if key in UserEntity.visible_props])
"""
def get_name(self):
"""
:rtype string
:return concat(first, ' ', last)
"""
return "{} {}".format(self.first, self.last)
def get_email_verification_token(self):
"""
:rtype string
:return the email verification token stored in the database
"""
return "token"
def serialize(self):
"""Return object data for jsonification"""
return {
'id': self.id,
'email': self.email,
'roles': [r.name for r in self.roles],
'first': self.first,
'last': self.last,
'minitial': self.minitial,
'is_active': True if self.active else False,
'is_expired': True if self.is_expired() else False,
'added_at': dump_datetime(self.added_at),
'email_confirmed_at': dump_datetime(self.email_confirmed_at),
'access_expires_at': dump_datetime(self.access_expires_at),
}
def __repr__(self):
return "<UserEntity (usrID: {0.id}, usrEmail: {0.email}, " \
"usrIsActive: {0.active})>".format(self)