##�ı�����Ȩ2005-07-06
##ԭ���ߣ�2004-11-18    ====MiniAcces Editor1.0part2 Access SQL�ű���д��(V37 PaintBlue.Net 2004 Acp Code)=========
from vb2py.vbfunctions import Variant,String,Chr,IsNumeric,Len,Instr,CreateObject,vbCrLf,Mid,vbForRange,LCase,Left,Right
import Module1
import win32com.client
InStr=Instr
def IsNull(a):
    if a.Value==None:
        return(1)
    else:
        return(0)
def Replace(a,b,c):
    return(a)
class Ma:
    def __init__(self):
        self.db_name='J:\\nb\\cs\\access2000\\data.mdb'
        self.rs=None
    def setDbName(self,mdbName):
        self.db_name=mdbName
    def on_Command1_mouseClick(self, event=None):
        db_name = self.db_name
        self.createAccessSql(db_name)
    def createAccessSql(self,db_name):
        remchar=""
        TtempStr=""
        TableStr=""
        self.rs = CreateObject('adodb.recordSet')
        self.__connstr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + db_name
        self.__conn = CreateObject('ADODB.Connection')
        self.__conn.Open(self.__connstr)
        #���/��������
        tbls = self.__conn.openSchema(20)
        tbls.Filter = ' TABLE_TYPE=\'TABLE\' '
        while not tbls.EOF:
            TableStr = TableStr + '|' + tbls.Fields['TABLE_Name'].Value
            tbls.MoveNext()
        tbls.Filter = 0
        tbls.Close()
        tbls = None
        TableStr = Mid(TableStr, 2)
        isexec=0
        if TableStr <> '':
            tabsArr = TableStr.split('|')
            ub = len(tabsArr)
            for i in range(ub):
                TtempStr = TtempStr + self.CreatTableSql(tabsArr[i]) + vbCrLf + vbCrLf
        f=open("cmd.py","w")
        f.write("cmds=["+TtempStr+"]")
        f.close()
        print TtempStr
    #~ def __CreatViewSql(self, isexec):
        #~ _ret = None
        #~ tmpstr=""
        #~ cols = self.__conn.openSchema(23)
        #~ cols.Filter = 0
        #~ while not cols.EOF:
            #~ TmpStr1 = ''
            #~ VIEW_DEFINITION = Replace(cols('VIEW_DEFINITION').Value, Chr(13), '')
            #~ print "=================",VIEW_DEFINITION
            #~ VIEW_DEFINITION = Replace(VIEW_DEFINITION, Chr(10), ' ')
            #~ VIEW_DEFINITION = Left(VIEW_DEFINITION, Len(VIEW_DEFINITION) - 1)
            #~ VIEW_DEFINITION = self.__TransView(cols('TABLE_NAME'), VIEW_DEFINITION)
            #~ TmpStr1 = 'Create view [dbo].[' + cols('TABLE_NAME').Value + '] As ' + VIEW_DEFINITION + ''
            #~ if isexec == 1:
                #~ TmpStr1 = 'CONN.execute("' + TmpStr1 + '")'
            #~ tmpstr = tmpstr + vbCrLf + TmpStr1
            #~ if isexec == 0:
                #~ tmpstr = tmpstr + vbCrLf + ' go'
            #~ cols.MoveNext()
        #~ cols.Close()
        #~ cols = None
        #~ _ret = tmpstr
        #~ return _ret
    #~ def __TransView(self, viewName, Str):
        #~ _ret = None
        #~ s = LCase(Str)
        #~ s = Replace(s, Chr(9), ' ')
        #~ s = Replace(s, Chr(32), ' ')
        #~ s = Replace(s, Chr(10), ' ')
        #~ s = Replace(s, Chr(13), '')
        #~ s = Replace(s, ';', ' ')
        #~ while InStr(s, '  ') > 0:
            #~ s = Replace(s, '  ', ' ')
        #~ s = Replace(s, 'count(*)', 'count(*) as count_x')
        #~ if InStr(LCase(s), '* from') == 0:
            #~ _ret = s
        #~ else:
            #~ _ret = Replace(s, '* from', self.__GetviewColumnStr(viewName) + ' from')
        #~ #rw GetviewColumnStr(viewName),1
        #~ #rw instr(lcase(S),"* from"),1
        #~ return _ret
    #~ def __GetviewColumnStr(self, viewName):
        #~ _ret = None
        #~ chg = False
        #~ #rw "[" & viewName & "]",0
        #~ rs = CreateObject('adodb.recordset')
        #~ #rw "select * from [" & tablename & "] where 1=0",1
        #~ rs.Open('[' + viewName + ']', self.__conn)
        #~ if rs.fields.Count > 0:
            #~ tmpstr = rs(0).Name
            #~ for i in vbForRange(1, rs.fields.Count - 1):
                #~ tmpstr = tmpstr + ',' + rs(i).Name
            #~ tmpstr = LCase(tmpstr)
            #~ arr = Split(tmpstr, ',')
            #~ for i in vbForRange(0, UBound(arr)):
                #~ tmp = arr[i]
                #~ arr[i] = '[' + arr[i] + ']'
                #~ if InStr(arr[i], '.') > 0:
                    #~ arr[i] = Replace(arr(i), '.', '].[')
                    #~ arr[i] = arr[i] + ' as ' + Replace(tmp, '.', '_')
                    #~ chg = True
            #~ if chg:
                #~ _ret = Join(arr, ',')
            #~ else:
                #~ _ret = '*'
        #~ else:
            #~ _ret = ''
        #~ return _ret

    def CreatTableSql(self, tablename):
        _ret = None
        isexec=0
        cols = self.__conn.openSchema(4)
        splitchar = ''
        splitchar1 = ''
        cols.Filter = 'Table_name=\'' + tablename + '\''
        if cols.EOF:
            return _ret
        n = 0
        # ��д���ű�
        autoclumn = self.__GetAutoincrementCoulmnT(tablename)
        TmpStr1 = '"""CREATE TABLE [' + tablename + '] (' + splitchar1 + vbCrLf
        if autoclumn <> None:
            autoclumnStr = '    ' + splitchar + '[' + autoclumn + '] autoincrement'
        n = 0
        while 1:
            n = n + 1
            cols.Filter = 'Table_name=\'' + str(tablename) + '\' and ORDINAL_POSITION=' + str(n)
            if cols.EOF:
                break
            if n > 1:
                TmpStr1 = TmpStr1 + ',' + splitchar1 + vbCrLf
            if autoclumn == cols('Column_name'):
                TmpStr1 = TmpStr1 + autoclumnStr
            else:
                #print cols('Column_name')
                #print splitchar
                s1=self.__datatypeStr(cols('DATA_TYPE'), cols('CHARACTER_MAXIMUM_LENGTH'))
                s1=LCase(s1)
                s2=self.__nullStr(cols('IS_NULLABLE'), tablename, cols('Column_name'))
                s3=""##self.__defaultStr(cols('DATA_TYPE'), cols('COLUMN_DEFAULT'), isexec)
                TmpStr1 = TmpStr1 + ' ' + splitchar + '[' + str(cols('Column_name')) + '] ' + s1 + s3 + s2
            cols.MoveNext()
        TmpStr1 = TmpStr1 + splitchar1 + vbCrLf + '  ' + splitchar + ') '
        cols.Close()
        TmpStr1 = TmpStr1 + splitchar1 + vbCrLf + '' + splitchar + '""",'
        tmpstr=""
        tmpstr = tmpstr + vbCrLf + TmpStr1
        # ��д�����ű�
        s11=self.__getInxArr(tablename)
        InxArr = s11.split(',')
        cols = self.__conn.openSchema(12)
        for i in range(len(InxArr)):
            cols.Filter = 'Table_name=\'' + tablename + '\' and index_name=\'' + InxArr[i] + '\''
            kstr = ''
            TmpStr1 = ''
            if not self.__isForeignIndex(tablename, InxArr[i]):
                while not cols.EOF:
                    kstr = kstr + ',[' + cols('column_name').Value + '] ' + self.__GetInxDesc(tablename, InxArr[i], cols('column_name'))
                    cols.MoveNext()
                if self.__isPrimaryKey(tablename, InxArr[i]):
                    TmpStr1 = TmpStr1 + '"""Alter TABLE [' + tablename + ']  ADD Primary Key(' + Mid(kstr, 2) + ') [PK_' + tablename + ']""",'
                else:
                    TmpStr1 = TmpStr1 + '"""CREATE '
                    if self.__isUnique(tablename, InxArr[i]):
                        TmpStr1 = TmpStr1 + 'Unique '
                    TmpStr1 = TmpStr1 + 'INDEX [' + InxArr[i] + '] on [' + tablename + '](' + Mid(kstr, 2) + ') """,'
                TmpStr1 = TmpStr1 + vbCrLf 
                tmpstr = tmpstr + vbCrLf + TmpStr1
        cols.Close()
        cols.Filter = 0
        _ret = tmpstr
        return _ret
    def __GetColumnStr(self, tablename):
        _ret = None
        rs = CreateObject('adodb.recordset')
        #rw "select * from [" & tablename & "] where 1=0",1
        cmd='select * from [' + tablename.Value + '] where 1=0'
        print cmd
        print self.__conn
        print rs
        tmpstr=""
        rs.Open(cmd, self.__conn)
        if rs.fields.Count > 0:
            for i in vbForRange(0, rs.fields.Count - 1):
                #rw rs(i).name & "_" & rs(i).type & "<br>",1
                if rs(i).Type <> 205:
                    tmpstr = tmpstr + ',' + rs(i).Name
            if tmpstr <> '':
                _ret = Mid(tmpstr, 2)
                _ret = ''
        else:
            _ret = ''
        return _ret

    def __Ac2SQLStr(self):
        tmpstr = ''
        rs = self.__conn.openSchema(20)
        rs.Filter = 'TABLE_TYPE=\'TABLE\''
        while not rs.EOF:
            tmpstr = tmpstr + 'SELECT  * INTO [tmp_' + rs('TABLE_NAME') + '] FROM OPENDATASOURCE(\'Microsoft.Jet.OLEDB.4.0\',\'Data Source="d:\\www\\lfgbox\\paintblue2.0f2\\pbbs\\database\\paintbase#.asa"\')...[' + rs('TABLE_NAME') + ']<br>'
            NN = NN + 1
            rs.MoveNext()
        rs.Filter = 0
        rs.Close()
        rs = None

    def __isForeignIndex(self, tablename, IndexName):
        _ret = None
        cols = self.__conn.openSchema(27)
        cols.Filter = 'FK_TABLE_Name=\'' + tablename + '\' and FK_NAME=\'' + IndexName + '\''
        if not cols.EOF:
            _ret = True
        else:
            _ret = False
        return _ret

    def __GetInxDesc(self, tablename, IndexName, columnName):
        _ret = None
        cat = CreateObject('ADOX.Catalog')
        cat.ActiveCONNection = self.__connstr
        t=cat.Tables('' + tablename + '')
        i=t.Indexes('' + IndexName + '')
        c=i.Columns('' + columnName.Value + '')
        if c.SortOrder == 2:
            _ret = 'Desc'
        else:
            _ret = ''
        cat = None
        return _ret

    def __getColumArr(self, tablename):
        _ret = None
        arr = vbObjectInitialize((- 1,), Variant)
        n = 0
        arr = vbObjectInitialize((n,), Variant)
        cols = self.__conn.openSchema(4)
        cols.Filter = 'Table_Name=\'' + tablename + '\''
        while not cols.EOF:
            arr = vbObjectInitialize((n,), Variant, arr)
            arr[n] = cols('column_name')
            cols.MoveNext()
            n = n + 1
        cols.Filter = 0
        cols.Close()
        cols = None
        _ret = arr
        return _ret

    def __getInxArr1(self, tablename):
        _ret = None
        arr = vbObjectInitialize((- 1,), Variant)
        n = 0
        cols = self.__conn.openSchema(12)
        cols.Filter = 'Table_Name=\'' + tablename + '\''
        while not cols.EOF:
            if cols('index_name') <> tmpCol:
                arr = vbObjectInitialize((n,), Variant, arr)
                arr[n] = cols('index_name')
                n = n + 1
            tmpCol = cols('index_name')
            cols.MoveNext()
        cols.Filter = 0
        cols.Close()
        cols = None
        ##self.__getInxArr() = arr
        return _ret

    def __getInxArr(self, tablename):
        _ret = None
        n = 0
        tmpCol=None
        tmps=""
        cols = self.__conn.openSchema(12)
        cols.Filter = 'Table_Name=\'' + tablename + '\''
        while not cols.EOF:
            if cols('index_name') <> tmpCol:
                tmps = tmps + ',' + cols('index_name').Value
                n = n + 1
            tmpCol = cols('index_name')
            cols.MoveNext()
        cols.Filter = 0
        cols.Close()
        cols = None
        _ret = Mid(tmps, 2)
        return _ret

    def __isUnique(self, tablename, IndexName):
        _ret = None
        cols = self.__conn.openSchema(12)
        cols.Filter = 'Table_Name=\'' + tablename + '\' and Index_Name=\'' + IndexName + '\' and UNIQUE=True'
        if not cols.EOF:
            _ret = True
        else:
            _ret = False
        cols.Filter = 0
        cols.Close()
        cols = None
        return _ret

    def __isPrimaryKey(self, tablename, IndexName):
        _ret = None
        cols = self.__conn.openSchema(12)
        cols.Filter = 'Table_Name=\'' + str(tablename) + '\' and Index_Name=\'' + IndexName + '\' and PRIMARY_KEY=True'
        if not cols.EOF:
            _ret = True
        else:
            _ret = False
        cols.Filter = 0
        cols.Close()
        cols = None
        return _ret

    def __getPrimaryKey(self, tablename, columnName):
        _ret = None
        cols = self.__conn.openSchema(12)
        cols.Filter = 'Table_Name=\'' + str(tablename) + '\' and Column_Name=\'' + str(columnName.Value) + '\' and PRIMARY_KEY=True'
        if not cols.EOF:
            _ret = cols('INDEX_NAME')
            #isPrimaryKey=true
        else:
            _ret = ''
            #isPrimaryKey=false
        cols.Filter = 0
        cols.Close()
        cols = None
        return _ret

    def __existPrimaryKey(self, tablename):
        _ret = None
        cols = self.__conn.openSchema(12)
        cols.Filter = 'Table_Name=\'' + tablename + '\' and PRIMARY_KEY=True'
        if not cols.EOF:
            _ret = True
        else:
            _ret = False
        cols.Filter = 0
        cols.Close()
        cols = None
        return _ret

    def __GetIncrement(self, tablename, columnName):
        _ret = ""
        cat = CreateObject('ADOX.Catalog')
        cat.ActiveCONNection = self.__connstr
        print tablename,columnName
        _ret = cat.Tables[ tablename ].Columns[ columnName].Properties['Increment']       
        cat = None
        return _ret

    def __GetSeed(self, tablename, columnName):
        _ret = None
        cat = Server.CreateObject('ADOX.Catalog')
        cat.ActiveCONNection = self.__connstr
        _ret = cat.Tables('' + tablename + '').Columns('' + columnName + '').Properties('Seed')
        cat = None
        return _ret

    def __GetAutoincrementCoulmnT(self, tablename):
        _ret = None
        self.rs.Open('select * from [' + tablename + '] where 1=0', self.__conn, 0, 1)
        for i in vbForRange(0, self.rs.fields.Count - 1):
            #if rs(i).Properties("isAutoIncrement")=True then
            if self.rs(i).Properties('isAutoIncrement') == True:
                _ret = self.rs(i).Name
                self.rs.Close()
                return _ret
        self.rs.Close()
        return _ret

    def __datatypeStr(self, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH):
        _ret = None
        _select0 = DATA_TYPE.Value
        #~ print type(CHARACTER_MAXIMUM_LENGTH)
        #~ print dir(CHARACTER_MAXIMUM_LENGTH)
        #~ print type(CHARACTER_MAXIMUM_LENGTH.Value)
        #~ print CHARACTER_MAXIMUM_LENGTH.Value
        if (_select0 == 130):
            if CHARACTER_MAXIMUM_LENGTH.Value == 0:
                _ret = 'text'
            else:
                _ret = 'varchar(' + str(CHARACTER_MAXIMUM_LENGTH.Value) + ')'
        elif (_select0 == 17):
            _ret = 'tinyint'
        elif (_select0 == 2):
            _ret = 'Smallint'
        elif (_select0 == 3):
            _ret = 'integer'
        elif (_select0 == 4):
            _ret = 'real'
        elif (_select0 == 5):
            _ret = 'float'
        elif (_select0 == 6):
            _ret = 'money'
        elif (_select0 == 7):
            _ret = 'datetime'
        elif (_select0 == 11):
            _ret = 'bit'
        elif (_select0 == 72):
            _ret = 'UNIQUEIDENTIFIER'
        elif (_select0 == 131):
            _ret = 'DECIMAL'
        elif (_select0 == 128):
            _ret = 'BINARY'
        return _ret

    def __defaultStr(self, DATA_TYPE, COLUMN_DEFAULT, isexec):
        _ret = None
        if IsNull(COLUMN_DEFAULT):
            _ret = ''
            return _ret
        if isexec == 1:
            splitchar = '""'
        elif isexec == 0:
            splitchar = '"'
        COLUMN_DEFAULT = self.__defaultStrfilter(COLUMN_DEFAULT)
        _select1 = DATA_TYPE
        if (_select1 == 130):
            COLUMN_DEFAULT = Replace(COLUMN_DEFAULT, '"', splitchar)
            _ret = ' Default (\'' + COLUMN_DEFAULT + '\')'
        elif (_select1 == 11):
            if LCase(COLUMN_DEFAULT) == 'true' or LCase(COLUMN_DEFAULT) == 'on' or LCase(COLUMN_DEFAULT) == 'yes':
                COLUMN_DEFAULT = 1
                COLUMN_DEFAULT = 0
            _ret = ' Default (' + COLUMN_DEFAULT + ')'
        elif (_select1 == 128):
            _ret = ' Default (0x' + COLUMN_DEFAULT + ')'
        elif (_select1 == 7):
            if LCase(COLUMN_DEFAULT) == 'now()' or LCase(COLUMN_DEFAULT) == 'date()' or LCase(COLUMN_DEFAULT) == 'time()':
                COLUMN_DEFAULT = 'getdate()'
            if Left(COLUMN_DEFAULT, 1) == '#':
                COLUMN_DEFAULT = Replace(COLUMN_DEFAULT, '#', '\'')
            _ret = ' Default (' + COLUMN_DEFAULT + ')'
        else:
            _ret = ' Default (' + COLUMN_DEFAULT + ')'
        return _ret

    def __defaultStrfilter(self, s):
        _ret = None
        s=str(s.Value)
        while Left(s, 1) == '"':
            s = Mid(s, 2)
        while Right(s, 1) == '"':
            s = Left(s, Len(s) - 1)
        while Left(s, 1) == '\'':
            s = Mid(s, 2)
        while Right(s, 1) == '\'':
            s = Left(s, Len(s) - 1)
        _ret = s
        return _ret

    def __nullStr(self, IS_NULLABLE, tablename, columnName):
        _ret = None
        if IS_NULLABLE.Value:
            if self.__getPrimaryKey(tablename, columnName) == '':
                _ret = ' null '
            else:
                _ret = ' not null '
        else:
            _ret = ' not null '
        return _ret
if __name__ == '__main__':
    a=Ma()
    a.setDbName('J:\\ncs_cs\\src\\access_2000\\data.mdb')
    a.on_Command1_mouseClick()