System Tables Structure
Chapter contents
This section discusses the structure of the system tables:
V1 Metadata System Tables
X$Attrib
The X$Attrib system table is associated with the file ATTRIB.DDF. X$Attrib contains information about the column attributes of each column in the database; there is an entry for each column attribute you define. The structure of X$Attrib is described in X$Attrib System Table Structure:
Table C-2 X$Attrib System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xa$Id
|
USMALLINT
|
2
|
not applicable
|
Corresponds to Xe$Id in X$Field.
|
|
Xa$Type
|
CHAR
|
1
|
No
|
C for character; D for default; H for heading; M for mask; O for column collation; R for range; or V for value.1
|
|
Xa$ASize
|
USMALLINT
|
2
|
not applicable
|
Length of text in Xa$Attrs.
|
|
Xa$Attrs
|
LONGVARCHAR
(NOTE)
|
<=2048
|
not applicable
|
Text that defines the column attribute.
|
|
1Attribute type C, H, M, R and V are legacy validation types valid only in a Pervasive.SQL 7 or Scalable SQL environment. Pervasive PSQL releases newer than Pervasive.SQL 7 use only the D (default) and O (column collation) attributes.
|
When you define multiple attributes for a single column, the X$Attrib system table contains multiple entries for that column ID - one for each attribute you define. If you do not define column attributes for a particular column, that column has no entry in the X$Attrib table. The text in the Xa$Attrs column appears exactly as you define it with Pervasive PSQL. One index is defined for the X$Attrib table, as explained in X$Attrib System Table Index Definitions:
Table C-3 X$Attrib System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xa$Id
|
No
|
not applicable
|
Yes
|
|
0
|
1
|
Xa$Type
|
No
|
No
|
No
|
X$Depend
The X$Depend system table is associated with the file DEPEND.DDF. X$Depend contains information about trigger dependencies such as tables, views, and procedures. The structure of X$Depend is as follows:
Table C-4 X$Depend System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xd$Trigger
|
CHAR
|
30
|
Yes
|
Name of trigger. It corresponds to Xt$Name in X$Trigger.
|
|
Xd$DependType
|
UNSIGNED
|
1
|
not applicable
|
1 for Table, 2 for View, 3 for Procedure.
|
|
Xd$DependName
|
CHAR
|
30
|
Yes
|
Name of dependency with which the trigger is associated. It corresponds to either Xf$Name in X$File, Xv$Name in X$View, or Xp$Name in X$Proc.
|
Two indexes are defined for the X$Depend table, as follows:
Table C-5 X$Depend System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xd$Trigger
|
No
|
Yes
|
Yes
|
|
0
|
1
|
Xd$DependType
|
No
|
not applicable
|
Yes
|
|
0
|
2
|
Xd$DependName
|
No
|
Yes
|
No
|
|
1
|
0
|
Xd$DependType
|
Yes
|
not applicable
|
Yes
|
|
1
|
1
|
Xd$DependName
|
Yes
|
Yes
|
No
|
Index Number corresponds to the value stored in the Xi$Number column in the X$Index system table. Segment Number corresponds to the value stored in the Xi$Part column in the X$Index system table.
X$Field
The X$Field system table is associated with the file FIELD.DDF. X$Field contains information about all the columns and named indexes defined in the database. The structure of X$Field is as follows:
Table C-6 X$Field System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xe$Id
|
USMALLINT
|
2
|
not applicable
|
Internal ID Pervasive PSQL assigns, unique for each field in the database.
|
|
Xe$File
|
USMALLINT
|
2
|
not applicable
|
ID of table to which this column or named index belongs. It corresponds to Xf$Id in X$File.
|
|
Xe$Name
|
CHAR
|
20
|
Yes
|
Column name or index name.
|
|
Xe$DataType
|
UTINYINT
|
1
|
not applicable
|
Control field:
0 through 26: column data type
227: constraint name
255: index name
|
|
Xe$Offset
|
USMALLINT
|
2
|
not applicable
|
Column offset in table; index number if named index. Offsets are zero-relative.
Index Number corresponds to the value stored in the Xi$Number column in the X$Index system table.
|
|
Xe$Size
|
USMALLINT
|
2
|
not applicable
|
Column size, representing the internal storage, in bytes, required for the field.
Size does not include the NULL byte for TRUE NULL fields.
|
|
Xe$Dec
|
UTINYINT
|
1
|
not applicable
|
Column decimal place (for DECIMAL, NUMERIC, NUMERICSA, NUMERICSTS, MONEY, or CURRENCY types). Relative bit positions for contiguous bit columns. Fractional seconds for TIMESTAMP data type.
|
|
Xe$Flags
|
USMALLINT
|
2
|
not applicable
|
Flags word.
Bit 0 is the case flag for string data types.
If bit 0 = 1, the field is case insensitive.
If bit 2 = 1, the field allows null values.
Bit 3 of Xe$flag is used to differentiate a Pervasive.SQL v7 1-byte TINYINT (B_TYPE_INTEGER unsigned) from SRDE's 1-byte TINYINT (B_TYPE_INTEGER, but signed).
If bit 3 = 1 and Xe$datatype = 1 and Xe$size =1, then it means that TINYINT column is created by the SRDE and is a signed 1-byte TINYINT.
If bit 3 = 0 and Xe$datatype = 1 and xe$size = 1 then it means that TINYINT column is created by the legacy SQL engine and is an unsigned 1-byte TINYINT.
If bit 12 = 1, the field is interpreted as BINARY.
If bit 13 = 1, the field is interpreted as DECIMAL with even-byte precision.
|
The column Xe$File corresponds to the column Xf$Id in the X$File system table and is the link between the tables and the columns they contain. So, for example, the following query shows you all of the field definitions in order for the Billing table.
SELECT "X$Field".*
FROM X$File,X$Field
WHERE Xf$Id=Xe$File AND Xf$Name = 'Billing' AND
Xe$DataType <= 26
The integer values in column Xe$DataType are codes that represent the Pervasive PSQL data types. See Pervasive PSQL Supported Data Types for the codes.
Five indexes are defined for the X$Field table, as follows:
Table C-7 X$Field System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xe$Id
|
No
|
not applicable
|
No
|
|
1
|
0
|
Xe$File
|
Yes
|
not applicable
|
No
|
|
2
|
0
|
Xe$Name
|
Yes
|
Yes
|
No
|
|
3
|
0
|
Xe$File
|
No
|
not applicable
|
Yes
|
|
3
|
1
|
Xe$Name
|
No
|
Yes
|
No
|
|
4
|
0
|
Xe$File
|
Yes
|
not applicable
|
Yes
|
|
4
|
1
|
Xe$Offset
|
Yes
|
not applicable
|
Yes
|
|
4
|
2
|
Xe$Dec
|
Yes
|
not applicable
|
No
|
X$File
The X$File system table is associated with the file FILE.DDF. For each table defined in the database, X$File contains the table name, the location of the associated table, and a unique internal ID number that Pervasive PSQL assigns. The structure of X$File is as follows:
Table C-8 X$File System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xf$Id
|
USMALLINT
|
2
|
not applicable
|
Internal ID Pervasive PSQL assigns.
|
|
Xf$Name
|
CHAR
|
20
|
Yes
|
Table name.
|
|
Xf$Loc
|
CHAR
|
64
|
No
|
File location (path name).
|
|
Xf$Flags
|
UTINYINT
|
1
|
not applicable
|
File flags. If bit 4 = 1, the file is a dictionary file. If bit 4 = 0, the file is user-defined. If bit 6=1, the table supports true nullable columns.
|
|
Xf$Reserved
|
CHAR
|
10
|
No
|
Reserved.
|
Two indexes are defined for the X$File table.
Table C-9 X$File System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xf$Id
|
No
|
not applicable
|
No
|
|
1
|
0
|
Xf$Name
|
No
|
Yes
|
No
|
X$Index
The X$Index system table is associated with the file INDEX.DDF. X$Index contains information about all the indexes defined on the tables in the database. The structure of X$Index is as follows:
Table C-10 X$Index System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xi$File
|
USMALLINT
|
2
|
not applicable
|
Unique ID of the table to which the index belongs. It corresponds to Xf$Id in X$File.
|
|
Xi$Field
|
USMALLINT
|
2
|
not applicable
|
Unique ID of the index column. It corresponds to Xe$Id in X$Field.
|
|
Xi$Number
|
USMALLINT
|
2
|
not applicable
|
Index number (range 0 - 119).
|
|
Xi$Part
|
USMALLINT
|
2
|
not applicable
|
Segment number (range 0 - 119).
|
|
Xi$Flags
|
USMALLINT
|
2
|
not applicable
|
Index attribute flags.
|
The Xi$File column corresponds to the Xf$Id column in the X$File system table. The Xi$Field column corresponds to the Xe$Id column in the X$Field system table. Thus, an index segment entry is linked to a file and to a field.
The Xi$Flags column contains integer values that define the index attributes. The following table describes how Pervasive PSQL interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.
Table C-11 X$Index System Table Index Definitions
|
Bit Position
|
Decimal Equivalent
|
Description
|
|
0
|
1
|
Index allows duplicates.
|
|
1
|
2
|
Index is modifiable.
|
|
2
|
4
|
Indicates an alternate collating sequence.
|
|
3
|
8
|
Null values are not indexed (refers to Btrieve NULLs, not SQL true NULLS).
|
|
4
|
16
|
Another segment is concatenated to this one in the index.
|
|
5
|
32
|
Index is case-insensitive.
|
|
6
|
64
|
Index is collated in descending order.
|
|
7
|
128
|
Index is a named index if bit 0 is 0. If bit 0 is 1 and bit 7 is 1, the index uses the repeating duplicates key method. If bit 0 is 1 and bit 7 is 0, the index uses the linked duplicates key method. See also LINKDUP . For a detailed discussion of linked duplicates method and repeating duplicates method, see Methods for Handling Duplicate Keys in Advanced Operations Guide.)
|
|
8
|
256
|
Index is a Btrieve extended key type.
|
|
9
|
512
|
Index is partial.
|
|
13
|
8192
|
Index is a foreign key.
|
|
14
|
16384
|
Index is a primary key referenced by some foreign key.
|
The value in the Xi$Flags column for a particular index is the sum of the decimal values that correspond to that index's attributes. Three indexes are defined for the X$Index table, as follows:
Table C-12 X$Index System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xi$File
|
Yes
|
not applicable
|
No
|
|
1
|
0
|
Xi$Field
|
Yes
|
not applicable
|
No
|
|
2
|
0
|
Xi$File
|
No
|
not applicable
|
Yes
|
|
2
|
1
|
Xi$Number
|
No
|
not applicable
|
Yes
|
|
2
|
2
|
Xi$Part
|
No
|
not applicable
|
No
|
Index Number corresponds to the value stored in the Xi$Number column in the X$Index system table. Index numbering starts at zero. Segment Number corresponds to the value stored in the Xi$Part column in the X$Index system table.
To see the information about the index segments defined for the Billing table, for example, issue the following query:
SELECT Xe$Name,Xe$Offset, "X$Index".*
FROM X$File,X$Index,X$Field
WHERE Xf$Id=Xi$File and Xi$Field=Xe$Id and Xf$Name =
`Billing'
X$Proc
The X$Proc system table is associated with the file PROC.DDF. X$Proc contains the compiled structure information for every stored procedure defined. The structure of X$Proc is as follows:
Table C-13 X$Proc System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xp$Name
|
CHAR
|
30
|
Yes
|
Stored procedure name.
|
|
Xp$Ver
|
UTINYINT
|
1
|
not applicable
|
Version ID. This is reserved for future use.
|
|
Xp$Id
|
USMALLINT
|
2
|
not applicable
|
0-based Sequence Number.
|
|
Xp$Flags
|
UTINYINT
|
1
|
not applicable
|
1 for stored statement, 2 for stored procedure or 3 for external procedure.
|
|
Xp$Misc
|
LONGVARCHAR
(LVAR)
|
<=990
|
not applicable
|
Internal representation of stored procedure.
|
Note
Stored statements and external procedures were supported in versions of Pervasive.SQL prior to Pervasive.SQL 2000i. Only stored procedures have been supported since Pervasive.SQL 2000i.
One index is defined for the X$Proc table, as follows:
Table C-14 X$Proc System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xp$Name
|
No
|
Yes
|
Yes
|
|
0
|
1
|
Xp$Id
|
No
|
not applicable
|
No
|
A single stored procedure may be stored in multiple entires in X$Proc, linked by Xp$Name.
X$Relate
The X$Relate system table is associated with the file RELATE.DDF. X$Relate contains information about the referential integrity (RI) constraints defined on the database. X$Relate is automatically created when the first foreign key is created, since this results in a relationship being defined.
The structure of X$Relate is as follows:
Table C-15 X$Relate System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xr$PId
|
USMALLINT
|
2
|
not applicable
|
Primary table ID.
|
|
Xr$Index
|
USMALLINT
|
2
|
not applicable
|
Index number of primary key in primary table.
|
|
Xr$FId
|
USMALLINT
|
2
|
not applicable
|
Dependent table ID.
|
|
Xr$FIndex
|
USMALLINT
|
2
|
not applicable
|
Index number of foreign key in dependent table.
|
|
Xr$Name
|
CHAR
|
20
|
Yes
|
Foreign key name.
|
|
Xr$UpdateRule
|
UTINYINT
|
1
|
not applicable
|
1 for restrict.
|
|
Xr$DeleteRule
|
UTINYINT
|
1
|
not applicable
|
1 for restrict, 2 for cascade.
|
|
Xr$Reserved
|
CHAR
|
30
|
No
|
Reserved.
|
Five indexes are defined for the X$Relate table, as follows:
Table C-16 X$Relate System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xr$PId
|
Yes
|
not applicable
|
No
|
|
1
|
0
|
Xr$FId
|
Yes
|
not applicable
|
No
|
|
2
|
0
|
Xr$Name
|
No
|
Yes
|
No
|
|
3
|
0
|
Xr$Pld
|
No
|
not applicable
|
Yes
|
|
3
|
1
|
Xr$Name
|
No
|
Yes
|
No
|
|
4
|
0
|
Xr$Fld
|
No
|
not applicable
|
Yes
|
|
4
|
1
|
Xr$Name
|
No
|
Yes
|
No
|
X$Rights
The X$Rights system table is associated with the file RIGHTS.DDF. X$Rights contains access rights information for each user. Pervasive PSQL uses this table only when you enable the security option. The structure of X$Rights is as follows:
Table C-17 X$Rights System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xr$User
|
USMALLINT
|
2
|
not applicable
|
User ID
|
|
Xr$Table
|
USMALLINT
|
2
|
not applicable
|
Table ID
|
|
Xr$Column
|
USMALLINT
|
2
|
not applicable
|
Column ID
|
|
Xr$Rights
|
UTINYINT
|
1
|
not applicable
|
Table or column rights flag
|
The Xr$User column corresponds to the Xu$Id column in the X$User table. The Xr$Table column corresponds to the Xf$Id column in the X$File table. The Xr$Column column corresponds to the Xe$Id column in the X$Field table.
Note
For any row in the system table that describes table rights, the value for Xr$Column is null.
The Xr$Rights column contains integer values whose rightmost 8 bits define the users' access rights. The following table describes how Pervasive PSQL interprets the value. Values from this table may be combined into a single Xr$Rights value.
Table C-18 Xr$Rights System Table Bit Position Definitions
|
Hex Value
|
Decimal Equivalent
|
Description
|
|
1
|
1
|
Reorganization in progress.
|
|
0x90
|
144
|
References rights to table.
|
|
0xA0
|
160
|
Alter Table rights.
|
|
0x40
|
64
|
Select rights to table or column.
|
|
0x82
|
130
|
Update rights to table or column.
|
|
0x84
|
132
|
Insert rights to table or column.
|
|
0x88
|
136
|
Delete rights to table or column.
|
A decimal equivalent of 0 implies no rights.
The value in the Xr$Rights column for a particular user is the bit-wise intersection of the hex values corresponding to the access rights that apply to the user. It is not the sum of the decimal values.
For example, the value in Xr$Rights for a user with all rights assigned would be represented as follows:
144 | 160 | 64 | 130 | 132 | 136 = 254
Three indexes are defined for the X$Rights table, as follows:
Table C-19 X$Rights System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xr$User
|
Yes
|
not applicable
|
No
|
|
1
|
0
|
Xr$User
|
No
|
not applicable
|
Yes
|
|
1
|
1
|
Xr$Table
|
No
|
not applicable
|
Yes
|
|
1
|
2
|
Xr$Column
|
No
|
not applicable
|
No
|
|
2
|
0
|
Xr$Table
|
Yes
|
not applicable
|
Yes
|
|
2
|
1
|
Xr$Column
|
Yes
|
not applicable
|
No
|
X$Trigger
The X$Trigger system table is associated with the file TRIGGER.DDF. X$Trigger contains information about the triggers defined for the database. The structure of X$Trigger is as follows:
Table C-20 X$Trigger System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xt$Name
|
CHAR
|
30
|
Yes
|
Trigger name.
|
|
Xt$Version
|
USMALLINT
|
2
|
not applicable
|
Trigger version. A 4 indicates Scalable SQL v4.
|
|
Xt$File
|
USMALLINT
|
2
|
not applicable
|
File on which trigger is defined. Corresponds to Xf$Id in X$File.
|
|
Xt$Event
|
UNSIGNED
|
1
|
not applicable
|
0 for INSERT, 1 for DELETE, 2 for UPDATE.
|
|
Xt$ActionTime
|
UTINYINT
|
1
|
not applicable
|
0 for BEFORE, 1 for AFTER.
|
|
Xt$ForEach
|
UTINYINT
|
1
|
not applicable
|
0 for ROW (default), 1 for STATEMENT.
|
|
Xt$Order
|
USMALLINT
|
2
|
not applicable
|
Order of execution of trigger.
|
|
Xt$Sequence
|
USMALLINT
|
2
|
not applicable
|
0-based sequence number.
|
|
Xt$Misc
|
LONGVARCHAR
(LVAR)
|
<=4054
|
not applicable
|
Internal representation of trigger.
|
A trigger that is long enough may require multiple entries in Trigger.DDF. Each entry has the same trigger name in the Xt$Name field, and is used in the order specified by the Xt$Sequence field.
Three indexes are defined for the X$Trigger table, as follows:
Table C-21 X$Trigger System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xt$Name
|
No
|
Yes
|
Yes
|
|
0
|
1
|
Xt$Sequence
|
No
|
not applicable
|
No
|
|
1
|
0
|
Xt$File
|
No
|
not applicable
|
Yes
|
|
1
|
1
|
Xt$Name
|
No
|
Yes
|
Yes
|
|
1
|
2
|
Xt$Sequence
|
No
|
not applicable
|
No
|
|
2
|
0
|
Xt$File
|
Yes
|
not applicable
|
Yes
|
|
2
|
1
|
Xt$Event
|
Yes
|
not applicable
|
Yes
|
|
2
|
2
|
Xt$ActionTime
|
Yes
|
not applicable
|
Yes
|
|
2
|
3
|
Xt$ForEach
|
Yes
|
not applicable
|
Yes
|
|
2
|
4
|
Xt$Order
|
Yes
|
not applicable
|
Yes
|
|
2
|
5
|
Xt$Sequence
|
Yes
|
not applicable
|
No
|
The trigger may be stored in more than one entry in X$Trigger, linked by Xt$Name and ordered by Xt$Sequence.
X$User
The X$User system table is associated with the file USER.DDF. X$User contains the name and password of each user and the name of each user group. Pervasive PSQL uses this table only when you enable the security option. The structure of X$User is as follows:
Table C-22 X$User System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xu$Id
|
USMALLINT
|
2
|
not applicable
|
Internal ID assigned to the user or group.
|
|
Xu$Name
|
CHAR
|
30
|
Yes
|
User or group name.
|
|
Xu$Password
|
CHAR
|
9
|
No
|
User password (encrypted)
|
|
Xu$Flags
|
USMALLINT
|
2
|
not applicable
|
User or group flags.
|
Note
For any row in the X$User system table that describes a group, the column value for Xu$Password is NULL.
The Xu$Flags column contains integer values whose rightmost 8 bits define the user or group attributes. The following table describes how Pervasive PSQL interprets each bit position when the bit has the binary value of 1. Bit position 0 is the rightmost bit in the integer.
Table C-23 Xu$Flags System Table Bit Position Definitions
|
Bit Position
|
Decimal Equivalent
|
Description
|
|
0
|
1
|
Reserved.
|
|
1
|
2
|
Reserved.
|
|
2
|
4
|
Reserved.
|
|
3
|
8
|
Reserved.
|
|
4
|
16
|
Reserved.
|
|
5
|
32
|
Reserved.
|
|
6
|
64
|
Name is a group name.
|
|
7
|
128
|
User or group has the right to define tables in the dictionary.
|
The value in the Xu$Flags column for a particular user or group is the sum of the decimal values corresponding to the attributes that apply to the user or group.
Two indexes are defined for the X$User table, as follows:
Table C-24 X$User System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xu$Id
|
Yes
|
not applicable
|
No
|
|
1
|
0
|
Xu$Name
|
No
|
Yes
|
No
|
X$View
The X$View system table is associated with the file VIEW.DDF. X$View contains view definitions, including information about joined tables and the restriction conditions that define views. You can query the X$View table to retrieve the names of the views that are defined in the dictionary.
The first column of the X$View table contains the view name; the second and third columns describe the information found in the LVAR column, Xv$Misc. The structure of X$View is as follows:
Table C-25 X$View System Table Structure
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xv$Name
|
CHAR
|
20
|
Yes
|
View name.
|
|
Xv$Ver
|
UTINYINT
|
1
|
not applicable
|
Version ID. This is reserved for future use.
|
|
Xv$Id
|
UTINYINT
|
1
|
not applicable
|
Sequence number.
|
|
Xv$Misc
|
LONGVARCHAR
(LVAR)
|
<=2000
|
not applicable
|
Pervasive PSQL internal definitions.
|
Two indexes are defined for the X$View table, as follows:
Table C-26 X$View System Table Index Definitions
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xv$Name
|
Yes
|
Yes
|
No
|
|
1
|
0
|
Xv$Name
|
No
|
Yes
|
Yes
|
|
1
|
1
|
Xv$Ver
|
No
|
not applicable
|
Yes
|
|
1
|
2
|
Xv$Id
|
No
|
not applicable
|
No
|
A single view may be stored in multiple X$View entries, linked by Xv$Name and ordered by Xv$Id.
V2 Metadata System Tables
X$Attrib
The X$Attrib system table is associated with the file PVATTRIB.DDF. X$Attrib contains information about the column attributes of each column in the database; there is an entry for each column attribute you define. The structure of X$Attrib is as follows:
Table C-27 X$Attrib System Table Structure for V2 Metadata
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xa$Id
|
UINTEGER
|
4
|
not applicable
|
Corresponds to Xe$Id in X$Field
|
|
Xa$Type
|
CHAR
|
4
|
No
|
D for default, O for column collation
|
|
Xa$ASize
|
USMALLINT
|
2
|
Not applicable
|
Length of text in Xa$Attrs
|
|
Xa$Attrs
|
LONGVARCHAR
(NOTE)
|
32,763
|
not applicable
|
Text that defines the column attribute
|
When you define multiple attributes for a single column, the X$Attrib system table contains multiple entries for that column ID - one for each attribute you define. If you do not define column attributes for a particular column, that column has no entry in the X$Attrib table. The text in the Xa$Attrs column appears exactly as you define it with Pervasive PSQL. One index is defined for the X$Attrib table, as follows:
Table C-28 X$Attrib System Table Index Definitions for V2 Metadata
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xa$Id
|
No
|
not applicable
|
Yes
|
|
0
|
1
|
Xa$Type
|
No
|
No
|
No
|
X$Depend
The X$Depend system table is associated with the file PVDEPEND.DDF. X$Depend contains information about trigger dependencies for such objects as tables, views, and procedures. The structure of X$Depend is as follows:
Table C-29 X$Depend System Table Structure for V2 Metadata
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xd$Trigger
|
CHAR
|
128
|
Yes
|
Name of trigger. It corresponds to Xt$Name in X$Trigger.
|
|
Xd$DependType
|
UTINYINT
|
1
|
not applicable
|
1 for Table, 2 for View, 3 for Procedure.
|
|
Xd$DependName
|
CHAR
|
128
|
Yes
|
Name of dependency with which the trigger is associated. It corresponds to either Xf$Name in X$File, Xv$Name in X$View, or Xp$Name in X$Proc.
|
Two indexes are defined for the X$Depend table, as follows:
Table C-30 X$Depend System Table Index Definitions for V2 Metadata
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xd$Trigger
|
No
|
Yes
|
Yes
|
|
0
|
1
|
Xd$DependType
|
No
|
not applicable
|
No
|
|
1
|
0
|
Xd$DependType
|
Yes
|
not applicable
|
Yes
|
|
1
|
1
|
Xd$DependName
|
Yes
|
Yes
|
No
|
X$Field
The X$Field system table is associated with the file PVFIELD.DDF. X$Field contains information about all the columns and named indexes defined in the database. The structure of X$Field is as follows:
Table C-31 X$Field System Table Structure for V2 Metadata
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xe$Id
|
UINTEGER
|
4
|
not applicable
|
Internal ID Pervasive PSQL assigns, unique for each field in the database.
|
|
Xe$File
|
UINTEGER
|
4
|
not applicable
|
ID of table to which this column or named index belongs. It corresponds to Xf$Id in X$File.
|
|
Xe$Name
|
CHAR
|
128
|
Yes
|
Column name or index name.
|
|
Xe$Datatype
|
UTINYINT
|
1
|
not applicable
|
0 through 26: column data type
227: constraint name
255: index name
|
|
Xe$Offset
|
UINTEGER
|
4
|
not applicable
|
Column offset in table; index number if named index. Offsets are zero-relative.
Index Number corresponds to the value stored in the Xi$Number column in the X$Index system table.
|
|
Xe$Size
|
UINTEGER
|
4
|
not applicable
|
Column size, representing the internal storage, in bytes, required for the field.
|
|
Xe$Dec
|
USMALLINT
|
2
|
not applicable
|
Column decimal place (for DECIMAL, NUMERIC, NUMERICSA, NUMERICSTS, MONEY, or CURRENCY types). Relative bit positions for contiguous bit columns. Fractional seconds for TIMESTAMP data type.
|
|
Xe$Flags
|
UINTEGER
|
4
|
not applicable
|
Flags word.
Bit 0 is the case flag for string data types.
If bit 0 = 1, the field is case insensitive.
If bit 2 = 1, the field allows null values.
Bit 3 of Xe$flag is used to differentiate a Pervasive.SQL v7 1-byte TINYINT (B_TYPE_INTEGER unsigned) from SRDE's 1-byte TINYINT (B_TYPE_INTEGER, but signed).
If bit 3 = 1 and Xe$datatype = 1 and Xe$size =1, then it means that TINYINT column is created by the SRDE and is a signed 1-byte TINYINT.
If bit 3 = 0 and Xe$datatype = 1 and xe$size = 1 then it means that TINYINT column is created by the legacy SQL engine and is an unsigned 1-byte TINYINT.
If bit 12 = 1, the field is interpreted as BINARY.
If bit 13 = 1, the field is interpreted as DECIMAL with even-byte precision.
|
The column Xe$File corresponds to the column Xf$Id in the X$File system table and is the link between the tables and the columns they contain. So, for example, the following query shows you all of the field definitions in order for the Billing table.
SELECT "X$Field".*
FROM X$File,X$Field
WHERE Xf$Id=Xe$File AND Xf$Name = 'Billing' AND
Xe$DataType <= 26
The integer values in column Xe$DataType are codes that represent the Pervasive PSQL data types. See "Pervasive PSQL Supported Data Types" on page A-2 for the codes.
Five indexes are defined for the X$Field table, as follows:
Table C-32 X$Field System Table Index Definitions for V2 Metadata
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xe$Id
|
No
|
not applicable
|
No
|
|
1
|
0
|
Xe$File
|
Yes
|
not applicable
|
No
|
|
2
|
0
|
Xe$Name
|
Yes
|
Yes
|
No
|
|
3
|
0
|
Xe$File
|
No
|
not applicable
|
Yes
|
|
3
|
1
|
Xe$Name
|
No
|
Yes
|
No
|
|
4
|
0
|
Xe$File
|
Yes
|
not applicable
|
Yes
|
|
4
|
1
|
Xe$Offset
|
Yes
|
not applicable
|
Yes
|
|
4
|
2
|
Xe$Dec
|
Yes
|
not applicable
|
No
|
X$File
The X$File system table is associated with the file PVFILE.DDF. For each table defined in the database, X$File contains the table name, the location of the associated table, and a unique internal ID number that Pervasive PSQL assigns. The structure of X$File is as follows:
Table C-33 X$File System Table Structure for V2 Metadata
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xf$Id
|
UINTEGER
|
4
|
not applicable
|
Internal ID Pervasive PSQL assigns.
|
|
Xf$Name
|
CHAR
|
128
|
Yes
|
Table name.
|
|
Xf$Loc
|
CHAR
|
250
|
No
|
File location (path name).
|
|
Xf$Flags
|
UINTEGER
|
4
|
not applicable
|
File flags. If bit 4 = 1, the file is a dictionary file. If bit 4 = 0, the file is user-defined. If bit 6=1, the table supports true nullable columns.
|
|
Xf$Reserved
|
CHAR
|
16
|
No
|
Reserved.
|
Two indexes are defined for the X$File table.
Table C-34 X$File System Table Index Definitions for V2 Metadata
|
Index Number
|
Segment Number
|
Column Name
|
Duplicates
|
Case Insensitive
|
Segmented
|
|
0
|
0
|
Xf$Id
|
No
|
not applicable
|
No
|
|
1
|
0
|
Xf$Name
|
No
|
Yes
|
No
|
X$Index
The X$Index system table is associated with the file PVINDEX.DDF. X$Index contains information about all the indexes defined on the tables in the database. The structure of X$Index is as follows:
Table C-35 X$Index System Table Structure for V2 Metadata
|
Column Name
|
Type
|
Size
|
Case Insensitive
|
Description
|
|
Xi$File
|
UINTEGER
|
4
|
not applicable
|
Unique ID of the table to which the index belongs. It corresponds to Xf$Id in X$File.
|
|
Xi$Field
|
UINTEGER
|
4
|
not applicable
|
Unique ID of the index column. It corresponds to Xe$Id in X$Field.
|
|
Xi$Number
|
UINTEGER
|
4
|
not applicable
|
Index number (range 0 - 119).
|
|
Xi$Part
|
UINTEGER
|
4
|
not applicable
|
Segment number (range 0 - 119).
|
|
Xi$Flags
|
UINTEGER
|
4
|
not applicable
|
Index attribute flags.
|
The Xi$File column corresponds to the Xf$Id column in the X$File system table. The Xi$Field column corresponds to the Xe$Id column i
