Introduction to spldb

library(data.table)
#> data.table 1.14.8 using 2 threads (see ?getDTthreads).  Latest news: r-datatable.com
library(magrittr)

Concept

dbconnection <- csdb::DBConnection_v9$new(
  driver = Sys.getenv("SC9_DBCONFIG_DRIVER"),
  server = Sys.getenv("SC9_DBCONFIG_SERVER"),
  port = as.integer(Sys.getenv("SC9_DBCONFIG_PORT")),
  db = Sys.getenv("SC9_DBCONFIG_DB_ANON"),
  user = Sys.getenv("SC9_DBCONFIG_USER"),
  password = Sys.getenv("SC9_DBCONFIG_PASSWORD"),
  trusted_connection = Sys.getenv("SC9_DBCONFIG_TRUSTED_CONNETION")
)
dbconnection
#> (disconnected)
#> 
#> Driver:              ODBC Driver 17 for SQL Server 
#> Server:              dm-prod 
#> Port:                1433 
#> DB:                  sc_interactive_anon 
#> User:                SA 
#> Password:            ********************* 
#> Trusted connection:
dbconnection$connect()

dbconnection$connection
#> <OdbcConnection> dbo@8f6800aff6b4
#>   Database: sc_interactive_anon
#>   Microsoft SQL Server Version: 15.00.4236
dbconnection$autoconnection
#> <OdbcConnection> dbo@8f6800aff6b4
#>   Database: sc_interactive_anon
#>   Microsoft SQL Server Version: 15.00.4236
dbconnection
#> (connected)
#> 
#> Driver:              ODBC Driver 17 for SQL Server 
#> Server:              dm-prod 
#> Port:                1433 
#> DB:                  sc_interactive_anon 
#> User:                SA 
#> Password:            ********************* 
#> Trusted connection:

dbconnection$disconnect()
dbconnection$connection
#> Error: external pointer is not valid
class(dbconnection$connection)
#> [1] "Microsoft SQL Server"
#> attr(,"package")
#> [1] ".GlobalEnv"
class(dbconnection$autoconnection)
#> [1] "Microsoft SQL Server"
#> attr(,"package")
#> [1] ".GlobalEnv"

dbtable <- csdb::DBTable_v9$new(
  dbconfig = list(
    driver = Sys.getenv("SC9_DBCONFIG_DRIVER"),
    server = Sys.getenv("SC9_DBCONFIG_SERVER"),
    port = as.integer(Sys.getenv("SC9_DBCONFIG_PORT")),
    db = Sys.getenv("SC9_DBCONFIG_DB_ANON"),
    schema = Sys.getenv("SC9_DBCONFIG_SCHEMA_ANON"),
    user = Sys.getenv("SC9_DBCONFIG_USER"),
    password = Sys.getenv("SC9_DBCONFIG_PASSWORD"),
    trusted_connection = Sys.getenv("SC9_DBCONFIG_TRUSTED_CONNETION")
  ),
  table_name = "anon_test",
  field_types = c(
      "granularity_time" = "TEXT",
      "granularity_geo" = "TEXT",
      "country_iso3" = "TEXT",
      "location_code" = "TEXT",
      "border" = "INTEGER",
      "age" = "TEXT",
      "sex" = "TEXT",
      "isoyear" = "INTEGER",
      "isoweek" = "INTEGER",
      "isoyearweek" = "TEXT",
      "season" = "TEXT",
      "seasonweek" = "DOUBLE",
      "calyear" = "INTEGER",
      "calmonth" = "INTEGER",
      "calyearmonth" = "TEXT",
      "date" = "DATE",
      "covid19_cases_testdate_n" = "INTEGER",
      "covid19_cases_testdate_pr100000" = "DOUBLE"
    ),
    keys = c(
      "granularity_time",
      "location_code",
      "date",
      "age",
      "sex"
    ),
    indexes = list(
      "ind1" = c("granularity_time", "granularity_geo", "country_iso3", "location_code", "border", "age", "sex", "date", "isoyear", "isoweek", "isoyearweek")
    ),
    validator_field_types = csdb::validator_field_types_blank,
    validator_field_contents = csdb::validator_field_contents_blank
)
dbtable$insert_data(csdb::nor_covid19_cases_by_time_location)
dbtable$connect()
dbtable$dbconnection$is_connected()
#> [1] TRUE
dbtable$tbl()
#> # Source:   table<anon_test> [?? x 18]
#> # Database: Microsoft SQL Server 15.00.4236[dbo@8f6800aff6b4/sc_interactive_anon]
#>    granula…¹ granu…² count…³ locat…⁴ border age   sex   isoyear isoweek isoye…⁵ season seaso…⁶ calyear
#>    <chr>     <chr>   <chr>   <chr>    <int> <chr> <chr>   <int>   <int> <chr>   <chr>    <dbl>   <int>
#>  1 day       county  nor     county…   2020 total total    2020       8 2020-08 2019/…      31    2020
#>  2 day       county  nor     county…   2020 total total    2020       8 2020-08 2019/…      31    2020
#>  3 day       county  nor     county…   2020 total total    2020       8 2020-08 2019/…      31    2020
#>  4 day       county  nor     county…   2020 total total    2020       9 2020-09 2019/…      32    2020
#>  5 day       county  nor     county…   2020 total total    2020       9 2020-09 2019/…      32    2020
#>  6 day       county  nor     county…   2020 total total    2020       9 2020-09 2019/…      32    2020
#>  7 day       county  nor     county…   2020 total total    2020       9 2020-09 2019/…      32    2020
#>  8 day       county  nor     county…   2020 total total    2020       9 2020-09 2019/…      32    2020
#>  9 day       county  nor     county…   2020 total total    2020       9 2020-09 2019/…      32    2020
#> 10 day       county  nor     county…   2020 total total    2020       9 2020-09 2019/…      32    2020
#> # … with more rows, 5 more variables: calmonth <int>, calyearmonth <chr>, date <date>,
#> #   covid19_cases_testdate_n <int>, covid19_cases_testdate_pr100000 <dbl>, and abbreviated variable
#> #   names ¹granularity_time, ²granularity_geo, ³country_iso3, ⁴location_code, ⁵isoyearweek,
#> #   ⁶seasonweek

Richard White

2020-02-17

Concept