dbdeal.4gl

开发工具 Informix-4gl

##############################################################
#/--CLASS db
#@DESC deal with database	
#@AUTHOR sam ying
#@CREATEDATE 2002/08/21
#@MODIFYLIST
# NAME	   DATE          REASON
#-/
##############################################################

##############################################################
#/--@METHOD
#@DESC    建立所有的周边数据库 
#@RETURN  0,继续/成功:1,失败 
##############################################################

FUNCTION db_create_all()
DEFINE lv_result   SMALLINT
DEFINE lv_answer   CHAR(1)

    LET lv_result = 0
    INITIALIZE lv_answer TO NULL
    #--1
    CALL db_create("_PRPINSDB") RETURNING lv_result
    CASE lv_result
        WHEN 0
        WHEN 2
            PROMPT "业务库已存在,不能进行完全安装!按任意键退出安装:"
                FOR lv_answer
            RETURN 1
        OTHERWISE
            RETURN 1
    END CASE

    --2
    CALL db_create("_UTILITYDB") RETURNING lv_result
    IF lv_result!=0 AND lv_result !=2 THEN
        RETURN 1
    END IF

    #--3
    CALL db_create("_WORKFLOWDB") RETURNING lv_result
    IF lv_result!=0 AND lv_result !=2 THEN
        RETURN 1
    END IF
    #--4
    CALL db_create("_UWDB") RETURNING lv_result
    IF lv_result!=0 AND lv_result !=2 THEN
        RETURN 1
    END IF
    #--5
    CALL db_create("_VISADB") RETURNING lv_result
    IF lv_result!=0 AND lv_result !=2 THEN
        RETURN 1
    END IF
    #--6
    CALL db_create("_PRPREINSDB") RETURNING lv_result
    IF lv_result!=0 AND lv_result !=2 THEN
        RETURN 1
    END IF
    #--7
    CALL db_create("_PRPSTATDB") RETURNING lv_result
    IF lv_result!=0 AND lv_result !=2 THEN
        RETURN 1
    END IF
    #--8
    CALL db_create("_SFFDB") RETURNING lv_result
    IF lv_result!=0 AND lv_result !=2 THEN
        RETURN 1
    END IF
    #--9
    CALL db_create("_REINSDB") RETURNING lv_result
    IF lv_result!=0 AND lv_result !=2 THEN
        RETURN 1
    END IF
    #--10
    CALL db_create("_LTRANSDB") RETURNING lv_result
    IF lv_result!=0 AND lv_result !=2 THEN
        RETURN 1
    END IF

    RETURN 0

END FUNCTION


##############################################################
#/--@METHOD
#@DESC    建立数据库 
#@RETURN  0,继续/成功:1,失败 
##############################################################

FUNCTION db_create(iv_productid)
#--/PARAMETER
DEFINE iv_productid CHAR(30)
#-/
DEFINE lv_dbname char(40)
DEFINE lv_answer CHAR(1)
DEFINE lv_errtxt CHAR(80)
DEFINE lv_result SMALLINT

    INITIALIZE lv_dbname,lv_answer,lv_errtxt TO NULL
  
    LET lv_dbname=fgl_getenv(iv_productid)
    LET lv_dbname=lv_dbname CLIPPED
    IF lv_dbname IS NULL THEN
        ERROR "请您设置环境变量",iv_productid
        SLEEP 3
        RETURN 1
    END IF
    
    LET lv_answer=0
    WHENEVER ERROR CONTINUE
    DATABASE lv_dbname
   
    CASE SQLCA.SQLCODE
        WHEN "0"
   
            DISPLAY "要建立的数据库 ",lv_dbname CLIPPED," 已存在!"
            SLEEP 1
            RETURN 2
   
        WHEN "-387"

            DISPlAY "无权连接数据库",lv_dbname CLIPPED
            SLEEP 1
            RETURN 1
   
        WHEN "-329"
   
            DISPLAY "正在创建数据库 ",lv_dbname CLIPPED," ..."
            #--创建数据库
            CALL db_create2(lv_dbname) RETURNING  lv_result
            IF lv_result=FALSE THEN
                RETURN 1
            END IF
            #--创建数据表
            CASE iv_productid
       
                WHEN "_VISADB"
                    RUN "sh $INSTALL_PATH/install/dbs/tab_visadb.sh"

                WHEN "_UWDB"
                    RUN "sh $INSTALL_PATH/install/dbs/tab_uwdb.sh"

                WHEN "_WORKFLOWDB"
                    RUN "sh $INSTALL_PATH/install/dbs/tab_workflowdb.sh"
   
                WHEN "_PRPREINSDB"
                   RUN "sh $INSTALL_PATH/install/dbs/tab_prprein.sh"
   
                WHEN "_REINSDB"
                   RUN "sh $INSTALL_PATH/install/dbs/tab_reins.sh"

                WHEN "_SFFDB"
                   RUN "sh $INSTALL_PATH/install/dbs/tab_sffdb.sh" 

                WHEN "_PRPSTATDB"
                   RUN "sh $INSTALL_PATH/install/dbs/tab_prpstat.sh"
   
                WHEN "_PRPINSDB"
                  RUN "sh $INSTALL_PATH/install/dbs/taa_prpins.sh"
     
                WHEN "_UTILITYDB"
                  RUN "sh $INSTALL_PATH/install/dbs/tab_uti.sh"

                WHEN "_LTRANSDB"
                  RUN "sh $INSTALL_PATH/install/dbs/tab_ltrans.sh"

             END CASE 
             DISPLAY "创建结束!"

         OTHERWISE
             INITIALIZE lv_errtxt TO NULL
             LET lv_errtxt= "创建数据库",lv_dbname CLIPPED,"失败,错误号:",
                     SQLCA.SQLCODE
             CALL errorlog (lv_errtxt)
             LET lv_errtxt=lv_errtxt,",按任意键退出:"
             PROMPT lv_errtxt FOR lv_answer
             RETURN 1
    END CASE

    RETURN 0
    
END FUNCTION

##############################################################
#/--@METHOD
#@DESC    建立数据库 
#@RETURN  SQLCODE
#-/
##############################################################
FUNCTION db_create2(iv_dbname)
#/--@PARAMETER
DEFINE iv_dbname     CHAR(40)
#-/
DEFINE lv_dbname1    CHAR(19)
DEFINE lv_execsql    CHAR(100)
DEFINE lv_dbsname    CHAR(15)
DEFINE lv_answer     CHAR(15)
DEFINE lv_errtxt     CHAR(80)
DEFINE lv_flag       SMALLINT
DEFINE i             SMALLINT

    LET lv_flag=0

    INITIALIZE lv_execsql,lv_dbsname,lv_answer,lv_dbname1,lv_errtxt TO NULL

##2002-12-16 liangna add,增加远程建库功能
    FOR i = 1 to LENGTH(iv_dbname)
        IF iv_dbname[i] = "@" THEN
            EXIT FOR
        ELSE
            LET lv_dbname1=lv_dbname1,iv_dbname[i]
        END IF
    END FOR
    LET i = i - 1
    IF i > 18 THEN
       ERROR "创建数据库失败,库名超过18个字符!" sleep 2
       LET lv_errtxt= "创建数据库失败,库名超过18个字符!" 
       CALL errorlog(lv_errtxt)
       RETURN FALSE
    END IF
     
    WHENEVER ERROR CONTINUE

    WHILE lv_flag=0
        PROMPT "请输入要安装数据库的dbspace名称(缺省rootdbs):"
               ATTRIBUTE(REVERSE) FOR lv_answer
        LET lv_dbsname=lv_answer
        IF lv_dbsname IS NULL OR LENGTH(lv_dbsname)=0 THEN
            LET lv_dbsname="rootdbs"
        END IF
        LET lv_execsql = "CREATE DATABASE ", iv_dbname CLIPPED ," IN ",
                     lv_dbsname CLIPPED ," WITH LOG"
        PREPARE pre_crtdatabase From lv_execsql
        EXECUTE pre_crtdatabase

        CASE SQLCA.SQLCODE 
            WHEN 0
                LET lv_flag=1

            WHEN -225
                ERROR "数据库空间输入错误，请重试!"

            OTHERWISE
                LET lv_errtxt= "创建数据库",iv_dbname CLIPPED,"失败,错误号:",
                     SQLCA.SQLCODE
                CALL errorlog(lv_errtxt) 
                LET lv_errtxt=lv_errtxt,",按任意键退出:"
                PROMPT lv_errtxt FOR lv_answer
                LET lv_flag=2

        END CASE
    END WHILE

    WHENEVER ERROR STOP
    IF lv_flag=1 THEN
        RETURN TRUE
    ELSE
        RETURN FALSE
    END IF

END FUNCTION

##############################################################
#/--@METHOD
#@DESC    删除iv_productid数据库所有同义名 
#@RETURN  lv_result 
##############################################################
FUNCTION syno_drop_all(iv_productid)
#--/PARAMETER
DEFINE iv_productid     CHAR(30)
#-/
DEFINE lv_dbname        CHAR(40)
DEFINE lv_tabname       CHAR(18)
DEFINE lv_sqltxt        CHAR(200)
DEFINE lv_errtxt        CHAR(80)
DEFINE lv_answer        CHAR(1)

    #指定数据库iv_productid
    INITIALIZE lv_dbname,lv_tabname,lv_sqltxt,lv_errtxt,lv_answer TO NULL
    LET lv_dbname=fgl_getenv(iv_productid)
    IF lv_dbname IS NULL THEN
        RETURN 1
    END IF

    WHENEVER ERROR CONTINUE
    DATABASE lv_dbname

    IF SQLCA.SQLCODE != 0 THEN
       DISPLAY "打开数据库 ",lv_dbname CLIPPED,"失败,错误号:",SQLCA.SQLCODE
       RETURN 1
    END IF 

    #--查询lv_dbname中的同义名表
    LET lv_sqltxt="SELECT tabname FROM systables ",
                  "WHERE tabid >=100 AND tabtype=\"S\""
    PREPARE syno_prp_pre FROM lv_sqltxt
    DECLARE curr_syno CURSOR FOR syno_prp_pre

    #--DROP 所有同义名表
    INITIALIZE lv_tabname TO NULL
    FOREACH curr_syno INTO lv_tabname
       LET lv_sqltxt = "DROP SYNONYM ",lv_tabname CLIPPED
       PREPARE pre_syno_drop FROM lv_sqltxt
       EXECUTE pre_syno_drop

       IF SQLCA.SQLCODE = 0
       THEN
       ELSE
           LET lv_errtxt= "删除同义词 ",lv_tabname CLIPPED," 失败!错误号:",SQLCA.SQLCODE
           CALL errorlog(lv_errtxt)
           LET lv_errtxt= lv_errtxt,",按任意键退出:" 
           PROMPT lv_errtxt FOR lv_answer
           RETURN 1
       END IF

    END FOREACH

    WHENEVER ERROR STOP
    RETURN 0

END FUNCTION

##############################################################
#/--@METHOD
#@DESC    建立与周边数据库的同义词 
#@RETURN  lv_result 
##############################################################

FUNCTION syno_create_all()

    DISPLAY "正在创建同义名..." AT 12,12
    CALL syno_create("_UTILITYDB")
    CALL syno_create("_PRPREINSDB")
    CALL syno_create("_REINSDB")
    CALL syno_create("_SFFDB")
    CALL syno_create("_PRPSTATDB")
    CALL syno_create("_VISADB")
    CALL syno_create("_UWDB")
    CALL syno_create("_WORKFLOWDB")
    CALL syno_create("_LTRANSDB")
    DISPLAY "创建同义名结束..." AT 12,12
    SLEEP 1
    DISPLAY "                   " AT 12,12

END FUNCTION


##############################################################
#/--@METHOD
#@DESC creat synonym