DHI.Services.MCLite – Internal Guide¶

This guide explains how to leverage DHI.Services.MCLite as the persistence layer for Domain Services. It focuses on core concepts, architecture, configuration, and usage patterns across all MCLite-backed repositories. Per-repository, domain-specific docs (Documents, TimeSeries, GIS, etc.) live in those packages’ sections. This document gives you the big picture and the practical patterns you’ll reuse everywhere.

What MCLite is (and isn’t)¶

What it is: a generic persistence provider layer that talks to PostgreSQL, SQL Server, or SQLite using a single abstraction (Db, DataUtility, and repository classes).
What it isn’t: a schema migrator. Unlike DHI.Services.PostgreSQL, MCLite does not create tables for you. The database, schema, and tables must already exist and match the expected names/columns.

Use MCLite when you:

need to support multiple DB vendors with the same service code,
want full control over schema management (migrations/DDL handled outside the service),
want light dependencies (no EF, no ORM), and explicit ADO.NET access with parameters.

Supported databases & vendor differences¶

DB	`DbFlavour`	Notes
PostgreSQL	`PostgreSQL`	Default. Uses `WITH RECURSIVE` in CTE queries; schema qualified with `schema.table`.
SQL Server	`SqlServer`	No `WITH RECURSIVE` keyword; schema qualified with `schema.table`.
SQLite	`SQLite`	Single file DB. Must exist ahead of time. No schemas; MCLite uses a table name delimiter `_` and stores GUIDs as BLOBs.

MCLite compiles vendor-specific SQL where needed (recursive CTEs, string ops, GUID handling).

Connection model¶

Create a repository with a single connection string interpreted by MCLite.Db:

// Example: PostgreSQL
var conn = "database=dhi_store;workspace=workspace1;host=db-internal;port=5432;username=dss_admin;password=***;dbflavour=PostgreSQL";

// Example: SQL Server
var conn = "database=dhi_store;workspace=workspace1;host=sql-internal;port=1433;username=dss_admin;password=***;dbflavour=SqlServer";

// Example: SQLite (file must exist)
var conn = "database=C:\\data\\mclite.db;workspace=workspace1;dbflavour=SQLite";

What the `Db` class does for you¶

Parses the connection string and selects the ADO.NET provider via DbConnectionFactory.
Resolves SchemaName from the workspace by querying master.workspace:
- The row { name = <workspace> } must exist, mapping to a schema_name.
- SQLite has no schemas; MCLite uses <schema>_<table> naming via TableDelimiter = "_".
Provides:
- Prefix = "@" (DB parameter prefix),
- TableDelimiter = "." (PG/SQL Server) or "_" (SQLite),
- DbFlavour, ConnectionString, FeatureCacheMode (settable via cachemode, defaults to All).

Checklist: before running services, ensure the workspace row, the schema, and all expected tables exist.

Schema expectations (high level)¶

MCLite references table and column names via a shared Names constant class (e.g., Names.TableJob, Names.FieldId, Names.FieldGroupId, …). Across domains you’ll see tables for:

Groups & entities per data type (e.g., time series groups, feature class groups, spreadsheet groups).
Entity metadata (entity_type, entity_description, metadata).
BLOB storage (blob with id, block_no, data) for large payloads.
Domain tables (jobs, job instances, documents, document folders & associations, time series, features, rasters, spreadsheets, places, …).

Because MCLite doesn’t create tables, schema creation/migrations live in your DB migration scripts (Flyway, Liquibase, SSDT, DDL scripts, etc.). Align your DDL with the Names.* constants used by the provider.

Groups, full names, and IDs¶

MCLite models “folders” as groups with hierarchical relationships:

Full name: POSIX-like path "/Group/Subgroup/EntityName".
Groups: stored in a group table per data type, with parent_id->child chain.
Entities: rows in the type’s entity table with a foreign key to a group (nullable for root).
Lookups:
- DataUtility.GetGroupId(db, dataType, "/Group/Sub") – traverses the hierarchy to return the child group ID (CTE differs by vendor).
- DataUtility.CreateGroup(db, dataType, "/Group/Sub") – ensures the full path exists (creates missing groups).
- DataUtility.GetId(db, dataType, "Name", "/Group/Sub") – returns an entity ID by name+group (special handling for Documents; see below).
- DataUtility.GetFullNameById(...) and GetGroupFullNameById(...) – rebuild paths from IDs.
- DataUtility.GetFullNames(db, dataType, IEnumerable<Guid> ids) – bulk path resolution.

Special case: Documents¶

Documents span three tables (document, document_folder, document_folder_association). Lookups in DataUtility handle this by:

Resolving folder path -> folder ID(s),
Collecting candidate document IDs via the association table,
Matching by document name,
Reconstructing the full path (or root if no folder).

Parameters, GUIDs, and vendor quirks¶

Always use DataUtility.CreateCommand and DataUtility.CreateParameter:

Parameters are prefixed with Db.Prefix (always "@").
GUIDs:
- PG/SQL Server: send as DbType.Guid.
- SQLite: GUIDs are stored as byte[]. CreateParameter converts automatically.
- When reading, DataUtility.ConvertToId normalizes byte[] -> Guid.

Versioning & optimistic updates¶

Most write operations bump the entity’s version column to a new Guid:

DataUtility.SetVersion(db, dataType, id) updates FieldVersion.
Repositories set Version = Guid.NewGuid() on insert/update.
There is no automated concurrency check in the base; if you need strict optimistic concurrency, add a WHERE version=@expected clause in custom repos.

Metadata & entity types¶

Entity description rows map an entity to a type:
- DataUtility.AddEntityDescription(db, entityId, "Job")
- DataUtility.DeleteEntityDescription(db, entityId)
Free-form metadata:
- DataUtility.AddMetadata(db, entityId, jsonString)
- DataUtility.DeleteMetadata(db, metadataId)
XML parsing helper: ParseXmlNode -> IDictionary<string, object> (handy for ingest).

Blobs¶

Large payloads can be stored/retrieved from the blob table:

DataUtility.GetBlob(db, guid) streams data by concatenating blocks ordered by block_no.
Write path is repository-specific (see domain docs where applicable).

Transactions & multi-step operations¶

Many higher-level operations involve multiple statements (e.g., ensure groups -> insert entity -> add metadata). Prefer the overloads that accept IDbConnection and IDbTransaction:

using var conn = DbConnectionFactory.CreateConnection(db);
conn.Open();
using var tx = conn.BeginTransaction();

var groupId = DataUtility.CreateGroup(db, dataType, "/A/B", conn, tx);
// Insert your entity in the appropriate table here...
DataUtility.AddEntityDescription(db, entityId, "MyType", conn); // optional

tx.Commit();

This prevents half-written structures if anything fails.

Repository catalog (what’s available)¶

MCLite ships repository implementations for common domains. Each follows the same patterns (compose table = db.SchemaName + db.TableDelimiter + Names.TableX, use DataUtility helpers, parameterize, handle versions).

DocumentRepository : BaseGroupedDocumentRepository<string>, IDocumentRepository<string>
FeatureRepository : BaseGroupedUpdatableGisRepository<string, Guid>
JobRepository : IJobRepository<Guid, string>
PlaceRepository : IPlaceRepository<string>
SpreadsheetRepository : BaseSpreadsheetRepository<string>, IGroupedUpdatableRepository
TaskRepository : BaseTaskRepository<Workflow, string>
TimeSeriesFromSpreadsheetRepository : BaseDiscreteTimeSeriesRepository<string, double>
TimeSeriesRepository : BaseGroupedUpdatableTimeSeriesRepository<string, double>, IGroupedUpdatableRepository

Detailed usage for each lives in the domain provider docs for each packages. The rest of this guide shows generic MCLite patterns you can apply in any repo.

Quick patterns you’ll use everywhere¶

1) Open a connection and build a fully qualified table name¶

var db = new Db(connString); // parses flavour, resolves SchemaName from workspace
using var cn = DbConnectionFactory.CreateConnection(db);
cn.Open();

var jobTable = db.SchemaName + db.TableDelimiter + Names.TableJob; // e.g., "workspace1.job" or "workspace1_job"

2) Insert with parameters (example from TaskRepository.Add)¶

var sql = $@"INSERT INTO {jobTable}(
    {Names.FieldId}, {Names.FieldName}, content, created, {Names.FieldVersion}, targetcomputer)
  VALUES (@ID, @Name, @Content, @Created, @Version, @Computer)";

using var cmd = DataUtility.CreateCommand(sql, cn);
DataUtility.CreateParameter(cmd, db, "ID",      DbType.Guid,    jobGuid);
DataUtility.CreateParameter(cmd, db, "Name",    DbType.String,  jobName);
DataUtility.CreateParameter(cmd, db, "Content", DbType.String,  json);
DataUtility.CreateParameter(cmd, db, "Created", DbType.DateTime, DateTime.UtcNow);
DataUtility.CreateParameter(cmd, db, "Version", DbType.Guid,    Guid.NewGuid());
DataUtility.CreateParameter(cmd, db, "Computer",DbType.String,  targetComputer);
cmd.ExecuteNonQuery();

DataUtility.AddEntityDescription(db, jobGuid, "Job", cn); // optional classification

3) Look up an entity by full name¶

var id = DataUtility.GetId(db, DataType.TimeSeries, "WaterLevel", "/Hydro/Observations", cn);
if (id is null)
{
    // not found
}

4) Ensure a group path exists (create if missing)¶

var groupId = DataUtility.CreateGroup(db, DataType.TimeSeries, "/Hydro/Observations", cn, tx);

5) Resolve full names from IDs (bulk)¶

var map = DataUtility.GetFullNames(db, DataType.TimeSeries, ids, cn);
// map[tsId] -> "/Hydro/Observations/WaterLevel"

6) Walk a group recursively¶

var groupIds = DataUtility.GetGroupIds(db, DataType.FeatureCollection, "/GIS/Layers", includeParent: true, recursive: true);
// use to fetch everything under a subtree

Health check: is my DB ready?¶

Drop this into a service startup probe:

bool IsMCLiteReady(string conn)
{
    var db = new Db(conn);
    using var cn = DbConnectionFactory.CreateConnection(db);
    cn.Open();

    // 1) workspace -> schema mapping
    if (string.IsNullOrWhiteSpace(db.SchemaName)) return false;

    // 2) essential tables exist?
    var mustExist = new[]{
        db.SchemaName + db.TableDelimiter + Names.TableEntityType,
        db.SchemaName + db.TableDelimiter + Names.TableEntityDescription,
        db.SchemaName + db.TableDelimiter + Names.TableMetadata,
        db.SchemaName + db.TableDelimiter + Names.TableBlob
    };

    foreach (var t in mustExist)
    {
        using var cmd = DataUtility.CreateCommand($"SELECT 1 FROM {t} WHERE 1=0", cn);
        try { cmd.ExecuteNonQuery(); }
        catch { return false; }
    }
    return true;
}

Migration notes (from `DHI.Services.PostgreSQL`)¶

DDL is your responsibility here—MCLite will not bootstrap tables.
Create the workspace row in master.workspace and the target schema for PG/SQL Server.
Align table/column names with Names.* in the provider and domain repos (case/quoting rules matter per vendor).
SQLite: pre-create the DB file; ensure GUID columns are BLOBs; table names will be prefixed with the schema via underscore.
Indexes: add them in your migrations (e.g., on name, group_id, association foreign keys); MCLite assumes performant lookups.

Performance tips¶

Prefer bulk full-name resolution via DataUtility.GetFullNames(db, type, ids) to avoid N+1 path walks.
Use the transactional overloads on CreateGroup, AddGroup, etc., when performing multi-step writes.
For document lookups, consider indexes on:
- document(name),
- document_folder(name,parent_id),
- document_folder_association(document_folder_id, document_id).
For time series, index (group_id, name) and any query attributes you filter by.

Error handling & diagnostics¶

Workspace not found -> Db._GetTrueSchemaName throws. Ensure master.workspace has the row.
SQLite DB missing -> Db throws: “The SQLite database … does not exist”.
Group or entity not found -> DataUtility.GetGroupId/GetId return null.
Schema/table mismatch -> Commands fail at runtime. Verify DDL vs. Names.*.
GUID type mismatch (SQLite) -> never hand-craft parameters; always use DataUtility.CreateParameter.

Example: TaskRepository at a glance¶

TaskRepository shows the canonical MCLite style:

Build table name: var table = db.SchemaName + db.TableDelimiter + Names.TableJob;
Create parameterized ADO.NET commands via DataUtility.CreateCommand.
Insert/update with a new version GUID.
Normalize IDs (Guid.TryParse(...) or resolve by name) using _GetTaskId.
Attach entity descriptions (AddEntityDescription) for cross-cutting metadata.

This same pattern is used for all other repositories.

When to pick which repository¶

Scenario	Repository
Store/list files in hierarchical groups (no auto-upload to cloud)	`DocumentRepository`
Persist vector features (read/write, grouped)	`FeatureRepository`
Queue and query jobs & job instances	`JobRepository`
Manage named places / place collections	`PlaceRepository`
Read/write tabular sheets	`SpreadsheetRepository`
Manage task definitions / workflows	`TaskRepository`
Time series from sheets (discrete)	`TimeSeriesFromSpreadsheetRepository`
Time series (grouped, updatable)	`TimeSeriesRepository`

Implementation details and HTTP surface for each domain are documented alongside those providers. The patterns above apply to all.