The main idea is solved, and the detailed design is started (take the Sybase ASE database as an example, other extensions):
1. Create an intermediate layer VDT_COLUMNS, the properties of this table are used to build columns in the pipeline.
Perform a 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 (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 =" ucher, "ls_sql =" UIDENTITY INT NULL "LS_SQL =") "EXECUTE IMMEDIATE: LS_SQL USING SRCSQLCA;
2. Build other related possible use of intermediate layer views:
System object view:
LS_SQL = 'Create View VDT_OBJECTS (UID, UUID, Uname, uType) AS' & 'SELECT ID, UID, Name, (Case Type When ~' Tr ~ 'TEN ~' T ~ 'ELSE TYPE End) from sysobjects' Execute Immediate: ls_sql use 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 Use srcsqlca;
3. Initialize the VDT_COLUMNS table.
Insert vdt_columns select sc.id, sol.name, sc.colid, 'n', sc.Type, (Case When (select count (*) from system types st where sc.type = st.type and sc.USERTYPE = ST. UserType) = 0 THEN (SELECT MAX (ST.NAME) from system system type (select st.name from system "st where sc.type = st.TYPE AND SC.USERTYPE = St.USERTYPE ), 'n', (Case When PREC IS NOT NULL THEN INULL (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, sysbobjects so, syscomments Sy where sc.id * = SO.ID and sc.cdefault * = SY .id using SrcSqlca; in Sybase, the primary key column is determined troublesome: 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 =' 'ggetnull_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 is it_getpk_ase () is used to determine if a column is the 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 STATUYCNT, 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 endiffexes Into: li_keycnt,: li_indexid,: li_indstat,: li_indstat2; loop close curs_sysindexes;
as_keys = ls_keys return 1
After passing through the above steps, the data of the intermediate layer is basically obtained. According to this data, there is basically no error in transmission, and the intermediate layer is constructed, and a good foundation is labeled for the expansion of different databases.
Different databases, the syntax of the intermediate layer is different, but the structure of the intermediate layer (view) is the same, so that the method in the program is also unified.
to be continued...
