sphinx.conf

searchd
{
    listen = 0.0.0.0:9312
    listen = 0.0.0.0:9306:mysql41
    log = /var/log/sphinx/searchd.log
    query_log  = /var/log/sphinx/query.log
    read_timeout = 5
    max_children = 30
    pid_file     = /var/run/sphinx/searchd.pid
    max_matches  = 500
    seamless_rotate = 1
    preopen_indexes = 0
    unlink_old = 1
    workers = threads
    collation_server = utf8_general_ci
    rt_flush_period = 3600
    binlog_path = /var/lib/sphinx
}

indexer
{
        mem_limit = 256M
        max_iops = 30
        max_iosize = 4M
}

source base
{
    type = pgsql
    sql_host = 127.0.0.1
    sql_user = baruwa
    sql_pass = password
    sql_db = baruwa
}

source messages : base
{
    sql_query_range = SELECT MIN(id), MAX(id) FROM messages WHERE ts < get_var('maxts')
    sql_range_step  = 10000
    sql_query_pre = SELECT set_var('maxts', NOW())
    sql_query = SELECT id, messageid, subject, CRC32(from_address) AS from_addr, CRC32(to_address) AS \
                to_addr, CRC32(from_domain) AS from_dom, CRC32(to_domain) AS to_dom, headers, \
                hostname, UNIX_TIMESTAMP(timestamp) AS timestamp, isquarantined FROM messages \
                WHERE ts < get_var('maxts') AND id >= $start AND id <= $end
    sql_query_post = SELECT update_indexer_counters('messages_tmp', get_var('maxts'))
    sql_query_post_index = DELETE FROM indexer_counters WHERE tablename='messages'
        sql_query_post_index = UPDATE indexer_counters SET tablename='messages' WHERE tablename='messages_tmp'
    sql_query_post_index = DELETE FROM indexer_killlist WHERE ts < (SELECT maxts FROM indexer_counters WHERE tablename='messages') \
                AND tablename='messages'
    sql_attr_uint = from_addr
    sql_attr_uint = to_addr
    sql_attr_uint = from_dom
    sql_attr_uint = to_dom
    sql_attr_timestamp = timestamp
    sql_attr_bool = isquarantined
}

source messagesdelta : base
{
    sql_query_range = SELECT MIN(id), MAX(id) FROM messages WHERE ts >= get_var('maxts') AND ts < get_var('maxtsdelta')
    sql_range_step  = 10000
    sql_query_pre = SELECT set_var('maxts', (SELECT maxts FROM indexer_counters WHERE tablename='messages'))
    sql_query_pre = SELECT set_var('maxtsdelta', NOW())
    sql_query = SELECT id, messageid, subject, CRC32(from_address) AS from_addr, CRC32(to_address) AS \
                to_addr, CRC32(from_domain) AS from_dom, CRC32(to_domain) AS to_dom, headers, hostname, \
                UNIX_TIMESTAMP(timestamp) AS timestamp, isquarantined FROM messages \
                WHERE ts >= get_var('maxts') AND ts < get_var('maxtsdelta') \
                AND id >= $start AND id <= $end
    sql_query_killlist = SELECT id FROM messages WHERE ts >= get_var('maxts') AND ts < get_var('maxtsdelta') \
                UNION SELECT id FROM indexer_killlist WHERE tablename='messages'
    sql_query_post = SELECT update_indexer_counters('messages_delta_tmp', get_var('maxtsdelta'))
    sql_query_post_index = DELETE FROM indexer_counters WHERE tablename='messages_delta'
    sql_query_post_index = UPDATE indexer_counters SET tablename='messages_delta' WHERE tablename='messages_delta_tmp'
    sql_attr_uint = from_addr
    sql_attr_uint = to_addr
    sql_attr_uint = from_dom
    sql_attr_uint = to_dom
    sql_attr_timestamp = timestamp
    sql_attr_bool = isquarantined
}

source archive : base
{
    sql_query_range = SELECT MIN(id), MAX(id) FROM archive WHERE ts < get_var('archive_maxts')
    sql_range_step  = 10000
    sql_query_pre = SELECT set_var('archive_maxts', NOW())
    sql_query = SELECT id, messageid, subject, CRC32(from_address) AS from_addr, CRC32(to_address) AS \
                to_addr, CRC32(from_domain) AS from_dom, CRC32(to_domain) AS to_dom, \
                headers, hostname, UNIX_TIMESTAMP(timestamp) AS timestamp FROM archive \
                WHERE ts < get_var('archive_maxts') AND id >= $start AND id <= $end
    sql_query_post = SELECT update_indexer_counters('archive_tmp', get_var('archive_maxts'))
    sql_query_post_index = DELETE FROM indexer_counters WHERE tablename='archive'
    sql_query_post_index = UPDATE indexer_counters SET tablename='archive' WHERE tablename='archive_tmp'
    sql_query_post_index = DELETE FROM indexer_killlist WHERE ts < (SELECT maxts FROM indexer_counters WHERE tablename='archive') \
                AND tablename='archive'
    sql_attr_uint = from_addr
    sql_attr_uint = to_addr
    sql_attr_uint = from_dom
    sql_attr_uint = to_dom
    sql_attr_timestamp = timestamp
}

source archivedelta : base
{
    sql_query_range = SELECT MIN(id), MAX(id) FROM archive WHERE ts >= get_var('archive_maxts') \
                AND ts < get_var('archive_maxtsdelta')
    sql_range_step  = 10000
    sql_query_pre = SELECT set_var('archive_maxts', (SELECT maxts FROM indexer_counters WHERE tablename='archive'))
    sql_query_pre = SELECT set_var('archive_maxtsdelta', NOW())
    sql_query = SELECT id, messageid, subject, CRC32(from_address) AS from_addr, CRC32(to_address) AS \
                to_addr, CRC32(from_domain) AS from_dom, CRC32(to_domain) AS to_dom, headers, hostname, \
                UNIX_TIMESTAMP(timestamp) AS timestamp FROM archive WHERE ts >= get_var('archive_maxts') \
                AND ts < get_var('archive_maxtsdelta') AND id >= $start AND id <= $end
    sql_query_killlist = SELECT id FROM archive WHERE ts >= get_var('archive_maxts') AND \
                ts < get_var('archive_maxtsdelta') UNION SELECT id FROM indexer_killlist \
                WHERE tablename='archive'
    sql_query_post = SELECT update_indexer_counters('archive_delta_tmp', get_var('archive_maxtsdelta'))
    sql_query_post_index = DELETE FROM indexer_counters WHERE tablename='archive_delta'
    sql_query_post_index = UPDATE indexer_counters SET tablename='archive_delta' WHERE tablename='archive_delta_tmp'
    sql_attr_uint = from_addr
    sql_attr_uint = to_addr
    sql_attr_uint = from_dom
    sql_attr_uint = to_dom
    sql_attr_timestamp = timestamp
}

source lists : base
{
    sql_query = SELECT id, from_address AS froma, to_address AS to, from_address, to_address, list_type, user_id FROM lists
    sql_attr_str2ordinal = from_address
    sql_attr_str2ordinal = to_address
    sql_attr_uint = list_type
    sql_attr_uint = user_id
}

source organizations : base
{
  sql_query = SELECT id, name FROM organizations
  sql_attr_multi = uint admins from query; \
                SELECT organization_id, user_id FROM organizations_admins
}

source domains : base
{
    sql_query = SELECT id, name, CRC32(name) AS domain_name FROM maildomains
    sql_attr_uint = domain_name
    sql_attr_multi = uint orgs from query; \
                SELECT domain_id, organization_id FROM domain_owners
                                }

source accounts : base
{
  sql_query = SELECT id, username, firstname, lastname, email, active, local FROM users
  sql_attr_uint = active
  sql_attr_uint = local
  sql_attr_multi = uint domains from query; \
                SELECT user_id, domain_id FROM domain_users
}

source auditlog : base
{
    sql_query = SELECT id, username, info, hostname, remoteip, category, timestamp FROM auditlog
    sql_attr_uint = category
    sql_attr_timestamp = timestamp
}

index lists
{
        source = lists
        path = /var/lib/sphinx/lists
        docinfo = extern
        charset_type = utf-8
        ondisk_dict = 1
}

index lists_rt
{
        type = rt
        path = /var/lib/sphinx/lists_rt
        rt_field = froma
        rt_field = to
        rt_attr_string = from_address
        rt_attr_string = to_address
        rt_attr_bigint = list_type
        rt_attr_bigint = user_id
        docinfo = extern
        charset_type = utf-8
}

index organizations
{
        source = organizations
        path = /var/lib/sphinx/organizations
        docinfo = extern
        charset_type = utf-8
        ondisk_dict = 1
}

index organizations_rt
{
        type = rt
        path = /var/lib/sphinx/organizations_rt
        rt_field = name
}

index domains
{
        source = domains
        path = /var/lib/sphinx/domains
        docinfo = extern
        charset_type = utf-8
        ondisk_dict = 1
}

index domains_rt
{
        type = rt
        path = /var/lib/sphinx/domains_rt
        rt_field = name
        rt_attr_bigint = domain_name
}

index accounts
{
        source = accounts
        path = /var/lib/sphinx/accounts
        docinfo = extern
        charset_type = utf-8
        ondisk_dict = 1
}

index accounts_rt
{
        type = rt
        path = /var/lib/sphinx/accounts_rt
        rt_field = username
        rt_field = firstname
        rt_field = lastname
        rt_field = email
        rt_attr_bigint = active
        rt_attr_bigint = local
}

index messages
{
        source = messages
        path = /var/lib/sphinx/messages
        docinfo = extern
        charset_type = utf-8
        ondisk_dict = 1
}


index messages_rt
{
        type = rt
        path = /var/lib/sphinx/messages_rt
        rt_field = messageid
        rt_field = subject
        rt_field = headers
        rt_field = hostname
        rt_attr_bigint = from_addr
        rt_attr_bigint = to_addr
        rt_attr_bigint = from_dom
        rt_attr_bigint = to_dom
        rt_attr_timestamp = timestamp
        rt_attr_uint = isquarantined
        docinfo = extern
        charset_type = utf-8
}

index archive
{
        source = archive
        path = /var/lib/sphinx/archive
        docinfo = extern
        charset_type = utf-8
        ondisk_dict = 1
}

index auditlog
{
        source = auditlog
        path = /var/lib/sphinx/auditlog
        docinfo = extern
        charset_type = utf-8
        ondisk_dict = 1
}

index auditlog_rt
{
        type = rt
        path = /var/lib/sphinx/auditlog_rt
        rt_field = username
        rt_field = info
        rt_field = remoteip
        rt_field = hostname
        rt_attr_bigint = category
        rt_attr_timestamp = timestamp
        docinfo = extern
        charset_type = utf-8
}

index archivedelta
{
        source = archivedelta
        path = /var/lib/sphinx/archivedelta
        docinfo = extern
        charset_type = utf-8
        ondisk_dict = 1
}

index messagesdelta
{
        source = messagesdelta
        path = /var/lib/sphinx/messagesdelta
        docinfo = extern
        charset_type = utf-8
        ondisk_dict = 1
}