SQL Extension

Database Transactions

Setup

First, you need to make sure that you have access to an SQL database where you have permission to execute the following types of statements:

• CREATE FUNCTION
• CREATE INDEX
• CREATE SCHEMA
• CREATE TABLE
• DELETE
• INSERT
• SELECT
• UPDATE

A PostgreSQL database is recommended since the SQL extension of InPUTj has so far only been tested with PostgreSQL, but it should work with any DBMS that conforms to the core and SQL/XML part of the SQL standard. PostgreSQL downloads, along with download instructions, can be found here.

Then, set up InPUTj in accordance with the instructions here. Lastly, download and add the latest InPUTj SQL jar file (InPUTj-SQL.jar) and a JDBC driver for your chosen DBMS to the classpath of your Java project. The InPUTj SQL jar file can be downloaded from here, and a JDBC driver for PostgreSQL can be downloaded from here. Information about the JDBC API can be found here.

Introduction

When using InPUTj, all types of SQL database transactions (export, import, updating and deletion) can be handled via instances of the class se.miun.itm.input.util.sql.DatabaseAdapter. DatabaseAdapter has two constructors. One of them takes a java.sql.Connection as argument and creates a DatabaseAdapter that isn't thread-safe, and the other one takes the same argument as the former along with a boolean that specifies whether the DatabaseAdapter shall be thread-safe.

In order to manage, export and import multiple related code mapping descriptors in a simple way, Document representations of code mapping descriptors can be added to instances of the class se.miun.itm.input.model.mapping.Framework. When a code mapping descriptor is exported, data about which framework it belongs to, if any, is also exported. That way, different sets of code mappings can easily be selected when experimental descriptors are imported. The Framework class, which implements the Set interface and supports all “optional” set operations, has two constructors that both takes as first argument the ID that the framework shall have. Aside from the ID, one of them takes a Collection of code mapping Document:s as argument and the other one takes an arbitrary number of code mapping Document:s either as an array or as a sequence of arguments. Document:s given to either of the constructors will be part of the created Framework.

Export

To export a Document or Exportable to an SQL database, simply create a DatabaseAdapter and call its export method with the Document/Exportable as argument. That way, both the Document/Exportable itself and all its auxiliary objects are exported to the database. For example, when an IExperiment is exported, not only its contents and its designs for algorithm, problem features, preferences and outputs are included in the export, but also its experimental context, the code mappings of the design spaces of its designs and the frameworks of the code mappings. If any of those auxiliary descriptors is already in the designated database, it is automatically updated in accordance with the corresponding local object.

The export method returns the value true wrapped in a Boolean if the export is successful, and the value false wrapped in a Boolean if there already is an experimental descriptor in the database that has the same ID and type as the one that was given as argument.

Below is a code snippet that illustrates how to export a Document and an Exportable to a database:

Connection connection;
DatabaseAdapter adapter;
Document document;
Exportable exportable;
				
// initializes all the variables except 'adapter'
...
				
try {
	adapter = new DatabaseAdapter(connection);

	adapter.export(document);
	adapter.export(exportable);
} catch (InPUTException e) {
	// handles the exception
	...
}

Import

To import an experimental descriptor from an SQL database, simply create a DatabaseAdapter and call one of its import methods. That way, both the descriptor itself and all its auxiliary objects are imported. DatabaseAdapter has four single-parameter methods called importDesign, importDesignSpace, importExperiment and importInPUT, respectively, which import and return an IDesign, IDesignSpace, IExperiment and IInPUT, respectively. Each of them takes as argument the ID of the descriptor that shall be imported, and each of them has a corresponding two-parameter method with the same name and return type that takes the same argument along with the ID of the framework that contains the code mappings that shall be used to code-map the imported descriptor. If the specified descriptor requires code mappings and no framework is specified, only frameworkless code mappings are included in the import. If the specified descriptor or any required code mapping is not in the database, null is returned.

Below is a code snippet that illustrates how to import IDesign, IDesignSpace, IExperiment, and IInPUT objects from a database:

Connection connection;
DatabaseAdapter adapter;
IDesign design1, design2;
IDesignSpace space1, space2;
IExperiment experiment1, experiment2;
IInPUT inPUT1, inPUT2;
String	designID,
		experimentID,
		frameworkID,
		inPUTid,
		spaceID;
				
// initializes 'connection' and the string variables
...
	
try {
	adapter = new DatabaseAdapter(connection);
			
	design1 = adapter.importDesign(designID);
	design2 = adapter.importDesign(designID, frameworkID);
	
	space1 = adapter.importDesignSpace(spaceID);
	space2 = adapter.importDesignSpace(spaceID, frameworkID);

	experiment1 = adapter.importExperiment(experimentID);
	experiment2 = adapter.importExperiment(experimentID, frameworkID);

	inPUT1 = adapter.importInPUT(inPUTid);
	inPUT2 = adapter.importInPUT(inPUTid, frameworkID);
} catch (InPUTException e) {
	// handles the exception
	...
}

Updating

To update an experimental descriptor that is stored in an SQL database, simply create a DatabaseAdapter and call its update method with an updated Document or Exportable representation of the descriptor as argument. That way, both the descriptor itself and all its auxiliary objects are updated in the designated database. The update method returns the value true if the update is successful, and the value false if there is no descriptor in the database that has the same ID and type as the one that was given as argument.

Below is a code snippet that illustrates how to update database-stored descriptors:

Connection connection;
DatabaseAdapter adapter;
Document document;
Exportable exportable;
						
// initializes all the variables except 'adapter'
...
						
try {
	adapter = new DatabaseAdapter(connection);
					
	adapter.update(document);
	adapter.update(exportable);
} catch (InPUTException e) {
	// handles the exception
	...
}

Deletion

To delete an experimental descriptor from an SQL database, simply create a DatabaseAdapter and call its delete method with a Document or Exportable representation of the descriptor as argument. That way, both the descriptor itself and all entities in the database that are dependent on it are deleted. The delete method returns the value true if the deletion was successful, and the value false if there was no descriptor in the database that had the same ID and type as the one that was given as argument.

Below is a code snippet that illustrates how to delete descriptors from a database:

Connection connection;
DatabaseAdapter adapter;
Document document;
Exportable exportable;
						
// initializes all the variables except 'adapter'
...
						
try {
	adapter = new DatabaseAdapter(connection);
					
	adapter.delete(document);
	adapter.delete(exportable);
} catch (InPUTException e) {
	// handles the exception
	...
}

Use cases

Let's say that this design space descriptor and a number of design descriptors that implement it, of which one is this descriptor, and a number of code mapping descriptors that code-maps it, of which one is this descriptor, has been exported to a PostgreSQL database. Then, you could for example query about the value of the subparameter “firstValue” of the parameter “task” in the linked design descriptor with the following SQL statement:

SELECT
	(xpath( 
		'/i:Design/i:SValue[@id="task"]/i:NValue[@id="firstValue"]/@value', 
		content, 
		ARRAY[ARRAY['i', 'http://TheInPUT.org/Design']]))[1]
	AS task_firstValue
FROM input.design 
WHERE id = 'investigation1';

You could also query about which of the design descriptors has the highest value of the parameter “amountTasks” and what that value is:

SELECT * FROM
	(SELECT id,
		CAST (XMLSERIALIZE (CONTENT (xpath( 
			'/i:Design/i:NValue[@id="amountTasks"]/@value',  
			content,  
			ARRAY[ARRAY['i', 'http://TheInPUT.org/Design']]))[1]
		AS varchar) AS numeric) AS amountTasks
	FROM input.design)
	AS pool_investigation_design
WHERE amountTasks IS NOT NULL
ORDER BY amountTasks DESC
LIMIT 1;

Moreover, you could query about the different code mappings of the parameter “task” in the linked design space descriptor:

SELECT id,
	(xpath(
		'/i:CodeMappings/i:Mapping[@id="task"]/@type',
		content,
		ARRAY[ARRAY['i', 'http://TheInPUT.org/CodeMappings']]))[1]
	AS "task_type"
FROM input.mappings
WHERE design_space = 'poolInvestigation';

In addition, you could update specific XML nodes of specific descriptors. For example, you could update the value of the parameter “executions” in the linked design descriptor to “4”:

UPDATE input.design
SET content = input.update_xml(
	'/i:Design/i:NValue[@id="executions"]/@value',
	content,
	ARRAY[ARRAY['i', 'http://TheInPUT.org/Design']],
	'4')
WHERE id = 'investigation1';

In short, virtually anything can be queried about database-stored experimental descriptors and any of their XML nodes can be individually updated.

Information about XPath and the SQL dialect of PostgreSQL can be found here and here, respectively, and the different parts of the InPUT SQL schema can be found in the SQL files in the package se.miun.itm.input.util.sql in the InPUTj SQL jar file.