Understanding Custom Commands

You can extend BroadSQL by creating your own commands which will be available in BroadSQL, like other regular BroadSQL commands.
To do this, create one Java class per command and produce a JAR file that contains all your commands.
You can deploy several jar files in your BroadSQL instance.

Deploying your custom commands

Your custom commands must be included in one or several JAR file.

• Simply copy these JAR files in the extensions folder of your BroadSQL.
• Make sure that the extension folder is explicitely defined in the INI file.
• Restart BroadSQL and that’s it, your commands are ready to be used.
  Note: Do NOT install third party JAR files from untrusted or unverified sources.

Parameters in the INI file

In the INI file, specify the location of your custom extensions folder by using the CustomExtensionsFolder parameter.
For example:

CustomExtensionsFolder=C:\\broadsql\\extensions

Notes:

• You can add several JAR files containing commands.
• The jar files must be located at the root of the extension folder, sub folders are not included.

Creating custom commands

Create a new Java project, and make sure to use the file lib/broadsql.jar from your local BroadSQL instance.
For each command, create a new Java class that will inherit the Command class.

Anatomy of a custom command

The following is a custom command class. It inherits the abstract Command class and must implement the following methods:

• getKeywords() returns the list of keywords for these commands. Keywords are the command name and its aliases.
• isHidden() determines whether the command should be hidden or not. Hidden commands are not listed in the HELP command.
• execute(String query) is what the command does actually
• getDescription() used in the HELP command to describe the features of the command
• getArguments() used in the HELP command to list the arguments of the command
• getExamples() used in the HELP command to show an usage example

package com.broadsql.samples;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.projectsontracks.controller.config.SpringPropertiesConfig;
import com.projectsontracks.controller.errors.BroadSQLException;
import com.projectsontracks.controller.shell.commands.Command;
import com.projectsontracks.controller.shell.commands.CommandUtils;
import com.projectsontracks.controller.shell.commands.CommandInterpreter;
import com.projectsontracks.dao.model.DatabaseDefinition;
import com.projectsontracks.controller.errors.BroadSQLErrorMessages;

/**
 * 
 * Display number of rows in a table
 * 
 */
public class CommandCnt2 extends Command {
	
	private static final Logger log = LoggerFactory.getLogger(CommandCnt2.class);

	static String[] keywords = { "CNT2", "COUNT2", "COUNT ROWS2"};

	@Override
	public String[] getKeywords() {
		return keywords;
	}

	@Override
	public boolean isHidden() {
		return false;
	}

	@Override
	public void execute(String query) throws BroadSQLException {

		String[] args = CommandUtils.getArgumentsFromQuery(query, keywords);

		String tableName = null;
		if (CommandUtils.isValidArgs(args)) {
			tableName = args[0].trim();
			if (StringUtils.isNotBlank(tableName)) {
				// The following executes any supported command, whether BroadSQL or database native
				String qry = CommandUtils.substituteMacros("SELECT COUNT(*) FROM " + tableName);
				getConsoleCommandInterpreter().setQuery(qry);
				getConsoleCommandInterpreter().executeCommand();
			} else {
				console.error(BroadSQLErrorMessages.ERR_GAL_01);
			}
		} else {
			console.error(BroadSQLErrorMessages.ERR_GAL_01);
		}
	}

	@Override
	public String getDescription() {
		return ("Displays result of a SELECT COUNT(*) for a specified table");
	}

	@Override
	public String getArguments() {
		return "<tableName> (mandatory) valid table name";
	}

	@Override
	public String getExamples() {
		return "CNT2 CUSTOMER;";
	}
}

The execute methods

The execute method specifies what the command performs. Its input is the command line entered by the user in the form of a query string.
The query string is the part after the command name. Note that the user can execute the command either by using the command name or one of its aliases.
In the previous example, the following are the same command:

CNT2 CUSTOMER;
COUNT2 CUSTOMER;
COUNT ROWS2 CUSTOMER;

Analyzing the user query

Each command receives a copy of the input entered by the user, inclusing the command name, or aliases, and its arguments.
The CommandUtils class provides a method for retrieving arguments from the query:

String[] args = CommandUtils.getArgumentsFromQuery(query, keywords);

The method isValidArgs makes sure that the arguments list is not null and returns an array of space separated arguments.

if (CommandUtils.isValidArgs(args)) {
	console.log("Number of arguments: " + args.length);
}

Sometimes, the user input query contains macros. For example, the macro <@fileName> retrieves values from a list and converts them into a list (‘a’,‘b’,‘c’, … ).
In that case, you want to convert the macros in SQL using the command substituteMacros of the CommandUtils class.

String tableName = args[0].trim();
String qry = CommandUtils.substituteMacros("SELECT COUNT(*) FROM " + tableName);

Writing/Reading on the console

Simply use one of the following instructions:

• console.info prints a message prefixed by INFO
• console.warn prints a message prefixed by WARNING
• console.error prints a message prefixed by ERROR
• console.print and console.println that will omit the prompt
• console.write and console.writeln that will write with a prompt
  All these instructions also write in the log files as well.
  Eg.:

console.info("This is an INFO");
console.warn("This is a WARNING");
console.error("This is an ERROR");
console.writeln("Prints a line without the prompt");
console.println("Prints a line without the prompt");

To read from the command line, use:

• console.readLine
• console.readPassword
• console.inputField to read from a list of values
  Eg.:

console.writeln("Enter your age: ");
String age = console.readLine();

Executing a BroadSQL or SQL query

You can execute any BroadSQL or native SQL query.
To do this, use the following instructions:

String qry = "SELECT COUNT(*) FROM USERS"; // Whatever BroadSQL or SQL command 
getConsoleCommandInterpreter().setQuery(qry);
getConsoleCommandInterpreter().executeCommand();

Executing complex SQL queries

To support more complex scenarios, BroadSQL provides a SPRING JdbcTemplate.
JdbcTemplate offers several methods for handling results of SQL queries, such as:

• return result as an Integer, a Boolean, String or other classes
• return results as a List of Object (String, Integer, etc.)
• return result as a List of your Object using RowMapper
  The following illustrates the counting of rows using JdbcTemplate:

String qry = "SELECT COUNT(*) FROM CUSTOMER";
Integer cnt5 = sqlDatabase.getJdbcTemplate().queryForObject(qry, Integer.class);
console.println("Found " + cnt5 + " records in table CUSTOMER");

String qry = new CommandInterpreter().substituteMacros("SELECT COUNT(*) FROM " + tableName);
getConsoleCommandInterpreter().setQuery(qry);
getConsoleCommandInterpreter().executeCommand();

Creating an extension file from a java program

Extension files are simply JAR files created from a Java program:

javac -cp ".;C:/broadsql/lib/*" com/broadsql/samples/*.java
jar -cf samplesExtensions.jar com/broadsql/samples/*.class