Views

Views can be created with most RDBMSs. The Omnis data file does not support views.

A is created on the database and is much like a query class in Omnis Studio. A view can include a reduced set of columns from one table. A view can be used to join columns from multiple tables. A view can change the names of columns.

A following example creates a simple view on the database:

CREATE VIEW ViewName AS
SELECT ColName1, ColName2, ColName3
FROM TableName

A view which joins two table and changes then names of tow columns is as follows:

CREATE VIEW ViewName AS
SELECT ColName1, ColName2 AS NewColName2, ColName3 AS NewColName3
FROM TableName1, TableName2
WHERE TableName1.ColName_fkey = TableName2.ColName_pkey

You can get as complicated as you like with the SELECT that is used to create the view. It could include max() or min() functions, left joins, sub-selects, additional where text, etc.

For each view you create on the database, you need to create a schema class in Omnis Studio which maps to the view. The schema class columns must match the names of the columns in the view. The columns order doesn't matter.

What makes views extremely handy for Omnis Studio is that you can create a view of what would normally be an extremely complicated SELECT in Omnis Studio. Once the view has been created on the database and a schema class is mapped to it, there is no complicated SELECT code needed on the Omnis Studio side. To Omnis Studio the view just another schema class and you can use a simple WHERE clause without thinking about the complicated SQL that may have been used to create the view.

Views are primarily used to select data.

To work with views in StudioWorks:

1. Prefix the schema class name with lower case v rather than lower case s. e.g. vViewname
   
   Doing so lets StudioWorks know that the schema class is mapped to a view rather than an actual basetable.
2. Enter the CREATE VIEW statement in the meta-data of the schema class.
   
   If the CREATE VIEW statement is entered there the Database Administrator tool is able to create the view for you when you sync your app to the database.

Multiple Joins To One Table

In StudioWorks we use views for making more than one join to a parent table.

Suppose you have a Person table in your database which is a list of people.

Person_pkey
LastName
FirstName

You then create a Rel table which is used to indicated the relationships between different people in the Person table.

Rel_pkey
Person1_fkey
Person2_fkey
RelPerson1to2
RelPerson2to1

Person1_fkey and Person2_fkey are foreign keys which reference Person.Person_pkey

We are unable to create a query class in Omnis Studio to make more than one join to the Person table.

To solve the problem we can create two views as follows:

CREATE VIEW Person1 AS
SELECT Person_pkey, LastName AS Person1LastName, FirstName AS Person1FirstName
FROM Person

CREATE VIEW Person2 AS
SELECT Person_pkey, LastName AS Person2LastName, FirstName AS Person2FirstName
FROM Person

We then create the view schema classes as follows:

vPerson1 with the $servertablename set to Person1 and the columns as follows:

Person_pkey
Person1LastName
Person1FirstName

vPerson2 with the $servertablename set to Person2 and the columns as follows:

Person_pkey
Person2LastName
Person2FirstName

Do not change the primary key column name in the view if you wish to use the view for StudioWorks generated lookups.

We need to create the query class for listing and editing relationship records. The following columns are included in the qRel query class which we can use to list and edit the Rel table records.

sRel - Rel_pky
vPerson1 - Person1LastName
vPerson1 - Person1FirstName
vPerson2 - Person2LastName
vPerson2 - Person2FirstName
sRel - RolePerson1to2
sRel - RolePerson2to1
sRel - Person1_fkey
sRel - Person2_fkey
sRel - EditNum
sRel - ModBy
sRel - ModDateTime
sRel - InsBy
sRel - InsDateTime

The $extraquerytext to join the tables is as follows:

WHERE Person1_fkey = Person1.Person_pkey AND Person2_fkey = Person2.Person_pkey

StudioWorks knows from the meta-data that Person1_fkey references Person.Person_pkey and that Person2_fkey references Person.Person_pkey. But StudioWorks can not quickly determine that vPerson1 and vPerson2 are views of the Person table. We need to specifically enter vPerson1 and vPerson2 respectively as the Lookup SQL Class Name fields in the meta-data for the vPerson... columns in the qRel query class where we want StudioWorks to do lookup type-ahead for us.

The query column meta-data also has a Lookup Where field. You can enter an SQL where clause which you want StudioWorks to include when selecting the name to display in the lookup droplist. In this situation we don't want a relationship record which has the same person as Person1 and Person2. Adding following lookup where text in the qRel columns meta-data will solve the problem:

In the Person1LastName and Person2FirstName columns lookupwhere meta-data of qRel:

WHERE Person_pkey <> Person2_fkey

In the Person2LastName and Person2FirstName columns lookupwhere meta-data of qRel:

WHERE Person_pkey <> Person1_fkey

With all of the above in place, StudioWorks can autoconfig the PersonList, PersonEdit, RelList, and RelEdit window instances, successfully generate the droplists in the Person1 and Person2 lookup fields of the RelEdit window instance, and correctly set the foreign key values in the Rel table.

StudioWorks Documentation - Copyright 2005 Vencor Software