[Feature Request] Export SQL schema

Question

I know this is probably out-of-scope and not too easy to accomodate various SQL dialects, but it would be really great to have the possibility to export an SQL schema directly. Maybe only on Quick Model?!

You could start with MySQL, that should be sufficient for most projects already.

Comments

This is topic which we already discuss with my colleagues several times and for now we don't have plans for it.

In most cases it's better to generate XML/YML/PHP schema and let ORM framework generate SQL query. Each ORM uses own way how to convert model to SQL (how to represent many-to-many, inheritance etc.)

In the future we want to focus on another ORMs (like Java Hibernate or JPA you mentioned), support migrations and another ORM-like features instead of SQL like-features.

On the other side it wouldn't be hard to create XSLT template which converts XML model to SQL queries, maybe maintained on the community base. In this case we will be happy to help with initial steps.

---

I'm thinking ORM Designer could be a modelling tool for pure SQL / non-ORM-projects as well. Why would people be forced to switch to MySQL workbench or others when they are already familiar with ORMD?! On the other hand, if it's too much special features overhead (like "I can do X in MySQL WB, why doesn't ORMD2 support that??") maybe one should really use a DB Designer instead of an ORM Designer.

---

It's possible that we add SQL features sometime in the future, but right now we have a limited capacity and we want to focus on ORM-like features.

All of this also depends on how ORMD2 will be accepted and of course how well we will be sold. Based on this we will be able to hire more programmers and maintain wider range of functions ;-)

---

OK, sure. It's a "nice to have" for a future version.

Answer 1

I agree with Ludek to let the ORM generate the database, that's what it's made for and does best. But you could try to get it working by adding an entry under 'External Tools' for the Doctrine command line tool (php doctrine orm:schema-tool:create). This way you can generate the database (or SQL) using just a button from ORM Designer. I haven't tried it yet but I'll keep you posted on this if I get around to it.

EDIT :

After some playing around, I got it to work (sort of). This is what you should do:

1. Install Doctrine2 (it's not even necessary to set up a complete project)

2. Create an entry in 'External Tools':

   • Title:
     Export SQL
   • Command:
     cmd /C php "C:/WAMP/www/doctrine2-master/bin/doctrine.php"
   • Arguments:
     orm:schema-tool:create --dump-sql 1> "export\SQL\schema-create.sql" 2>> "export\SQL\error.log"
   • Initial Directory:
     %PROJECT_DIRECTORY%

3. Create a Doctrine config file (cli-config.php) in your project's main folder:

   <?php
   use Doctrine\Common\ClassLoader;
   use Doctrine\Common\Annotations\AnnotationRegistry;
   use Doctrine\Common\Annotations\AnnotationReader;
   use Doctrine\ORM\Configuration;
   use Doctrine\ORM\EntityManager;
   use Doctrine\ORM\Mapping\Driver\AnnotationDriver;
   use Doctrine\ORM\Tools\Console\Helper\EntityManagerHelper;
   use Symfony\Component\Console\Helper\HelperSet;
   
   $includePath = realpath(__DIR__.'/export/Doctrine/PHP');
   
   $classLoader = new ClassLoader('Application\Model\Entity', $includePath);
   $classLoader->register();
   
   $entityPaths = array(
       $includePath.'/Application/Model/Entity'
   );
   
   AnnotationRegistry::registerFile('C:\WAMP\www\doctrine2-master\lib\Doctrine\ORM\Mapping\Driver\DoctrineAnnotations.php');
   $reader = new AnnotationReader();
   $driverImpl = new AnnotationDriver($reader, $entityPaths);
   
   $config = new Configuration();
   $config->setProxyDir(__DIR__ . '/Application/Model/Proxies');
   $config->setProxyNamespace('Proxies');
   $config->setMetadataDriverImpl($driverImpl);
   
   $conn = array(
       'driver'   => 'pdo_mysql',
       //'host'     => 'localhost',
       //'dbname'   => '***',
       //'user'     => '***',
       //'password' => '***',
   );
   
   $helperSet = new HelperSet(array(
       'em' => new EntityManagerHelper(EntityManager::create($conn, $config))
   ));
   

Comments

Good idea. It would need a project setup, where Doctrine is still used but that could be a workaround.

---

Since bug #536 is fixed, the above example should work. I successfully created two items under External Tools: Export SQL and Update database