How to use ForeignKey API to construct a query to test cardinality of FK relationships?

[David Bullock]

This is partly about how the ForeignKey API works, and partly about using the query-building API (from Kotlin).

My use-case is that I want to take a 3rd party database, and discover information about it such as the actual cardinality of entity-relationships.  (I need this information to make sure I don't screw up the 3rd party app's assumptions when I screw around with its data ... a sort of 'safety harness').

So I codegen, and reflect my.namepace.codegen.Keys to discover all the static members of type org.jooq.ForeignKey. For each one I want to generate SQL like:

SELECT a.id, count() as card

FROM a INNER JOIN b ON a.id = b.id

GROUP BY a.id

... where the name of table a, table b and their join fields are fetched from the jorg.jooq.ForeignKey

... and card == 1 might be 1:1 relationships, and card > 1 are definitely 1:N relationships.

I've got this far, but am a bit lost in a type bog (my sample does not compile), and I'm far from confident that I'm using the JOOQ API optimally :

enum class Cardinality { ONEONE, ONEMANY }

fun test(fk: ForeignKey<Record, Record>, create: DSLContext) : Cardinality {

    // add both the count() and the primary key of the parent table
    // (or at least the key used to reference it) to a collection,
    // to use in the select

    val selectFields = mutableListOf<SelectField<*>>()
    selectFields.addAll(fk.key.table.primaryKey?.fields ?: fk.key.fields)
    selectFields.add(count())

    // do a join on the parent and child tables, on the fields
    // that map the foreign key relationship, and count the number of
    // children per parent, and so guess the cardinality.

    val cardinality = create.select(selectFields)
        .from(fk.key.table)
        .join(fk.inverseKey.table)
        .on(
            // we should get a List<Condition> passed to the and() 
            // after mapping, but the eq() isn't working
            and( fk.key.fields.mapIndexed { i, parentField -> parentField.eq(fk.inverseKey.fields[i]) } )
        )
        .groupBy()
        .fetchSingle()
        .get(selectFields.size /*ie. the count()*/ ) as Int

    return if (cardinality > 1) Cardinality.ONEMANY else Cardinality.ONEONE

}

So the questions I have for the group are:

* Is this general approach the right one?

* is it correct to be using fk.key.fields and fk.inverseKey.fields the way I am?

* is the assumption that fk.key.fields is ordered equivalently to fk.inverseKey.fields justified?

* does JOOQ have a fancier type of join where I don't really need to enumerate the fields in the on() anyway?  (Assuming there is only 1 FK between the tables)
* why doesn't 'eq()' work the way I am trying to use it?  (I understand it could be a Kotlin thing)

Any comment to bump me in a better direction is welcome.

thanks,

David.

[Lukas Eder]

* Is this general approach the right one?

Do also check for unique constraints on the foreign key. It's not a widely adopted practice, but if you do encounter one, you'll know it's a 1-1 relationship (where 1 means 0..1). Other than that, I mean, it's a heuristic. You won't have any guarantees about 1-1 relationships if they're not enforced with a unique key. As with any heuristics: Good luck! :)

 

* is it correct to be using fk.key.fields and fk.inverseKey.fields the way I am?

Why wouldn't it be?

 

* is the assumption that fk.key.fields is ordered equivalently to fk.inverseKey.fields justified?

Yes, there's no reason to shuffle these. We're just swapping keyFields and fields of the ForeignKey, as well as the tables, of course.

 

* does JOOQ have a fancier type of join where I don't really need to enumerate the fields in the on() anyway?  (Assuming there is only 1 FK between the tables)

onKey(ForeignKey) would probably help?

 

* why doesn't 'eq()' work the way I am trying to use it?  (I understand it could be a Kotlin thing)

Well, the type is Field<*>, and as with any wildcards, these prevent comparisons / method calls like these. Just like you cannot add anything to a MutableList<*>, because you don't know what * stands for. In Java, the usual workaround is to use raw types here. In Kotlin, I guess you could unsafe cast both fields to Field<Any?>. But this will be unnecessary with onKey()

[David Bullock]

On Tue, 2 Jul 2024 at 22:01, Lukas Eder <lukas...@gmail.com> wrote:

Do also check for unique constraints on the foreign key.

Good thought, thanks.

 

As with any heuristics: Good luck! :)

Thanks :-)  For now, I just have to refrain from causing a 1:N where it might not be, so it should be sufficient.

 

* is it correct to be using fk.key.fields and fk.inverseKey.fields the way I am?

Why wouldn't it be?

Partly because with Kotlin, IDEA doesn't really surface the most likely methods/properties to the top of the auto-complete list (I'm not sure why).  I was initially looking for language like 'child' and 'parent' but all the methods with those names took a parameter I didn't know how to supply.  (Maybe those methods are useful to my cause, but I'm not sure how).  Even now, it isn't immediately intuitive that inverseKey.table is the 'child' table.  But it seems I got that right.

* does JOOQ have a fancier type of join where I don't really need to enumerate the fields in the on() anyway?  (Assuming there is only 1 FK between the tables)

onKey(ForeignKey) would probably help?

Yes!  Just the thing!

Thanks,

David.