📄 database.pas
字号:
if((FirstJOINP=0)and(FirstWHEREP>0)) then
FormTableEndP:= FirstWHEREP
else
if((FirstJOINP>0)and(FirstWHEREP>0)) then
FormTableEndP:= min(FirstJOINP,FirstWHEREP);
Tables:=copy(SQLStr,FirstFROMP+length('FROM'), FormTableEndP-(FirstFROMP+length('FROM')));
Fields:=copy(SQLStr,FirstSelectP+length('SELECT'), FirstFROMP-(FirstSelectP+length('SELECT')));
Fields :=pub.WordReplace(Fields,'AS',' ',[rfReplaceAll, rfIgnoreCase]);
for i:=0 to length(Tables) -1 do
begin
if ((pub.IsWordChar(Tables[i])=false)and(Tables[i]in['.',',']=false)) then
Tables:=StringReplace(Tables,Tables[i],' ',[]);//不可见字符替换成空格
end;
for i:=0 to length(Fields) -1 do
begin
if ((pub.IsWordChar(Fields[i])=false)and(Fields[i]in ['.',',','*','(',')','''']=false)) then
Fields:=StringReplace(Fields,Fields[i],' ',[]);//不可见字符替换成空格
end;
TS:=pub.SplitString(Tables,',');
for i:=0 to TS.Count -1 do
if trim(TS[i])<>'' then
TS_TAbles.Add(trim(TS[i])) ;
Tables:='';
ONINP:= FirstFROMP;//On 一定是在Form后
JOINP:=FirstJOINP;
while JOINP> FirstFROMP do
begin
JOINP:=pub.WordPos( 'JOIN',SQLStr,ONINP + length('JOIN'));
ONINP:=pub.WordPos( 'ON',SQLStr,JOINP + length('JOIN'));
if JOINP>FirstFROMP then
if ONINP>JOINP then//on 必须跟在join后面
begin
Tables:=copy(SQLStr,JOINP + length('JOIN'), ONINP - JOINP - length('JOIN'));
Tables:=trim(Tables);
if Tables<>'' then
TS_Tables.Add(Tables) ;
end;
end;
Tables:=TS_Tables.Text ;
Tables :=pub.WordReplace(Tables,'INNER','',[rfReplaceAll, rfIgnoreCase]);
Tables :=pub.WordReplace(Tables,'LEFT','',[rfReplaceAll, rfIgnoreCase]);
Tables :=pub.WordReplace(Tables,'OUTER','',[rfReplaceAll, rfIgnoreCase]);
Tables :=pub.WordReplace(Tables,'RIGHT','',[rfReplaceAll, rfIgnoreCase]);
Tables :=pub.WordReplace(Tables,'FULL','',[rfReplaceAll, rfIgnoreCase]);
Tables :=pub.WordReplace(Tables,'AS',' ',[rfReplaceAll, rfIgnoreCase]);
TS_Tables.Text := Tables;
//***********以上找到标,用空格表示别明
for I:=0 to TS_Tables.Count -1 do
if TS_Tables[I]='' then
TS_Tables.Delete(i);
SetLength(TableNameAai,TS_Tables.Count);
for i:=0 to TS_Tables.Count -1 do
begin
s:= TS_Tables[i];
TS:=pub.SplitString(s,' ');
if TS.Count =0 then continue;
if TS.Count >1 then
begin
TableNameAai[i].Code:=TS[1];
TableNameAai[i].Value :=TS[0];
end
else //没有别名
begin
TableNameAai[i].Code:=TS[0];
TableNameAai[i].Value :=TS[0];
end
end; //for i:=0 to TS_Tables.Count -1 do
//****************************************************
//开始分字段
//开始分析字段别名
TS:=pub.SplitString(Fields,',');
for I:=0 to TS.Count -1 do
if trim(TS[I])<>'' then
TS_Fields.Add(TS[I]);
SetLength(ViewFields,TS_Fields.Count );
for i:=0 to TS_Fields.Count -1 do
begin
S:=TS_Fields[I] ;
S:=trim(s);
TS:=pub.SplitString(S,' ');//分析是否有别名
if TS.Count =0 then continue;
if TS.Count =1 then //无别名
begin
TS2:=pub.SplitString(TS[0],'.');
//Server.dbo.Code_Class.ClassName,dbo.Code_Class.ClassName,Code_Class.ClassNameClassName
ViewFieldName:= TS2[TS2.count-1];//只有最后一个是View中的字段名
BaseFieldName:=TS[0];
end//if TS.Count =1 then //无别名
else
if TS.Count =2 then //用了别名 student.dbo.Code_Class.ClassName AS cn,
begin
ViewFieldName:= TS[1];//用了别名
BaseFieldName:=TS[0];//用了别名
end;//if TS.Count =2 then //用了别名
//TableName ,ViewFieldName
ViewFields[i].Code :=trim(ViewFieldName);
ViewFields[i].Value :=trim((BaseFieldName));
end; //for i:=0 to TS_Fields.Count -1 do
//开始分析字段对应的表的真实名字,将表别名转换成实际表名
for i:=0 to length(ViewFields)-1 do
begin
ViewFieldName:= ViewFields[i].Code;
BaseFieldName:= ViewFields[i].Value;//st.sname->student.sname
TS2:=pub.SplitString(BaseFieldName,'.');
if TS2.Count <=1 then Continue;
s:='';
if TS2.Count =2 then //tablename.fieldname,别名.FieldName
begin
TableName :=TS2[0];
S:=pub.FindValueByCode(TableNameAai,TableName);
if s<>'' then //找到了
TS2[0]:=s;
ViewFields[i].Value:=TS2[0] + '.' + TS2[1];
end;
end;
//**********************以上得到View字段名字与实际表名的对应关系
//ATables:=Tables;
for I:=0 to length(TableNameAai)-1 do
ATables:=ATables +TableNameAai[i].Value +',';
AViewFields:=ViewFields;
TS.Free ;
TS_Tables.Free ;
TS_Fields.Free ;
end;
function GetNullExpression(Const CDataType :string):string;
var
I,J:integer;
XMLFileName:String;
TempXMLNode:IXMLNode; //临时变量
NullExpressionNode:IXMLNode; //
elseExpression:string;
begin
result:='';
//***************************
XMLFileName:=ExtractFileDir(Application.ExeName) +'\Config.xml';
if FileExists(XMLFileName)=false then
exit;
with TXMLDocument.Create(Application) do
try
FileName:= XMLFileName;
Active:=true;
NullExpressionNode:=DocumentElement.ChildNodes['NullExpression'].ChildNodes['C'];
for I:=0 to NullExpressionNode.ChildNodes.Count -1 do
if NullExpressionNode.ChildNodes[I].Attributes['CType']=CDataType then
begin
TempXMLNode:=NullExpressionNode.ChildNodes[I];
for j:=0 to TempXMLNode.ChildNodes.Count -1 do
if TempXMLNode.ChildNodes[J].IsTextElement then
result:=result +TempXMLNode.ChildNodes[J].NodeValue;
break;
end;
if result='' then
for I:=0 to NullExpressionNode.ChildNodes.Count -1 do
if trim(lowerCase(NullExpressionNode.ChildNodes[I].Attributes['CType']))='else' then
begin
TempXMLNode:=NullExpressionNode.ChildNodes[I];
for j:=0 to TempXMLNode.ChildNodes.Count -1 do
if TempXMLNode.ChildNodes[J].IsTextElement then
result:=result +TempXMLNode.ChildNodes[J].NodeValue;
break;
end;
finally
free;
end;
{
if CDataType='Byte' then result:='Byte.MaxValue' else
if CDataType='DateTime' then result:='DateTime.MaxValue' else
if CDataType='Decimal' then result:='Decimal.MaxValue' else
if CDataType='Double' then result:='Double.MaxValue' else
if CDataType='Guid' then result:='"00000000-0000-0000-0000-000000000000"' else
if CDataType='Int16' then result:='Int16.MaxValue' else
if CDataType='Int32' then result:='Int32.MaxValue' else
if CDataType='Int64' then result:='Int64.MaxValue' else
if CDataType='Object' then result:='null' else
if CDataType='Single' then result:='Single.MaxValue' else
if CDataType='String' then result:='null' else
result:=''; }
end;
function GetTableNameByRefOrder(ADOConn: TADOConnection;const ADBName:string):TStrings;//按得到表名称按引用的顺序，用于生成插入表数据
VAR TS,TS2:Tstrings;
References:TCodeValueList;
B :boolean;
I,J,k,L,P,PP,FP,C:integer;
S:string;
function HasValue(AReferences:TCodeValueList):boolean;//是不是全为空了
var
I:integer;
begin
result:=false;
for I:=0 to Length(AReferences) -1 do
if ((AReferences[I].Code <>'')or (AReferences[I].Value <>'')) then
begin
result:=true;
break;
end;
end;
begin
TS:=TStringList.Create ;
TS2:=TStringList.Create ;
TS:=GetObjects(ADOConn,ADBName,'U');
for I:=0 to TS.Count -1 do
TS[I]:=pub.KuoTableName(TS[I]);
References:=GetReferences(ADOConn,ADBName);
L:=Length(References);
//开始判断是不是有环
{
for I:=0 to L-1 do
begin
s:= References[I].Value;
B:=false;
for J:=0 to L-1 do
if References[J].Code = s then //又是主键
begin
B:=true;
Break;
end;
if not B then
Huan:=false;
end; }
for I:=0 to L -1 do
begin
P:=TS.IndexOf(References[I].Code);
if p>=0 then
TS.Delete(P);
P:=TS.IndexOf(References[I].Value);
if p>=0 then
TS.Delete(P)
end; //删除已经在主要键/外键表中存在的
c:=0;
{
for I:=0 to L-1 do
TS2.Add(References[i].Code +'->'+ References[i].Value );
TS2.SaveToFile('c:\a.txt');
TS2.Clear ; }
while HasValue(References) do
begin
inc(c);
if C>L+1 then
begin
MessageDlg('外键引用存在环，程序无法确认插入顺序', mtError,[mbOk], 0);
TS:=GetObjects(ADOConn,ADBName,'U');//重新读表名称
break;
end;
for I:=0 to L-1 do
begin
B:=false;
s:= References[I].Value;
if S='' then Continue;
for J:=0 to L-1 do
if s=References[J].Code then
begin
B:=true;
break;//还是主键
end;
if not B then //叶子
if S<>'' then
begin
if TS2.IndexOf(s)=-1 then
TS2.Add(s); //剔出重复
for k:=0 to L-1 do
if References[k].Value=S then
begin
References[k].Value :=References[k].Code ;
References[k].Code :='';
end;
end;
end;//for I:=0 to -1 do
end;// while HasValue(References) do
for I:=TS2.Count -1 downto 0 do
TS.Add(TS2[I]);
TS2.Free ;
result:=TS;
end;
function GetReferences(ADOConn: TADOConnection;const ADBName:string):TCodeValueList ;
//得到引用关系，用于排插入顺序 Code 为主键表 value为外键表名称
var SQLStr:string;
R:integer;
begin
SQLStr:=' select user_name(o.uid) + ''.['' + o.name + '']'' PkTable,user_name(oo.uid) + ''.['' + oo.name + '']'' FKTable';
SQLStr:=SQLStr + ' from sysreferences r inner join sysObjects oo on oo.id=r.fkeyid inner join sysObjects o on o.id= r.rkeyid';
with TADOQuery.Create(nil) do
try
Connection :=ADOConn;
if Database.OpenConn(ADOConn,true,ADBName) then
begin
SQL.Text :=SQLStr;
Open ;
setLength(result,RecordCount);
R:=0;
while not Eof do
begin
result[R].Code :=FieldByName('PkTable').AsString;
result[R].Value:=FieldByName('FKTable').AsString;
inc(R);
Next;
end;
end//if(Database.OpenConn(ADOConn) then
finally
free;
end;
end;
function GetTableRecordCount(ADOConn: TADOConnection;Const ATableName:string;
Const Atop:integer;//-1 表示全部
const AWhere:string;out Count:integer):string;
//得到表中的记录数量 返回错误信息
var SQLStr:string;
begin
result:='';
try
with TADOQuery.Create(nil) do
try
Connection :=ADOConn;
if Database.OpenConn(ADOConn) then
begin
if Atop=-1 then
SQLstr:= 'select count(*) C from ' + ATableName + ' '+ pub.ExtWhere(AWhere)
else
SQLstr:= 'select count(*) C from (select top '+inttostr(ATop)+' * from ' + ATableName + ' '+ pub.ExtWhere(AWhere)
+ ')t ' ;
SQL.Text := SQLstr;
open;
Count:=FieldByName('C').AsInteger ;
end;
finally
free;
end;
except
on E:Exception do
result:=E.Message ;
end;
end;
//*****************
function GetSQLDataTypes(ADOConn: TADOConnection;out ADataTypes:TCodeValueList):Boolean;//
//code 是类型，包括字定义类型 value是 原始类型
var SQLStr:string;
R:integer;
begin
SQLStr:=' select systypes.name,t.name orgName from systypes inner join (select Xtype,name from systypes where Xtype=XuserType)t on t.Xtype=systypes.Xtype order by systypes.name';
with TADOQuery.Create(nil) do
try
try
Connection :=ADOConn;
if Database.OpenConn(ADOConn ) then
begin
SQL.Text :=SQLStr;
Open ;
setLength(ADataTypes,RecordCount);
R:=0;
while not Eof do
begin
ADataTypes[R].Code :=FieldByName('name').AsString;
ADataTypes[R].Value:=FieldByName('orgName').AsString;
inc(R);
Next;
end;
result:=true;
end//if(Database.OpenConn(ADOConn) then
finally
free;
end;
except
result:=false;
end;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -