Hi, I originally asked my question here: https://stackoverflow.com/questions/57796087/ <https://stackoverflow.com/questions/57796087/how-to-use-jooqs-parser-to-extract-table-names-from-sql-statements?noredirect=1#comment102048796_57796087>
I'm trying to parse SQL statements and extract table names using JOOQ's parser <https://www.jooq.org/doc/3.11/manual/sql-building/sql-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 -- 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/bcdbd88d-a3e0-401e-b4be-22b759a35c70%40googlegroups.com.
