1"""sqlglot expressions builders."""
   2
   3from __future__ import annotations
   4
   5import re
   6import typing as t
   7
   8from sqlglot.helper import seq_get, ensure_collection, split_num_words
   9from sqlglot.errors import ParseError, TokenError
  10from sqlglot.expressions.core import (
  11    Alias,
  12    Anonymous,
  13    Boolean,
  14    Column,
  15    Condition,
  16    EQ,
  17    Expr,
  18    Identifier,
  19    Literal,
  20    Null,
  21    Placeholder,
  22    TABLE_PARTS,
  23    Var,
  24    logger,
  25    SAFE_IDENTIFIER_RE,
  26    maybe_parse,
  27    maybe_copy,
  28    to_identifier,
  29    convert,
  30    alias_,
  31    column,
  32)
  33from sqlglot.expressions.datatypes import DataType, DType, Interval
  34from sqlglot.expressions.query import (
  35    CTE,
  36    From,
  37    Schema,
  38    Select,
  39    Table,
  40    TableAlias,
  41    Tuple,
  42    Values,
  43    Where,
  44    With,
  45    Query,
  46)
  47from sqlglot.expressions.ddl import Alter, AlterRename, RenameColumn
  48from sqlglot.expressions.dml import Delete, Insert, Merge, Update, When, Whens
  49from sqlglot.expressions.functions import Case, Cast
  50from sqlglot.expressions.array import Array
  51
  52
  53if t.TYPE_CHECKING:
  54    from collections.abc import Sequence, Iterable, Iterator
  55    from sqlglot.dialects.dialect import DialectType
  56    from sqlglot.expressions.core import ExpOrStr, Func
  57    from sqlglot.expressions.datatypes import DATA_TYPE
  58    from sqlglot._typing import ParserArgs, ParserNoDialectArgs, E, P
  59    from typing_extensions import Unpack, Concatenate
  60    from sqlglot.expressions.core import Dot
  61
  62
  63def select(
  64    *expressions: ExpOrStr,
  65    dialect: DialectType = None,
  66    copy: bool = True,
  67    **opts: Unpack[ParserNoDialectArgs],
  68) -> Select:
  69    """
  70    Initializes a syntax tree from one or multiple SELECT expressions.
  71
  72    Example:
  73        >>> select("col1", "col2").from_("tbl").sql()
  74        'SELECT col1, col2 FROM tbl'
  75
  76    Args:
  77        *expressions: the SQL code string to parse as the expressions of a
  78            SELECT statement. If an Expr instance is passed, this is used as-is.
  79        dialect: the dialect used to parse the input expressions (in the case that an
  80            input expression is a SQL string).
  81        **opts: other options to use to parse the input expressions (again, in the case
  82            that an input expression is a SQL string).
  83
  84    Returns:
  85        Select: the syntax tree for the SELECT statement.
  86    """
  87    return Select().select(*expressions, dialect=dialect, copy=copy, **opts)
  88
  89
  90def from_(
  91    expression: ExpOrStr,
  92    dialect: DialectType = None,
  93    copy: bool = True,
  94    **opts: Unpack[ParserNoDialectArgs],
  95) -> Select:
  96    """
  97    Initializes a syntax tree from a FROM expression.
  98
  99    Example:
 100        >>> from_("tbl").select("col1", "col2").sql()
 101        'SELECT col1, col2 FROM tbl'
 102
 103    Args:
 104        *expression: the SQL code string to parse as the FROM expressions of a
 105            SELECT statement. If an Expr instance is passed, this is used as-is.
 106        dialect: the dialect used to parse the input expression (in the case that the
 107            input expression is a SQL string).
 108        **opts: other options to use to parse the input expressions (again, in the case
 109            that the input expression is a SQL string).
 110
 111    Returns:
 112        Select: the syntax tree for the SELECT statement.
 113    """
 114    return Select().from_(expression, dialect=dialect, copy=copy, **opts)
 115
 116
 117def update(
 118    table: str | Table,
 119    properties: dict[str, object] | None = None,
 120    where: ExpOrStr | None = None,
 121    from_: ExpOrStr | None = None,
 122    with_: dict[str, ExpOrStr] | None = None,
 123    dialect: DialectType = None,
 124    copy: bool = True,
 125    **opts: Unpack[ParserNoDialectArgs],
 126) -> Update:
 127    """
 128    Creates an update statement.
 129
 130    Example:
 131        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz_cte", where="baz_cte.id > 1 and my_table.id = baz_cte.id", with_={"baz_cte": "SELECT id FROM foo"}).sql()
 132        "WITH baz_cte AS (SELECT id FROM foo) UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz_cte WHERE baz_cte.id > 1 AND my_table.id = baz_cte.id"
 133
 134    Args:
 135        properties: dictionary of properties to SET which are
 136            auto converted to sql objects eg None -> NULL
 137        where: sql conditional parsed into a WHERE statement
 138        from_: sql statement parsed into a FROM statement
 139        with_: dictionary of CTE aliases / select statements to include in a WITH clause.
 140        dialect: the dialect used to parse the input expressions.
 141        copy: whether to copy the input expressions.
 142        **opts: other options to use to parse the input expressions.
 143
 144    Returns:
 145        Update: the syntax tree for the UPDATE statement.
 146    """
 147    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect, copy=copy))
 148    if properties:
 149        update_expr.set(
 150            "expressions",
 151            [
 152                EQ(this=maybe_parse(k, dialect=dialect, copy=copy, **opts), expression=convert(v))
 153                for k, v in properties.items()
 154            ],
 155        )
 156    if from_:
 157        update_expr.set(
 158            "from_",
 159            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", copy=copy, **opts),
 160        )
 161    if isinstance(where, Condition):
 162        where = Where(this=where)
 163    if where:
 164        update_expr.set(
 165            "where",
 166            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", copy=copy, **opts),
 167        )
 168    if with_:
 169        cte_list = [
 170            alias_(
 171                CTE(this=maybe_parse(qry, dialect=dialect, copy=copy, **opts)), alias, table=True
 172            )
 173            for alias, qry in with_.items()
 174        ]
 175        update_expr.set(
 176            "with_",
 177            With(expressions=cte_list),
 178        )
 179    return update_expr
 180
 181
 182def delete(
 183    table: ExpOrStr,
 184    where: ExpOrStr | None = None,
 185    returning: ExpOrStr | None = None,
 186    dialect: DialectType = None,
 187    **opts: Unpack[ParserNoDialectArgs],
 188) -> Delete:
 189    """
 190    Builds a delete statement.
 191
 192    Example:
 193        >>> delete("my_table", where="id > 1").sql()
 194        'DELETE FROM my_table WHERE id > 1'
 195
 196    Args:
 197        where: sql conditional parsed into a WHERE statement
 198        returning: sql conditional parsed into a RETURNING statement
 199        dialect: the dialect used to parse the input expressions.
 200        **opts: other options to use to parse the input expressions.
 201
 202    Returns:
 203        Delete: the syntax tree for the DELETE statement.
 204    """
 205    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
 206    if where:
 207        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
 208    if returning:
 209        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
 210    return delete_expr
 211
 212
 213def insert(
 214    expression: ExpOrStr,
 215    into: str | Table,
 216    columns: Sequence[str | Identifier] | None = None,
 217    overwrite: bool | None = None,
 218    returning: ExpOrStr | None = None,
 219    dialect: DialectType = None,
 220    copy: bool = True,
 221    **opts: Unpack[ParserNoDialectArgs],
 222) -> Insert:
 223    """
 224    Builds an INSERT statement.
 225
 226    Example:
 227        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
 228        'INSERT INTO tbl VALUES (1, 2, 3)'
 229
 230    Args:
 231        expression: the sql string or expression of the INSERT statement
 232        into: the tbl to insert data to.
 233        columns: optionally the table's column names.
 234        overwrite: whether to INSERT OVERWRITE or not.
 235        returning: sql conditional parsed into a RETURNING statement
 236        dialect: the dialect used to parse the input expressions.
 237        copy: whether to copy the expression.
 238        **opts: other options to use to parse the input expressions.
 239
 240    Returns:
 241        Insert: the syntax tree for the INSERT statement.
 242    """
 243    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
 244    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
 245
 246    if columns:
 247        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
 248
 249    insert = Insert(this=this, expression=expr, overwrite=overwrite)
 250
 251    if returning:
 252        insert = insert.returning(returning, dialect=dialect, copy=False, **opts)
 253
 254    return insert
 255
 256
 257def merge(
 258    *when_exprs: ExpOrStr,
 259    into: ExpOrStr,
 260    using: ExpOrStr,
 261    on: ExpOrStr,
 262    returning: ExpOrStr | None = None,
 263    dialect: DialectType = None,
 264    copy: bool = True,
 265    **opts: Unpack[ParserNoDialectArgs],
 266) -> Merge:
 267    """
 268    Builds a MERGE statement.
 269
 270    Example:
 271        >>> merge("WHEN MATCHED THEN UPDATE SET col1 = source_table.col1",
 272        ...       "WHEN NOT MATCHED THEN INSERT (col1) VALUES (source_table.col1)",
 273        ...       into="my_table",
 274        ...       using="source_table",
 275        ...       on="my_table.id = source_table.id").sql()
 276        'MERGE INTO my_table USING source_table ON my_table.id = source_table.id WHEN MATCHED THEN UPDATE SET col1 = source_table.col1 WHEN NOT MATCHED THEN INSERT (col1) VALUES (source_table.col1)'
 277
 278    Args:
 279        *when_exprs: The WHEN clauses specifying actions for matched and unmatched rows.
 280        into: The target table to merge data into.
 281        using: The source table to merge data from.
 282        on: The join condition for the merge.
 283        returning: The columns to return from the merge.
 284        dialect: The dialect used to parse the input expressions.
 285        copy: Whether to copy the expression.
 286        **opts: Other options to use to parse the input expressions.
 287
 288    Returns:
 289        Merge: The syntax tree for the MERGE statement.
 290    """
 291    expressions: list[Expr] = []
 292    for when_expr in when_exprs:
 293        expression = maybe_parse(when_expr, dialect=dialect, copy=copy, into=Whens, **opts)
 294        expressions.extend([expression] if isinstance(expression, When) else expression.expressions)
 295
 296    merge = Merge(
 297        this=maybe_parse(into, dialect=dialect, copy=copy, **opts),
 298        using=maybe_parse(using, dialect=dialect, copy=copy, **opts),
 299        on=maybe_parse(on, dialect=dialect, copy=copy, **opts),
 300        whens=Whens(expressions=expressions),
 301    )
 302    if returning:
 303        merge = merge.returning(returning, dialect=dialect, copy=False, **opts)
 304
 305    if isinstance(using_clause := merge.args.get("using"), Alias):
 306        using_clause.replace(alias_(using_clause.this, using_clause.args["alias"], table=True))
 307
 308    return merge
 309
 310
 311def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
 312    """
 313    Parses a given string into an identifier.
 314
 315    Args:
 316        name: The name to parse into an identifier.
 317        dialect: The dialect to parse against.
 318
 319    Returns:
 320        The identifier ast node.
 321    """
 322    if isinstance(name, str) and SAFE_IDENTIFIER_RE.match(name):
 323        # Simple names parse to a single unquoted identifier in all dialects, so we can
 324        # avoid the tokenizer/parser round-trip for them.
 325        return Identifier(this=name, quoted=False)
 326
 327    try:
 328        expression = maybe_parse(name, dialect=dialect, into=Identifier)
 329    except (ParseError, TokenError):
 330        expression = to_identifier(name)
 331
 332    return expression
 333
 334
 335INTERVAL_STRING_RE = re.compile(r"\s*(-?[0-9]+(?:\.[0-9]+)?)\s*([a-zA-Z]+)\s*")
 336
 337
 338INTERVAL_DAY_TIME_RE = re.compile(
 339    r"\s*-?\s*\d+(?:\.\d+)?\s+(?:-?(?:\d+:)?\d+:\d+(?:\.\d+)?|-?(?:\d+:){1,2}|:)\s*"
 340)
 341
 342
 343def to_interval(interval: str | Expr) -> Interval:
 344    """Builds an interval expression from a string like '1 day' or '5 months'."""
 345    if isinstance(interval, Literal):
 346        if not interval.is_string:
 347            raise ValueError("Invalid interval string.")
 348
 349        interval = interval.this
 350
 351    interval = maybe_parse(f"INTERVAL {interval}")
 352    assert isinstance(interval, Interval)
 353    return interval
 354
 355
 356def to_table(
 357    sql_path: str | Table, dialect: DialectType = None, copy: bool = True, **kwargs: object
 358) -> Table:
 359    """
 360    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
 361    If a table is passed in then that table is returned.
 362
 363    Args:
 364        sql_path: a `[catalog].[schema].[table]` string.
 365        dialect: the source dialect according to which the table name will be parsed.
 366        copy: Whether to copy a table if it is passed in.
 367        kwargs: the kwargs to instantiate the resulting `Table` expression with.
 368
 369    Returns:
 370        A table expression.
 371    """
 372    if isinstance(sql_path, Table):
 373        return maybe_copy(sql_path, copy=copy)
 374
 375    try:
 376        table = maybe_parse(sql_path, into=Table, dialect=dialect)
 377    except ParseError:
 378        catalog, db, this = split_num_words(sql_path, ".", 3)
 379
 380        if not this:
 381            raise
 382
 383        table = table_(this, db=db, catalog=catalog)
 384
 385    return table.set_kwargs(kwargs)
 386
 387
 388def to_column(
 389    sql_path: str | Column,
 390    quoted: bool | None = None,
 391    dialect: DialectType = None,
 392    copy: bool = True,
 393    **kwargs: t.Any,
 394) -> Column | Dot:
 395    """
 396    Create a column from a `[table].[column]` sql path. Table is optional.
 397    If a column is passed in then that column is returned.
 398
 399    Args:
 400        sql_path: a `[table].[column]` string.
 401        quoted: Whether or not to force quote identifiers.
 402        dialect: the source dialect according to which the column name will be parsed.
 403        copy: Whether to copy a column if it is passed in.
 404        kwargs: the kwargs to instantiate the resulting `Column` expression with.
 405
 406    Returns:
 407        A column expression.
 408    """
 409    if isinstance(sql_path, Column):
 410        return maybe_copy(sql_path, copy=copy)
 411
 412    try:
 413        col = maybe_parse(sql_path, into=Column, dialect=dialect)
 414    except ParseError:
 415        return column(*reversed(sql_path.split(".")), quoted=quoted, **kwargs)
 416
 417    for k, v in kwargs.items():
 418        col.set(k, v)
 419
 420    if quoted:
 421        for i in col.find_all(Identifier):
 422            i.set("quoted", True)
 423
 424    return col
 425
 426
 427def subquery(
 428    expression: ExpOrStr,
 429    alias: Identifier | str | None = None,
 430    dialect: DialectType = None,
 431    copy: bool = True,
 432    **opts: Unpack[ParserNoDialectArgs],
 433) -> Select:
 434    """
 435    Build a subquery expression that's selected from.
 436
 437    Example:
 438        >>> subquery('select x from tbl', 'bar').select('x').sql()
 439        'SELECT x FROM (SELECT x FROM tbl) AS bar'
 440
 441    Args:
 442        expression: the SQL code strings to parse.
 443            If an Expr instance is passed, this is used as-is.
 444        alias: the alias name to use.
 445        dialect: the dialect used to parse the input expression.
 446        **opts: other options to use to parse the input expressions.
 447
 448    Returns:
 449        A new Select instance with the subquery expression included.
 450    """
 451    expr = (
 452        maybe_parse(expression, dialect=dialect, **opts).assert_is(Query).subquery(alias, copy=copy)
 453    )
 454    return Select().from_(expr, dialect=dialect, **opts)
 455
 456
 457def cast(
 458    expression: ExpOrStr,
 459    to: DATA_TYPE,
 460    copy: bool = True,
 461    dialect: DialectType = None,
 462    **opts: Unpack[ParserNoDialectArgs],
 463) -> Cast:
 464    """Cast an expression to a data type.
 465
 466    Example:
 467        >>> cast('x + 1', 'int').sql()
 468        'CAST(x + 1 AS INT)'
 469
 470    Args:
 471        expression: The expression to cast.
 472        to: The datatype to cast to.
 473        copy: Whether to copy the supplied expressions.
 474        dialect: The target dialect. This is used to prevent a re-cast in the following scenario:
 475            - The expression to be cast is already a exp.Cast expression
 476            - The existing cast is to a type that is logically equivalent to new type
 477
 478            For example, if :expression='CAST(x as DATETIME)' and :to=Type.TIMESTAMP,
 479            but in the target dialect DATETIME is mapped to TIMESTAMP, then we will NOT return `CAST(x (as DATETIME) as TIMESTAMP)`
 480            and instead just return the original expression `CAST(x as DATETIME)`.
 481
 482            This is to prevent it being output as a double cast `CAST(x (as TIMESTAMP) as TIMESTAMP)` once the DATETIME -> TIMESTAMP
 483            mapping is applied in the target dialect generator.
 484
 485    Returns:
 486        The new Cast instance.
 487    """
 488    expr = maybe_parse(expression, copy=copy, dialect=dialect, **opts)
 489    data_type = DataType.build(to, copy=copy, dialect=dialect, **opts)
 490
 491    # dont re-cast if the expression is already a cast to the correct type
 492    if isinstance(expr, Cast):
 493        from sqlglot.dialects.dialect import Dialect
 494
 495        target_dialect = Dialect.get_or_raise(dialect)
 496        type_mapping = target_dialect.generator_class.TYPE_MAPPING
 497
 498        existing_cast_type: DType = expr.to.this
 499        new_cast_type: DType = data_type.this
 500        types_are_equivalent = type_mapping.get(
 501            existing_cast_type, existing_cast_type.value
 502        ) == type_mapping.get(new_cast_type, new_cast_type.value)
 503
 504        if expr.is_type(data_type) or types_are_equivalent:
 505            return expr
 506
 507    expr = Cast(this=expr, to=data_type)
 508    expr.type = data_type
 509
 510    return expr
 511
 512
 513def table_(
 514    table: Identifier | str,
 515    db: Identifier | str | None = None,
 516    catalog: Identifier | str | None = None,
 517    quoted: bool | None = None,
 518    alias: Identifier | str | None = None,
 519) -> Table:
 520    """Build a Table.
 521
 522    Args:
 523        table: Table name.
 524        db: Database name.
 525        catalog: Catalog name.
 526        quote: Whether to force quotes on the table's identifiers.
 527        alias: Table's alias.
 528
 529    Returns:
 530        The new Table instance.
 531    """
 532    return Table(
 533        this=to_identifier(table, quoted=quoted) if table else None,
 534        db=to_identifier(db, quoted=quoted) if db else None,
 535        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
 536        alias=TableAlias(this=to_identifier(alias)) if alias else None,
 537    )
 538
 539
 540def values(
 541    values: Iterable[tuple[object, ...] | Tuple],
 542    alias: str | None = None,
 543    columns: Iterable[str] | dict[str, DataType] | None = None,
 544) -> Values:
 545    """Build VALUES statement.
 546
 547    Example:
 548        >>> values([(1, '2')]).sql()
 549        "VALUES (1, '2')"
 550
 551    Args:
 552        values: values statements that will be converted to SQL
 553        alias: optional alias
 554        columns: Optional list of ordered column names or ordered dictionary of column names to types.
 555         If either are provided then an alias is also required.
 556
 557    Returns:
 558        Values: the Values expression object
 559    """
 560    if columns and not alias:
 561        raise ValueError("Alias is required when providing columns")
 562
 563    return Values(
 564        expressions=[convert(tup) for tup in values],
 565        alias=(
 566            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
 567            if columns
 568            else (TableAlias(this=to_identifier(alias)) if alias else None)
 569        ),
 570    )
 571
 572
 573def var(name: ExpOrStr | None) -> Var:
 574    """Build a SQL variable.
 575
 576    Example:
 577        >>> repr(var('x'))
 578        'Var(this=x)'
 579
 580        >>> repr(var(column('x', table='y')))
 581        'Var(this=x)'
 582
 583    Args:
 584        name: The name of the var or an expression who's name will become the var.
 585
 586    Returns:
 587        The new variable node.
 588    """
 589    if not name:
 590        raise ValueError("Cannot convert empty name into var.")
 591
 592    if isinstance(name, Expr):
 593        name = name.name
 594    return Var(this=name)
 595
 596
 597def rename_table(
 598    old_name: str | Table,
 599    new_name: str | Table,
 600    dialect: DialectType = None,
 601) -> Alter:
 602    """Build ALTER TABLE... RENAME... expression
 603
 604    Args:
 605        old_name: The old name of the table
 606        new_name: The new name of the table
 607        dialect: The dialect to parse the table.
 608
 609    Returns:
 610        Alter table expression
 611    """
 612    old_table = to_table(old_name, dialect=dialect)
 613    new_table = to_table(new_name, dialect=dialect)
 614    return Alter(
 615        this=old_table,
 616        kind="TABLE",
 617        actions=[
 618            AlterRename(this=new_table),
 619        ],
 620    )
 621
 622
 623def rename_column(
 624    table_name: str | Table,
 625    old_column_name: str | Column,
 626    new_column_name: str | Column,
 627    exists: bool | None = None,
 628    dialect: DialectType = None,
 629) -> Alter:
 630    """Build ALTER TABLE... RENAME COLUMN... expression
 631
 632    Args:
 633        table_name: Name of the table
 634        old_column: The old name of the column
 635        new_column: The new name of the column
 636        exists: Whether to add the `IF EXISTS` clause
 637        dialect: The dialect to parse the table/column.
 638
 639    Returns:
 640        Alter table expression
 641    """
 642    table = to_table(table_name, dialect=dialect)
 643    old_column = to_column(old_column_name, dialect=dialect)
 644    new_column = to_column(new_column_name, dialect=dialect)
 645    return Alter(
 646        this=table,
 647        kind="TABLE",
 648        actions=[
 649            RenameColumn(this=old_column, to=new_column, exists=exists),
 650        ],
 651    )
 652
 653
 654def replace_children(
 655    expression: Expr,
 656    fun: t.Callable[Concatenate[Expr, P], object],
 657    *args: P.args,
 658    **kwargs: P.kwargs,
 659) -> None:
 660    """
 661    Replace children of an expression with the result of a lambda fun(child) -> exp.
 662    """
 663    for k, v in tuple(expression.args.items()):
 664        is_list_arg = type(v) is list
 665
 666        child_nodes = v if is_list_arg else [v]
 667        new_child_nodes = []
 668
 669        for cn in child_nodes:
 670            if isinstance(cn, Expr):
 671                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
 672                    new_child_nodes.append(child_node)
 673            else:
 674                new_child_nodes.append(cn)
 675
 676        if is_list_arg:
 677            expression.set(k, new_child_nodes)
 678        else:
 679            expression.set(k, seq_get(new_child_nodes, 0))
 680
 681
 682def replace_tree(
 683    expression: Expr,
 684    fun: t.Callable[[Expr], Expr],
 685    prune: t.Callable[[Expr], bool] | None = None,
 686) -> Expr:
 687    """
 688    Replace an entire tree with the result of function calls on each node.
 689
 690    This will be traversed in reverse dfs, so leaves first.
 691    If new nodes are created as a result of function calls, they will also be traversed.
 692    """
 693    stack = list(expression.dfs(prune=prune))
 694
 695    while stack:
 696        node = stack.pop()
 697        new_node = fun(node)
 698
 699        if new_node is not node:
 700            node.replace(new_node)
 701
 702            if isinstance(new_node, Expr):
 703                stack.append(new_node)
 704
 705    return new_node
 706
 707
 708def find_tables(expression: Expr) -> set[Table]:
 709    """
 710    Find all tables referenced in a query.
 711
 712    Args:
 713        expressions: The query to find the tables in.
 714
 715    Returns:
 716        A set of all the tables.
 717    """
 718    from sqlglot.optimizer.scope import traverse_scope
 719
 720    return {
 721        table
 722        for scope in traverse_scope(expression)
 723        for table in scope.tables
 724        if isinstance(table, Table) and table.name and table.name not in scope.cte_sources
 725    }
 726
 727
 728def column_table_names(expression: Expr, exclude: str = "") -> set[str]:
 729    """
 730    Return all table names referenced through columns in an expression.
 731
 732    Example:
 733        >>> import sqlglot
 734        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
 735        ['a', 'c']
 736
 737    Args:
 738        expression: expression to find table names.
 739        exclude: a table name to exclude
 740
 741    Returns:
 742        A list of unique names.
 743    """
 744    return {
 745        table
 746        for table in (column.table for column in expression.find_all(Column))
 747        if table and table != exclude
 748    }
 749
 750
 751def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
 752    """Get the full name of a table as a string.
 753
 754    Args:
 755        table: Table expression node or string.
 756        dialect: The dialect to generate the table name for.
 757        identify: Determines when an identifier should be quoted. Possible values are:
 758            False (default): Never quote, except in cases where it's mandatory by the dialect.
 759            True: Always quote.
 760
 761    Examples:
 762        >>> from sqlglot import exp, parse_one
 763        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
 764        'a.b.c'
 765
 766    Returns:
 767        The table name.
 768    """
 769
 770    expr = maybe_parse(table, into=Table, dialect=dialect)
 771
 772    if not expr:
 773        raise ValueError(f"Cannot parse {table}")
 774
 775    return ".".join(
 776        (
 777            part.sql(dialect=dialect, identify=True, copy=False, comments=False)
 778            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
 779            else part.name
 780        )
 781        for part in expr.parts
 782    )
 783
 784
 785def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
 786    """Returns a case normalized table name without quotes.
 787
 788    Args:
 789        table: the table to normalize
 790        dialect: the dialect to use for normalization rules
 791        copy: whether to copy the expression.
 792
 793    Examples:
 794        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
 795        'A-B.c'
 796    """
 797    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
 798
 799    return ".".join(
 800        p.name
 801        for p in normalize_identifiers(
 802            to_table(table, dialect=dialect, copy=copy), dialect=dialect
 803        ).parts
 804    )
 805
 806
 807def replace_tables(
 808    expression: E, mapping: dict[str, str], dialect: DialectType = None, copy: bool = True
 809) -> E:
 810    """Replace all tables in expression according to the mapping.
 811
 812    Args:
 813        expression: expression node to be transformed and replaced.
 814        mapping: mapping of table names.
 815        dialect: the dialect of the mapping table
 816        copy: whether to copy the expression.
 817
 818    Examples:
 819        >>> from sqlglot import exp, parse_one
 820        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
 821        'SELECT * FROM c /* a.b */'
 822
 823    Returns:
 824        The mapped expression.
 825    """
 826
 827    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
 828
 829    def _replace_tables(node: Expr) -> Expr:
 830        if isinstance(node, Table) and node.meta_get("replace") is not False:
 831            original = normalize_table_name(node, dialect=dialect)
 832            new_name = mapping.get(original)
 833
 834            if new_name:
 835                table = to_table(
 836                    new_name,
 837                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
 838                    dialect=dialect,
 839                )
 840                table.add_comments([original])
 841                return table
 842        return node
 843
 844    return expression.transform(_replace_tables, copy=copy)  # type: ignore
 845
 846
 847def replace_placeholders(expression: Expr, *args: object, **kwargs: t.Any) -> Expr:
 848    """Replace placeholders in an expression.
 849
 850    Args:
 851        expression: expression node to be transformed and replaced.
 852        args: positional names that will substitute unnamed placeholders in the given order.
 853        kwargs: keyword arguments that will substitute named placeholders.
 854
 855    Examples:
 856        >>> from sqlglot import exp, parse_one
 857        >>> replace_placeholders(
 858        ...     parse_one("select * from :tbl where ? = ?"),
 859        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
 860        ... ).sql()
 861        "SELECT * FROM foo WHERE str_col = 'b'"
 862
 863    Returns:
 864        The mapped expression.
 865    """
 866
 867    def _replace_placeholders(node: Expr, args: Iterator[object], **kwargs: object) -> Expr:
 868        if isinstance(node, Placeholder):
 869            if node.this:
 870                new_name = kwargs.get(node.this)
 871                if new_name is not None:
 872                    return convert(new_name)
 873            else:
 874                try:
 875                    return convert(next(args))
 876                except StopIteration:
 877                    pass
 878        return node
 879
 880    return expression.transform(_replace_placeholders, iter(args), **kwargs)
 881
 882
 883def expand(
 884    expression: Expr,
 885    sources: dict[str, Query | t.Callable[[], Query]],
 886    dialect: DialectType = None,
 887    copy: bool = True,
 888) -> Expr:
 889    """Transforms an expression by expanding all referenced sources into subqueries.
 890
 891    Examples:
 892        >>> from sqlglot import parse_one
 893        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
 894        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
 895
 896        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
 897        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
 898
 899    Args:
 900        expression: The expression to expand.
 901        sources: A dict of name to query or a callable that provides a query on demand.
 902        dialect: The dialect of the sources dict or the callable.
 903        copy: Whether to copy the expression during transformation. Defaults to True.
 904
 905    Returns:
 906        The transformed expression.
 907    """
 908    normalized_sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
 909
 910    def _expand(node: Expr):
 911        if isinstance(node, Table):
 912            name = normalize_table_name(node, dialect=dialect)
 913            source = normalized_sources.get(name)
 914
 915            if source:
 916                # Create a subquery with the same alias (or table name if no alias)
 917                parsed_source = source() if callable(source) else source
 918                subquery = parsed_source.subquery(node.alias or name)
 919                subquery.comments = [f"source: {name}"]
 920
 921                # Continue expanding within the subquery
 922                return subquery.transform(_expand, copy=False)
 923
 924        return node
 925
 926    return expression.transform(_expand, copy=copy)
 927
 928
 929def func(
 930    name: str, *args: t.Any, copy: bool = True, dialect: DialectType = None, **kwargs: t.Any
 931) -> Func:
 932    """
 933    Returns a Func expression.
 934
 935    Examples:
 936        >>> func("abs", 5).sql()
 937        'ABS(5)'
 938
 939        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
 940        'CAST(5 AS DOUBLE)'
 941
 942    Args:
 943        name: the name of the function to build.
 944        args: the args used to instantiate the function of interest.
 945        copy: whether to copy the argument expressions.
 946        dialect: the source dialect.
 947        kwargs: the kwargs used to instantiate the function of interest.
 948
 949    Note:
 950        The arguments `args` and `kwargs` are mutually exclusive.
 951
 952    Returns:
 953        An instance of the function of interest, or an anonymous function, if `name` doesn't
 954        correspond to an existing `sqlglot.expressions.Func` class.
 955    """
 956    if args and kwargs:
 957        raise ValueError("Can't use both args and kwargs to instantiate a function.")
 958
 959    from sqlglot.dialects.dialect import Dialect
 960
 961    dialect = Dialect.get_or_raise(dialect)
 962
 963    converted: list[Expr] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
 964    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
 965
 966    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
 967    if constructor:
 968        if converted:
 969            try:
 970                function = constructor(converted)
 971            except TypeError:
 972                function = constructor(converted, dialect=dialect)
 973        elif constructor.__name__ == "from_arg_list":
 974            function = constructor.__self__(**kwargs)  # type: ignore
 975        else:
 976            from sqlglot.expressions import FUNCTION_BY_NAME as _FUNCTION_BY_NAME
 977
 978            constructor = _FUNCTION_BY_NAME.get(name.upper())
 979            if constructor:
 980                function = constructor(**kwargs)
 981            else:
 982                raise ValueError(
 983                    f"Unable to convert '{name}' into a Func. Either manually construct "
 984                    "the Func expression of interest or parse the function call."
 985                )
 986    else:
 987        kwargs = kwargs or {"expressions": converted}
 988        function = Anonymous(this=name, **kwargs)
 989
 990    for error_message in function.error_messages(converted):
 991        raise ValueError(error_message)
 992
 993    return function
 994
 995
 996def case(
 997    expression: ExpOrStr | None = None,
 998    copy: bool = True,
 999    **opts: Unpack[ParserArgs],
1000) -> Case:
1001    """
1002    Initialize a CASE statement.
1003
1004    Example:
1005        case().when("a = 1", "foo").else_("bar")
1006
1007    Args:
1008        expression: Optionally, the input expression (not all dialects support this)
1009        copy: whether to copy the argument expressions.
1010        **opts: Extra keyword arguments for parsing `expression`
1011    """
1012    if expression is not None:
1013        this = maybe_parse(expression, copy=copy, **opts)
1014    else:
1015        this = None
1016    return Case(this=this, ifs=[])
1017
1018
1019def array(
1020    *expressions: ExpOrStr,
1021    copy: bool = True,
1022    dialect: DialectType = None,
1023    **kwargs: Unpack[ParserNoDialectArgs],
1024) -> Array:
1025    """
1026    Returns an array.
1027
1028    Examples:
1029        >>> array(1, 'x').sql()
1030        'ARRAY(1, x)'
1031
1032    Args:
1033        expressions: the expressions to add to the array.
1034        copy: whether to copy the argument expressions.
1035        dialect: the source dialect.
1036        kwargs: the kwargs used to instantiate the function of interest.
1037
1038    Returns:
1039        An array expression.
1040    """
1041    return Array(
1042        expressions=[
1043            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
1044            for expression in expressions
1045        ]
1046    )
1047
1048
1049def tuple_(
1050    *expressions: ExpOrStr,
1051    copy: bool = True,
1052    dialect: DialectType = None,
1053    **kwargs: Unpack[ParserNoDialectArgs],
1054) -> Tuple:
1055    """
1056    Returns an tuple.
1057
1058    Examples:
1059        >>> tuple_(1, 'x').sql()
1060        '(1, x)'
1061
1062    Args:
1063        expressions: the expressions to add to the tuple.
1064        copy: whether to copy the argument expressions.
1065        dialect: the source dialect.
1066        kwargs: the kwargs used to instantiate the function of interest.
1067
1068    Returns:
1069        A tuple expression.
1070    """
1071    return Tuple(
1072        expressions=[
1073            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
1074            for expression in expressions
1075        ]
1076    )
1077
1078
1079def true() -> Boolean:
1080    """
1081    Returns a true Boolean expression.
1082    """
1083    return Boolean(this=True)
1084
1085
1086def false() -> Boolean:
1087    """
1088    Returns a false Boolean expression.
1089    """
1090    return Boolean(this=False)
1091
1092
1093def null() -> Null:
1094    """
1095    Returns a Null expression.
1096    """
1097    return Null()
1098
1099
1100def apply_index_offset(
1101    this: Expr,
1102    expressions: list[E],
1103    offset: int,
1104    dialect: DialectType = None,
1105) -> list[E]:
1106    if not offset or len(expressions) != 1:
1107        return expressions
1108
1109    expression = expressions[0]
1110
1111    from sqlglot.optimizer.annotate_types import annotate_types
1112    from sqlglot.optimizer.simplify import simplify
1113
1114    if not this.type:
1115        annotate_types(this, dialect=dialect)
1116
1117    if t.cast(DataType, this.type).this not in (
1118        DType.UNKNOWN,
1119        DType.ARRAY,
1120    ):
1121        return expressions
1122
1123    if not expression.type:
1124        annotate_types(expression, dialect=dialect)
1125
1126    if t.cast(DataType, expression.type).this in DataType.INTEGER_TYPES:
1127        logger.info("Applying array index offset (%s)", offset)
1128        expression = simplify(expression + offset)
1129        return [expression]
1130
1131    return expressions
1132
1133
1134NONNULL_CONSTANTS = (
1135    Literal,
1136    Boolean,
1137)
1138
1139CONSTANTS = (
1140    Literal,
1141    Boolean,
1142    Null,
1143)