SQL

From Erights

Revision as of 11:06, 9 June 2010

Examples of using SQL from E can be found in the sql.updoc file. Here is an extract:

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)

(replace "mem" with "file" to store the database on disk, or replace "hsqldb" to connect to a full database server)

Wrap it with an SQL quasi-parser:

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

Create a test table:

 ? sql`CREATE TABLE users (
 >    id 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 id ASC` {
 >   println(`$name => $karma`)
 > }
 # stdout: alice => 1
 #         bob => 0
 #         carol => 0
 #

Get out a single row:

 ? def lookupUser(name :String) :int {
 >   def [id] := sql`SELECT id FROM users WHERE userName=$name`.singleton()
 >   return id
 > }
 
 ? lookupUser("bob")
 # value: 1
 
 ? lookupUser("fred")
 # problem: no rows returned by "SELECT id FROM users WHERE userName=?"
 
 We also get an exception if multiple rows are returned:
 
 ? sql`SELECT id FROM users`.singleton()
 # problem: multiple rows returned by "SELECT id FROM users"
 
 A default can be provided if there are no rows:
 ? sql`SELECT id FROM users WHERE userName='david'`.singleton(fn {-1})
 # value: -1

But this is still an error:

 ? sql`SELECT id FROM users`.singleton(fn {-1})
 # problem: multiple rows returned by "SELECT id FROM users"

Internally, we're constructing PreparedStatement objects, not doing string interpolation:

 ? def query := "SELECT id FROM users"
 ? sql`${query}`
 # problem: <SQLException: unexpected token: ?>

This means we don't have to worry about quoting:

 ? def ono := "O'No! $@?"
 ? sql`INSERT INTO users (userName, karma) VALUES ($ono, -5)`
 # value: 1
 
 ? sql`SELECT id FROM users WHERE karma < 0`.singleton()
 # value: [3]
 
 ? sql`INSERT INTO users (userName, karma) VALUES ('$$@@', -6)`
 ? sql`SELECT userName FROM users WHERE karma = -6`.singleton()
 # value: ["$@"]

As a convenience, we can get all the results as a list too:

 ? sql`SELECT id, karma FROM users WHERE karma >= 0 ORDER BY id ASC`.asList()
 # value: [[0, 1], [1, 0], [2, 0]]

See Safe database access in E