PrimeBase SQL Database Server Reference Manual

Copyright © 2001, SNAP Innovation GmbH. All rights reserved.

 

The information contained in this document applies to:

Last major update to this document on March 8th, 2001.

Table of Contents



1. Data Types

Integer types
Decimal types
Floating-point types
Boolean types
Time types
Character types
Binary types
Special types

2. DDL Reference

ADD USER
ALTER TABLE
ALTER USER
BACKUP DATABASE
BACKUP TABLE
CLOSE DATABASE
CLOSE DBMS
CLOSE TABLE
COMMENT ON
CREATE DATABASE
CREATE DEFAULT
CREATE DOMAIN
CREATE GROUP
CREATE INDEX
CREATE KEY
CREATE RULE
CREATE TABLE
CREATE VARIABLE
CREATE VIEW
DESCRIBE COLUMNS
DESCRIBE DATABASES
DESCRIBE DBMS
DESCRIBE LINKSETS
DESCRIBE OPEN DATABASES
DESCRIBE OPEN DBMS
DESCRIBE TABLES
DROP GROUP
DROP <object>
GRANT
MOUNT DATABASE
OPEN DATABASE
OPEN DBMS
OPEN TABLE
REMOVE USER
RENAME <object>
REORG TABLE
RESTORE DATABASE
REVOKE
SERVER CHECKPOINT
SERVER COMMENT
SERVER ERROR
SERVER RESTART
SERVER RESTORE
SERVER SHUTDOWN
SET VARIABLE
TRANS ERROR
TRANS RESTART
TRANS SHUTDOWN
UNMOUNT DATABASE
USE DATABASE
USE DBMS

3. Identification

Identifiers

Aliases

Database Alias
Table Alias
Column Alias

References

Object Reference
Column Reference
Column of Table Reference

5. System Procedures

Syntax

Devices

Add Device
Alter Device
Remove Device

5.3 Locations

Add Location
Alter Location
Remove Location

5.4 Partitions

Add Partition
Alter Partition
Remove Partition

6. System parameters

TransactionLimit
SystemFileLimit
LogBufferSize
LogThreshold
CheckpointThreshold
CacheSize
VirtualCacheSize
OfflineFunction
DataServerName
ConnectionLimit
ConnectionTotal
SerialNumber
ActivationKey
ExpiryDate
IdentificationString
InitialMemoryBlockSize
MemoryBlockSize
MemoryBlockTotal

Appendix A: System Database

A.1 Model Database

Domains
Tables

Appendix B: Error Codes

B.1 Data Definition Errors

Database related errors
Database alias related errors
Database objects
Database users and groups

B.2 Data Manipulation Errors

B.3 Privilege Violations

Primary errors
Secondary errors

B.4 Calculation and Conversion Errors

Invalid literal (string) values in conversion
String to floating point conversion errors
Invalid conversions
Error in calculations

B.5 Trapable Programmer Errors

Symbol related errors
Cursor related errors
Connection related errors

Appendix C: Golfers Database

Appendix D: Accessing PrimeBase SQL Database Server through Firewalls



1. Data Types



Integer types

TINYINT an unsigned 8-bit integer.

SMINT, SMALLINT a signed 16-bit integer.

INT, INTEGER a signed 32-bit integer.

<integer_literal> ::= [ '-' | '+' ] <digit> {<digit> }

<digit>           ::= '0' | '1' | '2' | '3' | '4' | '5' | '6' |
                        '7' | '8' | '9'


Decimal types

DECIMAL, NUMERIC a signed decimal number that has a total number of decimal digits and a scale, which is the total number of digits to the right of the decimal point.

MONEY a special type of decimal value that can be converted to and from character strings in the form of currency values, for example, $12.34, 1.234,000 DM.

<decimal_literal> ::= [ '-' | '+' ] <digit> { <digit> } '.'
                        <digit> { <digit> }

<money_literal>   ::= '$' <decimal_literal>


Floating-point types

SMFLOAT, SMALLFLOAT a 4-byte floating point value.

REAL a 4-byte floating point value.

FLOAT an 8-byte floating point value.

REAL10 a 10-byte floating point value. Supported on 68K MacOS only, PrimeBase-specific extension of the DAL standard.

REAL12 a 12-byte floating point value. Supported on 68K MacOS only, PrimeBase-specific extension of the DAL standard.

<float_literal> ::= [ '-' | '+' ] <digit> { <digit> }
                      [ '.' <digit> { <digit> } ] ( E | 'e' )
                      [ '-' | '+' ] { '0'..'9' }


Boolean types

BOOLEAN a truth value. PrimeBase uses 3-valued logic, therefore a boolean value can either be true false, or maybe.

<boolean_literal> ::= '$TRUE' | '$FALSE' | '$MAYBE'


Time types

DATE a 4-byte value consisting of the year, the month and the day.

TIME a 4-byte value consisting of hours (0-23), minutes, seconds and hundredths of a second.

TIMESTAMP, DATETIME an 8-byte value consisting of a date and a time value.

Submitted as character literals, according to the current value of either $datefmt, $timefmt, or $tsfmt.



Character types

CHAR, CHARACTER a fixed length character string.

VARCHAR a variable length character string.

<character_literal> ::= ( '"' | ''' ) { <character> }
                               ( '"' | ''' ) |
                          ':' <var_name>


Binary types

BIN, BINARY a fixed length byte string.

VAR, VARBINARY a variable length byte string.



Special types

GENERIC an item used to declare a variable that can assume any of the other data types when data is assigned to it.

OBJNAME a data item whose value identifies an identifier.



2. DDL Reference

This section provides a reference guide to the following statement groups: data control statements, database statements, information statements, object manipulation statements and users, groups and privileges. An explanation of the function of each command is given, followed by the syntax, and an explanation of each part of the syntax. Any idiosyncrasies of a particular command are noted under the section, "notes".

Most of the examples throughout this chapter are based on the "golfers" database which is provided in Appendix C.

The following list shows the statements of this manual grouped according to the kind of statement they are. The order in which they are documented in this manual, however, is alphabetical.

DBMS Statements


Database Manipulation Statements


Information Statements


Object Manipulation Statements


Database Privileges Statements


Server Control Statements



ADD USER

function

This statement is used to either add one or more users to a database, or, if the TO clause is included, to add the user to a specific group within the database.



syntax

ADD USER <user_name> { <user_detail> }
           {',' <user_name> <user_detail> {<user_detail>} }
           [ TO <group_name> ] ';'

<user_name>   ::= <character_literal>

<user_detail> ::= CREATOR <creator_name> |
                  PASSWORD <password> |
                  ABORT TIME <expression>

<password>    ::= <character_literal>

<group_name>  ::= <character_literal>


parameters

ADD USER identifying keywords.
<user_name> the name of the user who you are adding to the database. It must be a character literal - any printable character, enclosed by quotation marks.
<user_detail> information on the user being added - concerning password, creator name and the user's transaction abort time, which is the amount of time that a transaction started by this user may be idle, before it is aborted by the transaction manager.
CREATOR keyword, indicating that the creator name for the new user follows. This name must adhere to the rules for identifiers. This clause is only necessary if you are adding a new user to the database. If this clause is omitted, the default creator name is "Common".
<creator_name> the creator name of the user. The users creator name is used when the user creates objects. All objects have names consisting of two parts. The first part is the creator name of the user creating the object, and the second part is the name given to the object at creation time
PASSWORD keyword, indicating that the password to be used by the new user follows. As with the CREATOR clause, this clause is only necessary if you are adding a new user to the database. The default password is an empty string:"".
<password> the actual password the user will use.
ABORT TIME This clause allows the user's transaction abort time to be set. The value given is in seconds, and represents the amount of time a transaction is allowed to remain idle before it is aborted. An idle transaction is a transaction that does no disk I/O. It is also the time that the system takes to detect a complex deadlock between transactions. A complex deadlock is a deadlock that involves more than two transactions. The default abort time is 30 minutes.
<expression> A value given in seconds, representing the amount of time a transaction is allowed to remain idle before it is aborted.
TO this clause enables you to assign the user to a specific group within the database. This clause is optional.
<group_name> the actual name of the group to which you are assigning this user.


notes

A user may belong to any number of groups within the database; not just one group.

The <user_name> is unique within the whole database, and is used for log-in purposes. It identifies a user, and as a result, identifies also that user's privileges.

The <creator_name> is an identifier, and therefore must conform to the rules for identifiers. It is not necessarily database-wide unique. This name also becomes a component of the qualified name of any objects created by that user. In addition, if a user specifies a database object without completely qualifying it, then that user's <creator_name> is automatically used. If no object is found, the system will try using the creator names "Common" and "System".

New users to a database are automatically assigned to the system group called "Public".

To add a user to a normal database, the database must be in use, and the user must be the DBA.

To add a user to the Master database, the user must be an SA, and the Master database must be in use. Users added to this database do not receive SA status; they are, however, allowed to create their own databases. A user of the Master database can be promoted to SA, by granting SA privileges.

Users of the Master database that have SA (System Administrator's) privileges can open any database, and SAs are automatically made DBA (Database Administrator) of any database the open. However, normal users of the Master database that do not have SA privileges are not automatically made DBA of any database they open.

Users of the Master database, that do not have SA privileges, are not automatically made DBA of any database they open, as is the case with the SA.



examples

Example

  /*
  ** In this example, the user, called Caspar Fyson is
  ** added to the database. He is given a creator name,
  ** "golf", the password, "Birdy", and is made a member
  ** of the group called "GolfersPros".
  */

  CREATE GROUP "GolfersPros";

  ADD USER "Caspar Fyson" CREATOR golf PASSWORD "Birdy";
  ADD USER "Caspar Fyson" TO "GolfersPros";
 


see also

ALTER USER, REMOVE USER, GRANT, REVOKE, CREATE GROUP, DROP GROUP



ALTER TABLE

function

This statement is used to change the structure of an existing relation.



syntax

ALTER TABLE <table_reference> <column_command> ';'

<column_command> ::= <append_column> | <rename_column>

<append_column>  ::= (APPEND | ADD) [COLUMN] <column_def>

<rename_column>  ::= RENAME [COLUMN] <column_name>
                     TO <column_name>


parameters

ALTER TABLE statement identifying keywords.
<table_reference> the qualified name of the relation you want to alter.
<column_command> defines how you want to alter the relation; either APPEND or RENAME a column.
<append_column> adds another column to the relation.
<rename_column> changes the name of an existing column.
APPEND keyword.
<column_def> the definition of the column that is to be altered; either simple column definition or composite column definition. (See CREATE TABLE)
RENAME keyword.
<column_name> the name of the column you want to rename.


notes

In the APPEND clause, the name of the column must be distinct from those already existing in the table. If <column_def> is a composite column definition then the component columns must be simple columns already existing in the table. New columns must allow NULLs, as the value stored in new simple columns is NULL.



examples

Example

  ALTER TABLE Golfers APPEND COLUMN medalswon INT;

  ALTER TABLE Golfers RENAME COLUMN medalswon TO medals;
 


see also

CREATE TABLE, DROP TABLE, RENAME TABLE, REORG TABLE, BACKUP TABLE, OPEN TABLE, CLOSE TABLE



ALTER USER

function

This statement is used to alter details associated with a user.



syntax

ALTER [ USER <user_name> ] <user_detail>
      { <user_detail> }
      { ',' <user_name> <user_detail>
        { <user_detail> } } ';'

<user_detail> ::= CREATOR <creator_name> |
                  PASSWORD <password> |
                  ABORT TIME <expression>

<password>    ::= <character_literal>

<group_name>  ::= <character_literal>


parameter

ALTER statement identifying keywords.
USER if this clause is omitted then the current user is assumed.
<user_name> the name of the user to be affected by this statement.
CREATOR to set the new creator name for the user.
<creator_name> the new creator name. It must conform to the rules for identifiers.
PASSWORD to set the new password for the user.
<password> the new password.
ABORT TIME the maximum time a transaction (that belongs to the user) may be idle. A transaction is idle when not reading or writing. For example, when a transaction is waiting for a lock, it is idle.
<expression> the time in seconds that the transaction may be idle.


notes

Users may set their own passwords, but only the DBA may set the creator name of a user and the password of another user.

If the USER clause is omitted, then the current user is assumed.



examples

Example

  /*
  ** In this example, the password of the user, Caspar
  ** Fyson is altered, and is changed to "Eagle". The
  ** ABORT TIME is set at 5 seconds.
  */

  ADD USER "Caspar Fyson" PASSWORD "WOODY";

  ALTER USER "Caspar Fyson" PASSWORD "Eagle" ABORT TIME 5;
 


see also

ADD USER, REMOVE USER, GRANT, REVOKE, CREATE GROUP, DROP GROUP



BACKUP DATABASE

function

This statement is used to backup a database.



syntax

BACKUP DATABASE <database_name>
                { <file_location_spec> }
                { <backup_options> } ';'

<file_location_spec> ::= [ DATA | INDEX ] [ IN ]
                         LOCATION <character_literal>

<backup_options>     ::= <include_index> |
                         <preserve_previous>

<include_index>      ::= ( WITH | WITHOUT ) INDEX

<preserve_previous>  ::= ( REPLACE | PRESERVE | UPDATE )
                         [ PREVIOUS ]


parameters

BACKUP DATABASE keywords.
<database_name> the name of database.
<file_location_spec> this is an optional clause used to specify backup locations for the database.
DATA indicates a path specified for data files.
INDEX indicates a path specified for index files.
IN an optional keyword.
LOCATION indicates file system location follows.
<character_literal> location in the file system.
( WITH | WITHOUT ) INDEX these are optional keywords, to specify whether or not the database should be backed up including the indices, ( WITH INDEX ).
( REPLACE | PRESERVE | UPDATE) [ PREVIOUS ] these are optional keywords, to indicate whether a previous backup in the backup location should be overwritten or not. UPDATE mode is the same as REPLACE with the exception that blobs that have not changed since the last backup will not be copied to the backup.


notes

The BACKUP/RESTORE facility in PrimeBase is designed to guarantee complete recovery of a database including changes applied to the database after the backup was completed.

Backup of a database can be done while the database is online (i.e. while it is in normal use), using the BACKUP DATABASE statement. A backup of a database can be restored using the RESTORE DATABASE statement. The backup image of a database looks identical to the normal database image. However, if the backup was made while the database was in use, then the image may not be consistent, due to the fact that the tables were copied at different times. If the database was not in use during backup, the backup image can be mounted as any other database, provided the backup is the first of the database in that location. If this is done, the mounted database will reflect the state of the database at the time of the backup, and will not include any subsequent changes. It may be necessary to mount a backup image if any of the log files at the time of backup has been lost or corrupted.

By default, offline logs are deleted by the server. Offline logs are logs no longer needed by the server to do a normal restart (recovery). In order to bring a database completely up-to-date from a backup, the offline logs must be archived. To do this, the system administrator must set the system variable OfflineFunction to "Archive", as follows:

OPEN DATABASE master;

SET VARIABLE offlinefunction = "Archive";

CLOSE DATABASE;

To set the offline log function back to deletion, set Offline Function to "Delete". When the offline location function is set to Archive, log files are not deleted, but copied to a log archive location. An archived log is given a different name (the first letter of the log name is changed). The restore function will only look for an archived log in the archive location to which it was copied. In order for restore to succeed, all required archive logs must be available. This means that all volumes containing archive logs must be online.



examples

Example

  /*
  ** In this example, the database Golfers is backed up.
  */

  BACKUP DATABASE Golfers;
 


BACKUP TABLE

function

This statement does a backup of a table.



syntax

BACKUP TABLE <table_reference> ';'


parameters

BACKUP TABLE statement identifying keywords.
<table_reference> name of the table you want to backup.


notes

The backup of the table is added to the last backup done of the database. The backup options and locations used are those specified in the original database backup command. It is necessary to backup a table after it has been reorganized (REORG TABLE).



CLOSE DATABASE

function

This statement closes a currently open database.



syntax

CLOSE DATABASE [ <database_alias> ]';'
<database_alias> ::= <identifier>


parameters

CLOSE DATABASE statement identifying keywords.
<database_alias> an identifier. If no alias was specifically given in the ALIAS clause of OPEN DATABASE, then the default database is closed. An alias must conform to the rules for identifiers. See 3.1 Identifiers.


notes

When a database is opened it becomes the current default database. If the default database is closed it is not possible to determine which open database will become the new default database (unless there is only one open database left). The USE DATABASE statement below should be used to reset the default database. You can first use the statement DESCRIBE OPEN DATABASES, which lists the default database.



examples

Example

  /*
  ** In this example the database "Golfers" is closed. In
  ** OPEN DATABASE, "Golfers" was assigned the alias "G",
  ** which is then used in this CLOSE DATABASE statement.
  */

  OPEN DATABASE Golfers ALIAS G;

  CLOSE DATABASE G;
 


Example

  /*
  ** In this example, the same as above is achieved, but
  ** in two steps rather than one. Notice that the alias
  ** is not included in the syntax of the close statement.
  */

  OPEN DATABASE Golfers ALIAS G;

  USE DATABASE G;

  CLOSE DATABASE;
 


see also

BACKUP DATABASE, RESTORE DATABASE, OPEN DATABASE, USE DATABASE, CREATE DATABASE DROP DATABASE, MOUNT DATABASE, UNMOUNT DATABASE



CLOSE DBMS

function

This statement closes an open DBMS. If <dbms_brand> is not given, then the current DBMS is closed. All open databases of the DBMS are also closed by this statement.



syntax

CLOSE [<dbms_brand>] DBMS ';'


parameters

CLOSE DBMS keywords.
<dbms_brand> name of dbms brand to be closed.


examples

Example

  CLOSE DBMS;
 


Example

  CLOSE my_connection_alias DBMS;
 


notes

In PrimeBase the a DBMS is a server or gateway. An "open dbms" is an open connection to a server/gateway. The command CLOSE DBMS closes the connection.

Note that the name of the DBMS must be placed in quotes if it contains spaces or special characters.



see also

OPEN DBMS, USE DBMS, DESCRIBE DBMS, DESCRIBE OPEN DBMS



CLOSE TABLE

function

This statement closes a table opened with the OPEN TABLE statement.



syntax

CLOSE TABLE <table_reference> ';'

parameters

CLOSE TABLE keywords.
<table_reference> the name of the table being closed.


examples

Example

  /*
  ** This example is a transaction which opens
  ** (locks) the table "Golfers" for EXCLUSIVE
  ** access for the time of the transaction.
  ** Afterwards the table is closed (unlocked)
  ** again.
  */


  BEGIN;

  OPEN TABLE Golfers FOR EXCLUSIVE UPDATE;

  CLOSE TABLE Golfers;

  COMMIT;
 


notes

The PrimeBase server ignores this statement, as a table is automatically closed at the end of the transaction in which the table was opened.



see also

CREATE TABLE, DROP TABLE, RENAME TABLE, ALTER TABLE, BACKUP TABLE, REORG TABLE, OPEN TABLE



COMMENT ON

function

This statement allows you to place a comment on any type of object and on columns. The type of object may be specified, but it is optional.



syntax

COMMENT ON (<object_comment> | <column_comment>)
        IS <expression> ';'

<object_comment> ::= [<object_type>] <object_reference>

<object_type>    ::= DOMAIN | TABLE | KEY | DEFAULT |
                     INDEX | RULE | VIEW | VARIABLE

<column_comment> ::= COLUMN <column_of_table_reference>


parameters

COMMENT ON statement identifying keywords.
<object_comment> specifies the object to which the comment is bound.
<object_type> an optional specification of the type of the object.
<object_reference> the name of the object.
<column_comment> specifies the column to which the comment is bound.
COLUMN this keyword is required when placing a comment on a column.
<column_of_table_reference> the qualified name of a simple or composite column. The syntax is explained at the end of this Reference section.
<expression> a string (value of type CHAR or VARCHAR) that is the comment text to be placed on the object/column.


notes

A previous comment may be removed by specifying the comment as an empty string, ("").



examples

Example

  /*
  ** In this example, a comment is added to the Courses
  ** table.
  */

  COMMENT ON TABLE Courses
  IS "Each club has a number of courses. " +
     "The details of each hole of each course " +
     "are stored in the Courses table.";
 


see also

DESCRIBE DBMS, DESCRIBE OPEN DBMS, DESCRIBE DATABASES, DESCRIBE OPEN DATABASES, DESCRIBE TABLES, DESCRIBE LINKSETS



CREATE DATABASE

function

This statement creates the necessary system folders and files for a new database.



syntax

CREATE DATABASE <database_name> { <file_location_spec> } ';'

<file_location_spec> ::= [ DATA | INDEX] [ IN ]
                         LOCATION <character_literal>


parameters

CREATE DATABASE statement identifying keywords.
<database_name> a unique name for the database.
<file_location_spec> an optional location specifications for data and/or index files.
DATA keyword specifies that path name for data files follows.
INDEX keyword indicates that path name for index files follows.
IN optional keyword.
LOCATION indicates that path name follows.
<character_literal> path name.


notes

Only system administrators (SA) and master database users with DBA status may create a database.

The new database is created but not opened.

The creator of the database is entered as the second user of the database with DBA privileges. The first user of a database is the user "System". "System" is the creator and owner of the system tables and other system objects. When a system administrator opens a database in which he is not a user, he is then considered to be the user, "System".

In creating a database two file system locations may be specified. The location for the data (DATA keyword in the IN LOCATION clause), and the location for the indices (INDEX keyword). These locations may be the same (i.e. both DATA and INDEX keywords may be omitted. If no location is specified, then location for both data and index is the DataServer root path by default. The dataserver root path is given when installing the dataserver, and contains the Master and Model databases. The dataserver will append a directory to the specified location, and then place the data/index files within that directory. The name of the directory is identical to the name of the database.

A database name must be an identifier, whether specified as a character string or not. Since the name of the directory containing the database files is identical to that of the database, the names of databases are limited as for directory names of the underlying operating system. For example, a dataserver running under DOS would only support database names of maximum 8 characters in length. However, the system ensures that the names of databases are case insensitive like all other identifiers.

New databases are created by duplicating the model database. The SA is able to configure created databases by modifying the model database.

Warning: Do not remove, rename, or delete any files or directories created by the server. If you wish to delete a database, use the DROP DATABASE statement. If you want to change the location of a database, you can use the MOUNT DATABASE and UNMOUNT DATABASE command.



examples

Example

  /*
  ** In this example, the database "Golfers2" is created.
  */

  CREATE DATABASE Golfers2;
 


see also

RESTORE DATABASE, BACKUP DATABASE, OPEN DATABASE, CLOSE DATABASE, USE DATABASE, DROP DATABASE, MOUNT DATABASE, UNMOUNT DATABASE



CREATE DEFAULT

function

This statement is used to specify a value that will be automatically inserted into a column, if no value is explicitly supplied at insert time.



syntax

CREATE DEFAULT <default_reference> ON
               ( [ COLUMN ] <column_of_table_reference> |
                 DOMAIN <domain_reference> )
               <default_def> ';'

<default_def> ::= AS ( <expression> |
                       USER |
                       SERIAL <variable_reference> |
                       NOW )


parameters

CREATE DEFAULT statement identifying keywords.
<default_reference> qualified name of default.
ON identifying keyword; indicates that you are specifying the column, or simple domain to which the default value will be bound.
COLUMN optional identifying keyword; indicates that the default is to be bound to a column.
<column_of_table_reference> the qualified name of a simple column.
DOMAIN keyword; indicates that you want to bind the default to a simple domain.
<domain_reference> the qualified name of a simple domain.
<default_def> definition of the default.
AS keyword.
<expression> an expression that is evaluated when the default is created to produce a literal value.
USER the USER function returns the name of the current user in the case of character columns, or the database user identifier in the case of numeric columns.
SERIAL the SERIAL function returns the next in sequence of a particular data type.
<variable_reference> the name of a variable of type counter that has already been defined, see CREATE VARIABLE.
NOW this keyword returns the current time or date.


notes

Defaults may be specified on a simple column or a simple domain. If a default is placed on a domain, a further default may still be specified on a column defined on that domain. This default takes priority over the domain default. If no default value is stated, the value is recorded as missing (NULL). If the column does not allow missing values an insert in which the column value is not specified is rejected. On insert the column default takes priority.

The default value must be compatible with the data type of the column or domain to which it is bound.



examples

Example

  /*
  ** In this example, a default is created on the domain,
  ** GolferID2, called GolferDef2. It is a SERIAL default,
  ** based on the counter variable, called GolferCnt2.
  */

  CREATE COUNTER INTEGER GolferCnt2 = 1;

  CREATE DOMAIN GolferID2 INTEGER NOT NULL;

  CREATE DEFAULT GolferDef2 ON DOMAIN GolferID2
    AS SERIAL GolferCnt2;
 


Example

  /*
  ** In this example, a default, ParDef2, is created on
  ** the column Stroke of the table Courses. A default
  ** value of 4 is always inserted into this column.
  */
  
  CREATE DEFAULT ParDef2 ON Courses.Stroke AS 4;
 


see also

DROP DEFAULT, RENAME DEFAULT



CREATE DOMAIN

function

The domain manipulation statement, create domain, allows the declaration of a user-defined, extended data type, which is distinct from any other domain within the database (simple or composite).



syntax

CREATE [ PRIMARY ] DOMAIN <domain_reference>
                          ( <simple_domain_def> |
                            <composite_domain_def> ) ';'

<simple_domain_def>    ::= <data_type>
                           { [ ',' ]
                             <domain_specification> }

<domain_specification> ::= <missing_specification> |
                           <arithmetic_specification> |
                           <order_specification>

<missing_specification>    ::= [ NOT ] NULL

<arithmetic_specification> ::= ARITHMETIC [ NOT ]
                               APPLICABLE

<order_specification>  ::= ORDER [ [ NOT ] APPLICABLE ]
                           [ AS <sequence> ]

<sequence>             ::= COLLATING SEQUENCE
                             <variable_reference> |
                           <system_sequence>
                             { ',' <system_sequence> }

<system_sequence>      ::= COMMON |
                           CASE INSENSITIVE |
                           IGNORE DIACRITICAL MARKS

<composite_domain_def> ::= '(' <simple_domain>
                             ',' <simple_domain>
                               { ',' <simple_domain> } ')'

<simple_domain>        ::= <domain_reference> | <data_type>


parameters

CREATE [PRIMARY] DOMAIN statement identifying keywords.
<domain_reference> the identifying name of the domain you are creating.
<simple_domain_def> the actual definition of a simple domain; a domain that is based on a single basic data type.
<data_type> a basic data type.
<domain_specification> either a missing specification, an arithmetic specification, or an order specification.
<missing_specification> an indication of whether values in a column based on a domain may be missing. The default is: missing values permitted.
[NOT] NULL NULL means that values may be missing; NOT NULL indicates that values may not be missing.
<arithmetic_specification> the arithmetic specification indicates whether arithmetic operations ( '*', '/', '+', '-' , etc.) are allowed on the domain. The default is: arithmetic not permitted.
<order_specification> see notes for a full explanation of this clause.
ORDER APPLICABLE declares that the comparison operators; '<', '>', '<=' and '>=' can be meaningfully applied to the extended data type being defined. The default is: order not applicable.
NOT negates the above statement, (i.e. the comparison operators cannot be meaningfully applied).
<variable_reference> a system variable of type collating sequence. System variables of this type may be created by the user using the CREATE VARIABLE statement. See notes for an explanation of the different types of system defined sequences.
<system_sequence> There are three system defined sequences. See notes for details.
<composite_domain_def> a domain defined on a combination of simple domains
<simple_domain> either the name of an existing simple domain, or a basic data type.
<domain_reference> the name of a previously declared simple domain.


notes

A domain in its definition stores information as to its basic data type, whether values of the domain are allowed to be missing, and information as to whether the comparative and arithmetic operators can be meaningfully applied on data of this domain. (The operator '=' can always be meaningfully applied.)

The range of values permitted on a domain may be specified by placing a rule on the domain, (see CREATE RULE).

By default, the <missing_specification> should be set as NULL in the case of a non-primary domain, and NOT NULL in the case of a primary domain.

By specifying that a domain is primary (CREATE PRIMARY DOMAIN), the user indicates that values in primary keys defined on that domain must be domain-wide unique. For example, it is possible to create a number of primary keys that draw values from a common domain. The system ensures that the sets of values in various primary keys on a primary domain are disjoint. If the domain is not primary, uniqueness of primary key values on the domain are only ensured within the table on which the primary key is defined. Note that if there is only ONE primary key on a domain then it makes no difference whether the domain is primary or not.

Please remember, however, that when we talk about a primary domain, we do not mean a domain that has a primary key defined on it, but we mean a domain that has been explicitly declared as primary.

Domains cannot be declared recursively, in terms of one another.

Composite domains may include basic data types as well as simple domains as components. Domains cannot be declared recursively, in terms of one another.

The order of the components of a composite domain is significant in that, when sorting values in a domain, the left-most component is considered the most important. This means that if a domain is ordered, its components are sorted from right to left, and compared left to right.

Note that <domain_reference> in <simple_domain> is the name of a previously declared simple domain.

The order specification statement declares the following information:

1. Whether or not the operators '<', '>', '<=', or '>=' can be meaningfully applied to values of the domain (ORDER APPLICABLE). If order is not applicable, only equality tests, (equals ('='), not-equals ('!=')), may be done when comparing two values of the domain. Sorting, however, is still possible.

2. That a collating sequence should be used when comparing values of the domain. The name of the collating sequence may be explicitly specified (<variable_reference>), or a system defined collating sequence can be selected using the COMMON, CASE INSENSITIVE... keywords.

Warning

Values of domain A cannot be directly assigned to or directly compared to values of domain B, even though the underlying datatypes and sizes (e.g. domain A and domain B have the underlying datatype VARCHAR(64)) might be identical, else the following error will occur:

  Mismatch of underlying column domains in binary operation.

Explicit datatype conversion must be applied in such cases. For example change...

  SELECT * FROM MyTableA a, MyTableB b
  WHERE a.street = b.street;

...(assuming a.street and b.street are not based on the same (VARCHAR) domain) to...

  SELECT * FROM MyTableA a, MyTableB b
  WHERE VARCHAR( a.street ) = VARCHAR( b.street );

Collating Sequences

COMMON: The common ordering is an improved ordering of the ASCII character set, which places alphabetically similar characters together, and upper case before lower case:

AÄaäâBbCÇcç...

CASE INSENSITIVE: In a case-insensitive sequence, the case of the characters is ignored. In such a sequence, A=a, and Ä=ä, etc.

IGNORE DIACRITICAL MARKS: When ignoring diacritical marks, A=Ä, and a=ä, for example.

examples

Example

  /*
  ** In this example a series of domains, defaults and
  ** variables are created, to provide a structure for
  ** the table "Golfers".
  */

  CREATE DATABASE GolfersTemp;

  OPEN DATABASE GolfersTemp;

  CREATE COUNTER INTEGER GolferCnt = 1;

  CREATE DOMAIN GolferID INTEGER NOT NULL;

  CREATE DEFAULT GolferDef ON DOMAIN GolferID
    AS SERIAL GolferCnt;

  CREATE DOMAIN NameType VARCHAR[55] ORDER APPLICABLE
    AS CASE INSENSITIVE;

  CREATE DOMAIN StatusType CHAR[8];


  CREATE RULE StatusRule ON StatusType AS StatusType IN
  (
    'Amateur',
    'Pro',
    'Pro/Am'
  );


  CREATE DOMAIN HandicapType SMINT;

  CREATE RULE HandicapRule ON HandicapType AS HandicapType
  BETWEEN 36 AND -5;

  CREATE COUNTER INTEGER ClubCnt = 1;

  CREATE DOMAIN ClubID INTEGER;

  CREATE DEFAULT ClubDef ON DOMAIN ClubID
    AS SERIAL ClubCnt;


  CREATE TABLE Golfers
  (
    ID GolferID NOT NULL,

    SurName NameType NOT NULL,

    FirstNames NameType NOT NULL,

    Name (SurName, FirstNames),

    Title CHAR[10],

    Sex CHAR[1] NOT NULL,

    Nationality NameType,

    DateOfBirth DATE,

    Status StatusType,

    Handicap HandicapType,

    MemberOfClub ClubID,

    Earnings MONEY[12,2]
  );



  /*
  ** Set the currently in use database
  ** back to Golfers again.
  */

  USE DATABASE Golfers;
 


see also

DROP DOMAIN, RENAME DOMAIN



CREATE GROUP

function

This statement creates a group, or number of groups, within a database.



syntax

CREATE GROUP <group_name> { ',' <group_name> } ';'


parameter

CREATE GROUP statement identifying keywords.
<group_name> name of the group you are creating. This must be a character literal, and must be enclosed in quotation marks.


notes

The group will be created in the database that you are currently working in.



examples

Example

  /*
  ** In this example a group is created, called
  ** GolfersPros, containing the names of
  ** professional golfers, who use this database.
  */

  CREATE GROUP "GolfersPros";
 


see also

ADD USER, ALTER USER, ADD USER, REMOVE USER, GRANT, REVOKE, DROP GROUP



CREATE INDEX

function

This statement is used to create an index on a column, group of columns or a domain.



syntax

CREATE <index_spec> <index_reference>
  ON [TABLE] <table_reference>  <column_group>  ';'

<index_spec>::= INDEX { SUPPRESS ZERO | SUPPRESS NULL }


parameters

CREATE statement identifying keyword.
<index_spec> this specifies the definition of the index.
<index_reference> the qualified name of the index.
ON identifying keyword; introduces the clause indicating the object on which the index is created.
<table_reference> the qualified name of the table on which the index is to be created.
<column_group> an ordered list of columns (simple and/or composite), on which the index is to be created.
INDEX identifying keyword; indicates that an index is being created.
SUPPRESS ZERO identifying keyword indicating that these values are to be excluded from the index.
SUPPRESS NULL identifying keyword; indicates that these values are to be excluded from the index.


notes

Indices are a performance related feature, for example, they can speed up data retrieval, but can slow down data insert and update.

If duplicate key values occur very often, the speed of data retrieval will not increase, and update/delete will slow down.

The fewer rows there are that match the search conditions, the more effective the index will be.

Remember that is zero depression has been defined on an index, a search on that index will not retrieve any zeros.



examples

Example

  /*
  ** In this example an index is created, called
  ** GolfersIndex2, and is defined on the table
  ** called "Golfers" - on the column, ID. The
  ** primary key for the table is also defined on
  ** this column. It is recommended that you define
  ** your indices on the same columns as your primary
  ** keys.
  */

  CREATE INDEX GolfersIndex2 ON Golfers (Earnings);
 


Example

  /*
  ** In the next example, an index is defined on a
  ** composite column. In this case, each separate
  ** component of the composite column must be listed.
  ** You may not simply give the name of the composite
  ** column itself.
  */

  CREATE INDEX CoursesIndex2
  ON Courses (Club, Course);
 


see also

DROP INDEX, RENAME INDEX



CREATE KEY

function

The key manipulation statement, CREATE KEY, defines a primary, candidate or foreign key on a base relation.



syntax

CREATE <key_spec> <key_reference>
  ON [ COLUMN ] <column_of_table_reference>
    [ <reference_spec> ] ';'

<key_spec>           ::= UNIQUE | ( ( PRIMARY |
                           CANDIDATE | FOREIGN ) KEY )

<reference_spec>     ::= REFERENCES <table_reference>
                           { ',' <table_reference> }
                           { <triggered_action> }

<triggered_action>   ::= ON UPDATE <referential_action> |
                         ON DELETE <referential_action>

<referential_action> ::= RESTRICT | CASCADE | SET NULL |
                         SET DEFAULT


parameters

CREATE keyword.
<key_spec> key specification, either primary, candidate or foreign.
<key_reference> the qualified name of the simple or composite key you wish to define.
UNIQUE alternate keyword to specify the definition of a candidate key.
PRIMARY, CANDIDATE, FOREIGN
COLUMN identifying keyword; indicates that the key you are creating is to be bound to a column. This keyword is optional.
<column_of_table_reference> the qualified name of a simple or composite column.
<reference_spec> an optional specification of the target table(s) of the foreign keys. Each target table must have a primary key defined on the same domain as the foreign key. Only one of the tables is required to contain the corresponding primary key value.
REFERENCES keyword.
<table_reference> the qualified name of a target table.
<triggered_action> indicates that after certain commands performed on any of the target tables, a particular function is to be carried out.
ON UPDATE keywords, indicating that when an update is carried out on the target table, referential action must be taken!
ON DELETE keywords, indicating that when a delete is carried out on the target table, referential action must be taken.
<referential_action> from a list of functions, you can specify what happens to foreign key values that no longer have corresponding primary key values.
RESTRICTED The update or delete operation is restricted to the case where there are no related values (it is otherwise rejected).
CASCADES The update, or delete operation "cascades" to update the foreign key in all related values.
SET NULL On update, or deletion, the foreign key is set to null in all related values and the target record is then updated, or deleted (of course, this case could not apply if the foreign key cannot accept nulls in the first place).
SET DEFAULT On foreign key columns that have a default bound to them, on an update, or delete, operation to the primary key column, the foreign key is then updated to the default value - set by the CREATE DEFAULT statement.


notes

Foreign keys may only be defined on a column that is based on a previously defined domain (simple or composite).

All primary keys must fulfill the entity integrity rule, which states: no component of the primary key in a base relation is allowed to contain a NULL. When a primary key is defined on a column which allows NULLs, the column will no longer accept missing values.

All primary and candidate keys must satisfy the uniqueness property, which states: No two tuples of a key may have the same value, therefore although it is allowed for candidate keys to be defined on a column that allows NULLs, this columns may only include one NULL, as two NULLs are considered as duplicate values.

All composite candidate and primary keys must satisfy the minimality property, which states: If a candidate key is composite, then no component of that key can be removed from that combination without the uniqueness of that key being lost. However, adherence to this requirement of the relational model cannot be verified by the DBMS.

All foreign keys must fulfil the referential integrity rule, which states: The database may not contain any unmatched foreign key values. These values are all drawn from the primary key which is being referenced, via the primary domain on which the foreign key is based.

A base relation must have one and only one primary key defined on it.

A primary key must be defined on a base relation before the relation can be used.



examples

Example

  /*
  ** In this example, a primary key is created,
  ** GolfersPk, and defined on the column, ID,
  ** in the table, Golfers.
  */

  CREATE TABLE Test ( ID integer );

  CREATE PRIMARY KEY GolfersPk2 ON Test.ID;
 


Example

  /*
  ** A candidate key is defined on the same table, on the
  ** column ID.
  */

  CREATE TABLE Test ( ID integer );

  CREATE CANDIDATE KEY GolferNameCk2 ON Test.ID;
 


see also

DROP KEY, RENAME KEY



CREATE RULE

function

Rules can be applied to tables or simple domains. They restrict the values and combinations of values of a row of a table or a simple domain.



syntax

CREATE RULE <rule_reference>
  ON ( [TABLE] <table_reference> |
    [DOMAIN] <domain_reference> ) <rule_def> ';'

<rule_def>::= ( CHECK | AS ) <search_condition>


parameters

CREATE RULE statement identifying keyword.
ON specify to which table or domain the rule is to be bound.
<rule_reference> the qualified name of the rule.
<table_reference> the qualified name of the table to which you want to bind the rule.
<domain_reference> the qualified name of the domain to which the rule is bound. Rules can only be specified on simple domains.
<rule_def> specifies the conditions of the rule.
<search_condition> can be any expression that would be valid in a WHERE clause. Subqueries, however, are not allowed.


notes

If a base table is dropped, all rules defined on that table are also dropped.

In order for a new rule to be defined on a given table, the old rule must first be dropped.

When a rule is defined on a table, existing rules are not checked to conform to the rule. Only subsequent inserts and updates are checked.

Columns or components of columns referenced in <search_condition> are limited to the columns of <table_reference> (a single row). Note that only the comparison operators are defined on composite columns

Rules can only be specified on a simple domain (i.e. a domain defined on a single basic data type). In the case of rules defined on domains, the name of the domain (optionally qualified by the creator name) may be used in place of columns in <search_condition>.



examples

Example

  /*
  ** In this example a rule called StatusRule2
  ** is defined on the domain StatusType2.
  */

  CREATE DOMAIN StatusType2 CHAR[8];

  CREATE RULE StatusRule2 ON StatusType2
    AS StatusType2 IN ( 'Amateur', 'Pro', 'Pro/Am' );
 


Example

  /*
  ** A rule, ParRule is defined on the column
  ** NoOfCourses in the table Clubs.
  */

  CREATE RULE ParRule ON Clubs
    AS NoOfCourses IN ( 3, 4, 5 );
 


see also

DROP RULE, RENAME RULE



CREATE TABLE

function

This statement creates a relation within a currently open database.

syntax

CREATE TABLE <table_reference> <table_def> ';'

<table_def>     ::= '(' <column_def> {',' <column_def> } ')'

<column_def>    ::= <column_name> ( <simple_column_def> |
                      <composite_column_def> )

<simple_column_def>    ::= <simple_domain>
                             [<missing_specification>]

<simple_domain> ::= <domain_reference> | <data_type>

<composite_column_def> ::= <column_group>
                             [<domain_reference>]

<column_group>  ::= '(' <column_name>
                      { ',' <column_name> } ')'


parameters

CREATE TABLE statement identifying keywords.
<table_reference> the qualified name of the relation you are creating.
<table_def> the definition of the relation being created, which is a list of column definitions.
<column_def> the definition of a column of the relation, which consists of a column name followed by a simple or composite column specification.
<column_name> the identifying name for a particular column..
<simple_column_def> the definition of a simple column, specifying the type or domain of the column, and whether or not this column may contain NULLS (<missing_specification>).
<simple_domain> the domain from which a simple column draws its values. This must be an already existing simple domain (see section on domains), or a basic data type.
<data_type> any one of the basic data types. See "Data Types".
<composite_column_def> the definition of a composite column, consisting of two, or more, simple columns, optionally based on a composite domain.
<column_group> an ordered list of simple columns that comprise the composite column.
<domain_name> the composite domain on which the composite column is based.


notes

A column can either be simple or composite. A simple column is defined using a simple domain (<simple_domain> in <simple_column_def>). Please note however, that <simple_domain> also allows the direct specification of a basic data type. This means that the user is not required to declare a domain for every column in the database, and also renders PrimeBase compatible with other database management systems.

A composite column is a combination of simple columns.

If a composite domain is specified in the declaration of a composite column, it is not required that the simple columns mentioned in the <column_group> have been previously declared, as the simple column definition can be deduced from the composite domain that follows, (<domain_name> in <composite_column_def>).

The <missing_secification> for simple columns declared in this way is as per default. If the <missing_specification> in the definition of a simple column is omitted, the column will assume the <missing_specification> of the underlying domain, or missing value allowed (NULL) in the case of simple columns defined on a basic data type. If the underlying simple domain is defined as NOT NULL, then the column cannot be defined as NULL.

Please note, that a simple column may be a member of more than one composite column.

A table may not be used until a primary key has been defined on it. Equally, when a primary key is dropped from a base relation, that relation is temporarily disabled until a new primary key has been defined.

A CHAR or VARCHAR defined column with NULLs allowed takes up much more space than a VARCHAR or CHAR column where NULLs are not allowed.



examples

Example

  /*
  ** A table is created, called Results. Two of the
  ** columns are defined on the domains, CompetitionID,
  ** and GolferID. A composite column has been created,
  ** called Key. The primary key is defined on this
  ** column, as is the index.
  */

  CREATE TABLE Results2
  (
    Year SMINT NOT NULL,
    Competition CompetitionID NOT NULL,
    Place SMINT NOT NULL,
    Key ( Year, Competition, Place ),
    Golfer GolferID,
    TotalScore SMINT,
    Points SMINT,
    Winnings MONEY[10,2]
  );

  CREATE PRIMARY KEY Results2Pk ON Results2.Key;

  CREATE FOREIGN KEY Results2GolferFk ON Results2.Golfer;

  CREATE INDEX Results2Index
    ON Results2 (Year, Competition, Place);
 


see also

DROP OBJ, RENAME TABLE, ALTER TABLE, REORG TABLE, BACKUP TABLE, OPEN TABLE, CLOSE TABLE



CREATE VARIABLE

function

This statement is used to create a database variable.



syntax

CREATE (<collating_sequence> | <user_counter> |
         <user_variable> ) ';'

<collating_sequence>  ::= COLLATING SEQUENCE [VARIABLE]
                            <variable_reference> '='
                            <comparison_order>

<comparison_order>    ::= '('<equivalent_sequence> { ','
                            <equivalent_sequence> } ')'

<equivalent_sequence> ::= <expression> |
                          '(' <expression> { ','
                            <expression> } ')'

<user_counter>        ::= COUNTER [VARIABLE] <data_type>
                            <variable_reference>
                            [ '=' <expression> ]

<user_variable>       ::= VARIABLE <data_type>
                            <variable_reference>
                            [ '=' <expression> ]


parameters

CREATE keyword.
<collating_sequence> specifies how character string values are to be compared and sorted.
COLLATING SEQUENCE identifying keywords: indicate that a collating sequence variable is to be created.
[VARIABLE] an optional keyword.
<variable_reference> a name for the variable you are creating. It must conform to the rules for identifiers.
<comparison_order> an ascending list of <equivalent_sequence>s.
<equivalent_sequence> specifies that all characters in the sequence are considered to be equal for comparison purposes.
<expression> any valid expression, which is interpreted as a single or string of characters.
<user_counter> a database variable that can be used to generate unique identifiers. By using a counter as a serial default (see CREATE DEFAULT) the current value of the counter may be automatically inserted into a column, and then the counter variable incremented.
COUNTER [VARIABLE] identifying keywords: indicate that a variable of the counter type is being created.
<data_type> a numeric data type, such as INTEGER, FLOAT or DECIMAL.
<variable_reference> a name for the variable. It must conform to the rules for references.
<expression> any valid expression. This clause is optional. If you do not set an expression, the counter will start at zero, (0). An expression can also later be set with the SET VARIABLE statement.
<user_variable> a user defined variable.
VARIABLE statement identifying keyword: indicates what kind of a database structure is being created.
<data_type> any of the basic data types.
<variable_reference> a name for the variable. It must conform to the rules for references.
<expression> any valid expression. This clause is optional. If no expression is set here, this can be done later with the SET VARIABLE statement.


examples

Example

  /*
  ** In the following example, a collating sequence
  ** variable is created, called "normal". It defines
  ** the order for sorting and comparison purposes.
  */

  CREATE COLLATING SEQUENCE VARIABLE normal =
  (
    'AÀÄÃÅaáàâäãå',
    'Ææ',
    'Bb',
    'CÇcç',
    'Dd',
    'EÉeéèêë',
    'Ff',
    'Gg',
    'Hh',
    'Iiíìîï',
    'Jj',
    'Kk',
    'Ll',
    'Mm',
    'NÑnñ',
    'OÖÕoóòôöõ',
    'Œœ',
    'Øø',
    'Pp',
    'Qq',
    'Rr',
    'Ssß',
    'Tt',
    'UÜuúùûü',
    'Vv',
    'Ww',
    'Xx',
    'Yyÿ',
    'Zz',
    '0',
    '1',
    '2',
    '3',
    '4',
    '5',
    '6',
    '7',
    '8',
    '9'
  );
 


 

Here are some examples of how the equivalent sequence 'AÀÄÃÅaáàâäãå' could have been written:


  ('A', 'À', 'Ä', 'Ã', 'Å', 'a', 'á', 'à', 'â',
    'ä', 'ã', 'å' )
 

...or...


  ('AÀÄÃÅ', 'aáàâäãå' )
 


Note that the characters that are omitted (there are 256 characters, although many are not printable) from the collating sequence are added automatically, each in their own equivalent sequence in order of ASCII numbers.



 

Example

  /*
  ** In the next example, three counter variables are
  ** created. These counter variables are referred to
  ** in later default statements.
  */

  CREATE COUNTER INTEGER GolferCnt3 = 1;

  CREATE COUNTER INTEGER ClubCnt3 = 1;

  CREATE COUNTER INTEGER CompetitionCnt3 = 1;
 


 

Example

  /*
  ** In the last example, a user defined variable is
  ** created, called "year_end_no". It is of the data
  ** type, DATE, and the expression is the date of the
  ** year - "1992".
  */

  CREATE VARIABLE DATE year_end_no = "01/01/1992";
 


notes

A database user variable can be used to store certain values used by an application permanently in the database.

The order of characters within an <equivalent_sequence> is important when sorting

Each expression in an equivalent sequence represents a character or sequence of characters. Strings (CHAR, VARCHAR, BIN and VARBIN) represent a sequence of characters. Other numbers (INTEGER, FLOAT,...) represent single characters. The number is considered to be the ASCII value of a character (e.g. 65 = 'A', 66 = 'B', etc.).

All the characters in an <equivalent_sequence> are considered to be equal for comparison ('=', '<', '>', BETWEEN and LIKE) purposes. The order of characters within an <equivalent_sequence> is important when sorting (e.g. by the ORDER BY clause). In this case, characters are sorted ascending from left to right. For example, the <equivalent_sequence> "Aa" indicates that "A" and "a" are equal for comparison purposes, but when sorting, "A" will appear before "a".

While an <equivalent_sequence> consists of characters that are considered equal when compared, the <comparison_order> indicates the result of '<', '<=', '>' and '>=' operations performed on characters from different <equivalent_sequences>.

The only way of finding the current value of a database variable is by selecting the value from the SysVariables table.



see also

SET VARIABLE, DROP VARIABLE, RENAME VARIABLE



CREATE VIEW

function

The statement create view enables you to create a virtual, derived table. Any table that can be retrieved via a SELECT statement (any derivable table) can be defined as a view.



syntax

CREATE VIEW <view_reference> [ <column_group> ]
  AS <query_spec> ';'


parameters

CREATE VIEW statement identifying keyword.
AS specifies the mapping of that object to the conceptual level.
<view_reference> the qualified name of the view being created.
<column_group> optional; list of unique column names, should two or more columns of the view otherwise have the same name, or if view is derived from a function, operational expression, or a literal, and thus has no name that can be inherited.
<query_spec> query specification; the SELECT statement that defines the view.


example

Example

  /*
  ** In this example, a view, GolfersAmateurs is
  ** created. It consists of columns from the table
  ** Golfers, and includes all golfers with the status
  ** Amateur.
  */

  CREATE VIEW GolfersAmateurs2
  AS SELECT ID, Name, Title, Handicap, Status
  FROM Golfers
  WHERE Status = "Amateur"
  WITH CHECK OPTION;
 


notes

Views are dynamic, meaning that changes to the underlying table will automatically and immediately be reflected in the view.

Update operations (INSERT, UPDATE, DELETE) are not supported on view.



see also

DROP VIEW, RENAME VIEW



DESCRIBE COLUMNS

function

This statement describes all columns of a particular table. The table must be accessible to the user and must be in an already open database. The resulting table is described below:



syntax

DESCRIBE COLUMNS [ OF ] <table_reference>
  [ INTO <cursor> ] ';'


return values

col# Data Type Name Description
1 SMINT colnr Column number
2 SMINT level Column level number
3 VARCHAR[31] name Column name
4 SMINT type Column data type
5 SMINT len Column length in bytes
6 SMINT places Column scale
7 BOOLEAN nullsok Nulls allowed
8 BOOLEAN groupcol Group column
9 SMINT parentnr Parent column number
10 SMINT occurs Number of occurrences
11 SMINT occdep Occurs depending on column
12 BOOLEAN updtok Can the column be updated
13 VARCHAR[31] title Column title
14 VARCHAR[255] remarks Column remarks


parameters

DESCRIBE COLUMNS keywords.
OF an optional keyword
<table_reference> a reference to a table.
INTO an optional clause: rowset can be put into a specific cursor. If no cursor is mentioned, the rowset is placed into the system defined cursor, $cursor.
<cursor> A cursor variable to receive the rowset. It must conform to the rules for identifiers.


notes

It is not necessary for the table specified by <table_reference> to have been previously opened with an OPEN TABLE statement.



examples

Example

  /*
  ** Describe columns of the table Golfers is carried
  ** out in this example.
  */

  DESCRIBE COLUMNS OF Golfers;

  PRINTALL;
 


see also

DESCRIBE DBMS, DESCRIBE OPEN DBMS, DESCRIBE DATABASES, DESCRIBE OPEN DATABASES, DESCRIBE TABLES, DESCRIBE LINKSETS



DESCRIBE DATABASES

function

This statement returns a list of all databases on a specific DBMS (server or gateway).



syntax

DESCRIBE [ <dbms_brand> ] DATABASES
  [ [ IN ] LOCATION <character_literal> ]
  [ INTO <cursor> ] ';'


return values

The rowset returned into <cursor> is as follows:

col#

Data Type

Name
1 VARCHAR[31] name


parameters

DESCRIBE DATABASES keywords
<dbms_brand> specify which brand of DBMS. This parameter is optional. If given, it must be the name of a previously opened DBMS (see OPEN DBMS command). The default is the current DBMS as selected by the USE DBMS command.
IN an optional keyword. The IN LOCATION clause is ignored by the PrimeBase server.
LOCATION an optional clause, to specify the location of the databases.
<character_literal> In this case, the path name of the databases in the form of a string. This parameter is ignored by the PrimeBase server, due to the fact that the location of all databases is stored in the Master database.

When accessing a gateway, however, the use of this parameter depends on the type of DBMS supported by the gateway.
INTO an optional clause: rowset can be put into a specified cursor.
<cursor> A cursor variable to receive the rowset. Must conform to the rules for identifiers.


notes

A PrimeBase server can deliver information other than just the names f the databases. This information includes the ID of the databases and the privilege level of the user. The PBCTL statement is used to control what information is provided by DESCRIBE DATABASES. By default, only the names of the databases are listed in order to maintain DAL compatibility.



examples

Example

  /*
  ** In this example the databases of the default DBMS
  ** are listed.
  */

  DESCRIBE DATABASES;

  PRINTALL;
 


see also

DESCRIBE DBMS, DESCRIBE OPEN DBMS, DESCRIBE OPEN DATABASES, DESCRIBE TABLES, DESCRIBE LINKSETS, DESCRIBE COLUMNS, OPEN DATABASE



DESCRIBE DBMS

function

This function returns the names of all DBMSs that can be accessed by the client. The result is a rowset, as described below.



syntax

DESCRIBE DBMS [ INTO <cursor> ] ';'

<cursor>::= <identifier>


return values

The resulting rowset contains one row for each DBMS. Each row contains the 17 columns of information shown in the table below.

col#

Data Type

Name

Information
1 VARCHAR[31] brand DBMS name or server alias
2 VARCHAR[31] rev Version number
3 VARCHAR[31] brparms Brand open parameters
4 VARCHAR[31] dbparms Database open parameters
5 VARCHAR[31] tbparms Table open parameters
6 VARCHAR[31] struct Database structure info
7 VARCHAR[31] txns Transaction support
8 VARCHAR[31] types Supported data types
9 VARCHAR[31] stmts Supported statements
10 VARCHAR[31] queries Query processing options
11 VARCHAR[31] aggfcns Aggregate function support
12 VARCHAR[31] unused1 Reserved by standard DAL
13 VARCHAR[31] unused2 Reserved by standard DAL
14 VARCHAR[255] unused3 Reserved by standard DAL
15 VARCHAR[31] unused4 Reserved by standard DAL
16 VARCHAR[31] unused5 Reserved by standard DAL
17 VARCHAR[31] unused6 Reserved by standard DAL


parameters

DESCRIBE DBMS statement identifying keywords.
INTO an optional clause: rowset can be put into a specified cursor.
<cursor> A cursor variable to receive the rowset. Must conform to the rules for identifiers.


examples

Example

  /*
  ** In this example all available DBMSs are listed.
  ** The printall statement is used to print the
  ** results.
  */

  DESCRIBE DBMS;

  PRINTALL;
 


notes

Unlike standard DAL, the PrimeBase DAL software considers a DBMS to be a server or gateway anywhere on the network (not just on a particular host). The DESCRIBE DBMS command lists all such DBMSs for which a connection has been defined on the client machine.

Connections definitions are stored in the "connect.def" file in the "PrimeBase Setup" folder (called "PB-SETUP" under Windows, and "primebase.setup" under UNIX). A connection definition describes how to connect to a particular server or gateway anywhere on the network, or internet.

The DBMS brand (the first column of the rowset described above) is the connection alias used to identify the connection definition.

DAL compatible applications using PrimeBase DAL should always login to a "dummy" host called "PrimeBaseHost". The application then does a DESCRIBE DBMS and using OPEN DBMS will connect to a server using the selected connection definition.

Other details provided by the DESCRIBE DBMS command include the version number and profile strings.

The DBMS profile strings provide a description of a particular DBMS (PrimeBase server or gateway). They tell which features are supported or not supported by the server or gateway, what parameters are required or optional, and so forth. The strings are positional, with character positions numbered from 0 (the first one) to N-1, where N is the length of the string. In each position of each string, the character will either be a Y (meaning the feature is supported) or an N (meaning the feature is not supported).



BRPARMS

The profile string, BRPARMS, specifies which parameters of the OPEN DBMS statement are relevant for the DBMS brand. It has three characters in the following positions:

Position

PrimeBase Server

Meaning
0 Y Is the user name used?
1 Y Is the password used?
2 Y Is the option string used?


DBPARMS

The profile string DBPARMS specifies which parameters of the OPEN DATABASE statement are relevant for the DBMS brand. It has eight characters in the following positions:

Position

PrimeBase Server

Meaning
0 Y Is the database name used?
1 N Is the location used?
2 N Is the user name used?
3 N Is the password used?
4 N Is the option string used?
5 N Is SHARED mode supported?
6 Y Is PROTECTED mode supported?
7 N Is EXCLUSIVE mode supported?


TBPARMS

The profile string TBPARMS specifies which parameters of the OPEN TABLE statement are relevant for the DBMS brand. It has three characters in the following positions:

Position

PrimeBase Server

Meaning
0 N Is SHARED mode supported?
1 Y Is PROTECTED mode supported?
2 N Is EXCLUSIVE mode supported?


STRUCT

The profile string STRUCT specifies general structural information about how the DBMS brand organizes its databases and whether various database features are present or absent. It has nine characters in the following positions:

Position

PrimeBase Server

Meaning
0 Y Does the DBMS support creation of multiple databases on a single host system? (If N, there is one system-wide database.)
1 Y Are individual databases named? (If N, databases are unnamed.)
2 Y Does the DBMS use host locations (directories, catalogues) to structure its databases?
3 Y Does the DBMS support concurrent access to multiple databases? (If Y, this DBMS brand supports multiple OPEN DATABASE statements; if N, only one database of this brand can be open at a time.)
4 Y Does the DBMS support queries across different databases? (If Y, the FROM clause of a SELECT statement can include tables from multiple databases; if N, all tables must be from the same database.)
5 N Are linksets present in databases of this brand? (If N, the DESCRIBE LINKSETS statement will always produce a rowset with no row.)
6 N Are hierarchical columns present in databases of this brand? (If Y, column names can have the form a.b.c.)
7 N Are repeating columns present in databases of this brand? (If Y, columns names can have the form colname[6].)
8 N Are variable repeating columns present in databases of this brand?


TXNS

The profile string TXNS specifies the transaction-processing support provided by the DBMS brand. It has three characters in the following positions:

Position

PrimeBase Server

Meaning

0

Y

Does the DBMS support transactions (that is, is the DAL COMMIT / ROLLBACK mechanism supported?)

1

Y

Are transactions performed in repeatable-read (RR) mode? That is, can the client application be sure that data it has read during the current transaction will be identical if re-read before a COMMIT or ROLLBACK?

2

N

Are transactions performed in cursor-stability (CS) mode? That is, can the client application be sure only that data read through a single cursor is consistent? (Either CS or RR mode will be TRUE for a given DBMS, but not both.)


TYPES

The profile string TYPES specifies which DAL data types can result from a database of the DBMS brand. Each position of the string corresponds to a single DAL data type. It has 16 characters in the following positions:

Position

PrimeBase Server

Meaning