class SelectStatementSegment(ansi_SelectClauseSegment): """A snowflake `SELECT` statement including optional Qualify. https://docs.snowflake.com/en/sql-reference/constructs/qualify.html """ type = "select_statement" match_grammar = StartsWith( # NB: In bigquery, the select clause may include an EXCEPT, which # will also match the set operator, but by starting with the whole # select clause rather than just the SELECT keyword, we normally # mitigate that here. But this isn't BigQuery! So we can be more # efficient and just just the keyword. "SELECT", terminator=Ref("SetOperatorSegment"), ) parse_grammar = Sequence( Ref("SelectClauseSegment"), Ref("FromClauseSegment", optional=True), Ref("WhereClauseSegment", optional=True), Ref("GroupByClauseSegment", optional=True), Ref("HavingClauseSegment", optional=True), Ref("QualifyClauseSegment", optional=True), Ref("OrderByClauseSegment", optional=True), Ref("LimitClauseSegment", optional=True), )
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 UnorderedSelectStatementSegment(BaseSegment): """A `SELECT` statement without any ORDER clauses or later. We need to change ANSI slightly to remove LimitClauseSegment and NamedWindowSegment which don't exist in T-SQL. """ type = "select_statement" # match grammar. This one makes sense in the context of knowing that it's # definitely a statement, we just don't know what type yet. match_grammar = StartsWith( # NB: In bigquery, the select clause may include an EXCEPT, which # will also match the set operator, but by starting with the whole # select clause rather than just the SELECT keyword, we mitigate that # here. Ref("SelectClauseSegment"), terminator=OneOf( Ref("SetOperatorSegment"), Ref("WithNoSchemaBindingClauseSegment"), Ref("OrderByClauseSegment"), ), enforce_whitespace_preceding_terminator=True, ) parse_grammar = Sequence( Ref("SelectClauseSegment"), # Dedent for the indent in the select clause. # It's here so that it can come AFTER any whitespace. Dedent, Ref("FromClauseSegment", optional=True), Ref("PivotUnpivotStatementSegment", optional=True), Ref("WhereClauseSegment", optional=True), Ref("GroupByClauseSegment", optional=True), Ref("HavingClauseSegment", optional=True), )
class DistributeByClauseSegment(ansi.OrderByClauseSegment): """A `DISTRIBUTE BY` clause like in `SELECT`.""" type = "distributeby_clause" match_grammar: Matchable = StartsWith( Sequence("DISTRIBUTE", "BY"), terminator=OneOf( "SORT", "LIMIT", "HAVING", "QUALIFY", # For window functions "WINDOW", Ref("FrameClauseUnitGrammar"), "SEPARATOR", ), ) parse_grammar: Optional[Matchable] = Sequence( "DISTRIBUTE", "BY", Indent, Delimited( Sequence( OneOf( Ref("ColumnReferenceSegment"), Ref("NumericLiteralSegment"), Ref("ExpressionSegment"), ), ), terminator=OneOf( Ref.keyword("LIMIT"), Ref("FrameClauseUnitGrammar"), Ref.keyword("SORT") ), ), Dedent, )
class CreateAdapterScriptStatementSegment(BaseSegment): """`CREATE SCRIPT` statement create a adapter script. https://docs.exasol.com/sql/create_script.htm """ type = "create_adapter_script" is_ddl = True is_dml = False is_dql = False is_dcl = False match_grammar = StartsWith( "CREATE", Ref("OrReplaceGrammar", optional=True), OneOf("JAVA", "PYTHON", Ref("SingleIdentifierGrammar"), optional=True), "ADAPTER", "SCRIPT", ) parse_grammar = Sequence( "CREATE", Ref("OrReplaceGrammar", optional=True), OneOf("JAVA", "PYTHON", Ref("SingleIdentifierGrammar"), optional=True), "ADAPTER", "SCRIPT", Ref("ScriptReferenceSegment"), Ref("ScriptContentSegment"), )
class SortByClauseSegment(ansi.OrderByClauseSegment): """A `SORT BY` clause like in `SELECT`.""" type = "sortby_clause" match_grammar: Matchable = StartsWith( Sequence("SORT", "BY"), terminator=ansi.OrderByClauseSegment.match_grammar.terminator, # type: ignore ) parse_grammar: Optional[Matchable] = Sequence( "SORT", "BY", Indent, Delimited( Sequence( OneOf( Ref("ColumnReferenceSegment"), Ref("NumericLiteralSegment"), Ref("ExpressionSegment"), ), OneOf("ASC", "DESC", optional=True), Sequence("NULLS", OneOf("FIRST", "LAST"), optional=True), ), terminator=OneOf(Ref.keyword("LIMIT"), Ref("FrameClauseUnitGrammar")), ), Dedent, )
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 UpdateStatementSegment(BaseSegment): """A `Update from` statement. The UPDATE statement FROM clause is a Teradata extension to the ANSI SQL:2011 standard. UPDATE (<table name> | FROM Statement) SET <set clause list> [ WHERE <search condition> ] """ type = "update_statement" match_grammar = StartsWith("UPDATE") parse_grammar = Sequence( "UPDATE", OneOf( Ref("TableReferenceSegment"), Ref("FromUpdateClauseSegment"), Sequence( Ref("TableReferenceSegment"), Ref("FromUpdateClauseSegment"), ), ), Ref("SetClauseListSegment"), Ref("WhereClauseSegment", optional=True), )
class DropStatementSegment(BaseSegment): """A `DROP` statement.""" type = "drop_statement" match_grammar = StartsWith("DROP") parse_grammar = OneOf( Ref("DropDatabaseStatementSegment"), Ref("DropTableStatementSegment"), # TODO: add other drops )
class FromUpdateClauseSegment(BaseSegment): """A `FROM` clause like in `SELECT` but terminated by SET.""" type = "from_in_update_clause" match_grammar = StartsWith("FROM", terminator=Ref.keyword("SET")) parse_grammar = Sequence( "FROM", Delimited( # Optional old school delimited joins Ref("FromExpressionElementSegment"), ), )
class TruncateStatementSegment(BaseSegment): """`TRUNCATE TABLE` statement.""" type = "truncate_table" match_grammar = StartsWith("TRUNCATE") parse_grammar = Sequence( "TRUNCATE", Ref.keyword("TABLE", optional=True), Ref("TableReferenceSegment"), Ref("PartitionSpecGrammar", optional=True), )
class CreateUDFScriptStatementSegment(BaseSegment): """`CREATE SCRIPT` statement create a UDF script. https://docs.exasol.com/sql/create_script.htm """ type = "create_udf_script" is_ddl = True is_dml = False is_dql = False is_dcl = False match_grammar = StartsWith( Sequence( "CREATE", Ref("OrReplaceGrammar", optional=True), OneOf( "JAVA", "PYTHON", "LUA", "R", Ref("SingleIdentifierGrammar"), optional=True, ), OneOf("SCALAR", "SET"), "SCRIPT", )) parse_grammar = Sequence( "CREATE", Ref("OrReplaceGrammar", optional=True), OneOf("JAVA", "PYTHON", "LUA", "R", Ref("SingleIdentifierGrammar"), optional=True), OneOf("SCALAR", "SET"), "SCRIPT", Ref("ScriptReferenceSegment"), Bracketed( Sequence( Delimited(Ref("ColumnDatatypeSegment")), Ref("OrderByClauseSegment", optional=True), optional=True, ), optional=True, ), OneOf(Sequence("RETURNS", Ref("DatatypeSegment")), Ref("EmitsGrammar")), "AS", Ref("ScriptContentSegment"), )
class CreateFunctionStatementSegment(BaseSegment): """A `CREATE FUNCTION` statement.""" type = "create_function_statement" is_ddl = True is_dml = False is_dql = False is_dcl = False match_grammar = StartsWith( Sequence( "CREATE", Ref("OrReplaceGrammar", optional=True), "FUNCTION", ) ) parse_grammar = Sequence( "CREATE", Ref("OrReplaceGrammar", optional=True), "FUNCTION", Ref("FunctionReferenceSegment"), Bracketed( Delimited( Sequence( Ref("SingleIdentifierGrammar"), # Column name Ref.keyword("IN", optional=True), Ref("DatatypeSegment"), # Column type ), optional=True, ), ), "RETURN", Ref("DatatypeSegment"), OneOf("IS", "AS", optional=True), AnyNumberOf( Sequence( Ref("VariableNameSegment"), Ref("DatatypeSegment"), Ref("SemicolonSegment"), ), optional=True, ), "BEGIN", AnyNumberOf(Ref("FunctionBodySegment")), "RETURN", Ref("FunctionContentsExpressionGrammar"), Ref("SemicolonSegment"), "END", Ref("FunctionReferenceSegment", optional=True), Ref("SemicolonSegment", optional=True), )
class PartitionBySegment(BaseSegment): """PARTITION BY partition_expression.""" type = "partition_by_segment" match_grammar = StartsWith( "PARTITION", terminator=OneOf("CLUSTER", "OPTIONS", "AS", Ref("DelimiterSegment")), enforce_whitespace_preceding_terminator=True, ) parse_grammar = Sequence( "PARTITION", "BY", Ref("ExpressionSegment"), )
class UseStatementSegment(BaseSegment): """A snowflake `USE` statement. https://docs.snowflake.com/en/sql-reference/sql/use.html """ type = "use_statement" match_grammar = StartsWith("USE") parse_grammar = Sequence( "USE", OneOf("ROLE", "WAREHOUSE", "DATABASE", "SCHEMA", optional=True), Ref("ObjectReferenceSegment"), )
class ClusterBySegment(BaseSegment): """CLUSTER BY clustering_column_list.""" type = "cluster_by_segment" match_grammar = StartsWith( "CLUSTER", terminator=OneOf("OPTIONS", "AS", Ref("DelimiterSegment")), enforce_whitespace_preceding_terminator=True, ) parse_grammar = Sequence( "CLUSTER", "BY", Delimited(Ref("ExpressionSegment")), )
class InsertStatementSegment(BaseSegment): """A `INSERT` statement. N.B. not a complete implementation. """ type = "insert_statement" match_grammar = StartsWith("INSERT") parse_grammar = Sequence( "INSERT", Ref.keyword("INTO", optional=True), Ref("TableReferenceSegment"), Ref("BracketedColumnReferenceListGrammar", optional=True), Ref("SelectableGrammar"), )
class QualifyClauseSegment(BaseSegment): """A `QUALIFY` clause like in `SELECT`.""" type = "qualify_clause" match_grammar = StartsWith( "QUALIFY", terminator=OneOf("ORDER", "LIMIT", "QUALIFY", "WINDOW"), enforce_whitespace_preceding_terminator=True, ) parse_grammar = Sequence( "QUALIFY", Indent, OptionallyBracketed(Ref("ExpressionSegment")), Dedent, )
class InsertStatementSegment(BaseSegment): """An `INSERT` statement. Full Apache Hive `INSERT` reference here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML """ type = "insert_statement" match_grammar = StartsWith("INSERT") parse_grammar = Sequence( "INSERT", OneOf( Sequence( "OVERWRITE", OneOf( Sequence( "TABLE", Ref("TableReferenceSegment"), Ref("PartitionSpecGrammar", optional=True), Ref("IfNotExistsGrammar", optional=True), Ref("SelectableGrammar"), ), Sequence( Sequence("LOCAL", optional=True), "DIRECTORY", Ref("QuotedLiteralSegment"), Ref("RowFormatClauseSegment", optional=True), Ref("StoredAsGrammar", optional=True), Ref("SelectableGrammar"), ), ), ), Sequence( "INTO", "TABLE", Ref("TableReferenceSegment"), Ref("PartitionSpecGrammar", optional=True), OneOf( Ref("SelectableGrammar"), Ref("ValuesClauseSegment"), ), ), ), )
class SelectClauseSegment(ansi.SelectClauseSegment): """A group of elements in a select target statement. Remove OVERLAPS as a terminator as this can be part of SelectClauseModifierSegment """ match_grammar = StartsWith( Sequence( OneOf("SELECT", "SEL"), Ref("WildcardExpressionSegment", optional=True) ), terminator=OneOf( "FROM", "WHERE", Sequence("ORDER", "BY"), "LIMIT", Ref("SetOperatorSegment"), ), enforce_whitespace_preceding_terminator=True, ) parse_grammar = ansi.SelectClauseSegment.parse_grammar
class DeclareStatementSegment(BaseSegment): """Declaration of a variable. https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting#declare """ type = "declare_segment" match_grammar = StartsWith("DECLARE") parse_grammar = Sequence( "DECLARE", Delimited(Ref("NakedIdentifierSegment")), OneOf( Ref("DatatypeSegment"), Ref("DefaultDeclareOptionsGrammar"), Sequence( Ref("DatatypeSegment"), Ref("DefaultDeclareOptionsGrammar"), ), ), )
class CommentStatementSegment(BaseSegment): """A `Comment` statement. COMMENT [text] https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4009.htm """ type = "comment_statement" match_grammar = StartsWith(Sequence("COMMENT", "ON")) parse_grammar = Sequence( "COMMENT", "ON", Sequence( OneOf( Sequence( "TABLE", Ref("TableReferenceSegment"), ), Sequence( "COLUMN", Ref("ColumnReferenceSegment"), ), Sequence( "OPERATOR", Ref("ObjectReferenceSegment"), ), Sequence( "INDEXTYPE", Ref("IndexTypeReferenceSegment"), ), Sequence( "MATERIALIZED", "VIEW", Ref("TableReferenceSegment"), ), ), Sequence("IS", OneOf(Ref("QuotedLiteralSegment"), "NULL")), ), )
class CreateProcedureStatementSegment(BaseSegment): """A `CREATE OR ALTER PROCEDURE` statement. https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15 """ type = "create_procedure_statement" match_grammar = StartsWith( Sequence("CREATE", Sequence("OR", "ALTER", optional=True), OneOf("PROCEDURE", "PROC"))) parse_grammar = Sequence( "CREATE", Sequence("OR", "ALTER", optional=True), OneOf("PROCEDURE", "PROC"), Ref("ObjectReferenceSegment"), Ref("FunctionParameterListGrammar", optional=True), "AS", # Pending to add BEGIN END optional Ref("ProcedureDefinitionGrammar"), )
class CreateScriptingLuaScriptStatementSegment(BaseSegment): """`CREATE SCRIPT` statement to create a Lua scripting script. https://docs.exasol.com/sql/create_script.htm """ type = "create_scripting_lua_script" is_ddl = True is_dml = False is_dql = False is_dcl = False match_grammar = StartsWith( Sequence( "CREATE", Ref("OrReplaceGrammar", optional=True), Ref.keyword("LUA", optional=True), "SCRIPT", )) parse_grammar = Sequence( "CREATE", Ref("OrReplaceGrammar", optional=True), Ref.keyword("LUA", optional=True), "SCRIPT", Ref("ScriptReferenceSegment"), Bracketed( Delimited( Sequence(Ref.keyword("ARRAY", optional=True), Ref("SingleIdentifierGrammar")), optional=True, ), optional=True, ), Sequence(Ref.keyword("RETURNS"), OneOf("TABLE", "ROWCOUNT"), optional=True), "AS", Ref("ScriptContentSegment"), )
class PivotUnpivotStatementSegment(BaseSegment): """Declaration of a variable. https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15 """ type = "pivotunpivot_segment" match_grammar = StartsWith( OneOf("PIVOT", "UNPIVOT"), terminator=Ref("FromClauseTerminatorGrammar"), enforce_whitespace_preceding_terminator=True, ) parse_grammar = Sequence( OneOf( Sequence( "PIVOT", OptionallyBracketed( Sequence( OptionallyBracketed(Ref("FunctionSegment")), "FOR", Ref("ColumnReferenceSegment"), "IN", Bracketed(Delimited(Ref("ColumnReferenceSegment"))), )), ), Sequence( "UNPIVOT", OptionallyBracketed( Sequence( OptionallyBracketed(Ref("ColumnReferenceSegment")), "FOR", Ref("ColumnReferenceSegment"), "IN", Bracketed(Delimited(Ref("ColumnReferenceSegment"))), )), ), ), "AS", Ref("TableReferenceSegment"), )
class DeclareStatementSegment(BaseSegment): """Declaration of a variable. https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql?view=sql-server-ver15 """ type = "declare_segment" match_grammar = StartsWith("DECLARE") parse_grammar = Sequence( "DECLARE", Delimited(Ref("ParameterNameSegment")), Ref("DatatypeSegment"), Sequence( Ref("EqualsSegment"), OneOf( Ref("LiteralGrammar"), Bracketed(Ref("SelectStatementSegment")), Ref("BareFunctionSegment"), Ref("FunctionSegment"), ), optional=True, ), )
class DeclareStatementSegment(BaseSegment): """Declaration of a variable. https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting#declare """ type = "declare_segment" match_grammar = StartsWith("DECLARE") parse_grammar = Sequence( "DECLARE", Delimited(Ref("NakedIdentifierSegment")), Ref("DatatypeIdentifierSegment"), Sequence( "DEFAULT", OneOf( Ref("LiteralGrammar"), Bracketed(Ref("SelectStatementSegment")), Ref("BareFunctionSegment"), Ref("FunctionSegment"), ), optional=True, ), )
class BteqStatementSegment(BaseSegment): """Bteq statements start with a dot, followed by a Keyword. Non exhaustive and maybe catching too many statements? # BTEQ commands .if errorcode > 0 then .quit 2 .IF ACTIVITYCOUNT = 0 THEN .QUIT """ type = "bteq_statement" match_grammar = StartsWith(Ref("DotSegment")) parse_grammar = Sequence( Ref("DotSegment"), Ref("BteqKeyWordSegment"), AnyNumberOf( Ref("BteqKeyWordSegment"), # if ... then: the ... Sequence(Ref("ComparisonOperatorGrammar"), Ref("LiteralGrammar"), optional=True), optional=True, ), )
class ClusterByClauseSegment(ansi.OrderByClauseSegment): """A `CLUSTER BY` clause like in `SELECT`.""" type = "clusterby_clause" match_grammar: Matchable = StartsWith( Sequence("CLUSTER", "BY"), terminator=ansi.OrderByClauseSegment.match_grammar.terminator, # type: ignore ) parse_grammar: Optional[Matchable] = Sequence( "CLUSTER", "BY", Indent, Delimited( Sequence( OneOf( Ref("ColumnReferenceSegment"), Ref("NumericLiteralSegment"), Ref("ExpressionSegment"), ), ), terminator=OneOf(Ref.keyword("LIMIT"), Ref("FrameClauseUnitGrammar")), ), Dedent, )
class CreateTableStatementSegment(BaseSegment): """A `CREATE TABLE` statement. Full Apache Hive `CREATE TABLE` reference here: https://cwiki.apache.org/confluence/display/hive/languagemanual+ddl#LanguageManualDDL-CreateTable """ type = "create_table_statement" match_grammar = StartsWith( Sequence( "CREATE", Ref.keyword("TEMPORARY", optional=True), Ref.keyword("EXTERNAL", optional=True), "TABLE", )) parse_grammar = Sequence( "CREATE", Ref.keyword("TEMPORARY", optional=True), Ref.keyword("EXTERNAL", optional=True), "TABLE", Ref("IfNotExistsGrammar", optional=True), Ref("TableReferenceSegment"), OneOf( # Columns and comment syntax: Sequence( Bracketed( Delimited( OneOf( # TODO: support all constraints Ref("TableConstraintSegment", optional=True), Sequence( Ref("ColumnDefinitionSegment"), Ref("CommentGrammar", optional=True), ), ), bracket_pairs_set="angle_bracket_pairs", ), optional=True, ), Ref("CommentGrammar", optional=True), # `STORED AS` can be called before or after the additional table # properties below Ref("StoredAsGrammar", optional=True), Sequence( "PARTITIONED", "BY", Bracketed( Delimited( Sequence( Ref("ColumnDefinitionSegment"), Ref("CommentGrammar", optional=True), ), ), ), optional=True, ), Sequence( "CLUSTERED", "BY", Ref("BracketedColumnReferenceListGrammar"), Sequence( "SORTED", "BY", Bracketed( Delimited( Sequence( Ref("ColumnReferenceSegment"), OneOf("ASC", "DESC", optional=True), ))), optional=True, ), "INTO", Ref("NumericLiteralSegment"), "BUCKETS", optional=True, ), # Second call of `STORED AS` to match when appears after Ref("StoredAsGrammar", optional=True), Ref("SkewedByClauseSegment", optional=True), Ref("StorageFormatGrammar", optional=True), Ref("LocationGrammar", optional=True), Ref("TablePropertiesGrammar", optional=True), Ref("CommentGrammar", optional=True), Sequence( "AS", OptionallyBracketed(Ref("SelectableGrammar")), optional=True, ), ), # Create like syntax Sequence( "LIKE", Ref("TableReferenceSegment"), Ref("LocationGrammar", optional=True), Ref("TablePropertiesGrammar", optional=True), ), ), )