Table Class Structure

SQL92 is the standard which RDBMSs are supposed to comply with, however not all RDBMSs are fully SQL92 compliant and there are operations like getting the next primary key for a new record which are not part of the SQL92 standard.

The Omnis data file is only partially SQL92 compliant. It doesn't support foreign keys, restricted and cascading deletes, using bind variables with LIKE operators. PostgreSQL uses ILIKE rather than LIKE for case-insensitive selects. The list of SQL variances goes on an on.

In order to deal with SQL variances between different RDBMSs we needed to develop a class structure that is easy to extend and maintain.

Omnis Studio table classes are specially designed to work with databases. Table classes come with a number of built in methods to make life easier for developers. To use a table class you set its $sqlclassname property to a schema or query class and then instantiate and bind it to a list or row variable using the $definefromsqlclass method.

Do List.$definefromsqlclass('SQLClassName')

A base table class can be created and then subclassed for different schema or query classes. Custom methods can be added to the subclasses.

However, we run into problem with this superclass/subclass structure when we attempt to deal with SQL variances for the different RDBMs. A switch/case would have to be added to every method where a SQL variances would affect code execution. Not a good situation.

The solution is to use the bridge design pattern. When the base table class is initiailzed it instantiates an oTableClassMethods_DBMSVENDOR object class for the DBMS vendor of the database that we are connected to. All messages received by the base table class are forwarded to the matching oTableClassMethods_DBMSVENDOR method.

For some of the table class methods we would like to be able to access them outside of the table class instance/structure.

1. Primary key related methods are needed by the oDBAdmin classes.
2. Check data related methods could be needed by some window classes or object classes.

To make it easy to access and modify those methods we moved them to other object classes where they can be called by either the table class methods object, the DB Admin classes, or some other class.

1. Primary key related method are put in oPrimaryKey_DBMSVENDOR object classes.
2. Check data related methods are put in oCheckData_Tablename object classes.

The following diagram gives you an overview of the StudioWorks Table Class structure.

Table Class Methods Objects

In StudioWorks you should always get your SQL defined lists from the $retDefinedList method of oSQLLists.

Do lsts.$retDefinedList('SchemaOrQueryName') Returns List

The $retDefinedList method not only does the $definefromsqlclass method with tBase for you, it also sets the $sessionobject property, and initializes the table class instance sending it a number of parameters that are required for the tBase instance to work properly.

When the SQL defined list receives the $initialize message for $retDefinedList it instantiates the correct oTableClassMethods_DBMSVENDOR object class and sends the object an $initialize message that includes the table class instance.

Do ioMethods.$initialize($cinst,...)

oTableClassMethods sets its irListRow ivar to reference the table class instance.

Set reference irListRow to prTableClassInst.$ref

This is important to know when you are looking at oTableClassMethods methods. irListRow is a reference to the SQL defined list or row variable. The table class is so tightly bound to the list or row variable that the two are one. (Sounds like a marriage ceremony.)

You can override the StudioWorks oTableClassMethods_DBMSVENDOR series objects by creating your own subclasses with the same names in your main library. tBase will first look in the startup task library before using its own objects.

You can also subclass tBase to your own module and rename the subclass to tSchemaOrQueryName and override and set the $sqlclassname property to point to the appropriate schema or query class. The $retDefinedList method will use the subclass tBase2 in the module when requested to define a list for that schema or query class. You can add custom methods to the subclass. You could override the superclass methods... but this breaks the bridge pattern to the oTableClassMethods_DBMSVENDOR object for that method.

Primary Key Methods Objects

The methods which deal with setting primary keys are in an oPrimaryKey_DBMSVENDOR series of object classes. This makes it easy for the oDBADmin objects and the oTableClassMethods objects to access the same code.

For each of the oPrimaryKey methods the sender must pass in a statement object which the the method can use for executing its SQL.

oPrimaryKey_abstract is subclassed for each DBMS vendor. The uppercase of DBMS vendor's name (not the DAM name) is used as the suffix of the subclass.

For some DBMS vendors (PostgreSQL, FrontBase, SQLServer) the DBMS has primary key counter methods, for other DBMS vendors (Omnis data file, MySQL) the Refs table is used for the primary key counters. What gets used for the primary key counter is decided by the oPrimaryKey_DBMSVENDOR subclass object.

You can override the StudioWorks oPrimaryKey_DBMSVENDOR subclass object by copying or subclassing it to your main library. The subclass in your main library with the same will supercede the one in swBase4.

Check Data Methods Objects

The methods which deal with checking data are in an oCheckData series of object classes. This makes it easy for the window classes, object classes, or oTableClassMethods objects to access the same check data code.

It also makes it easy for schema and query classes using the same base table to access the same check data code.

You can create an oCheckData_Tablename subclass of oCheckData in the module where the schema class for that table class exists.

The subclass must be created in the same library as the base schema class.

The subclass suffix must be the cap(Tablename) of the servertablename. e.g. oCheckData_Contact, not oCheckData_contact

For example you may want to have the a Contact table with an Email column. For uniformity you want all email address to be lowercase characters. You also want to do some basic checking on the email address format to make sure it is valid. To check/fix the Email column you would do the following:

1. Create a subclass of oCheckData.
   
   F2 Browser > select the target library > Class Wizard > Object > Subclasses > select oCheckData
   
   Enter Class Name: oCheckData_Contact
   
   Click the Create Button
2. Add a $check_Email method to the oCheckData_Contact class.
3. Add appropriate code to the method. For example:
   
   

   If len(irListRow.EmailAddr)=0
      
      ; Empty email address is not an error for this table.
      Calculate FlagOK as kTrue
      
   Else
      
      ; Set the email address to lowercase.
      Calculate irListRow.EmailAddr as low(irListRow.EmailAddr)
      
      ; Check the format of the email address.
      If pos('@',irListRow.EmailAddr)=0
         Calculate Mssg as "The email address must include an @ character"
         Calculate Dtls as ''
      Else If pos('@',irListRow.EmailAddr)=1
         Calculate Mssg as "The email address can not begin with an @ character"
         Calculate Dtls as ''
      Else If pos('.',irListRow.EmailAddr)=1
         Calculate Mssg as "The email address can not begin with period character"
         Calculate Dtls as ''
      Else If pos('.',irListRow.EmailAddr)=0
         Calculate Mssg as "The email address must include a period character"
         Calculate Dtls as ''
      Else If pos('.',irListRow.EmailAddr)>len(irListRow.EmailAddr)-3
         Calculate Mssg as "The email address must include a domain name at the end"
         Calculate Dtls as ''
      End If
      If len(Mssg)
         Do $cinst.$_logError($cmethod,Mssg,Dtls)
      Else
         Calculate FlagOK as kTrue
      End If
   End If
   
   Quit method FlagOK

   
   
   
   

   For checking email addresses and phone numbers you will want to create a common object which specializes in checking and formatting email addresses and phone numbers and it then be called by all the applicable oCheckData methods.

4. The $check_Email method will automatically be called when oTableClassMethods is sent a $checkCurrRow message and its base table is contact.

If you wanted to do data validation of the Email field in a window class as the user leaves the field you would enter the following code in the $event method of the Email field.

On evAfter

Do iList.$checkColumn('Email') Returns FlagOK
If not(FlagOK)
   
   Do errhndlr.$promptonceLastError()
   
   ; If you want to keep the user in this field.
   Quit event handler (Discard event)
End If

; Be sure to pass the event to the next handler so that the SW field handler can undecorate the field.
Quit event handler (Pass to next handler)

StudioWorks Documentation - Copyright 2005 Vencor Software