sphinx.conf 18.8 KB
Newer Older
sreg's avatar
sreg committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334
source data_lbf_referenceformacode
{
	# data source type. mandatory, no default value
	# known types are 'mysql', 'pgsql', 'xmlpipe', 'xmlpipe2'
	type                   = mysql

	#####################################################################
	## SQL settings (for 'mysql' and 'pgsql' types)
	#####################################################################

	# some straightforward parameters for SQL source types
	sql_host               = 127.0.0.1
	sql_user               = 
	sql_pass               = 
	sql_db                 = 
	sql_port               = 3306  # optional, default is 3306
	mysql_connect_flags    = 32 # enable compression

	sql_query              = SELECT @newid:=@newid+1 AS id,REPLACE(FUNC_LOWERNOACC(r.label),'-',' ') AS label,r.extradata AS formacode,0 AS ad_id,r.id AS reference_id \
	                         FROM (SELECT @newid:=0) AS newid,reference r \
	                         WHERE r.type=5 AND r.status=1

	sql_attr_uint          = ad_id
	sql_attr_uint          = reference_id
}

index lbfreferenceformacode
{
	source                 = data_lbf_referenceformacode
	path                   = /home/sphinx/indexes/lbf/referenceformacode
	docinfo                = extern
	min_word_len           = 1
	min_prefix_len         = 1

	charset_table          = U+00AB->e,0..9, A..Z->a..z, _, a..z, \
	                         U+C0->a, U+C1->a, U+C2->a, U+C3->a, U+C4->a, U+C5->a, U+C6->a, \
	                         U+E0->a, U+E1->a, U+E2->a, U+E3->a, U+E4->a, U+E5->a, U+E6->a, U+C7->c,U+E7->c,\
	                         U+C8->e, U+C9->e, U+CA->e, U+CB->e, U+E8->e, U+E9->e, U+EA->e, U+EB->e, \
	                         U+CC->i, U+CD->i, U+CE->i, U+CF->i, U+EC->i, U+ED->i, U+EE->i, U+EF->i, \
	                         U+00C8->e, U+00C9->e, U+00CA->e, U+00CB->e, U+00E8->e, U+00E9->e, U+00EA->e, \
	                         U+00EB->e, U+0112->e, U+0113->e, U+0114->e, U+0115->e, U+0116->e, U+0117->e, \
	                         U+0118->e, U+0119->e, U+011A->e, U+011B->e, U+018E->e, U+0190->e, U+01DD->e, \
	                         U+0204->e, U+0205->e, U+0206->e, U+0207->e, U+0228->e, U+0229->e, U+0246->e, \
	                         U+0247->e, U+0258->e, U+025B->e, U+025C->e, U+025D->e, U+025E->e, U+029A->e, \
	                         U+1D07->e, U+1D08->e, U+1D31->e, U+1D32->e, U+1D49->e, U+1D4B->e, U+1D4C->e, \
	                         U+1D92->e, U+1D93->e, U+1D94->e, U+1D9F->e, U+1E14->e, U+1E15->e, U+1E16->e, \
	                         U+1E17->e, U+1E18->e, U+1E19->e, U+1E1A->e, U+1E1B->e, U+1E1C->e, U+1E1D->e, \
	                         U+1EB8->e, U+1EB9->e, U+1EBA->e, U+1EBB->e, U+1EBC->e, U+1EBD->e, U+1EBE->e, \
	                         U+1EBF->e, U+1EC0->e, U+1EC1->e, U+1EC2->e, U+1EC3->e, U+1EC4->e, U+1EC5->e, \
	                         U+1EC6->e, U+1EC7->e, U+2091->e,\
	                         U+00D2->o, U+00D3->o, U+00D4->o, U+00D5->o, U+00D6->o, U+00D8->o, U+00F2->o, U+00F3->o, \
	                         U+00F4->o, U+00F5->o, U+00F6->o, U+00F8->o, U+01030F->o, U+014C->o, U+014D->o, U+014E->o, \
	                         U+014F->o, U+0150->o, U+0151->o, U+0186->o, U+019F->o, U+01A0->o, U+01A1->o, U+01D1->o, \
	                         U+01D2->o, U+01EA->o, U+01EB->o, U+01EC->o, U+01ED->o, U+01FE->o, U+01FF->o, U+020C->o, \
	                         U+020D->o, U+020E->o, U+020F->o, U+022A->o, U+022B->o, U+022C->o, U+022D->o, U+022E->o, \
	                         U+022F->o, U+0230->o, U+0231->o, U+0254->o, U+0275->o, U+043E->o, U+04E6->o, U+04E7->o, \
	                         U+04E8->o, U+04E9->o, U+04EA->o, U+04EB->o, U+1D0F->o, U+1D10->o, U+1D11->o, U+1D12->o, \
	                         U+1D13->o, U+1D16->o, U+1D17->o, U+1D3C->o, U+1D52->o, U+1D53->o, U+1D54->o, U+1D55->o, \
	                         U+1D97->o, U+1DB1->o, U+1E4C->o, U+1E4D->o, U+1E4E->o, U+1E4F->o, U+1E50->o, U+1E51->o, \
	                         U+1E52->o, U+1E53->o, U+1ECC->o, U+1ECD->o, U+1ECE->o, U+1ECF->o, U+1ED0->o, U+1ED1->o, \
	                         U+1ED2->o, U+1ED3->o, U+1ED4->o, U+1ED5->o, U+1ED6->o, U+1ED7->o, U+1ED8->o, U+1ED9->o, \
	                         U+1EDA->o, U+1EDB->o, U+1EDC->o, U+1EDD->o, U+1EDE->o, U+1EDF->o, U+1EE0->o, U+1EE1->o, \
	                         U+1EE2->o, U+1EE3->o, U+2092->o, U+2C9E->o, U+2C9F->o
}

source data_lbf_reference
{
	# data source type. mandatory, no default value
	# known types are 'mysql', 'pgsql', 'xmlpipe', 'xmlpipe2'
	type                   = mysql

	#####################################################################
	## SQL settings (for 'mysql' and 'pgsql' types)
	#####################################################################

	# some straightforward parameters for SQL source types
	sql_host               = 127.0.0.1
	sql_user               = 
	sql_pass               = 
	sql_db                 = 
	sql_port               = 3306  # optional, default is 3306
	mysql_connect_flags    = 32 # enable compression

	#sql_query              = SELECT r.id,r.type,FUNC_LOWERNOACC(r.label) AS label,rl.level,REPLACE(rl.path,'/','_') AS path,rl.id AS locationid \
	#                         FROM reference r \
	#                         INNER JOIN reference rl ON rl.status=1 AND rl.type=6 AND rl.path=r.path \
	#                         WHERE r.status=1 AND r.type IN (6) AND r.path LIKE '/1/1/%' AND r.path!='/1/1/6/1/' AND r.level>2
	sql_query              = SELECT r.id, \
	                                r.type, \
	                                CONCAT(FUNC_LOWERNOACC(IF(r.labelsearch IS NOT NULL,r.labelsearch,r.label)),' ',FUNC_EXTRADATAALL('zc',r.extradata,'',null)) AS label, \
	                                r.level, \
	                                REPLACE(r.path,'/','_') AS path, \
	                                r.id AS locationid, \
	                         FUNC_EXTRADATA('in',r.extradata,'') AS codeinsee, \
	                         FUNC_EXTRADATA('rm',r.extradata,'') AS rome, \
	                         FUNC_EXTRADATA('ba',r.extradata,'') AS bassininsee, \
	                         FUNC_EXTRADATA('fm',r.extradata,'') AS formacode, \
	                         FUNC_EXTRADATA('dn',r.extradata,'') AS departementinsee, \
	                         TRIM(SUBSTRING(FUNC_EXTRADATA('fm',r.extradata,''),1,3)) AS racineformacode \
	                         FROM reference r \
	                         WHERE r.status=1

	sql_attr_uint          = type
	sql_attr_uint          = level
	sql_attr_uint          = locationid
	#sql_attr_string        = codeinsee
}

index lbfreference
{
	source                 = data_lbf_reference
	path                   = /home/sphinx/indexes/lbf/reference
	docinfo                = extern
	#    charset_type           = sbcs
	#    enable_star            = 1
	min_word_len           = 1
	min_prefix_len         = 1
}

source data_lbf_ad
{
	# data source type. mandatory, no default value
	# known types are 'mysql', 'pgsql', 'xmlpipe', 'xmlpipe2'
	type                   = mysql

	#####################################################################
	## SQL settings (for 'mysql' and 'pgsql' types)
	#####################################################################

	#alter table sphad connection='sphinx://127.0.0.1:3312/lbfad';

	# some straightforward parameters for SQL source types
	sql_host               = 127.0.0.1
	sql_user               = 
	sql_pass               = 
	sql_db                 = 
	sql_port               = 3306  # optional, default is 3306
	mysql_connect_flags    = 32 # enable compression

	sql_query_pre          = SET CHARACTER_SET_RESULTS=utf8
	sql_query_pre          = SET NAMES utf8

	sql_query              = SELECT s.id,a.id AS ad_id,a.catalogue_id,a.orga_id AS orgaid,s.id AS session_id,arb.id AS adrankid,arr.id AS adrankregionid,a.idorgaintercarif, \
	                                IF(s.beganat IS NULL,a.beganat,s.beganat) AS beganat,IF(s.endedat IS NULL,a.endedat,s.endedat) AS endedat, \
	                                REPLACE(REPLACE(a.codefinanceur,'[','_'),']','') AS codefinanceur, \
	                                a.idformintercarif,CONCAT(a.title,' ',o.name) AS title,IF(ar.followed>20,ar.cdi/IF(ar.followed>0,ar.followed,1),0) AS ratio, \
	                                IF(arb.bassinrate IS NULL,IF(arb.departementrate IS NULL,IF(arb.regionalrate IS NULL,arb.nationalrate,arb.regionalrate),arb.departementrate),arb.bassinrate) AS rate, \
	                                REPLACE(r.path,'/','_') AS locationpath, \
	                                REPLACE(r.path,'/','_') AS location, \
	                                REPLACE(r.path,'/','_') AS locationsearch, \
	                                a.romecode, \
	                                a.flags, \
	                                IF(a.flags&1,1,0) AS convention, \
	                                IF(a.flags&2,1,0) AS entreessortiespermanentes, \
	                                IF(a.flags&4,1,0) AS adistance, \
	                                IF(a.flags&8,1,0) AS diplomante, \
	                                IF(a.flags&16,1,0) AS certifiante, \
	                                IF(a.flags&32,1,0) AS contratapprentissage, \
	                                IF(a.flags&64,1,0) AS contratprofessionalisation, \
	                                IF(a.flags&128,1,0) AS rncp, \
	                                IF(a.flags&256,1,0) AS financementde, \
	                                IF((a.flags&256=0 OR a.flags&512) AND a.flags&32=0 AND a.flags&64=0,1,0) AS financementsalarie, \
	                                IF(a.flags&1024,1,0) AS financementpic, \
	                                a.formacode, \
	                                FUNC_EXTRADATA('in',r.extradata,'') AS codeinsee, \
	                                TRIM(SUBSTRING_INDEX(a.formacode,' ',1)) AS formacodeprincipal, \
	                                TRIM(SUBSTRING(SUBSTRING_INDEX(a.formacode,' ',1),1,3)) AS racineformacodeprincipal, \
	                                RADIANS(FUNC_EXTRADATA('lt',r.extradata,'')) AS lat,RADIANS(FUNC_EXTRADATA('lg',r.extradata,'')) AS lng, \
	                                IF(s.beganat IS NOT NULL,IF(IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat)>NOW() OR a.flags&2,10000000,DATEDIFF(IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat),NOW())),-9999999) AS proximitydate, \
	                                UNIX_TIMESTAMP(IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat)) AS beganattimestamp, \
	                                IF(a.duration>0,CEIL(a.duration/7/21),IF(s.beganat IS NOT NULL AND s.endedat IS NOT NULL,TIMESTAMPDIFF(MONTH,IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat),IF(month(s.endedat)=0,CONCAT(DATE_FORMAT(s.endedat, '%Y'), '-12-31'),s.endedat)),0)) AS dureeenmois, \
	                                IF(s.beganat IS NULL OR (a.flags&2)=1,0,IF(TIMESTAMPDIFF(MONTH,NOW(),IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat))<0,0,TIMESTAMPDIFF(MONTH,NOW(),IF(month(s.beganat)=0,CONCAT(DATE_FORMAT(s.beganat, '%Y'), '-01-01'),s.beganat)))) AS debutenmois, \
	                                a.niveausortie, \
	                                CONCAT(a.id,r.path) AS groupby \
	                         FROM ad a \
	                         INNER JOIN `session` s ON s.ad_id=a.id AND s.status='ACTIVE' AND (s.endedat IS NULL OR s.endedat>NOW()) \
	                         INNER JOIN orga o ON o.id=a.orga_id AND o.status='ACTIVE' \
	                         INNER JOIN reference r ON r.path=s.locationpath AND r.status=1 AND r.type=6 \
	                         LEFT OUTER JOIN adrankbassin arb ON arb.status='ACTIVE' AND arb.codeinsee=FUNC_EXTRADATA('ba',r.extradata,'') AND arb.formacode=TRIM(SUBSTRING_INDEX(a.formacode,' ',1)) \
	                         LEFT OUTER JOIN adrank ar ON ar.formacode=TRIM(SUBSTRING_INDEX(a.formacode,' ',1)) \
	                         LEFT OUTER JOIN adrankdepartement ard ON ard.formacode=TRIM(SUBSTRING_INDEX(a.formacode,' ',1)) AND ard.departementpath=FUNC_SUBPATH(a.locationpath,4) AND ard.status='ACTIVE' \
	                         LEFT OUTER JOIN adrankregion arr ON arr.formacode=TRIM(SUBSTRING_INDEX(a.formacode,' ',1)) AND arr.regionpath=FUNC_SUBPATH(a.locationpath,3) AND arr.status='ACTIVE' \
	                         WHERE a.status='ACTIVE'


	sql_attr_uint          = ad_id
	sql_attr_uint          = orgaid
	sql_attr_uint          = adrankid
	sql_attr_uint          = adrankregionid
	#sql_attr_uint          = locationid
	#sql_attr_uint          = locationparentid
	sql_attr_string        = location
	sql_attr_string        = formacodeprincipal
	sql_attr_float         = lat
	sql_attr_float         = lng
	sql_attr_float         = ratio
	sql_attr_float         = rate
	sql_attr_uint          = flags
	sql_attr_uint          = convention
	sql_attr_uint          = entreessortiespermanentes
	sql_attr_uint          = adistance
	sql_attr_uint          = certifiante
	sql_attr_uint          = diplomante
	sql_attr_uint          = contratapprentissage
	sql_attr_uint          = contratprofessionalisation
	sql_attr_uint          = rncp
	sql_attr_uint          = financementde
	sql_attr_uint          = financementsalarie
	sql_attr_uint          = financementpic
	sql_attr_uint          = racineformacodeprincipal
	sql_attr_float         = proximitydate
	sql_attr_timestamp     = beganattimestamp
	sql_attr_uint          = dureeenmois
	sql_attr_uint          = debutenmois
	sql_attr_uint          = niveausortie
	sql_attr_string        = groupby
}


index lbfad
{
	source                 = data_lbf_ad
	path                   = /home/sphinx/indexes/lbf/ad
	docinfo                = extern
	min_word_len           = 1
	min_prefix_len         = 1
	#enable_star           = true
	#morphology             = libstemmer_fr
	#expand_keywords        = 1
	min_stemming_len       = 3
	index_exact_words      = 1
	#wordforms              = /etc/sphinxsearch/wordforms.txt

	charset_table          = U+00AB->e,0..9, A..Z->a..z, _, a..z, \
	                         U+C0->a, U+C1->a, U+C2->a, U+C3->a, U+C4->a, U+C5->a, U+C6->a, \
	                         U+E0->a, U+E1->a, U+E2->a, U+E3->a, U+E4->a, U+E5->a, U+E6->a, U+C7->c,U+E7->c,\
	                         U+C8->e, U+C9->e, U+CA->e, U+CB->e, U+E8->e, U+E9->e, U+EA->e, U+EB->e, \
	                         U+CC->i, U+CD->i, U+CE->i, U+CF->i, U+EC->i, U+ED->i, U+EE->i, U+EF->i, \
	                         U+00C8->e, U+00C9->e, U+00CA->e, U+00CB->e, U+00E8->e, U+00E9->e, U+00EA->e, \
	                         U+00EB->e, U+0112->e, U+0113->e, U+0114->e, U+0115->e, U+0116->e, U+0117->e, \
	                         U+0118->e, U+0119->e, U+011A->e, U+011B->e, U+018E->e, U+0190->e, U+01DD->e, \
	                         U+0204->e, U+0205->e, U+0206->e, U+0207->e, U+0228->e, U+0229->e, U+0246->e, \
	                         U+0247->e, U+0258->e, U+025B->e, U+025C->e, U+025D->e, U+025E->e, U+029A->e, \
	                         U+1D07->e, U+1D08->e, U+1D31->e, U+1D32->e, U+1D49->e, U+1D4B->e, U+1D4C->e, \
	                         U+1D92->e, U+1D93->e, U+1D94->e, U+1D9F->e, U+1E14->e, U+1E15->e, U+1E16->e, \
	                         U+1E17->e, U+1E18->e, U+1E19->e, U+1E1A->e, U+1E1B->e, U+1E1C->e, U+1E1D->e, \
	                         U+1EB8->e, U+1EB9->e, U+1EBA->e, U+1EBB->e, U+1EBC->e, U+1EBD->e, U+1EBE->e, \
	                         U+1EBF->e, U+1EC0->e, U+1EC1->e, U+1EC2->e, U+1EC3->e, U+1EC4->e, U+1EC5->e, \
	                         U+1EC6->e, U+1EC7->e, U+2091->e,\
	                         U+00D2->o, U+00D3->o, U+00D4->o, U+00D5->o, U+00D6->o, U+00D8->o, U+00F2->o, U+00F3->o, \
	                         U+00F4->o, U+00F5->o, U+00F6->o, U+00F8->o, U+01030F->o, U+014C->o, U+014D->o, U+014E->o, \
	                         U+014F->o, U+0150->o, U+0151->o, U+0186->o, U+019F->o, U+01A0->o, U+01A1->o, U+01D1->o, \
	                         U+01D2->o, U+01EA->o, U+01EB->o, U+01EC->o, U+01ED->o, U+01FE->o, U+01FF->o, U+020C->o, \
	                         U+020D->o, U+020E->o, U+020F->o, U+022A->o, U+022B->o, U+022C->o, U+022D->o, U+022E->o, \
	                         U+022F->o, U+0230->o, U+0231->o, U+0254->o, U+0275->o, U+043E->o, U+04E6->o, U+04E7->o, \
	                         U+04E8->o, U+04E9->o, U+04EA->o, U+04EB->o, U+1D0F->o, U+1D10->o, U+1D11->o, U+1D12->o, \
	                         U+1D13->o, U+1D16->o, U+1D17->o, U+1D3C->o, U+1D52->o, U+1D53->o, U+1D54->o, U+1D55->o, \
	                         U+1D97->o, U+1DB1->o, U+1E4C->o, U+1E4D->o, U+1E4E->o, U+1E4F->o, U+1E50->o, U+1E51->o, \
	                         U+1E52->o, U+1E53->o, U+1ECC->o, U+1ECD->o, U+1ECE->o, U+1ECF->o, U+1ED0->o, U+1ED1->o, \
	                         U+1ED2->o, U+1ED3->o, U+1ED4->o, U+1ED5->o, U+1ED6->o, U+1ED7->o, U+1ED8->o, U+1ED9->o, \
	                         U+1EDA->o, U+1EDB->o, U+1EDC->o, U+1EDD->o, U+1EDE->o, U+1EDF->o, U+1EE0->o, U+1EE1->o, \
	                         U+1EE2->o, U+1EE3->o, U+2092->o, U+2C9E->o, U+2C9F->o
}

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

indexer
{
	# memory limit, in bytes, kiloytes (16384K) or megabytes (256M)
	# optional, default is 32M, max is 2047M, recommended is 256M to 1024M
	mem_limit              = 256M

	# maximum IO calls per second (for I/O throttling)
	# optional, default is 0 (unlimited)
	#
	# max_iops             = 40

	# maximum IO call size, bytes (for I/O throttling)
	# optional, default is 0 (unlimited)
	#
	# max_iosize           = 1048576
}

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

searchd
{
	# IP address to bind on
	# optional, default is 0.0.0.0 (ie. listen on all interfaces)
	#
	listen                 = 127.0.0.1:3312

	# log file, searchd run info is logged here
	# optional, default is 'searchd.log'
	log                    = /home/sphinx/log/searchd.log

	# query log file, all search queries are logged here
	# optional, default is empty (do not log queries)
	query_log              = /home/sphinx/log/query.log

	# client read timeout, seconds
	# optional, default is 5
	read_timeout           = 5

	# maximum amount of children to fork (concurrent searches to run)
	# optional, default is 0 (unlimited)
	max_children           = 30

	# PID file, searchd process ID file name
	# mandatory
	pid_file               = /var/run/sphinxsearch/searchd.pid

	# max amount of matches the daemon ever keeps in RAM, per-index
	# WARNING, THERE'S ALSO PER-QUERY LIMIT, SEE SetLimits() API CALL
	# default is 1000 (just like Google)
	#    max_matches            = 200000

	# seamless rotate, prevents rotate stalls if precaching huge datasets
	# optional, default is 1
	seamless_rotate        = 1

	# whether to forcibly preopen all indexes on startup
	# optional, default is 0 (do not preopen)
	# preopen_indexes        = 0

	# whether to unlink .old index copies on succesful rotation.
	# optional, default is 1 (do unlink)
	unlink_old             = 1
}