1sqlite
1sqlite

1sqlite

Внешняя компонента, предназначенная для работы с базами данный SQLite.

Версия 1.0.1.3

Основана на проекте http://www.sqlite.org.

Автор - Александр Орефков (orefkov@gmail.com)

(Здесь бы хотелось краткого объяснения для чайников, что такое SQLite, и чем его использование может пригодится в 1С. От себя просто скажу, что SQLite может создавать "in memory" базы данных (базы данных в памяти), что позволяет использовать его как альтернативу ТЗ и ИТЗ для временных расчетов.)

Кроме того, при работе в ДБФ-версии 1С, компонента позволяет посредством "движка" SQLite и встроенным в него механизмом "виртуальных таблиц" обращатся на чтение к таблицам базы данных 1С через "родные" методы самой 1С. Что позволяет выполнять запросы к базе 1С даже в монопольном режиме работы. (Подробнее см. Отображение ДБФ-таблиц 1С в базу данных SQLite)

Основные фичи компоненты:
  • SQLite версии 3.5.9
  • Движок SQLite доработан в плане регистронезависимости русских символов, нормально работают lower, upper, like, названия таблиц, полей.
  • Добавлено collate _1С - сравнение строк без учета регистра и завершающих пробелов.
  • Отображение ДБФ-таблиц 1С в базу данных SQLite и возможность использовать их в запросах.
  • Работа с ДБФ-таблицами 1С в монопольном режиме.
  • Получение "длинных" строк 1С-ДБФ.
  • Типизация результатов запроса типами данных 1С.
  • Работа с текстовыми и sql-параметрами в запросах.
  • Укладка в базу данных SQLite ТаблицЗначений.
  • Укладка в базу данных SQLite СписовЗначений с объектами 1С, с возможностью в ДБФ версии разворота групп справочников или счетов по иерархии.

Для подключения компоненты к 1С необходимо выполнить команду:

ЗагрузитьВнешнююКомпоненту("ПутьКФайлу_1sqlite.dll");
  • Отображение ДБФ-таблиц 1С в базу данных SQLite
  • Объекты компоненты
  • Связь между значениями 1С и значениями в базе данных
  • История версий


Отображение ДБФ-таблиц 1С в базу данных SQLite

1sqlite
Отображение ДБФ-таблиц 1С в базу данных SQLite

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

Осуществляется это с помощью механизма, названного в SQLite "виртуальные таблицы". Для создания виртуальной таблицы необходимо выполнить запрос:

create virtual table ЖелаемоеИмяТаблицы using ИмяМодуляПоставщика(НаборПараметровДляМодуляПоставщика)

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

Факт подключения виртуальной таблицы фиксируется в базе SQLite. Поэтому повторное создание таблицы с таким же ЖелаемоеИмяТаблицы не получится. Если база данных не в памяти, а в файле, то после открытия/закрытия базы ранее подключенные таблицы подключатся автоматом.

В данной внешней компоненте, при ее загрузке в ДБФ-базе 1С, к движку SQLite подключается модуль dbeng, посредством которого можно отобразить в базу SQLite таблицы ДБФ-базы 1С. Благодаря тому, что для получения данных из таблиц модуль dbeng использует вызов "родных" методов dbeng32.dll, возможно получение данных из таблиц 1С в SQL-запросах и в монопольном режиме, а также "прозрачное" получение "длинных" строк, которые в дбф-1С хранятся извращенно, и другими способами доступа (OLEDB, ODBC) извлекаются очень муторно.

При отображении таблиц 1С модуль dbeng регистрирует структуру полей таблицы, соответствующую полям дбф-файла. При этом для полей дбф-файла, которым соответствуют метаданные конфигурации, вместо названий полей из дбф-файла используются названия из конфигурации, что позволяет в текстах SQL-запросов обходиться без мета-парсера. То есть в запросах названия полей таблиц можно писать так, как они заданы в конфигурации.

Кроме того, для каждой таблицы SQLite позволяет использовать преодпределенное поле rowid, которое используется для идентификации записи. Модуль dbeng возвращает в этом поле номер записи в дбф-файле.

Также модуль dbeng при отображении таблиц создает в структуре подключаемой таблицы "виртуальные" поля индексов - это поле, которое для движка SQLite выглядит обычным полем, но данные, возвращаемые модулем dbeng для этого поля - есть ключ индекса текущей записи, с добавлением номера записи. Основных назначений таких полей два - использование как уникального ключа записи для использовании в ТабличномПоле 1С++, и оптимизация указания границ по составным полям. Имя таких полей формируется как:

idx_Поле1_Поле2_Итд

то есть префикс "idx", за которым через символы "_" перечислены названия поля, входящих в индекс.

О числовых полях.

В SQLite нет поддержки работы с полями типа numeric. Для числовых полей в SQLite используются либо целые числа, либо double. Тип double гарантирует 16 точных знаков числа, то есть для нецелых числовых полей таблиц 1С, с длинной больше 17, возможна потеря точности. Также известно, что нецелые числа в double хранятся не всегда точно (с возможной ошибкой в 17 знаке, те например 0.3 хранится как 0.30000000000000001). Например, условие 0.3 - 0.1 = 0.2 не будет выполнятся. Поэтому в запросах с умом пользуйтесь функцией round. Например, условие round(0.3 - 0.1, 1) = 0.2 будет выполнятся.

Небольшой пример подключения таблицы 1С к базе SQLite и выполнения запроса:

// Создаем объект - базу данных
Попытка
    база = СоздатьОбъект("SQLiteBase");
Исключение
    ЗагрузитьВнешнююКомпоненту("1sqlite.dll");
    база = СоздатьОбъект("SQLiteBase");
КонецПопытки;

// Откроем пустую базу данных в памяти
база.Открыть(":memory:");
// Создадим выполнитель запросов.
запрос = база.НовыйЗапрос();
// Так как база в памяти, то журналирование нам ни к чему, только зря будет отнимать время
запрос.ВыполнитьЗапрос("PRAGMA journal_mode = OFF");
// Подключим таблицу справочника товаров к SQLite
запрос.ВыполнитьЗапрос("create virtual table Товары using dbeng(Справочник.Номенклатура)");
// А теперь посчитаем, сколько у нас в справочнике элементов, без учета групп
Сообщить("Кол=" + запрос.ВыполнитьЗапрос("
|select count(*)
|from Товары
|where Товары.isfolder = 2
|").ПолучитьЗначение(1, 1));
  • Подключение таблиц справочников
  • Подключение таблиц шапок документов
  • Подключение таблиц строк документов
  • Подключение таблицы журнала документов
  • Подключение таблиц движений регистров
  • Подключение таблиц итогов регистров
  • Подключение системных таблиц 1Sxxx


Подключение таблиц справочников

1sqlite
Подключение таблиц справочников

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

Справочник|Reference.ИмяСправочника

Пример:

create virtual table Товары using dbeng(Справочник.Номенклатура)
create virtual table Customers using dbeng(Reference.Контрагенты)

Структура полей подключенной таблицы:

  • служебные поля с названиями, как есть (descr, code, id, isfolder и тп)
  • поля реквизитов справочника с именами, как задано в конфигурации (те например ВидТовара вместо SP73636)
  • поля реквизитов с типом "неограниченная строка", с именами, как задано в конфигурации (хотя в самой дбф-таблице этих полей нет)
  • виртуальные поля индексов (см. 1Cv7.DD)


Подключение таблиц шапок документов

1sqlite
Подключение таблиц шапок документов

Таблицы шапок документов подключаются, используя имя:

Документ|Document.ИмяДокумента

Пример:

create virtual table Счета using dbeng(Документ.ЗаявкаПокупателя)
create virtual table Invoices using dbeng(Document.СчетФактура)

Структура полей подключенной таблицы:

  • служебные поля с названиями, как есть (iddoc и тп)
  • поля реквизитов шапки документа с именами, как задано в конфигурации (те например Контрагент вместо SP73636)
  • поля реквизитов шапки документа с типом "неограниченная строка", с именами, как задано в конфигурации (хотя в самой дбф-таблице этих полей нет)
  • поля реквизитов табличной части документов с признаком "Итог по колонке", с именами, как задано в конфигурации.
  • Поля общих реквизитов документов без признака "отбор", с именами, как задано в конфигурации
  • виртуальные поля индексов (см. 1Cv7.DD)


Подключение таблиц строк документов

1sqlite
Подключение таблиц строк документов

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

ДокументСтроки|DocumentLines.ИмяДокумента

Пример:

create virtual table СчетаСтроки using dbeng(ДокументСтроки.ЗаявкаПокупателя)
create virtual table InvoicesLines using dbeng(DocumentLines.СчетФактура)

Структура полей подключенной таблицы:

  • служебные поля с названиями, как есть (iddoc, lineno и тп)
  • поля реквизитов табличной части документа с именами, как задано в конфигурации (те например Товар вместо SP73636)
  • виртуальные поля индексов (см. 1Cv7.DD)


Подключение таблицы журнала документов

1sqlite
Подключение таблицы журнала документов

Таблица журнала документов (1SJOURN) подключается, используя имя:

Журнал|Journal

Пример:

create virtual table Журнал using dbeng(Журнал)
create virtual table Journal using dbeng(Journal)

Структура полей подключенной таблицы:

  • служебные поля с названиями, как есть (iddoc, iddocdef и тп)
  • Поля общих реквизитов документов с признаком "отбор", с именами, как задано в конфигурации
  • Поля флага наличия движений по регистрам, с именами ИмяРегистраКакВКонфигурацииФр
  • Поля последовательностей с именами ИмяПоследовательностиВКонфигурацииПс
  • виртуальные поля индексов (см. 1Cv7.DD)


Подключение таблиц движений регистров

1sqlite
Подключение таблиц движений регистров

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

Регистр|Register.ИмяРегистра

Пример:

create virtual table Остатки using dbeng(Регистр.ОстаткиТоваров)
create virtual table Sales using dbeng(Register.Продажи)

Структура полей подключенной таблицы:

  • служебные поля с названиями, как есть (iddoc, lineno, debkred и тп)
  • поля измерений регистра с именами, как задано в конфигурации (те например Контрагент вместо SP73636)
  • поля ресурсов регистра с именами, как задано в конфигурации
  • поля реквизитов регистра с именами, как задано в конфигурации.
  • виртуальные поля индексов (см. 1Cv7.DD)


Подключение таблиц итогов регистров

1sqlite
Подключение таблиц итогов регистров

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

РегистрИтоги|RegisterTotals.ИмяРегистра

Пример:

create virtual table ОстаткиИтоги using dbeng(РегистрИтоги.ОстаткиТоваров)
create virtual table SalesTotals using dbeng(RegisterTotals.Продажи)

Структура полей подключенной таблицы:

  • служебные поля с названиями, как есть (period и тп)
  • поля измерений регистра с именами, как задано в конфигурации (те например Контрагент вместо SP73636)
  • поля ресурсов регистра с именами, как задано в конфигурации
  • виртуальные поля индексов (см. 1Cv7.DD)


Подключение системных таблиц 1Sxxx

1sqlite
Подключение системных таблиц 1Sxxx

Служебные таблицы 1С, чьи имена начинаются с 1S (кроме 1SJOURN), подключаются, используя имя:

_1С|_1S.ИмяТаблицыБез1С

Пример:

create virtual table ПланСчетов using dbeng(_1С.Accs)
create virtual table Consts using dbeng(_1S.Const)

Структура полей подключенной таблицы:

  • Поля дбф-файла с названиями, как есть.
  • виртуальные поля индексов (см. 1Cv7.DD)


Объекты компоненты

1sqlite
Объекты компоненты

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

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

  • Объект SQLiteBase
  • Объект SQLiteQuery


Объект SQLiteBase

1sqlite
Объект SQLiteBase

Объект предназначен для открытия базы данных SQLite и создания объектов-запросов.

Создается методом:

база = СоздатьОбъект("SQLiteBase");

Методы

Открыть / Open | Открывает/создает указанный файл с базой данных.
Открыта / IsOpen | Позволяет проверить, открыта ли в данный момент база данных.
Закрыть / Close | Закрывает базу данных.
НовыйЗапрос / NewQuery | Создает новый объект типа SQLiteQuery.
Замерять / DoProfile | Включает одноразовую трассировку выполнения следующего запроса.
Замер / Profile | Получить результат трассировки.
УложитьТЗ / PutVT | Загружает таблицу значений в базу данных, как таблицу базы данных.
УложитьОбъекты / PutObjects | Загружает значение либо список значений в базу данных, как таблицу базы данных.


Объект SQLiteBase::Открыть / Open

1sqlite
Объект SQLiteBase :: Открыть / Open

Синтаксис: Открыть(ИмяБазыДанных)

Параметры:
  • ИмяБазыДанных, тип: Строка. Путь к файлу базы данных

Возвращает: нет

Описание: Открывает/создает указанный файл с базой данных.

Если файл не существует, создается новая база данных в указанном файле. Если передано имя ":memory:", создается пустая база данных в памяти. При возникновении ошибки вызывает исключение, с описанием ошибки, возвращенным движком SQLite.


Объект SQLiteBase::Открыта / IsOpen

1sqlite
Объект SQLiteBase :: Открыта / IsOpen

Синтаксис: Открыта()

Параметры: нет

Возвращает: Число. 1 - база данных в данный момент открыта, 0 - не открыта.

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


Объект SQLiteBase::Закрыть / Close

1sqlite
Объект SQLiteBase :: Закрыть / Close

Синтаксис: Закрыть()

Параметры: нет

Возвращает: нет.

Описание: Закрывает базу данных.

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


Объект SQLiteBase::НовыйЗапрос / NewQuery

1sqlite
Объект SQLiteBase :: НовыйЗапрос / NewQuery

Синтаксис: НовыйЗапрос()

Параметры: нет

Возвращает: Объект SQLiteQuery

Описание: Создает новый объект типа SQLiteQuery.


Объект SQLiteBase::Замерять / DoProfile

1sqlite
Объект SQLiteBase :: Замерять / DoProfile

Синтаксис: Замерять()

Параметры: нет

Возвращает: нет

Описание: Включает одноразовую трассировку выполнения следующего запроса.

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

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


Объект SQLiteBase::Замер / Profile

1sqlite
Объект SQLiteBase :: Замер / Profile

Синтаксис: Замер()

Параметры: нет

Возвращает: Строка. Результат трассировки выполнения запроса.

Описание: Получить результат трассировки.

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

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


Объект SQLiteBase::УложитьТЗ / PutVT

1sqlite
Объект SQLiteBase :: УложитьТЗ / PutVT

Синтаксис: УложитьТЗ(ТЗ, ИмяТаблицы, [КакПостоянную])

Параметры:
  • ТЗ - тип: ТаблицаЗначений. Таблица, загружаемая в базу данных
  • ИмяТаблицы - тип: Строка. Имя созаваемой в базе данных таблицы.
  • КакПостоянную - тип: Число. 1 - создать обычную таблицу, 0 - создать временную таблицу. Необязательный параметр. По умолчанию 0.

Возвращает: нет

Описание: Загружает таблицу значений в базу данных, как таблицу базы данных.

Если таблица с указанным именем уже существует, она удаляется. Состав полей созданной таблицы соответствует составу колонок ТЗ. Имена полей созданной таблицы соответствуют идентификаторам колонок ТЗ. Формат в котором выгружаются значения из ТЗ, см в Преобразование значений 1С в значения базы данных (работа с параметрами)


Объект SQLiteBase::УложитьОбъекты / PutObjects

1sqlite
Объект SQLiteBase :: УложитьОбъекты / PutObjects

Синтаксис: УложитьОбъекты(Объект, ИмяТаблицы, [КакПостоянную], [Иерархия])

Параметры:
  • Объект - тип: СписокЗначений, Справочник, Счет. Выгружаемые объекты.
  • ИмяТаблицы - тип: Строка. Имя созаваемой в базе данных таблицы.
  • КакПостоянную - тип: Число. 1 - создать обычную таблицу, 0 - создать временную таблицу. Необязательный параметр. По умолчанию 0.
  • Иерархия - тип: Строка. Название справочника или плана счетов для разворота групп справочника или групп счетов. Необязательный параметр. По умолчанию - пустая строка.

Возвращает: нет

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

В базе данных создается таблица с заданным именем с одним полем val. Если таблица с таким именем существует, она уничтожается. В данную таблицу выгружаются внутренние идентификаторы объектов в формате char(9). Если первым параметром передан список значений, в таблицу укладываются значения из переданного списка.

Если задана иерархия (название справочника либо плана счетов), то вместо объектов-групп выгружаются все входящие в группу элементы/счета всех нижележащих уровней. Сами группы при этом не выгружаются. Работа с иеерархией выполняется только в дбф-базах, в sql-версии 1С будет генерироваться исключение.

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

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


Объект SQLiteQuery

1sqlite
Объект SQLiteQuery

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

Он создается методом SQLiteBase::НовыйЗапрос.

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

Методы

Отладка / Debug | Включает/выключает режим отладки.
ВыполнитьЗапрос / ExecuteQuery | Выполняет переданный sql-запрос.
Подготовить / Prepeare | Подготавливает переданный sql-запрос.
УстановитьПараметр / SetParam | Устанавливает значение sql-параметра подготовленного запроса.
Выполнить / Exec | Выполняет подготовленный sql-запрос.
Подставлять / Substitute | Устанавливает значение для текстового параметра.


Объект SQLiteQuery::Отладка / Debug

1sqlite
Объект SQLiteQuery :: Отладка / Debug

Синтаксис: Отладка([ФлагОтладки])

Параметры:
  • ФлагОтладки - тип: Число. 0 - выключить отладку, не 0 - включить отладку. Необязательный параметр. По умолчанию: 1.

Возвращает: нет.

Описание: Включает/выключает режим отладки.

При включенном режиме отладки перед подготовкой запроса в окно сообщений выводится текст запроса, обработанный мета-парсером. Также в процессе подготовки в окно сообщений выводится информация по подбору индексов для таблиц 1С, участвующих в запросе. После подготовки запроса режим отладки выключается. Учитывая, что в методе ВыполнитьЗапрос также происходит подготовка запроса, все сказанное относится и к методу ВыполнитьЗапрос.


Объект SQLiteQuery::ВыполнитьЗапрос / ExecuteQuery

1sqlite
Объект SQLiteQuery :: ВыполнитьЗапрос / ExecuteQuery

Синтаксис: ВыполнитьЗапрос(ТекстЗапроса, [ТЗ])

Параметры:
  • ТекстЗапроса - тип: Строка. Текст выполняемого запроса.
  • ТЗ - тип: ТаблицаЗначений. Если передать сюда ссылку на ТаблицуЗначений, то результат выполнения запроса выгрузиться в нее, предварительно очистив таблицу. Необязательный параметр.

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

Описание: Выполняет переданный sql-запрос.

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


Объект SQLiteQuery::Подготовить / Prepeare

Объект SQLiteQuery :: Подготовить / Prepeare

Синтаксис: Подготовить(ТекстЗапроса)

Параметры:
  • ТекстЗапроса - тип: Строка. Текст подготавливаемого запроса.

Возвращает: нет.

Описание: Подготавливает переданный sql-запрос.

Метод на основе переданного текста подготавливает запрос. Затем впоследствии можно неоднократно его выполнить методом SQLiteQuery::Выполнить, при необходимости указывая различные sql-параметры методом SQLiteQuery::УстановитьПараметр. При возникновении ошибки генерирует исключение.

Для чего применять подготовленные запросы.

Любой запрос в SQLite проходит следующие стадии жизни:

  • Подготовка запроса. SQLite на основе текста запроса генерирует программу его выполнения для VDBE (Virtual DataBase Engine - часть SQLite, непосредственно выполняющая запрос)
  • Одно или несколько выполнений сгенерированной программы, с получений результатов выполнения.
  • Закрытие запроса. Сгенерированная программа уничтожается.

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

Если же необходимо несколько раз выполнять однотипные запросы, но в которых нужно только менять некоторые входные данные, используют параметризированные запросы: подготовленные запросы, в которых часть запроса заменена sql-параметром. В тексте запроса они обозначаются следующими способами:

  • ? - неименованный параметр, установка возможна только по индексу.
  • ?Число - неименованный параметр, установка возможна по индексу Число.
  • $ИмяПараметра или @ИмяПараметра - именованный параметр, установка возможна по имени.

Допустим, нам необходимо выполнить один и тот же запрос, только для разных данных. Например, получение остатка какого-либо товара на ТА:

Перем запрос;
Процедура Инициализация()
    запрос = глБаза.НовыйЗапрос();
КонецПроцедуры

Функция ОстатокНаТА(Товар)
    запрос.Подставлять("ВыбТовар", Товар);
    запрос.Подставлять("ДатаТА", ПериодДляТА);
    тз = запрос.ВыполнитьЗапрос("select sum(Количество) from ОстаткиТоваровИтоги where period=:ДатаТА and Товар = :ВыбТовар");
    Вовзрат тз.ПолучитьЗначение(1, 1);
КонецФункции

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

Перем запрос;
Процедура Инициализация()
    запрос = глБаза.НовыйЗапрос();
    запрос.Подставлять("ДатаТА", ПериодДляТА);
    запрос.Подготовить("select sum(Количество) from ОстаткиТоваровИтоги where period=:ДатаТА and Товар = @ВыбТовар");
КонецПроцедуры

Функция ОстатокНаТА(Товар)
    запрос.УстановитьПараметр("@ВыбТовар", Товар);
    тз = запрос.Выполнить();
    Вовзрат тз.ПолучитьЗначение(1, 1);
КонецФункции

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


Объект SQLiteQuery::УстановитьПараметр / SetParam

Объект SQLiteQuery :: УстановитьПараметр / SetParam

Синтаксис: УстановитьПараметр(ИмяНомерПараметра, Значение, [Модификатор])

Параметры:
  • ИмяНомерПараметр - тип: Строка, Число. Либо название именнованого sql-параметра, либо его номер (нумерация параметров с 1).
  • Значение - тип: Любой. Устанавливаемое значение параметра.
  • Модификатор - тип: Число. Модификатор, задающий правило преобразования значения 1С в значение базы данных. Подробнее см. Преобразование значений 1С в значения базы данных (работа с параметрами). Необязательный параметр. По умолчанию 0.

Возвращает: нет.

Описание: Устанавливает значение sql-параметра подготовленного запроса.

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

  • ? - неименованый параметр, установка возможна только по индексу.
  • ?Число - неименованный параметр, установка возможна по индексу Число.
  • $ИмяПараметра или @ИмяПараметра - именованный параметр, установка возможна по имени.

Как именно значение будет подставлено в качестве параметра, зависит от модификатора.


Объект SQLiteQuery::Выполнить / Exec

Объект SQLiteQuery :: Выполнить / Exec

Синтаксис: Выполнить([ТЗ])

Параметры:
  • ТЗ - тип: ТаблицаЗначений. Если передать сюда ссылку на ТаблицуЗначений, то результат выполнения запроса выгрузиться в нее, предварительно очистив таблицу. Необязательный параметр.

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

Описание: Выполняет подготовленный sql-запрос.

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


Объект SQLiteQuery::Подставлять / Substitute

1sqlite
Объект SQLiteQuery :: Подставлять / Substitute

Синтаксис: Подставлять(ИмяПарметра, Значение)

Параметры:
  • ИмяПарметра - тип: Строка. Имя текстового параметра, для которого устанавливается значение.
  • Значение - тип: Любой. Значение, устанавливаемого для текстового параметра.

Возвращает: нет.

Описание: Устанавливает значение для текстового параметра.

Текстовые параметры - фрагенты текста запроса вида:

:ИмяПараметра[*~]

Перед передачей текста запроса движку SQLite, компонента вместо указанных в нем текстовых параметрах подставляет установленные для них значения.

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

Модификатор задается либо символом '*', либо нулем или более символов '~', следующими за именем параметра (пробелы между именем параметра и модификаторами недопустимы).

Символ '*' обозначает модификатор '-1', количество же символов '~' следующих за именем параметра задает его номер.

Например:

:ВыбТовар - модификатор 0
:ВыбКлиент~ - модификатор 1
:ПозДок~~~ - модификатор 3
:Установить* - модификатор -1

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

Предопределенные текстовые параметры

Помимо задаваемых пользователем текстовых параметров, существуют несколько преодпределенных текстовых параметров. В-основном они служат для получения идентификаторов метаданных различных видов объектов. Такие параметры имеют модификаторы - 0 и 1. При нулевом модификаторе они заменяются на строку длиной 4 символа - идентификатор метаданных в 36ричной записи, при модификаторе 1 - целое число.

Константа / Constant

Подстановка идентификатора указанной константы:

:Константа|Constant.ИмяКонстанты[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор константы в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор константы.

ВидСправочника / ReferenceKind

Подстановка идентификатора указанного вида справочника:

:ВидСправочника|ReferenceKind.ИмяСправочника[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор вида справочника в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор вида справочника.

ВидДокумента / DocumentKind

Подстановка идентификатора указанного вида документа:

:ВидДокумента|DocumentKind.ИмяДокумента[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор вида документа в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор вида документа.

ВидПеречисления / EnumKind

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

:ВидПеречисления|EnumKind.ИмяПеречисления[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор вида перечисления в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор вида перечисления.

ИсторияРеквизита / PropertyHistory

Подстановка идентификатора указанного периодического реквизита справочника:

:ИсторияРеквизита|PropertyHistory.ИмяСправочника.ИмяРеквизита[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор периодического реквизита в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор периодического реквизита.

ЖурналДокументов / DocsJournal

Подстановка идентификатора указанного журнала документов:

:ЖурналДокументов|DocsJournal.ИмяЖурнала[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор журнала документов в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор журнала документов.

ГрафаОтбора / SelectionColumn

Подстановка идентификатора указанной графы отбора:

:ГрафаОтбора|SelectionColumn.ИмяГрафыОтбора[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор графы отбора в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор графы отбора.

Календарь / Calendar

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

:Календарь|Calendar.ИмяКалендаря[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор календаря в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор календаря.

Праздники / Holidays

Подстановка идентификатора праздников:

:Праздники|Holidays[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор праздников в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор праздников.

ВидЖурналаРасчетов / CalcJournalKind

Подстановка идентификатора указанного вида журналов расчетов:

:ВидЖурналаРасчетов|CalcJournalKind.ИмяЖурналаРасчетов[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор вида журналов расчетов в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор вида журналов расчетов.

ВидРасчета / Algorithm

Подстановка идентификатора указанного вида расчета:

:ВидРасчета|Algorithm.ИмяВидаРасчетов[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор вида расчета в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор вида расчета.

ПустойИД / EmptyID

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

:ПустойИД|EmptyID

Подставляется строка 9 символов: ' 0 '

ПустойИД13 / EmptyID13

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

:ПустойИД13|EmptyID13

Подставляется строка 13 символов: ' 0 0 '

Перечисление / Enumeration

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

:Перечисление|Enumeration.ИмяВидаПеречисления.ИмяЗначенияПеречисления

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

ВидСубконто / SubcKind

Подстановка идентификатора вида субконто:

:ВидСубконто|SubcKind.ИмяВидаСубконто[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор вида субконто в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор вида субконто.

ПланСчетов / ChartOfAccounts

Подстановка идентификатора указанного плана счетов:

:ПланСчетов|ChartOfAccounts.ИмяПланаСчетов[~]

При нулевом модификаторе подставляется строка длиной 4 символа - идентификатор плана счетов в 36ричной записи

При модификаторе 1 подставляется целое число - идентификатор плана счетов.


Связь между значениями 1С и значениями в базе данных

1sqlite
Связь между значениями 1С и значениями в базе данных

Если вы хорошо знакомы со структурой базы данных 1С, то наверняка знаете, что одно и то же значение 1С может хранится в базе данных 1С по разному, в-зависимости от того, где оно хранится. Поэтому при работе с прямыми запросами к 1С возникают две задачи:

  • Правильно преобразовать значение из 1С в значение для базы данных
  • Правильно преобразовать значение из базы данных в значение для 1С

Автоматизировать этот процесс сложно (по сути дела можно, ведь 1С сама это делает), но человек всегда умнее машины, и поэтому в компоненте реализована возможность програмиста указать движку, что же он хочет.

  • Преобразование значений 1С в значения базы данных (работа с параметрами)
  • Преобразование значений базы данных в значения 1С (типизация запроса)


Преобразование значений 1С в значения базы данных (работа с параметрами)

1sqlite
Преобразование значений 1С в значения базы данных (работа с параметрами)

Данная потребность возникает в методах УложитьТЗ, УстановитьПараметр, Подставлять. При работе этих методов возникает необходимость указать, как же именно преобразовать значение из 1С в значение базы данных.

Данная задача решается на основе типа значения и модификатора. Тип значения компонента определяет сама, из переданного значения, а модификатор задает програмист:

  • В методе "УстановитьПараметр" модификатор передается програмистом как параметр метода.

  • Для текстовых параметров модификатор указывается в тексте запроса.

  • В методе "УложитьТЗ" модификатор задается для каждой колонки таблицы и определяется следующим образом:

    Если в заголовке колонки таблицы есть подстрока "mod=", то модификатор получается как число, следующее за этой подстрокой.

    Если в заголовке колонки таблицы нет подстроки "mod=", то если тип колонки не задан, модификатор равен -1. Если тип колонки задан, и является "агрегатным" типом, но Вид не задан (например просто "Справочник", "Документ"), то модификатор равен 1. В остальных случаях модификатор принимается равным 0.

Значение любого типа с модификатором -1 преобразуется в тип базы данных "Неопределенный", строка 23 символа. Модификаторы от 0 и выше для разных типов значений действуют по разному.

Ниже приводятся правила действия модификаторов на типы значений 1С.

Значение типа Строка

  • 0: Строка как есть.
  • 1: Переданная строка должна быть позицией документа, которая преобразуется в формат 1С date_time_iddoc.
  • 2: Переданная строка должна быть позицией документа, которая преобразуется в формат 1С date_time_iddoc + символ 'Я'

Значение типа Число

  • 0: Число как есть. При установке sql-параметра и выгрузке ТЗ, если число целое, то устанавливается как целое, иначе устанавливается как double.

Значение типа Дата

  • 0: Строка формата 'YYYYMMDD', как дата хранится в дбф-файлах
  • 1: Строка формата 'YYYYMMDDЯ', момент конца даты

Значение типа Перечисление

  • 0: Строка длинной 9 - внутренний идентификатор значения
  • 1: Строка длинной 13 - внутренний идентификатор значения с видом значения

Значение типа Справочник

  • 0: Строка длинной 9 - внутренний идентификатор значения
  • 1: Строка длинной 13 - внутренний идентификатор значения с видом значения
  • 2: Строка длинной 4 - внутренний идентификатор вида значения

Значение типа Документ

  • 0: Строка длинной 9 - внутренний идентификатор значения
  • 1: Строка длинной 13 - внутренний идентификатор значения с видом значения
  • 2: Строка длинной 4 - внутренний идентификатор вида значения
  • 3: Строка длинной 23 - позиция документа в формате date_time_iddoc
  • 4: Строка длинной 24 - позиция документа в формате date_time_iddoc + 'Я'

Значение типа Календарь

  • 0: Строка длинной 9 - внутренний идентификатор значения
  • 1: Строка длинной 13 - внутренний идентификатор значения с видом значения

Значение типа ВидРасчета

  • 0: Строка длинной 9 - внутренний идентификатор значения
  • 1: Строка длинной 13 - внутренний идентификатор значения с видом значения
  • 2: Строка длинной 4 - внутренний идентификатор вида значения

Значение типа Счет

  • 0: Строка длинной 9 - внутренний идентификатор значения
  • 1: Строка длинной 13 - внутренний идентификатор значения с видом значения
  • 2: Строка длинной 4 - внутренний идентификатор вида значения

Значение типа ВидСубконто

  • 0: Строка длинной 9 - внутренний идентификатор значения
  • 1: Строка длинной 13 - внутренний идентификатор значения с видом значения
  • 2: Строка длинной 4 - внутренний идентификатор вида значения

Значение типа ПланСчетов

  • 0: Строка длинной 9 - внутренний идентификатор значения
  • 1: Строка длинной 13 - внутренний идентификатор значения с видом значения
  • 2: Строка длинной 4 - внутренний идентификатор вида значения


Преобразование значений базы данных в значения 1С (типизация запроса)

1sqlite
Преобразование значений базы данных в значения 1С (типизация запроса)

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

Типизация колонок задается с помощью указания типа колонки в ее названии специальным образом: в названии колонки должен присутствовать символ '$' или ':', следом за которым следует указание типа. В результате запроса имя колонки будет состоять только из названия колонки в тексте запроса, без указания типа. Например:

select id [Товар :Справочник.Номенклатура]...

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

Если для колонки не задана типизация, значения в результате запросе будут "как есть" - либо строка, либо целое число, либо вещественное число (double) без округления.

Типизация Строка

Задается как:

$|:Строка|String[.ЧислоДлинаСтроки]

Значения результата запроса преобразуются в строку.

Типизация Число

Задается как:

$|:Число|Number[.ЧислоДлинаЧисла[.ЧислоТочностьЧисла]]

Значение результата запроса преобразуется в число. При указании точности числа, результат запроса округляется до указанной точности.

Типизация Дата

Задается как:

$|:Дата|Date

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

Типизация Справочник

Задается как:

$|:Справочник|Reference[.ИмяВидаСправочника]

При указании вида справочника результат должен быть строкой длинной 9 - внутренний идентификатор элемента справочника. Если вид справочника не указан, результат должен быть строкой 13 символов - внутренний идентификатор элемента справочника с идентификаторм вида справочника.

Типизация Документ

Задается как:

$|:Документ|Document[.ИмяВидаДокумента]

При указании вида документа результат должен быть строкой 9 символов - внутренний идентификатор документа. Если вид документа не указан, результат должен быть строкой 13 символов - внутренний идентификатор документа с идентификаторм вида документа. Либо результат может быть строкой 9 символов - внутренний идентификатор документа, и в составе полей запроса существует колонка с именем, как у этой колонки + "_вид", или "_kind", в которой лежит вид документа.

Типизация ВидДокумента

Задается как:

$|:ВидДокумента|DocumentKind

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

Типизация ВидДокументаПредставление

Задается как:

$|:ВидДокументаПредставление|DocumentKindPresent

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

Типизация Перечисление

Задается как:

$|:Перечисление|Enum[.ВидПеречисления]

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

Типизация Счет

Задается как:

$|:Счет|Account[.ПланСчетов]

При указании плана счетов результат должен быть строкой 9 символов - внутренний идентификатор счета. Если план счетов не указан, результат должен быть строкой 13 символов - внутренний идентификатор счета с идентификаторм плана счетов.

Типизация Неопределенный

Задается как:

$|:Неопределенный|Undefine

Результат запроса должен быть строкой длинной 23 символа.

Типизация ВидРасчета

Задается как:

$|:ВидРасчета|CalculationKind

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

Типизация Календарь

Задается как:

$|:Календарь|Calendar

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


История версий

1sqlite
История версий

  • 1.0.1.1

    • Исправлен вылет при попытке подключить таблицу строк документа, у которого нет ТЧ.
    • Добавлены текстовые параметры: :ВидСубконто и :ПланСчетов

  • 1.0.1.2

    • Исправлена работа с преобразованием значений типа Дата в формат БД.

  • 1.0.1.3

    • Исправлена ошибка в метапарсере при обработке вхождений текстовых параметров.