444 lines
15 KiB
PHP
444 lines
15 KiB
PHP
<?php
|
|
|
|
/**
|
|
* Simple query builder for MySQL and Sphinx Search (SphinxQL)
|
|
* Has no dependencies on actual connection & query layer (mysqli or whatever)
|
|
*
|
|
* Use it freely under the terms of GNU GPLv2 or later license
|
|
*
|
|
* Version: 2018-09-12
|
|
* (c) Vitaliy Filippov 2012-2018
|
|
*/
|
|
abstract class MysqlQueryBuilder
|
|
{
|
|
public $tableNames = [];
|
|
public $isSphinx = false;
|
|
|
|
/**
|
|
* Override this single method to use query builder
|
|
*/
|
|
abstract function quote($value);
|
|
|
|
function quoteId($name)
|
|
{
|
|
return "`".str_replace("`", "``", $name)."`";
|
|
}
|
|
|
|
/**
|
|
* Replace ? placeholders in $str with quoted parameters from $params
|
|
*
|
|
* @param string $str query fragment
|
|
* @param array $params bind parameters to interpolate into
|
|
*/
|
|
function quoteInto($str, $params)
|
|
{
|
|
$i = 0;
|
|
$r = '';
|
|
while (($p = strpos($str, '?')) !== false)
|
|
{
|
|
$r .= substr($str, 0, $p) . $this->quote($params[$i++]);
|
|
$str = substr($str, $p+1);
|
|
}
|
|
return $r.$str;
|
|
}
|
|
|
|
/**
|
|
* Builds SQL SELECT query text.
|
|
*
|
|
* @param mixed $tables see $this->tablesBuilder()
|
|
* @param mixed $fields Field definitions - either a string or an array.
|
|
* Strings are passed to resulting query text as-is.
|
|
* Arrays have the following format:
|
|
* [ 'field1', 'alias2' => 'expression2', ... ]
|
|
* @param mixed $where see $this->whereBuilder()
|
|
* @param array $options query options - array of:
|
|
* 'CALC_FOUND_ROWS'
|
|
* 'NO_CACHE' or 'CACHE'
|
|
* 'FOR UPDATE' or 'LOCK IN SHARE MODE'
|
|
* 'GROUP BY' => 'field ASC' or [ $groupby_field1 => 'ASC', $groupby_field2 => 'DESC' ]
|
|
* 'ORDER BY' => 'field ASC' or [ $orderby_field1 => 'ASC', $orderby_field2 => 'DESC' ]
|
|
* 'LIMIT' => [ $offset, $limit ] or [ $limit ] or just $limit
|
|
* 'OFFSET' => $offset, for the case when 'LIMIT' is just $limit
|
|
*
|
|
* Sphinx Search extensions:
|
|
* 'WITHIN GROUP ORDER BY' => [ $orderby_field => 'ASC' ]
|
|
* 'FIELD_WEIGHTS' => [ 'field' => <weight>, ... ]
|
|
* 'RANKER' => bm25|sph04|...|expr('...ranker expression...')
|
|
*/
|
|
function select_builder($tables, $fields, $where, $options = NULL)
|
|
{
|
|
if (!$options)
|
|
$options = [];
|
|
else
|
|
{
|
|
foreach ($options as $k => $v)
|
|
if (ctype_digit("$k"))
|
|
$options[$v] = true;
|
|
}
|
|
if (is_array($fields))
|
|
{
|
|
foreach ($fields as $k => $v)
|
|
if (!ctype_digit("$k"))
|
|
$fields[$k] = "$v AS ".$this->quoteId($k);
|
|
$fields = join(',', $fields);
|
|
}
|
|
$more = NULL;
|
|
$tables = $this->tables_builder($tables, $more);
|
|
if ($more)
|
|
$where = array_merge($where, (array)$more);
|
|
$where = $this->where_builder($where);
|
|
$sql = 'SELECT ';
|
|
if (isset($options['CALC_FOUND_ROWS']) || isset($options['SQL_CALC_FOUND_ROWS']))
|
|
$sql .= 'SQL_CALC_FOUND_ROWS ';
|
|
if (isset($options['NO_CACHE']) || isset($options['SQL_NO_CACHE']))
|
|
$sql .= 'SQL_NO_CACHE ';
|
|
elseif (isset($options['CACHE']) || isset($options['SQL_CACHE']))
|
|
$sql .= 'SQL_CACHE ';
|
|
$sql .= "$fields FROM $tables";
|
|
if ($where)
|
|
{
|
|
$sql .= " WHERE $where";
|
|
}
|
|
if (!empty($options['GROUP BY']) && $options['GROUP BY'] !== '0')
|
|
{
|
|
$sql .= " GROUP BY ".$this->order_option($options['GROUP BY']);
|
|
}
|
|
if (!empty($options['ORDER BY']) && $options['ORDER BY'] !== '0')
|
|
{
|
|
$sql .= " ORDER BY ".$this->order_option($options['ORDER BY']);
|
|
}
|
|
if ($this->isSphinx && !empty($options['WITHIN GROUP ORDER BY']) && $options['WITHIN GROUP GROUP BY'] !== '0')
|
|
{
|
|
// Sphinx Search extension
|
|
$sql .= " WITHIN GROUP ORDER BY ".$this->order_option($options['WITHIN GROUP ORDER BY']);
|
|
}
|
|
$sql .= $this->limit_option($options);
|
|
if ($this->isSphinx && !empty($options['FIELD_WEIGHTS']) || !empty($options['RANKER']))
|
|
{
|
|
// Sphinx Search extension
|
|
$opt = [];
|
|
if (!empty($options['FIELD_WEIGHTS']))
|
|
{
|
|
$weights = [];
|
|
foreach ($options['FIELD_WEIGHTS'] as $f => $w)
|
|
{
|
|
$weights[] = "`$f`=$w";
|
|
}
|
|
$opt[] = "field_weights=(".implode(', ', $weights).")";
|
|
}
|
|
if (!empty($options['RANKER']))
|
|
{
|
|
$opt[] = "ranker=".$options['RANKER'];
|
|
}
|
|
$sql .= " OPTION ".implode(', ', $opt);
|
|
}
|
|
if (isset($options['FOR UPDATE']))
|
|
$sql .= ' FOR UPDATE';
|
|
elseif (isset($options['LOCK IN SHARE MODE']))
|
|
$sql .= ' LOCK IN SHARE MODE';
|
|
return $sql;
|
|
}
|
|
|
|
/**
|
|
* Builds WHERE-part of an SQL query.
|
|
* $where can also be a string - then it's passed as-is.
|
|
*
|
|
* @param array $where Query conditions:
|
|
* [
|
|
* 'conditional expression',
|
|
* 'field_name' => 'value',
|
|
* 'field_name' => [ 'one', 'of', 'values' ],
|
|
* 'field_name < ?' => 'value',
|
|
* 'field_name < DATE_SUB(?, ?)' => [ 'arg1', 'arg2' ],
|
|
* 'field1,field2' => [ [ 1, 2 ], [ 3, 4 ] ],
|
|
* ]
|
|
*/
|
|
function where_builder($where)
|
|
{
|
|
if (!is_array($where))
|
|
return $where;
|
|
$wh = [];
|
|
foreach ($where as $k => $v)
|
|
{
|
|
if (ctype_digit("$k"))
|
|
{
|
|
if (is_array($v))
|
|
{
|
|
$str = array_shift($v);
|
|
$wh[] = $this->quoteInto($str, $v);
|
|
}
|
|
else
|
|
{
|
|
$wh[] = $v;
|
|
}
|
|
}
|
|
elseif (($p = strrpos($k, '?')) !== false)
|
|
{
|
|
$wh[] = $this->quoteInto($k, (array)$v);
|
|
}
|
|
elseif (is_array($v))
|
|
{
|
|
if (!$v)
|
|
{
|
|
throw new Exception("Error: empty array for '$k IN (...)', don't know what to do");
|
|
}
|
|
else
|
|
{
|
|
if (is_array(reset($v)))
|
|
{
|
|
// (a,b) IN ((1,2), (3,4)) ...
|
|
foreach ($v as &$l)
|
|
{
|
|
$l = "(" . implode(",", array_map([ $this, 'quote' ], $l)) . ")";
|
|
}
|
|
$wh[] = "$k IN (" . implode(",", $v) . ")";
|
|
}
|
|
else
|
|
{
|
|
$r = '';
|
|
$null = false;
|
|
foreach ($v as $i => $l)
|
|
{
|
|
if ($l === NULL)
|
|
{
|
|
$null = true;
|
|
}
|
|
else
|
|
{
|
|
$r .= $this->quote($l).',';
|
|
}
|
|
}
|
|
$r = $r !== '' ? "$k IN (" . substr($r, 0, -1) . ")" : '';
|
|
if ($null)
|
|
{
|
|
$r = $r !== '' ? "($r OR $k IS NULL)" : "$k IS NULL";
|
|
}
|
|
$wh[] = $r;
|
|
}
|
|
}
|
|
}
|
|
elseif (preg_match('/^-?\d+(\.\d+)?$/s', $v)) // int/float
|
|
$wh[] = "$k=$v";
|
|
elseif ($v !== NULL)
|
|
$wh[] = "$k=".$this->quote($v);
|
|
else
|
|
$wh[] = "$k IS NULL";
|
|
}
|
|
if ($this->isSphinx)
|
|
{
|
|
// Sphinx supports neither brackets nor OR operator as of 2.0.6-release O_o
|
|
$where = join(' AND ', $wh);
|
|
}
|
|
elseif ($where)
|
|
$where = '(' . join(') AND (', $wh) . ')';
|
|
else
|
|
$where = '';
|
|
return $where;
|
|
}
|
|
|
|
/**
|
|
* Handles ORDER BY or GROUP BY options
|
|
*/
|
|
protected function order_option($g)
|
|
{
|
|
if (is_array($g))
|
|
{
|
|
$g1 = [];
|
|
foreach ($g as $k => $v)
|
|
{
|
|
if (ctype_digit("$k"))
|
|
$g1[] = $v;
|
|
else
|
|
$g1[] = "$k $v";
|
|
}
|
|
$g = join(',', $g1);
|
|
}
|
|
return $g;
|
|
}
|
|
|
|
/**
|
|
* Handles a single LIMIT or LIMIT and OFFSET options.
|
|
*/
|
|
protected function limit_option($options)
|
|
{
|
|
if (isset($options['LIMIT']))
|
|
{
|
|
$g = $options['LIMIT'];
|
|
if (is_array($g))
|
|
$g = join(',', $g);
|
|
elseif ($g && isset($options['OFFSET']))
|
|
$g = "$options[OFFSET], $g";
|
|
if ($g)
|
|
return " LIMIT $g";
|
|
}
|
|
return '';
|
|
}
|
|
|
|
/**
|
|
* Builds FROM-part of an SQL query.
|
|
*
|
|
* $tables = [
|
|
* 'table',
|
|
* 'alias' => 'table',
|
|
* 'alias' => [ 'INNER', 'table_name', $where_for_on_clause ],
|
|
* 'alias(ignored)' => [ 'INNER', $nested_tables, $on_for_join_group ],
|
|
* ]
|
|
* or just a string "`table1` INNER JOIN `table2` ON ..."
|
|
* names taken into `backticks` will be transformed using $this->tableNames
|
|
*/
|
|
function tables_builder($tables, &$where = NULL)
|
|
{
|
|
if (is_array($tables))
|
|
{
|
|
$t = '';
|
|
foreach ($tables as $k => $v)
|
|
{
|
|
if (!is_array($v))
|
|
$v = [ 'INNER', $v, NULL ];
|
|
$join = strtolower(substr($v[0], 0, 4));
|
|
if ($join == 'righ')
|
|
$join = 'RIGHT';
|
|
elseif ($join == 'left')
|
|
$join = 'LEFT';
|
|
else /* if (!$join || $join == 'inne' || $join == 'join') */
|
|
$join = 'INNER';
|
|
if (is_array($v[1])) // nested join (left join (A join B on ...) on ...)
|
|
{
|
|
$more = NULL;
|
|
$table = $this->tables_builder($v[1], $more);
|
|
if ($more)
|
|
$v[2] = array_merge((array)$v[2], (array)$more);
|
|
if (count($v[1]) > 1)
|
|
$table = "($table)";
|
|
}
|
|
else
|
|
{
|
|
$table = (isset($this->tableNames[$v[1]]) ? $this->quoteId($this->tableNames[$v[1]]) : $v[1]);
|
|
if (!ctype_digit("$k"))
|
|
$table .= ' ' . $k;
|
|
}
|
|
if ($t)
|
|
$t .= " $join JOIN $table ON ".($this->where_builder($v[2]) ?: '1=1');
|
|
else
|
|
{
|
|
$t = $table;
|
|
$where = $v[2]; // extract ON to WHERE if only a single join is specified
|
|
}
|
|
}
|
|
$tables = $t;
|
|
}
|
|
else
|
|
$tables = (isset($this->tableNames[$tables]) ? $this->quoteId($this->tableNames[$tables]) : $tables);
|
|
return $tables;
|
|
}
|
|
|
|
/**
|
|
* Delete a set of rows.
|
|
*
|
|
* @param mixed $tables see $this->tables_builder()
|
|
* @param mixed $where see $this->where_builder()
|
|
* @param array $options Options for query:
|
|
* 'LIMIT' => [ $limit, $offset ] or [ $limit ] or just $limit
|
|
* 'OFFSET' => $offset, for the case when 'LIMIT' is just $limit
|
|
*/
|
|
function delete_builder($tables, $where, $options = NULL)
|
|
{
|
|
$tables = $this->tables_builder($tables);
|
|
$where = $this->where_builder($where) ?: '1=1';
|
|
$sql = "DELETE FROM $tables WHERE $where";
|
|
$sql .= $this->limit_option($options);
|
|
return $sql;
|
|
}
|
|
|
|
/**
|
|
* Builds an INSERT / REPLACE / INSERT IGNORE / INSERT OR UPDATE query.
|
|
*
|
|
* @param string $table Table name to insert rows to.
|
|
* @param array $rows Array of table rows to be inserted.
|
|
* @param string $action Conflict action: NULL, 'REPLACE', 'IGNORE' or 'UPDATE'
|
|
* REPLACE: delete matching rows, then insert all rows (MySQL REPLACE)
|
|
* IGNORE: ignore matching rows, insert missing rows (MySQL INSERT IGNORE)
|
|
* UPDATE: update matching rows, insert missing rows (MySQL INSERT ... ON DUPLICATE KEY UPDATE)
|
|
* @param array|string $uniqueKey Single unique key for conflict checking
|
|
* @param array|NULL $updateCols Columns to update in case of a conflict
|
|
*/
|
|
function insert_builder($table, $rows, $action = NULL, $uniqueKey = NULL, $updateCols = NULL)
|
|
{
|
|
if (!$rows || !is_array($rows))
|
|
return false;
|
|
$first = reset($rows);
|
|
if (!is_array($first))
|
|
$rows = [ $rows ];
|
|
if (isset($this->tableNames[$table]))
|
|
$table = $this->tableNames[$table];
|
|
$key = array_keys($rows[0]);
|
|
foreach ($rows as &$r)
|
|
{
|
|
$rs = [];
|
|
foreach ($key as &$k)
|
|
$rs[] = $this->quote($r[$k]);
|
|
$r = "(".implode(",", $rs).")";
|
|
}
|
|
foreach ($key as &$k)
|
|
if (strpos($k, '`') === false && (!$this->isSphinx || $k !== 'id'))
|
|
$k = $this->quoteId($k);
|
|
$sql = ($action == "REPLACE" ? "REPLACE" : "INSERT" . ($action == "IGNORE" ? " IGNORE" : "")).
|
|
" INTO $table (".implode(",",$key).") VALUES ".implode(",",$rows);
|
|
if ($action == "UPDATE")
|
|
{
|
|
if ($uniqueKey)
|
|
{
|
|
$uniqueKey = array_flip(is_array($uniqueKey) ? $uniqueKey : array_map('trim', explode(",", $uniqueKey)));
|
|
$cond = $uniqueKey;
|
|
foreach ($cond as $k => $v)
|
|
$v = "$k!=VALUES($k)";
|
|
// Trigger ERROR 1242 (21000): Subquery returns more than 1 row if trying to update based on different key conflict
|
|
$cond = "CASE WHEN ".implode(" OR ", $cond)." THEN (SELECT 1 UNION SELECT 2) ELSE ";
|
|
}
|
|
if ($updateCols)
|
|
{
|
|
$key = (array)$updateCols;
|
|
}
|
|
foreach ($key as &$k)
|
|
{
|
|
if ($uniqueKey && isset($uniqueKey[$k]))
|
|
$k = "$k=($cond $k END)";
|
|
else
|
|
$k = "$k=VALUES($k)";
|
|
}
|
|
$sql .= " ON DUPLICATE KEY UPDATE ".implode(",",$key);
|
|
}
|
|
return $sql;
|
|
}
|
|
|
|
/**
|
|
* Update row(s) in $table.
|
|
*
|
|
* @param string $table Table name to update.
|
|
* @param array $set Assoc array with values for update query.
|
|
* @param array $where Conditions for update query, see $this->where_builder().
|
|
* @param array $options Options for update query:
|
|
* 'LIMIT' => [ $limit, $offset ] or [ $limit ] or just $limit
|
|
* 'OFFSET' => $offset, for the case when 'LIMIT' is just $limit
|
|
*/
|
|
function update_builder($table, $set, $where = NULL, $options = NULL)
|
|
{
|
|
if (!$set)
|
|
return false;
|
|
if (count(func_get_args()) == 2)
|
|
throw new Exception(__CLASS__."::update(table, rows) is the old syntax, use upsert()");
|
|
$sql = [];
|
|
foreach ((array)$set as $k => $v)
|
|
{
|
|
if (!ctype_digit("$k"))
|
|
$sql[] = $k.'='.$this->quote($v);
|
|
else
|
|
$sql[] = $v;
|
|
}
|
|
$where = $this->where_builder($where) ?: '1=1';
|
|
$sql = 'UPDATE ' . $this->tables_builder($table) . ' SET ' . implode(', ', $sql) . ' WHERE ' . $where;
|
|
$sql .= $this->limit_option($options);
|
|
return $sql;
|
|
}
|
|
}
|