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
readwritesqlite also allows the user to
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.
To install the latest release from CRAN
install.packages("readwritesqlite")
To install the developmental version from GitHub
# install.packages("remotes")
remotes::install_github("poissonconsulting/readwritesqlite")
Key attribute information is preserved for many classes.
library(readwritesqlite)
# for nicer printing of data frames
library(tibble)
library(sf)
#> Linking to GEOS 3.12.2, GDAL 3.9.1, PROJ 9.4.1; sf_use_s2() is TRUE
conn <- rws_connect()
rws_data <- readwritesqlite::rws_data
rws_data
#> # A tibble: 3 × 6
#> logical date factor ordered posixct units
#> <lgl> <date> <fct> <ord> <dttm> [m]
#> 1 TRUE 2000-01-01 x x 2001-01-02 03:04:05 10
#> 2 FALSE 2001-02-03 y y 2006-07-08 09:10:11 11.5
#> 3 NA NA <NA> <NA> NA NA
rws_write(rws_data, exists = FALSE, conn = conn)
rws_read_table("rws_data", conn = conn)
#> # A tibble: 3 × 6
#> logical date factor ordered posixct units
#> <lgl> <date> <fct> <ord> <dttm> [m]
#> 1 TRUE 2000-01-01 x x 2001-01-02 03:04:05 10
#> 2 FALSE 2001-02-03 y y 2006-07-08 09:10:11 11.5
#> 3 NA NA <NA> <NA> NA NA
The attribute information is stored in the metadata table
rws_read_meta(conn = conn) #> # A tibble: 6 × 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 LOGICAL class: logical <NA> #> 4 RWS_DATA ORDERED ordered: 'y', 'x' <NA> #> 5 RWS_DATA POSIXCT tz: Etc/GMT+8 <NA> #> 6 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: 6 × 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 LOGICAL class: logical <NA>
#> 4 RWS_DATA ORDERED ordered: 'y', 'x' <NA>
#> 5 RWS_DATA POSIXCT tz: Etc/GMT+8 The time of a visit
#> 6 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.
For more information on using readwritesqlite see the vignette using-readwritesqlite.
Please report any issues.
Pull requests are always welcome.
Please note that the readwritesqlite project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4