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 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 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)
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 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 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)
date_created = db.Column(db.DateTime(timezone=True), default=func.now())
file_model_id = db.Column(db.Integer, db.ForeignKey('file.id'))
file_model = db.relationship("FileModel", foreign_keys=[file_model_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(timezone=True), server_default=func.now())
user_id = db.Column(db.String, default=None)
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 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 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)
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 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)
task_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_model_id = db.Column(db.Integer, db.ForeignKey('file.id'))
last_updated = db.Column(db.DateTime(timezone=True))
dependencies = db.relationship("LookupDataModel",
lazy="select",
backref="lookup_file_model",
cascade="all, delete, delete-orphan")
file_model = db.relationship("FileModel")