Skip to content
An R package that enhances writing and reading data to and from SQLite databases
Branch: master
Clone or download
Latest commit 2bcb876 May 19, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
R
data-raw rws_data to sf object Dec 16, 2018
data always tibble Mar 24, 2019
docs version 0.2.0 Mar 25, 2019
man
tests fix bug with more than 10 factor levels! Apr 24, 2019
vignettes connect and disconnect Mar 24, 2019
.Rbuildignore rws_data Dec 10, 2018
.gitignore documentation Dec 10, 2018
.travis.yml documentation Dec 10, 2018
CODE_OF_CONDUCT.md initial commit Nov 22, 2018
DESCRIPTION tidyverse hms May 19, 2019
LICENSE initial commit Nov 22, 2018
LICENSE.md initial commit Nov 22, 2018
NAMESPACE dttr to dttr2 May 19, 2019
README.Rmd
README.md cran badge May 19, 2019
_pkgdown.yml initial commit Nov 22, 2018
appveyor.yml trying to fix appveyor Mar 7, 2019
build.R start logging Nov 22, 2018
codecov.yml
readwritesqlite.Rproj rejigging but case sensitivity of table and column names an issue - n… Nov 24, 2018

README.md

lifecycle Travis build status AppVeyor build status Coverage status License: MIT CRAN status

readwritesqlite

SQLite databases are a simple, powerful way to validate, query and store related data frames particularly when used with the RSQLite package. However, current solutions do not preserve (or check) meta data, log changes or provide particularly useful error messages.

readwritesqlite is an R package that by default

  • preserves (and subsequently checks) the following metadata
    • the class for logical, Date and hms columns
    • the levels for factors and ordered factors
    • the time zone for POSIXct columns
    • the units for unit columns
    • the projection for sfc columns
    • the sf column for sf objects
  • logs
    • the date time
    • system user
    • table creation and data insertion or deletion
  • provides informative error messages if
    • columns are missing
    • NOT NULL columns contain missing values
    • PRIMARY KEY column values in the input data are not unique

readwritesqlite also allows the user to

  • write environments (or named lists) of data frames (useful for populating databases)
  • delete existing data (and meta data) before writing (useful for converting an existing database)
  • replace existing data which causes unique or primary key conflicts (useful for updating databases)
  • confirm data can be written without commiting any changes (useful for checking data)
  • check all existing tables are written to (useful for data transfers)
  • rearrange and add levels for factors and add levels for ordered factors
  • initialize the meta data for a new table by writing a data frame or sf data frame with no rows but logical, Date, factor, ordered, POSIXct, sfc or unit columns (useful for creating an empty database with additional informative checks)

readwritesqlite provides all these features through its rws_write() and rws_read() functions.

The rws_query() function allows the user to pass a SQL query. By default, the metadata (except the setting of the sf column) is, if unambigously defined, preserved for each column in the final query. To enable this functionality the user should ensure that a) columns in tables which will be referenced in the same query should have different names or identical metadata and b) column names in the final query should match those in the referenced base tables.

The init, meta and log data are stored in separate tables from the main data which means that they do not interfere with other ways of interacting with a SQLite database.

Demonstration

library(tibble)
library(units)
#> udunits system database from /usr/local/share/udunits
library(sf)
#> Linking to GEOS 3.6.1, GDAL 2.1.3, PROJ 4.9.3

library(readwritesqlite)

conn <- rws_connect()

rws_data
#> Simple feature collection with 3 features and 6 fields
#> geometry type:  POINT
#> dimension:      XY
#> bbox:           xmin: 0 ymin: 0 xmax: 1 ymax: 1
#> epsg (SRID):    4326
#> proj4string:    +proj=longlat +datum=WGS84 +no_defs
#> # A tibble: 3 x 7
#>   logical date       factor ordered posixct             units    geometry
#>   <lgl>   <date>     <fct>  <ord>   <dttm>                [m] <POINT [°]>
#> 1 TRUE    2000-01-01 x      x       2001-01-02 03:04:05  10.0       (0 1)
#> 2 FALSE   2001-02-03 y      y       2006-07-08 09:10:11  11.5       (1 0)
#> 3 NA      NA         <NA>   <NA>    NA                     NA       (1 1)

rws_write(rws_data, exists = FALSE, conn = conn)

DBI::dbReadTable(conn, "rws_data")
#>   logical  date factor ordered    posixct units   geometry
#> 1       1 10957      x       x  978433445  10.0 blob[21 B]
#> 2       0 11356      y       y 1152378611  11.5 blob[21 B]
#> 3      NA    NA   <NA>    <NA>         NA    NA blob[21 B]

rws_read_table("rws_data", conn = conn)
#> Simple feature collection with 3 features and 6 fields
#> geometry type:  POINT
#> dimension:      XY
#> bbox:           xmin: 0 ymin: 0 xmax: 1 ymax: 1
#> epsg (SRID):    4326
#> proj4string:    +proj=longlat +datum=WGS84 +no_defs
#> # A tibble: 3 x 7
#>   logical date       factor ordered posixct             units    geometry
#>   <lgl>   <date>     <fct>  <ord>   <dttm>                [m] <POINT [°]>
#> 1 TRUE    2000-01-01 x      x       2001-01-02 03:04:05  10.0       (0 1)
#> 2 FALSE   2001-02-03 y      y       2006-07-08 09:10:11  11.5       (1 0)
#> 3 NA      NA         <NA>   <NA>    NA                     NA       (1 1)

rws_disconnect(conn)

Information

For more information on using readwritesqlite see the vignette using-readwritesqlite.

Installation

To install the latest development version from the Poisson drat repository

install.packages("drat")
drat::addRepo("poissonconsulting")
install.packages("readwritesqlite")

Contribution

Please report any issues.

Pull requests are always welcome.

Please note that this project is released with a Contributor Code of Conduct. By participating in this project you agree to abide by its terms.

You can’t perform that action at this time.