Working proposal for adding RSQLite (SQLite/json1)

[rfhb]

This is a PR to supersede #24, addressing review comments and extending functionality.

Included is a working implementation of nodbi for use with RSQLite, in analogy to existing methods (in particular MongoDB). Also, a nested ragged json dataset (contacts) is included for testing.

RSQLite ships since 2016 with an sqlite version that includes the json1 extension, thus enabling a JSON store for small projects or for exploring.

The query method has many lines, for translating json strings for query and fields into SQL.

Thanks

[sckott]

thanks @rfhb sorry for the delay, vacation and other work stuff.

Will have a look at this hopefully today

[rfhb]

Thanks. Am still working on the query method to make this work exactly like the mongo method. Perhaps I can ping you here once the additional works are ready.

[sckott]

Create takes quite a long time for sqlite compared to all backends except etcd. Wondering if create can be sped up at all?

library(nodbi)
library(ggplot2)

src <- src_sqlite("test.sqlite")
system.time(docdb_create(src, "diamonds", diamonds))
#> 139 sec

src_mongo <- src_mongo()
system.time(docdb_create(src_mongo, "diamonds", diamonds))
#> 0.8 sec 

src_redis <- src_redis()
system.time(docdb_create(src_redis, "diamonds", diamonds))
#> 0.1 sec

src_elastic <- src_elastic()
system.time(docdb_create(src_elastic, "diamonds", diamonds))
#> 16 sec

src_couch <- src_couchdb()
system.time(docdb_create(src_couch, "diamonds", diamonds))
#> 80 sec

src_etcd <- src_etcd()
system.time(docdb_create(src_etcd, "/diamonds", diamonds))
#> really long time

[sckott]

any thoughts @rfhb ?

[rfhb]

Many thanks @sckott for getting back - I had been working on this over the last weeks. Happy to hear back from your thoughts on the latest commits!

[sckott]

thanks @rfhb - can you address my question about performance?

[rfhb]

thanks @sckott - performance is well improved, mentioned this in my comment #25 (comment) and here are figures on a laptop:

library(nodbi)

## database in file system
unlink("test.sqlite")
src <- src_sqlite("test.sqlite")
system.time(docdb_create(src, "diamonds", diamonds))
#> 20 sec elapsed time

## memory-based database
src <- src_sqlite()
system.time(docdb_create(src, "diamonds", diamonds))
#> 0.8 sec elapsed time

The difference in execution time is solely due to RSQLite::dbWriteTable() or its generic function DBI::dbAppendTable(), which takes longer for writing to disk compared to memory.

[sckott]

thanks @rfhb - leaving on vacation for a week - will take another look then - sorry about the delay

[rfhb]

Hi, would you want to have another look at the PR, please? I am now starting to use it for another project and believe to have addressed comments so far. Many thanks.

[sckott]

having a look now

[sckott]

You're right, the performance is very good in memory, and much improved when writing to disk.

[sckott]

looks good, thanks for your contribution, assigned on next milestone https://github.com/ropensci/nodbi/milestone/3