class Physical_data(db.Model):
__tablename__ = 'public_physical_data'
id = db.Column(db.Integer, primary_key=True)
initialTemp = db.Column(db.Float)
sampleTemp = db.Column(db.Float)
pH = db.Column(db.Float)
redox = db.Column(db.Float)
dO = db.Column(db.Float)
conductivity = db.Column(db.Float)
size = db.Column(db.String(20))
colour = db.Column(db.String(7))
ebullition = db.Column(db.String(50))
turbidity = db.Column(db.Float)
dnaVolume = db.Column(db.Float)
ferrousIronAbs = db.Column(db.Float)
def __init__(self, phys_id,iTemp,sTemp,ph,red,dis_ox,cond,date,size,colour,ebul,turb,dnaVol,ferIron):
self.id = phys_id
self.initialTemp = iTemp
self.pH = ph
self.redox = red
self.dO = dis_ox
self.conductivity = cond
self.size = size
self.colour = colour
self.ebullition = ebul
self.turbidity = turb
self.dnaVolume = dnaVol
self.ferrousIronAbs = ferIron
self.date_gathered = date
self.sampleTemp = sTemp
class User(db.Model):
username = db.Column(db.String(100), primary_key=True)
password = db.Column(db.String(100))
def __init__(self,username,password):
self.username = username
self.password = password
def check_password(self, password):
return check_password_hash(self.password,password)
class Taxonomy(db.Model):
__tablename__ = 'public_taxonomy'
id = db.Column(db.Integer, primary_key=True)
domain = db.Column(db.String(100), nullable=True)
phylum = db.Column(db.String(100), nullable=True)
Class = db.Column(db.String(100), nullable=True)
# read_count = db.Column(db.Integer, nullable=True)
def __getitem__(self,index):
return getattr(self,index)
class Sample(db.Model):
__tablename__ = 'public_sample'
id = db.Column(db.Integer, primary_key=True)
date_gathered = db.Column(db.DateTime, nullable=False)
sampler = db.Column(db.String(50), nullable=False)
sample_number = db.Column(db.String(50), nullable=False)
location_id = db.Column(db.Integer, db.ForeignKey("public_location.id"))
phys_id = db.Column(db.Integer, db.ForeignKey("public_physical_data.id"))
chem_id = db.Column(db.Integer, db.ForeignKey("public_chemical_data.id"))
location = db.relationship("Location",backref="Sample",lazy="select")
phys = db.relationship("Physical_data",backref="Sample",lazy="select")
image = db.relationship("Image",backref="Sample",lazy="select" , uselist=True)
chem = db.relationship("Chemical_data",backref="Sample",lazy="select")
samp_taxon = db.relationship("Sample_Taxonomy",backref="Sample",lazy="select")
taxon = association_proxy('samp_taxon', 'taxon')
def __init__(self,id,date,location,physID,chemID,sampleNum):
self.id = id
self.date_gathered = date
self.location_id = location
self.phys_id = physID
self.chem_id = chemID
self.sample_number = sampleNum
def getTaxonomy(self):
# Queries of views without primary keys don't fit very well in the
# SQLAlchemy ORM, so query the DB with raw SQL
column_names = ["read_count", "domain", "phylum", "class", "order", "family", "genus", "species"]
query = text(
'select `' + ('`,`'.join(column_names)) + '` from public_confident_taxonomy where sample_id = :sample_id' +
' order by `'+ ('`,`'.join(column_names[1:])) +'`'
)
rows = db.engine.execute(query, sample_id=self.id).fetchall()
return [dict(zip(column_names,row)) for row in rows]
def hasTaxonomy(self):
query = text(
'select * from public_sample_taxonomy where sample_id = :sample_id limit 1'
)
rows = db.engine.execute(query, sample_id=self.id).fetchall()
return len(rows) > 0
def __repr__(self):
return '<Sample {0} {1} {2}>'.format(self.id,self.location_id,self.date_gathered)
class Image(db.Model):
__tablename__ = 'public_image'
id = db.Column(db.Integer, primary_key=True)
sample_id = db.Column(db.Integer, db.ForeignKey("public_sample.id"), nullable=False)
image_path = db.Column(db.String (150), nullable = False)
image_type = db.Column(db.String (150))
def __init__(self,id,sid,iPath,iName):
self.id = id
self.sample_id = sid
self.image_path = iPath
self.image_name = iName
class Location(db.Model):
__tablename__ = 'public_location'
id = db.Column(db.Integer, primary_key=True)
feature_name = db.Column(db.String(50))
feature_system = db.Column(db.String(50))
district = db.Column(db.String(50))
location = db.Column(db.String(50))
lat = db.Column(db.Float)
lng = db.Column(db.Float)
description = db.Column(db.String(200))
private = db.Column(db.Boolean)
access = db.Column(db.String(15))
feature_type = db.Column(db.String(50))
def latestSample(self):
return Sample.query.filter(Location.id == self.id, Sample.location_id == Location.id ).order_by(Sample.location_id,Sample.date_gathered.desc()).first()
@staticmethod
def latestSampleIdsAllLocations():
query = text('select id from public_sample s where s.date_gathered = (select max(date_gathered) from public_sample where location_id = s.location_id limit 1 )')
rows = db.engine.execute(query).fetchall()
sample_ids = []
for row in rows:
sample_ids.append(row[0])
return sample_ids
def __init__(self,location_id,fName,latPos,lngPos,fSystem,dist,loc,desc,private,access):
self.id = location_id
self.feature_name = fName
self.feature_system = fSystem
self.district = dist
self.location = loc
self.lat = latPos
self.lng = lngPos
self.description = desc
self.private = private
self.access = access
def __repr__(self):
return '<Location {0} {1}>'.format(self.id,self.feature_name)