#
# Minimal Sphinx configuration sample (clean, simple, functional)
#


source firstformdb 
{
	type		= mssql
	sql_host		= <сервер>
	sql_user		= <пользователь>
	sql_pass		= <пароль>
	sql_db		= <БД>
	sql_port		= 1433                     # optional, default is 3306
}


source files : firstformdb 
{
	sql_query		= \
		SELECT FileId, Name, Ext, UploadDate, UploadUserId, Size FROM FileStorageFileInfoLatestVersion WHERE IsEncrypted=0 AND IsDeleted=0

	sql_field_string	= Name
	sql_field_string	= Ext
	sql_attr_timestamp	= UploadDate
	sql_attr_uint	= UploadUserId
	sql_attr_uint 	= Size 
	sql_attr_multi 	= uint access_users from query; \
                 select FileId, UserId FROM UserFilePermissionsForSearch
}

index filesIndex
{
	source		= files
	path		= c:/sphinx/data/filesIndex
}

source emails : firstformdb 
{
	sql_query		= \
		SELECT EmailId, Subject, [From] AS FromAddress, [To] AS ToAddress, TextBody FROM Emails with(nolock)

	sql_field_string	= Subject 
	sql_field_string	= FromAddress
	sql_field_string	= ToAddress
	sql_field_string	= TextBody
	sql_attr_multi 	= uint access_users from query; \
                 select EmailId, UserId FROM UserEmailPermissions
}

index emailsIndex
{
	source		= emails
	path		= c:/sphinx/data/emailsIndex
}

source tasks_main : firstformdb
{
	sql_query		= \
		SELECT t.TaskId, CAST(t.Description AS nvarchar(max)) AS Description, t.IsClosed, t.SubcatId, \
			CAST(s.FullPath AS nvarchar(1000)) AS FullPath, s.FullPathIds, \
			ISNULL(refc.RefCount, 0) AS RefCount \
		FROM Tasks t with(nolock) \
		JOIN Subcategories s with(nolock) ON t.SubcatId = s.SubcatId \
		LEFT JOIN vTaskReferenceCount refc with(nolock) ON t.TaskID = refc.TaskID \
		WHERE t.SubcatId <> 1507 AND t.IsEncrypted=0                                # в данном случае 1507 - ID системной категории "Календарные задачи"

	sql_field_string	= Description
	sql_attr_string	= FullPathIds	
	sql_attr_string	= FullPath 
	sql_attr_bool	= IsClosed
	sql_attr_uint	= SubcatId 
	sql_attr_uint	= RefCount 
	sql_attr_multi 	= uint access_users from query; \
                 select TaskId, UserId FROM UserTaskPermissions

	sql_query_post_index = \
		INSERT INTO SphinxIndexLog (IsIncrement, IndexName) VALUES (0, 'Tasks') 

}

source tasks_delta : firstformdb
{
	sql_query		= \
		SELECT t.TaskId, CAST(t.Description AS nvarchar(max)) AS Description, t.IsClosed, t.SubcatId, \
			CAST(s.FullPath AS nvarchar(1000)) AS FullPath, s.FullPathIds, \
			ISNULL(refc.RefCount, 0) AS RefCount \
		FROM Tasks t with(nolock) \
		JOIN Subcategories s with(nolock) ON t.SubcatId = s.SubcatId \
		LEFT JOIN vTaskReferenceCount refc with(nolock) ON t.TaskID = refc.TaskID \
		WHERE t.SubcatId <> 1507 AND t.IsEncrypted=0 \
			AND  ModifiedDate > (SELECT MAX([DATE]) FROM SphinxIndexLog WHERE IsIncrement = 0 AND IndexName = 'Tasks')        

	sql_field_string	= Description
	sql_attr_string	= FullPathIds	
	sql_attr_string	= FullPath 
	sql_attr_bool	= IsClosed
	sql_attr_uint	= SubcatId 
	sql_attr_uint	= RefCount 
	sql_attr_multi = uint access_users from query; \
                	SELECT up.TaskId, up.UserId \
			FROM UserTaskPermissions up \
			JOIN Tasks with(nolock) ON up.TaskID = Tasks.TaskID \
		 	WHERE ModifiedDate > (SELECT MAX([DATE]) FROM SphinxIndexLog WHERE IsIncrement = 0 AND IndexName = 'Tasks')

	sql_query_post_index = \
		INSERT INTO SphinxIndexLog (IsIncrement, IndexName) VALUES (1, 'Tasks') 

	sql_query_killlist = \
		SELECT TaskID \
		FROM Tasks with(nolock) \
		where ModifiedDate > (SELECT MAX([DATE]) FROM SphinxIndexLog WHERE IsIncrement = 0 AND IndexName = 'Tasks')

}

index tasksIndex_main
{
	source		= tasks_main 			
	path		= c:/sphinx/data/tasksIndex_main
	blend_chars	= +, U+23
	ignore_chars	= -
}


index tasksIndex_delta
{
	source		= tasks_delta
	path		= c:/sphinx/data/tasksIndex_delta
	blend_chars	= +, U+23
	ignore_chars	= -
}


index tasksIndex
{
	type 		= distributed
	local		= tasksIndex_main
	local 		= tasksIndex_delta	
}


source extParams_main : firstformdb
{
	sql_query		= \
		SELECT (v.TaskID *  10000 + v.ExtParamID) AS ID, \
			v.ExtParamID, v.TaskID, t.IsClosed, \
			CAST(t.Description AS nvarchar(max)) AS Description, \
			CAST(ep.ExtParamName AS nvarchar(max)) AS ExtParamName,  \
			CAST(v.ExtparamValue AS nvarchar(max)) AS ExtparamValue, \
			ISNULL(refc.RefCount, 0) AS RefCount \
		FROM ExtparamValues v with(nolock) \
		JOIN Tasks t with(nolock) ON v.TaskID = t.TaskID \
		JOIN ExtParamsInSubcat eps with(nolock) \
			ON eps.ExtParamID = v.ExtParamID AND eps.SubcatID = t.SubcatID \
		JOIN ExtParams ep with(nolock) ON ep.ExtParamID = v.ExtParamID \
		LEFT JOIN vTaskReferenceCount refc with(nolock) ON t.TaskID = refc.TaskID \
		WHERE eps.AccessControl IN (0,3) \
			AND t.IsClosed = 0 AND t.IsEncrypted=0 AND t.SubcatID <> 1507 \
			AND v.ExtParamValue <> ''                                          

	sql_field_string	= ExtparamValue
	sql_attr_uint	= ExtParamID
	sql_attr_uint	= TaskID 
	sql_attr_string	= Description
	sql_attr_string	= ExtParamName
	sql_attr_bool	= IsClosed 
	sql_attr_uint	= RefCount 
	sql_attr_multi 	= uint access_users from query; \
                SELECT (v.TaskID *  10000 + v.ExtParamID) AS ID, p.UserID \
		FROM ExtparamValues v with(nolock) \
		JOIN Tasks t with(nolock) ON v.TaskID = t.TaskID \
		JOIN ExtParamsInSubcat eps with(nolock) \
			ON eps.ExtParamID = v.ExtParamID AND eps.SubcatID = t.SubcatID \
		JOIN UserTaskPermissions p with(nolock) ON t.TaskID =p.TaskID \
		WHERE eps.AccessControl IN (0,3) \
			AND t.IsClosed = 0 AND t.IsEncrypted=0 AND t.SubcatID <> 1507 \
			AND v.ExtParamValue <> ''                                          
			

	sql_range_step 	= 100000
}

source extParams_delta : firstformdb
{
	sql_query		= \
		SELECT (v.TaskID *  10000 + v.ExtParamID) AS ID, \
			v.ExtParamID, v.TaskID, t.IsClosed, \
			CAST(t.Description AS nvarchar(max)) AS Description, \
			CAST(ep.ExtParamName AS nvarchar(max)) AS ExtParamName,  \
			CAST(v.ExtparamValue AS nvarchar(max)) AS ExtparamValue, \
			ISNULL(refc.RefCount, 0) AS RefCount \
		FROM ExtparamValues v with(nolock) \
		JOIN Tasks t with(nolock) ON v.TaskID = t.TaskID \
		JOIN ExtParamsInSubcat eps with(nolock) \
			ON eps.ExtParamID = v.ExtParamID AND eps.SubcatID = t.SubcatID \
		JOIN ExtParams ep with(nolock) ON ep.ExtParamID = v.ExtParamID \
		LEFT JOIN vTaskReferenceCount refc with(nolock) ON t.TaskID = refc.TaskID \
		WHERE eps.AccessControl IN (0,3) \
			AND t.IsClosed = 0 AND t.IsEncrypted=0 AND t.SubcatID <> 1507 \
			AND v.ExtParamValue <> '' \
			AND t.ModifiedDate > (SELECT MAX([DATE]) FROM SphinxIndexLog WHERE IsIncrement = 0 AND IndexName = 'Tasks')

	sql_field_string	= ExtparamValue
	sql_attr_uint	= ExtParamID
	sql_attr_uint	= TaskID 
	sql_attr_string	= Description
	sql_attr_string	= ExtParamName
	sql_attr_bool	= IsClosed 
	sql_attr_uint	= RefCount 
	sql_attr_multi = uint access_users from query; \
                SELECT (v.TaskID *  10000 + v.ExtParamID) AS ID, p.UserID \
		FROM ExtparamValues v with(nolock) \
		JOIN Tasks t with(nolock) ON v.TaskID = t.TaskID \
		JOIN ExtParamsInSubcat eps with(nolock) \
			ON eps.ExtParamID = v.ExtParamID AND eps.SubcatID = t.SubcatID \
		JOIN UserTaskPermissions p with(nolock) ON t.TaskID =p.TaskID \
		WHERE eps.AccessControl IN (0,3) \
			AND t.IsClosed = 0 AND t.IsEncrypted=0 AND t.SubcatID <> 1507 \
			AND v.ExtParamValue <> '' \
			AND t.ModifiedDate > (SELECT MAX([DATE]) FROM SphinxIndexLog WHERE IsIncrement = 0 AND IndexName = 'Tasks') \

	sql_range_step = 100000
}

index extParamsIndex_main
{
	source		= extParams_main
	path		= c:/sphinx/data/extParamsIndex
}
index extParamsIndex_delta
{
	source		= extParams_delta
	path		= c:/sphinx/data/extParamsIndex_delta
}
index extParamsIndex
{
	type 		= distributed
	local		= extParamsIndex_main
	local 		= extParamsIndex_delta
}


source comments_main : firstformdb
{
	sql_query		= \
		SELECT c.CommentID, c.TaskID, c.UserId, \
			CAST(c.Content AS nvarchar(max)) AS Content, \
			CAST(t.Description AS nvarchar(max)) AS Description, \
			ISNULL(refc.RefCount, 0)  AS RefCount \
		FROM Comments c with(nolock) \
		JOIN Tasks t with(nolock) ON c.TaskID = t.TaskID \
		LEFT JOIN vTaskReferenceCount refc with(nolock) ON t.TaskID = refc.TaskID \
		WHERE c.TypeID = 3 

	sql_field_string	= Content
	sql_attr_uint	= UserId
	sql_attr_uint	= TaskID
	sql_attr_string	= Description  
	sql_attr_uint	= RefCount 
	sql_attr_multi 	= uint access_users from query; \
                SELECT c.CommentID, perm.UserID \
		FROM Comments c with(nolock) \
		JOIN UserTaskPermissions perm with(nolock) ON c.TaskID=perm.TaskID \
		WHERE c.TypeID = 3 \

	sql_range_step 	= 100000
}

source comments_delta : firstformdb
{
	sql_query		= \
		SELECT c.CommentID, c.TaskID, c.UserId, \
			CAST(c.Content AS nvarchar(max)) AS Content, \
			CAST(t.Description AS nvarchar(max)) AS Description, \
			ISNULL(refc.RefCount, 0)  AS RefCount \
		FROM Comments c with(nolock) \
		JOIN Tasks t with(nolock) ON c.TaskID = t.TaskID \
		LEFT JOIN vTaskReferenceCount refc with(nolock) ON t.TaskID = refc.TaskID \
		WHERE c.TypeID = 3 \
		      AND c.Date > (SELECT MAX([DATE]) FROM SphinxIndexLog WHERE IsIncrement = 0 AND IndexName = 'Tasks') \

	sql_field_string	= Content
	sql_attr_uint	= UserId
	sql_attr_uint	= TaskID
	sql_attr_string	= Description  
	sql_attr_uint	= RefCount 
	sql_attr_multi 	= uint access_users from query; \
                SELECT c.CommentID, perm.UserID \
		FROM Comments c with(nolock) \
		JOIN UserTaskPermissions perm with(nolock) ON c.TaskID=perm.TaskID \
		WHERE c.TypeID = 3 \
		      AND c.Date > (SELECT MAX([DATE]) FROM SphinxIndexLog WHERE IsIncrement = 0 AND IndexName = 'Tasks') \

	

	sql_range_step = 100000
}

index commentsIndex_main
{
	source		= comments_main
	path		= c:/sphinx/data/commentsIndex
}
index commentsIndex_delta
{
	source		= comments_delta
	path		= c:/sphinx/data/commentsIndex_delta
}
index commentsIndex
{
	type 		= distributed
	local		= commentsIndex_main
	local 		= commentsIndex_delta
}


indexer
{
	mem_limit		= 128M
	
}


searchd
{

pid_file    		= c:/sphinx/data/searchd.pid
log         		= c:/sphinx/data/log/log.txt
query_log   	= c:/sphinx/data/log/query_log.txt
binlog_path 	= c:/sphinx/data/binlog/	
mysql_version_string = 5.0.0		


	listen		= 9306:mysql41

	read_timeout	= 5
	max_children	= 30
	seamless_rotate	= 1
	preopen_indexes	= 1
	unlink_old		= 1
	workers		= threads # for RT to work
	
}