public static function checkIndexesValid()
{
if (!Finder::checkIndexValid()) {
static::showSearchNotification();
}
$cnt = static::getCounter();
if ($cnt > static::HITS_BETWEEN_RECHECKS || !static::checkIndexValid()) {
$allOk = true;
$map = ImportProcess::getIndexMap();
if (is_array($map)) {
foreach ($map as $ixName => $ixInfo) {
if (!$ixInfo['DROP_ONLY'] && !DB\Helper::checkIndexNameExists($ixName, $ixInfo['TABLE'])) {
$allOk = false;
break;
}
}
} else {
$allOk = false;
}
if ($allOk) {
static::setIndexValid();
} else {
static::setIndexInvalid();
}
static::setCounter(0);
} else {
static::setCounter($cnt + 1);
}
if (!static::checkIndexValid()) {
static::showDBIndexNotification();
}
}
public static function updateDBSchemaRestoreLegacyIndexes()
{
$dbConnection = \Bitrix\Main\HttpApplication::getConnection();
if (!Helper::checkIndexNameExists('IXS_LOCATION_COUNTRY_ID', 'b_sale_location')) {
$dbConnection->query('create index IXS_LOCATION_COUNTRY_ID on b_sale_location (COUNTRY_ID)');
return "\\Bitrix\\Sale\\Location\\Migration\\CUpdaterLocationPro::updateDBSchemaRestoreLegacyIndexes();";
}
if (!Helper::checkIndexNameExists('IXS_LOCATION_REGION_ID', 'b_sale_location')) {
$dbConnection->query('create index IXS_LOCATION_REGION_ID on b_sale_location (REGION_ID)');
return "\\Bitrix\\Sale\\Location\\Migration\\CUpdaterLocationPro::updateDBSchemaRestoreLegacyIndexes();";
}
if (!Helper::checkIndexNameExists('IXS_LOCATION_CITY_ID', 'b_sale_location')) {
$dbConnection->query('create index IXS_LOCATION_CITY_ID on b_sale_location (CITY_ID)');
return false;
}
}
public static function createIndex()
{
Helper::createIndex(static::getTableName(), 'S', array('SITE_ID'));
}
protected static function addPropertyValueField($tableAlias = 'V', &$arFields, &$arSelectFields)
{
$tableAlias = \Bitrix\Main\HttpApplication::getConnection()->getSqlHelper()->forSql($tableAlias);
// locations kept in CODEs, but must be shown as IDs
if (CSaleLocation::isLocationProMigrated()) {
$arSelectFields = array_merge(array('PROP_TYPE'), $arSelectFields);
// P.TYPE should be there and go above our join
$arFields['VALUE'] = array("FIELD" => "\n\t\t\t\tCASE\n\n\t\t\t\t\tWHEN\n\t\t\t\t\t\tP.TYPE = 'LOCATION'\n\t\t\t\t\tTHEN\n\t\t\t\t\t\tCAST(L.ID as " . \Bitrix\Sale\Location\DB\Helper::getSqlForDataType('char', 255) . ")\n\n\t\t\t\t\tELSE\n\t\t\t\t\t\t" . $tableAlias . ".VALUE\n\t\t\t\tEND\n\t\t\t", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_location L ON (P.TYPE = 'LOCATION' AND " . $tableAlias . ".VALUE IS NOT NULL AND " . $tableAlias . ".VALUE = L.CODE)");
$arFields['VALUE_ORIG'] = array("FIELD" => $tableAlias . ".VALUE", "TYPE" => "string");
} else {
$arFields['VALUE'] = array("FIELD" => $tableAlias . ".VALUE", "TYPE" => "string");
}
}
public static function resetLegacyPath()
{
$dbConnection = Main\HttpApplication::getConnection();
$locTable = static::getTableName();
$types = array();
$res = TypeTable::getList(array('filter' => array('CODE' => array('COUNTRY', 'REGION', 'CITY')), 'select' => array('ID', 'CODE')));
while ($item = $res->fetch()) {
$types[$item['CODE']] = $item['ID'];
}
if (!empty($types)) {
if (!$dbConnection->isTableExists('b_sale_loc_rebind')) {
$dbConnection->query("create table b_sale_loc_rebind (TARGET_ID " . Helper::getSqlForDataType('int') . ", LOCATION_ID " . Helper::getSqlForDataType('int') . ")");
} else {
$dbConnection->query("truncate table b_sale_loc_rebind");
}
$sqlWhere = array();
foreach ($types as $code => $id) {
$sqlWhere[] = "'" . intval($id) . "'";
}
$dbConnection->query("update " . $locTable . " set COUNTRY_ID = NULL, REGION_ID = NULL, CITY_ID = NULL where TYPE_ID in (" . implode(', ', $sqlWhere) . ")");
if (intval($types['REGION']) && intval($types['COUNTRY'])) {
// countries for regions
$dbConnection->query("insert into b_sale_loc_rebind (TARGET_ID, LOCATION_ID) select A.ID as ONE, B.ID as TWO from " . $locTable . " A inner join " . $locTable . " B on A.TYPE_ID = '" . intval($types['REGION']) . "' and B.TYPE_ID = '" . intval($types['COUNTRY']) . "' and B.LEFT_MARGIN <= A.LEFT_MARGIN and B.RIGHT_MARGIN >= A.RIGHT_MARGIN");
Helper::mergeTables($locTable, 'b_sale_loc_rebind', array('COUNTRY_ID' => 'LOCATION_ID'), array('ID' => 'TARGET_ID'));
$dbConnection->query("truncate table b_sale_loc_rebind");
}
if (intval($types['REGION']) && intval($types['CITY'])) {
// regions for cities
$dbConnection->query("insert into b_sale_loc_rebind (TARGET_ID, LOCATION_ID) select A.ID as ONE, B.ID as TWO from " . $locTable . " A inner join " . $locTable . " B on A.TYPE_ID = '" . intval($types['CITY']) . "' and B.TYPE_ID = '" . intval($types['REGION']) . "' and B.LEFT_MARGIN <= A.LEFT_MARGIN and B.RIGHT_MARGIN >= A.RIGHT_MARGIN");
Helper::mergeTables($locTable, 'b_sale_loc_rebind', array('REGION_ID' => 'LOCATION_ID'), array('ID' => 'TARGET_ID'));
$dbConnection->query("truncate table b_sale_loc_rebind");
}
if (intval($types['COUNTRY']) && intval($types['CITY'])) {
// countries for cities
$dbConnection->query("insert into b_sale_loc_rebind (TARGET_ID, LOCATION_ID) select A.ID as ONE, B.ID as TWO from " . $locTable . " A inner join " . $locTable . " B on A.TYPE_ID = '" . intval($types['CITY']) . "' and B.TYPE_ID = '" . intval($types['COUNTRY']) . "' and B.LEFT_MARGIN <= A.LEFT_MARGIN and B.RIGHT_MARGIN >= A.RIGHT_MARGIN");
Helper::mergeTables($locTable, 'b_sale_loc_rebind', array('COUNTRY_ID' => 'LOCATION_ID'), array('ID' => 'TARGET_ID'));
}
Helper::dropTable('b_sale_loc_rebind');
if (intval($types['COUNTRY'])) {
$dbConnection->query("update " . $locTable . " set COUNTRY_ID = ID where TYPE_ID = '" . intval($types['COUNTRY']) . "'");
}
if (intval($types['REGION'])) {
$dbConnection->query("update " . $locTable . " set REGION_ID = ID where TYPE_ID = '" . intval($types['REGION']) . "'");
}
if (intval($types['CITY'])) {
$dbConnection->query("update " . $locTable . " set CITY_ID = ID where TYPE_ID = '" . intval($types['CITY']) . "'");
}
}
}
public static function resort($dontCareEvents = false)
{
$edges = array();
$nodes = array();
$res = parent::getList(array('select' => array('ID', 'PARENT_ID', 'LEFT_MARGIN', 'RIGHT_MARGIN')));
while ($item = $res->Fetch()) {
$nodes[$item['ID']] = array('LEFT_MARGIN' => $item['LEFT_MARGIN'], 'RIGHT_MARGIN' => $item['RIGHT_MARGIN']);
if (!intval($item['PARENT_ID'])) {
$edges['ROOT'][] = $item['ID'];
} else {
$edges[$item['PARENT_ID']][] = $item['ID'];
}
}
// walk tree in-deep to obtain correct margins
self::walkTreeInDeep('ROOT', $edges, $nodes, 0, 0, $dontCareEvents);
// now massively insert new values into the database, using a temporal table
$tabName = 'b_sale_location_temp_' . rand(99, 9999);
$entityTableName = static::getTableName();
$dbConnection = Main\HttpApplication::getConnection();
$dbConnection->query("create table " . $tabName . " (\n\t\t\tID " . Helper::getSqlForDataType('int') . ",\n\t\t\tLEFT_MARGIN " . Helper::getSqlForDataType('int') . ",\n\t\t\tRIGHT_MARGIN " . Helper::getSqlForDataType('int') . ",\n\t\t\tDEPTH_LEVEL " . Helper::getSqlForDataType('int') . "\n\t\t)");
$handle = new BlockInserter(array('tableName' => $tabName, 'exactFields' => array('ID' => array('data_type' => 'integer'), 'LEFT_MARGIN' => array('data_type' => 'integer'), 'RIGHT_MARGIN' => array('data_type' => 'integer'), 'DEPTH_LEVEL' => array('data_type' => 'integer')), 'parameters' => array('mtu' => self::BLOCK_INSERT_MTU)));
foreach ($nodes as $id => $node) {
$node['ID'] = $id;
$handle->insert($node);
}
$handle->flush();
// merge temp table with location table
Helper::mergeTables($entityTableName, $tabName, array('LEFT_MARGIN' => 'LEFT_MARGIN', 'RIGHT_MARGIN' => 'RIGHT_MARGIN', 'DEPTH_LEVEL' => 'DEPTH_LEVEL'), array('ID' => 'ID'));
$dbConnection->query("drop table {$tabName}");
}
define("NO_AGENT_CHECK", true);
define("NO_KEEP_STATISTIC", true);
$initialTime = time();
require_once $_SERVER["DOCUMENT_ROOT"] . "/bitrix/modules/main/include/prolog_admin_before.php";
require_once $_SERVER["DOCUMENT_ROOT"] . "/bitrix/modules/sale/prolog.php";
Loc::loadMessages(__FILE__);
$APPLICATION->SetTitle(Loc::getMessage('SALE_LOCATION_IMPORT_TITLE'));
require $_SERVER["DOCUMENT_ROOT"] . "/bitrix/modules/main/include/prolog_admin_after.php";
// check for indexes
$indexes = \Bitrix\Sale\Location\Import\ImportProcess::getIndexMap();
$absent = array();
if (is_array($indexes) && !empty($indexes)) {
foreach ($indexes as $name => $params) {
if ((string) $params['TABLE'] != '' && !$params['DROP_ONLY']) {
if (!\Bitrix\Sale\Location\DB\Helper::checkIndexNameExists($name, $params['TABLE'])) {
$absent[] = 'create index ' . $name . ' on ' . $params['TABLE'] . ' (' . implode(', ', $params['COLUMNS']) . ')' . \Bitrix\Sale\Location\DB\Helper::getQuerySeparatorSql();
}
}
}
}
if (!empty($absent)) {
?>
<span style="color: #ff0000">
<?php
echo Loc::getMessage('SALE_LOCATION_IMPORT_NO_INDEXES_WARNING', array('#ANCHOR_SQL_CONSOLE#' => '<a href="/bitrix/admin/sql.php" target="_blank">', '#ANCHOR_END#' => '</a>'));
?>
</span>
<br />
<br />
public static function createIndex()
{
Helper::createIndex(static::getTableName(), 'LPR', array('LOCATION_ID', 'POSITION', 'RELEVANCY'));
}
public static function rollBack()
{
if (Helper::checkTableExists(self::TABLE_LEGACY_RELATIONS)) {
Helper::mergeTables('b_sale_location', self::TABLE_LEGACY_RELATIONS, array('COUNTRY_ID' => 'COUNTRY_ID', 'REGION_ID' => 'REGION_ID', 'CITY_ID' => 'CITY_ID'), array('ID' => 'ID'));
}
Helper::truncateTable(self::TABLE_LOCATION_NAME);
Helper::truncateTable(self::TABLE_LOCATION_EXTERNAL);
\CSaleLocation::locationProSetRolledBack();
}
public static function mergeResort()
{
Helper::mergeTables(static::getTableName(), static::getTableNamePositionTemporal(), array('POSITION' => 'POSITION'), array('ID' => 'WORD_ID'));
Main\HttpApplication::getConnection()->query("drop table " . static::getTableNamePositionTemporal());
}
protected static function findUsingIndex($parameters)
{
$query = array();
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = Main\HttpApplication::getConnection()->getSqlHelper();
$filter = static::parseFilter($parameters['filter']);
$filterByPhrase = isset($filter['PHRASE']) && strlen($filter['PHRASE']['VALUE']);
if ($filterByPhrase) {
$bounds = WordTable::getBoundsForPhrase($filter['PHRASE']['VALUE']);
$firstBound = array_shift($bounds);
$k = 0;
foreach ($bounds as $bound) {
$query['JOIN'][] = " inner join " . ChainTable::getTableName() . " A" . $k . " on A.LOCATION_ID = A" . $k . ".LOCATION_ID and (\n\n\t\t\t\t\t" . ($bound['INF'] == $bound['SUP'] ? " A" . $k . ".POSITION = '" . $bound['INF'] . "'" : " A" . $k . ".POSITION >= '" . $bound['INF'] . "' and A" . $k . ".POSITION <= '" . $bound['SUP'] . "'") . "\n\t\t\t\t)";
$k++;
}
$query['WHERE'][] = $firstBound['INF'] == $firstBound['SUP'] ? " A.POSITION = '" . $firstBound['INF'] . "'" : " A.POSITION >= '" . $firstBound['INF'] . "' and A.POSITION <= '" . $firstBound['SUP'] . "'";
$mainTableJoinCondition = 'A.LOCATION_ID';
} else {
$mainTableJoinCondition = 'L.ID';
}
// site link search
if (strlen($filter['SITE_ID']['VALUE']) && SiteLinkTable::checkTableExists()) {
$query['JOIN'][] = "inner join " . SiteLinkTable::getTableName() . " SL on SL.LOCATION_ID = " . $mainTableJoinCondition . " and SL.SITE_ID = '" . $dbHelper->forSql($filter['SITE_ID']['VALUE']) . "'";
}
// process filter and select statements
// at least, we support here basic field selection and filtration + NAME.NAME and NAME.LANGUAGE_ID
$map = Location\LocationTable::getMap();
$nameRequired = false;
$locationRequred = false;
if (is_array($parameters['select'])) {
foreach ($parameters['select'] as $alias => $field) {
if ($field == 'NAME.NAME' || $field == 'NAME.LANGUAGE_ID') {
$nameRequired = true;
continue;
}
if (!isset($map[$field]) || !in_array($map[$field]['data_type'], array('integer', 'string', 'float', 'boolean')) || isset($map[$field]['expression'])) {
unset($parameters['select'][$alias]);
}
$locationRequred = true;
}
}
foreach ($filter as $field => $params) {
if ($field == 'NAME.NAME' || $field == 'NAME.LANGUAGE_ID') {
$nameRequired = true;
continue;
}
if (!isset($map[$field]) || !in_array($map[$field]['data_type'], array('integer', 'string', 'float', 'boolean')) || isset($map[$field]['expression'])) {
unset($filter[$field]);
}
$locationRequred = true;
}
// data join, only if extended select specified
if ($locationRequred && $filterByPhrase) {
$query['JOIN'][] = "inner join " . Location\LocationTable::getTableName() . " L on A.LOCATION_ID = L.ID";
}
if ($nameRequired) {
$query['JOIN'][] = "inner join " . Location\Name\LocationTable::getTableName() . " NAME on NAME.LOCATION_ID = " . $mainTableJoinCondition;
}
// and N.LANGUAGE_ID = 'ru'
// making select
if (is_array($parameters['select'])) {
$select = array();
foreach ($parameters['select'] as $alias => $field) {
if ($field != 'NAME.NAME' && $field != 'NAME.LANGUAGE_ID') {
$field = 'L.' . $dbHelper->forSql($field);
}
if ((string) $alias === (string) intval($alias)) {
$select[] = $field;
} else {
$select[] = $field . ' as ' . $dbHelper->forSql($alias);
}
}
$sqlSelect = implode(', ', $select);
} else {
$sqlSelect = $mainTableJoinCondition . ' as ID';
}
// making filter
foreach ($filter as $field => $params) {
if ($field != 'NAME.NAME' && $field != 'NAME.LANGUAGE_ID') {
$field = 'L.' . $dbHelper->forSql($field);
}
$query['WHERE'][] = $field . ' ' . $params['OP'] . " '" . $dbHelper->forSql($params['VALUE']) . "'";
}
if ($filterByPhrase) {
$sql = "\n\t\t\t\tselect " . ($dbConnection->getType() == 'oracle' ? '' : 'distinct') . " \n\t\t\t\t\t" . $sqlSelect . (\Bitrix\Sale\Location\DB\Helper::needSelectFieldsInOrderByWhenDistinct() ? ', A.RELEVANCY' : '') . "\n\n\t\t\t\tfrom " . ChainTable::getTableName() . " A\n\n\t\t\t\t\t" . implode(' ', $query['JOIN']) . "\n\n\t\t\t\t" . (count($query['WHERE']) ? 'where ' : '') . implode(' and ', $query['WHERE']) . "\n\n\t\t\t\torder by A.RELEVANCY asc\n\t\t\t";
} else {
$sql = "\n\n\t\t\t\tselect \n\t\t\t\t\t" . $sqlSelect . "\n\n\t\t\t\tfrom " . Location\LocationTable::getTableName() . " L\n\n\t\t\t\t\t" . implode(' ', $query['JOIN']) . "\n\n\t\t\t\t" . (count($query['WHERE']) ? 'where ' : '') . implode(' and ', $query['WHERE']) . "\n\t\t\t";
}
$offset = intval($parameters['offset']);
$limit = intval($parameters['limit']);
if ($limit) {
$sql = $dbHelper->getTopSql($sql, $limit, $offset);
}
$res = $dbConnection->query($sql);
return $res;
}
Loc::loadMessages(__FILE__);
$APPLICATION->SetTitle(Loc::getMessage('SALE_LOCATION_IMPORT_TITLE'));
require($_SERVER["DOCUMENT_ROOT"]."/bitrix/modules/main/include/prolog_admin_after.php");
// check for indexes
$indexes = \Bitrix\Sale\Location\Import\ImportProcess::getIndexMap();
$absent = array();
if(is_array($indexes) && !empty($indexes))
{
foreach($indexes as $name => $params)
{
if((string) $params['TABLE'] != '')
{
if(!\Bitrix\Sale\Location\DB\Helper::checkIndexNameExists($name, $params['TABLE']))
$absent[] = 'create index '.$name.' on '.$params['TABLE'].' ('.implode(', ', $params['COLUMNS']).');';
}
}
}
if(!empty($absent))
{
print('<span style="color: #ff0000">'.Loc::getMessage('SALE_LOCATION_IMPORT_NO_INDEXES_WARNING', array(
'#ANCHOR_SQL_CONSOLE#' => '<a href="/bitrix/admin/sql.php" target="_blank">',
'#ANCHOR_END#' => '</a>'
)).'</span><br /><br />');
foreach($absent as $sql)
{
print($sql.'<br />');
public function insert($row)
{
if (!is_array($row) || empty($row)) {
return;
}
$this->index++;
$this->bufferSize++;
if ($this->autoIncFld !== false) {
$row[$this->autoIncFld] = $this->index;
Helper::incrementSequenceForTable($this->tableName);
// if this is oracle and we insert auto increment key directly, we must provide sequence increment manually
}
$sql = Helper::getBatchInsertValues($row, $this->tableName, $this->fldVector, $this->map);
/*
MySQL & MsSQL: insert into b_test (F1,F2) values ('one','two'),('one1','two1'),('one2','two2')
Oracle: insert all into b_test (F1,F2) values ('one','two') into b_test (F1,F2) values ('one1','two1') into b_test (F1,F2) values ('one2','two2') select * from dual
*/
$nextBuffer = (empty($this->buffer) ? $this->insertHead : $this->buffer . Helper::getBatchInsertSeparator()) . $sql;
// here check length
if (defined(SITE_CHARSET) && SITE_CHARSET == 'UTF-8') {
$len = mb_strlen($nextBuffer);
} else {
$len = strlen($nextBuffer);
}
if ($this->mtu - (strlen($nextBuffer) + 100) < self::RED_LINE) {
$this->flush();
// flushing the previous buffer (now $this->buffer == '')
$this->buffer = $this->insertHead . $sql;
} else {
$this->buffer = $nextBuffer;
}
return $this->index;
}
public static function resetAutoIncrement($tableName, $startIndex = 1)
{
$startIndex = intval($startIndex);
if ($startIndex <= 0 || !strlen($tableName)) {
return false;
}
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = $dbConnection->getSqlHelper();
$tableName = $dbHelper->forSql(trim($tableName));
if (strlen($tableName) > 23) {
// too long
return false;
}
if ($sqName = Helper::checkSequenceExistsForTable($tableName)) {
$dbConnection->query('drop sequence ' . $sqName);
} else {
$sqName = static::getSequenceNameForTable($tableName);
}
$dbConnection->query('create sequence ' . $sqName . ' start with ' . $startIndex . ' increment by 1 NOMAXVALUE NOCYCLE NOCACHE NOORDER');
$dbConnection->query("\n\t\t\tCREATE OR REPLACE TRIGGER " . $tableName . "_INSERT\n\t\t\tBEFORE INSERT\n\t\t\tON " . $tableName . "\n\t\t\tFOR EACH ROW\n\t\t\tBEGIN\n\t\t\t\tIF :NEW.ID IS NULL THEN\n\t\t\t \t\tSELECT " . $sqName . ".NEXTVAL INTO :NEW.ID FROM dual;\n\t\t\t\tEND IF;\n\t\t\tEND;\n\t\t");
return true;
}
public static function addPropertyDefaultValueField($tableAlias = 'V', &$arFields)
{
$tableAlias = \Bitrix\Main\HttpApplication::getConnection()->getSqlHelper()->forSql($tableAlias);
// locations kept in CODEs, but must be shown as IDs
if (CSaleLocation::isLocationProMigrated()) {
$arFields['DEFAULT_VALUE'] = array("FIELD" => "\n\n\t\t\t\tCASE \n\n\t\t\t\t\tWHEN \n\t\t\t\t\t\tTYPE = 'LOCATION'\n\t\t\t\t\tTHEN \n\t\t\t\t\t\tCAST(L.ID as " . \Bitrix\Sale\Location\DB\Helper::getSqlForDataType('char', 255) . ")\n\n\t\t\t\t\tELSE \n\t\t\t\t\t\t" . $tableAlias . ".DEFAULT_VALUE \n\t\t\t\tEND", "TYPE" => "string", "FROM" => "LEFT JOIN b_sale_location L ON (TYPE = 'LOCATION' AND " . $tableAlias . ".DEFAULT_VALUE IS NOT NULL AND " . $tableAlias . ".DEFAULT_VALUE = L.CODE)");
$arFields['DEFAULT_VALUE_ORIG'] = array("FIELD" => $tableAlias . ".DEFAULT_VALUE", "TYPE" => "string");
} else {
$arFields['DEFAULT_VALUE'] = array("FIELD" => $tableAlias . ".DEFAULT_VALUE", "TYPE" => "string");
}
}