-
Notifications
You must be signed in to change notification settings - Fork 0
/
README.Rmd
117 lines (85 loc) · 4.31 KB
/
README.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
---
output: github_document
---
<!-- README.md is generated from README.Rmd. Please edit that file -->
```{r setup, include = FALSE}
pkgload::load_all()
```
# RSQLCipher
<!-- badges: start -->
[](https://lifecycle.r-lib.org/articles/stages.html#experimental)
[](https://github.com/bpvgoncalves/RSQLCipher/actions/workflows/R-CMD-check.yaml)
[](https://app.codecov.io/gh/bpvgoncalves/RSQLCipher?branch=develop)
<!-- badges: end -->
RSQLCipher embeds the SQLCipher database engine in R and provides an interface compliant with the
DBI package.
[SQLCipher](https://www.zetetic.net/sqlcipher/open-source/) is an open-source library that provides
transparent and secure 256-bit AES encryption of SQLite database files.
[SQLite](https://www.sqlite.org/index.html) is a public-domain, single-user, very light-weight
database engine that implements a decent subset of the SQL 92 standard, including the core table
creation, updating, insertion, and selection operations, plus transaction management.
This project started as a fork of the amazing [RSQLite](https://rsqlite.r-dbi.org) package, for
which its authors deserve full credit.
It is intended to keep this package updated with new updates to the RSQLite package to keep both as
compatible as possible.
This package allows for the use of regular SQLite database files or encrypted ones.
Currently, only the following differences exist between both drivers:
- `dbConnect()` with `RSQLCipher()` driver accepts the parameter `key` when creating a new
encrypted database or opening a connection to an existing one.
- There are 3 RSQLCipher specific functions:
- `databaseKeyAdd()`, to create an encrypted copy of an existing plain database.
- `databaseKeyChange()`, to modify the key on an encrypted database.
- `databaseKeyRemove()`, to create a decrypted copy of an encrypted database.
<!-- You can install the latest released version from CRAN with: -->
<!-- ```R -->
<!-- install.packages("RSQLCipher") -->
<!-- ``` -->
You can install the latest development version from GitHub with:
```R
# install.packages("devtools")
devtools::install_github("bpvgoncalves/RSQLCipher")
```
Discussions associated with DBI and related database packages take place on [R-SIG-DB](https://stat.ethz.ch/mailman/listinfo/r-sig-db).
The website [Databases using R](https://solutions.posit.co/connections/db/) describes the tools and
best practices in this ecosystem.
## Usage
This package has identical interface to RSQLite.
```{r}
library(DBI)
# Create an two temporary RSQLCipher databases on disk.
# A regular database and an encrypted one.
tmp_plain <- tempfile()
con_plain <- dbConnect(RSQLCipher::SQLCipher(),
tmp_plain)
tmp_enc <- tempfile()
con_enc <- dbConnect(RSQLCipher::SQLCipher(),
tmp_enc,
key = "0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF")
# Both databases behave the same on regular usage...
dbWriteTable(con_plain, "mtcars", mtcars)
dbWriteTable(con_enc, "mtcars", mtcars)
stopifnot(identical(dbListTables(con_plain),
dbListTables(con_enc)))
stopifnot(identical(dbListFields(con_plain, "mtcars"),
dbListFields(con_enc, "mtcars")))
stopifnot(identical(dbReadTable(con_plain, "mtcars"),
dbReadTable(con_enc, "mtcars")))
dbDisconnect(con_plain)
dbDisconnect(con_enc)
# ... but the database files on disk are different
if ("hexView" %in% installed.packages()) {
cat(">> Plain database\n")
print(hexView::readRaw(tmp_plain, nbytes = 128))
cat(">> Encrypted database\n")
print(hexView::readRaw(tmp_enc, nbytes = 128))
} else {
cat(">> Plain database\n")
print(suppressWarnings(readLines(tmp_plain)[1:10]))
cat(">> Encrypted database\n")
print(suppressWarnings(readLines(tmp_enc)[1:10]))
}
file.remove(tmp_plain)
file.remove(tmp_enc)
```