IRIDA/Lobby

I'll write up some more detailed instructions someplace else (github maybe, or our docs) but in general restoring sequencing files deleted from a sample involves the table sample_sequencingobject which stores a single row which which sample is associated with which sequencing object:

select * from sample_sequencingobject;
+----+---------------------+-----------+---------------------+
| id | created_date        | sample_id | sequencingobject_id |
+----+---------------------+-----------+---------------------+
|  1 | 2022-01-11 12:38:16 |         1 |                   1 |
+----+---------------------+-----------+---------------------+

The table sample_sequencingobject_AUD stores all modifications made to the sample_sequencingobject table:

select * from sample_sequencingobject_AUD;
+----+---------------------+-----------+---------------------+-----+---------+
| id | created_date        | sample_id | sequencingobject_id | REV | REVTYPE |
+----+---------------------+-----------+---------------------+-----+---------+
|  1 | 2022-01-11 12:38:16 |         1 |                   1 |   6 |       0 |
|  1 | 2022-01-11 12:38:16 |         1 |                   1 |  47 |       2 |
+----+---------------------+-----------+---------------------+-----+---------+

Here, REV is a unique identifier for every operation performed (e.g., create, update, delete). REVTYPE defines the specific operation performed (a REVTYPE of 2 means a deletion).

So if you look at:

select * from sample_sequencingobject_AUD where REVTYPE = 2;
+----+---------------------+-----------+---------------------+-----+---------+
| id | created_date        | sample_id | sequencingobject_id | REV | REVTYPE |
+----+---------------------+-----------+---------------------+-----+---------+
|  1 | 2022-01-11 12:38:16 |         1 |                   1 |  47 |       2 |
+----+---------------------+-----------+---------------------+-----+---------+

You can see all delete operations on the sample_sequencingobject table. You can also see that the deleted information is still saved in this table (e.g., the specific sample_id=1 and sequencingobject_id=1 defining which sequencing object used to be linked with which sample).

To restore the link between the sample_id and sequencingobject_id you can re-insert this data into the sample_sequencingobject table like so:

start transaction;
insert into sample_sequencingobject (id,created_date,sample_id,sequencingobject_id) select id,created_date,sample_id,sequencingobject_id from sample_sequencingobject_AUD where REV in (47) and REVTYPE = 2;
commit;

This will re-insert the entry into the sample_sequencingobject table linking the sample and sequence data back up in IRIDA:

select * from sample_sequencingobject;
+----+---------------------+-----------+---------------------+
| id | created_date        | sample_id | sequencingobject_id |
+----+---------------------+-----------+---------------------+
|  1 | 2022-01-11 12:38:16 |         1 |                   1 |
+----+---------------------+-----------+---------------------+

(see above screenshot)