Joseph Doherty
70 lines
2.8 KiB
Transact-SQL
70 lines
2.8 KiB
Transact-SQL
-- OtOpcUa SQL Server historian backend - schema version 1.
|
|
-- $(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.
|
|
IF SCHEMA_ID(N'$(Schema)') IS NULL EXEC(N'CREATE SCHEMA [$(Schema)]')
|
|
GO
|
|
IF OBJECT_ID(N'[$(Schema)].[SchemaVersion]', N'U') IS NULL
|
|
CREATE TABLE [$(Schema)].[SchemaVersion] (
|
|
Version int NOT NULL PRIMARY KEY,
|
|
AppliedUtc datetime2(7) NOT NULL)
|
|
GO
|
|
IF OBJECT_ID(N'[$(Schema)].[Tag]', N'U') IS NULL
|
|
CREATE TABLE [$(Schema)].[Tag] (
|
|
TagId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
|
Name nvarchar(400) NOT NULL CONSTRAINT UQ_Tag_Name UNIQUE,
|
|
DataType tinyint NOT NULL,
|
|
EngineeringUnit nvarchar(64) NULL,
|
|
Description nvarchar(400) NULL,
|
|
CreatedUtc datetime2(7) NOT NULL,
|
|
UpdatedUtc datetime2(7) NOT NULL)
|
|
GO
|
|
IF OBJECT_ID(N'[$(Schema)].[Sample]', N'U') IS NULL
|
|
CREATE TABLE [$(Schema)].[Sample] (
|
|
TagId int NOT NULL,
|
|
SourceTimeUtc datetime2(7) NOT NULL,
|
|
ValueKind tinyint NOT NULL,
|
|
NumValue float NULL,
|
|
IntValue bigint NULL,
|
|
StrValue nvarchar(max) NULL,
|
|
StatusCode int NOT NULL,
|
|
ServerTimeUtc datetime2(7) NOT NULL,
|
|
CONSTRAINT PK_Sample PRIMARY KEY CLUSTERED (TagId, SourceTimeUtc),
|
|
CONSTRAINT FK_Sample_Tag FOREIGN KEY (TagId) REFERENCES [$(Schema)].[Tag](TagId))
|
|
GO
|
|
IF OBJECT_ID(N'[$(Schema)].[Event]', N'U') IS NULL
|
|
CREATE TABLE [$(Schema)].[Event] (
|
|
EventId nvarchar(64) NOT NULL CONSTRAINT PK_Event PRIMARY KEY NONCLUSTERED,
|
|
SourceName nvarchar(400) NOT NULL,
|
|
AlarmId nvarchar(400) NOT NULL,
|
|
AlarmName nvarchar(400) NOT NULL,
|
|
AlarmTypeName nvarchar(100) NOT NULL,
|
|
Severity int NOT NULL,
|
|
EventKind nvarchar(50) NOT NULL,
|
|
Message nvarchar(max) NULL,
|
|
UserName nvarchar(200) NULL,
|
|
Comment nvarchar(max) NULL,
|
|
EventTimeUtc datetime2(7) NOT NULL,
|
|
ReceivedTimeUtc datetime2(7) NOT NULL)
|
|
GO
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'CX_Event_Time' AND object_id = OBJECT_ID(N'[$(Schema)].[Event]'))
|
|
CREATE CLUSTERED INDEX CX_Event_Time ON [$(Schema)].[Event] (EventTimeUtc, EventId)
|
|
GO
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Event_Source_Time' AND object_id = OBJECT_ID(N'[$(Schema)].[Event]'))
|
|
CREATE INDEX IX_Event_Source_Time ON [$(Schema)].[Event] (SourceName, EventTimeUtc)
|
|
GO
|
|
IF TYPE_ID(N'[$(Schema)].[SampleRow]') IS NULL
|
|
CREATE TYPE [$(Schema)].[SampleRow] AS TABLE (
|
|
SourceTimeUtc datetime2(7) NOT NULL PRIMARY KEY,
|
|
ValueKind tinyint NOT NULL,
|
|
NumValue float NULL,
|
|
IntValue bigint NULL,
|
|
StrValue nvarchar(max) NULL,
|
|
StatusCode int NOT NULL)
|
|
GO
|
|
IF TYPE_ID(N'[$(Schema)].[TimeRow]') IS NULL
|
|
CREATE TYPE [$(Schema)].[TimeRow] AS TABLE (T datetime2(7) NOT NULL PRIMARY KEY)
|
|
GO
|
|
IF NOT EXISTS (SELECT 1 FROM [$(Schema)].[SchemaVersion] WHERE Version = 1)
|
|
INSERT [$(Schema)].[SchemaVersion] (Version, AppliedUtc) VALUES (1, SYSUTCDATETIME())
|
|
GO
|