[R] Data Carpentry - Creating a New SQLite Database

[Ivan Krylov]

On Fri, 10 Jan 2020 11:31:58 -0700
"Phillip Heinrich" <herd_dog using cox.net> wrote:

> below is the text from the tutorial.  The black type is from the
> tutorial.  The green and blue is the suggested R code.  My comments
> are in red

R-help is a plain text mailing list, so the markup has been stripped
off (and since HTML-enabled mail clients don't quite care how the plain
text version of the e-mail looks, some paragraph breaks had to go, too).

> etc.surveys <- read_csv("data_raw/surveys.csv")
> plots <- read_csv("data_raw/plots.csv")

> Again no problem.  I’m just creating an R data files.

Note that it is not files that you are creating by running read_csv(),
but variables (of type "tibble", which is like "data.frame", either of
which should have been covered in earlier chapters in a good tutorial)
in the R environment. The files you downloaded previously are opened
in read only mode and are never changed.

> my_db_file <- "data/portal-database-output.sqlite"

> I’m creating something named my_db_file from another file named
> portal-database-output with an sqlite extension and then creating
> my_db from the My_db_file.

This something is just a text string that happens to contain a *path*
to a file. Just like the variable `greeting` in the following snippet:

greeting <- "Hello world"
print(greeting)

See [1] for more info on character vectors in R.

> Not sure where the sqlite extension file came from.

The authors of the tutorial decided that the file to be created should
be named like this. Feel free to change the extension (or the path) to
anything else: neither R, nor SQLite cares about it much (but the file
manager you use may display a different icon for it or become confused
if you name it .txt or .pdf).

> I can follow the directions to fill in my_db but I have no idea
> how to access the tables.

What exactly do you mean by "access"? At this point my_db should be a
dplyr "src" object, so the tools described in dplyr vignettes [2] should
be applicable. Try calling tbl() on it and passing the name of one of
the tables you have just created. Also try running:

example("src_sqlite")

> The text from the tutorial below says to check the location of our
> database.  Huh!  Can someone give me some direction.

The variable my_db_file contains the location of the file where the
database is stored. This is the same variable that you passed to the
src_sqlite() function.

-- 
Best regards,
Ivan

[1]
https://cran.r-project.org/doc/manuals/r-release/R-intro.html#Character-vectors

[2]
https://cran.r-project.org/web/packages/dplyr/vignettes/dplyr.html
https://cran.r-project.org/web/packages/dplyr/vignettes/programming.html
https://cran.r-project.org/web/packages/dplyr/vignettes/two-table.html
https://cran.r-project.org/web/packages/dplyr/vignettes/window-functions.html