[DBAL-493] Schema\Comparator incorrect diff with sqlite and unsigned integer Created: 12/Apr/13  Updated: 14/Apr/13  Resolved: 14/Apr/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Richard Hinkamp Assignee: Benjamin Eberlei
Resolution: Can't Fix Votes: 0
Labels: None
Environment:

php 5.3.10-1ubuntu3.6
SQLite Library => 3.7.9



 Description   

Using the Schema\Comparator to diff a table in sqlite with a unsigned integer. When creating the table and comparing it with the same definition it gives a diff which is not expected:

Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
 
$schema = new \Doctrine\DBAL\Schema\Schema();
$usersTable = $schema->createTable("test_users");
$usersTable->addColumn("id", "integer", array("unsigned" => true, 'autoincrement' => true));
$usersTable->setPrimaryKey(array("id"));
$usersTable->addColumn("username", "string", array("length" => 32));
$usersTable->addIndex( array( 'username' ) );
$usersTable->addColumn("password", "string", array("length" => 64));

$platform = new \Doctrine\DBAL\Platforms\SqlitePlatform();
$queries = $platform->getCreateTableSQL($usersTable);
foreach($queries as $query) {
    $conn->query($query);
}

$tableDetail = $sm->listTableDetails($usersTable->getName());
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$diff = $comparator->diffTable($tableDetail, $usersTable);
if ($diff) {
    print(implode("\n",$platform->getAlterTableSQL($diff)));
} else {
    print("no diff");
}

Expect result:

 
no diff

Actual result:

 
DROP INDEX IDX_1F5D03E2F85E0677
CREATE TEMPORARY TABLE __temp__test_users AS SELECT id, username, password FROM test_users
DROP TABLE test_users
CREATE TABLE test_users (id INTEGER NOT NULL, username VARCHAR(32) NOT NULL, password VARCHAR(64) NOT NULL, PRIMARY KEY(id))
INSERT INTO test_users (id, username, password) SELECT id, username, password FROM __temp__test_users
DROP TABLE __temp__test_users
CREATE INDEX IDX_1F5D03E2F85E0677 ON test_users (username)

When setting unsigned to false everything works. Shouldn't Doctrine know unsigned is not supported in sqlite?



 Comments   
Comment by Benjamin Eberlei [ 14/Apr/13 ]

SQLite doesn't have the notion of unsigned. You have to remote the "unsigned" => true flag to stop confusing Sqlite and the Comparision and it will work.

Generated at Thu Nov 27 05:19:45 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.