Use JOOQ to parse SQL and extract table names

[Tim Büthe]

Hi,

I originally asked my question here: https://stackoverflow.com/questions/57796087/

I'm trying to parse SQL statements and extract table names using JOOQ's parser. The problem is, the Query and SelectQuery don't seem to provide a public getter for the parsed table list.

    val parser = DSL.using(SQLDialect.POSTGRES_10).parser()

    val queries = parser.parse("SELECT foo, bar FROM mytable WHERE id = 1;")

    for (query in queries) {

        when (query) {

            is SelectQuery<*> -> println(query.fields().map { it.name }) // can find the fields, but not the tables

            else -> println(query)

        } 

    }

I could indeed help myself accessing the private field using reflection, but I wonder if there is an easier way:

    val field = query.javaClass.getDeclaredField("from")

    field.isAccessible = true

    println(field.get(query))

And of course, this should also work for other statements (e.g. Insert, update, delete).

Lukas encouraged me to come here and elaborate on the use case. So here it goes:

I have this central database which is accessed by a bunch of different applications. They all use the same credentials, which is the user account owning all tables with full permissions to everything. I want to introduce dedicated logins and restrict the permissions to what is currently used. 

To find which objects are used by a given consumer I want to do the following:

• create dedicated accounts with full permissions for each consumer
• activate statistics
• query pg_stat_statements to get the executed statements for a given username
• use JOOQ to extract table names and set permissions accordingly

If I don't find something that parses SQL reliably, I going to retrieve the list of existing tables and simple check if I find a given table name in the queries obtained from pg_stat_statements, which might be good enough actually.

If you could think of a simpler approach I'm would be happy to hear your thoughts on it.

regards,

Tim

 

[Lukas Eder]

Hi Tim,

Thank you very much for your email and for your patience.

In recent months, getting better access to the jOOQ expression tree model has been an increasingly popular feature request on all of our support channels. Let me first state that we've recognised this need and will make re-designing our expression tree model for internal and external use a top priority for jOOQ 3.13. There are numerous reasons why a new, immutable query object model will add a lot of value to everyone, specifically those with parser use cases.

Regarding your specific use-case, I think that your workaround, trying to find existing tables in query strings might indeed be a viable 80/20 solution (or even 95/5 if your table names are sufficiently specific). You can, however, use jOOQ 3.12 for this already. As long as you're not transforming your SQL, a VisitListener will probably do. A very similar question has been asked here: https://stackoverflow.com/q/53990723/521799, it aims for collecting field references from create table statements. See my answer here: https://stackoverflow.com/a/54006668/521799

If you're using the jOOQ API to create your SQL statements, then you don't even need the parser.

I hope this helps,
Lukas

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/bcdbd88d-a3e0-401e-b4be-22b759a35c70%40googlegroups.com.