CHAPTER 4: Data Definition Commands

Syntax

Notes

1. The tables underlying the view (known as base tables) can reside in several databases on several file systems. A discussion on using views to access distributed data will be found in the Empress SQL: User's Guide.

2. Views can be either simple or complex. A simple view gives access to a single table, and normally gives partial access to the attributes or records in the table. A complex view gives simultaneous access to several tables linked together on a common attribute or attributes. The operation which links records in this manner is called a join. Joins are described in this manual under the SELECT command, and in the Empress SQL: User's Guide under Query Language Topics.

3. Attribute names can be specified in the view. Note that surrounding parentheses are required if attribute names are specified.

4. The SELECT part of the CREATE VIEW command may include functions (MAX, AVG, etc.), GROUP BY or HAVING clauses. The WHERE clause in the SELECT may include expressions involving operators or subqueries. The keyword DISTINCT may be used.

5. A view may be created on a view (nested views). That is, the table in the SELECT part of the command may be a view. In general, views may be used whenever a table is specified for retrieval operations. Access to data in a view is controlled by the privileges on the view, not by privileges on the underlying tables.

6. If a table underlying a view is dropped, an attempt to select from the view results in an error message saying that the table does not exist.

   When a table is dropped no warning is given of the existence of views on the table.

7. The structure of a view can be displayed using the DISPLAY TABLE command. The SELECT command used to create the view is also displayed if the ALL option to the DISPLAY TABLE command is used.

8. A VIEW is a logical or virtual TABLE. Consequently many of the commands that are directed towards tables can be used on views. These commands are:
   
   
   • CREATE COMMENT
   • CREATE RANGE CHECK
   • CREATE REFERENTIAL CONSTRAINTS
   • DROP REFERENTIAL CONSTRAINTS
   • DELETE
   • DISPLAY
   • DROP COMMENT
   • DROP RANGE CHECK
   • DROP TABLE
   • DROP VIEW
   • GRANT/REVOKE PRIVILEGE
   • INSERT
   • RENAME
   • RUN REPORT
   • SELECT
   • UPDATE

   Commands that cannot be applied to views are: ALTER, CREATE INDEX, DROP INDEX, EMPTY, LOCK LEVEL, SORT.

9. Records may always be selected from a view, but update type operations are restricted to views that satisfy certain conditions. Simple views usually satisfy the conditions, but some care is required to construct complex views that satisfy them. These conditions are summarized below, and discussed at length in the Empress SQL: User's Guide under Query Language Topics.

   Below is a summary of the conditions under which Empress will permit update type operations on views:

   The first condition for update type operations to be allowed on a view is that a record from a base table can, in principle, appear at most once in a view. This condition can be expressed in three rules:

   • The WHERE clause used to define the view must be limited to AND conjunctions of simple conditions, where a simple condition is one of:
     • A comparison between an attribute and a constant.
     • A comparison between two attributes of the same table.
     • A condition of equality between two attributes of different tables.

     The rule excludes:

     • The NOT condition.
     • The OR conjunction.
     • Between tables, other than the equal(=) comparison.

   • There must be sufficient conditions of equality between attributes of the base tables. A technique for determining whether the rule is satisfied is to represent each base table as a point, and a condition of equality joining two tables as a line between the respective points. If it is possible to move from any point to any other point by traveling along lines, then the rule is satisfied. An adjunct to this rule is that a table may not be selected twice in the same view.

   • For each base table, at least one of the attributes participating in each join with another table must contain a key. This means that either at least one of the attributes has a unique index on it, or several of the attributes are combined in a unique composite index.

     A technique for determining whether this rule is satisfied is to pair each table with every table to which it is joined, and for each pair list the attributes of the first table involved in joins with the second table. Either one of the attributes in each list must have a unique index or several of the attributes must have a unique composite index.

   The second condition for update type operations to be allowed on views is: for every set of attributes in the base tables that must be equal, only one may appear in the view. When the view is updated, every attribute in such a set is assigned the value specified for the attribute that does appear.

   The third condition concerns attributes that are defined NOT NULL or that have unique indices on them. Since such attributes may not have null values inserted for them, they must either appear in the view or be equal to an attribute that does appear in the view if insert operations are to be permitted on the view.

Privileges Required

Example

1. Create a Simple View

   Create a simple view called employees on the personnel table, using only the name and phone attributes, with the command:

      CREATE VIEW employees AS
         SELECT name, phone FROM personnel;
   
   

   Now, when you enter the command:

      SELECT * FROM employees;
   
   

   you will see:

      name          phone
   
      Kilroy        426-9681
      Mosca         544-2243
      Wladislaw     723-6073
      Jones         667-2951
      Peterson      978-6060
      Scarlatti     961-7363
      Jordan        964-3335
   
   

   If you now use the DISPLAY TABLE command to display the structure of employees, you will see:

      DISPLAY employees;
   
      *** View: employees ***
   
      Attributes:
        name          char(25,1)
        phone          char(15,1)
   
   

   The DISPLAY TABLE command with the ALL option shows the command used to create the view:

      DISPLAY employees ALL;
   
      *** View: employees ***
   
      View: select attr 'name', attr 'phone' from 'personnel'
   
      Attributes:
        name          char(25,1)
        phone          char(15,1)
   
      Creator: joe
   
      Table #: 11
      Records: 7
   
   

2. Create a Complex View

   It is often convenient to make a view of a commonly-used join between two or more tables and select from the view, rather than having to specify the join conditions each time. For example, you can create a view called jonesloans on the loans and personnel tables to show Jones' loans and credit limit:

      CREATE VIEW jonesloans AS
         SELECT name, date, amount, credit_limit
         FROM loans, personnel
         WHERE loans.name = 'Jones' AND
         loans.name = personnel.name;
   
   

The DROP VIEW command removes a definition of a view from the data dictionary.

Syntax

DROP [VIEW] view;

where:

view	is the name of the view.

Notes

When a view is dropped, no warning is given of the existence of views on the view.

Privileges Required

DROP privilege.

Example

Remove the jonesloans view with the command:

   DROP VIEW jonesloans;

The CREATE COMMENT command places a comment on a table, attribute, module, routine or trigger. The comment will appear in the output from a DISPLAY DATABASE or DISPLAY TABLE command.

Syntax

Syntax 1:

CREATE COMMENT [ON]

|TABLE |ATTR[IBUTE] |MODULE |ROUTINE |TRIGGER

table table.attr module module.routine trigger

| [SET] comment_string; | | | |

Syntax 2:

COMMENT [ON]

|TABLE |ATTR[IBUTE] |MODULE |ROUTINE |TRIGGER

table table.attr module module.routine trigger

| [IS] comment_string; | | | |

where:

module	is a persistent stored module name.
routine	is a routine name. The routine name must prefixed by the module name. It must be quoted (either by single quotation marks or double quotation marks), this is because the routine name is registered as a dynamic keyword in Empress.
trigger	is a trigger name.
comment_string	is any character string in quotes.

Notes

1. Comment on attribute must be qualified by its table name.

2. CREATE COMMENT can also be used to a view.

3. Routines with the same name within a module share a common comment even though their external names are different.

Privileges Required

ALTER privilege.

Example

1. Create a Comment on a Table

   Put a comment on the loans table as follows:

      COMMENT ON TABLE loans IS "Loans to Employees";
   
   

   The DISPLAY TABLE command shows the comment on the table:

      DISPLAY loans;
   
      *** Table: loans ***
   
      Comment: Loans to Employees
   
      Attributes:
   
        number             integer
        name               char(25,1)
        date               date(1)
        amount             dollar(6,1)

2. Create a Comment on an Attribute

   Place a comment on the amount attribute in loans with:

   CREATE COMMENT ON ATTRIBUTE loans.amount
       SET 'Amounts must be multiples of $$25';
   
   

   The doubled dollar sign is necessary to avoid interpretation of "$25'' as an Empress variable. See the discussion on Variables and Strings in the chapter Empress Conventions.

   The DISPLAY TABLE command with the ALL option shows the comment:

      DISPLAY loans ALL;
   
      *** Table: loans ***
      
      Comment: Loans to Employees
      
      Attributes:
      
        number             integer
        name               char(25,1)
        date               date(1)
        amount             dollar(6,1)
      
      Comment: Amounts must be multiples of $25
      
      Creator: joe
      Lock Level: RECORD
      
      Table #: 8
      Records: 12
      Record size: 36
   
   

   The comment on the particular attribute can be displayed with the DISPLAY TABLE restricted to the attribute:

      DISPLAY loans ON amount ALL;
      
      *** Table: loans ***
      
      Comment: Loans to Employees
      
      Attributes:
      
        amount        dollar(6,1)
      
      Comment: Amounts must be multiples of $25
      
      Creator: joe
      Lock Level: RECORD
      
      Table #: 8
      Records: 12
      Record size: 36
   
   

The DROP COMMENT command removes the comments from a table or attribute.

Syntax

DROP COMMENT [ON]	|TABLE |ATTR[IBUTE] |MODULE |ROUTINE |TRIGGER	table table.attr module module.routine trigger	|; | | | |

Notes

DROP COMMENT can also be used to remove a comment from a view.

Privileges Required

ALTER privilege.

Example

Remove the comment on the loans table with the command:

   DROP COMMENT ON TABLE loans;

The GRANT PRIVILEGE command assigns access privileges on tables to other users or database roles. These other users may also be given the ability to grant their privileges to others. Privileges can be removed with the REVOKE PRIVILEGE command. Privileges can be displayed with the DISPLAY PRIVILEGE and DISPLAY GRANT PRIVILEGE commands.

Syntax

GRANT privilege {, privilege} [ON] table [TO] grantee {, grantee}

   [AS grantor] [[WITH] GRANT [OPTION]];

where:

privilege	is one of the privileges in the Table 4-3.
grantee	It can be one of: |CREATOR |DBA |PUBLIC |username |rolename | | | | | It represents the name of a user, the name of a database role and specifies the person given the privileges. The CREATOR is the creator of the table, the DBA refers to the Database Administrator, PUBLIC refers to all users and username is an operating system account name or login name.
grantor	It can be one of: |CREATOR |DBA |username | | | It refers to the person granting the privilege. If grantor is not specified, the default is the name of the user issuing the command.

Notes

1. The following table listed all database privileges:

   Table 4-3: Database Privileges

   Privilege	Permits Commands
   ALTER	ALTER TABLE, CREATE/DROP COMMENT, CREATE/DROP RANGE CHECK, CREATE/DROP REFERENTIAL CONSTRAINT, LOCK LEVEL, RENAME
   BYPASS_LOCK	SELECT BYPASS_LOCK
   DELETE	DELETE, SELECT
   DISPLAY	DISPLAY TABLE
   DROP	DROP TABLE
   EMPTY	EMPTY TABLE
   INDEX	CREATE/DROP INDEX
   INSERT	INSERT
   SELECT	SELECT
   SORT	SORT
   UPDATE [(attr {, attr})]	UPDATE, SELECT If attributes are specified, the privilege applies only to those attributes.

2. Provisions have been made to refer to commonly used groups of privileges by a single name. The names of these groups reflect their use. They are: DBA (Database Administrator), ALL and USER.

   Table 4-4: Groups Database Privileges by Name

   Privilege Type	Privileges
   DBA	ALTER, DROP, EMPTY, INDEX, SORT
   ALL	All privileges.
   USER	BYPASS_LOCK, DELETE, DISPLAY, INSERT, SELECT, UPDATE

3. The DBA is defined in tabzero by the variable MSDBADMINISTRATOR. The CREATOR of a table is defined in sys_tables in the data dictionary.

   The DBA of the database and the CREATOR of a table may be changed by updating the appropriate system data. Granting privileges to the DBA and CREATOR provides a systematic means to have the privileges inherited by users as they become the DBA or CREATOR.

4. When anyone tries to access a table, that person's access privileges are checked. If the person also happens to be the CREATOR of the table or the DBA, privileges accruing to those positions apply.

5. The CREATOR or DBA may grant any privilege on the table. Privileges may only be revoked by their grantor. However, the CREATOR and DBA may revoke any privilege as the grantor.

6. The GRANTOR type regulates the granting and revoking of privileges. By default, you may only grant those privileges for which you have the grant option.

7. Empress uses the file permissions that are set by the underlying operating system of each database. In order to access a table in a Empress database, a user must have both the appropriate operating system file permissions and the appropriate database privileges. e.g. in UNIX, the DBA must give file permissions to other users of the database according to their level of usage. If a user just wants to do query on the database (e.g. display/select), a read permission on the directory of the database and on the files corresponding to the queried tables would be enough. If a user needs to modify a database (insert/delete/update/alter), the user must be given write permission on the files corresponding to the modified tables and the system table. This can be done by using chmod command (refer to your UNIX manual for usage of this command). If the user needs to create new tables on a database, write permission on the directory containing the database must be given to the user.

8. The DBA can grant all privileges on all tables. To exercise this prerogative, the grantor must grant the privileges AS DBA. Default privileges on the table are granted as specified in the administrative variable MSDBDBAPRIVS in tabzero of the data dictionary. The default for this variable specifies the following privileges:

   Table 4-5: Default Privileges for DBA on a Table

   Grantor	Grantee	Privilege	Grant Option
   DBA	DBA	DBA privilege: ALTER, DROP EMPTY, INDEX, SORT	With no grant option
   DBA	DBA	USER privilege: BYPASS_LOCK, DELETE, DISPLAY, CREATOR, INSERT, SELECT, UPDATE	WITH GRANT OPTION

9. The creator of a table can grant all privileges on that table. When a table is created, the user issuing the command becomes the CREATOR of the table. Default privileges on the table are granted as specified in the administrative variable MSDBPRIVS in tabzero of the data dictionary. The default for this variable specifies the following privileges:

   Table 4-6: Default Privileges for the Creator of a Table

   Grantor	Grantee	Privilege	Grant Option
   CREATOR	CREATOR	DBA privilege: ALTER, DROP EMPTY, INDEX, SORT	With no grant option
   CREATOR	username (login name of the creator)	USER privilege: BYPASS_LOCK, DELETE, DISPLAY, CREATOR, INSERT, SELECT, UPDATE	WITH GRANT OPTION

10. Privileges can be granted on a view as well as on a table.

Privileges Required

The grantor must have the privilege in question with grant authorization.

Example

1. Grant Privileges to Specific Users

   Grant SELECT and INSERT privileges on the auto parts table to Mosca and Kilroy, whose login/user names are mosca and kilroy, with the command:

      GRANT SELECT, INSERT ON 'auto parts'
         TO mosca, kilroy;
   
   

2. Grant Privileges with Grant Option

   Grant the login/user name office INSERT, UPDATE, and DELETE privileges on the customers table as the creator of the table, with the option to grant these privileges to others, with the command:

      GRANT INSERT, UPDATE, DELETE ON customers
         TO office
         AS CREATOR
         WITH GRANT OPTION;
   
   

3. Grant Privilege to All Users

   Grant SELECT privileges to everyone for the customers table, again as the creator, with:

      GRANT SELECT ON customers
         TO PUBLIC
         AS CREATOR;
   
   

The DISPLAY PRIVILEGE command prints access privilege information for the named table.

Syntax

DISPLAY

|PRIVILEGE |PRIV

| table [ON [ATTR] |

|attr |[NOT] match_op pattern

|] |

[

|ALL |AS | |

|DBA |CREATOR |username

| | |

|]; | | |

where:

match_op	is one of LIKE, MATCH, !MATCH, SMATCH, or !SMATCH.
pattern	is any pattern accepted by a WHERE clause; it must be given as a quoted string.
username	is an operating system account name or login name.

Notes

1. The DISPLAY PRIVILEGE command without the ALL option shows what you can do on the named table. Privileges are listed in pre-assigned columns on the screen. If you have all privileges, the word ALL is printed.

2. If you have update privileges on some attributes and not others, the attributes you can update will be listed. To restrict the list to particular attributes, use the ON attr or the pattern matching options.

3. With the ALL option, your privileges are classified according to how you have them. Lists of privileges are printed for USER, PUBLIC, DBA and CREATOR, if applicable.

4. With the AS DBA option, the privileges accruing to you as the DBA are printed. If you are not the DBA no privileges are printed.

5. With the AS CREATOR option, the privileges accruing to you as the CREATOR of the table are printed. If you are not the CREATOR, no privileges are printed.

6. With the AS username option, the privileges accruing to the username are printed. This option only works if you are the DBA or the CREATOR.

7. DISPLAY PRIVILEGE can also be used to display privileges on a view.

Privileges Required

None.

Example

1. Display Privileges on a Table

   To find out your privileges on the loans table, do:

      DISPLAY PRIVILEGE loans;
   
   

   which produces:

      ALL
   
   

2. Display a Classification of the Privileges

   To include a classification of the privileges, use:

      DISPLAY PRIVILEGE loans ALL;
   
   

   which produces:

      USER: delete display insert select update
      CREATOR: alter drop empty index sort
   
   

The DISPLAY GRANT PRIVILEGE command prints grant privilege information for the named table.

Syntax

DISPLAY GRANT

|PRIVILEGE |PRIV

| table [ON [ATTR] |

|attr |[NOT] match_op pattern

|] |

[

|ALL |AS | |

|DBA |CREATOR |username

| | |

|]; | | |

where:

match_op	is one of LIKE, MATCH, !MATCH, SMATCH, or !SMATCH.
pattern	is any pattern accepted by a WHERE clause; it must be given as a quoted string.
username	is an operating system account name or login name.

Notes

1. The DISPLAY GRANT PRIVILEGE command without the ALL option shows what you can grant on the named table under your own username. Privileges are listed in pre-assigned columns on the screen. If you have the grant option on all privileges, the word ALL is printed.

2. If you can grant update privileges on some attributes and not others, the attributes you can grant update on will be listed. To restrict the list to particular attributes, use the ON attr or the pattern matching options.

3. With the ALL option, all your grant options are printed and classified according to how you have them. Lists of privileges are printed for USER, PUBLIC, DBA and CREATOR, if applicable.

4. With the AS DBA option, the grant options accruing to you as the DBA are printed. If you are not the DBA no grant options are printed.

5. With the AS CREATOR option, the grant options accruing to you as the CREATOR of the table are printed. If you are not the CREATOR, no grant options are printed.

6. With the AS username option, the grant options accruing to the username are printed. This option only works if you are the DBA or the CREATOR.

7. DISPLAY GRANT PRIVILEGE can also be used to display grant options on a view.

Privileges Required

None.

Example

1. Display Default Grant Options

   To find out your default grant options on the loans table, type:

      DISPLAY GRANT PRIVILEGE loans;
   
   

   which produces:

      USER: delete display insert select update
   
   

2. Display Full List of Grant Options

   To print a full list of your grant options, use:

      DISPLAY GRANT PRIVILEGE loans ALL;
   
   

   which produces:

      USER: delete display insert select update
      CREATOR: all
      DBA: all
   
   

The REVOKE PRIVILEGE command removes access privileges on tables from users or database roles.

Syntax

REVOKE privilege {, privilege} [ON] table [FROM] grantee {, grantee}

   [AS grantor];

where:

privilege	is one of the privileges listed in the TABLE 4-3.
grantee	It can be one of: |CREATOR |DBA |PUBLIC |username |rolename | | | | | It represents the name of a user, and specifies the person given the privileges. The CREATOR is the creator of the table, the DBA refers to the Database Administrator, PUBLIC refers to all users and username is an operating system account name or login name.
grantor	It can be one of: |CREATOR |DBA |username | | | It refers to the person granting the privilege. If grantor is not specified, the default is the name of the user issuing the command.

Notes

1. Three privilege types (DBA, ALL and USER) are provided for convenience. They are an easy way to refer to groups of privileges and is listed in Table 4-4.

2. Privileges granted by a grantor may, in general, only be revoked by the same grantor. However, the CREATOR and DBA may revoke any privilege as whoever granted the privilege.

3. Privileges can be granted and revoked on views as well as on tables.

Privileges Required

Must be grantor of the privilege in question.

Example

Revoke Mosca's INSERT privilege on the auto parts table with the command:

   REVOKE INSERT ON 'auto parts'
      FROM mosca;

The LOCK LEVEL command sets the level of locking on a table. The new lock level remains in force for all users of the table until the next LOCK LEVEL command.

Lock levels are provided so that you can control access if you export your database to a multi-user system.

Syntax

LOCK LEVEL [ON] table [IS]

|TABLE |GROUP [(n)] |RECORD [(n)] |NULL

|; | | |

Notes

1. If NULL is specified, all locking is disabled for the given table.

2. If TABLE is specified as the locking level, the entire table is locked for the duration of each Query Language command.

3. If GROUP is specified, all records affected by a command (rather than the entire table) are locked for the duration of the command.

   Unless the attributes referred to in a command are indexed, group level locking is effectively the same as table level locking, since Empress cannot tell in advance which records will be seen by the command.

4. Specifying RECORD locks only each record as it is encountered in a command.

5. Specifying RECORD (n) locks each record as it is encountered in a command plus (n-1) surrounding records. n records, locked in this case, are constituting a PAGE lock

6. Setting lock levels on views has no effect.

7. When table is created, the setting of the lock level is controlled by the system variable MSDBLOCKLEVEL in the data dictionary tabzero file. The default is record level locking.

Privileges Required

ALTER privilege on the table to be locked.

Example

1. To Set No Locking on a Table

   To set no locking on the personnel table with the command:

      LOCK LEVEL ON personnel IS NULL;
   
   

2. To Set Record Level Locking on a Table

   To set record-level locking on the loans table with:

      LOCK LEVEL ON loans IS RECORD;
   
   

The CREATE MODULE command is used to define the definition of the PSM (Persistent Stored Modules) module into the data dictionary. This creates an entry point for the user defined functions, operators, procedures or aggregate functions. The UPDATE MODULE command is necessary for linking the module definition with the module loadable shared library.

Syntax

CREATE MODULE module_name

   [LANGUAGE C [(lang_param {, lang_param})]]

|module_procedure |module_function |module_operator |module_agg_func

|; { | | |

|module_procedure |module_function |module_operator |module_agg_func

|;} | | |

   END MODULE;

where:

module_name	is the module name. Module name is case sensitive.
lang_param	is an identifier. This is not currently used by Empress.
module_procedure	is defined as: [DECLARE] PROCEDURE procedure_name ([[| IN |] [parameter_name] parameter_type | OUT | |INOUT| {, [| IN |] [parameter_name] parameter_type}]) | OUT | |INOUT| [ALLOCATE WORKSPACE | nbytes |] | variable | EXTERNAL [BEGIN EXPRESSION external_routine_name] [NAME external_routine_name] [END EXPRESSION external_routine_name] [|PARAMETER STYLE SQL |] |PARAMETER STYLE GENERAL|
module_function	is defined as: [DECLARE] FUNCTION function_name ([[IN] [parameter_name] parameter_type {, [IN] [parameter_name] parameter_type}]) RETURNS return_type [ALLOCATE WORKSPACE | nbytes |] | variable | EXTERNAL [BEGIN EXPRESSION external_routine_name] [NAME external_routine_name] [END EXPRESSION external_routine_name] [|PARAMETER STYLE SQL |] |PARAMETER STYLE GENERAL|
module_operator	is defined as: [DECLARE] OPERATOR |PREFIX |POSTFIX |INFIX [precedence] |COMPARISON |EQUALITY | operator_name | | | | ([[IN] [parameter_name] parameter_type {, [IN] [parameter_name] parameter_type}]) RETURNS return_type [ALLOCATE WORKSPACE | nbytes |] | variable | EXTERNAL [BEGIN EXPRESSION external_routine_name] [NAME external_routine_name] [END EXPRESSION external_routine_name] [|PARAMETER STYLE SQL |] |PARAMETER STYLE GENERAL|
module_agg_func	is defined as: [DECLARE] AGGREGATE FUNCTION aggr_function_name ([IN] parameter_name] parameter_type {, [IN] [parameter_name] parameter_type}) RETURNS return_type [ALLOCATE WORKSPACE | nbytes |] |variable| EXTERNAL [BEGIN EXPRESSION external_routine_name] BEGIN GROUP external_routine_name BODY external_routine_name END GROUP external_routine_name [END EXPRESSION external_routine_name] [|PARAMETER STYLE SQL |] |PARAMETER STYLE GENERAL|

Notes

1. Currently only C Programming Language is supported by Empress Persistent Stored Modules. In future, other languages may be supported.

   The C programming Language in the language specification can be either C or "C"; both upper and lower case are allowed.

2. There are three procedure parameter modes: IN, OUT and INOUT for input, output and input/output respectively. If the procedure parameter mode is not specified, the default is IN.

3. There are three different groups of parameter_type:

   MS Data Types	is Empress SQL Data Types. Refer to the Data types chapter of this manual.
   Generic Data Types	is Empress Generic Data Types. Refer to the Empress: User Defined Functions manual under Data Type Correspondences.
   C Data Type
   is a C programming language data types. The keyword C must be specified before the C data type and must be in quotes, for example, "C long".

   Parameters may belongs to different data type groups. For example:

        (number "C long",
         string CHAR,
         amount GENERIC FLOAT)
   
   

4. The return_type may have different data type group as parameter_type.

5. There are five operator classes:

   • PREFIX
   • POSTFIX
   • INFIX [precedence]
   • COMPARISON
   • EQUALITY

   The precedence for the INFIX operator class is: 1, 2, 3, 4, 5, 6 or right. For boolean operator, only 1, 2 is valid, default is 2; for arithmetic operator, all are valid, default is 4.

6. The parameter style can be either PARAMETER STYLE GENERAL or PARAMETER STYLE SQL. If not specified, the default is PARAMETER STYLE GENERAL.

7. In the text below, routine is generically refer to the procedure, function, operator and aggregate function.

   Rules for the names:

   • within a database, no duplicate module name;
   • within a module,
     • duplicate routine names are allowed provided the routines are of the same keytag (the correspondence between SQL-invoked routine and the keytag is defined in the User Defined Function manual) and the parameters are of different data types;
     • duplicate external routine names for BEGIN EXPRESSION are allowed;
     • duplicate external routine names for BEGIN GROUP are allowed;
     • no duplicate external routine name for BODY;
     • duplicate external routine names for END GROUP are allowed.
     • duplicate external routine names for END EXPRESSION are allowed;
   • no duplicate routine names across modules;

   The leading and trailing blanks of the module name, routine name and external name are ignored. The leading character should be either underscore (_) or alpha-character, remaining characters can consists of underscore, alpha-character or digit. The maximum length is 31 characters.

8. The external_routine_name fall into five classes:

   • external begin expression routine name
   • external begin group routine name
   • external body routine name
   • external end group routine name
   • external end expression routine name

   For aggregate function, all five classes may exist; for other routines, begin group and end group routines never exist.

   External body routine name is specified by body clause (BODY) for aggregate function, and by external name clause (NAME) for other routines. The default external body name is the same as routine name.

   If not explicitly specified, we assume begin expression routine, end expression routine do not exist.

   External begin group and end group routine names must be explicitly specified for aggregate function. In general, begin group expression routine is used for initialization and end group routine is used for returning result.

9. If ALLOCATE WORKSPACE is specified, Empress will allocate and deallocate the workspace for the PSM routines automatically. You should not allocate another workspace in the BEGIN EXPRESSION function or deallocate the workspace that system allocated.

   If variable name is given for the workspace, it must be defined in the external routine with the same variable name.

10. After CREATE MODULE is completed, you must use UPDATE MODULE to link the module definition with the module loadable shared library in order to use the procedures, functions, operators or aggregate functions.

11. Refer to User Defined Functions manual for detail information on Persistent Stored Modules (PSM).

Privileges Required

DBA privilege.

Example

1. The following example creates a module procedure procmodule. The example procedure log_event is used to create audit trail log.

      CREATE MODULE procmodule
         PROCEDURE log_event ()
            EXTERNAL;
         END MODULE;
   
   

2. The following example creates a module function funcmodule. This module function declares two functions: replacenull and roundup.

   The example function replacenull replaces the NULL value by a specified character string on output. The example function roundup rounds a number to the specified decimal digits.

      CREATE MODULE funcmodule
         FUNCTION replacenull (stringvalue CHAR, defaultstring CHAR)
            RETURNS CHAR
            EXTERNAL;
         FUNCTION roundup (number GENERIC FLOAT, digits GENERIC FLOAT)
            RETURNS GENERIC FLOAT
            EXTERNAL;
         END MODULE;
   
   

   Note that replacenull and roundup functions are equivalent to the Empress Build-In Functions nullval and round respectively. Both nullval and round are Empress keyword which cannot be used in this example.

3. The following example creates a module operator opermodule. The example operator concatenation concatenates character data. This operator is equivalent to the Empress Operators concat.

      CREATE MODULE opermodule
         OPERATOR INFIX concatenation 
            (str1 "C char", str2 "C char")
            RETURNS "C char"
            EXTERNAL;
         END MODULE;
   
   

4. The following example creates a module aggregate function aggrmodule. The example aggregate function ssd produces the final aggregate value associated with the squared standard deviation of the statistic x data.

      CREATE MODULE aggrmodule
         AGGREGATE FUNCTION ssd (LONGFLOAT)
            RETURNS LONGFLOAT
            ALLOCATE WORKSPACE somespace
            EXTERNAL BEGIN GROUP ssdigrp
                     BODY ssdrfns
                     END GROUP ssdtgrp;
         END MODULE;
   
   

   The external routine of this example is shown in the EMPRESS: User Defined Functions manual.

The UPDATE MODULE command links the module definition with the module loadable shared library.

Syntax

UPDATE MODULE module_name [FOR system_name] [|FROM | dll_file]; |USING|

where:

module_name	is the module name.
system_name	is a system id that has valid Empress RDBMS license.
dll_file	is the location of the dynamic loadable shared library object file.

Notes

1. UPDATE MODULE command is usually used with FROM dll_file or USING dll_file options to update the Module with an implementation of PSM routines.

2. If a module is converted from an old version (e.g. Version 8.20) to a newer version (e.g. Version 8.62), its old wrap.dll cannot be run on the new version. Use UPDATE MODULE command without FROM dll_file or USING dll_file options only if the database is upgraded to a newer version. In this case it just regenerates the wrap.dll file for a given module.

3. UPDATE MODULE command can be used as many times as needed after the module is created to change the content or path of the dynamic loadable shared library object file.

4. system_name can be obtained by using:

      empvers
   
   

   command at the operating system prompt. For example, it will produce the following information:

     EMPRESS V8.62
     (c) Copyright Empress Software Inc. 1983, 2006
   for Intel x86 running Linux OS Release 2.0 with libc.so.6 (ELF)
   [linux-libc6-x86]
   Port Code DOCS-08.62-A-00-S-ENG
   Port Code DOSV-08.62-A-00-S-ENG
   Port Code HYPM-08.62-A-00-S-ENG
   Port Code JDBC-08.62-A-00-S-ENG
   Port Code ODBC-08.62-A-00-S-ENG
   Port Code RDBM-08.62-A-00-S-ENG
   Installed in: /home/staff/joe/empress/v8.62
   
   

   The system name is indicated within the square brackets, i.e. linux-libc6-x86.

Privileges Required

DBA privilege.

Example

The following command updates the module procmodule from the dynamic loadable shared library object file procmodule.dll:

   UPDATE MODULE procmodule FROM 'procmodule.dll';

The DROP MODULE command removes the module definition from the database data dictionary. Any functions, procedures, operators and aggregate functions that are defined in the module will no longer recognized by Empress.

Syntax

DROP MODULE module_name;

where:

module_name

is the module name.

Notes

When a module is dropped, no warning is given; use it with care.

Privileges Required

DBA privilege.

Example

The following command drops a module with the module name procmodule:

   DROP MODULE procmodule;

The DISPLAY MODULE command displays the module definition from the database data dictionary.

Syntax

DISPLAY MODULE [module_name] [ALL] [DUMP] [

|INTO |ONTO

| file]; |

where:

module_name

is the module name.

Notes

1. If the module name is not specified, DISPLAY MODULE command will display the definition of all modules in the database.

2. The DISPLAY MODULE command without the ALL option lists the systems that this module is available to, all the SQL-invoke routines in the module and their external name.

   If module is not available for any system (indicated by NONE) means the module has been created (CREATE MODULE) but has not been linked (UPDATE MODULE).

3. The DISPLAY MODULE command with the ALL option provides additional information on the module, such as: parameter information, return data type, parameter style, creator of the module, module number and the programming language used to create the module (currently only C programming language).

4. If DUMP is used, the output is condensed and can be used to create a script file.

5. If INTO file or ONTO file are used, the output is sent to the named file rather than to the terminal. INTO overwrites any existing file, while ONTO appends to it. Both create the file if necessary.

Privileges Required

DISPLAY privilege.

Example

The following example shows different DISPLAY MODULE commands and their outputs:

   * DISPLAY MODULE;
   
     aggrmodule
     funcmodule
     opermodule
     procmodule

   
   * DISPLAY MODULE funcmodule;
   
     ****  Database: repairs  ****
   
     ***  Module: funcmodule  ***
   
         Available for systems:
           NONE
   
         replacenull                     function
           External Name:                  replacenull
   
         roundup                         function
           External Name:                  roundup

   
   * DISPLAY MODULE ALL;
   
     ****  Database: repairs  ****
   
     ***  Module: aggrmodule  ***
   
         Available for systems:
           NONE
   
         ssd                             aggregate function
   
     ***  Module: funcmodule  ***
   
         Available for systems:
           NONE
   
         replacenull                     function
         roundup                         function
   
     ***  Module: opermodule  ***
   
         Available for systems:
           NONE
   
         fact                            postfix operator
   
     ***  Module: procmodule  ***
   
         Available for systems:
           NONE
   
         log_event                       procedure

   
   * DISPLAY MODULE funcmodule ALL;
   
     ***  Module: funcmodule  ***
   
         Available for systems:
           NONE
   
         replacenull                     function
           Number of Parameters:           2
                1)   in      stringvalue         character(25,1)
                2)   in      defaultstring       character(25,1)
           Return Data Type:               character(25,1)
           Parameter Style:                general
           External Name:                  replacenull
   
         roundup                         function
           Number of Parameters:           2
                1)   in      number              generic float
                2)   in      digits              generic float
           Return Data Type:               generic float
           Parameter Style:                general
           External Name:                  roundup
   
         Creator:         zsu
         Module #:        2
         Language:        C

   
   * DISPLAY MODULE DUMP;
   
     aggrmodule
     funcmodule
     opermodule
     procmodule

The CREATE TRIGGER command creates the trigger definition into the database data dictionary.

Syntax

CREATE TRIGGER trigger_name

|BEFORE |AFTER

| |

|DELETE |INSERT |UPDATE [ OF (attr {, attr})] |SELECT [ OF (attr {, attr})]

| {, | | |

|DELETE |INSERT |UPDATE [ OF (attr {, attr})] |SELECT [ OF (attr {, attr})]

| } | | |

   ON table [FOR EACH ROW [when_clause]]

   EXECUTE procedure_name;

where:

trigger_name	is the trigger name. Trigger name is case sensitive. The rules for trigger name is the same as table name and attribute name.
when_clause	has the following syntax: WHEN |b_expr |(b_expr) |b_expr AND b_expr |b_expr OR b_expr |NOT b_expr | | | | | b_expr is a boolean expression. Details on the boolean expression is described in the WHERE Clause of this manual with the following exceptions: It does not support subquery. Attribute name must prefixed with the qualifier NEW. or OLD.. The referenced attribute can only come from the table that trigger was created on.
procedure_name	is a PSM (Persistent Stored Modules) procedure name.

Notes

1. When creating a trigger, the procedure_name must be declared first through the CREATE MODULE command or error message will occur.

   UPDATE MODULE command must be issued before trigger can be used or an error message complaining non-existence of procedure_name will occur.

2. The procedure for the trigger is a PSM procedure with no parameters. If a module procedure with parameter is used with the CREATE TRIGGER command, error message will be given.

3. Trigger that is created without FOR EACH ROW option is called statement trigger. It triggers the function once when SQL command is executed; even if the operation is applied to multiple rows.

   Trigger that is created with FOR EACH ROW option is called row trigger. It triggers the function each time the operation is applied to a row of data.

4. The trigger can be specified to fire either before the statement/operation (INSERT, DELETE, UPDATE) is attempted on a row/record or after the operation has been completed. In the case of the SELECT trigger however, it can be specified to fire only after the SELECT operation has been completed.

5. UPDATE and SELECT trigger can specify the corresponding attribute lists. If no attribute is specified, it means all attributes of a table.

6. The when_clause is associated with FOR EACH ROW option. If FOR EACH ROW is not assigned in CREATE TRIGGER command, the when_clause cannot be assigned. Once the when_clause is assigned for FOR EACH ROW option, only in the case when the accessed row satisfies the condition, the trigger will be fired.

7. The condition in when_clause is similar to that in the WHERE clause, except that all attributes must be in the table associated with the trigger and qualified by either new. or old. . Subqueries are not supported.

8. When using attribute values in the when_clause,

   • for UPDATE triggers, you can refer to old (existing) attribute value (specified as: old.attr) and new attribute value (specified as: new.attr)
   • for INSERT triggers, you can only refer to new attribute value; the old attribute value is not meaningful
   • for BEFORE DELETE triggers, you can only refer to old attribute values; the new attribute value is not meaningful
   • for AFTER DELETE triggers, the value of neither the old attribute nor the new attribute is meaningful
   If the qualifier is misused, no error message will be given and the attribute value will be treated as NULL.

9. When using an update trigger:

   • For row trigger, the values of old record and new record are compared to decide whether the trigger will be fired. When an attribute list is used, only those attibutes in the list are checked; without the attribute list, all attributes in the table are checked. If the corresponding values are the same, the trigger will not be fired.
   • For statement trigger, the values are not checked to fired the trigger. Even if the values are the same before and after update, the trigger will still be fired.

10. Renaming the table and/or attribute name (RENAME command) has no effect on the existing triggers. Triggers will function correctly under the new name automatically.

11. Altering the table by adding and/or changing the attributes (ALTER TABLE command), has no effect on triggers. Triggers will function correctly under the new table structure automatically. However, if an attribute is deleted, then all the triggers explicitly connected to that attribute will be deleted, the triggers connected to all attribute implicitly (no attribute specified) keep unchanged.

12. It is possible to create a trigger on a view.

13. Trigger can be temporarily disabled or enabled by using the ALTER TABLE command. When trigger is created, it is automatically enabled.

14. When multiple triggers are created for the same event, the priority within the trigger definition will determine the order of the trigger procedures to be executed. The priority is set to 0.000 when trigger is created. Use the ALTER TABLE command to change the priority of the trigger.

15. Trigger definition can be viewed by using DISPLAY TABLE table_name ALL; command.

Privileges Required

ALTER privilege.

Example

Create a trigger history_log whenever change is made to the loans table, a procedure log_event is executed to record the change.

Step one is to create a user defined procedure. In this example, user.c contains such procedure.

   #include   <usrfns.h>
   #include   <time.h>
   
   static void  print_rec (void*   mr,
                           void*   rec,
                           char*   tag)
   {
      extern  char*   mrganame (addr);
   
      void*           attr;
      int             i;
      char*           val;
   
      for (i = 1; ; ++i)
      {
         attr = mrigeta (mr, i);
         if (attr == 0)
         break;
   
         val = mrgetvs (rec, attr);
         if (val == 0)
            val = "(NULL)";
   
         printf ("\t%s %s = '%s'\n",
                  tag, mrganame (attr), val);
      }
   }
   
   static void  trigger_proc (char*  procname)
   {
      extern  char*   mrgname ();
      extern  int     strcmp ();
   
      time_t  t;
      void*   mr;
      void*   newrec;
      void*   oldrec;
      char*   s;
   
      mspsm_trig_get_record_info (& mr, & oldrec, & newrec);
   
      printf ("+++ Trigger Procedure +++  %s  for table '%s'\n",
               procname, mrgname (mr));
      time (& t);
      printf ("\ttime: %s", ctime (& t));
      if (oldrec == 0)
          printf ("\tNo Old Record\n");
      else
          print_rec (mr, oldrec, "Old");
      if (newrec == 0)
          printf ("\tNo New Record\n");
      else
          print_rec (mr, newrec, "New");
   }

   void    log_ins_upd_del ()
   {
       trigger_proc ("log_ins_upd_del");
   }

Compile the source file user.c and create a shared object file user.dll using emppsmcc from the operating system prompt:

   emppsmcc -o user.dll user.c 

Next step is to define module and trigger definition. Then link the shared object file user.dll to the module definition:

   CREATE MODULE for_trigger
      PROCEDURE log_event ( ) 
      EXTERNAL NAME log_ins_upd_del;
   END MODULE;

   CREATE TRIGGER history_log BEFORE
      INSERT OR DELETE OR UPDATE
      ON loans FOR EACH ROW
      EXECUTE log_event;

   UPDATE MODULE for_trigger FROM 'user.dll';

The command:

   DISPLAY TABLE loans ALL;

will produce the following result:

   *** Table: loans ***

   Attributes:
     number                integer
     name                  char(25,1)
     date                  date(1)
     amount                dollar(6,1)

   Triggers:
     history_log
       before
       insert or delete or update
       for each row
       execute log_event
       priority 0.000
       enable

   Creator: joe
   Lock Level: RECORD

   Table #: 8
   Records: 12
   Record size: 36

The following is the sample output from the trigger:

   INSERT loans VALUES (5, 'Mosca', '2 Mar 1998', '999'
                        3, 'Jones', '7 Feb 1998', '800');

   +++ Trigger Procedure +++  log_ins_upd_del  for table 'loans'
           time: Tue Mar  3 10:55:49 1998
           No Old Record
           New number = '5'
           New name = 'Mosca'
           New date = '2 March     1998'
           New amount = '$999.00'
   +++ Trigger Procedure +++  log_ins_upd_del  for table 'loans'
           time: Tue Mar  3 10:55:49 1998
           No Old Record
           New number = '3'
           New name = 'Jones'
           New date = '7 February  1998'
           New amount = '$800.00'
   
   UPDATE loans SET amount = '999.99' WHERE date > '31 Dec 1997';

   +++ Trigger Procedure +++  log_ins_upd_del  for table 'loans'
           time: Tue Mar  3 10:55:49 1998
           Old number = '5'
           Old name = 'Mosca'
           Old date = '2 March     1998'
           Old amount = '$999.00'
           New number = '5'
           New name = 'Mosca'
           New date = '2 March     1998'
           New amount = '$999.99'
   
   DELETE loans WHERE amount > 800;

   +++ Trigger Procedure +++  log_ins_upd_del  for table 'loans'
           time: Tue Mar  3 10:55:49 1998
           Old number = '5'
           Old name = 'Mosca'
           Old date = '2 March     1998'
           Old amount = '$999.99'
           No New Record

If the above trigger was created without FOR EACH ROW option, then the following command:

   INSERT loans VALUES (5, 'Mosca', '2 Mar 1998', '999'
                        3, 'Jones', '7 Feb 1998', '800');

will generate the following output:

   +++ Trigger Procedure +++  log_ins_upd_del  for table 'loans'
           time: Tue Mar  3 10:55:49 1998
           No Old Record
           New number = '5'
           New name = 'Mosca'
           New date = '2 March     1998'
           New amount = '$999.00'
           No Old Record
           New number = '3'
           New name = 'Jones'
           New date = '7 February  1998'
           New amount = '$800.00'

The DROP TRIGGER command removes the trigger definition from the database data dictionary.

Syntax

DROP TRIGGER trigger_name

where:

trigger_name

is the trigger name.

Notes

1. DROP TRIGGER will remove the trigger definition from the data dictionary. It will not remove the module procedure that trigger is used. Use DROP MODULE to delete the trigger procedure.

2. Trigger can be temporarily disabled or enabled by using the ALTER TABLE command instead of removing the trigger definition.

Privileges Required

ALTER privilege.

Example

To drop the trigger history_log:

   DROP TRIGGER history_log;

will remove the trigger definition from the table loans.

The DISPLAY DATABASE command prints the name of the database and the names of selected tables in the database.

Syntax

DISPLAY

|DATABASE |DB

| [database] [ON [TABLE] |

|table |[NOT] match_op pattern

|] |

[ALL] [DUMP]

[

|INTO |ONTO

| file]; |

where:

match_op	is one of LIKE, MATCH, !MATCH, SMATCH, or !SMATCH.
pattern	is any pattern accepted by a WHERE clause; it must be given as a quoted string.
file	is any filename.

Notes

1. The keyword DATABASE may be abbreviated to DB.

2. If a database name is specified, the names of the tables in that database are listed. If no database name is specified, the names of the tables in the current database are listed.

3. If a table name is specified, only information for that table is listed. If a pattern match is specified, information for tables that match the pattern is listed.

4. If ALL is specified, the structure of each user-created table in the database is also shown. DUMP produces output in a condensed form, listing just the table names in the database. If ALL is used with DUMP the condensed output includes table definitions as well.

5. If INTO file or ONTO file is used, the output is sent to the named file rather than to the terminal. INTO overwrites any existing file, while ONTO appends to it. Both create the file if necessary.

6. If there are no tables in the database, the command will print just the name of the database.

7. Views will be displayed along with the tables in the database.

Privileges Required

None required to display the database itself but only tables on which privileges have been granted will be displayed.

Example

1. List All Tables in a Database

   To list all the tables in the default database, use:

      DISPLAY DB;
   
   

   which produces the following output:

      **** Database: repairs ****
   
      auto parts
      customers
      loans
      personnel
   
   

2. List Structure of All Tables in a Database

   To show the structure of all the tables as well, use:

      DISPLAY DB ALL;
   
   

   which produces:

   
      ***Database: repairs ****
      
      *** Table: auto parts ***
      
      supplier            char(20,1) Not Null
      phone               char(8,1)
      part name           char(25,1)
      part no.            longinteger
      price               dollar(4,1)
      
      *** Table: customers ***
      
      name                char(20,1) Not Null
      address             text(30,124,30,1)
      comments            text(25,1,80,1)
      
      *** Table: loans ***
      
      number              integer
      name                char(25,1)
      date                date(1)
      amount              dollar(6,1)
      
      *** Table: personnel ***
      
      number              integer
      name                char(25,1)
      phone               char(15,1)
      credit_limit        dollar(6,1)
   
   

3. Display the Structure of a Specific Table in a Database

   To restrict the information listed to the loans table, use:

      DISPLAY DB ON loans ALL;
   
   

   which gives as output:

      **** Database: repairs **** 
   
      *** Table: loans *** 
   
      number             integer
      name               char(25,1)
      date               date(1)
      amount             dollar(6,1)
   
   

4. Store the List of Tables in a Database into an Operating System File

   To place the list of tables in the current database into a file called dblist, use:

      DISPLAY DB INTO 'dblist';
   
   

The CREATE REPLICATION MASTER claims a table to be a candidate Replication Master Table of a Replication Table (usually a Replicate Table). This command adds a Replication Master Entry for a replication table.

Syntax

CREATE REPLICATION MASTER replication_master_info 

   ON replication_table [WITH FORCE];

where:

replication_master_info	Information to access Replication Master Table. It consists of replication master server name, database name and table name of the replication master table. This is the table to be claimed as a candidate replication master table. It's format is: | server_name : database_name : table_name | | server_name : database_name .. table_name |
replication_table	is the name of the table for which the replication master entry will be added.

Notes

Privileges Required

ALTER privilege on replication_table.

Syntax 1

DROP REPLICATION MASTER replication_master_info 

   ON replication_table [WITH FORCE];

Syntax 2

DROP ALL REPLICATION |MASTERS | ON replication_table [WITH FORCE];
                     |MASTER  |

where:

 | server_name : database_name : table_name  |
 | server_name : database_name .. table_name |

Notes

Notes and examples for this command can be found in [ Manual F1: Replication User's Guide: References].

Privileges Required

ALTER privilege on replication_table.

Syntax:

   CREATE REPLICATION REPLICATE replication_replicate_info 

      ON replication_table [WHERE srsc];

where:

replication_replicate_info	Information to access Replication Replicate Table. It consists of host name, database name and table name of the Replication Replicate Table. It's format is: | host_name : database_name : table_name | | host_name : database_name .. table_name | Here, the host_name is the name of the host where the database resides. If it is in a different network than the replication_table, complete network address must be specified. host_name can also be IP address. The database_name is supposed to be absolute path to a replication database. If not, Empress issues a warning message, and considers this to be a logical database name. In addition, database_name is case sensitive. Hence improper use of characters in specifying disk drive or path in Microsoft Windows environment may produce error in creating replicate table.
replication_table	is the name of the replication table for which the Replication Replicate Entry will be added.
srsc	Subset Replication Search Condition. This is a restricted SQL search condition for subset replication. SRSC does not allow: explicit database and table references joins with other tables Sub-query statements aggregate function grouping clauses

Notes

Notes and examples for this command can be found in [ Manual F1: Replication User's Guide: References].

Privileges Required

This command is to be used by DBA, or the creator of replication_table (Note that this is different from DBA group privilege).

Syntax 1

DROP REPLICATION REPLICATE replication_replicate_info

    ON replication_table;

Syntax 2

DROP ALL REPLICATION |REPLICATES | ON replication_table;
                     |REPLICATE  |

where:

 | host_name : database_name : table_name  |
 | host_name : database_name .. table_name |

Notes

Privileges Required

This command is to be used by DBA, or the creator of replication_table (Note that this is different from DBA group privilege).

The CREATE REPLICATE TABLE command is used to create a table as a Replication Replicate Table of a Replication Master table.

Syntax

   CREATE [AND INSERT INTO] REPLICATE [TABLE] replicate_table 

      FROM replication_master_info;

where:

replication_master_info	Indicates access information to the replication master table from which replicate_table will be created. It consists of replication master server name, database name and table name of the master table. It's format is: | server_name : database_name : table_name | | server_name : database_name .. table_name |
replicate_table	is the name of the table that is going to be a replicate of the replication master table.

Notes

Notes and examples for this command can be found in [ Manual F1: Replication User's Guide: References].

Privileges Required

INSERT privilege on the system table sys_tables of the database to contain replicate_table. This is the same privilege required for creating any database table.

The CREATE ROLE command creates a role in a database. A role generally refers to a title or a set of duties, which is a named bundle of zero or more privileges. Roles are designed to ease the administration of privileges. They control access to database tables and permit easier management in the database.

Syntax

CREATE ROLE role;

where:

role	is a name of a role.

Notes

1. Roles allow to collect users into a single unit against which privileges can be applied.
2. The CREATE ROLE command specifically defines role to be a role rather than a User. This role cannot be a defined user in the database schema, such as DBA, USER, PUBLIC, etc.

Example

Create a Role

To create a role helpdesk, do:

   CREATE ROLE helpdesk;

The DROP ROLE command removes a role from a database.

Syntax

DROP ROLE role;

where:

role	is a name of a role.

Notes

1. Only creator of the role can drop it.
2. For each user/role, it was granted use of the role, the following revoke command will be called:

       REVOKE role FROM grantee RESTRICT
   

Example

Drop a Role

If the user joe enters the following sequence of commands:

   CREATE ROLE helpdesk;
   CREATE ROLE tech;
   GRANT helpdesk TO peter WITH ADMIN OPTION;
   GRANT helpdesk TO tech;
   DROP ROLE helpdesk;

where peter is another user and helpdesk and tech are roles, DROP ROLE command will implicitly invoke the following two commands:

   REVOKE helpdesk FROM tech RESTRICT;
   REVOKE helpdesk FROM peter RESTRICT;

However, if peter grants helpdesk role to another user before joe tries to drop the helpdesk role, DROP ROLE command will fail since it cannot remove dependant roles.

To drop a role tech, do:

   DROP ROLE tech;

The GRANT ROLE command grants the use of one or more roles to one or more users/roles.

Syntax

GRANT role {, role } TO grantee {, grantee } [ WITH ADMIN OPTION ]

where:

role	is a name of a role.
grantee	It can be one of: |username |role |PUBLIC | | | It represents the name of a user/role. PUBLIC refers to all users and username is an operating system account name or login name.

Notes

1. The optional WITH ADMIN OPTION clause defines a grantable role; one that grantee may, in turn, grant to other users/roles.
2. The grantor of the roles (i.e one who grants the roles) must hold these roles as grantable roles (that is, WITH ADMIN OPTION).

Example

Grant a Role

To grant a grantable role helpdesk to user peter, do:

   GRANT helpdesk TO peter WITH ADMIN OPTION;

The REVOKE ROLE command revokes one or more roles from one or more users/roles.

Syntax

   REVOKE [ ADMIN OPTION FOR ] role {, role } 

          FROM grantee {, grantee } | RESTRICT | 
                                    | CASCADE  |

where:

role	is a name of a role.
grantee	It can be one of: |username |role |PUBLIC | | | It represents the name of a user/role. PUBLIC refers to all users and username is an operating system account name or login name.

Notes

1. The optional ADMIN OPTION FOR clause allows one to revoke only the grantability of a role(s).
2. If the REVOKE ROLE command specifies CASCADE, it cascades down to revoke roles that would otherwise be abandoned. If the REVOKE ROLE command includes RESTRICT, the revoke succeeds only if the role being revoked has no dependent roles.

Example

Revoke a Role

To revoke a role helpdesk from the user peter, do:

   REVOKE helpdesk FROM peter;

The DISPLAY ROLE command displays role(s) in a database and it can be used in Empress interactive SQL.

Syntax

   DISPLAY ROLE [role] [ALL] [| INTO | file];
                              | ONTO |

where:

role	is a name of a role.
file	is a file name.

Notes

1. If role is not specified, the information on all database roles will be displayed. More details can be displayed with a keyword ALL.
2. The output of the command may be diverted from the terminal into a new file or appended to an existing file by specifying INTO or ONTO respectively, and a file name.

Example

Display a Role

To display a role helpdesk, do:

   DISPLAY ROLE helpdesk;

which produces the following output:

   ***  Role: helpdesk  ***

     Creator:         joe
     Admin Option:    Y
     Grantee:         tech
     Grantee:         peter (Admin)