Using Sql.Db.Mine to help end users read from/write to SQL databases

You can request a Sql.Db.Mine provider from FINBOURNE and run it locally to create and publish a custom provider in your LUSID domain that is able to read from, write to, or configure your own SQL database.

Note: You can connect to the following databases: MySql, Odbc, Oracle, PostgreSQL, SqLite, SqlServer, GoogleBigQuery, AwsAthena, Db2, Snowflake.

The goal is to enable end users to write simplified or more performant queries using the custom provider available from your domain's Luminesce catalog rather than interacting with the database directly, which they might not have the knowledge, infrastructure or permissions to do.

Note: To query your custom provider, an end user must have a valid user account in your LUSID domain, and suitable access control permissions to use that custom provider. More on permissions.

Consider the example of a CustomProviders.RetrieveInternalTransactionData custom provider you publish that:

• Retrieves particular transaction data from your database in a highly performant manner, for example instrument names, unique identifiers, settlement dates, quantities and prices.
• Calculates the cost of each transaction on-the-fly by multiplying quantity by price.

An end user can then write a simple Luminesce SQL query that retrieves data from your SQL database and processes it, perhaps by upserting it to LUSID. For example, the following query uses CustomProviders.RetrieveInternalTransactionData to retrieve all the transactions that occurred on 22 April 2022, and then the Lusid.Portfolio.Holding.Writer provider (supplied by FINBOURNE) to set them as holdings in LUSID.

Note: The column names retrieved from your database must match those expected by the FINBOURNE provider (hence some have been renamed).

@table_of_data = select
  'Finbourne-Examples' as PortfolioScope, 'UK-Equities' as PortfolioCode,
  SettleDate as EffectiveAt, Quantity as Units, Currency as CostCurrency, Cost as CostPrice,
  Figi,
  'Set' as WriteAction
from
  CustomProviders.RetrieveInternalTransactionData where SettleDate = #2022-4-22#;
select * from Lusid.Portfolio.Holding.Writer where toWrite = @table_of_data;

Contents

• Hosting the Sql.Db.Mine provider
• Connecting to your SQL database
• Understanding the Sql.Db.Mine provider syntax
• Reading from your SQL database
• Publishing your custom provider
• Managing your live custom provider
• Deleting your custom provider
   

Hosting the Sql.Db.Mine provider

To run Sql.Db.Mine locally:

1. Make sure your computer has .NET 6 installed.
2. As a LUSID user with the lusid-administrator role, navigate to https://<your-domain>.lusid.com/honeycomb/swagger/index.html, where <your-domain> is the root of your LUSID domain, for example acmecorp. If you do not have the lusid-adminstrator role, see the appendix.
3. Use Swagger's Try it out button for the DownloadBinary API to request the appropriate SqlDb_Providers_* binary, for example for PostGreSQL:
   
4. Click the Download file link in the response to download the binary to your browser's download location: 
   
5. Navigate to the download location and run an appropriate install command (omitting the .nupkg file extension), for example for PostgreSQL:

   C:\Users\JohnDoe\Download> dotnet tool install Finbourne.Luminesce.DbProviders.PostgreSql -g --add-source "."

   • Windows: %USERPROFILE%\.dotnet\tools\.store\
   • Linux/MacOS: $HOME/.dotnet/tools/.store/
6. Run the following command to start the service (omitting the .exe extension on Linux or MacOS):

   C:\Users\JohnDoe\.dotnet\tools> luminesce-database-providers.exe --quiet --authClientDomain=<your-domain> --certificateFallback=DownloadMayMint

   ...where <your-domain> is the root of your LUSID domain, for example --authClientDomain=acmecorp. Note the DownloadMayMint option mints a new certificate if you do not have one already; more information on managing certificates is coming soon.
    

   Note: For information on all the command line options, run the command luminesce-database-providers.exe --help.

7. When prompted in a browser window, authenticate to LUSID as a user with administrative privileges.

With the Sql.Db.Mine provider running locally, you can now write a Luminesce SQL query using any tool, for example the LUSID web app. Note the following:

• You may need to click the Refresh button in the web app catalog (highlighted in red) to see Sql.Db.Mine.
• FINBOURNE host a Sql.Db provider that may also appear in your catalog. Do not use this. Use Sql.Db.Mine.
• You can rename Sql.Db.Mine.

To stop Sql.Db.Mine, press CTRL-C. To start it again, rerun the luminesce-database-providers.exe command (it should be faster subsequent times). Your LUSID credentials are remembered for one hour, after which you must authenticate again.

To uninstall the package, perhaps in order to upgrade to a newer version, run the command dotnet tool uninstall Finbourne.Luminesce.DbProviders.PostgreSql -g.

Understanding access control permissions

You, and any other LUSID administrative user wishing to run queries against Sql.Db.Mine in order to publish custom providers for end users, must have access control permissions to use the Sql.Db.Mine provider. The general principle of creating policies for Luminesce providers, assigning them to roles and roles to users, is explained in this article. Note you should automatically have permission if you are the LUSID domain owner with the built-in lusid-administrator role.

You, and any other LUSID administrative user wishing to run queries, should also have access control permissions to use connection string aliases in order to connect Sql.Db.Mine to your SQL database.

Your end users should not have access control permissions to use Sql.Db.Mine. Instead, they require access control permissions just for those published custom providers you wish them to use.

Renaming Sql.Db.Mine

You can optionally rename the Mine suffix (though not the Sql.Db prefix). To do this, open <install-loc>/api-settings.json and change:

"ProviderNameFormat": "{0}.Mine",

to (for example):

"ProviderNameFormat": "{0}.Oracle",

The new name is reflected in the Luminesce catalog the next time you sign in. Note you will need to restart Sql.Db.Mine if you change this file while the service is running.

Connecting to your SQL database

You can connect to any supported SQL database for which you have a valid connection string.

You can hard-code the connection string in the provider (using the --connection option), and this might be more convenient when creating and testing your provider.

For publication, however, we recommend creating a connection string alias in <install-loc>/database-provider-settings.json, for example:

"ConnectionStrings":
 [
   {
     "Name":"MyDb",
     "ConnectionString":"DataSource=./Tests/TestData/sakila.sqlite",
     "LicenceCode": "honeycomb-standard-access"
   }
 ], 

You can then use the alias Name with the --connection option. Note you will need to restart Sql.Db.Mine if you change this file while the service is running.

To connect via aliases you must also create a policy consisting of the following JSON, assign it to a suitable role, and assign that role to your LUSID user (or any LUSID administrative user wishing to run queries against Sql.Db.Mine):

{
   "description": "Enable the Sql.Db.Mine provider to use connection string aliases",
   "applications": [
       "Honeycomb"
   ],
   "grant": "Allow",
   "selectors": [
       {
           "idSelectorDefinition": {
               "identifier": {
                   "code": "Connection/*",
                   "scope": "Honeycomb"
               },
               "actions": [
                   {
                       "scope": "Honeycomb",
                       "activity": "Execute",
                       "entity": "Feature"
                   }
               ],
               "name": "Luminesce connection string aliases",
               "description": "Enable the Sql.Db.Mine provider to use connection string aliases"
           }
       }
   ],
   "when": {
       "activate": "2022-03-03T00:00:00.0000000+00:00",
       "deactivate": "9999-12-31T23:59:59.9999999+00:00"
   }
}

Getting help

You can access the latest documentation in the help page for the Sql.Db.Mine provider at any time by running this query:

@x = use Sql.Db.Mine
--help
enduse;

Alternatively, run the following query to retrieve the help content as a table:

select distinct CustomSyntax from Sys.Registration where Name = 'Sql.Db.Mine'

Understanding the Sql.Db.Mine provider syntax

Sql.Db.Mine is a direct provider, and therefore follows these syntactic conventions.

The precise syntax is as follows:

@x = use Sql.Db.Mine [with @@scalar_var1 [, @@scalar_var2...]]
<options-section>
----
<sql-for-reading-section>
----
<sql-for-writing-section>
----
<sql-for-configuring-section>
enduse;
-- The following statement is optional but useful while creating the provider to test it returns meaningful data
select * from @x

Note the following:

• Each <section> must be separated by a line consisting of at least four hyphens (and no other characters).
• <options-section> 
  An option takes the form --<option>=<value>, for example --provider=Provider.MyProvider. Note no spaces are allowed either side of the = operator. If an option takes a boolean value, then specifying that option (for example --allDomains) sets it to True; omitting the option specifies False.
  
  The --type and --connection options are mandatory. To list all the options, access the help page and examine the table at the bottom. The options in the following table are particularly relevant:

  Option	Explanation
  --type	Mandatory. Valid arguments are: MySql, Odbc, Oracle, PostgreSql, SqLite, SqlServer, GoogleBigQuery, AwsAthena
  --connection	Mandatory. This can either be:   A connection string, for example --connection="Data Source=./Tests/TestData/sakila.sqlite;" The Name of a connection string alias, for example --connection="MyDb"
  --provider=<name>	Mandatory in order to publish your custom provider with the specified name in the Luminesce catalog, for suitably-permissioned end users to discover and query. Note it may be more performant to omit this option while creating and testing your provider.
  --withWriter	Mandatory if you include a <sql-for-writing-section> in order to write to your SQL database.
  --parameters <name>,<type>,<value> ,true/false[,description]	Defines scalar variables that can be passed into the custom provider by end users. Each declaration:   Must be on a separate line after --parameters, so if specified this option should always be last in the list. Must have a name and data type (either Boolean, Int, BigInt, Double, Decimal, Text, Date, DateTime). Must have an initial value appropriate to the data type. To include quote or comma characters in a Text value, encapsulate it in double quote marks (see the example below). Specify true as the fourth argument to make the initial value also the default value; specify false to use the initial value only when setting up the provider, and not when end users query the published version. Can have a description that appears as a tooltip for end users in the Luminesce catalog. To include quote characters in the tooltip, encapsulate them in double quote marks: @x = use Sql.Db.Mine --type=SqLite --connection="MyDb" --description="This is a tooltip for the provider as a whole" --parameters AssetClass,Text,Equity,true Isin,Text,GB124892739,true BondType,Text,"Vanilla, Complex and Inflation-Linked",true,"This is a parameter tooltip" AsAt,DateTime,0001-01-01,false,"This will ""appear"" as a parameter tooltip" ---- See also the #PARAMETERVALUE macro in the section below for inserting parameter values into the provider at the appropriate location.
  --deleteProvider	Removes a published provider from the Luminesce catalog. More information.
  --limit=<n> --offset=<n> --groupBy=<pipe-delimited(|)-set-of-expressions-to-group-by> --select=<pipe-delimited(|)-set-of-expressions-to-filter-by> --filter=<filter-expression> --distinct	Specifying these options optimises performance in conjunction with the appropriate macros (see the section below) while you are creating and testing the custom provider. Consider the following example: @x = use Sql.Db.Mine --type=SqLite --connection="MyDb" --distinct --filter=Isin is not null --limit=42 ---- select   #DISTINCT   #SELECT { { Isin : Isin } }   from my_database_table where   #RESTRICT { { Isin : Isin } } #LIMIT enduse; select * from @x Each time you run this query during the development phase, a maximum of 42 different bonds with ISIN identifiers is returned, enabling you to test the provider returns meaningful data in a reasonable timeframe. Note these options have no effect on a published provider, so you can either retain or omit them when you are ready to add the --provider option and go live.

• <sql-for-reading-section>
<sql-for-writing-section>
<sql-for-configuring-section> 
  Nominally optional, at least one of these sections is mandatory in order for your custom provider to perform a useful operation. They must be in the specified order. An ENDUSE statement must terminate whichever is the last of these sections.

Reading from your SQL database

You must write a SQL query in the <sql-for-reading-section> that is valid for the SQL database you are connecting to.

We recommend including the macros in the following table in your SQL query to optimise performance. 

Note: For more information on each macro, and for available macros that are not included below (such as #CHECKACCESS, #USERID and #CLIENTDOMAIN), access the help page.

@x = use Sql.Db.Mine
----
select #SELECT {
  { Instrument: i.DisplayName },
}
from my_database_table i
enduse;

@x = use Sql.Db.Mine
----
select DisplayName as Instrument from my_database_table
enduse;

select #SELECT {
  { some-column-name+^~Int|"This is an important column" : sql-for-special-column },
  { next-column-name : sql-for-ordinary-column },
}

@x = use Sql.Db.Mine
--parameter
AssetClass,Text,Equity
----
select * from my_database_table where Type is #PARAMETERVALUE(AssetClass)
enduse; 

For example, to create our example CustomProviders.RetrieveInternalTransactionData provider:

@x = use Sql.Db.Mine
--provider=CustomProviders.RetrieveInternalTransactionData
--type=SqLite
--connection="MyDb"
----
-- Note the syntax in this section depends on the SQL database you are connecting to
select

#DISTINCT
-- Using the #SELECT_AGG macro here (as opposed to #SELECT):
-- 1. Avoids duplicating the work of the #RESTRICT macro
-- 2. Improves performance by passing aggregate queries down to the SQL database.
-- Here we rename database columns for end users, as well as defining how the Cost column is calculated
#SELECT_AGG
{
  { Name: instrument },
  { Figi: figi },
  { SettleDate: txn_date},
  { Currency: currency },
  { Quantity: units },
  { Price: price },
  { Cost: price * units },
}

from
 my_database_table

where
 -- Enables filtering on all the columns specified in the #SELECT_AGG macro.
 #RESTRICT_AGG

#GROUP_BY

#LIMIT

enduse;
select * from @x

Note the following:

• When run, this query publishes a CustomProviders.RetrieveInternalTransactionData provider in the Luminesce catalog.
• An end user can filter/return seven columns from the SQL database table (Name, Figi, SettleDate, Currency, Quantity, Price and Cost).
• Values in the virtual Cost column are calculated on-the-fly by multiplying the database price by units columns.
• If an end user writes a query that aggregates any of the columns in the SELECT_AGG macro, the aggregation is passed down by the RESTRICT_AGG macro to the SQL database, improving performance. An example of such a query might be:

  select SettleDate, count(*) TransactionCount, max(Cost) as MostExpensive
  from CustomProviders.RetrieveInternalTransactionData
  where Currency = 'GBP' group by 1

Writing to your SQL database

Coming soon.

Configuring your SQL database

Coming soon.

Publishing your custom provider

Each time you run a query with the --provider option, a custom provider with the specified name is automatically created and made available in the Luminesce catalog (note you may need to click the highlighted Refresh button to see it):

Note: It is more performant to omit the --provider option while you are creating and testing your provider.

To grant end users the necessary permissions to query a custom provider, create a suitable access control policy and assign it to each LUSID end user.

Once published, an end user can write Luminesce SQL queries using any of the data fields published in the catalog, so for example:

select Name, Figi, Cost
from CustomProviders.RetrieveInternalTransactionData
where Currency = 'USD' and SettleDate > #2022-01-01#;

Managing your live custom provider

Once published, you can manage a custom provider by running queries, for example:

• To retrieve the original query used to create the view:

  select Sql, At
  from sys.logs.hcquery
  where Sql like '%Sql.Db%' and Sql like '%RetrieveInternalTransactionData%' and Sql not like '%HcQuery%' and ShowAll = true
  order by At desc limit 1

• To examine the current content of the view:

  select Content from Sys.File where Name like '%RetrieveInternalTransactionData%' and Extension = '.sql'

Deleting your custom provider

To remove a custom provider from the Luminesce catalog (which means it will no longer be available for end users to query), run your query with the --deleteProvider option. For example:

@x = use Sql.Db.Mine
--provider=CustomProviders.RetrieveInternalTransactionData
--type=SqLite
--connection="Data Source=./Tests/TestData/sakila.sqlite;"
--deleteProvider
----
select 1 as deleting
enduse;
select * from @x

Note if using Oracle, you must supply a from for your select 1 as deleting statement. For example:

@x = use Sql.Db.Mine
--provider=CustomProviders.RetrieveInternalTransactionData
--type=Oracle
--connection="Data Source=<data-source>;"
--deleteProvider
----
select 1 as deleting from dual
enduse;
select * from @x

{
    "description": "User can download PostgreSQL binary",
    "applications": [
        "Honeycomb"
    ],
    "grant": "Allow",
    "selectors": [
        {
            "idSelectorDefinition": {
                "identifier": {
                    "code": "SqlDb_Providers_Postgresql",
                    "scope": "Honeycomb"
                },
                "actions": [
                    {
                        "scope": "Honeycomb",
                        "activity": "DownloadBinary",
                        "entity": "Feature"
                    }
                ],
                "name": "PostgreSQL binary",
                "description": "User can download PostgreSQL binary"
            }
        }
    ],
    "when": {
        "activate": "2023-01-01T00:00:00.0000000+00:00",
        "deactivate": "9999-12-31T23:59:59.9999999+00:00"
    }
}