Joseph Doherty
cfd403710e
Every HistoryUpdate a SQL backend applies now leaves a modification trail
(prior value, principal, Part 11 update type, modification time), and
HistoryReadRaw with IsReadModified=true serves it as HistoryModifiedData.
- Core: HistoryUpdateContext threaded into all three IHistorianDataUpdater
methods; IHistorianDataSource.ReadModifiedAsync (default NotSupported =>
BadHistoryOperationUnsupported on Gateway/Null); HistoryModified* contracts.
- SqlServer: schema v2 (002_sample_modified.sql, RequiredVersion=2, docs copy);
trail written by OUTPUT ... INTO on the same MERGE/DELETE statement, so a
failed update leaves no audit row; retention purges SampleModified per tag on
the source-time cutoff; ReadModifiedAsync keyed on (SourceTimeUtc, ModifiedId).
- QuestDB: append-only {prefix}sample_modified (TTL like samples), trail row
written BEFORE the change (orphan-on-crash, never an untracked change);
existence reads now carry the prior value; ReadModifiedAsync.
- Node manager: modified reads dispatch + page via the #669 continuation store
(HistoryContinuationState gains HistoryCursorKind + LastModifiedId), gated by
the existing HistoryRead decision; session principal / key id passed to the
updater. IsDeleteModified stays unsupported.
- Client.CLI: historyread --modified.
- Tests: node-manager dispatch/paging/cursor binding/principal threading,
over-the-wire HistoryModifiedData, shared ModifiedValueConformance on both
backends, SqlServer atomicity (forced trail failure => no change) + v1->v2
upgrade + retention trail purge.
- Docs: Historian.md (Modified-value history section, schema v2 upgrade note),
security.md, Client.CLI.md, CLAUDE.md.
30 lines
1.5 KiB
Transact-SQL
30 lines
1.5 KiB
Transact-SQL
-- OtOpcUa SQL Server historian backend - schema version 2: OPC UA Part 11 modified-value history (Gitea #668).
|
|
-- $(Schema) is substituted by SqlServerSchemaBootstrapper (identifier-validated) BEFORE the script is split on GO lines.
|
|
-- Every statement is guarded so a re-run is a no-op.
|
|
--
|
|
-- One row per sample a HistoryUpdate inserted, replaced or deleted, carrying the PRIOR value (all NULL for an
|
|
-- insert). Rows are written by the SAME statement that changes hist.Sample (OUTPUT ... INTO), so a failed update
|
|
-- leaves no audit row and a committed one always has its row.
|
|
-- WARNING: OUTPUT ... INTO forbids, on its target table: enabled triggers, either side of a FOREIGN KEY, and CHECK
|
|
-- constraints. Do not add any of those to SampleModified, or every HistoryUpdate fails.
|
|
IF OBJECT_ID(N'[$(Schema)].[SampleModified]', N'U') IS NULL
|
|
CREATE TABLE [$(Schema)].[SampleModified] (
|
|
TagId int NOT NULL,
|
|
SourceTimeUtc datetime2(7) NOT NULL,
|
|
ModifiedId bigint IDENTITY(1,1) NOT NULL,
|
|
ModificationTimeUtc datetime2(7) NOT NULL,
|
|
UpdateType tinyint NOT NULL,
|
|
Principal nvarchar(200) NOT NULL,
|
|
ApiKeyId nvarchar(200) NULL,
|
|
ValueKind tinyint NULL,
|
|
NumValue float NULL,
|
|
IntValue bigint NULL,
|
|
StrValue nvarchar(max) NULL,
|
|
StatusCode int NULL,
|
|
ServerTimeUtc datetime2(7) NULL,
|
|
CONSTRAINT PK_SampleModified PRIMARY KEY CLUSTERED (TagId, SourceTimeUtc, ModifiedId))
|
|
GO
|
|
IF NOT EXISTS (SELECT 1 FROM [$(Schema)].[SchemaVersion] WHERE Version = 2)
|
|
INSERT [$(Schema)].[SchemaVersion] (Version, AppliedUtc) VALUES (2, SYSUTCDATETIME())
|
|
GO
|