Data sources are defined in the Sphinx configuration file (in our case /usr/local/sphinx/etc/sphinx.conf). The data source block looks something like:

source name
{
# Source options
type = mysql
…….
…….
}

We need to provide a name to each source followed by the source options. The type option specifies whether the data source is MySQL or PostgreSQL, or xmlpipe or xmlpipe2. We will be looking at the respective data source options, which are dependent on the type, later in the chapter.

As mentioned earlier, Sphinx ships with two SQL data sources: MySQL and PostgreSQL. Let's see how we can use these data sources to create indexes that can later be searched using the searchd daemon.

Let's start by understanding how the MySQL data source worked in the example we saw in Chapter 2,Getting Started.

The data source configuration was as given next:

source src1
{
type = mysql
sql_host = localhost
sql_user = test
sql_pass =
sql_db = test
sql_port = 3306 # optional, default is 3306
sql_query = 
SELECT id, group_id, UNIX_TIMESTAMP(date_added) AS date_added, title, content 
FROM documents
sql_attr_uint = group_id
sql_attr_timestamp = date_added
sql_query_info = SELECT * FROM documents WHERE id=$id
}

In this case we named the data source as src1. We specified that we will be using MySQL as the source database with the help of the type option.

The next few options like sql_host, sql_user, sql_pass, sql_db and sql_port were used to define the connection parameters for connecting to the MySQL database.

Now let's understand what sql_query is used for. This option is used to specify the main SQL query that will fetch the data from the database. This is a mandatory option if you are using an SQL data source and there can only be one main query per source.

Select as many fields in the query as you want to be included in the index. However, document ID must be the very first field and it must be a unique unsigned positive integer. In this case the id field of the documents table will be treated as document id in the created index.

All the fields (except document ID) selected by the query are, by default, treated as full-text fields in the created index. If you want one or more fields to act as attributes in the index then you can do so with the help of the sql_attr_* option. In the previous example, we declared group_id to be an attribute of type unsigned integer and date_added to be an attribute of type timestamp. The following options can be used to declare different types of attributes:

As we had inserted four rows in the documents table, the query will retrieve all the four rows and create the index with id as document id, title and content as full-text fields, group_id as an unsigned integer attribute, and date_added as timestamp attribute.

When a search is performed on the index, the search term is matched against title and content fields, while the two attributes can be used for sorting and filtering.

The last option in the source configuration is sql_query_info and this is optional. This option only applies to MySQL source type. It is used by the CLI search utility to fetch and display document information. The $id macro is required and it expands to the queried document ID. By default the CLI search utility will display only the attributes stored in the index for the matched documents. If you want to display the actual text that was full-text indexed or any other information related to the matched document, the sql_query_info option comes in handy.