FrontBase Documentation

Backtrack:
Welcome!
6. Original Documentation
Updated: 20-Nov-2000
prev — next
Table of Contents

6.3. Introduction to SQL 92

SQL 92 is the latest official standard for SQL from the ANSI/ISO bodies and as such represents an amalgamam of many years of experience with the language SQL and the various implementations

FrontBase is the first industrial strength database server that implements virtually all of SQL 92. This may not be important to the work you want to do with a database server, but there are at least a couple of issues that you need to be aware of. The single most concept we have seen that can create a little confusion is the concept of SCHEMAs. While many products on the market use the word SCHEMA in their literature, very few implement this concept (at least in the SQL 92 sense).

Actually, there is a layer on top of SCHEMAs called a CATALOG. An SQL 92 database is comprised of a number of CATALOGs, each holding a number of SCHEMAs. A SCHEMA can be viewed as the container for a number of objects: TABLEs, VIEWs, DOMAINs, COLLATIONs, etc.

CATALOGs

Currently, FrontBase offers the support for one CATALOG in a database. This CATALOG inherits the name of the database, e.g. if the database was created with the name Movies.fb, the catalog is named MOVIES. This catalog name is always used as the default catalog and thus you don't really need to worry about CATALOGs.

SCHEMAs

As mentioned earlier, a catalog can contain many SCHEMAs. A SCHEMA is owned by a user (see further below) and only this user can add or drop objects in the SCHEMA. A SCHEMA object can be a table, a view, a domain, ... By means of the GRANT/REVOKE statements, other users can be granted a number of privileges pertaining to the objects within a schema, e.g. the INSERT privilege on a table.

Objects in a SCHEMA can be referenced via so-called qualified names:
[[<catalog name>.] <schema name>.] <object name>
SQL 92 offers a rich "default for everything" setup, so whenever you want to reference objects in the current SCHEMA, only the <object name> needs to be given.

When a new database is created, two SCHEMAs are created as well: DEFINITION_SCHEMA and INFORMATION_SCHEMA. The DEFINITION_SCHEMA holds all the objects used to maintain all other SCHEMAs. The INFORMATION_SCHEMA holds various objects, which offer access to the objects in the DEFINITION_SCHEMA, and a number of convenience objects. For example if you want to see which TABLEs have been defined in a database, the following SQL 92 statement could be executed:
SELECT * FROM INFORMATION_SCHEMA.TABLES;
INFORMATION_SCHEMA.TABLES is actually a VIEW defined like:
CREATE VIEW INFORMATION_SCHEMA.TABLES AS
   SELECT * FROM DEFINITION_SCHEMA.TABLES;
The VIEWs in INFORMATION_SCHEMA are all non-updateable, i.e. an INSERT like:
INSERT INTO INFORMATION_SCHEMA.TABLES .... 
will fail.

The DEFINTION_SCHEMA is maintained exclusively by FrontBase and cannot be accessed or manipulated directly by any user.

To create a new SCHEMA (which the current user will then own):
CREATE SCHEMA <schema name>;
-- See the SQL 92 standard for the complete
-- syntax of which the example is only but a
-- tiny fragment.
To make a schema the current schema:
SET SCHEMA '<schema name>';
(Please note that the schema name is given using a character string).

To see the list of defined SCHEMAs:
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
To see what is the current schema, the CURRENT_SCHEMA string function is available. Please note that CURRENT_SCHEMA is a FrontBase extension to SQL 92.

USERs

The concept of users in SQL 92 is relatively simple, but is tied very closely to the concept of schemas.

To access a database, a user name is required, otherwise access is denied (FrontBase offers password protection as an extension to SQL 92).

When a new database is created, a number of user names are also created, among which are: _SYSTEM and _PUBLIC. Both these user names are considered by SQL 92 as special user names, in fact the leading underscore cannot be used in regular identifiers, and is not to be used.

To create a new user:
CREATE USER <user name> [DEFAULT SCHEMA <schema name>];
To change the default schema:
ALTER USER <user name> SET DEFAULT SCHEMA <schema name>;
The optional <schema name>, which must exist when the user name is created, will be the default schema for the user whenever the database is accessed. If no default <schema name> is given, a schema with the same spelling as the user name is created and used as default (this will happen the first time the user accesses the database).

To see who is the current user, the USER and CURRENT_USER string functions can be used (USER is simply a shorthand for CURRENT_USER).

To make a user name the current user:
SET SESSION AUTHORIZATION <user name>;
To see the list of defined user names:
SELECT * FROM INFORMATION_SCHEMA.USERS;
DATE, TIME and TIMESTAMP

SQL 92 has an elaborate time concept which includes the following datatypes:

DATE
TIME
TIME WITH TIME ZONE
TIMESTAMP
TIMESTAMP WITH TIME ZONE

DATE holds year, month and day, i.e. NO time components.
TIME holds hour, minute, and second.
TIMESTAMP holds year, month, day, hour, minute, and second.

When a TIME or TIMESTAMP literal is inserted into a database, the server's time zone is added to the literal. Example: If the server is running in Denmark and it is August, the server's time zone is GMT+02:00. If TIMESTAMP '1999-08-02 11:49:00' is inserted, the literal is thus adjusted with +02:00. If, however, TIMESTAMP '1999-01-02 11:49:00' is inserted, the literal is adjusted with +01:00 (because there is no daylight savings in January).

If you want to be in full control over the time zones, you should use the TIMESTAMP WITH TIME ZONE datatype. Example: TIMESTAMP '1999-08-02 11:49:00-08:00'.

The same comments apply to TIME and TIME WITH TIME ZONE.

Keywords and Identifiers

SQL 92 has a very extensive set of keywords and you may run into some surprises when selecting the spelling for an identifier of yours. It may very well collide with the spelling of an SQL 92 keyword. Please also note that an identifier cannot begin with an underscore.

There are a couple of ways to reduce the "collision problems":

There is no keyword in SQL 92 ending with an underscore, e.g. it will be perfectly legal to use SELECT_ as an identifier.

By enclosing the identifier in double quotes, essentially any spelling can be used as an identifier, e.g. "SELECT" is a legal identifier.
SQL 92 is case insensitive, .e.g Movies as an identifier is considered identical to MOVIES.

Learning more about SQL 92

You can always get hold of a copy of the standard itself from either ANSI or ISO, but the standard is not really aimed at users. A better way to get acquainted with SQL 92 is to buy "A Guide to The SQL Standard, Fourth Edition" by Chris J. Date and Hugh Darwen. This book explains all concepts and constructs of SQL 92, sometimes with quite an academic viewpoint, but nonetheless very complete and absolutely readable and understandable.

The book is published by Addison-Wesley and has ISBN #: 0-201-96426-0. An easy way to order this book is to go to www.amazon.com, but your local bookstore will most likely be able to help you as well.

If you have feedback or questions on this document, please send e-mail to doc-feedback@frontbase.com. Please reference the section number and topic. Thanks!!