Настройка значений параметров ленты

Настройка значений параметров ленты

Введение

Если запрос активной таблицы является хранимой процедурой, SQL-кодом, http-запросом или запросом к текстовому файлу и содержит параметры, то плагин SaveToDB автоматически отображает параметры на ленте.

При изменении параметров запрос перестраивается с новыми значениями.

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

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

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

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

Если такой список значений задан для таблицы или представления, то на ленте отображаются только заданные разработчиком поля, и пользователь не может изменить список полей.
Это можно использовать для защиты сервера от запросов по полям без индекса из больших таблиц.

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

Это удобно использовать при работе с иерархическими каталогами.

Представление конфигурации

Для указания объектов формирования списков значений используется конфигурационное представление* со следующими полями:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. PARAMETER_NAME
  4. SELECT_SCHEMA
  5. SELECT_NAME
  6. SELECT_TYPE
  7. SELECT_CODE

* Для Microsoft SQL Server Compact используется таблица, а не представление. Начиная с версии 7.0, также могут использоваться таблицы, а не представления.

Первым полем представления может быть поле идентификатора.

Плагин SaveToDB определяет конфигурационные представления по сигнатуре полей.

Формат представления:

TABLE_SCHEMATABLE_NAMEPARAMETER_NAMESELECT_SCHEMASELECT_NAMESELECT_TYPESELECT_CODE
<Схема объекта>
См. таблицу 1.1
<Имя объекта>
См. таблицу 1.1
[<Имя параметра>
или <Имя колонки>]
<Схема объекта запроса>
См. таблицу 1.2
<Имя объекта запроса>
См. таблицу 1.2
<Тип объекта запроса>
См. таблицу 1.2
<SQL-код>
См. таблицу 1.2
Таблица 1.1
TABLE_SCHEMATABLE_NAME
<Схема таблицы><Имя таблицы>
<Схема представления><Имя представления>
<Схема процедуры><Имя процедуры>
<Схема объекта><Имя SQL-кода>
<Схема объекта><Имя http-запроса>
<Схема объекта><Имя text-запроса>
Таблица 1.2
SELECT_SCHEMASELECT_NAMESELECT_TYPESELECT_CODE
<Схема процедуры><Имя процедуры>[PROCEDURE] 
<Схема функции><Имя функции>FUNCTION 
<Схема таблицы><Имя таблицы>TABLE 
<Схема представления><Имя представления>VIEW 
<Схема объекта><Имя SQL-кода>CODE<SQL-код>
<Схема><Имя>RANGE *<Имя или адрес диапазона>
<Схема><Имя>VALUES *<Список значений>

* Типы RANGE и VALUES доступны в SaveToDB 7.1 и выше.

Первые два поля идентифицируют объект базы данных (таблица, представление или хранимая процедура) или объект, настроенный через Список запросов.

Поля TABLE_SCHEMA и SELECT_SCHEMA игнорируются в SQLite и Microsoft SQL Server Compact.

Поле PARAMETER_NAME определяет параметр хранимой процедуры или запроса, или поле таблицы или представления.

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

Это могут быть хранимые процедуры, табличные функции, представления, таблицы или SQL-код.

Для использования SQL-кода в поле SELECT_CODE следует указать SQL-код, а в поле SELECT_TYPE тип CODE.

SQL-код может содержать параметры в виде:

  • @ParameterName для Microsoft SQL Server, Microsoft SQL Server Compact и SQLite.
  • :ParameterName для Oracle Database, IBM DB2, MySQL, MariaDB, PostgreSQL и NuoDB.

Например:

SELECT DISTINCT CategoryID, CategoryName FROM dbo.Category c WHERE c.ParentCategoryID = @CategoryID

Пример представления конфигурации параметров

Пример представления конфигурации:

Запрос uspItem имеет три параметра CategoryID, SubcategoryID и BrandID, которые будут выведены на ленту.
Для каждого параметра заданы процедуры формирования списка значений. Их объявления:

CREATE PROCEDURE [xls13].[uspParameterValues_CategoryID]
CREATE PROCEDURE [xls13].[uspParameterValues_SubcategoryID]
    @CategoryID int = NULL
CREATE PROCEDURE [xls13].[uspParameterValues_BrandID]
    @CategoryID int = NULL
    , @SubcategoryID int = NULL

Процедуры uspParameterValues_SubcategoryID и uspParameterValues_BrandID, формирующие списки значений параметров SubcategoryID и BrandID, имеют параметр CategoryID.
Таким образом, при изменении параметра CategoryID будут запрошены новые списки значений параметров SubcategoryID и BrandID, перестроен запрос таблицы и обновлены данные таблицы Microsoft Excel.

Обновление списков значений в Microsoft Excel

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

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

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

В приведенном выше примере, будут перезагружены списки параметров @Subcategory и @Brand, а список @Category останется неизменным.

Добавление пустого значения в список значений

Если запрос списка значений для параметра не указан, то пользователь может ввести любое значение.

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

Пустое значение (NULL) автоматически добавляется только в списки запросов с контекстными параметрами (@Subcategory и @Brand в примере выше).
Это необходимо, т.к. список значений зависимого параметра должен содержать NULL для отбора всех позиций родительского параметра.

Если же запрос списка значений не содержит параметров, то пустое значение автоматически не добавляется.
Разработчик имеет полный контроль над списком.
Иногда значение NULL должно быть, а иногда - нет. Например, если требуется обязательное указание номера счета.

Это поведение изменено в версии 3.0.

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

Например, это строка в начале запроса типа:

SELECT NULL AS ID, NULL AS Name UNION

Выходные поля процедур запроса списков значений

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

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

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

Выходные поля таблиц и представлений запроса списков значений

Если в качестве объекта запроса списка значений указана таблица или представление, то запрос выходных полей формируется плагином SaveToDB.

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

Например, при наличии полей-параметров Category и Subcategory, представление списка значений Subcategory должно зависеть от значения Category.

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

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

Приоритет выбора поля значений параметра:

  1. Поле с именем параметра.
  2. Поле с именем ID.
  3. Первое поле с типом данных параметра списка значений.

Приоритет выбора поля наименования значения параметра на ленте:

  1. Поле с именем Name, если оно не входит в поле отбора WHERE.
  2. Первое поле с типом данных *CHAR (char, nchar, varchar, nvarchar), если оно не входит в поле отбора WHERE.

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

Но, возможно, потребуется изменить порядок полей.

Например:

Представление возвращает поля CategoryID, SubcategoryID, CategoryName и SubcategoryName и используется для выбора значений поля SubcategoryID.

Если у базового запроса таблицы нет параметра CategoryID, то будет сформирован запрос:

SELECT DISTINCT SubcategoryID, CategoryName FROM ...

Если же базовый запрос таблицы содержит параметр CategoryID, то будет сформирован запрос

SELECT DISTINCT SubcategoryID, CategoryName FROM ... WHERE CategoryID = @CategoryID

В обоих случаях первым идет поле CategoryName, как первое поле с типом *CHAR.

Соответственно, для выбора значений поля SubcategoryID поле CategoryName надо или убрать или переместить за поле SubcategoryName.

Создание конфигурации в базе данных

С помощью SaveToDB Framework можно быстро добавить функциональность управления списками значений параметров в существующие базы данных Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, PostgreSQL, NuoDB и SQLite.

Этот веб-сайт использует куки. Продолжая использовать веб-сайт, Вы принимаете условия Cookie Policy.