readwritesqlite v0.0.1

0

Monthly downloads

0th

Percentile

Enhanced Reading and Writing for 'SQLite' Databases

Reads and writes data frames to 'SQLite' databases while preserving time zones (for POSIXct columns), projections (for 'sfc' columns), units (for 'units' columns), levels (for factors and ordered factors) and classes for logical, Date and 'hms' columns. It also logs changes to tables and provides more informative error messages.

Readme

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 committing 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 unambiguously 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.

Installation

To install the latest development version from GitHub

remotes::install_github("poissonconsulting/readwritesqlite")

To install the latest development version from the Poisson drat repository

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

Demonstration

Key attribute information is preserved for many classes.

library(readwritesqlite)

# for nicer printing of data frames
library(tibble) 
library(sf)
#> Linking to GEOS 3.6.1, GDAL 2.1.3, PROJ 4.9.3

conn <- rws_connect()

rws_data <- readwritesqlite::rws_data
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)

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)

The attribute information is stored in the metadata table

rws_read_meta(conn = conn)
#> # A tibble: 7 x 4
#>   TableMeta ColumnMeta MetaMeta                             DescriptionMeta
#>   <chr>     <chr>      <chr>                                <chr>          
#> 1 RWS_DATA  DATE       class: Date                          <NA>           
#> 2 RWS_DATA  FACTOR     factor: 'x', 'y'                     <NA>           
#> 3 RWS_DATA  GEOMETRY   proj: +proj=longlat +datum=WGS84 +n… <NA>           
#> 4 RWS_DATA  LOGICAL    class: logical                       <NA>           
#> 5 RWS_DATA  ORDERED    ordered: 'y', 'x'                    <NA>           
#> 6 RWS_DATA  POSIXCT    tz: Etc/GMT+8                        <NA>           
#> 7 RWS_DATA  UNITS      units: m                             <NA>

The user can add descriptions if they wish.

rws_describe_meta("rws_data", "posixct", "The time of a visit", conn = conn)
rws_describe_meta("rws_data", "units", "The site length.", conn = conn)
rws_read_meta(conn = conn)
#> # A tibble: 7 x 4
#>   TableMeta ColumnMeta MetaMeta                          DescriptionMeta   
#>   <chr>     <chr>      <chr>                             <chr>             
#> 1 RWS_DATA  DATE       class: Date                       <NA>              
#> 2 RWS_DATA  FACTOR     factor: 'x', 'y'                  <NA>              
#> 3 RWS_DATA  GEOMETRY   proj: +proj=longlat +datum=WGS84… <NA>              
#> 4 RWS_DATA  LOGICAL    class: logical                    <NA>              
#> 5 RWS_DATA  ORDERED    ordered: 'y', 'x'                 <NA>              
#> 6 RWS_DATA  POSIXCT    tz: Etc/GMT+8                     The time of a vis…
#> 7 RWS_DATA  UNITS      units: m                          The site length.

The log provides a record of data changes that have been made using readwritesqlite.

rws_read_log(conn = conn)
#> # A tibble: 2 x 5
#>   DateTimeUTCLog      UserLog TableLog CommandLog NRowLog
#>   <dttm>              <chr>   <chr>    <chr>        <int>
#> 1 2019-07-07 16:05:10 joe     RWS_DATA CREATE           0
#> 2 2019-07-07 16:05:11 joe     RWS_DATA INSERT           3

Don’t forget to disconnect when done.

rws_disconnect(conn)

Information

For more information on using readwritesqlite see the vignette using-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 contributing to this project, you agree to abide by its terms

Functions in readwritesqlite

Name Description
rws_read_log Read Log Data Table from SQLite Database
rws_write.list Write a Named List of Data Frames to a SQLite Database
rws_write.environment Write the Data Frames in an Environment to a SQLite Database
rws_write.data.frame Write a Data Frame to a SQLite Database
rws_query Query SQLite Database
rws_read_meta Read Meta Data table from SQLite Database
rws_read_table Read A Table from a SQLite Database
rws_write Write to a SQLite Database
rws_read Read from a SQLite Database
rws_read.SQLiteConnection Read All Tables from a SQLite Database
rws_read.character Read Tables from a SQLite Database
rws_read_init Read Initialization Data table from SQLite Database
readwritesqlite-package readwritesqlite: Enhanced Reading and Writing for 'SQLite' Databases
rws_describe_meta.character Add Descriptions to SQL Meta Data Table
rws_disconnect Close SQLite Database Connection
rws_connect Opens SQLite Database Connection
rws_data Example Data
rws_describe_meta.data.frame Add Data Frame of Descriptions to SQL Meta Data Table
check_sqlite_connection Check SQLite Connection
rws_describe_meta Add Descriptions to SQL Meta Data Table
rws_list_tables Table Names
No Results!

Vignettes of readwritesqlite

Name
using-readwritesqlite.Rmd
No Results!

Last month downloads

Details

License MIT + file LICENSE
URL https://github.com/poissonconsulting/readwritesqlite
BugReports https://github.com/poissonconsulting/readwritesqlite/issues
Encoding UTF-8
LazyData true
RoxygenNote 6.1.1
VignetteBuilder knitr
Language en-US
NeedsCompilation no
Packaged 2019-07-07 16:24:01 UTC; joe
Repository CRAN
Date/Publication 2019-07-11 10:47:35 UTC

Include our badge in your README

[![Rdoc](http://www.rdocumentation.org/badges/version/readwritesqlite)](http://www.rdocumentation.org/packages/readwritesqlite)