Reverse engineer MS Access/Jet databases (Python recipe) by Matt Keranen

ActiveState Code (http://code.activestate.com/recipes/52267/)

# jet2sql.py - M.Keranen <mksql@yahoo.com> [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()