Adding or Removing Indexed Fields
Indexed fields are used to optimize searching the database. They are defined as a constraint on the related column in the database table. Both single and composite indexes are supported.
Adding an Indexed Field
To add an indexed field to an existing database table:
1. Create a new building block that extends from the PTC building block which contains the manager for the entity that is related to the database table. These managers are specified in the DefaultGlobalManagerConfiguration table on the Configuration page of the PTC.Base.Manager. For example, to add an indexed key to a job order, navigate to the PTC.JobOrderImpl.Manager Thing.
For more information, see Create a New Building
Block.
2. Navigate to the manager Thing for your new building block.
3. Under Services, find and override the GetDBInfo service for the entity to which you are adding the indexed field. For example, to add an indexed field to the database table for job orders, find and override the GetJobOrderDBInfo service.
4. In the script editor, scroll down to the entry for the Data Shape to which you want to add the indexed field.
5. Add an indexedFields array for the Data Shape, with the following properties:
◦ unique—Specify if the column must have a unique value.
◦ fieldnames—Array containing the names of the columns. If only one column is specified, a single index is created. If multiple columns are specified, a composite index is created.
 |
A single index can be created by specifying name with a single value that is the name of the column, instead of specifying a single value for fieldnames. If both name and fieldnames are specified, name is ignored and the index is created using the fieldnames values.
|
◦ identifier—The name of the entity in the database. If not specified, then the system automatically generates the value in the format <table_name>_<column_name1>_<column_name2>_<column_nameN>_idx. If specified, then the value must be unique for both specified values and any automatically generated values. The maximum length for the value is the maximum length allowed by the database for identifiers.
6. Click Save to save the changes to the service.
7. Navigate to the PTC.DBConnection.Manager Thing.
8. Synchronize the database information with the database schema. For more information, see Synchronizing the Database Information and the Database Schema.
Removing an Indexed Field
To remove an indexed field:
|
|
Do not remove any indexed fields that are defined out-of-the-box. Remove only indexed fields that you have previously added, for example by Adding Custom Properties to Data Model Entities or Adding Custom Entities to the Data Model.
|
1. Create a new building block that extends from the PTC building block which contains the manager for the entity that is related to the database table. These managers are specified in the DefaultGlobalManagerConfiguration table on the Configuration page of the PTC.Base.Manager. For example, to remove an indexed field from a job order, navigate to the PTC.JobOrderImpl.Manager Thing.
For more information, see Create a New Building
Block.
2. Navigate to the database Thing configured for your system, for example, PTC.DBConnection.MSSQLDatabase.
3. Under Services, execute the RemoveIndex service with the following inputs:
◦ dataShapeName—The Data Shape for the database table from which you are removing the indexed field constraint. Required.
◦ fieldName—The field on the Data Shape from which you are removing the indexed field constraint. This input is required only when removing a single indexed field.
◦ dbInfo—The JSON description of the indexed field constraint to be removed. The same identifier (if any) and name information that is specified in the GetDBInfo service where the indexed field is defined must be provided. This input is required when removing a composite index.
4. Click Execute.
5. Navigate to the GetDBInfo service where the indexed field is defined, and remove the indexed field definition from the services. If this is not removed, then the indexed field is added back to the database the next time the synchronization services are run.
For example, to remove an indexed field constraint with a specified identifier from a field named WorkDefinitionUID on the MyObject Data Shape, specify the following for the input parameters:
• dataShapeName—MyObject
• fieldName—WorkDefinitionUID
• dbInfo—
{
"identifier":"myobject_workdefinitionuid_idx"
"name":"WorkDefinitionUID",
}
"identifier":"myobject_workdefinitionuid_idx"
"name":"WorkDefinitionUID",
}