Eloquence B.07.00 Release Notes

Eloquence B.07.00 Release Notes

Eloquence B.07.00 - Database

DBUTIL Script Syntax

The DBUTIL program runs in batch mode when a script file is specified on the command line. This document specifies the dbutil script syntax.

Script file format:

All statements are separated by a semicolon.
A leading hash sign (#) indicates a comment.
A quote character in a quoted string must be escaped with a backslash.
Data set, item and index item names can be written in quotes or quotes can be omitted.

Statement syntax:

CONNECT
EXIT
CREATE USER
CHANGE USER
DROP USER
CHANGE PASSWORD
RENAME DATABASE
DATABASE
CREATE GROUP
DROP GROUP
GRANT and REVOKE user privileges
GRANT and REVOKE group privileges
GRANT and REVOKE member privileges
GRANT and REVOKE data set privileges
CREATE PROPERTY
CHANGE PROPERTY
DROP PROPERTY
TurboIMAGE compatible database properties
CHANGE DATABASE
CREATE ITEM
CHANGE ITEM
DROP ITEM
CREATE IITEM
CHANGE IITEM
DROP IITEM
CREATE SET
CHANGE SET
DROP SET
SET clauses
- NAME
- ADD ITEM
- DELETE ITEM
- ADD INDEX
- DELETE INDEX
- ADD PATH
- DELETE PATH

CONNECT

Syntax:

  CONNECT TO "server" [AS "user" [PASSWORD "password"]];
  CONNECT AS "user" [PASSWORD "password"];

Specifies the server to contact and the user and password to logon to the server.

Server is specified with a [host][:service] syntax where host defaults to the local system and service defaults to the "eloqdb" service name. Alternatively, a server may be defined from the command line (using the -h option), or the EQ_DBSERVER environment variable may be used.

User and password can be a reference to a file if prepended by "file:". User and password may also be defined by either command line options or the EQ_DBUSER and EQ_DBPASSWORD environment variables.

Notes:

In a script, the CONNECT TO statement is optional and can be used to override the defaults.

Examples:

  CONNECT TO "server";
  CONNECT TO "server:eloqdb";

Legacy syntax (deprecated, do not use in new scripts):

  CONNECT "server[:service]";
  LOGON "user" [PASSWORD "password"];

EXIT

Syntax:

  EXIT;

Exit parser, the rest of the script file will be ignored.

CREATE USER

Syntax:

  CREATE USER "user" 
    [PASSWORD "password"] 
    [PRIVILEGES [user-priv-list]];

Create a new user with the specified password, if any.

The PRIVILEGES clause, if included, defines the privileges associated with the user. Multiple privileges can be specified, separated by a comma.

The following user privileges are available:

DBA: The user has server administration privileges.
CONNECT: The user is allowed to connect the server. This is implied if a user has the DBA privilege.
UADMIN: The user is allowed to administrate user accounts.

Notes:

The UADMIN privilege is required to maintain database users.
The CONNECT privilege is granted by default if the PRIVILEGES clause is not present.

Example:

  CREATE USER "mike" PASSWORD "secret" PRIVILEGES CONNECT;

CHANGE USER

Syntax:

  CHANGE USER "user"
    [NAME "name"]
    [PASSWORD "password"]
    [PRIVILEGES [user-priv-list]];

Changes a user. If the NAME clause is present, it specifies a new user name. If the PASSWORD clause is present, it specifies the new password or removes the password if empty (""). If the PRIVILEGES clause is specified, it defines the privileges associated with the user; if empty, all privileges will be reset.

Notes:

The UADMIN privilege is required to maintain database users.

Example:

  CHANGE USER "mike" PASSWORD "sesame";

DROP USER

Syntax:

  DROP USER "user1" [,"user2"];

Removes the user and all references.

Notes:

The UADMIN privilege is required to maintain database users.

Example:

  DROP USER "mike","marc";

CHANGE PASSWORD

Syntax:

  CHANGE PASSWORD FOR USER "user" TO "password";

Change the password (or remove if empty) for the specified user.

Notes:

The UADMIN privilege is required to maintain database users.

Example:

  CHANGE PASSWORD FOR USER "mike" TO "sesame";

Legacy syntax (deprecated, do not use in new scripts):

  ALTER PASSWORD FOR USER "user" TO "password";

RENAME DATABASE

Syntax:

  RENAME DATABASE ["Name"] TO "NewName";

Renames a database. If the previous name is not specified the current database is used.

Notes:

The current implementation allows renaming while the database is in use. The change will become effective when the database is no longer used, i.e. the last reference to it has been closed.

Example:

  RENAME DATABASE "db" TO "db2";

DATABASE

Syntax:

  DATABASE "database";

Specifies the database context.

Notes:

The Eloquence database server can hold more than one database.
For database specific statements, the database on which they should operate must be specified.
If the script restructures the database, only one DATABASE statement may be present per script.

Example:

  DATABASE "db";

CREATE GROUP

Syntax:

  CREATE GROUP "group" 
    [PRIVILEGES [group-priv-list]];

Create a new group in the current database. If the PRIVILEGES clause is specified, it defines the privileges associated with the group (could be empty).

Group capabilities which are not data set specific are specified by group privileges. Multiple privileges can be specified, separated by a comma.

The following group privileges are available:

DADMIN: Group members have administration privileges for this database (this is implied for users which have the DBA privilege).
DBPRIV: Group members are allowed to assign database specific privileges.

Notes:

The Eloquence database uses groups to manage database specific privileges.
A database context must be defined before managing database groups.
The DBPRIV privilege is required to maintain database groups.
When a user is associated with a group, it will gain all capabilities granted to the group.
A user can be a member of up to 8 groups per database.

Example:

  CREATE GROUP "users";

CHANGE GROUP

Syntax:

  CHANGE GROUP "group"
    [NAME "name"]
    [PRIVILEGES [group-priv-list]];

Changes a group. If the NAME clause is present, it specifies the new group name. If the PRIVILEGES clause is specified, it defines the privileges associated with the user (could be empty to reset all privileges).

Notes:

A database context must be defined before managing database groups.
The DBPRIV privilege is required to maintain database groups.

Example:

  CHANGE GROUP "uers" NAME "users";

DROP GROUP

Syntax:

  DROP GROUP "group" [,"group" ...];

Removes the group and all references.

Notes:

A database context must be defined before managing database groups.
The DBPRIV privilege is required to maintain database groups.

Example:

  DROP GROUP "users";

GRANT and REVOKE user privileges

Syntax:

  GRANT user-priv-list 
    TO {ALL|"user" [,"user" ...]};

  REVOKE user-priv-list 
    FROM {ALL|"user" [,"user" ...]};

Grant or revoke privileges to or from a user.

Multiple privileges can be specified, separated by a comma. The following user privileges are available:

DBA: The user has server administration privileges.
CONNECT: The user is allowed to connect the server. This is implied if a user has the DBA privilege.
UADMIN: The user is allowed to administrate user accounts.

Either a list of user names, separated by comma, or the keyword ALL or PUBLIC can be specified to indicate all users.

Notes:

The UADMIN privilege is required to maintain database users.
The CONNECT privilege is granted by default if the PRIVILEGES clause is not present.

Example:

  REVOKE CONNECT FROM ALL;
  GRANT CONNECT TO "mike","marc";

This will disallow all users except "mike" and "marc" to connect the database server.

Legacy syntax (deprecated, do not use in new scripts):

  GRANT user-priv-list 
    TO {PUBLIC|"user" [,"user" ...]};

  REVOKE user-priv-list 
    FROM {PUBLIC|"user" [,"user" ...]};

GRANT and REVOKE group privileges

Syntax:

  GRANT group-priv-list 
    TO {ALL|"group" [,"group" ...]};

  REVOKE group-priv-list 
    FROM {ALL|"group" [,"group" ...]};

Grant or revoke privileges to or from a group.

Group capabilities which are not data set specific are specified by group privileges. Multiple privileges can be specified, separated by a comma.

The following group privileges are available:

DADMIN: Group members have administration privileges for this database (this is implied for users which have the DBA privilege).
DBPRIV: Group members are allowed to assign database specific privileges.

Either a list of group names, separated by comma, or the keyword ALL can be specified to indicate all groups (user "dba" and "public" are excluded).

Notes:

A database context must be defined before managing database groups.
The DBPRIV privilege is required to maintain database groups.
When a user is associated with a group, it will gain all capabilities granted to the group.

Example:

  REVOKE DBPRIV FROM "users";
  GRANT DBPRIV,DADMIN TO "dba";

Legacy syntax (deprecated, do not use in new scripts):

  GRANT group-priv-list 
    TO {PUBLIC|"group" [,"group" ...]};

  REVOKE group-priv-list 
    FROM {PUBLIC|"group" [,"group" ...]};

GRANT and REVOKE member privileges

Syntax:

  GRANT "group" [,"group" ...] 
    TO {ALL|"user" [,"user" ...]};

  REVOKE "group" [,"group" ...] 
    FROM {ALL|"user" [,"user" ...]};

Grant or revoke privileges assigned to a group to or from a user.

A list of group names can be specified, separated by comma.

A list of user names can be specified, separated by comma, or the keyword ALL or PUBLIC can be used to indicate all users.

Notes:

A database context must be defined before managing database groups.
The DBPRIV privilege is required to maintain database groups.
When a user is associated with a group, it will gain all capabilities granted to the group.
A user can be a member of up to 8 groups per database.

Example:

  GRANT "users" TO "mike",marc";

This will make the users "mike" and "marc" members of the group "users".

Legacy syntax (deprecated, do not use in new scripts):

  GRANT "group" [,"group" ...] 
    TO {PUBLIC|"user" [,"user" ...]};

  REVOKE "group" [,"group" ...] 
    FROM {PUBLIC|"user" [,"user" ...]};

GRANT and REVOKE data set privileges

Syntax:

  GRANT {ALL PRIVILEGES|dataset-priv-list}
    ON {ALL|dataset-list} 
    TO {ALL|"group" [,"group" ...]};

  REVOKE {ALL PRIVILEGES|dataset-priv-list}
    ON {ALL|dataset-list} 
    TO {ALL|"group" [,"group" ...]};

Grant or revoke data set access privileges to or from a group. Access privileges include READ, WRITE and ERASE. Multiple privileges can be specified, separated by a comma.

If ALL is specified as the data set name, this will apply to all data sets. If PUBLIC or ALL is specified as the group name, this will apply to all groups.

Notes:

A database context must be defined before managing data set privileges.
The DBPRIV privilege is required to maintain data set privileges.

Example:

  REVOKE ALL PRIVILEGES ON ALL FROM "users";
  GRANT WRITE ON "CUSTOMERS","PARTS" TO "users";
  GRANT READ ON ALL TO "users";
  GRANT ERASE ON "HISTORY" TO "priv";

This will provide read access on all data sets to all members of the group "users" and write access to the data sets CUSTOMERS and PARTS. Members of the group "priv" are allowed to erase the data set HISTORY.

Legacy syntax (deprecated, do not use in new scripts):

  GRANT {ALL PRIVILEGES|dataset-priv-list}
    ON {ALL|dataset-list} 
    TO {PUBLIC|"group" [,"group" ...]};

  REVOKE {ALL PRIVILEGES|dataset-priv-list}
    ON {ALL|dataset-list} 
    TO {PUBLIC|"group" [,"group" ...]};

CREATE PROPERTY

Syntax:

  CREATE PROPERTY "key" 
    VALUE "value"
    [FLAGS [property-flag-list]];

Create a database property. The VALUE clause specifies the associated value. The FLAGS clause can be used to specify additional flags for this property, including SYSTEM and IMAGE3K.

Notes:

A database context must be defined before managing database properties.
The DADMIN privilege is required to maintain database properties.

CHANGE PROPERTY

Syntax:

  CHANGE PROPERTY "key"
    VALUE "value"
    [FLAGS [property-flag-list]];

Change a database property. The VALUE clause specifies the new associated value. The FLAGS clause can be used to modify flags (or remove all flags if empty) for this property.

Notes:

A database context must be defined before managing database properties.
The DADMIN privilege is required to maintain database properties.

DROP PROPERTY

Syntax:

  DROP PROPERTY "key" [,"key" ...];

Delete the database property. A comma separated list of property keys can be specified.

Notes:

A database context must be defined before managing database properties.
The DADMIN privilege is required to maintain database properties.

TurboIMAGE compatible database properties

The following statements provide a compatible syntax to set commonly used TurboIMAGE database properties.

SET LANGUAGE = n;

Set the TurboIMAGE LANGUAGE property to n where n is a numerical value.

Notes:

The value of the LANGUAGE property is returned by DBINFO mode 901.
This statement sets the HP3K_LANG database property.

SET CIUPDATE = mode;

Set the TurboIMAGE CIUPDATE property to mode where mode is DISALLOWED, ALLOWED or ON.
The default value is ALLOWED.

Notes:

The value of the CIUPDATE property is used by DBUPDATE and returned by DBINFO mode 502.
This statement sets the HP3K_CIUPDATE database property.

SET BTREEMODE1 = mode;

Set the TurboIMAGE BTREEMODE1 property to mode where mode is ON or OFF.
The default value is ON.

Notes:

The value of the BTREEMODE1 property is used by DBFIND and returned by DBINFO mode 113.
This statement sets the HP3K_BTREEMODE1 database property.

SET WILDCARD = "c";

Set the TurboIMAGE WILDCARD property to c where c is a valid ASCII character.
The default value is @.

Notes:

The value of the WILDCARD property is used by DBFIND and returned by DBINFO mode 113.
This statement sets the HP3K_WILDCARD database property.

SET SUBSYSTEM = mode;

Set the TurboIMAGE SUBSYSTEM property to mode where mode is NONE, READ or RW.
The default value is RW.

Notes:

The value of the SUBSYSTEM property is returned by DBINFO mode 501.
This statement sets the HP3K_SUBSYSTEM database property.

CHANGE DATABASE

Syntax:

  CHANGE DATABASE
    [NAME NewDatabaseName ;]
    [LANGUAGE "collseq" ;]

  CHANGE DATABASE {
     ...
  }

Change database settings. If NAME clause is present, is specifies the new database name. If the LANGUAGE clause is present, it specifies the new default collating sequence for the database. Multiple item definitions can be enclosed in curly braces ({}).

Notes:

A database context must be defined before modifying a database schema.
The DADMIN privilege is required to modify a database schema.
Chaning the default collating sequence changes all indexes using the previous default collating sequence. This will case rebuild of all indexes for master sets.

Examples:

  CHANGE DATABASE
   LANGUAGE "german@nofold";

CREATE ITEM

Syntax:

  CREATE ITEM
     Name, [count] type [(format)] ;

  CREATE ITEM {
     ...
  }

Create new item. Multiple item definitions can be enclosed in curly braces ({}).

Notes:

A database context must be defined before modifying a database schema.
The DADMIN privilege is required to modify a database schema.
New items are appended to the existing list of items.

Examples:

  CREATE ITEM
   matchcode,x10;

  CREATE ITEM {
   description1,X20;
   description2,X20;
  }

CHANGE ITEM

Syntax:

  CHANGE ITEM ItemName
    [NAME NewItemName ;]
    [TYPE [count] type [(format)] ;]
    [TYPE (format) ;]
    [FORMAT format ;]

  CHANGE ITEM ItemName {
    ...
  }

Change item definition. If NAME clause is present, is specifies the new item name. If the TYPE clause is present, it specifies the new item type or the format number. Multiple item definitions can be enclosed in curly braces ({}).

Notes:

A database context must be defined before modifying a database schema.
The DADMIN privilege is required to modify a database schema.
The order of items cannot be changed.
The search item of a manual master set can not be modified in a way which could lead to duplicates. For example, shortening a search item in a manual master from X20 to X10 is not allowed.

Examples:

  CHANGE ITEM matchcod
   NAME matchcode;

  CHANGE ITEM descr1 {
   NAME description1;
   TYPE x20;
  }

DROP ITEM

Syntax:

  DROP ITEM ItemName [,ItemName ...];

Delete a list of item definitions.

Notes:

A database context must be defined before modifying a database schema.
The DADMIN privilege is required to modify a database schema.
Deleting an item will fail if it is still used in a data set or index item.

Example:

  DROP ITEM matchcode;

CREATE IITEM

Syntax:

  CREATE IITEM 
    [iItemName = ItemName[:length] [, ...]] ;

  CREATE IITEM {
    [iItemName = ItemName[:length] [, ...]] ;
    ...
  }

Create new index item. Multiple item definitions can be enclosed in curly braces ({}).

Notes:

A database context must be defined before modifying a database schema.
The DADMIN privilege is required to modify a database schema.
New index items are appended to the existing list of index items.

Examples:

  CREATE IITEM
   imatchcode = matchcode;

  CREATE IITEM {
   imatchcode = matchcode;
   idescription = description1:10, description2:10;
  }

CHANGE IITEM

Syntax:

  CHANGE IITEM iItemName
    [NAME NewiItemName]
    [TYPE ItemName[:length] [, ...]];

  CHANGE IITEM iItemName {
    ...
  }

Change index item definition. Multiple item definitions can be enclosed in curly braces ({}). If NAME clause is specified, the index item name is changed. If the TYPE clause is present, the index item definition is replaced.

Note:

A database context must be defined before modifying a database schema.
The DADMIN privilege is required to modify a database schema.
The order of index items cannot be changed.
Any indexes using the specified index item will be rebuilt.

Example:

  CHANGE IITEM 
   imatchcode = matchcode:8;

DROP IITEM

Syntax:

  DROP IITEM iItemName [, ...];

Delete a list of index item definitions.

Notes:

A database context must be defined before modifying a database schema.
The DADMIN privilege is required to modify a database schema.
Deleting an index item will fail if it is still used in a data set.

Example:

  DROP IITEM imatchcode;

CREATE SET

Syntax:

  CREATE SET SetName, SetType {
    [ADD ITEM ... ;]
    [ADD INDEX ... ;]
    [ADD PATH ... ;]
  }

Create new data set. SetType is one of AUTOMATIC (A), MANUAL (M) or DETAIL (D). Specifying curly braces ({}) allows multiple change clauses.

Please see below for the syntax of the ADD ITEM, ADD INDEX and ADD PATH clauses.

Notes:

A database context must be defined before modifying a database schema.
The DADMIN privilege is required to modify a database schema.
New data sets are appended to the existing list of data sets. The exception are master sets which are inserted before the first path.

Example:

  CREATE SET custmers {
   ADD ITEM custno(*),matchcode,name1,name2;
   ADD INDEX imatchcode;
  }

CHANGE SET

Syntax:

  CHANGE SET SetName {
    [NAME ... ;]
    [ADD ITEM ... ;]
    [DELETE ITEM ... ;]
    [ADD INDEX ... ;]
    [DELETE INDEX ... ;]
    [ADD PATH ... ;]
    [DELETE PATH ... ;]
    [SET|UNSET ...;]
  }

Change data set definition. Specifying curly braces ({}) allows multiple change clauses.

Please see below for the syntax of the NAME, ADD ITEM, DELETE ITEM, ADD INDEX, DELETE INDEX, ADD PATH and DELETE PATH and SET and UNSET clauses.

Notes:

A database context must be defined before modifying a database schema.
The DADMIN privilege is required to modify a database schema.
The type of a data set can currently not be changed.
The order of data sets cannot be changed.

Examples:

  CHANGE SET custmrs
   NAME customers;

  CHANGE SET custmers {
   ADD ITEM matchcode;
   ADD INDEX imatchcode;
  }

DROP SET

Syntax:

  DROP SET SetName [, ...];

Delete the specified data sets from the database. Any paths or indexes are deleted automatically.

Note:

A database context must be defined before modifying a database schema.
The DADMIN privilege is required to modify a database schema.
If a path is deleted the related data set must be reorganized.

Example:

  DROP SET customers,parts;

SET clauses

The NAME clause is used to rename a data set.

Syntax:

  NAME NewSetName;

Change data set name to NewSetName.

Example:

 CHANGE SET custmrs
  NAME customers;

The ADD ITEM clause is used to add an item to a data set. By default, the item is appended to the entry.

Syntax:

  ADD ITEM 
    [BEFORE|AFTER ItemName]
    ItemName[([!]SetName[(SortItemName)])] [, ...];

  ADD ITEM 
    [BEFORE|AFTER ItemName]
    ItemName[(*)] [, ...];

Add one or multiple items to the data set. For a detail set a path can be specified as well.
When creating a master set, the search item can optionally be specified with a (*) if it is different from the first item.

Notes:

The dataset must be reorganized.
If a path is specified any related set must be reorganized.
The order of items in a data set cannot be changed. However, deleting an item and subsequently inserting the item in the desired position has the same effect. The database server will recognize this as a changed position.

Examples:

  CHANGE SET orders
   ADD ITEM orderno(!id(orderdate));

  CREATE SET customers
   ADD ITEM custname, custno(*);

  CHANGE SET customers
   ADD ITEM AFTER custno
    name, matchcode;

The DELETE ITEM clause is used to delete an item from a data set.

Syntax:

  DELETE ITEM ItemName [, ...];

Delete one or multiple items from data set.

Note:

The dataset must be reorganized.

Example:

  CHANGE SET customers
   DELETE ITEM comment;

The ADD INDEX clause is used to add an index to a data set.

Syntax:

  ADD INDEX iItemName [/"collseq"];

Add index to data set. If a collating sequence is specified, it is used to define the ordering of any string segment in the index.

Note:

The dataset must be reorganized if the first index is added.

Example:

  CHANGE SET customers
   ADD INDEX imatchcode / "german";

The DELETE INDEX clause is used to delete an index from a data set.

Syntax:

  DELETE INDEX iItemName;

Delete index from data set.

Note:

The dataset must be reorganized if the last index is deleted.

Example:

  CHANGE SET customers
   DELETE INDEX imatchcode;

The ADD PATH clause is used to add a new path to a data set.

Syntax:

  ADD PATH ItemName([!]SetName[(SortItemName)]);

Add path to detail data set, optionally specifying a sort item and primary path designator.

If the primary path designator (!) is specified, the new path is used as the primary path. If a sort item is specified the chain is ordered by this item and the subsequent items in the entry.

Notes:

This requires to reorganize both the detail set and the related master set.
To change a path, please delete the path first (using DELETE PATH) and add it again with the new specification.

Examples:

  CHANGE SET orders
   ADD PATH orderno(!id(orderdate));

  CHANGE SET orders {
   DELETE PATH orderno(id);
   ADD PATH orderno(!id(orderdate));
  }

The DELETE PATH clause is used to delete a path from a data set.

Syntax:

  DELETE PATH ItemName(SetName);

Delete path from detail data set. Can only be used with a detail set, SetName must refer to a master set.

Note:

This requires to reorganize both the detail set and the related master set.

Example:

  CHANGE SET orders
   DELETE PATH orderno(id);

The SET and UNSET clauses are used to change data set flags.

Syntax:

  SET set-flag [,set-flag ...];
  UNSET set-flag [,set-flag ...];

Currently, the only supported data set flag is INDEXED which is used with the turboIMAGE compatibility option to determine if BTREEMODE1 applies to a master data set.

Example:

  CHANGE SET customers
   SET INDEXED;