Designer Metadata Usage Requirements In Teiid Runtime

Version 3

    This article is in response to the thred http://community.jboss.org/thread/170698 that is looking for the Teiid Runtime use of the metadata that has been produced by the Teiid Designer. Based on the metadata exposed by the Teiid Desinger the below table shows which fields are required and how that information is being used in Teiid runtime currently as of Teiid version 7.5. A ODS file attached if you like to modify.

     

     

    TABLETypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesYesYesName of the Table
    NameInSourceStringYesYesYesName of Table in the source system, for view this can be empty, also used on variety of use cases
    CardinalityIntegerYesYesYesCardinality is used to calculate the cost of source node access
    TableTypeIntegerYesYesYesTable,View,Document,XmlMappingClass,XmlStagingTable,MaterializedTable
    IsVirtualBooleanYesYesYesIf this Table is a  view
    IsSystemBooleanYesYesNoOnly used for System metadata
    IsMaterializedBooleanYesYesYesTo identify that the table is materialized
    SupportsUpdateBooleanYesYesYesTo allow updates on the table
    PrimaryKeyIDStringYesKeyRecordYesUsed for creating indexes on temp tables, create default update/delete procedures, and in costing
    ForeignKeyIDsCollectionYesList<ForeignKey>YesUsed in join planning for allowed criteria and to establish key preservation
    IndexIDsCollectionYesList<KeyRecord>YesUsed for creating non-unique indexes on temp tables and in planning (estimate predicate cost)
    UniqueKeyIDsCollectionYesList<KeyRecord>YesUsed for creating unique indexes on temp tables and in planning (estimate predicate cost)
    AccessPatternIDsCollectionYesList<KeyRecord>YesUsed for enforcing the criteria on query
    MaterializedTableIDStringYesTableYesReference to Materialization table
    insertEnabledBoolean**YesYesFlag for checking insert procedure is enabled
    deleteEnabledBoolean**YesYesFlag for checking delete procedure is enabled
    updateEnabledBoolean**YesYesFlag for checking update procedure is enabled
    Select TransformationString**YesYesTransformation for Select in case of View
    Insert PlanString**YesYesTransformation for Insert in case of View
    Update PlanString**YesYesTransformation for Update in case of View
    Delete PlanString**YesYesTransformation for Delete in case of View
    BindingsCollection**YesYesXML mapping class input set bindings
    SchemaPathsCollection**YesYesXML Document











    COLUMNTypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesYesYesName of the column
    NameInSourceStringYesYesYesName of the column in source system
    IsSelectableBooleanYesYesYesColumn is allowed in select
    IsUpdatableBooleanYesYesYesColumn is allowed in Update/Insert/Delete
    NullTypeIntegerYesYesYesUsed for validation if null value allowed and if null can be used as default insert value
    IsAutoIncrementableBooleanYesYesYesDuring insert used to validate if a value is required or not
    IsCaseSensitiveBooleanYesYesNoUsed in System Metadata
    IsSignedBooleanYesYesNoUsed in System Metadata
    IsCurrencyBooleanYesYesNoUsed in System Metadata
    IsFixedLengthBooleanYesYesNoUsed in System Metadata
    IsTranformationInputParameterBooleanYesNoNoNo longer used
    SearchTypeIntegerYesYesYesUsed for defining the capability of the source
    LengthIntegerYesYesNoUsed in System Metadata
    ScaleIntegerYesYesNoUsed in System Metadata
    PrecisionIntegerYesYesNoUsed in System Metadata
    CharOctetLengthIntegerYesYesNoUsed in System Metadata
    RadixIntegerYesYesNoUsed in System Metadata
    DistinctValuesIntegerYesYesNoUsed for cost calculations, System metadata
    NullValuesIntegerYesYesNoUsed for cost calculations, System metadata
    MinValueStringYesYesNoUsed for cost calculations, System metadata
    MaxValueStringYesYesNoUsed for cost calculations, System metadata
    FormatStringYesYesNoUsed for cost calculations, System metadata
    RuntimeTypeStringYesYesYesMust be consistent with DatatypeObjectID
    NativeTypeStringYesYesYesTranslators can use this field to further plan
    DatatypeObjectIDStringYesNoYesUsed to set the Datatype
    DefaultValueStringYesYesYesUsed for Insert and procedure execute operations when the values are not supplied
    PositionIntegerYesYesYes1 based position of the column in the table/resultset











    PRIMARY KEYTypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesYesYesSee the KeyRecord, See Table
    NameInSourceStringYesYesNo
    ColumnIDsCollectionYesYesYes





    UNIQUE KEYTypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesYesYesSee the KeyRecord, See Table
    NameInSourceStringYesYesNo
    ColumnIDsCollectionYesYesYes





    INDEXTypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesYesYesSee the KeyRecord, See Table
    NameInSourceStringYesYesNo
    ColumnIDsCollectionYesYesYes





    ACCESS PATTERNSTypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesYesYesSee the KeyRecord, See Table
    NameInSourceStringYesYesNo
    ColumnIDsCollectionYesYesYes





    RESULT SETTypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesYesYesSee Procedure
    NameInSourceStringYesYesNo
    ColumnIDsCollectionYesYesYes





    FOREIGN KEYTypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesYesYesSee the KeyRecord, See Table
    NameInSourceStringYesYesNo
    ColumnIDsCollectionYesYesYes
    UniqueKeyIDStringYesYesYes





    DATATYPE (note - not currently extensible)
    TypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesYesNoOnly used for System metadata
    NameInSourceStringYesYesNoOnly used for System metadata
    LengthIntegerYesYesNoOnly used for System metadata
    PrecisionLengthIntegerYesYesNoOnly used for System metadata
    ScaleIntegerYesYesNoOnly used for System metadata
    RadixIntegerYesYesNoOnly used for System metadata
    IsSignedBooleanYesYesNoOnly used for System metadata
    IsAutoIncrementBooleanYesYesNoOnly used for System metadata
    IsCaseSensitiveBooleanYesYesNoOnly used for System metadata
    TypeIntegerYesYesNoOnly used for System metadata
    SearchTypeIntegerYesYesNoOnly used for System metadata
    NullTypeIntegerYesYesNoOnly used for System metadata
    JavaClassNameStringYesYesYesMaps to runtime type based on java class name
    RuntimeTypeNameStringYesYesNoOnly used for System metadata
    DatatypeIDStringYesYesNoOnly used for System metadata
    BaseTypeIDStringYesYesNoOnly used for System metadata
    PrimitiveTypeIDStringYesYesNoOnly used for System metadata
    VarietyTypeIntegerYesYesNoOnly used for System metadata
    VarietyPropsCollectionYesYesNoOnly used for System metadata





    PROCEDURETypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameString
    YesYesName of the Procedure
    NameInSourceString
    YesYesName of procedure in source if this is source model
    IsFunctionBoolean
    YesYesIf this represents a source function
    IsVirtualBoolean
    YesYesIf this is a virtual procedure
    ParametersIDsCollection
    YesYesParameter List
    ResultSetIDString
    YesYesResult set columns
    UpdateCountInteger
    YesYesUpdate count defines the number of sources being updated, only applicable for virtual procedures





    PROCEDURE PARAMETERTypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    ObjectIDStringYes??
    Same as Column
    FullNameStringYesYes
    Same as Column
    nameInSourceStringYesYes
    Same as Column
    defaultValueStringYesYes
    Same as Column
    RuntimeTypeStringYesYes
    Same as Column
    DatatypeObjectIDStringYesYes
    Same as Column
    LengthIntegerYesYes
    Same as Column
    RadixIntegerYesYes
    Same as Column
    ScaleIntegerYesYes
    Same as Column
    NullTypeIntegerYesYes
    Same as Column
    PrecisionIntegerYesYes
    Same as Column
    PositionIntegerYesYes
    Same as Column
    TypeStringYesYesYesDefines parameter is IN/OUT/RETURN
    OptionalBooleanYesYesNoDefines if the parameter is optional or not, only used system metadata





    SQL TRANSFORMATION(**)TypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    VirtualGroupNameStringYesNoYesSee Table, the properties defined on Table
    TransformedObjectIDStringYesNoYesSee Table, the properties defined on Table
    TransformationObjectIDStringYesNoYesSee Table, the properties defined on Table
    TransformationSqlStringYesNoYesSee Table, the properties defined on Table
    BindingsCollectionYesNoNoSee Table, the properties defined on Table
    SchemaPathsCollectionYesNoNoSee Table, the properties defined on Table





    VDBTypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesvdb.xmlYesName of the VDB
    NameInSourceString??NoNoNot required
    VersionStringYesvdb.xmlYesVDB version
    IdentifierStringYesNoNoNot required
    DescriptionStringYesvdb.xmlNoUsed by System metadata
    ProducerNameStringYesNoNoNot required
    ProducerVersionStringYesNoNoNot required
    ProviderStringYesNoNoNot required
    TimeLastChangedStringYesNoNoNot required
    TimeLastProducedStringYesNoNoNot required
    ModelIDsCollectionyesvdb.xmlYesDefines the model list in a VDB





    ANNOTATIONTypeIn DesignerIn Metadata APIRequired By RuntimeDescription
    FullNameStringYesYesNoUsed by System metadata, as description on procedure parameter
    NameInSourceStringYesNoNoNot required
    DescriptionStringYesNoNoNot required

     

     

    Designer UI Interactions to control metadata:

     

    • Costing:   When importing from a jdbc source, you can get the cardinality automatically set at modeling time.  For most sources aproximate table cardinalities are available from approximate indexes.  If approximate indexes are not selected, then manual updates can be done or the modeling cost update action can be run (for non-oracle sources this will typically issue aggregate queries to obtain statistics).  Additionally, there is a admin option to set cost information on the server, but the metadata isn't maintained after the server shutdown unless a MetadataRepository is configured.