def add_attrs(cursor, uuid_val, location):
studies = []
study_attrs = {}
try:
if location.attrs:
for ident in location.attrs:
attr_id, study_id = LocationEdit.get_or_create_location_attr_id(
cursor, ident)
if ident.study_name:
if ident.attr_type in study_attrs:
studies = study_attrs[ident.attr_type]
if study_id in studies:
raise DuplicateKeyException(
"Error inserting location - duplicate name for study {}"
.format(location))
else:
study_attrs[ident.attr_type] = []
study_attrs[ident.attr_type].append(study_id)
cursor.execute(
'INSERT INTO location_attrs(location_id, attr_id) VALUES (%s, %s)',
(uuid_val, attr_id))
except psycopg2.IntegrityError as err:
raise DuplicateKeyException(
"Error inserting location {}".format(location)) from err
def post(self, event_set_name):
resp = None
with self._connection:
with self._connection.cursor() as cursor:
event_set_id = None
try:
event_set_id = EventSetFetch.fetch_event_set_id(
cursor, event_set_name)
except MissingKeyException as err:
pass
if event_set_id:
raise DuplicateKeyException(
"Error inserting event set already exists {}".format(
event_set_name))
stmt = '''INSERT INTO event_sets (event_set_name) VALUES (%s)'''
args = (event_set_name, )
cursor.execute(stmt, args)
event_set_id = EventSetFetch.fetch_event_set_id(
cursor, event_set_name)
resp = EventSetFetch.fetch(cursor, event_set_id, 0, 0)
return resp
def post(self, assay_datum):
with self._connection:
with self._connection.cursor() as cursor:
uuid_val = uuid.uuid4()
stmt = '''INSERT INTO assay_data
(id, derivative_sample_id, ebi_run_acc)
VALUES (%s, %s, %s)'''
args = (uuid_val, assay_datum.derivative_sample_id,
assay_datum.ebi_run_acc)
try:
cursor.execute(stmt, args)
AssayDatumEdit.add_attrs(cursor, uuid_val, assay_datum)
except psycopg2.IntegrityError as err:
raise DuplicateKeyException(
"Error inserting assay_datum {}".format(
assay_datum)) from err
except DuplicateKeyException as err:
raise err
assay_datum = AssayDatumFetch.fetch(cursor, uuid_val)
return assay_datum
def check_for_duplicate(cursor, location, location_id):
stmt = '''SELECT id, ST_X(location) as latitude, ST_Y(location) as longitude,
accuracy, curated_name, curation_method, country
FROM locations WHERE location = ST_SetSRID(ST_MakePoint(%s, %s), 4326)'''
cursor.execute(stmt, (
location.latitude,
location.longitude,
))
existing_locations = []
for (loc_id, latitude, longitude, accuracy, curated_name,
curation_method, country) in cursor:
if location_id is None or loc_id != location_id:
existing_locations.append(loc_id)
for existing_id in existing_locations:
existing_location = LocationFetch.fetch(cursor, existing_id)
if existing_location.attrs:
for existing_ident in existing_location.attrs:
for ident in location.attrs:
if ident.attr_type == existing_ident.attr_type and\
ident.attr_value == existing_ident.attr_value and\
ident.study_name == existing_ident.study_name:
raise DuplicateKeyException(
"Error updating location - duplicate with {}".
format(existing_location))
def add_attrs(cursor, uuid_val, sampling_event):
if sampling_event.attrs:
for ident in sampling_event.attrs:
study_id = None
if ident.study_name:
study_id = SamplingEventEdit.fetch_study_id(
cursor, ident.study_name, True)
if not (ident.attr_type == 'partner_id' or \
ident.attr_type == 'individual_id'):
cursor.execute(
'''SELECT * FROM attrs
JOIN sampling_event_attrs ON sampling_event_attrs.attr_id =
attrs.id
WHERE attr_type = %s AND attr_value = %s AND
attr_source = %s''',
(ident.attr_type, ident.attr_value, ident.attr_source))
if cursor.fetchone():
raise DuplicateKeyException(
"Error inserting sampling_event attr {} {}".format(
ident.attr_type, sampling_event))
cursor.execute(
'''SELECT * FROM attrs
JOIN sampling_event_attrs ON sampling_event_attrs.attr_id =
attrs.id
WHERE attr_type = %s AND attr_value = %s AND
sampling_event_id != %s''',
(ident.attr_type, ident.attr_value, uuid_val))
if cursor.fetchone():
raise DuplicateKeyException(
"Error inserting sampling_event attr {} {}".format(
ident.attr_type, sampling_event))
attr_id = uuid.uuid4()
stmt = '''INSERT INTO attrs
(id, attr_type, attr_value, attr_source, study_id)
VALUES (%s, %s, %s, %s, %s)'''
cursor.execute(stmt,
(attr_id, ident.attr_type, ident.attr_value,
ident.attr_source, study_id))
stmt = '''INSERT INTO sampling_event_attrs
(sampling_event_id, attr_id)
VALUES (%s, %s)'''
cursor.execute(stmt, (uuid_val, attr_id))
def run_command(self, cursor, sampling_event_id, sampling_event):
stmt = '''SELECT id FROM sampling_events WHERE id = %s'''
cursor.execute(stmt, (sampling_event_id, ))
existing_sampling_event = None
for (sampling_event_id, ) in cursor:
existing_sampling_event = SamplingEvent(sampling_event_id)
if not existing_sampling_event:
raise MissingKeyException(
"Could not find sampling_event to update {}".format(
sampling_event_id))
SamplingEventEdit.check_date(sampling_event)
SamplingEventEdit.check_location_details(cursor,
sampling_event.location_id,
sampling_event.location)
SamplingEventEdit.check_location_details(
cursor, sampling_event.proxy_location_id,
sampling_event.proxy_location)
stmt = '''UPDATE sampling_events
SET doc = %s, doc_accuracy = %s,
location_id = %s, proxy_location_id = %s,
individual_id = %s
WHERE id = %s'''
args = (sampling_event.doc, sampling_event.doc_accuracy,
sampling_event.location_id, sampling_event.proxy_location_id,
sampling_event.individual_id, sampling_event_id)
try:
cursor.execute(stmt, args)
rc = cursor.rowcount
cursor.execute(
'DELETE FROM sampling_event_attrs WHERE sampling_event_id = %s',
(sampling_event_id, ))
SamplingEventEdit.add_attrs(cursor, sampling_event_id,
sampling_event)
except psycopg2.IntegrityError as err:
raise DuplicateKeyException(
"Error updating sampling_event {}".format(
sampling_event)) from err
except DuplicateKeyException as err:
raise err
sampling_event = SamplingEventFetch.fetch(cursor, sampling_event_id)
if rc != 1:
raise MissingKeyException(
"Error updating sampling_event {}".format(sampling_event_id))
return sampling_event
def check_for_duplicate(cursor, individual, individual_id):
for ident in individual.attrs:
(match, study) = IndividualEdit.get_or_create_individual_attr_id(
cursor, ident, create=False)
if match:
raise DuplicateKeyException(
"Error updating individual - duplicate with {}".format(
ident))
for ident in individual.attrs:
count = 0
for ident1 in individual.attrs:
if ident == ident1:
count = count + 1
if count > 1:
raise DuplicateKeyException(
"Error updating individual - duplicate attrs {}".format(
ident))
def add_attrs(cursor, uuid_val, original_sample):
if original_sample.attrs:
for ident in original_sample.attrs:
if not (ident.attr_type == 'partner_id' or \
ident.attr_type == 'individual_id'):
cursor.execute(
'''SELECT * FROM attrs
JOIN original_sample_attrs ON original_sample_attrs.attr_id =
attrs.id
WHERE attr_type = %s AND attr_value = %s AND
attr_source = %s''',
(ident.attr_type, ident.attr_value, ident.attr_source))
if cursor.fetchone():
raise DuplicateKeyException(
"Error inserting original_sample attr {} {}".
format(ident.attr_type, original_sample))
cursor.execute(
'''SELECT * FROM attrs
JOIN original_sample_attrs ON original_sample_attrs.attr_id =
attrs.id
WHERE attr_type = %s AND attr_value = %s AND
original_sample_id != %s''',
(ident.attr_type, ident.attr_value, uuid_val))
if cursor.fetchone():
raise DuplicateKeyException(
"Error inserting original_sample attr {} {}".
format(ident.attr_type, original_sample))
attr_id = uuid.uuid4()
stmt = '''INSERT INTO attrs
(id, attr_type, attr_value, attr_source)
VALUES (%s, %s, %s, %s)'''
cursor.execute(stmt, (attr_id, ident.attr_type,
ident.attr_value, ident.attr_source))
stmt = '''INSERT INTO original_sample_attrs
(original_sample_id, attr_id)
VALUES (%s, %s)'''
cursor.execute(stmt, (uuid_val, attr_id))
def run_command(self, cursor, derivative_sample_id, derivative_sample):
stmt = '''SELECT id FROM derivative_samples WHERE id = %s'''
cursor.execute(stmt, (derivative_sample_id, ))
existing_derivative_sample = None
for (derivative_sample_id, ) in cursor:
existing_derivative_sample = DerivativeSample(derivative_sample_id)
if not existing_derivative_sample:
raise MissingKeyException(
"Could not find derivative_sample to update {}".format(
derivative_sample_id))
stmt = '''UPDATE derivative_samples
SET original_sample_id = %s,
dna_prep = %s,
parent_derivative_sample_id = %s
WHERE id = %s'''
args = (derivative_sample.original_sample_id,
derivative_sample.dna_prep,
derivative_sample.parent_derivative_sample_id,
derivative_sample_id)
try:
cursor.execute(stmt, args)
rc = cursor.rowcount
cursor.execute(
'DELETE FROM derivative_sample_attrs WHERE derivative_sample_id = %s',
(derivative_sample_id, ))
DerivativeSampleEdit.add_attrs(cursor, derivative_sample_id,
derivative_sample)
except psycopg2.IntegrityError as err:
raise DuplicateKeyException(
"Error updating derivative_sample {}".format(
derivative_sample)) from err
except DuplicateKeyException as err:
raise err
derivative_sample = DerivativeSampleFetch.fetch(
cursor, derivative_sample_id)
if rc != 1:
raise MissingKeyException(
"Error updating derivative_sample {}".format(
derivative_sample_id))
return derivative_sample
def add_attrs(cursor, uuid_val, derivative_sample):
if derivative_sample.attrs:
for ident in derivative_sample.attrs:
cursor.execute(
'''SELECT * FROM attrs
JOIN derivative_sample_attrs ON derivative_sample_attrs.attr_id =
attrs.id
WHERE attr_type = %s AND attr_value = %s AND
attr_source = %s''',
(ident.attr_type, ident.attr_value, ident.attr_source))
if cursor.fetchone():
raise DuplicateKeyException(
"Error inserting derivative_sample attr {} {}".format(
ident.attr_type, derivative_sample))
cursor.execute(
'''SELECT * FROM attrs
JOIN derivative_sample_attrs ON derivative_sample_attrs.attr_id =
attrs.id
WHERE attr_type = %s AND attr_value = %s AND
derivative_sample_id != %s''',
(ident.attr_type, ident.attr_value, uuid_val))
if cursor.fetchone():
raise DuplicateKeyException(
"Error inserting derivative_sample attr {} {}".format(
ident.attr_type, derivative_sample))
attr_id = uuid.uuid4()
stmt = '''INSERT INTO attrs
(id, attr_type, attr_value, attr_source)
VALUES (%s, %s, %s, %s)'''
cursor.execute(stmt, (attr_id, ident.attr_type,
ident.attr_value, ident.attr_source))
stmt = '''INSERT INTO derivative_sample_attrs
(derivative_sample_id, attr_id)
VALUES (%s, %s)'''
cursor.execute(stmt, (uuid_val, attr_id))
def add_attrs(cursor, uuid_val, assay_datum):
if assay_datum.attrs:
for ident in assay_datum.attrs:
cursor.execute(
'''SELECT * FROM attrs
JOIN assay_datum_attrs ON assay_datum_attrs.attr_id =
attrs.id
WHERE attr_type = %s AND attr_value = %s AND
attr_source = %s''',
(ident.attr_type, ident.attr_value, ident.attr_source))
if cursor.fetchone():
raise DuplicateKeyException(
"Error inserting assay_datum attr {} {}".format(
ident.attr_type, assay_datum))
cursor.execute(
'''SELECT * FROM attrs
JOIN assay_datum_attrs ON assay_datum_attrs.attr_id =
attrs.id
WHERE attr_type = %s AND attr_value = %s AND
assay_datum_id != %s''',
(ident.attr_type, ident.attr_value, uuid_val))
if cursor.fetchone():
raise DuplicateKeyException(
"Error inserting assay_datum attr {} {}".format(
ident.attr_type, assay_datum))
attr_id = uuid.uuid4()
stmt = '''INSERT INTO attrs
(id, attr_type, attr_value, attr_source)
VALUES (%s, %s, %s, %s)'''
cursor.execute(stmt, (attr_id, ident.attr_type,
ident.attr_value, ident.attr_source))
stmt = '''INSERT INTO assay_datum_attrs
(assay_datum_id, attr_id)
VALUES (%s, %s)'''
cursor.execute(stmt, (uuid_val, attr_id))
def run_command(self, cursor, assay_datum_id, assay_datum):
stmt = '''SELECT id FROM assay_data WHERE id = %s'''
cursor.execute(stmt, (assay_datum_id, ))
existing_assay_datum = None
for (assay_datum_id, ) in cursor:
existing_assay_datum = AssayDatum(assay_datum_id)
if not existing_assay_datum:
raise MissingKeyException(
"Could not find assay_datum to update {}".format(
assay_datum_id))
stmt = '''UPDATE assay_data
SET derivative_sample_id = %s,
ebi_run_acc = %s
WHERE id = %s'''
args = (assay_datum.derivative_sample_id, assay_datum.ebi_run_acc,
assay_datum_id)
try:
cursor.execute(stmt, args)
rc = cursor.rowcount
cursor.execute(
'DELETE FROM assay_datum_attrs WHERE assay_datum_id = %s',
(assay_datum_id, ))
AssayDatumEdit.add_attrs(cursor, assay_datum_id, assay_datum)
except psycopg2.IntegrityError as err:
raise DuplicateKeyException(
"Error updating assay_datum {}".format(assay_datum)) from err
except DuplicateKeyException as err:
raise err
assay_datum = AssayDatumFetch.fetch(cursor, assay_datum_id)
if rc != 1:
raise MissingKeyException(
"Error updating assay_datum {}".format(assay_datum_id))
return assay_datum
def add_attrs(cursor, uuid_val, individual):
studies = []
study_attrs = {}
try:
if individual.attrs:
for ident in individual.attrs:
attr_id, study_id = IndividualEdit.get_or_create_individual_attr_id(
cursor, ident)
cursor.execute(
'INSERT INTO individual_attrs(individual_id, attr_id) VALUES (%s, %s)',
(uuid_val, attr_id))
except psycopg2.IntegrityError as err:
raise DuplicateKeyException(
"Error inserting individual {}".format(individual)) from err
def post(self, event_set_name, note_id, note):
ret = None
with self._connection:
with self._connection.cursor() as cursor:
event_set_id = EventSetFetch.fetch_event_set_id(
cursor, event_set_name)
try:
EventSetEdit.add_note(cursor, event_set_id, note)
except psycopg2.IntegrityError as err:
raise DuplicateKeyException(
"Error inserting event set note {} {}".format(
event_set_id, note)) from err
return ret
def run_command(self, cursor, individual_id, individual):
stmt = '''SELECT id FROM individuals WHERE id = %s'''
cursor.execute(stmt, (individual_id, ))
existing_individual = None
for (individual_id, ) in cursor:
existing_individual = Individual(individual_id)
if not existing_individual:
raise MissingKeyException(
"Error updating individual {}".format(individual_id))
stmt = '''UPDATE individuals
SET id = %s
WHERE id = %s'''
args = (individual_id, individual_id)
try:
cursor.execute(stmt, args)
rc = cursor.rowcount
IndividualEdit.delete_attrs(cursor, individual_id)
IndividualEdit.check_for_duplicate(cursor, individual,
individual_id)
IndividualEdit.add_attrs(cursor, individual_id, individual)
except psycopg2.IntegrityError as err:
raise DuplicateKeyException(
"Error updating individual {}".format(individual)) from err
individual = IndividualFetch.fetch(cursor, individual_id)
if rc != 1:
raise MissingKeyException(
"Error updating individual {}".format(individual_id))
#
return individual
def put(self, event_set_name, note_id, note):
ret = None
with self._connection:
with self._connection.cursor() as cursor:
event_set_id = EventSetFetch.fetch_event_set_id(cursor,event_set_name)
stmt = '''UPDATE event_set_notes SET note_text = %s, note_name = %s WHERE event_set_id = %s AND note_name = %s'''
try:
cursor.execute(stmt, (note.note_text, note.note_name, event_set_id, note_id))
except psycopg2.IntegrityError as err:
raise DuplicateKeyException("Error updating event set note id from {} to {} in {}".format(note_id, note.note_name, event_set_id )) from err
if cursor.rowcount != 1:
raise MissingKeyException('No note {} in event set {}'.format(note.note_name,
event_set_name))
ret = EventSetFetch.fetch(cursor, event_set_id, 0, 0)
return ret