SQL Classes

Schema Classes

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

• The $servertablename property of the schema class must match the table or view name in the database.
• The column names of the schema class map to the column names of the table or view in the database. It is possible to have extra columns in table or view in the database. It is not possible to have extra columns in the schema class.
• The data type of each column in the schema class must correctly match the data type of the column in the table or view.

If you have a database with existing tables you can open a session with the database and then drag the tables and views from the session in the SQL Browser to your library. Omnis Studio will automatically create a matching schema class for each table and view that you drag onto your library.

You can also use notation to create the schema classes (see Session Objects > $makeschema).

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. Omnis 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.

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. In StudioWorks we store information about the foreign keys in the $userinfo proper of each foreign key column.

In StudioWorks we store information about the foreign keys and/or indexes in a row variable and then copy it to the $userinfo property of the applicable schema class column. We refer this information as meta-data. We are then able create and alter tables, indexes, and columns in the database by comparing the database structure to the schema classes and their meta-data.

Schema Class Indexes

Unfortunately the schema classes are not set up to store information about indexes. If you wish to generate CREATE INDEX statements from your Omnis Studio application you need to store information about the indexs somewhere. A work around which I have used is to add @TAGs to the schema column descriptions.

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

I then use notation to generate the CREATE INDEX statements from the @IX and @UIX tags in the schema column descriptions.

The following sample code gives you an idea how this can be accomplished.

; Set a reference to the schema class.
Do $clib.$schemas.$findname('sBook') Returns rSchema
If rSchema
   
   ; Make a list of the column names and descriptions.
   Do rSchema.$objs.$makelist($ref().$name,$ref.$desc) Returns ColsList
   Do ColsList.$cols.1.$name.$assign('colname')
   Do ColsList.$cols.2.$name.$assign('coldesc')
   
   ; Search and keep any columns with an @FK tag in the desc.
   Do ColsList.$search(pos('@IX',$ref.coldesc)>0|pos('@UIX',$ref.coldesc)>0)
   Do ColsList.$remove(kListKeepSelected)
   
   Calculate TableName as rSchema.$servertablename
   
   ; Prepare the SQL text for adding foreign keys. (Varies for each RDBMS)
   Calculate Template as 'CREATE UNIQUE INDEX <index-name> ON <table-name> (<column-name>);'
   Calculate Template as replace(Template,'<table-name>',TableName)
   
   ; Loop through the @ tags adding the indexes.
   For ColsList.$line from 1 to ColsList.$linecount step 1
      
      Calculate SQLText as Template
      
      ; Parse the @UIX or @IX tag in the column description
      Calculate String as ColsList.coldesc
      If pos('@UIX',String)
         Calculate String as trim(mid(String,pos('@UIX:',String)+5))
      Else If pos('@IX',String)
         ; Remove UNIQUE from the SQL text.
         Calculate SQLText as replace(SQLText,'UNIQUE','')
         Calculate String as trim(mid(String,pos('@IX:',String)+4))
      End If
      
      ; Calculate the index name.
      If pos(low(TableName),low(ColsList.colname))=1
         Calculate IndexName as ColsList.colname
      Else
         Calculate IndexName as con(rSchema.$servertablename,'_',ColsList.colname)
      End If
      
      ; Prepare the SQL script.
      Calculate SQLText as replace(SQLText,'<index-name>',IndexName)
      Calculate SQLText as replace(SQLText,'<column-name>',ColsList.colname)
      
      ; Execute the SQL script.
      Do StmntObj.$execdirect(SQLText) Returns FlagOK
      If not(FlagOK)
         Break to end of loop
      End If
      
   End For
   If not(FlagOK)
      OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]////while attempting to execute the SQL script:////[SQLText]}
   End If
End If

Quit method FlagOK

If you want composite indexes, you can get fancy by adding an index name to the index tag. Using the same index name for more than one column would indicate that those columns must be combined into a composite index.

Schema Class 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 Omnis Studio application you will need to store the information about foreign keys somewhere. A work around which I have used is to add @TAGs to the schema column descriptions.

For example, the Book servertable has a column Author_fkey which is the foreign key to the Author servertable's primary key column Author_pkey. In my application I would have a schema class called sBook which has its $servertablename property set to Book. sBook would have a column Author_fkey and in the description column you would find the tag @FK:Author.Author_pkey,R. The tag reads: I am a foreign key column, pointing to the Author_pkey column of the Author servertable, 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 when their parent record is deleted.

I then use notation to generate the ALTER TABLE TableName ADD FOREIGN KEY... statements from the @FK tags in the schema column descriptions.

The following sample code gives you an idea how this can be accomplished.

; Set a reference to the schema class.
Do $clib.$schemas.$findname('sBook') Returns rSchema
If rSchema
   
   ; Make a list of the column names and descriptions.
   Do rSchema.$objs.$makelist($ref().$name,$ref.$desc) Returns ColsList
   Do ColsList.$cols.1.$name.$assign('colname')
   Do ColsList.$cols.2.$name.$assign('coldesc')
   
   ; Search and keep any columns with an @FK tag in the desc.
   Do ColsList.$search(pos('@FK:',$ref.coldesc)>0)
   Do ColsList.$remove(kListKeepSelected)
   
   ; Prepare the SQL text for adding foreign keys. (Varies for each RDBMS)
   Calculate Template as 'ALTER TABLE <table-name> ADD FOREIGN KEY (<column-name>) REFERENCES <refdtable-name> (<refdcolumn-name>) <cascade-restrict>;'
   Calculate Template as replace(Template,'<table-name>',rSchema.$servertablename)
   
   ; Loop through the @FK tags adding the foreign keys.
   For ColsList.$line from 1 to ColsList.$linecount step 1
      
      ; Parse the @FK tag in the column description
      Calculate String as ColsList.coldesc
      Calculate String as trim(mid(String,pos('@FK:',String)+4))
      Calculate RefdTable as trim(strtok('String','.'))
      Calculate RefdCol as trim(strtok('String',','))
      Calculate CascadeRestrict as mid(String,1,1)
      
      ; Prepare the SQL script.
      Calculate SQLText as Template
      Calculate SQLText as replace(SQLText,'<column-name>',ColsList.colname)
      Calculate SQLText as replace(SQLText,'<refdtable-name>',RefdTable)
      Calculate SQLText as replace(SQLText,'<refdcolumn-name>',RefdCol)
      Calculate SQLText as replace(SQLText,'<cascade-restrict>',CascadeRestrict)
      
      ; Execute the SQL script.
      Do StmntObj.$execdirect(SQLText) Returns FlagOK
      If not(FlagOK)
         Break to end of loop
      End If
      
   End For
   If not(FlagOK)
      OK message SQL Error (Icon) {$nativeerrortext = [StmntObj.$nativeerrortext]////while attempting to execute the SQL script:////[SQLText]}
   End If
End If

Quit method FlagOK

The Omnis data file does not support foreign keys nor the restricted and cascading constraints. You must enforce this with your own table class code.

Query Classes

Query classes are used to join tables/table columns together. See the topic Joining Tables. You can also use query classes 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 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 sBook schema class and from the sAuthor schema class in the F9 Catalog to the qBooksAuthors query class.

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_fkey = Author_pkey

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.$sessionobject.$assign(sessobj)
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_fkey = Author_pkey

You can not $update or $insert or $dowork from a query class. Don't blame Omnis. That's a SQL ANSI standard thing. It is too complex for SQL to deal with the connections, primary keys, foreign keys, etc from multiple tables/columns joined together in single variable. You have to work that code out yourself.

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 Omnis binds its own default table class in the background to your row or list variable.

You can also create your own table class and point it to a schema class (or query class). You set the table class' $sqlclassname property to point it to a schema or query class. You then use $definefromsqlclass to bind a list or row variable to your table class (rather than directly to the schema class). When learning Omnis Studio, I thought why bother? That seems like exta work. Then at an Omnis conference 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 window classes and object classes.

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

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

or

Do List.$definefromsqlclass(rSchema)

I normally prefix my schema class with the letter s. (e.g. sServertablename) and my table classes with the letter t. (e.g. tServertablename)

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.$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 servertable in the database. If the table class was an object class the above Do statement would have read:

Do oObjectClass.$ObjectMethodName(parameters) Return FlagOK

WIth an object class 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. The table class instance and the list or row variable are so tightly bound that the two become one.

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 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.

Table Class $sessionobject

$sessionobject

is a table class runtime property. Runtime properties can only be set using $assign on a instance of the table class. The Omnis Studio manual defines $sessionobject 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 following sample code defines and binds a list variable to a table class and then sets the session object using sessobj, a task variable which is used to logon to the database during startup.

; Define the list variable using a table class.
Do List.$definefromsqlclass('tAuthor')

; Set the session which the list is to use.
Do List.$sessionobject.$assign(sessobj)

Table Class Superclasses

You should create a base superclass table class, make it a supercomponent, and then whenever you create a new SQL table/schema make it a subclass of the superclass.

In the base table superclass create your own generic methods. Here's an example of a $getAllRecords method which combines the $select and $fetch for you.

; Select and fetch all the records in the table.
Do $cinst.$select() Returns FlagOK
If FlagOK
   Do $cinst.$fetch(kFetchAll) Returns FetchStatus
   If FetchStatus=kFetchError
      Calculate FlagOK as kFalse
   End If
End If
Quit method FlagOK

File Classes

If you are not using the Omnis data file, skip this topic.

The File Class is only used if you are working with the Omnis data file... and even then use of file classes is optional.

If you want to change the data type of a column in a table in an Omnis data file you need a file class which is correctly mapped to the data slot in the Omnis data file.

I discovered an undocumented way to create a file class that is mapped to an existing data slot. The trick is to open the data file and then copy the $datadict of the data slot to the $classdata of the file class. The following sample code shows how to do it.

; Get the path to the books demo data file.
Do pths.$:BooksDemoDataFile() Returns DataFilePath
If len(DataFilePath)
   
   ; Open the data file
   Open data file (Do not close other data,Read-only Studio/Omnis 7,Convert without user prompts) {[DataFilePath],BooksDataFile}
   
   ; Find the data slot.
   Calculate TableName as 'book'
   Do $datas.BooksDataFile.$slots.$findname(TableName) Returns rSlot
   If rSlot
      
      Do $clib.$files.$findname(TableName) Returns rFile
      If rFile
         Do $clib.$files.$remove(rFile)
      End If
      Do $clib.$files.$add(TableName) Returns rFile
      If rFile
         
         Do rFile.$classdata.$assign(rSlot.$datadict)
         
         Close data file {BooksDataFile}
         
         Modify class {[rFile().$name]}
      End If
   End If
End If

You can then change the data type of the column in the file class and reorganize the data file for that file class.

StudioTips Documentation - Copyright 2006 Vencor Software