Lukas, I've already thought to do some custom codegeneration, thanks to pointing me to the docs.
You got exaclty what I mean. I will do a test to see if your tip cover my needs. Thanks. On Thursday, December 20, 2018 at 2:00:25 PM UTC+1, Lukas Eder wrote: > > No, it's OK. No mcve is needed, thanks for the explanation. I see, so your > expectation is that in the absence of an identity column, but in the > presence of a primary key column, you would like to fetch back additional > generated values from the database after inserting the row, such as for > example created_at timestamps, and similar? Indeed, this currently doesn't > work with MySQL/MariaDB and a few other databases, as an identity column is > expected by jOOQ. > > You can tell the code generator to produce synthetic identity columns: > > https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-synthetic-identities > > That way, table.getIdentity() will no longer return null and the relevant > code will be executed to fetch generated values (I suspect?). Can you > confirm this? > > I wonder if we should also check for single column primary key meta > information in case table.getIdentity() is null... > > On Thu, Dec 20, 2018 at 1:14 PM <[email protected] <javascript:>> > wrote: > >> Hi Lukas, >> >> Using a trigger _could be_ an option for some scenario, but I don't this >> is a viable approach with a clustered database. >> Regaring Jooq limitation, I understand your concern, but could be not an >> issue. >> >> RDBS: mariadb 5.5 >> >> Id generation process use hilo algorithm, as described at >> https://stackoverflow.com/a/24368478 but is not really relevant which >> algorithm I use to create a new id, usually in testing I use a >> FakeIdGenerator that create sequential id in memory. >> I can create a small project using jooq-mcve is this ok os do you prefer >> having some code snippet just to have an idea? >> >> Cheers >> >> On Thursday, December 20, 2018 at 10:17:51 AM UTC+1, Lukas Eder wrote: >>> >>> Hi Daniele, >>> >>> My first thought in such a case is always: Is using a trigger an option? >>> Because triggers will be the most reliable way to guarantee the correct ID >>> assignment across database clients. Of course, you can do something with >>> jOOQ, but that will force you to use jOOQ for every single database >>> interaction, and it might even limit your possible usage of jOOQ (e.g. no >>> more plain SQL queries). >>> >>> Indeed, returning (in some databases) works only if there is an identity >>> column. That is very unfortunate, but if it is the case, it means that the >>> RDBMS or the JDBC driver do not offer any other way to fetch a "generated" >>> key. But maybe there's a way to work around this, or even fix something in >>> jOOQ. Could you please provide: >>> >>> - Your RDBMS vendor and version >>> - Some code including DDL and jOOQ code that shows how your ID >>> generation works >>> >>> Thanks, >>> Lukas >>> >>> On Wed, Dec 19, 2018 at 10:45 PM Daniele Antonini <[email protected]> >>> wrote: >>> >>>> Hi, >>>> >>>> I have a database which use global unique row ID instead of >>>> (autoincrement) ID per table. >>>> I'm trying to encapsulate this aspect so I don't need to manually use >>>> an ID generator every time. >>>> I implemented a VisitorListener to "inject" ID on insert query if not >>>> specified and works fine unless I trying to return a record. >>>> >>>> I tried to use returning() but it has a different behavior than >>>> expected (but in this case my expectation is probably wrong). >>>> >>>> Seems that returning() works only for database auto-generated stuff, >>>> and is hard to alter this bahavior. >>>> >>>> My attempt was the following: >>>> I used VisitorListener to inject id in the query, the implementation >>>> collect the generated ID and implement also the ExecuteListener interface >>>> which allow to manipulate the statement before the execution. So I create >>>> a >>>> prepared statement wrapper which is ids aware and return they when >>>> getGeneratedKeys() is invoked. I did it, but then jooq ask for >>>> table.getIdentity() and I stopped. >>>> >>>> My concerns are: >>>> - am I right simulating auto-generated key behavior? >>>> - how can I obtain a record automatically without executing an explicit >>>> select? Moreover in this case I don't even know the ID to use for the >>>> select because is injected by the visitor >>>> >>>> At this very moment, I discarded this idea, set id at application level >>>> and manually execute a select to obtain a record. >>>> >>>> Any thoughts? >>>> >>>> Cheers >>>> >>>> >>>> -- >>>> 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 [email protected]. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> -- >> 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 [email protected] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
