Skip to main content

Module Configuration

This is the 2nd article of the Build Java Module for Mango series. See the full series in the Module Development Overview.

In this series, we are building a custom module for Mango to store energy metering devices. The device table has the following columns:

  • id
  • xid
  • name
  • protocol
  • make
  • model
  • data
  • readPermission (defines who can read the device)
  • editPermission (defines who can edit the device)

Translation Files​

First, create a classes directory where you store the translation files. Create an i18n.properties file inside this directory with the following content:

energyMetering.name=Energy Metering
energyMetering.description=Energy Metering

energyMetering.header.device=Device

Module Properties​

Create a module.properties file with the following 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 tells Mango information about the module and what dependencies it needs.

SQL Table Definitions​

Next, create the .sql files to define the database structure of the module. Create these files inside the /resources/com/infiniteautomation/energyMetering directory.

H2 Database​

To create the tables, 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;

MySQL Database​

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;

Uninstall Script​

To delete the tables, use uninstall.sql:

DROP TABLE energyMeteringDevices;

Table Definition Class​

Create a DeviceTableDefinition.java class inside the /src/mango/spring/dao directory. This class defines the table columns using jOOQ, which Mango uses for type-safe SQL queries:

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);
    }
}

The READ_PERMISSION_ALIAS and EDIT_PERMISSION_ALIAS fields are needed when creating joins to filter devices by user permissions. This is covered in more detail in the Creating Your First Endpoint article.

Database Schema Definition​

Define an EnergyMonitoringSchemaDefinition.java class that tells Mango how to manage the module's database tables:

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 and manage database tables and other objects as necessary to perform its functionality.

Build and Verify​

Build the module with:

mvn install -Pinstall-module

This produces a EnergyMetering-4.0.0-SNAPSHOT.zip file that is installed into your Mango instance.

To verify the module installed correctly, go to the SQL Console inside Mango (it may be hidden -- you can enable the link in the Edit menu section under Administration) and run:

SELECT * FROM ENERGYMETERINGDEVICES

If everything worked, you will see an empty table with the fields that you defined.

Continue to Creating Your First Endpoint to build the REST API layer.