Modeler Custom Transformations

This is a follow up to the transformation I mentioned earlier where I mentioned how to write custom transformation in javascript for the design.  This is based on a java's pluggable script which means almost anything can be plugged in instead of javascript.  See here for details on the available languages.

This is the beginning of what I plan on building up over time which is a library of functions for use to make custom script very easily.  This is an example of adding the standard who columns to every table in the model.    

The first two functions are to delete and add columns to a table.

// import actual java classes for use later
importPackage(javax.swing);
// variable to keep a status message for later
var msg="";
/*
  Delete function takes in the table and name of the column to delete
*/

function deleteColumn(table,colName){ 
    columns = table.getElements();
 
 // iterate columns looking for the one to remove
    for (var i = 0; i < columns.length; i++) {
 if ( columns[i].getName().toUpperCase() == colName ){
   columns[i].remove();
   msg += "Deleted from "+ table.getName() + " : " + colName + "\n"; 
        }
  }
}
/*
  checkOrCreate function takes in the table and name of the column to add. This checks for the existence of the column before addin
*/

function checkOrCreate(table,colName,typeName,typeSize){ 
 hasCol = false;
 columns = table.getElements();
 
 for (var i = 0; i < columns.length; i++) {
 column = columns[i];
 if ( column.getName().toUpperCase() == colName ){
   hasCol=true;
 }
 }
  // if the column is not present add it
  if (! hasCol ) {
      newCol = table.createColumn();
      newCol.setName(colName);
      newCol.setUse(1);
   // lookup the logical datatype based on the name
   type = model.getDesign().getLogicalDatatypeSet().getLogTypeByName(typeName);
      newCol.setLogicalDatatype(type);
      if (typeSize != null  ) {
        newCol.setDataTypeParameter("size",typeSize);
      }
      msg += "Added to "+ table.getName() + " : " + colName+ "\n";
 }  
}

// grab all the table in the model as an array
tables = model.getTableSet().toArray();

for (var t = 0; t < tables.length;t++){
 // remove all these from the table
 deleteColumn(tables[t],"CREATED_BY");
 deleteColumn(tables[t],"CREATED_ON");
 deleteColumn(tables[t],"UPDATED_BY");
 deleteColumn(tables[t],"UPDATED_ON");
 
 // add them back with the specified datatypes
 checkOrCreate(tables[t],"CREATED_BY",   "VARCHAR",200);
 checkOrCreate(tables[t],"CREATED_ON",   "DATE");
 checkOrCreate(tables[t],"UPDATED_BY",   "VARCHAR",200);
 checkOrCreate(tables[t],"UPDATED_ON",   "DATE");
}

// notify the user what happened
JOptionPane.showMessageDialog(null, msg);

The end result of running this will be a dialog that shows all the actions performed.

APEX Listener startup/shutdown script

David asked me for a way to keep the listener up and running when a box is rebooted.  There may very well be better ways to do this but this script can be used to start, stop, get status, and tail the log file.  Also it can be dropped in as an init.d script to have the Listener start when the box starts.  Since we don't have access to init.d for David's case, we are just going to put in a cron job to check status and start if not already going.  The start function checks to ensure it's not already running before trying to start it up.  You should be able to use this but just changing the location variables in the top of the file.

The one addition over a normal init.d is the log command I added ( which is very simple).  Just do ./listener.sh log and it will tail the log file.

#!/bin/sh
#
. /etc/rc.d/init.d/functions
NAME="Oracle Application Express Listener"
JAVA="/my/install/path/to/jdk/jre/bin/java"
APEXWAR="/my/install/path/to/apex_listener/apex.war"


OPTIONS="-Xmx1024m -Xms256m  -jar $APEXWAR"

LOGFILE=/tmp/apex_listener.log
PIDFILE=/tmp/apex_listener.pid
start() {
        echo -n "Starting $NAME: "
        if [ -f $PIDFILE ]; then
                PID=`cat $PIDFILE`
                echo APEX Listener already running: $PID
                exit 2;
        else
                nohup $JAVA $OPTIONS 2>&1 > $LOGFILE  &
                RETVAL=$!
                echo Started PID: $RETVAL
                echo
                echo $RETVAL >>$PIDFILE
                return $RETVAL
        fi

}

status() {
        echo -n "Status $NAME: "
        if [ -f $PIDFILE ]; then
                PID=`cat $PIDFILE`
                echo APEX Listener already running: $PID
                ps -ef | grep $PID
        else
                echo APEX Listener not running
        fi
}

stop() {
        if [ -f $PIDFILE ]; then
                PID=`cat $PIDFILE`
                echo -n "Shutting down $NAME "
                echo
                kill $PID
                rm -f $PIDFILE
        else
                echo APEX Listener not running
        fi
        return 0
}

log() {
        tail -f $LOGFILE
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    status)
        status
        ;;
    restart)
        stop
        start
        ;;
    log)
        log
        ;;
    *)
        echo "Usage:  {start|stop|status|restart|log}"
        exit 1
        ;;
esac
exit $?