class QCCrossMatch(Entity):
"""
2009-9-22
add unique constraint ('strainid', 'target_id', 'qc_method_id')
2008-08-26
"""
strain = ManyToOne("Strain",
colname='strainid',
ondelete='CASCADE',
onupdate='CASCADE')
target_id = Field(Integer)
qc_method = ManyToOne("%s.QCMethod" % __name__,
colname='qc_method_id',
ondelete='CASCADE',
onupdate='CASCADE')
mismatch_rate = Field(Float)
no_of_mismatches = Field(Integer)
no_of_non_NA_pairs = Field(Integer)
readme = ManyToOne("%s.README" % __name__,
colname='readme_id',
ondelete='CASCADE',
onupdate='CASCADE')
using_options(tablename='qc_cross_match')
using_table_options(mysql_engine='InnoDB')
using_table_options(
UniqueConstraint('strainid', 'target_id', 'qc_method_id'))
class HaploGroup(Entity):
"""
2009-3-30
table to store the haplotype groups generated from Alex
"""
short_name = Field(String(40), unique=True)
ref_ecotype = ManyToOne(
"Ecotype",
colname='ref_ecotypeid',
ondelete='CASCADE',
onupdate='CASCADE') #the ecotype with the best reference genotype.
latitude = Field(Float)
longitude = Field(Float)
max_snp_typing_error_rate = Field(Float)
comment = Field(String(8192))
ecotypes = ManyToMany("Ecotype",
tablename='haplo_group2ecotype',
ondelete='CASCADE',
onupdate='CASCADE')
created_by = Field(String(200))
updated_by = Field(String(200))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='haplo_group',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
using_table_options(UniqueConstraint('short_name', 'ref_ecotypeid'))
class HaploGroupPairwiseGeneticDist(Entity):
"""
2009-4-16
genetic distance between each haplogroup pair
"""
haplo_group1 = ManyToOne('HaploGroup',
colname='haplo_group_id1',
ondelete='CASCADE',
onupdate='CASCADE')
haplo_group2 = ManyToOne('HaploGroup',
colname='haplo_group_id2',
ondelete='CASCADE',
onupdate='CASCADE')
mismatch_rate = Field(Float)
no_of_mismatches = Field(Integer)
no_of_non_NA_pairs = Field(Integer)
created_by = Field(String(200))
updated_by = Field(String(200))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='haplo_group_pairwise_genetic_dist',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
using_table_options(UniqueConstraint('haplo_group_id1', 'haplo_group_id2'))
class Strain(Entity):
"""
2009-9-22
add 'replicate' into the unique constraint.
change type of replicate from boolean to integer
"""
ecotype = ManyToOne("Ecotype",
colname='ecotypeid',
ondelete='CASCADE',
onupdate='CASCADE')
extraction = ManyToOne("Extraction",
colname='extractionid',
ondelete='CASCADE',
onupdate='CASCADE')
seqinfo1 = ManyToOne("SeqInfo",
colname='seqinfoid1',
ondelete='CASCADE',
onupdate='CASCADE')
seqinfo2 = ManyToOne("SeqInfo",
colname='seqinfoid2',
ondelete='CASCADE',
onupdate='CASCADE')
seqinfo3 = ManyToOne("SeqInfo",
colname='seqinfoid3',
ondelete='CASCADE',
onupdate='CASCADE')
seqinfo4 = ManyToOne("SeqInfo",
colname='seqinfoid4',
ondelete='CASCADE',
onupdate='CASCADE')
plateid = Field(String(25))
wellid = Field(String(3))
replicate = Field(Integer)
contaminant_type = ManyToOne("%s.ContaminantType" % __name__,
colname='contaminant_type_id',
ondelete='CASCADE',
onupdate='CASCADE')
call_qc_ls = OneToMany("%s.CallQC" % __name__)
ecotypeid_strainid2tg_ecotypeid = OneToOne("EcotypeIDStrainID2TGEcotypeID",
inverse="strain")
created_by = Field(String(128))
updated_by = Field(String(128))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='strain',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
using_table_options(
UniqueConstraint('ecotypeid', 'plateid', 'wellid', 'replicate'))
class known_partners(Entity):
id = Field(Integer, primary_key=True)
email = Field(String(100), required=True)
created = Field(DateTime, default=datetime.now)
users = OneToMany('sys_users')
using_options(tablename='known_partners')
using_table_options(mysql_engine='InnoDB')
class Citation(Entity):
"""
2012.6.6
"""
cit_id = Field(Integer) #- the unique id of citation
cit_key = Field(Text) # -- citation key
pubmed_id = Field(
Integer) # -- unique id in PubMed database (0 if not in PubMed)
medline_id = Field(
Integer) # -- unique id in MedLine database (0 if not in MedLine)
url = Field(Text) # -- URL associated with citation
text = Field(Text) #-- any text (usually article name and authors).
#-- The following characters are escaped in this text by a backslash:
#-- newline (appear as "\n"),
#-- tab character ("\t"),
#-- double quotes ('\"'),
#-- backslash character ("\\").
tax_list = ManyToMany("Node",
tablename='citation2node',
local_colname='citation_id')
# -- list of node ids separated by a single space
created_by = Field(String(256))
updated_by = Field(String(256))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='citation')
using_table_options(mysql_engine='InnoDB')
class Organism(Entity):
genus = Field(String(30))
species = Field(String(30))
using_options(tablename='organism',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class SNPsABAlleleMapping(Entity):
snp = ManyToOne('SNPs',
colname='snps_id',
ondelete='CASCADE',
onupdate='CASCADE')
allele_A_nt = Field(String(2))
allele_B_nt = Field(String(2))
tg_snps_name = Field(String(200))
NA_rate = Field(Float)
no_of_NAs = Field(Integer)
no_of_totals = Field(Integer)
relative_NA_rate = Field(Float)
relative_no_of_NAs = Field(Integer)
relative_no_of_totals = Field(Integer)
mismatch_rate = Field(Float)
no_of_mismatches = Field(Integer)
no_of_non_NA_pairs = Field(Integer)
readme = ManyToOne("README",
colname='readme_id',
ondelete='CASCADE',
onupdate='CASCADE')
created_by = Field(String(128))
updated_by = Field(String(128))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='snps_ab_allele_mapping',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class sys_groups(Entity):
gid = Field(Integer, primary_key=True)
groupname = Field(String(30), required=True)
group_password = Field(String(64), required=True)
users = OneToMany('sys_users')
groupusers = ManyToMany('sys_users', tablename='sys_groupmembers')
using_options(tablename='sys_groups')
using_table_options(mysql_engine='InnoDB')
class Extraction(Entity):
namelabel = Field(String(25))
datesubmitted = Field(DateTime, default=datetime.now)
notes = Field(String(255))
using_options(tablename='extraction',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class HaploGroupEigenValues(Entity):
"""
2009-4-16
table to store eigen values after PCA is applied on sequences from all haplo groups
"""
which_eigen = Field(Integer)
eigen_value = Field(Float)
variance_perc = Field(Float)
created_by = Field(String(200))
updated_by = Field(String(200))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='haplo_group_eigen_values',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
using_table_options(UniqueConstraint('which_eigen'))
class Card(Entity):
scanned_image = OneToOne('ScannedImage')
box = ManyToOne('Box')
box_index = Field(Integer)
card_id = Field(UnicodeText)
notes = Field(UnicodeText)
using_options(tablename='cards')
using_table_options(schema='inventory')
class Box(Entity):
name = Field(UnicodeText)
collection = ManyToOne('Collection')
collection_index = Field(Integer)
cards = OneToMany('Card')
using_options(tablename='boxes')
using_table_options(schema='inventory')
class Ecotype(Entity):
"""
2009-3-30
add column haplo_groups.
"""
donor = ManyToOne("Person",
colname='donorid',
ondelete='CASCADE',
onupdate='CASCADE')
collector = ManyToOne("Person",
colname='collectorid',
ondelete='CASCADE',
onupdate='CASCADE')
site = ManyToOne("Site",
colname='siteid',
ondelete='CASCADE',
onupdate='CASCADE')
trip = ManyToOne("Trip",
colname='tripid',
ondelete='CASCADE',
onupdate='CASCADE')
organism = ManyToOne("Organism",
colname='organismid',
ondelete='CASCADE',
onupdate='CASCADE')
cross = ManyToOne("Cross",
colname='crossid',
ondelete='CASCADE',
onupdate='CASCADE')
name = Field(String(50))
alias = Field(String(50))
description = Field(String(270))
barcode = Field(String(14))
stockparent = Field(String(10))
nativename = Field(String(50))
collectiondate = Field(DateTime)
latitude = Field(Float)
longitude = Field(Float)
locationquality = Field(Integer)
elevation = Field(String(5))
dnastatus = Field(Integer)
bulkstatus = Field(Integer)
bulkdate = Field(DateTime)
labderived = Field(Boolean)
incompleteplex = Field(Integer)
haplo_groups = ManyToMany("HaploGroup",
tablename='haplo_group2ecotype',
ondelete='CASCADE',
onupdate='CASCADE')
geographic_integrity = ManyToOne("GeographicIntegrity",
colname='geographic_integrity_id',
ondelete='CASCADE',
onupdate='CASCADE')
using_options(tablename='ecotype',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class Site(Entity):
address = ManyToOne("Address",
colname='addressid',
ondelete='CASCADE',
onupdate='CASCADE')
name = Field(String(100))
description = Field(String(500))
using_options(tablename='site', metadata=__metadata__, session=__session__)
using_table_options(mysql_engine='InnoDB')
class Region(Entity):
name = Field(String(50))
description = Field(Text)
created = Field(DateTime, default=datetime.now)
modified = Field(DateTime)
using_options(tablename='region',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class FilteredCalls(Entity):
"""
2009-3-30
table to store calls filtered (bad calls) by alex
"""
ecotype = ManyToOne("Ecotype",
colname='ecotypeid',
ondelete='CASCADE',
onupdate='CASCADE')
snp = ManyToOne("SNPs",
colname='snpid',
ondelete='CASCADE',
onupdate='CASCADE')
allele = Field(String(5)) #'call' is mysql reserved keyword
using_options(tablename='filtered_calls',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
using_table_options(UniqueConstraint('snpid', 'ecotypeid'))
class Calls_BySeq(Entity):
"""
2009-9-22
add the unique constraint: UniqueConstraint('ecotypeid', 'plateid', 'wellid', 'snpid', 'replicate')
"""
snp = ManyToOne("SNPs",
colname='snpid',
ondelete='CASCADE',
onupdate='CASCADE')
ecotype = ManyToOne("Ecotype",
colname='ecotypeid',
ondelete='CASCADE',
onupdate='CASCADE')
extraction = ManyToOne("Extraction",
colname='extractionid',
ondelete='CASCADE',
onupdate='CASCADE')
seqinfo = ManyToOne("SeqInfo",
colname='seqinfoid',
ondelete='CASCADE',
onupdate='CASCADE')
plateid = Field(String(25))
wellid = Field(String(3))
call1 = Field(String(1))
call2 = Field(String(1))
ext1area = Field(Float)
ext2area = Field(Float)
probearea = Field(Float)
ext1height = Field(Float)
ext2height = Field(Float)
probeheight = Field(Float)
ext1snr = Field(Float)
ext2snr = Field(Float)
probesnr = Field(Float)
replicate = Field(Integer)
description = Field(String(40))
using_options(tablename='calls_byseq',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
using_table_options(
UniqueConstraint('ecotypeid', 'plateid', 'wellid', 'snpid',
'replicate'))
class Cross(Entity):
maternalid = Field(Integer)
paternalid = Field(Integer)
name = Field(String(40))
barcode = Field(String(25))
generation = Field(Integer)
using_options(tablename='crosses',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class ScannedImage(Entity):
#this is listed as ManyToOne, though there will only ever be
#a single scanned image per card
card = ManyToOne('models.Card')
status = Field(Enum('unprocessed', 'candidate_match', 'confident_match'),
index=True)
scan_png = Field(BLOB)
using_options(tablename='scanned_images')
using_table_options(schema='scanned_images')
class Calls(Entity):
"""
2009-9-22
add the unique constraint: UniqueConstraint('strainid', 'snpid')
"""
strain = ManyToOne("Strain",
colname='strainid',
ondelete='CASCADE',
onupdate='CASCADE')
snp = ManyToOne("SNPs",
colname='snpid',
ondelete='CASCADE',
onupdate='CASCADE')
allele = Field(String(5)) #'call' is mysql reserved keyword
using_options(tablename='calls',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
using_table_options(UniqueConstraint('strainid', 'snpid'))
class Name(Entity):
"""
2012.6.6
taxonomy name
"""
tax = ManyToOne('%s.Node' % __name__,
colname='tax_id',
ondelete='CASCADE',
onupdate='CASCADE')
name_txt = Field(Text)
unique_name = Field(Text)
name_class = Field(Text)
created_by = Field(String(256))
updated_by = Field(String(256))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='name')
using_table_options(mysql_engine='InnoDB')
using_table_options(UniqueConstraint('tax_id', 'name_txt', 'name_class'))
class Country(Entity):
name = Field(String(100))
abbr = Field(String(10))
capital = Field(Text)
latitude = Field(Float)
longitude = Field(Float)
using_options(tablename='country',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class SNPset(Entity):
name = Field(String(200), nullable=False, unique=True)
description = Field(String(4000))
created_by = Field(String(128))
updated_by = Field(String(128))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='snpset',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class Submitter(Entity):
first = Field(String(50))
last = Field(String(50))
organization = Field(String(50))
email = Field(String(50))
created = Field(DateTime, default=datetime.now)
modified = Field(DateTime)
using_options(tablename='submitter',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class Delnode(Entity):
"""
2012.6.6
"""
tax_id = Field(Integer, unique=True) # -- taxonomy database division id
created_by = Field(String(256))
updated_by = Field(String(256))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='delnode')
using_table_options(mysql_engine='InnoDB')
class SeqInfo(Entity):
platename = Field(String(40))
experiment = Field(String(40))
chip = Field(String(40))
experiment_type = Field(String(40))
ms_name = Field(String(40))
creation_date = Field(DateTime, default=datetime.now)
using_options(tablename='seqinfo',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class HaploGroupPCValues(Entity):
"""
2009-4-16
table to store principle component values after PCA is applied on sequences from all haplo groups
"""
haplo_group = ManyToOne('HaploGroup',
colname='haplo_group_id',
ondelete='CASCADE',
onupdate='CASCADE')
which_pc = Field(Integer)
pc_value = Field(Float)
created_by = Field(String(200))
updated_by = Field(String(200))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='haplo_group_pc_values',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
using_table_options(UniqueConstraint('haplo_group_id', 'which_pc'))
class README(Entity):
#2008-08-07
title = Field(String(2000))
description = Field(String(60000))
created_by = Field(String(128))
updated_by = Field(String(128))
date_created = Field(DateTime, default=datetime.now)
date_updated = Field(DateTime)
using_options(tablename='readme',
metadata=__metadata__,
session=__session__)
using_table_options(mysql_engine='InnoDB')
class SNPs(Entity):
organism = ManyToOne("Organism",
colname='organismid',
ondelete='CASCADE',
onupdate='CASCADE')
snpid = Field(String(20), unique=True)
dir = Field(String(1))
chromosome = Field(Integer)
position = Field(Integer)
refcall = Field(String(1))
using_options(tablename='snps', metadata=__metadata__, session=__session__)
using_table_options(mysql_engine='InnoDB')