主要思路解决了,下面开始写详细设计(以Sybase ASE数据库为例,其他各位扩展):
1.建立中间层表vdt_columns,这个表的属性用于构建管道中的列资料.
执行类似的代码生成:
ls_sql = "create table vdt_columns ("
ls_sql +="uid int null ,"
ls_sql +="upkey varchar(1) null ,"
ls_sql +="udmid int null,"
ls_sql +="udmname varchar(30) null,"
ls_sql +="unulls varchar(1) null ,"
ls_sql +="uwidth int null ,"
ls_sql +="uscale int null,"
ls_sql +="uname varchar(30) null,"
ls_sql +="udefault varchar(255) null,"
ls_sql +="ucheck varchar(255) null,"
ls_sql +="uidentity int null"
ls_sql +=")"
EXECUTE IMMEDIATE :ls_sql using SrcSqlca;
2.构建其他相关的可能用到中间层视图:
系统对象视图:
ls_sql = 'create view vdt_objects (uid,uuid,uname,utype) as'+&
' select id,uid,name,(case type when~'TR~' then ~'T~' else type end) from sysobjects'
EXECUTE IMMEDIATE :ls_sql using SrcSqlca;
系统表视图:
ls_sql = 'create view vdt_tables (uid,uuid,uname)as'+&
' select id,uid,name from sysobjects where type = ~'U~''
EXECUTE IMMEDIATE :ls_sql using SrcSqlca;
3.初始化vdt_columns 表.
insert vdt_columns
select sc.id,so.name,sc.colid,'N',sc.type,
(case when (select count(*) from systypes st where sc.type=st.type and sc.usertype=st.usertype)=0 then (select max(st.name) from systypes st where sc.type=st.type) else (select st.name from systypes st where sc.type=st.type and sc.usertype=st.usertype) end),
'N',(case when prec is not null then isnull(sc.prec,0) else sc.length end),
sc.scale,sc.name,substring(sy.text,9,char_length(sy.text) -8),"0",(case when sc.status=128 then 1 else 0 end)
from syscolumns sc,sysobjects so ,syscomments sy
where sc.id*=so.id and sc.cdefault*=sy.id
using SrcSqlca;
在Sybase中,确定主键列比较麻烦:
declare cur_vdtcolumns cursor for
select distinct utname from vdt_columns
using SrcSqlca;
open cur_vdtcolumns;
fetch cur_vdtcolumns into :ls_utname;
do while SrcSqlca.sqlcode=0
wait(true)
ls_nulls='';ls_pkey=''
of_getnull_ase(ls_utname,ls_nulls)
of_getpk_ase(ls_utname,ls_pkey)
if len(ls_pkey)>0 then
update vdt_columns
set upkey = 'Y',unulls='N'
where CHARINDEX(uname,:ls_pkey)>0
and utname = :ls_utname
using SrcSqlca;
end if
fetch cur_vdtcolumns into :ls_utname;
loop
end if
其中of_getpk_ase()用于确定某列是否是主键.
/*Out of date*/
Long Ll_Cnt
int Li_keycnt,Li_indexid,Li_indstat,Li_indstat2
String Ls_keys,Ls_ThisKey
int Li_i
If Not IsValid(SrcSqlca) Then return -1
Select Count(*) Into :Ll_Cnt From sysobjects Where name = :as_tablename Using SrcSqlca;
If Ll_Cnt <= 0 Then
return -2
End if
DECLARE curs_sysindexes CURSOR FOR
SELECT keycnt, indid, status, status2
FROM sysindexes
WHERE id = object_id(:as_tablename) AND indid > 0 Using SrcSqlca;
OPEN curs_sysindexes ;
FETCH curs_sysindexes INTO :Li_keycnt, :Li_indexid, :Li_indstat, :Li_indstat2;
do while (SrcSqlca.Sqlcode = 0)
If Mod(int(Li_indstat2/2),2) = 1 Then
IF Mod(int(Li_indstat/2048),2) = 1 Then //主键
Ls_Keys = ''
Li_i = 1
do while Li_i <=Li_keycnt
Select distinct index_col(:as_tablename, :Li_indexid, :Li_i) into :Ls_ThisKey
From vdt_columns Using SrcSqlca;
If Isnull(Ls_ThisKey) Then
Exit
Else
If Li_i > 1 Then Ls_keys += ','
Ls_Keys += Ls_ThisKey
End if
Li_i ++
loop
End if
End if
FETCH curs_sysindexes INTO :Li_keycnt, :Li_indexid, :Li_indstat, :Li_indstat2;
loop
CLOSE curs_sysindexes;
as_keys=Ls_keys
return 1
经过以上的步骤,中间层的数据就基本获得了,根据这些数据,基本上能够无误差的传输绝大部分表.构建了中间层,为以后的不同数据库的扩展打下了一个良好的基础.
不同的数据库,构造中间层的语法各有不同,但是中间层的表(视图)的结构是一样的,这样程序中处理的方法也统一了.
待续...