Fix adding dates for report history

[cacereslab]

Currently dates from report history don’t make any sense. They seem to be updated every time a change in made to an inversion, included that of the original definition of the inversion (see for example HsInv0379 or HsInv0015). If information is stored somewhere we could try to recover original dates. Otherwise we can start adding right dates from now on.

[emsanz]

All the scripts and SQL functions/procedures look correct. The problem it's in the table configuration:

• Table: breakpoints
• Column: date
• Column default value: current_timestamp
• Column extras: on update current_timestamp

I've deleted the extras. Not tested, but it should work properly now.

MySQL command: ALTER TABLE breakpoints MODIFY date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

[emsanz]

Tested: the date of an old change is not replaced when a new change is done

Setback: only the date is setup, not the time. Solution: in MySQL, function add_BP() >> In the declaration of the "next_date" variable, the "type" of the variable is "date" when it should be "datetime". DECLARE next_date DATETIME ;

[RuthGG]

Update: The dates were being setup correctly, but all the times were stored as 00:00:00.

To solve it, in MySQL, te functions update_BP and add_BP do not declare the next_date variable anymore; they insert directly into the breakpoints table CURRENT_TIMESTAMP().

 INSERT INTO breakpoints    (inv_id, chr, bp1_start, bp1_end, bp2_start, bp2_end, definition_method, description, date)
        VALUES (inversion_id_val, chr_val, bp1s_val, bp1e_val,  bp2s_val, bp2e_val, "manual curation", description_val, CURRENT_TIMESTAMP());