search

simonw / sqlite-history

Track changes to SQLite tables using triggers
Apache License 2.0
104 stars 2 forks source link

Document how to handle alter table #8

Open simonw opened 1 year ago

simonw commented 1 year ago

I am tempted to say that alter table isn't supported at all.

But actually, I just realized that the _mask column can stay useful provided you only ever add new columns to the table, and you add them at the end.

You would need to update the triggers. All previous history records would assume a column storing null which I think is OK.

metamoof commented 1 year ago

I’m not sure that is OK. Strictly speaking, an ALTER TABLE should run the equivalent action of an UPDATE trigger adding the default value of the column to every single row of the table.

Maybe have that as an option on the ALTER TABLE function generator? Let the user decide if they want that specific traceability in their history?

simonw commented 1 year ago

https://social.lol/@rameez/110205766086091801

One way of handling ALTERed tables would be to split _mask into individual columns (_name_changed, _age_changed, etc.) instead and storing 0/1 in them.

andrewmarx commented 1 month ago

I have a side project I started a few years ago using shadow tables, and I've been trying to figure this out, too, because I want users to have the flexibility to modify their database, but I don't want them to lose data with a bad change accidentally. A couple of options I've considered are:

  1. Only allow columns to be modified in a non-destructive manner. I.e., numeric columns can be converted to string columns, but not vice-versa. This allows at least some level of alteration
  2. Instead of modifying columns directly, create a new column with the new data types, copy the values from the old column, and then delete the old column in the table but not the history. But this seems like it could get complicated fast