---
title: "Getting started with polyglotSQL"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Getting started with polyglotSQL}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(collapse = TRUE, comment = "#>")
```

polyglotSQL gives R a native SQL compiler front-end: parse, tokenize,
validate, format, analyze and translate SQL between more than 30 dialects.
All the heavy lifting happens in the embedded
[polyglot-sql](https://github.com/tobilg/polyglot) Rust crate — in-process,
with no external services.

```{r setup}
library(polyglotSQL)
polyglot_version()
```

## Your first translation

The flagship feature is dialect translation. SQL is parsed with the source
dialect into an abstract syntax tree (AST) and regenerated with the target
dialect's rules:

```{r}
sql_transpile(
  "SELECT IFNULL(a, b) FROM t",
  from = "mysql",
  to = "postgres"
)
```

Multiple statements are supported; the result has one element per statement:

```{r}
sql_transpile(
  "SELECT 1; SELECT IFNULL(a, b) FROM t;",
  from = "mysql",
  to = "postgres"
)
```

Set `pretty = TRUE` for indented output, and control what happens when a
construct has no equivalent in the target dialect with `unsupported`
(`"raise"` — the default — errors; `"warn"`/`"ignore"` return best-effort
SQL):

```{r}
cat(sql_transpile(
  "SELECT id, COUNT(*) AS n FROM logs GROUP BY id HAVING COUNT(*) > 10",
  from = "generic", to = "snowflake", pretty = TRUE
))
```

## Which dialects?

```{r}
head(sql_dialects(full = TRUE), 10)
```

Any function accepting a dialect also accepts the listed aliases — `"mssql"`
and `"sqlserver"` both mean `"tsql"`, `"postgresql"` means `"postgres"`.

## Formatting

```{r}
cat(sql_format("select id,sum(x) total from t where y=1 group by id"))
```

## Validating

`sql_validate()` returns a structured result instead of throwing:

```{r}
sql_validate("SELECT FROM WHERE")
```

## Parsing and round-tripping

```{r}
ast <- sql_parse("SELECT a, b FROM t WHERE x = 1")
ast
```

The AST is a plain nested list following the upstream JSON format, and can be
rendered back to SQL with any dialect's syntax rules (for full translation
with function rewrites, use `sql_transpile()`):

```{r}
sql_generate(sql_parse("SELECT `col name` FROM t", dialect = "mysql"),
             dialect = "postgres")
```

## Errors are classed conditions

All failures raise ordinary R conditions with useful classes
(`polyglot_parse_error`, `polyglot_transpile_error`,
`polyglot_validation_error`, `polyglot_guard_error`, all inheriting from
`polyglot_error`), so you can handle them precisely:

```{r}
tryCatch(
  sql_parse("SELECT ((( FROM"),
  polyglot_parse_error = function(e) conditionMessage(e)
)
```

A parse failure — even a bug-triggered panic inside Rust — never terminates
your R session.

## Where to next?

* `vignette("dialect-migration")` — migrating a query base between engines.
* `vignette("parsing-validation-lineage")` — ASTs, schemas, lineage and
  analysis.
* `vignette("installation-and-troubleshooting")` — Rust toolchain, offline
  builds, common problems.