Schema migration support for CREATE ALTER DROP in sql-psi

[griffio]

Opening this discussion topic in 🍔 SqlDelight (actually refers to sql-psi)

Problem

How to expand schema migration support?

I have been seeing/implementing a number of issues related to PostgreSql dialect, where adding just CREATE is only partially useful

Given that some schema DDL is not a standard and that these are mostly vendor extensions* - reference SQL in a Nutshell, 4th Edition
*CREATE has many variants in Postgres not listed below (ROLE, GROUP etc)

Current issues/support in SqlDelight/Sql-Psi

	CREATE	ALTER	DROP
DATABASE
DOMAIN	149
EXTENSION	4541
INDEX	✅		✅
SCHEMA	3761
SEQUENCE	4528
TABLE	✅	✅	✅
TRIGGER	✅		✅
TYPE	535
USER	3865
VIEW	✅		✅

It seems difficult or not possible to override the base stmt in dialects

stmt ::= [ EXPLAIN [ QUERY PLAN ] ] ( extension_stmt | alter_table_stmt | analyze_stmt | attach_stmt | begin_stmt | 
commit_stmt | create_index_stmt | create_table_stmt | create_trigger_stmt | create_view_stmt |
create_virtual_table_stmt | delete_stmt_limited | detach_stmt | drop_index_stmt | drop_table_stmt | 
drop_trigger_stmt |  drop_view_stmt | insert_stmt | pragma_stmt | reindex_stmt | release_stmt | rollback_stmt | 
savepoint_stmt | compound_select_stmt | update_stmt_limited | vacuum_stmt) {
  recoverWhile=sql_stmt_recovery
  pin = 2
}

Currently the extension_stmt rule in sql-psi is the only escape hatch 🐥 to add CREATE | ALTER | DROP rules

Also, having explicit statements allows the ordering of DDL - this is needed for correct database generation.
e.g CREATE TYPE statements must be executed before using the new type in CREATE TABLE see sqldelight/sql-psi#535 (comment).

🕊️ Is a solution to add most the possible statements to the sql core grammar, just as abstract cases, and let the dialects be responsible to override - this is the same as extension_stmt does ?

It would allow to support and extend more dialects like CockRoachDB and MariaDB

Private custom dialects can refine these as needed see #4541
e.g CREATE EXTENSION would require a specific dialect to contain the functions that the extension imports 👿 - these third party dialects could be shared