Summary
A sic_updated_timestamp column should be added to the cusi_case table
Background
- We need a column to store the latest timestamp when a given suggested investigations case last had signals or users added to it (see T411788 for why)
- We have a column for when the case was created, but we do not have a way to re-use it and so need a new column to store this
- We also want to be able to sort cases by this timestamp, so need to add it to an index
- This will need to be similar to the sic_created_timestamp_id index
Proposed schema changes
Note: All schema changes would be applied on extension1 and the table is very small so they likely could be applied via replication
First round of schema changes
Addition of a new sic_updated_timestamp column to cusi_case:
> describe cusi_case; +-----------------------+---------------------+------+-----+----------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+----------------+----------------+ | sic_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | sic_status | tinyint(3) unsigned | NO | MUL | 0 | | ... | sic_updated_timestamp | binary(14) | NO | MUL | | | +-----------------------+---------------------+------+-----+----------------+----------------+
Additionally, the addition of two indexes:
- sic_updated_timestamp_id on sic_updated_timestamp and sic_id
- sic_status_updated_timestamp_id on sic_status, sic_updated_timestamp and sic_id
+-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ ... | cusi_case | 0 | sic_updated_timestamp_id | 1 | sic_updated_timestamp | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_case | 0 | sic_updated_timestamp_id | 2 | sic_id | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_case | 0 | sic_status_updated_timestamp_id | 1 | sic_status | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_case | 0 | sic_status_updated_timestamp_id | 2 | sic_updated_timestamp | A | 0 | NULL | NULL | | BTREE | | | NO | | cusi_case | 0 | sic_status_updated_timestamp_id | 3 | sic_id | A | 0 | NULL | NULL | | BTREE | | | NO | +-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
Second round of schema changes
After the column is appropriately populated (using the current values of sic_created_timestamp), then remove the default from sic_updated_timestamp:
> describe cusi_case; +-----------------------+---------------------+------+-----+----------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+----------------+----------------+ | sic_id | int(10) unsigned | NO | PRI | NULL | auto_increment | ... | sic_updated_timestamp | binary(14) | NO | MUL | NULL | | +-----------------------+---------------------+------+-----+----------------+----------------+
If desired, we can drop the sic_created_timestamp_id and sic_status_created_timestamp_id indexes once T411788 is completed. However, if DBA is okay with it being left then I'd propose to leave the indexes in place in case they are needed in the future.
Acceptance criteria
- The first set of schema changes above has been merged into the master branch of the CheckUser
- A Schema-change-in-production ticket is filed for the first set of schema changes - T412687
- A ticket is filed to track implementing the second set of schema changes - T412688