This is the 2nd article of a Build Java Module for Mango series. You can check all the articles by clicking here
In this series, we are going to build a custom module for Mango to store energy metering devices.
The device table has:
- id
- xid
- name
- protocol
- make
- model
- data
- readPermission (it will define who can read the device)
- editPermission (it will define who can edit the device)
First, we create a classes directory, where we store the translation files. For now, we just create a i18n.properties file inside this directory, with the next content:
energyMetering.name=Energy Metering
energyMetering.description=Energy Metering
energyMetering.header.device=Device
We also need to create a module.properties file with the next content:
name=${project.name}
version=${project.version}
coreVersion=${coreApiVersion}
description=${project.description}
descriptionKey=energyMetering.description
dependencies=mangoApi:${coreApiVersion}
vendor=${project.organization.name}
vendorUrl=${project.organization.url}
This file will tell Mango more information about the modules, and what dependencies it needs.
Next, we create the .sql files to define database structure of the module. We create these files inside /resources/com/infiniteautomation/energyMetering directory:
- To create the tables we use
createTables-H2.sql:
CREATE TABLE energyMeteringDevices (
id int NOT NULL auto_increment,
xid VARCHAR(100) NOT NULL,
name VARCHAR(255) NOT NULL,
protocol VARCHAR(255) NOT NULL,
make VARCHAR(255) NOT NULL,
model VARCHAR(255) NOT NULL,
data longtext,
readPermissionId INT NOT NULL,
editPermissionId INT NOT NULL,
PRIMARY KEY (id)
) ;
ALTER TABLE energyMeteringDevices ADD CONSTRAINT energyMeteringDevicesUn1 UNIQUE (xid);
ALTER TABLE energyMeteringDevices ADD CONSTRAINT energyMeteringDevicesFk1 FOREIGN KEY (readPermissionId) REFERENCES permissions(id) ON DELETE RESTRICT;
ALTER TABLE energyMeteringDevices ADD CONSTRAINT energyMeteringDevicesFk2 FOREIGN KEY (editPermissionId) REFERENCES permissions(id) ON DELETE RESTRICT;
And createTables-MYSQL.sql :
CREATE TABLE energyMeteringDevices (
id int NOT NULL auto_increment,
xid VARCHAR(100) NOT NULL,
name VARCHAR(255) NOT NULL,
protocol VARCHAR(255) NOT NULL,
make VARCHAR(255) NOT NULL,
model VARCHAR(255) NOT NULL,
data JSON,
readPermissionId INT NOT NULL,
editPermissionId INT NOT NULL,
PRIMARY KEY (id)
) ;
ALTER TABLE energyMeteringDevices ADD CONSTRAINT energyMeteringDevicesUn1 UNIQUE (xid);
ALTER TABLE energyMeteringDevices ADD CONSTRAINT energyMeteringDevicesFk1 FOREIGN KEY (readPermissionId) REFERENCES permissions(id) ON DELETE RESTRICT;
ALTER TABLE energyMeteringDevices ADD CONSTRAINT energyMeteringDevicesFk2 FOREIGN KEY (editPermissionId) REFERENCES permissions(id) ON DELETE RESTRICT;
- To delete the tables we use
uninstall.sql:
DROP TABLE energyMeteringDevices;
Then, we need to create our DeviceTableDefinition.java class inside /src/mango/spring/dao directory with the next content:
package com.infiniteautomation.mango.spring.dao;
import com.infiniteautomation.mango.spring.db.AbstractTableDefinition;
import org.jooq.Field;
import org.jooq.Record;
import org.jooq.Table;
import org.jooq.impl.DSL;
import org.jooq.impl.SQLDataType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
public class DevicesTableDefinition extends AbstractTableDefinition {
public static final String TABLE_NAME = "energyMeteringDevices";
public static final String TABLE_ALIAS_NAME = "emd";
public static final Table<Record> TABLE = DSL.table(TABLE_NAME);
public static final Field<Integer> ID = DSL.field(
TABLE.getQualifiedName().append("id"),
SQLDataType.INTEGER.nullable(false)
);
public static final Field<String> NAME = DSL.field(
TABLE.getQualifiedName().append("name"),
SQLDataType.CHAR(255).nullable(false)
);
public static final Field<String> PROTOCOL = DSL.field(
TABLE.getQualifiedName().append("protocol"),
SQLDataType.CHAR(255).nullable(false)
);
public static final Field<String> MAKE = DSL.field(
TABLE.getQualifiedName().append("make"),
SQLDataType.CHAR(255).nullable(false)
);
public static final Field<String> MODEL = DSL.field(
TABLE.getQualifiedName().append("model"),
SQLDataType.CHAR(255).nullable(false)
);
public static final Field<String> DATA = DSL.field(
DSL.name("data"),
SQLDataType.CLOB.nullable(true)
);
public static final Field<Integer> READ_PERMISSION = DSL.field(
TABLE.getQualifiedName().append("readPermissionId"),
SQLDataType.INTEGER.nullable(false)
);
public static final Field<Integer> EDIT_PERMISSION = DSL.field(
TABLE.getQualifiedName().append("editPermissionId"),
SQLDataType.INTEGER.nullable(false)
);
public static final Field<Integer> READ_PERMISSION_ALIAS = DSL.field(
DSL.name(TABLE_ALIAS_NAME).append("readPermissionId"),
SQLDataType.INTEGER.nullable(false)
);
public static final Field<Integer> EDIT_PERMISSION_ALIAS = DSL.field(
DSL.name(TABLE_ALIAS_NAME).append("editPermissionId"),
SQLDataType.INTEGER.nullable(false)
);
@Autowired
public DevicesTableDefinition() {
super(DSL.table(TABLE_NAME), DSL.name(TABLE_ALIAS_NAME));
}
@Override
protected void addFields(List<Field<?>> fields) {
super.addFields(fields);
fields.add(PROTOCOL);
fields.add(MAKE);
fields.add(MODEL);
fields.add(DATA);
fields.add(READ_PERMISSION);
fields.add(EDIT_PERMISSION);
}
}
We create READ_PERMISSION_ALIAS and EDIT_PERMISSION_ALIAS because, the will help us when we create a join to get the devices that the user can see. We will talk more about this later.
Finally, we define an EnergyMonitoringSchemaDefinition.java class with the next content:
package com.infiniteautomation.energyMetering;
import com.infiniteautomation.mango.spring.dao.DevicesTableDefinition;
import com.serotonin.m2m2.module.DatabaseSchemaDefinition;
import java.util.List;
public class EnergyMonitoringSchemaDefinition extends DatabaseSchemaDefinition {
@Override
public String getNewInstallationCheckTableName() {
return DevicesTableDefinition.TABLE_NAME;
}
@Override
public void addConversionTableNames(List<String> tableNames) {
tableNames.add(DevicesTableDefinition.TABLE_NAME);
}
@Override
public String getUpgradePackage() {
return "com.infiniteautomation.energyMetering.upgrade";
}
@Override
public int getDatabaseSchemaVersion() {
return 1;
}
}
A database schema definition allows a module to create an manage database tables and other objects as necessary to perform its functionality
Once you build the module mvn install -Pinstall-module, you will get a EnergyMetering-4.0.0-SNAPSHOT.zip file with the module’s code, which you can install in Mango.
We can test really quick if the module was installed well. You can go to the SQL Console inside Mango (it can be hidden, you need to enable the link on Edit menu section, inside Administration) and run the next command:
SELECT * FROM ENERGYMETERINGDEVICES
If everything went fine, you will see an empty table with the fields that we defined.