sql__quasiParser [patch]

Hi all,

I've written an sql__quasiParser, to provide easy and safe access to SQL
databases. It's on my "proposed" branch:

  git pull git://gitorious.org/~tal-itinnov/repo-roscidus/it-innovation.git proposed

Here's the patch:

  http://gitorious.org/~tal-itinnov/repo-roscidus/it-innovation/commit/44f91197fb5d1ded1c59e84366539ebd2719564a

I've tested it using HSQLDB (a pure-Java database), but it should work
with any database supported by Java.

Internally, it generates a PreparedStatement from the template (by
replacing "${n}" with "?"), so there's no need for any special quoting
to avoid SQL injection attacks. The quasi-parser executes the statements
(rather than just creating statement objects), so an sql__quasiParser
itself confers the authority to access the database. The
PreparedStatements are cached automatically.

For queries, the result can be used as an E iterator in a loop
directory, and there are singleton/0 and singleton/1 convenience methods
if you're only expecting a single row result (the /1 version allows you
to provide code to handle the case where no rows are returned).


The updoc goes through various tests, but here are some simple examples:


Create an in-memory relational database using HSQLDB (http://hsqldb.org/):

? def driver := <unsafe:org.hsqldb.makejdbcDriver>()
? def connection := driver.connect("jdbc:hsqldb:mem:test", null)

Wrap it with an SQL quasi-parser:

? def makeSql__quasiParser := <import:org.erights.e.tools.database.makeSql__quasiParser>
? def sql__quasiParser := makeSql__quasiParser(connection)
# value: <sql__quasiParser>

Create a test table:

? sql`CREATE TABLE users (
>	userId BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY,
>	userName VARCHAR(256) NOT NULL,
>	created DATETIME DEFAULT NOW NOT NULL,
>	karma BIGINT NOT NULL,
>	comments VARCHAR(256) DEFAULT NULL)`

Insert some values:

? for name in ["alice", "bob", "carol"] {
>   sql`INSERT INTO users (userName, karma) VALUES ($name, 0)`
> }

Update one row:

? sql`UPDATE users SET karma=1 WHERE userName='alice'`
# value: 1

Read all the values out:

? for [name, karma] in sql`SELECT userName, karma FROM users ORDER BY userId ASC` {
>   println(`$name => $karma`)
> }
# stdout: alice => 1
#         bob => 0
#         carol => 0
#

Get out a single row:

? def lookupUser(name :String) :int {
>   def [userId] := sql`SELECT userId FROM users WHERE userName=$name`.singleton()
>   return userId
> }

? lookupUser("bob")
# value: 1

Quoting is handled for you:

? def ono := "O'No! $@?"
? sql`INSERT INTO users (userName, karma) VALUES ($ono, -5)`
# value: 1


There's also a blog post here, aimed at people less familiar with E:

http://labs.it-innovation.soton.ac.uk/index.php?option=com_content&view=article&id=74:safe-database-access-in-e&catid=36:experiments


-- 
Dr Thomas Leonard
IT Innovation Centre
2 Venture Road
Southampton
Hampshire SO16 7NP

Tel: +44 0 23 8076 0834
Fax: +44 0 23 8076 0833
mailto:tal-v5nx5w6akNyLE8xUarVfuPLx9OUvmyODWmv/[email protected]
http://www.it-innovation.soton.ac.uk