Поиск в ipb3 по именам загруженных файлов

[artmel]

Подскажите, как поправить sphinx.conf чтобы в ipb3 сделать поиск по именам загруженных

пользователями файлов.

 

закомментированный конфиг

############################ --- DOWNLOADS --- ##############################

дает ошибки, и на вид структура вообще не та.

ругается на 

 

REPLACE( pi.perm_view, '*', 0 ) as perm_view, \

 

 

 

#############################################################################

## data source definition

#############################################################################

 

source ipb_source_config

{

#setup

type = mysql

sql_host = localhost

sql_user = .....

sql_pass = ......

sql_db = .......

sql_port = 3306

}

 

 

############################### --- TAGS --- ################################

 

source core_tags_search_main : ipb_source_config

{

# Set our forum PID counter

sql_query_pre = SET NAMES utf8

sql_query_pre = REPLACE INTO cache_store VALUES( 'sphinx_core_tags_counter', (SELECT max(tag_id) FROM core_tags), 0, UNIX_TIMESTAMP(), 0 )

 

# Query posts for the main source

sql_query = SELECT t.tag_id, t.tag_id as search_id, t.tag_added, t.tag_member_id, t.tag_meta_id, t.tag_meta_parent_id, t.tag_text, t.tag_meta_app, REPLACE( t.tag_meta_area, '-', '_' ) as tag_meta_area, \

CONCAT( ',', p.tag_perm_text, ',') as tag_perm_text ,p.tag_perm_visible \

FROM core_tags t \

LEFT JOIN core_tags_perms p ON ( t.tag_aai_lookup=p.tag_perm_aai_lookup )

 

# Fields

sql_attr_uint = search_id

sql_attr_timestamp = tag_added

sql_attr_uint = tag_member_id

sql_attr_uint = tag_meta_id

sql_attr_uint = tag_meta_parent_id

sql_attr_uint = tag_perm_visible

sql_ranged_throttle = 0

}

 

source core_tags_search_delta : core_tags_search_main

{

# Override the base sql_query_pre

sql_query_pre = SET NAMES utf8

sql_query_pre =

 

# Query posts for the main source

sql_query = SELECT t.tag_id, t.tag_id as search_id, t.tag_added, t.tag_member_id, t.tag_meta_id, t.tag_meta_parent_id, t.tag_text, t.tag_meta_app, REPLACE( t.tag_meta_area, '-', '_' ) as tag_meta_area, \

CONCAT( ',', p.tag_perm_text, ',') as tag_perm_text ,p.tag_perm_visible \

FROM core_tags t \

LEFT JOIN core_tags_perms p ON ( t.tag_aai_lookup=p.tag_perm_aai_lookup ) \

WHERE t.tag_id > ( SELECT cs_value FROM cache_store WHERE cs_key='sphinx_core_tags_counter' )

}

 

index core_tags_search_main

{

source = core_tags_search_main

path = /var/sphinx/core_tags_search_main

 

docinfo = extern

mlock = 0

morphology = lemmatize_ru_all, lemmatize_en_all

min_word_len = 1

charset_type = utf-8

charset_table = 0..9, A..Z->a..z, _, -, @, *, U+002E, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F

html_strip = 0

infix_fields = tag_text

min_infix_len = 3

enable_star = 1

}

 

index core_tags_search_delta : core_tags_search_main

{

source = core_tags_search_delta

path = /var/sphinx/core_tags_search_delta

}

 

################################# --- FORUM --- ##############################

source forums_search_posts_main : ipb_source_config

{

# Set our forum PID counter

sql_query_pre = SET NAMES utf8

sql_query_pre = REPLACE INTO cache_store VALUES( 'sphinx_forums_counter_posts', (SELECT max(pid) FROM posts), 0, UNIX_TIMESTAMP(), 0 )

 

# Query posts for the main source

sql_query = SELECT p.pid, p.pid as search_id, p.author_id, p.post_date, REPLACE( p.post, '-', '&\#8208') as post, p.topic_id, p.queued, \

t.tid, LOWER(t.title) as tordinal, REPLACE( t.title, '-', '&\#8208') as title, t.views, t.posts, t.forum_id, t.last_post, t.state, t.start_date, t.starter_id, t.last_poster_id, t.topic_firstpost, \

CASE WHEN t.approved = -1 THEN 1 ELSE 0 END AS soft_deleted, \

CASE WHEN t.approved = -1 THEN 0 ELSE t.approved END AS approved, \

CASE WHEN t.topic_archive_status IN (0,3) THEN 0 ELSE 1 END AS archive_status, \

CONCAT( SUBSTRING( t.last_post, 2, 8 ), '0', LPAD( t.tid, 10, 0 ) ) as last_post_group \

FROM posts p \

LEFT JOIN topics t ON ( p.topic_id=t.tid )

 

# Fields

sql_attr_uint = queued

sql_attr_uint = approved

sql_attr_uint = soft_deleted

sql_attr_uint = archive_status

sql_attr_uint = search_id

sql_attr_uint = forum_id

sql_attr_timestamp = post_date

sql_attr_timestamp = last_post

sql_attr_timestamp = start_date

sql_attr_uint = author_id

sql_attr_uint = starter_id

sql_attr_uint = tid

sql_attr_uint = posts

sql_attr_uint = views

sql_attr_str2ordinal = tordinal

sql_attr_bigint = last_post_group

sql_attr_multi = uint tag_id from query; SELECT t.topic_firstpost, c.tag_id FROM core_tags c LEFT JOIN topics t ON (t.tid=c.tag_meta_id) WHERE c.tag_meta_app='forums' AND c.tag_meta_area='topics'

 

sql_ranged_throttle = 0

}

 

source forums_search_posts_delta : forums_search_posts_main

{

# Override the base sql_query_pre

sql_query_pre = SET NAMES utf8

sql_query_pre =

 

# Query posts for the delta source

sql_query = SELECT p.pid, p.pid as search_id, p.author_id, p.post_date, REPLACE( p.post, '-', '&\#8208') as post, p.topic_id, p.queued, \

t.tid, LOWER(t.title) as tordinal, REPLACE( t.title, '-', '&\#8208') as title, t.views, t.posts, t.forum_id, t.last_post, t.state, t.start_date, t.starter_id, t.last_poster_id, t.topic_firstpost, \

CASE WHEN t.approved = -1 THEN 1 ELSE 0 END AS soft_deleted, \

CASE WHEN t.approved = -1 THEN 0 ELSE t.approved END AS approved, \

CASE WHEN t.topic_archive_status IN (0,3) THEN 0 ELSE 1 END AS archive_status, \

CONCAT( SUBSTRING( t.last_post, 2, 8 ), '0', LPAD( t.tid, 10, 0 ) ) as last_post_group \

FROM posts p \

LEFT JOIN topics t ON ( p.topic_id=t.tid ) \

WHERE p.pid > ( SELECT cs_value FROM cache_store WHERE cs_key='sphinx_forums_counter_posts' )

 

sql_query_killlist = SELECT pid FROM posts WHERE pid > ( SELECT cs_value FROM cache_store WHERE cs_key='sphinx_forums_counter_posts' )

}

 

index forums_search_posts_main

{

source = forums_search_posts_main

path = /var/sphinx/forums_search_posts_main

 

docinfo = extern

mlock = 0

morphology = lemmatize_ru_all, lemmatize_en_all

min_word_len = 2

charset_type = utf-8

html_strip = 0

infix_fields = post, title

min_infix_len = 3

enable_star = 1

}

 

index forums_search_posts_delta : forums_search_posts_main

{

source = forums_search_posts_delta

path = /var/sphinx/forums_search_posts_delta

}

 

source forums_search_archive_main : ipb_source_config

{

# Set our forum PID counter

sql_query_pre = SET NAMES utf8

sql_query_pre = REPLACE INTO cache_store VALUES( 'sphinx_forums_counter_archives', (SELECT max(archive_id) FROM forums_archive_posts), 0, UNIX_TIMESTAMP(), 0 )

 

# Query posts for the main source

sql_query = SELECT p.archive_id, p.archive_id as search_id, p.archive_author_id as author_id, p.archive_content_date as post_date, REPLACE( p.archive_content, '-', '&\#8208') as archive_content, p.archive_topic_id, p.archive_queued as queued, \

t.tid, LOWER(t.title) as tordinal, REPLACE( t.title, '-', '&\#8208') as title, t.views, t.posts, t.forum_id, t.last_post, t.state, t.start_date, t.starter_id, t.last_poster_id, t.topic_firstpost, \

CASE WHEN t.approved = -1 THEN 1 ELSE 0 END AS soft_deleted, \

CASE WHEN t.approved = -1 THEN 0 ELSE t.approved END AS approved, \

CASE WHEN t.topic_archive_status IN (0,3) THEN 0 ELSE 1 END AS archive_status, \

CONCAT( SUBSTRING( t.last_post, 2, 8 ), '0', LPAD( t.tid, 10, 0 ) ) as last_post_group \

FROM forums_archive_posts p \

LEFT JOIN topics t ON ( p.archive_topic_id=t.tid )

 

# Fields

sql_attr_uint = queued

sql_attr_uint = approved

sql_attr_uint = soft_deleted

sql_attr_uint = archive_status

sql_attr_uint = search_id

sql_attr_uint = forum_id

sql_attr_timestamp = post_date

sql_attr_timestamp = last_post

sql_attr_timestamp = start_date

sql_attr_uint = author_id

sql_attr_uint = starter_id

sql_attr_uint = tid

sql_attr_uint = posts

sql_attr_uint = views

sql_attr_str2ordinal = tordinal

sql_attr_bigint = last_post_group

sql_attr_multi = uint tag_id from query; SELECT t.topic_firstpost, c.tag_id FROM core_tags c LEFT JOIN topics t ON (t.tid=c.tag_meta_id) WHERE c.tag_meta_app='forums' AND c.tag_meta_area='topics'

 

sql_ranged_throttle = 0

}

 

source forums_search_archive_delta : forums_search_archive_main

{

# Override the base sql_query_pre

sql_query_pre = SET NAMES utf8

sql_query_pre =

 

# Query posts for the delta source

sql_query = SELECT p.archive_id, p.archive_id as search_id, p.archive_author_id as author_id, p.archive_content_date as post_date, REPLACE( p.archive_content, '-', '&\#8208') as archive_content, p.archive_topic_id, p.archive_queued as queued, \

t.tid, LOWER(t.title) as tordinal, REPLACE( t.title, '-', '&\#8208') as title, t.views, t.posts, t.forum_id, t.last_post, t.state, t.start_date, t.starter_id, t.last_poster_id, t.topic_firstpost, \

CASE WHEN t.approved = -1 THEN 1 ELSE 0 END AS soft_deleted, \

CASE WHEN t.approved = -1 THEN 0 ELSE t.approved END AS approved, \

CASE WHEN t.topic_archive_status IN (0,3) THEN 0 ELSE 1 END AS archive_status, \

CONCAT( SUBSTRING( t.last_post, 2, 8 ), '0', LPAD( t.tid, 10, 0 ) ) as last_post_group \

FROM forums_archive_posts p \

LEFT JOIN topics t ON ( p.archive_topic_id=t.tid ) \

WHERE p.archive_id > ( SELECT cs_value FROM cache_store WHERE cs_key='sphinx_forums_counter_archives' )

 

sql_query_killlist = SELECT archive_id FROM forums_archive_posts WHERE archive_id > ( SELECT cs_value FROM cache_store WHERE cs_key='sphinx_forums_counter_archives' )

}

 

index forums_search_archive_main

{

source = forums_search_archive_main

path = /var/sphinx/forums_search_archive_main

 

docinfo = extern

mlock = 0

morphology = lemmatize_ru_all, lemmatize_en_all

min_word_len = 2

charset_type = utf-8

html_strip = 0

infix_fields = archive_content, title

min_infix_len = 3

enable_star = 1

}

 

index forums_search_archive_delta : forums_search_archive_main

{

source = forums_search_archive_delta

path = /var/sphinx/forums_search_archive_delta

}

############################ --- DOWNLOADS --- ##############################

 

#source downloads_search_main : ipb_source_config

#{

# # Set our forum PID counter

# sql_query_pre = REPLACE INTO ibf_cache_store VALUES( 'sphinx_downloads_counter', (SELECT max(file_id) FROM ibf_downloads_files), '', 0, UNIX_TIMESTAMP() )

#

# # Query posts for the main source

# sql_query = SELECT f.*, f.file_id as search_id, \

# REPLACE( pi.perm_view, '*', 0 ) as perm_view, \

# CASE WHEN pi.authorized_users IS NULL THEN 0 ELSE pi.authorized_users END AS authorized_users, \

# CASE WHEN pi.friend_only=0 THEN 0 ELSE f.file_submitter END AS friend_only, \

# CASE WHEN pi.owner_only=0 THEN 0 ELSE f.file_submitter END AS owner_only \

# FROM ibf_downloads_files f \

# LEFT JOIN ibf_permission_index pi ON ( pi.perm_type_id=f.file_cat AND pi.app='downloads' AND pi.perm_type='cat' ) \

# WHERE f.file_id <= ( SELECT cs_value FROM ibf_cache_store WHERE cs_key='sphinx_downloads_counter' )

#

# # Fields

# sql_attr_uint = search_id

# sql_attr_uint = friend_only

# sql_attr_uint = file_cat

# sql_attr_uint = owner_only

# sql_attr_timestamp = file_updated

# sql_attr_multi = uint perm_view from field

# sql_attr_multi = uint authorized_users from field

# sql_attr_uint = file_submitter

#

# sql_ranged_throttle = 0

#}

#

#source downloads_search_delta : downloads_search_main

#{

# # Override the base sql_query_pre

# sql_query_pre =

#

# # Query posts for the main source

# sql_query = SELECT f.*, f.file_id as search_id, \

# REPLACE( pi.perm_view, '*', 0 ) as perm_view, \

# CASE WHEN pi.authorized_users IS NULL THEN 0 ELSE pi.authorized_users END AS authorized_users, \

# CASE WHEN pi.friend_only=0 THEN 0 ELSE f.file_submitter END AS friend_only, \

# CASE WHEN pi.owner_only=0 THEN 0 ELSE f.file_submitter END AS owner_only \

# FROM ibf_downloads_files f \

# LEFT JOIN ibf_permission_index pi ON ( pi.perm_type_id=f.file_cat AND pi.app='downloads' AND pi.perm_type='cat' ) \

# WHERE f.file_id > ( SELECT cs_value FROM ibf_cache_store WHERE cs_key='sphinx_downloads_counter' )

#}

#

#index downloads_search_main

#{

# source = downloads_search_main

# path = /var/sphinx/downloads_search_main

#

# docinfo = extern

# mlock = 0

# morphology = stem_enru

# min_word_len = 2

# charset_type = sbcs

# html_strip = 0

#}

#

#index downloads_search_delta : downloads_search_main

#{

# source = downloads_search_delta

# path = /var/sphinx/downloads_search_delta

#}

 

#############################################################################

## indexer settings

#############################################################################

 

indexer

{

mem_limit = 256M

#lemmatizer_base = /var/sphinx/

}

 

#############################################################################

## searchd settings

#############################################################################

 

searchd

{

listen = 127.0.0.1

port = 9312

log = /var/sphinx/log/searchd.log

query_log = /var/sphinx/log/query.log

read_timeout = 5

max_children = 30

pid_file = /var/sphinx/log/searchd.pid

max_matches = 1000

seamless_rotate = 0

preopen_indexes = 0

unlink_old = 1

}

 

# --eof--

Изменено 28 Декабря 2014 пользователем artmel

[Denis Chursinov]

У меня оба этих запроса выполняются на отлично. Выдают все доступные файлы.

 

 

REPLACE INTO cache_store VALUES( 'sphinx_downloads_counter', (SELECT max(file_id) FROM downloads_files), '', 0, UNIX_TIMESTAMP() );
SELECT f.*, f.file_id as search_id,
REPLACE( pi.perm_view, '*', 0 ) as perm_view,
CASE WHEN pi.authorized_users IS NULL THEN 0 ELSE pi.authorized_users END AS authorized_users, 
CASE WHEN pi.friend_only=0 THEN 0 ELSE f.file_submitter END AS friend_only, 
CASE WHEN pi.owner_only=0 THEN 0 ELSE f.file_submitter END AS owner_only 
FROM downloads_files f 
LEFT JOIN permission_index pi ON ( pi.perm_type_id=f.file_cat AND pi.app='downloads' AND pi.perm_type='cat' ) 
WHERE f.file_id <= ( SELECT cs_value FROM cache_store WHERE cs_key='sphinx_downloads_counter' );

 

А чем структура не та? Все как у всех. Сначала прехапрос описан, который в кеш кладет дату обновления сфинкс-индекса. Потом идет основной запрос для построения индекса, его результат будет содержимым индекса. Потом описание типов данных для полей. Ну и дальше все повторяется для дельт-индекса, это короткий индекс, который хранит изменения, произошедшие между перестроением полного индекса.

[artmel]

Спасибо за ответ!

Дело в том , что у меня  файлы храняться в таблице 

attachments  (ipb3.4.7)

а в этом запросе она нигде не описана. 

[Denis Chursinov]

Так этот индекс никакого отношения к аттачам не имеет, это индексация файлов IP.Downloads. 

[artmel]

упс. Извиняюсь, ввел в заблуждение.

[artmel]

вот правильный запрос. 

Выдает текст поста название файла  и id поста и топика.

SELECT p.post, a.attach_file, a.attach_parent_id, a.attach_rel_id
FROM  attachments a
INNER JOIN  posts p ON ( p.pid = a.attach_rel_id ) 
WHERE a.attach_rel_module =  'post'
AND a.attach_file LIKE  '%BMW%'

Может кто помочь с составлением конфига на сфинкс.

и правильно выставить права доступа на найденое, или они не здесь выставляются?

[Denis Chursinov]

Я не понимаю, чем вам поможет индекс в сфинксе. Вам нужно сначала научить сам движок искать аттачи, для этого нужно написать расширение, а уже потом подключать ускорители. 

[artmel]

хм... 

Спасибо.

Буду думать.