CODEWITHSUNDEEP
phpdoctrine-orm
Andikac
Andikac

Reputation: 434

How to generate SQL for single Doctrine table?

I have this code:

$schema = $connection->getSchemaManager()->createSchema();

        if(!$schema->hasTable($name)) {

            $table  = $schema->createTable( $name );

            $table->addColumn('id','integer',['unsigned' => true]);
            $table->addColumn('string','string',['length'=> 64]);
            $table->addColumn('floats','float');
            $table->addColumn('integers','integer');
            $table->addColumn('date_time','datetime');

            ($schema->toSql( $connection->getDatabasePlatform() ));
            $connection->query( $schema->toSql( $connection->getDatabasePlatform() ) )[0] );
);

I have successfully create table with query:

array (size=1)
  0 => string 'CREATE TABLE level1 (id INT UNSIGNED NOT NULL...

But when executed for 2nd time, the last query also created, so I just get the last query from the array:

array (size=2)
  0 => string 'CREATE TABLE level1 (id INT UNSIGNED NOT NULL...
  1 => string 'CREATE TABLE level2 (id INT UNSIGNED NOT NULL...

but when bunch of table exist, i got

0 => string 'CREATE TABLE level1 (id INT UNSIGNED NOT NULL...
  1 => string 'CREATE TABLE position (id INT AUTO_INCREMENT NOT NULL...
  2 => string 'CREATE TABLE user (id INT AUTO_INCREMENT NOT NULL...
  3 => string 'CREATE TABLE level2 (id INT UNSIGNED NOT NULL, string VARCHAR(64) NOT NULL, floats DOUBLE PRECISION NOT NULL...
  4 => string 'ALTER TABLE user ADD CONSTRAINT FK_8D93D649A76ED395 FOREIGN KEY (user_id) REFERENCES position (id)

Its hard to point to the right query.

Is there any was to produce query for single for creating table?

The table is required to be created on demand and via script, not cli, and I don't have @Entity Class.

Upvotes: 1

Views: 712

Answers (1)

Ryan
Ryan

Reputation: 5066

The SchemaManager has a number of public methods, that will give you the SQL for the different components of the full schema SQL. By default the getCreateTableSQL() method will create the SQL for the table and its indexes, but that behaviour can be overridden.

    /** @var Doctrine\DBAL\Connection $connection */
    $connection = ...;
    $name = ...;
    $schemaManager = $connection->getSchemaManager();
    $schema = $schemaManager->createSchema();

    if(!$schema->hasTable($name)) {

        $table = $schema->createTable($name);

        $table->addColumn('id', 'integer', ['unsigned' => true]);
        $table->addColumn('string', 'string', ['length' => 64]);
        $table->addColumn('floats', 'float');
        $table->addColumn('integers', 'integer');
        $table->addColumn('date_time', 'datetime');
        $sql = $schemaManager->getDatabasePlatform()->getCreateTableSQL($table);
        $connection->query($sql);
    }

Upvotes: 2

Related Questions