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 )
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:
Resultclass which is not nullable and has no default value.$Schema::VERSIONand callApp::DHwith commandwrite_ddl.SQL::Translatorwill simply generateALTER TABLE foo ADD COLUMN barbut could do better by generating the fallback-style: create temporary table, copy data, recreate original table, insert back.My reasoning:
ADD COLUMNwill fail because theNOT NULLconstraint is violated.NULLcould be inserted which has the same result but I could edit the SQL much easier and just replaceNULLby any reasonable default value.1which 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: