SQL - Revision history

87.194.132.141: /* Statements and queries */

2010-06-09T18:34:33Z

Statements and queries

New page

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

= 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 change "hsqldb" to connect to a different database type, such as MySQL)

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 like this:

? 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
#

(if you want deterministic behaviour, it's important to include an 'ORDER BY' that fixes the order of the results, otherwise they can be returned in any order)

To get out a single row you can use the singleton/0 method.

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

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 [http://labs.it-innovation.soton.ac.uk/index.php?option=com_content&view=article&id=74:safe-database-access-in-e&catid=36:experiments Safe database access in E]

87.194.132.141 at 18:33, 9 June 2010

2010-06-09T18:33:45Z

←Older revision		Revision as of 18:33, 9 June 2010
Line 8:		Line 8:
	? def connection := driver.connect("jdbc:hsqldb:mem:test", null)		? 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~~)	+	(replace "mem" with "file" to store the database on disk, or change "hsqldb" to connect to a different database type, such as MySQL)

	Wrap it with an SQL quasi-parser:		Wrap it with an SQL quasi-parser:
Line 17:		Line 17:
	= Creating tables =		= Creating tables =

-	Create a test table:	+	Create a test table like this:

	? sql`CREATE TABLE users (		? sql`CREATE TABLE users (
Line 49:		Line 49:
	#		#

-	Get out a single row:	+	(if you want deterministic behaviour, it's important to include an 'ORDER BY' that fixes the order of the results, otherwise they can be returned in any order)
		+
		+	Get out a single row you can use the singleton/0 method.

	? def lookupUser(name :String) :int {		? def lookupUser(name :String) :int {
Line 91:		Line 93:
	? sql`SELECT id FROM users WHERE karma < 0`.singleton()		? sql`SELECT id FROM users WHERE karma < 0`.singleton()
	# value: [3]		# 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:		As a convenience, we can get all the results as a list too:

ThomasLeonard: /* Statements and queries */

2010-06-09T11:22:30Z

Statements and queries

←Older revision		Revision as of 11:22, 9 June 2010
Line 66:		Line 66:
	? sql`SELECT id FROM users`.singleton()		? sql`SELECT id FROM users`.singleton()
	# problem: multiple rows returned by "SELECT id FROM users"		# problem: multiple rows returned by "SELECT id FROM users"
-		+
	A default can be provided if there are no rows:		A default can be provided if there are no rows:

ThomasLeonard: /* Setup */

2010-06-09T11:20:47Z

Setup

←Older revision		Revision as of 11:20, 9 June 2010
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 =

ThomasLeonard at 11:12, 9 June 2010

2010-06-09T11:12:43Z

←Older revision		Revision as of 11:12, 9 June 2010
Line 1:		Line 1:
	Examples of using SQL from E can be found in the sql.updoc file. Here is an extract:		Examples of using SQL from E can be found in the sql.updoc file. Here is an extract:
		+
		+	= Setup =

	Create an in-memory relational database using HSQLDB (http://hsqldb.org/):		Create an in-memory relational database using HSQLDB (http://hsqldb.org/):
Line 13:		Line 15:
	? def sql__quasiParser := makeSql__quasiParser(connection)		? def sql__quasiParser := makeSql__quasiParser(connection)
	# value: <sql__quasiParser>		# value: <sql__quasiParser>
		+
		+	= Creating tables =

	Create a test table:		Create a test table:
Line 22:		Line 26:
	> karma BIGINT NOT NULL,		> karma BIGINT NOT NULL,
	> comments VARCHAR(256) DEFAULT NULL)`		> comments VARCHAR(256) DEFAULT NULL)`
		+
		+	= Statements and queries =

	Insert some values:		Insert some values:
Line 57:		Line 63:
	# problem: no rows returned by "SELECT id FROM users WHERE userName=?"		# problem: no rows returned by "SELECT id FROM users WHERE userName=?"

-	We also get an exception if multiple rows are returned:	+	We also get an exception if multiple rows are returned:

	? sql`SELECT id FROM users`.singleton()		? sql`SELECT id FROM users`.singleton()
	# problem: multiple rows returned by "SELECT id FROM users"		# problem: multiple rows returned by "SELECT id FROM users"

-	A default can be provided if there are no rows:	+	A default can be provided if there are no rows:
		+
	? sql`SELECT id FROM users WHERE userName='david'`.singleton(fn {-1})		? sql`SELECT id FROM users WHERE userName='david'`.singleton(fn {-1})
	# value: -1		# value: -1
Line 71:		Line 78:
	# problem: multiple rows returned by "SELECT id FROM users"		# problem: multiple rows returned by "SELECT id FROM users"

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

	? def query := "SELECT id FROM users"		? def query := "SELECT id FROM users"
Line 96:		Line 102:
	# value: [[0, 1], [1, 0], [2, 0]]		# value: [[0, 1], [1, 0], [2, 0]]

-	~~See~~ [http://labs.it-innovation.soton.ac.uk/index.php?option=com_content&view=article&id=74:safe-database-access-in-e&catid=36:experiments Safe database access in E]	+	= 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 [http://labs.it-innovation.soton.ac.uk/index.php?option=com_content&view=article&id=74:safe-database-access-in-e&catid=36:experiments Safe database access in E]

ThomasLeonard: SQL examples

2010-06-09T11:06:20Z

SQL examples

←Older revision		Revision as of 11:06, 9 June 2010
Line 1:		Line 1:
	Examples of using SQL from E can be found in the sql.updoc file. Here is an extract:		Examples of using SQL from E can be found in the sql.updoc file. Here is an extract:
-
-	~~= Setup =~~

	Create an in-memory relational database using HSQLDB (http://hsqldb.org/):		Create an in-memory relational database using HSQLDB (http://hsqldb.org/):
Line 8:		Line 6:
	? def connection := driver.connect("jdbc:hsqldb:mem:test", null)		? def connection := driver.connect("jdbc:hsqldb:mem:test", null)

-	(replace "mem" with "file" to store the database on disk, or ~~change~~ "hsqldb" to connect to a ~~different~~ database ~~type, such as MySQL~~)	+	(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:		Wrap it with an SQL quasi-parser:
Line 14:		Line 12:
	? 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 =~~	+	Create a test table:
-		+
-	Create a test table ~~like this~~:	+

	? sql`CREATE TABLE users (		? sql`CREATE TABLE users (
Line 25:		Line 22:
	> karma BIGINT NOT NULL,		> karma BIGINT NOT NULL,
	> comments VARCHAR(256) DEFAULT NULL)`		> comments VARCHAR(256) DEFAULT NULL)`
-
-	~~= Statements and queries =~~

	Insert some values:		Insert some values:
Line 49:		Line 44:
	#		#

-	~~(if you want deterministic behaviour, it's important to include an 'ORDER BY' that fixes the order of the results, otherwise they can be returned in any order)~~	+	Get out a single row:
-		+
-	~~To get~~ out a single row ~~you can use the singleton/0 method.~~	+

	? def lookupUser(name :String) :int {		? def lookupUser(name :String) :int {
Line 64:		Line 57:
	# problem: no rows returned by "SELECT id FROM users WHERE userName=?"		# problem: no rows returned by "SELECT id FROM users WHERE userName=?"

-	We also get an exception if multiple rows are returned:	+	We also get an exception if multiple rows are returned:

	? sql`SELECT id FROM users`.singleton()		? sql`SELECT id FROM users`.singleton()
	# problem: multiple rows returned by "SELECT id FROM users"		# problem: multiple rows returned by "SELECT id FROM users"
-		+
-	A default can be provided if there are no rows:	+	A default can be provided if there are no rows:
-		+
	? sql`SELECT id FROM users WHERE userName='david'`.singleton(fn {-1})		? sql`SELECT id FROM users WHERE userName='david'`.singleton(fn {-1})
	# value: -1		# value: -1
Line 79:		Line 71:
	# problem: multiple rows returned by "SELECT id FROM users"		# problem: multiple rows returned by "SELECT id FROM users"

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

	? def query := "SELECT id FROM users"		? def query := "SELECT id FROM users"
Line 93:		Line 86:
	? sql`SELECT id FROM users WHERE karma < 0`.singleton()		? sql`SELECT id FROM users WHERE karma < 0`.singleton()
	# value: [3]		# 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:		As a convenience, we can get all the results as a list too:
Line 99:		Line 96:
	# value: [[0, 1], [1, 0], [2, 0]]		# value: [[0, 1], [1, 0], [2, 0]]

-	~~= Data Access Objects =~~	+	See [http://labs.it-innovation.soton.ac.uk/index.php?option=com_content&view=article&id=74:safe-database-access-in-e&catid=36:experiments Safe database access in E]
-		+
-	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~~ [http://labs.it-innovation.soton.ac.uk/index.php?option=com_content&view=article&id=74:safe-database-access-in-e&catid=36:experiments Safe database access in E]	+