sorgalla.com

#!/usr/bin/env php
<?php
 
// Define path to application directory
defined('APPLICATION_PATH')
    || define('APPLICATION_PATH', realpath(dirname(__FILE__) . '/..'));
 
// Define application environment
//
// Use `export APPLICATION_ENV=development` or edit /etc/environment
// to change the application environment
defined('APPLICATION_ENV')
    || define('APPLICATION_ENV', (getenv('APPLICATION_ENV') ? getenv('APPLICATION_ENV') : 'production'));
 
// Ensure library/ is on include_path
set_include_path(implode(PATH_SEPARATOR, array(
    realpath(APPLICATION_PATH . '/../../library'),
    get_include_path(),
)));
 
/** Zend_Application */
require_once 'Zend/Application.php';
 
// Create application, bootstrap, and run
$application = new Zend_Application(
    APPLICATION_ENV,
    APPLICATION_PATH . '/configs/application.ini'
);
$config = $application->getOptions();
?>
source posts
{
    type                    = mysql
 
    sql_host                = <?php echo $config['resources']['db']['params']['host'] . "\n"; ?>
    sql_user                = <?php echo $config['resources']['db']['params']['username'] . "\n"; ?>
    sql_pass                = <?php echo $config['resources']['db']['params']['password'] . "\n"; ?>
    sql_db                  = <?php echo $config['resources']['db']['params']['dbname'] . "\n"; ?>
    # sql_port               = 3306    # optional, default is 3306
 
    sql_query_pre           = SET NAMES utf8
 
    sql_query               = \
        SELECT posts.id, posts.category_id, posts.title, posts.content, UNIX_TIMESTAMP(posts.created) AS created, \
               categories.name AS category_name, \
               GROUP_CONCAT(tags.tag SEPARATOR ', ') AS tags \
          FROM posts \
          JOIN categories ON (categories.id = posts.category_id) \
     LEFT JOIN posts_tags ON (posts_tags.post_id = posts.id) \
     LEFT JOIN tags ON (tags.id = posts_tags.tag_id) \
      GROUP BY posts.id
 
    sql_attr_uint           = category_id
    sql_attr_timestamp      = created
 
    sql_query_info          = \
        SELECT posts.id, posts.category_id, posts.title, posts.content, posts.created, \
               categories.name AS category_name, \
               GROUP_CONCAT(tags.tag SEPARATOR ', ') AS tags \
          FROM posts \
          JOIN categories ON (categories.id = posts.category_id) \
     LEFT JOIN posts_tags ON (posts_tags.post_id = posts.id) \
     LEFT JOIN tags ON (tags.id = posts_tags.tag_id) \
         WHERE posts.id=$id
}
 
index posts
{
    source                  = posts
    path                    = <?php echo $config['sphinx']['index']['path']; ?>/posts
    docinfo                 = extern
    html_strip              = 1
    charset_type            = utf-8
    # Character table for german (from http://www.sphinxsearch.com/forum/view.html?id=19)
    charset_table           = 0..9, A..Z->a..z, _, a..z, U+C4->U+E4, U+D6->U+F6, U+DC->U+FC, U+DF, U+E4, U+F6, U+FC
}
 
indexer
{
    mem_limit               = 32M
}
 
searchd
{
    listen                  = <?php echo $config['sphinx']['searchd']['listen'] . "\n"; ?>
    log                     = <?php echo $config['sphinx']['searchd']['log'] . "\n"; ?>
    query_log               = <?php echo $config['sphinx']['searchd']['query_log'] . "\n"; ?>
    read_timeout            = 5
    max_children            = 30
    pid_file                = <?php echo $config['sphinx']['searchd']['pid_file'] . "\n"; ?>
    max_matches             = 1000
    seamless_rotate         = 1
    preopen_indexes         = 0
    unlink_old              = 1
}

$ ./scripts/sphinx COMMAND ENVIRONMENT

$ ./scripts/sphinx start production

$ ./scripts/sphinx index-all development

$query = 'Mein Suchbegriff';
$select = $db->select();
$select->from('posts')
       ->joinInner('sphinx', 'sphinx.id = posts.id')
       ->where('sphinx.query = ?', $query . ';mode=any;sort=extended:@weight desc, @id asc;limit=10;offset=0;index=posts');
$result = $select->query()->fetchAll();