Jooq and MSSQL Synonyms

[David Scott]

Been using Jooq for a while, but this is the first time we've tried it with Synonyms and Views.   The problem I'm running into is that the generator is not picking up on the synonyms (although the views work fine).   Does the generator not support synonyms?

Here's the gradle code I'm using to run the generator:

// Task for generating jOOQ model objects
task generate << {
   def writer = new StringWriter()
   def xml = new groovy.xml.MarkupBuilder(writer)
         .configuration('xmlns': 'http://www.jooq.org/xsd/jooq-codegen-3.6.0.xsd') {
      jdbc() {
         driver('com.microsoft.sqlserver.jdbc.SQLServerDriver')
         url('jdbc:sqlserver://10.2.100.42:1433;databaseName=test_sandbox')
         schema('dbo')
         user('test_user')
         password('XXXX')
      }
      generator() {
         database() {
         }
         generate() {
         }
         target() {
            packageName('org.advanced.testapi.v2.jooq')
            directory('src/main/java')
         }
      }
   }

   // Run the code generator
   // ----------------------
   org.jooq.util.GenerationTool.main(
         javax.xml.bind.JAXB.unmarshal(
               new StringReader(writer.toString()),
               org.jooq.util.jaxb.Configuration.class
         )
   )
}

[Lukas Eder]

Hi David,

Thank you very much for your enquiry. The code generator does resolve synonyms in Oracle databases, e.g. when a PL/SQL stored procedure returns a synonym of an OBJECT type, we'll resolve that to refer to the original object type.

SQL Server's synonyms are a bit less sophisticated, so object resolution is not really necessary.

Nevertheless, it would be interesting to have a generated Synonyms.java class, where all synonyms would be listed, referring to the physical object (or to another synonym). There's a pending feature request for this:

https://github.com/jOOQ/jOOQ/issues/1563

I suspect we could re-evaluate its implementation for the upcoming jOOQ 3.8. What's your expectation towards this feature?

Best Regards,

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

[David Scott]

Hi Lukas-

I'm not looking for any special handling of synonyms -- I would just like to see the synonym treated the same way as a regular table or view.  Basically, that the generator detects the synonym and creates a corresponding TableImpl class, the same way it does for normal tables and views.  

Thanks,

-David

[Lukas Eder]

Hi David,

I've done some research in the past about how synonyms can be embedded in jOOQ's code generator output. I don't think it's a good idea to create separate TableImpl classes, specifically because foreign key information would be incomplete, or wrong. Imagine a table A referencing a table B. Now, you create a synonym X for A and Y for B. When you navigate from X to other tables, should you reach Y? Or B? Or both?

What happens with transitive synonyms? I.e. when you declare S as a synonym for Y? Should you now be able to navigate from A to S and/or Y and/or B?

I understand that synonyms can add some value also to jOOQ client code, but things get very complicated if classes are duplicated. If Java had type aliases like Ceylon, things would be much more easy to implement.

I'll continue thinking about what kind of support can be added to jOOQ. A Synonyms.java class that adds references to existing table literals is certainly doable.

Best Regards,

Lukas

[dfs]

Ok, I can see how that might be a problem.  In my case, the synonym was referring to a table that wasn't already specified (it was in a separate database), so it wasn't an issue.   The main reason we were using them is to be able to join tables across databases.  A view accomplishes this as well, so we're going to go with this solution for now.

Thanks,

-David

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/_gV6IYYppyQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hi David,

Ah, I see now. Interestingly, cross database synonyms would be rather difficult to resolve in this way anyway, because it is not easy to query the sys and information_schema tables from several different databases. The "use" command needs to be issued prior to foreign database items being visible in those views.

In jOOQ 3.8 (due in the next weeks), further support for Catalogs (i.e. SQL Server databases) will be added (see https://github.com/jOOQ/jOOQ/issues/4869). Perhaps that will resolve the issue more thoroughly for you, anyway.

I hope this helps,

Lukas