#SqlDelight #SqlLite3-35 #Type Hello people. I ha...

# squarelibraries

[Davide Giuseppe Farella]

#SqlDelight #SqlLite3-35 #Type

Hello people. I have the following View:

CREATE VIEW IF NOT EXISTS screenplay AS
    SELECT
        movie.tmdbId AS movieTmdbId,
        NULL AS tvShowTmdbId,
        ...
    FROM movie
    UNION ALL
    SELECT
        NULL AS movieTmdbId,
        tvShow.tmdbId AS tvShowTmdbId,
        ...
    FROM tvShow;

where movieTmdbId and tvShowTmdbId are of types

sealed interface ScreenplayTmdbId 
value class MovieTmdbId : ScreenplayTmdbId
value class TvShowTmdbId: ScreenplayTmdbId

I would like to query a single row by id.
It would be awesome if SqlDelight could infer the super-type ScreenplayTmdbId , but I see that’s not happening, as the following resolves tmdbId as String

WHERE IFNULL(movieTmdbId, tvShowTmdbId) == :tmdbId;

I tried then to separate into two separate queries, but, somehow, the first of the following queries compiles, while the second one doesn’t

findByMovieId:
    SELECT
        movieTmdbId,
        tvShowTmdbId,
        ...
    FROM screenplay
    WHERE movieTmdbId == :tmdbId;

findByTvShowId:
    SELECT
        movieTmdbId,
        tvShowTmdbId,
        ...
    FROM screenplay
    WHERE tvShowTmdbId == :tmdbId;

Caused by: java.lang.IllegalArgumentException: Cannot bind unknown types or null

I’m pretty sure I’m doing something wrong

[Davide Giuseppe Farella]

I wouldn’t mind using a String, the issue is that MovieTmdbId(123) is transformed to "movie:123" from the adapter, since a Movie and a TvShow could have the same id and I don’t really wanna cook some spaghetti in my data layer

[hfhbd]

First, sqldelight has no idea of Kotlin. Yeah, you could use import and as Value, but that's all. There is no source lockup, and I don't think so in the near future.
I still don't understand your String problem.

[Davide Giuseppe Farella]

Thank you for your answer.

The problem with the string is that I could not call findById("123") as it could match two different entries, like a movie row with id "movie:123" and a tv show one with id "tv:123".
As now I kept two different queries: WHERE tvShowId LIKE 'tv_' || :tmdbId || '' and same for the movie counterpart, but still I'm duplicating logic in the adapter and in sqlite statement.

[hfhbd]

Honestly, I don't get it and it sounds kind of strange, why do you need to use 'like tv' or have different toString implementations? 😅 In the end, sqldelight is a db abstraction, and you should handle logic in your logic layer, not at the db layer.

[Davide Giuseppe Farella]

I understand your thoughts. The fact is that on Tmdb, movies and tv shows are different entities, and a movie can share the same id of a tv show. In order to query them together, I created a View and resolved the conflicts by prefixing the ids by "movie:" or "tv:".

Now, since the two types of id are strongly typed in my domain, I don't need the prefix ( see MovieId(123) != TvShowId(123) ). Because of this I decided to map 123 -> "movie:123" in my db adapters: this might an arguable choice, but it seemed the most sensed one.

TL;DR, the problem with the String is that the adapter would be bypassed.
It would be great if the mentioned statement (last one in the main post) would compile as expected, allowing me to have 2 queries generated with the typed parameter, over a String

[hfhbd]

I would still not prefixing the ids at db level, but simple return both and create a getter: val id: ScreenplayTmdbId get() = movieTmdbId ?: tvShowTmdbId!!

Okay, could you create a reproducer and file a bug then?

[Davide Giuseppe Farella]

I would still not prefixing the ids at db level, but simple return both and create a getter: val id: ScreenplayTmdbId get() = movieTmdbId ?: tvShowTmdbId!!

Yes, that’s indeed what I did (see View declaration in the first post). The problem is querying them by id.

Okay, could you create a reproducer and file a bug then?

Sure thing!