Logger, G01, C01, LoggerG01, LoggerC01
D:\VC25\SQL\Loogers\1
Logger_C01.sql
------------------------------------------------------------------------------------------------
-- Создание базы данных
USE master;
IF DB_ID('LoggerC01') IS NOT NULL DROP DATABASE LoggerC01;
CREATE DATABASE LoggerC01;
GO
-- Подключение к новой базе данных
USE LoggerC01;
GO
-- Создаем таблицу Hosts
CREATE TABLE dbo.Hosts (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Descriptions NVARCHAR(50) NOT NULL,
Created DATETIME2(7) NOT NULL DEFAULT SYSDATETIME()
);
-- Создаем таблицу Apps
CREATE TABLE dbo.Apps (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Descriptions NVARCHAR(50) NOT NULL,
Created DATETIME2(7) NOT NULL DEFAULT SYSDATETIME()
);
-- Создаем таблицу Logs
CREATE TABLE dbo.Logs (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Descriptions NVARCHAR(50) NOT NULL,
Created DATETIME2(7) NOT NULL DEFAULT SYSDATETIME()
);
GO
-- 2 -- LogInstance
/*
CREATE TABLE dbo.LogInstance (
ID INT IDENTITY(1,1) PRIMARY KEY,
HostID INT NOT NULL,
AppID INT NOT NULL,
LogID INT NOT NULL,
Created DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(),
Modified DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(),
CONSTRAINT FK_LogInstance_Hosts FOREIGN KEY (HostID) REFERENCES dbo.Hosts(ID),
CONSTRAINT FK_LogInstance_Apps FOREIGN KEY (AppID) REFERENCES dbo.Apps(ID),
CONSTRAINT FK_LogInstance_Logs FOREIGN KEY (LogID) REFERENCES dbo.Logs(ID)
);
GO
*/
CREATE TABLE dbo.LogInstance (
ID INT IDENTITY(1,1) PRIMARY KEY,
HostID INT NOT NULL,
AppID INT NOT NULL,
LogID INT NOT NULL,
PrimeLogID INT NOT NULL
CONSTRAINT CK_LogInstance_PrimeLogID_unsigned CHECK (PrimeLogID >= 0),
Created DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(),
Modified DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(),
CONSTRAINT FK_LogInstance_Hosts FOREIGN KEY (HostID) REFERENCES dbo.Hosts(ID),
CONSTRAINT FK_LogInstance_Apps FOREIGN KEY (AppID) REFERENCES dbo.Apps(ID),
CONSTRAINT FK_LogInstance_Logs FOREIGN KEY (LogID) REFERENCES dbo.Logs(ID),
CONSTRAINT UQ_LogInstance_Host_App_Log UNIQUE (HostID, AppID, LogID)
);
GO
-- Индекс для ускоренной выборки по часто используемой фильтрации
CREATE NONCLUSTERED INDEX IX_LogInstance_Host_App_Log
ON dbo.LogInstance (HostID, AppID, LogID);
GO
-- 3 -- LogItems
CREATE TABLE dbo.LogItems (
LogItemID BIGINT IDENTITY(1,1) PRIMARY KEY,
DT DATETIME2 NOT NULL,
LogLevel INT NOT NULL,
Source NVARCHAR(50) NULL,
Entity NVARCHAR(50) NULL,
Operation NVARCHAR(50) NULL,
Description NVARCHAR(50) NULL,
LogInstanceID INT NOT NULL,
CONSTRAINT FK_LogItems_LogInstance FOREIGN KEY (LogInstanceID) REFERENCES dbo.LogInstance(ID)
);
GO
-- 4 -- Проверка
SELECT
fk.name AS FK_name,
tp.name AS parent_table,
cp.name AS parent_column,
tr.name AS referenced_table,
cr.name AS referenced_column
FROM
sys.foreign_keys AS fk
INNER JOIN
sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN
sys.tables AS tp ON fkc.parent_object_id = tp.object_id
INNER JOIN
sys.columns AS cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
INNER JOIN
sys.tables AS tr ON fkc.referenced_object_id = tr.object_id
INNER JOIN
sys.columns AS cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE
tp.name IN (N'LogInstance', N'LogItems');
GO
--------------------------------------------------------
Logger_G01.sql
-----------------------------------------------------------
-- Создание базы данных
USE master;
IF DB_ID('LoggerG01') IS NOT NULL DROP DATABASE LoggerG01;
CREATE DATABASE LoggerG01;
GO
-- Подключение к новой базе данных
USE LoggerG01;
GO
-- Создаем таблицу Hosts
CREATE TABLE dbo.Hosts (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Descriptions NVARCHAR(50) NOT NULL,
Created DATETIME2(7) NOT NULL DEFAULT SYSDATETIME()
);
-- Создаем таблицу Apps
CREATE TABLE dbo.Apps (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Descriptions NVARCHAR(50) NOT NULL,
Created DATETIME2(7) NOT NULL DEFAULT SYSDATETIME()
);
-- Создаем таблицу Logs
CREATE TABLE dbo.Logs (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Descriptions NVARCHAR(50) NOT NULL,
Created DATETIME2(7) NOT NULL DEFAULT SYSDATETIME()
);
GO
-- 2 -- LogInstance
--CREATE TABLE dbo.LogInstance (
-- ID INT IDENTITY(1,1) PRIMARY KEY,
-- HostID INT NOT NULL,
-- AppID INT NOT NULL,
-- LogID INT NOT NULL,
-- Created DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(),
-- Modified DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(),
-- CONSTRAINT FK_LogInstance_Hosts FOREIGN KEY (HostID) REFERENCES dbo.Hosts(ID),
-- CONSTRAINT FK_LogInstance_Apps FOREIGN KEY (AppID) REFERENCES dbo.Apps(ID),
-- CONSTRAINT FK_LogInstance_Logs FOREIGN KEY (LogID) REFERENCES dbo.Logs(ID)
--);
--GO
CREATE TABLE dbo.LogInstance (
ID INT IDENTITY(1,1) PRIMARY KEY,
HostID INT NOT NULL,
AppID INT NOT NULL,
LogID INT NOT NULL,
PrimeLogID INT NOT NULL, -- Новое поле PLID (сокращение от PrimaryLogID)
Created DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(),
Modified DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(),
CONSTRAINT UQ_LogInstance UNIQUE (HostID, AppID, LogID),
CONSTRAINT FK_LogInstance_Hosts FOREIGN KEY (HostID) REFERENCES dbo.Hosts(ID),
CONSTRAINT FK_LogInstance_Apps FOREIGN KEY (AppID) REFERENCES dbo.Apps(ID),
CONSTRAINT FK_LogInstance_Logs FOREIGN KEY (LogID) REFERENCES dbo.Logs(ID)
);
-- Индексирование для ускорения запросов
-- CREATE INDEX IX_LogInstance_HostID_AppID_LogID_PrimeLogID ON dbo.LogInstance(HostID, AppID, LogID, PrimeLogID);
CREATE INDEX IX_LogInstance_HostID_AppID_LogID ON dbo.LogInstance(HostID, AppID, LogID);
-- 3 -- LogItems
CREATE TABLE dbo.LogItems (
LogItemID BIGINT IDENTITY(1,1) PRIMARY KEY,
DT DATETIME2 NOT NULL,
LogLevel INT NOT NULL,
Source NVARCHAR(50) NULL,
Entity NVARCHAR(50) NULL,
Operation NVARCHAR(50) NULL,
Description NVARCHAR(50) NULL,
LogInstanceID INT NOT NULL,
CONSTRAINT FK_LogItems_LogInstance FOREIGN KEY (LogInstanceID) REFERENCES dbo.LogInstance(ID)
);
GO
-- 4 -- Проверка
SELECT
fk.name AS FK_name,
tp.name AS parent_table,
cp.name AS parent_column,
tr.name AS referenced_table,
cr.name AS referenced_column
FROM
sys.foreign_keys AS fk
INNER JOIN
sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN
sys.tables AS tp ON fkc.parent_object_id = tp.object_id
INNER JOIN
sys.columns AS cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
INNER JOIN
sys.tables AS tr ON fkc.referenced_object_id = tr.object_id
INNER JOIN
sys.columns AS cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE
tp.name IN (N'LogInstance', N'LogItems');
GO
