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

[Seth Falcon]

On Mon, Jul 5, 2010 at 12:48 PM, Jonathan Greenberg
<greenberg using ucdavis.edu> wrote:
> 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?

Probably? :-)

Again, I think at this point, I don't have enough detail to be of much
use.  I don't understand how you can be doing a query and
programmatically inserting and not know how many fields you have. Have
you tried creating the table within the loop (with the new arrangement
for commit?)


>
> --j
>
> On Mon, Jun 28, 2010 at 8:50 PM, Seth Falcon <seth using 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/
>>
>



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