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.


Copyright © Thunderstone Software     Last updated: Apr 26 2017
Copyright © 2017 Thunderstone Software LLC. All rights reserved.