How to create many databases from one set of generated files

[Jim McGlaughlin]

My situation is similar to trying to manage many Book Stores by creating a unique database for each new bookstore with a unique database name for each book store.

To create my generated classes I upload my vcs maintained bookstore_database.sql file and then run jooq's generate utility.  (That file does not have a USE statement)

I did not realize that in the generated files the generated bookstore_database.java file contains ...

    /**
     * No further instances allowed
     */
    private  bookstore_database () {
        super(" bookstore_database ", null);
    }

I create new book store databases using a command line routine that redirects the base sql file to a new bookstore database each time a new book store is added.

During initialization I could not figure out why insert/store statements were returning counts but no records were in the database, until I saw they were being added to the originally named bookstore_database instead of chicago_bookstore.

I am not sure if removing the private statement above would resolve the situation or there is a better way to achieve the desired many identical schema'd databases.

I appreciate any thoughts or suggestions.

Jim McGlaughlin, NH, USA

[Lukas Eder]

Hi Jim,

You can use the schema mapping features. In the code generator:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-catalog-and-schema-mapping

At runtime:

https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-render-mapping

This allows you to either remove the schema from being included in the generated SQL string, or to qualify your tables with the appropriate schema.

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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/6fde8f84-9f3a-44f0-9c29-c005e3ca298cn%40googlegroups.com.

[Jim McGlaughlin]

THANK YOU SO MUCH Lukas,

I will endeavor to understand creating new databases at runtime as users add new 'stores'.  It's great this capability is provided for.

Thank you for the work you have put into JOOQ over the many years and for still being so responsive !!

Jim

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/wOQceqi-KlU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7e6Zuaf3RG6f-EPGKV5gvmhagNyeVs55scvhvAnxiPSg%40mail.gmail.com.

[Lukas Eder]

You're welcome, Jim.

Fun fact, schema mapping has been one of jOOQ's first features, which was added about 10 years ago :) Seems like such a low hanging fruit when making a query builder.

Best,
Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAAheHVC3K6VktEGsPSghGZaZvvYQunpAghp4SkZDb%3DpjeVkJuw%40mail.gmail.com.