Doctrine schema changes while keeping data?

Question

We're developing a Doctrine backed website using YAML to define our schema. Our schema changes regularly (including fk relations) so we need to do a lot of:

    Doctrine::generateModelsFromYaml(APPPATH . 'models/yaml', APPPATH . 'models', array('generateTableClasses' => true));
    Doctrine::dropDatabases();
    Doctrine::createDatabases();
    Doctrine::createTablesFromModels();

We would like to keep existing data and store it back in the re-created database. So I copy the data into a temporary database before the main db is dropped.

How do I get the data from the "old-scheme DB copy" to the "new-scheme DB"? (the new scheme only contains NEW columns, NO COLUMNS ARE REMOVED)

NOTE:

This obviously doesn't work because the column count doesn't match.

SELECT * FROM copy.Table INTO newscheme.Table

This obviously does work, however this is consuming too much time to write for every table:

SELECT old.col, old.col2, old.col3,'somenewdefaultvalue' FROM copy.Table as old INTO newscheme.Table

Answer 1

How about writing a script (using the Doctrine classes for example) which parses the yaml schema files (both the previous version and the "next" version) and generates the sql scripts to run? It would be a one-time job and not require that much work. The benefit of generating manual migration scripts is that you can easily store them in the version control system and replay version steps later on. If that's not something you need, you can just gather up changes in the code and do it directly through the database driver.

Of course, the more fancy your schema changes becomes, the harder the maintenance will get i.e. column name changes, null to not null etc.

Answer 2

Have you looked into Migrations? They allow you to alter your database schema in programmatical way. WIthout losing data (unless you remove colums, of course)