History and trash

The History keeps every row change in Records with undo, and the trash keeps deleted tables and columns for 30 days.

History

Every row that records_change adds, changes or deletes in Records goes into the History as one entry, with the person, the agent, the table, the change and the values before and after. history_list shows three lists, newest first: the changes (entries), the actions (actions: each records_change call, each undo and each move to the trash) and the trash items (trash).

Undo

history_undo reverses row changes, and only row changes:

• With entry, it reverses one row change.
• With action, it reverses every row change one records_change call made, newest first. A call that changed no rows, such as CREATE TABLE or ALTER TABLE, has nothing to reverse and is refused with 42704.
• The table needs a primary key: undo finds the row by it. On a table without one, undo is refused with 55000. It is also refused with 55000 when the change altered the value of a GENERATED ALWAYS AS IDENTITY column; a GENERATED BY DEFAULT AS IDENTITY column does not block undo.
• When a newer change touched the same row, the undo is refused with 40001; undo the newer ones first. Undoing again what was already undone answers already_undone.

Changes to a table’s structure, such as creating a table or adding a column, cannot be undone. A table or column deleted with the functions below comes back through the trash, not through undo.

Trash

Records refuses DROP TABLE and DROP COLUMN. To delete a table, use SELECT siglata.drop_table('table'); to delete a column, SELECT siglata.drop_column('table', 'column'). The table or column goes to the trash and shows up in history_list with kind: "trash".

history_undo with trash brings the item back. Bringing back an item that already came back answers already_restored. The trash keeps each item for 30 days; after that, Siglata deletes it for good.