main.~pas

一个最新的解析SQL语句的代码

unit main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls,contnrs,
  gsqlparser,
  sqlstatement,
  lzbasetype,
  lzexpression;

type
  TForm1 = class(TForm)
    mmsqlinput: TMemo;
    mmsqloutput: TMemo;
    btnconvert: TButton;
    btnexit: TButton;
    Label1: TLabel;
    Label2: TLabel;
    Memo1: TMemo;
    procedure btnexitClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btnconvertClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
     agsqlparser : TGsqlparser;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btnexitClick(Sender: TObject);
begin
  form1.Close;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
 agsqlparser := TGsqlparser.Create(DBVOracle);
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  agsqlparser.Free;
end;

procedure TForm1.btnconvertClick(Sender: TObject);
var
  i,j,l,k,ret,sscount:integer;
  fromclause,lctn,whereclause,lctmpstr,lctmpstr2:String;
  lcstmt : TSelectSqlStatement;
  lcfmtopt : TLzFmtOpt;
  a: TStringlist;
  Counter: TJclCounter;
  EscapedTime : Real;
  isend : boolean;
  lctl : TLzTableList;
  lcts : TStringlist;
  lctable : TLzTable;
  lcexpr : TLzCustomExpression;


  function gettablenamefromexpr(pexpr : TLzCustomExpression):boolean;
  var
    lli : integer;
  begin
    result := false;
    pexpr.GetInvolvedDbObjects;
   // if pexpr.TableTokens.Count < 2 then exit;
    for lli:=0 to pexpr.TableTokens.Count - 1  do
      begin
        if lcts.IndexOf(pexpr.TableTokens[lli].astext) = -1 then
          lcts.Add(pexpr.TableTokens[lli].astext);
      end;
    result := lcts.Count = 2;
  end;

  function tablenameintablelist(ptablename :string):boolean;
    var
     i:Integer;
    begin
      result := false;
      for i:=0 to lctl.Count - 1 do
        begin
          if (AnsiCompareText(ptablename,lctl[i].TableName) = 0)
          or (AnsiCompareText(ptablename,lctl[i].TableAlias) = 0) then
            begin
              result := true;
              break;
            end;
        end;
    end;

  function findjointablenameinexpr(pexpr : TLzCustomExpression):string;
  var
    b1,b2 : Boolean;
  begin
    result := '';
    if pexpr.DummyTag <> 0 then exit;// this expression is processed already
    lcts.clear;
    if not gettablenamefromexpr(pexpr) then exit;
    b1 :=  tablenameintablelist(lcts[0]);
    b2 :=  tablenameintablelist(lcts[1]);
    if b1 and not b2 then
      result := lcts[1];
    if b2 and not b1 then
      result := lcts[0];
  end;

  function findjointablenameinexprs(pexprs : TObjectlist):string;
  var
    i:Integer;
    lct : string;
  begin
    lct := '';
    for i:=0 to pexprs.Count - 1 do
      begin
        lct := findjointablenameinexpr(TLzCustomExpression(pexprs[i]));
        if length(lct)>0 then break;
      end;
    result := lct;
  end;

  function checkexprbytablename(pt1 : string;pexpr : TLzCustomExpression):boolean;
  var
    i:Integer;
    b1,b2 : boolean;
  begin
    result := false;
    lcts.clear;
    if not gettablenamefromexpr(pexpr) then exit;
    b1 := AnsiCompareText(pt1,lcts[0]) = 0;
    b2 := AnsiCompareText(pt1,lcts[1]) = 0;
    if b1 then
      if tablenameintablelist(lcts[1]) then
        begin
          result := true;
          exit;
        end;

    if b2 then
      if tablenameintablelist(lcts[0]) then
        begin
          result := true;
          exit;
        end;
  end;

  function findexprbytablenameinexprs(pt1 : string;pexprs : TObjectlist):integer;
  var
    i,j:Integer;
  begin
    j := 0;
    for i:=0 to pexprs.Count - 1 do
      begin
        if  checkexprbytablename(pt1,TLzCustomExpression(pexprs[i])) then
          begin
            TLzCustomExpression(pexprs[i]).DummyTag := 2;
            inc(j);
          end;
      end;
    result := j;
  end;

  function FindTableByName(ptablename : string; ptlist : TLzTableList):TLzTable;
  var
    i:Integer;
  begin
      result := nil;
      for i:=0 to ptlist.Count - 1 do
        begin
          if (AnsiCompareText(ptablename,ptlist[i].TableName) = 0)
          or (AnsiCompareText(ptablename,ptlist[i].TableAlias) = 0) then
            begin
              lctl.Add(ptlist[i]);
              result := ptlist[i];
              break;
            end;
        end;
  end;

begin

  mmsqloutput.Clear;
  agsqlparser.SqlText := mmsqlinput.Lines;

  ret := agsqlparser.parse;

  if agsqlparser.ErrorCount = 0 then
    begin
      for i:=0 to agsqlparser.SqlStatements.Count -1 do
        begin
          if agsqlparser.SqlStatements[i].SqlStatementType <> sstSelect then continue;
          if agsqlparser.SqlStatements[i].Tables.Count <=1 then continue;
          lcstmt := agsqlparser.SqlStatements[i] as TSelectSqlStatement;
          if not assigned(lcstmt.WhereClause) then //Cartesian product
            begin
              for j:=0 to lcstmt.Tables.Count - 1 do
                begin
                  if j = 0 then
                    fromclause := lcstmt.Tables[j].AsText
                  else
                    fromclause := fromclause+#13#10+'cross join '+lcstmt.Tables[j].AsText
                end;
              lcstmt.FromClauseText := fromclause;
            end
          else
            begin
              lctl := TLzTableList.Create(false);
              lcts := TStringlist.create;
              try
                lcstmt.WhereClause.flatten;

                for j:=0 to length(lcstmt.WhereClause.operlist) - 1 do
                  begin
                   if (lcstmt.WhereClause.operlist[j] <> Expr_and) then
                    raise exception.Create('Must be and conditon,it is:'+inttostr(integer(lcstmt.WhereClause.operlist[j])));
                  end;

                for j:=0 to lcstmt.WhereClause.flatexprs.Count - 1 do
                  TLzCustomExpression(lcstmt.WhereClause.flatexprs[j]).dummytag := 0;

                fromclause := lcstmt.Tables[0].AsText;
                lctl.Add(lcstmt.Tables[0]);

                for k:= 1 to lcstmt.Tables.Count - 1 do
                  begin
                    // find next join table name
                    lctn := findjointablenameinexprs(lcstmt.WhereClause.flatexprs);
                    if length(lctn)=0 then
                      raise exception.Create('Join table is not found('+lcstmt.Tables[k].AsText+')');
                    if findexprbytablenameinexprs(lctn,lcstmt.WhereClause.flatexprs) = 0 then
                      raise exception.Create('Join table is not found by tablename('+lctn+')');
                    lcTable := FindTableByName(lctn,lcstmt.tables);
                    if not assigned(lcTable) then
                      raise exception.Create('table('+lctn+') is not found in from clause');
                    lctmpstr := '';
                    lctmpstr2 := '';
                    lcexpr := nil;
                    l := 0;
                    for j:=0 to lcstmt.WhereClause.flatexprs.Count - 1 do
                      begin
                        if TLzCustomExpression(lcstmt.WhereClause.flatexprs[j]).dummytag = 2 then
                          begin
                            lcexpr := TLzCustomExpression(lcstmt.WhereClause.flatexprs[j]);
                            if l>0 then
                              lctmpstr := lctmpstr+' and ';

                            if not assigned(lcexpr) then
                              raise exception('condition is not found for table('+lctn+')');

                            if assigned(lcexpr.lexpr) then
                              begin
                                if  TLzCustomExpression(lcexpr.lexpr).oper = Expr_OuterJoin then
                                  begin
                                   TLzCustomExpression(lcexpr.lexpr).opname.AsText := ''; //remove (+)
                                   lcts.Clear;
                                   gettablenamefromexpr(TLzCustomExpression(lcexpr.lexpr));
                                   if lcts.Count = 1 then
                                     begin
                                       if (AnsiCompareText(lcts[0],lctable.TableName) = 0)
                                       or (AnsiCompareText(lcts[0],lctable.TableAlias) = 0) then
                                         begin
                                           lctmpstr2 := 'left ';
                                         end
                                       else
                                         lctmpstr2 := 'right ';
                                     end;
                                  end
                              end;

                            if assigned(lcexpr.rexpr) then
                              begin
                                if  TLzCustomExpression(lcexpr.rexpr).oper = Expr_OuterJoin then
                                  begin
                                   TLzCustomExpression(lcexpr.rexpr).opname.AsText := ''; //remove (+)
                                   lcts.Clear;
                                   gettablenamefromexpr(TLzCustomExpression(lcexpr.rexpr));
                                   if lcts.Count = 1 then
                                     begin
                                       if (AnsiCompareText(lcts[0],lctable.TableName) = 0)
                                       or (AnsiCompareText(lcts[0],lctable.TableAlias) = 0) then
                                         begin
                                           lctmpstr2 := 'left ';
                                         end
                                       else
                                         lctmpstr2 := 'right ';
                                     end;
                                  end
                              end;

                            lctmpstr := lctmpstr+' '+TLzCustomExpression(lcstmt.WhereClause.flatexprs[j]).astext;
                            TLzCustomExpression(lcstmt.WhereClause.flatexprs[j]).dummytag := 1;
                            inc(l);
                          end;
                      end;


                    fromclause := fromclause+#13#10+lctmpstr2+'join '+lcTable.AsText+' on '+lctmpstr;
                   end;

                lcstmt.FromClauseText := fromclause;
                whereclause := '';
                for j:=0 to lcstmt.WhereClause.flatexprs.Count - 1 do
                  begin
                    if TLzCustomExpression(lcstmt.WhereClause.flatexprs[j]).dummytag = 0 then
                      begin
                        if length(whereclause) = 0 then
                         whereclause := TLzCustomExpression(lcstmt.WhereClause.flatexprs[j]).astext
                        else
                         whereclause := whereclause+' and '+TLzCustomExpression(lcstmt.WhereClause.flatexprs[j]).astext;
                      end;
                  end;
                if length(whereclause) > 0 then
                  lcstmt.WhereClausetext := whereclause
                else
                  lcstmt.WhereClausetext := ' ';

              finally
                lctl.free;
                lcts.free;
              end;
            end;
        end;

      for i:=0 to agsqlparser.SqlStatements.Count -1 do
        begin
          mmsqloutput.Lines.Add(agsqlparser.SqlStatements[i].AsText);
        end;
    end;

  if agsqlparser.ErrorCount > 0 then
    mmsqloutput.lines.add('syntax error:'+agsqlparser.ErrorMessages);
  if ret < 0 then
    mmsqloutput.lines.add('Fatal error in input');



end;

end.