FrontBase Documentation

Backtrack:
  Welcome!
    5. SQL 92
      5.1. SQL 92 Primer
Updated: 20-Nov-2000
prev — next
Table of Contents

5.1.2. SCHEMAs

As mentioned earlier, a catalog can contain many SCHEMAs. A SCHEMA is owned by a user and only this user can add or drop objects in the SCHEMA. A SCHEMA object can be a table, a view, a domain, etc. 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>'; -- 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.

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