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())
