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);
 
    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);
 
        // 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]))
            {
                $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)
    {
        return intval($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(string $field, string $value): string
    {
        $field = $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 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;
    }
}