mysqlcommonsqlhelper.php

<?php
namespace Bitrix\Main\DB;
 
use Bitrix\Main;
use Bitrix\Main\Type;
use Bitrix\Main\ORM;
use Bitrix\Main\ORM\Fields\ScalarField;
 
abstract class MysqlCommonSqlHelper extends SqlHelper
{
    public function getLeftQuote()
    {
        return '`';
    }
 
    public function getRightQuote()
    {
        return '`';
    }
 
    public function getAliasLength()
    {
        return 256;
    }
 
    public function getQueryDelimiter()
    {
        return ';';
    }
 
    public function getCurrentDateTimeFunction()
    {
        return "NOW()";
    }
 
    public function getCurrentDateFunction()
    {
        return "CURDATE()";
    }
 
    public function addSecondsToDateTime($seconds, $from = null)
    {
        if ($from === null)
        {
            $from = static::getCurrentDateTimeFunction();
        }
 
        return 'DATE_ADD('.$from.', INTERVAL '.$seconds.' SECOND)';
    }
 
    public function getDatetimeToDateFunction($value)
    {
        return 'DATE('.$value.')';
    }
 
    public function formatDate($format, $field = null)
    {
        static $search  = array(
            "YYYY",
            "MMMM",
            "MM",
            "MI",
            "DD",
            "HH",
            "GG",
            "G",
            "SS",
            "TT",
            "T"
        );
        static $replace = array(
            "%Y",
            "%M",
            "%m",
            "%i",
            "%d",
            "%H",
            "%h",
            "%l",
            "%s",
            "%p",
            "%p"
        );
 
        $format = str_replace($search, $replace, $format);
 
        if (mb_strpos($format, '%H') === false)
        {
            $format = str_replace("H", "%h", $format);
        }
 
        if (mb_strpos($format, '%M') === false)
        {
            $format = str_replace("M", "%b", $format);
        }
 
        if($field === null)
        {
            return $format;
        }
        else
        {
            return "DATE_FORMAT(".$field.", '".$format."')";
        }
    }
 
    public function getConcatFunction()
    {
        $str = "";
        $ar = func_get_args();
        if (is_array($ar))
            $str .= implode(", ", $ar);
        if ($str <> '')
            $str = "CONCAT(".$str.")";
        return $str;
    }
 
    public function getIsNullFunction($expression, $result)
    {
        return "IFNULL(".$expression.", ".$result.")";
    }
 
    public function getLengthFunction($field)
    {
        return "LENGTH(".$field.")";
    }
 
    public function getCharToDateFunction($value)
    {
        return "'".$value."'";
    }
 
    public function getDateToCharFunction($fieldName)
    {
        return $fieldName;
    }
 
    public function getConverter(ScalarField $field)
    {
        if($field instanceof ORM\Fields\DatetimeField)
        {
            return array($this, "convertFromDbDateTime");
        }
        elseif($field instanceof ORM\Fields\DateField)
        {
            return array($this, "convertFromDbDate");
        }
        else
        {
            return parent::getConverter($field);
        }
    }
 
    public function convertDatetimeField($value)
    {
        return $this->convertFromDbDateTime($value);
    }
 
    public function convertFromDbDateTime($value)
    {
        if($value !== null && $value != '0000-00-00 00:00:00')
        {
            return new Type\DateTime($value, "Y-m-d H:i:s");
        }
 
        return null;
    }
 
    public function convertDateField($value)
    {
        return $this->convertFromDbDate($value);
    }
 
    public function convertFromDbDate($value)
    {
        if($value !== null && $value != '0000-00-00')
        {
            return new Type\Date($value, "Y-m-d");
        }
 
        return null;
    }
 
    public function castToChar($fieldName)
    {
        return 'CAST('.$fieldName.' AS char)';
    }
 
    public function softCastTextToChar($fieldName)
    {
        return $fieldName;
    }
 
    public function getColumnTypeByField(ScalarField $field)
    {
        if ($field instanceof ORM\Fields\IntegerField)
        {
            return 'int';
        }
        elseif ($field instanceof ORM\Fields\DecimalField)
        {
            $defaultPrecision = 18;
            $defaultScale = 2;
 
            $precision = $field->getPrecision() > 0 ? $field->getPrecision() : $defaultPrecision;
            $scale = $field->getScale() > 0 ? $field->getScale() : $defaultScale;
 
            if ($scale >= $precision)
            {
                $precision = $defaultPrecision;
                $scale = $defaultScale;
            }
 
            return "decimal($precision, $scale)";
        }
        elseif ($field instanceof ORM\Fields\FloatField)
        {
            return 'double';
        }
        elseif ($field instanceof ORM\Fields\DatetimeField)
        {
            return 'datetime';
        }
        elseif ($field instanceof ORM\Fields\DateField)
        {
            return 'date';
        }
        elseif ($field instanceof ORM\Fields\TextField)
        {
            return 'text';
        }
        elseif ($field instanceof ORM\Fields\BooleanField)
        {
            $values = $field->getValues();
 
            if (preg_match('/^[0-9]+$/', $values[0]) && preg_match('/^[0-9]+$/', $values[1]))
            {
                return 'int';
            }
            else
            {
                return 'varchar('.max(mb_strlen($values[0]), mb_strlen($values[1])).')';
            }
        }
        elseif ($field instanceof ORM\Fields\EnumField)
        {
            return 'varchar('.max(array_map('strlen', $field->getValues())).')';
        }
        else
        {
            // string by default
            $defaultLength = false;
            foreach ($field->getValidators() as $validator)
            {
                if ($validator instanceof ORM\Fields\Validators\LengthValidator)
                {
                    if ($defaultLength === false || $defaultLength > $validator->getMax())
                    {
                        $defaultLength = $validator->getMax();
                    }
                }
            }
            return 'varchar('.($defaultLength > 0? $defaultLength: 255).')';
        }
    }
 
    public function getTopSql($sql, $limit, $offset = 0)
    {
        $offset = intval($offset);
        $limit = intval($limit);
 
        if ($offset > 0 && $limit <= 0)
            throw new \Bitrix\Main\ArgumentException("Limit must be set if offset is set");
 
        if ($limit > 0)
        {
            $sql .= "\nLIMIT ".$offset.", ".$limit."\n";
        }
 
        return $sql;
    }
 
    public function prepareMerge($tableName, array $primaryFields, array $insertFields, array $updateFields)
    {
        $insert = $this->prepareInsert($tableName, $insertFields);
        $update = $this->prepareUpdate($tableName, $updateFields);
 
        if (
            $insert && $insert[0] != "" && $insert[1] != ""
            && $update && $update[1] != ""
        )
        {
            $sql = "
                INSERT INTO ".$this->quote($tableName)." (".$insert[0].")
                VALUES (".$insert[1].")
                ON DUPLICATE KEY UPDATE ".$update[0]."
            ";
        }
        else
        {
            $sql = "";
        }
 
        return array(
            $sql
        );
    }
 
    public function getConditionalAssignment(string $field, string $value): string
    {
        $field = $this->quote($field);
        $value = $this->convertToDbString($value);
        $hash = $this->convertToDbString(sha1($value));
 
        return "IF(SHA1({$field}) = {$hash}, {$field}, {$value})";
    }
}