In our first article of this series published last month we covered the basics of ESRI versioning including how adds, deletes, and updates are managed within the versioned geodatabase. We explained that a version in the geodatabase is not a copy of the base business tables but is instead simply a mechanism for tracking the delta changes to the base business tables by using the Add & Delete (A&D) tables. And we took a look at how to locate the corresponding A&D tables for each base business table and the structure of each.
Let’s begin this article by taking a closer look at the SDE_STATE_ID (state id) column that is used within the A&D tables. We’ve previously noted that the state id is a numeric value tracked on both the Add table and the Delete table for each versioned business table:
Example Add table: Example Delete table:
Each time an edit is performed within an ESRI versioned geodatabase, a state id is assigned to that edit and the edited record is added to the Add and/or Delete table. To provide an example, in ArcMap I have created a version called SDE.TestVersioning that is a child of SDE.Default. Within that version I start an edit session and add two brand new records to the table shown above. The database will assign the next available state ids to my new records and add them to the appropriate Add table. In my test geodatabase the next available state id is 475075. I can query the Add table to check out the records along with their state ids. My example is using SQL Server and I’ve added description attributes to the records to help identify them:
select OBJECTID, Description, SDE_STATE_ID from [DATABASE].[BUSINESS DATA OWNER].A[REGID];
As you can see from the above query, my records were added to the Add table and automatically given sequential state ids - 475075 and 475076. It should be noted that I have not yet saved my edit session in ArcMap. This allows for the undo/redo functionality within the ArcMap edit session because each edit is uniquely identified by a state id. If we now SAVE the edit session and run the same query we will see that ALL of the edits in the edit session are assigned the same state id corresponding to the highest state id used - 475076 in this case:
After you have saved your edit session the undo/redo for those individual edits is no longer available within ArcMap because all of the edits now have the same state id. Next, we will continue this example by deleting the first record we created above - "Test Add 1". If we re-query the Add table, nothing changes. However, if we query the Delete table we will now see a new record:
select * from [DATABASE].[BUSINESS DATA OWNER].D[REGID]
Let’s now compare the Delete table entry to the Add table entry. Three things should be noted here:
The SDE_DELETES_ROW_ID in the DELETE table is the OBJECTID of the record (unique ESRI identifier.)
The SDE_STATE_ID in the DELETE table corresponds to the state the record was added. In this case 475076 is the SDE_STATE_ID of the same record from our ADD table.
The DELETED_AT column contains the new state id that was assigned to this edit. This is a new unique state id that has not been used before, 475077, which is the next available state id in the system.
These matched ids allow the system to show that the record was added at state 475076 and then was subsequently deleted at state 475077. Because these edits are all being tracked by individual states, the system can manage the visibility of each edit. In other words, if I viewed the data at state 475076 I would see the record on the map but if I viewed the data at state 475077 the record would be gone (deleted). Keep that in mind as we continue.
Finally, let’s review an update to the second record we created above. In my ArcMap session I have updated the Description value to be "Test Add 2 - Updated!". If we review the corresponding records in the Add and Delete tables we should see a delete record corresponding to the original state id plus an add record with the new values (see part 1 of the versioning articles for more information):
The first thing to note above is that the OBJECTID / SDE_DELETES_ROW_ID is the first matched value we can use to tie these records together. Next we will examine the state ids. In the ADD table (top) we now see TWO records with the same OBJECTID but with different state ids. The first state id, 475076, shows the record as it was originally added to the geodatabase with a description of "Test Add 2". We can then match that state id to the same SDE_STATE_ID value in the DELETE table which shows that this record has been deleted (the SDE_STATE_ID in the DELETE table corresponds to the state the record was added). And the DELETED_AT value in the DELETE table, 475078, gives us the state id where the record was deleted. We can then match that value of 475078 back to the ADD table SDE_STATE_ID to find the corresponding new record with the updated description of "Test Add 2 - Updated!". Therefore if we view the data at state 475078 the geodatabase would show the record on the map with the updated description of "Test Add 2 - Updated!" because it applies the edits by matching up the state ids.
In the above examples we have seen how records are added, deleted, and updated within the A&D tables in the geodatabase. It might be useful to re-read this section a few times to make sure you have a solid grasp on how this works because this is the basis of all versioning.
In these examples we noted that the geodatabase assigned the next available state id to the edits being performed in ArcMap. Next we’ll take a look at where those state ids are managed. There is a states table in your geodatabase that contains all of the current states which correspond to all of the edits that have occurred in your geodatabase. You can view the records in your states table using the following queries:
select * from SDE.states order by STATE_ID;
select * from SDE.SDE.SDE_states order by STATE_ID;
The following picture shows the table structure of the states table:
Here are the uses of each of these columns:
The state_id column captures the same state id that we’ve been reviewing in our above editing examples. It is a unique id that is created for each edit in each edit session.
The owner is the user who performed the edit.
The creation_time is the time the state was created which will match when the edit session was started.
The closing_time is the time when the state was closed which will match when the edit session was saved.
The parent_state_id is the preceding state in the state lineage - this is discussed in more detail below.
The lineage_name is a foreign key to another table which defines the full state lineage for any given state.
Each versioned geodatabase begins with an initial state with a state id of 0. This state always exists in the geodatabase and is the ultimate parent of all of the other state records in the states table. As edits are performed and new states are created the parent_state_id value is always populated with the next logical parent state which will eventually tie back to state 0. If we review the states corresponding to our example edits above we find the following records:
Our most recent saved edit session had a state id of 475078 and this is the bottom record above. The parent_state_id of this state is 475076 and we can trace that record back to the logical parent state of 475076 which corresponds to the first time we saved in ArcMap. The parent_state_id of this state is 0 and we can trace that record back to the logical parent state of state 0 which is the base state of the geodatabase. To reiterate the point, ALL state records correspond to edit sessions and ALL states can be traced back to state 0. And follow this closely - state 0 corresponds to the records that exist in the base business tables (i.e. not the Add and Delete tables but the original business table that existed BEFORE the table was registered as versioned). The path that any state takes back to state 0 is called the state lineage. This is just like tracing your family lineage back to your great, great grandfather except that we are tracing parent states as opposed to parent people.
There can be many different state lineages in a versioned geodatabase. When we query data from the base business table (which correspond to state 0) plus all of the edits tied to a specific state lineage we get... you guessed it, drum roll please... an SDE VERSION! We use names like SDE.TestVersioning to describe a version but all these names do for us is give us an easy to remember text-based name for a state id/lineage. This can be seen by querying the SDE versions table:
select * from SDE.versions where name='TestVersioning';
select * from SDE.SDE.SDE_versions where name='TestVersioning';
As you can see from the results above, the state_id for the SDE.TestVersioning version is 475078. Hopefully that number rings a bell because it matches the state id from the final save we performed in our example edit session above. When we traverse the state lineage back to state 0 we get all of the edits we performed in our version. And this is exactly what ArcMap is doing when you display data loaded from a specific transactional version.
The topic of the ESRI state id is a bit complex but I hope that these examples are helping it to make some sense. The state id is at the core of what versioning is and while we never see these values in ArcMap, they are what makes all the magic happen behind the scenes. In the next article we will delve into what happens to the edits in the A&D tables when we post data as well as how the all-important SDE compress operation cleans up the geodatabase.