SDDM Script to Create SQL Server Journal Tables

In my previous post, I talked about how Oracle SQL Developer Data Modeler (SDDM) is extendable with scripts written in languages that support JSR-223.  One of those languages is Groovy (http://www.groovy-lang.org/) and I showed how to add Groovy to the JVM classpath used by SDDM.  You might want to visit that post to see how.  The reason that I needed to write a script was that I wanted to easily add Journal Tables and the triggers for updating them to my database DDL.  SDDM actually comes with a script for doing this, but the script writes the triggers in PL/SQL for Oracle databases.  As my readers know, the database I was designing was a Microsoft SQL Server database, and the triggers needed to be written in Transact-SQL (T-SQL).

About Journal Tables

So, first of all, you might ask, is what is a Journal Table?  A Journal Table is a table that captures an before or after image of every change to a row in the table that is being monitored, usually with a date/time to show when the change occurred.  The idea is that we be able to reconstruct a history of changes to the data in a table.  Because of the overhead, you probably don't want a journal table behind all of your tables, but when you need to track who did it, when was it done, and what exactly was changed, a journal table can be a good solution.  It would also help to recover from changes that shouldn't have been done.  There are other solutions like Oracle's flashback query capabilities, but some databases can't do that, and some tables require a little more precise control. By the way, it is often necessary to set permissions on journal tables more stringently than permissions on the tables being journaled, so that hackers can't cover their tracks.

I personally prefer to make these before images, so that you can see what the row looked like before the change.  This would imply that you need before UPDATE and before DELETE triggers that INSERT the row of the table being journaled as it exists before the change to the journal table, but no before INSERT since before the INSERT the row didn't exist.  But my development lead wanted an after image, which is fine, since SQL Server only has AFTER statement triggers.  Here is an example of a table to be journaled, and a journal table:

CREATE TABLE my_table (

  my_id        INTEGER,

  my_char_data VARCHAR(30)

);

CREATE TABLE my_table_jn (

  my_id        INTEGER,

  my_char_data VARCHAR(30),

  operation    VARCHAR(10),

  date_changed DATETIME

);

If I do (on September 10):

INSERT INTO my_table (my_id, my_char_data)

   VALUES (1,'Example 1');

The after INSERT trigger should do:

INSERT INTO my_table_jn (my_id, my_char_data, operation, date_changed)

VALUES (1, 'Example1','INSERT', CONVERT(datetime,'09/10/2018',101));

If I do (on September 12):

UPDATE my_table SET my_char_data = 'Example2'

  WHERE my_id = 1;

The after UPDATE trigger should do:

INSERT INTO my_table_jn (my_id, my_char_data, operation, date_changed)

VALUES (1, 'Example2','UPDATE', CONVERT(datetime,'09/12/2018',101));

If I do (on September 14):

DELETE my_table

  WHERE my_id = 1;

The after DELETE trigger should do:

INSERT INTO my_table_jn (my_id, my_char_data, operation, date_changed)

VALUES (1, 'Example2','DELETE', CONVERT(datetime,'09/14/2018',101));

The data for DELETE is actually a before image, since after the delete there is no data.  By the way, triggers participate in the underlying transaction, so if the change to my_table is rolled back, so will the INSERT into my_table_jn.

The Script

As I said before, SDDM includes a script for adding code for Journal Tables to your DDL.  Though I couldn't use Oracle's script as written, it served as an excellent starting point for my version.  It also shows how you get access to the underlying SDDM data.  Here is the first part of the script:
/*
Writes CREATE commands for Journal Table and Triggers for SQL Server.
variable ddlStatementsList should be used to return the list with DDL statements
that are created by script - as shown below:
ddlStatementsList.add(new java.lang.String(ddl));
other available variables:
- model - relational model instance
- pModel - physical model instance
- table - the table in relational model
- tableProxy - table definition in physical model 
 */
Since the original script is written in Javascript, and mine is written in Groovy, I needed to change the syntax to Groovy, but much is the same or similar, including comments. Notice that SDDM hands you access points to the SDDM data - listed in the comments above.  But it doesn't tell you how to write to SDDM's log.  Fortunately, Dave Schleis provided the following code:
// get a handle to the application object
def app = oracle.dbtools.crest.swingui.ApplicationView
app.log("Creating DDL for Journal table for ${table.name}");
"ddl" is a variable to hold the code to be added to the ddl being exported for the table to be journaled.  In the original, this was a string variable, but strings in Groovy are immutable.  When you do "ddl = ddl + 'a string'" you are really creating a new string object.  So I changed it to a StringBuilder, which in Groovy and Java is an object to which you can append more data, without the waste of discarding old strings and creating new ones.
StringBuilder ddl;
String lname;
//journal table name suffix 
jnTabSuf = "_jn";
// trigger name suffix
jnAISuf = "_jn_ai";
jnAUSuf = "_jn_au";
jnADSuf = "_jn_ad";
prompt = model.appView.settings.includePromptInDDL;
useSchema = model.appView.settings.isIncludeSchemaInDDL();
if(model.storageDesign.open){
    if(useSchema){
        lname = tableProxy.longName;
    }else{
        lname = tableProxy.name;
    }
}else{
    if(useSchema){
        lname = table.longName;
    }else{
        lname = table.name;
    }
}
Here you will see a major advantage of using Groovy for your DDL Transformation (and other) scripts.  Groovy has a GString type, similar to strings in Java and Javascript, but you can embed variables in your GStrings.  In other languages you would have to concatenate strings.  This is a great space and time saver when the script is really code that writes code.
if(prompt){
    ddl = new StringBuilder("PRINT 'Creating Journal Table for ${lname};'\n");
}else{
    ddl = new StringBuilder("");
}
app.log("Creating Journal Table DDL.");
Most of the rest of the code is appending strings (GStrings) to the ddl variable.  Groovy overloads the "append()" method of StringBuilder to the "<<" operator, once again saving me a little time and space. Also notice that I'm using the triple quoted string in this section of code, which lets me use actual line feeds in place of the "\n" line feed character.  I didn't do this throughout, because I didn't want to fool with the original Javascript code more than necessary.
ddl <<
"""CREATE TABLE ${lname}${jnTabSuf}
  (${table.name}${jnTabSuf}_id INT IDENTITY(1,1) NOT NULL
  ,operation VARCHAR(10) NOT NULL
""";
cols = table.elements;
Here, I'm looping through the columns from the original table and writing the journal table with the same columns.  I don't add the date_changed column, because my tables already have a last_update_date column.  They also have a last_update_user_id.  You could probably add code that says to add these to the journal table if they don't exist.
cols.each {
    ddl <<
    "  ,$it.name $it.datatypeString";
    if (it.mandatory){
        ddl << " NOT NULL\n";
    }else{
        ddl << "\n";
    }
}
/* Primary key is non-clustered because queries of the Journal table will
 * usually be by the parent table's key.
 */
ddl <<
    "  ,CONSTRAINT ${table.name}${jnTabSuf}_pk\n" +
    "     PRIMARY KEY NONCLUSTERED (${table.name}${jnTabSuf}_id)\n" +
    " );\n" +
    "GO\n\n" + 
/* So instead, we create a clustered index on the parent table's PK.
 * No Foreign key, because we may delete rows of the parent table and still
 * keep the journal of changes to the now-deleted rows, including the datetime of
 * deletion.
 */
"CREATE CLUSTERED INDEX ${table.name}${jnTabSuf}_fki\n" +
    "  ON ${table.name}${jnTabSuf} (${table.name}_id);\n" +
    "GO\n"

if(prompt){
    ddl << "\nPRINT 'Creating Journal Triggers for ${lname};'\n";
}else{
    ddl << "\n";
}
So far, my code has been similar to the original, but my triggers are very different - SQL Server triggers are not at all like Oracle triggers. Leave a comment, if you want me to write a post to compare the way it works in SQL Server with triggers to do it in Oracle.
app.log("Creating Journal Table After Insert trigger.");
ddl <<
  "DROP TRIGGER IF EXISTS ${lname}${jnAISuf};\n" +
  "GO\n" +
  "CREATE TRIGGER ${table.name}${jnAISuf}\n" +
  "  ON ${lname}\n" +
  "  AFTER INSERT AS\n" +
  "BEGIN \n" +
  "  INSERT INTO ${lname}${jnTabSuf}\n" +
  "    (operation\n";
cols.each {
    ddl <<
    "    ,$it.name\n";  
}
ddl <<
    "    )\n" +
  "  SELECT 'INSERT' AS operation\n"
cols.each {
    ddl <<
    "    ,$it.name\n";  
}
ddl <<
    "    FROM inserted;\n" +
    "END;\n" +
    "GO\n\n";
The rest of the code is pretty similar.
app.log("Creating Journal Table After Update trigger.");
ddl <<
  "DROP TRIGGER IF EXISTS ${lname}${jnAUSuf};\n" +
  "GO\n" +
  "CREATE TRIGGER ${table.name}${jnAUSuf}\n" +
  "  ON ${lname}\n" +
  "  AFTER UPDATE AS\n" +
  "BEGIN \n" +
  "  UPDATE t\n" +
  "      SET t.last_update_date = CURRENT_TIMESTAMP\n" +
  "    FROM ${lname} AS t\n" +
  "    INNER JOIN inserted AS i\n" +
  "       ON t.${table.name}_id = i.${table.name}_id;\n\n" +
  "  INSERT INTO ${lname}${jnTabSuf}\n" +
  "    (operation\n";
cols.each {
    ddl <<
    "    ,$it.name\n";  
}
ddl <<
  "    )\n" +
  "  SELECT 'UPDATE' AS operation\n"
cols.each {
    ddl <<
    "    ,$it.name\n";  
}
ddl <<
    "    FROM inserted;\n" +
    "END;\n"+
    "GO\n\n"
app.log("Creating Journal Table After Delete trigger.");
ddl <<
  "DROP TRIGGER IF EXISTS ${lname}${jnADSuf};\n" +
  "GO\n" +
  "CREATE TRIGGER ${table.name}${jnADSuf}\n" +
  "  ON ${lname}\n" +
  "  AFTER DELETE AS\n" +
  "BEGIN \n" +
  "  INSERT INTO ${lname}${jnTabSuf}\n" +
  "    (operation\n";
cols.each {
    ddl <<
    "    ,$it.name\n";  
}
ddl <<
  "    )\n" +
  "  SELECT 'DELETE' AS operation\n"
cols.each {
    if (it.name == "last_update_date") {
ddl <<
    "    ,CURRENT_TIMESTAMP AS $it.name\n";  
    } else {
        ddl <<
    "    ,$it.name\n";  
    }
}
ddl <<
    "    FROM deleted;\n" +
    "END;\n"+
    "GO\n"
The last step is to add the ddl variable that I've been building to the DDL that will be exported.  Since my variable is a StringBuilder, not a String,we just need to use its toString method.
ddlStatementsList.add(ddl.toString());

So that's it.  You are welcome to cut and paste this into your own SDDM project.  Hope this was useful.

Scripting Oracle SQL Developer Data Modeler with Groovy

In my last post, I related how I am using Oracle SQL Developer Data Modeler (SDDM) to design a database to be implemented in a Microsoft SQL Server database.  I mentioned that one of the neat things about SDDM is that you can write scripts to do things that the tool doesn't do natively.  Scripts can be written in any language supported by the Java Scripting API (defined by JSR 223). Nashorn, the Java library for scripting in JavaScript is built into Java, so scripting in JavaScript works out of the box.  The SDDM developers have included a number of Nashorn scripts with the tool.  You can use these yourself, or use them as examples from which you can write your own scripts.

SDDM also comes with a good number of scripts written in JRuby - the JVM implementation of the Ruby language.  But to use the JRuby scripts, or scripts written in some other compliant scripting language, you need to copy the Java library(s) for that language to your SDDM classpath.  The easiest way to find a good place to put them is to go to the Help/About page, select the Properties tab, and find the property named java.ext.dirs:

With SDDM shut down, put the library in one of the directories listed in this property.  Then you can re-start SDDM. Mine is in %SDDM_HOME%\jdk\jre\lib\ext where SDDM_HOME is the root directory where you installed SDDM.  This information, and much of what I have learned about scripting SDDM, came from Dave Schleis, especially his blog post, Data Modeler Scripting-101-Lets start at the very beginning.

DDL Transformation Script for Journal Table

What I needed was a script to generate the DDL to create a journal table behind one of my tables, plus the database triggers to automatically write to the journal table every time DML is executed against the base table.  SDDM happens to come with a script, written in JavaScript (for Nashorn) to do this.  If you are designing an Oracle database, you may be able to use this script out of the box.  But I am writing for a SQL Server database, and while the CREATE TABLE for the journal table is almost identical, triggers in SQL Server are VERY different.  Not only are they written in T-SQL, which is quite different from PL/SQL, but triggers all run as statement level triggers, not row level triggers.  So I was glad to have the original script as an example, but I needed to do major changes.

Writing scripts in Groovy

Now, I could have kept the script in JavaScript, but I don't know that language very well.  I don't know Ruby very well either.  Dave Schleis's favorite language is Apache Groovy which like JRuby does support the JSR-223 standard, so some of his examples are in Groovy.  And I've gotten familiar with Groovy because of my past experience with Oracle Application Development Framework (ADF).  ADF's Model component, ADF Business Objects, can be extended with scripts written in Groovy.  So I decided to write my DDL Transformation for SQL Server Journal Tables in Groovy.

To write SDDM scripts in Groovy, all you need to do is copy the Groovy library to the SDDM classpath as I described above.  Download a copy of Groovy from http://www.groovy-lang.org/download.html.  While the latest stable version of Groovy is 2.5, it does not contain the library needed to support JSR-223.  So download the binary for the latest 2.4 version - I downloaded apache-groovy-binary-2.4.15.zip.  The library you want is groovy-all-2.4.15.jar, found in the "embeddable" directory.

To write scripts, select Tools/Design Rules and Transformations... from the SDDM menu.  The kind of script I wanted to write is a Table DDL Transformation, which can be run when exporting the DDL for your Relational/Physical design.  Choose the Relational Model for your script.  Then you will see:

I have already added my script. Notice that there are four possible scripts for each script set - one to add DDL code before the CREATE TABLE commands, one to actually replace the CREATE TABLE commands, one to add code after the CREATE TABLE, and one to add code at the end of the DDL for all the tables being exported.  You don't need to use all four, and in fact, my journal table code is only for After Create.  You won't be able to select a scripting engine until after you start writing the script - I'm not sure why. So start by just adding a comment or a few spaces. I started by selecting the Journal tables script that comes with SDDM and copying it, then pasting it into my new script. Then pick a scripting engine.  If you have correctly added the Groovy library to the classpath, "Groovy Scripting Engine" should be one of the choices.  Warning - there is currently (18.1 for me) a bug in SDDM where SDDM doesn't save the scripting engine choice for Groovy Scripting Engine.  Oddly, it does for Oracle Nashorn, the default, and for JRuby.  The work-around is to go into dr_custom_scripts.xml find the reference to your script and change the engine attribute. I'd make a back-up of this file first.

So in the next post, I'll go through the code for my script and talk a little about testing it.

Triggers, Jobs, Events and Queues - Part 1

A question in the OTN SQLand PL/SQL Forum got me to thinking about database triggers and the correct way to implement some requirements that lead people to misuse them.  This relates back to my earlier post on Updatable Views - Code Schema with Updatable Views.  I said:

"I must emphasize that INSTEAD OF triggers operate within the scope of the current transaction.  Any change they make to the underlying data is part of the same transaction, and all changes are either entirely committed, or entirely rolled back.  This can work to your advantage – take the example of the funds transfer that I mentioned before.  This is two INSERTs and either both are committed, or both are rolled back.  But there are things that a trigger could do that are not part of the transaction, such as calls to UTL_FILE to write a file outside of the database.  These happen even if the transaction is rolled back.  Not only that, but there are cases when the code in a trigger might actually be run more than once.  If you want to call one of the built-in packages with a name starting with “UTL”, you should probably queue an event to do it, rather than doing it directly."

Send an E-Mail on an Event

So here is a case where you might be tempted to use a trigger:  In the HR sample schema that comes with all Oracle databases, there is a table, EMPLOYEES.  Each Employee has a foreign key, DEPARTMENT_ID that points to DEPARTMENTS.  Each Department has a foreign key, MANAGER_ID, which points to the EMPLOYEES row for the Department Manager.  What we want to do is send an e-mail to the department manager whenever a new Employee is added to that Department, or an existing Employee’s DEPARTMENT_ID is changed to point to a new Department.

You say, "Fine, I’ve got a SEND_MAIL procedure that calls the UTL_SMTP built-in package to send e-mails.  I’ll just write a trigger to call it when inserting a new EMPLOYEES row with a non-null DEPARTMENT_ID, or updating it with the new DEPARTMENT_ID not equal to the old one."

CREATE OR REPLACE TRIGGER employees_manager_change_email
  AFTER INSERT OR UPDATE OF department_id
  ON employees
  FOR EACH ROW
DECLARE
  manager_first_name employees.first_name%TYPE;
  manager_last_name  employees.last_name%TYPE;
  manager_email      employees.email%TYPE;
BEGIN
  IF :NEW.department_id IS NOT NULL AND
     (INSERTING OR
      (UPDATING AND :NEW.department_id <> NVL(:OLD.department_id,0))) THEN
    SELECT first_name, last_name, email
      INTO manager_first_name, manager_last_name, manager_email
      FROM employees
     WHERE employee_id = (SELECT manager_id
                            FROM departments
                           WHERE department_id = :NEW.department_id);
    SEND_MAIL (p_from    => 'HR',
               P_to      => manager_email,
               P_subject => 'New Employee in your Department',
               P_text    => 'Please welcome '||:NEW.first_name ||
                            ' '||:NEW.last_name || ' to Department '||
                            TO_CHAR(:NEW.department_id)||' as a '||
                            :NEW.job_id ||'.');
  END IF;
END;

Ah, but it won’t work – you’ll get a mutating table exception because you can't select from the EMPLOYEES table at the same time that you are updating it.  Some people would then take the offending code and put it in a procedure and make it AUTONOMOUS transaction.  That way, since the SELECT from EMPLOYEES is in a separate transaction from the insert or update, it works.

I'm certainly in favor of making this a separate procedure, but making it autonomous is a bad idea.  Suppose the change to an Employee's department isn't committed?  The user changed his/her mind, and decided not to save the change.  The manager would get the e-mail anyway, because the e-mail transaction is no longer dependent on the insert or update transaction.  No, we need to make sure that the e-mail only gets sent if the change is committed.  Can we make the change raise some sort of notification that an e-mail should be sent, but as part of the current transaction, so it ONLY happens on COMMIT?

The Procedure

So, first, let's make this a procedure to separate the code for sending the e-mail from the trigger code.  We are still going to use a trigger to call it because we want it to send the e-mail automatically on the event, but this is not going to be an AUTONOMOUS TRANSACTION, so we can't just call it.  We'll need to pass it all the information it needs from the EMPLOYEES row that is being changed by the transaction.  Although I'm going to show this as a stand-alone procedure, my usual practice is to put all my procedures and functions in packages.

CREATE OR REPLACE PROCEDURE employee_change_manager_email (
  p_department_id IN employees.department_id%TYPE,
  p_first_name    IN employees.first_name%TYPE,
  p_last_name     IN employees.last_name%TYPE,
  p_job_id        IN employees.job_id%TYPE
  ) IS
  manager_first_name employees.first_name%TYPE;
  manager_last_name  employees.last_name%TYPE;
  manager_email      employees.email%TYPE;
BEGIN
  SELECT first_name, last_name, email
    INTO manager_first_name, manager_last_name, manager_email
    FROM employees
   WHERE employee_id = (SELECT manager_id
                          FROM departments
                         WHERE department_id = p_department_id);
  SEND_MAIL (p_from    => 'HR',
             p_to      => manager_email,
             p_subject => 'New Employee in your Department',
             p_text    => 'Please welcome '||p_first_name ||
                          ' '||p_last_name || ' to Department '||
                          TO_CHAR(p_department_id)||' as a '||
                          p_job_id ||'.');
END employee_change_manager_email;

But how to call it?  I can't just call it from the trigger, because it would still raise a mutating table error.  If I make it AUTONOMOUS, it gets called whether the transaction is committed or not.  So I need to call it in some way that depends on the transaction, but is still in some way autonomous.  Fortunately, there are two possibilities in Oracle.  We can submit a job with DBMS_JOB, or we can queue it as an event with Oracle Advanced Queuing (AQ).  DBMS_JOB is simpler, so I'll save Oracle AQ for another post.

Method 1: DBMS_JOB

We mostly think of the DBMS_JOB package as a way to schedule jobs and have them execute at particular times.  And in that role, it has mostly been supplanted by the DBMS_SCHEDULER package.  However, it is also possible to use DBMS_JOB to schedule a job to run immediately.  And a job runs in its own transaction context, independent of the transaction that scheduled it.  Best of all, the DBMS_JOB.SUBMIT procedure requires a COMMIT, which means that if you run it from a trigger, the job does not start running until the triggering transaction is committed.  DBMS_SCHEDULER does not need a commit, so it won't do for this task.  If the triggering transaction is rolled back, the job will not run.  This is exactly what we want for this requirement.  So here is our new version of the trigger:

CREATE OR REPLACE TRIGGER employees_manager_change_email
  AFTER INSERT OR UPDATE OF department_id
  ON employees
  FOR EACH ROW
DECLARE
  job_number NUMBER;
BEGIN
  IF :NEW.department_id IS NOT NULL AND
     (INSERTING OR
      (UPDATING AND :NEW.department_id <> NVL(:OLD.department_id,0))) THEN
    DBMS_JOB.SUBMIT (job  => job_number,
                     what => 'employee_change_manager_email (
  p_department_id => '||:new.department_id||',
  p_first_name    => '''||:new.first_name||''',
  p_last_name     => '''||:new.last_name||''',
  p_job_id        => '''||:new.job_id||''');'
                     );
  END IF;
END;

This is a little hard to read because the call to the procedure to send the mail is submitted as a string, and we need to concatenate the parameters into the string.  The trigger runs DBMS_JOB.SUBMIT to create and submit a job, and the missing "next_date" and "interval" parameters default to running the job immediately and only one time.  But the job doesn't actually get submitted until the COMMIT for the INSERT or UPDATE of EMPLOYEES.  When the job runs, the procedure runs in a separate transaction, so there is no mutating table.

In another post, I'll give the Oracle AQ method.