Профили SQL

Назначение

Система настройки и формирования файлов с SQL запросами, для обновления данных в удаленной системе. Основная идея в подготовке, на стороне учетной системы, готовых запросов SQL для обновления данных во внешней системе системе (интернет-магазине).

Процесс выгрузки данных

Основная идея в подготовке, на стороне учетной системы, готовых запросов SQL для обновления данных во внешней системе системе (интернет-магазине).

1. По настроенному шаблону структуры данных внешней системы и настроенным источникам данных формируются файлы с SQL запросами и каталог дополнительных файлов (картинки и пр.);
2. В процессе формирования файлов с запросами регистрируются коды данных внешних таблиц и связываются с локальными данными, для последующей выгрузки обновлений;
3. Архив с подготовленными файлами (текстовые файлы с SQL запросами и дополнительные файлы) копируется на сервер внешней системы через FTP;
4. Запускается процесс (Bash, Python, PHP Cli и т.п.) обработки выгруженных файлов на внешнем сервере по SSH;
5. В случае успешной отправки файлов во внешнюю систему, в локальной системе удаляются подготовленные файлы.

Для чего нужен промежуточный этап записи запросов в файлы, почему бы сразу не отправлять запросы SQL серверу внешней системы?

Данный метод используется в первую очередь, для выгрузки данных в интернет-магазины, которые как правило не работают в одной сети с учетной системой, а расположены на удаленном сервере, где закрыт доступ к SQL серверу, кроме локальных запросов из соображений безопасности. Но возможность выгрузить файлы по FTP и затем выполнить через консоль SSH команду для выполнения запросов из выгруженных файлов есть всегда.

Структура запросов

В большинстве случаев запросы заполняют сразу рабочие таблицы внешней системы.

В исключительных случаях, запросы заполняют предварительно созданную временную таблицу, из которой отдельным запросом обновляются данные в рабочих таблицах, что дает гибкость:

• Отказаться от обновления части, либо всех данных, в зависимости от прописанных в запросе условий;
• Добавить в запрос данные из базы внешней системы, для фильтров, либо как вспомогательные для обновления, создания данных.

Для этих целей структура выгруженных SQL запросов делится на части:

1. Перед запросами создания обновления данных. Выполняется глобальная очистка данных, создаются временные таблицы и тп.;
2. Процесс создания или обновления данных. Заполняют рабочие таблицы, либо предварительно созданные временные;
3. После создания данных.  Заполнение рабочих таблиц из временной, обработка данных (например рассчитать количество записей в группах) другие операции с базой данных.

Преимущества

Преимущества метода в сравнении с выгрузкой через XML файлы и API службы.

Скорость выгрузки

Максимально быстрый вариант, быстрее ничего не придумать. Достигается благодаря ряду причин:

1. Для формирования запросов достаточно выбора данных в виде простых таблиц, и относительно простыми и быстрыми запросами. В отличии от формирования XML файлов, либо объектов для API, где данные представляют собой дерево, иногда довольно сложное и требующее вложенных запросов;
2. Меньше избыточных данных, как теги в XML файлах, иногда довольно длинные, по требованию внешней системы;
3. Нет промежуточных операций преобразования данных, внешняя система получает готовые SQL запросы. При выгрузке через XML файлы, сначала формируется файл, затем файл читается и в итоге приходим к тем же запросам;
4. Возможность настройки наиболее подходящего модуля выбора для каждого вида данных, что позволяет для универсальности разработать несколько отдельных, более простых и быстрых вариантов выбора данных, вместо одного тяжелого, но учитывающего всею нюансы выбора данных;
5. Табличная структура данных на входе и выходе позволяет записывать данные в файл с запросами SQL для каждой строки. В оперативной памяти не формируются временные объекты данных, для последующей записи. Это быстрее и экономит ресурсы.

Как сделать выгрузку максимально тяжелой и медленной?

Взять за тело выгрузки XML файл с не очень удобной структурой и названиями тегов в пол строки, что бы дольше формировать и  читать файл, и было больше избыточных данных.

Сделать формирование файла на стороне 1С не прямой записью в файл, по мере формирования, а через объекты в памяти, что бы загрузить систему по максиму, и что бы падала при выгрузке больших объемов.

Затем на стороне сайта, сделать чтение файла так, что каждая строка не будет писать данные в базу сразу, а вызывать на каждой строке тяжелые встроенные функции записи данных.

Сделать более медленно нельзя даже намеренно. Примерно так это и реализовано в одной очень раскрученной CMS.

Универсальность

На стороне внешней системы не требуется никаких доработок ни в коде, ни в базе данных, так как система получает готовые запросы для создания и обновления данных.

Ограничения

Основные ограничения выгрузки данных в базу внешней системы через файлы с запросами SQL.

Несовместимая структура базы данных

Не любая структура базы данных внешней системы пригодна для данного способа выгрузки.

Основное ограничение! Во внешней системе (CMS, интернет-магазине) есть общие таблицы, в которых хранится несколько видов данных, такие системы не подходят!

Например в одной таблице хранятся названия товаров, названия групп и названия других материалов сайта.  Такой вариант не подходит для использования выгрузки данным методом!

Большие различия в логике хранения одного вида данных выгружаемых и принимаемых, никак не конвертировать в процессе выгрузки.

Совместное редактирование

Нельзя данные, хранящиеся в одной таблице, параллельно выгружать и создавать, непосредственно во внешней системе без выгрузки. Либо только выгружаем, либо только создаем во внешней системе.

Например, товары нельзя одновременно и выгружать из учетной системы и создавать в интернет магазине.

Ограничение касается именно записи данных в конкретную таблицу.

Например, выгружаем товары в интернет-магазин.

У товаров есть комментарии пользователей или другие связанные данные, которые хранятся в отдельных таблицах.

В такой ситуации товары добавлять нельзя, но комментарии и прочие связанные данные, добавлять и редактировать на стороне интернет-магазина без проблем.

Комментарии и прочие данные в выгрузке не участвуют, хранятся в отдельных таблицах, никак не пересекаются с выгружаемыми данными.

Во время выгрузки нет обратной связи с внешней системой. Поэтому первичные идентификаторы данных, создаваемых во внешней системе, создаются и записываются в момент выгрузки в локальной системе.

Идентификаторы необходимы для связи внешних и локальных данных, используются при повторной выгрузке и обновлении данных во внешней системе.

Проблему возможно решить, если создать во внешней системе отдельные таблицы, где хранить связь данных в двух системах, но это потребует доработки внешней системы, добавить таблицы. Так же усложнит запросы выгрузки данных. В готовых решениях данный подход, не используется, но при необходимости возможно реализовать.

В большинстве случаев проблема не критичная, достаточно того, что часть данных только выгружаем, часть только создаем во внешней системе.

Нет контроля выполнения запросов

После выгрузки файлов с запросами на сервер удаленной системы и запуска скрипта распаковки и выполнения запросов, процесс не контролируется. Если на стадии выполнения запросов есть ошибки, в структуре SQL запросов, этого не увидеть.

Данное ограничение не критично:

1. Если была изменена или обновлена структура базы данных удаленной системы (интернет-магазина);
2. На стадии разработки шаблона выгрузки данных для новой внешней системы.

В таких случаях для просмотра ошибок требуется выполнять запуск распаковки и выполнения запросов вручную из консоли, либо выполнять запросы через администратор базы данных внешней системы (PHPMyAdmin и т.п.) что бы отследить ошибки при выполнении сформированных SQL запросов.

Нет контроля данных

В случае полного контроля над системой отправляющей и принимающей данные, проблемы нет.

Например, когда данные выгружаются из учетной системы организации в интернет-магазин той же организации, и больше в этот интернет-магазин данные никто не выгружает.

Данный метод не подойдет, для выгрузки данных в какую то общую систему, куда производят выгрузку несколько независимых друг от друга систем.

Например выгрузка в маркетплейс, где данные выгружаются из различных систем (учетные системы клиентов маркетплейса), которые он не может контролировать.

Запросы к базе приходят в готовом виде и могут выполнить любые действия в том числе нарушить целостность данных.  Поэтому способ не приемлем, если нет полного контроля над системой отправляющей и принимающей данные.

Совместимость

СУБД под формат которых генерируются запросы:

• MySQL;
• MariaDB

Действия

Стандартные действия с профилем SQL.

Настройка

Настройка профиля формирования файлов.

Структура данных

Состоит из:

• Виды данных - список и настройка выгружаемых видов данных (список слева формы);
• Таблицы - список таблиц выбранного вида данных. Таблицы внешней базы данных (список справа, сверху);
• Поля - список полей выбранной таблицы, выбранного вида данных. Поля внешней таблицы внешней базы данных (список справа, снизу);
• Дополнительные запросы - дополнительные SQL запросы.

Виды данных

Таблица с настройками видов выгружаемых данных. Настраиваем свойства источника, какие виды данных и с какими параметрами будут переданы в выгрузку.

Выбран

Вид данных выбран для выгрузки. Отключенная строка не выгружается.

Вид данных

Название вида выгружаемых данных.

Узел обмена

Галочка, указывает на то, что выбранный модуль выгрузки содержит запрос с изменениями данных, по данным Узла обмена.

Выбор данных \ Обработка

Модуль выбора данных. Файлы SQL формируются по заданной структуре, в соответствии с требованиями базы данных внешней системы, но на нашей стороне данные могут быть заменяемыми.

Такой подход, используется для того, что бы отделить структуру получаемых данных, от источника, что дает ряд преимуществ:

1. Гибкость настройки - возможность сменить способ выбора исходных данных, на уровне настройки профиля;
2. Удобство доработки - многие решения подобных задач, представляют общее целое, есть один модуль внутри которого генерируются все виды данных. Задача, изменить выгрузку остатков, например к реальному складу добавить остатки поставщиков, которые мы грузим из прайс-листов. Пришлось бы мало того, что искать где в модулей именно эти данные, еще и после изменения модуль стал бы доработанным и обновлять его уже проблема. При данном подходе у нас все виды данные отдельно. Мы разрабатываем отдельный, небольшой модуль выбора остатков, регистрируем в системе, затем выбираем его в профиле выгрузки, вместо типового, при этом не трогаем логику основного модуля. Отдельный модуль по сути это свой запрос данных;
3. Производительность - ряд видов данных, зависит от особенностей учета в нашей системе, если делать что то универсальное, получится сложный запрос, да он будет удовлетворять большинству условий, и через дополнительные параметры менять результат, но нам придется жертвовать скоростью, ради гибкости. При модульном подходе, мы под каждый случай делаем модуль индивидуально, в итоге получится несколько модулей вместо одного, но зато каждый будет с более легким запросом, в нем не будет данных которые не пригодятся, значит работать эта выборка будет быстрее и с меньшей нагрузкой на систему.

Выбор данных \ Настройка

Настроенный источник данных обработки выбора. Позволяет настроить фильтры и параметры запроса выбора данных индивидуально, для строки вида данных, профиля выгрузки. Если настройка не выбрана используются общие фильтры и общие параметры, в большинстве случаев этого достаточно, пригодится для тонкой настройки.

Таблицы

Список с настройками структуры запросов SQL отправляемых в удаленную систему, в разрезе выбранного вида данных. В зависимости от комбинации следующих настроек:

• Выгрузит - выгрузить данные;
• Обновить - обновить данные;
• Очистить - очистить данные.

Запросы SQL формируются по следующим правилам:

Выгрузит

Загрузить только новые данные, не трогая существующие.

• INSERT IGNORE INTO ИмяТаблицы, заполняем данные новыми значениями, игнорируя существующие записи по первичному ключу.

Такой режим позволит выгрузить новые данные, но не менять уже существующие'. На случай, когда важно сохранить изменения данных созданные на стороне удаленной системы. Данные мы сохраняем, выгружаемые изменения игнорируем. Все выгружаемые изменения игнорируем, только новые данные добавляем.

Очистить + Выгрузит

Очистить текущие данные и записать выгруженные. Использовать для полной выгрузки, когда необходимо не просто записать новые данные но и удалить не актуальные, при этом какие данные не актуальные мы не знаем, их больше нет, и поэтому нет в выгрузке. Формируем запросы:

• TRUNCATE TABLE ИмяТаблицы, либо DELETE FROM ИмяТаблицы, с заданным WHERE если не допустима полная очистка (настраивается в шаблоне выгрузки) - чистим все данные;
• INSERT IGNORE INTO ИмяТаблицы, заполняем данные новыми значениями.

Обновить в данном случае не может использоваться, так как мы чистим все данные перед загрузкой, и обновлять будет нечего

Выгрузит + Обновить

Выгрузить новые и обновить измененные данные. Использовать для частичной выгрузки обновлений (новые и измененные данные).

• INDERT INTO ИмяТаблицы ON DUPLICATE KEY UPDATE Список обновляемых полей, добавляет новые данные, а существующие обновляются по выгруженным данным.

Описание

Описание назначения таблицы во внешней системе.

Таблица

Имя таблицы в базе данных внешней системы.

Поля

Настройка полей выбранной таблицы, выбранного вида данных.

Выгрузить

Включить поле в SQL запрос выгружаемых данных.

Обновить

Включить поле в SQL запрос обновляемых данных, в часть запроса ON DUPLICATE KEY UPDATE.

Поле может быть выбрано для выгрузки, но отключено для обновления. Пригодится для ситуаций когда какие то поля, необходимо редактировать на стороне внешней системы, но не менять значения при выгрузке, заполнять только для новых данных. В этом случае отключаем поле из обновления.

Ключевое

Признак, что поле является первичным ключе. Менять через профиль нельзя, только через изменение шаблона.

Описание

Описания назначения поля.

Поле

Имя поля в таблице базы данных внешней системы.

По умолчанию

Значение поля по умолчанию, еще не предусмотрено в запросе источника данных, либо для конкретной строки данных не заполнено.

Только для просмотра, настраивается через шаблон выгрузки.

Дополнительные запросы

Любые SQL запросы, добавляемые в пакет выгрузки:

• Перед выгрузкой - добавляемые для данных выбранной таблицы, перед основными запросами (список справа, снизу);
• После выгрузки - добавляемые для данных выбранной таблицы, после основных запросов (список справа, снизу).

Пример, после выгрузки Заполнить расчетные данные, выгружаем данные, в том числе группы товаров, в котором есть поле Количество товаров, при выгрузке рассчитать не можем, это может быть выгрузка обновлений. Пишем SQL запрос, который рассчитает по каждой группе количество товаров и обновить данные в таблице группы.

Пример, Обновить данные через временные таблицы. Задача обновить цены на 10000 товаров. Перед загрузкой создаем временную таблицу CREATE TEMPORARY TABLE с ценами, заполняем, После загрузки пишем запрос который из временной таблицы выполнить SELECT в паре с UPDATE, такой подход отработает в несколько раз быстрее чем выполнять отдельно 10000 отдельных запросов UPDATE.

Отбор

Настройка общих фильтров выбора данных.

База данных

Общие свойства локальной и внешней базы данных.

Файлы

Настройка правил формирования файлов выгрузки.

Обновление

Настройки использования Планов обмена, при выгрузке обновлений данных.

Формы

Ссылка: e1cib/list/Справочник.sqlexpПрофилиSQL

Форма элемента

Основная форма создание и редактирование элемента данных.

Реквизит	Описание	Тип значения
Наименование	Представление записи при просмотре в списках, и выбранных значениях реквизитов.	Строка(150)
Код	Уникальный идентификатор записи, для поиска в списках и при выборе значений	Строка(9)
Родитель	Ссылка на элемент родитель.	Профили SQL

Структура

Реквизит	Описание	Тип значения
Шаблон	Имя файла XML с шаблонов по которому будет сформирован файл с данными	Служебные файлы

База данных

Реквизит	Описание	Тип значения
Внешняя система	Внешняя система, в которую производится выгрузка данных. Используется для ведения серий внешний кодов, доступа к API (если предусмотрен)	Внешние системы
Префикс таблиц	Префикс таблиц базы данных (если есть)	Строка(30)

Снизить нагрузку на стороне внешней системы

Реквизит	Описание	Тип значения
Объектов в запросе	Максималное колиечество объектов в одном запросе	Число(10, 0)
Объектов в файле	Максимальное количество объектов верхнего уровня в файле. 0 - не делить, иначе будет сформированого несколько файлов, в каждом не больше заданного количества.	Число(10, 0)

Файлы

Реквизит	Описание	Тип значения
Выгрузка дополнителных файлов	-	Число(1, 0)

Куда записывать сфомрированные файлы или пакеты файлов. Каталог или Том в хранилище

Реквизит	Описание	Тип значения
Том	Том файлов в который будут записываться подготовленный пакеты. Том должен быть на сервере и с локальным доступом. Выбрать или том или каталог. Если выбрано то и другое используется Том.	Тома файлов
Каталог	Каталог на стороне сервера куда будет записан сфоримрованный файл в обычном режиме или создать подкаталог для пакетной выгрузки	Строка
Имя файла	Итоговое имя файла с расширением. Если включен режим разбивки файлов по количеству объектов, к очередному файлу будет дописан индекс с его номером.	Строка(50)
Архивировать подготовленные файлы	-	Включить или Отключить
Имя архива	Имя файла архива с расширением. В который будут помещены SQL файлы и дополнительные файлы, картинки и прочее	Строка(50)

Формирование файлов без пакетов

Реквизит	Описание	Тип значения
Удалять ранее сформированные файлы	Удалять файлы перед формированием. Для режима без пакетов.	Включить или Отключить
Каждая выгрузка в отдельном подкаталоге	Обязательно включить, если планируется выгрузка через пакеты.	Включить или Отключить

Обновление

Реквизит	Описание	Тип значения
Узел обмена	Узел регистрации изменении для выгрузки обновлений	Измененные объекты
Сброс изменений	Режим сброса регистрации изменений объектов	Сброс регистрации изменений
Количество объектов	Количество строк данных выбираемых за один запрос. Значение = 0, все строки. Важно!!! Для корректной работы данный функции все запросы в обработчиках должны быть созданы по правилам выбра данных частями! Если вы не знаете как, либо не уверены ставить = 0!	Число(10, 0)

Форма списка

Основная форма просмотра списка данных.

Форма выбора

Основная Форма выбора элемента данных.

Форма группы

Основная форма создания и редактирования группы данных.

Реквизит	Описание	Тип значения
Наименование	Представление записи при просмотре в списках, и выбранных значениях реквизитов.	Строка(150)
Код	Уникальный идентификатор записи, для поиска в списках и при выборе значений	Строка(9)
Родитель	Ссылка на элемент родитель.	Профили SQL

Форма выбора группы

Основная Форма выбора группы данных.

Просмотр данных

Реквизит	Описание	Тип значения
Настройка	-	-

Метаданные

Данные

Реквизиты

Синоним	Имя	Тип значения	Описание
Код	Код	Строка(9)	-
Наименование	Наименование	Строка(150)	-
Родитель	Родитель	Справочники: Профили SQL	-
Это группа	ЭтоГруппа	Булево	-
Шаблон	Шаблон	Справочники: Служебные файлы	Имя файла XML с шаблонов по которому будет сформирован файл с данными
Префикс таблиц	ПрефиксТаблиц	Строка(30)	Префикс таблиц базы данных (если есть)
Узел обмена	УзелОбмена	Планы обмена: Измененные объекты	Узел регистрации изменении для выгрузки обновлений
Том	Том	Справочники: Тома файлов	Том файлов в который будут записываться подготовленный пакеты. Том должен быть на сервере и с локальным доступом. Выбрать или том или каталог. Если выбрано то и другое используется Том.
Каталог	Каталог	Строка	Каталог на стороне сервера куда будет записан сфоримрованный файл в обычном режиме или создать подкаталог для пакетной выгрузки
Имя файла	ИмяФайла	Строка(50)	Итоговое имя файла с расширением. Если включен режим разбивки файлов по количеству объектов, к очередному файлу будет дописан индекс с его номером.
Архивировать подготовленные файлы	Архивировать	Включить или Отключить	-
Имя архива	ИмяАрхива	Строка(50)	Имя файла архива с расширением. В который будут помещены SQL файлы и дополнительные файлы, картинки и прочее
Сброс изменений	СбросИзменений	Перечисления: Сброс регистрации изменений	Режим сброса регистрации изменений объектов
Структура выгрузки	СтруктураВыгрузки	Хранилище значения	-
Настройки	Настройки	Хранилище значения	-
Параметры	Параметры	Хранилище значения	-
Объектов в запросе	ОбъектовВЗапросе	Число(10, 0)	Максималное колиечество объектов в одном запросе
Объектов в файле	ОбъектовВФайле	Число(10, 0)	Максимальное количество объектов верхнего уровня в файле. 0 - не делить, иначе будет сформированого несколько файлов, в каждом не больше заданного количества.
Удалять ранее сформированные файлы	УдалятьФайлы	Включить или Отключить	Удалять файлы перед формированием. Для режима без пакетов.
Каждая выгрузка в отдельном подкаталоге	Отдельно	Включить или Отключить	Обязательно включить, если планируется выгрузка через пакеты.
Внешняя система	ВнешняяСистема	Справочники: Внешние системы	Внешняя система, в которую производится выгрузка данных. Используется для ведения серий внешний кодов, доступа к API (если предусмотрен)
Количество объектов	КоличествоОбъектов	Число(10, 0)	Количество строк данных выбираемых за один запрос. Значение = 0, все строки. Важно!!! Для корректной работы данный функции все запросы в обработчиках должны быть созданы по правилам выбра данных частями! Если вы не знаете как, либо не уверены ставить = 0!
Выгрузка дополнителных файлов	ВыгрузкаДопФайлов	Число(1, 0)	-

Подсистемы

• Выгрузка
• Обмен данными
• SQL