Retrieving domain value information

Jen_Zumbado-Hannibal

 

I have perhaps an unusual request. So bear with me. 

I am attempting to write a Python 3 script that will list all domain names from my enterprise geodatabase that start with the letter "e", as well as their domain type, code/min, and description/max, and have them saved in a log file (.csv and .xlsx). At the same time, I also want to capture in another log file the history of only those domains that were altered (e.g., deleted, added, changed, etc.) and when it happened. 

Here is my three-part question: 

1. Does ESRI system tables (SQL Server 2019) keep track of when domain changes happen and what those changes were? 

2. Can a Python 3 script retrieve that info from system tables? 

3. Is there another way to go about this? 

The following code doesn't really give me what I need. Instead, it gives me a list of all the domains that start with "e" yet it adds the date of when I ran the script and not the date when I actually altered the domain. Plus, it doesn't add the header "Date_Modified_2" inside my log file. 

See attached log file (.csv). 

I want it to write: 

DOMAIN_NAME,DATE_MODIFIED,DATE_MODIFIED_2
eGuyType,2023-06-27 13:30:08.858138
eDeviceMounting,2023-06-27 13:30:08.858138,2023-06-29 15:20:00.590812
ePhase,2023-06-27 13:30:08.858138,2023-06-29 15:20:00.590812
eSecConductorType,2023-06-27 13:30:08.858138
eSwitchState,2023-06-27 13:30:08.858138,2023-06-29 15:20:00.590812
eImpedanceUnits,2023-06-27 13:30:08.859136,2023-06-29 15:20:00.590812
eConnectionTypeWyeDelta,2023-06-27 13:30:08.859136,2023-06-29 15:20:00.590812
eSecondaryType,2023-06-27 13:30:08.859136,2023-06-29 15:20:00.590812
ePrimaryType,2023-06-27 13:30:08.859136,2023-06-29 15:20:00.590812
eMountType,2023-06-27 13:30:08.859136

Thanks. 

 

import arcpy
import csv
from openpyxl import Workbook
from openpyxl.styles import Font
import datetime

gdb = r"E:\SDE\OWNER_PROD.sde"
xlsx_filepath = r"E:\ScriptLogs\ExportDomains.xlsx"
csv_filepath = r"E:\ScriptLogs\ExportDomains.csv"
modification_table_csv = r"E:\ScriptLogs\LPDomainHistory.csv"
modification_table_xlsx = r"E:\ScriptLogs\LPDomainHistory.xlsx"

domains = arcpy.da.ListDomains(gdb)

wb = Workbook()
ws = wb.active

# Write the header for domain list in Excel file
header_xlsx = ["DOMAIN_NAME", "DOMAIN_TYPE", "CODE/MIN", "DESCRIPTION/MAX"]
ws.append(header_xlsx)
header_font = Font(bold=True)
for cell in ws[1]:
    cell.font = header_font

# Write the header for the CSV file
header_csv = ["DOMAIN_NAME", "DATE_MODIFIED"]

# Read existing modification dates from the CSV file
modification_dates = {}
if arcpy.Exists(modification_table_csv):
    with open(modification_table_csv, 'r') as csv_file:
        reader = csv.reader(csv_file)
        headers = next(reader)  # Get the headers
        for row in reader:
            domain_name = row[0]
            modification_dates[domain_name] = row[1:]

# Capture domain modification information
for domain in domains:
    if domain.name.startswith("e"):
        if domain.domainType == 'CodedValue':
            coded_values = domain.codedValues

            if domain.name in modification_dates:
                if modification_dates[domain.name][0] != str(coded_values):
                    modification_dates[domain.name].append(str(datetime.datetime.now()))
            else:
                modification_dates[domain.name] = [str(coded_values), str(datetime.datetime.now())]

            for val, desc in coded_values.items():
                ws.append([domain.name, "Coded", val, desc, ""])
        else:
            range_min = domain.range[0]
            range_max = domain.range[1]

            if domain.name in modification_dates:
                if modification_dates[domain.name][0] != str(domain.range):
                    modification_dates[domain.name].append(str(datetime.datetime.now()))
            else:
                modification_dates[domain.name] = [str(domain.range), str(datetime.datetime.now())]

            ws.append([domain.name, "Range", range_min, range_max, ""])

# Save the workbook including both the domains and modification dates
wb.save(xlsx_filepath)

# Save the modification history to the CSV file
with open(modification_table_csv, 'w', newline='') as csv_file:
    writer = csv.writer(csv_file)
    writer.writerow(header_csv)
    for domain_name, modification_dates_row in modification_dates.items():
        row_data = [domain_name] + modification_dates_row
        writer.writerow(row_data)

# Append the new modification dates to the CSV file
header_csv_dict = {}
with open(modification_table_csv, 'r') as csv_file:
    reader = csv.reader(csv_file)
    headers = next(reader)  # Get the headers
    for i, header in enumerate(headers):
        header_csv_dict[header] = i

    if len(modification_dates) > 0:
        for domain_name in modification_dates:
            if domain_name not in header_csv_dict:
                header_csv.append(f"DATE_MODIFIED_{len(header_csv) - 1}")

        with open(modification_table_csv, 'a', newline='') as csv_file:
            writer = csv.writer(csv_file)
            for domain_name, modification_dates_row in modification_dates.items():
                row_data = [domain_name] + modification_dates_row
                writer.writerow(row_data)

# Convert the CSV file to Excel format
wb_csv = Workbook()
ws_csv = wb_csv.active

with open(modification_table_csv, 'r') as csv_file:
    reader = csv.reader(csv_file)
    for row in reader:
        ws_csv.append(row)

wb_csv.save(modification_table_xlsx)

 

 

LPDomainHistory.csv

MobiusSnake

Domains are managed in the GDB_ITEMS system table, with definitions in XML format, very similar (or possibly identical, I can't remember) to what you'd see in an XML Workspace Document.  The details of changes are not tracked by this system table, nor is any kind of history, it has no kind of archiving or anything like that on it.  You would need to track changes yourself, you could potentially do this with database triggers but I'd be very wary about putting my own triggers on GDB system tables, a scheduled task would probably be a safer approach.

You can query the GDB_ITEMS table with a Python search cursor.

Edit to add - I believe there's some Esri doc around this table (and the GDB_ITEMTYPES table, which you may want to look at as well), but besides that a good way to familiarize yourself with the tables would be by opening them up in SQL Server Management Studio first.