class LdapModel(db.Model):
uid = db.Column(db.String, primary_key=True)
display_name = db.Column(db.String)
given_name = db.Column(db.String)
email_address = db.Column(db.String)
telephone_number = db.Column(db.String)
title = db.Column(db.String)
department = db.Column(db.String)
affiliation = db.Column(db.String)
sponsor_type = db.Column(db.String)
date_cached = db.Column(db.DateTime(timezone=True), default=func.now())
@classmethod
def from_entry(cls, entry):
return LdapModel(uid=entry.uid.value,
display_name=entry.displayName.value,
given_name=", ".join(entry.givenName),
email_address=entry.mail.value,
telephone_number=entry.telephoneNumber.value,
title=", ".join(entry.title),
department=", ".join(entry.uvaDisplayDepartment),
affiliation=", ".join(entry.uvaPersonIAMAffiliation),
sponsor_type=", ".join(entry.uvaPersonSponsoredType))
def proper_name(self):
return f'{self.display_name} - ({self.uid})'
class WorkflowSpecDependencyFile(db.Model):
"""Connects a workflow to the version of the specification files it depends on to execute"""
file_data_id = db.Column(db.Integer,
db.ForeignKey(FileDataModel.id),
primary_key=True)
workflow_id = db.Column(db.Integer,
db.ForeignKey("workflow.id"),
primary_key=True)
file_data = db.relationship(FileDataModel)
class WorkflowLibraryModel(db.Model):
__tablename__ = 'workflow_library'
id = db.Column(db.Integer, primary_key=True)
workflow_spec_id = db.Column(db.String, db.ForeignKey('workflow_spec.id'), nullable=True)
library_spec_id = db.Column(db.String, db.ForeignKey('workflow_spec.id'), nullable=True)
parent = db.relationship(WorkflowSpecModel,
primaryjoin=workflow_spec_id==WorkflowSpecModel.id,
backref=backref('libraries',cascade='all, delete'))
library = db.relationship(WorkflowSpecModel,primaryjoin=library_spec_id==WorkflowSpecModel.id,
backref=backref('parents',cascade='all, delete'))
class ApprovalFile(db.Model):
file_data_id = db.Column(db.Integer,
db.ForeignKey(FileDataModel.id),
primary_key=True)
approval_id = db.Column(db.Integer,
db.ForeignKey("approval.id"),
primary_key=True)
approval = db.relationship("ApprovalModel")
file_data = db.relationship(FileDataModel)
class UserModel(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
uid = db.Column(db.String, unique=True)
email_address = db.Column(db.String)
display_name = db.Column(db.String)
affiliation = db.Column(db.String, nullable=True)
eppn = db.Column(db.String, nullable=True)
first_name = db.Column(db.String, nullable=True)
last_name = db.Column(db.String, nullable=True)
title = db.Column(db.String, nullable=True)
# TODO: Add Department and School
def encode_auth_token(self):
"""
Generates the Auth Token
:return: string
"""
hours = float(app.config['TOKEN_AUTH_TTL_HOURS'])
payload = {
'exp':
datetime.datetime.utcnow() +
datetime.timedelta(hours=hours, minutes=0, seconds=0),
'iat':
datetime.datetime.utcnow(),
'sub':
self.uid
}
return jwt.encode(
payload,
app.config.get('SECRET_KEY'),
algorithm='HS256',
)
@staticmethod
def decode_auth_token(auth_token):
"""
Decodes the auth token
:param auth_token:
:return: integer|string
"""
try:
payload = jwt.decode(auth_token,
app.config.get('SECRET_KEY'),
algorithms='HS256')
return payload
except jwt.ExpiredSignatureError:
raise ApiError(
'token_expired',
'The Authentication token you provided expired and must be renewed.'
)
except jwt.InvalidTokenError:
raise ApiError(
'token_invalid',
'The Authentication token you provided is invalid. You need a new token. '
)
class DataStoreModel(db.Model):
__tablename__ = 'data_store'
id = db.Column(db.Integer, primary_key=True)
last_updated = db.Column(db.DateTime(timezone=True),
server_default=func.now())
key = db.Column(db.String, nullable=False)
workflow_id = db.Column(db.Integer)
study_id = db.Column(db.Integer, nullable=True)
task_spec = db.Column(db.String)
spec_id = db.Column(db.String)
user_id = db.Column(db.String, nullable=True)
file_id = db.Column(db.Integer, db.ForeignKey('file.id'), nullable=True)
value = db.Column(db.String)
class LookupFileModel(db.Model):
"""Gives us a quick way to tell what kind of lookup is set on a form field.
Connected to the file data model, so that if a new version of the same file is
created, we can update the listing."""
__tablename__ = 'lookup_file'
id = db.Column(db.Integer, primary_key=True)
workflow_spec_id = db.Column(db.String)
field_id = db.Column(db.String)
is_ldap = db.Column(
db.Boolean) # Allows us to run an ldap query instead of a db lookup.
file_data_model_id = db.Column(db.Integer, db.ForeignKey('file_data.id'))
dependencies = db.relationship("LookupDataModel",
lazy="select",
backref="lookup_file_model",
cascade="all, delete, delete-orphan")
class WorkflowModel(db.Model):
__tablename__ = 'workflow'
id = db.Column(db.Integer, primary_key=True)
bpmn_workflow_json = db.Column(db.JSON)
status = db.Column(db.Enum(WorkflowStatus))
study_id = db.Column(db.Integer, db.ForeignKey('study.id'))
study = db.relationship("StudyModel", backref='workflow')
workflow_spec_id = db.Column(db.String, db.ForeignKey('workflow_spec.id'))
workflow_spec = db.relationship("WorkflowSpecModel")
total_tasks = db.Column(db.Integer, default=0)
completed_tasks = db.Column(db.Integer, default=0)
last_updated = db.Column(db.DateTime(timezone=True), server_default=func.now())
user_id = db.Column(db.String, default=None)
class WorkflowSpecModel(db.Model):
__tablename__ = 'workflow_spec'
id = db.Column(db.String, primary_key=True)
display_name = db.Column(db.String)
display_order = db.Column(db.Integer, nullable=True)
description = db.Column(db.Text)
category_id = db.Column(db.Integer, db.ForeignKey('workflow_spec_category.id'), nullable=True)
category = db.relationship("WorkflowSpecCategoryModel")
is_master_spec = db.Column(db.Boolean, default=False)
standalone = db.Column(db.Boolean, default=False)
library = db.Column(db.Boolean, default=False)
class TaskLogModel(db.Model):
__tablename__ = 'task_log'
id = db.Column(db.Integer, primary_key=True)
level = db.Column(db.String)
code = db.Column(db.String)
message = db.Column(db.String)
user_uid = db.Column(db.String)
study_id = db.Column(db.Integer,
db.ForeignKey(StudyModel.id),
nullable=False)
workflow_id = db.Column(db.Integer,
db.ForeignKey(WorkflowModel.id),
nullable=False)
task = db.Column(db.String)
timestamp = db.Column(db.DateTime(timezone=True),
server_default=func.now())
class UserModel(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
uid = db.Column(db.String, db.ForeignKey('ldap_model.uid'), unique=True)
ldap_info = db.relationship("LdapModel")
def is_admin(self):
# Currently admin abilities are set in the configuration, but this
# may change in the future.
return self.uid in app.config['ADMIN_UIDS']
def encode_auth_token(self):
"""
Generates the Auth Token
:return: string
"""
hours = float(app.config['TOKEN_AUTH_TTL_HOURS'])
payload = {
# 'exp': datetime.datetime.utcnow() + datetime.timedelta(hours=hours, minutes=0, seconds=0),
# 'iat': datetime.datetime.utcnow(),
'sub': self.uid
}
return jwt.encode(
payload,
app.config.get('SECRET_KEY'),
algorithm='HS256',
)
@staticmethod
def decode_auth_token(auth_token):
"""
Decodes the auth token
:param auth_token:
:return: integer|string
"""
try:
payload = jwt.decode(auth_token, app.config.get('SECRET_KEY'), algorithms='HS256')
return payload
except jwt.ExpiredSignatureError:
raise ApiError('token_expired', 'The Authentication token you provided expired and must be renewed.')
except jwt.InvalidTokenError:
raise ApiError('token_invalid', 'The Authentication token you provided is invalid. You need a new token. ')
class FileDataModel(db.Model):
__tablename__ = 'file_data'
id = db.Column(db.Integer, primary_key=True)
md5_hash = db.Column(UUID(as_uuid=True), unique=False, nullable=False)
data = deferred(db.Column(
db.LargeBinary)) # Don't load it unless you have to.
version = db.Column(db.Integer, default=0)
size = db.Column(db.Integer, default=0)
date_created = db.Column(db.DateTime(timezone=True),
server_default=func.now())
file_model_id = db.Column(db.Integer, db.ForeignKey('file.id'))
file_model = db.relationship("FileModel", foreign_keys=[file_model_id])
user_uid = db.Column(db.String, db.ForeignKey('user.uid'), nullable=True)
class StudyEvent(db.Model):
__tablename__ = 'study_event'
id = db.Column(db.Integer, primary_key=True)
study_id = db.Column(db.Integer, db.ForeignKey(StudyModel.id), nullable=False)
study = db.relationship(StudyModel, back_populates='events_history')
create_date = db.Column(db.DateTime(timezone=True), server_default=func.now())
status = db.Column(db.Enum(StudyStatus))
comment = db.Column(db.String, default='')
event_type = db.Column(db.Enum(StudyEventType))
user_uid = db.Column(db.String, db.ForeignKey('user.uid'), nullable=True)
class LookupDataModel(db.Model):
__tablename__ = 'lookup_data'
id = db.Column(db.Integer, primary_key=True)
lookup_file_model_id = db.Column(db.Integer,
db.ForeignKey('lookup_file.id'))
value = db.Column(db.String)
label = db.Column(db.String)
# In the future, we might allow adding an additional "search" column if we want to search things not in label.
data = db.Column(
db.JSON
) # all data for the row is stored in a json structure here, but not searched presently.
# Assure there is a searchable index on the label column, so we can get fast results back.
# query with:
# search_results = LookupDataModel.query.filter(LookupDataModel.label.match("INTERNAL")).all()
__table_args__ = (
Index(
'ix_lookupdata_tsv',
func.to_tsvector(
'simple',
label), # Use simple, not english to keep stop words in place.
postgresql_using='gin'), )
class ApprovalModel(db.Model):
__tablename__ = 'approval'
id = db.Column(db.Integer, primary_key=True)
study_id = db.Column(db.Integer,
db.ForeignKey(StudyModel.id),
nullable=False)
study = db.relationship(StudyModel)
workflow_id = db.Column(db.Integer,
db.ForeignKey(WorkflowModel.id),
nullable=False)
workflow = db.relationship(WorkflowModel)
approver_uid = db.Column(
db.String
) # Not linked to user model, as they may not have logged in yet.
status = db.Column(db.String)
message = db.Column(db.String, default='')
date_created = db.Column(db.DateTime(timezone=True), default=func.now())
date_approved = db.Column(db.DateTime(timezone=True), default=None)
version = db.Column(
db.Integer) # Incremented integer, so 1,2,3 as requests are made.
approval_files = db.relationship(ApprovalFile,
back_populates="approval",
cascade="all, delete, delete-orphan",
order_by=ApprovalFile.file_data_id)
class WorkflowModel(db.Model):
__tablename__ = 'workflow'
id = db.Column(db.Integer, primary_key=True)
bpmn_workflow_json = db.Column(db.JSON)
status = db.Column(db.Enum(WorkflowStatus))
study_id = db.Column(db.Integer, db.ForeignKey('study.id'))
study = db.relationship("StudyModel", backref='workflow')
workflow_spec_id = db.Column(db.String, db.ForeignKey('workflow_spec.id'))
workflow_spec = db.relationship("WorkflowSpecModel")
total_tasks = db.Column(db.Integer, default=0)
completed_tasks = db.Column(db.Integer, default=0)
last_updated = db.Column(db.DateTime)
# Order By is important or generating hashes on reviews.
dependencies = db.relationship(
WorkflowSpecDependencyFile,
cascade="all, delete, delete-orphan",
order_by="WorkflowSpecDependencyFile.file_data_id")
def spec_version(self):
dep_ids = list(dep.file_data_id for dep in self.dependencies)
return "-".join(str(dep_ids))
class StudyAssociated(db.Model):
"""
This model allows us to associate people with a study, and optionally
give them edit access. This allows us to create a table with PI, D_CH, etc.
and give access to people other than the study owner.
Task_Events will still work as they have previously
"""
__tablename__ = 'study_associated_user'
id = db.Column(db.Integer, primary_key=True)
study_id = db.Column(db.Integer, db.ForeignKey(StudyModel.id), nullable=False)
uid = db.Column(db.String, db.ForeignKey('ldap_model.uid'), nullable=False)
role = db.Column(db.String, nullable=True)
send_email = db.Column(db.Boolean, nullable=True)
access = db.Column(db.Boolean, nullable=True)
ldap_info = db.relationship(LdapModel)
class FileModel(db.Model):
__tablename__ = 'file'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
type = db.Column(db.Enum(FileType))
is_status = db.Column(db.Boolean)
content_type = db.Column(db.String)
is_reference = db.Column(db.Boolean, nullable=False,
default=False) # A global reference file.
primary = db.Column(
db.Boolean, nullable=False,
default=False) # Is this the primary BPMN in a workflow?
primary_process_id = db.Column(
db.String, nullable=True
) # An id in the xml of BPMN documents, critical for primary BPMN.
workflow_spec_id = db.Column(db.String,
db.ForeignKey('workflow_spec.id'),
nullable=True)
workflow_id = db.Column(db.Integer,
db.ForeignKey('workflow.id'),
nullable=True)
irb_doc_code = db.Column(
db.String, nullable=True
) # Code reference to the irb_documents.xlsx reference file.
# A request was made to delete the file, but we can't because there are
# active approvals or running workflows that depend on it. So we archive
# it instead, hide it in the interface.
archived = db.Column(db.Boolean, default=False, nullable=False)
class WorkflowSpecCategoryModel(db.Model):
__tablename__ = 'workflow_spec_category'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
display_name = db.Column(db.String)
display_order = db.Column(db.Integer)
class TaskEventModel(db.Model):
__tablename__ = 'task_event'
id = db.Column(db.Integer, primary_key=True)
study_id = db.Column(db.Integer, db.ForeignKey('study.id'))
user_uid = db.Column(
db.String, nullable=False
) # In some cases the unique user id may not exist in the db yet.
workflow_id = db.Column(db.Integer,
db.ForeignKey('workflow.id'),
nullable=False)
workflow_spec_id = db.Column(db.String, db.ForeignKey('workflow_spec.id'))
spec_version = db.Column(db.String)
action = db.Column(db.String)
task_id = db.Column(db.String)
task_name = db.Column(db.String)
task_title = db.Column(db.String)
task_type = db.Column(db.String)
task_state = db.Column(db.String)
task_lane = db.Column(db.String)
form_data = db.Column(
db.JSON) # And form data submitted when the task was completed.
mi_type = db.Column(db.String)
mi_count = db.Column(db.Integer)
mi_index = db.Column(db.Integer)
process_name = db.Column(db.String)
date = db.Column(db.DateTime(timezone=True), default=func.now())
class StudyModel(db.Model):
__tablename__ = 'study'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String)
short_title = db.Column(db.String, nullable=True)
last_updated = db.Column(db.DateTime(timezone=True), server_default=func.now())
status = db.Column(db.Enum(StudyStatus))
progress_status = db.Column(db.Enum(ProgressStatus))
irb_status = db.Column(db.Enum(IrbStatus))
primary_investigator_id = db.Column(db.String, nullable=True)
sponsor = db.Column(db.String, nullable=True)
ind_number = db.Column(db.String, nullable=True)
user_uid = db.Column(db.String, db.ForeignKey('user.uid'), nullable=False)
investigator_uids = db.Column(db.ARRAY(db.String), nullable=True)
requirements = db.Column(db.ARRAY(db.Integer), nullable=True)
on_hold = db.Column(db.Boolean, default=False)
enrollment_date = db.Column(db.DateTime(timezone=True), nullable=True)
#events = db.relationship("TaskEventModel")
events_history = db.relationship("StudyEvent", cascade="all, delete, delete-orphan")
short_name = db.Column(db.String, nullable=True)
proposal_name = db.Column(db.String, nullable=True)
def update_from_protocol_builder(self, study: ProtocolBuilderCreatorStudy, user_id):
self.title = study.TITLE
self.user_uid = user_id
self.last_updated = study.DATELASTMODIFIED
self.irb_status = IrbStatus.incomplete_in_protocol_builder
class StudyModel(db.Model):
__tablename__ = 'study'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String)
last_updated = db.Column(db.DateTime(timezone=True), default=func.now())
protocol_builder_status = db.Column(db.Enum(ProtocolBuilderStatus))
primary_investigator_id = db.Column(db.String, nullable=True)
sponsor = db.Column(db.String, nullable=True)
hsr_number = db.Column(db.String, nullable=True)
ind_number = db.Column(db.String, nullable=True)
user_uid = db.Column(db.String, db.ForeignKey('user.uid'), nullable=False)
investigator_uids = db.Column(db.ARRAY(db.String), nullable=True)
requirements = db.Column(db.ARRAY(db.Integer), nullable=True)
on_hold = db.Column(db.Boolean, default=False)
def update_from_protocol_builder(self, pbs: ProtocolBuilderStudy):
self.hsr_number = pbs.HSRNUMBER
self.title = pbs.TITLE
self.user_uid = pbs.NETBADGEID
self.last_updated = pbs.DATE_MODIFIED
self.protocol_builder_status = ProtocolBuilderStatus.ACTIVE
if pbs.HSRNUMBER:
self.protocol_builder_status = ProtocolBuilderStatus.OPEN
if self.on_hold:
self.protocol_builder_status = ProtocolBuilderStatus.HOLD
class AdminSessionModel(db.Model):
__tablename__ = 'admin_session'
id = db.Column(db.Integer, primary_key=True)
token = db.Column(db.String, unique=True)
admin_impersonate_uid = db.Column(db.String)
class EmailModel(db.Model):
__tablename__ = 'email'
id = db.Column(db.Integer, primary_key=True)
subject = db.Column(db.String)
sender = db.Column(db.String)
recipients = db.Column(db.String)
cc = db.Column(db.String, nullable=True)
bcc = db.Column(db.String, nullable=True)
content = db.Column(db.String)
content_html = db.Column(db.String)
study_id = db.Column(db.Integer,
db.ForeignKey(StudyModel.id),
nullable=True)
timestamp = db.Column(db.DateTime(timezone=True), default=func.now())
workflow_spec_id = db.Column(db.String, nullable=True)
study = db.relationship(StudyModel)
