database.pas

SQL 分析相关代码,SQL 脚本分析相关代码

                end;
               result:=true;
             end;
             finally
              free;
             end

  
  end;

//****************************
function  SQLDataToCDataType(Const ASQLDataType:string):string; //将来数据库类型转换成C#类型
   var   SQLDataType:string;
         reval:string;
begin
SQLDataType:=LowerCase(ASQLDataType);
reval:='string';

if SQLDataType= 'int' then
     reval:= 'Int32'
     else
    if SQLDataType= 'text' then
     reval:= 'String' 
     else
    if SQLDataType= 'bigint' then
     reval:= 'Int64' 
     else
    if SQLDataType= 'binary' then
     reval:= 'Byte[]' 
     else
    if SQLDataType= 'bit' then
     reval:= 'Boolean' 
     else
    if SQLDataType= 'char' then
     reval:= 'String' 
     else
    if SQLDataType= 'datetime' then
     reval:= 'DateTime' 
     else
    if SQLDataType= 'decimal' then
     reval:= 'Decimal' 
     else
    if SQLDataType= 'float' then
     reval:= 'Double' 
     else
    if SQLDataType= 'image' then
     reval:= 'Byte[]' 
     else
    if SQLDataType= 'money' then
     reval:= 'Decimal' 
     else
    if SQLDataType= 'nchar' then
     reval:= 'String' 
     else
    if SQLDataType= 'ntext' then
     reval:= 'String' 
     else
    if SQLDataType= 'numeric' then
     reval:= 'Decimal' 
     else
    if SQLDataType= 'nvarchar' then
     reval:= 'String' 
     else
    if SQLDataType= 'real' then
     reval:= 'Single' 
     else
    if SQLDataType= 'smalldatetime' then
     reval:= 'DateTime' 
     else
    if SQLDataType= 'smallint' then
     reval:= 'Int16' 
     else
    if SQLDataType= 'smallmoney' then
     reval:= 'Decimal' 
     else
    if SQLDataType= 'timestamp' then
     reval:= 'DateTime' 
     else
    if SQLDataType= 'tinyint' then
     reval:= 'Byte' 
     else
    if SQLDataType= 'uniqueidentifier' then
     reval:= 'Guid' 
     else
    if SQLDataType= 'varbinary' then
     reval:= 'Byte[]' 
     else
    if SQLDataType= 'varchar' then
     reval:= 'String' 
     else
    if SQLDataType= 'Variant' then
     reval:='Object'
    else
     reval:= 'String';

     result:= reval;
end;


 function FieldTypeToCDataType(Const AFieldType:TFieldType):string;
   //将来执行SQL语句返回的字段类型转换成C#类型

   var s:string;//临时变量
   begin

     s:=FieldTypeToSQLDataType(AFieldType);
     result:=SQLDataToCDataType(s);
 
   end;
   function ExeSQL(ADOConn: TADOConnection;Const ASQL:string):string;
    //执行SQL,返回错误；如果没有错误，则返回空
    begin
    result:='';
      try
      if Database.OpenConn(ADOConn) then
         ADOConn.Execute(ASQL) ;
      except
         on e:Exception do
          result:=e.Message ;
      end;

    end;

  //*******************************************************
 function GetViewScript(ADOConn: TADOConnection;
      const AViewName:string):string;
      //返回视图的脚本
  begin
    result:='';  
     with TADOQuery.Create(nil) do
          try
            Connection :=ADOConn;
            if Database.OpenConn(ADOConn) then
             begin
              SQL.Text :=
                'select c.text from dbo.syscomments c, dbo.sysobjects o where o.id = c.id and c.id = object_id(N'''+AViewName+''')';
              Open ;
              if not Eof then
                 result:=FieldByName('text').AsString;
            end//if(Database.OpenConn(ADOConn) then

          finally
            free;
          end;
     if result<>'' then
        result:=copy(Result, pub.WordPos('AS',result)+3,length(result));
  end;
//*********************************

function GetViewComments(ADOConn: TADOConnection;
    const AViewName:string):string;
    //返回视图的SQL备注
    var p:integer;
        Script:string;
        I:integer;
  begin
    Script:='';
     with TADOQuery.Create(nil) do
          try
            Connection :=ADOConn;
            if Database.OpenConn(ADOConn) then
             begin
              SQL.Text :=
                'select c.text from dbo.syscomments c, dbo.sysobjects o where o.id = c.id and c.id = object_id(N'''+AViewName+''')';
              Open ;
              if not Eof then
                 Script:=FieldByName('text').AsString;
            end//if(Database.OpenConn(ADOConn) then

          finally
            free;
          end;
     if Script<>'' then
       begin
         P:=pos('*/',Script); //总是以/*开头， */结尾
         if P>0 then  //有描述
           begin
            Script:=copy(Script,3,P-3);
            result:=StringReplace(Script,#13+#10,' ',[rfReplaceAll, rfIgnoreCase]);
           end;
       end;
  end;
 //********************************
 function GetSPScript(ADOConn: TADOConnection; const ASPName:string):string;
    //返回存储过程脚本
    begin
    result:='';  
     with TADOQuery.Create(nil) do
          try
            Connection :=ADOConn;
            if Database.OpenConn(ADOConn) then
             begin
              SQL.Text :=
                'select c.text from dbo.syscomments c, dbo.sysobjects o where o.id = c.id and c.id = object_id(N'''+ASPName+''')';
              Open ;
              if not Eof then
                 result:=FieldByName('text').AsString;
            end//if(Database.OpenConn(ADOConn) then

          finally
            free;
          end;
    end;

//*********************************
function ObjectExists(ADOConn: TADOConnection;const ObjectNAme:string):boolean;
     begin
       result:=false;
       with TADOQuery.Create(nil) do
          try
            Connection :=ADOConn;
            if Database.OpenConn(ADOConn) then
             begin
              SQL.Text :='select  object_id(N'''+ObjectNAme+''') oid';
              Open ;
              if not Eof then
                 result:=not FieldByName('oid').IsNull  ;
            end//if(Database.OpenConn(ADOConn) then

          finally
            free;
          end;
     end;
//**********************************************************

function  RemoveRemark(SQLStr: String): string;
var FSyntax:TCodeValueList;
//code为语法字符，有 ',/*.*/,--,#13#10
//value为开始位置
   SyntaxStr:string; //有 ',/*.*/,--,#13#10
   S:string;
   B:boolean;
   B2:boolean;
   BP,EP:integer;
   RemarkPos:array of TPoint;
   SQLStrL:integer;
   FSyntaxL:integer;
   i,J,p:integer;
begin
SQLStr:=SQLStr + #13 + #10;
SQLStrL:=length(SQLStr);
if SQLStrL=1 then
  begin
    result:='';
    exit;
  end;
FSyntaxL:=0;
i:=0;
while i<=SQLStrL-1 do
  begin
    SyntaxStr:='';
    if SyntaxStr='' then
     if SQLStr[i]='''' then
      if i=SQLStrL then   //最后一个字符
         SyntaxStr:=''''
         else
         begin
         if  SQLStr[i+1]<>'''' then   //是一个表示字符串的不是转义的
            SyntaxStr:=''''           
            else
              i:=i+1;
          end;

       if SyntaxStr='' then
        if ((SQLStr[i]='/')and(SQLStr[i+1]='*')) then  //多行备注开始,/不可能是最后一个字符,否侧语法错误
         SyntaxStr :='/*';

     if SyntaxStr='' then
        if ((SQLStr[i]='*')and(SQLStr[i+1]='/')) then
         SyntaxStr :='*/';

     if SyntaxStr='' then
      if ((SQLStr[i]='-')and(SQLStr[i+1]='-')) then  //单行备注结束
         SyntaxStr :='--';

      if SyntaxStr='' then
       if ((SQLStr[i]=#13)and(SQLStr[i+1]=#10)) then  //换行了
        SyntaxStr :=#13 + #10;

      if SyntaxStr<>'' then
       begin
         SetLength(FSyntax,FSyntaxL+1);
         FSyntax[FSyntaxL].Code :=SyntaxStr;
         FSyntax[FSyntaxL].Value :=inttostr(i);
         FSyntaxL:=FSyntaxL+1;
         //i:=i+1;
       end ;
       i:=i+1;
  end;

 //*********************************************************************************
 FSyntaxL:=length(FSyntax);
 
 i:=0;
//  BP,EP:
 while I<=FSyntaxL-1 do
   begin
    BP:=-1;
    EP:=-1;
    P:=-1;
     if FSyntax[I].Code ='''' then
       begin
        I:=pub.FindPosByCode(FSyntax,'''',true,I+ 1);
        if I=-1 then //没有配对的
          break;
        i:=i+1;  
        Continue;
       end;
        
     if FSyntax[I].Code ='/*' then
         p:=pub.FindPosByCode(FSyntax,'*/',true,I )
         else
     if FSyntax[I].Code ='--' then
         p:=pub.FindPosByCode(FSyntax,#13 + #10,true,I);

     if p<>-1 then //找到了
         begin
           BP:=-1;
           S:=  FSyntax[i].Value ;
           if S<>'' then
             BP:=StrToInt(S);//开始位置
           s:=FSyntax[p].Value ;
           if S<>'' then
             EP:=StrToInt(S);//找到对应的位置
          end;
     if ((EP<>-1)and(BP<>-1)) then
       begin
           Setlength(RemarkPos,length(RemarkPos) +1);
           RemarkPos[length(RemarkPos)-1].X :=BP;//开始位置
           RemarkPos[length(RemarkPos)-1].Y :=EP+1;//截至位置
           I:=P +1; //忽略I和P之间的值
       end else  
      I:=I + 1; 
   end;



//**********************************
 for I:=1 to SQLStrL-1 do
   begin
    b:=false;
    B2:=false;
    for J:=0 to length(RemarkPos) -1 do
      if ((I>=RemarkPos[J].x) and(I<=RemarkPos[J].Y)) then//是Remark
        begin
          B2:= RemarkPos[J].x=I;
          B:=true;//
          break;
        end;
    if not B then  //不在注视中
      result:= result + SQLStr[i]
      else
      begin//注释中
      if B2 then
        result:= result + ' ';//
      end
 
   end;
end;
//***********************************

procedure  AnalysisViewScript(const ASQLStr: String;out ATables: string;
    out AViewFields: TCodeValueList);
{
        //ATables 返回与视图相关的表 ,以逗号隔开
        //ViewFields返回 视图字段
          //Code 为视图字段别名
        //Value为实际的字段名，TableName.Fieldname的形式}
var
 i, j :integer;
 S:string;
 SQLStr:string;

 FirstSELECTP:integer;
 FirstFROMP:integer;
 FirstJOINP:integer;
 FirstWHEREP:integer;
 FormTableEndP:integer;

 Fields:string;//

 Tables:string;//
 TS:Tstrings;
 TS2:Tstrings;//临时变量
 TS_Tables:Tstrings;
 TS_Fields:Tstrings;

 TableName:String;//分析字段时候用于记录表明成
 BaseFieldName:string;//在实际表中的字段名
 ViewFieldName:string;//在视图中的字段名

 JOINP:integer; //Join的位置
 ONINP:integer; //接在join后的on 的位置

 TableNameAai:TCodeValueList; //这个才是真正的返回值
 //数据表明以及别名 Code为别名，value为实际名
 //如果没有别明，则Code=value;

 ViewFields:TCodeValueList; //
  //Code 为视图字段别名
  //Value为实际的字段名，TableName.Fieldname的形式
  {，如果没有表名，则暂时还不知道数据表名}
begin

SQLStr:= RemoveRemark(ASQLStr);

FirstSELECTP:= pub.WordPos('SELECT',SQLStr);

FirstFROMP:= pub.WordPos('FROM',SQLStr);
FirstJOINP:= pub.WordPos('JOIN',SQLStr);
FirstWHEREP:= pub.WordPos('WHERE',SQLStr);
TS_Tables:=TstringList.Create;
TS_Fields:=TstringList.Create;
TS2:=TstringList.Create;

if((FirstJOINP=0)and(FirstWHEREP=0)) then
  FormTableEndP:=length(SQLStr)
  else
   if((FirstJOINP>0)and(FirstWHEREP=0)) then
    FormTableEndP:= FirstJOINP
    else