Dynamic filtering

[Kevin Embree]

I have been following the example given in blog post. https://blog.jooq.org/2015/06/17/implementing-client-side-row-level-security-with-jooq/

In this example it pushes a condition if a specific column on a specific table is present in the query.

pushConditions(context, ACCOUNTS,

        ACCOUNTS.ID, ids);

I'm looking to push a condition for a column with a specific name on ANY table that has that column present. I'm having trouble figuring out how to identify/grab/return the Field of interest to push it into the condition. Any help you can provide would be greatly apprecitated.

Many thanks,
-Kevin


[Samir Faci]

Well, how would you imagine that working in raw SQL.  If you don't include the table name and you have more the one column with the same name you'll get an ambiguous error.  

Jooq is an abstraction of your DB and I'm trying to see how you'd build this query you're trying to write in Jooq.

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Thank you
Samir Faci

https://keybase.io/csgeek

[Lukas Eder]

Samir, I think what Kevin meant is to use jOOQ's generated classes, but to implement a VisitListener that works for all sorts of tables, not just the hard-wired ACCOUNTS (and TRANSACTIONS) tables as mentioned in the blog post.

Kevin, this is certainly possible. Check out the logic inside of pushConditions():

<E> void pushConditions(

        VisitContext context,

        Table<?> table,

        Field<E> field,

        E... values) {

 

    // Check if we're visiting the given table

    if (context.queryPart() == table) {

        List<Clause> clauses = clauses(context);

In your case, instead of accepting a table and field parameter, the method should probably accept a String for the field name, and then check if we're visiting any table that contains the field name:

<E> void pushConditions(

        VisitContext context,

        String name,

        E... values) {

 

    // Check if we're visiting a table containing the field name

    if (context.queryPart() instanceof Table && ((Table<?>) context.queryPart()).field(name) != null) {

        // ...

Beware that VisitListener logic must be implemented in a very performance sensitive way. The above usage of the field(String) method might not perform well enough, but it might give you an idea of where you need to go.

Let me know if this helps, and if you have any specific questions, I'll be very happy to assist you.

Best Regards,

Lukas

[Kevin Embree]

Thank you Lukas,

I think ...
((Table<?>) context.queryPart()).field(name)
... was the part I was missing.

As to your comment about performance, as my schema will not change after initialization of the system, when I initialize the EventListener i will set up something such a Hashmap <Table, Field>, to quickly find the corresponding field for the tables visited in the query context.

Thanks
-Kevin
 

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Thank you
Samir Faci

https://keybase.io/csgeek

--
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.

[Lukas Eder]

2017-04-08 21:32 GMT+02:00 Kevin Embree <embree...@gmail.com>:

Thank you Lukas,

I think ...
((Table<?>) context.queryPart()).field(name)
... was the part I was missing.

As to your comment about performance, as my schema will not change after initialization of the system, when I initialize the EventListener i will set up something such a Hashmap <Table, Field>, to quickly find the corresponding field for the tables visited in the query context.

That's one option. Alternatively, you could extend the code generator to implement a getSpecialField() method on each table

Hope this helps,

Lukas

[Kevin Embree]

Hi Lukas,
   My initial approach of using HashMap didn't appear to work as I got into an endless loop. Seems the hash function triggers the VisitListener.
 
   You idea for getSpecialField seemed a cleaner solution anyways, but in my attempts to write it I referenced how JavaGenerator generates getRecordVersion methods.
   I was hoping to just extend the JavaGenerator class and override generateTableClassFooter, but I ran into the problem that my method would need the recordType and something like the following line (plucked from the JavaGenerator)....
      

final String recordType = out.ref(getStrategy().getFullJavaClassName(table, Mode.RECORD));

   Isn't possible as all out.ref methods are protected. Am I missing an obvious way to get 'recordType'? I wouldn't be surprised quite often the obvious stuff eludes me.
  
   Thanks in advance for the words of wisdom and a wonderful framework.
   -Kevin

[Lukas Eder]

Hi Kevin,

2017-05-10 3:22 GMT+02:00 Kevin Embree <embree...@gmail.com>:

Hi Lukas,
   My initial approach of using HashMap didn't appear to work as I got into an endless loop. Seems the hash function triggers the VisitListener.

Yes indeed. The default implementation of AbstractQueryPart.hashCode() (like equals()) is currently based on the SQL string generated from such QueryPart. A VisitListener should thus prevent infinite recursion and apply only once on the call stack. That can be a bit tricky, and hashCode() / equals() won't be the only places where this can happen.

 

 
   You idea for getSpecialField seemed a cleaner solution anyways, but in my attempts to write it I referenced how JavaGenerator generates getRecordVersion methods.
   I was hoping to just extend the JavaGenerator class and override generateTableClassFooter, but I ran into the problem that my method would need the recordType and something like the following line (plucked from the JavaGenerator)....
      

final String recordType = out.ref(getStrategy().getFullJavaClassName(table, Mode.RECORD));

   Isn't possible as all out.ref methods are protected.

Hmm, yes indeed. A workaround would be to put your own generator in the same package as the JavaGenerator. You would then get access to those methods.

But you don't really need out.ref(). That's used only to avoid full qualification of your type (registering the type in the import list). It will work just the same otherwise.

 

Am I missing an obvious way to get 'recordType'? I wouldn't be surprised quite often the obvious stuff eludes me.

Don't blame yourself :) The current code generator wasn't really (well) designed for extension, so some things are simply much harder than they ought to be.

We're hoping to fix things in this area in jOOQ 4.0

[Kevin Embree]

One step closer, yet no joy.
I customized the Generation Strategy and get a Table that implements a custom interface 'AuditedTable'

public class Junit extends TableImpl<JunitRecord> implements AuditedTable {

But in the visitListener ...

    <E> void pushConditions(VisitContext context) {
        if (context.queryPart() instanceof Table){
            LOG.info("... Table " + context.queryPart().toString());
        // Check if we're visiting a special table
        if ((context.queryPart()) instanceof AuditedTable) {
                LOG.info("... AuditedTable " + context.queryPart().toString());

'instanceof Table' works as I expected.
But 'Instanceof AuditedTable' doesn't. I must be misunderstanding how the AST queryParts work.
Any nudge in the right direction would greatly appreciated.

[Lukas Eder]

Hi Kevin,

Just to be sure, in your snippet, you didn't omit any "else" or "return" or any other sort of statement that would break out of the method prior to checking your instanceof AuditTable check?

Also, what's the complete code of your visit listener right now? When is pushConditions() called?

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+unsubscribe@googlegroups.com.

[Kevin Embree]

On Thursday, May 18, 2017 at 3:43:31 AM UTC-4, Lukas Eder wrote:

Hi Kevin,

Just to be sure, in your snippet, you didn't omit any "else" or "return" or any other sort of statement that would break out of the method prior to checking your instanceof AuditTable check?

No, there is nothing in between.
 

Also, what's the complete code of your visit listener right now? When is pushConditions() called?

First line of Overriding visitEnd

    @Override
    public void visitEnd(VisitContext context) {
        pushConditions(context);

 

Lukas

2017-05-18 0:30 GMT+02:00 Kevin Embree <embree...@gmail.com>:

One step closer, yet no joy.
I customized the Generation Strategy and get a Table that implements a custom interface 'AuditedTable'

public class Junit extends TableImpl<JunitRecord> implements AuditedTable {

But in the visitListener ...

    <E> void pushConditions(VisitContext context) {
        if (context.queryPart() instanceof Table){
            LOG.info("... Table " + context.queryPart().toString());
        // Check if we're visiting a special table
        if ((context.queryPart()) instanceof AuditedTable) {
                LOG.info("... AuditedTable " + context.queryPart().toString());

'instanceof Table' works as I expected.
But 'Instanceof AuditedTable' doesn't. I must be misunderstanding how the AST queryParts work.
Any nudge in the right direction would greatly appreciated.

--
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.

[Lukas Eder]

Hi Kevin,

Thanks for your message. I'll be happy to review and debug this issue, but before I take a deeper look, do you perhaps have a complete example that I could use to reproduce this? E.g. the following test case works for me:

public class VisitListenerTest extends AbstractTest {

    class TableCatcher extends DefaultVisitListener {

        final Predicate<VisitContext> matcher;

        int hits;

        public TableCatcher(Predicate<VisitContext> matcher) {

            this.matcher = matcher;

        }

        @Override

        public void visitEnd(VisitContext ctx) {

            if (matcher.test(ctx))

                hits++;

            super.visitEnd(ctx);

        }

    }

    @Test

    public void testFindingCustomTableWithVisitListener() {

        TableCatcher c1 = new TableCatcher(ctx -> ctx.queryPart() instanceof MyTable);

        TableCatcher c2 = new TableCatcher(ctx -> ctx.queryPart() instanceof IMyTable);

        TableCatcher c3 = new TableCatcher(ctx -> ctx.queryPart() instanceof I);

        DSL.using(create.configuration().derive(providers(c1)))

           .selectFrom(new MyTable())

           .getSQL();

        DSL.using(create.configuration().derive(providers(c2)))

           .selectFrom(new MyTable())

           .getSQL();

        DSL.using(create.configuration().derive(providers(c3)))

           .selectFrom(new MyTable())

           .getSQL();

        assertEquals(1, c1.hits);

        assertEquals(1, c2.hits);

        assertEquals(0, c3.hits);

    }

    interface I {}

    interface IMyTable {}

    static class MyTable extends TableImpl<MyTableRecord> implements IMyTable {

        MyTable() {

            super(DSL.name("my_table"));

        }

    }

    static class MyTableRecord extends TableRecordImpl<MyTableRecord> {

        public MyTableRecord() {

            super(new MyTable());

        }

    }

}

Cheers,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Kevin Embree]

Thank you Lukas,
         You test code obviously worked, which made me more and more puzzled why mine wasn't. I found myself going back to basics and questioning everything.
          And found my issue, I had to versions of the the generated source code, the one being used by the JUNIT tests didn't implement the interface, so obviously my code didn't work.

          My apologies, for the stupid inquire at the end, my AST it working now, got to write some more Junits to test all conditions.
          Said it before, I'll say it again, JOOQ is awesome.

Thanks

[Lukas Eder]

Hi Kevin,

Oh no, I know exactly how that feels. I've spent ages debugging stuff when having some stale Maven cache sometime in the past - so I feel your pain.

Well, the good news is that things seem to work now :)

Let me know if you encounter any additional issues

Lukas