sqlhelper.php

См. документацию.

<?php
 
namespace Bitrix\Main\DB;
 
use Bitrix\Main;
use Bitrix\Main\Type;
use Bitrix\Main\ORM;
 
abstract class SqlHelper
{
    protected $connection;
 
    protected $idCache;

    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    public function getLeftQuote()
    {
        return '';
    }

    public function getRightQuote()
    {
        return '';
    }

    abstract public function getAliasLength();

    public function quote($identifier)
    {
        if (empty($this->idCache[$identifier]))
        {
            // security unshielding
            $quotedIdentifier = str_replace([$this->getLeftQuote(), $this->getRightQuote()], '', $identifier);
 
            // shield [[database.]tablename.]columnname
            if (str_contains($quotedIdentifier, '.'))
            {
                $quotedIdentifier = str_replace('.', $this->getRightQuote() . '.' . $this->getLeftQuote(), $quotedIdentifier);
            }
 
            // shield general borders
            $this->idCache[$identifier] = $this->getLeftQuote() . $quotedIdentifier . $this->getRightQuote();
        }
 
        return $this->idCache[$identifier];
    }

    abstract public function getQueryDelimiter();

    abstract public function forSql($value, $maxLength = 0);

    public function convertToDbBinary($value)
    {
        return "'" . $this->forSql($value) . "'";
    }

    public function convertToFullText($value, $maxLength = 0)
    {
        return "'" . $this->forSql($value, $maxLength) . "'";
    }

    abstract public function getCurrentDateTimeFunction();

    abstract public function getCurrentDateFunction();

    abstract public function addSecondsToDateTime($seconds, $from = null);

    public function addDaysToDateTime($days, $from = null)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    abstract public function getDatetimeToDateFunction($value);

    abstract public function formatDate($format, $field = null);

    public function getSubstrFunction($str, $from, $length = null)
    {
        $sql = 'SUBSTR('.$str.', '.$from;
 
        if (!is_null($length))
            $sql .= ', '.$length;
 
        return $sql.')';
    }

    abstract public function getConcatFunction();

    abstract public function getIsNullFunction($expression, $result);

    abstract public function getLengthFunction($field);

    abstract public function getCharToDateFunction($value);

    abstract public function getDateToCharFunction($fieldName);

    abstract public function castToChar($fieldName);

    abstract public function softCastTextToChar($fieldName);

    abstract public function getTopSql($sql, $limit, $offset = 0);

    public function prepareInsert($tableName, array $fields, $returnAsArray = false)
    {
        $columns = array();
        $values = array();
 
        $tableFields = $this->connection->getTableFields($tableName);
 
        $tableFields = array_change_key_case($tableFields, CASE_UPPER);
        $fields = array_change_key_case($fields, CASE_UPPER);
 
        foreach ($fields as $columnName => $value)
        {
            if (isset($tableFields[$columnName]))
            {
                $columns[] = $this->quote($columnName);
                $values[] = $this->convertToDb($value, $tableFields[$columnName]);
            }
            else
            {
                trigger_error("Column `{$columnName}` is not found in the `{$tableName}` table", E_USER_WARNING);
            }
        }
 
        $binds = $this->prepareBinds($tableFields, $fields);
 
        return array(
            $returnAsArray ? $columns : implode(", ", $columns),
            $returnAsArray ? $values : implode(", ", $values),
            $binds
        );
    }

    public function prepareUpdate($tableName, array $fields)
    {
        $update = array();
 
        $tableFields = $this->connection->getTableFields($tableName);
 
        // one registry
        $tableFields = array_change_key_case($tableFields, CASE_UPPER);
        $fields = array_change_key_case($fields, CASE_UPPER);
 
        foreach ($fields as $columnName => $value)
        {
            if (isset($tableFields[$columnName]))
            {
                $update[] = $this->quote($columnName).' = '.$this->convertToDb($value, $tableFields[$columnName]);
            }
            else
            {
                trigger_error("Column `{$columnName}` is not found in the `{$tableName}` table", E_USER_WARNING);
            }
        }
 
        $binds = $this->prepareBinds($tableFields, $fields);
 
        return array(
            implode(", ", $update),
            $binds
        );
    }

    abstract public function prepareMerge($tableName, array $primaryFields, array $insertFields, array $updateFields);

    protected function prepareBinds(array $tableFields, array $fields)
    {
        return array();
    }

    public function prepareAssignment($tableName, $columnName, $value)
    {
        $tableField = $this->connection->getTableField($tableName, $columnName);
 
        return $this->quote($columnName).' = '.$this->convertToDb($value, $tableField);
    }

    public function convertToDb($value, ORM\Fields\IReadable $field = null)
    {
        if ($value === null)
        {
            return "NULL";
        }
 
        if ($value instanceof SqlExpression)
        {
            return $value->compile();
        }
 
        if (is_a($field, '\Bitrix\Main\ORM\Fields\StringField'))
        {
            $size = $field->getSize();
            if ($size)
            {
                $value = mb_substr($value, 0, $size);
            }
        }
 
        if($field instanceof ORM\Fields\IReadable)
        {
            $result = $field->convertValueToDb($value);
        }
        else
        {
            $result = $this->convertToDbString($value);
        }
 
        return $result;
    }

    public function convertFromDb($value, ORM\Fields\IReadable $field)
    {
        return $field->convertValueFromDb($value);
    }

    public function convertToDbInteger($value, $size = 8)
    {
        $value = intval($value);
        if ($size == 2)
        {
            $value = max(-32768, min(+32767, $value));
        }
        elseif ($size == 4)
        {
            $value = max(-2147483648, min(+2147483647, $value));
        }
        return $value;
    }

    public function convertFromDbInteger($value)
    {
        return intval($value);
    }

    public function convertToDbFloat($value, $scale = null)
    {
        $value = doubleval($value);
        if(!is_finite($value))
        {
            $value = 0;
        }
 
        return $scale !== null ? "'".round($value, $scale)."'" : "'".$value."'";
    }

    public function convertFromDbFloat($value, $scale = null)
    {
        $value = doubleval($value);
 
        return $scale !== null ? round($value, $scale) : $value;
    }

    public function convertToDbString($value, $length = null)
    {
        return "'".$this->forSql($value, $length)."'";
    }

    public function convertFromDbString($value, $length = null)
    {
        if ($length > 0)
        {
            $value = mb_substr($value, 0, $length);
        }
 
        return strval($value);
    }

    public function convertToDbText($value)
    {
        return $this->convertToDbString($value);
    }

    public function convertFromDbText($value)
    {
        return $this->convertFromDbString($value);
    }

    public function convertToDbDate($value)
    {
        if (empty($value))
        {
            return "NULL";
        }
        elseif($value instanceof Type\Date)
        {
            return $this->getCharToDateFunction($value->format("Y-m-d"));
        }
        else
        {
            throw new Main\ArgumentTypeException('value', '\Bitrix\Main\Type\Date');
        }
    }

    public function convertFromDbDate($value)
    {
        return new Type\Date($value);
    }

    public function convertToDbDateTime($value)
    {
        if (empty($value))
        {
            return "NULL";
        }
        elseif($value instanceof Type\Date)
        {
            if($value instanceof Type\DateTime)
            {
                $value = clone($value);
                $value->setDefaultTimeZone();
            }
            return $this->getCharToDateFunction($value->format("Y-m-d H:i:s"));
        }
        else
        {
            throw new Main\ArgumentTypeException('value', '\Bitrix\Main\Type\Date');
        }
    }

    public function convertFromDbDateTime($value)
    {
        return new Type\DateTime($value);
    }

    public function convertDatetimeField($value)
    {
        return $this->convertFromDbDateTime($value);
    }

    public function convertDateField($value)
    {
        return $this->convertFromDbDate($value);
    }

    public function getConverter(ORM\Fields\ScalarField $field)
    {
        return false;
    }

    abstract public function getColumnTypeByField(ORM\Fields\ScalarField $field);

    abstract public function getFieldByColumnType($name, $type, array $parameters = null);

    public function getAscendingOrder()
    {
        return 'ASC';
    }

    public function getDescendingOrder()
    {
        return 'DESC';
    }

    public function getConditionalAssignment($field, string $value): string
    {
        $field = $field instanceof SqlExpression ? $field->compile() : $this->quote($field);
        $hash = $this->convertToDbString(sha1($value));
        $value = $this->convertToDbString($value);
 
        return 'case when ' . $this->getSha1Function($field) . ' = ' . $hash . ' then ' . $field . ' else ' . $value . ' end';
    }

    public function getInsertIgnore($tableName, $fields, $sql)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function getRandomFunction()
    {
        return 'rand()';
    }

    public function getSha1Function($field)
    {
        return 'sha1(' . $field . ')';
    }

    public function getRegexpOperator($field, $regexp)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function getIlikeOperator($field, $value)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function values($identifier)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function getMatchFunction($field, $value)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function getMatchAndExpression($values, $prefixSearch = false)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function getMatchOrExpression($values, $prefixSearch = false)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function prepareMergeMultiple($tableName, array $primaryFields, array $insertRows)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function prepareMergeSelect($tableName, array $primaryFields, array $selectFields, $select, $updateFields)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function prepareDeleteLimit($tableName, array $primaryFields, $where, array $order, $limit)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function initRowNumber($variableName)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function getRowNumber($variableName)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function prepareCorrelatedUpdate($tableName, $tableAlias, $fields, $from, $where)
    {
        throw new Main\NotImplementedException('Method should be implemented in a child class.');
    }

    public function prepareMergeValues(string $tableName, array $primaryFields, array $insertRows, array $updateFields = []): string
    {
        $insertColumns = array_keys($insertRows[array_key_first($insertRows)] ?? []);
        $insertValuesStrings = [];
        foreach ($insertRows as $row)
        {
            [, $rowValues] = $this->prepareInsert($tableName, $row);
            $insertValuesStrings[] = $rowValues;
        }
 
        if (empty($updateFields))
        {
            $notPrimaryFields = array_diff($insertColumns, $primaryFields);
            if (empty($notPrimaryFields))
            {
                trigger_error("Only primary fields to update, use getInsertIgnore() or specify fields", E_USER_WARNING);
            }
            $updateFields = $notPrimaryFields;
        }
 
        $compatibleUpdateFields = [];
 
        foreach ($updateFields as $key => $value)
        {
            if (is_numeric($key) && is_string($value))
            {
                $compatibleUpdateFields[$value] = new SqlExpression('?v', $value);
            }
            else
            {
                $compatibleUpdateFields[$key] = $value;
            }
        }
 
        $insertValueString = 'values (' . implode('),(', $insertValuesStrings) . ')';
 
        return $this->prepareMergeSelect($tableName, $primaryFields, $insertColumns, $insertValueString, $compatibleUpdateFields);
    }

    public function getOrderByStringField(string $field, array $values, bool $quote = true): string
    {
        return $this->getOrderByField($field, $values, [$this, 'convertToDbString'], $quote);
    }

    public function getOrderByIntField(string $field, array $values, bool $quote = true): string
    {
        return $this->getOrderByField($field, $values, [$this, 'convertFromDbInteger'], $quote);
    }

    protected function getOrderByField(string $field, array $values, callable $callback, bool $quote = true): string
    {
        return $quote ? $this->quote($field) : $field;
    }

    public function getQueryTables(string $sql, int $maxLevel = -1) : array
    {
        $level = 0;
        $tables = [];
 
        $escaped = false;
        $singleQuotes = false;
        $doubleQuotes = false;
 
        $isFrom = [0 => false];
        $isTable = [0 => false];
        $isIf = [0 => false];
 
        $sql = preg_replace('/\s\s+/m', ' ', $sql);
        $sql = preg_replace('/(HOUR|MINUTE|SECOND|YEAR|QUARTER|WEEK|MICROSECOND)(\s+)FROM/is', 'XXX_FROM', $sql);
 
        foreach (preg_split('/([,()"\'\\\\]|\s+)/s', $sql, -1, PREG_SPLIT_DELIM_CAPTURE) as $token)
        {
            if ($maxLevel > -1 && $level > $maxLevel)
            {
                break;
            }
 
            $token = trim($token, "` ;\t\n\r");
            if ($token === '\\')
            {
                $escaped = !$escaped;
                continue;
            }
 
            if ($token === '"' && !$escaped)
            {
                $doubleQuotes = !$doubleQuotes;
                continue;
            }
 
            if ($token === '\'' && !$escaped)
            {
                $singleQuotes = !$singleQuotes;
                continue;
            }
 
            if ($token && !$doubleQuotes && !$singleQuotes)
            {
                if ($token === '(')
                {
                    $isTable[$level] = false;
                    $level++;
                    $isFrom[$level] = false;
                    $isTable[$level] = false;
                    $isIf[$level] = false;
                }
                elseif ($token === ')')
                {
                    $isTable[$level] = false;
                    if ($level > 0)
                    {
                        $level--;
                    }
                }
                else
                {
                    switch (strtoupper($token))
                    {
                        case 'INTO':
                            $isTable[$level] = true;
                            break;
                        case 'FROM':
                        case 'UPDATE':
                        case 'TABLE':
                        case 'TRUNCATE':
                            $isFrom[$level] = true;
                            $isTable[$level] = true;
                            break;
                        case 'EXISTS':
                            if ($isIf[$level])
                            {
                                $isFrom[$level] = true;
                                $isTable[$level] = true;
                            }
                            break;
 
                        case 'WHERE':
                        case 'GROUP':
                        case 'HAVING':
                        case 'ORDER':
                        case 'LIMIT':
                        case 'SET':
                            $isFrom[$level] = false;
                            break;
                        case ',':
                        case 'JOIN':
                        case 'STRAIGHT_JOIN':
                            if ($isFrom[$level])
                            {
                                $isTable[$level] = true;
                            }
                            break;
                        case 'IF':
                            $isIf[$level] = true;
                            $isTable[$level] = false;
                            break;
                        case 'TEMPORARY':
                            $isTable[$level] = false;
                            break;
                        default:
                            if ($isTable[$level])
                            {
                                $tables[$token] = $token;
                                $isTable[$level] = false;
                            }
                    }
                }
            }
        }
 
        return $tables;
    }

    public function isBigType($type): bool
    {
        return false;
    }
}