FrontBase

FrontBase

is a SQL92 compliant RDBMS (Relation Database Management System) available for the Mac/Window/Linux platforms. I have been using FrontBase since 2004 and have been very happy with its speed, reliability, and support. The management and support staff are quick to respond to any questions on the FrontBase Developers list. Their responses to bug reports and enhancement requests are great.

You can purchase a free renewable 6 months developer licenses to develop with FrontBase and then purchase the full version for when or your customer is ready for deployment.

FrontBase appealed to me because it runs on most server platforms, supports Mac and Wintel clients, 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 SQL92 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 becomes 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 still consider myself a SQL newbie, coming from OmnisDML, to OmnisSQL, and the FrontBase.

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.

Don't assume my advice is correct. You are at your own risk. If you are an SQL expert and notice anything in this section that is incorrect, please send me an email and set me straight. I don't want to be passing along poor advice.

Installing FrontBase

This section covers the adventures of installing FrontBase. Installation was easy!

Connecting to FrontBase

To connect to FrontBase from Omnis Studio you first need to create a database. Using the FrontBase Manager application provided by FrontBase.

1. Create a new database. e.g. TestDB
2. FrontBase creates a user named _SYSTEM with no password.
3. Connect to the TestDB as _SYSTEM.
4. Select Users and create a new user named DBADMIN with the password secret.
   
   
   

   Following the advice of other SQL gurus I always create a user DBADMIN user which is the tablesowner user. I don't use the superuser (root or _system) for creating tables.

5. Close FrontBase Manager.

We are now ready to connect to the TestDB from Omnis Studio.

1. Open Omnis Studio. Be sure to installed the correct FrontBase DAM in the Omnis Studio xcomp folder before you open Omnis Studio.
2. Click the SQL Browser node in the F2 Browser treelist and click Sessions Manager.
3. Create a new session template with the following settings:
   
   Session Name: FB_TESTDB
   Vendor: FrontBase
   DAM: FRONTBASEDAM
   Hostname: localhost/TestDB
   User: DBADMIN
   Password: secret
   
   
   

   Leave the Database field empty. For FrontBase the database is appended to the server name in the Hostname field

4. Click Okay, then click Back.
5. Click Open Session and select FB_TESTDB. All going well you Omnis Studio will connect to your TestDB FrontBase database.

Learning FrontBase

The first time I tackled FrontBase I got all bogged down in non-visual DAMs and the Omnis Studio manuals.

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

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

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: secret
   User Name: DBADMIN
   User Password: secret
4. Click the Connect pushbutton.
   
   
   

   When you are testing connections to databases it is handy to have the SQL Browser node open in the F2 Browser. If the logon code works you will see your session show up as a child node. If the logon fails, you can test your settings by creating a session template in the SQL Browser. Then test it using SQL Browser > Open Session.

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 SQL Brower node and double-click on the session node. Double-click the Tables node. Double-click the DRIVERS table 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.

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.)
4. You will need to learn how Omnis Studio table classes and multi-thread DAMS work together.

Add Constraint - Unique Index

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

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

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

ADD CONSTRAINT CountryName UNIQUE (Country) NOT DEFERRABLE INITIALLY IMMEDIATE;

Add Foreign Key

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

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

ADD FOREIGN KEY (Author_fkey)
REFERENCES Author (Author_pkey)
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT DEFERRABLE INITIALLY IMMEDIATE;

Alter Table - Add Column

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

ALTER TABLE Country ADD COLUMN CountryName VARCHAR(30);

Copy Table

To copy a table in FrontBase:

1. Create the new table.
2. Copy the contents from the old table into the new table using:
   
   

   INSERT INTO SELECT * FROM ;

To create the new table from the old table you can use FrontBase Manager as follows:

1. Select Schema Objects > Tables.
2. Select the old table and click the Open Definition button
3. Select SQL and copy the SQL to the clipboard.
4. Close the table definition window.
5. Select SQL Interpreter.
6. Paste in the SQL from the clipboard and change the old table name to the new table name.
7. Click the Execute SQL button.

Create Table - Primary Key

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

CREATE TABLE Country (
Country_pkey INT NOT NULL PRIMARY KEY,
CountryName VARCHAR (30) NOT NULL,
);

Create Table - Relate to Schema

Each table in FrontBase is related to a schema in FrontBase (This is nothing to do with Schema Classes in OMST) 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.

Issuing a CREATE TABLE... will create the table and relate it to the schema object owned by the current user.

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.

1. Long Character Fields - Set the length of your character fields within reason of the actual length you need. You should avoid leaving character fields at 1 million characters when you only need 100 character or when 5000 character is sufficient. Super long fields are stored as CLOBS rather than text. Unnecessarily long character fields can complicate searches and give you headaches with different RDBMSs
2. DROP TABLE requires the addition of CASCADE.
3. Exception 384: Transaction MUST be SERIALIZABLE, PESSIMISTIC - This comes up if you try to alter a table or do some action that requires a special transaction setting. Issue the following SQL statement to change the transaction setting. Once you've issued it once, you don't need to reissue it for the rest of the session.
   
   

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;

   
   
   At the time of writing this SET TRANSACTION has to be issued using $prepare and $execute. Using $execdirect would not work.
4. Binary DataTypes - You can not save a blank (empty) value to a BLOB data type. (PICTURE, LIST, ROW datatypes) You must either save a value or a NULL.
5. Dates - 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.
6. Case-insenstive Indexes - FrontBase does a great job of case-insensitive searchs but you first need to create the case-insenstive collation in your FrontBase database. The following SQL wil do the trick:
   
   

   CREATE COLLATION CASE_INSENSITIVE for INFORMATION_SCHEMA.SQL_TEXT FROM EXTERNAL ('CaseInsensitive.coll1');

   
   
   You only need to do this once per database. Once the collation has been created you need to specify the columns which you want to use case-insensitive collations. The syntax is as follws:
   
   

   ALTER COLUMN TableName.ColName TO COLLATE CASE_INSENSTIVIE;

Move Database to Another Platform

If you need to move a FrontBase database to a different platform (Mac to Window or Linux) you will need to write all the data out to a file, copy the file to the target computer, create the database on the target computer, and then import the data from the file.

The following steps were used for moving a FrontBase database from Mac OS X to a WindowXP computer. You can interpolate these instructions accordingly for other platforms:

1. In the Terminal app on the Mac, type the command to open FrontBase:
   
   

   /Library/FrontBase/bin/sql92

2. Connect to the database you wish to export:
   
   

   CONNECT TO database-name USER user-name;

   
   
   database-name is the name of your database to export, and user-name is the name of the tables owner user.
3. Export the whole database using the command:
   
   

   WRITE ALL OUTPUT (DIR='directory-path',CONTENT=TRUE);

   
   
   directory-path is the full path of the directory to export all of the files to. e.g. '/Users/myname/Desktop/DumpDatabase'. Several files will be created so you should output the files to an empty folder which you create for the output files.
4. Quit Frontbase by type quit at the SQL92 prompt.
5. Copy the directory with the enclosed output files to the target computer. In this example we are copy the files to a Windows computer.
6. Inside the directory there will be a file named schema.sql.
7. Open the schema.sql file with a text editor.
8. Find all of the occurrences of the directory-path previously used to output the database. Change each occurence to the new directory where the files are now located on target computer. For example:
   
   Change all occurences of: '/Users/myname/Desktop/DumpDatabase'
   
   To: C:\Documents and Settings\myname\Desktop\DatabaseDump
9. Create a new database with the name you want the converted database to be. Creating the new database is probably easiest to do with FrontBase Manager.
10. In the command shell type in the command to open Frontbase. (Include the quotes)
    
    

    'C:\Program Files\Frontbase\bin\sql92.exe'

11. Type in the command:
    
    

    SCRIPT 'directory-name/schema.sql';

    
    
    directory-name is the path to the folder.
    
    e.g. C:\Documents and Settings\All Users\Desktop\DatabaseDump
12. You should see a bunch of text scroll past, depending on how large your database is. This text doesn't matter. Just look at the last few lines. If there is a message like Errors, Exiting because ... Read the because. That will tell you what went wrong.
13. All going well the tables, indexes, views, etc. will be created in the new database and all of the content will be imported.

Multi-threaded DAMs

Remove Monitored Databases

If you have a several databases on your local area network in your Monitored Databases on your laptop and then leave the office and decide to start up FrontBase Manager, you are in for an eternal wait.

On Mac OS X to solve this problem you need to delete the com.frontbase.FrontBaseManager.plist file located in ~/Library/Preferences/

You can directly remove the file. From the Terminal enter:

defaults remove com.frontbase.frontbasemanager

You can edit the file as follows:

1. Open the Property Manager from the Terminal:
   
   

   open ~/Library/Preferences/com.frontbase.FrontBaseManager.plist

2. Go to Root > monitoredDatabases and you will see 0,1,2,3,4,etc. You can open those numbered nodes to see the individual monitored databases. You can delete the numbered nodes to delete specific monitored databases (it will auto-renumber).
   
   You can keep an at work copy and an at home copy of the file and switch between them.

Rename Table

To rename a table in FrontBase:

ALTER TABLE NAME TO ;

Select top() Function

For web interfaces if the user does a search which results in selecting a large number of records we will want to:

• return the results in small batches of records per page
• give the user an indication of the total number of pages
• give the user the ability to jump directly to any of the pages

The top(Offset,NumberOfRecords) function which FrontBase supports is ideal for this situation.

For example...

SELECT top(0,10) LastName, FirstName FROM Contact ORDER BY LastName, FirstName

... selects records 1-10

SELECT top(20,10) LastName, FirstName FROM Contact ORDER BY LastName, FirstName

... selects records 21-30

Putting this into practice for a web interface where the user asks for all the last names beginning with the letter 'S'

SELECT count(*) FROM Contact WHERE LastName LIKE 'S%'

Returns for this example a count of 232 records. If we display 50 records per page that would be 5 pages.

To display the records for page 4...

SELECT top(150, 50) LastName, FirstName, MiddleNames FROM Contacts
WHERE LastName LIKE 'S%' ORDER BY LastName, FirstName, MiddleNames

System Date and Time

The FrontBase SQL syntax for setting a timestamp column's date to the current date/time is as follows:

CAST(CURRENT_TIMESTAMP AS TIMESTAMP)

Calculate SQLText as "UPDATE TableName SET ModDateColName=CAST(CURRENT_TIMESTAMP AS TIMESTAMP),ColName1=[$cinst.ColName1], etc."
Do StmntObj.$execdirect(SQLText) Returns FlagOK

Other FrontBase date/time functions are as follows:

CURRENT_DATE
CURRENT_TIME

Transactions

If you are 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.

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 refers 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 transactions, so in OmnisSQL every SQL statement is automatically committed and there is no rollback option. You can set any RDBMS to autocommit transactions. You control this with the $transactionmode property.

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

However, with databases such as FrontBase that support transactions you would set the $tranactionmode property to kSessionTranManual if you are doing a batch of transactions or a series of transaction which a linked to each other.

The FrontBase Manager Manual > Appendix 1 > Transaction Settings, gives a pretty user friendly explanation about Isolation Levels and Locking Disciplines for FrontBase. 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, as opposed to 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 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.

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 Geert B. Clemmensen FrontBase, Inc.

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.

Select by One User Cause Other User Selects to Hang Explained by Geert B. Clemmensen FrontBase, Inc.

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

StudioTips Documentation - Copyright 2006 Vencor Software