Ubilling/docs/sphinxsearch/sphinx2.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
        expand_keywords         = 1
        html_strip              = 0
        blend_mode              = trim_both, skip_pure
        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
}
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
	# on_json_attr_error = fail_index
	# json_autoconv_numbers = 1
	# json_autoconv_keynames = lowercase
	# lemmatizer_base = /usr/local/share/sphinx/dicts
	# 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
	max_matches		= 1000
	seamless_rotate		= 1
	preopen_indexes		= 1
	unlink_old		= 1
	# attr_flush_period	= 900
	# ondisk_dict_default	= 1
	mva_updates_pool	= 1M
	max_packet_size		= 8M
	# crash_log_path		= /var/db/sphinxsearch/log/crash
	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/db/sphinxsearch/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
	# plugin_dir			= /usr/local/sphinx/lib
	# collation_server		= utf8_general_ci
	# collation_libc_locale	= ru_RU.UTF-8
	# watchdog				= 1
	# compat_sphinxql_magics	= 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/
}

# --eof--