The extension-functions.c file which I sent via email replaces the file of the same name and incorporates:

• added locate as a synonym for charindex for MySQL compatibility
• trim, ltrim, rtrim are no longer provided since they already exist in SQLite

• Remove valueClass
• Don't use argument names
• Remove ... if not used (from args and docs)

Basic vignette should show how to use RSQLite.

As done by RPostgreSQL and RMySQL

This issue was received by email to the maintainer:

Try this self contained code with the github version of RSQLite.
Column b seems to be copied correctly from dd to ddt but column a
(which has an NA) is converted to character but should not be.
Regards.

> library(RSQLite)
> con <- dbConnect(SQLite())
> dd <- data.frame(a = c(NA, 3.5), b = c(1.5, 2.5))
> dbWriteTable(con, "dd", dd, row.names = FALSE)
[1] TRUE
> dbGetQuery(con, "create temporary table ddt(a, b)")
NULL
> dbGetQuery(con, "insert into ddt select * from dd")
NULL
> str(dbGetQuery(con, "select * from dd"))
'data.frame':   2 obs. of  2 variables:
 $ a: num  NA 3.5
 $ b: num  1.5 2.5
> str(dbGetQuery(con, "select * from ddt"))
'data.frame':   2 obs. of  2 variables:
 $ a: chr  NA "3.5"
 $ b: num  1.5 2.5

RSQLite guesses the type of columns using a heuristic. The schema of the table is consulted, if available. Otherwise, the first row of the result set is used. This is imperfect, but allows for more efficient code.

I suspect what's happening here is that the way the temp table is created, doesn't capture the same schema details.

and use in sqliteNewConnection

Hi, In your announcement in R-sig-DB of the imminent release of RSQLite 1.0.0, you said "idIsValid() is deprecated. Please use dbIsValid() instead." However, dbIsValid is not exported.

Would be nice to have the MySQL substring_index function in extension-functions.c. Since parsing fields consisting of delimited subfields, e.g. AB,D,EFG,H can be fairly difficult with only the supplied functions. See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

If length(dbListResults(con)) > 0, functions:

• clone silently (e.g. sqliteTableFields)
• throws an error (e.g. sqliteTransactionStatement)
• do nothing (the majority)

We need a clearly articulated strategy to adhere to.

So e.g. sqliteWriteTable() doesn't have to jump through so many hoops.

and use Authors@R

Prior to the update, my code was working just fine. I set the row.names=FALSE and all was good. Today I ran my code and I'm getting a message saying that I'm one column short. Did something change?

in S4R.R

i looked over the method and it wasn't obvious why a ... doesn't get passed from the dbWriteTable() call to the read.table() call inside, but even if that's not allowed, could dec= be added on its own? sorry if i missed something, the word "dec" keeps popping up search results for december. thanks!

dbWriteTable uses dbBegin ... dbCommit but these do not nest so dbWriteTable cannot be within a larger transaction.

These should be changed to use SQLite savepoints as those can be nested.

dbGetQuery(con, "savepoint dbWriteTable")
....
dbGetQuery(con, "release dbWriteTable")

For more info see: http://sqlite.org/lang_transaction.html

Maybe selection of base datasets? This would avoid a lot of repetition in examples.

This is according to the docs:

row.names: A string or an index specifying the column in the DBMS table to use as row.names in the output data.frame (a NULL, "", or 0 specifies that no column should be used as row.names in the output.)

This is what happens (using 9f75343):

Browse[1]> dbReadTable(connection, table, row.names = NULL)
Error: length(row.names) == 1 is not TRUE
Browse[1]> dbReadTable(connection, table, row.names = TRUE)
  Prime
1     2
2     3
3     5
4     7
5    11
Browse[1]> dbReadTable(connection, table, row.names = FALSE)
  N Prime
1 1     2
2 2     3
3 3     5
4 4     7
5 5    11

I'll be glad to provide a more complete test case if necessary. Could you please re-enable support for row.names = NULL?

Reference: KentonWhite/ProjectTemplate#118

That automates the currently manual instructions.

It would be nice to be able to do this with dbUnloadDriver() so .onUnload() could cleanly unload the database (this would make my devel workflow a lot smoother).

@seth it looks like manager used to once look after a list of connections, but it doesn't anymore. Do you recall why? Are there any workarounds?

e.g. ":memory:"

I have an open question on StackOverflow (http://stackoverflow.com/questions/26816880/debugging-sqlite-rtree), where a commenter indicated that my problem was related to an RSQLite bug. The SO question comes with a minimal example, but in essence:

When creating an R*tree the comparator querying gets broken. Asking for all records where a value is above a certain level would yield no results, even though there is lots of records with higher values (again it is all described more thoroughly in the SO question).

Is it possible to examine whether it is indeed a bug in RSQLite that causes this behaviour?

Below is a small program which simply uses two R process's to insert integers into a table. The table is created as "create table vals (value integer not null)". I created it outside the driver function simply to investigate whether it's an issue between the main the child process. It's not. After about 50 inserts, I get an error, which is not helpful since R loses the actual error when mcapply is used.

In any case, I tried starting and ending a transaction, as well as unloading the driver - same. Looks like the database gets locked and the other writes fail.

require(RSQLite)
require(parallel)

write.one.value = function(val, db.path)
{
   driver = SQLite()
   connection = dbConnect(driver, dbname=db.path)

   data = expand.grid(val)
   colnames(data) = c("value")
   query = paste("insert into vals (value) values (@value)", sep="")
   dbGetPreparedQuery(connection, query, bind.data=data)
   dbDisconnect(connection)
}

write.values = function(db.path)
{
   res = mclapply(seq(1,100,1), write.one.value, mc.cores=2, db.path=db.path)
}

# write.values("~/db.sqlite")

While from the help file :

# A zero row data frame just creates a table definition.
dbWriteTable(con, "mtcars2", mtcars[0, ])
dbReadTable(con, "mtcars2")

Did I miss something? Should we change the doc?

Hi RSQLite devs,

we are currently working on a R package that among other things lets linguists and speech scientist perform hierarchical queries of annotation structures. While looking for a feasible solution we stumbled on the ability of sqlite (>= 3.8.3) being able to handle common table expression queries. I was wondering if we can expect to see a version bump of sqlite in RSQLite some time in the future?

In a naive attempt to update the sqlite version I replaced the three files in src/sqlite/ with the according files of the current amalgamation release and updated the version check of inst/UnitTests/dbGetInfo_test.R to 3.8.4.2. This seem to do the trick. The package builds without any errors and from what I can tell works fine after installation (including CTE queries). But I might be missing something...

cheers

i.e. flags and vfs

Updating to the latest version of RSQLite today on Mac/Mavericks I get:

dbConnect(drv='SQLite', dbname='./testdb.sqlite')
Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘dbConnect’ for signature ‘"character"’

I get:

sqlite-all.c:1:35: warning: extra tokens at end of #ifdef directive [-Wextra-tokens]
#ifdef RSQLITE_USE_BUNDLED_SQLITE || WIN32

I don't know enough about C to know whether or not this is spurious or not.

dbWriteTable fails if the data frame has no rows. (Similar code does work with the RH2 driver for the H2 database.)

> library(RSQLite)
Loading required package: DBI
> con <- dbConnect(SQLite())
> DF <- data.frame(a = numeric(0), b = numeric(0))
> dbWriteTable(con, "DF", DF)
[1] FALSE
Warning message:
In value[[3L]](cond) : bind.data must have non-zero dimensions

as per generic docs.

To make for a simple design, I suggest we further reduce the use of multiple inheritance, instead replacing it with composition. Currently, SQLiteObject is used as a base class underlying all three main classes (driver, connection and results). This is not great because it forces the use of multiple inheritance (which makes method dispatch harder to predict) and it's only providing a very thin wrapper around an external pointer (and the type of the object that that points to is different in each case).

This change would mean the every old method definition for SQLiteObject would need to be duplicated three times, but that's just show, dbDataType, make.db.names, isSQLKeyword and SQLKeywords.

Would you have any objections to moving the generics currently defined by RSQLite into DBI? I think they're of general use to other DB packages.

(I'm talking with David James about taking over maintainership of DBI)

It would be nice to be able to specify a comment.char to RSQLite when importing files with dbWriteTable. Also quote= as in read.table.

I have a problem using RSQLite 1.0.0 with rApache. In an interactive R console, everything works as expected, but when sending a request to the same package with OpenCPU/rApache, I get a version mismatch error.

$ curl http://198.105.216.164/ocpu/library/hcapi3/R/getLayer/json -d '{"var" : ["whea_h", "AEZ16_CLAS"], "by" : "ADM1_NAME_ALT"}' -X POST -H "Content-Type:application/json"
error in evaluating the argument 'drv' in selecting a method for function 'dbConnect': Error in SQLite(shared.cache = T) :
  SQLite mismatch between compiled version 3.8.6 and runtime version 3.6.20

SQLite 3.8 doesn't seem available from CentOS6 repos, so not sure what's the easiest way around.

Thanks, --Mel.

(I sent the message below to Seth Falcon, adding it here so it does not get lost)

I recently had a problem with importing a sqlite DB table through your package. The sqlite DB contained apparently 8 byte "bigints" which RSQLite than happily converted to R integers and overflowing values. I only noticed that because all values should have been > 0, but I encountered negative values in R.

I solved this problem be explicitly casting these fields to double in my SELECT statement.

Note that I understand that such large INTS cannot (currently) be represented in R and I am not complaining about having to do that cast, but I find it worrisome and error-prone that the above mentioned internal conversion is performed without any error or warning. I also could not find a hint in the documentation about this, maybe this would help other users?

Would be nice to include SQLite 3.8.7 in the next version of RSQLite even if it delays the release:

http://permalink.gmane.org/gmane.comp.db.sqlite.general/90549

• Replace SQLite.R and SQLiteSupport.R with one file for each class, with methods and their implementations interwoven
• Move inst/NEWS to NEWS, and remove the files in inst/ that are out of date. Combine readme.md and inst/README (cran now supports markdown readmes)

e.g.

setMethod("dbConnect", "character",
  definition = function(drv, ...){
    d <- dbDriver(drv)
    dbConnect(d, ...)
  },
  valueClass = "SQLiteConnection"
)

seems a bit outside of out responsibility

Currently when first value is NA, column is returned as character.

The github and tracker URL should be added to the DESCRIPTION file so users know where to post bug reports and find further info.

Hi, I was trying to test RSQLite 1.0.0 per Hadley's request on R-sig-DB.
First I did devtools::install_github("rstats-db/DBI") with no problems, then I tried devtools::install_github("rstats-db/RSQLite") with the following results:

[...]
gcc -std=gnu99 -I/home/biocbuild/bbs-3.0-bioc/R/include -DNDEBUG -DRSQLITE_USE_BUNDLED_SQLITE -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_SOUNDEX -DSQLITE_MAX_VARIABLE_NUMBER=40000 -DSQLITE_MAX_COLUMN=30000 -DTHREADSAFE=0 -I/usr/local/include    -fpic  -g -O2  -Wall -c extension-functions.c -o extension-functions.o
extension-functions.c:124:24: fatal error: sqlite3ext.h: No such file or directory
 #include "sqlite3ext.h"
                        ^
compilation terminated.
make: *** [extension-functions.o] Error 1
ERROR: compilation failed for package 'RSQLite'
* removing '/home/biocbuild/bbs-3.0-bioc/R/library/RSQLite'
* restoring previous '/home/biocbuild/bbs-3.0-bioc/R/library/RSQLite'
Error: Command failed (1)

This is on Ubuntu 14.04 and I do have libsqlite3-dev installed.

> sessionInfo()
R version 3.1.1 (2014-07-10)
Platform: x86_64-unknown-linux-gnu (64-bit)

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

loaded via a namespace (and not attached):
 [1] devtools_1.5   digest_0.6.4   evaluate_0.5.5 httr_0.4       memoise_0.2.1 
 [6] parallel_3.1.1 RCurl_1.95-4.3 stringr_0.6.2  tools_3.1.1    whisker_0.3-2 

Incidentally (I don't think this is related) I had to set options(unzip="unzip") (it was set to "") before install_github() would work.

I run a SQL statement via RSQLite over a local sqlite3 database:

SELECT * FROM
    (SELECT Stkcd1, Stkcd2,
        ((psum-(sum1*sum2/n))*(psum-(sum1*sum2/n)))/((sum1sq-sum1*sum1/n)*(sum2sq-sum2*sum2/n))) AS corr,
        n FROM
        (SELECT c1.Stkcd AS Stkcd1, c2.Stkcd AS Stkcd2,
            SUM(t1.Adjprcwd) AS sum1,
            SUM(t2.Adjprcwd) AS sum2,
            SUM(t1.Adjprcwd*t1.Adjprcwd) AS sum1sq,
            SUM(t2.Adjprcwd*t2.Adjprcwd) AS sum2sq,
            SUM(t1.Adjprcwd*t2.Adjprcwd) AS psum,
            COUNT(*) AS n FROM SCompany AS c1, SCompany AS c2,
            CTick AS t1, CTick AS t2
        WHERE c1.Stkcd < c2.Stkcd AND
            c1.Stkcd = t1.Stkcd AND
            c2.Stkcd = t2.Stkcd AND
            t1.Trddt = t2.Trddt
        GROUP BY c1.Stkcd, c2.Stkcd)
        )
    WHERE ABS(corr)>=%f AND n>=%d
    ORDER BY ABS(corr) DESC
    LIMIT %d

In the statement, I calculate the correlation of two time series and aggregate the data by correlations. %f, %d will be replaced by sprintf function in R. Although the SQL currently looks stupid and of low efficiency, it should work both in R and SQLite Manager with the same results.

However, an error occurs when RSQLite runs:
Error in sqliteFetch(rs, n = -1, ...) :
RSQLite driver: (RS_SQLite_fetch: failed first step: Domain error)

I don't know how I should debug the code in R, so I run the same code with parameters replaced in SQLite Manager in Firefox. It perfectly worked. But the same code simply does not work in RSQLite. I don't know what "Domain error" means and there seems to be little information online about this error.

Anyone has an idea what might be the problem? Or how should I debug the error?