BaSyx SQL Submodel provider
Contents
SQL sub model provider servlet
The SQL sub model provider provides the contents of a SQL database as Asset Administration Shell sub model. It uses JDBC drivers to access the data base. Therefore, most JDBC compatible data base backends will work with this provider class. The SQL sub model provider supports configuration of sub model properties and operations through its configuration file. For properties, the VAB operations create, update, retrieve, and delete may be configured and mapped to SQL statements. The mapping of sub model operations to SQL statements are configured through the configuration file of the SQL sub model provider as well. The SQL sub model provider is implemented by servlet class org.eclipse.basyx.components.servlets.SQLSubModelProviderServlet
. It accepts the following configuration parameter:
- config: Path to configuration file
The following snippet illustrates an Apache Tomcat configuration for the SQL sub model provider Example_SQLProvider. It configures the location of the configuration file inside the WEB-INF folder and configures the URL pattern that is used to access the sub model to /Testsuite/components/BaSys/1.0/provider/sqlsm/*
<servlet> <servlet-name>Example_SQLProvider</servlet-name> <servlet-class> org.eclipse.basyx.components.servlets.SQLSubModelProviderServlet </servlet-class> <init-param> <param-name>config</param-name> <param-value>/WEB-INF/config/sqlprovider/sampledb.properties</param-value> </init-param> <load-on-startup>5</load-on-startup> </servlet> <servlet-mapping> <servlet-name>Example_SQLProvider</servlet-name> <url-pattern>/Testsuite/components/BaSys/1.0/provider/sqlsm/*</url-pattern> </servlet-mapping>
SQL sub model provider servlet configuration
The main configuration file of the SQL sub model provider contains the sub model ID, the database configuration and access credentials, the SQL driver configuration, and the configuration of the exported sub model. The following keys are supported for the configuration file:
basyx.submodelID ID of sub model basyx.sql.dbuser Database user name that will be used by the SQL sub model provider basyx.sql.dbpass Database password basyx.sql.dburl URL to the data base basyx.sql.driver Java class that implements the JDBC driver basyx.sql.prefix Prefix for the JDBC driver basyx.sql.properties Sub model properties that are to be exported by the SQL sub model provider basyx.sql.operations -
The SQL sub model provider understands the following configuration values for each configured property:
<propertyName>.type Type of the property <propertyName>.semanticsInternal Identifier that identifies the semantics of the property. <propertyName>.category Property category value that describes for example which elements are to be expected or not <propertyName>.description Human readable property description <propertyName>.qualifier Namespace qualifier of the property <propertyName>.get SQL statement that implements the property get/retrieve operation <propertyName>.get.type Return type of the SQL statement <propertyName>.get.result Provider operation that converts the SQL result into a conforming VAB/AAS data type <propertyName>.delete SQL statement that implements the property delete operation <propertyName>.create SQL statement that implements the property create operation <propertyName>.set SQL statement that implements the property set/update operation
Most SQL operations require parameter. The SQL sub model provider supports the definition of named SQL parameter. For example, a create operation could be defined as following:
sensorNames.create = "INSERT INTO vibrations.sensors (sensorname, sensorid) VALUES ('$sensorname', '$sensorid')"
Values $sensorname and $sensorid must be provided as JSON serialized map:
Map<String, Object> newTableLine = new HashMap<>(); newTableLine.put("sensorname", "VS_0003"); newTableLine.put("sensorid", "033542");
The BaSyx SDK supports the conforming serialization of the Map as JSON object and realizes the web service call via HTTP REST. However, all of this may also be implemented manually if preferred:
// - Insert line into table connSubModel.createElement("/aas/submodels/SQLTestSubmodel/properties/sensorNames/value", newTableLine);
Configuration example
The following example file illustrates the configuration of the SQL sub model provider:
# ############################################################## # SQL Sub model provider configuration file # ############################################################## # ############################################################## # Sub model provider configuration basyx.submodelID = SQLTestSubmodel # ############################################################## # Database configuration basyx.sql.dbuser = postgres basyx.sql.dbpass = admin basyx.sql.dburl = //localhost/basyx-sample-vibrations? # ############################################################## # SQL driver configuration basyx.sql.driver = org.postgresql.Driver basyx.sql.prefix = jdbc:postgresql: # ############################################################## # SQL sub model configuration basyx.sql.properties = sensorNames basyx.sql.operations = sensorIDForName addSensorID # ############################################################## # Properties # sensorNames property sensorNames.type = PropertySingleValued sensorNames.semanticsInternal = basys.semantics.internal.sensorNames sensorNames.category = sensorNamesProperty sensorNames.description = Sensor names property description sensorNames.qualifier = basys.test.sensorNamesScope sensorNames.get = "SELECT * FROM vibrations.sensors" sensorNames.get.type = STRING[] sensorNames.get.result = "stringArray(sensorname:String)" sensorNames.delete = "DELETE FROM vibrations.sensors WHERE sensorid='$sensorid'" sensorNames.create = "INSERT INTO vibrations.sensors (sensorname, sensorid) VALUES ('$sensorname', '$sensorid')" sensorNames.set = "UPDATE vibrations.sensors SET sensorname='$sensorname' WHERE sensorid='$sensorid'" # ############################################################## # Operations # sensorIDForName operation sensorIDForName.operation.parameter = 1 sensorIDForName.operation.kind = query sensorIDForName.operation.type = OBJECT[] sensorIDForName = "SELECT * FROM vibrations.sensors WHERE sensorname='$1'" sensorIDForName.operation.result = "mapArray(sensorid:int, sensorname:String)" # addSensorID operation addSensorID.operation.parameter = 2 addSensorID.operation.kind = update addSensorID.operation.type = void addSensorID = "INSERT INTO vibrations.sensors ($1) VALUES ($2)" addSensorID.operation.result =
SQL sub model provider Java example
The following code snippets illustrate the SQL sub model provider use from the JAVA SDK.
- Connect to SQL sub model provider via directory service. The SQL sub model provider is available with ID SQLTestSubmodel.
// Connect to sub model "CfgFileTestAAS" VABElementProxy connSubModel = this.connManager.connectToVABElement("SQLTestSubmodel");
- Read value of sensorNames property that contains all sensor names
// Get property value Object value1 = connSubModel.readElementValue("/aas/submodels/SQLTestSubmodel/properties/sensorNames/value");
- Read meta data “category” of sensorNames property
// Get property meta data value Object value5 = connSubModel.readElementValue("/aas/submodels/SQLTestSubmodel/properties/sensorNames/category");
- Read sensorNames property that contains all meta data. The property value is provided in the value attribute.
Object value1a = connSubModel.readElementValue("/aas/submodels/SQLTestSubmodel/properties/sensorNames");
- The call returns the following JSON serialized data structure:
{ id_semantics = {idType=2, id=basys.semantics.internal.sensorNames}, parent = null, idShort = sensorNames, kind = 1, qualifier = [basys.test.sensorNamesScope], description = Sensor names property description, category = sensorNamesProperty, hasFullSemanticDescription = [], value = [VS_0001, VS_0002] }
- Invoke operation sensorIDForName to resolve sensor ID of sensor named VS_0001.
// Get property value (1) Object value1 = connSubModel.invoke("/aas/submodels/SQLTestSubmodel/operations/sensorIDForName", "VS_0001"); System.out.println("Value:"+value1);
- Invoke operation that adds a sensor with name VS_0005 and id 321.
connSubModel.invoke("/aas/submodels/SQLTestSubmodel/operations/addSensorID", "sensorname, sensorid", "'VS_0005', '321'");
- Create a new sensor using the VAB create primitive <<<cf: VAB primitives>>> on the sensorNames property
// Create a new property // - HashMap that contains new table line Map<String, Object> newTableLine = new HashMap<>(); newTableLine.put("sensorname", "VS_0003"); newTableLine.put("sensorid", "033542"); // - Insert line into table connSubModel.createElement("/aas/submodels/SQLTestSubmodel/properties/sensorNames/value", newTableLine);
- Delete sensor with ID VS_0005.
// Delete property 'VS_0005' // - Collection that contains call values Collection<String> callValues4 = new LinkedList<>(); callValues4.add("VS_0005"); // - Delete sensor from table connSubModel.deleteElement("/aas/submodels/SQLTestSubmodel/properties/sensorNames", callValues4);
- Update sensor with ID 033542 to name VS_0004.
// Update property value // - Here this adds a new value into the table // - Collection that contains call values Map<String, Object> updatedTableLine = new HashMap<>(); updatedTableLine.put("sensorname", "VS_0004"); updatedTableLine.put("sensorid", "033542"); // - Update table line connSubModel.updateElementValue("/aas/submodels/SQLTestSubmodel/properties/sensorNames/value", updatedTableLine);