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)