attachment:accesssql.py of MicroProj/2005-07-06

   1 ##¸Ä±àÂíºìȨ2005-07-06
   2 ##Ô­×÷Õߣº2004-11-18    ====MiniAcces Editor1.0part2 Access SQL½Å±¾±àдÆ÷(V37 PaintBlue.Net 2004 Acp Code)=========
   3 from vb2py.vbfunctions import Variant,String,Chr,IsNumeric,Len,Instr,CreateObject,vbCrLf,Mid,vbForRange,LCase,Left,Right
   4 import Module1
   5 import win32com.client
   6 InStr=Instr
   7 def IsNull(a):
   8     if a.Value==None:
   9         return(1)
  10     else:
  11         return(0)
  12 def Replace(a,b,c):
  13     return(a)
  14 class Ma:
  15     def __init__(self):
  16         self.db_name='J:\\nb\\cs\\access2000\\data.mdb'
  17         self.rs=None
  18     def setDbName(self,mdbName):
  19         self.db_name=mdbName
  20     def on_Command1_mouseClick(self, event=None):
  21         db_name = self.db_name
  22         self.createAccessSql(db_name)
  23     def createAccessSql(self,db_name):
  24         remchar=""
  25         TtempStr=""
  26         TableStr=""
  27         self.rs = CreateObject('adodb.recordSet')
  28         self.__connstr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + db_name
  29         self.__conn = CreateObject('ADODB.Connection')
  30         self.__conn.Open(self.__connstr)
  31         #±àд±í/Ë÷Òý¶ÔÏó
  32         tbls = self.__conn.openSchema(20)
  33         tbls.Filter = ' TABLE_TYPE=\'TABLE\' '
  34         while not tbls.EOF:
  35             TableStr = TableStr + '|' + tbls.Fields['TABLE_Name'].Value
  36             tbls.MoveNext()
  37         tbls.Filter = 0
  38         tbls.Close()
  39         tbls = None
  40         TableStr = Mid(TableStr, 2)
  41         isexec=0
  42         if TableStr <> '':
  43             tabsArr = TableStr.split('|')
  44             ub = len(tabsArr)
  45             for i in range(ub):
  46                 TtempStr = TtempStr + self.CreatTableSql(tabsArr[i]) + vbCrLf + vbCrLf
  47         f=open("cmd.py","w")
  48         f.write("cmds=["+TtempStr+"]")
  49         f.close()
  50         print TtempStr
  51     #~ def __CreatViewSql(self, isexec):
  52         #~ _ret = None
  53         #~ tmpstr=""
  54         #~ cols = self.__conn.openSchema(23)
  55         #~ cols.Filter = 0
  56         #~ while not cols.EOF:
  57             #~ TmpStr1 = ''
  58             #~ VIEW_DEFINITION = Replace(cols('VIEW_DEFINITION').Value, Chr(13), '')
  59             #~ print "=================",VIEW_DEFINITION
  60             #~ VIEW_DEFINITION = Replace(VIEW_DEFINITION, Chr(10), ' ')
  61             #~ VIEW_DEFINITION = Left(VIEW_DEFINITION, Len(VIEW_DEFINITION) - 1)
  62             #~ VIEW_DEFINITION = self.__TransView(cols('TABLE_NAME'), VIEW_DEFINITION)
  63             #~ TmpStr1 = 'Create view [dbo].[' + cols('TABLE_NAME').Value + '] As ' + VIEW_DEFINITION + ''
  64             #~ if isexec == 1:
  65                 #~ TmpStr1 = 'CONN.execute("' + TmpStr1 + '")'
  66             #~ tmpstr = tmpstr + vbCrLf + TmpStr1
  67             #~ if isexec == 0:
  68                 #~ tmpstr = tmpstr + vbCrLf + ' go'
  69             #~ cols.MoveNext()
  70         #~ cols.Close()
  71         #~ cols = None
  72         #~ _ret = tmpstr
  73         #~ return _ret
  74     #~ def __TransView(self, viewName, Str):
  75         #~ _ret = None
  76         #~ s = LCase(Str)
  77         #~ s = Replace(s, Chr(9), ' ')
  78         #~ s = Replace(s, Chr(32), ' ')
  79         #~ s = Replace(s, Chr(10), ' ')
  80         #~ s = Replace(s, Chr(13), '')
  81         #~ s = Replace(s, ';', ' ')
  82         #~ while InStr(s, '  ') > 0:
  83             #~ s = Replace(s, '  ', ' ')
  84         #~ s = Replace(s, 'count(*)', 'count(*) as count_x')
  85         #~ if InStr(LCase(s), '* from') == 0:
  86             #~ _ret = s
  87         #~ else:
  88             #~ _ret = Replace(s, '* from', self.__GetviewColumnStr(viewName) + ' from')
  89         #~ #rw GetviewColumnStr(viewName),1
  90         #~ #rw instr(lcase(S),"* from"),1
  91         #~ return _ret
  92     #~ def __GetviewColumnStr(self, viewName):
  93         #~ _ret = None
  94         #~ chg = False
  95         #~ #rw "[" & viewName & "]",0
  96         #~ rs = CreateObject('adodb.recordset')
  97         #~ #rw "select * from [" & tablename & "] where 1=0",1
  98         #~ rs.Open('[' + viewName + ']', self.__conn)
  99         #~ if rs.fields.Count > 0:
 100             #~ tmpstr = rs(0).Name
 101             #~ for i in vbForRange(1, rs.fields.Count - 1):
 102                 #~ tmpstr = tmpstr + ',' + rs(i).Name
 103             #~ tmpstr = LCase(tmpstr)
 104             #~ arr = Split(tmpstr, ',')
 105             #~ for i in vbForRange(0, UBound(arr)):
 106                 #~ tmp = arr[i]
 107                 #~ arr[i] = '[' + arr[i] + ']'
 108                 #~ if InStr(arr[i], '.') > 0:
 109                     #~ arr[i] = Replace(arr(i), '.', '].[')
 110                     #~ arr[i] = arr[i] + ' as ' + Replace(tmp, '.', '_')
 111                     #~ chg = True
 112             #~ if chg:
 113                 #~ _ret = Join(arr, ',')
 114             #~ else:
 115                 #~ _ret = '*'
 116         #~ else:
 117             #~ _ret = ''
 118         #~ return _ret
 119 
 120     def CreatTableSql(self, tablename):
 121         _ret = None
 122         isexec=0
 123         cols = self.__conn.openSchema(4)
 124         splitchar = ''
 125         splitchar1 = ''
 126         cols.Filter = 'Table_name=\'' + tablename + '\''
 127         if cols.EOF:
 128             return _ret
 129         n = 0
 130         # ±àд±í½Å±¾
 131         autoclumn = self.__GetAutoincrementCoulmnT(tablename)
 132         TmpStr1 = '"""CREATE TABLE [' + tablename + '] (' + splitchar1 + vbCrLf
 133         if autoclumn <> None:
 134             autoclumnStr = '    ' + splitchar + '[' + autoclumn + '] autoincrement'
 135         n = 0
 136         while 1:
 137             n = n + 1
 138             cols.Filter = 'Table_name=\'' + str(tablename) + '\' and ORDINAL_POSITION=' + str(n)
 139             if cols.EOF:
 140                 break
 141             if n > 1:
 142                 TmpStr1 = TmpStr1 + ',' + splitchar1 + vbCrLf
 143             if autoclumn == cols('Column_name'):
 144                 TmpStr1 = TmpStr1 + autoclumnStr
 145             else:
 146                 #print cols('Column_name')
 147                 #print splitchar
 148                 s1=self.__datatypeStr(cols('DATA_TYPE'), cols('CHARACTER_MAXIMUM_LENGTH'))
 149                 s1=LCase(s1)
 150                 s2=self.__nullStr(cols('IS_NULLABLE'), tablename, cols('Column_name'))
 151                 s3=""##self.__defaultStr(cols('DATA_TYPE'), cols('COLUMN_DEFAULT'), isexec)
 152                 TmpStr1 = TmpStr1 + ' ' + splitchar + '[' + str(cols('Column_name')) + '] ' + s1 + s3 + s2
 153             cols.MoveNext()
 154         TmpStr1 = TmpStr1 + splitchar1 + vbCrLf + '  ' + splitchar + ') '
 155         cols.Close()
 156         TmpStr1 = TmpStr1 + splitchar1 + vbCrLf + '' + splitchar + '""",'
 157         tmpstr=""
 158         tmpstr = tmpstr + vbCrLf + TmpStr1
 159         # ±àдË÷Òý½Å±¾
 160         s11=self.__getInxArr(tablename)
 161         InxArr = s11.split(',')
 162         cols = self.__conn.openSchema(12)
 163         for i in range(len(InxArr)):
 164             cols.Filter = 'Table_name=\'' + tablename + '\' and index_name=\'' + InxArr[i] + '\''
 165             kstr = ''
 166             TmpStr1 = ''
 167             if not self.__isForeignIndex(tablename, InxArr[i]):
 168                 while not cols.EOF:
 169                     kstr = kstr + ',[' + cols('column_name').Value + '] ' + self.__GetInxDesc(tablename, InxArr[i], cols('column_name'))
 170                     cols.MoveNext()
 171                 if self.__isPrimaryKey(tablename, InxArr[i]):
 172                     TmpStr1 = TmpStr1 + '"""Alter TABLE [' + tablename + ']  ADD Primary Key(' + Mid(kstr, 2) + ') [PK_' + tablename + ']""",'
 173                 else:
 174                     TmpStr1 = TmpStr1 + '"""CREATE '
 175                     if self.__isUnique(tablename, InxArr[i]):
 176                         TmpStr1 = TmpStr1 + 'Unique '
 177                     TmpStr1 = TmpStr1 + 'INDEX [' + InxArr[i] + '] on [' + tablename + '](' + Mid(kstr, 2) + ') """,'
 178                 TmpStr1 = TmpStr1 + vbCrLf 
 179                 tmpstr = tmpstr + vbCrLf + TmpStr1
 180         cols.Close()
 181         cols.Filter = 0
 182         _ret = tmpstr
 183         return _ret
 184     def __GetColumnStr(self, tablename):
 185         _ret = None
 186         rs = CreateObject('adodb.recordset')
 187         #rw "select * from [" & tablename & "] where 1=0",1
 188         cmd='select * from [' + tablename.Value + '] where 1=0'
 189         print cmd
 190         print self.__conn
 191         print rs
 192         tmpstr=""
 193         rs.Open(cmd, self.__conn)
 194         if rs.fields.Count > 0:
 195             for i in vbForRange(0, rs.fields.Count - 1):
 196                 #rw rs(i).name & "_" & rs(i).type & "<br>",1
 197                 if rs(i).Type <> 205:
 198                     tmpstr = tmpstr + ',' + rs(i).Name
 199             if tmpstr <> '':
 200                 _ret = Mid(tmpstr, 2)
 201                 _ret = ''
 202         else:
 203             _ret = ''
 204         return _ret
 205 
 206     def __Ac2SQLStr(self):
 207         tmpstr = ''
 208         rs = self.__conn.openSchema(20)
 209         rs.Filter = 'TABLE_TYPE=\'TABLE\''
 210         while not rs.EOF:
 211             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>'
 212             NN = NN + 1
 213             rs.MoveNext()
 214         rs.Filter = 0
 215         rs.Close()
 216         rs = None
 217 
 218     def __isForeignIndex(self, tablename, IndexName):
 219         _ret = None
 220         cols = self.__conn.openSchema(27)
 221         cols.Filter = 'FK_TABLE_Name=\'' + tablename + '\' and FK_NAME=\'' + IndexName + '\''
 222         if not cols.EOF:
 223             _ret = True
 224         else:
 225             _ret = False
 226         return _ret
 227 
 228     def __GetInxDesc(self, tablename, IndexName, columnName):
 229         _ret = None
 230         cat = CreateObject('ADOX.Catalog')
 231         cat.ActiveCONNection = self.__connstr
 232         t=cat.Tables('' + tablename + '')
 233         i=t.Indexes('' + IndexName + '')
 234         c=i.Columns('' + columnName.Value + '')
 235         if c.SortOrder == 2:
 236             _ret = 'Desc'
 237         else:
 238             _ret = ''
 239         cat = None
 240         return _ret
 241 
 242     def __getColumArr(self, tablename):
 243         _ret = None
 244         arr = vbObjectInitialize((- 1,), Variant)
 245         n = 0
 246         arr = vbObjectInitialize((n,), Variant)
 247         cols = self.__conn.openSchema(4)
 248         cols.Filter = 'Table_Name=\'' + tablename + '\''
 249         while not cols.EOF:
 250             arr = vbObjectInitialize((n,), Variant, arr)
 251             arr[n] = cols('column_name')
 252             cols.MoveNext()
 253             n = n + 1
 254         cols.Filter = 0
 255         cols.Close()
 256         cols = None
 257         _ret = arr
 258         return _ret
 259 
 260     def __getInxArr1(self, tablename):
 261         _ret = None
 262         arr = vbObjectInitialize((- 1,), Variant)
 263         n = 0
 264         cols = self.__conn.openSchema(12)
 265         cols.Filter = 'Table_Name=\'' + tablename + '\''
 266         while not cols.EOF:
 267             if cols('index_name') <> tmpCol:
 268                 arr = vbObjectInitialize((n,), Variant, arr)
 269                 arr[n] = cols('index_name')
 270                 n = n + 1
 271             tmpCol = cols('index_name')
 272             cols.MoveNext()
 273         cols.Filter = 0
 274         cols.Close()
 275         cols = None
 276         ##self.__getInxArr() = arr
 277         return _ret
 278 
 279     def __getInxArr(self, tablename):
 280         _ret = None
 281         n = 0
 282         tmpCol=None
 283         tmps=""
 284         cols = self.__conn.openSchema(12)
 285         cols.Filter = 'Table_Name=\'' + tablename + '\''
 286         while not cols.EOF:
 287             if cols('index_name') <> tmpCol:
 288                 tmps = tmps + ',' + cols('index_name').Value
 289                 n = n + 1
 290             tmpCol = cols('index_name')
 291             cols.MoveNext()
 292         cols.Filter = 0
 293         cols.Close()
 294         cols = None
 295         _ret = Mid(tmps, 2)
 296         return _ret
 297 
 298     def __isUnique(self, tablename, IndexName):
 299         _ret = None
 300         cols = self.__conn.openSchema(12)
 301         cols.Filter = 'Table_Name=\'' + tablename + '\' and Index_Name=\'' + IndexName + '\' and UNIQUE=True'
 302         if not cols.EOF:
 303             _ret = True
 304         else:
 305             _ret = False
 306         cols.Filter = 0
 307         cols.Close()
 308         cols = None
 309         return _ret
 310 
 311     def __isPrimaryKey(self, tablename, IndexName):
 312         _ret = None
 313         cols = self.__conn.openSchema(12)
 314         cols.Filter = 'Table_Name=\'' + str(tablename) + '\' and Index_Name=\'' + IndexName + '\' and PRIMARY_KEY=True'
 315         if not cols.EOF:
 316             _ret = True
 317         else:
 318             _ret = False
 319         cols.Filter = 0
 320         cols.Close()
 321         cols = None
 322         return _ret
 323 
 324     def __getPrimaryKey(self, tablename, columnName):
 325         _ret = None
 326         cols = self.__conn.openSchema(12)
 327         cols.Filter = 'Table_Name=\'' + str(tablename) + '\' and Column_Name=\'' + str(columnName.Value) + '\' and PRIMARY_KEY=True'
 328         if not cols.EOF:
 329             _ret = cols('INDEX_NAME')
 330             #isPrimaryKey=true
 331         else:
 332             _ret = ''
 333             #isPrimaryKey=false
 334         cols.Filter = 0
 335         cols.Close()
 336         cols = None
 337         return _ret
 338 
 339     def __existPrimaryKey(self, tablename):
 340         _ret = None
 341         cols = self.__conn.openSchema(12)
 342         cols.Filter = 'Table_Name=\'' + tablename + '\' and PRIMARY_KEY=True'
 343         if not cols.EOF:
 344             _ret = True
 345         else:
 346             _ret = False
 347         cols.Filter = 0
 348         cols.Close()
 349         cols = None
 350         return _ret
 351 
 352     def __GetIncrement(self, tablename, columnName):
 353         _ret = ""
 354         cat = CreateObject('ADOX.Catalog')
 355         cat.ActiveCONNection = self.__connstr
 356         print tablename,columnName
 357         _ret = cat.Tables[ tablename ].Columns[ columnName].Properties['Increment']       
 358         cat = None
 359         return _ret
 360 
 361     def __GetSeed(self, tablename, columnName):
 362         _ret = None
 363         cat = Server.CreateObject('ADOX.Catalog')
 364         cat.ActiveCONNection = self.__connstr
 365         _ret = cat.Tables('' + tablename + '').Columns('' + columnName + '').Properties('Seed')
 366         cat = None
 367         return _ret
 368 
 369     def __GetAutoincrementCoulmnT(self, tablename):
 370         _ret = None
 371         self.rs.Open('select * from [' + tablename + '] where 1=0', self.__conn, 0, 1)
 372         for i in vbForRange(0, self.rs.fields.Count - 1):
 373             #if rs(i).Properties("isAutoIncrement")=True then
 374             if self.rs(i).Properties('isAutoIncrement') == True:
 375                 _ret = self.rs(i).Name
 376                 self.rs.Close()
 377                 return _ret
 378         self.rs.Close()
 379         return _ret
 380 
 381     def __datatypeStr(self, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH):
 382         _ret = None
 383         _select0 = DATA_TYPE.Value
 384         #~ print type(CHARACTER_MAXIMUM_LENGTH)
 385         #~ print dir(CHARACTER_MAXIMUM_LENGTH)
 386         #~ print type(CHARACTER_MAXIMUM_LENGTH.Value)
 387         #~ print CHARACTER_MAXIMUM_LENGTH.Value
 388         if (_select0 == 130):
 389             if CHARACTER_MAXIMUM_LENGTH.Value == 0:
 390                 _ret = 'text'
 391             else:
 392                 _ret = 'varchar(' + str(CHARACTER_MAXIMUM_LENGTH.Value) + ')'
 393         elif (_select0 == 17):
 394             _ret = 'tinyint'
 395         elif (_select0 == 2):
 396             _ret = 'Smallint'
 397         elif (_select0 == 3):
 398             _ret = 'integer'
 399         elif (_select0 == 4):
 400             _ret = 'real'
 401         elif (_select0 == 5):
 402             _ret = 'float'
 403         elif (_select0 == 6):
 404             _ret = 'money'
 405         elif (_select0 == 7):
 406             _ret = 'datetime'
 407         elif (_select0 == 11):
 408             _ret = 'bit'
 409         elif (_select0 == 72):
 410             _ret = 'UNIQUEIDENTIFIER'
 411         elif (_select0 == 131):
 412             _ret = 'DECIMAL'
 413         elif (_select0 == 128):
 414             _ret = 'BINARY'
 415         return _ret
 416 
 417     def __defaultStr(self, DATA_TYPE, COLUMN_DEFAULT, isexec):
 418         _ret = None
 419         if IsNull(COLUMN_DEFAULT):
 420             _ret = ''
 421             return _ret
 422         if isexec == 1:
 423             splitchar = '""'
 424         elif isexec == 0:
 425             splitchar = '"'
 426         COLUMN_DEFAULT = self.__defaultStrfilter(COLUMN_DEFAULT)
 427         _select1 = DATA_TYPE
 428         if (_select1 == 130):
 429             COLUMN_DEFAULT = Replace(COLUMN_DEFAULT, '"', splitchar)
 430             _ret = ' Default (\'' + COLUMN_DEFAULT + '\')'
 431         elif (_select1 == 11):
 432             if LCase(COLUMN_DEFAULT) == 'true' or LCase(COLUMN_DEFAULT) == 'on' or LCase(COLUMN_DEFAULT) == 'yes':
 433                 COLUMN_DEFAULT = 1
 434                 COLUMN_DEFAULT = 0
 435             _ret = ' Default (' + COLUMN_DEFAULT + ')'
 436         elif (_select1 == 128):
 437             _ret = ' Default (0x' + COLUMN_DEFAULT + ')'
 438         elif (_select1 == 7):
 439             if LCase(COLUMN_DEFAULT) == 'now()' or LCase(COLUMN_DEFAULT) == 'date()' or LCase(COLUMN_DEFAULT) == 'time()':
 440                 COLUMN_DEFAULT = 'getdate()'
 441             if Left(COLUMN_DEFAULT, 1) == '#':
 442                 COLUMN_DEFAULT = Replace(COLUMN_DEFAULT, '#', '\'')
 443             _ret = ' Default (' + COLUMN_DEFAULT + ')'
 444         else:
 445             _ret = ' Default (' + COLUMN_DEFAULT + ')'
 446         return _ret
 447 
 448     def __defaultStrfilter(self, s):
 449         _ret = None
 450         s=str(s.Value)
 451         while Left(s, 1) == '"':
 452             s = Mid(s, 2)
 453         while Right(s, 1) == '"':
 454             s = Left(s, Len(s) - 1)
 455         while Left(s, 1) == '\'':
 456             s = Mid(s, 2)
 457         while Right(s, 1) == '\'':
 458             s = Left(s, Len(s) - 1)
 459         _ret = s
 460         return _ret
 461 
 462     def __nullStr(self, IS_NULLABLE, tablename, columnName):
 463         _ret = None
 464         if IS_NULLABLE.Value:
 465             if self.__getPrimaryKey(tablename, columnName) == '':
 466                 _ret = ' null '
 467             else:
 468                 _ret = ' not null '
 469         else:
 470             _ret = ' not null '
 471         return _ret
 472 if __name__ == '__main__':
 473     a=Ma()
 474     a.setDbName('J:\\ncs_cs\\src\\access_2000\\data.mdb')
 475     a.on_Command1_mouseClick()