System Catalog

In Texis, information about the database, such as the names of tables, columns, and indexes, is maintained within a set of tables referred to as the system catalog. Texis automatically maintains these tables in the system catalog in response to commands issued by users. For example, the catalog tables are updated automatically when a new table is defined using the CREATE TABLE command.

Database administrators and end users can access data in the system catalog just as they access data in other Texis tables by using the SELECT statement. This enables a user to inquire about data in the database and serves as a useful reference tool when developing queries.

Table here lists the tables that make up the system catalog for Texis.

Table Name	Description
`SYSTABLES`	Contains one row per table in the database.
`SYSCOLUMNS`	Contains one row per column for each database table.
`SYSINDEX`	Contains one row per canonical index in the database.
`SYSPERMS`	Holds the permissions information.
`SYSUSERS`	Contains information about users known to the system.
`SYSTRIG`	Contains one row per trigger defined to the system.
`SYSMETAINDEX`	Contains one row per Metamorph index in the database.

Table: Overview of System Catalog Tables in Texis

One commonly referenced table, SYSTABLES, contains a row for each table that has been defined. For each table, the name of the table, authorized ID of the user who created the table, type of table, and so on is maintained. When users access SYSTABLES, they see data pertaining to tables that they can access.

Texis's system catalog table, "SYSTABLES" has these columns, defined with the following data types:

NAME     -  CHAR(20)
     TYPE     -  CHAR
     WHAT     -  CHAR(255)
     FC       -  BYTE
     CREATOR  -  CHAR(20)
     REMARK   -  CHAR(80)

Each field is fixed length rather than variable length, so the designated size limits do apply.

NAME

is the name of the table. Each of the tables comprising the system catalog are entered here, as well as each of the other database relations existing as "normal" tables.

TYPE

indicates the type of table.

S: indicates a System table, and is Texis owned. `S' is assigned to all tables where the user who created the table is "texis".
T: indicates a normal Table.
V: indicates a normal View.
B: indicates a Btree table. A Btree is a special type of table that can be created through the API only, that contains all the data in the index. It is of limited special purpose use. It is somewhat quicker and more space efficient if you have a few, small fields, and if you will never need to index on the fields in a different order. Use of the API is covered in Part V, Chapter here.
t: indicates a temporary table. These are not directly accessible, and exist only briefly. They are used when a temporary table is needed by the system - for example when compacting a table - and may have the same name as another, normal table. They are automatically removed when no longer needed.
D: indicates a Deleted table. On some operating systems (such as Windows), when a table is DROPped, it cannot be removed immediately and must continue to exist - as a deleted table - for a short time. Deleted tables are not directly accessible, and are automatically removed as soon as possible.

WHAT

is the filename designating where the table actually exists on the system.

FC

stands for Field Count. It shows how many columns have been defined for each table entered.

CREATOR

is a User ID and shows who created the table.

REMARK

is reserved for any explanatory comments regarding the table.

Example: Provide a list of all tables in the database with this statement:

SELECT  NAME, TYPE
     FROM    SYSTABLES ;

The result will be a listing of the available tables, as follows:

NAME             TYPE

  SYSCOLUMNS       S
  SYSINDEX         S
  SYSMETAINDEX     S
  SYSTABLES        S
  CODES            T
  DEPARTMENT       T
  EMPLOYEE         T
  NEWS             T
  REPORT           T
  RESUME           T

In the above example, the first four tables: SYSCOLUMNS, SYSINDEX, SYSMETAINDEX, and SYSTABLES, comprise the system catalog and are marked as type S, for "system".

The next six in the list are the tables which have been used for examples throughout this manual: CODES, DEPARTMENT, EMPLOYEE, NEWS, REPORT, and RESUME. These are marked as type T, for "table".

The table SYSCOLUMNS contains a row for every column of every table in the database. For each column, its name, name of the table to which it belongs, data type, length, position in the table, and whether NULL is permitted in the columns is maintained information. Users querying SYSCOLUMNS can retrieve data on columns in tables to which they have access.

Texis's system catalog table "SYSCOLUMNS" has these columns, defined with the following data types:

NAME     -  CHAR(20)
     TBNAME   -  CHAR(20)
     TYPE     -  CHAR(15)
     INDEX    -  CHAR(20)
     NONNULL  -  BYTE
     REMARK   -  CHAR(80)

NAME: is the column name itself.
TBNAME: is the table the column is in.
TYPE: is the data type assigned to the column, defined as a string. TYPE might contain "char", "varchar", "integer", "indirect", and so on.
INDEX: is the name of an index created on this column. (This field is reserved for use in future versions of Texis. As it is not currently being used, one should not be surprised if the INDEX field is empty.)
NONNULL: indicates whether NULL fields should be disallowed. (This field is reserved for use in future versions of Texis. As it is not currently being used, one should not be surprised if the INDEX field is empty.)
REMARK: is reserved for any user comment about the column.

Example: A user wants to obtain data about employees in the R&D Department, but doesn't know any of the column names in the EMPLOYEE table. Assume that the user does know there is a table named EMPLOYEE.

This statement:

SELECT  NAME
     FROM    SYSCOLUMNS
     WHERE   TBNAME = 'EMPLOYEE' ;

would result in the following:

NAME

  EID
  ENAME
  DEPT
  RANK
  BENEFITS
  SALARY

In this way one can find out what kind of data is stored, so as to better formulate queries which will reveal what you actually want to know.

Texis has two other system catalog tables called "SYSINDEX" and "SYSMETAINDEX". Texis's system catalog table "SYSINDEX" has these columns, defined with the following data types:

NAME     -  CHAR(20)
     TBNAME   -  CHAR(20)
     FNAME    -  CHAR(20)
     ORDER    -  CHAR
     TYPE     -  BYTE
     UNIQUE   -  BYTE
     FIELDS   -  CHAR(20)

NAME: is the name of the index.
TBNAME: is the table the index is on.
FNAME: is the file name of the index.
ORDER: indicates sort order. `A' indicates ascending; `D' indicates descending. This field is not currently used, but is planned for future releases.
TYPE: indicates the type of index, either Btree or Metamorph.
UNIQUE: indicates whether the values entered should be unique. This field is not currently used, but is planned for future releases.
FIELDS: indicates which field is indexed.

"SYSMETAINDEX" controls a demon that checks Metamorph indexes, those indexes used on text oriented columns. The demon waits a certain number of seconds between checks, and has a threshold in bytes at which size the update process is required to run.

Texis's system catalog table "SYSMETAINDEX" has these columns, defined with the following data types:

NAME     -  CHAR(20)
     WAIT     -  INTEGER
     THRESH   -  INTEGER

NAME: is the name of the Metamorph index.
WAIT: indicates how long to wait in seconds between index checks.
THRESH: is a number of bytes which have changed. This is the threshold required to re-index.

The system catalog tables are a good place to start when initially becoming familiar with what a database has to offer.