SQL Extensions

The following extensions have been made to SQL2:

Creating and deleting catalogs

QSM2 allows multiple catalogs to be accessed concurrently. These catalogs may provide access to many different types of data. For example one catalog might facilitate access to local texts, another access to oracle, and another to restricted information on a remote site. To create a catalog the following command is issued:

CREATE CATALOG catalogname IN 'filename_location' [database qualifier] [blocksize]

For example, to create a catalog named catalog1 in a file named "d:/catalog1" one would issue the command

create catalog catalog1 in 'd:/catalog1';

This will create a catalog having the indicated name in the indicated file location. It will also create a link to this file in the master directory (see installation). Note that catalogs may not be created in the master directory, since their name is reserved for the link referencing them. By default the catalog created will be a catalog intended to facilitate access to TextServer data and texts.

Catalogs, the links to them and the master directory containing the links can all be restricted according to desired NT access rights and auditing. Care should be taken to ensure that these files may not be casually deleted by unauthorized users.

Database qualifier:

If the database qualifier is present its syntax is:

DATABASE = type [( parameters )]

Thus to creates an oracle catalog which was going to define how users were to connect to the BLUE instance of oracle one would thus specify:

DATABASE=oracle(sid='BLUE')

If the SID is not specified it defaults to the current value of the environment variable ORACLE_SID.

Blocksize:

If the blocksize is present its syntax is

SIZE=n

Currently the only valid values for n are 0,10-13. 0 specifies that the default blocking factor is to be used in the created catalog. Otherwise blocks will be 2n bytes. I/O transfers of only this size will occur. A larger blocking factor will cause more data to be read and written to the catalog when disk transfers occur, but may reduce the number of occasions when indirection occurs because records exceed block sizes. It may also reduce the number of levels of index needed to access information in very large systems.

The specified catalog will not be registered (ie. have a shortcut created to it) unless it is successfully created, and initialized. A catalog once created may be removed by issuing the command

DROP CATALOG name {RESTRICT|CASCADE}

There is currently no distinction between RESTRICT and CASCADE. This command will only be preformed when issued by someone having permission to drop the specified catalog issues the request. The specified catalog and the shortcut to it will be deleted. If the catalog cannot be deleted because of the NT permissions associated with it, the shortcut will also not be deleted.

It is arguably as easy to delete the catalog and remove the shortcut to it, as to issue the above command. This may indeed be necessary if one or other of these files is accidently deleted.

Schemas

Schemas are created by issuing the SQL2 command:

CREATE_KW SCHEMA_KW [schemaname] [AUTHORIZATION username] [DEFAULT CHARACTER SET charsetname] ...

The schemaname defaults to the creators name, and the authorization (owner) defaults to the creator. Alternative character sets may be specified but are redundant. All internal characters are stored in unicode.

An information schema may be assigned to any catalog by merely creating a schema in this catalog named information_schema.

A schema may be dropped by issuing the command:

DROP SCHEMA schemaname {CASCADE|RESTRICT}

Grants

In addition to the standard SQL2 grants associated with tables and views (ie. SELECT, DELETE, INSERT, UPDATE, REFERENCE and USAGE) permissions may also be associated on a named catalog with specific authorizations existing within that catalog.

Possible permissions allowed the granted authorization(s) are:

GROUP:

Grantees of this permission inherit any access rights of the grantor has to tables, views and schemas. (The with grant option is meaningless in this context since one can only delegate ones own access rights to others)

CONNECT [AS]:

Grantees of this permission may connect using the grantors authorization without the need of any password. They may thus freely impersonate the grantor. (The with grant option is as above meaningless in this context)

SET SESSION:

May connect to any authorization (using the SET SESSION AUTHORIZATION command) without the need to know passwords. This permission is preserved across authorizations.

CREATE USER:

May create new authorizations (using the CREATE AUTHORIZATION command).

CREATE SCHEMA:

May create new schemas (using the CREATE SCHEMA command).

CREATE GROUP:

May GRANT {GROUP|CONNECT} to others.

DROP CATALOG:

May drop the catalog this authorization is contained within.

SYSTEM_USER:

May perform operations on tables, schemata and authorizations as though one was the creator. Specifically will have full access to all tables and views and may create and alter tables and views in schema not owned by invoker. May drop any table, schema, or authorization.

The syntax to grant one or more of the permissions shown above is:

GRANT {list of permissions} ON CATALOG <catalogname> TO {list of grantees} ...

Authorizations

Two authorizations are automatically created whenever a new catalog is created. A "PUBLIC" authorization is created, initially having no permissions. All users inherit any permissions granted to "PUBLIC". The second authorization is that of the user creating the catalog. This authorization is initially granted all permissions.

Neither of these two authorizations may be removed. The authorization responsible for the creation of a catalog is granted the special option of granting itself (when it is the only grantee specified) any permission without requiring the corresponding "with grant option". This facility is intended to protect against accidental loss of permissions, which could not subsequently be restored.

If you wish to access TextServer database facilities from the Web you must create an authorization for IUSR_<machine>@<machine>. If this authorization is not created TextServer will report that IIS is an unknown user, and refuse service to IIS. You must also ensure that the appropriate TextServer catalogs are readable by this user.

To create additional authorizations in a given catalog the CREATE USER permission is required on that catalog. The necessary command is:

CREATE AUTHORIZATION <authorization>  
 [PASSWORD=passwd] 
 [[DEFAULT] SCHEMA=schema]

Since <authorization> is an identifier it must be double quoted if it contains characters not normally associated with an identifier. The default authorization used to connect a requestor to the system is: username@machinename

Any authorization may be revoked by the authorization that created the authorization or by a superuser. The syntax to remove an authorization is:

DROP AUTHORIZATION authorization {RESTRICT|CASCADE}

Attempting to remove an authorization which has created schemas, tables, granted access to tables, created subordinate authorizations or granted permissions to authorizations will fail if RESTRICT is specified. If CASCADE is specified all schemas created by the dropped authorization will be removed as will any tables/views contained within them. Subordinate authorizations created by the dropped authorization will themselves be recursively dropped, and permissions granted by the dropped authorization will be recursively revoked. This behaviour will also occur if someone (granted the superuser permission) drops either the PUBLIC authorization or the catalogs creator authorization. However the authorizations themselves will not be removed.

User Settings

Upon initially connecting to the system you have neither a default catalog nor a default schema. To avoid the need to constantly specify these and to circumvent problems with commands such as CREATE AUTHORIZATION which require that a default catalog exists, it is recommended that a default catalog, and schema be specified as follows:

SET CATALOG 'catalogname'
SET SCHEMA 'schemaname' [COMMITTED]

If the schemaname is prefixed by a catalogname the default catalog is set as indicated, before setting the specified schema. If the change in the default schemaname is committed, this default schema becomes the initial default schema for this user, whenever accessing the specified default catalog. The session authorization may also be changed at any point by issuing the command:

SET SESSION AUTHORIZATION 'value' [PASSWORD identifier]

The password to be used by default in future can also be set by issuing the command:

SET PASSWORD 'password' [COMMITTED]

If the optional keyword COMMITTED is specified, these changes are committed to disk, and will remain in effect next time you connect to the current authorization.

The default machine timezone can be set by issuing the command:

SET ZONE [ date-time interval]

If no date-time interval is specified this defaults to the machine timezone. Note that Toronto has a time zone of typically -5:00 hours GMT, since Toronto is 5 hours behind GMT.

SET COUNT value

This command sets the maximum number of marks that may be stored in a text. Attempts to store more than this number of marks in a text will cause errors to be reported cleanly. This option exists to avoid an obvious case where a command might use excessive memory. Changes to this value will be denied once the OLE/DB datasource property QSMPROP_MAXCHANGABLE has been set false.

SET CHAR_LENGTH value

This command sets the maximum length of a text string that may be recovered from a text. Attempts to read more than this number of characters in a text will cause errors to be reported cleanly. This option exists to avoid an obvious case where a command might use excessive memory. Changes to this value will be denied once the OLE/DB datasource property QSMPROP_MAXCHANGABLE has been set false.

SET TRUE 'xpath verbose'

This causes cautionary messages to appear when the XPATH expressions differ radically from knowledge about the text's such expressions are applied against. By default this option is off.

SET FALSE 'xpath verbose'

Disables the specified option.

Creating references to external files

CREATE EXTERNAL ... TABLE ... IN "<filename>"

This requests that an internal table be constructed from the information contained in the external file. The name of this external file should be an absolute pathname. TextServer path macros (stored in the registary) may be employed in this file name to allow flexibility.

At present such tables may contain only one column. If this column has type text, the filename is presumed to refer to a text index. If it is of type CHAR (or one of the related variant types) it is presumed to refer to an ascii file, whose content is to become the value of this field. These are currently the only allowed column types. The keyword EXTERNAL distinguishes such tables from conventional SQL2 tables.

Relationships between internal tables and external files may be changed by issuing the command:

ALTER TABLE tablename IN "<filename>"