[Enh]: (CLOSED DUPLICATE) Support `@db()` substitution function.

[JerryNixon]

What?

In the same way @env() and @akv() allow string replacements in the DAB configuration, introduce @db() which reads from SQL Server extended properties.

Important

This allows configuration metadata to be maintained in the database and injected into the configuration during startup.

Behavior

• Resolves during configuration load after the database connection is established.
• Supported only when data-source.type is mssql.
• Applicable to any property name or value.
• Never writes back to the database.
• If the referenced metadata is missing, it resolves to an empty string ('').
• Values are resolved once during startup and are not dynamically refreshed during runtime.

Syntax

@db('<scope>:<property>')

Where <scope> defines the target (database, schema, table, column, or parameter).

DAB automatically infers parameter scope if the final segment begins with @.

Examples

@db(':CompanyName')                          // database-level
@db('dbo:DisplayName')                       // schema-level
@db('dbo.Author:MS_Description')             // table-level
@db('dbo.Author.Id:MS_Description')          // column-level
@db('dbo.GetCustomer.@CustomerId:MS_Description')  // parameter-level

In the parameter-level case, the leading @ indicates a parameter and switches the lookup from table to procedure.

Order of operation

@db() is resolved after @env() and @akv().

sequenceDiagram
  actor Engine as Engine
  participant ConfigInMem as ConfigInMem
  participant Environment as Environment
  participant AKV as AKV
  participant DB as Database
  participant Config as ConfigFile

  Engine ->> Config: Load Config
  Config -->> Engine: Config Data
  Engine ->> ConfigInMem: Create In-Memory Config

  Note over Engine: Perform Environment Replacements

  activate Engine
  ConfigInMem -->> Engine: Parse @env Values
  Engine ->> Environment: Get
  Environment -->> Engine: Values
  Engine ->> ConfigInMem: Replace @env Values
  deactivate Engine

  Note over Engine: Perform Key Vault Replacements

  activate Engine
  ConfigInMem -->> Engine: Parse @akv Values
  Engine ->> AKV: Request
  AKV -->> Engine: Secrets
  Engine ->> ConfigInMem: Replace @akv Values
  deactivate Engine

  Note over Engine: Perform Database Metadata Replacements

  activate Engine
  ConfigInMem -->> Engine: Parse @db Values
  Engine ->> DB: Query Extended Properties
  DB -->> Engine: Property Values
  Engine ->> ConfigInMem: Replace @db Values
  deactivate Engine

  Note over Engine: Replacements Complete

  Engine ->> Engine: Start

Loading

Example configuration

{
  "entities": {
    "Author": {
      "description": "@db('dbo.Author:MS_Description')",
      "source": {
        "object": "dbo.Author",
        "type": "table"
      },
      "fields": {
        "Id": {
          "description": "@db('dbo.Author.Id:MS_Description')"
        }
      }
    },
    "GetCustomer": {
      "description": "@db('dbo.GetCustomer:MS_Description')",
      "parameters": {
        "CustomerId": {
          "description": "@db('dbo.GetCustomer.@CustomerId:MS_Description')"
        }
      }
    },
    "Metadata": {
      "description": "@db(':CompanyName')"
    }
  }
}

Considerations

1. Wrap each metadata lookup in an OpenTelemetry activity for traceability.
2. Cache results by scope to minimize repeated queries.
3. Consider future expansion for triggers, constraints, and user-defined functions.
4. If a non-MSSQL data source is configured, log a warning and skip resolution.

Sample query

DECLARE @property NVARCHAR(255) = 'MS_Description';
DECLARE @schema NVARCHAR(255) = 'dbo';
DECLARE @object NVARCHAR(255) = 'Author';
DECLARE @column NVARCHAR(255) = 'Id';

SELECT value
FROM fn_listextendedproperty (
    @property,
    'schema', NULLIF(@schema, ''),
    CASE
        WHEN @object LIKE 'Get%' THEN 'procedure'
        ELSE 'table'
    END,
    NULLIF(@object, ''),
    CASE
        WHEN LEFT(@column, 1) = '@' THEN 'parameter'
        ELSE 'column'
    END,
    NULLIF(@column, ''),
    default, default
);

Examples

Scope	Call
Database	@db(':CompanyName') → EXEC fn_listextendedproperty('CompanyName', default, default, default, default, default, default)
Schema	@db('dbo:DisplayName') → EXEC fn_listextendedproperty('DisplayName','schema','dbo',default,default,default,default)
Table	@db('dbo.Author:MS_Description') → EXEC fn_listextendedproperty('MS_Description','schema','dbo','table','Author',default,default)
Column	@db('dbo.Author.Id:MS_Description') → EXEC fn_listextendedproperty('MS_Description','schema','dbo','table','Author','column','Id')
Parameter	@db('dbo.GetCustomer.@CustomerId:MS_Description') → EXEC fn_listextendedproperty('MS_Description','schema','dbo','procedure','GetCustomer','parameter','@CustomerId')

[JerryNixon]

Related #3224