[#INSTRM-978] Provide functional requirement and use cases of the opDB standard library from the viewpoint of DRP (and more general aspects)

Comment by hassan [ 10/Jun/20 ]

Experimental SPS Log

We need to continue to be able to extract SPS log information from the opDB and present that from a web server to enable users to view what exposures have been made for a given visit. Current query performed is (provided by cloomis):

select seq.visit_set_id as set_id, sequence_type, name, min(visits.pfs_visit_id) as min_visit, max(visits.pfs_visit_id) as max_visit, cmd_str from sps_sequence seq join visit_set visits on visits.visit_set_id = seq.visit_set_id  where seq.visit_set_id >= 361 group by seq.visit_set_id  order by seq.visit_set_id;

Comment by hassan [ 10/Jun/20 ]

Or this query (provided by arnaud.lefur in #drp-2d channel 2020-06-10):

select sequence_type, name, comments, sps_sequence.visit_set_id, visit_set.pfs_visit_id, exp_type, sps_module_id, arm, notes, data_flag from sps_sequence \
inner join visit_set on visit_set.visit_set_id=sps_sequence.visit_set_id \
inner join sps_visit on sps_visit.pfs_visit_id=visit_set.pfs_visit_id \
inner join sps_exposure on sps_exposure.pfs_visit_id=visit_set.pfs_visit_id \
inner join sps_camera on sps_camera.sps_camera_id=sps_exposure.sps_camera_id \
left outer join sps_annotation on sps_exposure.pfs_visit_id=sps_annotation.pfs_visit_id order by visit_set.pfs_visit_id desc

Comment by hassan [ 10/Jun/20 ]

Extract YAML configuration file

For 2D DRP processing, it is important to be able to determine which biases, darks, flats and arcs need to be processed at a given point. This work is described in PIPE2D-423 and related tickets. There will be specific child tickets for opDB interactions raised shortly. No example queries are yet available on this specific work, but the query provided by A Lefur in the earlier comment may be a basis.

Comment by fmadec [ 02/Jul/20 ]

Here is the code I have for now to create log pages from opdb. I will clean that code and update pfs_utils lib to add the second page.

but it will give you an idea:

with psycopg2.connect("host='{}' port={} dbname='{}' user={}".format(host, port, dbname, user)) as conn:
    sql = "select seq.visit_set_id as visit_set_id, sequence_type, name,\
        min(visits.pfs_visit_id) as min_visit,\
        max(visits.pfs_visit_id) as max_visit, \
        cmd_str, comments, status \
        from sps_sequence seq \
        join visit_set visits on visits.visit_set_id = seq.visit_set_id \
        where seq.visit_set_id >= 361 \
        group by seq.visit_set_id \
        order by seq.visit_set_id DESC; \
        "
    dat = pd.read_sql_query(sql, conn, index_col="visit_set_id")dat.to_html("logbook_opdb_sm1_subaru.html")

and the all visits page:

sql_all = "select cmd_str,sps_exposure.pfs_visit_id,\
sps_sequence.visit_set_id, \
exp_type, time_exp_start,\
notes, data_flag, \
comments, name, status, sequence_type,\
sps_module_id,arm,sps_exposure.sps_camera_id \
from sps_exposure \
inner join visit_set on sps_exposure.pfs_visit_id=visit_set.pfs_visit_id \
inner join sps_sequence on visit_set.visit_set_id=sps_sequence.visit_set_id \
inner join sps_visit on sps_exposure.pfs_visit_id=sps_visit.pfs_visit_id \
inner join sps_camera on sps_exposure.sps_camera_id = sps_camera.sps_camera_id \
left outer join sps_annotation on sps_exposure.pfs_visit_id=sps_annotation.pfs_visit_id \
where sps_sequence.visit_set_id >= 361 \
order by sps_exposure.pfs_visit_id DESC; \

with psycopg2.connect("host='{}' port={} dbname='{}' user={}".format(host, port, dbname, user)) as conn:
    df_all = pd.read_sql_query(sql_all, conn)

df_all.sort_values("pfs_visit_id", inplace=True)
df_all.set_index(['visit_set_id', 'pfs_visit_id'], inplace=True)
df_all.to_html("logbook_opdb_visit_sm1_subaru.html")

[INSTRM-978] Provide functional requirement and use cases of the opDB standard library from the viewpoint of DRP (and more general aspects) Created: 11/May/20 Updated: 10/Nov/20 Resolved: 10/Nov/20
Status:	Done
Project:	Instrument control development
Component/s:	spt_operational_database
Affects Version/s:	None
Fix Version/s:	None

[INSTRM-978] Provide functional requirement and use cases of the opDB standard library from the viewpoint of DRP (and more general aspects) Created: 11/May/20 Updated: 10/Nov/20 Resolved: 10/Nov/20

Experimental SPS Log

Extract YAML configuration file