topic Re: Python or SQL for 'Version Changes'? in Data Management Questions

Python or SQL for 'Version Changes'?

FredSpataro — Thu, 30 Nov 2023 22:58:20 GMT

Hi All,

There's an ArcPro tool called 'Version Changes' on the Versioning ribbon (see picture below). Is there a python/gp tool or SQL template to achieve the same thing?

We need to implement some automated QA/QC and auditing tasks. It would be 'waaaayyyy' better if we could grab this info without asking the user to press the button.

Thanks

Re: Python or SQL for 'Version Changes'?

DanPatterson — Thu, 30 Nov 2023 23:05:40 GMT

arcpy can access all arctoolbox tools such as those in

An overview of the Versions toolset—ArcGIS Pro | Documentation

or via arcpy directly if this is the versions you are referring to

https://pro.arcgis.com/en/pro-app/latest/arcpy/data-access/listversions.htm

Version—ArcGIS Pro | Documentation

Re: Python or SQL for 'Version Changes'?

MarceloMarques — Thu, 30 Nov 2023 23:45:15 GMT

ArcGIS Pro "Version Changes" is not exposed as a geoprocessing tool therefore it is also not exposed in arcpy.

An overview of the Versions toolset—ArcGIS Pro | Documentation

An overview of ArcPy functions—ArcGIS Pro | Documentation

-------------------------------

But you can use ArcGIS Pro SDK for .NET.

ArcGIS Pro SDK | Documentation

FeatureClass Class—ArcGIS Pro - Featureclass.Differences

Solved: Version Differences - Esri Community - this link has a code example.

-------------------------------

You could also use FME Workbench.

Extracting Differences from an ArcSDE Geodatabase (safe.com)

I am not sure if this is exposed in the ArcGIS Pro Data Interoperability Extension, but if it is then you could use an ArcGIS Pro Model Builder to call the ArcGIS Pro Data Interoperability Transformer.

-------------------------------

There in no way to see the version changes using SQL.

Edit versioned data in SQL Server using SQL—ArcMap | Documentation (arcgis.com)

-------------------------------

I hope this helps.

Re: Python or SQL for 'Version Changes'?

FredSpataro — Thu, 30 Nov 2023 23:53:02 GMT

@MarceloMarques thanks... i knew it wasn't in the version toolbox (hoping it might be somewhere else hidden...)

I'll look into the SDK, i have a vague recollection of doing something way back with the ArcObejcts SDK. I profiled the database when the button was run, hoping for some SQL insight but it's really complicated. We've got a start with some SQL comparing on the multiversion views ... just seems like that button would be useful from arcpy or sql...

Re: Python or SQL for 'Version Changes'?

Bud — Fri, 01 Dec 2023 16:45:10 GMT

Might be worthwhile to submit an idea/enhancement request?

Re: Python or SQL for 'Version Changes'?

MarceloMarques — Fri, 01 Dec 2023 16:44:03 GMT

@FredSpataro - I agree that ArcGIS Pro "Version Changes" needs to be available as an arctoolbox geoprocessing tool and in arcpy for users, if you want to submit an enhancement request then please open an Esri Technical Support Case, I also suggest creating an ArcGIS Idea about the enhancement so other users can vote on the idea, that helps build the case for the enhancement request to get implemented in a future release of ArcGIS Pro.

Re: Python or SQL for 'Version Changes'?

FredSpataro — Fri, 01 Dec 2023 17:38:35 GMT

Done: https://community.esri.com/t5/data-management-ideas/add-version-changes-to-the-versions-toolset/idi-p/1355811/jump-to/first-unread-message

thanks again for the replies.

Re: Python or SQL for 'Version Changes'?

MarceloMarques — Fri, 01 Dec 2023 17:58:36 GMT

@FredSpataro - Thanks for creating the ArcGIS Idea, I have added a few comments to the idea to bring more clarification, I hope it helps.

Re: Python or SQL for 'Version Changes'?

Bud — Fri, 01 Dec 2023 18:17:27 GMT

Could you query the ADDS and DELETES tables, in conjunction with the default version, using SQL to get the information you need?
When clicking that button in the UI, what SQL query does Pro send to the database? Can you look at the SQL logs in the Diagnostic monitor? Or get your DBA to do a SQL trace?
Can you show us more examples of what you want?
Could you mock up something in Excel to show us what the data/result would look like?

Re: Python or SQL for 'Version Changes'?

MarceloMarques — Fri, 01 Dec 2023 18:41:21 GMT

@Bud - The SQL Queries in a traditional versioned and branch versioned geodatabase are very complex SQL Queries, that includes the state lineage number, among many other parameters and values.

If you run a database trace to capture the SQL Queries you will see that the logic is not easy to reproduce, hence making it impossible to manually create SQL Queries to fetch the Geodatabase "Version Changes".

I agree that the ability to use SQL Queries to fetch the Geodatabase "Version Changes" would be a nice enhancement.

@FredSpataro wrote above

"I profiled the database when the button was run, hoping for some SQL insight but it's really complicated. We've got a start with some SQL comparing on the multiversion views ... just seems like that button would be useful from arcpy or sql... "

Re: Python or SQL for 'Version Changes'?

FredSpataro — Fri, 01 Dec 2023 19:32:06 GMT

@Bud yeah, the SQL profile is crazy lots of dynamic sql text and cursor fetches... the As&Ds look simple enough when you see a few edits on a version or two but the live data makes me think I'll miss something unless I know it's perfectly crafted.

Current strategy is:

SQL sproc:

cursor over version names

set_current_version

select OIDs to temp (use editor tracking columns if it helps)

merge against base temp OIDs

do some tweaks for special cases

...

we'll see