0. Table of Contents
1. Introduction
2. The Metadata Tables
Summary
Warnings
Tables
Table Relationships
3. The Table Schemas
_Db
_File
_Field
_Index
_Index-Field
_File-Trig
_Field-Trig
_Sequence
_User
_View
_View-Col
_View-Ref
4. The Metadata
Indexes
5. Reading the Metadata
1. Introduction
The term metadata means "data about
data". Every Progress database contains metadata -
descriptions of the data stored there, including the
metadata itself. Just like all data in the database, these
descriptions are stored in tables. The information held in
the metadata is used for many different purposes. Some of
them are:
- When you create a table, new metadata are added to
describe the table's logical schema.
- The 4GL compiler uses metadata to know what tables
and fields exist and what they look like. So when you
write "for each customer: display
customer.", the compiler knows what the word
"customer" means and what data values the display should
include. The metatdata allow the "name space" of the 4GL
to include not only program variables, functions, and
procedures, but also the tables in the database.
- The 4GL and SQL query optimizers use information
about indexes and keys to make decisions about how to
access the data in the most efficient way.
- Various utilities use the information to know things
like how to format data when it is dumped and how read
previously dumped data.
- Database repair utilities, such is the index rebuild
tool and index fix tool use the metadata to know what
should be there.
This article contains a fairly complete description of
the metadata tables for Progress Version 8 and explains how
they work together. It also shows how you can use the 4GL to
examine the information in these tables. But heed this
warning: the metadata can and do change from one
major release to another. Always use the Data Dictionary
tools to modify metadata.
2. The Metadata
Tables
Summary
In version 8, the metadata are composed of 12 tables that
together define all objects stored within a database. This
includes tables, indexes, views, and sequence generators.
The tables fall into the following general categories:
- Table definitions
- Index definitions
- View definitions
- Other information
These tables can be queried and manipulated with the 4GL
just like any other tables. Some of the information they
contain is:
- Table and index names
- Column names, data types, and default values
- Default formats for column and report labels and for
displaying data values
- Validation rules for column values
- Userid's of the database users
- etcetera
The Progress Data Dictionary is the tool that you use to
define and change database objects. It operates by
manipulating the contents of the metadata tables. Use of the
Data Dictionary is described in Chapter 5 and 6 of the
Progress Basic Development Tools manual. You should
not update the metadata tables directly. If you do, and you
do it incorrectly, you can permanently damage your data.
This article does not contain all the information you need
to do it correctly.
An empty database contains only metadata tables. To
create a new database you make a copy of an empty database
using the prodb or procopy commands and
then add your own tables to it with the Data Dictionary.
A void multi-volume structure created by the
"prostrct create" command contains only empty
space. It does not contain any of these tables. That is what
defines it as void. After creating a void structure, you
copy the empty database (or another database) into it.
Warnings
Different releases of Progress have different metadata.
Within a major release family (i.e. version 8) the metadata
tables are identical. Across major
release families (6, 7, 8, 9) the metadata tables are
generally different. The differences can be enormous.
Upward or downward
compatibility of any 4GL applications that use these tables
MUST NOT BE EXPECTED. This point is strongly
emphasized: Upward or downward
compatibility of any 4GL applications that use these tables
MUST NOT BE EXPECTED. Do not call Progress to complain if
you ignore this warning.
This article is correct for version 8 (8.0A through
8.3A).
Tables
The table below lists the 12 tables that are part of the
metadata, with a brief description of each.
|
Table Name
|
Description
|
|
_Db
|
"Owns" the metadata and holds some additional
information about the database.
|
|
_File
|
Defines each table including the metadata tables
themselves
|
|
_Field
|
Defines each field (column) of each table
|
|
_Index
|
Defines each index
|
|
_Index-Field
|
Defines each index component of each field
|
|
_File-Trig
|
Describes each table schema trigger. The trigger
logic is a 4GL program.
|
|
_Field-Trig
|
Describes each field schema trigger. The trigger
logic is a 4GL program.
|
|
_Sequence
|
Defines each sequence generator
|
|
_User
|
Holds information about each registered user of
the database.
|
|
_View
|
Defines each SQL-89 View
|
|
_View-Col
|
Holds information about each column in the
result-set of each SQL-89 View
|
|
_View-Ref
|
Holds information about each table referred to
by each SQL-89 View
|
Table Relationships
There is a hierachical structure to the
interrelationships among the metadata tables. Since "a
picture is worth a thousand words":
Metadata Table Hierarchy
Everything inherits from the "_Db" table,
which is a bit like the "head of the family". An _Db record
describes the local database. The other tables describe the
various things that are in the database. Note that there can
be more than one row in the _Db table. One always describes
the local database. There may be others for foreign
databases if the database is a schema holder for a
DataServer. There may also be autoconnect records giving the
locations of other Progress databases.
In the preceding diagram, the arrows indicate a
parent-child or "has-a" relationship, with the parent on the
left and the child on the right. The connections are made
using record identifiers in the following manner: the child
has a field named <_parent>-recidthat contains the
record identifier of the parent. Using record identifiers
makes it easy and efficient to follow the relationships.
Also, if a table is renamed, it is unnecessary to update any
of its fields or indexes.
Let's say you have a database with 1,000 tables and
15,000 fields. The "customer" table has 10 fields. So one of
1,000 rows in the _File table defines the "customer" table.
Of 15,000 rows in the _Field table, 10 of them define the 10
fields of the customer table and each contains the record
identifier of the customer _File row.
In the last section of this article there are several
example 4GL programs that show how to follow the
relationships. Try them.
3. The Table Schemata
The 12 tables of the metadata are described
in the following sections. But first, a few general comments
about them.
- All the field (column) names begin with an underscore
character ( _ ). This is to help distinguish them from
the tables you define. Names that begin with an
underscore are reserved for use by Progress.
- The metadata tables are not included in Data
Dictionary reports. By default the Dictionary does not
include tables that are marked hidden.
- These 12 tables exist in every database. The empty
database contains them, and whenever you create a new
database from it, it will also contain these tables.
- A child's parent record identifier field is named
with the convention <parent>-recid. For example,
"_File-recid" would contain the record identifier of the
parent _File record.
- Field names that begins with "_For" are used by the
various DataServers to hold additional information about
the foreign database's schema. They are not descibed
here.
- Field names that end in "-misc1", "-misc2", "-res1",
"-res2" and the like are either unused spares or used by
the various DataServers.
- All the names are supposed to begin with an
underscore followed by a capital letter. A few don't have
the capital letter because whoever added them was sloppy.
- A number of fields are described as "Used by
DataServers". This article does not cover that subject.
The _Db Table
There is at least one _Db record in every
Progress database. It is the "root" of the metadata and all
the other metadata tables are "attached" to it. The
attachment is made by storing the _Db record's record
identifier in the other tables and by using it as a
component in some of the indexes on the tables.
|
Column Name
|
Data Type
|
Description
|
|
_Db-addr
|
character
|
If not local, the physical database name
(autoconnect) or the data source name (dataservers)
|
|
_Db-coll-name
|
character
|
Name of the character set in which character
data stored in this database is represented
|
|
_Db-collate
|
raw
|
Character data collation tables for this
database
|
|
_Db-comm
|
character
|
Communication parameters for remote connections
|
|
_Db-local
|
logical
|
True if this _Db record is for the local
database (the database that contains this _Db
record)
|
|
_Db-misc1
|
integer
|
Used by DataServers
|
|
_Db-misc2
|
character
|
Used by DataServers
|
|
_Db-name
|
character
|
If not the local database, then the name of a
foreign or remote database
|
|
_Db-res1
|
integer
|
Spare
|
|
_Db-res2
|
character
|
Spare
|
|
_Db-revision
|
integer
|
Time-stamp of the schema. Updated whenever
changes to the table or index definitions are made.
|
|
_Db-slave
|
logical
|
True if this is a schema holder database
|
|
_Db-type
|
character
|
Name of the database type (e.g. "PROGRESS",
"ORACLE")
|
|
_Db-xl-name
|
character
|
Character translation table name
|
|
_Db-xlate
|
raw
|
Character translation tables
|
The _Field Table
There is one row in the _Field table for each
field (column) of a table. For example, the customer table
in the demo database has 18 fields so there are 18 _Field
rows for the customer table.
|
Column Name
|
Data Type
|
Description
|
|
_Can-Read
|
character
|
Permission string that determines who can read
this field's value
|
|
_Can-Write
|
character
|
Permission string that determines who can write
(update) this field's value
|
|
_Col-label
|
character
|
Default column label string. "!" characters
indicate line breaks.
|
|
_Col-label-SA
|
character
|
Column label string attributes used for display
and translation
|
|
_Data-Type
|
character
|
Data type name (character, date, decimal,
integer, logical, raw, recid)
|
|
_Decimals
|
integer
|
Number of digits to the right of the decimal
point if the data type is decimal
|
|
_Desc
|
character
|
Arbitrary text description string displayed by
the Data Dictionary
|
|
_dtype
|
integer
|
Data type expressed as an integer
|
|
_Extent
|
integer
|
Number of elements if this field is an array
|
|
_Field-Name
|
character
|
Name of the field (1 to 32 characters)
|
|
_field-rpos
|
integer
|
Relative position of the field within a row. The
first field is position 2. Assigned when fields are
created.
|
|
_File-recid
|
recid
|
Record identifier of the _File record that
defines the table of which this field is a part
|
|
_Fld-case
|
logical
|
Case sensitivity if this is a character field
|
|
_Fld-misc1
|
integer
|
Used by DataServers
|
|
_Fld-misc2
|
character
|
Used by DataServers
|
|
_Fld-res1
|
integer
|
Used by DataServers
|
|
_Fld-res2
|
character
|
Spare
|
|
_Fld-stdtype
|
integer
|
Used by DataServers
|
|
_Fld-stlen
|
integer
|
Used by DataServers
|
|
_Fld-stoff
|
integer
|
Used by DataServers
|
|
_For-Allocated
|
integer
|
Used by DataServers
|
|
_For-Id
|
integer
|
Used by DataServers
|
|
_For-Itype
|
integer
|
Used by DataServers
|
|
_For-Maxsize
|
integer
|
Used by DataServers
|
|
_For-Name
|
character
|
Used by DataServers
|
|
_For-Primary
|
integer
|
Used by DataServers
|
|
_For-Retrieve
|
logical
|
Used by DataServers
|
|
_For-Scale
|
integer
|
Used by DataServers
|
|
_For-Separator
|
character
|
Used by DataServers
|
|
_For-Spacing
|
integer
|
Used by DataServers
|
|
_For-Type
|
character
|
Used by DataServers
|
|
_For-Xpos
|
integer
|
Used by DataServers
|
|
_Format
|
character
|
Default display format string. Unrelated
to how the field value is stored in Progress
databases.
|
|
_Format-SA
|
character
|
Default display format string attributes used
for display and translation.
|
|
_Help
|
character
|
Help text string
|
|
_Help-SA
|
character
|
Help string attributes used for display and
translation
|
|
_Initial
|
character
|
The value that this field is set to when a new
row is created.
|
|
_Initial-SA
|
character
|
Initial value string attributes used for display
and translation
|
|
_Label
|
character
|
Default side label string for display. If set to
the unknown value, the field name is used.
|
|
_Label-SA
|
character
|
Side label string attributes used for display
and translation
|
|
_Mandatory
|
logical
|
True if this field may not contain the unknown
value. Another value must be assigned when a new
row is created.
|
|
_Order
|
integer
|
An integer value that determines default field
display order
|
|
_sys-field
|
logical
|
True if the field should not be included by
default in displays.
|
|
_Valexp
|
character
|
A validation expression string (a logical 4GL
expression) that is evaluated, before the row is
stored, if the field's value was changed
|
|
_Valmsg
|
character
|
Validation message string, a string that is
inserted into an error message displayed if the
validation fails.
|
|
_Valmsg-SA
|
character
|
Validation message string attributes used for
display and translation
|
|
_View-As
|
character
|
Default 4GL screen widget type used for display
|
The _Field-Trig Table
There is one row in the _Field-Trig table for
each schema trigger on a field of a table.
|
Column Name
|
Data Type
|
Description
|
|
_Event
|
character
|
Name of a triggering event
|
|
_Field-Recid
|
recid
|
Record identifier of the _Field record that
defines the field this trigger is on
|
|
_Field-Rpos
|
integer
|
Triggering field's relative record position
|
|
_File-Recid
|
recid
|
The record identifier of the _File record that
defines the table of which the field is a part
|
|
_Override
|
logical
|
True if the schema trigger can be overridden at
execution time by a session trigger
|
|
_Proc-Name
|
character
|
The name of the 4GL procedure that is executed
when the triggering event occurs
|
|
_Trig-Crc
|
integer
|
A checksum of the contents of trigger procedure
r-code, used to ensure that the trigger program
r-code has not been replaced to subvert the
trigger's logic
|
The _File Table
The _File table contains one row for each
table (file) in the database. For example, the customer
table in the demo database has one _File row that contains
"Customer" in the _File-name field.
|
Column Name
|
Data Type
|
Description
|
|
_Cache
|
raw
|
Contains information used to construct the
in-memory schema cache for this table and also a
list of the events for which triggers are defined.
|
|
_Can-Create
|
character
|
Permission string that determines who can create
rows in this table
|
|
_Can-Delete
|
character
|
Permission string that determines who can delete
rows from this table.
|
|
_Can-Dump
|
character
|
Permission string that determines who can dump
the contents of this table
|
|
_Can-Load
|
character
|
Permission string that determines who can load
data into this table
|
|
_Can-Read
|
character
|
Permission string that determines who can read
rows from this table
|
|
_Can-Write
|
character
|
Permission string that determines who can write
(update) rows in this table
|
|
_CRC
|
integer
|
A checksum of the table definition, used to
establish that the table definition embodied in a
compiled 4GL application's r-code matches the table
definition stored in the database
|
|
_DB-lang
|
integer
|
Distinguishes tables created with the 4GL from
tables created with SQL
|
|
_Db-recid
|
recid
|
Record identifier of the _Db record that defines
the database of which this table is part
|
|
_Desc
|
character
|
Arbitrary table description string displayed by
the Data Dictionary
|
|
_dft-pk
|
logical
|
True if this table has a default primary key
|
|
_Dump-name
|
character
|
Default name of a file that data are written to
when the table is dumped
|
|
_Fil-misc1
|
integer
|
Used by DataServers
|
|
_Fil-misc2
|
character
|
Used by DataServers
|
|
_Fil-res1
|
integer
|
Used by DataServers
|
|
_Fil-res2
|
character
|
Used by DataServers
|
|
_File-Label
|
character
|
Label string for display
|
|
_File-Label-SA
|
character
|
Label string attributes
|
|
_File-Name
|
character
|
Name of the table (1 to 32 characters)
|
|
_File-Number
|
integer
|
A number that identifies the table. Assigned by
the database when the table is created.
|
|
_For-Cnt1
|
integer
|
Used by DataServers
|
|
_For-Cnt2
|
integer
|
Used by DataServers
|
|
_For-Flag
|
integer
|
Used by DataServers
|
|
_For-Format
|
character
|
Used by DataServers
|
|
_For-Id
|
integer
|
Used by DataServers
|
|
_For-Info
|
character
|
Used by DataServers
|
|
_For-Name
|
character
|
Used by DataServers
|
|
_For-Number
|
integer
|
Used by DataServers
|
|
_For-Owner
|
character
|
Used by DataServers
|
|
_For-Size
|
integer
|
Used by DataServers
|
|
_For-Type
|
character
|
Used by DataServers
|
|
_Frozen
|
logical
|
True if the table definition is frozen and
cannot not be changed.
|
|
_Hidden
|
logical
|
True if the table should not be included by
default in Data Dictionary displays and reports.
|
|
_Last-change
|
integer
|
Used by DataServers
|
|
_numfld
|
integer
|
Number of columns in the table
|
|
_numkcomp
|
integer
|
Total number of key components in all the
indexes on the table
|
|
_numkey
|
integer
|
Number of indexes on the table
|
|
_numkfld
|
integer
|
Number of key fields
|
|
_Prime-Index
|
recid
|
Record identifier of the _Index table row that
defines this table's primary index
|
|
_Template
|
recid
|
Record identifier of a "template" row for this
table, used to create the inital row values when a
new row is created.
|
|
_Valexp
|
character
|
A validation expression string containing a
logical 4GL expression that is evaluated before a
row is deleted from this table.
|
|
_Valmsg
|
character
|
Validation message string, a string that is
inserted into an error message displayed when the
validation fails.
|
|
_Valmsg-SA
|
character
|
Validation message string attributes used for
display and translation
|
The _File-trig Table
The _File-Trig table contains one row for
each schema trigger on a table.
|
Column Name
|
Data Type
|
Description
|
|
_Event
|
character
|
Name of the triggering event
|
|
_File-Recid
|
recid
|
Record identifier of the _File record that
defines the table this trigger is on
|
|
_Override
|
logical
|
True if the schema trigger can be overridden by
a session trigger at run time
|
|
_Proc-Name
|
character
|
The name of the 4GL procedure that is executed
when the triggering event occurs
|
|
_Trig-Crc
|
integer
|
A checksum of the contents of trigger procedure
r-code, used to ensure that the trigger program
r-code has not been replaced to subvert the
trigger's logic
|
The _Index Table
The _Index table contains one row for each
index in the database.
|
Column Name
|
Data Type
|
Description
|
|
_Active
|
logical
|
True if this index is active
|
|
_Desc
|
character
|
Arbitrary description string displayed by the
Data Dictionary
|
|
_File-recid
|
recid
|
Record identifier of the _File record that
defines the table this index is on
|
|
_For-Name
|
character
|
Used by DataServers
|
|
_For-Type
|
character
|
Used by DataServers
|
|
_I-misc1
|
integer
|
Used by DataServers
|
|
_I-misc2
|
character
|
Used by DataServers
|
|
_I-res1
|
integer
|
Used by DataServers
|
|
_I-res2
|
character
|
spare
|
|
_idx-num
|
integer
|
A number that identifies the index. Assigned by
the database when the index is created.
|
|
_Index-Name
|
character
|
Index name (1 to 32 characters)
|
|
_num-comp
|
integer
|
Number of component fields in a key
|
|
_Unique
|
logical
|
True if entries in this index must be unique
|
|
_Wordidx
|
integer
|
Indicates this is an ordinary index or a
word-index on a character field
|
The _Index-Field Table
The _Index-Field table contains one row for
each key component of an index. A three component index
would have one _Index row and three _Index-Field rows.
|
Column Name
|
Data Type
|
Description
|
|
_Abbreviate
|
logical
|
True if queries using this index are performed
by a partial match on a character field. The
character field must be the last (or only)
component of the key. Abbreviated indexes are a
deprecated feature and their use is not
recommended. The setting of this field has no
effect on how index entries are stored.
|
|
_Ascending
|
logical
|
True if the field values go in increasing
numeric or collation order in the index
|
|
_Field-recid
|
recid
|
Record identifier of the _field record that
defines the key component field
|
|
_If-misc1
|
integer
|
spare
|
|
_If-misc2
|
character
|
spare
|
|
_If-res1
|
integer
|
spare
|
|
_If-res2
|
character
|
spare
|
|
_Index-recid
|
recid
|
The record identifier of the _index record that
defines the index of which this field is a
component
|
|
_Index-Seq
|
integer
|
Used by DataServers
|
|
_Unsorted
|
logical
|
Used by DataServers
|
The _Sequence Table
Each row in the _Sequence table defines one
sequence generator. Sequence generators are used to create
unique numbers without the locking overhead and contention
that occurs when numbers are generated by updating records.
The generation of a number from a sequence generator cannot
be undone.
|
Column Name
|
Data Type
|
Description
|
|
_Cycle-Ok
|
logical
|
True if the sequence value should be set to the
initial value when the next value becomes less than
the minimum or greater than the maximum
|
|
_Db-recid
|
recid
|
Record identifier of the _Db record that defines
the database of which this sequence is a part
|
|
_Seq-Incr
|
integer
|
Amount the value of the sequence should be
changed by when the next value is generated
|
|
_Seq-Init
|
integer
|
Initial or starting value of the sequence
|
|
_Seq-Max
|
integer
|
Maximum value of the sequence
|
|
_Seq-Min
|
integer
|
Minimum value of the sequence
|
|
_Seq-Misc
|
character
|
Used by DataServers
|
|
_Seq-Name
|
character
|
Name of the sequence (1 to 32 characters)
|
|
_Seq-Num
|
integer
|
A number that identifies this sequence. Assigned
by the database when the sequence is created.
|
The _User Table
The _User table contains one row for each
registered user of the database. The Userid can be specified
either when connecting to the database or by using the
SETUSERID function.
|
Column Name
|
Data Type
|
Description
|
|
_Password
|
character
|
Encrypted password string
|
|
_U-misc1
|
integer
|
Spare
|
|
_U-misc2
|
character
|
Spare
|
|
_User-Name
|
character
|
Full user name or other text string
|
|
_Userid
|
character
|
User id (1 to 32 characters)
|
The _View Table
The _View table contains one row for each
SQL-89 view.
|
Column Name
|
Data Type
|
Description
|
|
_Auth-Id
|
character
|
The user id of this view's owner (creator)
|
|
_Base-Tables
|
character
|
A list of the base tables referred to directly
or indirectly in the FROM clause of this view
definition.
|
|
_Can-Create
|
character
|
A list of user ids of users who can create rows
in this view
|
|
_Can-Delete
|
character
|
A list of user ids of users who can delete rows
from this view
|
|
_Can-Read
|
character
|
A list of user ids of users who can read data
through this view
|
|
_Can-Write
|
character
|
A list of user ids of users who can update rows
in this view.
|
|
_Desc
|
character
|
Arbitrary description string
|
|
_Group-By
|
character
|
The GROUP BY clause of this view definition if
one was specified. If a CHECK OPTION was specified,
it is included.
|
|
_Updatable
|
logical
|
True if this view can be updated.
|
|
_View-Def
|
character
|
The original text of this view definition as
specified in the CREATE VIEW statement.
|
|
_View-Name
|
character
|
The name of this view (1 to 32 characters)
|
|
_Where-Cls
|
character
|
The WHERE clause of this view definition. All
base table references are fully qualified.
|
The _View-Col Table
The _View-Col table contains one row for each
column in a SQL-89 view definition.
|
Column Name
|
Data Type
|
Description
|
|
_Auth-Id
|
character
|
User id of the owner of the view
|
|
_Base-Col
|
character
|
Fully qualified base table column name or an
expression
|
|
_Can-Create
|
character
|
Reserved for future use
|
|
_Can-Write
|
character
|
List of user ids of users who can update this
column's value.
|
|
_Col-Name
|
character
|
Name of the view column
|
|
_Vcol-Order
|
integer
|
Default left-to-right display order of the
column.
|
|
_View-Name
|
character
|
Name of the view that contains this column
|
The _View-Ref Table
The _View-Ref table contains one row for each
column that is referred to by an SQL-89 view. The column may
be a column in a base table or a column in another view.
|
Column Name
|
Data Type
|
Description
|
|
_Auth-Id
|
character
|
User id owner of the view
|
|
_Base-Col
|
character
|
Fully qualified base table column
|
|
_Ref-Table
|
character
|
Name of a referenced table or view that contains
the column
|
|
_View-Name
|
character
|
Name of the view that refers to the column
|
4. The Metadata Indexes
Just as with any table, the tables that
belong to the metadata can be accessed more efficiently if
they have indexes defined on them. In addition, unique
indexes are a convenient way to ensure that certain fields
or combinations of fields have unique values. The table
below lists the 21 indexes on the metadata tables and the
fields that compose the keys.
|
Table Name
|
Index Name
|
Key Components
|
Unique
|
|
_Db
|
_Database
|
_Db-Name
|
Yes
|
|
_File
|
_Db/File
|
_Db-Recid
_File-Name
|
Yes
|
|
_File-Name
|
_File-Name
|
No
|
|
_Dump-Name
|
_Db-Recid
_Dump-Name
|
Yes
|
|
_Field
|
_File/Field
|
_File-Recid
_Field-Name
|
Yes
|
|
_Field-Name
|
_Field-Name
_File-Recid
|
Yes
|
|
_Field-Position
|
_File-Recid
_Field-Order
|
Yes
|
|
_Index
|
_File/Index
|
_File-Recid
_Index-Name
|
Yes
|
|
_Index-Name
|
_Index-Name
|
No
|
|
_Index-Field
|
_Index/Number
|
_Index-Recid
_Index-Seq
|
Yes
|
|
_Field
|
_Field-Recid
|
No
|
|
_File-Trig
|
_File-Trig
|
_File-Recid
_Event
|
Yes
|
|
_Field-Trig
|
_Field-Trig-DBK
|
_Field-Recid
|
No
|
|
_Field-Trig-Rpos
|
_File-Recid
_Field-Rpos
_Event
|
Yes
|
|
_Sequence
|
_Seq-Name
|
_Db-Recid
_Seq-Name
|
Yes
|
|
_User
|
_Userid
|
_Userid
|
Yes
|
|
_View
|
_View-Name
|
_View-Name
|
Yes
|
|
_View-Col
|
_View-Col
|
_View-Name
_Col-Name
|
Yes
|
|
_Vcol-Position
|
_View-Name
_Vcol-Order
|
Yes
|
|
_View-Ref
|
_View-Ref
|
_Ref-Table
_Base-Col
|
No
|
|
_Vref-View
|
_View-Name
|
No
|
5. Reading the Metadata
This section shows how you can use the 4GL to
examine various parts of the metadata. In theory you could
use the 4GL to change them as well - after all, the Data
Dictionary tools are all written in the 4GL. But in
practice, you can't do it very easily because the
interrelationships among the various columns in the tables
are complicated and if you get something wrong, you can
permanently damage your data. This article does not contain
sufficient information to do it correctly. Also, these
tables can (and do) change from one major release to the
next. Upward or downward
compatibility of any 4GL applications that use these tables
MUST NOT BE EXPECTED.
So do not create or delete rows in the metadata tables
or update any of the values in them.
Hints
- Always assume there are multiple schemas within a
database. There is only one local schema such that
_Db.db-local = TRUE. Any other _Db rows that may
exist are for DataServer schema holders or autoconnect
databases (pointers to a database in another location).
- Metadata tables have negative file numbers. There are
other things that also have negative file numbers. For
example Fast Track tables have file numbers in the range
-7 to -29 inclusive. Fast Track is no longer sold, but
databases still contain the table definitions. Virtual
system tables also have negative file numbers.
- Parent-child relationships among the tables are
connected through record identifiers.
- Do not update the metadata tables.
An Example: List Tables and Fields
This 4gl program fragment generates a very
simple report that lists the names of all the fields
(columns) in all the tables in the database, including the
metadata tables. From this you should get the basic idea of
the techniques used to read the data. If you make a copy of
the demo database and run this program, you can see what
some of it looks like inside.
output to stuff1.txt.
/* Get the _db record for the database. There is only
one of these. */
find first _db where _db-local.
/* Now examine all the tables in that database.
Note that we only look at files with negative
file numbers. This excludes any user defined
tables that might be present. */
for each _file of _db:
/* print out the table class and name */
if (0 < _file-num) then put "Table ".
if (-6 <= _file-num) and (_file-num <= -1) then
put "Metadata Table ".
if (-29 <= _file-num) and (_file-num <= -7) then
put "Fast Track Table ".
if (-30 < _file-num) and (_file-num <= -16383) then
put "Metadata Table ".
if (-32767 <= _file-num) and (_file-num <= -16385) then
put "Virtual System Table ".
put _file-name skip.
/* examine all the fields that make up a row
in the table. This is an extremely short
examination to illustrate the point. All we
do is list the name of the field */
for each _field of _file:
put " Field: " _field-name skip.
end.
/* add a blank line after each table's fields */
put "" skip.
end.
output close.
Example: Fields in the Customer Table
This example lists all the fields of just the
customer table in the demo database.
output to stuff2.txt.
find _File where _file-name = "customer".
put "Table: " _file-name skip.
for each _field of _file:
/* Field names */
put " Field: " _field-name skip.
end.
output close.
Example: Triggers on Table Fields
This example shows how to get a list of the
table fields that have triggers defined on them. Note the
find statement used to look up field information from the
trigger definition.
output to stuff3.txt.
find first _db where _db-local.
/* Loop over all the tables */
for each _File of _db:
put "Table: " _file-name skip.
/* Loop over all field triggers for one table */
for each _Field-trig of _file:
/* need to get the field name */
find _Field of _Field-trig.
put " Field trigger on: " _field-name skip.
end.
/* blank line */
put "" skip.
end.
output close.
Example: Key Components of Indexes
This example shows how to get a list of all
the key components in each index on a table:
output to stuff4.txt.
find first _db where _db-local.
/* Loop over all tables */
for each _File of _db:
/* Loop over all indexes */
for each _Index of _File:
put "Index: " _Index-name skip.
/* loop over key components of an index */
for each _Index-field of _Index:
/* go get _field record */
find _Field of _Index-field.
put " Key column: " _Index-seq
" "_field-name skip.
end.
/* blank line */
put "" skip.
end.
end.
output close.
| 
