2

I'm using Silex with Doctrine DBAL. I try to create a table:

$schema = new \Doctrine\DBAL\Schema\Schema();
$table = $schema->createTable('admins');
$table->addColumn('id', 'smallint', array('unsigned' => true, 'autoincrement' => true));
$table->addColumn('username', 'string', array('length' => 10));
$table->addColumn('password', 'string', array('length' => 45));
$table->setPrimaryKey(array('id'));
$table->addUniqueIndex(array('username'));

$queries = $schema->toSql(new \Doctrine\DBAL\Platforms\PostgreSqlPlatform());
// or $queries = $schema->toSql(new \Doctrine\DBAL\Platforms\MysqlPlatform());
foreach ($queries as $query)
{
  echo $query . ";\n";
}

This is the output for the MySQL platform: 

CREATE TABLE admins (
id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
username VARCHAR(10) NOT NULL,
password VARCHAR(45) NOT NULL,
UNIQUE INDEX UNIQ_A2E0150FF85E0677 (username), PRIMARY KEY(id)
) ENGINE = InnoDB;

It's absolutely right ! We can notice the "AUTO_INCREMENT" for the "id" column.

But If I choose the PostgreSQL platform, this is the output:

CREATE TABLE admins (
id SMALLINT NOT NULL,
username VARCHAR(10) NOT NULL,
password VARCHAR(45) NOT NULL, 
PRIMARY KEY(id)
);
CREATE UNIQUE INDEX UNIQ_A2E0150FF85E0677 ON admins (username);

The auto_increment doesn't work on PostgreSQL platform... But in the documentation, "autoincrement" is in the "Portable options" section. What's the problem ?

Thank you

Thomas K
  • 1,067
  • 1
  • 15
  • 35

1 Answers1

2

You need to create a sequence manually or use a serial type since AUTO_INCREMENT like magic flag doesn't exist on PostgreSQL platform. It's documented as "portable" because of DBAL can handle this requirement on all platforms, but via different ways.

Try this:

$table = $schema->createTable('admins');
$schema->createSequence('admins_seq');
$table->addColumn('id', 'smallint', array('unsigned' => true));
$table->addColumn( ... );
// ...

Hope it helps.

Update : Ah, after comments i think i figured out what's happening. @Thomas, how and where did you get that $schema instance? What is the output of the get_class($schema)?

You have to use a Schema Manager instance which can be easily grabbed from $connection instance and you should issue your commands over that instance for maximum portability.

Example:

$sm = $connection->getSchemaManager();
$table = new \Doctrine\DBAL\Schema\Table('admin');
$id = $table->addColumn('id', 'integer');
$id->setAutoincrement(true); 
$table->addColumn('username', 'string');
$sm->createTable($table);

This should work.

edigu
  • 9,878
  • 5
  • 57
  • 80
  • Thank's Foozy but I thought the goal of this kind of library was to hide these differences... The problem is when I create a sequence and I change the platform, for example MySQL, it throws an exception. So, I have to use "if" statements to handle different cases... It's barely better than maintaining a SQL file for each database... – Thomas K Jun 01 '14 at 20:52
  • I never had to add an implicit `->createSequence()` into my schema. Which version of Doctrine DBAL you are using? – common sense Jun 02 '14 at 07:48
  • I think using implict createSequence() depends on __construct options of the $schema instance. This quote from latest DBAL reference's Portability section: `Some vendors use sequences for identity generation, some auto-increment approaches. Both are completely different (pre- and post-insert access) and therefore need special handling.` http://doctrine-dbal.readthedocs.org/en/latest/reference/portability.html – edigu Jun 02 '14 at 08:33
  • I edited my first post to show the complete code. Foozy, your code raises an exception "Argument 1 passed to Doctrine\DBAL\Schema\AbstractSchemaManager::createTable() must be an instance of Doctrine\DBAL\Schema\Table, string given". @StefanoKowalke I use Doctrine DBQL 2.2.* – Thomas K Jun 02 '14 at 16:22
  • Oh, sorry. My bad. I just corrected the last example. It should work now. – edigu Jun 03 '14 at 10:38