##************************************************************************
## Brooke Reams
## www.geospatialanalyst.com
## June 22, 2010
## Unregisters default domains from fields, deletes all feature classes in
##      workspace, and deletes domains based on SQL statement that returns
##      domain names remaining in SDE tables.
##************************************************************************
 
import cx_Oracle, arcgisscripting, os, sys, traceback
gp = arcgisscripting.create()
 
# User defined varialbes
owner = sys.argv[1]
pw = sys.argv[2]
ws = sys.argv[3]
sdeown = sys.argv[4]

try:
    # Set workspace
    gp.Workspace = ws
 
    # Oracle/SQL variables
    orcl = cx_Oracle.connect(owner + "/" + pw)
    curs = orcl.cursor()    
 
    # Loop through all feature classes in ws and run sql statemnt    
    fcList = gp.ListFeatureClasses()
    fc = fcList.Next()

    while fc:
        # Remove owner name from feature class name
        fc = fc.split(".")
        fc = fc[1]
        gp.AddMessage("Current feature class: " + fc)

        # Get feature class ID
        gp.AddMessage("Acquiring feature class ID...")
        sqlID = "select id from " + sdeown + ".gdb_objectclasses where name='" + fc + "' and owner='" + owner + "'"
        # Execute sql statement
        rowsID = curs.execute(sqlID).fetchall()
        for rowID in rowsID:
            fcID = str(rowID[0])

        # Get fields with default domains
        gp.AddMessage("Acquiring fields with default domains...")        
        # Get field names for current feature class if the field has a default domain
        sqlFLD = "select fieldname from " + sdeown + ".gdb_fieldinfo where classid=" + fcID + " and defaultdomainname is not null" 
        # Execute sql statement
        rowsFLD = curs.execute(sqlFLD).fetchall()
        for rowFLD in rowsFLD:
            # Convert domain to string
            fld = str(rowFLD[0])
            # Remove Domain From Field
            gp.AddMessage("Removing default domain from " + fld + " field...")
            gp.RemoveDomainFromField(fc, fld)

        # Delete feature class
        gp.AddMessage("Deleting " + fc + " feature class...")
        gp.Delete_Management(fc)
        gp.AddMessage("")
        gp.AddMessage("")    

        # Refresh Catalog
        gp.RefreshCatalog(ws)

        # Next feature class
        fc = fcList.Next()
 
    # Get remaining domains in workspace
    sqlDMN = "select domainname, owner from " + sdeown + ".gdb_domains where domainname not in (select distinct domainname from " + sdeown + ".gdb_attrrules) order by domainname"
    # Execute SQL statement
    rowsDMN = curs.execute(sqlDMN).fetchall()
 
    # List all domains that meet SQL statement conditions
    for rowDMN in rowsDMN:
        # Convert domain and owner to string
        domain = str(rowDMN[0])
        own = str(rowDMN[1])
        if own == owner:
            # Delete domain
            gp.AddMessage("Deleting " + domain + " domain...")
            gp.DeleteDomain_management(ws, domain)

    # Close the Oracle cursor 
    curs.close()
 
except arcgisscripting.ExecuteError:
    # Get the geoprocessing error messages
    msgs = gp.GetMessage(0)
    msgs += gp.GetMessages(2)
 
    # Return gp error messages for use with a script tool
    gp.AddError(msgs)
 
    # Print gp error messages for use in Python/PythonWin
    print msgs
 
except:
    # Get the traceback object
    tb = sys.exc_info()[2]
    tbinfo = traceback.format_tb(tb)[0]
 
    # Concatenate information together concerning the error into a 
    # message string
    pymsg = tbinfo + "\n" + str(sys.exc_type)+ ": " + str(sys.exc_value)
 
    # Return python error messages for use with a script tool
    gp.AddError(pymsg)
 
    # Print Python error messages for use in Python/PythonWin
    print pymsg