SQL Classes

File classes

The "File Class" is only used if you are working with the Omnis data file... and even then use of file classes is optional. The advantage of using file classes if you are using the Omnis data file is that you can check and reorganize the data file if you have file classes which are CORRECTLY mapped to your data file slots.

USING FILE CLASSES FOR STORING SQL TABLE INFORMATION

I read a posting by Reg Paling where he mentioned he was storing his SQL table information in the file classes. After testing the idea I found it worked very well.

If you are using the Omnis data file, there are added advantages in that you can change a field name or field name definition and then reorganize the Omnis data file, something you can't do with using OmnisSQL.

Using notation you can generate the CREATE TABLE and CREATE INDEX statements from the file classes. Using notation you can also get the tables and indexes from a SQL server and create the File Classes.

A WORD OF CAUTION: Omnis maps the file classes to the Omnis data file by row number in the file classes NOT field name. NEVER try to move the position of a field in the File Class if you are using the Omnis Data file. Your data will not be mapped correctly.

Do NOT leave empty rows in your file classes, they can give you grief later on. For those of us who like our field names list in a logically grouped and sorted order, forget it when it comes to SQL server tables. Just keep adding new fields to the end of the list. Do NOT reorder the fields in the File Classes.

The schema classes ARE mapped by name, so if you want to change the order of the column names in the schema classes that is no problem. Just drag the columns to whatever order you like. The F9 Catalog window will reflect the order in the schema class. But remember, the next time you 'Create a Schema from the Server' your nicely reordered list will be history.

Query classes

Query classes are used to join tables/table columns together. See Joining Tables. You can also use them for subsets of schema class columns, where the query class has less columns than the schema class.

I tend to use query classes for reports and for importing data from other data files.

The Omnis data file doesn't support "SQL Views", so query classes help to fill the void. Even if you are using a real RDBMS the query classes can be quite handy.

For example if I wanted to run a report for all the books in the books database and include the author name in the rerport I would need to join the "book" table and the "author" table. To do this with a query class I would create a query class named "qBooksAuthors" and drag and drop the columns from from the "s_book" schema class and from the "s_author" schema class in the F9 Catalog to the "qBooksAuthors".

Each query class has a multi-line entry field for text to be appended to queries. I would enter the following extra query text:

WHERE author.author_Key = book.author_Key

When we bind a list variable to the query class and issue a $select, Omnis Studio will automatically include the extra query text in the select statement.

Do List.$definefromsqlclass('qBooksAuthors')
Do List.$select()
Do List.$fetch(kFetchAll)

The select statement issued by Omnis Studio will look something like:

SELECT (query class column names) FROM author,book WHERE author.author_Key = book.author_Key

Schema classes

For every table in the database (server table), you must have a matching schema class. The schema class maps your Omnis Studio application to the server table.

You can manually type up your schema classes (the hard way). Using the SQL Browser you can drag and drop a server table onto your library (the easy way). You can using notation to create your schema classes (see $makeschema).

IMPORTANT: Make sure you have the primary key column in your schema class is set to kTrue. When I was learning Studio/SQL I couldn't figure out why I was able to $insert records, but not $update them. Eventually I discovered it was because I had not set primarykey=kTrue in my schema class. Studio needs to know the primary key column in order for the built in $update method to work.

FOREIGN KEYS

Unfortunately the schema classes are not set up to store information about foreign keys. If you wish to generate 'CREATE TABLE' statements from your Studio application you need to store this information somewhere. A work around which I use is to add @TAGs to the schema column descriptions.

For example, the "book" server table has a column "author_Key" which is the foreign key to the "author" server table's primary key column "author_Key". In my application I would have a schema class called "s_book" which has its $servertablname property set to "book". "s_book" would have a column "author_Key" and in the description column you would find the tag "@FK:author.author_Key,R". The tag reads: I am a foreign key column, pointing to the "author_Key" column of the "author" server table and the relationship is "restricted" meaning you can't delete my parent if I exist. The opposite to "restricted" is "cascading". With a "cascading" relationship child records would automatically be deleted with the parent record. The Omnis data file does not support restricted and cascading, you must enforce this with your own table class code.

See SQL Assist for more information on @TAGs and generating CREATE/ALTER TABLE statements from schema classes.

INDEXES

If you wish to generate 'CREATE TABLE' statements from your Studio application you need to store information about the indexs somewhere in your application. A work around which I use is to add @TAGs to the schema column descriptions.

@IX means this is an indexed column.
@UIX means this is a unique index column.

Table Classes

If you are using SQL, then you will have a schema class for each table on your SQL server.

Omnis's schema classes have some built in methods such as: $select, $fetch, $insert, $update, $dowork. You can then use $definefromsqlclass to bind a list or row variable to the schema class and use those built in schema class methods. (From what I understand in the background Omnis binds its own default table class to your row or list variable.)

You can also create your own table class linked to a schema class (or query class) and then $definefromsqlclass to bind a list or row variable to your table class (rather than directly to the schema class). When learning Studio, I thought why bother? That seems like exta work. Then at the Omnis conference in Denver my eyes were opened to the power of creating custom table class methods. WOW! Table classes are powerful!

I recommend you create a matching table class for EVERY schema class. Do ALL your data fetching, checking, inserting, updating, etc. through your table classes. This is the best point of data control. It will take a lot of the 'load' (and code) off your windows and objects.

TABLE CLASSES - BRIEF EXPLANATION

When you $definefromsqlclass a list or row with a table class, Omnis "binds" your table class to the list or row variable.

Do LISTNAME.$definefromsqlclass("[LibName.] TableClassName")

I normally prefix my schema class with "s_" (s_ServerTableName)
and my table classes with 't_' (t_ServerTableName)
Note: ServerTableName is the table name in the database, not to be confused with table class name.

After you bind your table class to the list or row variable using $definefromsqlclass all the public methods (methods starting with $) in the table class are now directly available to your list or row variable.

Do List|Row".$TableMethodName (parameters) Return FlagOK

Think of the table class as an object class specially designed for working between your list or row variable and the server table in the database. If the table class was an object class the above Do statement would have read:

Do oObjectClass.$ObjectMethodName (parameters) Return FlagOK

Plus you would have to send the list or row variable along as a Field Reference parameter. Not so with a list or row bound to a table class.

Another thing I like about the table class is that calculations inside the table class methods do not have the list or row prefix. Outside the table class methods you might do something like:

Calculate $cinst.iList.ColumnName as 10

Inside the table class methods you would simply do:

Calculate $cinst.ColumnName as 10

That's because your list or row and the table class are 'one and the same', they've been "bound" together.

The other great thing about table classes is you can intercept the $update, $insert, $delete, $dowork, and other Omnis hidden table class methods. You do this by simply adding a method by the same name.

In my $insert and $update I first call a table method which I name $checkData. For each table it makes sure the data is acceptable before it can be saved to the database. If $checkData doesn't pass on any column it gives an OK message stating the problem, and Quit method kFalse. Otherwise it returns kTrue and $update then does 'Do default'.

TABLE CLASSES - INHERITANCE USING BASE SUPERCLASSES

You should create a base superclass table class, make it a supercomponent, and then whenever you create a new SQL table/schema, drag the base superclass table class out of the Component Store and then name and point it to your new schema class.

In the base table superclass create your own generic methods.

I have included in StudioTips my base superclass table class for your review. The methods are also listed under their own tab strip heading "Table Classes."

When it comes to SQL there are so many different ways to do things, everyone has their own style and issues that they have to address, so just look at my table class methods for ideas. I do not intend to state that the methods I use are the best way to do things. At the time of writing this my experience and scope is mainly with OmnisSQL.

If you have any table class methods or ideas that you find work very well or could improve on ones that I've listed, I'd love to hear from you. table classes are powerful! They are a very compelling reason for using SQL rather than DML. Use table classes to your fullest advantage.

TABLE CLASSES - $sessionobject

$sessionobject is a table class runtime property. Runtime properties can only be set using $assign on a instance of the table class. The Studio manual defines $sessionname as follows:

A row or list variable defined from a SQL class has the $sessionobject property which is the session object that is used by the table. For a new table instance $sessionobject is initially empty. The $sessionobject may be assigned in the table class $construct method or elsewhere.

So, if you assign the $sessionobject in the $construct of your table class, you never have to worry about using the correct session (database) before reading or writing to the data file. Every time you use the list or row variable bound to that table class instance, Studio will automatically use the $sessionobject for that table class.

StudioTips Documentation - Copyright 2006 Vencor Software