Query Builder for SphinxQL

About

This is a SphinxQL Query Builder used to work with SphinxQL, a SQL dialect used with the Sphinx search engine. It maps most of the functions listed in the SphinxQL reference and is generally faster than the available Sphinx API.

This Query Builder has no dependencies except PHP 5.3, \MySQLi extension, and Sphinx. It is also compatible with HHVM.

This package is BETA QUALITY. It is recommended that you do extensive testing in development before using it in a production environment.

Missing methods?

SphinxQL evolves very fast.

Most of the new functions are static one liners like SHOW PLUGINS. We'll avoid trying to keep up with these methods, as they are easy to just call directly (SphinxQL::create($conn)->query($sql)->execute()). You're free to submit pull requests to support these methods.

If any feature is unreachable through this library, open a new issue or send a pull request.

Code Quality

The majority of the methods in the package have been unit tested. The unit tests are run both in PHP and HHVM.

The only methods that have not been fully tested are the Helpers, which are mostly simple shorthands for SQL strings.

How to Contribute

Pull Requests

1. Fork the SphinxQL Query Builder repository
2. Create a new branch for each feature or improvement
3. Submit a pull request from each branch to the master branch

It is very important to separate new features or improvements into separate feature branches, and to send a pull request for each branch. This allows me to review and pull in new features or improvements individually.

Style Guide

All pull requests must adhere to the PSR-2 standard.

Unit Testing

All pull requests must be accompanied by passing unit tests and complete code coverage. The SphinxQL Query Builder uses phpunit for testing.

Learn about PHPUnit

Installation

This is a Composer package. You can install this package with the following command: php composer.phar install

Usage

The following examples will omit the namespace.

<?php
use Foolz\SphinxQL\SphinxQL;
use Foolz\SphinxQL\Connection;

// create a SphinxQL Connection object to use with SphinxQL
$conn = new Connection();
$conn->setParams(array('host' => 'domain.tld', 'port' => 9306));

$query = SphinxQL::create($conn)->select('column_one', 'colume_two')
    ->from('index_ancient', 'index_main', 'index_delta')
    ->match('comment', 'my opinion is superior to yours')
    ->where('banned', '=', 1);

$result = $query->execute();

Connection

• $conn = new Connection()

  Create a new Connection instance to be used with the following methods or SphinxQL class.

• $conn->silenceConnectionWarning($enable = true)

  Suppresses any warnings and errors displayed by the \MySQLi extension upon connection failure. This is disabled by default.

• $conn->setParams($params = array('host' => '127.0.0.1', 'port' => 9306))

  Sets the connection parameters used to establish a connection to the server. Supported parameters: 'host', 'port', 'socket', 'options'.

• $conn->query($query)

  Performs the query on the server. Returns an array of results for SELECT, or an int with the number of rows affected.

More methods are available in the Connection class, but usually not necessary as these are handled automatically.

SphinxQL

• SphinxQL::create($conn)

  Creates a SphinxQL instance used for generating queries.

Bypass Query Escaping

Often, you would need to call and run SQL functions that shouldn't be escaped in the query. You can bypass the query escape by wrapping the query in an \Expression.

• SphinxQL::expr($string)

  Returns the string without being escaped.

Query Escaping

There are cases when an input must be escaped in the SQL statement. The following functions are used to handle any escaping required for the query.

• $sq->escape($value)

  Returns the escaped value. This is processed with the \MySQLi::real_escape_string() function.

• $sq->quoteIdentifier($identifier)

  Adds backtick quotes to the identifier. For array elements, use $sq->quoteIdentifierArray($arr).

• $sq->quote($value)

  Adds quotes to the value and escapes it. For array elements, use $sq->quoteArr($arr).

• $sq->escapeMatch($value)

  Escapes the string to be used in MATCH.

• $sq->halfEscapeMatch($value)

  Escapes the string to be used in MATCH. The following characters are allowed: -, |, and ".

  Refer to $sq->match() for more information.

SELECT

• $sq = SphinxQL::create($conn)->select($column1, $column2, ...)->from($index1, $index2, ...)

  Begins a SELECT query statement. If no column is specified, the statement defaults to using *. Both $column1 and $index1 can be arrays.

INSERT, REPLACE

This will return an INT with the number of rows affected.

• $sq = SphinxQL::create($conn)->insert()->into($index)

  Begins an INSERT.

• $sq = SphinxQL::create($conn)->replace()->into($index)

  Begins an REPLACE.

• $sq->set($associative_array)

  Inserts an associative array, with the keys as the columns and values as the value for the respective column.

• $sq->value($column1, $value1)->value($column2, $value2)->value($column3, $value3)

  Sets the value of each column individually.

• $sq->columns($column1, $column2, $column3)->values($value1, $value2, $value3)->values($value11, $value22, $value33)

  Allows the insertion of multiple arrays of values in the specified columns.

  Both $column1 and $index1 can be arrays.

UPDATE

This will return an INT with the number of rows affected.

• $sq = SphinxQL::create($conn)->update($index)

  Begins an UPDATE.

• $sq->value($column1, $value1)->value($column2, $value2)

  Updates the selected columns with the respective value.

• $sq->set($associative_array)

  Inserts the associative array, where the keys are the columns and the respective values are the column values.

DELETE

Will return an array with an INT as first member, the number of rows deleted.

• $sq = SphinxQL::create($conn)->delete()->from($index)->where(...)

  Begins a DELETE.

WHERE

• $sq->where($column, $operator, $value)

  Standard WHERE, extended to work with Sphinx filters and full-text.

  <?php
  // WHERE `column` = 'value'
  $sq->where('column', 'value');
  
  // WHERE `column` = 'value'
  $sq->where('column', '=', 'value');
  
  // WHERE `column` >= 'value'
  $sq->where('column', '>=', 'value')
  
  // WHERE `column` IN ('value1', 'value2', 'value3')
  $sq->where('column', 'IN', array('value1', 'value2', 'value3'));
  
  // WHERE `column` NOT IN ('value1', 'value2', 'value3')
  $sq->where('column', 'NOT IN', array('value1', 'value2', 'value3'));
  
  // WHERE `column` BETWEEN 'value1' AND 'value2'
  // WHERE `example` BETWEEN 10 AND 100
  $sq->where('column', 'BETWEEN', array('value1', 'value2'))

  It should be noted that OR and parenthesis are not supported and implemented in the SphinxQL dialect yet.

MATCH

• $sq->match($column, $value, $half = false)

  Search in full-text fields. Can be used multiple times in the same query. Column can be an array. Value can be an Expression to bypass escaping (and use your own custom solution).

  <?php
  $sq->match('title', 'Otoshimono')
      ->match('character', 'Nymph')
      ->match(array('hates', 'despises'), 'Oregano');

  By default, all inputs are escaped. The usage of SphinxQL::expr($value) is required to bypass the default escaping and quoting function.

  The $half argument, if set to true, will not escape and allow the usage of the following characters: -, |, ". If you plan to use this feature and expose it to public interfaces, it is recommended that you wrap the query in a try catch block as the character order may throw a query error.

  <?php
  try
  {
      $result = SphinxQL::create($conn)->select()
          ->from('rt')
          ->match('title', 'Sora no || Otoshimono', true)
          ->match('title', SphinxQL::expr('"Otoshimono"/3'))
          ->match('loves', SphinxQL::expr(custom_escaping_fn('(you | me)')));
          ->execute();
  }
  catch (\Foolz\SphinxQL\DatabaseException $e)
  {
      // an error is thrown because two `|` one after the other aren't allowed
  }

GROUP, WITHIN GROUP, ORDER, OFFSET, LIMIT, OPTION

• $sq->groupBy($column)

  GROUP BY $column

• $sq->withinGroupOrderBy($column, $direction = null)

  WITHIN GROUP ORDER BY $column [$direction]

  Direction can be omitted with null, or be ASC or DESC case insensitive.

• $sq->orderBy($column, $direction = null)

  ORDER BY $column [$direction]

  Direction can be omitted with null, or be ASC or DESC case insensitive.

• $sq->offset($offset)

  LIMIT $offset, 9999999999999

  Set the offset. Since SphinxQL doesn't support the OFFSET keyword, LIMIT has been set at an extremely high number.

• $sq->limit($limit)

  LIMIT $limit

• $sq->limit($offset, $limit)

  LIMIT $offset, $limit

• $sq->option($name, $value)

  OPTION $name = $value

  Set a SphinxQL option such as max_matches or reverse_scan for the query.

TRANSACTION

• SphinxQL::create($conn)->transactionBegin()

  Begins a transaction.

• SphinxQL::create($conn)->transactionCommit()

  Commits a transaction.

• SphinxQL::create($conn)->transactionRollback()

  Rollbacks a transaction.

Executing and Compiling

• $sq->execute()

  Compiles, executes, and returns an array of results of a query.

• $sq->executeBatch()

  Compiles, executes, and returns an array of results for a multi-query.

• $sq->compile()

  Compiles the query.

• $sq->getCompiled()

  Returns the last query compiled.

• $sq->getResult()

  Returns the last result.

Multi-Query

• $sq->enqueue(SphinxQL $next = null)

  Queues the query. If a $next is provided, $next is appended and returned, otherwise a new SphinxQL object is returned.

• $sq->executeBatch()

  Returns an array of the results of all the queued queries.

<?php
$result = SphinxQL::create($this->conn)
    ->select()
    ->from('rt')
    ->match('title', 'sora')
    ->enqueue(SphinxQL::create($this->conn)->query('SHOW META')) // this returns the object with SHOW META query
    ->enqueue() // this returns a new object
    ->select()
    ->from('rt')
    ->match('content', 'nymph')
    ->executeBatch();

$result[0] will contain the first select. $result[1] will contain the META for the first query. $result[2] will contain the second select.

Helper

The Helper class contains useful methods that don't need "query building".

Remember to ->execute() to get a result.

• Helper::pairsToAssoc($result)

  Takes the pairs from a SHOW command and returns an associative array key=>value

The following methods return a prepared SphinxQL object. You can also use ->enqueue($next_object):

<?php
$result = SphinxQL::create($this->conn)
    ->select()
    ->from('rt')
    ->where('gid', 9003)
    ->enqueue(Helper::create($this->conn)->showMeta()) // this returns the object with SHOW META query prepared
    ->enqueue() // this returns a new object
    ->select()
    ->from('rt')
    ->where('gid', 201)
    ->executeBatch();

• Helper::create($conn)->showMeta() => 'SHOW META'
• Helper::create($conn)->showWarnings() => 'SHOW WARNINGS'
• Helper::create($conn)->showStatus() => 'SHOW STATUS'
• Helper::create($conn)->shotTables() => 'SHOW TABLES'
• Helper::create($conn)->showVariables() => 'SHOW VARIABLES'
• Helper::create($conn)->setVariable($name, $value, $global = false)
• Helper::create($conn)->callSnippets($data, $index, $query, $options = array())
• Helper::create($conn)->callKeywords($text, $index, $hits = null)
• Helper::create($conn)->describe($index)
• Helper::create($conn)->createFunction($udf_name, $returns, $soname)
• Helper::create($conn)->dropFunction($udf_name)
• Helper::create($conn)->attachIndex($disk_index, $rt_index)
• Helper::create($conn)->flushRtIndex($index)
• Helper::create($conn)->optimizeIndex($index)
• Helper::create($conn)->showIndexStatus($index)
• Helper::create($conn)->flushRamchunk($index)