# jet2sql.py - M.Keranen [07/12/2000] # -------------------------------------------------------------------- # Creates ANSI SQL DDL from a MS Jet database file, useful for reverse # engineering database designs in E/R tools. # # Requires DAO 3.6 library. # -------------------------------------------------------------------- # Usage: python jet2sql.py infile.MDB outfile.SQL import sys, string, pythoncom, win32com.client const = win32com.client.constants daoEngine = win32com.client.Dispatch('DAO.DBEngine.36') class jetReverse: def __init__ (self, infile): self.jetfilename=infile self.dtbs = daoEngine.OpenDatabase(infile) return def terminate(self): return def writeTable(self, currTabl): self.writeLine('\ncreate table ' + chr(34) + currTabl.Name + chr(34),"",1) self.writeLine('(',"",1) # Write Columns cn=0 for col in currTabl.Fields: cn = cn +1 self.writeColumn(col.Name, col.Type, col.Size, col.Required, col.Attributes, col.DefaultValue, col.ValidationRule, currTabl.Fields.Count-cn) # Validation Rule tablRule = currTabl.ValidationRule if tablRule <> "": tablRule = " check(" + tablRule + ") " self.writeLine("",",",1) # add a comma and CR previous line self.writeLine(tablRule,"",0) # Primary Key pk=self.getPrimaryKey(currTabl) if pk <> "": self.writeLine("",",",1) # add a comma and CR previous line self.writeLine(pk,"",0) # End of table self.writeLine("","",1) # terminate previous line self.writeLine(');',"",1) # Write table comment try: sql = currTabl.Properties("Description").Value except pythoncom.com_error: sql="" if sql <> "": sql = "comment on table " + chr(34) + currTabl.Name + chr(34) + " is " + chr(34) + sql + chr(34) +";" self.writeLine(sql,"",1) # Write column comments for col in currTabl.Fields: try: sql = col.Properties("Description").Value except pythoncom.com_error: sql="" if sql <> "": sql = "comment on column " + chr(34) + currTabl.Name + chr(34) + "." + chr(34) + col.Name + chr(34) + " is " + chr(34) + sql + chr(34) + ";" self.writeLine(sql,"",1) # Write Indexes self.writeIndexes(currTabl) return def writeColumn(self, colName, colType, length, requird, attributes, default, check, colRix): # colRix: 0 based index of column from right side. 0 indicates rightmost column if colType == const.dbByte: dataType = "Byte" elif colType == const.dbInteger: dataType = "Integer" elif colType == const.dbSingle: dataType = "Single" elif colType == const.dbDouble: dataType = "Double" elif colType == const.dbDate: dataType = "DateTime" elif colType == const.dbLongBinary: dataType = "OLE" elif colType == const.dbMemo: dataType = "Memo" elif colType == const.dbCurrency: dataType = "Currency" elif colType == const.dbLong: if (attributes & const.dbAutoIncrField): dataType = "Counter" else: dataType = "LongInteger" elif colType == const.dbText: if length == 0: dataType = "Text" else: dataType = "char("+str(length)+")" elif colType == const.dbBoolean: dataType = "Bit" if default == "Yes": default = "1" else: default = "0" else: if length == 0: dataType = "Text" else: dataType = "Text("+str(length)+")" if default <> "": defaultStr = "default " + default + " " else: defaultStr = "" if check <> "": checkStr = "check(" + check + ") " else: checkStr = "" if requird or (attributes & const.dbAutoIncrField): mandatory = "not null " else: mandatory = "" sql = " " + chr(34) + colName + chr(34) + " " + dataType + " " + defaultStr + checkStr + mandatory if colRix > 0: self.writeLine(sql,",",1) else: self.writeLine(sql,"",0) return def getPrimaryKey(self, currTabl): # Get primary key fields sql = "" for idx in currTabl.Indexes: if idx.Primary: idxName = idx.Name sql = " primary key " cn=0 for col in idx.Fields: cn=cn+1 sql = sql + chr(34) + col.Name + chr(34) if idx.Fields.Count > cn : sql = sql + "," return sql def writeIndexes(self, currTabl): # Write index definition nIdx = -1 for idx in currTabl.Indexes: nIdx = nIdx + 1 idxName = idx.Name tablName = currTabl.Name if idx.Primary: idxName = tablName + "_PK" elif idxName[:9] == "REFERENCE": idxName = tablName + "_FK" + idxName[10:] else: idxName = tablName + "_IX" + str(nIdx) sql = "create " if idx.Unique: sql = sql + "unique " if idx.Clustered: sql = sql + "clustered " sql = sql + "index " + chr(34) + idxName + chr(34) sql = sql + " on " + chr(34) + tablName + chr(34) + " (" # Write Index Columns cn=0 for col in idx.Fields: cn = cn + 1 sql = sql + chr(34) + col.Name + chr(34) if col.Attributes & const.dbDescending: sql = sql + " desc" else: sql = sql + " asc" if idx.Fields.Count > cn: sql = sql + "," sql=sql + " );" self.writeLine(sql,"",1) return def writeForeignKey(self, currRefr): # Export foreign key sql = "\nalter table " + chr(34) + currRefr.ForeignTable + chr(34) self.writeLine(sql,"",1) sql = " add foreign key (" cn = 0 for col in currRefr.Fields: cn = cn + 1 sql = sql + chr(34) + col.ForeignName + chr(34) if currRefr.Fields.Count > cn: sql = sql + "," sql = sql + ")" self.writeLine(sql,"",1) sql = " references " + chr(34) + currRefr.Table + chr(34) + " (" cn = 0 for col in currRefr.Fields: cn = cn + 1 sql = sql + chr(34) + col.Name + chr(34) if currRefr.Fields.Count > cn: sql = sql + "," sql = sql + ")" if (currRefr.Attributes & const.dbRelationUpdateCascade) <> 0: sql = sql + " on update cascade" if (currRefr.Attributes & const.dbRelationDeleteCascade) <> 0: sql = sql + " on delete cascade" sql=sql+";" self.writeLine(sql,"",1) return def writeQuery(self, currQry): sql = "\ncreate view " + chr(34) + currQry.Name + chr(34) + " as" self.writeLine(sql,"",1) # Write Query text sql=string.replace(currQry.SQL,chr(13),"") # Get rid of extra linefeeds self.writeLine(sql,"",1) # Write Query comment try: sql = currQry.Properties("Description").Value except pythoncom.com_error: sql="" if sql <> "": sql = "comment on table " + chr(34) + currQry.Name + chr(34) + " is " + chr(34) + sql + chr(34) self.writeLine(sql,"",1) return def writeLine(self,strLine, delimit, newline): # Used for controlling where lines terminate with a comma or other continuation mark sqlfile.write(strLine) if delimit: sqlfile.write(delimit) if newline: sqlfile.write('\n') return if __name__ == '__main__': if len(sys.argv)<2: print "Usage: jet2sql.py infile.mdb outfile.sql" else: jetEng = jetReverse(sys.argv[1]) outfile = sys.argv[2] sqlfile = open(outfile,'w') print "\nReverse engineering %s to %s" % (jetEng.jetfilename, outfile) # Tables sys.stdout.write("\n Tables") for tabl in jetEng.dtbs.TableDefs: sys.stdout.write(".") if tabl.Name[:4] <> "MSys" and tabl.Name[:4] <> "~TMP": jetEng.writeTable(tabl) # Relations / FKs sys.stdout.write("\n Relations") for fk in jetEng.dtbs.Relations: sys.stdout.write(".") jetEng.writeForeignKey(fk) # Queries sys.stdout.write("\n Queries") for qry in jetEng.dtbs.QueryDefs: sys.stdout.write(".") jetEng.writeQuery(qry) print "\n Done\n" # Done sqlfile.close() jetEng.terminate()