Last edit: 2008-07-11 Graham Wideman

ModelRight

Introduction to ModelRight scripting and automation

Contents

Outline of sections below

• Introduction
• Orientation to ModelRight programmability
  • What there is to know
• 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 ("Hello world")
  • Script Explorer
  • Script Editor
• ModelRight Object Model
  • Orientation from Model Explorer
  • Model Browser
  • The ModelRight object model uses generic objects
  • Typical ModelRight model
  • Sample VBScript code to navigate the model
• Interpreting the browser info for scripting purposes
• Model Browser revisited
• MetaModel browser
• To-dos
• ModelRight Objects (Classes) Reference
• Other References

Introduction

As delivered, ModelRight provides a comprehensive environment for forward and reverse engineering databases. In addition, the environment provides a great deal of control over the graphical appearance of the model views, including the capability to add annotations and control style features.

To this foundation, ModelRight adds programmability features which provide opportunities to access and manipulate the database model and the diagram graphic appearance in powerful ways.  Many of ModelRight's built-in capabilities use this programmability: during the forward-and reverse engineering processes, when ModelRight generates SQL to read or manipulate the database, it uses scripts to compose the SQL statements.  You can modify these SQL-generating scripts to suit your own special purposes, different dialects of SQL and so on.

But that is just one use for ModelRight's programmability; here are some other scenarios that position ModelRight as a key component in a larger application development and maintenance process.

• Use ModelRight models as part of a process to automatically generate application code from database structure.
   
• After reverse engineering a model from a database that lacks relationships, use knowledge from the application code (or from inspection of indexes) to add relationships and thereby greatly improve the understandability of the model and the application. This scenario occurs often with web applications and MySQL.
   
• (More examples)

Orientation to ModelRight programmability

What there is to know

To gain traction on ModelRight programmability, there are two main themes to master:

• What kinds of code do you write to interact with ModelRight's world? 
  
  ModelRight offers a choice from two programmability approaches:
  • Built-in VBScript environment which runs within the ModelRight environment
  • COM Automation interface to ModelRight models, suitable for almost any external programming language
     
• What ModelRight features can your code interact with?
   
  • ModelRight exposes all aspects of its model data, such as tables, columns and all properties, and also including the visual aspects ("graphics") of ModelRight diagrams.

These topics are described in more detail below.

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 to inspect the database model and produce some kind of output, often SQL statements.

Scripts defined by the user are associated with a particular model and stored in the currently-open ModelRight model file (".wer" file).

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
• code you write is stored in the "document" file (in this case "model" file) that it's associated with
• 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 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, variables in VBS are all "variant" -- they don't need to be declared as a particular data type. This is good for small tasks, but less suited to large and complicated bodies of code.

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 provides 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 used to drive other processes, like generating code for applications that access the target database.

In other scenarios, automation using an external program may simply be a better choice for accomplishing tasks that are beyond the complexity readily handled within the VBS environment.

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.
• 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.

Programmability not currently available:

• VBS scripts within ModelRight cannot automate the application's user-interface itself (such as commands on the menus) : (... unlike VBA in Excel or Access, for example). For example scripts cannot initiate reverse-engineering a database into a new model.
• Similarly, external programs cannot "remote-control" the ModelRight program itself (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 ("Hello world")

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.

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 or reverse engineering operations. 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.
  [Needs more info on how to manage that... can you save and load different versions of the MySQL script-set, for example?]
• 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.)

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).

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 called "Evaluate_OnLoad" -- so named because it's the procedure that VBS should run when the script is loaded. 
  • For the many database-manipulating SQL-generating scripts, ModelRight "loads" the script(s) that are involved, and VBS runs each one's Evaluate_OnLoad procedure.  (The VBS environment lacks a way to run any old procedure in ad hoc fashion, as you might in VBA.  Hence all other procedures and functions in a script are subsidiary -- presumably called from Evaluate_OnLoad, since they cannot be called separately.)
  • For user scripts, the "Execute the script" button loads the script, triggering teh "Evaluate_OnLoad" procedure.

To manually run a script (ie: cause it to load), click on the leftmost button (hint says: "Execute the script").

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

Here you see:

• Output from our script (ie: Document.Write) has appeared.
• 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 -- and here it would be ready to push to the server and run, possible step-by-step.

(Side note: Obviously with features to handle scripts in two languages -- VBS and SQL -- there's some room for ambiguity and confusion.)

ModelRight Object Model

Having covered some basic skills in operating ModelRight's programming environment, we can proceed to discuss the objects that are available for your scripts to interact with: ModelRight's object model.  To learn what to expect in ModelRight's data structure, we will first look at various browsers. After that we will make the jump to ModelRight's actual programming object model.

Orientation from Model Explorer

You no doubt already expect to find ModelRight's data to be organized like you see in the Model Explorer, pictured here, and as you see in the properties that display in the Property Browser as you select each item in the Model Explorer (or on the diagram).

The branches shown in the "Model Objects" panel are obviously of great interest, as these describe the database objects themselves: Tables, columns etc.

Of interest in other scenarios are the diagrams, appearing in the upper panel. Here the structure is Model > Model Subsets > Diagrams.  Again, related properties appear in the Properties Browser as you select them in Model Explorer.

Less obvious is the fact that "drilling down" into a diagram brings you to the graphics for tables, views and so on that appear on the actual diagram page. You can make these appear in the Model Explorer by setting Tools > Options > Model Explorer > Diagrams: Display Graphics Objects. Whether you select graphics objects in the Model Explorer or on the diagram page, their properties again appear in the Property Browser.

We can follow tree branches like:

Model > Model Subsets > Diagrams > Table Graphics > Column Graphics.

So, the Model Objects branches correspond recognizably to our knowledge of databases and their parts, and the Diagrams branches correspond to the visual diagrams and graphics we see in ModelRight.

So far so good, but the Model Explorer, the diagrams themselves and the Property Browser are all intended for the end user (ie: database designer/analyst), and hide the technical details of the actual script-accessible objects.  More investigation is needed.

Model Browser

The Model Browser (Model > Model Browser, and not to be confused with Model Explorer) displays the entire tree of objects and properties that constitute a ModelRight model, and shows it in a single tree view.  Here in one view we can dig into all the collections, objects and properties that we previously saw in the two panes of the Model Explorer, and on the diagram, and in many panes-worth of Property Browser. 

You may want to dig into the "Model > Table" branch, and the "Model > Model Subset...Diagram...etc" branches of an example model to reassure yourself that it's all there to be found, and to be manipulated by your scripts, if only you knew how to navigate to the data you want.

The ModelRight object model uses generic objects

Given all the build-up above, you might have expected that objects that ModelRight exposes to your script would work something like:

But this is not how ModelRight works. ModelRight has instead implemented the model using multi-purpose "MRObjects" from which to assemble a tree, and on which to hang names and database properties.  An MRObject could hold data for a table, or for a column, or an index etc. In short it's a general purpose object that holds a collection of properties and a collection of children (themselves MRObjects), and can itself be a child of some other MRObject.

This allows ModelRight to make the tree adapt to different vendors' databases (and versions thereof) -- where different database may have different complements of features and properties. This adaptability is defined by ModelRight in a separate structure called the MetaModel, which guides what ModelRight may or may not assemble into an MRModel for a particular database, and helps define what operations it can perform.

In more detail, most of the database model structure is built from five (VBS) classes of objects:

In practice, the Collections objects aren't accessed directly by your script statements, as MRObject includes methods to retrieve child MRObjects and MRProperties from the collections it owns.

Typical ModelRight model

Combining the above-described classes with a couple of additional classes gives us the following sketch of a typical database model, as contained in ModelRight objects:

Things to note:

• The MRObject class shows up in numerous places, but its "type" property tells whether it's describing a Table, View, Index etc.
• MRObjects have a collection of children attached to them, which I've shown as a "Children" member, plus a crows-foot connector to imply multiple objects attached.  This is shorthand for several functions to access children which you can see in the MRObject section of the ModelRight Objects Reference . Examples:
   

  Set MyColumn = MyTable.AllChildren(1)	Retrieves a single child, but it might be a column, an index, or possibly something else
  Set MyColumns = MyTable.Children("Column") For each AColumn in MyColumns   ' do something with AColumn Next	First retrieves a collection of children of type "Column", then iterates through them.

• Properties, and their values appear to be connected to MRObject in a multi-step way. However, MRObject provides a variety of methods to access them readily -- again refer to the detailed docs on MRObject and MRProperty. For example
   

  S = Column.Property("Datatype").AsString

  Obtains string telling the value of this property. The Datatype property reports the datatype of the column to which it's attached.

Sample VBScript code to navigate the model

To consolidate this knowledge, here is a sample procedure which navigates a ModelRight model, picking out some basic info:

The lines involving Context and Model are often-used preliminary steps to gain initial access to the ModelRight programming interface.  For more details on this see the ScriptContext and MRModel sections in the ModelRight Objects Reference

Interpreting the browser info for scripting purposes

Now that the concept of multi-purpose MRObjects has been covered, we can see how to use the MRXxx object model to navigate the database model, but since MRObjects are so general all we know so far is that MRObjects have generic properties and generic children, and we don't know precisely what properties and what kind of children.

For that purpose we need to return to the ModelRight environment and its browsers/explorers. 

Model Browser revisited

If you open the Model Browser (Model > Model Browser) for a typical database, you can readily follow the tree to familiar looking features and attributes, but how do these relate to the objects that your script has to work with? The basic relationships are as follows:

MetaModel browser

What if you want to know the structure of parts of the ModelRight model that you don't happen to have an example for, and so can't view in Model Browser?  This is one of the purposes of the MetaModel Browser (Tools > MetaModel Browser).  In addition, in MetaModel Browser the tree of objects and properties is captioned in a manner somewhat closer to what you need to know as a programmer.

One minor issue with the MetaModel Browser is that both it and the Model Browser pop up in modal dialogs, so you can only open one of them at a time, when it would be useful to compare them side-by-side.  You can work around this problem by opening a second instance of ModelRight  (File > New)  and opening MetaModel Browser from there.

Here are some examples comparing what you see in Model Browser vs the same branch of the tree in MetaModel Browser.

The MetaModel browser makes the Children and Properties collections explicit.  Like the Model Explorer and Model Browser, it shares the idiosyncrasy of captioning collections with singular nouns rather than plural.

Suggestion for amplifying the value of the MetaModel Browser and Model Browser:

To-dos

Below are a number of additional topics, many of which touched on in ModelRight Evangelist's basic tutorial of 2008-07-09

Automation from VBA

• Initialization, and explanation
• Finalization -- any required?

Automation from other environments

Debugging strategies

MetaModel discussion

IDs

• Of Model  (GUID?)
• Of all MRObjects

How are IDs useful?

Properties

Local, vs inherited, vs calculated

This is an issue that non-programmer power-users also work with within the UI, so presumably it's discussed somewhere on that level? At any rate, probably does need a programmer-perspective discussion too.

Property terminology

modelright evangelist wrote:

Any object and any property have a type. For instance, we have objects of type “Table”, “View” or “Column”; we have properties of type “Name”, “Datatype”, etc.

I agree on MRObject, however I don't see how MRProperty has a "type" of Name, Datatype.  Looks to me like MRProperty has:
type: integer
Name: String

Transactions

Required

Scripts for generating DDL

ModelRight Objects (Classes) Reference

Note: As of 2008-07-11, in a number of cases I'm just guessing on the descriptions.

Classes are described in approximate order  of top-to-bottom of the overall model tree, starting with the two possible gateways to the ModelRight data:  ScriptFramework and ScriptContext.

ScriptFramework

ScriptContext

ScriptDocument

MRModel

Note that an MRModel also includes the features of an MRObject. See the MRModel.AsObject method.

MRObject

MRObjectCollection

In general it's not necessary to interact with an MRObjectCollection directly, as the MRObject that owns it provides methods for accessing the collection's items.

MRProperty

MRPropertyCollection

In general it's not necessary to interact with an MRPropertyCollection directly, as the MRObject that owns it provides methods for accessing the collection's items.

MRPropertyValue

MRVectorPropertyValue

Other References

Active Call Center programmability: Integrating COM Applications