Использование SQL в MVDB

Теория — это когда все известно, но ничего не работает. Практика — это когда все работает, но никто не знает почему. Мы же объединяем теорию и практику: ничего не работает… и никто не знает почему!

Альберт Эйнштейн

Автоматизация My Visual Database

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

Рассмотрим в качестве примера простую таблицу, содержащую сведения о товарах (item): название (name), артикул (code) и цену (price).

Чтобы отобразить данные из таблицы item достаточно указать в мастере настройки таблицы отображаемые данные (2) и источник данных (1) – основные параметры команды SELECT:

В результате для получения данных автоматически формируется SQL-запрос:

SELECT 'item'.'code', 'item'.'name', 'item'.'price', 'item'.id FROM itemCode language: JavaScript (javascript)

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

В результате формируется запрос

SELECT 'doctype'.'name', 'doctype'.id FROM doctypeCode language: JavaScript (javascript)

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

CREATE TABLE item (
    id           INTEGER PRIMARY KEY ASC AUTOINCREMENT,
    name         TEXT,
    code         TEXT,
    price        REAL
);

📝 Вторая особенность внутренних запросов MVDB – оборачивание названий таблиц и полей кавычками, что позволяет защитить работу от ошибок пользователя, например, в случае использования в качестве имен таблиц и полей служебных слов SQL.

Настройка подключения к БД

При запуске приложения, настроенного на работу с СУБД SQLite, происходит автоматическое подключение к файлу базы данных. По умолчанию этот файл располагается рядом с исполняемым файлом. Для подключения к другому файлу можно использовать стандартный пункт меню:

Затем изменить тип расположения файла БД (1), выбрать другой существующий файл (2) и сохранить результат (3).

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

[Options]
DBMS=sqlite
server=E:\MVD\Teach\Продвинутый уровень\bases\sqlite.db

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

Вычисляемые поля

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

☝ Вычисляемое поле не хранится в базе данных, но позволяет отображать необходимую информацию с помощью визуальных компонент My Visual Database. 

Как же работают вычисляемые поля? My Visual Database вставляет написанный вами SQL-код  в конструкции запросов SELECT, которые он генерирует на основании описания настройки таблицы. Рассмотрим это на простом примере.

У нас есть таблица с одним текстовым полем и одним вычисляемым полем:

Вычисляемое поле добавляем двойные кавычки к тексту:

Для отображения данных создадим табличное представление:

В результате My Visual Database сформирует запрос:

SELECT DISTINCT 
  "Test"."text" as "Test.text",
  ( '"' || test.text || '"' ) AS 'Test.q_text',
  "Test".id 
FROM "Test"Code language: PHP (php)

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

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

<SQL-конструкция> AS <название поля>Code language: HTML, XML (xml)

Для добавления вычисляемого поля на вкладке “Таблицы базы данных” находим нужную таблицу, к которой будет привязано поле (привязка необходима для работы визуальных компонент My Visual Database: таблиц и выпадающих списков). Затем нажимаем кнопку “Новое поле” (1), а в выпадающем списке выбираем “Вычисляемое поле” (2)

Откроется окно конструктора, в котором необходимо ввести название поля (1), выбрать его тип (2) и написать часть SQL-запроса (3). Этот текст в дальнейшем автоматически будет вставлен системой в основной SQL-запрос, после ключевого слова SELECT, в раздел “Данные”.

📝 Для версии 6.2. тип ДЕНЬГИ работает так же, как тип ВЕЩЕСТВ.ЧИСЛО – то есть без форматирования в денежный формат. Для форматирования необходимо использовать скрипты, использование которых будет рассмотрено в третьей части курса.

После окончания ввода сохраняем результат (4). Также имеется кнопка (5), с помощью которой можно добавить в текст названия полей или функций:

Более подробно функции будут рассмотрены в отдельном разделе.

☝ Для версии MVDB 6.2. отображаются названия функций только для SQLite.

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

Чтобы отобразить в одном поле одновременно код (code) и название (name), добавим вычисляемое поле с полным названием (fullname). Для соединения строковых значений используем две вертикальные черты:

☝ Хотя синтаксис SQL допускает написание имени поля без указания таблицы, всегда пишите полное имя поля: <имя таблицы>.<имя поля> . Это позволит избежать ошибки неоднозначности определения имен в тексте запроса (ambiguous name).

При настройке выпадающего списка указываем источник данных (1) и созданное нами вычисляемое поле (2).  

В результате получаем одновременное отображение артикула и кода:

Если вы затрудняетесь сразу написать нужный текст запроса, можно сделать “заглушку” и написать комментарий, поясняющий, что здесь должно быть реализовано:

Это позволит использовать данное вычисляемое поле в таблицах, не вызывая ошибку построения SQL-запроса. Кроме того, если вы оставите поле “Выражение” пустым, то при следующем открытии проекта данное вычисляемое поле исчезнет из списка полей таблицы.

Фильтрация данных

Механизмы фильтрации данных для таблиц и выпадающих списков также реализованы на языке SQL. Тема обширная и будет рассмотрена в отдельном разделе.

Кнопка с SQL-запросом

В палитре компонентов My Visual Database для выполнения большинства действий используется кнопка (1), которая подробно описана в разделе “Волшебная кнопка“. Напомню, что тип действия кнопки определяется её свойством Action. Самое время узнать, что можно сделать с помощью данной кнопки (2), если выбрать Action = [SQL ЗАПРОС] (3).

Появится форма настройки кнопки:

Кроме поля для ввода SQL-команды (1) на форме расположены дополнительные элементы, которые помогут с написанием: кнопка (2) открытия Expression Editor, который описан выше; ссылка (3) на форум My Visual Database, где можно найти примеры написания SQL-команд или задать вопрос сообществу разработчиков; ссылка (4) на документацию SQLite, в частности – на список ключевых слов языка SQL. 

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

Если SQL-команда (например, SELECT) возвращает данные, то для их отображения  можно указать табличный элемент интерфейса (6), размещенный на той же форме, что и кнопка. Вы также можете указать подписи полей, разделив их запятой. 

☝ Для запросов, в которых выражение FROM встречается несколько раз, необходимо указать главную таблицу (7) . Это связано с реализацией внутренних алгоритмов My Visual Database, в частности – определение редактируемой таблицы при открытии формы редактирования по первому вхождению данного выражения.

Не забудьте сохранить результат настройки кнопкой ОК (8).

Отобразим с помощью запроса содержимое таблицы item. Введите в редакторе приведенный ниже SQL-запрос, выберите таблицу для отображения и сохраните настройки. Обратите внимание на то, что последняя колонка содержит идентификатор записи. Это может понадобиться в случае, если вы планируете использовать стандартные возможности MVD для редактирования табличных данных – кнопки с действиями [НОВАЯ ЗАПИСЬ], [ПОКАЗАТЬ ЗАПИСЬ] и [УДАЛИТЬ ЗАПИСЬ]

☝ Если вы хотите скрыть какую-то колонку, то необходимо в качестве подписи к данной колонке написать  delete_col

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

Подстановка данных из формы

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

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

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

Отображение служебных полей

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

SELECT
  "$checkbox",
  "$autoinc",
  data
FROM
  test
Code language: JavaScript (javascript)

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

Сложная сортировка

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

Более подробно сортировка описана в отдельном разделе.

SQL Executor

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

Разместите на форме мемо-поле, кнопку и таблицу (1). Выберите кнопку (2) и настройте её свойство Action (3) – выберите действие SQL-запрос (4), а в поле для ввода запроса наберите имя мемо-поля, заключённое в фигурные скобки (5); выберите таблицу для отображения результата (6) и сохраните настройки (7).

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

☝ Для обозначения строковых значений в данном приложении необходимо использовать только двойные кавычки. Использование одинарных кавычек приводит к их удвоению. Эта встроенная логика оправдывает себя при использовании значения из поля Memo в качестве значения в SQL-запросе, но приводит к сбою, если Memo содержит весь запрос.

А теперь немного практики – создайте своё приложение и подключите его к базе данных!