sql本地数据库与远程数据库比较

sql本地数据库与远程数据库比较

<%

dim sql,connB,contentA,contentB,totalMoney,splA,splB,sA,sB,i,sMsg,table,sField,c

call openconn()
sql="select count(*) mv from ["&db_PREFIX&"ballGame2021]" 
rs.open sql,conn,1,1
totalMoney=IIF(isnull(rs("mv"))=true,0,rs("mv")):rs.close 
call echo("总数",totalMoney)

'服务器，本地测试用'
sqlServerHostIP="151.***.***.***"
sqlServerPassword="#****"


Set connB = CreateObject("Adodb.Connection")
call selectOpenConn(connB)
rsx.open sql,connB,1,1
totalMoney=IIF(isnull(rsx("mv"))=true,0,rsx("mv")) 
call echo("总数",totalMoney)


contentA=getTableList_Conn(conn)
contentB=getTableList_Conn(connB)

call echo("contentA",contentA)
call echo("contentB",contentB)

contentA=lcase(contentA)
contentB=lcase(contentB)
splA=split(contentA,vbcrlf)
for each table in splA
    table=lcase(table)
    if table<>"" then
        sMsg=IIF( instr(vbcrlf & contentB & vbcrlf , vbcrlf & table &vbcrlf)>0,"存在","不存在")
        contentA=replace(vbcrlf & contentA & vbcrlf , vbcrlf & table &vbcrlf ,vbcrlf)
        contentB=replace(vbcrlf & contentB & vbcrlf , vbcrlf & table &vbcrlf ,vbcrlf)

        sField=""
        if sMsg="存在" then
            sField=getCheckFieldList(table)
        end if
        call echo("table",table & "("& sMsg &")对比字段：" & sField)
        doEvents
    end if
next
 
for i=1 to 100
  contentA=replace(contentA,vbcrlf & vbcrlf,vbcrlf)
  contentB=replace(contentB,vbcrlf & vbcrlf,vbcrlf)
next
call echo("contentA(对比后)",replace(contentA,vbcrlf," , "))
call echo("contentB(对比后)",replace(contentA,vbcrlf," , "))

call echo("提示",getTimer())

'content=lcase(getTableList() )

function getCheckFieldList(table)
    dim splxx,s,c,fieldA,fieldB,fieldAlt,sDefault,fieldName,sEnd,stype

    fieldA=getFieldList_conn(table,conn)
    fieldB=getFieldList_conn(table,connB)

    fieldA=lcase(fieldA)
    fieldB=lcase(fieldB)
  ' call echo("fieldA",fieldA)
  ' call echo("fieldB",fieldB)
    splxx=split(fieldA,",")
    for each s in splxx
        if s<>"" then
            fieldA=replace(","& fieldA &",", ","& s &",", ",")
            fieldB=replace(","& fieldB &",", ","& s &",", ",")
        end if
    next
  ' call echo("fieldA",fieldA)
  ' call echo("fieldB",fieldB) 
  for i=1 to 100
    fieldA=replace(fieldA,",,",",")
    fieldB=replace(fieldB,",,",",")
  next
  if fieldA<>"," then
      c=c & "【fieldA】=" & fieldA
  end if
  if fieldB<>"," then
      c=c & "【fieldB】=" & fieldB
      splxx=split(fieldB,",")
      for each fieldName in splxx
          if fieldName<>"" then 
              stype=getFieldAlt_conn(table,fieldName,connB)
              sDefault=getFieldDefaltValue_Conn(table,fieldName,connB)  
              sDefault=handleFieldDefaultVal(sDefault)
              call echo("sDefault=" & len(sDefault),sDefault)
              call echo("sType",sType)
              if isNul(sDefault) then
                  if sType="Int" then
                      sDefault="0"
                  end if
              end if
              sEnd=","
              c=c&"        sql = sql & ""["& fieldName &"] "& stype &" Default "& sDefault &""& sEnd &""""  & "<br>" &vbcrlf

          end if
      next


  end if
  getCheckFieldList=c
end function

'处理字符默认值'
function handleFieldDefaultVal(s)
  dim i
    for i=1 to 2
      if left(s,1)="(" and right(s,1)=")" then
          s=mid(s,2)
          s=mid(s,1,len(s)-1)
      end if
    next
    handleFieldDefaultVal=s
end function