A quick tour of geodatabase system tables

The internal structure of the geodatabase is represented as a series of simple tables called the geodatabase system tables (the tables prefixed with GDB_), which track the contents of each geodatabase. They essentially describe the geodatabase schema that specifies all dataset definitions, rules, and relationships. These system tables contain and manage all the metadata required to implement geodatabase properties, data validation rules, and behaviors.

The internal structure of these tables was restructured beginning with the ArcGIS 10 release. The information related to the schema in the geodatabase, which prior to ArcGIS 10 was stored in over 35 geodatabase system tables, was consolidated into four main tables:

• GDB_Items: Contains a listing of all items contained within a geodatabase such as feature classes, topologies and domains
• GDB_ItemTypes: Contains a predefined list of recognized item types, such as Table
• GDB_ItemRelationships: Contains schema associations between items such as which feature classes are contained within a feature dataset
• GDB_ItemRelationshipTypes: Contains a predefined list of recognized relationship types such as DatasetInFeatureDataset

The GDB_Items and GDB_ItemRelationships tables represent the user-defined schema within a particular geodatabase. The contents of the tables are modified as the schema of the geodatabase is modified. The GDB_ItemTypes and GDB_ItemRelationshipTypes tables are static. The following diagram illustrates the relationships between these four tables:

Tip:

See the whitepaper XML Schema of the Geodatabase for XML definitions.

The GDB_Items table

A geodatabase is a container of geographic and nongeographic items. These items include tabular datasets, such as feature classes and tables; dataset containers, such as feature datasets, topologies, and geometric networks; datasets that control more advanced geodatabase behavior; and other items such as domains, toolboxes, and workspace extensions. Beginning with ArcGIS 10, all these items are stored within the geodatabase in the GDB_Items table, where each item is stored as a single row.

An item has the following attributes:

• UUID: A unique identifier for the item
• Name: The name of the item

  Be aware that an item's name is not guaranteed to be unique; for example, a relationship class and feature class can have the same name. However, it is not recommended that you give items the same name, since item names should be descriptive enough to differentiate what the item contains.

• Type: A UUID representing the type of item in the record; this corresponds with the unique identifier.
• Definition: An XML document that defines the properties of an item; these properties and the XML schema vary based on item type. For example, the definition of an object class contains information such as subtypes, whereas the definition of a coded value domain contains code/value pairs, and the definition of a relationship class contains (among other things) relationship rules.
• Documentation: An XML document storing the item's metadata
• Properties: An integer value that can be used with bitmasks to retrieve several Boolean properties of the item. Currently, this is only used to indicate whether an item is visible in ArcGIS for Desktop.
• Shape: The extent of the dataset as a polygon, if applicable (the data type depends on the underlying DBMS).

These seven attributes are those you are most likely to access using SQL.

The next attributes store information from an item's definition (such as a feature class's geometry type), which are duplicated outside the XML Definition to allow more efficient browsing of the geodatabase. Examples of these are the Dataset Subtype and Dataset Info properties, the codes of which are dependent on the item's type.

• Physical Name: The fully qualified name of the item
• DatasetSubtype1: For feature classes and raster catalogs, DatasetSubtype1 stores the feature type of the table. For relationship classes, it stores the cardinality of the relationship class. For topologies, the topology ID is stored.
• DatasetSubtype2: For feature classes and raster catalogs, DatasetSubtype2 stores the geometry type. For relationship classes, it stores a value indicating whether the relationship is attributed or nonattributed.
• DatasetInfo1: Stores the name of the shape field for feature class items
• DatasetInfo2: Stores information for feature classes that participate in topologies
• Path: The unique relative path to the item
• URL: The associated URL for the item; used with catalog services
• Defaults: Item metadata; not used directly by the geodatabases, but used by other clients, such as ArcIMS metadata services
• ItemInfo: Storage information for the item, such as symbology, that is independent of the underlying dataset

The GDB_ItemTypes table

The GDB_ItemTypes table contains the predefined set of recognized item types in the geodatabase. Item types are used to normalize the GDB_Items table (the type of an item in the GDB_Items table is represented as a UUID), to provide better support for future geodatabase functionality enhancements and define a type hierarchy. All parent types are abstract; therefore, parent items will not actually be found in the GDB_Items table.

The contents of the GDB_ItemTypes table are static; it is not modified as items are created or deleted.

An item type has the following attributes:

• UUID: A unique identifier of an item type and the foreign key to the GDB_Item table's type column
• ParentTypeID: The item type's parent type. In the case of the abstract item type (which has no parent), this is a null UUID
• Name: The name of an item type such as Feature Class or Range Domain
• ObjectID: The unique identifier for the GDB_ItemTypes table

The GDB_ItemRelationships table

Nearly all geodatabase items have relationships with at least one other geodatabase item. For example, a stand-alone table is related to the root folder of a geodatabase, a domain is related to the feature classes or tables that rely on it for validation, and feature classes are related to the topology in which they participate. The GDB_ItemRelationships table is used to track these relationships.

Item relationships have the following attributes:

• UUID: A unique identifier of an item relationship
• Type: A UUID representing the relationship's type. This corresponds with the unique identifier of the item relationship's type.
• OriginID: The unique identifier of the relationship's origin item
• DestinationID: The unique identifier of the relationship's destination item
• Attributes: An XML document containing detailed information about the item relationship

  Many relationship types do not include attributes; they are only used in some relationships between controller datasets and their controlled datasets.

• ObjectID: Unique identifier
• Properties: An integer value that can be used with bitmasks to retrieve several Boolean properties of the item. Currently, this is only used to indicate whether the items participating in the relationship are visible in ArcGIS for Desktop.

You would likely only access the first five attributes using SQL.

Tip:

It is important to understand that an item relationship is not analogous to a relationship class. In fact, every relationship class has item relationships with two object classes (its source and destination classes) as well as an additional item relationship with its container (either the geodatabase's root folder or a feature dataset).

The GDB_ItemRelationshipTypes table

In the same way that a static collection of item types is used to classify items, a static collection of item relationship types is used to classify item relationships. For example, DatasetInFeatureDataset (indicating that a feature class, relationship class, or controller dataset exists inside of a feature dataset) and DomainInDataset (indicating that a dataset uses a domain for class-level or subtype-level validation) are two types of item relationships. Unlike item types, item relationship types do not have a hierarchy concept with parent and child types.

Item relationship types have the following attributes:

• UUID: A unique identifier of an item relationship type and the primary key for the item relationship table's type column
• Name: The name of the item relationship type, such as DatasetInFolder
• Forward Label: A description of the relationship from the context of the origin item
• Backward Label: A description of the relationship from the context of the destination item
• Origin Item Type ID: The UUID of the item type that can act as the origin item in item relationships of this type
• Destination Item Type ID: The UUID of the item type that can act as the origin item in item relationships of this type
• Is Containment: Indicates whether the destination item can exist even if the origin item is deleted.

Tip:

The Origin and Destination Item Type IDs are often identifiers of abstract item types. For example, the Origin Type ID of the DomainInDataset relationship type is Domain, which is an abstract item type with two subtypes: Range Domain and Coded Value Domain.

XML in geodatabase system tables

As mentioned in the attribute definitions for the geodatabase system tables, some of the fields use an XML data type. In ArcSDE geodatabases in IBM DB2, Microsoft SQL Server, and PostgreSQL, these columns use the native XML of the database management system and, therefore, can be queried using XPath expressions with SQL.

In geodatabases in Oracle and Informix, the XML columns use ArcSDE XML, which stores information as BLOBs in a series of separate tables. As such, they cannot be directly accessed with SQL.

To allow you to view the contents of the XML columns in the system tables in geodatabases in Oracle, two system views exist that store the contents from the geodatabase system table XML columns in a CLOB data type. The GDB_Items_vw shows the contents of the Definition, Documentation, and ItemInfo columns from the GDB_Items table in CLOB columns. The GDB_ItemRelationships_vw shows the contents of the Attributes column of the GDB_ItemRelationships table in a CLOB column. The contents of the CLOB columns can be read as text.

When querying these views, you extract the string from the CLOB column, convert it to an XML type, and execute an XPath query on it. Be aware that if you attempt to create an XML type for a column that contains a blank string, it will fail with the message "XML parsing failed" because Oracle does not support this.

To query the contents of the CLOB columns, you must configure the Oracle server to accept SQL connections. See Configuring the Oracle extproc to access the geodatabase with SQL for more information.