Add support for { CREATE | ALTER | DROP } MATERIALIZED VIEW

[lukaseder]

We currently cannot CREATE, ALTER, or DROP materialized views. Supporting these statements is a prerequisite for being able to export them using Meta.ddl(): https://github.com/jOOQ/jOOQ/issues/6412

Tasks

• [x] Support statements in API
  • [x] CREATE VIEW
  • [x] ~OR REPLACE~ (this isn't supported natively anywhere, except in Snowflake). Will do later: https://github.com/jOOQ/jOOQ/issues/15593
  • [x] IF NOT EXISTS
  • [x] ALTER VIEW
  • [x] IF EXISTS
  • [x] DROP VIEW
  • [x] IF EXISTS
  • [x] COMMENT ON MATERIALIZED VIEW
  • Subtask: https://github.com/jOOQ/jOOQ/issues/15607
  • Derived task: https://github.com/jOOQ/jOOQ/issues/15606
• [x] Add parser support
• [x] Add interpreter support
  • [x] Add DDL export support
• [x] Add documentation
• [x] Meta model support
  • [x] Code generation
  • [x] Sources
  • [x] MetaImpl
  • [x] Might need MetaSQL support for some dialects
  • [x] Updated source query for:
    • [x] Oracle
    • [x] PostgreSQL
  • [x] Use source query as a hint for a TABLE being a materialized view (as DatabaseMetaData::getTables reports TABLE, instead of MATERIALIZED VIEW) for:
    • [x] Oracle
  • [x] XML / InformationSchema
  • [x] XMLGenerator
  • [x] XMLDatabase
  • [x] Interpreter
  • [x] Diff
• [x] Dialect support:
  • [x] CockroachDB: https://www.cockroachlabs.com/docs/stable/views#materialized-views
  • [x] Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-MATERIALIZED-VIEW.html#GUID-EE262CA4-01E5-4618-B659-6165D993CA1B
  • [x] PostgreSQL: https://www.postgresql.org/docs/current/sql-creatematerializedview.html
  • [x] Redshift: https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-create-sql-command.html
  • [x] Snowflake: https://docs.snowflake.com/en/sql-reference/sql/create-materialized-view
  • [x] Sybase SQL Anywhere: https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbreference/create-materialized-view-statement.html
  • [x] YugabyteDB: https://docs.yugabyte.com/preview/api/ysql/the-sql-language/statements/ddl_create_matview/

Workarounds for limitations:

• https://github.com/jOOQ/jOOQ/issues/15612

Dialects with similar features, which are out of scope for now:

• SQL Server: https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16&redirectedfrom=MSDN
• SQL Data Warehouse: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-materialized-view-as-select-transact-sql?view=azure-sqldw-latest

Dialects with buggy implementations:

• H2: http://h2database.com/html/commands.html#create_materialized_view
  • H2 isn't ready yet https://github.com/h2database/h2database/issues/3897

Future work

• This task will explicitly exclude any vendor specific storage clauses, which are probably of interest to some folks, but out of scope for now.
• Refreshment is probably interesting to many users, but will be done separately: https://github.com/jOOQ/jOOQ/issues/15533
• More dialect support: https://github.com/jOOQ/jOOQ/issues/15744

[dharrigan]

Would it be possible to also support refresh (admittingly I think just a PostgreSQL feature)

https://www.postgresql.org/docs/11/sql-refreshmaterializedview.html

i.e.,

refresh materialized view FOO with data refresh materialized view FOO with no data

and maybe

refresh concurrently materialized view FOO ....

Presently I'm using raw SQL to do it, which is fine :-) Just if you're in that area, perhaps supporting refresh might be doable too :-)

Thank you.

[lukaseder]

@dharrigan Thanks for your suggestion. We could consider this, but it's unlikely this will be prioritised any time soon. There are more compelling syntaxes to support before this one...

[dharrigan]

No problem :-) As mentioned, having the ability to use raw sql is totally fine to accomplish this...you have a lot on your plate anyway :-) Maybe one day...

thank you.

[lukaseder]

I'll move REFRESH to its own issue:

• https://github.com/jOOQ/jOOQ/issues/15533

Will look into this issue (without REFRESH) for jOOQ 3.19