TMCore SQL Extensions Reference Sheet

Views Summary

View Name Description
tm_assoc Binds an association with the topic that defines the association type, and the type and role player of a single role in that association.
tm_assoc2 Binds an association with the topic that defines the association type, and the type and role player of two separate roles in that association.
tm_assocScope Binds an association with each of the topics in its scope.
tm_classHierarchy Binds a topic with its subclass(es).
tm_directInstanceOf Binds a topic with the topic(s) which define its type.
tm_name Binds a topic with its names.
tm_nameScope Binds a topic name with the topics in the scope of the topic name.
tm_nameValue Binds a topic with its names and their string values.
tm_nameVariant Binds a topic with its names and name variants
tm_nameVariantResource Binds a topic with a name, a variant of that name and the address pointed to by the variant.
tm_nameVariantValue Binds a topic with a name, a variant of that name and the string value of the variant.
tm_npclAssociationRoleConstraints Returns information on all association role cosntraints defined in the specified topic map.
tm_npclExtensionValues Extracts all occurrences which meta-typed as NPCL extensions.
tm_npclOccurrenceConstraints Binds all occurrence constraints defined in the topic maps to the topic type and occurrence type that are constrained, the minimum and maximum cardinality of the constraint and the name and subject identifier for the constraint topic itself.
tm_npclRolePlayerConstraints Returns information on all NPCL role player constraints defined in the specified topic map.
tm_npclScoping A view that binds topic type identifier to the scoping facet value for that topic type.
tm_npclTypes Returns all instances of the type identified by @psi bound to a display name and their subject identifier(s).
tm_npclValueFacets Returns all instances of the type identified by @psi bound to a display name, their subject identifier(s) and value type facet values.
tm_occur Binds a topic with each of its occurrences and the occurrence type only.
tm_occurResource Binds a topic with an occurrence, the occurrence type and the address of the resource referenced by the occurrence.
tm_occurScope Binds an occurrence with the topics in the scope of the occurrence.
tm_occurValue Binds a topic with and occurrence, the occurrence type and the string value of the occurrence.
tm_si Binds a topic to its subject identifiers.
tm_sl Binds a topic to its subject locators.
tm_src Binds topic map objects with their source locator(s) and a code indicating the type of topic map object.
tm_topic Binds a topic with the topics that define its types.
tm_topicmap Binds a topic map id to its name.
tm_tsrc Binds a topic to its source locators.
tm_variantScope Binds a variant name with the topics in the scope of the variant name.

View : tm_assoc

Summary

Binds an association with the topic that defines the association type, and the type and role player of a single role in that association.

View Description

Column Type Description
topicmap int The ID of the containing topic map
association_id int The ID of the association
association_type int The ID of the topic that types the association
association_role_id int The ID of the association role.
r1p int The ID of a role player in the association
r1t int The ID of the topic that types the role played by the role player.

Remarks

This view contains one row for each association role in each association, so a binary association will be represented by two rows with the same association_id column value.

View : tm_assoc2

Summary

Binds an association with the topic that defines the association type, and the type and role player of two separate roles in that association.

View Description

Column Type Description
topicmap int The ID of the containing topic map
association_id int The ID of the association
association_type int The ID of the topic that types the association
r1_id int The ID of the first association role.
r1p int The ID of the first role player in the association
r1t int The ID of the topic that types the role played by the first role player.
r2_id int The ID of the second association role.
r2p int The ID of the second role player in the association
r2t int The ID of the topic that types the role played by the second role player.

Remarks

This view displays all possible combinations of two different association roles from all associations. Associations with only one association role will not appear in this view at all. Associations with N roles will be represented by (N-1)2 rows representing all possible combinations of two roles in that association. The result of this redundancy is that it is possible to query for an association by role player or role type without concern for whether it appears as the first or second role in this table (as it will appear as both).

View : tm_assocScope

Summary

Binds an association with each of the topics in its scope.

View Description

Column Type Description
topicmap int The ID of the containing topic map
assoc_id int The ID of the association.
scoping_topic_id int The ID of a topic that is in the scope of the association.

Remarks

This view contains one row for each topic in the scope of each association in the system. An association which is unscoped will not appear in this view at all. An association with N topics in its scope will appear in the view N times (once for each scoping topic).

View : tm_classHierarchy

Summary

Binds a topic with its subclass(es).

View Description

Column Type Description
topicmap int The identifier of the topic map containing the topics.
superclass_id int The identifier of the superclass topic
subclass_id int The identifier of the subclass topic
association_id int The identifier of the association that binds the topics together

View : tm_directInstanceOf

Summary

Binds a topic with the topic(s) which define its type.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the topic that is the instance
type_id int The ID of the topic that types the instance

Remarks

This view only presents the direct types of the topic. To query topic types taking the XTM-defined superclass-subclass association into account, use the function tm_instanceOf

View : tm_name

Summary

Binds a topic with its names.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the containing topic
name_id int The ID of the topic name

Remarks

This view contains one row for each topic name in the system and provides access to the container hierarchy of the topic name.

To extract the string value of the name, use the view tm_nameValue.

View : tm_nameScope

Summary

Binds a topic name with the topics in the scope of the topic name.

View Description

Column Type Description
name_id int The ID of the topic name
scoping_topic_id int The ID of a topic in the scope of the topic name

Remarks

This view contains one row for each topic in the scope of each topic name in the system. A topic name which is unscoped will not appear in this view at all. A topic name with N topics in its scope will appear in the view N times (once for each scoping topic).

View : tm_nameValue

Summary

Binds a topic with its names and their string values.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the containing topic
name_id int The ID of the topic name
name_value varchar(512) The string value of the topic name

Remarks

This view contains one row for each topic name in the system and provides access to the container hierarchy for the topic name and the string value of the topic name.

View : tm_nameVariant

Summary

Binds a topic with its names and name variants

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the containing topic
name_id int The ID of the containing topic name
variant_id int The ID of the variant name

Remarks

This view contains one row for each variant name in the system. The columns of the view provide access to the complete container hierarchy for the variant name.

To query variants by their value, use either the view tm_nameVariantValue to query string-valued variants or tm_nameVariantResource to query locator-valued variants.

View : tm_nameVariantResource

Summary

Binds a topic with a name, a variant of that name and the address pointed to by the variant.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the containing topic
name_id int The ID of the containing topic name
variant_id int The ID of the variant name
variant_resource varchar The address of the locator value of the variant

Remarks

This view contains one row for each variant name in the system with a non-null locator value. The columns of the view provide access to the complete container hierarchy for the variant name and to the locator value of the variant name.

To query variants by their string value use the view tm_nameVariantValue.

View : tm_nameVariantValue

Summary

Binds a topic with a name, a variant of that name and the string value of the variant.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the containing topic
name_id int The ID of the containing topic name
variant_id int The ID of the variant name
variant_value varchar The string value of the variant

Remarks

This view contains one row for each variant name in the system with a non-null string value. The columns of the view provide access to the complete container hierarchy for the variant name and to the string value of the variant name.

To query variants by their locator value use the view tm_nameVariantResource.

View : tm_npclAssociationRoleConstraints

Summary

Returns information on all association role cosntraints defined in the specified topic map.

View Description

Column Type Description
topicmap int The identifier of the topic map that contains the association role constraint definition.
association_type_id int The identifier of the topic that defines the association type that the association role constraint applies to.
constraint_id int The identifier of the topic that defines the association role constraint.
mincard ntext The value of the minimun cardinality facet for the association role constraint.
maxcard ntext The value of the maximum cardinality facet for the association role constraint.
role_type_id int The identifier of the topic that defines the type of role allowed by the association role constraint.

View : tm_npclExtensionValues

Summary

Extracts all occurrences which meta-typed as NPCL extensions.

View Description

Column Type Description
topicmap int The identifier of the topic map containing the extension.
item_id int The identifier of the topic that the extension applies to.
extension_type nvarchar(440) The extension type identifier.
extension_value ntext The extension value.

View : tm_npclOccurrenceConstraints

Summary

Binds all occurrence constraints defined in the topic maps to the topic type and occurrence type that are constrained, the minimum and maximum cardinality of the constraint and the name and subject identifier for the constraint topic itself.

View Description

Column Type Description
topic_type_id int The identifier of the topic that defines the topic type that the occurrence constraint applies to
constraint_id int The identifier of the topic that defines the occurrence constraint.
constraint_name nvarchar(512) The display name for the occurrence constraint.
constraint_psi nvarchar(440) The subject identifier for the occurrence constraint.
mincard ntext The minimum cardinality for the occurrence constraint. This value should be interpreted as a non-negative integer.
maxcard ntext The maximum cardinality for the occurrence constraint. This value should be interpreted as either the string 'unbounded' or a non-negative integer.
occur_type_id int The identifier of the topic that defines the occurrence type that this occurrence constraint allows.

View : tm_npclRolePlayerConstraints

Summary

Returns information on all NPCL role player constraints defined in the specified topic map.

View Description

Column Type Description
topicmap int The identifier of the topic map that contains the role player constraint.
topic_type_id int The identifier of the topic that defines the topic type that the role player constraint applies to.
constraint_id int The identifier of the topic that defines the role player constraint.
mincard ntext The value of the minimum cardinality facet for the role player constraint.
maxcard ntext The value of the maximum cardinality facet for the role player constraint.
role_type_id int The identifier of the topic that defines the role type allowed by the role player constraint.
assoc_type_id int The identifier of the topic that defines the association type allowed by the role player constraint.

View : tm_npclScoping

Summary

A view that binds topic type identifier to the scoping facet value for that topic type.

View Description

Column Type Description
topicmap int The identifier of the topic map that contains the topic type.
topic_type_id int The identifier of the topic that defines the topic type.
scoping_facet ntext The value of the NPCL scoping facet for the topic type.

Remarks

View : tm_npclTypes

Summary

Returns all instances of the type identified by @psi bound to a display name and their subject identifier(s).

View Description

Column Type Description
topicmap int the id of the topic map containing the type
type_id int the id of the topic representing the type
type_name nvarchar(512) the display name for the topic
type_psi nvarchar(440) the subject identifier for the topic
meta_type_psi nvarchar(440) the meta-type subject identifier for the topic
is_abstract nvarchar(10) the value of the Abstract Facet for the type. Expected values are 'true' and 'false'. NOTE: The value of this column has been changed for TMCore07 SP4 from an ntext column to an nvarchar(10) column.

Remarks

View : tm_npclValueFacets

Summary

Returns all instances of the type identified by @psi bound to a display name, their subject identifier(s) and value type facet values.

View Description

Column Type Description
topicmap int The identifier of the topic map containing the facets
item_id int the id of the topic that the facets apply to
item_name nvarchar(512) the display name for the topic
item_psi nvarchar(440) the subject identifier for the topic
datatype ntext the value of the value datatype facet for the item
min_value ntext the value of the minimum value facet for the item
max_value ntext the value of the maximum value facet for the item
value_pattern ntext the value of the value pattern facet for the item

Remarks

View : tm_occur

Summary

Binds a topic with each of its occurrences and the occurrence type only.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the containing topic
occur_id int The ID of the occurrence
occur_type int The ID of the topic that types the occurrence

Remarks

This view contains one row for each occurrence in the system. The view provides access to the container hierarchy for the occurrence and to the type of the occurrence. To access the string or locator value of the occurrence, use either tm_occurValue for string-valued occurrences or tm_occurResource for locator-valued occurrences.

View : tm_occurResource

Summary

Binds a topic with an occurrence, the occurrence type and the address of the resource referenced by the occurrence.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the containing topic
occur_id int The ID of the occurrence
occur_type int The ID of the topic that types the occurrence
occur_resource varchar(512) The address of the occurrence locator value

Remarks

This view contains one row for each occurrence in the system with a non-null resource reference locator. The view provides access to the container hierarchy of the occurrence, the occurrence type and the address pointed to by the locator value of the occurrence.

To query occurrences with a string value use the view tm_occurValue.

View : tm_occurScope

Summary

Binds an occurrence with the topics in the scope of the occurrence.

View Description

Column Type Description
occur_id int The ID of the occurrence
scoping_topic_id int The ID of a topic in the scope of the occurrence

Remarks

This view contains one row for each topic in the scope of each occurrence in the system. An occurrence which is unscoped will not appear in this view at all. An occurrence with N topics in its scope will appear in the view N times (once for each scoping topic).

View : tm_occurValue

Summary

Binds a topic with and occurrence, the occurrence type and the string value of the occurrence.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the containing topic
occur_id int The ID of the topic occurrence
occur_type int The ID of the topic that types the occurrence
occur_value ntext The string value of the occurrence

Remarks

This view contains one row for each occurrence in the system with a non-null string value. The view provides access to the container hierarchy for the occurrence, the type of the occurrence and the string value of the occurrencce.

To query occurrences with a locator value use the view tm_occurResource.

View : tm_si

Summary

Binds a topic to its subject identifiers.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the topic
subj_id varchar(512) The subject identifier address string

Remarks

This view contains one row for each subject identifier in each separate topic map in the system.

Because this view spans multiple topic maps, it is possible for the view to contain multiple rows with the same subj_id column value, but the combination of subj_id and topicmap must be unique due to topic map merging constraints.

View : tm_sl

Summary

Binds a topic to its subject locators.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int the ID of the topic
subj_loc varchar(512) the subject locator address string

Remarks

This view contains one row for each topic subject locator in each separate topic map in the system.

Because this view spans multiple topic maps, it is possible for the view to contain multiple rows with the same subj_loc column value, but the combination of subj_loc and topicmap must be unique due to topic map merging constraints.

View : tm_src

Summary

Binds topic map objects with their source locator(s) and a code indicating the type of topic map object.

View Description

Column Type Description
sl varchar(512) the value of the object source locator
id int the id of the object with the source locator sl
tm int the id of the topic map that contains the object
object_type char A single character object type code. The following codes are used:
  • A = Association
  • R = Association Role
  • N = Topic Name
  • O = Occurrence
  • T = Topic
  • M = Topic Map
  • V = Variant Name

Remarks

The view contains one row for each source locator in each topic map managed by the system.

View : tm_topic

Summary

Binds a topic with the topics that define its types.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int The ID of the topic.
type_id int The ID of the topic type.

Remarks

This view contains one row for each type of each topic in the TMCore System. Untyped topics are represented by a single row with the type_id column set to NULL.

View : tm_topicmap

Summary

Binds a topic map id to its name.

View Description

Column Type Description
topicmap int The identifier of the topic map.
topicmap_name nvarchar(512) The name of the topic map.

Remarks

This view contains one row for each topic map in the system.

View : tm_tsrc

Summary

Binds a topic to its source locators.

View Description

Column Type Description
topicmap int The ID of the containing topic map
topic_id int the ID of the topic
src_loc varchar(512) the source locator address string

Remarks

This view contains one row for each topic source locator in each separate topic map in the system.

Because this view spans multiple topic maps, it is possible for the view to contain multiple rows with the same src_loc column value, but the combination of src_loc and topicmap must be unique due to topic map merging constraints.

View : tm_variantScope

Summary

Binds a variant name with the topics in the scope of the variant name.

View Description

Column Type Description
variant_id int the id of the variant name
scoping_topic_id int the id of a topic in the scope of the variant name

Remarks

This view contains one row for each topic in the scope of each variant name in the system. A variant name which is unscoped will not appear in this view at all. A variant name with N topics in its scope will appear in the view N times (once for each scoping topic).



Function Summary

Function Description
tm_displayName Returns a display string for a topic selected from the topics variant names and topic names.
tm_instanceOf Binds a topic to the classes that it is an instance of.
tm_sortName Returns a sort string for a topic selected from the topics variant names and topic names.
tm_subclasses Binds a topic representing a class to the topics that represent all subclasses of that class.
tm_superclasses Binds a topic representing a class to the topics that represent all subclasses of that class.
tm_tc Calculates and returns the transitive closure formed by walking an association from a starting point traversing specific types of association and association role.
tm_tc2 Calculates and returns the transitive closure formed by walking an association from a starting point traversing specific types of association and association role. This version of the function returns each node in the transitive closure with its parent in the transitive closure path.
tm_tc3 Calculates and returns the transitive closure formed by walking an association from a starting point traversing specific types of association and association role. This version of the function returns each node in the transitive closure with its parent in the transitive closure path.

Function : tm_displayName

Summary

Returns a display string for a topic selected from the topics variant names and topic names.

Input Parameters:

Name Type Description
@topic int the ID of the topic to return the display name for

Returns

The string value of the display name for the topic or NULL if no suitable name was found.

Remarks

This function applies the following algorithm:

  1. If the topic has a variant name with a scope including a topic with the subject identifier http://www.topicmaps.org/xtm/1.0/core.xtm#display, return the string value of that variant name.
  2. If the topic has a topic name with a scope including a topic with the subject identifier http://www.topicmaps.org/xtm/1.0/core.xtm#display, return the string value of that topic name.
  3. If the topic has a topic name that is not scoped, return the string value of that topic name.
  4. Return the string value of a topic name of the topic.

If any of those steps match multiple names, then the oldest item is returned. If none of the above steps match any names, NULL is returned.

Function : tm_instanceOf

Summary

Binds a topic to the classes that it is an instance of.

Input Parameters:

Name Type Description
@topicmap int The topic map to query
@topic_id int the ID of the instance topic
@class_id int the ID of the class

Return Table

Column Type Description
topic_id int The ID of the instance topic
class_id int The ID of the class

Remarks

This function observes the XTM standard superclass/subclass association.

If the topic_id parameter is not NULL, and the class_id parameter is NULL, then this method returns all direct types and supertypes of the direct types of the topic_id.

If the topic_id parameter is NULL and the class_id parameter is not NULL, then this method returns all instances of class_id and all instances of all subclasses of class_id.

If both topic_id and class_id are not NULL, this method returns a single row containing topic_id and class_id if and only if class_id is a direct type of topic_id or is a supertype of a direct type of topic_id.

If both topic_id and class_id are NULL, this method calculates all direct types and superclasses for all topics in the topic map identified by topicmap. In this mode, the operation has the potential to be highly resource-intensive and so use should be avoided where possible.

Function : tm_sortName

Summary

Returns a sort string for a topic selected from the topics variant names and topic names.

Input Parameters:

Name Type Description
@topic int the ID of the topic to return the sort name for

Returns

The string value of the sort name for the topic or NULL if no suitable name was found.

Remarks

This function applies the following algorithm:

  1. If the topic has a variant name with a scope including a topic with the subject identifier http://www.topicmaps.org/xtm/1.0/core.xtm#sort, return the string value of that variant name.
  2. If the topic has a topic name with a scope including a topic with the subject identifier http://www.topicmaps.org/xtm/1.0/core.xtm#sort, return the string value of that topic name.
  3. If the topic has a topic name that is not scoped, return the string value of that topic name.
  4. Return the string value of any topic name of the topic.

If any of those steps match multiple names, then the oldest name is returned. If none of the above steps match any names, NULL is returned.

Function : tm_subclasses

Summary

Binds a topic representing a class to the topics that represent all subclasses of that class.

Input Parameters:

Name Type Description
@TopicMap int the topic map to be queried
@cls int the base class to find all subclasses of

Return Table

Column Type Description
subclass int the ID of the topic that is a direct or indirect subclass of @cls

Remarks

This function uses the standard XTM-defined superclass-subclass association and association role types to return all subclasses of the topic identified by the parameter cls. The return from this function is a table with a single column listing the ID of each topic which is either a direct subclass of cls (i.e. plays the role 'subclass' in a 'superclass-subclass' association with cls playing the role 'superclass') or an indirect subclass (i.e. it is a subclass of a subclass of cls).

Function : tm_superclasses

Summary

Binds a topic representing a class to the topics that represent all subclasses of that class.

Input Parameters:

Name Type Description
@TopicMap int the topic map to be queried
@cls int the base class to find all superclasses of

Return Table

Column Type Description
superclass int the ID of the topics that are a direct or indirect superclass of @cls.

Remarks

This function uses the standard XTM-defined superclass-subclass association and association role types to return all superclasses of the topic identified by the parameter cls. The return from this function is a table with a single column listing the ID of each topic which is either a direct superclass of cls or an indirect superclass (i.e. it is a superclass of a superclass of cls).

Function : tm_tc

Summary

Calculates and returns the transitive closure formed by walking an association from a starting point traversing specific types of association and association role.

Input Parameters:

Name Type Description
@START int The ID of the topic to start the transitive closure from.
@ASSOCTYPE int The ID of the topic defining the type of association to be traversed.
@FROMROLETYPE int The ID of the topic defining the type of association role to be walked *from* the edge of the transitive closure.
@TOROLETYPE int the ID of the topic defining the type of the association role to be walked *to* across the edge of the transitive closure.

Return Table

Column Type Description
start_id int the id of the start topic in the transitive closure path
to_id int the id of the end topic in the transitive closure path

Remarks

A transitive closure is computed as a set of paths starting with START. Each step in the path is found by traversing an association role of type FROMROLETYPE to an association of type ASSOCTYPE and traversing an association role of type TOROLETYPE to another topic.

Each row in the return table represents one path in the transitive closure. Only the starting point and the end point of the path are included in the results - intermediate topics are ommitted.

This function is particularly useful for traversing hierarchies of related topics.

Function : tm_tc2

Summary

Calculates and returns the transitive closure formed by walking an association from a starting point traversing specific types of association and association role. This version of the function returns each node in the transitive closure with its parent in the transitive closure path.

Input Parameters:

Name Type Description
@START int The ID of the topic to start the transitive closure from.
@ASSOCTYPE int The ID of the topic defining the type of association to be traversed.
@FROMROLETYPE int The ID of the topic defining the type of association role to be walked *from* the edge of the transitive closure.
@TOROLETYPE int the ID of the topic defining the type of the association role to be walked *to* across the edge of the transitive closure.

Return Table

Column Type Description
from_id int the id of the parent topic in the transitive closure path
to_id int the id of the end topic in the transitive closure path

Remarks

A transitive closure is computed as a set of paths starting with START. Each step in the path is found by traversing an association role of type FROMROLETYPE to an association of type ASSOCTYPE and traversing an association role of type TOROLETYPE to another topic.

Each row in the return table represents one path in the transitive closure. Only the starting point and the end point of the path are included in the results - intermediate topics are ommitted.

This function is particularly useful for traversing hierarchies of related topics.

Function : tm_tc3

Summary

Calculates and returns the transitive closure formed by walking an association from a starting point traversing specific types of association and association role. This version of the function returns each node in the transitive closure with its parent in the transitive closure path.

Input Parameters:

Name Type Description
@START int The ID of the topic to start the transitive closure from.
@ASSOCTYPES nvarchar(500) The ID of the topic defining the type of association to be traversed.
@FROMROLETYPES nvarchar(500) The ID of the topic defining the type of association role to be walked *from* the edge of the transitive closure.
@TOROLETYPES nvarchar(500) the ID of the topic defining the type of the association role to be walked *to* across the edge of the transitive closure.

Return Table

Column Type Description
from_id int the id of the parent topic in the transitive closure path
to_id int the id of the end topic in the transitive closure path

Remarks

A transitive closure is computed as a set of paths starting with START. Each step in the path is found by traversing an association role of type FROMROLETYPE to an association of type ASSOCTYPE and traversing an association role of type TOROLETYPE to another topic.

Each row in the return table represents one path in the transitive closure. Only the starting point and the end point of the path are included in the results - intermediate topics are ommitted.

This function is particularly useful for traversing hierarchies of related topics.