@krlmlr krlmlr released this Nov 30, 2016 · 558 commits to master since this release

Assets 2

RSQLite 1.1 (2016-11-25)

  • New maintainer: Kirill Müller.

Bundled SQLite

  • RSQLite always builds with the included source, which is located in src/sqlite3. This prevents bugs due to API mismatches and considerably simplifies the build process.
  • Current version: 3.11.1.
  • Enable JSON1 extension (#152, @tigertoes).
  • Include support for FTS5 (@mkuhn).
  • Compilation limits SQLITE_MAX_VARIABLE_NUMBER and SQLITE_MAX_COLUMN have been reset to the defaults. The documentation suggests setting to such high values is a bad idea.
  • Header files for sqlite3 are no longer installed, linking to the package is not possible anymore. Packages that require access to the low-level sqlite3 API should bundle their own copy.

Breaking changes

  • RSQLite() no longer automatically attaches DBI when loaded. This is to
    encourage you to use library(DBI); dbConnect(RSQLite::SQLite()).
  • Functions that take a table name, such as dbWriteTable() and dbReadTable(),
    now quote the table name via dbQuoteIdentifier().
    This means that caller-quoted names should be marked as such with DBI::SQL().

New features

  • RSQLite has been rewritten (essentially from scratch) in C++ with
    Rcpp. This has considerably reduced the amount of code, and allows us to
    take advantage of the more sophisticated memory management tools available in
    Rcpp. This rewrite should yield some minor performance improvements, but
    most importantly protect against memory leaks and crashes. It also provides
    a better base for future development. In particular, it is now technically
    possible to have multiple result sets per connection, although this feature
    is currently disabled (#150).
  • You can now use SQLite's URL specification for databases. This allows you to
    create shared in-memory databases
  • Queries (#69), query parameters and table data are always converted to UTF-8 before being sent to the database.
  • Adapted to DBI 0.5, new code should use dbExecute() instead of dbGetQuery(), and dbSendStatement() instead of dbSendQuery() where appropriate.
  • New strategy for prepared queries. Create a prepared query with dbSendQuery() or dbSendStatement() and bind values with dbBind(). The same query/statement can be executed efficiently multiple times by passing a data-frame-like object (#168, #178, #181).
  • dbSendQuery(), dbGetQuery(), dbSendStatement() and dbExecute()
    also support inline parameterised queries,
    like dbGetQuery(datasetsDb(), "SELECT * FROM mtcars WHERE cyl = :cyl", params = list(cyl = 4)). This has no performance benefits but protects you
    from SQL injection attacks.
  • Improve column type inference: the first non-NULL value decides the type of a column (#111). If there are no non-NULL values, the column affinity is used, determined according to sqlite3 rules (#160).
  • dbFetch() uses the same row name strategy as dbReadTable() (#53).
  • dbColumnInfo() will now return information even before you've retrieved any data.
  • New sqliteVersion() prints the header and library versions of RSQLite.
  • Deprecation warnings are given only once, with a clear reference to the source.
  • datasetsDb() now returns a read-only database, to avoid modifications to the installed file.

Deprecated functions

  • make.db.names() has been formally deprecated. Please use dbQuoteIdentifier() instead. This function is also used in dbReadTable(), dbRemoveTable(), and dbListFields() (#106, #132).
  • sqliteBuildTableDefinition() has been deprecated. Use DBI::sqlCreateTable() instead.
  • dbGetException() now raises a deprecation warning and always returns list(errorNum = 0L, errorMsg = "OK"), because querying the last SQLite error only works if an error actually occurred (#129).
  • dbSendPreparedQuery() and dbGetPreparedQuery() have been reimplemented (with deprecation warning) using dbSendQuery(), dbBind() and dbFetch() for compatibility with existing packages (#100, #153, #168, #181). Please convert to the new API, because the old function may be removed completely very soon: They were never part of the official API, and do less argument checking than the new APIs. Both dbSendPreparedQuery() and dbGetPreparedQuery() ignore parameters not found in the query, with a warning (#174).
  • Reimplemented dbListResults() (with deprecation warning) for compatibility with existing packages (#154).
  • Soft-deprecated dbGetInfo(): The "Result" method is implemented by DBI, the methods for the other classes raise a warning (#137). It's now better to access the metadata with individual functions dbHasCompleted(), dbGetRowCount() and dbGetRowsAffected().
  • All summary() methods have been removed: the same information is now displayed in the show() methods, which were previously pretty useless.

Compatibility fixes

  • The raw data type is supported in dbWriteTable(), creates a TEXT column with a warning (#173).
  • Numeric values for the row.names argument are converted to logical, with a warning (#170).
  • If the number of data frame columns matches the number of existing columns for dbWriteTable(append = TRUE), columns will be matched by position for compatibility, with a warning in case of a name mismatch (#164).
  • dbWriteTable() supports the field.types argument when creating a new table (#171), and the temporary argument, default FALSE (#113).
  • Reexporting dbGetQuery() and dbDriver() (#147, #148, #183).
  • sqliteCopyDatabase() accepts character as to argument again, in this case a temporary connection is opened.
  • Reimplemented dbWriteTable("SQLiteConnection", "character", "character") for import of CSV files, using a function from the old codebase (#151).
  • dbWriteTable("SQLiteConnection", "character", "data.frame") looks
    for table names already enclosed in backticks and uses these,
    (with a warning), for compatibility with the sqldf package.


  • The dbExistsTable() function now works faster by filtering the list of tables using SQL (#166).


  • Start on a basic vignette: vignette("RSQLite") (#50).
  • Reworked function and method documentation, removed old documentation (#121).
  • Using dbExecute() in documentation and examples.
  • Using both ":memory:" and ":file::memory:" in documentation.
  • Added additional documentation and unit tests for
    autoincrement keys (#119, @wibeasley).


  • Avoid warning about missing long long data type in C++98 by using a compound data type built from two 32-bit integers, with static assert that the size is 8 indeed.
  • Remove all compilation warnings.
  • All DBI methods contain an ellipsis ... in their signature. Only the name argument to the transaction methods appears before the ellipsis for compatibility reasons.
  • Using the DBItest package for testing (#105), with the new constructor_relax_args tweak.
  • Using the plogr for logging at the C++ level, can be enabled via RSQLite:::init_logging().
  • Using new sqlRownamesToColumn() and sqlColumnToRownames() (r-dbi/DBI#91).
  • Using astyle for code formatting (#159), also in tests (but only if sources can be located), stripped space at end of line in all source files.
  • Tracking dependencies between source and header files (#138).
  • Moved all functions from headers to modules (#162).
  • Fixed all warnings in tests (#157).
  • Checking message wording for deprecation warnings (#157).
  • Testing simple and named transactions (#163).
  • Using container-based builds and development version of testthat on Travis.
  • Enabled AppVeyor testing.
  • Differential reverse dependency checks.
  • Added upgrade script for sqlite3 sources and creation script for the datasets database to the data-raw directory.