Access to column names for given repo in Repo instance

[lbialy]

Hi,

I'm looking into how one can leverage work done by Magnum's reflection to help write future-proof queries in Repo subclasses. One thing that would be massively helpful would be if selectable and insertable columns (eElemNamesSql and ecElemNamesSql in RepoDefaults macros respectively) would be available on either RepoDefaults (one can always summon[RepoDefaults[...]]) or even better, on ImmutableRepo (selectable columns) and Repo (insertable columns). I imagine you could return them in a custom collection type (a wrapper over Vector maybe?) that has an overridden toString() so that it can format them correctly as in SqliteDbType:

    val selectKeys = eElemNamesSql.mkString(", ")
    val ecInsertKeys = ecElemNamesSql.mkString("(", ", ", ")")

You could expose that logic on DbType subtypes and then use it by catching proper DbType in macro just as you do now in RepoDefaults and passing it to the custom collection instance to format columns on interpolation. This would allow user to filter out columns on demand while still providing correct set of columns driven by the structure of actual scala case classes and not via flaky query strings.

What do you think? Would you accept a PR with such functionality?

[lbialy]

Motivating example:

    def insertIgnoreDuplicates(insert: ProjectInsert)(using DbCon): Int =
      sql"""
         INSERT OR IGNORE INTO projects (organization, repository, logo)
         VALUES (${insert.organization}, ${insert.repository}, ${insert.logo})
         """.update.run()

could very well be:

    def insertIgnoreDuplicates(insert: ProjectInsert)(using DbCon): Int =
      sql"""
         INSERT OR IGNORE INTO projects $insertColumns
         VALUES ($insertParameters)
         """.update.run()

under the assumption that in scope of Repo subclass there would be an insertColumns value/method available and also insertParameters (where that would probably be DbCodec[EC].queryRepr). Probably, by the same logic, columns and parameters would be necessary too.

[AugustNagro]

That's a great idea, I will definitely accept a PR.

I also like the idea of adding such methods to ImmutableRepo.

[lbialy]

This could be closed but it also can track the work necessary to actually include TableInfo in Repo/ImmutableRepo scopes. Given that this was the goal of this issue I will leave this open and when I have more time (and the compiler issue is fixed!) I'll dive into this again.

[AugustNagro]

Sounds good :)