Last edit: 2009-05-04 Graham Wideman

ModelRight

Sample ModelRight Scripts

Contents

• Script Samples Introduction
  • Sample Model
  • Visual Basic Script documentation
• Navigating and "dumping" a Database Model
• Modifying a database model
• Manipulating ModelRight "Graphics": visibility and style properties

Script Samples Introduction

The script samples on this page are written in VB Script language and are intended to run in ModelRight's VB Script environment. A second page of sample code shows how to manipulate ModelRight files using COM Automation from other languages.

Sample Model

Each of the samples makes use of the "sakilla" sample data model supplied with ModelRight (for MySQL). If you would like to follow allong with these samples, you may wish to start by making a copy of that model (just copy the sakilla.wer file) to a convenient location, and modify that with the sample scripts below.

The sample code is not particularly dedicated to that model, so you can start with whatever model you like.

Visual Basic Script documentation

The syntax, functions and features of the Visual Basic Script language (not specific to ModelRight) are as documented at Microsoft Developer Network: VBScript. In general it is very similar to Visual Basic (up to version 6, not VB.NET) and Visual Basic for Applications (as in Microsoft Word and Excel), though all variables are of "variant" type -- there is no use of the Dim statement.

Navigating and "dumping" a Database Model

Here is a script that demonstrates loops which visit several levels of the database model, printing some information about each object to the Document window.

Sub Evaluate_OnLoad
  Set Context = CreateObject("SCF.ScriptContext")
  Set Document = Context.ScriptDocument
  Set ThisScript = Context.Object

  Set Model = ThisScript.Model
  Document.WriteLine "Model: " & Model.Name & " " & Model.Id
  Set ModelMRObject = Model.AsObject
  Document.WriteLine "ModelMRObject: " & ModelMRObject.Name

  Set Tables = ModelMRObject.Children("Table")
  Document.WriteLine "------ Tables and columns -------"
  For Each Table In Tables
    Set TableNameProp = Table.Property("Name")
    TableName = TableNameProp.AsString
    Document.WriteLine Table.TypeName & ": " & Table.Name
    Set Columns = Table.Children("Column")
    For Each Column in Columns
      Document.WriteLine "  " & TableName _ 
                      &   "." & Column.Property("Name").AsString _
                      & " : " & Column.Property("Datatype").AsString
    Next
  Next
end sub

The above script produces results like this (using the sakilla database model supplied with ModelRight MySQL):

Model: Model 1 {F848A0DB-3725-460A-8C03-0DD0D5F9F2C0}
ModelMRObject: Model 1
------ Tables and columns -------
Table: actor
  actor.actor_id : SMALLINT
  actor.first_name : VARCHAR(45)
  actor.last_name : VARCHAR(45)
  actor.edit_by : VARCHAR(33)
  actor.edit_date : TIMESTAMP
Table: address
  address.address_id : SMALLINT
  address.address : VARCHAR(50)
    [...]

Notes:

1. Gaining access to ModelRight's loaded data:

The first step is to gain access to ModelRight's data, which this code does by using the VBScript COM method CreateObject, and requesting a reference to the object called "SCF.ScriptContext".  As you will see by referring to the Object Reference guide, ScriptContext provides references to Document and Model, which are both needed in this example.

2. The code then proceeds in a straightforward manner to obtain from Model the set of child MRObjects representing Tables, and then each of their children representing Columns, reading some properties and printing out some information along the way.

Modifying a database model

This sample code demonstrates modifying an existing database model. The scenario: we're revising some "housekeeping fields" in the model. The existing "last_update" field will be renamed to "edit_date", and we will add a new field "edit_by".  To keep the example clearer, we'll just apply it to the one actor table. To apply it to all tables, use the loop strategy from the previous example.

Sub Evaluate_OnLoad
  Set Framework     = CreateObject("SCF.ScriptFramework")
  Set Model         = Framework.CurrentModel
  Set ModelMRObject = Model.AsObject
  Set Context       = CreateObject("SCF.ScriptContext")
  Set Document      = Context.ScriptDocument

  Document.WriteLine "--- start ---"

  Set Table = ModelMRObject.ChildByName("Table", "actor")

  '-----------------------------------
  '    Rename existing column 
  '-----------------------------------
  OldColName = "last_update"
  Set Col = Table.ChildByName("Column", OldColName)
  if Col is Nothing then 
    Document.Writeline "could not find " & OldColName
    exit sub
  end if

  NewColName = "edit_date"
  Model.BeginTransaction "Rename column to " & NewColName
  
  Set PropVal = Framework.CreatePropertyValue("Column", "Name")
  PropVal.FromString(NewColName)
  Col.SetProperty "Name", PropVal
  
  Model.EndTransaction

  '-----------------------------------
  '     Add new column
  '-----------------------------------
  ChildOrderEditDate = Table.ChildOrder(Col)

  NewColName = "edit_by"
  Model.BeginTransaction "Add column " & NewColName
  Set Col = Framework.CreateObject("Column", Table)
  Table.SetChildOrder Col, ChildOrderEditDate

  Set PropValue = Framework.CreatePropertyValue("Column", "Name")
  PropValue.FromString(NewColName)
  Col.SetProperty "Name", PropValue

  Col.SetDataType("VARCHAR")

  Set PropValue = Framework.CreatePropertyValue("Column", "Datatype Length")
  PropValue.FromInteger(33)
  Col.SetProperty "Datatype Length", PropValue
  Model.EndTransaction

  Document.WriteLine "--- done ---"
end sub    

Notes:

1. Preliminaries: In this example we need access to some methods of ScriptFramework and some of ScriptContext, so the code requests both.
2. Testing that ChildByName succeeded:  When getting a child from a collection (for example using ChildByName() ), you can test whether the result is Nothing to see if the child was actually found.
3. Setting a Property: To set a property value requires a couple of steps. First you must use CreatePropertyValue to create a PropertyValue object with a specific name, and suited to a specific parent object. Then you can set the value contained in the PropertyValue object (using FromString or the other FromXxx methods). Finally you can hand the PropertyValue object to the desired parent using SetProperty.  If that parent already has an existing property of that name, it will be discarded, and thus replaced by your new one.
4. Adding a column (or child in general): The basic pattern is to use the CreateObject methods of ScriptFramework to create the object and include it in the Children collection of the desired parent. You can now add or change the properties of the newly created object.  If you want to set the order of the child within the parent's Children collection (as you might with columns) you can use ChildOrder and SetChildOrder methods to find and set the orderings.
5. Setting Column properties; Column.DataType: For the most part, besides requiring the CreatePropertyValue procedure, setting column properties is straightforward.  However, setting the column's Datatype property is slightly different. For this use the SetDataType method for the column. This method is provided because the Datatype property is not a simple property (such as a string) but instead holds an object (in fact an MRObject with Type = "Type"), and needs to correspond to an object in the list of types (which you can view in the ModelBrowser Type collection, where you will find the list of strings you can use with the SetDataType method.)
6. Transaction Statements are Required: All changes to the model must be bracketed by Model.BeginTransaction and Model.EndTransaction statements. This gives ModelRight a batch of changes to commit and reconcile at once. It also places that batch of changes into the Undo queue, and permits you to use the Undo button to undo it, or the Edit > Undo menu item.  The latter shows a list of recent transactions by caption, so it's helpful to provide a distinctive caption when calling BeginTransaction.

Manipulating ModelRight "Graphics": visibility and style properties

This sample code demonstrates the manipulation of the "graphics" part of the data, that is to say the visibility and style features of a particular diagram.

Scenario: To enhance conceptual understanding of the system, we want to alter the background color of tables to convey which subsystem they pertain to. Also, we want to reduce clutter by hiding housekeeping fields. To keep things simple, the sample code will just change the color of one table, and hide its "last_update" field.

Before and after:

Here's the code which demonstrates how to access the graphics part of the model.

Sub Evaluate_OnLoad
  Set Framework     = CreateObject("SCF.ScriptFramework")
  Set Model         = Framework.CurrentModel
  Set ModelMRObject = Model.AsObject
  Set Context       = CreateObject("SCF.ScriptContext")
  Set Document      = Context.ScriptDocument

  Document.WriteLine "--- start ---"
  Set ModelSubset = ModelMRObject.ChildByName("Model Subset", "Model Subset 1")
  Document.WriteLine "Model Subset: " & ModelSubset.Name

  Set Diagram = ModelSubset.ChildByName("Diagram", "Diagram 1")
  Document.WriteLine "Diagram: " & Diagram.Name

  Model.BeginTransaction "Fiddle with graphics"

  '-------------------------------------------
  ' Change table background gradient to red 
  '------------------------------------------- 
  Set TableGraphics = Diagram.ChildByName("Table Graphics", "film")
  Document.WriteLine "TableGraphics: " & TableGraphics.Name  

  PropName = "Fill Blend To Color"
  Set PropValue = Framework.CreatePropertyValue("Table Graphics", PropName)
  PropValue.FromInteger &H8080FF
  TableGraphics.SetProperty PropName, PropValue

  '-------------------------------------------
  ' Hide housekeeping field 
  '------------------------------------------- 
  Set ColumnGraphics = TableGraphics.ChildByName("Column Graphics", "last_update")
  Document.WriteLine "ColumnGraphics: " & ColumnGraphics.Name  

  PropName = "Not Displayed"
  Set PropValue = Framework.CreatePropertyValue("Column Graphics", PropName)
  PropValue.FromBoolean True
  ColumnGraphics.SetProperty PropName, PropValue  

  Model.EndTransaction
  Document.WriteLine "--- done ---"
end sub

Notes

1. "Model Subset" > "Diagram" etc:  The key to accessing the graphics is to follow the "Model Subset" part of ModelRight's tree of data. As usual you can view that in ModelBrowser and Metamodel Browser.  Be aware when browsing in ModelBrowser that on any particular object you will see only the properties which have actually been set -- ModelRight assumes values for any other properties, possibly via inheritance to default objects.  For a complete picture of the possible properties, see the Metamodel Browser. For example, that's where we would find out that the "Not Displayed" property can be used.
2. Colors -- Use Hex: As shown in the sample code here, it's convenient to use hexadecimal for color values, as this allows you to see the Blue, Green, Red values separately:  &Hbbggrr.
3. Error handling: The sample code omits error handling to reduce clutter. In your scripts you will probably benefit from some error handling, primarily checking that statements that retrieve members of collections (such as ChildByName) actually succeeded.

Ideas

If you are interested in creating versions of your database model diagrams that have maximum impact for different audiences, it will be worthwhile examining the Model Subset branches in the Metamodel Browser to see how much control you can actually gain.  Under control of scripts, you can lay out diagrams and style them completely following your own rules.

• You could produce diagrams that highlight recently added tables and columns, or tables and columns proposed for deletion.
• In a system where you want to show auxiliary "list-of-value" tables, but want them to take minimal space, you can hide all but the table's title-bar.
• As you think about manipulating these appearance features with scripts, be sure to look first at the ModelRight's "domain" features, particularly the Graphics Domains. Creating new Graphics Domains for your new styles will allow you to apply them consistently, whether by hand or programmatically.