D3 SQL catalog

The D3 SQL Catalog is the Tables file located in the SQL account.

Using the D3 SQL Catalog

The table below lists and describes the attribute-defining items for the Tables file. Direct modification of the Catalog is not supported. The structure of the Tables file is proprietary and its exact structure cannot be guaranteed on future releases.

To display the complete table descriptor for an SQL table, log to the SQL account and execute this AQL statement:

SELECT TABLES WITH table_name AND WITH account_name

A select list is created, which can be viewed using this statement:

list tables

SQL-DISPLAY-TABLE provides a partial listing of the table descriptor for a SQL table.

Attr #	Attribute Name	Description
0	table_id	Unique number for referencing the table. The item-id of the table in the Tables file.
1	table_name	SQL table name, which is often, but not always, the same as the D3 file name.
2	account	Account where the D3 file exists.
3	pick_file	Name of the D3 file corresponding to the table. For example, orders or orders,department1.
4	table_type	Integer indicating the table type. This integer value translates to the Base_types file. Translated values are: ALIAS (not used), DELETED (not used), TABLE, SYSTEM_TABLE, SYNONYM, VIEW (not implemented).
5	macro	List of columns referenced by the , as in SELECT FROM table_name.
6	column_name	List of column names.
7	retrieve_type	Integer indicating the retrieval type of the column. The values can be translated using the retrieve_types file to the tags below: IID (0) – a column mapped to the D3 item ID, CONTROL (1) – a standard column which is used as the controlling element in a nested set. This corresponds to the controlling element in a D3 controlling-dependent set, DIRECT (2) – a standard column, SYNONYM (3) – a reference to another column.
8	ordinal_position	Attribute number of each column in standard D3 files.
9	reserved	Reserved.
10	column_default	Default value for the column.
11	is_nullable	1 = null strings display as NULL. 0 = null strings display as empty strings.
12	data_type	Integer translating to the data_types file, which indicated the SQL data type of the column: CHAR (3), DATE (4), NUMERIC or DECIMAL (5), INTEGER (8), SMALLINT (11), TIMESTAMP (12), TIME (13), VARCHAR (15).
13	character_maximum_ length or numeric_precision	Precision of the numeric column or the length of the string column.
14	numeric_scale	Scale of a numeric column.
15	pick_scale	Multiplier used to store decimal numbers as integers in the actual database file. This is used for the mrXX conversions when they are detected by the SQL-CREATE-TABLE utility.
16	reserved	Reserved.
17	correlative	D3 correlative code. This is a translation code that is automatically applied at retrieval time only. The correlative is useful for parsing non-relational data into a format that can be utilized by SQL. Translates and/or BASIC calls may be performed using these functions. Note, however, that the ACCESS() functions in BASIC are not functional when those programs are called from SQL correlatives.
18	reserved	Reserved.
19	column_remarks	User information about the column.
20	reserved	Reserved.
	constraint_type	Integer translating to the constraint_types file which indicates the type of constraint: PRIMARY (0), FOREIGN (1), UNIQUE (2), NESTED (3), NOT NULL (4).
	constraint_column_name	Columns which apply to the current constraint.
	referenced_table	NESTED and FOREIGN (key) constraints.
	referenced_columns	NESTED and FOREIGN (key) constraints. A comma-separated list of referenced columns.
	reserved	Reserved.
	reserved	Reserved.
	reserved	Reserved.
	Dict_id	Dictionary item names.
	table_remarks	User remarks about the file.
	status_byte	Reserved for system tables.
	Column_deleted	List of column numbers of columns that were deleted.
	index_name	Name identifying an index. Index names are either program generated or assigned by the user through the SQL Create Index statement.
33	index_type	Integer indicating the index type. This integer translates to the Index_types file. Translated values are HASHED (for indexes placed on primary key columns) and OTHER (for all other columns).
34	non_unique	1 = index is not unique. The index can have duplicates. 0 = index is unique. The index values must be unique. Only indexes that are program generated to support primary and unique keys are unique.
35	indexed_columns	List of the indexed columns delimited by sub-value marks.
36	seq_in_index	Column sequence numbers in the index. Indexed columns are numbered starting at one.
37	constraint_name	Name identifying a constraint. All constraint names are program generated. Each constraint has an index associated with it that is identified by the same name except for not null constraints (an index is not created for the not null constraint). Refer to attribute 32 (Index_name) for information on the format of this attribute.
38	index_acorr	A-correlative used to create the index.
39	full path	Path to the D3 file in the form of account, file, datalevel. If the Q-pointer is used in SQL-CREATE-TABLE, the location of the D3 file is determined.