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)