---=============================================================================================================
--- This script is the Microsoft SQL Server version for upgrading the TM Server schema from the internal version
--- 8.14 to 8.15. Please read the corresponding upgrade instructions before running this script. Contact SDL
--- Technical Support if you have any questions. 
---=============================================================================================================

-- all searches support descending ascending order
/* sps updated by the script
Container changes
	added new columns to translation_memories: aligner_definition VARBINARY(MAX), fga_support INT NOT NULL
	added new table-valued types: Hashes, TuFragmentIndexEntries, TuIds, TMVTokens, TMMKeys
	added SP add_translation_model
	update SP update_tm with @fga_support field
	update SP get_tms_by_resource with @fga_support field
	update SP get_tms with @fga_support field
	update SP add_tm with @fga_support field

Changes for all TM tables/sps
tables
	translation_units_ 
		add source_token_data, target_token_data, alignment_data, align_model_date fields
	translation_unit_fragments_ created

indexes
	idx_tufragments_ids_ created
	idx_tufragments_hashes_ created

table-valued types
	Hashes, SegmentHashes, TuFragmentIndexEntries, TuIds, TMVTokens, TMMKeys

sps
	add_tu_ update/get_tu_/update_tu_data_/get_tus_/get_tus_ex_/fuzzy_search_/exact_search_/exact_search_ex_/duplicate_search_/fuzzy_search_concordance_ 
		changed to return extra fields source_token_data, target_token_data,alignment_data, align_model_date
	tm_tdb_search_, add_tu_subsegmentindices_, update_tu_alignmentdata_, dta_search_, get_tus_by_ids_ added
	get_tus_ToAlign_
*/

-- make sure to run this script for the correct database:
-- USE **YOUR_CONTAINER_NAME**
--GO

set NOCOUNT on

--======================== TM Server Database Version Checking ==========================
	declare @VersionFrom varchar(100)
	declare @VersionTo varchar(100)
	set @versionFrom =  '8.14'
	set @VersionTo   =  '8.15'
	print '--> Start Upgrading Database Schema from TM Server ' + @VersionFrom + ' to ' + @VersionTo + '...';

	-- make sure that TM Server schema exists in the current database

	IF NOT EXISTS (SELECT 1 FROM dbo.sysobjects WHERE NAME='parameters' AND type='U')
	BEGIN
		declare @dbname varchar(256)
		select @dbname = db_name()
		raiserror('Cannot find configuration data for current database: %s - this is not a TM Server database/container',16,127, @dbname)
	END;

	-- Check the version to see if this script has already been run
	DECLARE @containerVersion nvarchar(256);
	declare @Success bit
	declare @errorMessage nvarchar(256);
	set @Success = 1
	
	SELECT @containerVersion=value 
		FROM [dbo].parameters 
		WHERE name='VERSION';

	if(@containerVersion >=  @VersionTo)
		begin
		print('--> WARNING: TM Server database has already been upgraded to ' + @VersionTo + '!')	
		end;
	else if (@containerVersion <> @versionFrom)
		begin
		print('--> Error: TM Server database version checking Failed!')
		declare @tempString varchar(100)
		set @tempString = 'Current TM Server schema version is not the expected version. Expected ' + @versionFrom + ', current version is %s'
		raiserror(@tempString,16,127, @containerVersion)
		end;
	else begin

		print('--> TM Server schema version check successful!');
		
		--------------------- Upgrade TMs ---------------------------------------
		DECLARE @tmId		int, @startID int, @finishID int;
		DECLARE @tmIdStr	nvarchar(10), @spName nvarchar(50), @sp_body nvarchar(MAX), @typeName nvarchar(50), @tableName nvarchar(50) 
		DECLARE @TMids		table(id int primary key Identity(1,1), tmId INT)
		DECLARE @alterTableCmd nvarchar(1000);

		BEGIN TRANSACTION;
BEGIN TRY
		print('----> Start Container specific TM operations Upgrade to version' + @VersionTo + ' ...');

		-----------------------------------
		-- alter tables
		-----------------------------------
		SET @alterTableCmd = 'alter table translation_memories add aligner_definition VARBINARY(MAX), fga_support INT NOT NULL default(0)';
		EXECUTE (@alterTableCmd);
		print('--> Altered translation_memories');
		-----------------------------------
		-- add table-valued types
		-----------------------------------

		SET @typeName = 'Hashes';
		IF NOT EXISTS (SELECT 1 FROM sys.types
			WHERE name = @typeName 
				AND is_table_type = 1
				) 
		BEGIN			
			set @sp_body = 'CREATE TYPE dbo.Hashes AS TABLE 
			(
				Hash bigint not null
			)';
			EXECUTE (@sp_body);
			print('--> Created ' + @typeName);	
		END;


		SET @typeName = 'SegmentHashes';
		IF NOT EXISTS (SELECT 1 FROM sys.types
			WHERE name = @typeName 
				AND is_table_type = 1
				) 
		BEGIN			
			set @sp_body = 'CREATE TYPE dbo.SegmentHashes AS TABLE 
			(
				SegmentHash bigint not null
			)';
			EXECUTE (@sp_body);
			print('--> Created ' + @typeName);	

		END;


		SET @typeName = 'TuFragmentIndexEntries';
		IF EXISTS (SELECT 1 FROM sys.types
			WHERE name = @typeName 
				AND is_table_type = 1
				) 
		BEGIN			
			set @sp_body = 'DROP TYPE [dbo].' + @typeName			
			Execute ( @sp_body);
			print('--> Dropped ' + @typeName);
		END;

		set @sp_body = 'CREATE TYPE dbo.TuFragmentIndexEntries AS TABLE
		(
			FragmentHash bigint not null,
			FragmentLength tinyint not null,
			FragmentSigWordCount tinyint not null
		)';
		EXECUTE (@sp_body);
		print('--> Created ' + @typeName);	

		SET @typeName = 'TuIds';
		IF EXISTS (SELECT 1 FROM sys.types
			WHERE name = @typeName 
				AND is_table_type = 1
				) 
		BEGIN			
			set @sp_body = 'DROP TYPE [dbo].' + @typeName			
			Execute ( @sp_body);
			print('--> Dropped ' + @typeName);
		END;


		set @sp_body = 'CREATE TYPE dbo.TuIds AS TABLE
		(
			TuId integer not null
		)';
		EXECUTE (@sp_body);
		print('--> Created ' + @typeName);	

		SET @typeName = 'TMVTokens';
		IF EXISTS (SELECT 1 FROM sys.types
			WHERE name = @typeName 
				AND is_table_type = 1
				) 
		BEGIN			
			set @sp_body = 'DROP TYPE [dbo].' + @typeName			
			Execute ( @sp_body);
			print('--> Dropped ' + @typeName);
		END;

		set @sp_body = 'CREATE TYPE dbo.TMVTokens AS TABLE
		(
			vocab VARBINARY(200) NOT NULL
		)';
		EXECUTE (@sp_body);
		print('--> Created ' + @typeName);	

		SET @typeName = 'TMMKeys';
		IF EXISTS (SELECT 1 FROM sys.types
			WHERE name = @typeName 
				AND is_table_type = 1
				) 
		BEGIN			
			set @sp_body = 'DROP TYPE [dbo].' + @typeName			
			Execute ( @sp_body);
			print('--> Dropped ' + @typeName);
		END;

		set @sp_body = 'CREATE TYPE dbo.TMMKeys AS TABLE
		(
			matrixkey int NOT NULL
		)';
		EXECUTE (@sp_body);
		print('--> Created ' + @typeName);	
		

		-----------------------------------
		-- create tables
		-----------------------------------

		set @tableName = 'translation_models';
		set @sp_body = 'CREATE TABLE dbo.' + @tableName + ' (
			id INT IDENTITY NOT NULL PRIMARY KEY,
			name NVARCHAR(200) NOT NULL UNIQUE,
			source_lcid INT NOT NULL,
			target_lcid INT NOT NULL,
			model_date DATETIME,
			model_type INT -- future use
		)';
		EXECUTE (@sp_body);
		print('--> Created ' + @tableName);	

		set @tableName = 'translation_model_corpora';
		set @sp_body = 'CREATE TABLE dbo.' + @tableName + ' (
			id INT IDENTITY NOT NULL PRIMARY KEY,
			model_id INT NOT NULL CONSTRAINT FK_tmc_trans_model REFERENCES dbo.translation_models(id) ON DELETE CASCADE,
			alignable_corpus_id VARBINARY(MAX) NOT NULL				
		)';
		EXECUTE (@sp_body);
		print('--> Created ' + @tableName);	

		-----------------------------------
		-- update sps
		-----------------------------------

		SET @spName = 'update_tm';
		IF EXISTS (SELECT 1 FROM information_schema.routines 
			WHERE routine_name = @spName 
				AND routine_type = 'PROCEDURE'
				AND ROUTINE_CATALOG = db_name()
				) 
		BEGIN			
			set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
			Execute ( @sp_body);
			print('--> Dropped ' + @spName);
		END;
			
		set @sp_body = 'CREATE PROCEDURE dbo.update_tm @id INT, @guid UNIQUEIDENTIFIER, @name NVARCHAR(200), @src_lang NVARCHAR(50), @trg_lang NVARCHAR(50), 
		@copyright NVARCHAR(2000) = NULL, @description NVARCHAR(2000) = NULL, @settings INT, @cru NVARCHAR(50), @crd DATETIME, 
		@expd DATETIME = NULL, @fuzzy_indexes INT, @fga_support INT AS
		UPDATE dbo.translation_memories SET guid = @guid, name = @name, source_language = @src_lang, target_language = @trg_lang, 
		copyright = @copyright, description = @description, settings = @settings, creation_user = @cru, creation_date = @crd, 
		expiration_date = @expd, fuzzy_indexes = @fuzzy_indexes, fga_support = @fga_support
		WHERE id = @id'

		EXECUTE (@sp_body);
		print('--> Created ' + @spName);	


		SET @spName = 'add_translation_model';
		IF EXISTS (SELECT 1 FROM information_schema.routines 
			WHERE routine_name = @spName 
				AND routine_type = 'PROCEDURE'
				AND ROUTINE_CATALOG = db_name()
				) 
		BEGIN			
			set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
			Execute ( @sp_body);
			print('--> Dropped ' + @spName);
		END;
			
		set @sp_body = 'CREATE PROCEDURE dbo.add_translation_model @name NVARCHAR(200), @src_lcid INT, @trg_lcid INT, @model_type INT
		AS
			DECLARE @model_id INT
			SET NOCOUNT ON
			INSERT INTO dbo.translation_models(name, source_lcid, target_lcid, model_type) 
				VALUES(@name, @src_lcid, @trg_lcid, @model_type)
			SET @model_id = SCOPE_IDENTITY() 
			SELECT @model_id'

		EXECUTE (@sp_body);
		print('--> Created ' + @spName);	


		SET @spName = 'get_tms_by_resource';
		IF EXISTS (SELECT 1 FROM information_schema.routines 
			WHERE routine_name = @spName 
				AND routine_type = 'PROCEDURE'
				AND ROUTINE_CATALOG = db_name()
				) 
		BEGIN			
			set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
			Execute ( @sp_body);
			print('--> Dropped ' + @spName);
		END;
			
		set @sp_body = 'CREATE PROCEDURE dbo.get_tms_by_resource @resource_id INT AS
			SET NOCOUNT ON
			SELECT t.id, t.guid, t.name, t.source_language, t.target_language, 
				t.copyright, t.description, t.settings, t.creation_user, t.creation_date, 
				t.expiration_date, t.fuzzy_indexes, t.last_recompute_date, t.last_recompute_size, t.fga_support
			FROM dbo.translation_memories t
			INNER JOIN dbo.tm_resources tr ON t.id = tr.tm_id
			WHERE tr.resource_id = @resource_id
			';

		EXECUTE (@sp_body);
		print('--> Created ' + @spName);	


		
		SET @spName = 'get_tms';
		IF EXISTS (SELECT 1 FROM information_schema.routines 
			WHERE routine_name = @spName 
				AND routine_type = 'PROCEDURE'
				AND ROUTINE_CATALOG = db_name()
				) 
		BEGIN			
			set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
			Execute ( @sp_body);
			print('--> Dropped ' + @spName);
		END;
			
		set @sp_body = 'CREATE PROCEDURE dbo.get_tms @id INT = NULL, @guid UNIQUEIDENTIFIER = NULL, @name NVARCHAR(200) = NULL AS
	SET NOCOUNT ON
	IF @id IS NOT NULL
		SELECT id, guid, name, source_language, target_language, copyright, description, settings, creation_user, creation_date, expiration_date, 
			fuzzy_indexes, last_recompute_date, last_recompute_size, fga_support
			FROM dbo.translation_memories WHERE id = @id
	ELSE IF @guid IS NOT NULL
		SELECT id, guid, name, source_language, target_language, copyright, description, settings, creation_user, creation_date, expiration_date, 
			fuzzy_indexes, last_recompute_date, last_recompute_size, fga_support
			FROM dbo.translation_memories WHERE guid = @guid
	ELSE IF @name IS NOT NULL
		SELECT id, guid, name, source_language, target_language, copyright, description, settings, creation_user, creation_date, expiration_date, 
			fuzzy_indexes, last_recompute_date, last_recompute_size, fga_support
			FROM dbo.translation_memories WHERE name = @name
	ELSE
		SELECT id, guid, name, source_language, target_language, copyright, description, settings, creation_user, creation_date, expiration_date, 
			fuzzy_indexes, last_recompute_date, last_recompute_size, fga_support
			FROM dbo.translation_memories'

		EXECUTE (@sp_body);
		print('--> Created ' + @spName);	

		
		SET @spName = 'add_tm';
		IF EXISTS (SELECT 1 FROM information_schema.routines 
			WHERE routine_name = @spName 
				AND routine_type = 'PROCEDURE'
				AND ROUTINE_CATALOG = db_name()
				) 
		BEGIN			
			set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
			Execute ( @sp_body);
			print('--> Dropped ' + @spName);
		END;
			
		set @sp_body = 'CREATE PROCEDURE dbo.add_tm @guid UNIQUEIDENTIFIER, @name NVARCHAR(200), @src_lang NVARCHAR(50), @trg_lang NVARCHAR(50), @copyright NVARCHAR(MAX) = NULL, 
	@description NVARCHAR(MAX) = NULL, @settings INT, @cru NVARCHAR(50), @crd DATETIME, @expd DATETIME = NULL, @fuzzy_indexes INT, @fga_support INT AS
	DECLARE @tm_id INT
	SET NOCOUNT ON
	INSERT INTO dbo.translation_memories(guid, name, source_language, target_language, copyright, description, settings, creation_user, 
		creation_date, expiration_date, fuzzy_indexes, fga_support) 
		VALUES(@guid, @name, @src_lang, @trg_lang, @copyright, @description, @settings, @cru, @crd, @expd, @fuzzy_indexes, @fga_support)
	SET @tm_id = SCOPE_IDENTITY() 
	INSERT INTO dbo.parameters(translation_memory_id, name, value) VALUES(@tm_id, ''ADDTOMINSCORE'', 0)
	INSERT INTO dbo.parameters(translation_memory_id, name, value) VALUES(@tm_id, ''MINHAVING1'', 3)
	INSERT INTO dbo.parameters(translation_memory_id, name, value) VALUES(@tm_id, ''MINHAVING2'', 5)
	INSERT INTO dbo.parameters(translation_memory_id, name, value) VALUES(@tm_id, ''MINHAVING4'', 5)
	INSERT INTO dbo.parameters(translation_memory_id, name, value) VALUES(@tm_id, ''MINHAVING8'', 3)
	SELECT @tm_id'

		EXECUTE (@sp_body);
		print('--> Created ' + @spName);	


		print('----> End Container specific TM operations Upgrade to version' + @VersionTo + ' ...');

		----------------------------------------------------------------------------
		-- convert TMS
		----------------------------------------------------------------------------

		insert into @TMids
		select id from [dbo].translation_memories;
		
		select @startID = min(id), @finishID = max(id) 
		from @TMids
		
		print( '----> Start TM ' + @VersionTo + ' Upgrade...');

		while @startID <= @finishID
		begin
			select @tmId= tmId
			from @TMids 
			where id = @startID;
				
			SET @tmIdStr = convert(NVARCHAR, @tmId);
				
			print( '------>  Upgrading TM#' + @tmIdStr);	

			SET @alterTableCmd = ' alter table translation_units_' + @tmIdStr + ' add source_token_data VARBINARY(MAX), target_token_data VARBINARY(MAX), alignment_data VARBINARY(MAX), align_model_date DATETIME';
			Execute ( @alterTableCmd);
			print('--> Altered translation_units_' + @tmIdStr);

			SET @sp_body = replace('CREATE TABLE dbo.translation_unit_fragments_%%(
			translation_unit_id INT NOT NULL 
				CONSTRAINT FK_tuf_tu1_%% REFERENCES dbo.translation_units_%%(id) ON DELETE CASCADE,
			fragment_hash bigint NOT NULL,
			fragment_length TINYINT NOT NULL, 
			fragment_sigwords TINYINT NOT NULL
			)','%%', @tmId);
			EXECUTE(@sp_body);
			print('--> Created translation_unit_fragments_' + @tmIdStr);

			SET @sp_body = replace('CREATE INDEX idx_tufragments_ids_%% ON dbo.translation_unit_fragments_%%(translation_unit_id)','%%', @tmId)
			EXECUTE(@sp_body)
			print('--> Created idx_tufragments_ids_' + @tmIdStr);

			SET @sp_body = replace('CREATE INDEX idx_tufragments_hashes_%% ON dbo.translation_unit_fragments_%%(fragment_hash)','%%', @tmId)
			EXECUTE(@sp_body);
			print('--> Created idx_tufragments_hashes_' + @tmIdStr);






			--------------------------------------------------------------------------------
			SET @spName = 'add_tu_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.add_tu_%% @guid UNIQUEIDENTIFIER, 
	@source_hash BIGINT, @source_text NTEXT, 
	@target_hash BIGINT, @target_text NTEXT, 
	@crd DATETIME, @cru NVARCHAR(50), 
	@chd DATETIME, @chu NVARCHAR(50), @lud DATETIME, @luu NVARCHAR(50), 
	@usc INT = 0, @id INT = 0, @flags INT = 0,
	@data1 VARBINARY(MAX) = NULL, 
	@data2 VARBINARY(MAX) = NULL, 
	@data4 VARBINARY(MAX) = NULL,
	@data8 VARBINARY(MAX) = NULL,
	@source_token_data VARBINARY(MAX) = NULL,
	@target_token_data VARBINARY(MAX) = NULL,
	@alignment_data VARBINARY(MAX) = NULL,
	@align_model_date DATETIME = NULL
	 
AS
	SET NOCOUNT ON
	IF @id = 0 BEGIN
	
		INSERT INTO dbo.translation_units_%%(guid, source_hash, source_segment, 
			target_hash, target_segment, 
			creation_date, creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date)
			VALUES(@guid, @source_hash, @source_text, @target_hash, @target_text, 
			@crd, @cru, @chd, @chu, @lud, @luu, @usc, @flags, @source_token_data, @target_token_data, @alignment_data, @align_model_date)
		
		SET @id = CAST(SCOPE_IDENTITY() AS INT)
		
	END 
	ELSE BEGIN
	
		SET IDENTITY_INSERT dbo.translation_units_%% ON
		INSERT INTO dbo.translation_units_%%(id, guid, source_hash, source_segment, 
			target_hash, target_segment, 
			creation_date, creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date)
			VALUES(@id, @guid, @source_hash, @source_text, @target_hash, @target_text, 
			@crd, @cru, @chd, @chu, @lud, @luu, @usc, @flags, @source_token_data, @target_token_data, @alignment_data, @align_model_date)
		SET IDENTITY_INSERT dbo.translation_units_%% OFF
		
	END
		
	IF @data1 IS NOT NULL
		exec dbo.add_features_%% @id, @data1, 1
	IF @data2 IS NOT NULL
		exec dbo.add_features_%% @id, @data2, 2
	IF @data4 IS NOT NULL
		exec dbo.add_features_%% @id, @data4, 4
	IF @data8 IS NOT NULL
		exec dbo.add_features_%% @id, @data8, 8
		
	SELECT @id
	','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);			

			--------------------------------------------------------------------------------
			SET @spName = 'get_tu_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.get_tu_%% @id INT AS
	SET NOCOUNT ON
	SELECT id, guid, %%, source_hash, source_segment, 0, 0, 
		target_hash, target_segment, 0, 0, creation_date, 
		creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
		FROM dbo.translation_units_%% WHERE id = @id

		   SELECT da.translation_unit_id, da.attribute_id, a.name, a.type, da.value
			  FROM date_attributes_%% da INNER JOIN dbo.attributes_%% a ON da.attribute_id = a.id
			  WHERE da.translation_unit_id = @id
			  ORDER BY da.attribute_id;

		   SELECT sa.translation_unit_id, sa.attribute_id,a.name, a.type, sa.value
			  FROM string_attributes_%% sa INNER JOIN dbo.attributes_%% a ON sa.attribute_id = a.id
			  WHERE sa.translation_unit_id = @id
			  ORDER BY sa.attribute_id;

		  SELECT na.translation_unit_id, na.attribute_id, a.name, a.type, na.value
			  FROM numeric_attributes_%% na INNER JOIN dbo.attributes_%% a ON na.attribute_id = a.id
			  WHERE na.translation_unit_id = @id
			  ORDER BY na.attribute_id;

		  SELECT pa.translation_unit_id, pv.attribute_id, a.name, a.type, pa.picklist_value_id
			  FROM picklist_attributes_%% pa 
					INNER JOIN picklist_values_%% pv ON pv.id = pa.picklist_value_id
					INNER JOIN dbo.attributes_%% a ON pv.attribute_id = a.id
			  WHERE pa.translation_unit_id = @id
			  ORDER BY pv.attribute_id;

		  SELECT translation_unit_id, left_source_context, left_target_context
			  FROM translation_unit_contexts_%%
			  WHERE translation_unit_id = @id
	','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);
			--------------------------------------------------------------------------------
			SET @spName = 'add_tu_subsegmentindices_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;

			set @sp_body = replace('CREATE PROCEDURE dbo.add_tu_subsegmentindices_%% @guid UNIQUEIDENTIFIER, @index_entries  as TuFragmentIndexEntries readonly
AS
	DECLARE @tu_id INT
	SELECT @tu_id = id FROM dbo.translation_units_%% WHERE guid = @guid
	DELETE FROM dbo.translation_unit_fragments_%% WHERE translation_unit_id = @tu_id

	INSERT INTO dbo.translation_unit_fragments_%%(translation_unit_id, fragment_hash, fragment_length, fragment_sigwords ) 
		SELECT @tu_id, FragmentHash, FragmentLength, FragmentSigWordCount FROM @index_entries
			','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);
			--------------------------------------------------------------------------------
			SET @spName = 'update_tu_alignmentdata_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;

			set @sp_body = replace('CREATE PROCEDURE dbo.update_tu_alignmentdata_%% @guid UNIQUEIDENTIFIER, @data VARBINARY(MAX) = NULL, @align_model_date DATETIME = NULL
AS
	DECLARE @tu_id INT
	SELECT @tu_id = id FROM dbo.translation_units_%% WHERE guid = @guid

	UPDATE dbo.translation_units_%% SET alignment_data = @data, align_model_date = align_model_date WHERE id = @tu_id
			','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);
			--------------------------------------------------------------------------------
			SET @spName = 'dta_search_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;

			set @sp_body = replace('CREATE PROCEDURE dbo.dta_search_%%
	@source_hashes  as Hashes readonly,
	@min_length TINYINT,
	@min_sigwords TINYINT,
	@count INT, 
	@last_id INT,
	@DescendingOrder BIT

AS
	SET NOCOUNT ON
	
	SELECT TOP (@count) translation_unit_id, fragment_hash
	FROM dbo.translation_unit_fragments_%%
	inner join @source_hashes as sourceHashes on dbo.translation_unit_fragments_%%.fragment_hash = sourceHashes.Hash
	--inner join dbo.translation_units_%% on dbo.translation_unit_fragments_%%.translation_unit_id = id
	WHERE  
		(@DescendingOrder=0 and translation_unit_id > @last_id or @DescendingOrder=1 and translation_unit_id <= @last_id) 
		AND
		(fragment_length >= @min_length AND fragment_sigwords >= @min_sigwords)
	order by
		case when @DescendingOrder=0 then translation_unit_id end ASC,  
		case when @DescendingOrder=1 then translation_unit_id end DESC  
			','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);
			--------------------------------------------------------------------------------
			SET @spName = 'get_tus_by_ids_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;

			set @sp_body = replace('CREATE PROCEDURE dbo.get_tus_by_ids_%%
	@tu_ids as TuIds readonly,
	@LeftTuContextSource BIGINT,
	@LeftTuContextTarget BIGINT,
	@DescendingOrder BIT

AS
	SET NOCOUNT ON
	
	
	SELECT tu.id, guid, 1, source_hash, source_segment, 
		target_hash, target_segment, creation_date, 
		creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
	FROM dbo.translation_units_%% tu  
		INNER JOIN @tu_ids res ON tu.id = res.TuId
	ORDER BY 
				case when @DescendingOrder=0 then change_date end ASC,  
				case when @DescendingOrder=0 then tu.id end ASC,  
				case when @DescendingOrder=1 then change_date end DESC,  
				case when @DescendingOrder=1 then tu.id end DESC

	SELECT da.translation_unit_id, da.attribute_id, a.name, a.type, da.value 
				FROM dbo.date_attributes_%% da 
			INNER JOIN dbo.attributes_%% a ON da.attribute_id = a.id 
					INNER JOIN @tu_ids res ON da.translation_unit_id = res.TuId
				ORDER BY da.translation_unit_id, da.attribute_id
		
	SELECT sa.translation_unit_id, sa.attribute_id, a.name, a.type, sa.value 
				FROM dbo.string_attributes_%% sa 
			INNER JOIN dbo.attributes_%% a ON sa.attribute_id = a.id
					INNER JOIN @tu_ids res ON sa.translation_unit_id = res.TuId
				ORDER BY sa.translation_unit_id, sa.attribute_id
		
	SELECT na.translation_unit_id, na.attribute_id, a.name, a.type, na.value 
				FROM dbo.numeric_attributes_%% na 
			INNER JOIN dbo.attributes_%% a ON na.attribute_id = a.id
					INNER JOIN @tu_ids res ON na.translation_unit_id = res.TuId
				ORDER BY na.translation_unit_id, na.attribute_id
		
	SELECT pa.translation_unit_id, pv.attribute_id, a.name, a.type, pa.picklist_value_id 
			FROM dbo.picklist_attributes_%% pa 
				INNER JOIN dbo.picklist_values_%% pv ON pv.id = pa.picklist_value_id
			INNER JOIN dbo.attributes_%% a ON pv.attribute_id = a.id
				INNER JOIN @tu_ids res ON pa.translation_unit_id = res.TuId
			ORDER BY pa.translation_unit_id, pv.attribute_id
	
	if (@LeftTuContextSource != -1 )--can''t have source -1
	begin
		DECLARE @FullContextMatch TABLE(translation_unit_id INT NOT NULL PRIMARY KEY, left_source_context BIGINT, left_target_context BIGINT)
		
		INSERT INTO @FullContextMatch
		SELECT ctx.translation_unit_id, left_source_context, left_target_context 
			FROM dbo.translation_unit_contexts_%% ctx
				INNER JOIN @tu_ids res ON ctx.translation_unit_id = res.TuId
			WHERE left_source_context = @LeftTuContextSource and left_target_context = @LeftTuContextTarget 
					and @LeftTuContextTarget != -1 --can''t have target -1
			ORDER BY translation_unit_id

		--retrive either context match for source and target or just matches for source
		IF EXISTS(SELECT * FROM @FullContextMatch)
			SELECT translation_unit_id, left_source_context, left_target_context 
			FROM @FullContextMatch
		ELSE
			SELECT ctx.translation_unit_id, left_source_context, left_target_context 
			FROM dbo.translation_unit_contexts_%% ctx
				INNER JOIN @tu_ids res ON ctx.translation_unit_id = res.TuId
			WHERE left_source_context = @LeftTuContextSource 
			ORDER BY translation_unit_id
	end
			','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);
			--------------------------------------------------------------------------------

			SET @spName = 'update_tu_data_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.update_tu_data_%% 
	@id INT, 
	@source_hash BIGINT, 
	@source_text NTEXT, 
	@target_hash BIGINT, 
	@target_text NTEXT,
	@data1 VARBINARY(MAX) = NULL, 
	@data2 VARBINARY(MAX) = NULL, 
	@data4 VARBINARY(MAX) = NULL,
	@data8 VARBINARY(MAX) = NULL,
	@source_token_data VARBINARY(MAX) = NULL,
	@target_token_data VARBINARY(MAX) = NULL
AS
	SET NOCOUNT OFF
	-- delete existing fuzzy index data
	DELETE FROM dbo.fuzzy_index1_%% WHERE translation_unit_id = @id
	DELETE FROM dbo.fuzzy_index2_%% WHERE translation_unit_id = @id
	DELETE FROM dbo.fuzzy_index4_%% WHERE translation_unit_id = @id
	DELETE FROM dbo.fuzzy_index8_%% WHERE translation_unit_id = @id
	
	UPDATE dbo.translation_units_%% 
	SET source_hash = @source_hash, 
		source_segment = @source_text, 
		target_hash = @target_hash, 
		target_segment = @target_text,
		source_token_data = @source_token_data,
		target_token_data = @target_token_data
	WHERE 
		id = @id		
	
	IF @data1 IS NOT NULL
		exec dbo.add_features_%% @id, @data1, 1
	IF @data2 IS NOT NULL
		exec dbo.add_features_%% @id, @data2, 2
	IF @data4 IS NOT NULL
		exec dbo.add_features_%% @id, @data4, 4
	IF @data8 IS NOT NULL
		exec dbo.add_features_%% @id, @data8, 8
','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);
--------------------------------------------------------------------------------
			SET @spName = 'get_tus_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.get_tus_%% @start_after INT, @count INT, @forward INT AS
	SET NOCOUNT ON
	IF @forward > 0
		SELECT TOP (@count) id, guid, %%, source_hash, source_segment, 0, 0, 
			target_hash, target_segment, 0, 0,
			creation_date, creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
			FROM dbo.translation_units_%% WHERE id > @start_after
			ORDER BY id ASC
	ELSE		
		SELECT TOP (@count) id, guid, %%, source_hash, source_segment, 0, 0, 
			target_hash, target_segment, 0, 0, 
			creation_date, creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
			FROM dbo.translation_units_%% WHERE id <= @start_after
			ORDER BY id DESC
			
','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);
--------------------------------------------------------------------------------
			SET @spName = 'get_tus_ex_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.get_tus_ex_%% 
	@start_after INT, 
	@count INT, 
	@forward INT 
AS
	SET NOCOUNT ON
	
	DECLARE @ResultIDs TABLE(id INT NOT NULL PRIMARY KEY)
	
	IF @forward > 0
		INSERT INTO @ResultIDs 
			SELECT TOP (@count) id
			FROM dbo.translation_units_%% 
			WHERE id > @start_after
			ORDER BY id ASC
	ELSE
		INSERT INTO @ResultIDs
			SELECT TOP (@count) id
			FROM dbo.translation_units_%% 
			WHERE id <= @start_after
			ORDER BY id DESC

	SELECT id, guid, %%, source_hash, source_segment, 
		target_hash, target_segment, 
		creation_date, creation_user, change_date, change_user, 
		last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
	FROM dbo.translation_units_%% 
	WHERE id IN (SELECT id FROM @ResultIDs)
	
	SELECT da.translation_unit_id, da.attribute_id, a.name, a.type, da.value 
		FROM dbo.date_attributes_%% da INNER JOIN dbo.attributes_%% a ON da.attribute_id = a.id 	
		WHERE da.translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY da.translation_unit_id, da.attribute_id		
		
	SELECT sa.translation_unit_id, sa.attribute_id, a.name, a.type, sa.value 
		FROM dbo.string_attributes_%% sa INNER JOIN dbo.attributes_%% a ON sa.attribute_id = a.id 		
		WHERE sa.translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY sa.translation_unit_id, sa.attribute_id
		
	SELECT na.translation_unit_id, na.attribute_id, a.name, a.type, na.value 
	FROM dbo.numeric_attributes_%% na  INNER JOIN dbo.attributes_%% a ON na.attribute_id = a.id 
		WHERE na.translation_unit_id IN (SELECT id FROM @ResultIDs) 

	ORDER BY na.translation_unit_id, na.attribute_id
	
	SELECT pa.translation_unit_id, pv.attribute_id,  a.name, a.type, pa.picklist_value_id 
	FROM dbo.picklist_attributes_%% pa 
		INNER JOIN dbo.picklist_values_%% pv ON pv.id = pa.picklist_value_id		
		INNER JOIN dbo.attributes_%% a ON pv.attribute_id = a.id
		WHERE pa.translation_unit_id IN (SELECT id FROM @ResultIDs) 
	ORDER BY pa.translation_unit_id, pv.attribute_id

	SELECT translation_unit_id, left_source_context, left_target_context 
		FROM dbo.translation_unit_contexts_%% 
		WHERE	translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY translation_unit_id
			
','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);

--------------------------------------------------------------------------------
			SET @spName = 'fuzzy_search_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.fuzzy_search_%% @features NVARCHAR(MAX), @length INT, @minScore INT, @count INT, @type INT, @last_id INT, @LeftTuContextSource BIGINT,
									@LeftTuContextTarget BIGINT, @DescendingOrder BIT
	AS
	DECLARE @cmd NVARCHAR(MAX)
	SET NOCOUNT ON
	DECLARE @maxLength INT, @ignored INT, @minMatches INT, @newMinMatches INT, @minHaving INT
	SET @minHaving = (SELECT value FROM dbo.parameters WHERE translation_memory_id = %% AND name = ''MINHAVING'' + LTRIM(STR(@type)))
	SET @minScore = @minScore + (SELECT value FROM dbo.parameters WHERE translation_memory_id = %% AND name = ''ADDTOMINSCORE'')
	IF @minScore > 100 SET @minScore = 100
	SET @minMatches = (@minScore * @length + 99) / 100
	SET @maxLength = (@length * 100 + @minScore - 1) / @minScore
	SET @newMinMatches = @minMatches
	
	DECLARE @ResultTable TABLE(id INT PRIMARY KEY, [length] int, matches INT)
	CREATE TABLE #CountTable (feature INT PRIMARY KEY, occurences INT, ignored INT DEFAULT 0)

	DECLARE @s NVARCHAR(MAX), @dataLen INT, @pos INT, @w NVARCHAR(50), @found INT
	
	SET @dataLen = (DATALENGTH(@features) / 2) - 2
	SET @s = SUBSTRING(@features, 2, @dataLen)

	SET @pos = 1
	SET @found = CHARINDEX(N'','', @s, @pos)
	WHILE @found > 0
	BEGIN
		SET @w = SUBSTRING(@s, @pos, @found - @pos)
		if  @newMinMatches > @minHaving
			INSERT INTO #CountTable VALUES(@w, dbo.get_feature_frequency_%%(@w, @type), 0)
		else
			INSERT INTO #CountTable VALUES(@w, 0, 0)
	
		SET @pos = @found + 1
		SET @found = CHARINDEX(N'','', @s, @pos)
	END
	
	SET @w = SUBSTRING(@s, @pos, @dataLen + 1 - @pos)
	
	if  @newMinMatches > @minHaving
		INSERT INTO #CountTable VALUES(@w, dbo.get_feature_frequency_%%(@w, @type), 0)
	else
		INSERT INTO #CountTable VALUES(@w, 0, 0)
		
			
	IF @length > 1
	BEGIN
		DECLARE @occ INT
		
		WHILE @newMinMatches > @minHaving
		BEGIN
			SELECT @occ = (SELECT MAX(occurences) FROM #CountTable WHERE ignored = 0)
			IF @occ IS NULL OR @occ < 1000
				BREAK;

			UPDATE TOP(1) #CountTable 
				SET ignored = 1 
				WHERE occurences = @occ
			
			SET @newMinMatches = @newMinMatches - 1
		END
	END

	SELECT @ignored = COUNT(*) FROM #CountTable WHERE ignored = 1

	IF @type = 1	
			INSERT INTO @ResultTable 
			SELECT translation_unit_id, fi.[length], COUNT(*) AS matches 
			FROM dbo.fuzzy_index1_%% fi
				INNER JOIN #CountTable ct
				ON fi.feature = ct.feature
			WHERE 
				ct.ignored = 0
				AND fi.length BETWEEN @minMatches AND @maxLength 
				AND (@DescendingOrder=0 and fi.translation_unit_id > @last_id or @DescendingOrder=1 and fi.translation_unit_id < @last_id)
			GROUP BY fi.translation_unit_id, fi.[length]   
			HAVING COUNT(*) >= @newMinMatches
			
	ELSE IF @type = 2	
	
		INSERT INTO @ResultTable 
			SELECT translation_unit_id, fi.[length], COUNT(*) AS matches 
			FROM dbo.fuzzy_index2_%% fi
				INNER JOIN #CountTable ct
				ON fi.feature = ct.feature
			WHERE 
				ct.ignored = 0
				AND (@DescendingOrder=0 and fi.translation_unit_id > @last_id or @DescendingOrder=1 and fi.translation_unit_id < @last_id)
			GROUP BY fi.translation_unit_id, fi.[length]   
			HAVING COUNT(*) >= @newMinMatches
			
	ELSE IF @type = 4	
	
		INSERT INTO @ResultTable 
			SELECT translation_unit_id, fi.[length], COUNT(*) AS matches 
			FROM dbo.fuzzy_index4_%% fi
				INNER JOIN #CountTable ct
				ON fi.feature = ct.feature
			WHERE 
				ct.ignored = 0
				AND (@DescendingOrder=0 and fi.translation_unit_id > @last_id or @DescendingOrder=1 and fi.translation_unit_id < @last_id)
			GROUP BY fi.translation_unit_id, fi.[length]   
			HAVING COUNT(*) >= @newMinMatches
		
	ELSE IF @type = 8 	
		INSERT INTO @ResultTable 
			SELECT translation_unit_id, fi.[length], COUNT(*) AS matches 
			FROM dbo.fuzzy_index8_%% fi
				INNER JOIN #CountTable ct
				ON fi.feature = ct.feature
			WHERE 
				ct.ignored = 0
				AND fi.length BETWEEN @minMatches AND @maxLength 
			AND (@DescendingOrder=0 and fi.translation_unit_id > @last_id or @DescendingOrder=1 and fi.translation_unit_id < @last_id)
			GROUP BY fi.translation_unit_id, fi.[length]   
			HAVING COUNT(*) >= @newMinMatches

	IF @ignored > 0
	BEGIN

		DECLARE @resultTopMinMatches INT
		
		SELECT @resultTopMinMatches = MIN(matches)
		FROM @ResultTable
		WHERE id IN (SELECT TOP(@count) id FROM @ResultTable ORDER BY matches DESC)

		DELETE FROM @ResultTable
		WHERE 
			matches + @ignored < @minMatches
			OR matches + @ignored < @resultTopMinMatches
				
		IF @type = 1
			UPDATE @ResultTable SET matches = matches + (SELECT COUNT(*) FROM dbo.fuzzy_index1_%% fi WITH (INDEX = fi1_address_%%)
				JOIN @ResultTable r ON r.id = fi.translation_unit_id 
                JOIN #CountTable c ON fi.feature = c.feature 
				WHERE c.ignored = 1)
		ELSE IF @type = 2
			UPDATE @ResultTable SET matches = matches + (SELECT COUNT(*) FROM dbo.fuzzy_index2_%% fi WITH (INDEX = fi2_address_%%)
				JOIN @ResultTable r ON r.id = fi.translation_unit_id 
                JOIN #CountTable c ON fi.feature = c.feature 
                WHERE c.ignored = 1)
		ELSE IF @type = 8
			UPDATE @ResultTable SET matches = matches + (SELECT COUNT(*) FROM dbo.fuzzy_index8_%% fi WITH (INDEX = fi8_address_%%)
				JOIN @ResultTable r ON r.id = fi.translation_unit_id 
                JOIN #CountTable c ON fi.feature = c.feature 
                WHERE c.ignored = 1)
		ELSE IF @type = 4
			UPDATE @ResultTable SET matches = matches + (SELECT COUNT(*) FROM dbo.fuzzy_index4_%% fi WITH (INDEX = fi4_address_%%)
				JOIN @ResultTable r ON r.id = fi.translation_unit_id 
                JOIN #CountTable c ON fi.feature = c.feature 
                WHERE c.ignored = 1)
    END
	
	DECLARE @ResultIDs TABLE(id INT)
	if @DescendingOrder = 1
	begin
		INSERT INTO @ResultIDs 
		SELECT TOP (@count) rt.id 
		FROM @ResultTable rt
		WHERE rt.matches >= @minMatches 
		ORDER BY rt.matches DESC, rt.[length], rt.id desc
	end
	else
	begin
		INSERT INTO @ResultIDs 
		SELECT TOP (@count) rt.id 
		FROM @ResultTable rt
		WHERE rt.matches >= @minMatches 
		ORDER BY rt.matches DESC, rt.[length], rt.id ASC
	end


	SELECT id, guid, 1, source_hash, source_segment,
		target_hash, target_segment,
		creation_date, creation_user, change_date, change_user, 
		last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
	FROM dbo.translation_units_%% 
			WHERE id IN (SELECT id FROM @ResultIDs) 
	
	SELECT da.translation_unit_id, da.attribute_id, a.name, a.type, da.value
		FROM dbo.date_attributes_%% da INNER JOIN dbo.attributes_%% a ON da.attribute_id = a.id
		WHERE da.translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY da.translation_unit_id, da.attribute_id
		
	SELECT sa.translation_unit_id, sa.attribute_id, a.name, a.type, sa.value
		FROM dbo.string_attributes_%% sa INNER JOIN dbo.attributes_%% a ON sa.attribute_id = a.id
		WHERE sa.translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY sa.translation_unit_id, sa.attribute_id
		
	SELECT na.translation_unit_id, na.attribute_id, a.name, a.type, na.value
		FROM dbo.numeric_attributes_%% na INNER JOIN dbo.attributes_%% a ON na.attribute_id = a.id
		WHERE na.translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY na.translation_unit_id, na.attribute_id
		
	SELECT pa.translation_unit_id, pv.attribute_id, a.name, a.type, pa.picklist_value_id 
		FROM dbo.picklist_attributes_%% pa 
			INNER JOIN dbo.picklist_values_%% pv ON pv.id = pa.picklist_value_id	
			INNER JOIN dbo.attributes_%% a ON pv.attribute_id = a.id	
		WHERE pa.translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY pa.translation_unit_id, pv.attribute_id

	DROP TABLE #CountTable

	--not required
			
','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);

--------------------------------------------------------------------------------
			SET @spName = 'exact_search_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.exact_search_%% 
	@source_hash BIGINT, 
	@target_hash BIGINT = NULL, 
	@count INT, 
	@last_id INT 
AS
	SET NOCOUNT ON
	IF @target_hash IS NOT NULL
		SELECT TOP (@count) id, guid, %%, source_hash, source_segment, 0, 0,
			target_hash, target_segment, 0, 0, creation_date, 
			creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
		FROM dbo.translation_units_%% 
		WHERE source_hash = @source_hash AND target_hash = @target_hash AND id > @last_id
		ORDER BY id ASC
	ELSE
		SELECT TOP (@count) id, guid, %%, source_hash, source_segment, 0, 0,
			target_hash, target_segment, 0, 0, creation_date, 
			creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
		FROM dbo.translation_units_%% 
		WHERE source_hash = @source_hash AND id > @last_id
		ORDER BY id ASC
','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);

--------------------------------------------------------------------------------
			SET @spName = 'exact_search_ex_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.exact_search_ex_%% 
	@source_hash BIGINT, 
	@target_hash BIGINT = NULL, 
	@count INT, 
	@last_id INT,
	@LeftTuContextSource BIGINT,
	@LeftTuContextTarget BIGINT,
	@DescendingOrder BIT
AS
	SET NOCOUNT ON
	
	DECLARE @ResultIDs TABLE(id INT NOT NULL PRIMARY KEY)
	
	IF @target_hash IS NOT NULL
		INSERT INTO @ResultIDs 
			SELECT TOP (@count) id
			FROM dbo.translation_units_%% 
			WHERE source_hash = @source_hash AND target_hash = @target_hash AND
			    (@DescendingOrder=0 and id > @last_id or @DescendingOrder=1 and id <= @last_id) 
			order by
				case when @DescendingOrder=0 then change_date end ASC,  
				case when @DescendingOrder=0 then id end ASC,  
				case when @DescendingOrder=1 then change_date end DESC,  
				case when @DescendingOrder=1 then id end DESC  
			
	ELSE
		INSERT INTO @ResultIDs 
			SELECT TOP (@count) id
			FROM dbo.translation_units_%% 
			WHERE source_hash = @source_hash AND   
			    (@DescendingOrder=0 and id > @last_id or @DescendingOrder=1 and id <= @last_id) 
			order by
				case when @DescendingOrder=0 then change_date end ASC,  
				case when @DescendingOrder=0 then id end ASC,  
				case when @DescendingOrder=1 then change_date end DESC,  
				case when @DescendingOrder=1 then id end DESC  
			
	
	SELECT tu.id, guid, %%, source_hash, source_segment, 
		target_hash, target_segment, creation_date, 
		creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
	FROM dbo.translation_units_%% tu 
		INNER JOIN @ResultIDs res ON tu.id = res.id
	ORDER BY 
				case when @DescendingOrder=0 then change_date end ASC,  
				case when @DescendingOrder=0 then tu.id end ASC,  
				case when @DescendingOrder=1 then change_date end DESC,  
				case when @DescendingOrder=1 then tu.id end DESC

	SELECT da.translation_unit_id, da.attribute_id, a.name, a.type, da.value 
				FROM dbo.date_attributes_%% da 
			INNER JOIN dbo.attributes_%% a ON da.attribute_id = a.id 
					INNER JOIN @ResultIDs res ON da.translation_unit_id = res.id
				ORDER BY da.translation_unit_id, da.attribute_id
		
	SELECT sa.translation_unit_id, sa.attribute_id, a.name, a.type, sa.value 
				FROM dbo.string_attributes_%% sa 
			INNER JOIN dbo.attributes_%% a ON sa.attribute_id = a.id
					INNER JOIN @ResultIDs res ON sa.translation_unit_id = res.id
				ORDER BY sa.translation_unit_id, sa.attribute_id
		
	SELECT na.translation_unit_id, na.attribute_id, a.name, a.type, na.value 
				FROM dbo.numeric_attributes_%% na 
			INNER JOIN dbo.attributes_%% a ON na.attribute_id = a.id
					INNER JOIN @ResultIDs res ON na.translation_unit_id = res.id
				ORDER BY na.translation_unit_id, na.attribute_id
		
	SELECT pa.translation_unit_id, pv.attribute_id, a.name, a.type, pa.picklist_value_id 
			FROM dbo.picklist_attributes_%% pa 
				INNER JOIN dbo.picklist_values_%% pv ON pv.id = pa.picklist_value_id
			INNER JOIN dbo.attributes_%% a ON pv.attribute_id = a.id
				INNER JOIN @ResultIDs res ON pa.translation_unit_id = res.id
			ORDER BY pa.translation_unit_id, pv.attribute_id
	
	if (@LeftTuContextSource != -1 )--can''t have source -1
	begin
		DECLARE @FullContextMatch TABLE(translation_unit_id INT NOT NULL PRIMARY KEY, left_source_context BIGINT, left_target_context BIGINT)
		
		INSERT INTO @FullContextMatch
		SELECT ctx.translation_unit_id, left_source_context, left_target_context 
			FROM dbo.translation_unit_contexts_%% ctx
				INNER JOIN @ResultIDs res ON ctx.translation_unit_id = res.id
			WHERE left_source_context = @LeftTuContextSource and left_target_context = @LeftTuContextTarget 
					and @LeftTuContextTarget != -1 --can''t have target -1
			ORDER BY translation_unit_id

		--retrive either context match for source and target or just matches for source
		IF EXISTS(SELECT * FROM @FullContextMatch)
			SELECT translation_unit_id, left_source_context, left_target_context 
			FROM @FullContextMatch
		ELSE
			SELECT ctx.translation_unit_id, left_source_context, left_target_context 
			FROM dbo.translation_unit_contexts_%% ctx
				INNER JOIN @ResultIDs res ON ctx.translation_unit_id = res.id
			WHERE left_source_context = @LeftTuContextSource 
			ORDER BY translation_unit_id
	end

			
','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);

--------------------------------------------------------------------------------
			SET @spName = 'tm_tdb_search_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.tm_tdb_search_%%
	@source_hashes  as SegmentHashes readonly,
	@count INT, 
	@last_id INT,
	@LeftTuContextSource BIGINT,
	@LeftTuContextTarget BIGINT,
	@DescendingOrder BIT

AS
	SET NOCOUNT ON
	
	DECLARE @ResultIDs TABLE(id INT NOT NULL PRIMARY KEY)
	

		INSERT INTO @ResultIDs 
			SELECT TOP (@count) id
			FROM dbo.translation_units_%%
			inner join @source_hashes as sourceHashes on dbo.translation_units_%%.source_hash = sourceHashes.SegmentHash
			WHERE  
			    (@DescendingOrder=0 and id > @last_id or @DescendingOrder=1 and id <= @last_id) 
			order by
				case when @DescendingOrder=0 then change_date end ASC,  
				case when @DescendingOrder=0 then id end ASC,  
				case when @DescendingOrder=1 then change_date end DESC,  
				case when @DescendingOrder=1 then id end DESC  
			
	
	SELECT tu.id, guid, 1, source_hash, source_segment, 
		target_hash, target_segment, creation_date, 
		creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
	FROM dbo.translation_units_%% tu 
		INNER JOIN @ResultIDs res ON tu.id = res.id
	ORDER BY 
				case when @DescendingOrder=0 then change_date end ASC,  
				case when @DescendingOrder=0 then tu.id end ASC,  
				case when @DescendingOrder=1 then change_date end DESC,  
				case when @DescendingOrder=1 then tu.id end DESC

	SELECT da.translation_unit_id, da.attribute_id, a.name, a.type, da.value 
				FROM dbo.date_attributes_%% da 
			INNER JOIN dbo.attributes_%% a ON da.attribute_id = a.id 
					INNER JOIN @ResultIDs res ON da.translation_unit_id = res.id
				ORDER BY da.translation_unit_id, da.attribute_id
		
	SELECT sa.translation_unit_id, sa.attribute_id, a.name, a.type, sa.value 
				FROM dbo.string_attributes_%% sa 
			INNER JOIN dbo.attributes_%% a ON sa.attribute_id = a.id
					INNER JOIN @ResultIDs res ON sa.translation_unit_id = res.id
				ORDER BY sa.translation_unit_id, sa.attribute_id
		
	SELECT na.translation_unit_id, na.attribute_id, a.name, a.type, na.value 
				FROM dbo.numeric_attributes_%% na 
			INNER JOIN dbo.attributes_%% a ON na.attribute_id = a.id
					INNER JOIN @ResultIDs res ON na.translation_unit_id = res.id
				ORDER BY na.translation_unit_id, na.attribute_id
		
	SELECT pa.translation_unit_id, pv.attribute_id, a.name, a.type, pa.picklist_value_id 
			FROM dbo.picklist_attributes_%% pa 
				INNER JOIN dbo.picklist_values_%% pv ON pv.id = pa.picklist_value_id
			INNER JOIN dbo.attributes_%% a ON pv.attribute_id = a.id
				INNER JOIN @ResultIDs res ON pa.translation_unit_id = res.id
			ORDER BY pa.translation_unit_id, pv.attribute_id
	
	if (@LeftTuContextSource != -1 )--can''t have source -1
	begin
		DECLARE @FullContextMatch TABLE(translation_unit_id INT NOT NULL PRIMARY KEY, left_source_context BIGINT, left_target_context BIGINT)
		
		INSERT INTO @FullContextMatch
		SELECT ctx.translation_unit_id, left_source_context, left_target_context 
			FROM dbo.translation_unit_contexts_%% ctx
				INNER JOIN @ResultIDs res ON ctx.translation_unit_id = res.id
			WHERE left_source_context = @LeftTuContextSource and left_target_context = @LeftTuContextTarget 
					and @LeftTuContextTarget != -1 --can''t have target -1
			ORDER BY translation_unit_id

		--retrive either context match for source and target or just matches for source
		IF EXISTS(SELECT * FROM @FullContextMatch)
			SELECT translation_unit_id, left_source_context, left_target_context 
			FROM @FullContextMatch
		ELSE
			SELECT ctx.translation_unit_id, left_source_context, left_target_context 
			FROM dbo.translation_unit_contexts_%% ctx
				INNER JOIN @ResultIDs res ON ctx.translation_unit_id = res.id
			WHERE left_source_context = @LeftTuContextSource 
			ORDER BY translation_unit_id
	end

			
','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);

--------------------------------------------------------------------------------
			SET @spName = 'duplicate_search_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.duplicate_search_%% @last_source_hash BIGINT, @last_tu_id INT, @count INT, @forward INT AS
	SET NOCOUNT ON
		DECLARE @ResultIDs TABLE(id INT NOT NULL PRIMARY KEY)

	IF @forward > 0
		IF @last_tu_id > 0
			INSERT INTO @ResultIDs 
			SELECT TOP (@count) id
			FROM dbo.translation_units_%% 
			WHERE source_hash = @last_source_hash
				AND id > @last_tu_id ORDER BY id ASC
		ELSE
			INSERT INTO @ResultIDs 
			SELECT TOP (@count) id
			FROM dbo.translation_units_%% 
			WHERE source_hash IN
				(SELECT TOP 1 source_hash FROM dbo.translation_units_%% 
					WHERE source_hash > @last_source_hash
					GROUP BY source_hash HAVING COUNT(*) > 1 ORDER BY source_hash ASC)
				ORDER BY id ASC
	ELSE
		IF @last_tu_id > 0
			INSERT INTO @ResultIDs 
			SELECT TOP (@count) id
				FROM dbo.translation_units_%% WHERE source_hash = @last_source_hash
				AND id <= @last_tu_id ORDER BY id DESC
		ELSE
			INSERT INTO @ResultIDs 
			SELECT TOP (@count) id
				FROM dbo.translation_units_%% WHERE source_hash IN
				(SELECT TOP 1 source_hash FROM dbo.translation_units_%% 
					WHERE source_hash <= @last_source_hash
					GROUP BY source_hash HAVING COUNT(*) > 1 ORDER BY source_hash DESC)
				ORDER BY id DESC

	select dbo.translation_units_%%.id, guid, %%, source_hash, source_segment,
				target_hash, target_segment,
				creation_date, creation_user, change_date, change_user, last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date
	from dbo.translation_units_%% 
		WHERE id IN (SELECT id FROM @ResultIDs) 
	
	IF  EXISTS (SELECT * FROM @ResultIDs)
	begin 
	SELECT da.translation_unit_id, da.attribute_id, a.name, a.type, da.value 
				FROM dbo.date_attributes_%% da 
			INNER JOIN dbo.attributes_%% a ON da.attribute_id = a.id 
					INNER JOIN @ResultIDs res ON da.translation_unit_id = res.id
				ORDER BY da.translation_unit_id, da.attribute_id
		
	SELECT sa.translation_unit_id, sa.attribute_id, a.name, a.type, sa.value 
				FROM dbo.string_attributes_%% sa 
			INNER JOIN dbo.attributes_%% a ON sa.attribute_id = a.id
					INNER JOIN @ResultIDs res ON sa.translation_unit_id = res.id
				ORDER BY sa.translation_unit_id, sa.attribute_id
		
	SELECT na.translation_unit_id, na.attribute_id, a.name, a.type, na.value 
				FROM dbo.numeric_attributes_%% na 
			INNER JOIN dbo.attributes_%% a ON na.attribute_id = a.id
					INNER JOIN @ResultIDs res ON na.translation_unit_id = res.id
				ORDER BY na.translation_unit_id, na.attribute_id
		
	SELECT pa.translation_unit_id, pv.attribute_id, a.name, a.type, pa.picklist_value_id 
			FROM dbo.picklist_attributes_%% pa 
				INNER JOIN dbo.picklist_values_%% pv ON pv.id = pa.picklist_value_id
			INNER JOIN dbo.attributes_%% a ON pv.attribute_id = a.id
				INNER JOIN @ResultIDs res ON pa.translation_unit_id = res.id
			ORDER BY pa.translation_unit_id, pv.attribute_id

	--no contexts returned in duplicate search	
	end
			
','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);

--------------------------------------------------------------------------------
			SET @spName = 'fuzzy_search_concordance_' + @tmIdStr;
			IF EXISTS (SELECT 1 FROM information_schema.routines 
				WHERE routine_name = @spName 
					AND routine_type = 'PROCEDURE'
					AND ROUTINE_CATALOG = db_name()
					) 
			BEGIN			
				set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
				Execute ( @sp_body);
				print('--> Dropped ' + @spName);
			END;
			
			set @sp_body = replace('CREATE PROCEDURE dbo.fuzzy_search_concordance_%% @features NVARCHAR(MAX), @length INT, @minScore INT, @count INT, @type INT, @last_id INT, @LeftTuContextSource BIGINT,
									@LeftTuContextTarget BIGINT, @DescendingOrder bit AS
	DECLARE @cmd NVARCHAR(MAX)
	SET NOCOUNT ON
	DECLARE @maxLength INT, @ignored INT, @minMatches INT, @newMinMatches INT, @minHaving INT
	SET @minHaving = (SELECT value FROM dbo.parameters WHERE translation_memory_id = %% AND name = ''MINHAVING'' + LTRIM(STR(@type)))
	SET @minScore = @minScore + (SELECT value FROM dbo.parameters WHERE translation_memory_id = %% AND name = ''ADDTOMINSCORE'')
	IF @minScore > 100 SET @minScore = 100
	SET @minMatches = (@minScore * @length + 99) / 100
	SET @maxLength = (@length * 100 + @minScore - 1) / @minScore
	SET @newMinMatches = @minMatches
	
	DECLARE @ResultTable TABLE(id INT PRIMARY KEY, [length] int, matches INT)
	CREATE TABLE #CountTable (feature INT PRIMARY KEY, occurences INT, ignored INT DEFAULT 0)

	DECLARE @s NVARCHAR(MAX), @dataLen INT, @pos INT, @w NVARCHAR(50), @found INT
	
	SET @dataLen = (DATALENGTH(@features) / 2) - 2
	SET @s = SUBSTRING(@features, 2, @dataLen)

	SET @pos = 1
	SET @found = CHARINDEX(N'','', @s, @pos)
	WHILE @found > 0
	BEGIN
		SET @w = SUBSTRING(@s, @pos, @found - @pos)
		if  @newMinMatches > @minHaving
			INSERT INTO #CountTable VALUES(@w, dbo.get_feature_frequency_%%(@w, @type), 0)
		else
			INSERT INTO #CountTable VALUES(@w, 0, 0)
	
		SET @pos = @found + 1
		SET @found = CHARINDEX(N'','', @s, @pos)
	END
	
	SET @w = SUBSTRING(@s, @pos, @dataLen + 1 - @pos)
	
	if  @newMinMatches > @minHaving
		INSERT INTO #CountTable VALUES(@w, dbo.get_feature_frequency_%%(@w, @type), 0)
	else
		INSERT INTO #CountTable VALUES(@w, 0, 0)
		
			
	IF @length > 1
	BEGIN
		DECLARE @occ INT
		
		WHILE @newMinMatches > @minHaving
		BEGIN
			SELECT @occ = (SELECT MAX(occurences) FROM #CountTable WHERE ignored = 0)
			IF @occ IS NULL OR @occ < 1000
				BREAK;

			UPDATE TOP(1) #CountTable 
				SET ignored = 1 
				WHERE occurences = @occ
			
			SET @newMinMatches = @newMinMatches - 1
		END
	END

	SELECT @ignored = COUNT(*) FROM #CountTable WHERE ignored = 1

	IF @type = 1 -- source word index
	
	
			INSERT INTO @ResultTable 
				SELECT translation_unit_id, fi.[length], COUNT(*) AS matches 
				FROM dbo.fuzzy_index1_%% fi
					INNER JOIN #CountTable ct
					ON fi.feature = ct.feature
				WHERE 
					ct.ignored = 0
					AND (@DescendingOrder=0 and fi.translation_unit_id > @last_id or @DescendingOrder=1 and fi.translation_unit_id < @last_id)
				GROUP BY fi.translation_unit_id, fi.[length]   
				HAVING COUNT(*) >= @newMinMatches
			
	ELSE IF @type = 2 -- source chars index
	
		INSERT INTO @ResultTable 
			SELECT translation_unit_id, fi.[length], COUNT(*) AS matches 
			FROM dbo.fuzzy_index2_%% fi
				INNER JOIN #CountTable ct
				ON fi.feature = ct.feature
			WHERE 
				ct.ignored = 0
				AND (@DescendingOrder=0 and fi.translation_unit_id > @last_id or @DescendingOrder=1 and fi.translation_unit_id < @last_id)
			GROUP BY fi.translation_unit_id, fi.[length]   
			HAVING COUNT(*) >= @newMinMatches
			
	ELSE IF @type = 4 -- target words index
	
		INSERT INTO @ResultTable 
			SELECT translation_unit_id, fi.[length], COUNT(*) AS matches 
			FROM dbo.fuzzy_index4_%% fi
				INNER JOIN #CountTable ct
				ON fi.feature = ct.feature
			WHERE 
				ct.ignored = 0
				AND (@DescendingOrder=0 and fi.translation_unit_id > @last_id or @DescendingOrder=1 and fi.translation_unit_id < @last_id)
			GROUP BY fi.translation_unit_id, fi.[length]   
			HAVING COUNT(*) >= @newMinMatches
		
	ELSE IF @type = 8 -- target chars index
	
	INSERT INTO @ResultTable 
			SELECT translation_unit_id, fi.[length], COUNT(*) AS matches 
			FROM dbo.fuzzy_index8_%% fi
				INNER JOIN #CountTable ct
				ON fi.feature = ct.feature
			WHERE 
				ct.ignored = 0
				AND (@DescendingOrder=0 and fi.translation_unit_id > @last_id or @DescendingOrder=1 and fi.translation_unit_id < @last_id)
			GROUP BY fi.translation_unit_id, fi.[length]   
			HAVING COUNT(*) >= @newMinMatches

	IF @ignored > 0
	BEGIN

		DECLARE @resultTopMinMatches INT
		
		SELECT @resultTopMinMatches = MIN(matches)
		FROM @ResultTable
		WHERE id IN (SELECT TOP(@count) id FROM @ResultTable ORDER BY matches DESC)

		DELETE FROM @ResultTable
		WHERE 
			matches + @ignored < @minMatches
			OR matches + @ignored < @resultTopMinMatches
				
		IF @type = 1
			UPDATE @ResultTable SET matches = matches + (SELECT COUNT(*) FROM dbo.fuzzy_index1_%% fi WITH (INDEX = fi1_address_%%)
				JOIN @ResultTable r ON r.id = fi.translation_unit_id 
                JOIN #CountTable c ON fi.feature = c.feature 
				WHERE c.ignored = 1)
		ELSE IF @type = 2
			UPDATE @ResultTable SET matches = matches + (SELECT COUNT(*) FROM dbo.fuzzy_index2_%% fi WITH (INDEX = fi2_address_%%)
				JOIN @ResultTable r ON r.id = fi.translation_unit_id 
                JOIN #CountTable c ON fi.feature = c.feature 
                WHERE c.ignored = 1)
		ELSE IF @type = 8
			UPDATE @ResultTable SET matches = matches + (SELECT COUNT(*) FROM dbo.fuzzy_index8_%% fi WITH (INDEX = fi8_address_%%)
				JOIN @ResultTable r ON r.id = fi.translation_unit_id 
                JOIN #CountTable c ON fi.feature = c.feature 
                WHERE c.ignored = 1)
		ELSE IF @type = 4
			UPDATE @ResultTable SET matches = matches + (SELECT COUNT(*) FROM dbo.fuzzy_index4_%% fi WITH (INDEX = fi4_address_%%)
				JOIN @ResultTable r ON r.id = fi.translation_unit_id 
                JOIN #CountTable c ON fi.feature = c.feature 
                WHERE c.ignored = 1)
    END
	
	DECLARE @ResultIDs TABLE(id INT)
	if @DescendingOrder = 1
	begin
		INSERT INTO @ResultIDs 
		SELECT TOP (@count) rt.id 
		FROM @ResultTable rt
		WHERE rt.matches >= @minMatches 
		ORDER BY rt.matches DESC, rt.id desc
	end
	else
	begin
		INSERT INTO @ResultIDs 
		SELECT TOP (@count) rt.id 
		FROM @ResultTable rt
		WHERE rt.matches >= @minMatches 
		ORDER BY rt.matches DESC, rt.id ASC
	end

	SELECT id, guid, 1, source_hash, source_segment,
		target_hash, target_segment,
		creation_date, creation_user, change_date, change_user, 
		last_used_date, last_used_user, usage_counter, flags, source_token_data, target_token_data, alignment_data, align_model_date 
	FROM dbo.translation_units_%% 
			WHERE id IN (SELECT id FROM @ResultIDs) 
	
	SELECT da.translation_unit_id, da.attribute_id, a.name, a.type, da.value
		FROM dbo.date_attributes_%% da INNER JOIN dbo.attributes_%% a ON da.attribute_id = a.id
		WHERE da.translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY da.translation_unit_id, da.attribute_id
		
	SELECT sa.translation_unit_id, sa.attribute_id, a.name, a.type, sa.value
		FROM dbo.string_attributes_%% sa INNER JOIN dbo.attributes_%% a ON sa.attribute_id = a.id
		WHERE sa.translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY sa.translation_unit_id, sa.attribute_id
		
	SELECT na.translation_unit_id, na.attribute_id, a.name, a.type, na.value
		FROM dbo.numeric_attributes_%% na INNER JOIN dbo.attributes_%% a ON na.attribute_id = a.id
		WHERE na.translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY na.translation_unit_id, na.attribute_id
		
	SELECT pa.translation_unit_id, pv.attribute_id, a.name, a.type, pa.picklist_value_id 
		FROM dbo.picklist_attributes_%% pa 
			INNER JOIN dbo.picklist_values_%% pv ON pv.id = pa.picklist_value_id	
			INNER JOIN dbo.attributes_%% a ON pv.attribute_id = a.id	
		WHERE pa.translation_unit_id IN (SELECT id FROM @ResultIDs) 
		ORDER BY pa.translation_unit_id, pv.attribute_id

	DROP TABLE #CountTable

	--TUContext not required for fuzzy_concordance search
			
','%%', @tmId)
				
			EXECUTE (@sp_body);
			print('--> Created ' + @spName);

		SET @spName = 'get_tus_ToAlign_'  + @tmIdStr;
		IF EXISTS (SELECT 1 FROM information_schema.routines 
			WHERE routine_name = @spName 
				AND routine_type = 'PROCEDURE'
				AND ROUTINE_CATALOG = db_name()
				) 
		BEGIN			
			set @sp_body = 'DROP PROCEDURE [dbo].' + @spName			
			Execute ( @sp_body);
			print('--> Dropped ' + @spName);
		END;
			
		set @sp_body = replace('CREATE PROCEDURE dbo.get_tus_ToAlign_%% 
	@start_after INT, 
	@count INT,
	@postdatedAligment BIT
AS
	SET NOCOUNT ON
	
	DECLARE @ResultIDs TABLE(id INT NOT NULL PRIMARY KEY)	

	INSERT INTO @ResultIDs 
		SELECT TOP (@count) id
		FROM dbo.translation_units_%% 
		WHERE id > @start_after and 
			(alignment_data is NULL or (@postdatedAligment=1 and align_model_date < change_date))
		ORDER BY id ASC	

	SELECT id, guid, source_hash, source_segment, target_hash, target_segment, 	
		 source_token_data, target_token_data, alignment_data, align_model_date
	FROM dbo.translation_units_%% 
	WHERE id IN (SELECT id FROM @ResultIDs)	
','%%', @tmId)

		EXECUTE (@sp_body);
		print('--> Created ' + @spName);

--------------------------------------------------------------------------------
			set @startID = @startID+1;
		end;

		print( '----> End TM ' + @VersionTo + ' Upgrade!');

	--------------============= Upgrade Schema Version Data ==================================
END TRY
		BEGIN CATCH
			set @errorMessage = ERROR_MESSAGE()
			ROLLBACK TRAN	
			SET @Success=0
				print('--> Error: TM Server database schema update failed!')
				raiserror('Current TM Server schema can''t be upgraded. update Failed with sql error message [%s]. Failed script is:', 16, 127, @errorMessage )
			PRINT CAST(@sp_body AS NTEXT)
		END CATCH;
			
			IF @Success=1
			BEGIN
				UPDATE [dbo].parameters
					SET value = @VersionTo
					WHERE name = 'VERSION';
				COMMIT TRANSACTION;
				print('--> TM Server schema version successfully upgraded to ' + @VersionTo); 
				print('**** Please check the Release Notes and run other scripts to complete the schema upgrade!****');
			END
		
	END;

--============================= End of the Script ======================================
