SQL Server code generation: forcedType knowledge of alias types

[Roded Bahat]

Hi,
I'd like to save Unix times in SQL Server as BIGINTs and have jOOQ convert them to java.time.Instants.
However, for code generation, it seems to me to be a bit coarse to apply such a conversion to all BIGINTs columns and I don't really like the idea of converting columns based on the pattern of their name.
I was hoping that I could define a alias type called UNIX_TIME (based on BIGINT) and then apply jOOQ's forced type conversion to it (UNIX_TIME) to gain finer control.
It seems though that this does not work. Am I limited to the types in SQLDataType for the forcedType.types configuration?
Is this a legitimate feature request?
Any helpful inputs or recommendations welcome.
Thanks,
Roded

[Lukas Eder]

Hi Roded,

Thanks for your question. Interesting, I wasn't aware that SQL Server 2016 introduced type aliases. They look a lot like the SQL standard (and PostgreSQL) "domains". I've updated the relevant feature request here and increased its priority. I think that domains are a really interesting SQL feature:

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

Currently, jOOQ doesn't fully support domains as there are a lot of open questions how to support them in a very useful, yet database agnostic way. Certainly, once they're supported, it should be possible to pattern-match them in the existing <types> element as you described.

Unfortunately, right now, that's not possible, so you will have to combine <types>BIGINT</types> with an expression matching either all columns, or a well-defined naming pattern.

A workaround might be to write a short SQL query that queries the SQL Server INFORMATION_SCHEMA (or SYS tables) to find out all the columns that are of alias type UNIX_TIME, and then generate a regular expression from that. If you were using the programmatic generator configuration, it would then be easy to embed this resulting regular expression in the configuration:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-programmatic

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

[Roded Bahat]

Thanks for the reply Lukas. That issue is very elucidating.
I'll set aside BIGINTs for usage as unix time for now and consider the programmatic generator option as an option.
Thanks again.
Roded

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