def insert_job(conn: Connection, *, algorithm_id: str, algorithm_name: str,
algorithm_version: int, job_id: str, name: str, scene_id: str,
status: str, tide: float, tide_min_24h: float,
tide_max_24h: float, user_id: str) -> None:
log = logging.getLogger(__name__)
log.info('Db insert job', action='database insert record')
query = """
INSERT INTO job (job_id, algorithm_id, algorithm_name, algorithm_version, created_by, name,
scene_id, status, tide, tide_min_24h, tide_max_24h)
VALUES (%(job_id)s, %(algorithm_id)s, %(algorithm_name)s, %(algorithm_version)s, %(created_by)s, %(name)s,
%(scene_id)s, %(status)s, %(tide)s, %(tide_min_24h)s, %(tide_max_24h)s)
"""
params = {
'job_id': job_id,
'algorithm_id': algorithm_id,
'algorithm_name': algorithm_name,
'algorithm_version': algorithm_version,
'created_by': user_id,
'name': name,
'scene_id': scene_id,
'status': status,
'tide': tide,
'tide_min_24h': tide_min_24h,
'tide_max_24h': tide_max_24h,
}
conn.execute(query, params)
def update_status(conn: Connection, *, job_id: str, status: str) -> None:
log = logging.getLogger(__name__)
log.info('Db update status', action='database update record')
query = """
UPDATE job
SET status = %(status)s
WHERE job_id = %(job_id)s
"""
params = {
'job_id': job_id,
'status': status,
}
conn.execute(query, params)
def insert_job_user(conn: Connection, *, job_id: str, user_id: str) -> None:
log = logging.getLogger(__name__)
log.info('Db job user', action='database insert record')
query = """
INSERT INTO job_user (job_id, user_id)
VALUES (%(job_id)s, %(user_id)s)
ON CONFLICT DO NOTHING
"""
params = {
'job_id': job_id,
'user_id': user_id,
}
conn.execute(query, params)
def insert_job_failure(conn: Connection, *, error_message: str,
execution_step: str, job_id: str) -> None:
log = logging.getLogger(__name__)
log.info('Db insert job failure', action='database insert record')
query = """
INSERT INTO job_error (job_id, error_message, execution_step)
VALUES (%(job_id)s, %(error_message)s, %(execution_step)s)
"""
params = {
'job_id': job_id,
'error_message': error_message or None,
'execution_step': execution_step,
}
conn.execute(query, params)
def exists(conn: Connection, *, job_id: str) -> bool:
query = """
SELECT 1 FROM job WHERE job_id = %(job_id)s
"""
params = {
'job_id': job_id,
}
return conn.execute(query, params).rowcount > 0
def insert_detection(conn: Connection, *, job_id: str,
feature_collection: str) -> None:
# FIXME -- I know we can do better than this...
log = logging.getLogger(__name__)
log.info('Db insert detection', action='database insert record')
query = """
INSERT INTO detection (job_id, feature_id, geometry)
SELECT %(job_id)s AS job_id,
row_number() OVER () AS feature_id,
ST_GeomFromGeoJSON(fc.features->>'geometry') AS geometry
FROM (SELECT json_array_elements(%(feature_collection)s::json->'features') AS features) fc
"""
params = {
'job_id': job_id,
'feature_collection': feature_collection,
}
conn.execute(query, params)
def insert_user(
conn: Connection,
*,
user_id: str,
user_name: str,
api_key: str) -> None:
log = logging.getLogger(__name__)
log.info('Db insert user', action='database insert record')
query = """
INSERT INTO useraccount (user_id, user_name, api_key)
VALUES (%(user_id)s, %(user_name)s, %(api_key)s)
"""
params = {
'user_id': user_id,
'user_name': user_name,
'api_key': api_key,
}
conn.execute(query, params)
def select_outstanding_jobs(conn: Connection) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db select outstanding jobs', action='database query record')
query = """
SELECT job_id,
DATE_TRUNC('second', NOW() - created_on) AS age
FROM job
WHERE status IN ('Submitted', 'Pending', 'Running')
ORDER BY created_on ASC
"""
return conn.execute(query)
def select_all(conn: Connection):
log = logging.getLogger(__name__)
log.info('Db select all', action='database query record')
query = """
SELECT productline_id, algorithm_id, algorithm_name, category, compute_mask, created_by,
created_on, max_cloud_cover, name, owned_by, spatial_filter_id, start_on, stop_on,
ST_AsGeoJSON(bbox) AS bbox
FROM productline
WHERE NOT deleted
ORDER BY created_on ASC
"""
return conn.execute(query)
def delete_job_user(conn: Connection, *, job_id: str, user_id: str) -> bool:
log = logging.getLogger(__name__)
log.info('Db delete job user', action='database delete record')
query = """
DELETE FROM job_user
WHERE job_id = %(job_id)s
AND user_id = %(user_id)s
"""
params = {
'job_id': job_id,
'user_id': user_id,
}
return conn.execute(query, params).rowcount > 0
def delete_productline(conn: Connection, *,
productline_id: str) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db delete productline', action='database delete record')
query = """
UPDATE productline
SET deleted = TRUE
WHERE productline_id = %(productline_id)s
"""
params = {
'productline_id': productline_id,
}
return conn.execute(query, params)
def insert_productline_job(conn: Connection, *, job_id: str,
productline_id: str) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db insert productline job', action='database insert record')
query = """
INSERT INTO productline_job (job_id, productline_id)
VALUES (%(job_id)s, %(productline_id)s)
ON CONFLICT DO NOTHING
"""
params = {
'job_id': job_id,
'productline_id': productline_id,
}
return conn.execute(query, params)
def select_user_by_api_key(
conn: Connection,
*,
api_key: str) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db select user by api', action='database query record')
query = """
SELECT u.user_id, u.user_name, u.api_key, u.created_on
FROM useraccount u
WHERE u.api_key = %(api_key)s
"""
params = {
'api_key': api_key,
}
return conn.execute(query, params)
def select_password_hash(
conn: Connection,
*,
user_id: str) -> str:
log = logging.getLogger(__name__)
log.info('Get user password hash', action='database query record', actee=user_id)
query = """
SELECT password_hash
FROM useraccount
WHERE user_id = %(user_id)s
"""
params = {
'user_id': user_id,
}
return conn.execute(query, params).scalar()
def select_jobs_for_scene(conn: Connection, *, scene_id: str) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db select jobs for scene', action='database select record')
query = """
SELECT j.job_id, j.algorithm_name, j.algorithm_version, j.created_by, j.created_on, j.name, j.scene_id, j.status, j.tide, j.tide_min_24h, j.tide_max_24h,
ST_AsGeoJSON(s.geometry) AS geometry, s.sensor_name, s.captured_on
FROM job j
LEFT OUTER JOIN scene s ON (s.scene_id = j.scene_id)
WHERE j.scene_id = %(scene_id)s
AND j.status IN ('Submitted', 'Running', 'Success')
ORDER BY created_on DESC
"""
params = {
'scene_id': scene_id,
}
return conn.execute(query, params)
def select_productline(conn: Connection, *,
productline_id: str) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db select productline', action='database query record')
query = """
SELECT productline_id, algorithm_id, algorithm_name, category, compute_mask, created_by,
created_on, max_cloud_cover, name, owned_by, spatial_filter_id, start_on, stop_on,
ST_AsGeoJSON(bbox) AS bbox
FROM productline
WHERE NOT deleted
AND productline_id = %(productline_id)s
"""
params = {
'productline_id': productline_id,
}
return conn.execute(query, params)
def select_job(conn: Connection, *, job_id: str) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db select job', action='database query record')
query = """
SELECT j.job_id, j.algorithm_name, j.algorithm_version, j.created_by, j.created_on, j.name, j.scene_id, j.status, j.tide, j.tide_min_24h, j.tide_max_24h,
e.error_message, e.execution_step,
ST_AsGeoJSON(s.geometry) AS geometry, s.sensor_name, s.captured_on
FROM job j
LEFT OUTER JOIN job_error e ON (e.job_id = j.job_id)
LEFT OUTER JOIN scene s ON (s.scene_id = j.scene_id)
WHERE j.job_id = %(job_id)s
"""
params = {
'job_id': job_id,
}
return conn.execute(query, params)
def select_jobs_for_productline(conn: Connection, *, productline_id: str,
since: datetime) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db select jobs for productline', action='database query record')
query = """
SELECT j.job_id, j.algorithm_name, j.algorithm_version, j.created_by, j.created_on, j.name, j.scene_id, j.status, j.tide, j.tide_min_24h, j.tide_max_24h,
ST_AsGeoJSON(s.geometry) AS geometry, s.sensor_name, s.captured_on
FROM productline_job p
LEFT OUTER JOIN job j ON (j.job_id = p.job_id)
LEFT OUTER JOIN scene s ON (s.scene_id = j.scene_id)
WHERE p.productline_id = %(productline_id)s
AND (j.status IN ('Submitted', 'Running', 'Success'))
AND (s.captured_on >= %(since)s)
ORDER BY captured_on DESC
"""
params = {
'productline_id': productline_id,
'since': since,
}
return conn.execute(query, params)
def insert(conn: Connection, *, captured_on: datetime, catalog_uri: str,
cloud_cover: float, geometry: dict, resolution: int, scene_id: str,
sensor_name: str) -> str:
log = logging.getLogger(__name__)
log.info('Db insert scene', action='database insert record')
query = """
INSERT INTO scene (scene_id, captured_on, catalog_uri, cloud_cover, geometry, resolution, sensor_name)
VALUES (%(scene_id)s, %(captured_on)s, %(catalog_uri)s, %(cloud_cover)s, ST_GeomFromGeoJSON(%(geometry)s),
%(resolution)s, %(sensor_name)s)
ON CONFLICT DO NOTHING
"""
params = {
'scene_id': scene_id,
'captured_on': captured_on,
'catalog_uri': catalog_uri,
'cloud_cover': cloud_cover,
'geometry': json.dumps(geometry),
'resolution': resolution,
'sensor_name': sensor_name,
}
return conn.execute(query, params)
def select_detections(conn: Connection, *, job_id: str) -> ResultProxy:
# Construct the GeoJSON directly where the data lives
log = logging.getLogger(__name__)
log.info('Db select detection', action='database query record')
query = """
SELECT to_json(fc)::text AS "feature_collection"
FROM (SELECT 'FeatureCollection' AS "type",
array_agg(f) AS "features"
FROM (SELECT concat_ws('#', d.job_id, d.feature_id) AS "id",
to_json(p) AS "properties",
ST_AsGeoJSON(d.geometry)::json AS "geometry",
'Feature' AS "type"
FROM detection d
INNER JOIN provenance AS p ON (p.job_id = d.job_id)
WHERE d.job_id = %(job_id)s
) AS f
) AS fc
"""
params = {
'job_id': job_id,
}
return conn.execute(query, params)
def select_jobs_for_inputs(conn: Connection, *, algorithm_id: str,
scene_id: str) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db select jobs for inputs', action='database query record')
query = """
SELECT job_id,
CASE status
WHEN 'Success' THEN 0
WHEN 'Submitted' THEN 1
WHEN 'Running' THEN 2
END AS _sort_precedence
FROM job
WHERE algorithm_id = %(algorithm_id)s
AND scene_id = %(scene_id)s
AND status IN ('Submitted', 'Running', 'Success')
ORDER BY _sort_precedence ASC
"""
params = {
'algorithm_id': algorithm_id,
'scene_id': scene_id,
}
return conn.execute(query, params)
def select_summary_for_scene(conn: Connection, *, captured_on: date,
cloud_cover: int, min_x: float, min_y: float,
max_x: float, max_y: float) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db select summary for scene', action='database query record')
query = """
SELECT productline_id, algorithm_id, name, owned_by
FROM productline
WHERE NOT deleted
AND bbox && ST_MakeEnvelope(%(min_x)s, %(min_y)s, %(max_x)s, %(max_y)s)
AND max_cloud_cover >= %(cloud_cover)s
AND start_on <= %(captured_on)s
AND (stop_on >= %(captured_on)s OR stop_on IS NULL)
"""
params = {
'captured_on': captured_on,
'cloud_cover': cloud_cover,
'min_x': min_x,
'min_y': min_y,
'max_x': max_x,
'max_y': max_y,
}
return conn.execute(query, params)
def insert_productline(conn: Connection,
*,
productline_id: str,
algorithm_id: str,
algorithm_name: str,
bbox: tuple,
category: str = None,
max_cloud_cover: int,
name: str,
spatial_filter_id: str = None,
start_on: date,
stop_on: date = None,
user_id: str) -> ResultProxy:
log = logging.getLogger(__name__)
log.info('Db insert productline', action='database insert record')
query = """
INSERT INTO productline (productline_id, algorithm_id, algorithm_name, category, created_by, max_cloud_cover, name, owned_by, spatial_filter_id, start_on, stop_on, bbox)
VALUES (%(productline_id)s, %(algorithm_id)s, %(algorithm_name)s, %(category)s, %(user_id)s, %(max_cloud_cover)s, %(name)s, %(user_id)s, %(spatial_filter_id)s, %(start_on)s, %(stop_on)s, ST_MakeEnvelope(%(min_x)s, %(min_y)s, %(max_x)s, %(max_y)s))
"""
params = {
'productline_id': productline_id,
'algorithm_id': algorithm_id,
'algorithm_name': algorithm_name,
'category': category,
'compute_mask': None,
'max_cloud_cover': max_cloud_cover,
'min_x': bbox[0],
'min_y': bbox[1],
'max_x': bbox[2],
'max_y': bbox[3],
'name': name,
'spatial_filter_id': spatial_filter_id,
'start_on': start_on,
'stop_on': stop_on,
'user_id': user_id,
}
return conn.execute(query, params)