Sys.Registration.Metadata.Writer

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

Providing you have sufficient access control permissions, the Sys.Registration.Metadata.Writer provider enables you to write a Luminesce SQL query that creates or updates metadata for providers and custom views in Luminesce.

You must construct a valid table of data to write, one provider per record. Sys.Registration.Metadata.Writer lists the fields (columns) available to populate with values for each record, and has a set of parameters to help you construct a valid table.

See also: Sys.Registration.Metadata, Sys.Registration

Basic usage

@table_of_data = <select-statement>;
select * from Sys.Registration.Metadata.Writer where toWrite = @table_of_data;

Query parameters

Sys.Registration.Metadata.Writer has parameters that help you construct a valid table of data to write.

Note: The ToWrite parameter is mandatory and describes the metadata you are creating.

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 = 'Sys.Registration.Metadata.Writer' and FieldType = 'Parameter';

Data fields

Sys.Registration.Metadata.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 the table of data:

Operation	Mandatory fields
Upsert, Delete	ProviderName MetadataKey MetadataValue

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 = 'Sys.Registration.Metadata.Writer' and FieldType = 'Column';

Examples

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

Example 1: Write metadata for an existing provider

In this example, Sys.Registration.Metadata.Writer is used to store some metadata keys and values for a custom view Views.MaturityDaysToExpiry. By default, Sys.Registration.Metadata.Writer returns all metadata for all providers, so a ProviderName is specified. Note you can only add metadata to a provider after it has been created. 

@data_to_write = select 
  'Views.MaturityDaysToExpiry' as ProviderName, 
  'InstrumentExpiryAlgo' as MetadataKey,
  'True' as MetadataValue;

select * from Sys.Registration.Metadata.Writer where ToWrite = @data_to_write and ProviderName = 'Views.MaturityDaysToExpiry';

The query returns a table of data that contains metadata for the provider.

Example 2: Update metadata for a provider

In this example, a metadata value is updated by specifying 'Delete' as WriteAction for the current value and 'Upsert' as WriteAction for the new value. 

@vals = values
('True', 'Delete'),
('False', 'Upsert');
@table_of_data = select 'Views.MaturityDaysToExpiry' as ProviderName, 'InstrumentExpiryAlgo' as MetadataKey, column1 as MetadataValue, column2 as WriteAction from @vals;
select * from Sys.Registration.Metadata.Writer where ToWrite = @table_of_data and ProviderName = 'Views.MaturityDaysToExpiry';

Example 3: Delete metadata from a provider

In this example, 'Delete' as WriteAction is added to the ToWrite table of data to delete some metadata.

@data_to_write = select 
  'Views.MaturityDaysToExpiry' as ProviderName,
  'InstrumentExpiryAlgo' as MetadataKey,
  'False' as MetadataValue,
  'Delete' as WriteAction; 

select * from Sys.Registration.Metadata.Writer where ToWrite = @data_to_write;

Example 4: Write metadata to multiple providers at the same time

In this example, the query defaults to an upsert operation as no explicit WriteAction is specified.

@vals = values
('RebalanceAlgo.RemoveSmallPositions'),
('RebalanceAlgo.IncreaseEquity'),
('RebalanceAlgo.IncreaseBond');
@table_of_data = select column1 as ProviderName, 'RebalanceAlgo' as MetadataKey, 'True' as MetadataValue from @vals;
select * from Sys.Registration.Metadata.Writer where ToWrite = @table_of_data;