old_code/test_DBinR.R

In b-becker/eatGADS: Normalizing, Subsetting and Reshaping Hierarchical Data

library(DBI)
library(RSQLite)

library(dbplyr)
library(sqldf)


# I.) Connecting and Inspecting
###############################################################################
### connect to db
con <- dbConnect(RSQLite::SQLite(), dbname = "C:/sqLite/db/GADStest2.db")

# inspect
dbListTables(con)
dbListFields(con, "sch_unimp")
# get pre Info about data tables
virtDat <- dbSendQuery(con, '
                       SELECT *
                       FROM stud_unimp')
dbColumnInfo(virtDat)
## that way easy accesible information about data tables in data base!
dbListObjects(con)


# II.) Extracting data
###############################################################################
# extract single data table (frame)
singleDF <- dbReadTable(con, "stud_unimp")
str(singleDF)

### extract student data
studDat <- dbGetQuery(con, '
           SELECT *
           FROM stud_unimp
           LEFT JOIN stud_imp ON stud_imp.ID_stud = stud_unimp.ID_stud')

### extract a data frame with all information
allDat <- dbGetQuery(con, '
           SELECT *
           FROM stud_unimp
           LEFT JOIN stud_imp ON stud_imp.ID_stud = stud_unimp.ID_stud
           LEFT JOIN teach_unimp ON teach_unimp.ID_teach = stud_unimp.ID_teach
           LEFT JOIN teach_imp ON teach_imp.ID_teach = teach_unimp.ID_teach
           LEFT JOIN class_unimp ON class_unimp.ID_class = teach_unimp.ID_class
           LEFT JOIN class_imp ON class_imp.ID_class = class_unimp.ID_class
           LEFT JOIN sch_unimp ON sch_unimp.ID_sch = class_unimp.ID_sch
           LEFT JOIN sch_imp ON sch_imp.ID_sch = sch_unimp.ID_sch')
str(allDat)

# iteration etc. via:
?dbBind
?dbSendPreparedQuery


# III.) Writing tables
###############################################################################
?dbWriteTable

# create new table in data base
?dbExecute
dbExecute(con, '
          CREATE TABLE bista_imp (
          ID_stud INTEGER,
          n_imp INTEGER,
          bista_500 REAL,
          bista_lvl TEXT,
          PRIMARY KEY (ID_stud, n_imp),
          FOREIGN KEY (ID_stud) REFERENCES stud_unimp (ID_stud)
          )')
dbListFields(con, "bista_imp")

# simulate data set to append to data base
lvls <- c("I", "II", "III", "IV", "V")
bistaDat <- data.frame(ID_stud = sort(rep(1:500, 5)),
                       n_imp = rep(1:5, 500),
                       bista_500 = rnorm(n = 2500, mean = 500, sd = 50),
                       bista_lvl = sample(lvls, 2500, replace = T))
str(bistaDat)

### append data to data base table
?dbWriteTable
dbWriteTable(con, "bista_imp", value = bistaDat, append = TRUE)

##### test
testDF <- dbReadTable(con, "bista_imp")
str(testDF)
all.equal(bistaDat, testDF)

testQuery <- dbGetQuery(con, '
                        SELECT *
                        FROM bista_imp
                        LEFT JOIN stud_unimp ON stud_unimp.ID_stud = bista_imp.ID_stud')



####### directly with writetable?
dbWriteTable(con, "bista_imp2", value = bistaDat, append = F)
dbListFields(con, "bista_imp2")
testQuery2 <- dbGetQuery(con, '
                        SELECT *
                        FROM bista_imp2
                        LEFT JOIN stud_unimp ON stud_unimp.ID_stud = bista_imp2.ID_stud')
str(testQuery2)

#### Comparison of the two approaches
# does work too, differences probably only in integrity check
all.equal(testQuery, testQuery2)


## remove tables
?dbRemoveTable
dbRemoveTable(con, "bista_imp")
dbRemoveTable(con, "bista_imp2")


############# disconnect when finished
dbDisconnect(con)


####### random thoughts:
# sufficient, if complete data tables can be selected!
# reshapen stays the same

# value and variable labels have to live in a seperate sheet (own excel-file or sth alike?)!!!