So my exact circumstances are pretty convoluted. I think good equivalent would be "I get a json file with new fields, put the fields in the DB". In my case I also know type information so it's a little bit more reasonable.
On Monday, October 12, 2020 at 1:40:02 PM UTC-7 [email protected] wrote: > I’m curious about the actual context in which such a comparison is being > performed. In particular, is the comparison between two active databases > across separate connections?, separate vendors?. Or is this a check that > the jooQ connected db matches some static definition? > > > On Oct 12, 2020, at 11:54 AM, Megan A <[email protected]> wrote: > > Yes! That's what I wanted. I must have been confused because to do this > manually I had to query the "INFORMATION_SCHEMA" table. > > In the grand scheme of things though what I really need is this: > https://blog.jooq.org/category/migrations/ > > So I can do to something like this: > > ctx.meta( > "create table t (i int)" > ).diff(ctx.meta( > "create table t (" > + "i int not null, " > + "j int null, " > + "primary key (i))" > )) > > But it looks like that is still in development. So I have to look at what > columns are in the DB and add the ones that are missing. > > On Monday, October 12, 2020 at 9:53:40 AM UTC-7 [email protected] wrote: > >> Hi Megan, >> >> Thanks for reporting those NPE. I'll look into this ASAP. There should >> obviously be more meaningful error messages. >> >> In any case, what are you trying to do with those method calls? >> DSLContext::informationSchema turns jOOQ meta data into the JAXB annotated >> information schema format, which can be exported as XML. Since you're >> calling InformationSchema::getColumns on it, I'd say you might be more >> interested in calling something like >> >> for (Table table : context.meta().getTables("MY_TABLE_NAME")) { >> Field<?>[] fields = table.fields(); >> } >> >> >> On Mon, Oct 12, 2020 at 6:25 PM Megan A <[email protected]> wrote: >> >>> Hi Lukas, >>> >>> So I've run into an NPE when doing the following: >>> >>> context.informationSchema(table(name("MY_TABLE_NAME"))).getColumns(); >>> >>> The NPE seems to be coming from lines 98 -99 in InformationSchemaExport >>> >>> for (Schema s : includedSchemas) >>> includedCatalogs.add(s.getCatalog()); >>> >>> If I force in a schema: >>> >>> context.informationSchema(table(name("MY_HARD_CODED_SCHEMA", >>> "MY_TABLE_NAME"))).getColumns(); >>> >>> I get an NPE just a few lines later. >>> >>> for (Catalog c : includedCatalogs) >>> exportCatalog0(result, c); >>> >>> Are my Settings (above) incorrect? Changing "render catalog" above does >>> nothing to change the behavior? >>> >>> I have the same issues using meta(). >>> >>> Thanks :) >>> >>> >>> On Thursday, October 8, 2020 at 12:53:32 AM UTC-7 [email protected] >>> wrote: >>> >>>> Hi Megan, >>>> >>>> DSL.table(String) is part of the plain SQL templating APIs, whereas >>>> DSL.table(Name) constructs a table from an identifier. The purpose of the >>>> plain SQL templating API is to be able to construct templates for features >>>> that jOOQ doesn't support, such as e.g. >>>> >>>> context.selectFrom(table("some_table_valued_function(x <fancy_operator> >>>> y)")).fetch(); >>>> >>>> >>>> More information about plain SQL templating can be found here: >>>> https://www.jooq.org/doc/latest/manual/sql-building/plain-sql/ >>>> >>>> https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/ >>>> >>>> We cannot assume that the template is simply an identifier. And because >>>> we don't know what's in there, we cannot parse it either, we might not >>>> understand its syntax. Now, there are some APIs, mainly the DDL APIs, >>>> where >>>> templates never make sense. In those cases, the String overloads of the >>>> DSL >>>> correspond to passing a Name instance, not a template (org.jooq.SQL) >>>> instance. >>>> >>>> This blog post here wraps it up nicely. What’s a “String” in the jOOQ >>>> API? >>>> https://blog.jooq.org/2020/04/03/whats-a-string-in-the-jooq-api/ >>>> >>>> Regarding your questions: >>>> >>>> 1. There is actually no schema set by default. So the replacement was >>>>> trying to match "empty", is there a better way of doing this? >>>>> >>>> >>>> You can also match the empty string, using >>>> >>>> new MappedSchema().withInput("").withOutput("x") >>>> >>>> >>>> In your case, the mapping will obviously apply to all schemas, which >>>> might not be desired depending on how you aim to implement this. Of >>>> course, >>>> if you're constructing identifiers throughout your application, there >>>> might >>>> be a way to abstract over these identifiers already from within your >>>> application and automatically add the right schema name to the identifier, >>>> e.g. by using >>>> >>>> table(name(schemaName, tableName)) >>>> >>>> >>>> >>>>> 2. Is this the best way to get support? We have a licence and are >>>>> using SQLServer and I'm having issues around temp tables. >>>> >>>> >>>> This is a good way to get support. A lot of people read this mailing >>>> list, and if you have a question that is more about how to best put jOOQ >>>> into use in some scenarios, there might be someone who has an idea of how >>>> they built things. Other alternatives for community support (where we also >>>> reply) are: >>>> >>>> - Stack Overflow https://stackoverflow.com/questions/tagged/jooq >>>> - Github https://github.com/jOOQ/jOOQ/issues/new/choose >>>> - Reddit https://www.reddit.com/r/jOOQ >>>> >>>> An advantage of the above community support channels is that it's >>>> public, so as in your case, a coworker can continue the discussion easily. >>>> >>>> You can also send an email to our commercial support email address, >>>> which gives you access to our professional support. Benefits of that are >>>> guaranteed reaction times, although we usually respond quite fast on the >>>> community support channels as well, and 1:1 support, in case you need to >>>> share confidential information, for which the community support channel is >>>> not a good place. >>>> >>>> Pick the one that you feel suits you the most. >>>> >>>> On Wed, Oct 7, 2020 at 8:06 PM Megan A <[email protected]> wrote: >>>> >>>>> Hi Lukas, >>>>> >>>>> I'm working on this issue. Thanks to Greg for starting his >>>>> conversation. I switched from: >>>>> >>>>> context.mergeInto(table(tableName), columnNames) >>>>> >>>>> to >>>>> >>>>> context.mergeInto(table(*name*(tableName)), columnNames) >>>>> >>>>> And it seems to be working now! Is there a reason table() would not >>>>> do a name()? >>>>> >>>>> Two things: >>>>> >>>>> 1. There is actually no schema set by default. So the replacement was >>>>> trying to match "empty", is there a better way of doing this? >>>>> 2. Is this the best way to get support? We have a licence and are >>>>> using SQLServer and I'm having issues around temp tables. >>>>> >>>>> Thanks so much for your help, >>>>> >>>>> Megan >>>>> >>>>> On Wednesday, October 7, 2020 at 12:48:33 AM UTC-7 [email protected] >>>>> wrote: >>>>> >>>>>> Hi Greg, >>>>>> >>>>>> Thanks for your message. There's nothing wrong with your setup. This >>>>>> should work for DDL and DML alike. There is one caveat I can think of, >>>>>> though. How do you create your DML statements? If you're using plain SQL >>>>>> templating API ( >>>>>> https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/), >>>>>> >>>>>> then schema mapping does not apply, because we don't parse the >>>>>> templates. >>>>>> You can recognise plain SQL templating API as it is annotated with >>>>>> org.jooq.PlainSQL. In that case, you probably should use DSL.name() to >>>>>> construct your identifiers instead. >>>>>> >>>>>> Note, I would recommend using "^.*$" as a pattern to prevent >>>>>> generating MY_SCHEMAMY_SCHEMA as can be shown here: >>>>>> >>>>>> >>>>>> jshell> "abc".replaceAll(".*+", "xyz") >>>>>> $1 ==> "xyzxyz" >>>>>> >>>>>> jshell> "abc".replaceAll("^.*$", "xyz") >>>>>> $2 ==> "xyz" >>>>>> >>>>>> >>>>>> Thanks, >>>>>> Lukas >>>>>> >>>>>> On Wed, Oct 7, 2020 at 7:03 AM Greg Nielsen <[email protected]> >>>>>> wrote: >>>>>> >>>>>>> Hello, >>>>>>> >>>>>>> Using jOOQ Professional, we are writing to a SQL Server instance. >>>>>>> We're finding that only the DDL statements (eg create tables) seem to >>>>>>> honor >>>>>>> the MY-SCHEMA mapped schema, while the DML (eg inserts) is not. >>>>>>> >>>>>>> We're initializing our Settings object with: >>>>>>> >>>>>>> >>>>>>> @Bean >>>>>>> public Settings settings() { >>>>>>> return new Settings() >>>>>>> .withRenderCatalog(true) >>>>>>> .withRenderSchema(true) >>>>>>> .withRenderMapping(new RenderMapping() >>>>>>> .withSchemata(new MappedSchema() >>>>>>> .withInputExpression(Pattern.compile(".*+")) >>>>>>> .withOutput("MY_SCHEMA")) ); >>>>>>> } >>>>>>> I believe we're runing 3.13 - is there an additional setting we need >>>>>>> to be configuring? >>>>>>> >>>>>>> Let me know what other info might be helpful. >>>>>>> >>>>>>> Thanks, >>>>>>> Greg >>>>>>> >>>>>>> -- >>>>>>> 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]. >>>>>>> To view this discussion on the web visit >>>>>>> https://groups.google.com/d/msgid/jooq-user/f1597c43-6dc6-41d8-afc5-a957d9cd4b76n%40googlegroups.com >>>>>>> >>>>>>> <https://groups.google.com/d/msgid/jooq-user/f1597c43-6dc6-41d8-afc5-a957d9cd4b76n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>>> . >>>>>>> >>>>>> >>>>> -- >>>>> 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]. >>>>> >>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/jooq-user/e5ce7303-78c4-4066-b618-1d2ef1123ad7n%40googlegroups.com >>>>> >>>>> <https://groups.google.com/d/msgid/jooq-user/e5ce7303-78c4-4066-b618-1d2ef1123ad7n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> >>> -- >>> 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]. >>> >> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/jooq-user/b747ddcb-cdf3-4f90-9c53-86b3c1f1cc82n%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/jooq-user/b747ddcb-cdf3-4f90-9c53-86b3c1f1cc82n%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> > -- > 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]. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/465bb79b-f1e9-414c-acdd-e182218dcb3an%40googlegroups.com > > <https://groups.google.com/d/msgid/jooq-user/465bb79b-f1e9-414c-acdd-e182218dcb3an%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/f53269b8-9351-4d93-9805-31107e64c30en%40googlegroups.com.
