Missing data in completed observations table

[chazshapiro]

We need to verify all of the data is getting into the database.

Also, COMPLETED_OBSERVATIONS is looking sparse:

+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| OBSERVATION-LOG-ID | int          | NO   | PRI | NULL    | auto_increment |
| OBSERVATION_ID     | int          | YES  |     | NULL    |                |
| SET_ID             | int          | YES  |     | NULL    |                |
| TARGET_NUMBER      | int          | YES  |     | NULL    |                |
| SEQUENCE_NUMBER    | int          | YES  |     | NULL    |                |
| NAME               | varchar(128) | NO   | PRI | NULL    |                |
| FITS-FILE          | varchar(512) | YES  |     | NULL    |                |
| START-TIME         | datetime     | YES  |     | NULL    |                |
| END-TIME           | datetime     | YES  |     | NULL    |                |
| DURATION           | int          | YES  |     | NULL    |                |
| RA                 | varchar(32)  | YES  |     | NULL    |                |
| DECL               | varchar(32)  | YES  |     | NULL    |                |
| EPOCH              | varchar(8)   | YES  |     | NULL    |                |
| EXPTIME            | double       | YES  |     | NULL    |                |
| SLITWIDTH          | double       | YES  |     | NULL    |                |
| SLITOFFSET         | double       | YES  |     | NULL    |                |
| BINSPECT           | int          | YES  |     | NULL    |                |
| BINSPAT            | int          | YES  |     | NULL    |                |
| OBSMODE            | varchar(32)  | YES  |     | NULL    |                |
| CASANGLE           | float        | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

What is DURATION? I think EPOCH is deprecated.

I think we should at least add the following (and maybe change some names for consistency):

• OWNER
• ALT
• AZ
• AIRMASS (start or end or both)
• EXPTIME request
• SLIT request
• SLITANGLE request
• POINTMODE
• NOTE
• NOTBEFORE
• SLITANGLE (calculated from CASANGLE - maybe skip)
• OTMflag

These are all optional inputs but could be useful:

• AIRMASS_MAX
• WRANGE_LOW
• WRANGE_HIGH
• CHANNEL
• MAGNITUDE
• MAGSYSTEM
• MAGFILTER
• SRCMODEL

[astronomerdave]

• Yes remove EPOCH
• DURATION ? The only reference to that word in the design document is this, which doesn't sound like something that would be stored in the database:

The left bar indicates the progress of overhead operations including slew, settling, acquisition, etc. The right bar indicates the progress of the exposure. In both cases, the elapsed time of the task (underway or pending) is displayed within the bar along with the total duration. Figure 17 shows two progress indicators that illustrate this.

[chazshapiro]

Looks like it's measuring time starting from the slew. So we could add SLEW_START and then subtract that from END-TIME to get DURATION.

[chazshapiro]

New table -- note it is now completed_obs . Scroll to the right to see column definitions. It's OK to not put in a value (except for NAME and OWNER).

UPDATE: Changed some doubles to floats.

mysql> show full columns from completed_obs;   
+-----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+------------------------------------------------+
| Field           | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment                                        |
+-----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+------------------------------------------------+
| LOG_ID          | int          | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |                                                |
| OWNER           | varchar(32)  | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | Name of target list owner                      |
| OBSERVATION_ID  | int          | NULL            | YES  |     | NULL    |                | select,insert,update,references | ID from the master targets table               |
| SET_ID          | int          | NULL            | YES  |     | NULL    |                | select,insert,update,references | ID of the user's target set                    |
| TARGET_NUMBER   | int          | NULL            | YES  |     | NULL    |                | select,insert,update,references |                                                |
| SEQUENCE_NUMBER | int          | NULL            | YES  |     | NULL    |                | select,insert,update,references |                                                |
| NAME            | varchar(128) | utf8_general_ci | NO   | PRI | NULL    |                | select,insert,update,references | Name of the astronomical target or calibration |
| FITSFILE        | varchar(512) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | File with the spectrum images                  |
| RA              | varchar(32)  | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | Target Right Ascension                         |
| DECL            | varchar(32)  | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | Target Declination                             |
| TELRA           | varchar(32)  | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | Telescope RA                                   |
| TELDECL         | varchar(32)  | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | Telescope DECL                                 |
| ALT             | float        | NULL            | YES  |     | NULL    |                | select,insert,update,references | Telescope Altitude (deg) at exposure start     |
| AZ              | float        | NULL            | YES  |     | NULL    |                | select,insert,update,references | Telescope Azimuth (deg) at exposure start      |
| AIRMASS         | float        | NULL            | YES  |     | NULL    |                | select,insert,update,references | Telescope Airmass at exposure start            |
| CASANGLE        | float        | NULL            | YES  |     | NULL    |                | select,insert,update,references | Cassegrain angle of the P200                   |
| SLITANGLE_REQ   | varchar(8)   | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | Slit angle request                             |
| POINTMODE       | varchar(8)   | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | Where to place target (ACAM or SLIT)           |
| NOTBEFORE       | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references | Earliest date/time to start exposure           |
| SLEW_START      | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references | Slew start date/time                           |
| SLEW_END        | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references | Slew end date/time                             |
| EXPTIME         | float        | NULL            | YES  |     | NULL    |                | select,insert,update,references | Exposure time (s)                              |
| EXPTIME_REQ     | varchar(12)  | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | Exposure time request                          |
| EXP_START       | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references | Exposure start date/time                       |
| EXP_END         | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references | Exposure end date/time                         |
| SLITWIDTH       | float        | NULL            | YES  |     | NULL    |                | select,insert,update,references | Spectrograph slit width (arcsec)               |
| SLITWIDTH_REQ   | varchar(12)  | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | Slit width request                             |
| SLITOFFSET      | float        | NULL            | YES  |     | NULL    |                | select,insert,update,references | Slit lateral offset (arcsec)                   |
| BINSPECT        | int          | NULL            | YES  |     | NULL    |                | select,insert,update,references | CCD binning in spectral direction              |
| BINSPAT         | int          | NULL            | YES  |     | NULL    |                | select,insert,update,references | CCD binning in spatial direction               |
| OBSMODE         | varchar(32)  | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | Observation mode, CCD settings                 |
| NOTE            | varchar(512) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | Observer's note on this target                 |
| OTMFLAG         | varchar(20)  | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references | OTM flag codes at time of exposure             |
+-----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+------------------------------------------------+
33 rows in set (0.00 sec)

[chazshapiro]

LOG_ID -- filled in automatically OWNER -- not optional NAME -- not optional

OBSMODE -- means CCDMODE

[astronomerdave]

New table -- note it is now completed_obs . Scroll to the right to see column definitions. It's OK to not put in a value (except for NAME and OWNER).

UPDATE: Changed some doubles to floats.

mysql> show full columns from completed_obs;   
+-----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+------------------------------------------------+
| Field           | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment                                        |
+-----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+------------------------------------------------+
| LOG_ID          | int          | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |                                                |
| OWNER           | varchar(32)  | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references | Name of target list owner                      |
| OBSERVATION_ID  | int          | NULL            | YES  |     | NULL    |                | select,insert,update,references | ID from the master targets table               |
| SET_ID          | int          | NULL            | YES  |     | NULL    |                | select,insert,update,references | ID of the user's target set                    |
| TARGET_NUMBER   | int          | NULL            | YES  |     | NULL    |                | select,insert,update,references |                                                |
| SEQUENCE_NUMBER | int          | NULL            | YES  |     | NULL    |                | select,insert,update,references |                                                |
etc

@chazshapiro is there anything special about the order here?

[chazshapiro]

mysql doesn't care about column order or column case

[astronomerdave]

What is the format of thesse datetime fields, is it a string "YYYYMMDDTHHMMSS"?

| NOTBEFORE | datetime | NULL | YES | | NULL | | select,insert,update,references | Earliest date/time to start exposure | | SLEW_START | datetime | NULL | YES | | NULL | | select,insert,update,references | Slew start date/time | | SLEW_END | datetime | NULL | YES | | NULL | | select,insert,update,references | Slew end date/time | | EXP_START | datetime | NULL | YES | | NULL | | select,insert,update,references | Exposure start date/time | | EXP_END | datetime | NULL | YES | | NULL | | select,insert,update,references | Exposure end date/time |

[astronomerdave]

Why are the requests strings, while the actuals are floats?

| EXPTIME_REQ | varchar(12) | utf8_general_ci | YES | | NULL | | select,insert,update,references | Exposure time request | | SLITWIDTH_REQ | varchar(12) | utf8_general_ci | YES | | NULL | | select,insert,update,references | Slit width request |

[chazshapiro]

Request strings include the "SET" or other ETC command

[chazshapiro]

datetime format appears to be: 2023-11-01 22:18:17.086

[astronomerdave]

| SLITANGLE_REQ | varchar(8) | utf8_general_ci | YES | | NULL | | select,insert,update,references | Slit angle request |

What you call SLITANGLE_REQ here, is that the same as OTMslitangle read in from the targets table?

[chazshapiro]

In the targets table it's just SLITANGLE

[chazshapiro]

I think the design doc is correct. I think we're just choosing names poorly.

We could let SLITANGLE mean the same thing in targets and completed_obs, i.e. the request. Then we could have SLITANGLE_ACT in completed_obs to denote the actual value.

OR... we could change the targets table request from SLITANGLE to SLITANGLE_REQ to match the completed_obs table I made above.

Either way, the OTMxxxx columns are intermediate and mostly not copied to completed_obs

[chazshapiro]

Even though it's more work, I think it will be less confusing long term to go back and rename some of the columns in the targets table.

[astronomerdave]

Renaming is fine, or not, I just need the names of the columns to read from the targets table and their corresponding names to write back into the completed targets table.