[DBIx-Class-Devel] [dbsrgits/sql-translator] Support new column info 'initially_populated_from' and populate new columns when added (#94)

[Daniel Böhmer]

I use DBIC DeploymentHandler to generate DDL files for upgrading my database during schema changes. The generated SQL is valid but could be improved. Currently I use SQLite for development but it might apply to other DBMS as well.

My issue:
- I add a column to my `Result` class which is not nullable and has no default value.
- I upgrade the `$Schema::VERSION` and call `App::DH` with command `write_ddl`.
- `SQL::Translator` will simply generate `ALTER TABLE foo ADD COLUMN bar` but could do better by generating the fallback-style: create temporary table, copy data, recreate original table, insert back.

My reasoning:
- For 0 existing rows the result is the same: It just adds the column and works.
- For >=1 rows the simple `ADD COLUMN` will fail because the `NOT NULL` constraint is violated.
- With the fallback-style SQL `NULL` could be inserted which has the same result but *I could edit the SQL much easier and just replace `NULL` by any reasonable default value*.
- SQL::Translator could also use any global default like `1` which would make the SQL actually work in many cases.

Before I really understood the issue I talked through this on IRC with ribasushi and he came up with this solution:
> so I think what you actually want
> is an {extra} field of 'initially_populated_from_column'
> which is handled just like 'renamed_from' for columns themselves
> https://metacpan.org/source/ILMARI/SQL-Translator-0.11021/lib/SQL/Translator/Diff.pm#L390
> then the boilerplate can literally generate what you want without any hand editing
> and remains usable outside of your particular case as well ( it is a useful feature in general )
> probably just 'initially_populated_from' - takes both a scalar ( a column name ) and a scalarref ( a literal default )

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/dbsrgits/sql-translator/issues/94
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class-devel/attachments/20170825/d01934a3/attachment.htm>