class SetStatementSegment(BaseSegment):
"""Setting an already declared variable.
https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting#set
"""
type = "set_segment"
match_grammar = StartsWith("SET")
parse_grammar = Sequence(
"SET",
OneOf(
Ref("NakedIdentifierSegment"),
Bracketed(Delimited(Ref("NakedIdentifierSegment"))),
),
Ref("EqualsSegment"),
Delimited(
OneOf(
Ref("LiteralGrammar"),
Bracketed(Ref("SelectStatementSegment")),
Ref("BareFunctionSegment"),
Ref("FunctionSegment"),
Bracketed(
Delimited(
OneOf(
Ref("LiteralGrammar"),
Bracketed(Ref("SelectStatementSegment")),
Ref("BareFunctionSegment"),
Ref("FunctionSegment"),
)
)
),
Ref("ArrayLiteralSegment"),
),
),
)
class FromPivotExpressionSegment(BaseSegment):
"""A PIVOT expression.
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator
"""
type = "from_pivot_expression"
match_grammar = Sequence("PIVOT", Bracketed(Anything()))
parse_grammar = Sequence(
"PIVOT",
Bracketed(
Delimited(
Sequence(
Ref("FunctionSegment"),
Ref("AliasExpressionSegment", optional=True),
),
),
"FOR",
Ref("SingleIdentifierGrammar"),
"IN",
Bracketed(
Delimited(
Sequence(
Ref("LiteralGrammar"),
Ref("AliasExpressionSegment", optional=True),
),
)
),
),
)
class DatatypeSegment(BaseSegment):
"""A data type segment.
In particular here, this enabled the support for
the STRUCT datatypes.
"""
type = "data_type"
match_grammar = OneOf( # Parameter type
Ref("DatatypeIdentifierSegment"), # Simple type
Sequence("ANY", "TYPE"), # SQL UDFs can specify this "type"
Sequence(
"ARRAY",
Bracketed(
Ref("DatatypeSegment"),
bracket_type="angle",
bracket_pairs_set="angle_bracket_pairs",
),
),
Sequence(
"STRUCT",
Bracketed(
Delimited( # Comma-separated list of field names/types
Sequence(
Ref("ParameterNameSegment"),
Ref("DatatypeSegment"),
),
delimiter=Ref("CommaSegment"),
bracket_pairs_set="angle_bracket_pairs",
),
bracket_type="angle",
bracket_pairs_set="angle_bracket_pairs",
),
),
)
class TableAttributeSegment(BaseSegment):
"""Redshift specific table attributes.
As specified in
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html
"""
type = "table_constraint"
match_grammar = AnySetOf(
Sequence("DISTSTYLE",
OneOf("AUTO", "EVEN", "KEY", "ALL"),
optional=True),
Sequence("DISTKEY",
Bracketed(Ref("ColumnReferenceSegment")),
optional=True),
OneOf(
Sequence(
OneOf("COMPOUND", "INTERLEAVED", optional=True),
"SORTKEY",
Bracketed(Delimited(Ref("ColumnReferenceSegment"))),
),
Sequence("SORTKEY", "AUTO"),
optional=True,
),
Sequence("ENCODE", "AUTO", optional=True),
)
class ColumnAttributeSegment(BaseSegment):
"""Redshift specific column attributes.
As specified in
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html
"""
type = "column_attribute_segment"
match_grammar = AnySetOf(
Sequence("DEFAULT", Ref("ExpressionSegment")),
Sequence(
"IDENTITY",
Bracketed(Delimited(Ref("NumericLiteralSegment"))),
),
Sequence(
"GENERATED",
"BY",
"DEFAULT",
"AS",
"IDENTITY",
Bracketed(Delimited(Ref("NumericLiteralSegment"))),
),
Sequence("ENCODE", Ref("ColumnEncodingGrammar")),
"DISTKEY",
"SORTKEY",
Sequence("COLLATE", OneOf("CASE_SENSITIVE", "CASE_INSENSITIVE")),
)
class TableConstraintSegment(BaseSegment):
"""Redshift specific table constraints.
As specified in
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html
"""
type = "table_constraint"
match_grammar = AnySetOf(
Sequence("UNIQUE",
Bracketed(Delimited(Ref("ColumnReferenceSegment")))),
Sequence(
"PRIMARY",
"KEY",
Bracketed(Delimited(Ref("ColumnReferenceSegment"))),
),
Sequence(
"FOREIGN",
"KEY",
Bracketed(Delimited(Ref("ColumnReferenceSegment"))),
"REFERENCES",
Ref("TableReferenceSegment"),
Sequence(Bracketed(Ref("ColumnReferenceSegment"))),
),
)
class DatatypeSegment(BaseSegment):
"""A data type segment.
Updated for Transact-SQL to allow bracketed data types with bracketed schemas.
"""
type = "data_type"
match_grammar = Sequence(
# Some dialects allow optional qualification of data types with schemas
Sequence(
Ref("SingleIdentifierGrammar"),
Ref("DotSegment"),
allow_gaps=False,
optional=True,
),
OneOf(
Ref("DatatypeIdentifierSegment"),
Bracketed(Ref("DatatypeIdentifierSegment"), bracket_type="square"),
),
Bracketed(
OneOf(
Delimited(Ref("ExpressionSegment")),
# The brackets might be empty for some cases...
optional=True,
),
# There may be no brackets for some data types
optional=True,
),
Ref("CharCharacterSetSegment", optional=True),
)
class CreateIndexStatementSegment(BaseSegment):
"""A `CREATE INDEX` statement.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15
"""
type = "create_index_statement"
match_grammar = Sequence(
"CREATE",
Ref("OrReplaceGrammar", optional=True),
Sequence("UNIQUE", optional=True),
OneOf("CLUSTERED", "NONCLUSTERED", optional=True),
"INDEX",
Ref("IfNotExistsGrammar", optional=True),
Ref("IndexReferenceSegment"),
"ON",
Ref("TableReferenceSegment"),
Sequence(Bracketed(Delimited(
Ref("IndexColumnDefinitionSegment"), ), )),
Sequence(
"INCLUDE",
Sequence(
Bracketed(Delimited(Ref("IndexColumnDefinitionSegment"), ), )),
optional=True,
),
)
class FunctionDefinitionGrammar(BaseSegment):
"""This is the body of a `CREATE FUNCTION AS` statement."""
match_grammar = Sequence(
AnyNumberOf(
Sequence(
"LANGUAGE",
# Not really a parameter, but best fit for now.
Ref("ParameterNameSegment"),
Sequence(
"OPTIONS",
Bracketed(
Delimited(
Sequence(
Ref("ParameterNameSegment"),
Ref("EqualsSegment"),
Anything(),
),
delimiter=Ref("CommaSegment"),
)),
optional=True,
),
),
# There is some syntax not implemented here,
Sequence(
"AS",
OneOf(
Ref("DoubleQuotedLiteralSegment"),
Ref("QuotedLiteralSegment"),
Bracketed(
OneOf(Ref("ExpressionSegment"),
Ref("SelectStatementSegment"))),
),
),
))
class TdTablePartitioningLevel(BaseSegment):
"""Partitioning Level.
https://docs.teradata.com/reader/eWpPpcMoLGQcZEoyt5AjEg/e0GX8Iw16u1SCwYvc5qXzg
partition_expression or
COLUMN [[NO] AUTO COMPRESS] [[ALL BUT] column_partition] [ADD constant]
column_partition := ([COLUMN|ROW] column_name (, column_name2, ...) NO AUTOCOMPRESS
partition_expression := CASE_N, RANGE_N, EXTRACT, expression and in case of multi-level in parenthesis
"""
type = "td_partitioning_level"
match_grammar = OneOf(
Sequence(
Ref("FunctionNameSegment"),
Bracketed(Anything(optional=True)),
),
Bracketed(
Delimited(
Sequence(
Ref("FunctionNameSegment"),
Bracketed(Anything(optional=True)),
),
delimiter=Ref("CommaSegment"),
), ),
)
class SamplingExpressionSegment(BaseSegment):
"""A sampling expression."""
type = "snowflake_sample_expression"
match_grammar = Sequence(
OneOf("SAMPLE", "TABLESAMPLE"),
OneOf("BERNOULLI", "ROW", "SYSTEM", "BLOCK", optional=True),
Bracketed(Ref("NumericLiteralSegment"), Ref.keyword("ROWS", optional=True)),
Sequence(OneOf("REPEATABLE", "SEED"), Bracketed(Ref("NumericLiteralSegment"))),
)
class DatatypeSegment(BaseSegment):
"""Data types."""
type = "data_type"
match_grammar = OneOf(
Ref("PrimitiveTypeSegment"),
Sequence(
"ARRAY",
Bracketed(
Ref("DatatypeSegment"),
bracket_pairs_set="angle_bracket_pairs",
bracket_type="angle",
),
),
Sequence(
"MAP",
Bracketed(
Sequence(
Ref("PrimitiveTypeSegment"),
Ref("CommaSegment"),
Ref("DatatypeSegment"),
),
bracket_pairs_set="angle_bracket_pairs",
bracket_type="angle",
),
),
Sequence(
"STRUCT",
Bracketed(
Delimited(
Sequence(
Ref("NakedIdentifierSegment"),
Ref("ColonSegment"),
Ref("DatatypeSegment"),
Ref("CommentGrammar", optional=True),
),
bracket_pairs_set="angle_bracket_pairs",
),
bracket_pairs_set="angle_bracket_pairs",
bracket_type="angle",
),
),
Sequence(
"UNIONTYPE",
Bracketed(
Delimited(
Ref("DatatypeSegment"), bracket_pairs_set="angle_bracket_pairs"
),
bracket_pairs_set="angle_bracket_pairs",
bracket_type="angle",
),
),
)
class FunctionSegment(BaseSegment):
"""A scalar or aggregate function.
Maybe in the future we should distinguish between
aggregate functions and other functions. For now
we treat them the same because they look the same
for our purposes.
"""
type = "function"
match_grammar = Sequence(
Sequence(
Ref("FunctionNameSegment"),
Bracketed(
Ref(
"FunctionContentsGrammar",
# The brackets might be empty for some functions...
optional=True,
ephemeral_name="FunctionContentsGrammar",
)),
# Functions returning ARRYS in BigQuery can have optional
# OFFSET or ORDINAL clauses
Sequence(
Bracketed(
OneOf(
"OFFSET",
"ORDINAL",
),
Bracketed(Ref("NumericLiteralSegment"), ),
bracket_type="square",
),
optional=True,
),
# Functions returning STRUCTs in BigQuery can have the fields
# elements referenced (e.g. ".a"), including wildcards (e.g. ".*")
# or multiple nested fields (e.g. ".a.b", or ".a.b.c")
Sequence(
Ref("DotSegment"),
AnyNumberOf(
Sequence(
Ref("ParameterNameSegment"),
Ref("DotSegment"),
), ),
OneOf(
Ref("ParameterNameSegment"),
Ref("StarSegment"),
),
optional=True,
),
),
Ref("PostFunctionGrammar", optional=True),
)
class TdTableConstraints(BaseSegment):
"""Teradata specific table attributes.
e.g.
UNIQUE PRIMARY INDEX Column_name | ( Column_name, ... )
NO PRIMARY INDEX
...
"""
type = "td_table_constraint"
match_grammar = AnyNumberOf(
# PRIMARY Index
OneOf(
Sequence( # UNIQUE PRIMARY INDEX Column_name | ( Column_name, ... )
Ref.keyword("UNIQUE", optional=True),
"PRIMARY",
"INDEX",
Ref("ObjectReferenceSegment", optional=True), # primary index name
OneOf(
Bracketed(
Delimited(
Ref("SingleIdentifierGrammar"),
)
),
Ref("SingleIdentifierGrammar"),
),
),
Sequence("NO", "PRIMARY", "INDEX"), # NO PRIMARY INDEX
),
# PARTITION BY ...
Sequence( # INDEX HOPR_TRN_TRAV_SIN_MP_I ( IND_TIPO_TARJETA );
"PARTITION",
"BY",
Ref("TdTablePartitioningLevel"),
),
# Index
Sequence( # INDEX HOPR_TRN_TRAV_SIN_MP_I ( IND_TIPO_TARJETA );
Ref.keyword("UNIQUE", optional=True),
"INDEX",
Ref("ObjectReferenceSegment"), # Index name
Ref.keyword("ALL", optional=True),
Bracketed( # Columns making up constraint
Delimited(Ref("ColumnReferenceSegment")),
),
),
# WITH DATA
Sequence("WITH", Sequence("NO", optional=True), "DATA"),
# ON COMMIT PRESERVE ROWS
Sequence("ON", "COMMIT", OneOf("PRESERVE", "DELETE"), "ROWS"),
)
class FromBeforeExpressionSegment(BaseSegment):
"""A BEFORE expression."""
type = "from_before_expression"
match_grammar = Sequence("BEFORE", Bracketed(Anything()))
parse_grammar = Sequence(
"BEFORE",
Bracketed(
OneOf("TIMESTAMP", "OFFSET", "STATEMENT"),
Ref("ParameterAssignerSegment"),
Ref("ExpressionSegment"),
),
)
class SkewedByClauseSegment(BaseSegment):
"""`SKEWED BY` clause in a CREATE / ALTER statement."""
type = "skewed_by_clause"
match_grammar = Sequence(
"SKEWED",
"BY",
Ref("BracketedColumnReferenceListGrammar"),
"ON",
Bracketed(
Delimited(
OneOf(Ref("LiteralGrammar"),
Bracketed(Delimited(Ref("LiteralGrammar")))))),
Sequence("STORED", "AS", "DIRECTORIES", optional=True),
)
class PrimitiveTypeSegment(BaseSegment):
"""Primitive data types."""
type = "primitive_type"
match_grammar = OneOf(
"TINYINT",
"SMALLINT",
"INT",
"INTEGER",
"BIGINT",
"BOOLEAN",
"FLOAT",
Sequence("DOUBLE", Ref.keyword("PRECISION", optional=True)),
"STRING",
"BINARY",
"TIMESTAMP",
Sequence(
OneOf("DECIMAL", "DEC", "NUMERIC"),
Bracketed(
Ref("NumericLiteralSegment"),
Ref("CommaSegment"),
Ref("NumericLiteralSegment"),
optional=True,
),
),
"DATE",
"VARCHAR",
"CHAR",
)
class FromPivotExpressionSegment(BaseSegment):
"""A PIVOT expression."""
type = "from_pivot_expression"
match_grammar = Sequence("PIVOT", Bracketed(Anything()))
parse_grammar = Sequence(
"PIVOT",
Bracketed(
Ref("FunctionSegment"),
"FOR",
Ref("SingleIdentifierGrammar"),
"IN",
Bracketed(Delimited(Ref("LiteralGrammar"), delimiter=Ref("CommaSegment"))),
),
)
class CreateTableStatementSegment(BaseSegment):
"""A `CREATE TABLE` statement."""
type = "create_table_statement"
# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_statement
match_grammar = Sequence(
"CREATE",
Ref("OrReplaceGrammar", optional=True),
Ref("TemporaryTransientGrammar", optional=True),
"TABLE",
Ref("IfNotExistsGrammar", optional=True),
Ref("TableReferenceSegment"),
# Column list
Sequence(
Bracketed(
Delimited(
OneOf(
Ref("TableConstraintSegment"),
Ref("ColumnDefinitionSegment"),
),
)
),
Ref("CommentClauseSegment", optional=True),
optional=True,
),
Ref("PartitionBySegment", optional=True),
Ref("ClusterBySegment", optional=True),
Ref("OptionsSegment", optional=True),
# Create AS syntax:
Sequence(
"AS",
OptionallyBracketed(Ref("SelectableGrammar")),
optional=True,
),
)
class TdOrderByStatClauseSegment(BaseSegment):
"""An `ORDER BY (VALUES|HASH) (column_name)` clause in COLLECT STATS."""
type = "stat_orderby_clause"
match_grammar = Sequence(
"ORDER", "BY", OneOf("VALUES", "HASH"), Bracketed(Ref("ColumnReferenceSegment"))
)
class QualifyClauseSegment(BaseSegment):
"""A `QUALIFY` clause like in `SELECT`.
https://docs.snowflake.com/en/sql-reference/constructs/qualify.html
"""
type = "having_clause"
match_grammar = StartsWith(
"QUALIFY",
terminator=OneOf(
"ORDER",
"LIMIT",
),
)
parse_grammar = Sequence(
"QUALIFY",
Indent,
OneOf(
Bracketed(
Ref("ExpressionSegment"),
),
Ref("ExpressionSegment"),
),
Dedent,
)
class CallStoredProcedureSegment(BaseSegment):
"""This is a CALL statement used to execute a stored procedure.
https://dev.mysql.com/doc/refman/8.0/en/call.html
"""
type = "call_segment"
match_grammar = Sequence(
"CALL",
OneOf(
Ref("SingleIdentifierGrammar"),
Ref("QuotedIdentifierSegment"),
),
Bracketed(
AnyNumberOf(
Delimited(
Ref("QuotedLiteralSegment"),
Ref("NumericLiteralSegment"),
Ref("DoubleQuotedLiteralSegment"),
Ref("SessionVariableNameSegment"),
Ref("LocalVariableNameSegment"),
Ref("FunctionSegment"),
),
),
),
)
class TypelessStructSegment(BaseSegment):
"""Expression to construct a STRUCT with implicit types.
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#typeless_struct_syntax
"""
type = "typeless_struct"
match_grammar = Sequence(
"STRUCT",
Bracketed(
Delimited(
AnyNumberOf(
Sequence(
OneOf(
Ref("LiteralGrammar"),
Ref("FunctionSegment"),
Ref("IntervalExpressionSegment"),
Ref("ObjectReferenceSegment"),
Ref("ExpressionSegment"),
),
Ref("AliasExpressionSegment", optional=True),
), ),
delimiter=Ref("CommaSegment"),
),
optional=True,
),
)
class TdColumnOptionSegment(BaseSegment):
"""Teradata specific column attributes.
e.g. CHARACTER SET LATIN or [NOT] CASESPECIFIC
"""
type = "td_column_attribute_constraint"
match_grammar = Sequence(
OneOf(
Sequence( # CHARACTER SET LATIN
"CHARACTER", "SET", Ref("SingleIdentifierGrammar")),
Sequence( # [NOT] CASESPECIFIC
Ref.keyword("NOT", optional=True),
"CASESPECIFIC",
),
Sequence( # COMPRESS [(1.,3.) | 3. | NULL],
"COMPRESS",
OneOf(
Bracketed(
Delimited(Ref("LiteralGrammar"),
delimiter=Ref("CommaSegment"))),
Ref("LiteralGrammar"),
"NULL",
optional=True,
),
),
), )
class TdCollectStatisticsStatementSegment(BaseSegment):
"""A `COLLECT STATISTICS (Optimizer Form)` statement.
# TODO: Make complete
COLLECT [SUMMARY] (STATISTICS|STAT) [[COLUMN| [UNIQUE] INDEX] (expression (, expression ...)] ON TABLENAME
"""
type = "collect_statistics_statement"
match_grammar = Sequence(
"COLLECT",
Ref.keyword("SUMMARY", optional=True),
OneOf("STATISTICS", "STAT"),
OneOf(
Sequence(
OneOf(
"COLUMN",
Sequence(
Ref.keyword("UNIQUE", optional=True),
"INDEX",
),
),
OneOf(
Bracketed(
Delimited(Ref("ObjectReferenceSegment"),
delimiter=Ref("CommaSegment"))),
Ref("ObjectReferenceSegment"),
),
),
optional=True,
),
"ON",
Ref("ObjectReferenceSegment"),
)
class CreateTableAsStatementSegment(BaseSegment):
"""A `CREATE TABLE AS` statement.
As specified in
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_AS.html
"""
type = "create_table_as_statement"
match_grammar = Sequence(
"CREATE",
Sequence(
Ref.keyword("LOCAL", optional=True),
OneOf("TEMPORARY", "TEMP"),
optional=True,
),
"TABLE",
Ref("ObjectReferenceSegment"),
Bracketed(
Delimited(Ref("ColumnReferenceSegment"), ),
optional=True,
),
Sequence("BACKUP", OneOf("YES", "NO"), optional=True),
Ref("TableAttributeSegment", optional=True),
"AS",
OptionallyBracketed(Ref("SelectableGrammar")),
)
class CreateTableStatementSegment(BaseSegment):
"""A `CREATE TABLE` statement."""
type = "create_table_statement"
# https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15
# https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse?view=aps-pdw-2016-au7
match_grammar = Sequence(
"CREATE",
"TABLE",
Ref("TableReferenceSegment"),
OneOf(
# Columns and comment syntax:
Sequence(
Bracketed(
Delimited(
OneOf(
Ref("TableConstraintSegment"),
Ref("ColumnDefinitionSegment"),
), )),
Ref("CommentClauseSegment", optional=True),
),
# Create AS syntax:
Sequence(
"AS",
OptionallyBracketed(Ref("SelectableGrammar")),
),
# Create like syntax
Sequence("LIKE", Ref("TableReferenceSegment")),
),
Ref("TableDistributionIndexClause",
optional=True), # Azure Synapse Analytics specific
)
parse_grammar = match_grammar
class TableDistributionIndexClause(BaseSegment):
"""`CREATE TABLE` distribution / index clause.
This is specific to Azure Synapse Analytics.
"""
type = "table_distribution_index_clause"
match_grammar = Sequence(
"WITH",
Bracketed(
OneOf(
Sequence(
Ref("TableDistributionClause"),
Ref("CommaSegment"),
Ref("TableIndexClause"),
),
Sequence(
Ref("TableIndexClause"),
Ref("CommaSegment"),
Ref("TableDistributionClause"),
),
Ref("TableDistributionClause"),
Ref("TableIndexClause"),
)),
)
class AlterTableColumnClausesSegment(BaseSegment):
"""ALTER TABLE `column_clauses` per defined in Oracle's grammar.
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ALTER-TABLE.html
If possible, please match the order of this sequence with what's defined in
Oracle's column_clauses grammar.
"""
type = "alter_table_column_clauses"
match_grammar = OneOf(
# add_column_clause
# modify_column_clause
Sequence(
OneOf(
"ADD",
"MODIFY",
),
OneOf(
Ref("ColumnDefinitionSegment"),
Bracketed(Delimited(Ref("ColumnDefinitionSegment"))),
),
),
# drop_column_clause
# @TODO: extend drop_column_clause
Sequence(
"DROP",
OneOf(
Sequence("COLUMN", Ref("ColumnReferenceSegment")),
Bracketed(Delimited(Ref("ColumnReferenceSegment"))),
),
),
# @TODO: add_period_clause
# @TODO: drop_period_clause
# rename_column_clause
Sequence(
"RENAME",
"COLUMN",
Ref("ColumnReferenceSegment"),
"TO",
Ref("ColumnReferenceSegment"),
)
# @TODO: modify_collection_retrieval
# @TODO: modify_LOB_storage_clause
# @TODO: alter_varray_col_properties
)
class TupleSegment(BaseSegment):
"""Expression to construct a TUPLE.
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#tuple_syntax
"""
type = "tuple"
match_grammar = Bracketed(Delimited(Ref("BaseExpressionElementGrammar")))
