Lusid.CustomEntity.Definition.Writer

Type	Read/write	Author	Availability
Data provider	Write	Finbourne	Provided with LUSID

The Lusid.CustomEntity.Definition.Writer provider enables you to write a Luminesce SQL query that creates or updates a custom entity type in LUSID.

Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and interact with custom entity types in LUSID. This should automatically be the case if you are the domain owner.

You can use this provider to help create dedicated read and write Luminesce providers for your custom entities. Find out more about this.

You must construct a valid table of data to write, one record per user-specified data field in the custom entity type. Lusid.CustomEntity.Definition.Writer lists the fields (columns) available to populate with values for each record, and has parameters to help you construct a valid table.

Your query can use the WriteAction field to perform one of the following operations:

• Upsert a custom entity type; that is, create the type if it does not exist, and update it if it does. This is the default operation if you omit WriteAction.
• Insert a new custom entity type.
• Update an existing custom entity type.

Note the following:

• You can update any aspect of a custom entity type but note this is a risky operation if custom entities of the type already exist.
• There is currently no mechanism for deleting a custom entity type.

See also: Lusid.CustomEntity.Definition

Basic usage

@table_of_data = <select-statement>;
select * from Lusid.CustomEntity.Definition.Writer where ToWrite = @table_of_data;

Query parameters

Lusid.CustomEntity.Definition.Writer has parameters to help you construct a valid table of data to write.

Note: The ToWrite parameter is mandatory and used to actually write data to LUSID.

To list available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.CustomEntity.Definition.Writer' and FieldType = 'Parameter';

Data fields

Lusid.CustomEntity.Definition.Writer lists the fields you can populate in your table of data to write. 

Depending on the operation you want to perform, the following fields are mandatory to include in your table of data:

Operation	Specify using...	Mandatory fields	Notes
Upsert	'Upsert' as WriteAction (or omit)	EntityTypeName, DisplayName, Description, FieldName, FieldLifeTime (either Perpetual or TimeVariant), FieldType (either String, Boolean, DateTime or Decimal), FieldCollectionType (either Single or Array), FieldRequired (either True or False)	The EntityTypeName, DisplayName and Description built-in data fields must be the same for each record. The Field* user-specified data fields are specific to each record.
Insert	'Insert' as WriteAction
Update	'Update' as WriteAction

To list all available fields, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.CustomEntity.Definition.Writer' and FieldType = 'Column';

Write errors

We recommend examining the results of every write query using one or more of the WriteError, WriteErrorCode and WriteErrorDetail fields.

For each record in the table of data to write, Lusid.CustomEntity.Definition.Writer returns an error code. If the operation is successful, the error code is 0. If unsuccessful, a positive error code and explanation help you discover why LUSID considers the operation invalid.

Examples

Note: For more example Luminesce SQL queries, visit our Github repo.

Example 1: Create a custom entity type with a single user-specified data field

@table_of_data = select 'Office' as EntityTypeName, 'Office location' as DisplayName,
'An office or branch location' as Description, 'address' as FieldName,
'Perpetual' as FieldLifeTime, 'String' as FieldType, 'Single' as FieldCollectionType,
True as FieldRequired, 'The postal address of the location' as FieldDescription;

select * from Lusid.CustomEntity.Definition.Writer where toWrite = @table_of_data; 

Example 2: Create a custom entity type with multiple user-specified data fields

@data_fields = values
('address', 'Perpetual', 'String', 'Single', True, 'The postal address of the location'),
('seatingCapacity', 'TimeVariant', 'Decimal', 'Single', False, 'The seating capacity of the location'),
('isHeadOffice', 'TimeVariant', 'Boolean', 'Single', True, 'Whether or not the location is a head office'),
('amenities', 'TimeVariant', 'String', 'Array', False, 'A list of facilities for staff');

@table_of_data = select 'Office' as EntityTypeName, 'Office location' as DisplayName,
'An office or branch location' as Description, column1 as FieldName,
column2 as FieldLifeTime, column3 as FieldType, column4 as FieldCollectionType,
column5 as FieldRequired, column6 as FieldDescription from @data_fields;

select * from Lusid.CustomEntity.Definition.Writer where toWrite = @table_of_data;

Example 3: Update the description of a custom entity type

Note: Updating the data fields of a custom entity type is a risky operation if custom entities of the type already exist.

You must specify all the built-in data fields for the custom entity type and for one of its user-specified data fields, retaining existing values for all except Description.

@data_fields = values
('address', 'Perpetual', 'String', 'Single', True, 'The postal address of the location');

@table_of_data = select 'Office' as EntityTypeName, 'Office location' as DisplayName,
'THIS IS A NEW DESCRIPTION' as Description, column1 as FieldName,
column2 as FieldLifeTime, column3 as FieldType, column4 as FieldCollectionType,
column5 as FieldRequired, column6 as FieldDescription, 'Update' as WriteAction from @data_fields;

select * from Lusid.CustomEntity.Definition.Writer where toWrite = @table_of_data;