[R-sig-DB] Fwd: concurrent reading/writing in "chunks" with RSQLite (need some help troubleshooting)

[Seth Falcon]

Hi again,

I had a chance to play with this a bit.  You may find it useful to
read up on the how SQLite does locking
(http://www.sqlite.org/lockingv3.html).

I think what's happening is that read_con with the open read_query is
holding a shared lock on the db.  While that shared lock is held,
writes are not allowed.  The discussion of transaction in the SQLite
doc referenced above gave me the idea to try putting the writes into a
transaction and waiting to commit until after the read_query is
complete.  This seems to work.  Here's an example:

library("RSQLite")
mysqldb='test.sqlite'
unlink(mysqldb)
fetch_n=3

# Create a data table.
somedata=data.frame(data1=seq(1:10),data2=seq(1:10)*3)

# Make a table in our database with it.
m <- dbDriver("SQLite")
con=dbConnect(m, dbname=mysqldb)
dbWriteTable(con,"TABLEA",somedata,overwrite=TRUE)
dbGetQuery(con, "CREATE table t2 (data1 INTEGER, data2 FLOAT)")
dbDisconnect(con)

# Now we want to read from TABLEA in "chunks" and write to TABLEB.
read_con=dbConnect(m, dbname=mysqldb)
write_con=dbConnect(m, dbname=mysqldb)

read_query=dbSendQuery(read_con,"SELECT * FROM TABLEA")
dbBeginTransaction(write_con)
while (!dbHasCompleted(read_query))
{
       read_chunk=fetch(read_query,fetch_n)
       new_data=data.frame(data3=read_chunk$data1*4,data4=read_chunk$data2*4)
       dbGetPreparedQuery(write_con, "INSERT into t2 values (?, ?)", new_data)
}
dbClearResult(read_query)
dbCommit(write_con)

dbDisconnect(read_con)
dbDisconnect(write_con)



Unfortunately, dbWriteTable does not currently allow you to interact
with transactions so I'm not sure there is a way to accomplish what
you want and still use the dbWriteTable convenience function.

Hope that helps,

+ seth

-- 
Seth Falcon | @sfalcon | http://userprimary.net/