USE [VIGIL-Settings]
GO

-- DROP VIEWS

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_registry]'))
DROP VIEW [dbo].[vw_registry]
GO


-- DROP TABLES
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_Version]') AND type in (N'U'))
DROP TABLE [dbo].[Table_Version]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[registry_keys]') AND type in (N'U'))
DROP TABLE [dbo].[registry_keys]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[registry_paths]') AND type in (N'U'))
DROP TABLE [dbo].[registry_paths]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[registry_users]') AND type in (N'U'))
DROP TABLE [dbo].[registry_users]
GO


-- CREATE TABLES
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Table_Version](
	[entity] [nvarchar](50) NOT NULL,
	[version] [int] NOT NULL,
 CONSTRAINT [PK_Table_Version] PRIMARY KEY CLUSTERED
(
	[entity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE dbo.registry_paths 
( 
	idx INT IDENTITY(1,1) NOT NULL, 
	path_name VARCHAR(1024) NOT NULL UNIQUE, 
	CONSTRAINT PK_registry_paths PRIMARY KEY (idx) 
)
GO

CREATE TABLE dbo.registry_users 
( 
	idx INT IDENTITY(1,1) NOT NULL, 
	user_name VARCHAR(512) NOT NULL UNIQUE, 
	CONSTRAINT PK_registry_users PRIMARY KEY (idx) 
)
GO

CREATE TABLE dbo.registry_keys 
( 
	idx INT NOT NULL IDENTITY(1,1), 
	path_id INT NOT NULL,
    user_id INT NOT NULL, 
	key_name VARCHAR(512), 
	key_value NVARCHAR(4000) NOT NULL, 
	key_type INT NOT NULL, 
	CONSTRAINT PK_registry_keys PRIMARY KEY (idx), 
	CONSTRAINT FK_registry_keys_path_id FOREIGN KEY([path_id]) REFERENCES [dbo].[registry_paths] ([idx]) ON DELETE CASCADE
)
GO


-- CREATE VIEWS


-- CREATE INDEXES

CREATE NONCLUSTERED INDEX [ix_registry_paths_path_name] ON [dbo].registry_paths
(
	path_name ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-- CREATE INDEXES
CREATE NONCLUSTERED INDEX [ix_registry_users_user_name] ON [dbo].registry_users
(
	user_name ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [ix_registry_keys_user_path_id_key_name] ON [dbo].[registry_keys]
(
    [user_id] ASC,
	[path_id] ASC,
	[key_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE VIEW vw_registry 
AS
	SELECT k.idx, p.path_name, k.path_id, 
	u.user_name, k.user_id, k.key_name, k.key_type, k.key_value 
	FROM registry_keys k, registry_users u, registry_paths p 
	WHERE k.path_id = p.idx AND k.user_id = u.idx
GO
