Классика баз данных - статьи

Деревянный интерфейс


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

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

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

На приведен пример работающего приложения.

Это приложение напоминает всем известный Проводник в операционной системе Windows. Ничего оригинального в плане внешнего вида здесь нет, оригинальность идеи заключается именно в самой методике построении дерева. Как уже было указано – дерево динамическое. То есть, оно строится на основании информации содержащейся в базе данных. Структура такого дерева хранится в отдельной таблице, и, что очень важно, эта таблица не содержит самих данных, она хранит только структуру представления данных. Повторюсь еще раз – хранится только структура представления данных, которая не содержит самих данных. Данные, которые нужно отображать лежат в своих таблицах и ничего не знают о том, как их будут отображать и не имеют каких либо связей со структурой отображения.

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

Проще всего пояснить работу такого дерева на примере. Рассмотрим формирование дерева на примере списка трестов (см. ).

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

select 5 as IdType, ‘Id_Trust’ as IdName, Id_Trust as IdValue, to_char (Id_Trust) ' - ' name as Name ‘Trust’ as Icon from Main.Trust order by Id_Trust asc



Приведенный SQL запрос выбирает данные из таблицы Main.Trust, в которой содержатся данные по трестам. Поясним значения полей:



  1. Первое поле IdType – идентификатор добавляемых элементов. То есть это идентификатор элемента в таблице структуры. Это поле сообщает приложению, элементы какого типа будут добавлены в дерево в качестве дочерних элементов. В данной реализации это поле числовое, но для более удобного использования может быть символьным, и принимать осмысленные значения, например ‘Trust’ as IdType.
  2. Второе поле IdName - символьное и содержит название идентификатора объекта. На первый взгляд это поле избыточно, но на самом деле оно необходимо и его роль мы рассмотрим чуть ниже.
  3. Третье поле IdValue содержит значение уникального идентификатора узла. В данном случае треста - Id_Trust . Это поле является первичным ключом таблицы трестов и его значение позволяет однозначно идентифицировать конкретный трест.
  4. Четвертое поле Name – текстовая строка, которая содержит название объекта. Именно эта строка будет показана в дереве как название элемента.
  5. Пятое поле Icon - название иконки, которая будет показана в качестве иконки элемента.
  6. Так же может присутствовать поле IdRight, которое определяет номер права для отображения конкретного элемента.




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

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

Опускаемся по дереву чуть ниже, теперь нам нужно получить список участков для конкретного треста. В этом случае в элементе Участки хранится такой SQL запрос:



select 10 as IdType, ‘Id_Site’ as IdName, Id_Site as IdValue, to_char (Code) '->' Name as Name, ‘Site’ as Icon from Main.Site where Id_Trust=[Id_Trust] order by Code

Обратите внимание на конструкцию [Id_Trust]. Такой конструкции нет в SQL, она была введена искусственно. Приложение перед отправкой запроса на сервер анализирует текст запроса и заменяет эту конструкцию на значение элемента с указанным именем находящееся выше по дереву. Именно по этому каждый элемент дерева хранит не только значение, но и название идентификатора объекта. Это позволяет найти объект нужного типа и получить его значение. В таком случае при развороте элемента трест с Id_Trust = 55, в SQL запрос на получение списка участков конкретного треста будет подставлено where Id_Trust=55, то есть конструкция [Id_Trust] будет заменена на значение конкретного треста внутри которого мы находимся в дереве. При таком подходе появляется возможность использовать значения любых элементов находящихся вверх по дереву (всех родителей) по отношению к текущему элементу. Вместо подстановки значений в текст SQL запроса можно использовать связанные переменные, выражения или использовать представления, что повышает эффективность выполнения запросов, но в данном случае нас интересует сама методика построения дерева, а не конкретный способ реализации. Поэтому вопросы оптимизации мы затрагивать не будем. Например, условие на выборку мастеров, находящихся внутри участка может иметь вид:

where Id_Trust =[Id_Trust] and Id_Site = [Id_Site] and .

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



Помимо явного указания значения объекта по имени в SQL – запросе для удобства дополнительно вводятся предикаты current – что означает значение текущего элемента и parent - значение родителя уровня n, например parent1 – вернет значение родителя указанного элемента, parent2 – родителя родителя и т.д. В таком случае соответственно parent0 = current.

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



Рис 3. Выбор способа представления.

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


Сама таблица, в общем виде, состоит из трех полей Id_Right – уникальный идентификатор права, Parent – ссылка на родительское право и Name – наименование права. В этом случае раздел дерева Право для получение корневых элементов дерева прав будет иметь SQL запрос такого вида:

select 7 as IdType, ‘Id_Right’ as IdName, Id_Right as IdValue, Name as Name, ‘Right’ as Icon from Right.Right where Parent is null

В этом SQL запросе в качестве дочерних элементов выбираются корневой список прав у которых Parent is null. Далее элемент Право должен иметь запрос такого вида:

select 7 as IdType, ‘Id_Right’ as IdName, Id_Right as IdValue, Name as Name, ‘Right’ as Icon from Right.Right where Parent = [current]

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

Используя возможности SQL по объединению результатов SQL-запросов можно формировать элементы разного типа на одном уровне дерева. Именно в этом случае необходимо поле IdName для того, что бы можно было для элементов разного типа задавать соответствующие им названия идентификаторов.

Например, при отображении прав можно сделать SQL запрос такого вида:

select 7 as IdType, ‘Id_Right’ as IdName, Id_Right as IdValue, Name as Name, ‘Right’ as Icon from Right.Right where Parent = [current]

union all

select 8 as IdType ‘ListRight’ as IdName, null as IdValue, ‘Обладатели права’ as Name, ‘ListRight’ as Icon from Dual

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

select 8 as IdType ‘ListRight’ as IdName, null as IdValue, ‘Обладатели права’ as Name, ‘ListRight’ as Icon from Dual where 1 in (select 1 from Main.RightList where Id_Right = [IdRight])



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

Помимо этого отображением элементов управляет система прав. Так, например, любому элементу дерева может быть назначено право. Если элементу право не назначено, то он отображается всем пользователям, а если право указано, то этот элемент будет показан только тем пользователям, которые обладают указанным правом. Благодаря этому получается, что одно и то же дерево для разных пользователей отображается по разному и каждый пользователь видит только ту часть дерева, которая нужна ему для выполнения именно его функций. В существующей системе права могут быть заданы на уровне пользователя, на уровне структурного подразделения, на уровне группы. Используемая нами система управления правами аналогична упрощенной системе назначения прав в дереве каталогов программных продуктов Novell и мы не будем на ней останавливаться подробно. Скажем только, что управление правами и пользователями так же осуществляется с использованием предлагаемой методики.

В большинстве случаев отображаемые данные можно разделить по группам таким образом, что бы количество элементов в каждом узле не превышала 500 элементов, так как при большем количестве довольно сложно найти нужный элемент. Однако существуют объекты, количество которых велико и при этом их нельзя разбить на группы или такая разбивка не очень удобна. Например, список лицевых счетов или список номеров телефонов. Отображать их в предлагаемом виде не представляется возможным из-за большого количества. Конечно, можно разбить лицевые счета по группам, например, сначала отображается первая цифра счета, затем вторая и т.д. или разбить по диапазонам, но в данном случае это не удобно. Для отображения таких данных используется небольшая модификация предлагаемой методики. В этом случае используется вспомогательная таблица, которая хранит список объектов, с которыми работает пользователь. То есть, изначально список объектов указанного элемента дерева пуст.


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

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

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


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

Чуть подробнее остановимся на формировании свойств объектов в виде html странички как наиболее удобного способа отображения информации. В нашей реализации Html страничка формируется средствами привычными для разработчика Oracle на языке PLSQL с использованием собственных пакетов. При этом страничка может содержать интерактивные элементы – кнопки, выпадающие списки. Например, нажав на кнопку можно запустить формирование отчета, форму, выполнить процедуру и т.д.. Можно, например, выбрать месяц отображения информации. В этом случае, при изменении месяца будут переформированы и отображены данные выбранного месяца, и система запомнит, с каким месяцем работает конкретный пользователь, и при повторном отображении в первую очередь отобразит именно этот месяц. Процедуры отображения информации назначаются каждому элементу дерева и могут находится в пакетах базы данных. В качестве примера, приведем текст процедуры отображения информации по счетчикам:

procedure Html (IdCounter in Main.Counter.Id_Counter%type) is lPrivilegeName vPrivilege.FullName%type; begin pHtml.SetColumn ('<td align="right" class="style_green">', '</td>', 2, false);

pHtml.TableFromQuery ('select "<b>Счетчик</b>", ' '"<b>" Name "</b>" ' 'from Main.vCounter ' 'where Id_Counter = ' to_char (IdCounter), IsHeader => false); pHtml.AddText ('<p>'); pHtml.TableFromQuery ('select ET.Name as "Обслуживаемые обьекты", (select count(*) from Finance.vEntityAcct EA where EA.Id_Entity = C.Id_Entity) as "Кол-во счетов" from Finance.vEntityTree ET, Main.vCounter C where ET.Id_Entity = C.id_entity and C.Id_Counter = ' to_char (IdCounter), IsHeader => true); end;



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

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

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

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

Описанная методика реализована с использованием базы данных Oracle 8i и средства разработки Oracle Developer 6i и успешно эксплуатируется более трех лет на двух крупных предприятиях – биллинговая система телекоммуникационной компании и центр начислений жилищно-коммунального хозяйства города.Внедрение этой методики позволило существенно увеличить скорость разработки приложений и значительно сократить время реакции системы на пожелания пользователей.

В настоящее время автором этой методики создана аналогичная система в сети Интернет с использованием веб-сервера Apache, языка программирования PHP и базы данных MySQL. Основная часть этой системы запущена, прекрасно себя зарекомендовала и активно развивается.


Содержание раздела