YY.EventLogExportAssistant/Scripts/PostgreSQL. Скрипт создания базы для хранения данных журнала регистрации.sql

create table if not exists "Applications"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"Name" varchar(500),
	constraint "PK_Applications"
		primary key ("InformationSystemId", "Id")
);

create unique index if not exists "IX_Applications_InformationSystemId_Id"
	on "Applications" ("InformationSystemId", "Id");

create index if not exists "IX_Applications_InformationSystemId_Name"
	on "Applications" ("InformationSystemId", "Name");

create table if not exists "Computers"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"Name" varchar(500),
	constraint "PK_Computers"
		primary key ("InformationSystemId", "Id")
);

create unique index if not exists "IX_Computers_InformationSystemId_Id"
	on "Computers" ("InformationSystemId", "Id");

create index if not exists "IX_Computers_InformationSystemId_Name"
	on "Computers" ("InformationSystemId", "Name");

create table if not exists "Events"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"Name" varchar(500),
	"Presentation" varchar(500),
	constraint "PK_Events"
		primary key ("InformationSystemId", "Id")
);

create unique index if not exists "IX_Events_InformationSystemId_Id"
	on "Events" ("InformationSystemId", "Id");

create index if not exists "IX_Events_InformationSystemId_Name"
	on "Events" ("InformationSystemId", "Name");

create table if not exists "InformationSystems"
(
	"Id" bigint generated by default as identity
		constraint "PK_InformationSystems"
			primary key,
	"Name" varchar(250),
	"Description" varchar(500)
);

create unique index if not exists "IX_InformationSystems_Id"
	on "InformationSystems" ("Id");

create table if not exists "LogFiles"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"FileName" text,
	"CreateDate" timestamp not null,
	"ModificationDate" timestamp not null,
	"LastEventNumber" bigint not null,
	"LastCurrentFileReferences" text,
	"LastCurrentFileData" text,
	"LastStreamPosition" bigint,
	constraint "PK_LogFiles"
		primary key ("InformationSystemId", "Id")
);

create unique index if not exists "IX_LogFiles_InformationSystemId_FileName_CreateDate_Id"
	on "LogFiles" ("InformationSystemId", "FileName", "CreateDate", "Id");

create table if not exists "Metadata"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"Name" varchar(500),
	"Uuid" uuid not null,
	constraint "PK_Metadata"
		primary key ("InformationSystemId", "Id")
);

create unique index if not exists "IX_Metadata_InformationSystemId_Id"
	on "Metadata" ("InformationSystemId", "Id");

create index if not exists "IX_Metadata_InformationSystemId_Name_Uuid"
	on "Metadata" ("InformationSystemId", "Name", "Uuid");

create table if not exists "PrimaryPorts"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"Name" varchar(500),
	constraint "PK_PrimaryPorts"
		primary key ("InformationSystemId", "Id")
);

create unique index if not exists "IX_PrimaryPorts_InformationSystemId_Id"
	on "PrimaryPorts" ("InformationSystemId", "Id");

create index if not exists "IX_PrimaryPorts_InformationSystemId_Name"
	on "PrimaryPorts" ("InformationSystemId", "Name");

create table if not exists "RowsData"
(
	"InformationSystemId" bigint not null,
	"Id" bigint not null,
	"Period" timestamp with time zone not null,
	"SeverityId" bigint,
	"ConnectId" bigint,
	"Session" bigint,
	"TransactionStatusId" bigint,
	"TransactionDate" timestamp,
	"TransactionId" bigint,
	"UserId" bigint,
	"ComputerId" bigint,
	"ApplicationId" bigint,
	"EventId" bigint,
	"Comment" text,
	"MetadataId" bigint,
	"Data" text,
	"DataUUID" text,
	"DataPresentation" text,
	"WorkServerId" bigint,
	"PrimaryPortId" bigint,
	"SecondaryPortId" bigint,
	constraint "PK_RowsData"
		primary key ("InformationSystemId", "Period", "Id")
);

create index if not exists "IX_RowsData_InformationSystemId_DataUUID"
	on "RowsData" ("InformationSystemId", "DataUUID");

create unique index if not exists "IX_RowsData_InformationSystemId_Period_Id"
	on "RowsData" ("InformationSystemId", "Period", "Id");

create index if not exists "IX_RowsData_InformationSystemId_UserId_Period"
	on "RowsData" ("InformationSystemId", "UserId", "Period");

create table if not exists "SecondaryPorts"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"Name" varchar(500),
	constraint "PK_SecondaryPorts"
		primary key ("InformationSystemId", "Id")
);

create unique index if not exists "IX_SecondaryPorts_InformationSystemId_Id"
	on "SecondaryPorts" ("InformationSystemId", "Id");

create index if not exists "IX_SecondaryPorts_InformationSystemId_Name"
	on "SecondaryPorts" ("InformationSystemId", "Name");

create table if not exists "Severities"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"Name" varchar(500),
	constraint "PK_Severities"
		primary key ("InformationSystemId", "Id")
);

create unique index if not exists "IX_Severities_InformationSystemId_Id"
	on "Severities" ("InformationSystemId", "Id");

create index if not exists "IX_Severities_InformationSystemId_Name"
	on "Severities" ("InformationSystemId", "Name");

create table if not exists "TransactionStatuses"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"Name" varchar(500),
	constraint "PK_TransactionStatuses"
		primary key ("InformationSystemId", "Id")
);

create table if not exists "Users"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"Name" varchar(500),
	"Uuid" uuid not null,
	constraint "PK_Users"
		primary key ("InformationSystemId", "Id")
);

create unique index if not exists "IX_Users_InformationSystemId_Id"
	on "Users" ("InformationSystemId", "Id");

create index if not exists "IX_Users_InformationSystemId_Name_Uuid"
	on "Users" ("InformationSystemId", "Name", "Uuid");

create table if not exists "WorkServers"
(
	"InformationSystemId" bigint not null,
	"Id" bigint generated by default as identity,
	"Name" varchar(500),
	constraint "PK_WorkServers"
		primary key ("InformationSystemId", "Id")
);

create unique index if not exists "IX_WorkServers_InformationSystemId_Id"
	on "WorkServers" ("InformationSystemId", "Id");

create index if not exists "IX_WorkServers_InformationSystemId_Name"
	on "WorkServers" ("InformationSystemId", "Name");

create or replace view vw_eventlog("InformationSystemId", "InformationSystemName", "Period", "RowId", "SeverityId", "SeverityName", "Connection", "Session", "TransactionStatusId", "TransactionDate", "TransactionId", "UserId", "UserName", "UserUUID", "ComputerId", "ComputerName", "Data", "DataUUID", "DataPresentation", "Comment", "ApplicationId", "ApplicationName", "EventId", "EventName", "MetadataId", "MetadataName", "MetadataUUID", "WorkServerId", "WorkServerName", "PrimaryPortId", "PrimaryPortName", "SecondaryPortId", "SecondaryPortName") as
	SELECT "RD"."InformationSystemId",
       "IS"."Name"      AS "InformationSystemName",
       "RD"."Period",
       "RD"."Id"        AS "RowId",
       "RD"."SeverityId",
       "SV"."Name"      AS "SeverityName",
       "RD"."ConnectId" AS "Connection",
       "RD"."Session",
       "RD"."TransactionStatusId",
       "RD"."TransactionDate",
       "RD"."TransactionId",
       "RD"."UserId",
       "USR"."Name"     AS "UserName",
       "USR"."Uuid"     AS "UserUUID",
       "RD"."ComputerId",
       "CMP"."Name"     AS "ComputerName",
       "RD"."Data",
       "RD"."DataUUID",
       "RD"."DataPresentation",
       "RD"."Comment",
       "RD"."ApplicationId",
       "APPS"."Name"    AS "ApplicationName",
       "RD"."EventId",
       "EVNT"."Name"    AS "EventName",
       "RD"."MetadataId",
       "META"."Name"    AS "MetadataName",
       "META"."Uuid"    AS "MetadataUUID",
       "RD"."WorkServerId",
       "WSRV"."Name"    AS "WorkServerName",
       "RD"."PrimaryPortId",
       "PPRT"."Name"    AS "PrimaryPortName",
       "RD"."SecondaryPortId",
       "SPRT"."Name"    AS "SecondaryPortName"
FROM "RowsData" "RD"
         LEFT JOIN "InformationSystems" "IS" ON "RD"."InformationSystemId" = "IS"."Id"
         LEFT JOIN "Severities" "SV"
                   ON "RD"."InformationSystemId" = "SV"."InformationSystemId" AND "RD"."SeverityId" = "SV"."Id"
         LEFT JOIN "Users" "USR"
                   ON "RD"."InformationSystemId" = "USR"."InformationSystemId" AND "RD"."UserId" = "USR"."Id"
         LEFT JOIN "Computers" "CMP"
                   ON "RD"."InformationSystemId" = "CMP"."InformationSystemId" AND "RD"."ComputerId" = "CMP"."Id"
         LEFT JOIN "Applications" "APPS"
                   ON "RD"."InformationSystemId" = "APPS"."InformationSystemId" AND "RD"."ApplicationId" = "APPS"."Id"
         LEFT JOIN "Events" "EVNT"
                   ON "RD"."InformationSystemId" = "EVNT"."InformationSystemId" AND "RD"."EventId" = "EVNT"."Id"
         LEFT JOIN "Metadata" "META"
                   ON "RD"."InformationSystemId" = "META"."InformationSystemId" AND "RD"."MetadataId" = "META"."Id"
         LEFT JOIN "WorkServers" "WSRV"
                   ON "RD"."InformationSystemId" = "WSRV"."InformationSystemId" AND "RD"."WorkServerId" = "WSRV"."Id"
         LEFT JOIN "PrimaryPorts" "PPRT"
                   ON "RD"."InformationSystemId" = "PPRT"."InformationSystemId" AND "RD"."PrimaryPortId" = "PPRT"."Id"
         LEFT JOIN "SecondaryPorts" "SPRT" ON "RD"."InformationSystemId" = "SPRT"."InformationSystemId" AND
                                              "RD"."SecondaryPortId" = "SPRT"."Id";