After adding generating ddl for views in 3.18 get an error during generate ddl for complexe view

Rosenrot48 commented 1 year ago

Expected behavior


select t.*, array_to_string(analyzer_models, '; ') as analyzer_models_search, array_to_string(t.test_tags,'; ') as test_tags_search
from
    (select
         ot.id,
         ot.catalog_id,
         olc.name as catalog,
         ot.is_active,
         ot.is_real,
         ot.updated_at,
         ot.analyte_id,
         ot.analyzer_id,
         ot.biomaterial_id,
         ot.reagent_id,
         gt.code::varchar(12) as guc_code,
         oat.code as analyte_code,
         oat.name as analyte_name,
         ob.name as biomaterial_name,
         case when ore.manufacturer is null then gmr.name else ore.manufacturer end  as reagent_manufacturer,
         case when ore.name is null then gr.full_name else ore.name end  as reagent_full_name,
         gat.full_name as guc_analyte_full_name,
         gb.full_name as guc_biomaterial_full_name,
         gmaz.name as guc_analyzer_manufacturer,
         gazf.name as guc_analyzer_family,
         gmr.name as guc_reagent_manufacturer,
         gr.full_name as guc_reagent_full_name,
         array (select oam.name from omni_catalog.omc_analyzer_analyzer_models oaam
                                         inner join omni_catalog.omc_analyzer_models oam on oam.id = oaam.model_id
                where oaam.analyzer_id = ot.analyzer_id ) as analyzer_models,
         array (select ott.name from omni_catalog.omc_test_test_tags ottt
                                         inner join omni_catalog.omc_test_tags ott on ott.id = ottt.tag_id
                where ottt.test_id = ot.id ) as test_tags

     from omni_catalog.omc_tests ot
              inner join omni_catalog.omc_local_catalogs olc on olc.id = ot.catalog_id
              inner join omni_catalog.omc_analytes oat on oat.id = ot.analyte_id
              inner join omni_catalog.omc_analyzers oaz on oaz.id = ot.analyzer_id
              inner join omni_catalog.omc_biomaterials ob on ob.id = ot.biomaterial_id
              inner join omni_catalog.omc_reagents ore on ore.id = ot.reagent_id
              inner join unified_catalog.guc_analytes gat on gat.id = oat.guc_id
              inner join unified_catalog.guc_analyzers gaz on gaz.id = oaz.guc_id
              inner join unified_catalog.guc_biomaterials gb on gb.id = ob.guc_id
              inner join unified_catalog.guc_reagents gr on gr.id = ore.guc_id
              left join unified_catalog.guc_tests gt
                        on gt.analyte_id = oat.guc_id and gt.analyzer_id = oaz.guc_id
                            and  gt.biomaterial_id = ob.guc_id and gt.reagent_id = ore.guc_id
              inner join unified_catalog.guc_manufacturers gmr on gmr.id = gr.manufacturer_id
              inner join unified_catalog.guc_manufacturers gmaz on gmaz.id = gaz.manufacturer_id
              inner join unified_catalog.guc_analyzer_families gazf on gazf.id = gaz.family_id) t
;

Actual behavior

Exception during generating ddl for complex view

Steps to reproduce the problem

error.txt

ADD jooq 3.18 building jar to project. take main branch with fixing issue (https://github.com/jOOQ/jOOQ/issues/9818)
Execute java code
Get an error Source code of demo available on https://github.com/Rosenrot48/jooq-ddl/tree/view_ddl_issue

jOOQ Version

JOOQ 3.18 build

Database product and version

PostgreSQL 13.3 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 11.0.3 (clang-1103.0.32.62), 64-bit

Java Version

graalvm-ce-17

OS Version

macos 13.0

JDBC driver name and version (include name if unofficial driver)

org.postgresql:postgresql:42.5.4

lukaseder commented 1 year ago

Thanks for your report.

While we can't support all the possible functions in our parser, such an error shouldn't have this kind of effect on an API call that may not necessarily require parsing. Will have to check why we needed it in the first place.

I'll investigate this, soon.

lukaseder commented 1 year ago

OK, the main problem is that:

Some RDBMS contain view sources including the CREATE VIEW prefix
Others omit the CREATE VIEW prefix
Some qualify the view with the schema
Others omit the schema

Without being able to parse the view entirely, it will be difficult to standardise over the contents.

The expectation might be to leave the source as it is, without parsing/translating it, though? What is your expectation of this API?

Rosenrot48 commented 1 year ago

@lukaseder Hi and thanks for looking for solutions for my issue!

The expectation might be to leave the source as it is, without parsing/translating it, though? What is your expectation of this API?

My expectation is pretty simple, I just need a solution for migration from one database AS IS to another. So yes, my expectation looks like you described - I need the source as it is.

And if it would be possible to get view DDL without processing it - this would be a best solution in my case.

If you already have such method in API (that give DDL information without parsing/translating) - please share to me a link.

lukaseder commented 1 year ago

Use https://www.postgresql.org/docs/current/app-pgdump.html and pgrestore in that case...

Rosenrot48 commented 1 year ago

I know about that. But in my case I have to migrate from absolutely different rdbms. It could be MySQL, MS Server and etc. And JOOQ looks useful for me

lukaseder commented 2 weeks ago

I created this view:

create view v (s) as select array_to_string(array[1, 2], ',');

Then ran this code:

var queries = ctx.meta().filterTables(t -> t.getName().equals("v")).ddl();
for (Query query : queries) {
    println(query);
}

There is no more exception and the output is as expected:

create view "public"."v"("s")
as
SELECT array_to_string(ARRAY[1, 2], ','::text) AS s;

This is in the info logs:

14:29:34,189  INFO [DDL                           ] - ParserException          : Cannot parse source of: "public"."v". Source: CREATE VIEW "v" AS  SELECT array_to_string(ARRAY[1, 2], ','::text) AS s;
14:29:34,194  INFO [DDL                           ] - Cannot parse view source (to skip parsing, use Settings.parseMetaViewSources): CREATE VIEW "v" AS  SELECT array_to_string(ARRAY[1, 2], ','::text) AS s;
org.jooq.impl.ParserException: Unknown function: [1:44] CREATE VIEW "v" AS  SELECT array_to_string([*]ARRAY[1, 2], ','::text) AS s;
    at org.jooq.impl.DefaultParseContext.exception(ParserImpl.java:16004) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseUnaryOps(ParserImpl.java:8733) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseExp(ParserImpl.java:8685) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseFactor(ParserImpl.java:8662) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseSum(ParserImpl.java:8603) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseOp(ParserImpl.java:8547) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseCollated(ParserImpl.java:8392) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseConcat(ParserImpl.java:8373) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parsePredicate(ParserImpl.java:7060) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseNot(ParserImpl.java:6866) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseAnd(ParserImpl.java:6856) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseXor(ParserImpl.java:6847) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseOr(ParserImpl.java:6838) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseFieldOrRow(ParserImpl.java:8284) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseSelectField(ParserImpl.java:8257) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseSelectList(ParserImpl.java:8194) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseQueryPrimary(ParserImpl.java:1847) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseQueryTerm(ParserImpl.java:1751) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseQueryExpressionBody(ParserImpl.java:1717) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseSelect(ParserImpl.java:1458) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseWithOrSelect(ParserImpl.java:1449) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseWithOrSelect(ParserImpl.java:1445) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseCreateView(ParserImpl.java:4334) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseCreate(ParserImpl.java:3059) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parseQuery(ParserImpl.java:1175) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.lambda$2(ParserImpl.java:945) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.wrap(ParserImpl.java:16197) ~[classes/:?]
    at org.jooq.impl.DefaultParseContext.parse(ParserImpl.java:933) ~[classes/:?]
    at org.jooq.impl.ParserImpl.parse(ParserImpl.java:811) ~[classes/:?]
    at org.jooq.impl.ParserImpl.parse(ParserImpl.java:806) ~[classes/:?]
    at org.jooq.impl.DDL.applyAs(DDL.java:229) [classes/:?]
    at org.jooq.impl.DDL.createTableOrView(DDL.java:161) [classes/:?]
    at org.jooq.impl.DDL.queries(DDL.java:574) [classes/:?]
    at org.jooq.impl.AbstractMeta.ddl(AbstractMeta.java:794) [classes/:?]
    at org.jooq.impl.AbstractMeta.ddl(AbstractMeta.java:787) [classes/:?]
    at org.jooq.testscripts.JDBC.main(JDBC.java:35) [test-classes/:?]

This has been fixed via:

15238

Closing this as a duplicate

jOOQ / jOOQ