[ANN] SQLite.jl package update

[Jacob Quinn]

Hey all,

We've been working on a fairly major upgrade of the SQLite.jl package over the last few months and are happy to announce a new release. This is a breaking change with the most recent tagged version in METADATA, so if you wish to stay on the old API version, just run `Pkg.pin("SQLite")`. Otherwise, to see the updates, you can simply run `Pkg.update()` if the `SQLite.jl` package is already installed, or run `Pkg.add("SQLite")` to install the package for the first time.

The newer package boasts some great updates including a more Julian interface (the older package was modeled after the sqldf R package), the removal of DataFrames dependency making the package much more lightweight, but still easy to feed the new `SQLite.ResultSet` type into a DataFrame; there are also some awesome features allowing the use of custom julia scalar and aggregate functions in SQL statements by registering the julia function. Usage of custom Julia types is also supported for storing and loading in SQL tables (using the serialization interface). 

We've tried to put in many more tests and push the docs further along, but are of course always looking to improve.

For those unfamiliar, SQLite is a lightweight, relational database system easy to run on a local machine. It's extremely handy for working with medium to large datasets that still fit on a single machine (MBs to GBs). It supports SQL statements to create, update, and delete relational tables, as well as select statements to perform calculations, or subset specific datasets. 

-Jacob Quinn and Sean Marshallsay

[Valentin Churavy]

Great work! 

Do you think it would make sense to extract some common functionality and function names in a DB package? There is https://github.com/JuliaDB/DBI.jl. 

I like the interface you provided and would like to use parts of it for the Postgres driver I am working on.

- Valentin

[Sean Marshallsay]

Do you think it would make sense to extract some common functionality and function names in a DB package?

There was some discussion about that here but the problem is SQLite.jl is currently slightly too SQLite-specific for this to be an easy task and I don't think any of us really have the impetus to do it. No harm opening an issue though if it's something you're interested in. Heck you could even give it a go yourself if you want.

Great work!

Thanks! 

[Jacob Quinn]

Yeah, I'd say there's definite interest in providing a DBI interface. See the link Sean posted for more of the discussion. It's on my to-do list :)

-Jacob

[Christopher Fisher]

Hi Jacob. I was wondering if you (or someone else) would mind answering a few questions? First, can you elaborate more on the method of converting a ResultSet to a DataFrame? Second, is there a way to list all of the tables in a database, something akin to dblistTables() in R? Thank you. 

[Sean Marshallsay]

Hi Chris, see this comment (https://github.com/quinnj/SQLite.jl/issues/60#issuecomment-69058463) for how to convert to a DataFrame. As for dblistTables(), SQLite.jl exports a tables(db::SQLiteDB) method, does this do what you want?

[Christopher Fisher]

Excellent. This does what I want. Thank you Sean!