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.
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to jooq-user+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAE14wDV%2B4zPUy8fygNEKMuWuei1K6_iJ%3DZ1zZoQKBCLGfgUdMA%40mail.gmail.com.
