#sqlglot.mbt

let sql = @sqlglot.transpile(
  "SELECT EPOCH_MS(1618088028295)",
  read="duckdb",
  write="hive",
)[0]
// SELECT FROM_UNIXTIME(1618088028295 / POW(10, 3))

let ast = @sqlglot.parse_one("SELECT a FROM t WHERE b > 1", read="postgres")
let out = @sqlglot.generate(ast, dialect="snowflake", pretty=true)

#Status

#Known differences

• Integers are 64-bit (Python's are unbounded).
• Things that need Python runtime features aren't supported, such as defining new expression classes at runtime.
• The port stores no None argument values in Expr::args. An argument given as None at construction (mk(Column, [("db", null_arg)])) is remembered only as a key. Python's "db" in e.args is Expr::has_key, and list(e.args) is Expr::arg_keys. get, has, equality, hashing, serde and SQL generation ignore the key, as Python ignores None values. set(key, None) removes the key, as in Python. Use has for Python's truthiness of e.args.get(key), get(key) is None for is None, and has_key for in. False, 0, "" and [] are stored and behave as in Python: False and [] compare equal to a missing argument, while 0 and "" don't (except in Literal and Identifier, which hash raw values).
• None elements of list arguments are dropped (Array[Expr?] arguments lose their None entries). Python keeps them, which changes equality, serde and SQL. Python's generator skips a None element but still writes the separator before it, so Tuple(expressions=[1, None]) is (1, ) in Python and (1) in the port. List elements that generate no SQL but aren't None, such as "", degrade exactly as in Python.
• A Literal without its required is_string argument is generated as a number. Python raises KeyError.
• Errors are variants of SqlglotError. Python raises some builtin exceptions instead (for example AttributeError from Dialect.get_or_raise, or a plain SqlglotError from lineage). The port raises its closest variant, usually ValueError, with Python's message.

#Layout

• src/core: the AST (Expr, with a generated Kind enum for every Python expression class), tokenizer, parser, generator, transforms, builders, and the dialect infrastructure and shared dialect helpers (the module-level functions in dialects/dialect.py).
• src/dialects/<name>: one package per dialect.
  • gen_config.mbt (generated) holds the dialect's data class attributes, stored as diffs from the parent dialect.
  • tokenizer.mbt, parser.mbt and generator.mbt hold the hand-ported callables and method overrides.
  • dialect.mbt (generated) derives the dialect from its parent and registers it.
• src/optimizer: schema, scope, the optimizer rules, optimize, and per-dialect type annotators (sqlglot/typing). It installs the real annotate_types and simplify into core's hooks.
• src/lineage, src/diff, src/planner, src/executor, src/anonymize: ports of the corresponding Python modules. src/cli and src/cmd/sqlglot provide the command-line tool.
• src/ (package bobzhang/sqlglot): the facade (transpile, parse_one, parse, generate, dialect). It imports and registers all dialects.
• src/tests, src/generator_tests, src/dialect_tests: conformance suites generated from Python. src/dialect_tests is generated by tools/extract_dialect_tests.py (run with --manifest) followed by tools/gen_dialect_fixtures.py cases.jsonl manifest.json. The extraction fails if any Python test fails under instrumentation. The generation fails if a test method's direct assertions are neither ported to src/dialect_unit_tests nor excluded.
• tools/: generators for metadata, configuration and fixtures. They run against the Python checkout in .repos/sqlglot.

#How Python features map to MoonBit

• Expression subclasses become a single dynamic Expr { kind, args }.
  • Python isinstance(e, exp.Foo) becomes e.kind.is_a(Foo), which follows the generated MRO tables.
  • Python type(e) is exp.Foo becomes e.kind == Foo.
• Dialect subclassing: Dialect::subclass(parent, name, configure) copies the parent's configuration and its function tables (FUNCTIONS, *_PARSERS, TRANSFORMS, method overrides), then applies the child's changes.
• Overridden methods go through typed hook tables, ParserHooks and GeneratorHooks.
• super(): a child dialect captures its parent's implementation when it is configured. See docs/review-02-dialects.md.
• Unicode: str.upper/lower/casefold (full mappings, ß -> SS, Final_Sigma) and the str.is* / re (\w, \d, \s, re.IGNORECASE) character classes use tables generated from Python's Unicode database (tools/gen_unicode_tables.py -> src/core/gen_unicode.mbt, helpers in src/core/unicode.mbt). Strings are indexed by code point, as in Python.
• Integers: SQL number literals stay text, and constant folding uses big integers (as do relativedelta date shifts), so they behave like Python at any size. Integers that are stored as host values are Int64: AST arguments (Value::Int, e.g. JSON path subscripts and serde payloads), PyInt (Expr.to_py()) and the executor's ints. Where Python would hold an integer outside Int64 there, the port raises an error whose message mentions "Int64 range" (@core.int64_range_error; an OverflowError in the executor) instead of wrapping. src/robust_tests checks both against Python (tools/gen_unicode_fixtures.py, tools/gen_numeric_fixtures.py).

#Development

moon check
moon test -p bobzhang/sqlglot/tests            # base parser/generator conformance
moon test -p bobzhang/sqlglot/generator_tests  # generator conformance
moon test -p bobzhang/sqlglot/dialect_tests    # per-dialect conformance (prints DIALECT <module>: ...)
moon test -p bobzhang/sqlglot/dialect_tests -F "*dialect snowflake*"
moon test                                         # everything (845 tests)

moon run src/bench --release [--target native] -- [case] [op] [size]
moon run src/bench --release -- many_ctes rules 512   # per optimizer rule

#bobzhang/sqlglot

import {
  "bobzhang/sqlglot",
}

#Transpiling

///|
test "transpile between dialects" {
  inspect(
    @sqlglot.transpile(
      "SELECT EPOCH_MS(1618088028295)",
      read="duckdb",
      write="hive",
    )[0],
    content="SELECT FROM_UNIXTIME(1618088028295 / POW(10, 3))",
  )
  inspect(
    @sqlglot.transpile(
      "SELECT STRFTIME(x, '%y-%-m-%S')",
      read="duckdb",
      write="hive",
    )[0],
    content="SELECT DATE_FORMAT(x, 'yy-M-ss')",
  )
}

///|
test "generator options" {
  inspect(
    @sqlglot.transpile("SELECT a FROM t WHERE b = 1", write="spark", identify=Always)[0],
    content="SELECT `a` FROM `t` WHERE `b` = 1",
  )
  inspect(
    @sqlglot.transpile(
      "SELECT cardinality(x) FROM t",
      read="presto",
      write="presto",
      normalize_functions=Lower,
    )[0],
    content="SELECT cardinality(x) FROM t",
  )
  inspect(
    @sqlglot.transpile(
      "WITH baz AS (SELECT a, c FROM foo WHERE a = 1) SELECT f.a, b.b, baz.c, CAST(\"b\".\"a\" AS REAL) d FROM foo f JOIN bar b ON f.a = b.a LEFT JOIN baz ON f.a = baz.a",
      write="spark",
      identify=Always,
      pretty=true,
    )[0],
    content=(
      #|WITH `baz` AS (
      #|  SELECT
      #|    `a`,
      #|    `c`
      #|  FROM `foo`
      #|  WHERE
      #|    `a` = 1
      #|)
      #|SELECT
      #|  `f`.`a`,
      #|  `b`.`b`,
      #|  `baz`.`c`,
      #|  CAST(`b`.`a` AS FLOAT) AS `d`
      #|FROM `foo` AS `f`
      #|JOIN `bar` AS `b`
      #|  ON `f`.`a` = `b`.`a`
      #|LEFT JOIN `baz`
      #|  ON `f`.`a` = `baz`.`a`
    ),
  )
}

#Parsing and errors

///|
test "parse, inspect and generate" {
  let ast : @sqlglot.Expr = @sqlglot.parse_one(
    "SELECT a, b + 1 AS c FROM t WHERE a > 1",
  )
  inspect(
    ast.find_all([Column]).map(c => c.name()).collect().join(", "),
    content="a, b, a",
  )
  inspect(
    @sqlglot.generate(ast, dialect="spark"),
    content="SELECT a, b + 1 AS c FROM t WHERE a > 1",
  )
}

///|
test "parse errors" {
  try @sqlglot.parse_one("SELECT foo FROM (SELECT baz FROM t") catch {
    @sqlglot.SqlglotError::ParseError(_, errors) => {
      let e = errors[0]
      inspect("\{e.description} at \{e.line}:\{e.col}", content="Expecting ) at 1:34")
    }
    _ => fail("expected a parse error")
  } noraise {
    _ => fail("expected a parse error")
  }
}

#Building queries

///|
test "build queries" {
  let columns : Array[&@sqlglot.IntoPy] = [@sqlglot.column("a"), "b + 1 AS c"]
  let base = @sqlglot.select(columns).from_("t")
  let q1 = base.where_(["a > 1"])
  let conditions : Array[&@sqlglot.IntoPy] = [
    @sqlglot.condition("a < 0"),
    "c IS NOT NULL",
  ]
  let q2 = base
    .where_([@sqlglot.and_(conditions)])
    .order_by(["c"])
    .limit_(10)
  inspect(@sqlglot.generate(base), content="SELECT a, b + 1 AS c FROM t")
  inspect(@sqlglot.generate(q1), content="SELECT a, b + 1 AS c FROM t WHERE a > 1")
  inspect(
    @sqlglot.generate(q2),
    content="SELECT a, b + 1 AS c FROM t WHERE a < 0 AND NOT c IS NULL ORDER BY c LIMIT 10",
  )
  let x = @sqlglot.column("x").eq_(1).or_(["y = 2"])
  inspect(
    @sqlglot.generate(
      @sqlglot.select(["x"]).from_("tbl").where_([x]),
      dialect="duckdb",
    ),
    content="SELECT x FROM tbl WHERE x = 1 OR y = 2",
  )
}

#Optimizing

///|
test "optimize" {
  let schema : Map[String, @sqlglot.SchemaNode] = {
    "x": Dict({
      "A": Type("INT"),
      "B": Type("INT"),
      "C": Type("INT"),
      "D": Type("INT"),
      "Z": Type("STRING"),
    }),
  }
  let optimized = @sqlglot.optimize(
    "SELECT A OR (B OR (C AND D)) FROM x WHERE Z = date '2021-01-01' + INTERVAL '1' month OR 1 = 0",
    schema~,
  )
  inspect(
    @sqlglot.generate(optimized, pretty=true),
    content=(
      #|SELECT
      #|  (
      #|    "x"."a" <> 0 OR "x"."b" <> 0 OR "x"."c" <> 0
      #|  )
      #|  AND (
      #|    "x"."a" <> 0 OR "x"."b" <> 0 OR "x"."d" <> 0
      #|  ) AS "_col_0"
      #|FROM "x" AS "x"
      #|WHERE
      #|  CAST("x"."z" AS DATE) = CAST('2021-02-01' AS DATE)
    ),
  )
}

#Executing

///|
test "execute" {
  let tables : Map[String, @sqlglot.TableData] = {
    "sushi": Records([[("id", Int(1)), ("price", Float(1.0))], [("id", Int(2)), ("price", Float(2.0))]]),
    "order_items": Records([
      [("sushi_id", Int(1)), ("order_id", Int(1))],
      [("sushi_id", Int(1)), ("order_id", Int(1))],
      [("sushi_id", Int(2)), ("order_id", Int(1))],
      [("sushi_id", Int(2)), ("order_id", Int(2))],
    ]),
    "orders": Records([
      [("id", Int(1)), ("user_id", Int(1))],
      [("id", Int(2)), ("user_id", Int(2))],
    ]),
  }
  let result : @sqlglot.Table = @sqlglot.execute(
    (
      #|SELECT o.user_id, SUM(s.price) AS price
      #|FROM orders o
      #|JOIN order_items i ON o.id = i.order_id
      #|JOIN sushi s ON i.sushi_id = s.id
      #|GROUP BY o.user_id
      #|ORDER BY o.user_id
    ),
    tables~,
  )
  inspect(result.columns.join(", "), content="user_id, price")
  inspect(
    result,
    content=(
      #|user_id price
      #|      1   4.0
      #|      2   2.0
    ),
  )
}