Automigration: handling of non-constant DEFAULT values

[ikusteu]

TL;DR

Handle cases when automigration requires the creation of columns with non-constant DEFAULT values. This is currently broken and should either:

• be explicitly forbidden (failing early)
• handled to the best of the ability with current flow (no trivial)

The Issue

When first specifying a table, e.g.:

CREATE TABLE user (
  id INTEGER NOT NULL,
  // ...
  updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now') * 1000),
  PRIMARY KEY(id)
)

the non-constant default value (for updated_at in this case) is acceptable as it instructs the DB engine on how to assign default values on (future) row insertions.

It's a challenge, however in ALTER TABLE statements: in ALTER TABLE, non-constant default values aren't allowed as the DEFAULT values are used both for:

• future row insertions (as in CREATE TABLE case)
• setting of values for all existing rows that now have a new column

Since crsql_automigrate loads up the schema in :memory: DB, diffs the ctx DB and mem DB and applies updates (table/column insertions, drops, etc.) to the ctx DB, using ALTER TABLE (in case of new columns), it breaks on non-constant default values (being added to a table), e.g. following schema update:

CREATE TABLE user (
   id INTEGER NOT NULL,
   // ...
   updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now') * 1000),
+ last_seen INTEGER NOT NULL DEFAULT (strftime('%s', 'now') * 1000),
   PRIMARY KEY(id)
)

This is a non-trivial thing to support and I see it as a limitation of current way of automigrating (based on schema diff), one can't always infer how the (non-constant) default value should be used in case of setting values for existing rows (when adding the column) and how it should behave for future inserts (ok...this part is clear from the schema).