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

             else s1:=s1+s[i];

tables[0].name:=inttostr(c);

//формируем для каждой таблицы  список атрибутов

 

s1:='';

c:=0;

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

begin

s:=GetListAttr(tables[i].name);

for j:= 1 to length(s) do

   begin

    if s[j]=#13 then begin

                     inc(c);

                     tables[i].atribs[c].name:=s1;

                     tables[i].atribs[c].typ:=GetTypeStr(tables[i].name,s1);

                     s1:=''

                     end

                else s1:=s1+s[j];

 

    tables[i].atribs[0].name:=inttostr(c);

   end;

c:=0;

end;

 

usetables[0].name:='0';

end;

 

procedure Tform1.InitCombo;

var i,j,k:integer;

begin

 

for i:=1 to StringGrid2.colcount do

for j:= 0 to 2 do

  begin

   Combo[i,j]:=TComboBox.Create(form1.StringGrid2);

   Combo[i,j].BoundsRect := StringGrid2.CellRect(i,j);

   Combo[i,j].Top:=combo[i,j].Top + StringGrid2.Top + 2;

   Combo[i,j].Left:=combo[i,j].Left + StringGrid2.Left + 2;

   Combo[i,j].Visible:=false;

   Combo[i,j].Parent:=Form1;

   Combo[i,j].Tag:=i;

   Combo[i,j].OnChange:=form1.ComboBoxChange;

 

       case j of

       2: begin

          Combo[i,j].Items.Add('по возрастанию');

          Combo[i,j].Items.Add('по убыванию');

          end;

       end;

  end;

 

for i:=1 to StringGrid2.colcount do

begin

  Check[i]:=TCheckBox.Create(form1.StringGrid2);

  Check[i].BoundsRect := StringGrid2.CellRect(i,3);

  Check[i].Top:=  Check[i].Top  + StringGrid2.Top + 2;

  Check[i].Left:= Check[i].Left + StringGrid2.Left + 2;

  check[i].Caption:=inttostr(i);

  Check[i].Parent:=Form1;

end;

 

end;

 

 

 

procedure TForm1.FormCreate(Sender: TObject);

begin

OpenSysCatFiles;

CreateTableList(Tables);

InitTable;

InitCombo;

ListTables;

 

end;

 

 

procedure tform1.ListTables;

 

var s,a,cur,tem:string;row,i,j:byte;

begin

  cur:='';tem:='';Row:=2;

  s:=GetListTable;

  for i:=1 to Length(s) do

  if s[i]=#13 then

  begin

    StringGrid1.RowCount:=Row;

    StringGrid1.Cells[0,Row-1]:=cur;

    a:=GetListAttr(cur);

    for j:=1 to Length(a) do

    if a[j]=#13 then

    begin

      StringGrid1.Cells[1,Row-1]:=tem;

      StringGrid1.Cells[2,Row-1]:=GetTypeStr(cur,tem);

      if IsPrimaryKey(cur,tem) then StringGrid1.Cells[3,Row-1]:='Primary key';

      StringGrid1.Cells[4,Row-1]:=GetForeignKey(cur,tem);

      inc(Row); StringGrid1.RowCount:=Row;

      tem:='';

    end else tem:=tem+a[j];

    cur:='';

  end else cur:=cur+s[i];

 

 

end;

 

 

procedure TForm1.StringGrid2SelectCell(Sender: TObject; ACol,

  ARow: Integer; var CanSelect: Boolean);

begin

if ARow<3 then

  begin

  //показываем соответствующий combo

   Combo[ACol,ARow].BoundsRect := StringGrid2.CellRect(ACol,ARow);

   Combo[ACol,ARow].Top:=combo[ACol,ARow].Top + StringGrid2.Top + 2;

   Combo[ACol,ARow].Left:=combo[ACol,ARow].Left + StringGrid2.Left + 2;

   combo[ACol,ARow].Visible:=true;

  end;

  if ARow=4 then begin

                //загружаем образ формы в форму3

                i:=ACol;

 

                form3.Edit1.Text:=unit3.frm[i].edit1;

                form3.Edit2.Text:=unit3.frm[i].edit2;

                form3.Edit3.Text:=unit3.frm[i].edit3;

                form3.Edit4.Text:=unit3.frm[i].edit4;

 

 

                case unit3.frm[i].IndexRadio of

                 1: form3.RadioButton1.Checked:=true;

                 2: form3.RadioButton2.Checked:=true;

                 3: form3.RadioButton3.Checked:=true;

                 else begin

                      form3.RadioButton1.Checked:=false;

                      form3.RadioButton2.Checked:=false;

                      form3.RadioButton3.Checked:=false;

                      end;

                 end;

 

                form3.ComboBox1.Text:=unit3.frm[i].combo1;

                form3.ComboBox2.Text:=unit3.frm[i].combo2;

                form3.ComboBox3.Text:=unit3.frm[i].combo3;

 

                form3.setindex(ACol);

                form3.visible:=true;

               end;

form1.caption:='Acol  '+inttostr(acol)+' Arow '+inttostr(Arow);

end;

 

procedure TForm1.Button2Click(Sender: TObject);

begin

form2.Edit1.Clear;

form2.Button2.Enabled:=false;

form2.Button3.Enabled:=false;

form2.ComboBox1.Text:='';

form2.Visible:=true;

end;

 

procedure TForm1.ListBox1DblClick(Sender: TObject);

begin

form2.ComboBox1.Enabled:=false;

form2.Button2.Enabled:=True;

form2.Button1.Enabled:=false;

form2.Button3.Enabled:=true;

form2.ComboBox1.Text:=usetables[listbox1.itemindex+1].name;

form2.Edit1.Text:=usetables[listbox1.itemindex+1].psevdo;

form2.Visible:=true;

end;

 

procedure AddForCom(var str:string);

//добавление внешних ключей

        function GetName(s:string; var s1:string):string;

        var i:integer;

        begin

        result:='';

        i:=1;

        while s[i]<>'.' do

         begin

         result:=result+s[i];

         inc(i);

         end;

        s1:=copy(s,i,length(s));

        end;

 

var s,s1:string;

j,i,k:integer;

begin

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

begin

s1:='';

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

  begin

  s:=getforeignkey(usetables[i].name,usetables[i].atribs[j].name);

  if s<>''

  then

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

        if GetName(s,s1)=usetables[k].name then

        begin

          if usetables[i].psevdo<>'' then str:=str+'('+usetables[i].psevdo+'.'+usetables[i].atribs[j].name+'='

                                     else str:=str+'('+usetables[i].name+'.'+usetables[i].atribs[j].name+'=';

 

          if usetables[k].psevdo<>'' then str:=str+usetables[k].psevdo+s1+')'+'and '

                                     else str:=str+usetables[k].name+s1+')'+'and '; 

        end;

  end;

end;

end;

 

procedure TForm1.Button3Click(Sender: TObject);

 

        function Dif:boolean;

        var i,j:integer;

        begin

        Result:=false;

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

            for j:= 1 to strtoint(UseTAbles[0].name) do

             if (Usetables[i].name<>UseTables[j].name) then result:=True;

        end;

 

var select,from,where,orderby, query:string;

    i,j:integer;

    m:set of byte;

    count:byte;

    f,join:boolean;

begin

m:=[];

 

select:='select ';

 

where:='';

orderby:='';

 

if checkbox1.Checked then select:=select+'distinct ';

count:=0;

for i:= 1 to 20 do if ((combo[i,0].text<>'')and(combo[i,1].Text<>'')and(Stringgrid2.Cells[i,4]='')) then inc(count);

f:=false;

if (strtoint(usetables[0].name)=2)and(count=2)and(Dif) then begin

                                                        f:=true;

                                                        join:=true;

                                                    end;

 

for i:=1 to 20 do

begin

  if check[i].Checked then select:=select + combo[i,0].Text+'.'+combo[i,1].Text+', ';         //select

  if (combo[i,2].Text<>'')and(combo[i,1].Text<>'')then

  if combo[i,2].text='по возрастанию' then orderby:=orderby+combo[i,0].Text+'.'+combo[i,1].Text+', '             //orderby

                                      else orderby:=orderby+combo[i,0].Text+'.'+combo[i,1].Text+'desc, ';

 

  if (combo[i,0].text<>'')and(combo[i,1].Text<>'')and(unit3.frm[i].usl<>'')then where:=where + '('+unit3.frm[i].usl+')'+' and ';

end;

 

for i:= 1 to StrToint(UseTAbles[0].name)do

if f

then

      begin

            if usetables[i].psevdo<>'' then from:=from+' '+usetables[i].name+' as '+usetables[i].psevdo+' '

                                       else from:=from+' '+usetables[i].name+'  ';

            if join then begin

                         from:=from+'full outer join';

                         join:=false;

                         end;

     end

else

    if usetables[i].psevdo<>'' then from:=from+usetables[i].name+' as '+usetables[i].psevdo+', '

                               else from:=from+usetables[i].name+', ';

 

 

select:=copy(select,1,length(select)-2)+' '; //удаление лишних запятых and  и т.д.

from:='from '+copy(from,1,length(from)-2)+' ';

addforcom(where);

if where <>'' then

if f then where:='on '+copy(where,1,length(where)-4)+' '

     else where:='where '+copy(where,1,length(where)-4)+' ';

 

if orderby<>'' then orderby:='order by'+copy(orderby,1,length(orderby)-2)+' ';

 

query:=select+from+where+orderby;

memo1.Clear;

memo1.Lines.Add(query);

end;

 

procedure TForm1.Button4Click(Sender: TObject);

begin

memo1.Lines.Add(getforeignkeys(combo[1,0].text));

end;

 

end.

 

unit Unit2;

 

interface

 

uses

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

  Dialogs, StdCtrls, unit1;

 

type

 

  TForm2 = class(TForm)

    ComboBox1: TComboBox;

    Edit1: TEdit;

    Button1: TButton;

    Button2: TButton;

    Button3: TButton;

    procedure FormCreate(Sender: TObject);

    procedure Button1Click(Sender: TObject);

    procedure FormActivate(Sender: TObject);

    procedure Button2Click(Sender: TObject);

    procedure Button3Click(Sender: TObject);

  private

    { Private declarations }

  public

    { Public declarations }

  end;

 

var

  Form2: TForm2;

  prev:string;

implementation

 

{$R *.dfm}

 

procedure TForm2.FormCreate(Sender: TObject);

var i,k:integer;

begin

k:=strtoint(unit1.Tables[0].name);

for i:= 1 to k do

combobox1.Items.Add(unit1.tables[i].name);

end;

 

procedure TForm2.Button1Click(Sender: TObject);

var k,i,j:integer;

f:boolean;

begin

//Добавление в UseTAble

f:=true;

k:=strtoint(unit1.UseTables[0].name);

 

j:=form1.findTable_n(combobox1.text,Tables);

 

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

  begin

  if (usetables[i].name=combobox1.Text)and(usetables[i].psevdo='') then showmessage('повторяющаяся таблица не имеет псевдонима');

 

  if (usetables[i].psevdo=edit1.Text)then

          if ((edit1.Text='')and(usetables[i].name=combobox1.Text))or(edit1.Text<>'')then

           begin

           showmessage('псевдоним не уникален');

           f:=false;

           end;

 

  end;

 

 

if f then             //если добалвен уникальный псевдоним

begin

inc(k);

unit1.UseTables[k]:=unit1.tables[j];

unit1.UseTables[k].psevdo:=edit1.Text;

unit1.UseTables[0].name:=inttostr(k);

form1.ListBox1.Items.Add(unit1.UseTables[k].name+'  :  '+unit1.UseTables[k].psevdo);

 

for i:= 1 to 20 do

if edit1.Text='' then unit1.combo[i,0].items.add(usetables[k].name)

                  else unit1.combo[i,0].items.add(usetables[k].psevdo);

 

form2.Visible:=false;

end;

 

end;

 

procedure TForm2.FormActivate(Sender: TObject);

begin

prev:=edit1.Text;