"""Repair and/or compact MDBs using Jet/DAO 3.6
This process tries to compact (and repair if needed) a .MDB file.
I checks if users are connected and shows who and the exit states 
(to get hints about anby workstation corrupting the database).
Before compacting the file, it makes a backup in .zip format.
"""

## the program
import sys, os
import time, traceback, zipfile
from binascii import b2a_hex as tohex
from win32com.client import Dispatch
import pywintypes, win32con, win32file

# special errors
class DBOpenError(Exception): pass
class AbortError(Exception): pass
DBError = pywintypes.com_error


def compactOrRepairDBWithCopy(mdbfilename):
    """Repair/compact the given DB."""

    # check if the file exists
    try:
        f = file(mdbfilename, "rb")
        f.close()
    except:
        print u"The given file '%s' does not exist. " \
          "Please specify a existing .mdb file." % mdbfilename
        pause()
        sys.exit(1)


    print u"""\n
    Compacting/repairing of the Access Database
    ===========================================================

        Make sure that nobody is accessing the database.
    """
    pause()

    try:
        ldbfilename = os.path.splitext(mdbfilename)[0]+".ldb"
        while 1:
            print "\n- Checking if any user connected to the DB\n"
            if not whoIsConnected(mdbfilename): break
            try: os.unlink(ldbfilename)
            except:
                print "\n\nThere are still connected users.\n"
                resp = raw_input("Stop the process? (y/n) [n] ")
                if resp.lower() == "y": raise AbortError
            print "\n\n"

        makeCopy(mdbfilename)
        compactMdb(mdbfilename)
        print u"* Process executed successfully."

    except DBOpenError:
        print u"""\n!!! Error: The DB is still open.
    """
                
    except DBError, exc:
        print u"\n\n!!! Error: %s" % exc[2][2]
        print u"Contact tecnical support. (Record the message text!)"

    except AbortError:
        print u"\n!!! The process has been interrupted."

    except:
        print u"\n\n!!! This program has an error."
        traceback.print_exc()

    print "\n\n"; pause()

def makeCopy(filename):
    ## first, make copy of the file (.zip format)
    targetZip = os.path.splitext(filename)[0]+"_%s.zip" 
    targetZip = targetZip % time.strftime("%Y-%m-%d_%H-%M-%S")
    print "- Making copy of to %s" % targetZip
    zf = zipfile.ZipFile(targetZip, "w", zipfile.ZIP_DEFLATED)
    zf.write(filename)
    zf.close()

def compactMdb(filename):
    """Compact / repair .MDB file"""
    tempMdb = filename + ".tmp"
    # delete file if exists (previous temp file left on drive)
    try: os.unlink(tempMdb)
    except: pass
    # access Jet engine
    jet=Dispatch("DAO.DBEngine.36")
    print u"- Trying to repair and compact %s. "\
        "Please wait, this process takes a bit of time..." % filename
    res = jet.CompactDatabase(filename, tempMdb)
    print u"- Repairing/compacting ok."
    # rename old DB
    print u"- Replacing old DB with new one."
    os.unlink(filename)
    # rename new DB to the original name
    os.rename(tempMdb, filename)

def whoIsConnected(mdbfile):
    """Returns a list of machines still connected to the .mdb (.ldb) file.
    """
    ldbfile = os.path.splitext(mdbfile)[0]+".ldb"
    # read ldb file first
    if not os.access(ldbfile, os.F_OK):
        return []
    users = getConnectionRecords(ldbfile)

    # read database header
    pagesize = 4096
    commitpos = 0x600
    filedata=file(mdbfile,"rb").read(pagesize)
    # read offset 0x15 to see which version
    if ord(filedata[0x15]) == 0:
        # version 3 (Acc97) mdb format
        pagesize = 2048
        version = 3
    else:
        # version 4 (Acc 2k, XP, 2003) mdb format
        version = 4

    # first 2 bytes are for excl. access
    dbcommitstate = tohex(filedata[commitpos:commitpos+2]) 
    # rest for users
    commitwords = filedata[commitpos+2 : commitpos+2+510]  
    # print "DB commit bytes: %s" % dbcommitstate
    for num, user in enumerate(users):
        user=users[num]
        user.state = tohex(commitwords[num*2:num*2+2])
        print u"Connection %s: %-10s connected: %s    exit state: %s  (%s)" % \
              (num, user.name, ["no","yes"][user.loggedOn],
               ["ok","bad"][user.state=="0100"], user.state)
    return users

class ConnectionRecord:
    """Record of who is connected to .mdb file."""
    name=""
    loggedOn=False
    state=None

def getConnectionRecords(ldbfile):
    """Read connection records out of ldbfile.
    A connection record has a name attribute which specifies
    the name of the computer which opened the .mdb file
    and a loggedOn attribute which specifies if the
    Returns a list of those records.
    """
    if isinstance(ldbfile,basestring): ldbfile=file(ldbfile,"rb")
    recordlen=64
    filedata=ldbfile.read()
    filelen=len(filedata)
    pos=0; i=0
    users=[]
    while pos<2:
        mdbfile = "c:/test.mdb"
    else:
        mdbfile = sys.argv[1]
    compactOrRepairDBWithCopy(mdbfile)