/*
 * $Id: schema69_addon.sql 100106 2011-06-16 00:30:20Z rlowery $
 *
 * Description:
 *
 * SQL Script to build the ManageSoft database on Microsoft SQL Server.
 * It is the first script that has to be run to setup the database.
 *
 * The steps performed by this script are:
 * 1. Create new database
 * 2. Create database table and initialiase data (skip if the table exists)
 *
 * If running the script manually, you might need to substitute some
 * configuration values:
 * DATABASEFILENAME should be set to the pathname (.mdf) for the database file
 * DEFAULT_NAMING_CONTEXT should be set to the DN of your domain controller
 *
 * Protected by US patent 6,006,034; patents pending.
 * Copyright 2000-2002 ManageSoft Corporation. All rights reserved.
 * Reproduction, adaptation, or translation without prior permission
 * is prohibited.
 */
USE ManageSoft
GO

PRINT '------------------------';
PRINT 'Creating database tables';
PRINT '------------------------';
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'OperatingSystem'
)
BEGIN
	CREATE TABLE OperatingSystem (
		-- Auto-generated identity #
		OperatingSystemID	int IDENTITY NOT NULL,
		-- Name of operating system
		OperatingSystemName	nvarchar(128) NULL,
		-- Reference to operating system category
		Category		int NULL,
		CONSTRAINT FK_OperatingSystem_Category
			FOREIGN KEY(Category)
			REFERENCES OperatingSystem(OperatingSystemID),
		CONSTRAINT UQ_OperatingSystem
			UNIQUE CLUSTERED(OperatingSystemID)
	);

END
ELSE
	PRINT 'Using existing OperatingSystem table'
GO

-- A NetworkDevice is a loose record of the discovery of a device on
-- a network, using any of a number of discovery methods. As such, the same
-- device may be found in more than one way (see DuplicateID which may be
-- able to track this fact if known), or by more than one distinguishing feature.
-- Accordingly this table has a somewhat unsatisfactory Primary key!
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'NetworkDevice'
)
BEGIN
	CREATE TABLE NetworkDevice (
		-- Auto-generated identity #
		DeviceID		int IDENTITY NOT NULL,
		-- Reference to another discovery record for this device,
		-- if known
		DuplicateID		int NULL,
		-- ManageSoft Computer ID, if known
		ComputerID		int NULL,
		-- NetBIOS name for computers or any name for other devices,
		-- if known
		DeviceName		nvarchar(64) NULL,
		-- Fully qualified DNS name, if known
		DNSFullName		nvarchar(256) NULL,
		-- NT domain name, if known
		NTDomainName		nvarchar(256) NULL,
		-- IP address of the device
		IPAddress		varchar(64) NULL,
		-- IP subnet that contains the node
		IPSubnet		varchar(64) NULL,
		-- IP subnet mask for the subnet contains the device
		IPSubnetMask		varchar(64) NULL,
		-- Network adapter physical address of the node
		-- Can be MAC address or token ring address
		PhysicalAddress		varchar(64) NULL,
		-- What role does the device perform?
		-- NULL = unknown
		-- 0 = Computer (don't know if server or workstation)
		-- 1 = Workstation
		-- 2 = Server
		-- 3 = Printer
		-- 4 = Switch
		-- 5 = Router
		-- 6 = Hub
		DeviceRole		int NULL,
		-- Operating system of node if computer
		OperatingSystem		nvarchar(128) NULL,
		-- Operating system of node if computer
		OperatingSystemID	int NULL,
		-- Is the device to be managed by ManageSoft
		-- 0 = false, 1 = true, NULL = unknown
		IsManaged		int NULL,
		-- Customer-entered description of device
		Description		nvarchar(256) NULL,
		-- System description of device
		SystemDescription	nvarchar(256) NULL,
		-- System location of device
		SystemLocation		nvarchar(256) NULL,
		-- System contact of device
		SystemContact		nvarchar(256) NULL,
		-- The date and time that the node was first discovered
		FirstDiscovered		datetime NOT NULL DEFAULT getdate(),
		-- The last time the node was checked or updated
		LastUpdate		datetime NOT NULL DEFAULT getdate(),
		-- A name that identifies where the discovery information came
		-- from (eg. physical location, server...etc)
		LastDataSourceName	nvarchar(128) NULL,
		-- The type of data source (eg. Excel, Fluke, NM, Text)
		LastDataSourceType	nvarchar(32) NULL,
		CONSTRAINT FK_NetworkDevice_OperatingSystem
			FOREIGN KEY(OperatingSystemID)
			REFERENCES OperatingSystem(OperatingSystemID),
		CONSTRAINT PK_NetworkDevice
			PRIMARY KEY NONCLUSTERED(DeviceID)
	);

	CREATE NONCLUSTERED INDEX NetworkDeviceByComputerID
		ON NetworkDevice(ComputerID)

	CREATE NONCLUSTERED INDEX NetworkDeviceByDeviceName
		ON NetworkDevice(DeviceName)

	CREATE NONCLUSTERED INDEX NetworkDeviceByDuplicateID
		ON NetworkDevice(DuplicateID)

	CREATE NONCLUSTERED INDEX NetworkDeviceByIPAddress
		ON NetworkDevice(IPAddress)

	CREATE NONCLUSTERED INDEX NetworkDeviceByNTDomainName
		ON NetworkDevice(NTDomainName)

	CREATE NONCLUSTERED INDEX NetworkDeviceByPhysicalAddress
		ON NetworkDevice(PhysicalAddress)

END
ELSE
	PRINT 'Using existing NetworkDevice table'
GO

-- By which DS was this device discovered?
-- Sometimes useful when other identifying features are duplicated,
-- and when the DS should do something to the device.
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'NetworkDeviceDiscoveredBy'
)
BEGIN
	CREATE TABLE NetworkDeviceDiscoveredBy (
		-- Device discovered
		DeviceID		int NOT NULL,
		-- DS which discovered it
		ServerUID		binary(16) NOT NULL,
		-- Does the DS have administrative privileges for the device?
		CanAdminister		bit NULL DEFAULT 0,
		CONSTRAINT FK_NetworkDeviceDiscoveredBy_Device
			FOREIGN KEY(DeviceID)
			REFERENCES NetworkDevice(DeviceID),
		CONSTRAINT PK_NetworkDeviceDiscoveredBy
			PRIMARY KEY CLUSTERED(DeviceID, ServerUID)
	);

END
ELSE
	PRINT 'Using existing NetworkDeviceDiscoveredBy table'
GO


IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
        WHERE CONSTRAINT_NAME = 'FK_NetworkDeviceDiscoveredBy_Server'
)
BEGIN
        PRINT 'Create FK_NetworkDeviceDiscoveredBy_Server constraint'

        ALTER TABLE NetworkDeviceDiscoveredBy
	        ADD CONSTRAINT FK_NetworkDeviceDiscoveredBy_Server
		FOREIGN KEY(ServerUID)
		REFERENCES DistributionServer(ServerUID)
END
ELSE
        PRINT 'Using existing FK_NetworkDeviceDiscoveredBy_Server constraint'
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'TaskType'
)
BEGIN
	CREATE TABLE TaskType (
		TaskTypeID		int IDENTITY NOT NULL,
		TaskTypeName		nvarchar(32) NOT NULL,
		CONSTRAINT UQ_TaskTypeID
			UNIQUE CLUSTERED(TaskTypeID),
		CONSTRAINT PK_TaskType
			PRIMARY KEY NONCLUSTERED(TaskTypeName)
	);

END
ELSE
	PRINT 'Using existing TaskType table'
GO

IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'TaskSchedule'
)
BEGIN
	CREATE TABLE TaskSchedule (
		TaskScheduleID		int IDENTITY NOT NULL,
		StartTime		datetime NULL,
		EndTime			datetime NULL,
		RetryCount		int NULL DEFAULT 1,
		MinRetryDelay		int NULL DEFAULT 60,
		RepeatDelay		int NULL DEFAULT 24 * 3600,
		NumParallelTasks	int NULL DEFAULT 1,
		SleepBetweenTasks	int NULL DEFAULT 0,
		CONSTRAINT UQ_TaskScheduleID
			UNIQUE CLUSTERED(TaskScheduleID)
	);

END
ELSE
	PRINT 'Using existing TaskSchedule table'
GO

IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Task'
)
BEGIN
	CREATE TABLE Task (
		TaskID			int IDENTITY NOT NULL,
		TaskTypeID		int NOT NULL,
		TaskName		nvarchar(128) NOT NULL,
		-- For Distribution task, which package:
		PackagePathID		int NULL,
		TaskScheduleID		int NOT NULL,
		CONSTRAINT FK_Task_TaskType
			FOREIGN KEY(TaskTypeID)
			REFERENCES TaskType(TaskTypeID),
		CONSTRAINT UQ_TaskID
			UNIQUE CLUSTERED(TaskID),
		CONSTRAINT PK_Task
			PRIMARY KEY NONCLUSTERED(TaskName)
	);

END
ELSE
	PRINT 'Using existing Task table'
GO

IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Job'
)
BEGIN
	CREATE TABLE Job (
		JobUID			binary(16) NOT NULL,
		TaskID			int NOT NULL,
		ServerUID		binary(16) NOT NULL,
		CONSTRAINT FK_Job_Task
			FOREIGN KEY(TaskID)
			REFERENCES Task(TaskID),
		CONSTRAINT PK_Job
			PRIMARY KEY NONCLUSTERED(TaskID, ServerUID),
		CONSTRAINT UQ_Job
			UNIQUE NONCLUSTERED(JobUID)
	);

END
ELSE
	PRINT 'Using existing Job table'
GO

-- All possible states for an action are reflected in a record here
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ActionState'
)
BEGIN
	CREATE TABLE ActionState (
		ActionStateID		int IDENTITY NOT NULL,
		ActionStateName		nvarchar(32) NOT NULL,
		CONSTRAINT UQ_ActionStateID
			UNIQUE CLUSTERED(ActionStateID),
		CONSTRAINT PK_ActionState
			PRIMARY KEY NONCLUSTERED(ActionStateName)
	);

END
ELSE
	PRINT 'Using existing ActionState table'
GO

-- An Action arising from a Task, to be applied (possibly repeatedly)
-- by an actor (often a DS) to a set of target devices, or to itself
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'Action'
)
BEGIN
	CREATE TABLE Action (
		-- The unique identifier for the action
		ActionUID		binary(16) NOT NULL,
		-- The Task which gave rise to this action
		TaskID			int NOT NULL,
		-- Only if delegated to a DS
		ServerUID		binary(16) NULL,
		-- The job which instructed the DS to perform the action,
		-- if Job still exists
		JobUID			binary(16) NULL,
		-- One of the predefined action states
		ActionStateID		int NOT NULL,
		-- If Task is of type Distribution, a PackageVersion applies
		PackageVersionID	int NULL,
		-- If not empty, text describing the reason the action failed
		FailureReason		ntext NULL,
		CONSTRAINT FK_Action_ActionState
			FOREIGN KEY(ActionStateID)
			REFERENCES ActionState(ActionStateID),
		CONSTRAINT FK_Action_Job
			FOREIGN KEY(JobUID)
			REFERENCES Job(JobUID),
		CONSTRAINT FK_Action_Task
			FOREIGN KEY(TaskID)
			REFERENCES Task(TaskID),
		CONSTRAINT UQ_ActionUID
			UNIQUE CLUSTERED(ActionUID),
		CONSTRAINT UQ_Action
			UNIQUE NONCLUSTERED(TaskID, ServerUID)
	);

END
ELSE
	PRINT 'Using existing Action table'
GO

-- An action applies/applied to this computer, DNS, IP or MAC address
IF NOT EXISTS (
	SELECT * FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME = 'ActionApplies'
)
BEGIN
	CREATE TABLE ActionApplies (
		-- Action which applies
		ActionUID		binary(16) NOT NULL,
		-- One of these 5 must be non-null (precedence top->bottom):
		ComputerID		int NULL,
		-- Index into the NetworkDevice table for this device
		DeviceID		int NULL,
		-- Network hardware address of the device
		MACAddress		char(18) NULL,
		-- DNS name of the device
		DNSName			nvarchar(128) NULL,
		-- IP Address of the device
		IPAddress		varchar(64) NULL,
		-- One of the predefined action states
		ActionStateID		int NOT NULL,
		-- If not empty, text describing the reason the action failed
		FailureReason		ntext NULL,
		CONSTRAINT FK_ActionApplies_Action
			FOREIGN KEY(ActionUID)
			REFERENCES Action(ActionUID),
		CONSTRAINT FK_ActionApplies_ActionState
			FOREIGN KEY(ActionStateID)
			REFERENCES ActionState(ActionStateID)
	);

	CREATE NONCLUSTERED INDEX UQ_ActionApplies_Device
		ON ActionApplies(ActionUID, DeviceID)

	CREATE NONCLUSTERED INDEX UQ_ActionApplies_Computer
		ON ActionApplies(ActionUID, ComputerID)

	CREATE NONCLUSTERED INDEX UQ_ActionApplies_DNSName
		ON ActionApplies(ActionUID, DNSName)

	CREATE NONCLUSTERED INDEX UQ_ActionApplies_IPAddress
		ON ActionApplies(ActionUID, IPAddress)

	CREATE NONCLUSTERED INDEX UQ_ActionApplies_MACAddress
		ON ActionApplies(ActionUID, MACAddress)

	CREATE NONCLUSTERED INDEX ActionApplies_Device
		ON ActionApplies(DeviceID)

END
ELSE
	PRINT 'Using existing ActionApplies table'
GO


IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
        WHERE CONSTRAINT_NAME = 'FK_Job_Server'
)
BEGIN
        PRINT 'Create FK_Job_Server constraint'

        ALTER TABLE Job
	        ADD CONSTRAINT FK_Job_Server
		FOREIGN KEY(ServerUID)
		REFERENCES DistributionServer(ServerUID)
END
ELSE
        PRINT 'Using existing FK_Job_Server constraint'
IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
        WHERE CONSTRAINT_NAME = 'FK_Action_Server'
)
BEGIN
        PRINT 'Create FK_Action_Server constraint'

        ALTER TABLE Action
	        ADD CONSTRAINT FK_Action_Server
		FOREIGN KEY(ServerUID)
		REFERENCES DistributionServer(ServerUID)
END
ELSE
        PRINT 'Using existing FK_Action_Server constraint'
IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
        WHERE CONSTRAINT_NAME = 'FK_ActionApplies_Computer'
)
BEGIN
        PRINT 'Create FK_ActionApplies_Computer constraint'

        ALTER TABLE ActionApplies
	        ADD CONSTRAINT FK_ActionApplies_Computer
		FOREIGN KEY(ComputerID)
		REFERENCES Computer(ComputerID)
END
ELSE
        PRINT 'Using existing FK_ActionApplies_Computer constraint'
/*
 * $Id: schema69_addon.sql 100106 2011-06-16 00:30:20Z rlowery $
 *
 * After creating the database tables,
 * populate initial values and create views, etc.
 */

/* name mapping for TaskType */
IF NOT EXISTS (SELECT * FROM TaskType)
BEGIN
	-- populate table with initial task types
	SET IDENTITY_INSERT TaskType ON
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (1, 'Unknown');
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (2, 'CommandExecutionClient');
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (3, 'CommandExecutionServer');
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (4, 'Inventory');
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (5, 'Adoption');
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (6, 'PolicyUpdate');
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (7, 'Discovery');
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (8, 'Cancel');
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (9, 'PackageDistribution');
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (10, 'SecurityUpdate');
	INSERT INTO TaskType (TaskTypeID, TaskTypeName)
		VALUES (11, 'WakeOnLAN');
		
	SET IDENTITY_INSERT TaskType OFF
END /* TaskType */

/* name mapping for ActionState */
IF NOT EXISTS (SELECT * FROM ActionState)
BEGIN
	-- populate table with action status'
	SET IDENTITY_INSERT ActionState ON
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (1, 'Created');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (2, 'DistributionInProgress');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (3, 'DistributionFailed');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (4, 'DistributionComplete');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (5, 'SchedulePending');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (6, 'ScheduleFailed');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (7, 'Scheduled');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (8, 'Applied');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (9, 'ApplyFailed');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (10, 'CancelInProgress');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (11, 'CancelFailed');
	INSERT INTO ActionState (ActionStateID, ActionStateName)
		VALUES (12, 'Cancelled');
	SET IDENTITY_INSERT ActionState OFF
END /* ActionState */

