Hm, I could try to hack a bit on this but I would need some more info on where to start. Right now I'm thinking that I want to tell jOOQ that I have a primary key for the views, not tables as your issues seems to suggest.
Would one feasible way be to get jOOQ to treat the views as tables and then use the synthetic primary key feature on those tables? I can say that "for each view named FOO with a table named FOO_TAB is a 'updateable view'". In our case we always have a "id NUMBER(18,0)" column as primary key. -- Trygve kl. 11:31:24 UTC+1 tirsdag 25. mars 2014 skrev Lukas Eder følgende: > > Hello Trygve > > I think you are right. jOOQ's multi-tenancy features (table mapping) might > not be suitable enough for your specific use-case. > > I am wondering, however, if we should add a code-generation feature to > allow for generating "synthetic" primary keys in tables. We have already > implemented #3093 for jOOQ 3.4 to allow for overriding primary key > declarations by unique keys: > https://github.com/jOOQ/jOOQ/issues/3093 > > This is useful in SQL Server, for instance, where people often use > composite unique indexes with nullable columns as substitutes for primary > keys. We could go even further and allow for declaring "primary keys" in > the code-generator's XML configuration with these attributes: > > - name (optional, could be generated) > - pattern to match table / column(s) > > Note that you can already now override the JavaGenerator to supply > jOOQ-meta with your own "primary keys" as a last resort. > > Best Regards, > Lukas > > 2014-03-25 9:05 GMT+01:00 Trygve Laugstøl <[email protected] <javascript:>> > : > >> Hi. >> >> I've checked with our local DBAs and they don't know if it's readily >> available from any views either. I think that the updatability of a view is >> something that happens at runtime inside Oracle so it might not really be >> available anyway. >> >> The database design we're thinking of involves having views around tables >> and using triggers to migrate data to the old/new columns. So the table >> will be the sum of all the columns required so I was hoping I could get >> JOOQ to just look at the views. I wouldn't mind telling JOOQ that all views >> are updateable by default, but I don't have a good way of figuring out >> which field is the primary key. >> >> The tables will contain more fields than the views as they're covering >> more than one version of the schema which is why I want the fields in the >> first place so your rewriting trick doesn't work out of the box. I'm right >> now thinking that it might be easiest to have a clean target schema with >> only tables, run the generator on that and treat the migration of the real >> schema as a different problem. >> >> -- >> Trygve >> >> kl. 18:55:48 UTC+1 fredag 21. mars 2014 skrev Lukas Eder følgende: >>> >>> Hello, >>> >>> In the past, we had been thinking about a potential implementation of >>> such a feature: >>> http://stackoverflow.com/q/5500738/521799<http://www.google.com/url?q=http%3A%2F%2Fstackoverflow.com%2Fq%2F5500738%2F521799&sa=D&sntz=1&usg=AFQjCNHkY4jsixj6pRXaHKA2WO10FDurvQ> >>> >>> Unfortunately, there is no reliable way to know that a view is indeed: >>> >>> - updatable at all >>> - updatable through a given primary / unique key >>> >>> If, by database schema design, you know that foo_tab and foo are exact >>> equivalents of each other (same columns) then you can use jOOQ's runtime >>> table mapping feature to either >>> >>> - rewrite reads from foo to be reads from foo_tab >>> - rewrite writes to foo_tab to be writes to foo >>> >>> See the manual for details: >>> http://www.jooq.org/doc/latest/manual/sql-building/ >>> dsl-context/runtime-schema-mapping/ >>> >>> Let me know if this helps or if you had something else in mind >>> >>> Regards, >>> Lukas >>> >>> >>> 2014-03-21 15:36 GMT+01:00 Trygve Laugstøl <[email protected]>: >>> >>>> Hi >>>> >>>> I was wondering if it is possible to get JOOQ to support insertable >>>> views? We're going to have to use insertable/updatable views to be able to >>>> do more running upgrades of our application, so for most tables we will be >>>> following the pattern of having a Java entity "Foo", a table "foo_tab" and >>>> a view called "foo". >>>> >>>> Our database is Oracle, but several other databases (even open source >>>> ones) support insertable/updatable views and it would be nice if JOOQ >>>> could >>>> make the Records extends UpdatableRecordImpl instead of >>>> just TableRecordImpl. In other words, I would like this to still work: >>>> >>>> PersonRecord r = new PersonRecord(); >>>> r.setId(id); >>>> r.setName(person.name); >>>> dsl.attach(r); >>>> r.insert(); >>>> >>>> instead of having to do this: >>>> >>>> PersonRecord r = dsl.insertInto(PERSON). >>>> set(PERSON.ID, id). >>>> set(PERSON.NAME, person.name). >>>> returning(). >>>> fetchOne(); >>>> >>>> -- >>>> Trygve >>>> >>>> -- >>>> 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.
