FrontBase Documentation

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

6.5. FrontBase Extensions to the SQL 92 Standard

Although SQL 92 is a very comprehensive language, we have found the need for a few extensions. All of which are described below:

User management
   CREATE USER <user name> [ DEFAULT SCHEMA <schema name> ];
Creates the specified user name, i.e. inserts a row into INFORMATION_SCHEMA.USERS. Whenever a user connects to a database via e.g a CONNECT statement, a default schema is established (can be examined via CURRENT_SCHEMA). If no default schema name is given when the user name is created, a default schema name with the same spelling as the user name is assumed.
   DROP USER <user name> RESTRICT | CASCADE;
Deletes the given user name from INFORMATION_SCHEMA.USERS. If RESTRICT is specified and the user owns one or more SCHEMAs, an error message is returned and the drop fails. If CASCADE is specified, a DROP SCHEMA <schema name< CASCADE is executed for each schema the user owns.
   SET DEFAULT SCHEMA <schema name> FOR USER <user name>;
   ALTER USER <user name> SET DEFAULT SCHEMA <schema name>;
Sets or changes the default schema for the given user (see CREATE USER).

All of the above statements are transaction initiating, i.e. a COMMIT is needed to make the changes visible to other users.

Index management
   CREATE INDEX  [<index name>] FOR | ON <table name> (<column name list>);
Creates an index for the given table and columns. There is no restriction on the number of indexes that can be created for a table. There is no restriction on how many columns that can be included in an index definition. The <index name> is needed if the index is to be dropped later on. The current user must be the owner of the schema that holds the specified table.

As the index is created while-you-wait, the execution of this statement may take a little time depending on how many rows the table holds.

Please note that indexes created via CREATE INDEX are only used to optimize SELECTs, i.e. these indexes do not imply any integrity constraint checks.
   DROP INDEX <index name>;
Drops the specified index. The current user must be the owner of the schema that holds the index.

As a convenience to aid in porting existing SQL applications, we have also introduced:
   CREATE UNIQUE INDEX [<index name>] FOR | ON <table name> 
         (<column name list>);
This is semantically identical to:
   ALTER TABLE <table name> ADD [CONSTRAINT <index name>]
         UNIQUE (<column name list>) INITIALLY IMMEDIATE NOT DEFERRABLE;
All of the above statements are transaction initiating, i.e. a COMMIT is needed to make the changes visible to other users.

Table cache management
   ALTER TABLE <table name> SET CACHE ([<lower>], [<upper>], [<percent>]);
Sets or adjusts the cache parameters for the given table. <lower> and <upper> are given as absolute row counts, i.e. the server will keep min. <lower> rows and max. <upper> rows in memory. Defaults are 2.000 for <lower> and 20.000 for <upper>. The <percent> value tells the server to keep a varying number of rows in the cache, while still obeying the <upper> value. The default value for <percent> is 20.
   ALTER TABLE <table name> SET CACHE PRESERVE FALSE | TRUE;
Instructs the server to maintain (TRUE) the cache for the given table even if there are no references to the table. FALSE means that the server will discard the cache when there are no more references to the given table.
   ALTER TABLE <table name> SET CACHE PREPARE FALSE | TRUE;
Instructs the server to load (TRUE) the cache fully for the given table the first time the table is referenced. FALSE means that the cache is loaded as dictated by the actual use of the given table.

All of the above statements are transaction initiating, i.e. a COMMIT is needed to make the changes visible to other users.

Renaming table and column names
   ALTER TABLE NAME <old table name> TO <new table name>;
Effectively changes the spelling of a table name.
   ALTER COLUMN NAME <table name>.<old column name> TO <new column name>;
Effectively changes the spelling of a column name.

Transaction management
   SET TRANSACTION ISOLATION LEVEL VERSIONED;
In addition to the isolation levels defined by SQL 92, FrontBase supports an isolation level called VERSIONED. This level is ideal for the situation where many users are fetching from the database and where only a few users update the database. Users that only fetch rows will never have to wait for an update to complete, they will instead get the data as they existed prior to the start of the update. Once an update has been successfully committed, the updates are available as the next version.

The SET TRANSACTION statement has also been extended with a locking discipline option:
   SET TRANSACTION LOCKING REALISTIC | OPTIMISTIC | PESSIMISTIC;
Backup
   WRITE DATA [WITH TRANSACTION DATA];
Will initiate a backup of a live database. Once this statement has completed its execution without errors, the backup is done. The resulting file will go into /Local/Library/FrontBase/Backups/<database name>/B_<yymmdd>_<hhmmss>. The backup will latch onto the newest possible version of the database. To make sure that a rollforward (in connection with a restore operation) including ongoing transactions can succeed, the WITH TRANSACTION DATA option should be specified.
   DROP TRANSACTION DATA;
Drops the generation of transaction data (as initiated by e.g. WRITE DATA WITH TRANSACTION DATA). This will limit how close to the current state of a database a rollforward action can come.

Unloading a database
   WRITE ALL OUTPUT ('<directory name >' [, 'YES' | 'NO']);
Will write the proper SQL 92 statements to recreate the entire database into a file called schema.sql that will be created in the specified directory. If 'YES' is specified as the second argument to OUTPUT, all tables will be unloaded into flat files.

To rebuild an unloaded database you need to create a new database, connect via e.g. the FBDatabaseManager application as _SYSTEM and execute the schema.sql file.

Miscellaneous
   SELECT UNIQUE FROM <table name>;
Returns a value that can be used as a primary key (of type INT) for the given table.
   SET UNIQUE FOR <table name> (<primary key column>);
Sets the value that will be returned by the next SELECT UNIQUE... The value is calculated as SELECT MAX(<primary key column>) FROM <table name>;
   SET UNIQUE = <unsigned number> FOR <table name>;
Sets the value that will be returned by the next SELECT UNIQUE...
   CURRENT_CATALOG
Returns the current catalog as a string (VARCHAR).
   CURRENT_SCHEMA
Returns the current schema as a string (VARCHAR).

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