The relational engine is the backbone of dbiScript; it enables dbiScript to instantly adapt to any relational model, making data models such as entity-relationship diagrams obsolete.

This incredible technology drastically simplifies the arduous task of creating a custom database application.

GUI Generation

The application's graphical user interface is generated on-the-fly, whenever the browser is refreshed or a user logs in.

The GUI is presented as a series of page frames which are generated in a just-in-time (JIT) manner, following these basic steps:

  1. The relational model is scanned for tables which do not have parent (foreign) tables. These tables form the tabs of the initial page frame.
  2. When a record is selected in a tab of the initial page frame, the relational model is scanned for all descendant tables of the selected table. These tables form the tabs of the second page frame, which is displayed beneath the initial page frame.
  3. When a record is selected in a tab of the second page frame, the procedure of step 2 is repeated to generate a third page frame.

Each successive page frame will have fewer tabs than the previous one. Eventually, there will be no more descendant tables to display and selecting a record does not generate a new page frame. The number of steps it takes to get to this point depends on the complexity of the relational model and the path the user has chosen to take through the data tables.

The steps outlined above are only the basic steps taken to produce the application GUI. We dig into the more complex steps later on (future link).

The key takeaway is that dbiScript does all the heavy lifting.

  • Every custom enterprise database application has a unique relational model.
  • The relational engine instantly adapts to any relational model.
  • Users have access to every possible path through the data tables of the relational model.
  • CRUD (Create, Update, and Delete) functionality is provided for every table of every page frame.

A particularly noteworthy benefit of the relational engine applies to large enterprise applications:

  • System administrators can specify tables which individual users or groups of users do not have access.
  • At login, the GUI is generated for the user as if these tables and their descendant tables do not exist in the application.
  • A single dbiScript enterprise application can be configured to deliver a separate GUI to, say, the HR and Sales departments just by specifying group permissions.

Application Skeleton

Creating an application skeleton is the best way to start your dbiScript application. The application skeleton is a prototype of a full dbiScript application.

  • A dbiScript application skeleton is working software. No data modeling required!
  • It establishes the data flow (navigation from table to table) of the dbiScript application and enables problems in the relational model to be quickly spotted and fixed.
  • It provides access to every possible pathway through the tables in the application, just like a fully developed dbiScript application.
  • It's quickly created by adding tables to the application and specifying the relationships between them.
  • A skeleton for database with 100 tables can be completed in about an hour.
  • Add or remove tables and fields as necessary. Change the relationships at any time. Just refresh the browser to get the current GUI.

Adding a Table

Adding a table to the dbiScript application is as simple as:

var o = app.gui.appendDataTable("order","Orders","Order");
o.appendData("orderDate","Order Date");

The parameters of the appendDataTable function are

  • Name of the table in the database
  • Plural form of the display name (text of the table's tab in the page frame)
  • Singular form of the display name (for communication with the user via dialog boxes)

The parameters of the appendData function are

  • Name of the field (column) in the database
  • Display name of the field

Each table needs to have at least one field added to it so there's some data to display. The "Orders" table does not have any parent (foreign) tables and would be displayed in the initial page frame of the dbiScript application.

Specifying the Relationships

Single-Parent Relationships

The optional fourth parameter of the appendDataTable function specifies the foreign relationships between the specified table and other tables in the database.

Adding a table with a single parent (foreign) table is as simple as:

var o = app.gui.appendDataTable("orderitem","Order Items","Order Item","order:orderid");
o.appendData("quantity","Quantity");

In this example, we're indicating that the "orderitem" table contains a foreign key ("orderid") to the "order" table.

With this relationship defined, the "Order Items" table is not displayed in the initial page frame. When a user selects a row in the "Orders" table, a second page frame containing the "Order Items" table is displayed below the initial page frame, and only those rows related to the selected "Order" row are displayed in the "Order Items" table:

Order + Order Items Example

Note that the "Users" and "Groups" tabs are part of every dbiScript application; dbiScript automatically generates and maintains the underlying tables for these tabs.

Multiple-Parent Relationships

The fourth parameter of the appendDataTable accepts a comma delimited list of relationships:

app.gui.appendDataTable("patpolicymst","Patient Policies","Patient Policy","patient:patientid,policymst:policymstid");

In this example, we're indicating that the "patpolicymst" has two foreign keys; "patientid" to the "patient" table and "policymstid" to the "policymst" table.

This example is taken from the Clinic sample application which is included in the download.

Reference Fields

Reference fields provide an alternative method of specifying a relationship between tables. The reference field provides a lookup type of interface which is used to select a row from the foreign table. The reference field is discussed in more detail below.

GUI Navigation

Navigation through a dbiScript GUI is determined by the relationships established between the tables of the application. As shown above, these relationships are created when tables are added to the application and also when reference fields are added to tables in the application. The table-defined relationships have a greater effect than the reference field-defined relationships on the navigation pathways between tables in the dbiScript GUI.

When creating your application's GUI, dbiScript takes into consideration all the defined relationships; these relationships effectively form the data model of your application. Sophisticated algorithms are used to analyze the data model at every step through the application GUI. When a row is selected in a table, these algorithms identify all the possible direct and indirect pathways through the data model from this point. A child page frame is created to provide access to all the tables which are identified as possible "next steps" through the data model. This child page frame is created "just-in-time" the first time a row is selected in the parent table. When a row is selected from a table in the child page frame, the process is repeated. Eventually, there will be no more tables to navigate through and selecting a row does not produce a new child page frame.

Each data model is unique and produces entirely different results. As you build your dbiScript application, the complexity of the data model increases exponentially. dbiScript delivers a powerful yet elegant GUI which gracefully masks the true complexity of the underlying data model.

Skeleton Example

Data Model

Let's take a look at the dbiScript application skeleton for the following data model

Clinic ER Disgram

This is a partial model of the Clinic sample application from the download.

dbiScript Code Skeleton

We'll add each table in the model to the dbiScript application along with a minimum number of fields.

app.addDefinitionTable("dfnmst","descr","srcdbf","srcfld");

var o = app.gui.appendDataTable("patient","Patients","Patient");
o.appendData("fname","First Name");
o.appendData("mi","MI");
o.appendData("lname","Last Name");

o = app.gui.appendDataTable("insmst","Insurance Companies","Insurance Company");
o.appendData("name","Company Name");

o = app.gui.appendDataTable("policymst","Insurance Policies","Insurance Policy","insmst:insmstid");
o.appendReference("patientid","Policy Owner","patient",["lname","fname","mi"]);

o = app.gui.appendDataTable("patpolicymst","Patient Policies","Patient Policy","patient:patientid,policymst:policymstid");
o.appendSelect("relationid","Relation to Policy Owner",["dfnmst","POLICYMST","RELATIONID"]);
  • This dbiScript code produces this dbiScript application skeleton. No other code required.

dbiScript Application Skeleton

Clinic Application Skeleton 1

Let's take a closer look at this screen shot. It took a few steps to get to this point. When the application first loads, only the first page frame is displayed. From that point:

  1. Patient Brian Boyd is selected.
  2. dbiScript displays the second page frame.
  3. The patient table is minimized (icon at the right of the toolbar).
  4. The Golden Rule insurance company is selected.
  5. dbiScript displays the third page frame.
  6. The insurance company table is minimized.
  7. The Brian Boyd insurance policy is selected.
  8. dbiScript displays the fourth page frame.

A few noteworthy items about this application skeleton:

  • In the first page frame, dbiScript displays (by default) only those patients which have an existing patient policy.
  • To change the display to all patients, click the check mark button in the table's left column.
  • The row's check mark indicates that the displayed patient has an existing patient policy.
  • The same logic applies to the second and third page frames of this skeleton
  • In more complex relational models where the check mark button affects the display of more than one multi-parent child table, the button toggles between "any of" and "none of" the related tables and there is a context-sensitive (right-click) menu available for selecting just one of the multi-parent child tables.
  • Each of the background tabs provides access to another pathway through the data.

We'll get back to the other pathways through the data in a just a bit. First let's take another look at the relationship in the "patpolicymst" table. One of the business rules for this particular example is that a patient can only have one patient policy with an insurance policy. In other words there can only be one "patpolicymst" row for each unique combination of "patient" and "policymst" foreign ids. In dbiScript, we specify this by setting the 6th parameter of the appendDataTable function (Maximum # of Rows) to 1.

We change this line of code (from above):

o = app.gui.appendDataTable("patpolicymst","Patient Policies","Patient Policy","patient:patientid,policymst:policymstid");

To this (change is at the end of the line, in bold text):

o = app.gui.appendDataTable("patpolicymst","Patient Policies","Patient Policy","patient:patientid,policymst:policymstid",0,1);

Once we refresh the browser and navigate back to the same point, we now have this:

Clinic Application Skeleton 2

To dbiScript, the change we made means it can "pull" the patient policy row up into the insurance policy page frame.

Every table which has its maximum number of rows parameter set to 1 will be similarly "pulled" up into the parent row. If we go back to our "Orders" -> "Order Items" example and set the maximum number of rows parameter to 1 in the "Order Items" table, the "Order Items" would be "pulled" up from their own page frame into the page frame of "Order" table. This doesn't make sense for the "Orders" -> "Order Items" scenario, but there are many scenarios where this type of relationship works well. These types of tables are basically optional groups of information for their parent table.

So let's go back to discussing the alternate pathways through this example and take a look at this screen shot:

Clinic Application Skeleton 3

Just like the previous screen shots, it took a few steps to get to this point. Starting from when the application first loads:

  1. The Insurance Companies tab of the first page frame is selected (clicked).
  2. The Golden Rule insurance company is selected.
  3. dbiScript displays the second page frame.
  4. The insurance company table is minimized (icon at the right of the toolbar).
  5. The Brian Boyd insurance policy is selected.
  6. dbiScript displays the third page frame.
  7. Patient Brian Boyd is selected.
  8. dbiScript displays the fourth page frame.

Fleshing Out the Application

Once you're satisfied with your skeleton, it's time to start fleshing out your dbiScript application. Most tables have an inline (grid) section and an optional drilldown (floating) section. The drilldown fields are displayed only when a row has been selected or pinned.

Clinic Fleshed Out Patient Table

The following 30 field types are available to choose from when adding fields to your tables:

  • Age - Displays the current age (e.g. 7 years) based on a DateTime field
  • Aggregate - Displays an aggregate value based on one or more fields in a descendant table
  • AggregateMoney - Displays an aggregate value based on one or more fields in a descendant table and formatted as currency
  • AjaxButton - Submit data to a web service and process the returned data
  • Boolean - Two and three value (NULL where supported in the DB) Boolean data
  • Button - A button with an onclick data-driven function (e.g. use to open a new browser tab to map an address)
  • CopyButton - Copy a row (and optional descendant rows) to another table in the database
  • DateTime - Date and time data with a popup calendar
  • Email - Email links in display mode, email validation in edit mode
  • FileVault - A file repository for a single file with history and check in / out privileges
  • Iconlist - A list of icons, manually or through a
  • MaskedString - For data like phone numbers; for example, display (416) 555-1212 but store 4165551212
  • Memo - Long string data, edited in a textarea of the drilldown
  • MemoPreview - A shorter read-only preview of a memo field displayed as an inline field
  • Money - Numeric data formatted as currency (e.g. $100.00)
  • MoveButton - Move a row (and optional descendant rows) to another table in the database
  • Number - Unformatted numeric data
  • Percentage - Numeric data formatted as a percentage (e.g. 25%)
  • PhotoAlbum - Upload and manage a group of photos
  • Picklist - Choose a list of items from a large list of items stored in a helper table
  • Quickpick - Chose a single value, narrowed down as you type, from a large list of items stored in a helper table
  • Radio - Chose a single value from a list of radio options populated from a hard-coded list or a helper table
  • Reference - Chose a row from another table in the database
  • RowState - Toggle through a set of row states (icons) by clicking a button
  • SectionHeader - Place a dividing line and section caption in the drilldown section
  • Select - Chose a single item from a small list of items presented in a select element
  • String - Unformatted string data
  • Treeview - Select an item from a large list of items stored in a helper table and organized into a tree
  • URL - URL links in display mode, URL validation in edit mode
  • WebServiceRequest - Submit data to a web service and display the returned data (e.g. Stock Price above)

Reference Fields

The Reference field is a special field type which enables the user to select a row from another table. For example, in the Clinic sample application, the patient table has reference fields for the "primary physician", "referring physician", "attorney", and "referring patient" fields. Each of these fields enables the user to select a row from a foreign table in the database and display customized identifying information about the foreign row in the field's space within the patient row. Additionally, the reference field includes a toggle button which controls the display of all the data from the foreign row in a space beneath the drilldown of the patient row.

The reference field includes a button Reference Field Display Foreign Row Toggle Button to toggle the display of the foreign row. When the foreign row is displayed, it appears beneath the active row. With the toggle on, foreign rows are displayed for the active row and for pinned rows.

Let's add a reference field for the Referring Patient to the patient table of our skeleton:

var o = app.gui.appendDataTable("patient","Patients","Patient");
o.appendData("fname","First Name");
o.appendData("mi","MI");
o.appendData("lname","Last Name");
o.drilldown.appendReference("refpatientid","Referred By","patient",["lname","fname","mi"]);

Note that while, in this case, we're referencing the same table which the field belongs to (patient), the referenced table can be any other table in the application.

Here's a screenshot of our skeleton application with the foreign row display toggle off:

Reference Field Foreign Row Hidden

And another shot of the application with the foreign row display toggle on:

Reference Field Foreign Row Displayed

Another effect of the relational engine can be seen in the second page frame. In this case, dbiScript has automatically added a tab for patients which were referred by the selected patient. Here's a shot of that tab in action:

Reference Field Foreign Row Displayed

The caption of the "Referred By" tab is explicit for this "self-referencing" type of reference field in order to explain the purpose of the tab. It wouldn't be obvious if the caption was just "Patients". The tab's caption is implicit for reference fields which reference another table in the application. For example, our fleshed out Clinic application includes a reference field for the Patient's Primary Physician which references the sdrmst (MDs) table. For this reference field, dbiScript knows that whenever the MDs table is displayed it needs to include a tab in the child page frame for the patients who have the selected doctor as their primary physician and that the purpose of the tab is obvious when its caption is "Patients".

Search

The search dialog enables users to filter records by any criteria.

A single conditional statement:

The list of available conditional operators:

Two conditional statements joined by an AND operator:

Two conditional statements joined by an OR operator:

Three conditional statements joined by a combination of AND / OR operators:

The use of brackets to change the logical order of operations:

The additional field-level conditions for descendant tables:

The Group By options available to aggregate conditionals (e.g. Number of Rows):

The optional conditionals (HAVING clause of the SQL statement) for aggregate conditionals (e.g. Number of Rows):

The aggregate functions available to numeric fields in descendant tables:

The Group By options available to aggregate conditionals (e.g. Sum(Patient Charge)):

The optional conditionals (HAVING clause of the SQL statement) for aggregate conditionals (e.g. Sum(Patient Charge)):

Examples of validation errors - the errors are displayed after the OK button is clicked: