1.2 Standard tables

First we'll start with a quick tour of the system tables, and what information you can glean from them, and which you might want to modify. The standard sytem tables are SYSTABLES, SYSCOLUMNS, SYSINDEX, SYSMETAINDEX, SYSTRIGGER, SYSUSER, SYSPERMS, and SYSSTATISTICS. These tables store meta-data about the database. With the exception of SYSMETAINDEX you should not edit these tables without a very good reason, and understanding the risks involved.

SYSMETAINDEX contains information that controls chkind. The fields in SYSMETAINDEX are NAME, WAIT and THRESH. NAME is the name of the index. WAIT controls the delay between checks of the index. The index will be checked approximately every WAIT seconds as long as chkind runs. It may be longer if other index updates are occurring. THRESH controls the number of bytes of changed data to require an index update. If there is no entry in SYSMETAINDEX for an index the default is to wait an hour between checks, and look for a million bytes of changed data. Entries in SYSMETAINDEX are not removed if the index is dropped, so you can drop and recreate an index while maintaining settings.

SYSTABLES contains information about the tables in the database, including filename, creator, and type. If the filename is not a fully qualified path then it is relative to the database directory, and does not contain the extension. Moving a table might be a valid reason for updating SYSTABLES.

SYSCOLUMNS contains information about the fields in the tables. It is not used during normal Texis operation, and is provided as a reference. There should be no reason to edit this table.

SYSINDEX contains information about the indices in the database, including table, fields, type and filename. The filename follows similar rules to SYSTABLES, as do the suggestions for when the table might be updated. The type field contains a single character identifying the type of index. If you have a really old database you might see 2 hex digits instead (if so you might consider upgrading the database, as there may be other new features you are missing out on). The known types are:

TYPE	DESCRIPTION
F,f	Metamorph Inverted Index (being created)
M,m	Metamorph Index (being created)
B	Regular (BTREE) index
3,C	Old style metamorph index (being created)
V	Inverted index
T	Temporary index
D	Deleted index

SYSTRIGGER holds information about triggers. Its information includes the name of the trigger, the table, when the trigger is invoked, and the command to invoke. This table should never need to be hand-edited.

SYSUSER contains information about the Texis users. The two default users, PUBLIC and _SYSTEM are automatically entered into the table when the database is created. Their passwords are empty by default. You should not need to hand-edit this file. If you have permission to copy a default SYSUSER.tbl file into the database then you will have effectively reset all the user information.

SYSPERMS works in conjunction with SYSUSER and SYSTABLES, and assigns permissions on tables to users. The permissions are bitmasks of permissions. The creator of the table gets full permissions by default. Any permission that PUBLIC has is usable by any user. The _SYSTEM user ignores permissions, and can do anything.

SYSSTATISTICS keeps statistics about tables, such as the number of rows and amount of data in them. This information is intended for the SQL engine to use to help it make better choices when choosing indexes.

Back: Designing the database

Next: Disk layout of the table and indexes