Getting started with ModelRight scripting

Contents

• Choice of two programmability approaches
  • Built-in VBScript scripting environment
  • Automation using external programs
• What ModelRight features can be programmed?
  • Accessing and navigating ModelRight's objects
• ModelRight's programming UI and "Hello world" example
  • Script Explorer
  • Creating and saving a script
  • Script Editor

Choice of two programmability approaches

ModelRight provides two main programmability approaches:

1. Built-in VBScript scripting environment 

ModelRight includes Microsoft's Visual Basic Scripting (VBS) environment, which is at version 5.6 at this writing. Scripts can be created, edited and run within ModelRight, and are generally used to manipulate the current database model or its graphics, or to inspect the database model and produce some kind of text output, perhaps a report, or perhaps SQL statements to be run on the database.

You may already be familiar with the Visual Basic for Applications (VBA) environment found in Microsoft products like Access, Excel and Word.  The VBS environment is similar in concept:

•  the language is recognizably Visual Basic
• you write and execute code within the environment of a host application, like Excel or Word VBA
• Code you write can be stored in the "document" file (in this case "model" file) that it's associated with, or made accessible for use with any document (model).
• your code can interact with the data of the "document", by working with an object model made available by the application. For Excel those are objects like WorkSheet and Cell. For ModelRight the objects represent database, tables and columns, for example.

However, there are significant differences between VBA and VBS.  In general VBS is suited to smaller-scaled functionality for less-demanding tasks. For example, in VBS one works with only one module at a time, which is suited to small tasks, but less suited to large and complicated bodies of code. Programming is somewhat simplified by virtue of all variables being of type "variant", but function parameters are untyped and thus not checked for type.

There are a variety of ways that the integration of VBS into ModelRight has been made specific to the needs of ModelRight. However the language itself, its general functions and features (not specific to ModelRight) are as documented here at Microsoft Developer Network: VBScript.

2. Automation using external programs

In addition to the built-in VBS programmability, ModelRight also installs an API through which to manipulate ModelRight models/diagrams from an external program, and this API is accessed via COM Automation. Consequently, such external programs can be written in your choice of language, including VBA from Access or Excel; VB, C# or C++ from .NET; Delphi, Python, PHP or any language which can create an Automation client.

This approach is especially powerful where ModelRight modeling activities are part of a larger database application workflow, as suggested in the introduction.  For example:

• ModelRight models and diagram views can be automatically built or augmented from external sources of data
• ModelRight models can be read and the model data used to drive other processes, like generating code for applications that access the target database.

In other scenarios, automation using an external program may be resorted to after a VBScript project gets too complex or cumbersome.

What ModelRight features can be programmed?

ModelRight provides your code with the capability to read and manipulate its model of the database itself (tables, columns, relationships etc), and its model of the diagrams of the database (particular layouts of table graphics and relationship graphics on a page; annotations; and formatting of these).

• VBS scripts running within ModelRight have access to the model that's currently loaded. Hence, changes that your code makes to that model appear immediately on screen, possibly for use by subsequent steps.
• External programs (using automation) can load ModelRight files, read and manipulate that data and save it to a new file. (Presumably such a program should not be used to operate on a file that ModelRight currently has open.)
• VBS scripts and external automation programs have equivalent capabilities to access ModelRight's models -- the difference is only that VBS scripts access the model within the ModelRight program while external programs operate on ModelRight files independent of the ModelRight program.  The API offered to external programs is simply a DLL (SCF.dll) which provides COM access to ModelRight *.wer files in a structured way.

Programmability not currently available:

• Unlike automation in Microsoft Excel or Word, VBS scripts within ModelRight cannot automate the application's user-interface itself (such as commands on the menus) : For example scripts cannot initiate reverse-engineering a database into a new model.
• Similarly, external programs cannot "remote-control" the ModelRight program itself via Automation. (again unlike Excel/Word/Access automation).

Accessing and navigating ModelRight's objects

To write useful code for ModelRight, you will need to become familiar with ModelRight's object model -- that is to say the objects that ModelRight exposes for your code to interact with. The general strategy is very similar to working with VBA to manipulate objects in Word or Excel, and in ModelRight goes something like this:

• Your code gets a reference to an initial object, such as Model
   
• Your code navigates to Model's children (for example Tables), and to their Children (such as columns), and is able to create, delete and modify them, and read and write their properties (names, data types and so on).

Exact details (or how to find out the exact details) are described in sections below. But first, a look at the user interface for programming, so that you can use it to explore ModelRight's objects.

ModelRight's programming UI  and "Hello world" example

Script Explorer

In the ModelRight application, you can browse, create or select scripts in the Script Explorer panel. If this is not visible, you can summon it from the main menu: Window > Script Explorer. Assuming you have a model file open, you should see something like the screenshot here.  To try this out, either start a new model, or make a copy of another model file, perhaps one of the samples that comes with ModelRight.

Script Explorer shows three or more top-level folders, which entail three different kinds of scripts:

• User-defined scripts: You are free to add scripts to this folder, and use them for whatever you like.
• Database-manipulating scripts (the MySQL folder in this example): These scripts are called upon by ModelRight to generate SQL statements to  perform forward engineering operations such as creating or modifying a database. The scripts are organized in sub-folders according to the database object operated upon, and then individual script files to generate SQL for specific operations, such as Alter, Create, Drop and so on.
  
  You cannot add scripts to this folder, but you can edit the scripts if you need to deal with special circumstances that require ModelRight to use different SQL for its work.
   
• GlobalNamespace:  A single script containing common functions that can be called by other scripts. (This is equivalent to a utility module in VBA. VBS does not have the concept of modules, so its capacity to reuse standard chunks of code is limited.)

Creating and saving a script

Create a new script by right-clicking on the User Defined item. You can name the new script item that appears. 

• If you take no further action to save the script, it will be saved as part of the current model file, and be available only when this model is open. 
• To make the script available always within ModelRight "application-wide", right-click on the script and choose Save to disk. This will save the script into a separate file in the ModelRight application directory (within Program Files) under Scripts\User Defined\.
• If there already exists a User Defined script of a particular name, and you want to override that script in a particular model only, you can do so by selecting the particular script in the Script Explorer, and simply entering new code for it.  Do not use Save to disk on this new code (as that will replace the existing "application-wide" version of the script). Instead just let the override script be saved with the model.

Script Editor

As you select (or create) scripts in the Script Explorer, the text of the script appears in an editor in the Property Browser panel. (To open: Window > Property Browser).  To get started, here's a script that can be pasted into the script editor:

'--------------------------
Sub Evaluate_OnLoad()
'--------------------------
  Set Context = CreateObject("SCF.ScriptContext")
  Set Doc = Context.ScriptDocument

  Doc.WriteLine("Hello!")
  Doc.WriteLine("VBS version: " & ScriptEngineMajorVersion & _
                "." & ScriptEngineMinorVersion)
  Doc.WriteLine("MyFunc: " & MyFunc)
end sub

'--------------------------
function MyFunc
'--------------------------
  MyFunc = "Hi from MyFunc"
end function

The screencap below shows some of the key features of the Script editor panel.

Main points:

• When initially creating or browsing to a script, the Property Browser's Script tab shows the script editor, as indicated by the high-lighted "Edit the script" button, 2nd from left a the top of the editor.
• Each script has a "main" procedure named "Evaluate_OnLoad" -- so named because it's the procedure that VBS should run when the script is loaded.  Any other procedures and functions in a script are subsidiary -- called directly or indirectly from Evaluate_OnLoad, since they cannot be called separately.
• To manually run a script the "Execute the script" button loads the script, triggering the "Evaluate_OnLoad" procedure. (It also changes the editing pane as shown below.) 
  • Note: Here we are showing a User Defined script.  You can also try to run a script from the list of built-in database-manipulation scripts. However this generally results in an error because these scripts usually have statements that depend on a particular context having been set, telling which database object (eg: table or column) to operate upon.

Pressing the "Execute the script" button also causes the display to change like this:

Here you see:

• Output from our script has appeared. As this is from Document.Write or WriteLine statements, this area is called the Document window.
• Around the window are controls for running SQL. This is not useful for our test script, but the purpose of many scripts in ModelRight is to generate SQL DDL into the Document window.  In that case the controls can be used to push to the SQL statements, possibly step-by-step, to the server and run.

Tip: The buttons and other features that address two different scripting languages and stages could cause some confusion. Be sure to identify which features are for running VBScript, and which are for running SQL.

Original article by Graham Wideman