def fetch(cursor, sampling_event_id, locations=None):
if not sampling_event_id:
return None
stmt = '''SELECT sampling_events.id, doc, doc_accuracy,
location_id, proxy_location_id, individual_id
FROM sampling_events
WHERE sampling_events.id = %s'''
cursor.execute( stmt, (sampling_event_id,))
sampling_event = None
for (sampling_event_id, doc, doc_accuracy, location_id,
proxy_location_id, individual_id) in cursor:
sampling_event = SamplingEvent(str(sampling_event_id),
doc=doc, doc_accuracy=doc_accuracy,
individual_id=individual_id)
if location_id:
sampling_event.location_id = str(location_id)
sampling_event.public_location_id = str(location_id)
if proxy_location_id:
sampling_event.proxy_location_id = str(proxy_location_id)
sampling_event.public_location_id = str(proxy_location_id)
if not sampling_event:
return sampling_event
sampling_event.attrs = SamplingEventFetch.fetch_attrs(cursor, sampling_event_id)
sampling_event.event_sets = SamplingEventFetch.fetch_event_sets(cursor, sampling_event_id)
if sampling_event.location_id:
location = LocationFetch.fetch(cursor, sampling_event.location_id)
if locations is not None:
if sampling_event.location_id not in locations:
locations[sampling_event.location_id] = location
else:
sampling_event.location = location
if sampling_event.proxy_location_id:
proxy_location = LocationFetch.fetch(cursor, sampling_event.proxy_location_id)
if locations is not None:
if sampling_event.proxy_location_id not in locations:
locations[sampling_event.proxy_location_id] = proxy_location
else:
sampling_event.proxy_location = proxy_location
return sampling_event
def get(self, partner_id):
with self._connection:
with self._connection.cursor() as cursor:
cursor.execute(
'''SELECT DISTINCT location_id FROM location_attrs
JOIN attrs ON attrs.id = location_attrs.attr_id
WHERE attr_type = %s AND attr_value = %s''', (
'partner_name',
partner_id,
))
locations = Locations()
locations.locations = []
locations.count = 0
locs = []
for (location_id, ) in cursor:
locs.append(location_id)
for location_id in locs:
location = LocationFetch.fetch(cursor, location_id)
locations.locations.append(location)
locations.count = locations.count + 1
return locations
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 get(self, latitude, longitude):
with self._connection:
with self._connection.cursor() as cursor:
cursor.execute(
"SELECT id FROM locations WHERE ST_X(location) = %s AND ST_Y(location) = %s",
(
latitude,
longitude,
))
locations = Locations()
locations.locations = []
locations.count = 0
ids = []
for (location_id, ) in cursor:
ids.append(str(location_id))
for location_id in ids:
location = LocationFetch.fetch(cursor, location_id)
locations.locations.append(location)
locations.count = locations.count + 1
if len(locations.locations) == 0:
raise MissingKeyException("GPS location not found {}, {}".format(
latitude, longitude))
return locations
def get(self, location_id):
location = None
with self._connection:
with self._connection.cursor() as cursor:
location = LocationFetch.fetch(cursor, location_id)
return location
def get(self, study_code=None, start=None, count=None, orderby='location'):
result = Locations()
with self._connection:
with self._connection.cursor() as cursor:
query_body = ' FROM locations l'
args = ()
if study_code or orderby == 'study_name':
query_body = query_body + ''' LEFT JOIN location_attrs li ON li.location_id = l.id
JOIN attrs a ON li.attr_id = a.id
LEFT JOIN studies s ON s.id = a.study_id'''
if study_code:
query_body = query_body + " WHERE study_code = %s"
args = (study_code[:4], )
count_args = args
count_query = 'SELECT COUNT(DISTINCT l.id) ' + query_body
if orderby:
query_body = query_body + " ORDER BY " + orderby + ", l.id"
if not (start is None and count is None):
query_body = query_body + ' LIMIT %s OFFSET %s'
args = args + (count, start)
if orderby:
cursor.execute(
'SELECT DISTINCT l.id, ' + orderby + query_body, args)
else:
cursor.execute(
'SELECT DISTINCT l.id, l.curated_name ' + query_body,
args)
locations = []
for (location_id, ignored) in cursor:
with self._connection.cursor() as lcursor:
location = LocationFetch.fetch(lcursor, location_id)
locations.append(location)
if not (start is None and count is None):
cursor.execute(count_query, count_args)
result.count = cursor.fetchone()[0]
else:
result.count = len(locations)
result.locations = locations
return result
def check_location_details(cursor, location_id, location):
current_location = None
if location_id:
current_location = LocationFetch.fetch(cursor, location_id)
cli = current_location.attrs
#It's OK if the location id is set but the location object not filled in
if location:
idents = location.attrs
if location != current_location:
raise NestedEditException(
"Implied location edit not allowed for {}".format(
location_id))
return current_location
def post(self, location):
with self._connection:
with self._connection.cursor() as cursor:
LocationEdit.check_for_duplicate(cursor, location, None)
uuid_val = uuid.uuid4()
stmt = '''INSERT INTO locations
(id, location, accuracy, curated_name, curation_method, country, notes)
VALUES (%s, ST_SetSRID(ST_MakePoint(%s, %s), 4326), %s, %s, %s, %s, %s)'''
args = (uuid_val, location.latitude, location.longitude,
location.accuracy, location.curated_name,
location.curation_method, location.country,
location.notes)
cursor.execute(stmt, args)
LocationEdit.add_attrs(cursor, uuid_val, location)
location = LocationFetch.fetch(cursor, uuid_val)
return location
def get(self, location_id, start, count):
with self._connection:
with self._connection.cursor() as cursor:
locations = {}
try:
location = LocationFetch.fetch(cursor, location_id)
except MissingKeyException as mke:
raise mke
fields = '''SELECT sampling_events.id'''
query_body = ''' FROM sampling_events
WHERE location_id = %s OR proxy_location_id = %s'''
args = (
location_id,
location_id,
)
count_args = args
count_query = 'SELECT COUNT(sampling_events.id) ' + query_body
query_body = query_body + ''' ORDER BY doc, id'''
if not (start is None and count is None):
query_body = query_body + ' LIMIT %s OFFSET %s'
args = args + (count, start)
sampling_events = SamplingEvents(sampling_events=[], count=0)
stmt = fields + query_body
cursor.execute(stmt, args)
samp_ids = []
for samp_id in cursor:
samp_ids.append(samp_id)
locations = {}
sampling_events.sampling_events = []
for samp_id in samp_ids:
event = SamplingEventFetch.fetch(cursor, samp_id,
locations)
sampling_events.sampling_events.append(event)
sampling_events.locations = locations
if not (start is None and count is None):
cursor.execute(count_query, count_args)
sampling_events.count = cursor.fetchone()[0]
else:
sampling_events.count = len(
sampling_events.sampling_events)
sampling_events.attr_types = []
for samp_id in samp_ids:
col_query = '''select distinct attr_type from sampling_event_attrs se
JOIN attrs a ON se.attr_id=a.id
WHERE sampling_event_id = %s'''
cursor.execute(col_query, (samp_id, ))
for (attr_type, ) in cursor:
if attr_type not in sampling_events.attr_types:
sampling_events.attr_types.append(attr_type)
return sampling_events
def get(self, location_id, start, count):
with self._connection:
with self._connection.cursor() as cursor:
try:
location = LocationFetch.fetch(cursor, location_id)
except MissingKeyException as mke:
raise mke
fields = '''SELECT os.id, study_name, sampling_event_id,
days_in_culture, partner_species'''
query_body = ''' FROM original_samples os
JOIN sampling_events se ON se.id = os.sampling_event_id
LEFT JOIN partner_species_identifiers psi ON psi.id = os.partner_species_id
LEFT JOIN studies s ON s.id = os.study_id
WHERE location_id = %s OR proxy_location_id = %s'''
args = (
location_id,
location_id,
)
count_args = args
count_query = 'SELECT COUNT(os.id) ' + query_body
query_body = query_body + ''' ORDER BY study_name, os.id'''
if not (start is None and count is None):
query_body = query_body + ' LIMIT %s OFFSET %s'
args = args + (count, start)
original_samples = OriginalSamples(original_samples=[],
count=0)
stmt = fields + query_body
cursor.execute(stmt, args)
original_samples.original_samples, original_samples.sampling_events = OriginalSampleFetch.load_original_samples(
cursor, True)
if not (start is None and count is None):
cursor.execute(count_query, count_args)
original_samples.count = cursor.fetchone()[0]
else:
original_samples.count = len(
original_samples.original_samples)
original_samples.attr_types = []
col_query = '''select distinct attr_type from original_sample_attrs osa
JOIN attrs a ON osa.attr_id=a.id
JOIN original_samples os ON os.id = osa.original_sample_id
JOIN sampling_events se ON se.id = os.sampling_event_id
WHERE se.location_id = %s OR se.proxy_location_id = %s'''
cursor.execute(col_query, (location_id, location_id))
for (attr_type, ) in cursor:
original_samples.attr_types.append(attr_type)
return original_samples