class ProbeSource(db.Model, MGIModel): __tablename__ = "prb_source" _source_key = db.Column(db.Integer, primary_key=True) _organism_key = db.Column(db.Integer) _cellline_key = db.Column(db.Integer) _gender_key = db.Column(db.Integer) _refs_key = db.Column(db.Integer, mgi_fk("bib_refs._refs_key")) _strain_key = db.Column(db.Integer, mgi_fk("prb_strain._strain_key")) _tissue_key = db.Column(db.Integer) name = db.Column(db.String()) description = db.Column(db.String()) age = db.Column(db.String()) # column properties cellline = db.column_property( db.select([VocTerm.term]).where(VocTerm._term_key == _cellline_key)) gender = db.column_property( db.select([VocTerm.term]).where(VocTerm._term_key == _gender_key)) organism = db.column_property( db.select([Organism.commonname ]).where(Organism._organism_key == _organism_key)) tissue = db.column_property( db.select([ProbeTissue.tissue ]).where(ProbeTissue._tissue_key == _tissue_key)) # relationships reference = db.relationship("Reference", uselist=False) strain = db.relationship("Strain", uselist=False)
class MGIUser(db.Model, MGIModel): __tablename__ = "mgi_user" _user_key = db.Column(db.Integer, primary_key=True) _usertype_key = db.Column(db.Integer, mgi_fk("voc_term._term_key")) _userstatus_key = db.Column(db.Integer, mgi_fk("voc_term._term_key")) login = db.Column(db.String()) name = db.Column(db.String()) usertype_object = db.relationship( "VocTerm", primaryjoin="VocTerm._term_key==MGIUser._usertype_key", uselist=False) userstatus_object = db.relationship( "VocTerm", primaryjoin="VocTerm._term_key==MGIUser._userstatus_key", uselist=False) # Properties for Flask-Login functionality def is_authenticated(self): return True def is_active(self): return True def is_anonymous(self): return False def get_id(self): return self.login
class CVMixin(object): """ Mixin for all controlled vocab tables with shared columns """ key = db.Column(db.Integer, primary_key=True) name = db.Column(db.String()) description = db.Column(db.String()) active = db.Column(db.Boolean)
class DbInfo(db.Model, MGIModel): __tablename__ = "mgi_dbinfo" public_version = db.Column(db.String(), primary_key=True) product_name = db.Column(db.String()) schema_version = db.Column(db.String()) snp_schema_version = db.Column(db.String()) snp_data_version = db.Column(db.String()) lastdump_date = db.Column(db.DateTime) creation_date = db.Column(db.DateTime, onupdate=datetime.now) modification_date = db.Column(db.DateTime, onupdate=datetime.now)
class MappingExperiment(db.Model, MGIModel): __tablename__ = "mld_expts" _expt_key = db.Column(db.Integer, primary_key=True) _refs_key = db.Column(db.Integer, mgi_fk("bib_refs._refs_key")) expttype = db.Column(db.String()) tag = db.Column(db.Integer()) chromosome = db.Column(db.String()) # key constants _mgitype_key = 4 # other constants # these are expttypes that are valid to display in the P-WI VALID_EXPTTYPES = [ 'TEXT', 'TEXT-Genetic Cross', 'TEXT-Physical Mapping', 'TEXT-QTL', 'TEXT-Congenic', 'TEXT-Meta Analysis', 'TEXT-QTL-Candidate Genes' ] # column properties mgiid = db.column_property( db.select([Accession.accid]).where( db.and_(Accession._mgitype_key == _mgitype_key, Accession.prefixpart == 'MGI:', Accession.preferred == 1, Accession._logicaldb_key == 1, Accession._object_key == _expt_key))) # relationships mgiid_object = db.relationship( "Accession", primaryjoin="and_(Accession._object_key==MappingExperiment._expt_key," "Accession.prefixpart=='MGI:'," "Accession.preferred==1," "Accession._logicaldb_key==1," "Accession._mgitype_key==%d)" % _mgitype_key, foreign_keys="[Accession._object_key]", uselist=False) experiment_notechunks = db.relationship("ExperimentNoteChunk") # beware, this field is limitting its search to only displayable experiment types in the P-WI # this lets the marker detail know when to display a summary link to mapping data marker_assocs = db.relationship( "ExperimentMarkerAssoc", primaryjoin= "and_(ExperimentMarkerAssoc._expt_key==MappingExperiment._expt_key," "MappingExperiment.expttype.in_(%s))" % VALID_EXPTTYPES, order_by="ExperimentMarkerAssoc.sequencenum") reference = db.relationship("Reference", backref="mapping_experiments") @property def experimentnote(self): return "".join([nc.note for nc in self.experiment_notechunks])
class ProbeAllele(db.Model, MGIModel): __tablename__ = "prb_allele" _allele_key = db.Column(db.Integer, primary_key=True) _rflv_key = db.Column(db.Integer, mgi_fk("prb_rflv._rflv_key"), primary_key=True) allele = db.Column(db.String()) fragments = db.Column(db.String()) probe_allele_strains = db.relationship("ProbeAlleleStrain", backref=db.backref("probe", uselist=False))
class TrackRec(db.Model): __tablename__ = "wts_trackrec" key = db.Column(db.Integer, primary_key=True) priority_key = db.Column(db.Integer, db.ForeignKey("cv_wts_priority.key")) size_key = db.Column(db.Integer, db.ForeignKey("cv_wts_size.key"), index=True) status_key = db.Column(db.Integer, db.ForeignKey("cv_wts_status.key"), index=True) status_user_key = db.Column(db.Integer, db.ForeignKey("cv_user.key"), index=True) status_set_date = db.Column(db.DateTime()) locked_user_key = db.Column(db.Integer, db.ForeignKey("cv_user.key"), index=True) locked_date = db.Column(db.DateTime()) title = db.Column(db.String()) has_directory = db.Column(db.Boolean) attention_by = db.Column(db.DateTime()) creation_date = db.Column(db.DateTime(), default=db.func.now()) modification_date = db.Column(db.DateTime(), default=db.func.now()) description = db.Column(db.String()) progress_notes = db.Column(db.String()) priority = db.relationship("Priority", uselist=False, backref="trs") size = db.relationship("Size", uselist=False, backref="trs") status = db.relationship("Status", uselist=False, backref="trs") areas = db.relationship("Area", secondary=TRAreaAssoc.__table__, backref="trs") types = db.relationship("Type", secondary=TRTypeAssoc.__table__, backref="trs") assigned_users = db.relationship("User", secondary=TRAssignedUserAssoc.__table__) requested_by = db.relationship("User", secondary=TRRequestedUserAssoc.__table__) statusChanges = db.relationship("StatusHistory") child_trs = db.relationship( "TrackRec", secondary=TrackRecChildAssoc.__table__, primaryjoin="TrackRec.key==TrackRecChildAssoc.tr_key", secondaryjoin="TrackRec.key==TrackRecChildAssoc.child_tr_key", backref=db.backref("parent", uselist=False))
class Report(db.Model): __tablename__ = "pwi_report" __bind_key__ = "mgd" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(), index=True, nullable=False) description = db.Column(db.String()) sql_text = db.Column(db.String(), nullable=False) report_author = db.Column(db.String(), index=True, nullable=False) requested_by = db.Column(db.String(), index=True, nullable=False) type = db.Column(db.String()) created = db.Column(db.DateTime(), index=True, nullable=False) last_run = db.Column(db.DateTime()) last_run_duration = db.Column(db.String()) report_views = db.Column(db.String()) labels = db.relationship("ReportLabel", order_by="ReportLabel.label", backref=db.backref("report")) @property def tagString(self): return ", ".join(["%s" % l for l in self.labels]) @property def safeFileName(self): return "%s_%s.rpt" % (css(self.name), self.id) def __repr__(self): return "<Report id:%s,name:%s,author:%s>" % (self.id, self.name, self.report_author)
class NOM_Marker(db.Model, MGIModel): __tablename__ = "nom_marker" _nomen_key = db.Column(db.Integer, primary_key=True) _marker_type_key = db.Column(db.Integer) _marker_type_key.hidden = True _nomenstatus_key = db.Column(db.Integer) _nomenstatus_key.hidden = True symbol = db.Column(db.String()) name = db.Column(db.String()) chromosome = db.Column(db.String()) statusnote = db.Column( db.String(convert_unicode='force', unicode_error="ignore")) # key constants _mgitype_key = 21 _nomenstatus_vocab_key = 16 # mapped fields markertype = db.column_property( db.select([MarkerType.name ]).where(MarkerType._marker_type_key == _marker_type_key)) nomenstatus = db.column_property( db.select([VocTerm.term]). where(db.and_(VocTerm._term_key==_nomenstatus_key, \ VocTerm._vocab_key==_nomenstatus_vocab_key) ) ) mgiid = db.column_property( db.select([Accession.accid]).where( db.and_(Accession._mgitype_key == _mgitype_key, Accession.prefixpart == 'MGI:', Accession.preferred == 1, Accession._logicaldb_key == 1, Accession._object_key == _nomen_key))) # one to many objects synonyms = db.relationship( "Synonym", primaryjoin="and_(NOM_Marker._nomen_key==Synonym._object_key, " "Synonym._mgitype_key==%d)" % _mgitype_key, order_by="Synonym.synonym", foreign_keys="[Synonym._object_key]") def __repr__(self): return "<NOM %s (%s) - %s>" % (self.symbol, self.mgiid, self.nomenstatus)
class Accession(db.Model,MGIModel): __tablename__ = "acc_accession" _accession_key = db.Column(db.Integer,primary_key=True) accid = db.Column(db.String()) prefixpart = db.Column(db.String()) numericpart = db.Column(db.Integer()) _logicaldb_key = db.Column(db.Integer(), mgi_fk("acc_logicaldb._logicaldb_key")) _object_key = db.Column(db.Integer()) _mgitype_key = db.Column(db.Integer()) private = db.Column(db.Integer()) preferred = db.Column(db.Integer()) _createdby_key = db.Column(db.Integer()) _modifiedby_key = db.Column(db.Integer()) logicaldb = db.column_property( db.select([LogicalDb.name]). where(LogicalDb._logicaldb_key==_logicaldb_key) ) # relationships logicaldb_object = db.relationship("LogicalDb", uselist=False) mgitype = db.relationship("MGIType", uselist=False, primaryjoin="MGIType._mgitype_key==Accession._mgitype_key", foreign_keys="[MGIType._mgitype_key]") references = db.relationship("Reference", secondary=AccessionReference.__table__, backref="accessions") emapsids = db.relationship("Accession", primaryjoin="Accession.accid==EmapSMapping.accid", secondary=EmapSMapping.__table__, secondaryjoin="Accession.accid==EmapSMapping.emapsid", foreign_keys="[EmapSMapping.emapsid, Accession.accid]", backref="adstructureids") vocterm = db.relationship("VocTerm", primaryjoin="VocTerm._term_key==Accession._object_key", foreign_keys="VocTerm._term_key", uselist=False ) def __repr__(self): return "<AccID %s>"%(self.accid,)
class VisualizationMethod(db.Model, MGIModel): """ Visualization Method for ProbePrep """ __tablename__ = "gxd_visualizationmethod" _visualization_key = db.Column(db.Integer, primary_key=True) visualization = db.Column(db.String())
class ProbeReferenceNotes(db.Model, MGIModel): __tablename__ = "prb_ref_notes" _reference_key = db.Column(db.Integer, mgi_fk("prb_reference._reference_key"), primary_key=True) sequencenum = db.Column(db.Integer, primary_key=True) note = db.Column(db.String())
class User(db.Model): __tablename__ = "cv_user" key = db.Column(db.Integer, primary_key=True) login = db.Column(db.String()) active = db.Column(db.Boolean)
class ImagePaneAssocView(db.Model, MGIModel): __tablename__ = "img_imagepane_assoc_view" _assoc_key = db.Column(db.Integer, primary_key=True) _imagepane_key = db.Column(db.Integer, mgi_fk("img_imagepane._imagepane_key"), primary_key=True) _object_key = db.Column(db.Integer()) _mgitype_key = db.Column(db.Integer()) _imageclass_key = db.Column(db.Integer()) isprimary = db.Column(db.Integer()) mgiid = db.Column(db.String()) allele = db.relationship( "Allele", primaryjoin="and_(ImagePaneAssocView._object_key==Allele._allele_key, " "ImagePaneAssocView._mgitype_key==11) ", foreign_keys="[Allele._allele_key]", uselist=False) genotype = db.relationship( "Genotype", primaryjoin= "and_(ImagePaneAssocView._object_key==Genotype._genotype_key, " "ImagePaneAssocView._mgitype_key==12) ", foreign_keys="[Genotype._genotype_key]", uselist=False)
class GxdLabel(db.Model, MGIModel): """ Label for ProbePrep + AntibodyPrep """ __tablename__ = "gxd_label" _label_key = db.Column(db.Integer, primary_key=True) label = db.Column(db.String())
class InSituResult(db.Model, MGIModel): __tablename__ = "gxd_insituresult" _result_key = db.Column(db.Integer, primary_key=True) _specimen_key = db.Column(db.Integer, mgi_fk("gxd_specimen._specimen_key")) _strength_key = db.Column(db.Integer) _pattern_key = db.Column(db.Integer) sequencenum = db.Column(db.Integer) resultnote = db.Column(db.String()) pattern = db.column_property( db.select([GxdPattern.pattern ]).where(GxdPattern._pattern_key == _pattern_key)) strength = db.column_property( db.select([GxdStrength.strength ]).where(GxdStrength._strength_key == _strength_key)) # Relationships imagepanes = db.relationship("ImagePane", secondary=InSituResultImagePane.__table__, backref="insituresults") structures = db.relationship("ADStructure", secondary=InSituResultStructure.__table__, order_by="ADStructure.printname", backref="insituresults")
class FixationMethod(db.Model, MGIModel): """ Fixation Method for Specimen """ __tablename__ = "gxd_fixationmethod" _fixation_key = db.Column(db.Integer, primary_key=True) fixation = db.Column(db.String())
class EmbeddingMethod(db.Model, MGIModel): """ Embedding Method for Specimen """ __tablename__ = "gxd_embeddingmethod" _embedding_key = db.Column(db.Integer, primary_key=True) embeddingmethod = db.Column(db.String())
class ProbeNoteChunk(db.Model, MGIModel): __tablename__ = "prb_notes" _probe_key = db.Column(db.Integer, db.ForeignKey("prb_probe._probe_key"), primary_key=True) note = db.Column(db.String()) sequencenum = db.Column(db.Integer, primary_key=True)
class ProbeSense(db.Model, MGIModel): """ Sense for ProbePrep """ __tablename__ = "gxd_probesense" _sense_key = db.Column(db.Integer, primary_key=True) sense = db.Column(db.String())
class MarkerDetailClipNoteChunk(db.Model, MGIModel): __tablename__ = "mrk_notes" _marker_key = db.Column(db.Integer, db.ForeignKey("mrk_marker._marker_key"), primary_key=True) note = db.Column(db.String()) sequencenum = db.Column(db.Integer, primary_key=True)
class ExperimentNoteChunk(db.Model, MGIModel): __tablename__ = "mld_expt_notes" _expt_key = db.Column(db.Integer, mgi_fk("mld_expts._expt_key"), primary_key=True) sequencenum = db.Column(db.Integer, primary_key=True) note = db.Column(db.String())
class ExperimentMarkerAssoc(db.Model, MGIModel): __tablename__ = "mld_expt_marker" _expt_key = db.Column(db.Integer, mgi_fk("mld_expts._expt_key"), primary_key=True) _marker_key = db.Column(db.Integer, mgi_fk("mrk_marker._marker_key"), primary_key=True) sequencenum = db.Column(db.Integer, primary_key=True) _allele_key = db.Column(db.Integer, mgi_fk("all_allele._allele_key")) _assay_type_key = db.Column(db.Integer) description = db.Column(db.String()) matrixdata = db.Column(db.Integer) # column properties assaytype = db.column_property( db.select([ ExperimentAssayType.description ]).where(ExperimentAssayType._assay_type_key == _assay_type_key)) # relationships allele = db.relationship("Allele") marker = db.relationship("Marker", backref="mapping_experiment_assocs") @property def matrixdata_display(self): return self.matrixdata and 'yes' or 'no'
class MLDReferenceNoteChunk(db.Model, MGIModel): __tablename__ = "mld_notes" _refs_key = db.Column(db.Integer, mgi_fk("bib_refs._refs_key"), primary_key=True) sequencenum = db.Column(db.Integer, primary_key=True) note = db.Column(db.String())
class ProbeAlias(db.Model, MGIModel): __tablename__ = "prb_alias" _alias_key = db.Column(db.Integer, primary_key=True) _reference_key = db.Column(db.Integer, mgi_fk("prb_reference._reference_key"), primary_key=True) alias = db.Column(db.String())
class MarkerLocationCache(db.Model, MGIModel): __tablename__ = "mrk_location_cache" _cache_key = db.Column(db.Integer, primary_key=True) _marker_key = db.Column(db.Integer, db.ForeignKey("mrk_marker._marker_key")) _organism_key = db.Column(db.Integer()) chromosome = db.Column(db.String()) cytogeneticoffset = db.Column(db.String()) cmoffset = db.Column(db.Float()) genomicchromosome = db.Column(db.String()) startcoordinate = db.Column(db.Float()) endcoordinate = db.Column(db.Float()) strand = db.Column(db.String()) mapunits = db.Column(db.String()) provider = db.Column(db.String()) version = db.Column(db.String()) @property def providerString(self): if not self.provider: return "" return "From %s annotation of %s" % (self.provider, self.version) def __repr__(self): if not self.startcoordinate or not self.endcoordinate: return "Chr%s" % (self.chromosome) return "Chr%s:%d-%d bp, %s strand" % (self.chromosome, self.startcoordinate, self.endcoordinate, self.strand)
class Sequence(db.Model, MGIModel): __tablename__ = "seq_sequence" _sequence_key = db.Column(db.Integer, primary_key=True) _sequencetype_key = db.Column(db.Integer) _sequencequality_key = db.Column(db.Integer) _sequencestatus_key = db.Column(db.Integer) _sequenceprovider_key = db.Column(db.Integer) _organism_key = db.Column(db.Integer, mgi_fk("mgi_organism._organism_key")) length = db.Column(db.String()) description = db.Column(db.String()) version = db.Column(db.String()) division = db.Column(db.String()) # constants _mgitype_key = 19 # column definitions type = db.column_property( db.select([VocTerm.term ]).where(VocTerm._term_key == _sequencetype_key)) #relationships markers = db.relationship( "Marker", secondary=SeqMarkerCache.__table__, primaryjoin="Sequence._sequence_key==SeqMarkerCache._sequence_key", secondaryjoin="SeqMarkerCache._marker_key==Marker._marker_key", foreign_keys="[Sequence._sequence_key,Marker._marker_key]", backref="sequences", order_by="Marker.symbol") # sequence can have multiple IDs accession_objects = db.relationship( "Accession", primaryjoin="and_(Accession._object_key==Sequence._sequence_key," "Accession.preferred==1," "Accession._mgitype_key==%s)" % (_mgitype_key), foreign_keys="[Accession._object_key]", order_by="Accession.accid") source = db.relationship("ProbeSource", secondary=SeqSourceAssoc.__table__, uselist=False)
class Synonym(db.Model, MGIModel): __tablename__ = "mgi_synonym" _synonym_key = db.Column(db.Integer, primary_key=True) _object_key = db.Column(db.Integer) _mgitype_key = db.Column(db.Integer) _synonymtype_key = db.Column(db.Integer) _refs_key = db.Column(db.Integer) synonym = db.Column(db.String()) def __repr__(self): return self.synonym
class SeqMarkerCache(db.Model, MGIModel): __tablename__ = "seq_marker_cache" _cache_key = db.Column(db.Integer, primary_key=True) _sequence_key = db.Column(db.Integer, mgi_fk("seq_sequence._sequence_key")) _marker_key = db.Column(db.Integer, mgi_fk("mrk_marker._marker_key")) _organism_key = db.Column(db.Integer) _logicaldb_key = db.Column(db.Integer, mgi_fk("acc_logicaldb._logicaldb_key")) accid = db.Column(db.String()) rawbiotype = db.Column(db.String()) _sequenceprovider_key = db.Column(db.Integer) _biotypeconflict_key = db.Column(db.Integer, mgi_fk("voc_term._term_key")) annotation_date = db.Column(db.DateTime) # constants # the biotype conflict term _biotypeconflict_yes_key = 5420767 # column properties sequenceprovider = db.column_property( db.select([VocTerm.term ]).where(VocTerm._term_key == _sequenceprovider_key)) marker = db.relationship("Marker") logicaldb_obj = db.relationship("LogicalDb") @property def sequence_url(self): """ This requires loading logicaldb_obj and logicaldb_obj.actualdb """ url = "" if self.logicaldb_obj and \ self.logicaldb_obj.actualdb: url = self.logicaldb_obj.actualdb.url.replace("@@@@", self.accid) return url
class ProbeMarkerCache(db.Model, MGIModel): __tablename__ = "prb_marker" _probe_key = db.Column(db.Integer, mgi_fk("prb_probe._probe_key"), primary_key=True) _marker_key = db.Column(db.Integer, mgi_fk("mrk_marker._marker_key"), primary_key=True) _refs_key = db.Column(db.Integer, mgi_fk("bib_refs._refs_key"), primary_key=True) relationship = db.Column(db.String())