There are two ways you might want to interact with custom database objects via the API. First, if you have a local table or view in the Tessitura database from which you'd like to insert, get or delete data, you can easily do that using the /custom resource. In this case, no stored procedure is necessary. Second, you can execute custom business logic in the database using /custom/execute resource. The Custom resource is designed to allow service exposure of custom tables, views, and stored procedures. Custom resources are designed to augment the existing API by allowing additional entities and processes to be exposed in a manner similar to standard API resources.
Tables registered can be used similar to other CRUD resources in the API. Registered views provide a GET (readonly) resource. Local stored procedures allow more complex operations passed a set of parameters with one or more returning data sets.
Setup for custom resources is done in the System Tables window of the client application or directly in the database. All tables mentioned in this help document can be edited directly using the System Tables editor.
Custom resources can be accessed by registering a standard table or view, a custom table or view in TR_DATASERVICE_TABLES. Typically the registration involves providing friendly names that do not follow the naming convention requirements of the Tessitura database. For example a local table LT_STUDENT or view LVS_STUDENT would be registered with a collection name (resource name) of Students and a singular (entity) name of Student. The singular and plural service exposure names can be defined in TR_DATASERVICE_TABLES. By default columns in the table or view are named exactly as they appear in the database. To adjust these names, the property name on the custom entity for a column can be defined in TR_DATASERVICE_COLUMNS. Note that the entity only needs to be defined in TR_DATASERVICE_TABLES.
TR_DATASERVICE_COLUMNS is an important reference table used in more advanced configuration of properties for a custom resource.
For create (POST) and update (PUT), property values can be validated against a custom SQL query.
For example, the custom table LT_EDUCATION_CLASSES.semester_id has a foreign key reference to LT_SEMESTER.id. To restrict the values that could be in the column to semesters where the year > 2019, the following configuration could be provided in TR_DATASERVICE_COLUMNS:
[0-9]
/\b[\w\.-]+@[\w\.-]+\.\w{2,4}\b/gi/(19|20)\d\d-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])/g
[^-\s]
The sub-entity column in TR_DATASERVICE_TABLES can be used to add sub-entities to an exposure. For example if you create two custom entities, one called EducationSemesters and one called EducationClasses and you wanted to see all of the Classes for each Semester in the EducationSemesters resource you would:
You can enter a comma separated list of values if you want more than one subentity to appear for an entity.
In TR_DATASERVICE_TABLES you can also indicate whether the entity is control-grouped and how:
Creating data via sub-entities is possible on a POST but not on PUT. Using the above example, EducationClasses could be created in a collection property in a single EducationSemester request to POST Custom/EducationSemesters. Using PUT to update a sub-entity collection under a custom entity is not supported. If a PUT request is made to a custom resource, any sub-entity collection submitted in the request are simply ignored, but the response will provide a refreshed collection from the service. After an initial create (POST), sub-entities should be edited using their specific resource directly. For example, instead of editing EducationClasses through a collection on Custom/EducationSemesters, individual EducationClasses can be updated through a PUT to Custom/EducationClasses.
Note that if a table exposed in TR_DATASERVICE_TABLES will have rows added (a POST method will be called) the service needs to know how to assign a next id value. In TR_DATASERVICE_TABLES there are two columns to control this:
Next_Id_Type: If you choose “Next_ID Counter” as the update method, you just select a Next_ID_Type from the list of possible values in the T_NEXT_ID table.
Be very careful when doing this as you can interfere with data being written to other tables.
In most cases if you use this method you will be selecting ‘CT’, the custom table next_id type.
| Next Id Method | Value | NextI Id Type |
|---|---|---|
| Max + 1 | 1 | Not Applicable |
| Next_Id_Counter | 2 | Default is ‘CT’. Otherwise any type from T_NEXT_ID table. |
| Identity | 3 | Not Applicable |
The Custom area has two special resources to allow the execution of registered stored procedures. Stored procedures can optionally perform data manipulation or return a data set. Custom/Execute (POST) will return the first result set (if present) of a custom stored procedure. Custom/Execute/MultipleResultSets (POST) permits multiple result sets, using a slightly different response contract. Both resources accept a LocalProcedureRequest entity, which defines which procedure and any parameters provided.
Local stored procedures are registered in TR_LOCAL_PROCEDURE.
Requests to both Custom/Execute and Custom/Execute/MultipleResultSets accept a LocalProcedureRequest.
<LocalProcedureRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<ParameterValues>
<ParameterValue>
<Name>param1</Name>
<Value>abcdef</Value>
</ParameterValue>
<ParameterValue>
<Name>param2</Name>
<Value>2019-04-01</Value>
</ParameterValue>
</ParameterValues>
<ProcedureId>1</ProcedureId>
<ProcedureName>LP_CUSTOM_PROCEDURE</ProcedureName>
</LocalProcedureRequest>
{
"ProcedureId": 1,
"ProcedureName": "sample string 1",
"Parameters": "sample string 2",
"ParameterValues": [{
"Name": "sample string 1",
"Value": "sample string 2"
}, {
"Name": "sample string 1",
"Value": "sample string 2"
}
]
}