DHI.Services.PostgreSQL for Scalars — Internal Developer Guide¶

Persist Scalars (key/value with metadata) in PostgreSQL. This provider implements the same repository/service interfaces as the Scalars Core package, so you can swap storage without touching application logic.

Repository: DHI.Services.Provider.PostgreSQL.ScalarRepository
Works with services: ScalarService<string,int> and GroupedScalarService<string,int>
NuGet: DHI.Services.Provider.PostgreSQL (+ DHI.Services.Scalars)
See also: PostgreSQL Providers, Scalars Core docs, and Scalars Web API docs

Install¶

dotnet add package DHI.Services.Scalars
dotnet add package DHI.Services.Provider.PostgreSQL

What this provider does¶

Stores scalar metadata and the current value (not a history) in a relational table.
Supports grouped identifiers (hierarchical Group/Name “full names”) and fast lookups by id or group.
Enforces the same API semantics exposed by IGroupedScalarRepository<string,int>:
- Get, GetAll, Add, Update, Remove
- Contains, ContainsGroup, GetByGroup
- SetData(id, data) and SetLocked(id, locked)

Locking behavior (“refuse updates when Locked is true”) is enforced by the service layer. Use the ScalarService / GroupedScalarService wrappers for correct behavior and change logging.

Storage model¶

On first use the repository auto-creates the table if missing (requires CREATE permissions):

CREATE TABLE IF NOT EXISTS public.scalars (
  id            varchar(255) PRIMARY KEY,
  groupname     varchar(255),
  name          varchar(255) NOT NULL,
  valuetypename varchar(255) NOT NULL,
  description   varchar(1024),
  locked        bool,
  value         text,
  datetime      timestamp without time zone,
  flag          int
);
CREATE INDEX IF NOT EXISTS scalars_index ON public.scalars USING btree (datetime DESC);

Notes:

id: the FullName (e.g., ProjectA/MaxLevel) — max 255 chars.
value: stored as text; the runtime type is determined by valuetypename.
datetime: the timestamp of the data point (not the DB row timestamp).
Supported valuetypename values out-of-the-box:
- System.Double, System.Int32, System.Boolean, System.String, System.DateTime
- DHI.Services.Notifications.NotificationLevel, DHI.Services (enum)
Reads use an internal parser to convert value from text back to the correct CLR type; writes stringify values using invariant culture.

If you need a different table/schema, add Table=myschema.mytable to the connection string (see below).

Connection string¶

Pass a standard Npgsql connection string. You can optionally include and strip a Table= addition:

Default table: public.scalars
Override: add Table=customschema.customtable (it will be removed before opening the DB connection)

Examples

Host=localhost;Port=5432;Database=ProviderTest;Username=postgres;Password=secret
Host=pg:5432;Database=prod;Username=svc;Password=...;Table=custom.scalars_v2

Quick start (code)¶

A) Construct the repository¶

using DHI.Services.Provider.PostgreSQL;
using DHI.Services.Scalars;

// Optionally include `Table=...` in the connection string
var repo = new ScalarRepository(
    "Host=localhost;Port=5432;Database=ProviderTest;Username=postgres;Password=Solutions!;Table=public.scalars");

// Wrap it in a service to enforce locking + get logging hooks
var svc = new GroupedScalarService<string,int>(repo);

B) Create and set a value¶

// Metadata (no value yet)
svc.Add(new DHI.Services.Scalars.Scalar<string,int>(
    id: "ProjectA/MaxLevel",
    name: "MaxLevel",
    valueTypeName: "System.Double",
    group: "ProjectA")
{
    Description = "Highest allowed reservoir level (m)"
});

// Set the current value
svc.SetData("ProjectA/MaxLevel",
    new DHI.Services.Scalars.ScalarData<int>(42.3, DateTime.UtcNow, flag: 1));

// Lock it (further updates will be rejected by the service)
svc.SetLocked("ProjectA/MaxLevel", true);

C) Query by group¶

foreach (var s in svc.GetByGroup("ProjectA"))
{
    Console.WriteLine($"{s.FullName} -> {s.GetData().Value}");
}

Registration options¶

You can wire the PostgreSQL-backed repository into the service locator the same three ways used across Domain Services.

1) Quick registration (code)¶

ServiceLocator.Register(
    new GroupedScalarService<string,int>(
        new DHI.Services.Provider.PostgreSQL.ScalarRepository(
            "Host=localhost;Port=5432;Database=ProviderTest;Username=postgres;Password=Solutions!;Table=public.scalars")),
    "postgresql");

2) Via connection object (code)¶

using DHI.Services.Scalars.WebApi; // connection type lives here

var conn = new GroupedScalarServiceConnection("postgresql", "PostgreSQL provider for Scalars")
{
    RepositoryType = "DHI.Services.Provider.PostgreSQL.ScalarRepository, DHI.Services.Provider.PostgreSQL",
    RepositoryConnectionString = "Host=localhost;Port=5432;Database=ProviderTest;Username=postgres;Password=Solutions!"
    // Optional logger wiring:
    // LoggerType = "DHI.Services.Logging.SimpleLogger, DHI.Services",
    // LoggerConnectionString = "[AppData]scalars.log"
};
ServiceLocator.Register(conn.Create(), conn.Id);

3) Via `connections.json` (declarative)¶

Use this verbatim if you leverage the Connections module:

{
  "$type": "System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[DHI.Services.IConnection, DHI.Services]], mscorlib",
  "postgresql": {
    "$type": "DHI.Services.Scalars.WebApi.GroupedScalarServiceConnection, DHI.Services.Scalars.WebApi",
    "RepositoryConnectionString": "Server=localhost;Port=5432;Database=ProviderTest;User Id=postgres;Password=Solutions!",
    "RepositoryType": "DHI.Services.Provider.PostgreSQL.ScalarRepository, DHI.Services.Provider.PostgreSQL",
    "LoggerConnectionString": "[AppData]scalars.log",
    "LoggerType": "DHI.Services.Logging.SimpleLogger, DHI.Services",
    "Name": "PostgreSQL provider for scalar service connection",
    "Id": "postgresql"
  }
}

Then, at startup:

Services.Configure(new ConnectionRepository("[AppData]connections.json".Resolve()), lazyCreation: true);

// Later
var svc = Services.Get<IGroupedScalarService<string,int>>("postgresql");

Provider behavior & implementation notes¶

Table creation: On first construction the provider checks for the target table and creates it (plus an index on datetime). If the role cannot create objects, pre-create the table using the DDL above.
Value conversion: value is stored as text and converted based on valuetypename. Ensure you set valueTypeName correctly (e.g., "System.Double") when creating scalars.
DateTime: Column datetime is a timestamp without time zone. Use UTC in your application for consistency. Parameters are normalized via an internal helper.
Groups: GetByGroup and ContainsGroup use a PostgreSQL regex to match group and its children: ^<group>(\/|$). Stick to conventional group tokens (letters/digits//) to avoid surprising regex semantics.
Locking: The repository writes whatever it’s asked to write; call through the service to respect the Locked flag (the service reads, checks, and throws if locked).
Conflicts: Add will fail on duplicate id (PK). Use AddOrUpdate on the service if you want “upsert-like” semantics.
Logging: Pass an ILogger to ScalarService to get automatic “value changed” info logs when data actually changes.

Convenience models (optional)¶

The provider includes wrappers that derive from the core models to make construction a bit shorter:

// Provider model aliases
var s  = new DHI.Services.Provider.PostgreSQL.Scalar("MaxLevel", "System.Double", "ProjectA");
var sd = new DHI.Services.Provider.PostgreSQL.ScalarData(42.3, DateTime.UtcNow);

// They are fully compatible with the core service/repository APIs.

Local dev tips¶

Minimal dev connection string:

Host=localhost;Port=5432;Database=ProviderTest;Username=postgres;Password=postgres

If you want to separate environments or test tables, append ;Table=public.scalars_dev (the provider will create it).

Troubleshooting¶

“relation does not exist” → The DB user couldn’t auto-create the table. Pre-create using the DDL above or grant CREATE on the target schema.
Type mismatch when reading → valuetypename must match the actual data type (e.g., "System.Double"). Data is parsed from text using invariant culture.
Updates ignored when locked → That’s by design: use SetLocked(id,false) before Update/SetData (or don’t lock it).
Group queries not returning children → Ensure your group token matches the leading segment(s) of FullName exactly (case-sensitive) and doesn’t include trailing /.