Loggers, Project, Create

Loggers, Project

Loggers - does not work
Loggers1
Loggers2 work

D:\VC25\SQL\Loogers\1

Loggers_ALL_Works.sql
Loggers2_ALL_Works.sql

D:\VC25\SQL\Loogers\1

Create All

D:\VC25\SQL\Loogers\1\Create_Loggers_Whole_01.sql

Or Separatly
Host_App_Log_01.sql
Log_Instance_02.sql

LogItems_02.sql

Alter_Connections_01.sql

--------------------------------------------------------------------------

1. Create DB

------------------------------------------------------------------

-- Создание базы данных

USE master;

IF DB_ID('Loggers') IS NOT NULL DROP DATABASE Loggers;

CREATE DATABASE Loggers;

GO

-- Подключение к новой базе данных

USE Loggers;

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. Create LogInstance

-------------------------------------------------------------------

USE Loggers

GO

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

------------------------------------------------------------------------

3. Create LogItems
------------------------------------------------------------------------

USE Loggers

GO

CREATE TABLE dbo.LogItems (

    EventLogItemID 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. Check Create DB FK PK

-----------------------------------------------------------------------

USE Loggers

GO

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

-------------------------------------------------------------

FK_LogInstance_Hosts LogInstance HostID                 Hosts         ID

FK_LogInstance_Apps     LogInstance AppID                 Apps         ID

FK_LogInstance_Logs         LogInstance LogID                 Logs         ID

FK_LogItems_LogInstance LogItems LogInstanceID LogInstance ID

--------------------------------------------------------------------------