kreta/Kreta.DataAccess.Migrations/DBScripts/Database/create.tables.advanced.sql

-- ================================================================================================================================== 
-- T_ENTITYATTRIBUTEHISTORY & T_ENTITYHISTORY & EGYÉB táblák létrehozása
-- ==================================================================================================================================
CREATE TABLE [dbo].[T_ENTITYHISTORY] (
   [ID] INT IDENTITY(1, 1) NOT NULL
  ,[C_ALTERATIONDATE] DATETIME NULL
  ,[C_ENTITYID] INT NULL
  ,[C_ENTITYNAME] NVARCHAR(50) NULL
  ,[C_REASON] NVARCHAR(1000) NULL
  ,[C_FELHASZNALOID] INT NULL
  ,[C_INTEZMENYID] INT NULL
  ,[C_TANEVID] INT NULL
  ,[CREATED] DATETIME NOT NULL DEFAULT GETDATE()
  ,[HOSTNAME] VARCHAR(1000) NULL
  ,CONSTRAINT PK_EntityHistory PRIMARY KEY (ID) WITH (DATA_COMPRESSION = PAGE)
)
GO

CREATE TABLE [dbo].[T_ENTITYATTRIBUTEHISTORY] (
   [C_ENTITYHISTORYID] INT NOT NULL,
   [C_PROPERTYNAME] VARCHAR(1000) NOT NULL,
   [C_ORIGINALVALUE] NVARCHAR(4000) NULL,
   [C_CURRENTVALUE] NVARCHAR(4000) NULL,
   CONSTRAINT FK_EntityAttributeHistory_EntityHistoryId_REF_EntityHistory FOREIGN KEY (C_ENTITYHISTORYID) REFERENCES [dbo].[T_ENTITYHISTORY](ID)
)
GO

CREATE CLUSTERED COLUMNSTORE INDEX CCI_ENTITYATTRIBUTEHISTORY ON dbo.T_ENTITYATTRIBUTEHISTORY WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

CREATE TABLE [dbo].[T_ENTITYBLOBVALUES](
    C_ENTITYHISTORYID INT NOT NULL,
    C_PROPERTYNAME VARCHAR(1000) NOT NULL,
    C_ORIGINALVALUE NVARCHAR(MAX) NULL,
    C_CURRENTVALUE NVARCHAR(MAX) NULL,
    CONSTRAINT PK_EntityBLOBValues PRIMARY KEY CLUSTERED (C_ENTITYHISTORYID, C_PROPERTYNAME),
    CONSTRAINT FK_EntityBLOBValues FOREIGN KEY (C_ENTITYHISTORYID) REFERENCES dbo.T_ENTITYHISTORY(ID)
)
GO

CREATE TABLE T_ORAREND (
   C_ORARENDIORAID int NOT NULL
  ,C_DATUM datetime NOT NULL
  ,C_TANEVID int NOT NULL
  ,C_INTEZMENYID int NOT NULL
  ,CONSTRAINT PK_Orarend PRIMARY KEY (C_ORARENDIORAID, C_DATUM)
);
GO

CREATE INDEX NCI_Orarend_TanevId ON T_ORAREND ( C_TANEVID)
CREATE INDEX NCI_Orarend_IntezmenyId ON T_ORAREND ( C_INTEZMENYID)
CREATE INDEX NCI_Orarend_Datum_TanevId_IntezmenyId ON T_ORAREND (C_DATUM, C_TANEVID, C_INTEZMENYID)
GO

ALTER TABLE [DBO].T_ORAREND 
  ADD CONSTRAINT FK_Orarend_OrarendiOraId_REF_OrarendiOraId 
  FOREIGN KEY (C_ORARENDIORAID) 
  REFERENCES [DBO].T_ORARENDIORA (ID)
  
ALTER TABLE [DBO].T_ORAREND 
  ADD CONSTRAINT FK_Orarend_TanevId_REF_Tanev 
  FOREIGN KEY (C_TANEVID) 
  REFERENCES [DBO].T_TANEV (ID)

ALTER TABLE [DBO].T_ORAREND 
  ADD CONSTRAINT FK_Orarend_IntezmenyId_REF_Intezmeny 
  FOREIGN KEY (C_INTEZMENYID) 
  REFERENCES [DBO].T_INTEZMENY (ID)  
GO

CREATE TABLE [dbo].[T_ORARENDTELJES](
  [C_INTEZMENYID] [int] NOT NULL,
  [C_TANEVID] [int] NOT NULL,
  [C_DATUM] [datetime] NOT NULL,
  [C_ORARENDIORAID] [int] NOT NULL,
  [C_ORARENDIORAGROUPID] [int] NULL,
  [C_OSZTALYCSOPORTID] [int] NULL,
  [C_FELADATKATEGORIAID] [int] NULL,
  [C_TANTARGYID] [int] NULL,
  [C_ISOSZTALYORARENDBENEMLATSZIK] [char](1) NULL,
  [C_TANARID] [int] NULL,
  [C_TEREMID] [int] NULL,
  [C_ORASZAM] [int] NULL,
  [C_CSENGETESIRENDID] [int] NULL,
  [C_BONTOTT] [char](1) NULL,
  [C_HETIREND] [int] NULL,
  [C_HETNAPJA] [int] NULL,
  [C_HETSORSZAMA] [int] NULL,
  [C_NAPDATUMA] [datetime] NULL,
  [C_ORAERVENYESSEGKEZDETE] [datetime] NULL,
  [C_ORAERVENYESSEGVEGE] [datetime] NULL,
  [C_ORAKEZDETE] [datetime] NULL,
  [C_ORAVEGE] [datetime] NULL,
  [C_EGYEDINAP] [char](1) NULL,
  [C_KOZPONTIORAGROUPID] [uniqueidentifier] NULL,
  [C_KOZPONTILAGTOROLTORAGROUPID] [uniqueidentifier] NULL,
  [C_HELYETTESTANAROKID] [int] NULL,
  [C_HELYETTESITESID] [int] NULL,
  [C_MAXORASZAM] [tinyint] NULL
    CONSTRAINT PK_OrarendTeljes PRIMARY KEY CLUSTERED (
      [C_INTEZMENYID] ASC,
      [C_TANEVID] ASC,
      [C_DATUM] ASC,
      [C_ORARENDIORAID] ASC
    )
)
GO

CREATE TABLE [dev].[EnvironmentInfo] (
   [Key] nvarchar(255) NOT NULL PRIMARY KEY
  ,[Value] nvarchar(1000) NULL
  ,[Description] nvarchar(1000) NULL
) 
GO

CREATE TABLE [dbo].[VersionInfo](
   Version bigint NOT NULL PRIMARY KEY
  ,AppliedOn datetime NULL
  ,Description nvarchar(1024) NULL
)
GO

CREATE TABLE [dev].[VersionInfoSPFN](
  [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [Name] [nvarchar](255) NULL,
  [IsProcedure] [bit] NULL,
  [IsSchemaUpdated] [bit] NULL,
  [RegisteredAt] [datetime] NULL
)
GO

CREATE TABLE [core].[AdatszotarElemek](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Nev] [nvarchar](128) NOT NULL,
  [PluszOszlopok] [nvarchar](1000) NULL,
  [TableName] [nvarchar](128) NULL,
  [TableSchemaName] [nvarchar](128) NULL,
  CONSTRAINT [PK_AdatszotarElemek] PRIMARY KEY CLUSTERED (ID)
)
GO

CREATE TABLE [auditlog].[Users](
  [USERID] [int] IDENTITY(-1000, -1) NOT NULL,
  [USERNAME] [nvarchar](1000) NOT NULL,
  [CREATED] [datetime] NOT NULL DEFAULT (getdate()),
  CONSTRAINT [PK_auditlog_Users] PRIMARY KEY CLUSTERED (
    [USERID] DESC
  ),
  CONSTRAINT [UK_auditlog_Users] UNIQUE NONCLUSTERED (
    [USERNAME] ASC
  )
)
GO

CREATE TABLE dbo.T_OSZTONDIJHISTORY(
  ID INT NOT NULL,
  C_ENTITYID int NOT NULL,
  C_TABLEID tinyint NOT NULL,
  C_ALTERATIONDATE datetime NOT NULL,
  C_REASONID tinyint NOT NULL, -- 1:(New) INSERT, 2:(Removed) DELETE, 3:(Modified) UPDATE
  C_FELHASZNALOID int NULL,
  C_TANULOID int NOT NULL,
  C_TANEVID int NOT NULL,
  C_PROPERTYNAME varchar (100) COLLATE Hungarian_CI_AI NOT NULL,
  C_ORIGINALVALUE nvarchar (2000) COLLATE Hungarian_CI_AI NULL,
  C_CURRENTVALUE nvarchar (2000) COLLATE Hungarian_CI_AI NULL,
  CONSTRAINT PK_OsztondijHistory PRIMARY KEY CLUSTERED (C_TANEVID, C_TANULOID, ID, C_PROPERTYNAME)
    WITH (DATA_COMPRESSION = PAGE, FILLFACTOR = 100)
)
GO

CREATE TABLE [dbo].[T_LEGMAGASABBSZINTUFENNTARTO] (
    [ID]                  INT              IDENTITY (1, 1) NOT NULL,
    [C_AZONOSITO]         INT              NOT NULL,
    [C_GUID]              UNIQUEIDENTIFIER NOT NULL,
    [C_NEV]               NVARCHAR (1000)  NOT NULL,
    [TOROLT]              CHAR (1)         NOT NULL,
    [SERIAL]              INT              NOT NULL,
    [LASTCHANGED]         DATETIME         NOT NULL,
    [CREATED]             DATETIME         NOT NULL,
    [MODIFIER]            INT              NULL,
    [CREATOR]             INT              NULL,
    CONSTRAINT [PK_LegmagasabbSzintuFenntarto] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [UK_LegmagasabbSzintuFenntarto_Guid] UNIQUE NONCLUSTERED ([C_GUID] ASC),
    CONSTRAINT [UK_LegmagasabbSzintuFenntarto_Azonosito] UNIQUE NONCLUSTERED ([C_AZONOSITO] ASC)
)