Migrating computed Fields?

[Martin Felder]

Hi everyone,

I have a small migration problem here: In my Table (Postgres adapter, in case this matters) I originally had the fields

    Field('co2_pot_masse', type='double'),
    Field('thermisch_bedingt', compute=lambda r: 0.18*r.co2_pot_masse),

Now, if I change this to

    Field('co2_pot_masse', type='double'),
    Field('thermisch_bedingt', type='double', compute=lambda r: 0.18*r.co2_pot_masse),

which obviously makes more sense (my bad..) I get an error:

<class 'psycopg2.ProgrammingError'> column "elektrisch_bedingt__tmp" is of type double precision but expression is of type character varying LINE 1: UPDATE Zement SET elektrisch_bedingt__tmp=elektrisch_bedingt... ^ HINT: You will need to rewrite or cast the expression.

At this point, I am reluctant to drop the table and recreate it, because there are linked tables and data has been edited manually. Is there a simpler way for recovery?

Thanks a lot,
Martin

[Johann Spies]

One possibility (I think) is to do it in phases:

remove the original computed field from the table definition.

Run 'python web2py -M -N -S <yourapp>'

and check that the new definition is active (without the removed field).

Make sure that the field was dropped in postgresql.  If not, drop the field using pgadmin3 or psql..

Then add the correct definition of the field to your table definition and run a small script function to update the computed field.

I use something like this:

rows = db(db.sabinet_artikels).select(db.sabinet_artikels.id,
                                      db.sabinet_artikels.ti)
for row in rows:
    row.update_record(ltitle = db.sabinet_artikels.ltitle.compute(row))

Regards
Johann
--

Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)