From a3b2caf8416c687306b3c2e83b2fdc6e8708cce0 Mon Sep 17 00:00:00 2001 From: Dhruv Goel Date: Mon, 25 Jun 2012 00:03:46 +0530 Subject: [feature/sphinx-fulltext-search] include sample sphinx.conf in docs PHPBB3-10946 --- phpBB/docs/sphinx.sample.conf | 96 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 96 insertions(+) create mode 100644 phpBB/docs/sphinx.sample.conf (limited to 'phpBB/docs') diff --git a/phpBB/docs/sphinx.sample.conf b/phpBB/docs/sphinx.sample.conf new file mode 100644 index 0000000000..d7e59a11fc --- /dev/null +++ b/phpBB/docs/sphinx.sample.conf @@ -0,0 +1,96 @@ +source source_phpbb_{AVATAR_SALT}_main +{ + type = mysql + sql_host = localhost + sql_user = username + sql_pass = password + sql_db = db_name + sql_port = 3306 #optional, default is 3306 + sql_query_range = SELECT MIN(post_id), MAX(post_id) FROM phpbb_posts + sql_range_step = 5000 + sql_query = SELECT \ + p.post_id AS id, \ + p.forum_id, \ + p.topic_id, \ + p.poster_id, \ + IF(p.post_id = t.topic_first_post_id, 1, 0) as topic_first_post, \ + p.post_time, \ + p.post_subject, \ + p.post_subject as title, \ + p.post_text as data, \ + t.topic_last_post_time, \ + 0 as deleted \ + FROM phpbb_posts p, phpbb_topics t \ + WHERE \ + p.topic_id = t.topic_id \ + AND p.post_id >= $start AND p.post_id <= $end + sql_query_post = + sql_query_post_index = REPLACE INTO phpbb_sphinx ( counter_id, max_doc_id ) VALUES ( 1, $maxid ) + sql_query_info = SELECT * FROM phpbb_posts WHERE post_id = $id + sql_query_pre = SET NAMES utf8 + sql_query_pre = REPLACE INTO phpbb_sphinx SELECT 1, MAX(post_id) FROM phpbb_posts + sql_attr_uint = forum_id + sql_attr_uint = topic_id + sql_attr_uint = poster_id + sql_attr_bool = topic_first_post + sql_attr_bool = deleted + sql_attr_timestamp = post_time + sql_attr_timestamp = topic_last_post_time + sql_attr_str2ordinal = post_subject +} +source source_phpbb_{AVATAR_SALT}_delta : source_phpbb_{AVATAR_SALT}_main +{ + sql_query_range = + sql_range_step = + sql_query = SELECT \ + p.post_id AS id, \ + p.forum_id, \ + p.topic_id, \ + p.poster_id, \ + IF(p.post_id = t.topic_first_post_id, 1, 0) as topic_first_post, \ + p.post_time, \ + p.post_subject, \ + p.post_subject as title, \ + p.post_text as data, \ + t.topic_last_post_time, \ + 0 as deleted \ + FROM phpbb_posts p, phpbb_topics t \ + WHERE \ + p.topic_id = t.topic_id \ + AND p.post_id >= ( SELECT max_doc_id FROM phpbb_sphinx WHERE counter_id=1 ) + sql_query_pre = +} +index index_phpbb_{AVATAR_SALT}_main +{ + path = {DATA_PATH}/index_phpbb_{AVATAR_SALT}_main + source = source_phpbb_{AVATAR_SALT}_main + docinfo = extern + morphology = none + stopwords = + min_word_len = 2 + charset_type = utf-8 + charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, U+FF21..U+FF3A->a..z, A..Z->a..z, a..z, U+0149, U+017F, U+0138, U+00DF, U+00FF, U+00C0..U+00D6->U+00E0..U+00F6, U+00E0..U+00F6, U+00D8..U+00DE->U+00F8..U+00FE, U+00F8..U+00FE, U+0100->U+0101, U+0101, U+0102->U+0103, U+0103, U+0104->U+0105, U+0105, U+0106->U+0107, U+0107, U+0108->U+0109, U+0109, U+010A->U+010B, U+010B, U+010C->U+010D, U+010D, U+010E->U+010F, U+010F, U+0110->U+0111, U+0111, U+0112->U+0113, U+0113, U+0114->U+0115, U+0115, U+0116->U+0117, U+0117, U+0118->U+0119, U+0119, U+011A->U+011B, U+011B, U+011C->U+011D, U+011D, U+011E->U+011F, U+011F, U+0130->U+0131, U+0131, U+0132->U+0133, U+0133, U+0134->U+0135, U+0135, U+0136->U+0137, U+0137, U+0139->U+013A, U+013A, U+013B->U+013C, U+013C, U+013D->U+013E, U+013E, U+013F->U+0140, U+0140, U+0141->U+0142, U+0142, U+0143->U+0144, U+0144, U+0145->U+0146, U+0146, U+0147->U+0148, U+0148, U+014A->U+014B, U+014B, U+014C->U+014D, U+014D, U+014E->U+014F, U+014F, U+0150->U+0151, U+0151, U+0152->U+0153, U+0153, U+0154->U+0155, U+0155, U+0156->U+0157, U+0157, U+0158->U+0159, U+0159, U+015A->U+015B, U+015B, U+015C->U+015D, U+015D, U+015E->U+015F, U+015F, U+0160->U+0161, U+0161, U+0162->U+0163, U+0163, U+0164->U+0165, U+0165, U+0166->U+0167, U+0167, U+0168->U+0169, U+0169, U+016A->U+016B, U+016B, U+016C->U+016D, U+016D, U+016E->U+016F, U+016F, U+0170->U+0171, U+0171, U+0172->U+0173, U+0173, U+0174->U+0175, U+0175, U+0176->U+0177, U+0177, U+0178->U+00FF, U+00FF, U+0179->U+017A, U+017A, U+017B->U+017C, U+017C, U+017D->U+017E, U+017E, U+0410..U+042F->U+0430..U+044F, U+0430..U+044F, U+4E00..U+9FFF + min_prefix_len = 0 + min_infix_len = 0 +} +index index_phpbb_{AVATAR_SALT}_delta : index_phpbb_{AVATAR_SALT}_main +{ + path = {DATA_PATH}/index_phpbb_{AVATAR_SALT}_delta + source = source_phpbb_{AVATAR_SALT}_delta +} +indexer +{ + mem_limit = 512M +} +searchd +{ + compat_sphinxql_magics = 0 + listen = 127.0.0.1 + port = 3312 + log = {DATA_PATH}/log/searchd.log + query_log = {DATA_PATH}/log/sphinx-query.log + read_timeout = 5 + max_children = 30 + pid_file = {DATA_PATH}/searchd.pid + max_matches = 20000 +} -- cgit v1.2.1 From 10b706674e0fc100ff4e21d5fe100a9b532bb4bf Mon Sep 17 00:00:00 2001 From: Dhruv Goel Date: Tue, 10 Jul 2012 01:49:38 +0530 Subject: [feature/sphinx-fulltext-search] add binlog_path to config binlog files are now added to the data folder. PHPBB3-10946 --- phpBB/docs/sphinx.sample.conf | 1 + 1 file changed, 1 insertion(+) (limited to 'phpBB/docs') diff --git a/phpBB/docs/sphinx.sample.conf b/phpBB/docs/sphinx.sample.conf index d7e59a11fc..000d8157d6 100644 --- a/phpBB/docs/sphinx.sample.conf +++ b/phpBB/docs/sphinx.sample.conf @@ -93,4 +93,5 @@ searchd max_children = 30 pid_file = {DATA_PATH}/searchd.pid max_matches = 20000 + binlog_path = {DATA_PATH} } -- cgit v1.2.1 From 172c583f1941a8b162f1a7bf258bb3e38149606d Mon Sep 17 00:00:00 2001 From: Dhruv Date: Wed, 11 Jul 2012 16:57:18 +0530 Subject: [feature/sphinx-fulltext-search] use new unique id instead of salt a new unique id is generated by sphinx and stored in the config table instead of using avatar_salt. PHPBB3-10946 --- phpBB/docs/sphinx.sample.conf | 16 ++++++++-------- 1 file changed, 8 insertions(+), 8 deletions(-) (limited to 'phpBB/docs') diff --git a/phpBB/docs/sphinx.sample.conf b/phpBB/docs/sphinx.sample.conf index 000d8157d6..06595a766f 100644 --- a/phpBB/docs/sphinx.sample.conf +++ b/phpBB/docs/sphinx.sample.conf @@ -1,4 +1,4 @@ -source source_phpbb_{AVATAR_SALT}_main +source source_phpbb_{SPHINX_ID}_main { type = mysql sql_host = localhost @@ -38,7 +38,7 @@ source source_phpbb_{AVATAR_SALT}_main sql_attr_timestamp = topic_last_post_time sql_attr_str2ordinal = post_subject } -source source_phpbb_{AVATAR_SALT}_delta : source_phpbb_{AVATAR_SALT}_main +source source_phpbb_{SPHINX_ID}_delta : source_phpbb_{SPHINX_ID}_main { sql_query_range = sql_range_step = @@ -60,10 +60,10 @@ source source_phpbb_{AVATAR_SALT}_delta : source_phpbb_{AVATAR_SALT}_main AND p.post_id >= ( SELECT max_doc_id FROM phpbb_sphinx WHERE counter_id=1 ) sql_query_pre = } -index index_phpbb_{AVATAR_SALT}_main +index index_phpbb_{SPHINX_ID}_main { - path = {DATA_PATH}/index_phpbb_{AVATAR_SALT}_main - source = source_phpbb_{AVATAR_SALT}_main + path = {DATA_PATH}/index_phpbb_{SPHINX_ID}_main + source = source_phpbb_{SPHINX_ID}_main docinfo = extern morphology = none stopwords = @@ -73,10 +73,10 @@ index index_phpbb_{AVATAR_SALT}_main min_prefix_len = 0 min_infix_len = 0 } -index index_phpbb_{AVATAR_SALT}_delta : index_phpbb_{AVATAR_SALT}_main +index index_phpbb_{SPHINX_ID}_delta : index_phpbb_{SPHINX_ID}_main { - path = {DATA_PATH}/index_phpbb_{AVATAR_SALT}_delta - source = source_phpbb_{AVATAR_SALT}_delta + path = {DATA_PATH}/index_phpbb_{SPHINX_ID}_delta + source = source_phpbb_{SPHINX_ID}_delta } indexer { -- cgit v1.2.1 From 118b57f71d9f563f8eeda8e5925d482c38ab1af8 Mon Sep 17 00:00:00 2001 From: Dhruv Date: Thu, 12 Jul 2012 04:28:55 +0530 Subject: [feature/sphinx-fulltext-search] minor changes in sphinx.conf PHPBB3-10946 --- phpBB/docs/sphinx.sample.conf | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'phpBB/docs') diff --git a/phpBB/docs/sphinx.sample.conf b/phpBB/docs/sphinx.sample.conf index 06595a766f..eaef081aa1 100644 --- a/phpBB/docs/sphinx.sample.conf +++ b/phpBB/docs/sphinx.sample.conf @@ -6,6 +6,8 @@ source source_phpbb_{SPHINX_ID}_main sql_pass = password sql_db = db_name sql_port = 3306 #optional, default is 3306 + sql_query_pre = SET NAMES 'utf8' + sql_query_pre = REPLACE INTO phpbb_sphinx SELECT 1, MAX(post_id) FROM phpbb_posts sql_query_range = SELECT MIN(post_id), MAX(post_id) FROM phpbb_posts sql_range_step = 5000 sql_query = SELECT \ @@ -27,8 +29,6 @@ source source_phpbb_{SPHINX_ID}_main sql_query_post = sql_query_post_index = REPLACE INTO phpbb_sphinx ( counter_id, max_doc_id ) VALUES ( 1, $maxid ) sql_query_info = SELECT * FROM phpbb_posts WHERE post_id = $id - sql_query_pre = SET NAMES utf8 - sql_query_pre = REPLACE INTO phpbb_sphinx SELECT 1, MAX(post_id) FROM phpbb_posts sql_attr_uint = forum_id sql_attr_uint = topic_id sql_attr_uint = poster_id -- cgit v1.2.1 From b81941a997760eca4f209cc100fe2baec3ef4468 Mon Sep 17 00:00:00 2001 From: Dhruv Date: Thu, 12 Jul 2012 16:30:45 +0530 Subject: [feature/sphinx-fulltext-search] use CASE instead of IF IF is not supported in pgsql, use CASE instead supported in both mysql and pgsql. PHPBB3-10946 --- phpBB/docs/sphinx.sample.conf | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'phpBB/docs') diff --git a/phpBB/docs/sphinx.sample.conf b/phpBB/docs/sphinx.sample.conf index eaef081aa1..3ab2552096 100644 --- a/phpBB/docs/sphinx.sample.conf +++ b/phpBB/docs/sphinx.sample.conf @@ -15,7 +15,7 @@ source source_phpbb_{SPHINX_ID}_main p.forum_id, \ p.topic_id, \ p.poster_id, \ - IF(p.post_id = t.topic_first_post_id, 1, 0) as topic_first_post, \ + CASE WHEN p.post_id = t.topic_first_post_id THEN 1 ELSE 0 END as topic_first_post, \ p.post_time, \ p.post_subject, \ p.post_subject as title, \ @@ -47,7 +47,7 @@ source source_phpbb_{SPHINX_ID}_delta : source_phpbb_{SPHINX_ID}_main p.forum_id, \ p.topic_id, \ p.poster_id, \ - IF(p.post_id = t.topic_first_post_id, 1, 0) as topic_first_post, \ + CASE WHEN p.post_id = t.topic_first_post_id THEN 1 ELSE 0 END as topic_first_post, \ p.post_time, \ p.post_subject, \ p.post_subject as title, \ -- cgit v1.2.1 From 81959927e53ebc62765ff075d23feeaf9b40a95d Mon Sep 17 00:00:00 2001 From: Dhruv Date: Thu, 12 Jul 2012 17:22:03 +0530 Subject: [feature/sphinx-fulltext-search] use Update in sphinx query Instead of REPLACE use UPDATE since pgsql does not support REPLACE. A row is inserted at time of creating table so REPLACE is no longer needed. PHPBB3-10946 --- phpBB/docs/sphinx.sample.conf | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'phpBB/docs') diff --git a/phpBB/docs/sphinx.sample.conf b/phpBB/docs/sphinx.sample.conf index 3ab2552096..8ffd54a880 100644 --- a/phpBB/docs/sphinx.sample.conf +++ b/phpBB/docs/sphinx.sample.conf @@ -7,7 +7,7 @@ source source_phpbb_{SPHINX_ID}_main sql_db = db_name sql_port = 3306 #optional, default is 3306 sql_query_pre = SET NAMES 'utf8' - sql_query_pre = REPLACE INTO phpbb_sphinx SELECT 1, MAX(post_id) FROM phpbb_posts + sql_query_pre = UPDATE phpbb_sphinx SET max_doc_id = MAX(post_id) WHERE counter_id = 1 sql_query_range = SELECT MIN(post_id), MAX(post_id) FROM phpbb_posts sql_range_step = 5000 sql_query = SELECT \ @@ -27,7 +27,7 @@ source source_phpbb_{SPHINX_ID}_main p.topic_id = t.topic_id \ AND p.post_id >= $start AND p.post_id <= $end sql_query_post = - sql_query_post_index = REPLACE INTO phpbb_sphinx ( counter_id, max_doc_id ) VALUES ( 1, $maxid ) + sql_query_post_index = UPDATE phpbb_sphinx SET max_doc_id = $maxid WHERE counter_id = 1 sql_query_info = SELECT * FROM phpbb_posts WHERE post_id = $id sql_attr_uint = forum_id sql_attr_uint = topic_id -- cgit v1.2.1 From 161e469b5a67b2911089ec0dfdb70bef355ed07e Mon Sep 17 00:00:00 2001 From: Dhruv Date: Sun, 22 Jul 2012 02:50:53 +0530 Subject: [feature/sphinx-fulltext-search] makes sql host configurable The SQL server host which sphinx connects to index the posts is now configurable via ACP. PHPBB3-10946 --- phpBB/docs/sphinx.sample.conf | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'phpBB/docs') diff --git a/phpBB/docs/sphinx.sample.conf b/phpBB/docs/sphinx.sample.conf index 8ffd54a880..d0a897e0cc 100644 --- a/phpBB/docs/sphinx.sample.conf +++ b/phpBB/docs/sphinx.sample.conf @@ -1,7 +1,7 @@ source source_phpbb_{SPHINX_ID}_main { - type = mysql - sql_host = localhost + type = mysql #mysql or pgsql + sql_host = localhost #SQL server host sphinx connects to sql_user = username sql_pass = password sql_db = db_name -- cgit v1.2.1 From 39bac86f7db881a1035bebad56507145103218d5 Mon Sep 17 00:00:00 2001 From: Dhruv Date: Sun, 22 Jul 2012 03:43:50 +0530 Subject: [feature/sphinx-fulltext-search] improve port option Use listen instead of deprecated port value in sphinx config file. sqlhost uses default $dbhost. PHPBB3-10946 --- phpBB/docs/sphinx.sample.conf | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) (limited to 'phpBB/docs') diff --git a/phpBB/docs/sphinx.sample.conf b/phpBB/docs/sphinx.sample.conf index d0a897e0cc..aa0e8d905d 100644 --- a/phpBB/docs/sphinx.sample.conf +++ b/phpBB/docs/sphinx.sample.conf @@ -5,7 +5,7 @@ source source_phpbb_{SPHINX_ID}_main sql_user = username sql_pass = password sql_db = db_name - sql_port = 3306 #optional, default is 3306 + sql_port = 3306 #optional, default is 3306 for mysql and 5432 for pgsql sql_query_pre = SET NAMES 'utf8' sql_query_pre = UPDATE phpbb_sphinx SET max_doc_id = MAX(post_id) WHERE counter_id = 1 sql_query_range = SELECT MIN(post_id), MAX(post_id) FROM phpbb_posts @@ -85,8 +85,7 @@ indexer searchd { compat_sphinxql_magics = 0 - listen = 127.0.0.1 - port = 3312 + listen = localhost:9312 log = {DATA_PATH}/log/searchd.log query_log = {DATA_PATH}/log/sphinx-query.log read_timeout = 5 -- cgit v1.2.1 From f1729281e6f69202730c0926f3799da516fd3ae9 Mon Sep 17 00:00:00 2001 From: Dhruv Date: Fri, 27 Jul 2012 11:28:14 +0530 Subject: [feature/sphinx-fulltext-search] add sphinx to Authors file PHPBB3-10946 --- phpBB/docs/AUTHORS | 1 + 1 file changed, 1 insertion(+) (limited to 'phpBB/docs') diff --git a/phpBB/docs/AUTHORS b/phpBB/docs/AUTHORS index f0b4e25549..85653f04e2 100644 --- a/phpBB/docs/AUTHORS +++ b/phpBB/docs/AUTHORS @@ -75,6 +75,7 @@ Jabber Class (c) 2006 Flyspray.org, http://www.flyspray.org/ Chora (c) 2000-2006, The Horde Project. http://horde.org/chora/ Horde Project (c) 2000-2006, The Horde Project. http://horde.org/ jQuery (c) 2011, John Resig. http://jquery.com/ +Sphinx Technologies Inc (c) 2001-2012 Andrew Aksyonoff, http://sphinxsearch.com/ PHP License, version 3.0: Pear (c) 2001-2004 PHP Group, http://pear.php.net -- cgit v1.2.1