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.

Javascript Frameworks

Continuing on with a few opinions about UI design, development and implementation ...

So what is "cool" now?  Javascript frameworks like Angular, Ember, React and Oracle's own JET. These are great, and they have their place, I'm sure.  The argument is that since these frameworks use the power of the user's browser, they can be more responsive.  There is less communication between the server and the client, which should also speed things up. They can do exactly what the designer wants them to do - no component framework getting in the way, wanting to do things its way.  They are open source for the most part, which frees us from Oracle's tyranny.  And working in HTML and CSS can let you use graphic designers and their toolboxes, like DreamWeaver.

The first problem I have with Javascript frameworks is that this is a return to code it yourself.  The tooling builds some of the code as sample files, where you take some prebuilt code and tweak it.  For the most part, however, you have to write the HTML, the CSS, the Javascript.  Fortunately, there is some help in the libraries, like JQueryUI, with some precoded components, and lots of sample code.  But I miss JDeveloper's ability to drag a data control onto a page, say you want to drop it as a bar graph, do a little configuration, and presto.

Tooling and IDEs

Of course, in some respects, this is a problem with tooling rather than with the framework.  I suppose that if I could point Eclipse or Netbeans at a REST service and have it generate the code for say, an Angular 2 service module that accesses the service, I might feel differently.  Or if I could generate a first-cut form to do CRUD with that service, I'd feel better about these frameworks.  I also get the feeling that developers WANT to code it, and hate handing over part of the job to someone else, especially with something like ADF, where you can't easily see the source code.

The proponents say, "but you can make it look EXACTLY the way you want."  True, but at what cost? Can your users wait while you do it?  But you say "It's Agile, we'll do 80% now and get it closer to 100% with each iteration."  And I reply, "I can give you 98% in the time it takes you to do 80%, and do the users care if I can't get to 100%?"  If they DO care, guess what, ADF 12.2.1 uses JSF 2.2 (and so do the latest versions of other Faces libraries), and you CAN code in HTML5/CSS/Javascript what you can't do with JSF, yet still interact with the pure JSF parts. Someday, I'd love to do a contest - who can meet the user's requirements fastest.

Responsiveness and Speed

Another advantage claimed for these environments is that most of the processing takes place on the user's computer, and interacts directly with the browser.  Since the application doesn't have to interact with the server as much, it runs faster.  And on the server side, it isn't as complicated and doesn't require as much server resources.  All the server is doing is downloading files to the client, and running REST services.  What's more, the browser usually caches files that it has already downloaded, so if the file hasn't changed, it will use its local copy of the file, and won't go to the server for a fresh copy.  Please note that this can be a disadvantage for mobile devices with slow connections and limited storage.  There is a lot more code to be downloaded and stored.

The honest truth is that in ADF Faces many of the Rich Faces components are rendered using Oracle JET, so they have all the responsiveness of this Javascript library, except that I didn't have to code it.  Similarly, PrimeFaces renders with JQuery and JQueryUI.  Because PrimeFaces does this, it can use JQuery tools to create themes.  ADF Faces uses skinning through CSS-like files and the basis for its skins is in the open source Apache Trinidad Faces library.  Of course, this means that JSF frameworks can be subject to some of the same problems as the Javascript frameworks that they use for rendering.

Testing and Security

One problem that I have with Javascript frameworks is that all of the source code is delivered to the user's browser.  Javascript is interpreted code, which means that some bugs won't show themselves until runtime.  Compilers can catch some problems up front, which can speed development.  I should note that Angular 2 & 4 are usually written in Typescript which compiles to Javascript - a little of the best of both worlds.  But the biggest problem I have with this is the advantage it gives to hackers.  When a hacker can see ALL of the source code, including code that is intended to keep bad data out of the database, he/she can re-write it to bypass your safeguards.  If you are going to write REST services for your Javascript framework to use, you need to make sure that you have server-side protection.  Never accept data from a browser without validating it at the server.  Don't assume that the Javascript code that you wrote will be executed unchanged.

So there I am – an old developer who found a great way to develop and doesn't want to give it up.  I'm not against the Javascript frameworks, and if you want to build applications that way, good for you.  I'll do my best to support you.  I know enough HTML and CSS to be useful, and even some Javascript.  As a long-time database advocate, SQL and PL/SQL expert and developer, I'll write good solid secure REST services for you.  Does that make me obsolete?  I don't believe so.  I'm not quite ready to leave you without my help and advice yet.