Have you considered Liquibase? It provides an abstraction from SQL, with an escape hatch to write custom scripts. It may be easier to generate a changelog document from your inferred schema (and unit test it with H2), injecting custom SQL for a dialect as needed.
On Wednesday, May 20, 2015 at 10:12:23 PM UTC-7, Sujeevan Nagarajah wrote: > > Thank you Lukas. Now I understand the primary use case of jOOQ createTable > feature. > > Typical application has a fixed schema or schema that rarely changes. Also > when there is a change, schema changes applied during application start. > But my use case is totally different from it. I'm working on a feature > where schema of a dataset is detected and corresponding table is created in > run-time. Since the schema is generated programatically in runtime, I'm > looking for some kind of Java DSL library like jOOQ createTable. Obviously > Flyway can be useful here to manage the schema generated by DSL but it > doesn't help to generate it. What would you recommend for my use-case? An > idea I have is to do some String manipulation on the table statement result > generated by jOOQ. > > Thank you. > > On Wednesday, May 20, 2015 at 11:24:13 PM UTC+8, Lukas Eder wrote: >> >> Hello Sujee, >> >> Thanks for your enquiry. The main reason why jOOQ supports DDL statements >> is the fact that developers may want to quickly reproduce a simplified >> version of their schema on a test database like H2, for instance. The >> workflow would be : >> >> - Write DDL for Oracle >> - Generate jOOQ classes from your DDL >> - Generate DDL for H2 and execute it >> - Run integration tests using jOOQ classes on H2 instead of Oracle >> >> We cannot go beyond the above, because each vendor has a very rich set of >> syntax extensions to the SQL standard, involving storage clauses, hints, >> flags, etc. While we could add support for the keywords you've mentioned >> (DISTKEY, SORTKEY, DISTSTYLE), you would be quickly missing other keywords >> later on, again. >> >> This is why we generally suggest using something like Flyway for database >> migrations and DDL management. Since Flyway is a SQL template based API, it >> will not suffer from these limitations. >> >> More information can be found here: >> http://www.jooq.org/doc/latest/manual/sql-building/ddl-statements >> >> I hope this helps, >> Lukas >> >> 2015-05-20 16:53 GMT+02:00 Sujeevan Nagarajah <[email protected]>: >> >>> Hello, >>> I am using jOOQ 3.7.0-SNAPSHOT to generate Redshift CREATE TABLE >>> statement. I'm able to successfully generate simple table statement. >>> Currently I'm looking for a way to add Redshift specific keywords like >>> DISTKEY, SORTKEY, DISTSTYLE, ..etc ( >>> http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html). >>> But I couldn't find any methods related to them. Can someone advice me >>> on how to specify them? >>> >>> My code currently looks something like below, >>> >>> DSL.using(SQLDialect.REDSHIFT).createTable(name("schemaName", >>> "tableName")).column("sales", RedshiftDataType.DECIMAL.precision(19, >>> 3)).column("product", RedshiftDataType.VARCHAR).toString(); >>> >>> Thank you. >>> >>> Best Regards, >>> Sujee >>> >>> -- >>> 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]. For more options, visit https://groups.google.com/d/optout.
