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"); } }