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

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

Проблематика

Итак, вы – пользователь, имеющий право на чтение в некой СУБД. Вероятно, перед вами стоит набор типовых подзадач:

• Разобраться со структурой данных
• Найти в ней нужные сущности
• Найти в них нужные поля
• Найти связи между сущностями
• Найти интересующие значения
• Отобрать набор значений
• Выбрать нужные данные
• Убедиться, что это действительно ТЕ САМЫЕ данные, которые вы искали
• Сохранить результаты
• Подготовить из них отчеты

Наконец, весьма вероятно, что эти задачи вам надо решать регулярно.

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

• Редко где встретишь возможность зафиксировать найденные знания, каждый новый запрос – как с чистого листа
• Ранее подготовленные запросы можно поднять только в
  чистом SQL
• Структура запроса (поля, фильтры, группировки) тяжело изменяема. Как правило, цена быстрым изменениям – потеря целостности или остаточная избыточность
• Подготовка фильтров производится вслепую
• Связи между сущностями подлежат ручному указанию
• На пользователя вываливается вся доступная ему схема БД

Все это на фоне отсутствия документации по структуре. Несмотря на то, что она находится вне инструмента, проблема касается инструментов напрямую.

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

Решение

Комплексное решение обозначенных проблем в программе «Свобода Выборки» ведется по нескольким направлениям одновременно. Текст этого раздела детально описывает примененные концепции и нюансы реализации

Понятная и удобная структура данных

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

Отличить необходимое от полностью лишнего – одна из первых задач, решаемых нашим инструментом.

Базы данных и таблицы

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

В полном списке таблиц можно найти интересующие поля по подстроке, например:

Контекст

Контекст сущности/таблицы – понятие, близкое юниверсам Business Objects, но более широкое. Естественно исходить из предположения, что пользователя не интересуют таблицы, не связанные с выбранной. При этом было бы ошибкой думать, что в контекст входят только непосредственно связанные таблицы – поэтому в контексте мы можем видеть вложенность любого уровня:

Узлы – это сущности, листья – это поля. Связанные сущности показываются рядом с полями той сущности, с которой у них связь. Множественные связи на одну и ту же сущность различаются полями, через которые указана ссылка:

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

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

Распознавание

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

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

Определение

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

Мы можем их определить самостоятельно. Для этого есть несколько способов.

Простейший – это разовая связка по одному полю, через контекстное меню и диалог (в отдельных случаях это можно сделать через drag’n’drop полей друг на друга):

Более сложный это связка по нескольким полям:

Наконец, высший пилотаж – это многократная типовая связь всех нужных таблиц:

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

Все эти связи будут сохранены в локальной «схеме», существующей как дополнение к автоматически распознаваемым ссылкам через драйвер к БД, и впредь будут определять контексты наравне с ними.

Прочие возможности

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

Прекрасно, но для чего все это?!

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

Удобство построения запроса

Мы сделали первый шаг для удобной работы – отделили важное от второстепенного. Теперь наша задача – упростить отбор насущного из доступного.

На ходу

Наша задача – раскрывать контекст на нужную глубину и выбирать требуемые поля. Таблицы – это лишь  способ навигации к ним, их указание не требуется совсем. Типовые операции с полями в запросе – добавление в выборку, добавление в фильтр и добавление в выражение (в выборке или фильтре).

На рисунке выделены три кнопки-иконки типовых действий и показан результат одного из них (добавление в фильтр) на примере поля Name таблицы ServiceSubGroup, на которую ссылается таблица Service:

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

…И добавить поле из глубины контекста (выполнено через drag’n’drop):

…найти максимальное значение поля в группах и количество строк:

…затем перегруппировать поля:

…сделать запрос (доопределение фильтра опущено, о нем позже):

…временно убрать из выборки одно поле и разгруппировать другое:

…сделать новый запрос и увидеть более интересный результат:

Все это и многое другое теперь мы может делать практически минимумом нажатий.

Объектная модель

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

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

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

Затем мы хотим сгруппировать только по подгруппе, для этого «прячем» название услуги:

В запросе Shortname исчезло из обеих частей (select, group by).

Наконец, уберем условие отбора (опять же, временно):

В запросе не только полностью исчезла секция where, но и стало на один join меньше, поскольку поля таблицы ServiceGroup нам больше не нужны.

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

Редактор выражений

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

Выход достаточно простой: надо продолжать использовать поля как объекты, а весь остальной богатейший синтаксис (кроме стандартных агрегатных функций) всех различных СУБД отдать на откуп пользователю. То есть все, что не является полями, он напишет текстом, плюс есть возможность сослаться или не сослаться на поля в «правильном стиле».

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

На рисунке можно видеть, что мы составили разность между суммой сессии и суммой сумма операций (в надежде, что она даст 0, как и должна) и поделили все это на 100 (суммы изначально в копейках). В данном выражении шесть управляемых и различаемых инструментом объекта: открывающая скобка, два поля, агрегатная функция и две закрывающие скобки. Скобки вынесены в управляемые объекты для упрощения контроля их парности (при удалении скобки с одной стороны автоматически удаляется скобка/функция с другой) и возможности проверки вхождения поля в агрегатное выражение. Все остальные символы: —,/,1,0,0 будут переданы на сервер «как есть» и для инструмента они не значат ничего.

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

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

Максимально удобная фильтрация

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

Задание фильтров в несколько кликов

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

В контейнер есть несколько способов добавить фильтр:

• Создать новый, пустой
• Нажать на кнопку «в фильтр» в схеме
• Перетащить поле мышью из схемы
• Перетащить в контейнер выражение из выборки (будет создан фильтр с копией выражения)

В итоге мы получим что-то вроде (фильтры лишь набросаны, но все не-доопределены):

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

Предиктивный выбор значений

Независимо от сложности выражения в левой части, у нас есть возможность быстро разобраться во множестве возможных значений, которые мы собираемся фильтровать. Сделаем наше выражение чуть более броским, и посмотрим, что нам предложит «справочник»:

Справочник старательно выдал нам все комбинации для данного выражения.

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

Они расположены по возрастанию длительности запроса.

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

«Ожидаемые с фильтром» учитывают наложенные фильтры, тем самым дополнительно сужая варианты. Естественно, текущий фильтр при этом отбрасывается.

Удалив из предыдущего примера пустой фильтр, и поставив отбор «Ожидаемые с фильтром», мы увидим следующий результат:

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

Группировка, перегруппировка и отключение фильтров

Мы пока рассмотрели простейший набор из нескольких простых фильтров. Однако панель позволяет группировать их в любые комбинации, выполнять над ними операции объединения по признаку «и», «или», отрицать. Это делается при помощи жеста drag’n’drop и кнопок на панели  инструментов. Каждый фильтр можно отключить, даже корневой. Можно, скажем, создать такую композицию:

Добротно и на века

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

Сохранение конфигурации связей

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

Восстанавливаемая структура запроса

Созданный запрос можно сохранить и перенести как полноценный «документ»: при открытии в другое время или в другом месте он будет восстановлен во всех мелочах. Единственное естественное ограничение – соединение с базой данных должно быть доступно и ее структура должна совпадать с точностью до использованных полей. Перенос настроек контекста не требуется, копии нужных связей прописаны в документе.

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

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

Автосохранение и восстановление сессии

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

Направления развития

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

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

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

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