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

[Boris Kolpackov]

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