| I l@ve RuBoard |
|
8.14 Module: jet2sql桟reating a SQL DDL from an Access DatabaseCredit: Matt Keranen If you need to migrate a Jet (Microsoft Access .mdb) database to another DBMS system, or need to understand the Jet database structure in detail, you must reverse engineer from the database a standard ANSI SQL DDL description of its schema. Example 8-1 reads the structure of a Jet database file using Microsoft's DAO services via Python COM and creates the SQL DDL necessary to recreate the same structure (schema). Microsoft DAO has long been stable (which in the programming world is almost a synonym for dead) and will never be upgraded, but that's not really a problem for us here, given the specific context of this recipe's use case. Additionally, the Jet database itself is almost stable, after all. You could, of course, recode this recipe to use the more actively maintained ADO services instead of DAO (or even the ADOX extensions), but my existing DAO-based solution seems to do all I require, so I was never motivated to do so, despite the fact that ADO and DAO are really close in programming terms. This code was originally written to aid in migrating Jet databases to larger RDBMS systems through E/R design tools when the supplied import routines of said tools missed objects such as indexes and FKs. A first experiment in Python, it became a common tool. Note that for most uses of COM from Python, for best results, you need to ensure that Python has read and cached the type library. Otherwise, for example, constant names cannot be used, since only type libraries hold those names. You would have to use numeric literals instead, seriously hampering readability and usability (not to mention the bother of finding out which numeric literals you should use, when all available documentation is written in terms of symbolic constants). In recent releases of win32all, the simplest way to make sure that the type library has indeed been cached is to substitute, in lieu of the statement in the recipe: daoEngine = win32com.client.Dispatch('DAO.DBEngine.36')
the equivalent statement: daoEngine = win32com.client.gencache.EnsureDispatch('DAO.DBEngine.36')
EnsureDispatch ensures the relevant type library is cached, instantiates the requested COM server, and returns a reference just like Dispatch would. Alternatively, you can use makepy.py, either by hand or through the Tools menu of PythonWin (in this case, from the COM Makepy Utility, select an entry such as Microsoft DAO 3.6 Library). Yet another possibility is calling win32com.client.gencache.EnsureModule, but this is inelegant and unwieldy, because you need to find out the UUID and version numbers for the (registered) type library you want to ensure is cached. The newer EnsureDispatch is far handier, since it takes a good old ProgID string, which is easier to find out, more readable, and more compact. Microsoft's widespread Jet (a.k.a. Access) database engine isn't quite SQL-compliant, but it comes close. Using this engine, all you need to migrate a database to a standard SQL relational database is a little help in reverse engineering the details of the structure, as shown in Example 8-1. Example 8-1. Creating a SQL DDL from an Access database# jet2sql.py - Matthew C Keranen <mck@mpinet.net> [07/12/2000]
# --------------------------------------------------------------------
# Creates ANSI SQL DDL from a MS Jet database file. Useful to reverse
# engineer a database's design in various 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')
quot = chr(34)
class jetReverse:
def _ _init_ _(self, infile):
self.jetfilename=infile
self.dtbs = daoEngine.OpenDatabase(infile)
def terminate(self):
pass
def writeTable(self, currTabl):
self.writeLine('\ncreate table '
+ quot + currTabl.Name + quot, "", 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
self.writeLine(tablRule, "", 0)
# Primary key
pk=self.getPrimaryKey(currTabl)
if pk <> "":
self.writeLine("", ",", 1) # add a comma and CR
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 " + quot + currTabl.Name + quot +
" is " + quot + sql + quot + ";")
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 " + quot + currTabl.Name
+ quot + "." + quot + col.Name + quot +
" is " + quot + sql + quot + ";")
self.writeLine(sql,"",1)
# Write indexes
self.writeIndexes(currTabl)
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 = (" " + quot + colName + quot + " " + dataType
+ " " + defaultStr + checkStr + mandatory)
if colRix > 0:
self.writeLine(sql, ",", 1)
else:
self.writeLine(sql, "", 0)
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 + quot + col.Name + quot
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 " + quot + idxName + quot
sql = sql + " on " + quot + tablName + quot + " ("
# Write index columns
cn = 0
for col in idx.Fields:
cn = cn + 1
sql = sql + quot + col.Name + quot
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)
def writeForeignKey(self, currRefr):
# Export foreign key
sql = "\nalter table " + quot + currRefr.ForeignTable + quot
self.writeLine(sql, "", 1)
sql = " add foreign key ("
cn = 0
for col in currRefr.Fields:
cn = cn + 1
sql = sql + quot + col.ForeignName + quot
if currRefr.Fields.Count > cn: sql = sql + ","
sql = sql + ")"
self.writeLine(sql, "", 1)
sql = " references " + quot + currRefr.Table + quot + " ("
cn = 0
for col in currRefr.Fields:
cn = cn + 1
sql = sql + quot + col.Name + quot
if currRefr.Fields.Count > cn: sql = sql + ","
sql = sql + ")"
if currRefr.Attributes & const.dbRelationUpdateCascade:
sql = sql + " on update cascade"
if currRefr.Attributes & const.dbRelationDeleteCascade:
sql = sql + " on delete cascade"
sql = sql + ";"
self.writeLine(sql, "", 1)
def writeQuery(self, currQry):
sql = "\ncreate view " + quot + currQry.Name + quot + " as"
self.writeLine(sql, "", 1)
# Write query text
sql = string.replace(currQry.SQL, chr(13), "") # Remove extra \ns
self.writeLine(sql, "", 1)
# Write query comment
try: sql = currQry.Properties("Description").Value
except pythoncom.com_error: sql = ""
if sql <> "":
sql = ("comment on table " + quot + currQry.Name +
quot + " is " + quot + sql + quot)
self.writeLine(sql,"",1)
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')
if _ _name_ _ == '_ _main_ _':
if len(sys.argv) != 3:
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:
if tabl.Name[:4] != "MSys" and tabl.Name[:4] != "~TMP":
sys.stdout.write(".")
jetEng.writeTable(tabl)
else:
sys.stdout.write(",")
# 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( )
|
| I l@ve RuBoard |
|