如何?100%的動態資料管道(二)

來源:互聯網
上載者:User
動態|資料 主要思路解決了,下面開始寫詳細設計(以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



    經過以上的步驟,中介層的資料就基本獲得了,根據這些資料,基本上能夠無誤差的傳輸絕大部分表.構建了中介層,為以後的不同資料庫的擴充打下了一個良好的基礎.


不同的資料庫,構造中介層的文法各有不同,但是中介層的表(視圖)的結構是一樣的,這樣程式中處理的方法也統一了.


    待續...


    


    



相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。