SQLite column comment generates invalid alter query

[kesselb]

Bug Report

Q	A
Version	3.3.8

Summary

Nextcloud got a report that column comments on sqlite leads to an sql error like: General error: 1 incomplete input¹²

The code to add the column.

$table->addColumn('foo', Types::INTEGER, [
	'notnull' => false,
	'default' => 0,
	'comment' => 'unix-timestamp',
]);

It works to run all migrations on a clean database.
It breaks when the migration with the column comment is executed alone.

The generated alter table query is:

ALTER TABLE oc_forms_v2_forms ADD COLUMN last_updated INTEGER DEFAULT 0 --unix-timestamp

Comments with -- are not supported (or broken) according to: https://sqlite.org/forum/forumpost/4bb7806a96e863f0

A possible solution could be to drop comments for alter table statments on sqlite.

Index: src/Platforms/SqlitePlatform.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/Platforms/SqlitePlatform.php b/src/Platforms/SqlitePlatform.php
--- a/src/Platforms/SqlitePlatform.php	(revision 3fc4ea3565d54ff1cc944e5c4e01cc9dc2920df2)
+++ b/src/Platforms/SqlitePlatform.php	(date 1677275353199)
@@ -1263,6 +1263,9 @@
                 $definition['length'] ??= 255;
             }
 
+            // SQLite does not support sql schema comments in alter table statements
+            unset($definition['comment']);
+
             $sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' ADD COLUMN '
                 . $this->getColumnDeclarationSQL($definition['name'], $definition);
         }

Migrating from -- to /* */ should also work, but it seems complicated to make dbal work with the sql style comments and c style comments.

Current behaviour

The generated sql query results in an error

How to reproduce

• Use sqlite
• Create a table
• Add a column with a comment
• 💥

Expected behaviour

No sql error ;)

Footnotes

1. https://github.com/nextcloud/server/pull/36803 ↩

2. https://github.com/nextcloud/forms/pull/1479#issuecomment-1439234202 ↩

[derrabus]

You are running an outdated version if DBAL. If this still happens on the current release, do you want to work in a PR to fix it?

[susnux]

I can confirm that this still is an issue with DBAL 3.6 (and also with 4.0 preview).

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 1 error in table users after add column: incomplete input in /tmp/test/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:32
Stack trace:
#0 /tmp/test/vendor/doctrine/dbal/src/Driver/PDO/Connection.php(32): PDO->exec()
#1 /tmp/test/vendor/doctrine/dbal/src/Connection.php(1200): Doctrine\DBAL\Driver\PDO\Connection->exec()
#2 /tmp/test/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php(1617): Doctrine\DBAL\Connection->executeStatement()
#3 /tmp/test/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php(1243): Doctrine\DBAL\Schema\AbstractSchemaManager->_execSql()
#4 /tmp/test/test.php(31): Doctrine\DBAL\Schema\AbstractSchemaManager->alterTable()
#5 {main}

Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[HY000]: General error: 1 error in table users after add column: incomplete input in /tmp/test/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28
Stack trace:
#0 /tmp/test/vendor/doctrine/dbal/src/Driver/PDO/Connection.php(38): Doctrine\DBAL\Driver\PDO\Exception::new()
#1 /tmp/test/vendor/doctrine/dbal/src/Connection.php(1200): Doctrine\DBAL\Driver\PDO\Connection->exec()
#2 /tmp/test/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php(1617): Doctrine\DBAL\Connection->executeStatement()
#3 /tmp/test/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php(1243): Doctrine\DBAL\Schema\AbstractSchemaManager->_execSql()
#4 /tmp/test/test.php(31): Doctrine\DBAL\Schema\AbstractSchemaManager->alterTable()
#5 {main}

Next Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1 error in table users after add column: incomplete input in /tmp/test/vendor/doctrine/dbal/src/Driver/API/SQLite/ExceptionConverter.php:83
Stack trace:
#0 /tmp/test/vendor/doctrine/dbal/src/Connection.php(1931): Doctrine\DBAL\Driver\API\SQLite\ExceptionConverter->convert()
#1 /tmp/test/vendor/doctrine/dbal/src/Connection.php(1874): Doctrine\DBAL\Connection->handleDriverException()
#2 /tmp/test/vendor/doctrine/dbal/src/Connection.php(1202): Doctrine\DBAL\Connection->convertExceptionDuringQuery()
#3 /tmp/test/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php(1617): Doctrine\DBAL\Connection->executeStatement()
#4 /tmp/test/vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php(1243): Doctrine\DBAL\Schema\AbstractSchemaManager->_execSql()
#5 /tmp/test/test.php(31): Doctrine\DBAL\Schema\AbstractSchemaManager->alterTable()
#6 {main}
  thrown in /tmp/test/vendor/doctrine/dbal/src/Driver/API/SQLite/ExceptionConverter.php on line 83