## Creating a database, adding, appending, and removing tables

For this first part of the class, we will be working with a SQLite database, which is self-contained in a file within our hard drive, without any need to set up a server. The dataset we will work with is all Facebook posts by members of the U.S. Congress in 2017, as collected from the public Pages API while it was available. You can download the data from Moodle.

We will start by connecting to the database, creating an .sqlite file, and adding the first table, with information at the level of member of congress. 

```{r}
#install.packages(c("DBI", "RSQLite"))
library(DBI)
```

```{r, eval=FALSE}
# Create database: This will create a file in our hard drive if it does not exist already. Note, you could equally use "facebook-db.db" here.
db <- dbConnect(RSQLite::SQLite(), "data/facebook-db.sqlite")

# Reading the first file into R
congress <- read.csv("data/congress-facebook-2017.csv",
	stringsAsFactors = FALSE)

# Adding a first table: User-level data
dbWriteTable(db, "congress", congress)

# Testing that it works with a simple query
dbListFields(db, "congress")
dbGetQuery(db, "SELECT * FROM congress LIMIT 5")
```

Note that we have used `dbWriteTable` to add the data frame to our database, and then check that it all worked with `dbListFields` and `dbGetQuery`. As we will see in a second, this second function is what we will use to query the database, and it will always return a table.

We now switch to the second table. However, the files are too big to open them all in memory. Instead, we will open them one by one, and then __append__ them to the table. Let us see how that works...

```{r, eval=FALSE}
fls <- list.files("data/posts", full.names = TRUE)

for (f in fls){
  
  message(f)

  # Read file into memory
  fb <- read.csv(f, stringsAsFactors = FALSE)
  
  # Adding to table in SQL database
  dbWriteTable(db, "posts", fb, append = TRUE)
  
}
```

Testing that it worked:

```{r}
# All columns in the posts table
dbListFields(db, "posts")

# First five rows from all columns
dbGetQuery(db, 'SELECT * FROM posts LIMIT 5')

# What if we make a mistake and want to remove the table?
# dbRemoveTable(db, "posts")

# And we close the connection for now
dbDisconnect(db)
```


## Querying the database

Now that we have our tables in the database, let us see how we can query them. First we connect using `dbConnect` and then we type our query with `dbGetQuery`. Generally, we will write the query __clauses__ in capital letters and the column names in lowercase.

```{r}
db <- dbConnect(RSQLite::SQLite(), "data/facebook-db.sqlite")
test <- dbGetQuery(db, 'SELECT * FROM congress LIMIT 5')
str(test)
```

As a side note: RMarkdown actually allows us to write the SQL query directly as a chunk of code:

```{sql, connection=db}
SELECT * FROM congress LIMIT 5
```

```{sql, connection=db}
SELECT * FROM posts LIMIT 5
```


Continuing with our previous example, this is the list of query clauses that we will use to work with the database table in this script:

- __SELECT__: determines which columns to include in the query's result set

- __FROM__: identifies the tables from which to draw data and how tables should be joined

- __LIMIT__: in combination with SELECT, limits the number of records returned

- __WHERE__: filter out unwanted data

And then three important query clauses that we will introduce in the next coding exercise:  

- __GROUP BY__: groups rows together by common column values

- __ORDER BY__: sorts the rows in the final result set by one or more columns

- __JOIN__ (and friends): join two tables together based on columns


Recall the column names both in the congress and in the posts table:

```{r}
dbListFields(db, "congress")
dbListFields(db, "posts")
```

Let us start with some examples of __SELECT__:

```{r}
# Querying just one column
dbGetQuery(db, "SELECT name FROM congress LIMIT 10")

# Multiple columns
dbGetQuery(db, "SELECT name, party FROM congress LIMIT 10")

# Adding expressions (division returns integer) -- remember to use NULLIF(var,0) in the demoninator, which will return a null if dividing by zero.
dbGetQuery(db, "SELECT from_name, likes_count/ NULLIF(comments_count, 0), UPPER(type) FROM posts LIMIT 10")

# Adding expressions (division returns float)
dbGetQuery(db, "SELECT from_name, CAST(likes_count AS FLOAT)/ NULLIF(comments_count, 0), UPPER(type) FROM posts LIMIT 10")

# Adding aliases to the new columns
dbGetQuery(db, "SELECT from_name, CAST(likes_count AS FLOAT) / NULLIF(comments_count, 0) AS like_ratio FROM posts LIMIT 10")

# It is often a good idea to write a query in multiple lines so that it is easier to read:
dbGetQuery(db, "SELECT from_name, CAST(likes_count AS FLOAT) / NULLIF(comments_count, 0) AS like_ratio 
           FROM posts 
           LIMIT 10")

# Selecting unique values from a column
dbGetQuery(db, "SELECT DISTINCT from_name 
           FROM posts 
           LIMIT 10")

# Not adding distinct
dbGetQuery(db, "SELECT from_name 
           FROM posts 
           LIMIT 10")

```

Now some examples of how we can use __WHERE__ to subset the database based on values of specific columns (note, the date column is in "YYYY-MM-DD" format):

```{r}
# Selecting based on values of a column
dbGetQuery(db, "SELECT name, party 
           FROM congress
           WHERE party = 'Republican'
           LIMIT 10")

# Greater than, working with dates
dbGetQuery(db, "SELECT from_name, type, date 
           FROM posts
           WHERE date > '2017-01-01'
           LIMIT 10")

# AND operator
dbGetQuery(db, "SELECT from_name, type, date, likes_count 
           FROM posts
           WHERE date > '2017-06-01' AND type != 'photo' 
              AND likes_count > 500
           LIMIT 10")

# OR operator
dbGetQuery(db, "SELECT from_name, type, date, comments_count 
           FROM posts
           WHERE comments_count > 100 AND (type = 'photo' OR type = 'video')
           LIMIT 10")

# OR operator again -- note the importance of the parentheses...
dbGetQuery(db, "SELECT from_name, type, date, comments_count 
           FROM posts
           WHERE comments_count > 100 AND type = 'photo' OR type = 'video'
           LIMIT 10")

# Membership of a set: IN ('')
dbGetQuery(db, "SELECT from_name, type, date, comments_count 
           FROM posts
           WHERE type IN ('video', 'event')
           LIMIT 10")

# Matching conditions:
# _ matches exactly one character
# % matches any number of characters
dbGetQuery(db, "SELECT from_name, type, date, comments_count 
           FROM posts
           WHERE date LIKE '2017-01-__'
           LIMIT 10")

dbGetQuery(db, "SELECT from_name, type, date, comments_count 
           FROM posts
           WHERE date LIKE '2017-03%'
           LIMIT 10")

# SQLite does not have regular expressions, but we can get creative...
dbGetQuery(db, "SELECT from_name, message, date
           FROM posts
           WHERE message LIKE '%london%'
           LIMIT 1")

```



