class LimsApi():
def __init__(self, lims_credentials: Optional[DbCredentials] = None):
if lims_credentials:
self.lims_db = PostgresQueryMixin(
dbname=lims_credentials.dbname,
user=lims_credentials.user,
host=lims_credentials.host,
password=lims_credentials.password,
port=lims_credentials.port)
else:
# Currying is equivalent to decorator syntactic sugar
self.lims_db = (credential_injector(LIMS_DB_CREDENTIAL_MAP)(
PostgresQueryMixin)())
def get_experiment_id(self):
return self.experiment_id
def get_behavior_tracking_video_filepath_df(self):
query = '''
SELECT wkf.storage_directory || wkf.filename AS raw_behavior_tracking_video_filepath, attachable_type
FROM well_known_files wkf WHERE wkf.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'RawBehaviorTrackingVideo')
'''
return pd.read_sql(query, self.lims_db.get_connection())
def get_eye_tracking_video_filepath_df(self):
query = '''
SELECT wkf.storage_directory || wkf.filename AS raw_behavior_tracking_video_filepath, attachable_type
FROM well_known_files wkf WHERE wkf.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'RawEyeTrackingVideo')
'''
return pd.read_sql(query, self.lims_db.get_connection())
def get_ophys_experiment_df():
api = PostgresQueryMixin()
query = '''
SELECT
oec.visual_behavior_experiment_container_id as container_id,
oec.ophys_experiment_id,
oe.workflow_state,
d.full_genotype as full_genotype,
id.depth as imaging_depth,
st.acronym as targeted_structure,
os.name as session_name,
equipment.name as equipment_name
FROM ophys_experiments_visual_behavior_experiment_containers oec
LEFT JOIN ophys_experiments oe ON oe.id = oec.ophys_experiment_id
LEFT JOIN ophys_sessions os ON oe.ophys_session_id = os.id
LEFT JOIN specimens sp ON sp.id=os.specimen_id
LEFT JOIN donors d ON d.id=sp.donor_id
LEFT JOIN imaging_depths id ON id.id=os.imaging_depth_id
LEFT JOIN structures st ON st.id=oe.targeted_structure_id
LEFT JOIN equipment ON equipment.id=os.equipment_id
'''
return pd.read_sql(query, api.get_connection())
def get_containers_df(only_passed=True):
api = PostgresQueryMixin()
if only_passed is True:
query = '''
SELECT *
FROM visual_behavior_experiment_containers vbc
WHERE workflow_state IN ('container_qc','publish');
'''
else:
query = '''
SELECT *
FROM visual_behavior_experiment_containers vbc
'''
return pd.read_sql(
query,
api.get_connection()).rename(columns={'id': 'container_id'})[[
'container_id', 'specimen_id', 'workflow_state'
]]
class OphysLimsApi(CachedInstanceMethodMixin):
def __init__(self,
ophys_experiment_id: int,
lims_credentials: Optional[DbCredentials] = None):
self.ophys_experiment_id = ophys_experiment_id
if lims_credentials:
self.lims_db = PostgresQueryMixin(
dbname=lims_credentials.dbname,
user=lims_credentials.user,
host=lims_credentials.host,
password=lims_credentials.password,
port=lims_credentials.port)
else:
# Currying is equivalent to decorator syntactic sugar
self.lims_db = (credential_injector(LIMS_DB_CREDENTIAL_MAP)(
PostgresQueryMixin)())
def get_ophys_experiment_id(self):
return self.ophys_experiment_id
@memoize
def get_ophys_experiment_dir(self):
query = '''
SELECT oe.storage_directory
FROM ophys_experiments oe
WHERE oe.id = {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_nwb_filepath(self):
query = '''
SELECT wkf.storage_directory || wkf.filename AS nwb_file
FROM ophys_experiments oe
LEFT JOIN well_known_files wkf ON wkf.attachable_id=oe.id AND wkf.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'NWBOphys')
WHERE oe.id = {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_sync_file(self, ophys_experiment_id=None):
query = '''
SELECT sync.storage_directory || sync.filename AS sync_file
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
LEFT JOIN well_known_files sync ON sync.attachable_id=os.id AND sync.attachable_type = 'OphysSession' AND sync.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysRigSync')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_max_projection_file(self):
query = '''
SELECT wkf.storage_directory || wkf.filename AS maxint_file
FROM ophys_experiments oe
LEFT JOIN ophys_cell_segmentation_runs ocsr ON ocsr.ophys_experiment_id = oe.id AND ocsr.current = 't'
LEFT JOIN well_known_files wkf ON wkf.attachable_id=ocsr.id AND wkf.attachable_type = 'OphysCellSegmentationRun' AND wkf.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysMaxIntImage')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_max_projection(self, image_api=None):
if image_api is None:
image_api = ImageApi
maxInt_a13_file = self.get_max_projection_file()
pixel_size = self.get_surface_2p_pixel_size_um()
max_projection = mpimg.imread(maxInt_a13_file)
return ImageApi.serialize(max_projection,
[pixel_size / 1000., pixel_size / 1000.],
'mm')
@memoize
def get_targeted_structure(self):
query = '''
SELECT st.acronym
FROM ophys_experiments oe
LEFT JOIN structures st ON st.id=oe.targeted_structure_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_imaging_depth(self):
query = '''
SELECT id.depth
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
LEFT JOIN imaging_depths id ON id.id=oe.imaging_depth_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_stimulus_name(self):
query = '''
SELECT os.stimulus_name
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
stimulus_name = self.lims_db.fetchone(query, strict=False)
stimulus_name = 'Unknown' if stimulus_name is None else stimulus_name
return stimulus_name
@memoize
def get_experiment_date(self):
query = '''
SELECT os.date_of_acquisition
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
experiment_date = self.lims_db.fetchone(query, strict=True)
return pytz.utc.localize(experiment_date)
@memoize
def get_reporter_line(self):
query = '''
SELECT g.name as reporter_line
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
JOIN donors d ON d.id=sp.donor_id
JOIN donors_genotypes dg ON dg.donor_id=d.id
JOIN genotypes g ON g.id=dg.genotype_id
JOIN genotype_types gt ON gt.id=g.genotype_type_id AND gt.name = 'reporter'
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
result = self.lims_db.fetchall(query)
if result is None or len(result) < 1:
raise OneOrMoreResultExpectedError(
'Expected one or more, but received: {} from query'.format(
result))
return result
@memoize
def get_driver_line(self):
query = '''
SELECT g.name as driver_line
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
JOIN donors d ON d.id=sp.donor_id
JOIN donors_genotypes dg ON dg.donor_id=d.id
JOIN genotypes g ON g.id=dg.genotype_id
JOIN genotype_types gt ON gt.id=g.genotype_type_id AND gt.name = 'driver'
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
result = self.lims_db.fetchall(query)
if result is None or len(result) < 1:
raise OneOrMoreResultExpectedError(
'Expected one or more, but received: {} from query'.format(
result))
return result
@memoize
def get_external_specimen_name(self, ophys_experiment_id=None):
query = '''
SELECT sp.external_specimen_name
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return int(self.lims_db.fetchone(query, strict=True))
@memoize
def get_full_genotype(self):
query = '''
SELECT d.full_genotype
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
JOIN donors d ON d.id=sp.donor_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_equipment_id(self):
query = '''
SELECT e.name
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN equipment e ON e.id=os.equipment_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_dff_file(self):
query = '''
SELECT dff.storage_directory || dff.filename AS dff_file
FROM ophys_experiments oe
LEFT JOIN well_known_files dff ON dff.attachable_id=oe.id AND dff.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysDffTraceFile')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_cell_roi_ids(self):
cell_specimen_table = self.get_cell_specimen_table()
assert cell_specimen_table.index.name == 'cell_specimen_id'
return cell_specimen_table['cell_roi_id'].values
@memoize
def get_objectlist_file(self):
query = '''
SELECT obj.storage_directory || obj.filename AS obj_file
FROM ophys_experiments oe
LEFT JOIN ophys_cell_segmentation_runs ocsr ON ocsr.ophys_experiment_id = oe.id AND ocsr.current = 't'
LEFT JOIN well_known_files obj ON obj.attachable_id=ocsr.id AND obj.attachable_type = 'OphysCellSegmentationRun' AND obj.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysSegmentationObjects')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_demix_file(self):
query = '''
SELECT wkf.storage_directory || wkf.filename AS demix_file
FROM ophys_experiments oe
LEFT JOIN well_known_files wkf ON wkf.attachable_id=oe.id AND wkf.attachable_type = 'OphysExperiment' AND wkf.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'DemixedTracesFile')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_average_intensity_projection_image_file(self):
query = '''
SELECT avg.storage_directory || avg.filename AS avgint_file
FROM ophys_experiments oe
LEFT JOIN ophys_cell_segmentation_runs ocsr ON ocsr.ophys_experiment_id = oe.id AND ocsr.current = 't'
LEFT JOIN well_known_files avg ON avg.attachable_id=ocsr.id AND avg.attachable_type = 'OphysCellSegmentationRun' AND avg.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysAverageIntensityProjectionImage')
WHERE oe.id = {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_rigid_motion_transform_file(self):
query = '''
SELECT tra.storage_directory || tra.filename AS transform_file
FROM ophys_experiments oe
LEFT JOIN well_known_files tra ON tra.attachable_id=oe.id AND tra.attachable_type = 'OphysExperiment' AND tra.well_known_file_type_id IN (SELECT id FROM well_known_file_types WHERE name = 'OphysMotionXyOffsetData')
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_motion_corrected_image_stack_file(self):
query = f"""
select wkf.storage_directory || wkf.filename
from well_known_files wkf
join well_known_file_types wkft on wkft.id = wkf.well_known_file_type_id
where wkft.name = 'MotionCorrectedImageStack'
and wkf.attachable_id = {self.get_ophys_experiment_id()}
"""
return safe_system_path(self.lims_db.fetchone(query, strict=True))
@memoize
def get_foraging_id(self):
query = '''
SELECT os.foraging_id
FROM ophys_experiments oe
LEFT JOIN ophys_sessions os ON oe.ophys_session_id = os.id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
def get_raw_dff_data(self):
dff_path = self.get_dff_file()
with h5py.File(dff_path, 'r') as raw_file:
dff_traces = np.asarray(raw_file['data'])
return dff_traces
@memoize
def get_rig_name(self):
query = '''
select e.name as device_name
from ophys_experiments oe
join ophys_sessions os on os.id = oe.ophys_session_id
join equipment e on e.id = os.equipment_id
where oe.id = {}
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_field_of_view_shape(self):
query = '''
select {}
from ophys_experiments oe
where oe.id = {}
'''
X = {
c:
self.lims_db.fetchone(query.format('oe.movie_{}'.format(c),
self.get_ophys_experiment_id()),
strict=True)
for c in ['width', 'height']
}
return X
@memoize
def get_metadata(self):
metadata = {}
metadata['rig_name'] = self.get_rig_name()
metadata['sex'] = self.get_sex()
metadata['age'] = self.get_age()
metadata['excitation_lambda'] = 910.
metadata['emission_lambda'] = 520.
metadata['indicator'] = 'GCAMP6f'
metadata['field_of_view_width'] = self.get_field_of_view_shape(
)['width']
metadata['field_of_view_height'] = self.get_field_of_view_shape(
)['height']
return metadata
@memoize
def get_ophys_cell_segmentation_run_id(self):
query = '''
select oseg.id
from ophys_experiments oe
join ophys_cell_segmentation_runs oseg on oe.id = oseg.ophys_experiment_id
where oe.id = {} and oseg.current = 't'
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_raw_cell_specimen_table_dict(self):
ophys_cell_segmentation_run_id = self.get_ophys_cell_segmentation_run_id(
)
query = '''
select *
from cell_rois cr
where cr.ophys_cell_segmentation_run_id = {}
'''.format(ophys_cell_segmentation_run_id)
cell_specimen_table = pd.read_sql(
query,
self.lims_db.get_connection()).rename(columns={
'id': 'cell_roi_id',
'mask_matrix': 'image_mask'
})
cell_specimen_table.drop(
['ophys_experiment_id', 'ophys_cell_segmentation_run_id'],
inplace=True,
axis=1)
return cell_specimen_table.to_dict()
@memoize
def get_cell_specimen_table(self):
cell_specimen_table = pd.DataFrame.from_dict(
self.get_raw_cell_specimen_table_dict()).set_index(
'cell_roi_id').sort_index()
fov_width, fov_height = self.get_field_of_view_shape(
)['width'], self.get_field_of_view_shape()['height']
image_mask_list = []
for sub_mask in cell_specimen_table['image_mask'].values:
curr_roi = roi.create_roi_mask(fov_width,
fov_height, [(fov_width - 1), 0,
(fov_height - 1), 0],
roi_mask=np.array(sub_mask,
dtype=np.bool))
image_mask_list.append(curr_roi.get_mask_plane().astype(np.bool))
cell_specimen_table['image_mask'] = image_mask_list
cell_specimen_table = cell_specimen_table[sorted(
cell_specimen_table.columns)]
cell_specimen_table.index.rename('cell_roi_id', inplace=True)
cell_specimen_table.reset_index(inplace=True)
cell_specimen_table.set_index('cell_specimen_id', inplace=True)
return cell_specimen_table
@memoize
def get_surface_2p_pixel_size_um(self):
query = '''
SELECT sc.resolution
FROM ophys_experiments oe
JOIN scans sc ON sc.image_id=oe.ophys_primary_image_id
WHERE oe.id = {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_workflow_state(self):
query = '''
SELECT oe.workflow_state
FROM ophys_experiments oe
WHERE oe.id = {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_sex(self):
query = '''
SELECT g.name as sex
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
JOIN donors d ON d.id=sp.donor_id
JOIN genders g ON g.id=d.gender_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
@memoize
def get_age(self):
query = '''
SELECT a.name as age
FROM ophys_experiments oe
JOIN ophys_sessions os ON oe.ophys_session_id = os.id
JOIN specimens sp ON sp.id=os.specimen_id
JOIN donors d ON d.id=sp.donor_id
JOIN ages a ON a.id=d.age_id
WHERE oe.id= {};
'''.format(self.get_ophys_experiment_id())
return self.lims_db.fetchone(query, strict=True)
def from_lims(cls, ophys_experiment_id: int,
lims_db: PostgresQueryMixin) -> Optional["RigGeometry"]:
query = f'''
SELECT oec.*, oect.name as config_type, equipment.name as
equipment_name
FROM ophys_sessions os
JOIN observatory_experiment_configs oec ON oec.equipment_id =
os.equipment_id
JOIN observatory_experiment_config_types oect ON oect.id =
oec.observatory_experiment_config_type_id
JOIN ophys_experiments oe ON oe.ophys_session_id = os.id
JOIN equipment ON equipment.id = oec.equipment_id
WHERE oe.id = {ophys_experiment_id} AND
oec.active_date <= os.date_of_acquisition AND
oect.name IN ('eye camera position', 'led position', 'screen position')
''' # noqa E501
# Get the raw data
rig_geometry = pd.read_sql(query, lims_db.get_connection())
if rig_geometry.empty:
# There is no rig geometry for this experiment
return None
# Map the config types to new names
rig_geometry_config_type_map = {
'eye camera position': 'camera',
'screen position': 'monitor',
'led position': 'led'
}
rig_geometry['config_type'] = rig_geometry['config_type'] \
.map(rig_geometry_config_type_map)
rig_geometry = cls._select_most_recent_geometry(
rig_geometry=rig_geometry)
# Construct dictionary for positions
position = rig_geometry[['center_x_mm', 'center_y_mm', 'center_z_mm']]
position.index = [
f'{v}_position_mm' if v != 'led' else f'{v}_position'
for v in position.index
]
position = position.to_dict(orient='index')
position = {
config_type:
Coordinates(values['center_x_mm'], values['center_y_mm'],
values['center_z_mm'])
for config_type, values in position.items()
}
# Construct dictionary for rotations
rotation = rig_geometry[[
'rotation_x_deg', 'rotation_y_deg', 'rotation_z_deg'
]]
rotation = rotation[rotation.index != 'led']
rotation.index = [f'{v}_rotation_deg' for v in rotation.index]
rotation = rotation.to_dict(orient='index')
rotation = {
config_type:
Coordinates(values['rotation_x_deg'], values['rotation_y_deg'],
values['rotation_z_deg'])
for config_type, values in rotation.items()
}
# Combine the dictionaries
rig_geometry = {
**position,
**rotation, 'equipment': rig_geometry['equipment_name'].iloc[0]
}
return RigGeometry(**rig_geometry)