SQL clause should persist adapted type

[veyndan]

See #2056 (comment).

Currently, if we union two tables with each column having the same java type, the generated query interface will use the underlying java type, as it is possible that each column has a different ColumnAdapter, so the query implementation wouldn't be able to know which ColumnAdapter to use when creating the adapted type when constructing the query interface. Ideally, what we would want is the union to produce the adapted type, iff the ColumnAdapter for the column in each table are identical.

Example

Status Quo

People.sq

CREATE TABLE children(
  birthday TEXT AS java.time.LocalDate
);

CREATE TABLE adults(
  birthday TEXT AS java.time.LocalDate
);

birthdays:
SELECT birthday
FROM children
UNION
SELECT birthday
FROM adults;

Birthdays.kt

data class Birthdays(
  val birthday: String?
) {
  …
}

Database Initialization

test_database(
    driver,
    Adults.Adapter(birthdayAdapter = LocalDateColumnAdapter()),
    Children.Adapter(birthdayAdapter = LocalDateColumnAdapter())
)

Proposal

People.sq

typealias Date = java.time.LocalDate -- Or some other syntax

CREATE TABLE children(
  birthday TEXT AS Date
);

CREATE TABLE adults(
  birthday TEXT AS Date
);

birthdays:
SELECT birthday
FROM children
UNION
SELECT birthday
FROM adults;

Birthdays.kt

data class Birthdays(
  val birthday: java.time.LocalDate?
) {
  …
}

Database Initialization

test_database(
    driver,
    dateAdapter = LocalDateColumnAdapter()
)

[veyndan]

@AlecStrong I'm thinking about working on this, but as it's a non-trivial change to the SQLDelight grammar, I'd be interested to hear your thoughts on this feature before I go ahead with implementing it 😄

[Calvin-redrick]

Its really nice. On Nov 11, 2020 2:33 AM, "Veyndan Stuart" <notifications@github.com> wrote: @AlecStrong <https://github.com/AlecStrong> I'm thinking about working on this, but as it's a non-trivial change to the SQLDelight grammar, I'd be interested to hear your thoughts on this feature before I go ahead with implementing it 😄 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#2067 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQMHD34RAD6C4PMFQERQ4XLSPHEVZANCNFSM4S5ZE3ZA> .

[AlecKazakova]

we could also make this the default behavior for columns with the same kotlin type and throw a runtime exception if the two adapters are different, something like

fun selectBirthdays(): Query<T> {
  check(adapter1 == adapter2)
  ...
}

I would be surprised if that behavior would be undesirable. thoughts?

[veyndan]

I really like your idea, as it keeps the SQLDelight grammar simple, and the user doesn't have to be aware of typealiases to receive the benefits of stronger typed generated code. Also, and I think this is probably the biggest benefit going in this direction, is that it'd remove a class of bugs. For example, carrying on from the original issue comment, if the database initialization was something like this:

test_database(
    driver,
    Adults.Adapter(birthdayAdapter = LocalDateColumnAdapter(format = "dd/MM/yyyy")),
    Children.Adapter(birthdayAdapter = LocalDateColumnAdapter(format = "dd MM yyyy"))
)

where a JOIN on the original SQL would be technically fine, but it'd be undesirable as the format of each column is different.

The only benefits that I currently see of typealiases over a check would be that typealiases are compile-time safe, and refactoring the Kotlin type would be a lot easier with typealiases, as you'd just need to change the typealias in the .sq file (ignoring .sqm files) and change the associated adapter.

So overall I think I'm siding with your proposal. Are you good with going in that direction?

[AlecKazakova]

yea sounds good. agreed compile time safety is good, i usually err on the side of not making our own sql dialect

[veyndan]

@AlecStrong There's a potential regression with the "adapter equality" approach, where (for example) the SELECT statement in a UNION does the same transformation that the column adapter does. This would be logically valid SQL, but would throw because the adapters are of different types.

I made an example to illustrate what I mean.

[AlecKazakova]

even if you just do

SELECT age * 12
FROM person;

that should return an Int and not an Age. So then the union would be a union of an (Int->Age) and an Int which with current rules would be exposed as an Int

[veyndan]

Ah gotcha, thanks for the clarification! 😄