Migrations in ullright

Introduction

Doctrine offers a fine migration mechanism to keep the database schema up to date.
But ullright needs more than that to allow easy upgrading for customer installations.

Upgrade Workflow

When upgrading a typical customer installation to the latest ullright version, the following steps are performed:

Subversion update to get the latest files from the ullright repository
Perform the "pre build model" migrations
This migration is mainly used for file system changes.
Example: Add or modify or delete files in the custom area
Build the doctrine model classes
Get and load the production database
Get production uploads
Perform the original doctrine migrations
This migration is used for database schema changes
Perform the "custom" migrations
This migration is used for custom database changes
Perform the "data" migrations
This migration is used for modifying the database data

References

Pre Build Model Migration

This migraton type is used to perform file system changes outside of the generic ullright areas.

Examples: Add a file in apps/frontend/, delete model files in lib/model/, ...

They work the same way as the original doctrine migrations with the following differences:

Instead of lib/migration/doctrine, they are stored in lib/migration/pre_build_model/

There is a separate command line task to execute the migrations:
php symfony ullright:migrate-pre-build-model
The arguments and options are the same as with the original "php symfony doctrine:migrate" task
The current migration version is not stored in the database but in data/pre_build_model_version.txt

Note: To delete model files in lib/model - e.g. for a removed table - this migration type needs to be called before build-model during upgrades.

Doctrine Migration

Used for changed to the schema.

Examples: Add a new table, add a column, ...

Create new migration

Note: You don't need to manually generate a migration if you want to add a new database table. Use the "semi-automatisation" described below.

To create a new migration class type the following command:

php symfony doctrine:generate-migration ull_wiki_deleted_at

Note:

Give the name of the migration class in underscore syntax
Don't use the following naming syntax: add_model_name e.g. add_ull_mail_queued_message as this collides with the migrations generated by "php symfony doctrine:generate-migrations-model" (see below).

The new class will be generated in lib/migration/doctrine/

See the doctrine documentation for all available options:
http://www.doctrine-project.org/documentation/manual/1_2/en/migrations

Make sure the down() method contains valid downwards migration code or throws a Doctrine_Migration_IrreversibleMigrationException

Semi-automatisation for new tables

If you create a complete new table you can make your life easier as follows:

Change your schema (schema.yml)
Run php symfony doctrine:build-model
Run php symfony doctrine:generate-migrations-model
This creates migration classes which represent the current state of the database
- The files are created in: plugins/ullCorePlugin/lib/migrations
- There is one migration foreach table and a final migration for the foreign keys
Delete all migrations except the one(s) for your new table(s),

Add a postUp() method to the last migration with the command to refresh/recreate foreign keys:

public function postUp()
{
    RecreateForeignKeysTask::recreateAllForeignKeysFromModel();
}

Be sure to delete the unused auto-created migrations before commiting!
Run migration:
- php symfony doctrine:migrate

Custom Migration

This migration type is similar to the original doctrine migration, but for custom database schema updates.

Examples: Add a custom table, or add a custom column

They work the same way as the original doctrine migrations with the following differences:

Instead of lib/migration/doctrine, they are stored in lib/migration/custom/

There is a separate command line task to execute the migrations:
php symfony ullright:migrate-custom
The arguments and options are the same as with the original "php symfony doctrine:migrate" task
If you want to use the ullright:fake-migration-version task, add the 'custom' option
php symfony ullright:fake-migration-version 2 custom

See the chapter about doctrine migrations for more information.

Data Migration

The data migration type is used to modify the data of the database.

Originally this was ment to be performed by the original doctrine migrations in the postUp() method. But since this incurres a lot of problems ullright introduced a separate migration type for data.

A more detailed explanation of these problems

They work the same way as the original doctrine migrations with the following differences:

Instead of lib/migration/doctrine, they are stored in lib/migration/data/

There is a separate command line task to execute the migrations:
php symfony ullright:migrate-data
The arguments and options are the same as with the original "php symfony doctrine:migrate" taskTips and tricks

Adding a foreign key column

When creating a new column for foreign key usage, be sure to check for

type: integer
length: 8

Running a symfony task

Since we have no symfony context in the migrations, but we usually are in the base directory, we can simply do

echo shell_exec('php symfony cache:clear');

Various initializations

    // Load symfony config
    $configuration = ProjectConfiguration::getApplicationConfiguration('frontend', 'prod', false);

    // Manually activate class overriding in apps/ for columnConfigs
    $autoloader = sfSimpleAutoload::getInstance();
    $autoloader->addDirectory(sfConfig::get('sf_lib_dir'));
    $autoloader->addDirectory(sfConfig::get('sf_app_lib_dir'));
    $autoloader->register();
   
    $databaseManager = new sfDatabaseManager($configuration);
    $dbh = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
   
    // Create symfony context
    sfContext::createInstance($configuration);

    // Load helpers
    sfContext::getInstance()->getConfiguration()->loadHelpers(array('ull', 'I18N'));
    
    // Get cultures in use
    $cultures = sfConfig::get('app_i18n_supported_languages', array('en'));

TODO

A fourth type of migration is necessary in certain cases for each developer instance.

Example: Changing the svn repo url

Workflow

Similiar to pre-build-model migration with a file counting the numbers
The file need to be ignores by svn

Tips and tricks

Adding a foreign key column

When creating a new column for foreign key usage, be sure to check for

type: integer
length: 8

Add a default value

$this->addColumn('ull_course', 'is_with_children', 'boolean', null, array('default' => true));

Running a symfony task

Since we have no symfony context in the migrations, but we usually are in the base directory, we can simply do

echo shell_exec('php symfony cache:clear');

Example for deletion of a model

    echo shell_exec('svn --force delete ' . $libPath . '/model/doctrine/ullFlowPlugin/base/BaseUllFlowAppPermission.class.php');
    echo shell_exec('svn --force delete ' . $libPath . '/model/doctrine/ullFlowPlugin/UllFlowAppPermission.class.php');
    echo shell_exec('svn --force delete ' . $libPath . '/model/doctrine/ullFlowPlugin/UllFlowAppPermissionTable.class.php');
   
    echo shell_exec('svn --force delete ' . $libPath . '/form/doctrine/ullFlowPlugin/base/BaseUllFlowAppPermissionForm.class.php');
    echo shell_exec('svn --force delete ' . $libPath . '/form/doctrine/ullFlowPlugin/UllFlowAppPermissionForm.class.php');
   
    echo shell_exec('svn --force delete ' . $libPath . '/filter/doctrine/ullFlowPlugin/base/BaseUllFlowAppPermissionFormFilter.class.php');
    echo shell_exec('svn --force delete ' . $libPath . '/filter/doctrine/ullFlowPlugin/UllFlowAppPermissionFormFilter.class.php');

Using dbh

Deprecated for normal migrations. Could still be useful for custom migrations in the postUp() method.

// Get dbh
$dbh = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
    
// SELECT
$result = $dbh->query("SELECT id FROM ull_entity WHERE display_name = 'NewsletterAdmins'");
$data = $result->fetch(PDO::FETCH_ASSOC);
$id = $data['id'];

// INSERT
$dbh->query("INSERT INTO ull_entity (type, display_name, namespace) 
      VALUES ('group', 'NewsletterAdmins', 'ull_newsletter')");

$id = $dbh->lastInsertId();

// UPDATE
$result = $dbh->query("UPDATE ull_cms_item SET sequence='20090101' WHERE sequence IS NULL and type = 'page'");
 
// DELETE
$dbh->query("DELETE FROM ull_permission WHERE slug='ullFlow_trouble_ticket_global_read'");
 
// Add a column if it does not exist yet:
try
{
  $result = $dbh->query("SELECT birth_date FROM ull_user LIMIT 1");
}
catch (Exception $e)
{
  $this->addColumn('ull_user', 'birth_date', 'date');
}    

// Bulk/Mass Insert without good speed and no memory exhaustion
        $data = array();
        
        foreach($userIds as $userId)
        {
          $data[] =
            "(" .
               "$newListId, " .
               "$userId, " .
               "NOW(), " .
               "NOW(), " .
               "$loggedInUserId, " .
               "$loggedInUserId " .
            ")"
          ;           
        }
        
        $dataString = implode(', ', $data);
      
        $q = 
            "INSERT INTO ull_newsletter_mailing_list_subscriber (" . 
               "ull_newsletter_mailing_list_id, " .
               "ull_user_id, " .
               "created_at, " .
               "updated_at, " .
               "creator_user_id, " .
               "updator_user_id " .
            ") VALUES " . $dataString 
          ;
          
        $dbh->query($q);

Update records with added behaviour

Example: add sluggable and create slugs for each record

      $entries = Doctrine::getTable('UllLanguage')->findAll();
      foreach ($entries as $entry)
      {
        $entry->state(Doctrine_Record::STATE_DIRTY);
        $entry->save();
      }

Old Stuff

Automate Migrations with "Diff" Feature

As of 2009-01-18 and sf1.3 the automatic diff "php symfony doctrine:generate-migrations-diff" does not work.

Fatal error: Class 'ToPrfxUllRecord' not found in /tmp/toprfx_doctrine_tmp_dirs/ToPrfxUllProject.php on line 17

Recherche

Involved classes:

sfDoctrineGenerateMigrationsDiffTask
Doctrine_Task_GenerateMigrationsDiff
Doctrine_Migration_Diff
Doctrine_Migration_Builder

Call Stack:

    0.0008      60500   1. {main}() /var/www/ullright_with_sf13/symfony:0
    0.0164     324740   2. include('/var/www/ullright_with_sf13/lib/vendor/symfony/lib/command/cli.php') /var/www/ullright_with_sf13/symfony:14
    0.5165    7496216   3. sfSymfonyCommandApplication->run() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/command/cli.php:20
    0.5336    7497956   4. sfTask->runFromCLI() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/command/sfSymfonyCommandApplication.class.php:76
    0.5337    7498236   5. sfBaseTask->doRun() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/task/sfTask.class.php:97
    0.5669    8355404   6. sfDoctrineGenerateMigrationsDiffTask->execute() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/task/sfBaseTask.class.php:68
    1.1863    9344056   7. sfDoctrineBaseTask->callDoctrineCli() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/task/sfDoctrineGenerateMigrationsDiffTask.class.php:65
    1.2408   10182720   8. Doctrine_Cli->run() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/task/sfDoctrineBaseTask.class.php:64
    1.2408   10182784   9. Doctrine_Cli->_run() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Cli.php:452
    1.2413   10184504  10. Doctrine_Cli->executeTask() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Cli.php:498
    1.2414   10184648  11. Doctrine_Task_GenerateMigrationsDiff->execute() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Cli.php:516
    1.4141   12636532  12. Doctrine_Migration_Diff->generateMigrationClasses() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Task/GenerateMigrationsDiff.php:48
    1.4223   12865712  13. Doctrine_Migration_Builder->generateMigrationsFromDiff() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Migration/Diff.php:113
    1.4223   12865776  14. Doctrine_Migration_Diff->generateChanges() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Migration/Builder.php:147
    3.9516   14566248  15. Doctrine_Migration_Diff->_diff() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Migration/Diff.php:101
    4.3495   36377288  16. Doctrine_Core::loadModels() /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Migration/Diff.php:127
    4.3500   36513672  17. require_once('/tmp/toprfx_doctrine_tmp_dirs/ToPrfxUllProject.php') /var/www/ullright_with_sf13/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Core.php:664

Problem

Database schema changes and system database values need to be implemented twice: In the fixture files and as migrations. That is a time consuming, repetitive and boring task.

Explanation

In the ullright development environment we've got fixture files which serve for the automated tests and as test data for the web frontend.

New developments change the schema, and add system database values which need to be distributed to the customer's installations. For that we use doctrine migrations, which have to be created manually additionally to the updated fixture files.

Solution

None right now.

Migration diff-tool of doctrine 1.1?

Workaround

The hardest task is to create the migration statements for new tables and foreign keys.

Here's how you can save some time:

Develop, adapt the schema and add fixtures
Build the schema / load the fixtures
Create your own new migration class
- Tip: You need to create two separate migration classes if you add a field to a table, and then want to add some data for this new field!
Run php symfony doctrine:generate-migrations-model
- Creates migration classes which represent the current state of the database
- Path: plugins/ullCorePlugin/lib/migrations
- ! Don't forget to implement the constraints (They're in the last generated migration)
  - Currently the contraints migration is broken in doctrine because all "adds" are in one line.
    -> In Eclipse -> find/replace ->
    - Find: \t\t
    - Replace with: \n
    - Tick the Regular expressions checkbox -> "Replace All"
- Delete the auto-created migrations before commiting
Copy the migration statements for new tables and foreign keys from the generated migrations to you migration
Delete the generated migrations.

Updated at 04/22/2020 10:25

https://www.ullright.org/ullWiki/show/migrations-in-ullright

Create

Links

Migrations in ullright

Introduction

Upgrade Workflow

References

Pre Build Model Migration

Doctrine Migration

Create new migration

Semi-automatisation for new tables

Custom Migration

Data Migration

Adding a foreign key column

Running a symfony task

Various initializations

TODO

Tips and tricks

Adding a foreign key column

Add a default value

Running a symfony task

Example for deletion of a model

Using dbh

Update records with added behaviour

Recherche

Problem

Explanation

Solution

Workaround