FrontBase

FrontBase is an SQL backend which can run on MacOSX, Linux, Unix, and Windows NT.

FrontBase appealed to me because it runs on most server platforms, supports Mac and Wintel clients, and has DAMs built for Omnis Studio, and is priced affordably with unlimited runtime licensing. (At least at the time of writing this)

FrontBase is very stringent in following the SQL92 standard. Some developers (& competitors) criticize FrontBase for being so stringent. Personally, this trait appeals to me. If I'm going to learn SQL92 I'd like to learn and follow the real rules so that my application can be backend vendor independent. If I start using non-SQL92 tricks that are specific to one vendor my code is now married to that backend vendor.

If you are a SQL guru, you might laugh at some of the problems I ran into or the things I didn't understand. At the time of writing this, I am a still an SQL newbie, coming from OmnisDML and OmnisSQL.

A lot of the jargon in SQL just plain baffled me. Here's just a few terms that you need to deal with when trying to understand SQL record locking: Transactions, Concurrency, Isolation Levels, Read Uncommitted, Read Committed. Repeatable Read, Serializable, Pessimistic Locking, Optimistic Locking, Share and Exclusive Locks, Commit, Rollback, Discrete Commit, Granularity of Locks.

Yikes! For those of us new to SQL it's enough to make you run back to OmnisDML!

In this section of StudioTips, I will take you through my adventures in installing and working with FrontBase.

Add Constraint - Unique Index

In OmnisSQL we would use "CREATE UNIQUE INDEX" to enforce unique values on a column.

OmnisSQL uses "CREATE UNIQUE INDEX". SQL92 uses "ADD CONSTRAINT".

SQL92 uses "ADD CONSTRAINT" to set a unique index constraint for a specific column.

An example script for adding a unique constraint in FrontBase/SQL92 is as follows:

Add Foreign Key

OmnisSQL does not support foreign keys. With OmnisSQL you have to write your own table class methods/code to handle foreign key constraints.

An example script for adding a foreign key in FrontBase/SQL92 is as follows:

ADD FOREIGN KEY (To_Person_Key) REFERENCES person (Person_Key) ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;

"NO ACTION" can be replaced with "CASCADE". For "ON DELETE" cascade would cause all the child records to automatically be deleted. In some cases that would be handy. In many cases that would be lethal.

Alter Table - Add Column

Adding a column is quite easy. An example script follows:

Create Table - Primary Key

When you create a table in SQL92 you can (should) specify the primary key.

CREATE TABLE "person" (
person_key INT NOT NULL PRIMARY KEY,
person_name VARCHAR (30) NOT NULL,
);

GOTCHA: Be careful with carriage returns!
I caused grief for myself more than once by inadvertently including a carriage return in the table name of the CREATE table statement. FrontBase is very particular about following the SQL92 standard, so if you follow their examples they alway enclose table and column names in quotes. Here's an example of how I accidentally added a carriage return to the table name.

CREATE TABLE "person
"(
"person_key" INT NOT NULL PRIMARY KEY,
"person_name" VARCHAR (30) NOT NULL,
);

The carriage return is locate between "n" of "person" and it's close double quote character.
Arrgh this little mistake can have you scratching your head for a while!

Create Table - Relate to Schema

Each table is related to a schema. (NOTHING to do with "Schema Classes"! See Users & Schemas) Each schema is owned by a user.

To create a table related to a schema you must logon as the user which owns the schema.

If you logon as a different user and try to create a table:

1. If the user owns a schema, the table will be related to that schema.

2. If the user doesn't own a schema, an error message will be generated stating that the user is not the owner of the current schema.

CREATE TABLE person (...

... will create the table "person" and relate it to the schema object owned by the current user.

NOTE: Only the owner of a table can DROP a table.

At this point in the SQL92 learning curve, I'm thinking to keep things simple, only use one schema, and relate all tables in the database to the one schema.

FrontBase Gotchas

Here's some new things I had to learn when starting to work with FrontBase. Be sure to test these gotchas, some of them may be my misunderstanding or may have been fixed/changed in newer version of FrontBase.

Omnis character fields - Length of 10 million.

FrontBase will hiccup and your SQL won't work. Don't go over 1 million characters long in your schema classes on character fields. If you use SQL ASSIST to create your schema classes and CREATE/ALTER SQL statements, SQL ASSIST will automatically limit them to 1 million characters.

DROP TABLE - requires the addition of "CASCADE".

If you use SQL ASSIST, it handles this for you.

SQL statements must be suffixed with ";" colon.

FrontBase requires all SQL statement to end with a semi-colon ";". If you are submitting your SQL statements throught the "FRONTBASESESS" V3DAM, the DAM adds the ";" for you. (unless you issue a "Do StatementObj.$sqlbrowser.$assign(kFalse)")

ALTER TABLE SYNTAX

I had been using the syntax "ALTER TABLE table ADD (ColName DataType, ColName DataType)" ;

Frontbase won't allow you to put multiple columns inside brackets like OmnisSQL.

The syntax for adding a column is as follows:

ALTER TABLE tablename ADD COLUMN colname VARCHAR (50) ;

This means you have to issue ADD COLUMN statements one at a time for each column. Once I got the ALTER TABLE syntax right, I ran into another problem. FrontBase reported:

Exception 384: Transaction MUST be SERIALIZABLE, PESSIMISTIC

To get around this, I had to issue the following "SET" SQL statement before the first time you issue an ADD COLUMN statement. Once you've issued it once, you don't need to reissue it for the rest of the session.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;

PICTURE, LIST, ROW datatypes

You can not save a "blank" (empty) values to BLOB data types. You either save a value or a NULL.

DATE datatypes

If you attempt to save a "blank" date FrontBase converts it to the default first date (Jan 1, 19...). To save an empty date it must be NULL.

CASE SENSITIVE INDEXES

With OmnisSQL you have to CREATE CASE SENSITIVE indexes if you want decent performance. FrontBase does not need (nor allow you to create) case sensitive indexes. SQL Assist handles this automatically for you.

SET TRANSACTION

Installing FrontBase

This section covers the adventures of installing FrontBase on Mac OS X. Installation was easy!

Learning FrontBase

The first time I tackled FrontBase I got all bogged down in V3DAMs and the Omnis Studio manuals.

It was frustrating trying to learn and understand the V3DAMs, session objects and all that new stuff, not knowing whether the problem was with my code, with FrontBase, or the V3 DAMs. Things did not go very smoothly.

AN OMNIS STUDIO APPLICATION - FRONTBASE TUTORIAL

The 2nd time around, I downloaded the following PDF from the Documenation section of the FrontBase web site: "An Omnis Studio Application - Describes how to use Omnis Studio as a front end to a FrontBase database."

Following this tutorial (and unfortunately not so clear screen shots) gave me much better confidence that FrontBase and Omnis Studio were compatable cousins. Everything worked as it should. Within 30 minutes you are reading and writing records to FrontBase and even doing a query class. I can't say I agree with the naming conventions they use or the SQL Wizard's window interface, but heh, they get you up and running!

After you've built the application, go look at the $construct code in the window classes, and study the code behind the Insert, Update, and Delete pushbuttons. It is surprisingly simple.

You wouldn't want to build an application with all that duplicate code, but it gives you a starting point that is simple to follow.

FRONTBASE TEST LIBRARY

The FrontBase DAM download also comes with a Test Library folder which contains a FrontBase Test.lbs library. Open the library and tinker around with the it.

1. Create a new database using FrontBase Manager and get it running.

2. Open the "Connect" window in the FrontBase Test library.

3. Enter the logon information. In my case:
->Server Name: localhost
->Database Name: TestDB
->Database Password:
->User Name: _System
->User Password:

4. Click the Connect pushbutton. All going well you will connect. One time I had problems with 'localhost' not be recognized. I replaced 'localhost' with the actual IP address of my computer, and then it would work.

TIP: When you are testing connections to databases it is handy to have the Tools menu > SQL Browser window open. If the logon code works you will see your session show up in the SQL Object Browser window. If the logon fails, you can test your settings by creating a Session Template in the SQL Object Browser. (Sessions menu > Modify Session Template). Then test it using Sessions > Open >.

5. The Connect window will display a "Drivers & Lists" tab pane. Click the Create Drivers pushbutton. This creates the DRIVERS table in the Frontbase database.

6. Insert a few drivers. Then click Select All to list the drivers.

7. Go to the Lists tab and repeat the same thing. (Create List, Insert List, Select All)

8. Open the Tools menu > SQL Brower window and double-click on the session object in the window. Double-click the Tables icon. Double-click the DRIVERS icon. Here you will see the column names.

Take a look at the code behind the window's buttons. I found it interesting to see that this library doesn't use schema classes or table classes. The Create List button demonstrates a list/BLOB type column. The "Connect" pushbutton shows you the code for logging on to FrontBase.

SUMMARY

It helps to build your confidence by seeing Omnis Studio working with FrontBase before you venture into building your own logon method.

The code, naming conventions, window designs, etc. in these tutorials is not great, but that isn't the objective of the FrontBase tutorial and the FrontBase test libraries.

Multi-threaded DAMs

Transactions and Concurrency

In the SQL world we are faced with a number of options dealing with transactions and concurrency.

Transactions are groups of SQL statements that are effectively executed as single units.

Concurrency rerfers to mechanisms that the DBMS uses to keep operations on the same data by simultaneous users from interfering with one another.

You begin a transaction whenever you initiate a session with SQL. All the statements you enter will be part of the same transaction until you complete it by entering either a COMMIT WORK or a ROLLBACK WORK statement. OmnisSQL does not support COMMIT and ROLLBACK, so in OmnisSQL every SQL statement is automatically committed and there is no ROLLBACK option. You can set any DBMS to autocommit transactions. With the V3 DAMs you control this with the $transactionmode property.

Do $sessions.SESSIONNAME.$transactionmode.$assign(kSessionTranAutomatic)

However, with databases such as FrontBase that support COMMIT and ROLLBACK you should make use of transactions and set the $transacation mode property to kSessionTranManual.

The FrontBase Manager manual > Appendix 1 - Transaction Settings, gives a pretty user friendly explanation about how Isolation Levels and Locking Disciplines for FrontBase. But to the newbie, like myself, even after reading it carefully and referring to other SQL manuals I still wasn't 100% sure what isolation level and locking discipline settings I should use. A telephone call to Joe Maus, helped to fill in the empty blanks.

Everyone has their own opinions on what is right and wrong for these settings, so feel free to make your own choices. The following is what I decided to go with.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED, LOCKING DEFERRRED, READ WRITE;

READ COMMITTED means that each time I select and fetch records, FrontBase automatically commits the transactions, so that the records are not blocked from other users who wish to read and write those records.

LOCKING DEFERRED means that if I read some records and then another user reads the same records, if I update my records, the other user is blocked from updating the same records. (Hope I got that right)

READ WRITE means that I can read and write records to the database, vs. READ ONLY.

For most of the work I do, I don't want to block users from reading the same records at the same time. Doing so, would cause unnecessary blocks and waits when two users want to look at the same record, or run reports on the same records.

I prefer to use an EditNum (or TS) integer column in each of my SQL tables. The EditNum is incremented +1 each time the record is updated. When the update is done, I check to make sure the record's EditNum value matches the OldRow.EditNum value. This can be done by accomplished by appending "AND EditNum = OldRow.EditNum" to the SQL update statement. This methodology is considered optimistic locking as opposed to pessimistic locking which won't allow other users to access records selected by another user and not yet committed.

The transaction settings are session specific. You can change the transaction settings for a session at any time during the session. (Although not for records you've already selected)

To set the transaction setting you issue the SET TRANSACTION statement after the session has been started.

GOTCHA: For FrontBase you must use $execdirect for SET TRANSACTION. If you use $prepare and $execute your SET TRANSACTION statement will fail. (SQL92 specific reasons for this.)

LOCKING DEFERRED - EXPLAINED BY FRONTBASE

The DEFERRED locking discipline is almost like PESSIMISTIC with the main difference being that when a row/table is referenced, a READ lock is acquired. If the row/table is updated, the READ lock is automatically updated to a WRITE lock. The DEFERRED locking discipline is thus ideal when updates occur less frequently than reads.

Geert B. Clemmensen FrontBase, Inc.

SELECT BY ONE USER CAUSES OTHER USER SELECTS TO HANG
A SELECT needs a transaction so if there is no active transaction, one will get created. No matter
the actual transaction settings, you will eventually (and the sooner the better) have to either ROLLBACK or COMMIT a transaction.

Transcations

If you ar new to SQL, probably one of the most confusing things it figuring out this business of transactions. For many of us it would be nice if there was just one way to handle transactions and we would just follow that way. But it isn't that simple. There are many different choices and you need to work through things and figure out what will work best for your situation. In fact, you can have different users accessing your database with different transaction settings that suit the particular data they ware working with.

The following is an explanation about transactions by Geert Clemmensen of FrontBase.

The most important bottom line there is in regards to transaction is: A transaction is the only way, no matter what, to guarantee anything about the integrity of your most valuable IT asset: your data. The sooner you learn and understand transactions and what they can do for you, the sooner will you be able to master them and thus provide the solutions your customers ask for.

I have included a brief PDF document about transactions, which actually stems from a Keynote (Apple's presentation software) presentation I gave at AmerOmnis in March. Maybe this will help shed some light on the "preferred" transaction settings.

FrontBase implements the transaction model defined in the SQL 92 standard, a model that has been accepted by most modern database layers, incl. JDBC.

We have augmented the transaction model of SQL 92 in two ways:

1) The VERSIONED isolation level. A VERSIONED transactions provides a snapshot of the latest COMMITted version
of the database, i.e. the snap-shot represents history and the transaction is thus read-only and there is no locking
whatsoever involved.

2) Locking discipline. FrontBase offers OPTIMISTIC, PESSIMISTIC and DEFERRED as locking disciplines.
OPTIMISTIC means that you will always get access to your data, but if you modify them, a subsequent COMMIT
may fail because somebody modified the same data and executed a COMMIT before you.
PESSIMISTIC means that you will acquire a lock and that this lock isn't released until ROLLBACK or COMMIT time.
This also means that PESSIMISTIC is a so-called blocking locking discipline.
DEFERRED is a variant of PESSIMISTIC in which you will acquire a READ lock, if you modify the data, the lock
will automatically be upgraded to a WRITE lock. This means that it is possible to have concurrent PESSIMISTIC
readers.
The locking discipline has no meaning whatsoever if the isolation level is VERSIONED.

The SELECT ... FOR UPDATE syntax that lately has been referenced on fb-dev is a legacy or old-fashioned syntax that doesn't make sense in SQL 92, each SELECT in SQL 92 is automatically a FOR UPDATE select. Each table and row that a SELECT "touches", is automatically locked according to the isolation level and locking discipline. In many "older" database servers, a SELECT without the FOR UPDATE clause means that the server is using a one-shot transaction (a kind of auto commit), which can mean that two subsequent SELECTs executed seemingly immediately after each other can return different data. Please note that a SELECT ... FOR UPDATE is a blocking statement.

When you do a backup in FrontBase (WRITE BACKUP ...;) it will implicitly use the VERSIONED locking discipline, i.e. you can continue to modify the database while the database is running.

Let me try and address some specific issues/questions that have been posted to fb-dev.

In step 1 any data can be selected and shown, regardless if the same data is
used by another session-process.
The fullest meaning of this implies what is called dirty-reads which is not supported by FrontBase and which doesn't really make any sense from the users perspective. The choices are in reality:

1) OPTIMISTIC, i.e. multiple users can see the same COMMITted data. COMMITting an OPTIMISTIC transaction
is much more likely to cause a transaction conflict than a PESSIMISTIC transaction, but the upside is a much
higher degree of concurrency.

2) PESSIMISTIC, i.e. only one user at a time can access and modify a given table or row.

The strategy of having two connections, i.e. one for doing SELECTs and one for doing modifications has been adopted by quite a few developers. The downside is that rows you have selected between the time of SELECT and UPDATE, may have been changed or even deleted by somebody else (the whole table may have been DROPped). The upside is a high degree of concurrency.

The TEST AND SET LOCKING ON < table > (< row index >) statement is a twist to the PESSIMISTIC locking discipline that can tell you if a given row is already locked by somebody else (or let you acquire the lock).

The ONLY way to make the strategy 100% watertight is to SELECT and UPDATE the data in the same transaction using a PESSIMISTIC locking discipline. This applies not just to FrontBase, but to all database servers that are serious about data integrity.

If your app can deal gracefully with transaction conflicts, something that is less than 100% watertight can be made to work quite appealing, while still preserving the data integrity.

1. User can selected any data available (for that screen)
2. User can signal a wish to alter the data shown (on the screen)
3. User can input the alterations.
4. User must signal whether the alterations must be stored or not.
C. At the same time any process must be able to read the same data without
hindrance.
D. Waiting by users for release of a lock by another user is not
allowed-appreciated. So in case this could happen a test must be available
to avoid it.
The only way to achieve the "conflicting" requirements put forward above, is to SELECT the data (and COMMIT the transaction right away or use a separate OPTIMISTIC or VERSIONED connection), lock the appropriate rows when editing starts (using a SELECT or TEST AND SET LOCKING). NO MATTER WHAT CHOICE YOU MAKE, YOUR APP MUST BE ABLE TO DEAL WITH TRANSACTION CONFLICTS (somebody may have changed the data between the SELECT and UPDATE).

An analogy describing the OPTIMISTIC situation is ordering food in a cafeteria: You make your choice of meal (SELECT) and are told to go and pick it up after it has been paid for (COMMIT), you go to the cashier and want to pay (UPDATE and COMMIT), only to find out that somebody else has just paid for the last portion of that meal. This means that you will have to go back and make a new choice (ROLLBACK), go to the cashier, ...

The PESSIMISTIC version of the cafeteria analogy is: you make your choice (SELECT, nobody else can now get it), get your meal (UPDATE), go to the cashier and pay for it (COMMIT).

Connection 1 (C-RW) has the following settings:
set transaction isolation level repeatable read, locking optimistic, read write;
SET TRANSACTION ISOLATION LEVEL VERSIONED; is enough, as it implies a read only transaction that only can read historic data (the latest COMMITted version of the database).

Here I also have to note that this can be tricky in Frontbase, and connections sometimes "hang" without any obvious reason.
I am willing to be a six-pack of your choice that there isn't a bug in FrontBase in this area :o) If you can forward us a reproducible "hang" situation, my bet is that we can explain the problem to be on the client side.

Hope the above helps.

Users and Schemas

If you come into FrontBase from OmnisSQL the whole issue of "Users" and "Schemas" in SQL92/FrontBase is an extra layer that gets in between you and the server tables. Learning to work with this extra layer can be frustrating when you are first moving to FrontBase/SQL92.

The concept of "Schemas" in SQL92 is not to be confused with "Schema Classes" in Studio. Studio's choice of the term "Schema Class" got in the way of my understanding SQL92 Schemas. When we talk about "Schemas" in FrontBase/SQL92, don't even think about Schema Classes in Studio!

According to Mastering SQL by Martin Gruber; A group of interrelated tables is called a schema. A database can contain any number of schemas. So, a SQL92 "schema" is a collection of server tables. (plus views and few other things)

The concept of "Users" is explained by Martin Gruber as follows:

* A DBMS (Database management system) must be capable of recognizing individual "users". Users are identified with a logon procedure (username/password).

* Once they logon they initiate a "session" (also called a "connection") with the database. Within a session a sequence of "statements" is issued. Statements from simultaneous sessions of the same or different users for independent sequences.

* Users have a set of "privileges" that inidcate what they may and may not do.

* A database user typically has ownership of one "schema" and may have access to others as well. (Remember a schema is a collection of tables)

* A user can own one or more schemas.

To the best of my knowledge OmnisSQL does not support SQL92 "schemas" or "users". So when you move from OmnisSQL this is NEW territory!

USERS & SCHEMAS - FRONTBASE MANAGER

Assuming you are using the FrontBase Manager, if you create a new database and then connect and logon as _SYSTEM and then click "Users" you will find that the user _SYSTEM has a "Default Schema" of "None". You can edit the user and change the Default Schema to "_SYSTEM". (Go ahead try it.)

If you click on"Schema" you will see a schema by the name of "_SYSTEM" that is owned by the user "_SYSTEM". (The names are the same, but they don't have to be.)

If you click on "Schema Objects" you will see a list of the "Schemas". If you click on the "_SYSTEM" schema, you will see that a schema has; Tables, Views, Procedures, Functions, and Collations.

If you click on "Tables" you will see the tables related to the schema. If this is a brand new database then there won't be any tables listed yet.

SUMMARY:

There is a "user" called "_SYSTEM".
There is a "schema" called "_SYSTEM" which is "owned" by the user "_SYSTEM".
The "schema" called "_SYSTEM" can have a group of interrelated tables.

USERS & SCHEMA STRUCTURE

Being very new to SQL92 I was (am) unsure what the best structure is for Users & Schema. I was told to think of the _SYSTEM user as the super user, and that it would probably be best not to related tables directly to _SYSTEM.

In my own application, I did the following from the FrontBase Manager.

1. Connected as the user, "_SYSTEM".
2. Added a user called "SYSADMIN" without creating a default schema for it.
3. Added a user called "STAFF" without creating a default schema for it.
4. Closed and reconnected as "SYSADMIN"
5. Created a Schema called "DATABASE". (The owner is automatically the current user, "SYSADMIN".
6. Closed and reconnected as "_SYSTEM".
7. Edited the user, SYSADMIN and set its default schema to DATABASE.
8. Edited the user, STAFF and set its default schema to DATABASE.

We now have a schema called DATABASE which is owned by SYSADMIN. We would normally connect to the database as SYSADMIN. Any tables we create when connected as SYSADMIN will automatically be related to the DATABASE schema which is owned by SYSADMIN.

SYSADMIN would GRANT privileges to STAFF for the many of the tables & view which it creates.

Other users would connect as the user STAFF (think of STAFF as privileges for a group). Additional types of users could be added if we wanted to further fine tune privileges. (eg. ACCOUNTING)

Using FrontBase

So now you've seen that FrontBase works and the Omnis Studio can talk with it. What next?

1. You need to get an understanding of how the multi-thread DAMs work in Omnis Studio.

2. You will need to likely need to build a logon window and logon session object class for your application.

3. You need to get an understanding of transactions and concurrency. (Commit, Rollback, etc.)

StudioTips Documentation - Copyright 2006 Vencor Software