Oracle Migrations Bundle Laravel Package

Getting Started

Minimal Steps

1. Installation Run:

   composer require bueue/oracle-migrations-bundle
   

   Ensure your project uses Symfony 2.x (last release: 2017) and Doctrine Migrations Bundle.

2. Enable the Bundle Register in app/AppKernel.php:

   new Bueue\OracleMigrationsBundle\BueueOracleMigrationsBundle(),
   

3. Configure Oracle DBAL Add driver_class to your config.yml under doctrine.dbal.connections.default:

   driver_class: Bueue\OracleMigrationsBundle\Driver\OracleDriver
   

4. First Migration Generate and run a migration as usual:

   php bin/console doctrine:migrations:diff
   php bin/console doctrine:migrations:migrate
   

First Use Case

• Performance Optimization: Use this bundle when migrating large Oracle schemas where default Doctrine migrations are slow. The bundle replaces the default Doctrine\DBAL\Driver\OCI8\Connection with a custom driver optimized for Oracle migrations.

Implementation Patterns

Workflows

1. Migration Generation

   • Use doctrine:migrations:diff to generate migrations with Oracle-specific optimizations.
   • The bundle ensures generated SQL is Oracle-compatible and avoids unsupported syntax.

2. Batch Processing

   • For large tables, split migrations into smaller batches using custom migration logic:

     // src/DocRoot/Migrations/VersionYYYYMMDDHMS.php
     public function up(Schema $schema)
     {
         $this->addSql('ALTER TABLE large_table MOVE PARTITION p1');
         // Use Oracle-specific syntax for performance
     }
     

3. Transaction Management

   • Oracle has stricter transaction limits. Use explicit commits in migrations:

     $connection->beginTransaction();
     try {
         $connection->executeStatement('CREATE TABLE ...');
         $connection->commit();
     } catch (\Exception $e) {
         $connection->rollBack();
         throw $e;
     }
     

Integration Tips

• Doctrine Migrations Bundle: Ensure you have doctrine/doctrine-migrations-bundle installed (composer require doctrine/doctrine-migrations-bundle).
• Oracle-Specific Schema Dumps: Use doctrine:schema:update --dump-sql to preview SQL before migration.
• Custom Migration Classes: Extend Bueue\OracleMigrationsBundle\Migration\OracleAwareMigration for Oracle-specific logic.

Gotchas and Tips

Pitfalls

1. Deprecated Symfony Version

   • The bundle targets Symfony 2.x. If using Symfony 3/4/5, expect compatibility issues. Consider forking or rewriting for modern Symfony.

2. Oracle-Specific SQL Limitations

   • Avoid unsupported syntax (e.g., LIMIT/OFFSET in INSERT). Use Oracle equivalents like ROWNUM:

     $this->addSql('INSERT INTO table SELECT * FROM source WHERE ROWNUM <= 1000');
     

3. Connection Handling

   • The bundle replaces the default oci8 driver. Ensure no other part of the app manually instantiates OCI8Connection without the custom driver.

4. Migration Locking

   • Oracle migrations may fail due to locks. Use SET TRANSACTION READ WRITE or NOWAIT clauses:

     $this->addSql('ALTER TABLE table LOCK NOWAIT');
     

Debugging

• Enable SQL Logging Add to config.yml:

  doctrine:
      dbal:
          logging: true
          logging_format: '%%SQL%% %%TIME%%'
  

  Check logs for Oracle-specific errors (e.g., ORA-01756 for unsupported syntax).

• Check Driver Registration Verify the custom driver is loaded:

  php bin/console debug:container bueue_oracle_migrations.driver
  

Extension Points

1. Custom Driver Logic Extend Bueue\OracleMigrationsBundle\Driver\OracleDriver to add Oracle-specific features (e.g., parallel DDL execution).

2. Migration Pre/Post Hooks Override Bueue\OracleMigrationsBundle\Migration\OracleAwareMigration to add pre-migration checks:

   protected function preUp(Schema $schema)
   {
       if (!$this->connection->getDatabasePlatform()->supports('ORACLE_FEATURE')) {
           throw new \RuntimeException('Unsupported Oracle feature');
       }
   }
   

3. Batch Size Configuration Configure batch sizes in migrations for large operations:

   # config.yml
   bueue_oracle_migrations:
       batch_size: 5000  # Default batch size for INSERT/UPDATE