---
title: "Getting started"
knitr:
  opts_chunk:
    collapse: true
    comment: "#>"
description: |
  A short introduction to interacting with ggsql from R
vignette: >
  %\VignetteIndexEntry{Getting started}
  %\VignetteEngine{quarto::html}
  %\VignetteEncoding{UTF-8}
---

```{r setup}
library(ggsql)
```

The ggsql package is an R binding to the [ggsql visualization library](https://ggsql.org). It allows you to set up readers to interact with different data backends, execute queries, and define writers to handle rendering.

In addition to this it also provide a knitr engine so that you can write ggsql blocks in your Rmarkdown and Quarto documents. This part of the package has it's own vignette so head there if that is your main interest.

## Package anatomy
The ggsql R package follows the same division as the main library which is split into a reader, plot, and writer module. You start by creating a reader which interacts with your data backend. You then execute queries against this reader (effectively using the plot module). Lastly, you can render the result using a writer you define.

### Creating a reader
ggsql understands multiple backends, but in this vignette we focus on the DuckDB reader since it is both powerful and doesn't require any additional setup. You create a DuckDB reader using the aptly named `duckdb_reader()` function:

```{r}
ddb <- duckdb_reader()
```

By default it creates a connection to an in-memory database, but you can point it at a database file to connect to that instead:

```{r}
#| eval: false
ddb2 <- duckdb_reader("my/amazing/data.db")
```

While a reader is mainly in service of executing queries against, you can do something else with it as well, e.g. register data to it that will be available to queries on equal footing with any "real" tables in the database:

```{r}
ggsql_register(ddb, gapminder::gapminder, "gapminder")
```

We can see all the tables currently available with: 

```{r}
ggsql_table_names(ddb)
```

And if we wanted to we could fetch the data back using `ggsql_table()` (works for both registered and native tables and views), or unregister it using `ggsql_unregister()` (only for registered tables)

### Executing a query
As mentioned, the main purpose of a reader is to be the backend on which a ggsql query are executed. While it is possible to execute pure SQL queries and get back a data.frame:

```{r}
ggsql_execute_sql(ddb, "
SELECT MAX(pop) AS population, country FROM gapminder
GROUP BY country
ORDER BY population DESC
LIMIT 6
")
```

The main motivation is of course to create visualizations. This vignette will not go into detail with the ggsql syntax. For that, head to the [documentation on the website](https://ggsql.org/syntax/). If you are familiar with ggplot2 you should be able to understand the gist of it.

```{r}
plot <- ggsql_execute(ddb, "
SELECT * FROM gapminder
WHERE year = 2007

VISUALIZE gdpPercap AS x, lifeExp AS y, continent AS stroke, pop AS size
DRAW point
  SETTING fill => null
SCALE size TO (2, 15)
")

str(plot)
```

As you can see, executing a visual query does not in itself produce a plot, but
creates a data structure that contains the information necessary to render it. The analogue is the ggplot object you create when using ggplot2.

You can use this object for introspection, e.g. get the visual part of the query:

```{r}
ggsql_visual(plot)
```

### Rendering a plot
Finally we arrive at what we was here for all along: How to get a plot out of it. ggsql works with the concept of writers which are somewhat analogous to graphics devices in R. They are responsible for figuring out a visual representation of the abstract encoding in the plot specification. For the moment ggsql only comes with a vegalite writer so this is also the only one available in the R package:

```{r}
vlr <- vegalite_writer()
```

Often you don't have to think about this at all since printing the result of a query automatically creates a writer and renders it so that it behaves in the same way as ggplot object (this is also true in Quarto/Rmarkdown documents):

```{r}
plot
```

However, if you are interested in capturing what the writer produces you can call `ggsql_render()` explicitly:

```{r}
ggsql_render(vlr, plot)
```

We might also be interested in capturing the plot as a static image. We can use the `ggsql_save()` function for that which can save the output to either a vegalite json object (like the one above), an SVG or a PNG. For the latter two the plot is rendered using the V8 package and the final image captured.

```{r}
tmp <- tempfile(fileext = ".svg")
ggsql_save(plot, tmp)
readLines(tmp)
```