SQL

From Erights

(Difference between revisions)
Jump to: navigation, search
(Setup)
Line 14: Line 14:
   ? def makeSql__quasiParser := <import:org.erights.e.tools.database.makeSQLQuasiParser>
   ? def makeSql__quasiParser := <import:org.erights.e.tools.database.makeSQLQuasiParser>
   ? def sql__quasiParser := makeSql__quasiParser(connection)
   ? def sql__quasiParser := makeSql__quasiParser(connection)
-
  # value: <sql__quasiParser>
 
= Creating tables =
= Creating tables =

Revision as of 11:20, 9 June 2010

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

Contents

Setup

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)

Creating tables

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)`

Statements and queries

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]]

Data Access Objects

The SQL quasi-parser is a powerful authority that typically gives complete access to a whole database. You normally don't want to give out this authority to every part of your program that uses the database. You should therefore provide DAO wrapper objects that give more limited access (for example, that give access to a single table, or access only to certain rows).

For an example, see Safe database access in E

Personal tools
more tools