[R-sig-DB] RSQLite enhancements UPDATED

[Seth Falcon]

Hi all,

I have applied Ashish's enhacement patches (with some important fixes
on top).  I would love to hear feedback from anyone who can try out
the new package before we push it to CRAN.

The new stuff is in svn:

   https://hedgehog.fhcrc.org/compbio/r-dbi/trunk/SQLite/RSQLite

   user: readonly
   pass: readonly

Or you can grab 0.4-11 from here:

   http://www.bioconductor.org/packages/misc


This update includes two significant speed improvements:

1. Transactions.  By default, SQLite operates in auto-commit mode.
   This means that each SQL statement sent to the DB is immediately
   committed.  If you are doing many INSERTS or UPDATES, it is _much_
   faster to start a transaction (which turns of auto-commit), do the
   changes, and then commit.  Here's an example:

   dbBeginTransaction(db)
   ## execute many update queries here
   dbCommit(db)

   Note also that if an error occurs along the way, you can also use
   dbRollback to undo to where you issued the begin transaction.

2. Prepared queries and bound parameters.  This will also speed
   inserts and updates.  Instead of looping on calls to dbGetQuery,
   you can instead collect the data in a data.frame and make one call
   to the DB.  Here's a simple example:

   df <- data.frame(a=x, b=y, c=z)
   dbBeginTransaction(db)
   dbGetPreparedQuery(db, 
                      "update points set xpos = :a, ypos = :b where flag = :c",
                      bind.data=df)
   dbCommit(db)


Best Wishes,

+ seth