SQLITE Autoincrement

[s0me0ther]

doctrine: 2.5.4
sqlite: 3

The docs are talking about, that this annotation should result in an SQLITE Autoincrement ID field:

    /**
     * @var int
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */

The id is created, but the autoincrement flag is not checked in the sqlitedb..

if i insert an new entity on a fresh db it will have id 1
if i delte this entity and create a new entity afterwards, the new entity will also have the id 1

do i miss somethis or is this a bug in doctrine?

[deeky666]

can you please provide the DDL that was used to create the table?

[s0me0ther]

This Query will be executed:
CREATE TABLE jobs (id INTEGER NOT NULL, created DATETIME NOT NULL, keyword VARCHAR(255) NOT NULL, weight INTEGER DEFAULT 1000 NOT NULL, status VARCHAR(255) NOT NULL, PRIMARY KEY(id));

From this Entity:

namespace Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 *
 * @ORM\Entity
 * @ORM\Table(name="jobs")
 * @ORM\HasLifecycleCallbacks
 */
class Job
{
    /**
     * @var int
     * @ORM\Id
     * @ORM\Column(type="integer", options={"autoincrement":true})
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var \DateTime
     * @ORM\Column(type="datetime", nullable=false, unique=false)
     */
    protected $created;

    /**
     * @var string
     * @ORM\Column(type="string", nullable=false, unique=false)
     */
    protected $keyword;

    /**
     * @var int
     * @ORM\Column(type="integer", nullable=false, unique=false, options={"default":1000})
     */
    protected $weight;

    /**
     * @var string
     * @ORM\Column(type="string", nullable=false, unique=false)
     */
    protected $status;

    /**
     * @var Request[]
     * @ORM\OneToMany(targetEntity="Entity\Request", mappedBy="job", cascade={"all"}, orphanRemoval=true)
     */
    protected $requests;
}

The options={"autoincrement":true} flag does not help here to get an autoincrement flag into the sqlite db.

Do you need any more informations?

[jschirrmacher]

The problem may lie in function _getCommonIntegerTypeDeclarationSQL() which simply ignores the autoincrement definition:

// sqlite autoincrement is implicit for integer PKs, but not when the field is unsigned
if ( ! empty($columnDef['autoincrement'])) {
    return '';
}

It should, IMHO return AUTOINCREMENT, the corresponding keyword for Sqlite, like the code in MySqlPlatform.php:

$autoinc = '';
if ( ! empty($columnDef['autoincrement'])) {
  $autoinc = ' AUTOINCREMENT';
}
$unsigned = (isset($columnDef['unsigned']) && $columnDef['unsigned']) ? ' UNSIGNED' : '';
return $unsigned . $autoinc;

[mattmoehr]

This also happens in DBAL. So it's not a Doctrine ORM problem, just fyi.

Create table code:

$schema = new \Doctrine\DBAL\Schema\Schema();

$table = $schema->createTable('grants');

$table->addColumn('grant_id',           'integer', array('autoincrement' => true));  
$table->addColumn('grant_body',         'string',  array('notnull' => false));
$table->addColumn('grant_mechanism',    'string',  array('notnull' => false));
$table->addColumn('grant_number',       'string',  array('notnull' => false));

$table->setPrimaryKey(array('grant_id'));

$table->addUniqueIndex(array('grant_body', 'grant_mechanism', 'grant_number'));

$sql = $schema->toSql($platform);

Resulting SQL:

CREATE TABLE grants (
grant_id INTEGER NOT NULL, 
grant_body VARCHAR(255) DEFAULT NULL, 
grant_mechanism VARCHAR(255) DEFAULT NULL, 
grant_number VARCHAR(255) DEFAULT NULL, 
PRIMARY KEY(grant_id)
)

[mattmoehr]

In case anyone else comes across this bug, this is the workaround I used:

$table->addColumn('grant_id',           'integer', array('columnDefinition' => 'INTEGER PRIMARY KEY AUTOINCREMENT'));

And note that you MUST put integer, primary key, and autoincrement in that particular order or else SQLite will throw an uninformative error. (Error message something like "error near autoincrement.") And also note that you are now adding the PRIMARY KEY directly on the field definition so you have to remove the $table->setPrimaryKey() function.

[martinsotirov]

I'm experiencing this issue, too, with sqlite 3.

[runawaycoin]

Changing the return statement to this works

• Doctrine/DBAL/Platforms/SqlitePlatform.php:

protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)
    {
        // sqlite autoincrement is implicit for integer PKs, but not when the field is unsigned
        if ( !empty($columnDef['autoincrement']) && !empty($columnDef['primary']) ) {
            return ' PRIMARY KEY AUTOINCREMENT ';
        }
...

However this breaks it as it is trying to define the primary key again at the end of the create table statement.
Annoyingly you need to state PRIMARY KEY AUTOINCREMENT when creating the autoincrement field
https://sqlite.org/autoinc.html

 protected function _getCreateTableSQL($name, array $columns, array $options = array())
    {
     ...
        if (isset($options['primary']) && ! empty($options['primary']) ) {
            $keyColumns = array_unique(array_values($options['primary']));
            $queryFields.= ', PRIMARY KEY('.implode(', ', $keyColumns).')';
        }

[pierre-H]

Same problem when using a sqlite with phpunit and symfony

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.