Ubilling/docs/sphinxsearch/sphinx3.conf

#############################################################################
## data source definition
#############################################################################

source src1
{
	type			= mysql
	sql_host		= 127.0.0.1
	sql_user		= root
	sql_pass		= rootpassword
	sql_db			= stg
	sql_port		= 3306	# optional, default is 3306
	sql_query_pre		= SET NAMES utf8
        sql_query               = \
                SELECT `nethosts`.`id`, 'ip' AS `title`,  `nethosts`.`ip` as value, `users`.`login` \
                FROM `nethosts` RIGHT JOIN `users` ON `nethosts`.`ip` = `users`.`ip` 

	sql_attr_string		= login
	sql_attr_string		= title
        sql_field_string        = value
	sql_ranged_throttle	= 0
}
source src2 : src1
{       
        sql_query               = \
                SELECT `nethosts`.`id`, 'realname' AS `title`, `users`.`login`, `realname`.`realname` as value \
                FROM `nethosts` RIGHT JOIN `users` ON `nethosts`.`ip` = `users`.`ip` JOIN `realname` ON `realname`.`login` = `users`.`login` WHERE `realname`.`realname` <> ''
        
	sql_attr_string		= login
	sql_attr_string		= title
        sql_field_string        = value
}
source src3 : src1
{       
        sql_query               = \
                SELECT `nethosts`.`id`, 'mac' AS `title`, `nethosts`.`mac` AS `value`, `users`.`login` \
                FROM `nethosts` RIGHT JOIN `users` ON `nethosts`.`ip` = `users`.`ip`
 
	sql_attr_string		= login
	sql_attr_string		= title
        sql_field_string        = value
}
source src4 : src1
{
	sql_query		= \
		SELECT `nethosts`.`id`, 'login' AS `title`, `users`.`login` AS `value`, `users`.`login` AS `login` \
		FROM `nethosts` RIGHT JOIN `users` ON `nethosts`.`ip` = `users`.`ip`

	sql_attr_string		= login
	sql_attr_string		= title
	sql_field_string	= value
}
source src5 : src1
{
	sql_query		= \
		SELECT nethosts.id, 'address' AS `title`, `users`.`login`,concat(`cityname`, ' ', `streetname`, ' ', `buildnum`, IF(`apt`, concat('/',`apt`), '')) AS `value` \
		FROM `users` LEFT JOIN `nethosts` USING (`ip`) \
		LEFT JOIN `address` ON (`users`.`login`=`address`.`login`) \
		LEFT JOIN `apt` ON (`address`.`aptid`=`apt`.`id`) \
		LEFT JOIN `build` ON (`apt`.`buildid`=`build`.`id`) \
		LEFT JOIN `street` ON (`build`.`streetid`=`street`.`id`) \
		LEFT JOIN `city` ON (`street`.`cityid`=`city`.`id`) 

	sql_attr_string		= login
	sql_attr_string		= title
	sql_field_string	= value

}
source src6 : src1
{
	sql_query		= \
                SELECT `nethosts`.`id`, 'mobile' AS `title`, `users`.`login`, `phones`.`mobile` as `value` \
                FROM `nethosts` RIGHT JOIN `users` ON `nethosts`.`ip` = `users`.`ip` LEFT JOIN `phones` ON `users`.`login` = `phones`.`login` WHERE `phones`.`mobile` <> ''

	sql_attr_string		= login
	sql_attr_string		= title
	sql_field_string	= value

}
source src7 : src1
{
	sql_query		= \
                SELECT `nethosts`.`id`, 'phone' AS `title`, `users`.`login`, `phones`.`phone` as `value` \
                FROM `nethosts` RIGHT JOIN `users` ON `nethosts`.`ip` = `users`.`ip` LEFT JOIN `phones` ON `users`.`login` = `phones`.`login` WHERE `phones`.`phone` <> ''

	sql_attr_string		= login
	sql_attr_string		= title
	sql_field_string	= value

}
source src8 : src1
{
	sql_query		= \
		SELECT `nethosts`.`id`, 'contracts' AS `title`, `users`.`login`, `contracts`.`contract` as `value` \
		FROM `nethosts` RIGHT JOIN `users` ON `nethosts`.`ip` = `users`.`ip` LEFT JOIN `contracts` ON `users`.`login` = `contracts`.`login`

	sql_attr_string		= login
	sql_attr_string		= title
	sql_field_string	= value

}
source src9 : src1
{
	sql_query		= \
		SELECT `nethosts`.`id`, 'paymentid' AS `title`, `users`.`login`, `op_customers`.`virtualid` as `value` \
		FROM `nethosts` RIGHT JOIN `users` ON `nethosts`.`ip` = `users`.`ip` LEFT JOIN `op_customers` ON `users`.`login` = `op_customers`.`realid`
	
	sql_attr_string		= login
	sql_attr_string		= title
	sql_field_string	= value

}

source src10 : src1
{
	sql_query		= SELECT `nethosts`.`id`,'mac' AS `title`,CONCAT(SUBSTRING_INDEX(`nethosts`.`mac`, ':', 1),'-',SUBSTRING_INDEX(SUBSTRING_INDEX(`nethosts`.`mac`,':',2),':', -1),'-',SUBSTRING_INDEX(SUBSTRING_INDEX(`nethosts`.`mac`,':',3),':', -1),'-',SUBSTRING_INDEX(SUBSTRING_INDEX(`nethosts`.`mac`,':',4),':', -1),'-',SUBSTRING_INDEX(SUBSTRING_INDEX(`nethosts`.`mac`,':',5),':', -1),'-',SUBSTRING_INDEX(`nethosts`.`mac`, ':', -1)) as `value`, `users`.`login` FROM `nethosts` RIGHT JOIN `users` USING(`ip`)
                
        sql_attr_string         = login
        sql_attr_string         = title
        sql_field_string        = value
                
}

source src11 : src1
{
	sql_query		= SELECT `nethosts`.`id`,'mac' AS `title`,CONCAT(SUBSTRING_INDEX(`nethosts`.`mac`, ':', 1),SUBSTRING_INDEX(SUBSTRING_INDEX(`nethosts`.`mac`,':',2),':', -1),'.',SUBSTRING_INDEX(SUBSTRING_INDEX(`nethosts`.`mac`,':',3),':', -1),SUBSTRING_INDEX(SUBSTRING_INDEX(`nethosts`.`mac`,':',4),':', -1),'.',SUBSTRING_INDEX(SUBSTRING_INDEX(`nethosts`.`mac`,':',5),':', -1),SUBSTRING_INDEX(`nethosts`.`mac`, ':', -1)) as `value`, `users`.`login` FROM `nethosts` RIGHT JOIN `users` USING(`ip`)
                
        sql_attr_string         = login
        sql_attr_string         = title
        sql_field_string        = value
                
}




#############################################################################
## index definition
#############################################################################
index ip
{
	source			= src1
	path			= /opt/sphinx/sphinxdata/ip
	mlock			= 0
	morphology		= none
	min_word_len		= 1
	min_prefix_len		= 1
#	min_infix_len		= 2
	expand_keywords		= 1
#	index_exact_words	= 1
	html_strip		= 0
	blend_mode		= trim_both, skip_pure
#				  +     -       :      .       ,      '      `
#				                              U+0020
	blend_chars		= U+002B,U+002D,U+003A,U+002E,U+002C,U+0027,U+0060
}
index mac : ip
{
	source			= src3
	path			= /opt/sphinx/sphinxdata/mac
}
index realname : ip
{
	source			= src2
        path                    = /opt/sphinx/sphinxdata/realname
}
index login : ip
{
	source			= src4
	path			= /opt/sphinx/sphinxdata/login
}
index fulladdress : ip
{
	source			= src5
	path			= /opt/sphinx/sphinxdata/fulladdres
}
index mobile : ip
{
	source			= src6
	path			= /opt/sphinx/sphinxdata/mobile
	min_infix_len		= 2
}
index phone : ip
{
	source			= src7
	path			= /opt/sphinx/sphinxdata/phone
}
index contract : ip
{
	source			= src8
	path			= /opt/sphinx/sphinxdata/contract
}

#index paymentid : ip
#{
#	source			= src9
#	path			= /opt/sphinx/sphinxdata/paymentid
#}

#index macminus : ip
#{
#        source                  = src10
#        path                    = /opt/sphinx/sphinxdata/macminus
#}

#index macdot : ip
#{
#        source                  = src11
#        path                    = /opt/sphinx/sphinxdata/macdot
#}


#############################################################################
## indexer settings
#############################################################################

indexer
{
	mem_limit		= 128M
	# max_iops		= 40
	# max_iosize		= 1048576
	# max_xmlpipe2_field	= 4M
	# write_buffer		= 1M
	# max_file_field_buffer	= 32M
	# on_file_field_error = skip_document
	# lemmatizer_cache = 512M
}

#############################################################################
## searchd settings
#############################################################################

searchd
{
        listen                  = 127.0.0.1:9312
        listen                  = 127.0.0.1:9306:mysql41
	log			= /opt/sphinx/sphinxdata/logs/searchd.log
	query_log		= /opt/sphinx/sphinxdata/logs/query.log
	read_timeout		= 5
	client_timeout		= 300
	max_children		= 30
	persistent_connections_limit	= 30
	pid_file		= /opt/sphinx/sphinxdata/searchd.pid
	seamless_rotate		= 1
	preopen_indexes		= 1
	unlink_old		= 1
	# attr_flush_period	= 900
	#mva_updates_pool	= 1M
	max_packet_size		= 8M
	max_filters		= 256
	max_filter_values	= 4096
	# listen_backlog		= 5
	# read_buffer		= 256K
	# read_unhinted		= 32K
	max_batch_queries	= 32
	# subtree_docs_cache	= 4M
	# subtree_hits_cache	= 8M
	workers			= threads # for RT to work
	# dist_threads		= 4
	binlog_path		= # disable logging
	# binlog_path		= /var/data # binlog.001 etc will be created there
	# binlog_flush		= 2
	# binlog_max_log_size	= 256M
	# thread_stack			= 128K
	# expansion_limit		= 1000
	# rt_flush_period		= 900
	# query_log_format		= sphinxql
	# mysql_version_string	= 5.0.37
	# collation_server		= utf8_general_ci
	# collation_libc_locale	= ru_RU.UTF-8
	# watchdog				= 1
	# predicted_time_costs	= doc=64, hit=48, skip=2048, match=64
	# sphinxql_state			= sphinxvars.sql
	# rt_merge_iops			= 40
	# rt_merge_maxiosize		= 1M
	# ha_ping_interval		= 0
	# ha_period_karma			= 60
	# prefork_rotation_throttle	= 100
	# snippets_file_prefix		= /mnt/common/server1/
}

#############################################################################
## common settings
#############################################################################

common
{
	# lemmatizer_base = /usr/local/share/sphinx/dicts
	# on_json_attr_error = fail_index
	# json_autoconv_numbers = 1
	# json_autoconv_keynames = lowercase
	# rlp_root = /usr/local/share/sphinx/rlp
	# rlp_environment = /usr/local/share/sphinx/rlp/rlp/etc/rlp-environment.xml
	# rlp_max_batch_size = 100k
	# rlp_max_batch_docs = 100
	# plugin_dir			= /usr/local/sphinx/lib
}

# --eof--