<p>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.</p>
<p>My issue:</p>
<ul>
<li>I add a column to my <code>Result</code> class which is not nullable and has no default value.</li>
<li>I upgrade the <code>$Schema::VERSION</code> and call <code>App::DH</code> with command <code>write_ddl</code>.</li>
<li><code>SQL::Translator</code> will simply generate <code>ALTER TABLE foo ADD COLUMN bar</code> but could do better by generating the fallback-style: create temporary table, copy data, recreate original table, insert back.</li>
</ul>
<p>My reasoning:</p>
<ul>
<li>For 0 existing rows the result is the same: It just adds the column and works.</li>
<li>For &gt;=1 rows the simple <code>ADD COLUMN</code> will fail because the <code>NOT NULL</code> constraint is violated.</li>
<li>With the fallback-style SQL <code>NULL</code> could be inserted which has the same result but <em>I could edit the SQL much easier and just replace <code>NULL</code> by any reasonable default value</em>.</li>
<li>SQL::Translator could also use any global default like <code>1</code> which would make the SQL actually work in many cases.</li>
</ul>
<p>Before I really understood the issue I talked through this on IRC with ribasushi and he came up with this solution:</p>
<blockquote>
<p>so I think what you actually want<br>
is an {extra} field of 'initially_populated_from_column'<br>
which is handled just like 'renamed_from' for columns themselves<br>
<a href="https://metacpan.org/source/ILMARI/SQL-Translator-0.11021/lib/SQL/Translator/Diff.pm#L390">https://metacpan.org/source/ILMARI/SQL-Translator-0.11021/lib/SQL/Translator/Diff.pm#L390</a><br>
then the boilerplate can literally generate what you want without any hand editing<br>
and remains usable outside of your particular case as well ( it is a useful feature in general )<br>
probably just 'initially_populated_from' - takes both a scalar ( a column name ) and a scalarref ( a literal default )</p>
</blockquote>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">&mdash;<br />You are receiving this because you are subscribed to this thread.<br />Reply to this email directly, <a href="https://github.com/dbsrgits/sql-translator/issues/94">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AASeAv1l0GqKu7RUM18hTru2GfnEJ_cFks5sbuJhgaJpZM4PCyMn">mute the thread</a>.<img alt="" height="1" src="https://github.com/notifications/beacon/AASeApeyuStTt2nlssCIod_MO6FF_7DSks5sbuJhgaJpZM4PCyMn.gif" width="1" /></p>
<div itemscope itemtype="http://schema.org/EmailMessage">
<div itemprop="action" itemscope itemtype="http://schema.org/ViewAction">
  <link itemprop="url" href="https://github.com/dbsrgits/sql-translator/issues/94"></link>
  <meta itemprop="name" content="View Issue"></meta>
</div>
<meta itemprop="description" content="View this Issue on GitHub"></meta>
</div>

<script type="application/json" data-scope="inboxmarkup">{"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4bb","name":"GitHub"},"entity":{"external_key":"github/dbsrgits/sql-translator","title":"dbsrgits/sql-translator","subtitle":"GitHub repository","main_image_url":"https://cloud.githubusercontent.com/assets/143418/17495839/a5054eac-5d88-11e6-95fc-7290892c7bb5.png","avatar_image_url":"https://cloud.githubusercontent.com/assets/143418/15842166/7c72db34-2c0b-11e6-9aed-b52498112777.png","action":{"name":"Open in GitHub","url":"https://github.com/dbsrgits/sql-translator"}},"updates":{"snippets":[{"icon":"DESCRIPTION","message":"Support new column info 'initially_populated_from' and populate new columns when added (#94)"}],"action":{"name":"View Issue","url":"https://github.com/dbsrgits/sql-translator/issues/94"}}}</script>