How to implement 100% Dynamic Data pipeline (II.)

Dynamic | The main idea of the data has been solved, the following start to write detailed design (in the Sybase ASE database for example, others to expand):

1. Establish the middle-tier table vdt_columns, which is used to build the column data in the pipeline.



To perform similar code generation:

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 () NULL,"
Ls_sql + = "unulls varchar (1) NULL,"
Ls_sql + = "Uwidth int null,"
Ls_sql + = "Uscale int null,"
Ls_sql + = "uname varchar () 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. Build other related possible uses to the middle-tier view:


System Objects View:


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;


system table View:


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. Initialize the Vdt_columns table.


Insert Vdt_columns
Select Sc.id,so.name,sc.colid, ' N ', Sc.type,
(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.u Sertype) end),
' N ', (case when prec isn't 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 of 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;

In Sybase, it's more cumbersome to determine primary key columns:


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


where Of_getpk_ase () is used to determine whether a column is a primary key.


/*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//PRIMARY key

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



Through the above steps, the middle tier of data is basically obtained, according to these data, basically can not error transmission of most of the table. The middle layer is constructed, which lays a good foundation for the future expansion of different databases.


Different databases, the syntax for constructing the middle tier is different, but the middle tier table (view) has the same structure, so that the methods that are handled in the program are unified.


Cond...