safeSQL: Return a function that safely performs sql queries on a...
In jbrzusto/motusClient: fetch data from the motus data server

Description Usage Arguments Value Note Author(s)

This uses the 'params' parameter for DBI::dbGetQuery and DBI::dbExecute (for RSQLite and ":" parameters) or dbQuoteStrings (for MySQL or RSQLite " prevent e.g. SQL injection attacks.

1	safeSQL(con, busyTimeout = 300)

`con`	RSQLite connection to database, as returned by dbConnect(SQLite(), ...), or character scalar giving path to SQLite database, or MySQLConnection, or dplyr::src
`busyTimeout`	how many total seconds to wait while retrying a locked database. Default: 300 (5 minutes). Uses `pragma busy_timeout` to allow for inter-process DB locking. Only implemented for SQLite connections, as it appears unnecessary for MySQL connections.

a function, S with class "safeSQL" taking two or more parameters:

query sqlite query with parameter handling indicated by:
- words beginning with ":", which does parameter binding for RSQLite,
- or sprintf-style formatting codes (e.g. "%d") which does parameter substitution for RSQLite or MySQL
... list of named (for ":" binding) or unnamed (for " query. For ":" binding, all items must be named and have the same length. For "

For RSQLite, these items are passed to data.frame, along with the parameter stringsAsFactors=FALSE.

":"-binding example; SQLite only: S("insert into contacts values(:address, :phone)", address=c("123 West Blvd, Truro, NS", "5 East St., Digby, NS"), phone=c("902-555-1234", "902-555-6789"))
" S("insert into contacts values(\" S("insert into contacts values(\"
.CLOSE boolean scalar; if TRUE, close the underlying database connection, disabling further use of this function.
.QUOTE boolean scalar (only for RMySQL connections); if TRUE, the default, quote string parameters using dbQuoteString. Any parameter wrapped in DBI::SQL will not be quoted. The only reason to use .QUOTE=FALSE is for a query where you know all parameters must not be quoted, and don't want to clutter your code with multiple DBI::SQL. A table name used as a parameter to a query should not be quoted, so for example, s = safeSQL(dbConnect(MySQL(), 'motus')); tableName = "tags" columnName = "fullID" columnValue = "Mytags#123:4.7@166.38" s("select * from } would select all rows from the \code{tags} table where \code{fullID="Mytags#123:4.7@166.38" Without using DBI::SQL, the resulting query would be the incorrect: select * from 'tags' where 'fullID' = 'Mytags#123:4.7@166.38'

for convenience, access is provided to some safeSQL internals, via the "$" method for class safeSQL

$con the underlying db connection
$db the underlying database filename

For MySQL, only one line of an insert can be provided per call; i.e. there is no SendPreparedQuery method to allow binding a data.frame's data to a prepared query. Moreover, character parameters are quoted using dbQuoteString unless the parameter is wrapped in DBI::SQL, or if you specify .QUOTE=FALSE

safeSQL is meant for multi-process access of a DB with small, fast queries; this is the case for the server.sqlite database that holds job information. The longer accesses required by e.g. the tag finder are handled by locking the receiver DB via lockSymbol().

For both MySQL and SQLite connections, queries that fail due to deadlock-prevention by the database are retried after a random wait of 0 to 10 seconds.

John Brzustowski jbrzusto@REMOVE_THIS_PART_fastmail.fm

jbrzusto/motusClient documentation built on May 30, 2019, 4:33 p.m.