AS: GenerateSqlDelightInterface works, verifySqlDelightMigration fails

[hfhbd]

SQLDelight Version

SNAPSHOT > 20200715

Operating System

macOS

Gradle Version

7.4.2

Kotlin Version

1.7.10

Dialect

sqldelight-db2-dialect: https://github.com/hfhbd/sqldelight-db2-dialect

AGP Version

No response

Describe the Bug

I overwrite the generated_clause:

generated_clause ::=
(GENERATED (ALWAYS | BY DEFAULT) ( as_identity_clause | as_row_change_timestamp_clause ) |
(GENERATED [ALWAYS] (AS 'TRANSACTION START ID' | as_row_change_timestamp_clause | AS '(' <<expr '-1'>> ')'))
) {
  extends = "com.alecstrong.sql.psi.core.psi.impl.SqlGeneratedClauseImpl"
  implements = "com.alecstrong.sql.psi.core.psi.SqlGeneratedClause"
  override = true
}
as_identity_clause ::= AS 'IDENTITY' [ '(' as_identity_clause_options (',' as_identity_clause_options)+ ')' ]

The task generateSqlDelightInterface successfully compiles the dialect. The unit tests pass (using a real and slow db2 instance), but the automatically task verifySqlDelightMigration fails with a compiler error.

Link to PR/CI: hfhbd/sqldelight-db2-dialect#4

SQL:

CREATE TABLE foo(
  id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  name VARCHAR(255) NOT NULL
);

Stacktrace

org.gradle.api.tasks.TaskExecutionException: Execution failed for task ':testing:verifyMainTestingDBMigration'.

Caused by: java.lang.IllegalStateException: Error compiling Error compiling CREATE TABLE foo(
  id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  name VARCHAR(255) NOT NULL
)
	at app.cash.sqlite.migrations.CatalogDatabase$Companion.init(CatalogDatabase.kt:54)
	at app.cash.sqlite.migrations.CatalogDatabase$Companion.fromFile(CatalogDatabase.kt:36)
	at app.cash.sqlite.migrations.CatalogDatabase$Companion.withInitStatements(CatalogDatabase.kt:32)
	at app.cash.sqldelight.gradle.VerifyMigrationTask$VerifyMigrationAction.createCurrentDb(VerifyMigrationTask.kt:145)
	at app.cash.sqldelight.gradle.VerifyMigrationTask$VerifyMigrationAction.execute(VerifyMigrationTask.kt:118)
	at org.gradle.workers.internal.DefaultWorkerServer.execute(DefaultWorkerServer.java:63)
	at ....
	... 2 more
Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "AS": syntax error)
	at org.sqlite.core.DB.newSQLException(DB.java:1030)
	at org.sqlite.core.DB.newSQLException(DB.java:1042)
	at org.sqlite.core.DB.throwex(DB.java:1007)
	at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
	at org.sqlite.core.NativeDB.prepare(NativeDB.java:137)
	at org.sqlite.core.DB.prepare(DB.java:257)
	at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
	at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:25)
	at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:35)
	at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:241)
	at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:205)
	at app.cash.sqlite.migrations.CatalogDatabase$Companion.init(CatalogDatabase.kt:52)
	... 36 more

Gradle Build Script

plugins {
    kotlin("jvm")
    id("app.cash.sqldelight")
}

dependencies {
    implementation("app.cash.sqldelight:jdbc-driver:2.0.0-alpha03")

    testImplementation(kotlin("test-junit"))
    testImplementation("org.testcontainers:db2:1.17.3")
    testImplementation("com.ibm.db2:jcc:11.5.7.0")
    testRuntimeOnly("ch.qos.logback:logback-classic:1.2.11")
}

sqldelight {
    database("TestingDB") {
        dialect(project(":sqldelight-db2-dialect"))
        packageName = "app.softwork.sqldelight.db2dialect"
    }
}

[AlecKazakova]

that syntax must not work on the version of xerial sqlite bundled

[hfhbd]

What do you mean? The verifySqlDelightMigration tasks uses a sqlite db, which obviously does not work with DB2? If so, how can you disable the task in the dialect api?

[AlecKazakova]

I see, do you intentionally not have deriveSchemaFromMigrations = true? Regardless, we should probably only add that task for sqlite since it uses a sqlite db to do the verification (you can see that in the stack trace)

[hfhbd]

Ahh, I shorted the stack trace from all useless Gradle worker calls to have a more readable version, but didn't read it again...
Of course there is the sqlite db used with catalog database.
Okay, I think the fix is easy: Just check verifyMigration before calling createDb, which tries to compile the sql with sqlite.

[hfhbd]

I see, do you intentionally not have deriveSchemaFromMigrations = true?

Yeah, I didn't need it for this test case 🤔