SQL

groovity-sql

Groovity sql is a small module that offers a consistent access pattern for applications to leverage SQL datasources - datasources may be managed by the container and loaded via JNDI, or programmatically bound e.g. for use in unit tests, or for integrating with external configuration systems.

A typical pattern to retrieve a Groovy SQL facade for a named datasource would be to assign it to a script field, which will happen automatically at script load time and ensure fast-failure if the datasource is not available

@Field sql = load('/groovity/sql').sql('myDBName')

To programatically bind a datasource, you would typically want to do it in a static initializer to ensure the datasource is available before the application completes startup. For example, the groovity sample app programmatically installs an H2 in-memory datasource for unit tests:

import org.h2.jdbcx.JdbcDataSource;

static init(){
	def ds = new JdbcDataSource()
	ds.url = "jdbc:h2:mem:test;MODE=MySQL;DATABASE_TO_UPPER=false;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"
	load('/groovity/sql').bind('sampleDB',ds)
}

A typical startup concern for applications might be initializing DB tables or kicking off background processes that hit the DB; since groovity allows programmatic binding of datasources, order of operations can become a pain point at startup, so groovity-sql offers two convenient methods for interacting with datasources that may not yet be bound.

First, the init method allows deferred execution of a list of SQL statements (i.e. they will execute immediately if the datasource is already available, otherwise they will be executed after the datasource is bound)

load('/groovity/sql').init('sampleDB',[
	'''
		CREATE TABLE IF NOT EXISTS users(
			id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
			userName VARCHAR(255) NOT NULL,
			digest VARBINARY(255) NOT NULL,
			secretKey VARBINARY(255) NOT NULL,
			created DATETIME NOT NULL,
			modified DATETIME NOT NULL,
			PRIMARY KEY (id),
			UNIQUE(userName)
		) ENGINE=InnoDB
	''',
	'''
		CREATE TABLE IF NOT EXISTS notes(
			id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
			userId INT UNSIGNED NOT NULL,
			message VARCHAR(65535) NOT NULL,
			worldRead BOOL DEFAULT 0,
			created DATETIME NOT NULL,
			modified DATETIME NOT NULL,
			PRIMARY KEY (id),
			FOREIGN KEY (userId) REFERENCES users(id),
		)
	'''
	])

Second, the call method allows you to define a callback that will be executed with the Groovy SQL facade once the named datasource is bound and initialized

load('/groovity/sql').call('sampleDB',{
  sql ->  
  //do some deferred logic with the SQL object
})

Another function groovity-sql offers is the ability to easily declare that a given table in a datasource is to be used to load configuration data that will be applied using a Groovity Configurator. The only requirement is that the table have columns named "path", "property" and "value" referring to the configuration path, property name and value to be loaded.

load('/groovity/sql').configurator('sampleDB','myConfigTable')

The groovity-sql module offers a convenient general-purpose HTML paginated SQL table visualization as a custom Tag. For example, to visualize the contents of the configuration table in the previous example, a groovity web page can be set up with the following call:

<g:sqlTable id="config" table="myConfigTable" title="Config" sql="${load('/groovity/sql').sql('sampleDB')}" cols="${[path:'Path',property:'Property',value:'Value']}"/>

Groovity sql also provides a data source implementation for groovity-data so that you can develop read/write DataModels on top of SQL databases. It comes with a single trait HasRowId that can be used to automatically handle mapping between primary keys and data pointers.

The sql data source recognizes three special configuration variables on a data type:

sql.dataSource - the name of the bound JDBC datasource to connect to for this data type

sql.tableName - the table name this data type reads from and writes to

sql.dateCol - the date field in the table to be used to watch for data updates

For example, the note type from the sample web application uses a SQL data source:

/* /data/types/note.grvt */

static conf=[
	source: 'sql',
	ttl: 5,
	refresh: 3,
	'sql.tableName': 'notes',
	'sql.dataSource': 'sampleDB',
	'sql.dateCol': 'modified'
]

// Domain model: a single Note
public class Note implements DataModel, Stored, HasRowId {
	long userId;
	String message;
	boolean worldRead;
	Date created;
	Date modified;

	@ModelSkip public String getUserName(){
		if(userId){
			return load('/data/factory')('user',userId.toString())?.userName
		}
	}

}

new Note()

Using the data factory, a new note can be easily constructed and saved to the sql database

load '/data/factory'

factory('note').putAll(userId:123, message:'Hello World').store()

The groovity-sql data source supports issuing SQL queries using a query-string format to the factory; this will return a list of DataModels that match the query.

load '/data/factory'

notes = factory('note','userId=123&worldRead=true')
notes.each{
	//...
}

Or you can load a single DataModel by ID with a primary key lookup

load '/data/factory'

note = factory('note','456')
note.message += " updated with more text"
note.store()