（转载）根据数据字典表定义的表结构,生成创建表的SQL语句

<来源网址：http://www.delphifans.com/infoview/Article_221.html>
根据数据字典表定义的表结构,生成创建表的SQL语句

//1．类名：TJZDbSqlCreate
//2．父类：Tcomponent
//3．主要属性：
// DataBaseName:String //数据源。数据字典存放路径(有大小写区别)
// TableName:String // 欲生成Create Table 语句的表(有大小写区别)
// TableType:String //表类型，从PARADOX,ACCESS,MSSQL中选择
// (无大小写区别)
// Sql;Tstings //生成的sql语句（不可见）
//4．功能：根据数据字典表定义的表结构，生成创建该表的Sql语句，
// 需要考虑的字段类型包括：字符、整数、数值、日期
//5．使用说明：
// 举例如下：
//VAR TJZDbSqlCreate1: TJZDbSqlCreate;
//TJZDbSqlCreate 1.DataBaseName:='JY';
//TJZDbSqlCreate1 .TableName:='SCHOOL';
//TJZDbSqlCreate1.TableType:='MSSQL';
//在"MSSQL","PARADOX","ACCESS"中取值；
//TJZDbSqlCreate1.exec;
//Memo1.lines.assign(TJZDbSqlCreate1.sql);
//*****************************************************************************
unit JZDBSqlCreate;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, Db,
DsgnIntf, DBTables;
type
TJZDBSqlCreate = class(TComponent)
private
{ Private declarations }
FDataBaseName:string;
FTableName:string;
Fsql:TStrings;
FTableType:string;
qry: TQuery;
protected
{ Protected declarations }
public
property sql:TStrings read Fsql write Fsql;
{ Public declarations }
constructor Create(AOwner: TComponent); override;
destructor Destroy;override;
procedure Exec;
published
{ Published declarations }
property DatebaseName:string read FDataBaseName write FDataBaseName;
property TableName:String read FTableName write FTableName;
property TableType:String read FTableType write FTableType;
//ocedure Exec;
end;

TTableTypePropEditor = class(TStringProperty)//Property editor for DataBaseName property
public
procedure GetValues(Proc:TGetStrProc);override;
function GetAttributes:TPropertyAttributes;override;
end;
TDatebaseNameEditor = class(TStringProperty)//Property editor for DataBaseName property
public
procedure GetValues(Proc:TGetStrProc);override;
function GetAttributes:TPropertyAttributes;override;
// function GetName:string;override;
end;
procedure Register;
implementation
constructor TJZDBSqlCreate.Create(AOwner: TComponent);
begin //创建成员
inherited Create(AOwner);
qry:=tquery.create(SELF)
end;
destructor TJZDBSqlCreate.Destroy;
begin //清空成员
Qry.Close;
Qry.Free;
Fsql.free;
inherited;
end;
procedure TTableTypePropEditor.GetValues(Proc:TGetStrProc);//Get list all uknown databases
begin
Proc('MSSQL');
Proc('PARADOX');
Proc('ACCESS');
end;

function TTableTypePropEditor.GetAttributes:TPropertyAttributes;
begin
Result:=[paValueList,paSortList,paReadOnly];
end;
procedure TDatebaseNameEditor.GetValues(Proc:TGetStrProc);//Get list all uknown databases
var
List:TStringList;
i:Integer;
begin
Session.Active:=True;
List:=TStringList.Create;
Session.GetDatabaseNames(List);
for i :=0 to List.Count-1 do Proc(List.Strings[i]);
List.Free;
Session.Active:=False;
Proc('MSSQL');
Proc('PARADOX');
Proc('ACCESS');
end;
function TDatebaseNameEditor.GetAttributes:TPropertyAttributes;
begin
Result:=[paValueList,paSortList];
end;
{function TDBNameEditor.GetName:string;
begin
Result:='DataBaseName';
end;}
procedure Register;
begin
RegisterPropertyEditor(TypeInfo(String),TJZDBSqlCreate,'DatabaseName',TDatebaseNameEditor);
RegisterPropertyEditor(TypeInfo(String),TJZDBSqlCreate,'TableType',TTableTypePropEditor);
RegisterComponents('JZ', [TJZDBSqlCreate]);
end;
procedure TJZDBSqlCreate.Exec;
var SQL1,Sql2,sqlkey,sqlnokey:STRING;
sql3:string;//字段标注
sqltype:string;
sqllength:string;
sqldegigits:string;
sqlisnull:string;
SQLISPKEY:STRING;
SQLDEFAULT:STRING;
begin
SQLISPKEY:='';
sqlkey:='' ;
sqlnokey:='';
Fsql := TSTRINGlist.Create;
qry:=tquery.create(SELF) ;
IF (FDataBaseName='') OR (FTableName='') OR (FTableType='') THEN
BEGIN
Application.MessageBox('属性没有设置','错误提示', MB_OK);
exit;
END;
IF (trim(UPPERCASE(FTABLETYPE))='ACCESS') OR (trim(uppercase(FTableType))='MSSQL') OR (trim(uppercase(FTableType))='PARADOX') THEN
ELSE
BEGIN
Application.MessageBox('TableType属性设置错误！','错误提示', MB_OK);
exit;
END;
try
try
Qry.SQl.text:='SELECT Ttables.CTABNAME, Ttables.CTABMEANS, '+
' Ttables.CMEM, Tcolumns.CCOLNAME, Tcolumns.CCOLMEANS, Tcolumns.CCOLTYPE,'+
' Tcolumns.ICOLLENGTH, Tcolumns.ICOLDIGITS, Tcolumns.ICOLISPKEY, Tcolumns.ICOLISNULL,'+
' Tcolumns.CCOLDEFAULT FROM TTABLES.db Ttables'+
' INNER JOIN TCOLUMNS.db Tcolumns'+
' ON (Ttables.CTABNAME = Tcolumns.CTABNAME) WHERE Ttables.CTABNAME=:TABNAME';
Qry.DatabaseName:=FDataBaseName;
Qry.ParamByName('TabName').asstring:=FTableName;
Qry.Open;
except ON ed:EDATABASEERROR do
begin
Application.MessageBox('数据字典设置错误','错误提示', MB_OK);
exit;
end;
end;
Qry.First;
if (not (qry.eof)) and (not ((QRY.FieldByName('CTABMEANS').asstring)=''))
AND (NOT(trim(UPPERCASE(FTABLETYPE))='ACCESS')) then
Sql2:=' /*'+QRY.FieldByName('CTABMEANS').asstring+'*/'+chr(13)+chr(10); //设置标题
While Not (Qry.Eof) DO
BEGIN
if QRY.FieldByName('CCOLMEANS').asstring='' then
sql3:=''
else
IF trim(UPPERCASE(FTABLETYPE))='ACCESS' THEN
sql3:=''//主要因为ACCESS注释如何写有疑问？
ELSE
sql3:=' /*'+QRY.FieldByName('CCOLMEANS').asstring+'*/';
//--------------------------------------------
IF trim(UPPERCASE(FTABLETYPE))='MSSQL' THEN
BEGIN
sqldegigits:='';
//确定字段类型
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='字符' then
sqltype:='CHAR';
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='整数' then
sqltype:='int';
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='数值' then
sqltype:='float';
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='日期' then
sqltype:='datetime';
//确定字段是否为空 0为空，1为不空
if Qry.FieldByName('icolisNull').asinteger=0 then
sqlisnull:='NULL'
ELSE
sqlisnull:='NOT NULL';
//确定主键
{IF Qry.FieldByName('iColIsPKey').asINTEGER=0 THEN
SQLISPKEY:=''
ELSE
SQLISPKEY:='primary key';}
IF Qry.FieldByName('iColIsPKey').asINTEGER=1 THEN
if SQLISPKEY='' then
SQLISPKEY:='CONSTRAINT '+Qry.FieldByName('CTABNAME').ASSTRING+ 'constraint primary key ('+Qry.FieldByName('cCOLname').asstring
else
SQLISPKEY:= SQLISPKEY+','+Qry.FieldByName('cCOLname').asstring;

//确定缺省
IF trim(uppercase(Qry.FieldByName('ccolDefault').asstring))<>'' THEN
begin
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='字符' then
SQLDEFAULT:='default '''+ Qry.FieldByName('ccolDefault').asstring+''''
else
SQLDEFAULT:='default '+Qry.FieldByName('ccolDefault').asstring;
end
else
SQLDEFAULT:='';
// 确定字段长度
IF (trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='整数') or
(trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='日期') or
(trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='数值') then
sqllength:=''
else
sqllength:='('+Qry.FieldByName('icollength').asstring+')';
Sql1:=sql1+' '+Qry.FieldByName('cCOLname').asstring+' '+sqltype+
' '+sqllength+
' '+sqldegigits+
' '+sqlisnull+
' '+SQLDEFAULT ;
Qry.NEXT;
//
if not(qry.eof) then
sql1:=sql1+','+sql3+CHR(13)+chr(10)
else
if SQLISPKEY='' then
sql1:=sql1+sql3+CHR(13)+chr(10)
else
sql1:=sql1+','+sql3+CHR(13)+chr(10);
end;//mssql第一个if
//------------------------------------
//paracox
IF trim(UPPERCASE(FTABLETYPE))='PARADOX' THEN
BEGIN

//确定小数点位置
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='数值' then
begin
if QRY.FieldByName('icoldigits').asstring='' then
sqldegigits:=',0)'
else
sqldegigits:=','+QRY.FieldByName('icoldigits').asstring+')' ;
end
else
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='字符' then
sqldegigits:=')'
else
sqldegigits:='';
//确定字段类型
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='字符' then
sqltype:='character';
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='整数' then
sqltype:='integer';
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='数值' then
sqltype:='float';
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='日期' then
sqltype:='date';
//确定字段是否为空 0为空，1为不空
{if Qry.FieldByName('icolisNull').asinteger=0 then //有疑问
sqlisnull:='NULL'
ELSE
sqlisnull:='NOT NULL';}
sqlisnull:='';
//确定主键
IF Qry.FieldByName('iColIsPKey').asINTEGER=1 THEN
if SQLISPKEY='' then
SQLISPKEY:='primary key ('+Qry.FieldByName('cCOLname').asstring
else
SQLISPKEY:= SQLISPKEY+','+Qry.FieldByName('cCOLname').asstring;
//

SQLDEFAULT:='';
// 确定字段长度
IF (trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='整数') or
(trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='日期') then
sqllength:=''
else
sqllength:='('+Qry.FieldByName('icollength').asstring;
//
if Qry.FieldByName('iColIsPKey').asINTEGER=1 THEN
begin
Sqlkey:=sqlkey+' '+Qry.FieldByName('cCOLname').asstring+' '+sqltype+
' '+sqllength+
' '+sqldegigits+
' '+sqlisnull+
' '+SQLDEFAULT;
end
else
begin
Sqlnokey:=sqlnokey+' '+Qry.FieldByName('cCOLname').asstring+' '+sqltype+
' '+sqllength+
' '+sqldegigits+
' '+sqlisnull+
' '+SQLDEFAULT;
end ;
Qry.NEXT;
//
if not(qry.eof) then
begin//11
IF Qry.FieldByName('iColIsPKey').asINTEGER=1 THEN
begin if sqlkey<>'' then sqlkey:=sqlkey+','+CHR(13)+chr(10) end
else
begin if sqlnokey<>'' then sqlnokey:=sqlnokey+','+CHR(13)+chr(10); end;
end //11
else
begin//2
if SQLISPKEY='' then
begin//21
if (sqlnokey<>'') and (sqlkey<>'') then
sql1:=sqlkey+','+CHR(13)+chr(10)+sqlnokey
else
begin
if sqlkey='' then sql1:=sqlnokey+CHR(13)+chr(10);
if sqlnokey='' then sql1:=sqlkey+CHR(13)+chr(10);
end;
end//21
else
begin//22
if (sqlnokey<>'') and (sqlkey<>'') then
sql1:=sqlkey+','+CHR(13)+chr(10)+sqlnokey+','+CHR(13)+chr(10)
else
begin
if sqlkey='' then sql1:=sqlnokey+CHR(13)+chr(10);
if sqlnokey='' then sql1:=sqlkey+CHR(13)+chr(10);
end;
end;//22
end//2

{if SQLISPKEY='' then
sql1:=sql1+sql3+CHR(13)+chr(10)
else
if sqlnokey<>'' then
sql1:=sqlkey+','+sql3+CHR(13)+chr(10); }
end;//PARADOX第一个if
//-----------------------------------
IF trim(UPPERCASE(FTABLETYPE))='ACCESS' THEN
BEGIN
//确定小数点位置
{IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='数值' then
begin
if QRY.FieldByName('icoldigits').asstring='' then
sqldegigits:=',0)'
else
sqldegigits:=','+QRY.FieldByName('icoldigits').asstring+')' ;
end
else }
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='字符' then
sqldegigits:=')'
else
sqldegigits:='';
//确定字段类型
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='字符' then
sqltype:='char';
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='整数' then
sqltype:='integer';
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='数值' then
sqltype:='float';
IF trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='日期' then
sqltype:='datetime';
//确定字段是否为空 0为空，1为不空
if Qry.FieldByName('icolisNull').asinteger=0 then
sqlisnull:='NULL'
ELSE
sqlisnull:='NOT NULL';
//确定主键
IF Qry.FieldByName('iColIsPKey').asINTEGER=1 THEN
if SQLISPKEY='' then
SQLISPKEY:='CONSTRAINT '+Qry.FieldByName('CTABNAME').ASSTRING+ 'constraint primary key ('+Qry.FieldByName('cCOLname').asstring
else
SQLISPKEY:= SQLISPKEY+','+Qry.FieldByName('cCOLname').asstring;
SQLDEFAULT:='';
// 确定字段长度
IF (trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='整数') or
(trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='日期') OR
(trim(Uppercase(QRY.FieldByName('cCOLTYPe').asstring))='数值') then
sqllength:=''
else
sqllength:='('+Qry.FieldByName('icollength').asstring;
//
Sql1:=sql1+' '+Qry.FieldByName('cCOLname').asstring+' '+sqltype+
' '+sqllength+
' '+sqldegigits+
' '+sqlisnull+
' '+SQLDEFAULT;
Qry.NEXT;
//

if not(qry.eof) then
sql1:=sql1+','+sql3+CHR(13)+chr(10)
else
if SQLISPKEY='' then
sql1:=sql1+CHR(13)+chr(10)
else
sql1:=sql1+','+CHR(13)+chr(10);
end;//
//ACCESS第一个if
//-----------------------------

end; //while
if sql1='' then
begin
Application.MessageBox('数据字典记录为空！','错误提示', MB_OK);
exit ;
end ;
if SQLISPKEY<>'' then
sql1:=sql1+SQLISPKEY+')'+CHR(13)+chr(10);
IF trim(UPPERCASE(FTABLETYPE))='PARADOX' THEN
begin
{if SQLISPKEY<>'' then
sql1:=sql1+SQLISPKEY+')'+CHR(13)+chr(10);}

sql1:='create table "'+FTableName+'.db"'+CHR(13)+chr(10)+'('+chr(13)+chr(10)+sql1 +')'
end
else
sql1:='create table '+FTableName+CHR(13)+chr(10)+'('+chr(13)+chr(10)+sql1 +')' ;
sql1:=sql2+sql1;
fsql.ADD(sql1);
except
Fsql.FREE;
qry.FREE;
end
end;
end.
（出处：www.delphibbs.com）

posted @ 2013-10-24 17:33 angelbd 阅读(4004) 评论(0) 编辑收藏举报

刷新页面返回顶部

angelbd

（转载）根据数据字典表定义的表结构,生成创建表的SQL语句

公告