SQL
From Erights
Revision as of 11:06, 9 June 2010 by ThomasLeonard (Talk)
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]]