helper.php

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

<?php
 
namespace Bitrix\Sale\Location\DB;
 
use Bitrix\Main;
 
final class Helper extends CommonHelper
{
    public static function getSqlForAutoIncrement()
    {
        $dbConnection = Main\HttpApplication::getConnection();
        if ($dbConnection->getType() === 'pgsql')
        {
            return 'generated by default as identity';
        }
 
        return 'auto_increment';
    }
 
    public static function mergeTables($toTable, $fromTable, $fldMap, $fldCondition)
    {
        $dbConnection = Main\HttpApplication::getConnection();
        $dbHelper = $dbConnection->getSqlHelper();
 
        $toTable = $dbHelper->forSql(trim($toTable));
        $fromTable = $dbHelper->forSql(trim($fromTable));
 
        if(!mb_strlen($toTable) || !mb_strlen($fromTable) || !is_array($fldMap) || empty($fldMap) || empty($fldCondition))
            return false;
 
        // update tab1, tab2 set tab1.aa = tab2.bb, tab1.cc = tab2.dd where tab1.ee = tab2.ff
 
        if ($dbConnection->getType() === 'pgsql')
        {
            $toFields = [];
            $fromFields = [];
            $where = [];
            foreach ($fldMap as $toFld => $fromFld)
            {
                $toFields[] = $dbHelper->forSql(trim($toFld));
                $fromFields[] = $dbHelper->forSql(trim($fromFld));
            }
            foreach ($fldCondition as $left => $right)
            {
                $where[] = $toTable.'.'.$dbHelper->forSql(trim($left)).' = '.$fromTable.'.'.$dbHelper->forSql(trim($right));
            }
            $sql = 'update '.$toTable.' set ('.
                implode(', ', $toFields).
                ') = (select '.
                implode(', ', $fromFields).
                ' from '.$fromTable.' where '.implode(' and ', $where).')';
        }
        else
        {
            $sql = 'update '.$toTable.', '.$fromTable.' set ';
 
            $fields = array();
            foreach($fldMap as $toFld => $fromFld)
                $fields[] = $toTable.'.'.$dbHelper->forSql(trim($toFld)).' = '.$fromTable.'.'.$dbHelper->forSql(trim($fromFld));
 
            $sql .= implode(', ', $fields);
 
            $where = array();
            foreach($fldCondition as $left => $right)
                $where[] = $toTable.'.'.$dbHelper->forSql(trim($left)).' = '.$fromTable.'.'.$dbHelper->forSql(trim($right));
 
            $sql .= ' where '.implode(' and ', $where);
        }
 
        $dbConnection->query($sql);
 
        return true;
    }
 
    public static function checkIndexNameExists($indexName, $tableName)
    {
        $dbConnection = Main\HttpApplication::getConnection();
        $dbHelper = $dbConnection->getSqlHelper();
 
        $indexName = trim((string)$indexName);
        $tableName = $dbHelper->forSql(trim((string)$tableName));
 
        if ($indexName === '' || $tableName === '')
        {
            return false;
        }
 
        if ($dbConnection->getType() === 'pgsql')
        {
            $res = $dbConnection->query("SELECT INDEXNAME FROM PG_INDEXES WHERE TABLENAME = '$tableName'");
 
            while($item = $res->fetch())
            {
                if (isset($item['INDEXNAME']) && $item['INDEXNAME'] === $indexName)
                {
                    return true;
                }
            }
        }
        else
        {
            $res = $dbConnection->query("show index from ".$tableName);
 
            while($item = $res->fetch())
            {
                if (isset($item['Key_name']) && $item['Key_name'] === $indexName)
                {
                    return true;
                }
                if (isset($item['KEY_NAME']) && $item['KEY_NAME'] === $indexName)
                {
                    return true;
                }
            }
        }
 
        return false;
    }
 
    public static function dropIndexByName($indexName, $tableName)
    {
        $dbConnection = Main\HttpApplication::getConnection();
        $dbHelper = $dbConnection->getSqlHelper();
 
        $indexName = $dbHelper->forSql(trim($indexName));
        $tableName = $dbHelper->forSql(trim($tableName));
 
        if(!mb_strlen($indexName) || !mb_strlen($tableName))
            return false;
 
        if(!static::checkIndexNameExists($indexName, $tableName))
            return false;
 
        $dbConnection->query("alter table {$tableName} drop index {$indexName}");
 
        return true;
    }
 
    public static function getMaxTransferUnit()
    {
        $dbConnection = Main\HttpApplication::getConnection();
        if ($dbConnection->getType() === 'pgsql')
        {
            return parent::getMaxTransferUnit();
        }
 
        $res = $dbConnection->query("SHOW VARIABLES LIKE 'max_allowed_packet'")->fetch();
        if(!($res['Variable_name'] == 'max_allowed_packet' && $mtu = intval($res['Value'])))
            return 0;
 
        return $mtu;
    }
 
    // this function is used to adjust auto_increment value of a table to a certain position
    public static function resetAutoIncrement($tableName, $startIndex = 1)
    {
        $startIndex = intval($startIndex);
        if($startIndex <= 0 || !mb_strlen($tableName))
            return false;
 
        $dbConnection = Main\HttpApplication::getConnection();
        $dbHelper = $dbConnection->getSqlHelper();
 
        $tableName = $dbHelper->forSql(trim($tableName));
 
        if ($dbConnection->getType() === 'pgsql')
        {
            $dbConnection->query("alter sequence {$tableName}_id_seq RESTART WITH $startIndex");
        }
        else
        {
            $dbConnection->query('alter table '.$tableName.' AUTO_INCREMENT = '.$startIndex);
        }
 
        return true;
    }
 
    public static function getQuerySeparatorSql()
    {
        return ";";
    }
}