concurrent reading/writing in "chunks" with RSQLite (need some help troubleshooting)
Seth: Second follow-up question. If you do NOT, in advance, know the number of columns that will be in the output table, is there any way to safely perform the initial output table creation within the loop (e.g. the end of the first fetch determine the table # of columns)? Or should I do a "first pass" on a single row of input data to determine the output data table parameters? --j
On Mon, Jun 28, 2010 at 8:50 PM, Seth Falcon <seth at userprimary.net> wrote:
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/