Sounds good, tks Lukas. Actually I've got no more ideas. 2016-10-19 16:00 GMT+02:00 Lukas Eder <lukas.e...@gmail.com>:
> Hi Denis, > > Thank you very much for sharing this. That looks very sophisticated - nice > Stream usage. > > I wonder if we should add some methods to the jOOQ API, e.g. > Table.fieldStream() to get a Stream<Field<?>> as opposed to Table.fields() > which returns Field<?>[]. This would allow for flatmapping on that method, > e.g. > > Map<String, List<Field<?>>> onlyLiveColumns = liveTables.stream() > // .flatMap(table -> > Arrays.asList(table.fields()).stream()) > .flatMap(table::fieldStream)... > > > I've created an issue for this: > https://github.com/jOOQ/jOOQ/issues/5603 > > Thanks again for sharing! Seeing actual user code is always great > inspiration for the API design. > > Let me know if you see any additional features that you might want to see > to improve your schema meta data navigation experience. > Lukas > > 2016-10-14 8:23 GMT+02:00 Denis Miorandi <denis.miora...@gmail.com>: > >> Hi Lukas, >> this is a code snippet to achieve comparision. It's limited to >> tables (columns). Comparision is made from left to right and from right to >> left. >> Outcome is what's in only on left and what is only on right (if a table >> is missing all table columns are printed otherwise just different columns). >> We use it sucessfully to prevent install in production if code is not in >> sync with db (in a rest service). We have got also a gui tool to show >> what's different. >> >> String result = ""; >> List<ApiWebToolsTabellaListItem> tabellePreprod = new ArrayList<>(); >> List<ApiWebToolsTabellaListItem> tabelleProd = new ArrayList<>(); >> >> try { >> String databaseName = >> System.getProperty(PROPERTY_ENVIRONMENT).equals(ICEF_ENVIRONMENT) >> ? SQL : POSTGRES; >> >> ArrayList<String> functionsToExclude = getDatabaseFunctions(); >> >> List<Table<?>> generatedTables = getGeneratedTables().stream() >> .filter(x -> >> !functionsToExclude.contains(x.getName())).collect(Collectors.toList()); >> >> Schema schema = create.meta().getSchemas().stream().filter(x -> >> x.getName().equals(databaseName)).findFirst().orElse(null); >> List<Table<?>> liveTables = schema.getTables().stream() >> .filter(x -> >> !x.getName().equals(V_VE_EDIL_RIPARTO_FINALE_2007)) >> .filter(x -> >> !functionsToExclude.contains(x.getName())).collect(Collectors.toList()); >> >> Map<String, List<Field<?>>> onlyLiveColumns = liveTables.stream() >> .flatMap(table -> Arrays.asList(table.fields()).stream()) >> .filter(e -> (generatedTables.stream().flatMap(table -> >> Arrays.asList(table.fields()).stream()) >> .noneMatch(d -> { >> String dt = ((TableField<?, ?>) >> d).getTable().getName(); >> String et = ((TableField<?, ?>) >> e).getTable().getName(); >> return (dt + "." + d.getName()).equals(et + >> "." + e.getName()); >> }))) >> .collect(Collectors.groupingBy( >> x -> ((TableField) x).getTable().getName(), >> Collectors.mapping(x -> x, >> Collectors.toList()))); >> Map<String, List<Field<?>>> onlyGeneratedColumns = >> generatedTables.stream() >> .flatMap(table -> Arrays.asList(table.fields()).stream()) >> .filter(e -> (liveTables.stream().flatMap(table -> >> Arrays.asList(table.fields()).stream()) >> .noneMatch(d -> { >> String dt = ((TableField<?, ?>) >> d).getTable().getName(); >> String et = ((TableField<?, ?>) >> e).getTable().getName(); >> return (dt + "." + d.getName()).equals(et + >> "." + e.getName()); >> }))) >> .collect(Collectors.groupingBy( >> x -> ((TableField) x).getTable().getName(), >> Collectors.mapping(x -> x, >> Collectors.toList()))); >> ; >> System.out.println("______________________________"); >> System.out.println("ONLY LIVE"); >> onlyLiveColumns.entrySet().stream().map(x -> { >> System.out.println("------------------"); >> System.out.println(x.getKey()); >> System.out.println("------------------"); >> return x; >> }) >> .flatMap(x -> x.getValue().stream()) >> .map(x -> x.getName()) >> .forEach(System.out::println); >> >> System.out.println("______________________________"); >> System.out.println("ONLY GEN"); >> onlyGeneratedColumns.entrySet().stream().map(x -> { >> System.out.println("------------------"); >> System.out.println(x.getKey()); >> System.out.println("------------------"); >> return x; >> }) >> .flatMap(x -> x.getValue().stream()) >> .map(x -> x.getName()) >> .forEach(System.out::println); >> >> >> >> >> Il giorno martedì 20 settembre 2016 15:25:39 UTC+2, Lukas Eder ha scritto: >>> >>> Excellent, thanks for the feedback. Once we have something workable, I >>> can also take another look at speeding up meta queries for these use-cases. >>> There's always room for improvement. >>> >>> 2016-09-20 14:59 GMT+02:00 Denis Miorandi <denis.m...@gmail.com>: >>> >>>> got it, now is less than 10 seconds >>>> >>>> Schema schema=create.meta().getSchemas().stream().filter(x->x.getNa >>>> me().equals("dbo")).findFirst().orElse(null); >>>> List<Table<?>> liveTables=schema.getTables(); >>>> >>>> so that I've to make some work on maven plugin generation to to avoid >>>> clean + rebuild all. >>>> >>>> Tks Lukas >>>> >>>> Il giorno martedì 20 settembre 2016 14:33:06 UTC+2, Lukas Eder ha >>>> scritto: >>>>> >>>>> Yes, this does take a while too. Behind the scenes, JDBC's >>>>> DatabaseMetaData is invoked for every catalog / schema, and possibly also >>>>> for every table to collect columns. >>>>> >>>>> You might be able to speed it up by choosing meta().getSchemas() and >>>>> then calling schema.getTables() only for the desired schema. >>>>> >>>>> 2016-09-20 14:28 GMT+02:00 Denis Miorandi <denis.m...@gmail.com>: >>>>> >>>>>> got it. I'll update you about my progress, to discuss more if needed. >>>>>> >>>>>> Just to know create.meta().getTables() takes 90 seconds, ignoring >>>>>> compilation time. >>>>>> >>>>>> >>>>>> >>>>>> Il giorno martedì 20 settembre 2016 13:25:48 UTC+2, Lukas Eder ha >>>>>> scritto: >>>>>>> >>>>>>> Hi Denis, >>>>>>> >>>>>>> Sure, there are many interesting features and ideas that we could >>>>>>> integrate from users. Before providing an actual PR, however, I'd love >>>>>>> to >>>>>>> discuss concrete ideas, though. There's quite a big productization leap >>>>>>> from "works for me" to "works for everyone". I'm sure there's potential >>>>>>> in >>>>>>> this topic, though. >>>>>>> >>>>>>> Interesting to know that your generation is slow. My take here is >>>>>>> that this is probably due to the queries jOOQ is running, not the actual >>>>>>> generation work. Writing the files is very quick. Recompilation should >>>>>>> be >>>>>>> as well, as jOOQ doesn't touch files whose content haven't changed, so >>>>>>> you >>>>>>> can profit from incremental compilation. This leaves us with the >>>>>>> queries. >>>>>>> You could turn on debugging during your code generation, and we'll >>>>>>> probably >>>>>>> see quite a bit where the generator wastes its time. >>>>>>> >>>>>>> Best Regards, and thanks again for offering your help! >>>>>>> Lukas >>>>>>> >>>>>>> 2016-09-20 12:23 GMT+02:00 Denis Miorandi <denis.m...@gmail.com>: >>>>>>> >>>>>>>> ok now I'm understand why you don't use select *. >>>>>>>> Compare live and generated sounds good. To achieve this I should >>>>>>>> compare following objects >>>>>>>> >>>>>>>> List<Table<?>> generatedTables=it.clesius.dat >>>>>>>> alayer.icef.jooq.Dbo.DBO.getTables(); >>>>>>>> List<Table<?>> liveTables=create.meta().getTables(); >>>>>>>> >>>>>>>> I'm going to do it. >>>>>>>> >>>>>>>> This is very interesting and raise me into some other questions? >>>>>>>> >>>>>>>> 1. If I wrote this code could you integrate it into jooq ? >>>>>>>> 2. Using this function should be theoretically possibile to >>>>>>>> make a code generation by difference, I mean generate only changed >>>>>>>> tables. >>>>>>>> This is important to me cause my db is >1000 tables and >>>>>>>> generation is quite slow. >>>>>>>> Could be an interesting issue to open, isn't it? >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> Il giorno martedì 20 settembre 2016 10:58:40 UTC+2, Lukas Eder ha >>>>>>>> scritto: >>>>>>>>> >>>>>>>>> Hi Denis, >>>>>>>>> >>>>>>>>> Thank you very much for your question. Excellent SQL standard >>>>>>>>> document style definition of T1, C1, C2, etc. :) >>>>>>>>> >>>>>>>>> That's a very interesting question. I believe that somewhen in the >>>>>>>>> past, there had been a similar enquiry on this mailing list where >>>>>>>>> someone >>>>>>>>> solved the problem by comparing the jOOQ meta data with the production >>>>>>>>> schema. You can do this by comparing: >>>>>>>>> >>>>>>>>> - MY_GENERATED_SCHEMA.getTables() and then fields() >>>>>>>>> - DSLContext.meta().getTables() >>>>>>>>> >>>>>>>>> That could be run as some sort of integration test or smoke test. >>>>>>>>> Another option would be a nightly build that re-generates the jOOQ >>>>>>>>> schema >>>>>>>>> and compares the nightly build's version's Java code (or byte code) >>>>>>>>> with >>>>>>>>> the version that is checked in in version control and running in >>>>>>>>> production. >>>>>>>>> >>>>>>>>> Another option will be available in jOOQ 3.9, when it will be very >>>>>>>>> easy to export meta data information in XML format, which is much >>>>>>>>> easier to >>>>>>>>> compare than Java source code or byte code. Some example issues: >>>>>>>>> >>>>>>>>> - https://github.com/jOOQ/jOOQ/issues/5347 >>>>>>>>> - https://github.com/jOOQ/jOOQ/issues/5437 >>>>>>>>> >>>>>>>>> Indeed, you mentioned "typesafe mode with select *" - by that you >>>>>>>>> probably mean using DSLContext.selectFrom(), which returns generated >>>>>>>>> records instead of the untyped org.jooq.Record. In that case, you >>>>>>>>> cannot >>>>>>>>> automatically check column names explicitly. >>>>>>>>> >>>>>>>>> Probably I should better know the engine behavior, but I'm >>>>>>>>>> wondering about why jooq does not render T1.* instead exploding >>>>>>>>>> fields. >>>>>>>>> >>>>>>>>> >>>>>>>>> The reason is because jOOQ will expect exactly the columns that >>>>>>>>> are available from the generated record and optimises fetching them by >>>>>>>>> using access-by-index rather than access-by-name. If T1.* was >>>>>>>>> generated in >>>>>>>>> the SQL statement, you would only defer the problem until later. It's >>>>>>>>> usually desireable to have this query fail early. >>>>>>>>> >>>>>>>>> You could force jOOQ to generate a SELECT * query and discover the >>>>>>>>> actual columns from the actual result set meta data if there's at >>>>>>>>> least one >>>>>>>>> "unknown" table (i.e. plain SQL table) in the FROM clause. But that's >>>>>>>>> generally not desireable. >>>>>>>>> >>>>>>>>> Btw in a full jooq environment this is not an issue, but legacy >>>>>>>>>> code exists... >>>>>>>>> >>>>>>>>> >>>>>>>>> Sure, I perfectly understand this. >>>>>>>>> >>>>>>>>> I hope you've found my ideas useful. I'm sure there are other >>>>>>>>> ideas too, to detect this issue as early as possible. >>>>>>>>> >>>>>>>>> Lukas >>>>>>>>> >>>>>>>>> 2016-09-20 8:29 GMT+02:00 Denis Miorandi <denis.m...@gmail.com>: >>>>>>>>> >>>>>>>>>> hi Lukas, >>>>>>>>>> we have a mix of jooq (mine) and legacy non jooq (aka string >>>>>>>>>> queries) code in production. >>>>>>>>>> >>>>>>>>>> Sometimes happens that : >>>>>>>>>> >>>>>>>>>> let *T1* be a table and *C1,C2* it's column. >>>>>>>>>> let *JAPP* be a jooq app that use T1.C1 >>>>>>>>>> let *LAPP* a legacy APP that use T2.C2 >>>>>>>>>> let T1 be shared between *JAPP and LAPP* >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Suppose a developer on LAPP rename T1.C2==>T1.C3 and don't care >>>>>>>>>> about put in production jooq generated code. >>>>>>>>>> Now JAPP is broken cause when jooq generate query on whole table >>>>>>>>>> it generate >>>>>>>>>> create.select().from(T1) ==> SELECT FROM T1.C1, T1.C2, *but >>>>>>>>>> T1.C2 was renamed to T1.C3* >>>>>>>>>> >>>>>>>>>> The issue is that JAPP does not use directly T1.C2 in code so >>>>>>>>>> neither an automatic build could solve this problem due to dynamic >>>>>>>>>> nature >>>>>>>>>> of select fields. >>>>>>>>>> Probably the only compatible/working solution should be to >>>>>>>>>> generate select T1.*, but seems that jooq can not work in typesafe >>>>>>>>>> mode >>>>>>>>>> with select *. >>>>>>>>>> >>>>>>>>>> Probably I should better know the engine behavior, but I'm >>>>>>>>>> wondering about why jooq does not render T1.* instead exploding >>>>>>>>>> fields. >>>>>>>>>> Btw in a full jooq environment this is not an issue, but legacy >>>>>>>>>> code exists... >>>>>>>>>> Have you got any ideas how to avoid this issues? >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> 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. >>>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>> 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. >>>>>>>> >>>>>>> >>>>>>> -- >>>>>> 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. >>>>>> >>>>> >>>>> -- >>>> 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. >>>> >>> >>> -- >> 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+unsubscr...@googlegroups.com. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > 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/8PVbaU_qbAk/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > jooq-user+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- Dott. Denis Miorandi via dei Vignai 27 38060 - Nogaredo (TN) -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
