Hit the road Jack, and don't you come back no more….

In my blogpost “Being in command“, about custom commands I said that I couldn’t find a way to unregister a registered custom command, other than restarting SQLcl.
That is still true. I couldn’t.
But Philipp Salvisberg could. He was able to find a way to do just that.

That means that with his hard work as input, we are now able to Unregister a previously registered custom command.
That makes it possible to register the command in an edited new version without the need to restart SQLcl.

So, like Jack in the famous song, let’s try to kick a registered custom command out of our SQLcl house.

Go straight to

Introduction
The command we will register here
The problem and its solution
– Nameless listeners
– Function for unregistering
– Calling the unregister function
The final script
The proof that it works
What’s next?

Introduction

First of all: this post will deal with the code needed to unregister a custom command.
In my humble opinion just, like registering a command, it takes too much code and effort to be considered user friendly.
In my next blog post I will introduce a library which enables you to both register and unregister commands with just two simple commands (one of which is loading the library).

So, if you don’t care about the how and just want easy registration/unregistration, go straight to the next post.

Also, this disclaimer: What you will see here is not invented by me. I can not and will not take credit for that.
Philipp Salvisberg did the heavy lifting. All I did was change some minor details, and of course explain what it does.
The original code from Philipp is in his format.js script for formatting code according to the Trivadis plsql-formatting guidelines.
It’s worth having a look. Lot’s of interesting tricks in that script, and: the Trivadis code formatting guidelines are pretty good. Not perfect, because only my code formatting is perfect of course, but it comes close ?

Go Back to top

The command we will register here

This post is not about showing you how to build useful scripts for SQLcl. It’s about showing how to unregister them once you’ve registered them as a custom command.
Therefore I won’t distract you with an unnecessarily complex script.
The script will just go through the arguments and print them to screen.

"use strict";

// Report each argument value
function mainCode (argList) {
  for (var i=0; i < argList.length; i++) {
    ctx.write ("Argument " + i + " is: " + argList[i] + "\n");
  }
}

// Execute the mainCode function, passing it the args pseudo array
mainCode (args);

"use strict";

// Report each argument value

function mainCode (argList) {

for (var i=0; i < argList.length; i++) {

ctx.write ("Argument " + i + " is: " + argList[i] + "\n");

}

// Execute the mainCode function, passing it the args pseudo array

mainCode (args);

We will register this code as command “ListParams“.

Go Back to top

The problem and its solution

Nameless listeners

Especially while developing a script that you want available as a custom command, you will often make changes and register the new script again.
As we’ve seen in the previous post this will not work, because the previous registration is still in place, and will handle the command before the new one gets a chance to do so.

So, you will want to unregister the previously registered command and then register the new script as this command.
The main problem when you want to do this is that by default we can’t identify the listener that is created to handle the command. After all, we didn’t assign a name or other identifier to the listener.

The trick that we’ll use to enable this identification is to override the toString method of the listener, such that it returns the name of the command it handles.
Simple, but very effective.

Note: in below code the function that splits the command line in individual arguments and returns them as an array is different than the one in the previous post.
This version is based on what I saw in Philipp’s code, and it’s a lot more elegant, a lot shorter and a lot more maintainable than what I did before.

Based on what you could read in the previous post the command here would be registered like this

"use strict";

var commandName = "ListParams";

var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener");
var jPattern        = Java.type("java.util.regex.Pattern");


// Report each argument value
function mainCode (argList) {
  for (var i=0; i < argList.length; i++) {
    ctx.write ("Argument " + i + " is: " + argList[i] + "\n");
  }
}

//########################################### Custom Command Registration ####
// Function takes the commandline and splits it into individual arguments.
function commandArguments (cmdLine) {
  var pattern  = jPattern.compile('("([^"]*)")|([^ ]+)');
  var matcher  = pattern.matcher(cmdLine.trim());

  var argsArray = [];
  while (matcher.find()) {
      argsArray.push(matcher.group(3) != null ? matcher.group(3) : matcher.group(2));
  }
  return argsArray;
}

// Registration functionality
function registerCommand () {
  var custom_handle   = function (conn,ctx,cmd) {
                          var stmntArgs = commandArguments (cmd.getSql());
                          if (stmntArgs[0].equalsIgnoreCase(commandName)) {
                            mainCode (stmntArgs);
                            return true;
                          }
                          return false;
                        }
  var custom_begin    = function (conn,ctx,cmd) {}
  var custom_end      = function (conn,ctx,cmd) {}
  var customToString  = function() {
      return commandName;
  }
  
  var myCustomCommand = Java.extend(CommandListener, {
                                      handleEvent: custom_handle ,
                                      beginEvent : custom_begin  ,
                                      endEvent   : custom_end    ,
                                      toString   : customToString
                                    }
                                   );
  
  // Unregister the command if it has been registered before, and then register it
  CommandRegistry.addForAllStmtsListener(myCustomCommand.class);
}

registerCommand ();

"use strict";

var commandName = "ListParams";

var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");

var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener");

var jPattern = Java.type("java.util.regex.Pattern");

// Report each argument value

function mainCode (argList) {

for (var i=0; i < argList.length; i++) {

ctx.write ("Argument " + i + " is: " + argList[i] + "\n");

}

//########################################### Custom Command Registration ####

// Function takes the commandline and splits it into individual arguments.

function commandArguments (cmdLine) {

var pattern = jPattern.compile('("([^"]*)")|([^ ]+)');

var matcher = pattern.matcher(cmdLine.trim());

var argsArray = [];

while (matcher.find()) {

argsArray.push(matcher.group(3) != null ? matcher.group(3) : matcher.group(2));

}

return argsArray;

}

// Registration functionality

function registerCommand () {

var custom_handle = function (conn,ctx,cmd) {

var stmntArgs = commandArguments (cmd.getSql());

if (stmntArgs[0].equalsIgnoreCase(commandName)) {

mainCode (stmntArgs);

return true;

}

return false;

}

var custom_begin = function (conn,ctx,cmd) {}

var custom_end = function (conn,ctx,cmd) {}

var customToString = function() {

return commandName;

}

var myCustomCommand = Java.extend(CommandListener, {

handleEvent: custom_handle ,

beginEvent : custom_begin ,

endEvent : custom_end ,

toString : customToString

}

);

// Unregister the command if it has been registered before, and then register it

CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

}

registerCommand ();

What we need to change here to be able to later identify the listener we are now registering, is to override the toString method.
For this we declare another function expression that will return the command name.

var customToString  = function() {
    return commandName;
}

var customToString = function() {

return commandName;

}

Then the customToString is also added to the listener in the “extend”.

var myCustomCommand = Java.extend(CommandListener, {
                                    handleEvent: custom_handle ,
                                    beginEvent : custom_begin  ,
                                    endEvent   : custom_end    ,
                                    toString   : customToString
                                  }
                                 );

var myCustomCommand = Java.extend(CommandListener, {

handleEvent: custom_handle ,

beginEvent : custom_begin ,

endEvent : custom_end ,

toString : customToString

}

);

This means that now, our listener can be identified by executing its toString method.
If it returns the name of our command then the listener must be the one for our command. Right?

Go Back to top

Function for unregistering

The command has now been registered in such a way that we can identify its listener. Great. But how do we go about getting rid of it?
For that we will introduce this function…

function unregisterCommand () {
  var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
  var Collectors      = Java.type("java.util.stream.Collectors")                          ;
  var SQLCommand      = Java.type("oracle.dbtools.raptor.newscriptrunner.SQLCommand")     ;

  var listeners    = CommandRegistry.getListeners(ctx.getBaseConnection(), ctx).get(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE);
  
  CommandRegistry.removeListener(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE);

  CommandRegistry.clearCaches(null, ctx);
  CommandRegistry.clearCaches(ctx.getBaseConnection(), ctx);

  var remainingListeners = CommandRegistry.getListeners(ctx.getBaseConnection(), ctx).get(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE).stream().map(function(l) l.getClass()).collect(Collectors.toSet());

  for (var i in listeners) {
      if (!listeners.get(i).toString().equalsIgnoreCase(commandName) && !remainingListeners.contains(listeners.get(i).getClass())) {
          CommandRegistry.addForAllStmtsListener(listeners.get(i).getClass());
      }
  }
 
}

function unregisterCommand () {

var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");

var Collectors = Java.type("java.util.stream.Collectors") ;

var SQLCommand = Java.type("oracle.dbtools.raptor.newscriptrunner.SQLCommand") ;

var listeners = CommandRegistry.getListeners(ctx.getBaseConnection(), ctx).get(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE);

CommandRegistry.removeListener(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE);

CommandRegistry.clearCaches(null, ctx);

CommandRegistry.clearCaches(ctx.getBaseConnection(), ctx);

var remainingListeners = CommandRegistry.getListeners(ctx.getBaseConnection(), ctx).get(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE).stream().map(function(l) l.getClass()).collect(Collectors.toSet());

for (var i in listeners) {

if (!listeners.get(i).toString().equalsIgnoreCase(commandName) && !remainingListeners.contains(listeners.get(i).getClass())) {

CommandRegistry.addForAllStmtsListener(listeners.get(i).getClass());

}

The steps involved are:

Retrieve all the listeners (Line 6)
This gets all the listener classes that have been registered with CommandRegistry.addForAllStmtsListener and stores them in an array
.
Remove the listeners (Line 8)
Here an attempt is made to remove all the listener classes that have been registered with CommandRegistry.addForAllStmtsListener.
I speak of “attempt” because some listeners for non-custom things may not be removeable. This command will just remove those it can remove.
.
Clear the cache (Line 10 – 11)
After removing the listeners, the commands can still be used/executed, because they are still available in the cache, so we need to clear that.
It appears that there is a cache for each connection you’ve had in the current SQLcl session plus a ‘general’ cache for non-connection sessions (e.g. starting SQLcl with nolog, or losing your connection when reconnecting with wrong credentials).
Line 11 clears the cache for the current connection. It clears the ‘general’ cache if you currently have no connection.
Line 10 clears the ‘general’ cache. I have added the clearing of this general cache as an extra safeguard to make sure that after you unregistered a command and then at some point lose your connection, you don’t suddenly have an environment
here the (old version of ) the command is still there.
Note however, that the caches of other connections you may have opened during this SQLcl session are not cleared here, so going back to such a connection would indeed revive the (old version of ) the command.
Solving this would make the code a lot more complex, while solving a minor problem, since this unregistering is usually mainly done only during development of a script/command and in that case usually you won’t be switching between loads of connections.
Still: I think there should be a way to easily clear all those caches at once.
.
Again retrieve a list of the listeners (Line 13)
Because some listeners might not have been removed in step 2 we need to find out which ones are still in place now so we don’t re-register those.
.
Re-register removed listeners that we are not trying to remove (Line 15-19)
Every listener that was originally there (step 1) is registered again, except in either of these cases:
- It’s in the list of step 4, meaning: It wasn’t removed
- Its toString method returns the command name, meaning: It is the listener for the command we are trying to unregister
  .

Go Back to top

Calling the unregister function

Now that we have a function that will unregister our command, we can execute it just before registering the command.
This will make sure that if the command has been registered before in the same or a different version it will be removed before it is registered again.
So calling the unregisterCommand function needs to be done just before executing the addForAllStmtsListener.

unregisterCommand();
CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

1 2	unregisterCommand(); CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

Go Back to top

The final script

Getting everything we’ve done so far together in the script we started out with results in

"use strict";

var commandName = "ListParams";

// Registration functionality
var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener");
var jPattern        = Java.type("java.util.regex.Pattern")                              ;
var Collectors      = Java.type("java.util.stream.Collectors")                          ;
var SQLCommand      = Java.type("oracle.dbtools.raptor.newscriptrunner.SQLCommand")     ;


// Report each argument value
function mainCode (argList) {
  for (var i=0; i < argList.length; i++) {
    ctx.write ("Argument " + i + " is: " + argList[i] + "\n");
  }
}

//########################################### Custom Command Registration ####
// Function takes the commandline and splits it into individual arguments.
function commandArguments (cmdLine) {
  var pattern  = jPattern.compile('("([^"]*)")|([^ ]+)');
  var matcher  = pattern.matcher(cmdLine.trim());

  var argsArray = [];
  while (matcher.find()) {
      argsArray.push(matcher.group(3) != null ? matcher.group(3) : matcher.group(2));
  }
  return argsArray;
}


function unregisterCommand () {
  // Get the current list of listeners
  var listeners    = CommandRegistry.getListeners(ctx.getBaseConnection(), ctx).get(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE);
  
  // remove all commands registered with CommandRegistry.addForAllStmtsListener
  CommandRegistry.removeListener(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE);

  // clear the general (no-connection) cache and the connection specific cache
  CommandRegistry.clearCaches(null, ctx);
  CommandRegistry.clearCaches(ctx.getBaseConnection(), ctx);

  // get the list of listeners that have not been removed, if any
  var remainingListeners = CommandRegistry.getListeners(ctx.getBaseConnection(), ctx).get(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE)
                           .stream().map(function(l) l.getClass()).collect(Collectors.toSet());

  // re-register all commands except for the one we are trying to unregister and any remaining (not removed) listener classes
  for (var i in listeners) {
      if (!listeners.get(i).toString().equalsIgnoreCase(commandName) && !remainingListeners.contains(listeners.get(i).getClass())) {
          // It is NOT the listener for our command, and
          // It is not one of the listener classes that couldn't be removed
          // .... So re-register it
          CommandRegistry.addForAllStmtsListener(listeners.get(i).getClass());
      }
  }
}


function registerCommand () {
  var custom_handle   = function (conn,ctx,cmd) {
                          var stmntArgs = commandArguments (cmd.getSql());
                          if (stmntArgs[0].equalsIgnoreCase(commandName)) {
                            mainCode (stmntArgs);
                            return true;  //exit the function and indicate the command has been handled
                          }
                          return false;  //exit the function and indicate the command has not been handled
                        }
  var custom_begin    = function (conn,ctx,cmd) {}
  var custom_end      = function (conn,ctx,cmd) {}
  var customToString  = function() {
      return commandName;
  }
  
  var myCustomCommand = Java.extend(CommandListener, {
                                      handleEvent: custom_handle ,
                                      beginEvent : custom_begin  ,
                                      endEvent   : custom_end    ,
                                      toString   : customToString
                                    }
                                   );
  
  // Unregister the command if it has been registered before, and then register it
  unregisterCommand();
  CommandRegistry.addForAllStmtsListener(myCustomCommand.class);
}

registerCommand ();

"use strict";

var commandName = "ListParams";

// Registration functionality

var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");

var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener");

var jPattern = Java.type("java.util.regex.Pattern") ;

var Collectors = Java.type("java.util.stream.Collectors") ;

var SQLCommand = Java.type("oracle.dbtools.raptor.newscriptrunner.SQLCommand") ;

// Report each argument value

function mainCode (argList) {

for (var i=0; i < argList.length; i++) {

ctx.write ("Argument " + i + " is: " + argList[i] + "\n");

}

//########################################### Custom Command Registration ####

// Function takes the commandline and splits it into individual arguments.

function commandArguments (cmdLine) {

var pattern = jPattern.compile('("([^"]*)")|([^ ]+)');

var matcher = pattern.matcher(cmdLine.trim());

var argsArray = [];

while (matcher.find()) {

argsArray.push(matcher.group(3) != null ? matcher.group(3) : matcher.group(2));

}

return argsArray;

}

function unregisterCommand () {

// Get the current list of listeners

var listeners = CommandRegistry.getListeners(ctx.getBaseConnection(), ctx).get(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE);

// remove all commands registered with CommandRegistry.addForAllStmtsListener

CommandRegistry.removeListener(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE);

// clear the general (no-connection) cache and the connection specific cache

CommandRegistry.clearCaches(null, ctx);

CommandRegistry.clearCaches(ctx.getBaseConnection(), ctx);

// get the list of listeners that have not been removed, if any

var remainingListeners = CommandRegistry.getListeners(ctx.getBaseConnection(), ctx).get(SQLCommand.StmtSubType.G_S_FORALLSTMTS_STMTSUBTYPE)

.stream().map(function(l) l.getClass()).collect(Collectors.toSet());

// re-register all commands except for the one we are trying to unregister and any remaining (not removed) listener classes

for (var i in listeners) {

if (!listeners.get(i).toString().equalsIgnoreCase(commandName) && !remainingListeners.contains(listeners.get(i).getClass())) {

// It is NOT the listener for our command, and

// It is not one of the listener classes that couldn't be removed

// .... So re-register it

CommandRegistry.addForAllStmtsListener(listeners.get(i).getClass());

}

function registerCommand () {

var custom_handle = function (conn,ctx,cmd) {

var stmntArgs = commandArguments (cmd.getSql());

if (stmntArgs[0].equalsIgnoreCase(commandName)) {

mainCode (stmntArgs);

return true; //exit the function and indicate the command has been handled

}

return false; //exit the function and indicate the command has not been handled

}

var custom_begin = function (conn,ctx,cmd) {}

var custom_end = function (conn,ctx,cmd) {}

var customToString = function() {

return commandName;

}

var myCustomCommand = Java.extend(CommandListener, {

handleEvent: custom_handle ,

beginEvent : custom_begin ,

endEvent : custom_end ,

toString : customToString

}

);

// Unregister the command if it has been registered before, and then register it

unregisterCommand();

CommandRegistry.addForAllStmtsListener(myCustomCommand.class);

}

registerCommand ();

Go Back to top

The proof that it works

Awesome!
Cool code!
Great!

But……..? Does it do what it’s supposed to?

Well, as they say: the proof of the pudding is in the eating.

If I save the code above in a file called ListParams_reg.js and run it with the command line statement

script ListParams_reg.js then I should be able to execute command “ListParams” and it would have to print all arguments from the command line.

… and it does.
But that doesn’t prove anything yet, because we were able to register commands before.
Now, change line 16 from

ctx.write ("Argument " + i + " is: " + argList[i] + "\n");

16	ctx.write ("Argument " + i + " is: " + argList[i] + "\n");

into

ctx.write ("Argument " + i + " is supposed to be: " + argList[i] + "\n");

16	ctx.write ("Argument " + i + " is supposed to be: " + argList[i] + "\n");

then if the script is run again, the output of the command should be changed, reflecting the new code that is executed by the command.

Go Back to top

What’s next?

All this code that is necessary to register and unregister a command.
Constantly copy pasting all of this to the next script, adjusting it for that script and then debugging it again because obviously I made a typo somewhere.
I hate it.
So in the next post we’ll make sure that this code is all in one place and can be called with 2 simple statements.

Go Back to top

Reacties

Hit the road Jack, and don’t you come back no more…. — Geen reacties

Geef een reactie Reactie annuleren

Deze site gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.

HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <q cite=""> <s> <strike> <pre class="" title="" data-url="">

EvROCS

Completing the puzzle

Hit the road Jack, and don’t you come back no more….

Go straight to

Introduction

The command we will register here

The problem and its solution

Nameless listeners

Function for unregistering

Calling the unregister function

The final script

The proof that it works

What’s next?

Reacties

Hit the road Jack, and don’t you come back no more…. — Geen reacties

Geef een reactie Reactie annuleren