Mysql Workbench Schema Exporter Laravel Package

Getting Started

Minimal Steps to Begin

1. Install the Package

   composer require --dev mysql-workbench-schema-exporter/mysql-workbench-schema-exporter
   

   For a specific exporter (e.g., Doctrine 2):

   composer require --dev mysql-workbench-schema-exporter/doctrine2-exporter
   

2. Export via CLI Run the CLI tool directly:

   vendor/bin/mysql-workbench-schema-export --export=doctrine2-annotation path/to/schema.mwb ./output_dir
   

   Or use a config file (export.json) for reusable settings:

   vendor/bin/mysql-workbench-schema-export --config=export.json path/to/schema.mwb
   

3. First Use Case Convert a .mwb file (MySQL Workbench model) to Doctrine 2 YAML/Annotations for Laravel:

   vendor/bin/mysql-workbench-schema-export --export=doctrine2-annotation --saveconfig schema.mwb
   

   This generates entity classes with annotations (e.g., @ORM\Entity) in ./temp/.

Implementation Patterns

Workflows in Laravel Projects

1. Schema-to-Entity Generation

   • Use the exporter to generate Doctrine 2 annotations or YAML schemas, then integrate them into Laravel via:

     use Doctrine\ORM\Mapping\Driver\AnnotationDriver;
     $driver = new AnnotationDriver(new \Doctrine\Common\Annotations\AnnotationReader(), [__DIR__.'/generated_entities']);
     $config->setMetadataDriverImpl($driver);
     

   • Store generated files in app/Entities or a dedicated database/doctrine folder.

2. CI/CD Integration

   • Add a script to composer.json to auto-generate entities on post-install:

     "scripts": {
       "post-install-cmd": [
         "php vendor/bin/mysql-workbench-schema-export --config=export.json resources/schema.mwb database/doctrine"
       ]
     }
     

   • Use Git to track .mwb files and ignore generated files (add to .gitignore):

     /database/doctrine/*
     

3. Dynamic Configuration

   • Load exporter settings from Laravel config (e.g., config/database.php):

     $params = config('mysql_workbench.exporter');
     $exporter = new \MwbExporter\Doctrine2\Exporter($params);
     $exporter->export($mwbFile, $outputDir);
     

4. Hybrid Workflows

   • Combine with Laravel Migrations:
     • Use the exporter to generate initial entities from .mwb.
     • Manually tweak entities for Laravel-specific logic (e.g., accessors, custom validation).
     • Sync migrations via php artisan make:migration or tools like DoctrineMigrations.

5. Testing

   • Export test schemas for unit tests:

     vendor/bin/mysql-workbench-schema-export --export=doctrine2-annotation --dir=tests/Entities schema_test.mwb
     

   • Use PHPUnit to validate generated entities against test data.

Integration Tips

• Laravel Service Provider Register the exporter as a service in AppServiceProvider:

  public function register()
  {
      $this->app->singleton('mwb.exporter', function ($app) {
          return new \MwbExporter\Doctrine2\Exporter(config('mysql_workbench.exporter'));
      });
  }
  

  Call it via:

  $exporter = app('mwb.exporter');
  $exporter->export(storage_path('app/schema.mwb'), base_path('database/doctrine'));
  

• Artisan Command Create a custom command for on-demand exports:

  php artisan make:command ExportMwbSchema
  

  // app/Console/Commands/ExportMwbSchema.php
  public function handle()
  {
      $exporter = new \MwbExporter\Doctrine2\Exporter($this->getExporterConfig());
      $exporter->export($this->argument('file'), $this->argument('destination'));
  }
  

  Run with:

  php artisan export:mwb-schema schema.mwb database/doctrine
  

• Event Listeners Trigger exports after .mwb file changes (e.g., via filesystem events):

  // app/Providers/EventServiceProvider.php
  protected $listen = [
      'filesystem.updated' => ['App\Listeners\ExportMwbSchema'],
  ];
  

Gotchas and Tips

Pitfalls

1. Namespace Collisions

   • Doctrine 2 exporter requires explicit entityNamespace and bundleNamespace in config.
   • Fix: Set entityNamespace: "App\\Entities" in export.json to avoid Class 'User' not found errors.

2. Many-to-Many Relations

   • Disabled by default (enhanceManyToManyDetection: false). Enable with:

     "params": {
       "enhanceManyToManyDetection": true
     }
     

   • Tip: Verify generated entities for correct join table names (e.g., user_role vs. role_user).

3. Reserved Keywords

   • MySQL keywords (e.g., order, group) in table/column names may break generated code.
   • Fix: Use asIsUserDatatypePrefix or manually rename in MySQL Workbench.

4. File Overwrites

   • backupExistingFile: true creates backups (e.g., User.php.bak), but may clutter directories.
   • Tip: Set backupExistingFile: false in CI/CD or use --no-backup (if supported).

5. PHP Version Mismatches

   • The package requires PHP 5.4+, but some exporters (e.g., Doctrine 2) may need PHP 7.2+ for full compatibility.
   • Fix: Use a Docker container with the required PHP version for exports.

6. Zend Framework 1 Exporter

   • The zend1-exporter is outdated and may not work with modern Laravel. Avoid unless maintaining legacy code.

7. Sencha Exporters

   • ExtJS3/ExtJS4 exporters generate frontend models. Useful for Laravel + Vue/React projects but requires manual frontend integration.

Debugging

1. Enable Logging Add to export.json:

   "logToConsole": true,
   "logFile": "export.log"
   

   Check for errors like:

   [ERROR] Table 'order' could not be exported: Reserved keyword.
   

2. Validate .mwb Files

   • Corrupted .mwb files may cause silent failures. Test with the Sakila sample database.
   • Tip: Use --list-exporter to verify available exporters.

3. Generated Code Issues

   • Doctrine 2: Run php artisan doctrine:schema:validate to check for syntax errors.
   • Propel: Validate XML/YAML schemas with Propel’s CLI tools.

4. Permission Errors

   • Ensure the web server user (e.g., www-data) has write access to the output directory:

     chmod -R 775 database/doctrine
     

Tips for Daily Use

1. Template Configs Create reusable export.json templates for different environments (dev/staging/prod):

   // export.dev.json
   {
     "export": "doctrine2-annotation",
     "dir": "database/doctrine_dev",
     "params": {
       "indentation": 4,
       "useTabs": false,
       "skipPluralNameChecking": true
     }
   }
   

2. Partial Exports Use {MwbExporter:category} comments to export only specific tables:

   -- In MySQL Workbench: Right-click table > Properties > Comment
   {MwbExporter:category}admin{/MwbExporter:category}
   

   Then filter in config:

   "exportOnlyTableCategorized": "admin"
   

3. Custom Filename Patterns Override default filenames (e.g., %entity%_%table%.php) for clarity:

   "filename": "%entity%Entity.php"
   

4. Laravel Eloquent Compatibility Post-process generated entities to add Laravel-specific traits:

   // After export, replace:
   // @ORM\Entity
   // With:
   // @ORM\Entity(repositoryClass="App\Repositories\UserRepository")
   

5. Git Submodules Store .mwb files in a submodule to share across projects:

   git submodule add https://github.com/your-team/db-schema.git resources/schema
   

6