def setup(session):
""" populate database with essential GenAF data, mostly taken from FATools constants """
EK.bulk_insert(ek_initlist, dbsession=session)
# get default group, which is system
adm_group = Group.search("_DataAdm_", dbsession=session)
# create undefined marker
marker = Marker(code="undefined", species="X")
cerr("INFO - marker 'undefined' created.")
session.add(marker)
# create ladder marker
marker = Marker(code="ladder", species="X")
cerr("INFO - marker 'ladder' created.")
session.add(marker)
# create combined marker
marker = Marker(code="combined", species="X")
cerr("INFO - marker 'combined' created.")
session.add(marker)
# create default panel
panel = Panel(code="undefined", group_id=adm_group.id)
cerr("INFO - panel 'undefined' created.")
session.add(panel)
# create default batch
batch = Batch(code="default", group_id=adm_group.id, assay_provider_id=adm_group.id, species="X")
cerr("INFO - default batch created.")
session.add(batch)
def search(country, level1='', level2='', level3='', level4='', auto=False, dbsession=None):
assert dbsession
country_id = EK._id(country.strip(), dbsession, '@REGION', auto)
level1_id = EK._id(level1.strip(), dbsession, '@REGION', auto)
level2_id = EK._id(level2.strip(), dbsession, '@REGION', auto)
level3_id = EK._id(level3.strip(), dbsession, '@REGION', auto)
level4_id = EK._id(level4.strip(), dbsession, '@REGION', auto)
q = Location.query(dbsession).filter(
and_(Location.country_id == country_id,
Location.level1_id == level1_id,
Location.level2_id == level2_id,
Location.level3_id == level3_id,
Location.level4_id == level4_id) )
r = q.all()
if len(r) == 0 and auto:
location = Location( country_id = country_id,
level1_id = level1_id,
level2_id = level2_id,
level3_id = level3_id,
level4_id = level4_id )
dbsession.add( location )
dbsession.flush([location])
return location
return r[0]
def grep(term, dbsession):
regions = EK.get_members('@REGION', dbsession).filter( EK.key.contains( term.lower() ) )
ids = [ r.id for r in regions ]
return Location.query(dbsession).filter(
or_( Location.country_id.in_( ids ), Location.level1_id.in_( ids ),
Location.level2_id.in_( ids ), Location.level3_id.in_( ids ),
Location.level4_id.in_( ids ) ) )
class Subject(BaseMixIn, Base):
""" subjects """
__tablename__ = 'subjects'
code = Column(types.String(24), unique=True, nullable=False)
yearofbirth = Column(types.Float, nullable=False)
gender = Column(types.String(1), nullable=False)
notes = deferred( Column(types.Text(), nullable=False, server_default='') )
## optional fields, unique personal identity
name = Column(types.String(48), nullable=False, server_default='')
birthday = Column(types.Date)
birthplace = Column(types.String(32), nullable=False, server_default='')
idnumber = Column(types.String(32), nullable=False, server_default='')
nationality_id = Column(types.Integer, ForeignKey('eks.id'), nullable=False)
nationality = EK.proxy('nationality_id', '@REGION')
## custom fields
int1 = Column(types.Integer, nullable=False, server_default='0') # custom usage
int2 = Column(types.Integer, nullable=False, server_default='0') # custom usage
string1 = Column(types.String(16), nullable=False, server_default='') # custom usage
string2 = Column(types.String(16), nullable=False, server_default='') # custom usage
flags = Column(types.Integer, nullable=False, server_default='0')
__table_args__ = ( UniqueConstraint( 'code', 'gender', 'yearofbirth' ), {} )
@staticmethod
def search( code, gender=None, yearofbirth=None, auto=False, dbsession=None ):
assert dbsession
q = Subject.query().filter( and_( Subject.code == code, Subject.gender == gender,
Subject.yearofbirth == yearofbirth ) )
r = q.all()
if len(r) == 0:
if auto:
subject = Subject( code = code, gender = gender, yearofbirth = yearofbirth )
dbsession.add( subject )
#dbsession.flush()
return subject
return None
return r[0]
@staticmethod
def autocode():
return '#%08x' % random.randrange(256**4)
def update(self, d):
if type(d) == dict:
if not self.nationality_id:
self.nationality = ''
if d.get('nationality', None) is not None:
self.nationality = d['nationality']
cerr('set nationality to %s' % d['nationality'])
else:
raise NotImplementedError('can only update from dict')
def setup( dbh ):
""" populate the database with basic, essential data """
if get_datalogger():
get_clsreg().sync()
session.commit()
dbsession = dbh.session()
EK.bulk_update( ek_initlist, dbsession=dbsession )
Group.bulk_insert( essential_groups, dbsession=dbsession )
UserClass.bulk_insert( system_userclass, dbsession=dbsession )
group_id = Group._id('_SysAdm_', dbsession)
file = File( path='/', group_id = group_id, permanent = True )
dbsession.add( file )
file.type = 'file/folder'
file.mimetype = 'application/x-directory'
cerr('INFO: root password is %s\n' % root_password)
class SubjectStringData(Base):
__tablename__ = 'subjectstrings'
id = Column(types.Integer, primary_key=True)
subject_id = Column(types.Integer, ForeignKey('subjects.id', ondelete='CASCADE'),
nullable=False)
key_id = Column(types.Integer, ForeignKey('eks.id', ondelete='CASCADE'),
nullable=False)
key = EK.proxy('key_id', '@EXTFIELD')
value = Column(types.String(64), nullable=False)
__table_args__ = ( UniqueConstraint('subject_id', 'key_id'), {} )
def search(code, session):
""" provide case-insensitive search for marker code """
if '/' in code:
species, code = code.split('/')
if callable(session):
session = session()
species_id = EK._id(species, dbsession=session)
q = Marker.query(session).filter( func.lower(Marker.code) == func.lower(code),
Marker.species_id == species_id)
else:
q = Marker.query(session).filter( func.lower(Marker.code) == func.lower(code) )
return q.one()
def setup_db( *ops ):
""" setup the database (create tables, etc) and populate with basic data """
base = get_base()
# WARN: use alembic to create initial tables
#base.metadata.create_all()
with transaction.manager:
if get_datalogger():
get_clsreg().sync()
with transaction.manager:
EK.bulk_insert( ek_initlist )
Group.bulk_insert( essential_groups )
file = File( path='/', type='file/folder', mimetype='application/x-directory',
group_id = Group._id('_SysAdm_'), permanent = True )
dbsession.add( file )
for op in ops:
op()
def update(self, obj):
if type(obj) == dict:
if obj.get('storage') is not None:
self.storage = obj.get('storage') # blood storage
if obj.get('method') is not None:
self.method = obj.get('method') # blood withdrawal method
if obj.get('pcr_method') is not None:
self.pcr_method_id = EK._id(obj.get('pcr_method'), grp = '@PCR_METHOD',
dbsession = object_session(self), auto=True)
if obj.get('pcr') is not None:
self.pcr = obj.get('pcr')
if obj.get('microscopy') is not None:
self.microscopy = obj.get('microscopy')
if obj.get('type') is not None:
self.type = obj.get('type')
if obj.get('day') is not None:
self.day = int(obj.get('day'))
if self.day == 0 and self.type == '':
self.type = 'P'
if obj.get('case_detection') is not None:
self.passive_case_detection = obj.get('case_detection').lower().startswith('y')
if obj.get('symptomatic_status') is not None:
self.symptomatic_status = obj.get('symptomatic_status').lower().startswith('y')
if obj.get('nationality_status') is not None:
self.nationality_status = obj.get('nationality_status').lower().startswith('y')
if obj.get('imported_case') is not None:
self.imported_case = obj.get('imported_case').lower().startswith('y')
if obj.get('parasite_density') is not None:
self.parasitemia = int(obj.get('parasite_density', -1))
# deals with subject
subject_code = obj.get('subject_code', None)
related_sample = obj.get('related_sample', None)
session = object_session(self)
if subject_code:
# find subject
subject = Subject.search(code=subject_code, dbsession = session)
self.subject_id = subject.id
elif related_sample:
# find sample
sample = self.batch.search_sample(related_sample)
if sample is None:
raise RuntimeError('ERROR for sample code %s: related sample code %s does not exist' % (self.code, related_sample))
self.subject_id = sample.subject_id
elif not self.subject:
# create new subject
while True:
code = '#' + random_string(8)
# we need to check subject code first !
if Subject.query(session).filter(Subject.code == code).count() == 0:
break
subject = Subject( code = code, gender = obj['gender'],
yearofbirth = obj['yearofbirth'] )
session.add(subject)
subject.update( obj )
session.flush([subject])
cerr('subject %s nationality_id %d' % (subject.code, subject.nationality_id))
self.subject = subject
#self.subject_id = 0
# now if subject_code &
else:
raise NotImplementedError('PROG/ERR - not implemented yet')
super().update( obj )
return
class PathoSample(get_dbhandler_class().Sample):
""" PlasmogenSample - Plasmodium Sample
This class contains information about Plasmodium samples
"""
passive_case_detection = Column(types.Boolean, nullable=True)
symptomatic_status = Column(types.Boolean, nullable=True)
imported_case = Column(types.Boolean, nullable=True)
nationality_status = Column(types.Boolean, nullable=True)
storage_id = Column(types.Integer, ForeignKey('eks.id'), nullable=False)
storage = EK.proxy('storage_id', '@BLOOD-STORAGE')
""" sample storage method """
method_id = Column(types.Integer, ForeignKey('eks.id'), nullable=False)
method = EK.proxy('method_id', '@BLOOD-WITHDRAWAL')
""" blood withdrawal method """
pcr_method_id = Column(types.Integer, ForeignKey('eks.id'), nullable=False)
pcr_method = EK.proxy('pcr_method_id', '@PCR-METHOD')
""" PCR method for detection """
pcr_id = Column(types.Integer, ForeignKey('eks.id'), nullable=False)
pcr = EK.proxy('pcr_id', '@SPECIES')
""" species identification based on PCR """
microscopy_id = Column(types.Integer, ForeignKey('eks.id'), nullable=False)
microscopy = EK.proxy('microscopy_id', '@SPECIES')
""" species identification based on microscopy """
parasitemia = Column(types.Float, nullable=False, server_default='-1')
recurrent = Column(types.Boolean, nullable=False, default=False)
""" if this a recurrent case """
subject_id = Column(types.Integer, ForeignKey('subjects.id'), nullable=False)
subject = relationship(Subject, uselist=False,
backref=backref("samples", lazy='dynamic', passive_deletes=True))
""" link to subject/individual """
day = Column(types.Integer, nullable=False, server_default = '0')
""" sampling day for a particular subject/individual """
__mapper_args__ = { 'polymorphic_identity': 2 }
def update(self, obj):
if type(obj) == dict:
if obj.get('storage') is not None:
self.storage = obj.get('storage') # blood storage
if obj.get('method') is not None:
self.method = obj.get('method') # blood withdrawal method
if obj.get('pcr_method') is not None:
self.pcr_method_id = EK._id(obj.get('pcr_method'), grp = '@PCR_METHOD',
dbsession = object_session(self), auto=True)
if obj.get('pcr') is not None:
self.pcr = obj.get('pcr')
if obj.get('microscopy') is not None:
self.microscopy = obj.get('microscopy')
if obj.get('type') is not None:
self.type = obj.get('type')
if obj.get('day') is not None:
self.day = int(obj.get('day'))
if self.day == 0 and self.type == '':
self.type = 'P'
if obj.get('case_detection') is not None:
self.passive_case_detection = obj.get('case_detection').lower().startswith('y')
if obj.get('symptomatic_status') is not None:
self.symptomatic_status = obj.get('symptomatic_status').lower().startswith('y')
if obj.get('nationality_status') is not None:
self.nationality_status = obj.get('nationality_status').lower().startswith('y')
if obj.get('imported_case') is not None:
self.imported_case = obj.get('imported_case').lower().startswith('y')
if obj.get('parasite_density') is not None:
self.parasitemia = int(obj.get('parasite_density', -1))
# deals with subject
subject_code = obj.get('subject_code', None)
related_sample = obj.get('related_sample', None)
session = object_session(self)
if subject_code:
# find subject
subject = Subject.search(code=subject_code, dbsession = session)
self.subject_id = subject.id
elif related_sample:
# find sample
sample = self.batch.search_sample(related_sample)
if sample is None:
raise RuntimeError('ERROR for sample code %s: related sample code %s does not exist' % (self.code, related_sample))
self.subject_id = sample.subject_id
elif not self.subject:
# create new subject
while True:
code = '#' + random_string(8)
# we need to check subject code first !
if Subject.query(session).filter(Subject.code == code).count() == 0:
break
subject = Subject( code = code, gender = obj['gender'],
yearofbirth = obj['yearofbirth'] )
session.add(subject)
subject.update( obj )
session.flush([subject])
cerr('subject %s nationality_id %d' % (subject.code, subject.nationality_id))
self.subject = subject
#self.subject_id = 0
# now if subject_code &
else:
raise NotImplementedError('PROG/ERR - not implemented yet')
super().update( obj )
return
@staticmethod
def search( subject_id = None, code=None, collection_date=None, location_id=None,
batch_id = None, auto=False, _q = None ):
if not _q:
_q = PlasmoSample.query()
if code:
_q = _q.filter( PlasmoSample.code == code )
if collection_date:
_q = _q.filter( PlasmoSample.collection_date == collection_date )
if location_id:
_q = _q.filter( PlasmoSample.location_id == location_id )
if batch_id:
_q = _q.filter( PlasmoSample.batch_id == batch_id )
#q = q.filter( and_( *filters ) )
r = _q.all()
if len(r) == 0:
if auto:
if not batch_id:
raise RuntimeError('Sample searching with auto-creation must be supplied '
'with batch_id' )
sample = PlasmoSample( subject_id=subject_id, code=code,
location_id=location_id, collection_date=collection_date,
batch_id = batch_id)
#raise RuntimeError(collection_date)
print('Creating sample with code: %s with batch_id: %d' %
(sample.code, sample.batch_id))
dbsession.add( sample )
return sample
return None
if len(r) == 1:
return r[0]
return r
@staticmethod
def csv2dict( *args, **kwargs ):
return dictfmt.csv2dict( *args, **kwargs )
def update(self, obj):
if type(obj) == dict:
if obj.get('storage') is not None:
self.storage = obj.get('storage') # blood storage
if obj.get('method') is not None:
self.method = obj.get('method') # blood withdrawal method
if obj.get('pcr_method') is not None:
self.pcr_method_id = EK._id(obj.get('pcr_method'), grp = '@PCR_METHOD',
dbsession = object_session(self), auto=True)
if obj.get('pcr') is not None:
self.pcr = obj.get('pcr')
if obj.get('microscopy') is not None:
self.microscopy = obj.get('microscopy')
if obj.get('type') is not None:
self.type = obj.get('type')
if obj.get('day') is not None:
self.day = int(obj.get('day'))
if self.day == 0 and self.type == '':
self.type = 'P'
if obj.get('case_detection') is not None:
self.passive_case_detection = obj.get('case_detection').lower().startswith('y')
if obj.get('symptomatic_status') is not None:
self.symptomatic_status = obj.get('symptomatic_status').lower().startswith('y')
if obj.get('nationality_status') is not None:
self.nationality_status = obj.get('nationality_status').lower().startswith('y')
if obj.get('imported_case') is not None:
self.imported_case = obj.get('imported_case').lower().startswith('y')
if obj.get('parasite_density') is not None:
self.parasitemia = int(obj.get('parasite_density', -1))
# deals with subject
subject_code = obj.get('subject_code', None)
related_sample = obj.get('related_sample', None)
session = object_session(self)
if subject_code:
# find subject
subject = Subject.search(code=subject_code, dbsession = session)
self.subject_id = subject.id
elif related_sample:
# find sample
sample = self.batch.search_sample(related_sample)
if sample is None:
raise RuntimeError('ERROR for sample code %s: related sample code %s does not exist' % (self.code, related_sample))
self.subject_id = sample.subject_id
elif not self.subject:
# create new subject
while True:
code = '#' + random_string(8)
# we need to check subject code first !
if Subject.query(session).filter(Subject.code == code).count() == 0:
break
subject = Subject( code = code, gender = obj['gender'],
yearofbirth = obj['yearofbirth'] )
session.add(subject)
subject.update( obj )
session.flush([subject])
cerr('subject %s nationality_id %d' % (subject.code, subject.nationality_id))
self.subject = subject
#self.subject_id = 0
# now if subject_code &
else:
raise NotImplementedError('PROG/ERR - not implemented yet')
super().update( obj )
return
if obj.passive_case_detection is not None:
self.passive_case_detection = obj.passive_case_detection
if obj.storage_id is not None:
self.storage_id = 0 #obj.storage_id
if obj.method_id is not None:
self.method_id = 0 #obj.method_id
if obj.pcr_method_id is not None:
self.pcr_method_id = 0 #obj.pcr_method_id
if obj.pcr_id is not None:
self.pcr_id = 0 #obj.pcr_id
if obj.microscopy_id is not None:
self.microscopy_id = 0 #obj.microscopy_id
if obj.recurent is not None:
self.recurrent = obj.recurrent
if obj.subject_is:
self.subject_id = obj.subject_id
def setup(dbh):
assert dbh
EK.bulk_update(ek_initlist, dbsession=dbh.session())
def setup(dbsession):
assert dbsession
EK.bulk_insert( ek_initlist, dbsession=dbsession)
