-- before adding additional default data
-- set the seeding back to the default area
-- so new values won't be added as custom

declare @maxID int
SET @maxID = NULL

select @maxID= max(ID) from TS_Triples where ID < 100000
DBCC CheckIdent(TS_Triples, reseed, @maxID)

select @maxID= max(Hash) from TS_Resources where hash < 100000
DBCC CheckIdent(TS_Resources, reseed, @maxID)

/******************************************************************************************************************
-- Check for standard name
--*******************************************************************************************************************/
--Creating [dbo].[udfMMS_IsStandardNameOK]'

if exists (select * from sysobjects where name='udfMMS_IsStandardNameOK' and  xtype = 'FN')
	DROP FUNCTION udfMMS_IsStandardNameOK
GO

--*****************************************************************************
/* ##FND_BEGIN dbo.udfMM_IsStandardNameOK ^^
This functions checks a Measurement standard name to ensure that it contains
only valid characters that will work with the simple encoding scheme used by 
udfMM_EscapeStandardName. 

Returns 1 if the standard name is OK, or 0 if there is an invalid character
in the name.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##FND_END */
--
-- $Id: //rdsw/dev/cayman/SW_VB6/business/MSDEUpgrader/scripts/NOM/NOM11pt1_Upgrade.sql#1 $
-- 
--*****************************************************************************
CREATE FUNCTION [dbo].[udfMMS_IsStandardNameOK]
(
	@input NVARCHAR(400)
)
RETURNS BIT
BEGIN
	DECLARE @inputOK BIT
	SET @inputOK = 1
	DECLARE @allowed NVARCHAR(200)
	-- this is the list of characters that we accept in the unescaped standard name
	SET @allowed = 
		N'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789+-_() /.^'
	-- We're going to loop over the input string and inspect each character to 
	-- ensure that it is in our list of allowed characters.
	DECLARE @found INT			-- will tell us if we found the charcter
	DECLARE @char NVARCHAR(1)	-- the currect character we are checking
	DECLARE @idx INT			-- index into the input string
	SET @idx = 1				-- start at first character
	WHILE(@idx <= LEN(@input))	-- loop over input string
	BEGIN
		SET @char = SUBSTRING(@input, @idx, 1)
		SET @found = CHARINDEX(@char, @allowed)
		IF(@found = 0)
		BEGIN
			SET @inputOK = 0
			BREAK
		END	
		SET @idx = @idx + 1
	END
	RETURN @inputOK
END
GO

-- modifying spMM_CheckNewNames
if exists (select * from sysobjects where name='spMM_CheckNewNames' and  xtype = 'P')
DROP PROCEDURE spMM_CheckNewNames
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
--*****************************************************************************
/* ##BD_BEGIN
Checks the uniqueness of the signature/fullname and standard names and 
raises an error if values already exist in the measurement map. 
While the unique constraints ensure that the values cannot be inserted, this
lets us generate a custom SQL error that has improved troubleshooting 
information.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/MM_MeasurementMap/StoredProcedures/09.spMM_CheckNewNames.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE [dbo].[spMM_CheckNewNames]
(
	@aMeasurementSignature		NVARCHAR(400),
	@aMeasurementStandardName	NVARCHAR(250)
)
AS
	-- declare some variables - we'll reuse them 
	DECLARE @id 	MeasurementID
	DECLARE @val	NVARCHAR(400)

	-- check signature
	SET @id = NULL
	SELECT 
		@id = ID
	FROM
		dbo.MM_MeasurementMap
	WHERE
		FullName = @aMeasurementSignature
	-- return the conflicting id if we found it
	IF(@id IS NOT NULL)
	BEGIN
		RETURN @id
	END

	-- check standard name
	SET @id = NULL
	SELECT 
		@id = ID
	FROM
		dbo.MM_MeasurementMap
	WHERE
		StandardName = @aMeasurementStandardName
	-- return the conflicting id if we found it
	IF(@id IS NOT NULL)
	BEGIN
		RETURN @id
	END

RETURN 0
	
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'spEntryConflictHandler' AND xtype = 'P')
	DROP PROCEDURE spEntryConflictHandler
GO

CREATE PROCEDURE dbo.spEntryConflictHandler
	@oldID				SMALLINT,
	@ID					SMALLINT,
	@conflictingName	NVARCHAR(250),
	@deleteOld			BIT = 1
AS
	IF @oldID = 0
	BEGIN
		SELECT @oldID=ID FROM MM_MeasurementMap WHERE StandardName=@conflictingName
		IF @oldID = NULL
			RETURN
	END
	IF @oldID = @ID
		RETURN

	DELETE FROM dbo.[IAS_MeasurementAddress] WHERE MeasurementID = @oldID AND IsManual = 0
	UPDATE dbo.[IAS_MeasurementAddress] SET MeasurementID = @ID WHERE MeasurementID = @oldID
	UPDATE dbo.[IRM_LabelMap] SET MeasurementID = @ID WHERE MeasurementID = @oldID
	UPDATE dbo.[MM_LabelMap] SET MeasurementID = @ID WHERE MeasurementID = @oldID
	IF exists (select * from sysobjects where name='RPT_MeasurementHierarchyNodes' and  xtype = 'U')
		UPDATE dbo.[RPT_MeasurementHierarchyNodes] SET MeasurementID = @ID WHERE MeasurementID = @oldID
	IF exists (select * from sysobjects where name='SRC_LDTypeMeasurement' and  xtype = 'U')
		UPDATE dbo.[SRC_LDTypeMeasurement] SET MeasurementID = @ID WHERE MeasurementID = @oldID

	IF(@deleteOld = 1)
		DELETE FROM dbo.MM_MeasurementMap WHERE ID = @oldID

GO

/****************************************************************************************************************
-- Rename standard measurements
****************************************************************************************************************/
if exists (select * from sysobjects where name='tmpRenameStandardEntry' and  xtype = 'P')
DROP PROCEDURE dbo.tmpRenameStandardEntry
GO

CREATE PROCEDURE dbo.tmpRenameStandardEntry
( 
	@ID SMALLINT,
	@oldStandardName NVARCHAR(200),
	@standardName NVARCHAR(200)
)
AS 
	-- the full name uses an escaped version of the standard name ...
	DECLARE @escapedStandardName NVARCHAR(300)

	SELECT @escapedStandardName = N'http://rddl.xmlinside.net/PowerMeasurement/configuration/measurement/1/#' + dbo.udfMMS_EscapeStandardName(@standardName)
	IF EXISTS(SELECT 1 FROM dbo.MM_MeasurementMap WHERE ID= @ID)
	BEGIN
		EXEC spEntryConflictHandler 0, @ID, @standardname
	END
	UPDATE dbo.MM_MeasurementMap SET StandardName = @standardName, FullName = @escapedStandardName  WHERE StandardName = @oldStandardName
GO

----------------------------------------------
----------------------------------------------
-----------------------------------------------
-- modifying spMM_AddMapEntry_Compatible
if exists (select * from sysobjects where name='spMM_AddMapEntry_Compatible' and  xtype = 'P')
DROP PROCEDURE [dbo].[spMM_AddMapEntry_Compatible]
GO

--*****************************************************************************
/* ##BD_BEGIN
Adds an entry for which the ID is known.
  If the specified ID already exists, an error is raised and the entry is not added.
  If the standard name has a duplicate:
   1) the entry's ID is reassigned on upgrades.
   2) or an error is raised on new installs.
This is primarily to allow compatibility with previous ID-based quantity schemes.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##BD_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/MM_MeasurementMap/StoredProcedures/10.spMM_AddMapEntry_Compatible.sql#2 $
-- 
--*****************************************************************************
CREATE PROCEDURE dbo.spMM_AddMapEntry_Compatible
(
	@ID				SMALLINT,
	@fullname		NVARCHAR(400),
	@standardname	NVARCHAR(250),
	@debug 			BIT = 0
)
AS
IF(@debug = 1)
BEGIN
	PRINT N'Compatible entry:' + CAST(@ID AS NVARCHAR(10)) + N' -> [' + @fullname + N'] ' + @standardname
	SET NOCOUNT OFF
END
ELSE
BEGIN
	SET NOCOUNT ON
END

DECLARE @result INT
DECLARE @ExistingName NVARCHAR(1000)
DECLARE @oldID SMALLINT

-- check for identical record
IF NOT EXISTS
(

	SELECT
		1 
	FROM 
		dbo.MM_MeasurementMap
	WHERE 
			ID            = @ID
		AND	FullName      = @fullname
		AND	StandardName  = @standardname
)
BEGIN
	SELECT
		@ExistingName=StandardName
	FROM
		dbo.MM_MeasurementMap 
	WHERE
		ID = @ID

	IF @ExistingName IS NOT NULL
	BEGIN
		RAISERROR (N'Cannot insert compatible map entry because the ID (%d) already exists.', 16, 1, @ID)
		RETURN -1
	END

	-- check names for uniqueness
	EXEC @oldID = dbo.spMM_CheckNewNames @fullname, @standardname
	IF @oldID != 0
	BEGIN
		--Upgrade only: rename before delete because of the foreign key constraint with IRM_LabelMap
		UPDATE MM_MeasurementMap Set StandardName = 'tmpForDelete', FullName = 'tmpForDelete' WHERE ID=@oldID
	END

	INSERT INTO dbo.MM_MeasurementMap
		(
			ID, 
			FullName, 
			StandardName
		)
	VALUES 
		(
			@ID, 
			@fullname, 
			@standardname
		)

	IF @oldID != 0
	BEGIN
		EXEC spEntryConflictHandler @oldID, @ID, @standardname
	END
	SET @result = @@ERROR
	RETURN @result
END
ELSE
-- identical record found.  Do nothing and return 0
BEGIN
RETURN 0
END
GO
--Creating [dbo].[spIRM_MapLabelToMeasurement]'
if exists (select * from sysobjects where name='spIRM_MapLabelToMeasurement' and  xtype = 'P')
DROP PROCEDURE spIRM_MapLabelToMeasurement
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].spIRM_MapLabelToMeasurement
	@Label NVARCHAR(1000), 
	@Pattern NVARCHAR(1000),
	@PhaseLabelType NVARCHAR(10),
	@Measurement NVARCHAR(1000)
AS

SET NOCOUNT ON

DECLARE @MeasurementID INT
DECLARE @ExistingMeasurementID INT
DECLARE @LabelMapID SMALLINT

DECLARE @PhaseLabelTypeID TINYINT
SET @MeasurementID = NULL
SET @LabelMapID  = NULL
SET @PhaseLabelTypeID  = NULL

-- get the measurement ID; this is required 
EXEC spMM_GetIDByName_StandardFirst @Measurement, @MeasurementID OUTPUT
IF @MeasurementID IS NULL OR @MeasurementID=0
BEGIN
   RAISERROR('Unknown measurement ''%s''', 16, 1, @Measurement)
   RETURN -1
END

-- make sure we have a pattern value
IF @Pattern IS NULL
BEGIN
	SET @Pattern=@Label
END

-- check the lengths of the label and pattern
-- this is mainly to help diagnose build script problems
DECLARE @fieldSize INT
SET @fieldSize = 100
IF LEN(@Label) > @fieldSize 
BEGIN
	RAISERROR('Label ''%s'' is larger than the database field (%d).', 16, 1, @Label, @fieldSize)
	RETURN -1
END
IF LEN(@Pattern) > @fieldSize
BEGIN
	RAISERROR('Pattern ''%s'' is larger than the database field (%d).', 16, 1, @Pattern, @fieldSize)
	RETURN -1
END

-- get the phase label ID
IF @PhaseLabelType IS NOT NULL
BEGIN
	SELECT 
		@PhaseLabelTypeID=ID 
	FROM 
		IRM_PhaseLabelType 
	WHERE 
		PhaseLabels=@PhaseLabelType
END

-- lookup the label map ID and insert a new record if not found
SELECT 
	@LabelMapID=ID,
	@ExistingMeasurementID = MeasurementID
FROM 
	IRM_LabelMap 
WHERE 
	Pattern=@Pattern
	
IF @LabelMapID IS NULL
BEGIN
	INSERT INTO IRM_LabelMap
		(MeasurementID, Pattern)
		VALUES
		(@MeasurementID, @Pattern)
	SELECT @LabelMapID = SCOPE_IDENTITY()
END
ELSE IF @ExistingMeasurementID != @MeasurementID
BEGIN
	-- we know the row as we have the identity value ...
	UPDATE IRM_LabelMap Set MeasurementID = @MeasurementID WHERE ID = @LabelMapID
END

-- This is unexpected - fail the call.
IF @LabelMapID IS NULL
BEGIN
   RAISERROR('Could not acquire label map ID for Label ''%s'' and Measurement ''%s''', 16, 1, @Label, @Measurement)
   RETURN -1
END

-- make sure we have a label variant entry
IF NOT EXISTS (SELECT Label,PhaseLabelTypeID from IRM_LabelVariant 
WHERE (Label = @Label AND (PhaseLabelTypeID IS NOT NULL AND PhaseLabelTypeID = @PhaseLabelTypeID))
OR (Label = @Label AND PhaseLabelTypeID IS NULL))
	INSERT INTO IRM_LabelVariant (Label,PhaseLabelTypeID,LabelMapID) VALUES (@Label,@PhaseLabelTypeID,@LabelMapID)

RETURN 0
GO


/******************************************************************************************************************
-- Check for escaped standard name
--*******************************************************************************************************************/
--Creating [dbo].[udfMMS_EscapeStandardName]'


if exists (select * from sysobjects where name='udfMMS_EscapeStandardName' and  xtype = 'FN')
	DROP FUNCTION udfMMS_EscapeStandardName
GO

--*****************************************************************************
/* ##FND_BEGIN Name ^^
Function that escapes a string containing a standard name. 
Checks that the string contains valid characers and performs a simple escaping
scheme by replacing spaces with %20 and ^ with %5E.

If there is a problem with the standard name provided to the function, 
NULL is returned.

<P><I>For more information, follow the Source link at the top of the page.</I></P>
##FND_END */
--
-- $Id: //rdsw/dev/cayman/SW_ION_E/database/Components/MMS_MeasurementMapSupport/UserDefinedFunctions/10.udfMMS_EscapeStandardName.sql#2 $
-- 
--*****************************************************************************
CREATE FUNCTION [dbo].[udfMMS_EscapeStandardName]
( 
	@unescapedStandardName NVARCHAR(200)
)
RETURNS NVARCHAR(200)
BEGIN 
	DECLARE @escapedStandardName NVARCHAR(200)
	SET @escapedStandardName = NULL
	DECLARE @isNameOK INT
	SELECT @isNameOK = dbo.udfMMS_IsStandardNameOK(@unescapedStandardName)
	IF(@isNameOK = 1)
	BEGIN
		-- let's do our simplistic escaping routine
		SET @escapedStandardName = REPLACE(@unescapedStandardName, N' ', N'%20')
		SET @escapedStandardName = REPLACE(@escapedStandardName, N'^', N'%5E')
	END
	RETURN @escapedStandardName
END
GO
