Визуальный построитель запросов на извлечение

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

При задании условия сравнения, пользователь выбирает знак (>,<,=, >=, <=) и тип значения, с которым будет сравнивать поле.

Этот тип задается в выпадающем списке с полями “поле” и “значение”.

На форме существуют 2 элемента в одном месте, поле ввода и выпадающий список.

Если для сравнения было выбрано  "поле”, то поле ввода станет невидимым, а в выпадающем списке появятся все поля используемых отношений (в формате имя/псевдоним_таблицы . имя_атрибута), с которыми можно сравнить текущий атрибут, то есть все атрибуты, которые по типу  соответствуют текущему, для этого просматривается весь массив UseTAbles.

Если для сравнения было выбрано  "значение”, тогда видимым станет поле ввода, и введенное значение воспримется в зависимости от типа атрибута в апострофах или без них.

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

Достигается это следующим образом.

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

TImageForm=record

           IndexRadio:integer;

           edit1,edit2,edit3,edit4:string;

           combo1,combo2,combo3:string;

           usl:string;

           end;

  frm:array[1..20] of TImageForm;

 При нажатии кнопки “Оk” в соответствующий элемент массива заносятся текущие данные с формы, а при выборе ячейки таблицы отвечающей за условие, значения из “образа формы” передаются её полям.

Массив frm изначально инициализируется нулями.

В записи TImageForm предусмотрено поле usl, которое не относится непосредственно к внешнему виду формы. В нем хранится условие, которое задается данной формой.

Формирование этой строки происходит в соответствии с выбранным переключателем Radiobutton и составляется из служебных слов (таких как like и between) и значений полей ввода.

 

 

3. Алгоритм построения  запроса

Итак, когда заданы все  условия, выбраны отношения и  атрибуты, программа строит сам запрос.

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

Однако этому предшествует несколько проверок.

Программа построена таким образом, что если выбраны отношения, атрибуты на вывод, но не задано ни одного условия, это воспринимается как попытка  построить внешнее полное соединение (outter full join). Внешнее соединение строится на основе заданных внешних ключей только в том случае, когда задано 2 разные таблицы и ни задано условий. Но если заданы 2 одинаковые таблицы с разными псевдонимами, то строится обычный запрос с разделом where.

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

 Если все-таки строится обычный  запрос, то в цикле, параллельно  строятся все разделы запроса.

Итерация цикла происходит следующим образом:

Если в текущем просматриваемом  столбце поставлена галка, то этот атрибут  добавляется в переменную select : string в формате имя/псевдоним_таблицы . имя_атрибута.

Если третья строка не пуста, то если в зависимости от её значения в  переменную orderby : string добавляется имя/псевдоним_таблицы. имя_атрибута и если выбрано упорядочивание по убыанию добавляется служебное слово desc.

Формирование раздела where зависит от типа строящегося запроса,  и если строится внешнее соединение, этот раздел начинается с “on”, иначе с “where”. Далее просматривается соответствующий итерации элемент массива frm, в котором содержатся условия, и последовательно добавляются в переменную where , разделенные скобками и словом “and” (в случае с внешним соединением эти элементы просто будут пустыми).

После окончания цикла в раздел where необходимо добавить связи по внешним ключам (процедура AddForCom). Для этого просматривается весь список используемых отношений UseTables, и для каждого её атрибута вызывается функция getforeignkey из модуля SysCat. Но Эта функция вернет имя таблицы и имя атрибута, а если была задействована не одна подобная таблица, то необходим псевдоним, поэтому приходится  еще раз просматривать UseTAbles и добавлять в where связи со всеми таблицами с заданным именем, то есть каждый внешний ключ связывается со всеми таблицами, с этим именем.

Формирование раздела from идет отдельно и зависит от того, как прошли проверки на внешнее соединение. В from добавляются все таблицы и псевдонимы UseTAbles. Если строится внешнее соединение, то ожидается, что в этом разделе будет всего два имени (имя_таблицы as псевдоним) разделенных фразой “outer full join”.

Если стоит галка  на CheckBox “Исключая дубли”, то к разделу select добавится слово distinct.

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

 

4. Тестирование и пример работы программы

Допустим имеется 2 отношения: Сотрудник (Номер_Удостоверения, ФИО, Адрес), и Должности (Номер_сорудника, Должность, зарплата), причем  Должности.Номер_сотрудника является внешним ключом для Сотрудник.Номер_удостоверения.

Пусть нам необходимо найти сотрудника, который получает больше остальных среди всех, чье ФИО начинается на “Ъ”.

Для решения нам будет  необходимо добавить одну таблицу Сотрудник  и 2 таблицы Должности (с разными  псевдонимами). Отношению сотрудник  псевдоним можно не задавать.

Рисунок 4 - Добавление таблиц для построения запроса

Зададим условие на атрибут  Сотрудник.ФИО.

Рисунок 5 - Условие на атрибут Сотрудник.ФИО

 

Зададим Условие на t1.зарплата.

 

 

 

Рисунок 6 - Условие на t1.зарплата

 

Поставим галку исключая дубли.

Рисунок 7.  Ограничение  на исключение дублей.

 

В результате, получился  следующий запрос:

select distinct Сотрудник.ФИО  from Сотрудник, Должности as t1, Должности  as t2 where (Сотрудник.ФИО like 'Ъ%') and (t1.Зарплата>=t2.Зарплата) and (Сотрудник.Номер_удовстоврения=t1.Номер_сотрудника)and (Сотрудник.Номер_удовстоврения=t2.Номер_сотрудника).

 

Заключение

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

 

 

Список литературы

1. Хомоненко А.Д. Работа с базами данных в DELPHI, 3-е издание // А.Д. Хомоненко, В.Э. Гофман – СПб.: БХВ-Петербург 2005,  – 623 с.
2. Хомоненко А.Д. Базы данных // А.Д. Хомоненко– СПб.: Корона-Принт 2004.
3. Microsoft Access 2002 Шаг за Шагом// - Москва – Эком 2002.

 

 

 

 

Приложение

unit Unit1;

 

interface

 

uses

  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

  Dialogs, StdCtrls, Grids, syscat, ExtCtrls;

 

type

  TAtrib = record

           name,typ:string;

           end;

  TTables = record

            name, psevdo,foreign_key,primary_key:string;

            atribs:array [0..10] of TAtrib;

            end;

  TArTables = array [0..10] of TTables;

 

  TForm1 = class(TForm)

    StringGrid1: TStringGrid;

    StringGrid2: TStringGrid;

    ListBox1: TListBox;

    Button2: TButton;

    Button3: TButton;

    Memo1: TMemo;

    CheckBox1: TCheckBox;

    procedure FormCreate(Sender: TObject);

    procedure ComboBoxChange(sender : Tobject);

    procedure InitTable;

    procedure InitCombo;

    function findTable_n(name:string;tables:TArTables):integer;

    function findTable_p(psevdo:string;tables:TArTables):integer;

    function FindTable(s:string;tables:TArTables):integer;

    procedure ListTables;

    procedure CreateTableList(var tables:TArTables);

    procedure StringGrid2SelectCell(Sender: TObject; ACol, ARow: Integer;

      var CanSelect: Boolean);

    procedure Button2Click(Sender: TObject);

    procedure ListBox1DblClick(Sender: TObject);

    procedure Button3Click(Sender: TObject);

    procedure Button4Click(Sender: TObject);

 

  private

    { Private declarations }

  public

    { Public declarations }

  end;

 

var

  Form1: TForm1;

  Combo : array [1..20,0..2] of TCombobox;

  Check : array [1..20] of TCheckBox;

  Btn : array [1..20] of tButton;

 

  Tables, UseTables: TArTables;

 

implementation

 

uses Unit2, Unit3;

 

{$R *.dfm}

 

// поиск BOxa в массиве

procedure findCombo(sender : TObject; var x,y:integer);

var i,j:integer;

begin

for i:=1 to 20 do

for j:= 0 to 2 do

if sender=combo[i,j] then begin x:= i; y:= j; end;

end;

//поиск таблицы по имени

function tform1.findTable_n(name:string;tables:TArTables):integer;

var i:integer;

begin

result:=0;

for i := 1 to strtoint(tables[0].name)do

if tables[i].name=name then begin

                             result:=i;

                             break;

                             end;

end;

 

//поиск таблицы по псевдониму

function tform1.findTable_p(psevdo:string;tables:TArTables):integer;

var i:integer;

begin

result:=0;

for i := 1 to strtoint(tables[0].name)do

if tables[i].psevdo=psevdo then begin

                                 result:=i;

                                 break;

                                 end;

end;

 

function tform1.FindTable(s:string;tables:TArTables):integer;

begin

if findTable_p(s,tables)=0 then result:=findTable_n(s,tables)

                           else result:=findTable_p(s,tables)

end;

 

 

procedure Tform1.ComboBoxChange(sender : TObject);

var k,x,y,i:integer;

CurCombo:TCombobox;

begin

 

findCombo(sender,x,y);

CurCombo:=Combo[x,y];

k:=findTable_p(CurCombo.Text,usetables);

if k=0 then k:=findTable_n(CurCombo.Text,usetables);

//загружаем в combo второй строки  атрибуты из таблицы

case y of

     0: begin

        if CurCombo.Text<>'' then

           begin

            combo[x,y+1].Clear;

            for i :=1 to strtoint(usetables[k].atribs[0].name)do

            combo[x,y+1].Items.add(usetables[k].atribs[i].name);

           end;

        end;

     end;

 

 

stringgrid2.Cells[x,y]:=(sender as TCombobox).Text;

(sender as TCombobox).Visible:=false;

end;

 

 

procedure Tform1.InitTable;

begin

// инициализация надписей

Stringgrid1.Cells[0,0]:='Таблица';

Stringgrid1.Cells[1,0]:='Атрибут';

Stringgrid1.Cells[2,0]:='Тип';

Stringgrid1.Cells[3,0]:='Перв. ключ';

Stringgrid1.Cells[4,0]:='Внешний ключ';

 

stringgrid2.Cells[0,0]:='   таблица';

stringgrid2.Cells[0,1]:='      поле';

stringgrid2.Cells[0,2]:='сортировка';

stringgrid2.Cells[0,3]:=' результат';

stringgrid2.Cells[0,4]:='   условие';

end;

 

procedure tform1.CreateTableList(var tables:TArTables);

var s,s1:string;

c,i,j:integer;

begin

c:=0;

s:=GetListTable;

s1:='';

//находим имена таблиц

for i:= 1 to length(s) do

if s[i]=#13 then begin

                  inc(c);

                  tables[c].name:=s1;

                  s1:=''

                  end