Теория — это когда все известно, но ничего не работает. Практика — это когда все работает, но никто не знает почему. Мы же объединяем теорию и практику: ничего не работает… и никто не знает почему!
Альберт Эйнштейн
Автоматизация 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 может добавлять служебные поля с чекерами или порядковым номером, если указать специальные названия полей:
| Служебное имя | Результат |
|---|---|
| “$checkbox” | Отобразится поле с чекером |
| “$autoinc” | Отобразится поле с порядковым номером строки табличного компонента |
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 содержит весь запрос.