GeezerVison

Purpose

Fix a regression from #2700: every PostgreSQL ALTER DATABASE now throws a ParseException, for example ALTER DATABASE mydb OWNER TO app or ALTER DATABASE mydb SET search_path TO app, public. JSqlParser 5.4 returned an UnsupportedStatement for them. Without a dialect, MySQL's ALTER SCHEMA db DEFAULT ENCRYPTION = 'Y' regressed the same way.

While here, give the database options the same typed treatment as the MySQL ones:

• PostgreSQL CREATE DATABASE options are one opaque OTHER option.
• The MySQL CHARSET spelling is not recognized, and CREATE SCHEMA db DEFAULT CHARSET = utf8mb4 fails.
• Every parsed CREATE DATABASE ends with an empty raw option, which adds a trailing space to toString().

Current design (As-is)

• Alter() sends every ALTER DATABASE to AlterDatabase(), which accepts only MySQL options. Without the MySQL dialect, ALTER SCHEMA goes to the PostgreSQL AlterSchema(), which accepts only RENAME and OWNER.
• AlterRole stores PostgreSQL's SET/RESET of a configuration parameter in six fields: action, parameter, values, useEquals, fromCurrent and useDefault. FROM CURRENT and DEFAULT can both be set; rendering picks the first.
• CreateDatabase keeps two representations: the option nodes, and a flat token list computed when the options are set. captureRest() always appends a raw option, even an empty one.

Target design (To-be)

• Add ConfigurationParameterChange in net.sf.jsqlparser.statement for SET p {TO|=} values|DEFAULT, SET p FROM CURRENT and RESET p|ALL:
  • Action (SET, RESET), the parameter name, ValueKind (VALUES, DEFAULT, FROM_CURRENT), useEquals and a mutable ExpressionList of values;
  • one grammar production, used by ALTER ROLE and ALTER DATABASE; ALTER FUNCTION/PROCEDURE can reuse both.
• AlterRole stores a ConfigurationParameterChange. Its existing parameter getters and setters remain and read or edit that change.
• AlterDatabase gains an Action: OPTIONS (the default), RENAME, OWNER, SET_TABLESPACE, REFRESH_COLLATION_VERSION and CONFIGURATION_PARAMETER, each with its own payload. OPTIONS takes PostgreSQL's [WITH] ALLOW_CONNECTIONS | CONNECTION LIMIT | IS_TEMPLATE ... as well as the MySQL options. PostgreSQL forms require a database name; MySQL options may still omit it.
• DatabaseOption gets one kind per PostgreSQL option: OWNER, TEMPLATE, ENCODING, STRATEGY, LOCALE, LC_COLLATE, LC_CTYPE, BUILTIN_LOCALE, ICU_LOCALE, ICU_RULES, LOCALE_PROVIDER, COLLATION_VERSION, TABLESPACE, ALLOW_CONNECTIONS, CONNECTION_LIMIT, IS_TEMPLATE and OID. Values keep their SQL text. CREATE DATABASE and ALTER DATABASE share the production for these options.
• MySQL CHARACTER SET, CHAR SET and CHARSET are all CHARACTER_SET; getName() keeps the spelling. This also fixes CREATE SCHEMA ... DEFAULT CHARSET.
• CreateDatabase keeps one live option list and an isUseWith() flag. Other option text remains one raw option, appended only if present. getDatabaseOptions() is derived from the options.
• Dispatch:
  • With Dialect.MYSQL, ALTER DATABASE/SCHEMA always parses as AlterDatabase, as before.
  • Otherwise, a syntactic lookahead selects AlterDatabase only for forms it models. Other ALTER DATABASE forms, such as SQL Server's SET RECOVERY SIMPLE, return to UnsupportedStatement as in 5.4.
  • ALTER SCHEMA followed by MySQL options parses as AlterDatabase in every dialect. PostgreSQL's ALTER SCHEMA ... RENAME/OWNER remains an AlterSchema.
• No new reserved words; option words are matched as identifiers.
• Wire the changes into StatementDeParser (SET values go through the expression deparser) and into validation: StatementValidator checks the new Feature.alterDatabase. The MySQL, MariaDB and PostgreSQL capabilities now enable createDatabase and alterDatabase; no capability enabled createDatabase before. Document the API in the usage guide.

Compatibility

• AlterRole keeps all public accessors, and its SQL output is unchanged. FROM CURRENT and DEFAULT are now mutually exclusive: setFromCurrent(false) and setUseDefault(false) return to the explicit values.
• CreateDatabase.getOptions() is never null and returns the live list; setOptions copies its argument.
• getDatabaseOptions() is now an unmodifiable snapshot that includes WITH, and still null without options. Previously, edits to it were silently ignored for parsed statements. addDatabaseOptions now appends a raw option instead of turning every option into raw tokens.
• The empty raw option and the trailing space are gone.

Validation

• New and extended tests (65 cases) cover:
  • parse, toString, deparse and reparse with Dialect.POSTGRESQL, with Dialect.MYSQL and without a dialect, plus statement boundaries in scripts;
  • the AST of every action and option kind, including signed, boolean, string and DEFAULT values;
  • CHARSET as an option keyword and as a database name;
  • unmodelled forms: UnsupportedStatement without a dialect and with PostgreSQL, an error with MySQL; PostgreSQL ALTER SCHEMA unchanged;
  • AlterRole and AlterDatabase sharing one ConfigurationParameterChange, and the legacy AlterRole accessors and setters;
  • CreateDatabase list ownership and the legacy token methods;
  • validation features and statement feature analysis.
• Java 17 offline Gradle spotlessApply check: 10,425 tests, 10,400 passed, 25 skipped, zero failures/errors. The grammar choice conflict check passes.
• Compared with master (bdf8ac31) on a corpus of 635 MySQL 8.4 and PostgreSQL 18 DDL/DCL statements, each accepted by the server, plus variants:
  • Fixed with Dialect.POSTGRESQL and without a dialect: ALTER DATABASE ... SET/RENAME TO/OWNER TO/RESET ALL/WITH ALLOW_CONNECTIONS, SET work_mem = '64MB' and OWNER TO CURRENT_USER.
  • FIXED: MySQL CREATE SCHEMA ... DEFAULT CHARSET and, without a dialect, ALTER SCHEMA ... DEFAULT ENCRYPTION.
  • No regressions; no other parse results change.
  • In the AST, PostgreSQL CREATE DATABASE options and DEFAULT CHARSET are typed, the empty raw option is gone, and ALTER ROLE SET/RESET moves into parameterChange. Rendered SQL is unchanged except for the removed trailing space.
• MySQL 8.4 and PostgreSQL 18.3: every corpus statement this branch parses ran as written on one server and as rendered by toString() on a second, identical server, in the same order. All 206 MySQL and 332 PostgreSQL statements give the same result (success, or the same error).

Integration validation

Applied #2806–#2819 together on master (bdf8ac31), with the stacked PRs on top of their bases (#2806 → #2807 → #2808, #2806 → #2809 → #2810 and #2811 → #2812).

• Each branch merges cleanly into master. Between the branches, conflicts are limited to adjacent additions (README/index rows, usage sections, imports, visitor and feature lists, one enum line and one alias guard).
• The integrated build passes Java 17 offline Gradle spotlessApply check: 11,494 tests, 11,469 passed, 25 skipped, zero failures/errors. The grammar choice conflict check passes.
• On a corpus of 635 MySQL 8.4 and PostgreSQL 18 DDL/DCL statements, each accepted by the server, the integrated build parses 265 of 271 MySQL statements (master: 206) and 363 of 364 PostgreSQL statements (master: 328). Every MySQL 8.4 non-reserved word and PostgreSQL 18 unreserved or column-name keyword parses as a column and table name.
• On paired MySQL 8.4 and PostgreSQL 18.3 servers, all 263 MySQL and 362 PostgreSQL statements rendered by the integrated build give the same results as the originals.