[INSTRM-1293] pull out status column from sps_sequence table Created: 18/Jun/21  Updated: 15/Jul/21  Resolved: 15/Jul/21

Status: Done
Project: Instrument control development
Component/s: spt_operational_database
Affects Version/s: None
Fix Version/s: None

Type: Task Priority: Normal
Reporter: arnaud.lefur Assignee: Kiyoto Yabe
Resolution: Done Votes: 0
Labels: SPS
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Blocks
blocks INSTRM-1291 Create visit_set and sps_sequence row... Done
Sprint: SM1PD-2021 A 12

 Description   

If we want to insert sps_sequence at the beginning, we need to pull out status column which is obviously known only at the end.

We would need a new table :
sps_sequence_status:

  • visit_set_id int4 not null
  • status_flag int4
  • comments varchar

You need to create the table in the first place then I will dump data from the existing column to the new table, then we can drop the status column from sps_sequence.

 Comments   
Comment by Kiyoto Yabe [ 18/Jun/21 ]

OK, making a new table is very easy, but migrating existing data is a little bit tricky, so we need a coordination. We are going to update the opDB schema very soon for the PFI integration anyway, so we can target then for this update. Is this needed for only `obslog` business?

Comment by arnaud.lefur [ 18/Jun/21 ]

Right. Yes, that's the main reason.

Comment by arnaud.lefur [ 28/Jun/21 ]

With INSTRM-1291 merged, we're ready to go forward.
But regarding the migration, we could maybe take advantage of INSTRM-1305, we copy sps_sequence to iic_sequence (without status column) and we keep sps_sequence as backup while we do the migration, sps_sequence_status should also be renamed to iic_sequence_status I believe.

Comment by Kiyoto Yabe [ 29/Jun/21 ]

In terms of alembic operation, the following procedure is easier for me:

  • create `iic_sequence` and `iic_sequence_status` (keeping `sps_sequence`) with alembic update (as a part of INSTRM-1293)
  • migrate existing records from `sps_sequence` to `iic_sequence` and `iic_sequence_status` (currently ~13000 rows, so not so large)
  • update `visit_set` constraints and drop `sps_sequence` (as a part of INSTRM-1305)
  • we probably need to ask michitaro to change `obslog` before we remove `sps_sequence`

BTW, `output` (in `iic_sequence_status`) seems a reserved keyword by SQL (not by postgres, but one cannot rule out the possibility that it becomes reserved by postgres in future). Any other good candidate names? If not, we can proceed with it, I think.

Comment by arnaud.lefur [ 29/Jun/21 ]

sounds good.

okay, let's switch to cmd_output then.

Generated at Sat Feb 10 16:33:52 JST 2024 using Jira 8.3.4#803005-sha1:1f96e09b3c60279a408a2ae47be3c745f571388b.