#install.packages('RSQLite')


library(DBI)
# Create an ephemeral in-memory RSQLite database
con <- dbConnect(RSQLite::SQLite(), ":memory:")
" Path: :memory:
  Extensions: TRUE"
dbListTables(con)
"mtcars
mpg cyl  disp  hp drat    wt  qsec vs am gear carb
Mazda RX4           21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4  ..
" 


rownames(mtcars)  # giver indexværdier, car-mark

dbWriteTable(con, "mtcars", mtcars)  #skriver mtcars dataframe til mtcars tabel
dbListTables(con)  
#her kommer indexværdier, car-mark, ikke med
## [1] "mtcars"
dbListFields(con, "mtcars")

#tilfører car-mark eksplicit til første kolonne
mtcars$mark<-rownames(mtcars)  #index-kolonnen
df2<-mtcars %>% relocate(mark)   # rykker mark column til første kolonne
df2


dbRemoveTable(con, "mtcars") #sletter tabel for at genindlæse med mark-kolonnen medtaget

dbWriteTable(con, "mtcars", df2)  
dbListTables(con)  
#nu er indexværdier med!  men ikke som index; for stor tabel bør index defineres
dbListFields(con, "mtcars")

# SaveData2SQLiteFile----
library(RSQLite)
con2 <- dbConnect(SQLite(), "mydb.sqlite") #filen oprettes, hvis den ikke findes
con2
" Path: C:\Users\bh\Documents\programmering\R\mydb
  Extensions: TRUE"
dbListTables(con2)
dbWriteTable(con2, "mytable", df2)  #umiddelbart vises størrelse=0, 
dbGetQuery(con2, "select count(*) from mytable")  # ensure it is there, refresh ses størrele>0

db <- dbConnect(SQLite(), dbname="mydb")
dbWriteTable(con2 = db, name = "mtcars", mtcars, overwrite=TRUE,
             row.names=TRUE)

# Delete the column belonging to the Mazda RX4. You will see a 1 as the output.
dbExecute(con, "DELETE FROM mtcars WHERE mark = 'Mazda RX4'")
dbGetQuery(con, "select count(*) from mtcars") # ok, slettet

# Insert the data for the Mazda RX4. This will also ouput a 1
dbExecute(con, "INSERT INTO mtcars VALUES ('Mazda RX4',21.0,6,160.0,110,3.90,2.620,16.46,0,1,4,4)")
# See that we re-introduced the Mazda RX4 succesfully at the end:
dbGetQuery(con, "SELECT * FROM mtcars") 

# Close the database connection to CarsDB
dbDisconnect(con)

df<-dbReadTable(con, "mtcars") #fejl, for con netop closed
df

# ParametriseretSQL----
# Lets assume that there is some user input that asks us to look only into cars that have over 18 miles per gallon (mpg)
# and more than 6 cylinders
mpg <-  18
cyl <- 6
Result <- dbGetQuery(con2, 'SELECT * FROM mtcars WHERE mpg >= ? AND cyl >= ?', params = c(mpg,cyl))
Result

# You can fetch all results:
res <- dbSendQuery(con2, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)

# dbClearResult(res)  #nulstiller res
# Or a chunk at a time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while (!dbHasCompleted(res)) {
  chunk <- dbFetch(res, n = 5) #read 5 rows 
  print(chunk)
  print(nrow(chunk))
}