sql-parser-cst

Breaking changes

• Use TriggerEventExpr instead of plain TriggerEvent (d5ca16d)
• Extract timeKw from TriggerEvent (9e0d309)
• Extract TriggerTarget from TriggerEvent (d37a94b)
• Rename TriggerCondition to WhenClause (7cd0758)
• Replace CreateTriggerStmt.eachKw and .when fields with .clauses (56a69b4)
• Don't parse unicode escapes in PostgreSQL unicode strings and identifiers (f715a15)

Improved PostgreSQL support

• Support FILTER() in aggregate functions
• Revert an accidental change to row_constructor syntax
• Support PostgreSQL TRIGGER syntax:
  • Support CREATE TRIGGER
  • Support REFERENCING clause in TRIGGER
  • Support trigger timing clause
  • Support trigger FROM clause
  • Support FOR EACH clause
  • Support TRUNCATE event
• Support ALTER PROCEDURE & ALTER FUNCTION
• Support newline-separated strings, E'strings' and U&'strings'
• Support UESCAPE in unicode strings & identifiers
• Support underscores in number literals
• Update keywords list for PostgreSQL 16 (add SYSTEM_USER)

New parameter type: $nr

• The paramTypes option now accepts "$nr", to match parameters in the form of $1, $2, $3

Breaking changes

• Various changes to AlterActionAddConstraint node to support BigQuery ADD CONSTRAINT IF NOT EXISTS syntax (72dadd1)
• Moved ReindexStmt from AllSqliteNodes to AllIndexNodes (79b68c2)
• Moved IndexSpecification from AllConstraintNodes to AllIndexNodes (2ba172f)
• Moved IndexIncludeClause from AllConstraintNodes to AllIndexNodes to support INCLUDE (...) in CREATE INDEX (bde541a)
• CreateIndexStmt.name field is now optional (e90647d)
• The CreateViewStmt.columns field now contains list of ColumnDefinition nodes instead of just list of Identifier nodes. (1ba2459)

Support for latest BigQuery (as of 31. January 2024)

• PRIMARY KEY constraint
• FOREIGN KEY constraint
• ALTER TABLE .. DROP PRIMARY KEY
• ALTER TABLE .. DROP CONSTRAINT
• NOT ENFORCED modifier in PRIMARY/FOREIGN KEY
• ALTER TABLE .. ADD CONSTRAINT IF NOT EXISTS
• ALTER VIEW .. ALTER COLUMN .. SET OPTIONS()
• OPTIONS() in CREATE VIEW columns list
• CREATE MATERIALIZED VIEW AS REPLICA OF
• VECTOR_SEARCH() function
• CREATE VECTOR INDEX
• GROUPING() function
• GROUP BY .. GROUPING SETS / CUBE / ROLLUP
• quantified LIKE operator

Improved PostgreSQL support

• CREATE INDEX statement
• DROP INDEX statement
• ALTER INDEX statement
• REINDEX statement
• Parameters for opclass (mainly in the context of CREATE INDEX)

Breaking changes

• Replace TableKind, ViewKind and SequenceKind nodes with RelationKind.

Bugfixes

• The above also fixes the issue of CreateViewStmt.kind field containing TableKind not ViewKind (#68)

Breaking changes

• Introduced AlterTableAllInTablespaceStmt alongsideAlterTableStmt statement. This means the addition of AllAlterTableNodes and AllAlterTableStatements types (7c546fd)
• Renamed WithStorageParametersClause to PostgresqlWithOptions (4052303)
• Renamed AlterActionSetOptions to AlterActionSetBigqueryOptions (b21538f)
• All VIEW nodes are now exported as AllViewNodes, the AllViewStatements is still exported, but no more includes all
  view-related nodes. (0fcf413)
• Replaced *Kw fields in CreateViewStmt with kinds: ViewKind[] (c8beba7)
• Renamed CreateTableTablespaceClause to TablespaceClause (f2f6b79)
• Renamed CreateTableWithDataClause to WithDataClause (32c64f1)
• Replaced materializedKw field with kind in CreateViewStmt & DropViewStmt (b636853)
• Renamed DropSchemaStmt.name field to schemas (e41a9fc)
• AlterViewStmt.actions now contains ListExpr (4ab708d)
• Renamed AlterActionRenameTable to AlterActionRename (2aa2314)
• Renamed CreateSchemaStmt.options field to clauses (a3aa401)
• CreateSchemaStmt.name field is now optional (397b146)
• Renamed TruncateStmt.cascadeOrRestrictKw field to behaviorKw (0d7eee4)

Improved PostgreSQL support

• Finalized support for CREATE TABLE
• Full support for CREATE FOREIGN TABLE
• Full support for ALTER TABLE
• Full support for CREATE/DROP/ALTER SEQUENCE
• Full support for CREATE/ALTER/DROP VIEW
• Full support for CREATE/ALTER/DROP MATERIALIZED VIEW
• Full support for REFRESH MATERIALIZED VIEW
• Full support for ALTER/DROP SCHEMA
• Nearly full support for CREATE SCHEMA (only missing support of nested CREATE TRIGGER and GRANT statements)

Breaking changes

• Removed procedure_param node. The function_param node is now used in both CREATE FUNCTION and CREATE PROCEDURE statements.
• function_param node now contains the mode field that was part of procedure_param.
• name field of function_param is now optional.

Improved PostgreSQL support

• Full support for
  • CREATE FUNCTION
  • DROP FUNCTION
  • CREATE PROCEDURE
  • DROP PROCEDURE
  • See #63 for details.

Other changes

• block_stmt node now contains optional atomicKw field. To allow for PostgreSQL-specific BEGIN ATOMIC ... END syntax.

Breaking changes

• The constraint_deferrable node has been replaced with array of constraint_modifier nodes.
  Accordingly the deferrable field in constraint node has been renamed to modifiers.
• onConflict field has been replaced with clauses array.
  The affects: constraint_primary_key, constraint_unique, constraint_check, constraint_not_null.
• Various *Kw fields have been replaced with kind field that contains table_kind node:
  • in create_table_stmt: temporaryKw, unloggedKw, externalKw, snapshotKw, virtualKw.
  • in drop_table_stmt: temporaryKw, externalKw, snapshotKw.
  • in into_table_clause: temporaryKw, unloggedKw.
• The sort_specification node has been replaced with index_specification node in places that reference indexes.
  This effects: constraint_primary_key, create_index_stmt, upsert_clause.

Bugfixes

• MySQL column KEY constraint is now parsed as constraint_primary_key (instead of constraint_index).
• doKw field in upsert_clause now has correct type: Keyword<"DO"> (instead of Keyword<"DOR">).

Improved MySQL/MariaDB support

• Improved CREATE TABLE support:

  • Support index name in FOREIGN KEY
  • Support TABLESPACE & STORAGE options
  • Support UNION option
  • Support [NOT] ENFORCED modifier on CHECK() constraint

• Improved ALTER TABLE support:

  • Support ALTER COLUMN (SET {VISIBLE | INVISIBLE})
  • Support ADD CONSTRAINT
  • Support DROP CONSTRAINT
  • Support ALTER CONSTRAINT

Improved PostgreSQL support

• Almost complete CREATE TABLE support:

  • Support DEFAULT expr
  • Support STORAGE constraint
  • Support COMPRESSION constraint
  • Support GENERATED AS IDENTITY
  • Support UNIQUE NULLS [NOT] DISTINCT
  • Support SET NULL/DEFAULT with columns inside REFERENCES action
  • Support GLOBAL|LOCAL TEMP TABLE
  • Support UNLOGGED TABLE
  • Support NO INHERIT constraint modifier
  • Support CREATE TABLE .. INHERITS ()
  • Support PARTITION BY clause
  • Support PARTITION OF
  • Support MINVALUE & MAXVALUE FOR VALUES
  • Support FOR VALUES WITH ... MODULUS & REMAINDER
  • Support ON COMMIT clause
  • Support TABLESPACE clause
  • Support USING clause
  • Support WITH clause
  • Support WITHOUT OIDS clause
  • Support CREATE TABLE .. OF type
  • Support EXCLUDE constraint
  • Support LIKE clause inside columns list
  • Support index parameter clauses
  • Support WITH OPTIONS column definitions
  • Support CREATE TABLE AS .. WITH [NO] DATA

• Basic ALTER TABLE support:

  • Support [ONLY] name [*]
  • Support CASCADE|RESTRICT in ALTER TABLE .. DROP COLUMN
  • Support ALTER COLUMN .. SET NOT NULL
  • Support basic ALTER COLUMN .. SET DATA TYPE
  • Support ADD CONSTRAINT
  • Support DROP CONSTRAINT
  • Support ALTER CONSTRAINT
  • Support RENAME CONSTRAINT
  • Support VALIDATE CONSTRAINT

• Basic CREATE/DROP INDEX support

Bugfix

• Fix errors when parsing of SQLite builtin functions: glob(), like(), replace() #53

Breaking changes

• Renamed TruncateStmt.table field to tables which now contains ListExpr
• Moved ReturningClause and ClusterByClause types from AllSelectClauses to AllOtherClauses
• Renamed UpsertClause.columns field to conflictTarget
• Divided MergeStmt to MergeStmt and MergeClause (to accommodate WithClause inside MergeStmt)
• Renamed MergeActionInsert.values field to clauses

Improved PostgreSQL support

• Full support for UPDATE statement
  • Support [ONLY] table [*]
  • Support SET col = DEFAULT
  • Support WHERE CURRENT OF clause
• Full support for DELETE statement
  • Support [ONLY] table [*]
  • Support USING clause
  • Support WHERE CURRENT OF clause
• Full support for TRUNCATE statement
  • Support [ONLY] table [*]
  • Support multiple tables
  • Support CASCADE/RESTRICT
  • Support RESTART/CONTINUE IDENTITY
• Full support for MERGE statement
  • Support WITH clause
  • Support [ONLY] table [*]
  • Support OVERRIDING clause
  • Support INSERT DEFAULT VALUES
  • Support DO NOTHING action
• Almost full support for INSERT statement
  • Support ON CONFLICT clause
  • Support ON CONFLICT (expr)
  • Support ON CONFLICT ON CONSTRAINT
  • Support OVERRIDING clause

Bugfixes

• Fixed some serious performance regressions, especially when parsing PostgreSQL (#52)
• YEAR_MONTH keyword is now case-insensitive (like all others)

• Support parsing of PostgreSQL table function call with alias and column definitions #51
• Made the asKw field in func_call_with_column_definitions node optional and changed type of funcCall field.

Fix parsing of SQLite PRIMARY KEY { ASC | DESC }.

Breaking changes

• Replace distinctKw, hints and asStructOrValueKw fields in select_clause with modifiers field
  • Rename mysql_hint to mysql_modifier
  • Parse DISTINCT and ALL not as plain keywords, but as new separate nodes
  • Parse AS STRUCT / VALUE as select_as_struct / select_as_value nodes.
• Rename hints field to modifiers in insert_clause, update_clause, delete_clause.
• Replace plain ASC/DESC keyword with sort_direction_* nodes (both in order_by_clause and constraint_primary_key)
• Change tablesample_expr to allow for custom methods, changing type of tablesampleKw field
• Remove single ROLLUP() in GROUP BY restriction, changing the type of columns field

PostgreSQL support

• Query syntax:
  • Support UPDATE, INSERT, DELETE inside WITH clause
  • Support GROUP BY GROUPING SETS, CUBE(), ()
  • Support CYCLE clause in WITH
  • Support SEARCH clause in WITH
  • Support ORDER BY .. USING operator
  • Support column aliases in table aliases
  • Support table function with column definitions
  • Support ROWS FROM
  • Support LATERAL subqueries and table functions
  • Support REPEATABLE clause in TABLESAMPLE
  • Support table * and ONLY table inheritance syntax
  • Support SELECT DISTINCT ON ()
• Operators:
  • Support := syntax for named function arguments
  • Support ~ as both a unary and binary operator
  • Support OPERATOR() syntax

Bugfixes

• Properly support full table names in TABLE clause/statement
• Restrict the type of expr field in common_table_expr node
• Remove ! operator support from SQLite
• Fix ~ operator precedence in PostgreSQL

Internals

• Switch to Peggy 3

Breaking changes

• Removed insert_columns_clause node. Reintroduced columns field to insert_clause. This effectively reverts the change done in 0.18.0.
• Related to the above, PARTITION selection in MySQL INSERT and DELETE statements is now parsed as partitioned_table. The partition_clause node has been removed.
• PostgreSQL array_bounds node now uses empty node instead of empty list_expr node to represent missing number in definition like INT[].
• Renamed quantifier field in quantifier_expr node to quantifierKw for consistency with other keyword-containing fields.
• Dropped unused pair_expr node.

New Features

• Named function arguments are now supported for PostgreSQL.

Bugfixes

• The rowsKw field of fetch_clause node is now mandatory. It was always present already, now also correct in type definition.
• Fixed parsing of PostgreSQL GROUP BY DISTINCT
• Fixed parsing of MySQL full text match expressions.

Breaking changes

• Unary + is now parsed as an operator, not as part of a number
• data_type has been renamed to named_data_type
• select_clause columns are now optional
• SymbolOperator type is now plain string (because it needs to accommodate custom PostgreSQL operators)
• RowConstructor type is now part of Expr type

SQLite bugfixes

• Support unicode letters in SQLite identifiers

Improved PostgreSQL support

• Use the actual Postgres reserved words list
• Nested block-comments
• Remove incorrect #-comment support from Postgres
• Strings:
  • Dollar-quoted strings
  • String with C-style escapes
  • Unicode strings
  • Repeated-quote escapes in E-strings
  • Bit-string blobs
• Identifiers:
  • Unicode identifiers
• Literals:
  • JSON & JSONB literals
  • INTERVAL literals
• ROW() constructor
• EXTRACT() expression
• Special paren-less functions
• All keywords can be used in explicit aliases
• Operators:
  • IS UNKNOWN
  • IS DISTINCT FROM
  • ISNULL
  • NOTNULL
  • % operator
  • ^ operator
  • || operator
  • Bitwise ~ and # operators
  • ILIKE
  • BETWEEN SYMMETRIC
  • SIMILAR TO
  • IS NORMALIZED
  • AT TIME ZONE
  • COLLATE
  • Array-access operator
  • Array-slice operator
  • :: cast operator
  • All the builtin PostgreSQL operators
  • Almost fully correct PostgreSQL operator precedence
• Types:
  • All builtin data types
  • Special Object Identifier types
  • multi-dimensional array types
  • TIME types WITH/WITHOUT TIME ZONE
  • INTERVAL type
  • Custom data types
• SELECT support:
  • Support NULLS FIRST/LAST
  • LIMIT offset,count is not supported
  • Support FETCH..OFFSET
  • Support FOR-clause
  • Support INTO TABLE clause
  • Support empty SELECT
  • Support GROUP BY DISTINCT
  • Support LIMIT ALL
  • Support basic TABLE clause
• Basic TRUNCATE support
• Basic ANALYZE support
• Basic EXPLAIN support
• Basic EXECUTE support

Improved MySQL & MariaDB support

• Support FOR-clause in MySQL and MariaDB
• Support LOCK IN SHARE MODE in MySQL/MariaDB

Add experimental support for Postgres. This is really more on the level of "please don't use it".

Performance

• Improve performance of parsing nested parenthesis, especially in MySQL #38

Breaking changes

• Renamed common_table_expression to common_table_expr (for consistency with other _expr node types).

New features

• paramTypes now supports new type "@`name`" #37
• cstVisitor now supports skipping child nodes by returning VistorAction.SKIP.