sqlite query with fallback non null value from argument

[vanniktech]

I have the following two tables:

CREATE TABLE channel (
  id TEXT NOT NULL PRIMARY KEY,
  directoryId TEXT,
  backgroundRefreshInterval INTEGER AS Int,
  FOREIGN KEY (directoryId) REFERENCES directory(id)
);

CREATE TABLE directory (
  id TEXT NOT NULL PRIMARY KEY,
  backgroundRefreshInterval INTEGER AS Int
);

I wrote the following query:

backgroundRefreshChannels:
SELECT
  channel.id AS channelId,
  channel.lastBackgroundRefresh AS lastBackgroundRefresh,
  COALESCE(channel.backgroundRefreshInterval, directory.backgroundRefreshInterval), CAST(:backgroundRefreshInterval AS INTEGER) AS backgroundRefreshInterval
  FROM channel
  LEFT JOIN directory
    ON channel.directoryId = directory.id
;

I want to get a backgroundRefreshInterval, either from the channel if present, otherwise from the directory if present and if both are null, I want to have a non-null default value that I pass into the query.

The problem is that sqldelight infers the type to be of Long?, how can I tell sqldelight to make this non null?

[hfhbd]

With pure SQL this is impossible I think.
We need to extend https://github.com/cashapp/sqldelight/blob/master/sqldelight-compiler/src/main/kotlin/app/cash/sqldelight/core/sqldelight.bnf to allow Kotlin "things" in the statements too.

And the syntax has to be unambiguous, this is a problem.

For example using AS kotlin.Int is ambiguous and error-prone: SELECT foo AS bar AS kotlin.Int.
What about nullable? SELECT foo AS bar AS kotlin.Int?
Next use case would be annotations:
UPDATE foo WHERE bar = :bar AS @IntRange(from=1, to=4) kotlin.Int.

[vanniktech]

What if we were to allow:

CAST(:backgroundRefreshInterval NOT NULL)

With #3431 backgroundRefreshInterval should be able to be inferred to INTEGER and then NOT NULL adds the finishing touch.

[hfhbd]

Hm, on the one hand I like the sql like syntax, on the other hand I dislike the sql like syntax because it is (invalid) sql and with sql you still have the limited sql types.
CAST(:foo AS INTEGER NOT NULL)would give you a non null sql integer which would be mapped to kotlin.Int which is okay.
But some dialects eg sqlite does not have a boolean type, so there is no possibility to write a kotlin type here which is mapped to sql (INTEGER) with the given adapter. If we create such overload I would like to have a good solution fixing this (your) use case too.
But I like the idea overwriting the CAST function, it has a similar meaning and it is reserved, so it is impossible to have a conflict:
Because the sql cast function does not support more arguments, I would suggest this:
CAST(sqlExpr, kotlinExpr)
CAST(:foo AS INTEGER, @SomeAnnotation kotlin.Boolean?) with the comma, we don't have any problems with CAST(:foo AS INTEGER AS kotlin.Boolean) or similar, and its additionally optionally kotlin parameter is clear. And yes, it should be possible to write this CAST(:foo, @SomeAnnotation kotlin.Boolean?).

[vanniktech]

CAST(:foo, @SomeAnnotation kotlin.Boolean?) sounds good to me!

[hfhbd]

Bonus: it is easily to remove the kotlin expression for schemacrawler or dump sql schema: https://regex101.com/r/N4wKoq/1

[vanniktech]

Another fun one:

COALESCE(column1, column2) yield Long

Where columns are defined as:

column1 INTEGER AS Int
column2 INTEGER AS Int

so I'd expect Int.

[hfhbd]

I will create another issue... Background: Inferring only uses the sql types, not the actual column definition.