The System  DB Modeller 

Orixa includes a set of built-in tools for extendng your App. One of the main ones is the System DB Modeller, which is used to view the overall structure of your App and help to design and extend it.

The purpose of the System DB Modeller to provide a visual environment in which you can view and review all the tables in your App, their relationships, and all their related meta data such as Indexes, Triggers, Views, Generation & Computation details, Relationships and Constraints. It includes a SQL Editor which can be used to craft SQL Scripts to update and extend your App, and tools to help you write these scripts.

The System Modeller also displays System Stored Procedures and Functions.

System DB Modeller with Context Menu visible  

Accessing the System DB Modeller

Accessing the System DB Modeller

The System DB Modeller is accessed from the main "System" Menu. Note that this menu is only visible in your App if you have an "Admin" level Security Level. Simply click on the item shown at (1) in the image to open the "System" menu, then click (2) to open the modeller.

Main Features of the System DB Modeller Window

System DB Modeller Main Panel  

The image above shows the main Modeller Window. It has been cut up to reduce its size.

1. All Functions and Procedures of your App are shown in 2 list-boxes in the top left of the screen.
2. The central area of the screen holds your BusinessObjects
3. The Orixa System-Tables (Searches, Resources etc.) are held together on the top right of the system.
4. The "Types" and "Status" list-tables.
5. Specialized data-tables which are built into Orixa for content like Phones, Addresses etc.
6. The "Images system-table which is used to hold all pictures associated with data in your App.

 

Elements of the System Modeller BusinessObject window

System DB Modeller BusinessObject  

1. Name of the BusinessObject
2. Whether it is "M", "N", "C", "E", "H" for Master, Normal, Child, Extension and Hidden. Full details of the meanings of different BusinessObject types are covered in Help topics about developing your App, but are based on the common relationship-types used in database-development.
3. Letters are added to indicate which "Scripts" have been added to the BusinessObject. Each Script adds specific useful functionality in your App. Letters can include V, D, L, Di, S for View, Display, List, Diary and Summary scripts.
4. Each field in your BusinessObject's data-table is shown, with its name, data-type and data-definition. In the item shown in the image note that the value of the field is set using a function.
5. In front of each field-name there is either a "-" or a "+" The "+" indicates that this field is linked to some other field in the database by a constraint / relationship.
6. If fields are [GENERATED] or [COMPUTED] this is added. Note that the details of the code called by the GENERATE statment can be viewed by clicking the "+" on the edge of the screen.

Accessing Indexes, Triggers, Creation-script and other Attributes of your BusinessObjects

System DB Modeller data-table with Attributes  

Elements are added to the edges of the data-table display to indicate the presence of linked Attributes.

1. Click on the "+" in the top right corner of the data-table to open a view of its CREATE script.
2. If any field in the data-table includes SQL code to GENERATE or COMPUTE a value, then this SQL can be viewed here.
3. All INDEXES for a data-table are linked to "+" icons along the bottom of the object.
4. Constraints / Relationships that a data-table shares with others are indicated by blue crosses. If these are on the left side that indicates the table is a child / extension, if the crosses are on the right side that indicates that the table is a master / parent.

System Modeller Double Click to access BusinessObjects Edit Form  

You can directly access the BusinessObject details of any item in the model just by double-clicking on the object.

Accessing details of your DB Functions

 

System DB Modeller Functions  

1. The "Functions" section of the DB Modeller window will show all your App's database functions.
2. If you click on the "+" beside the name of the function a SQL window will open with the content of the function.
3. The SQL window containing the SQL in the function. Note that you can right-click this wiindow to access a context menu, which is detailed below.

 

Viewing Constraints and relationships in your App

System DB Modeller with Constraints Visible

When you open the System DB Modeller the backgound is white. If you select "Show Hide Constraints" (1) then lines will be drawn on the surface of the modeller showing the constraints. Constraints are colour-coded to aid visual interpretation. All constraints from the Types and Status tables are black. Links from "Master" BusinessObjects are shown thicker and in green. Other constraints are shown in a red/pink.

System DB Modeller Link Details

You can click on any Constraint link to see details of the constraint. In a larger model it can be useful to do this to see where a constraint from one data-table goes.

 

System DB Modeller Main Menu

System DB Modeller Main Menu  

Full details of each menu item are provided below.

1. Alter the scaling of the System Modeller.
2. Show the SQL Scripting window.
3. Open tools for generation of scripts to create a new BusinessObject.
4. Open the system Testing window.
5. Navigate around the model.
6. Jump to any object in the modeller.

 

System DB Modeller Script Window	The System DB Modeller can be used to generate SQL Scripts which can be run to extend and alter your system. From the Scripts menu (1), select "Show Hide script editing Window" (2) to open a script window on the left side of the screen. Note that any SQL shown in SQL windows on the main screen can be copied into the script window via the context menu of each SQL window (3).
System Modeller Automation Menu	The "Automation" menu gives access to tools which assist in the creation of new BusinessObjects. Note the "Create SQL ..." menu item (detailed below). "Generate default Searches ..." menu item will analyse the selected BusinessObject(s) and create SQL to insert records into the Searches system-table for commonly used and useful searches (such as "date-range", "recently edited" etc.)
System Modeller Testing Menu	The System DB Modeller Testing menu (1) opens the System Testing window (2). This will run SQL in all the Orixa System-Tables and check whether errors occur. Full details of how this works are covered in the following topic: The System "Run Tests" window
System DB Modeller Navigation menu	The Navigation menu (1) gives choices to use different mouse-based methods for moving around your model, and allows you to draw a link between 2 data-tables and auto-generate a SQL script to create a relationship / constraint between them. It also includes the option (2) to open a "mini view" of the whole model. Click anywhere on the mini-view and the the main diagram will jump to the same location.
System DB Modeller Jump to Products	The Jump To menu (1) automatically lists all data-tables in the model. Click on any one (2) and the model will automatically bring it into view.