[odb-users] Altering column from integer to double precision

[Philip Ethier]

Thanks Boris. I got this working my way using the manual scripts that I
wrote.

But then I had an issue with an in code migration. I was trying to use the
void schema_version_migration() function to set my version, but it doesn't
seem to be persisting even though no exceptions are thrown. Do I have to
wrap in a transaction? Any other ideas?

Thanks again,
On Aug 13, 2014 7:27 AM, "Boris Kolpackov" <boris at codesynthesis.com> wrote:

> Hi Philip,
>
> Philip Ethier <philip.ethier at gmail.com> writes:
>
> > So I came up with the following approach, do you think it will work
> > correctly?
> >
> > 1 remove the int member var, odb compile with updated version
> > 2 add the double member var, odb compile with updated version
> > 3 write manual alter sql to change the column type from int to double
> > precision
> > 4 SQL to update the schema_version version column to the latest
> > 5 start the program, and make sure to not run the pre and post migration
> sql
>
> I "think" this will work.
>
> Generally, when it comes to database schema evolution, I believe your
> changes should be in the "obviously will work" category rather than
> "think will work". Otherwise this stuff will quickly get out of hand.
> Which brings us to your next questions:
>
>
> > Is this the recommended way to do the alter?
>
> No, this is definitely not the recommended way ;-). You are half way
> relying on ODB and half way doing your custom migration.
>
> The way I would implement this is like this:
>
> 1. Increment the model version.
>
> 2. Soft-delete the int member variable in this version (you can rename
>    it also but keep its column name the same; db column pragma)
>
> 3. Soft-add the double member variable in this version (you can use
>    the old var's name but make sure to give it a new column name).
>
> 4. Register data migration function that converts int's to double's.
>
> While this may not be as efficient (we have to load all the int's
> and store all the double's) or "seamless" (we will end up with a
> new column name), it is simple to the point that you can trust
> it will actually do what you think it should.
>
> You can read more about soft-add/delete in Section 13.4, "Soft Object
> Model Changes" in the ODB manual.
>
> Boris
>