Export to GitHub

cakephp-migrations - issue #3

recipe for migrating from values to keys


Posted on Mar 18, 2008 by Swift Rhino

This is more of a call for snippets or best practices using the toolkit.

if i have a table with column that had values (e.g. varchar) and i later want to make those values an entity itself, i have to make a table and do an update query to copy unique values into that new table, replace column with id referencing the entity table instead.

it's a multi-step process to migrate in this case: 1. create new_entity table 2. insert into new_entity table where select distinct existing_table.entity_value 3. alter existing_table add column entity_id int not null 4. update existing_table set entity_id = (select id from new_entity table where new_entity.value = existing_table.entity_value) 5. remove existing_table column entity_value %% optional I suppose

I think migrations don't allow multisteps to go to a particular revision. Does this need to be done across multiple revisions?

Comment #1

Posted on Mar 19, 2008 by Swift Rhino

After some experimentation, it appears that at least the attributes of 'DOWN' migration are done in sequence, i.e. DOWN: add_field: bugs: component: [string, 64] query: bugs: update bugs set component = (select component_name from components as c where c.id = component_id)

results in first adding a field, then invoking the SQL.

Comment #2

Posted on Mar 19, 2008 by Swift Rhino

If I try to combine all these into a single migration file however, the steps don't seem to run right.

DOWN: add_fields: bugs: component: [string, 64] query: bugs: update bugs set component = (select component_name from bug_components as c where c.id = component_id) drop_table: bug_components drop_field: bugs: [component_id]

results in: Migrating database down from version 3 to 2 ...

[003] Create Components ...
    > adding column 'component' on 'bugs'
    > running SQL
    > dropping table 'bug_components'
    > dropping column 'component_id' on 'bugs'

Migrations completed.

but schema shows that 'component' is missing from 'bugs' table.

Comment #3

Posted on May 13, 2008 by Grumpy Ox

I tested and this works fine.

Status: WontFix

Labels:
Type-Defect Priority-Medium