We’ve come to my final installment in Versioning for Dummies. We started off by looking at what versioning is and why you should care about it, then dove deep into the use of the State ID, and last month we reviewed how the reconcile and post operations affect the state tree in your geodatabase. This month we want to hit on another very useful topic that will enable you to consume and manage your geodatabase directly through SQL access (outside of the Esri software).
As you hopefully remember from our past articles, a version name is simply an easy to remember text-based name for a state id/lineage. The state lineage is used to apply certain edits from our Add & Delete (A&D) tables on top of the corresponding business base tables to create the version of the data that we see in ArcMap or ArcCatalog. It is a common misconception that once we post our edits to SDE.Default that they are moved into the base tables but as we demonstrated last month, this is absolutely not true. If I just lost you, go back and do a quick refresher because we aren’t going to dive into it again here.
This scenario often presents a challenge to a business and even a typical IT department because access to the data must go through the Esri tools which can handle the rendering of the versioned data. Users cannot use the same basic SQL skills that they may use in their other standard relational database management systems (RDBMS) such as customer information, accounting, or asset management. When users are not educated about versioning and try to use SQL directly against a geodatabase they can get misleading results from their queries and can even cause data loss/corruption if they execute updates against the geodatabase. There are two typical scenarios we run into on a regular basis:
Scenario 1 – Querying Data Out of the Geodatabase
A user decides to use a SQL query to extract data out of a geodatabase because either it’s easier than going through the Esri tools OR they want to do some complicated joins between tables that are not readily available in the Esri tools. So they write a query against the base tables in the geodatabase and extract their data. Sometimes this works but many times the users get misleading results back.
Recently we had a customer call and tell us that either their post operation or their compress operation was not working. They had posted a record to SDE.Default in ArcMap but when they queried the database they were not seeing the record. Why could they see the record in ArcMap when it did not exist in the database table?
The answer lies in last month’s article on reconciling and posting. As you post data to SDE.Default, that version is simply updated to point to a new state lineage. The edits are not moved into the base table until the edits have been reconciled down into all other versions and then a compress operation is performed. Therefore querying the base tables in a versioned geodatabase is almost never an acceptable way to extract data – even data from SDE.Default.
Scenario 2 – Update Data in the Geodatabase
The second scenario we run into on a regular basis is when a user wants to update data within the geodatabase. They write a simple SQL update statement like they would in any other database – “update SomeTable set Column1=X where Column2=Y”. Simple right? Not in geodatabase. When an update is executed against the base table in a geodatabase you are executing against the state 0 data. If there are outstanding edits against that same record via another state, your edit can be lost completely. Definitely not a sturdy way to make updates.
So are we left with no way to make SQL updates to our geodatabase? Is all of our knowledge of RDBMS SQL useless within an SDE environment? It’s not quite that bad but it is a bit more complicated than just using SQL.
Esri Multiversion Views were created as the answer to these problems. As you might imagine, behind the scenes in an SDE versioned geodatabase there are many database views, stored procedures, and triggers that manage and expose the versioned data to the Esri applications. A Multiversion View exposes this same database process of managing versioned data directly via SQL. This provides us an easy way to query data using SQL from a specific version in the geodatabase. And when used correctly can even allow us to insert, update, and delete from the versioned data as well.
Esri Multiversion Views are not available in our geodatabase by default. We have to create them using the SDE command line. The SDE command line tools can be installed on any computer – i.e. you can install them on your local laptop or workstation, they don’t have to be installed on the same server where the SDE geodatabase is installed (though they usually are). The installation is available on your Esri ArcGIS Server – SDE disc. Be aware that there are different installations based on the RDMBS you are using.
As usual we will focus on SQL Server and Oracle in this series since these are the most common databases used within utilities. Simply install the correct software based on your RDMBS (yes you can install both SQL Server and Oracle to different directories) and cancel the post installation setup if you aren’t actually creating an SDE database or service on your local computer.
Now you are ready to create your Multiversion View. Choose one or more feature or object classes in your geodatabase and use the following SDETABLE syntax to create the views from the command line:
sdetable -o create_mv_view -T [ViewName] -t [OWNER.TABLENAME] -i [SDEService] -s
[SDEServer] -D [SQLDatabaseName] -u [DataOwner] -p [DataOwnerPassword]
sdetable -o create_mv_view -T [ViewName] -t [OWNER.TABLENAME] -i [SDEService] -s
[SDEServer] -u [DataOwner] -p [DataOwnerPassword]
A few notes on the parameters which you may already know… but just in case:
- The SQLDatabaseName parameter is only required for SQL server and should be the name of the SQL Server database where your business data exists (different than the SDE database).
- The SDEService parameter can be the service name – esri_sde, the service port – 5151, or a direct connect string such as sde:sqlserver:instanceName or sde:oracle10g:/;LOCAL=TNSName
- The DataOwner and DataOwnerPassword should correspond to the owner of the feature/object class that you are creating the view for
- The SDEService, SDEServer, and SQLDatabaseName will be the same parameters you use to connect to the geodatabase via ArcCatalog and ArcMap.
- The ViewName parameter specifies the name of the view in the database. We often recommend that our clients name their Multiversion Views with the same name as the underlying feature/object class but with a “_MV” attached to the end of the name. This will make your views easy to organize and find in the database.
Once you have created the MultiVersion View via the SDE command line, log into the geodatabase with ArcCatalog using the data owner and you should see your Multiversion View at the root of the geodatabase. Right-click it and select Privileges. Give permissions on the view to any users or roles that need to access it.
Your Multiversion View is now ready to use and can be accessed directly within your SQL tools including Oracle SQL Plus, Oracle SQL Developer, TOAD, SQL Server Management Studio, a custom application, etc. If you want to view the schema of the Multiversion Views using one of the above tools, simply browse to the Views in the correct database/schema within the database. The Multiversion Views will appear just like any other standard views in the database. Don’t be fooled though, they are anything but standard views.
Now instead of querying the base business tables, you can query your Multiversion Views and get the versioned results. You can validate this by running a simple test. For example let’s assume we have a versioned feature class called Utility.Pole with 100 existing pole features and we have just created a new Multiversion View for this feature class. In ArcMap create a new version and place 5 new poles. From our previous articles we know these are entered into the “add” table behind the scenes. Now post the version to SDE.Default. The edits still exist in the “add” table and are not available in the base business table. We can now prove this out using SQL. First run the following query against the base business table:
select count(*) from Utility.Pole;
This query will return 100 records matching the original count in the feature class. Now run the following query against the Multiversion View:
select count(*) from Utility.Pole_MV;
This query will now return 105 records because it includes our versioned edits that we posted above. The records exist in the “add” table but the Multiversion View has joined that data to the base business table and given us the compiled result.
Note that we did not explicitly specify a version to run our query against. When we just query against a Multiversion View without specifying a version, it will return records from SDE.Default by default (no pun intended). This can be very useful for running queries against our as-built, energized data.
But we can also run queries against specific versions within our geodatabase. To do this we must first call a stored procedure in the database to tell the Multiversion View where to get the data. The syntax varies slightly by RDBMS:
call sde.version_util.set_current_version (‘SDE.Working’);
exec sde.sde.set_current_version ‘SDE.Working’;
The parameter above ‘SDE.Working’ can be changed to any other version in the system that your user has access to. Beware the version name is case sensitive in Oracle. The same version rules apply regarding public, private, and protected versions. If your user can’t access the version in ArcCatalog/ArcMap, it will not be able to access it via SQL.
Using the above proc you can now run Multiversion View SQL queries against any version in your geodatabase. Just remember to reference the _MV (or whatever you name it) view instead of the base business table. This can be super helpful when doing analysis on a batch update in a version, data validation on an edit session, and/or to extract any type of information about edits that have not yet been posted.
For example when we integrate our work management system (WMS) to Telvent Designer™ we use the above stored procedures plus some SQL queries to extract additional data about the GIS features that have been created as part of the design (within the un-posted edit session). Our WMS is web-based and using the Multiversion Views allows us to get this versioned data directly via the database without requiring Esri ArcObjects or any GIS licenses.
The above versioned query tools are very powerful but we can take it to the next level by actually editing versioned data via SQL as well. Before I give you the details I want to put out a few disclaimers:
- Editing via Multiversion Views should be implemented very carefully because you are affecting the underlying state ids, state lineage, and the open edit states in the database. I would recommend testing any edit scripts out thoroughly in a test environment before running them in a production database.
- I would NEVER recommend editing SDE.Default via a MultiVersion view. The risk is too high and you could really get your geodatabase out of whack. Ideally create a NEW version whenever you want to perform Multiversion View edits. The purpose of the version should be for your SQL edit operations – you typically don’t want to mix Multiversion View edits with your ArcMap edits.
- You can’t use a MultiVersion View to create or delete any features that participate in a geometric network. There are a lot of additional network-related edits that occur via ArcMap and the MultiVersion View won’t handle any of them. You can, however, safely update attributes of existing geometric network features – but that’s about it.
- You aren’t able to create or edit any spatial (shape) data via Multiversion Views so this may limit where you would use them. We have typically used them to create/update object class records which have no spatial component and to possibly update feature class attribution.
- As I remember back to the first Multiversion View editing application I wrote in the early 2000’s, I also remember our good friend, Telvent’s Rich Ruh (now VP of Product Development) making the strong point that editing versioned data via Multiversion Views bypasses any and all business logic you may have implemented via ArcFM™ AutoUpdaters or even Esri Feature Class Extensions.These might include your audit tracking (create/update user and date), any fields set by spatial searches (PLSS grid values), Feeder Manager tracing and updates, and any other custom functions you may have in your geodatabase. This can affect your data quality, integrations with other systems, etc. Esri has officially stated “the only way to guarantee the integrity of your geodatabase is to edit it through an ArcObjects application”. But then again Esri also gave us Multiversion Views along with instructions on using them. We’ve used them for years in our applications and can safely say that when used appropriately, they can add tremendous value.
To sum up the above important warnings, plan and test your MultiVersion View edits carefully and think through any ramifications before you actually make the edits to a production environment. If you have any doubts, call in an experienced and trusted GIS consultant. That’s what we are here for… well that and our exceptionally good humor.
OK, now that the warnings are out of the way, let’s take a look at how this editing can be accomplished. Just like editing a version in ArcMap, our first step is to start editing. We obviously don’t have a handy start editing button available to us in SQL but the underlying concept is exactly the same. We must execute a combination of stored procedures to set the current version and then to open the SDE state for editing:
call sde.version_util.set_current_version (‘SDE.Working’);
call sde.version_user_ddl.edit_version (‘SDE.Working’, 1);
exec sde.sde.set_current_version ‘SDE.Working’;
exec sde.sde.edit_version ‘SDE.Working’, 1;
In the above SQL, the first statement simply sets the connection to point to the specified version and the second statement effectively starts an edit session. The value of “1” causes the database to start editing. In the above example the specified version would already have to exist in the geodatabase. There are some additional stored procedures that can be used to create a new version and/or delete an existing version but I often create and manage my versions in ArcCatalog and ArcMap the old-fashioned way. You can google those topics for some additional reading if interested.
Once you’ve got an open edit state on the specified version (i.e. started editing) you can now execute insert, update, or delete statements against your Multiversion Views. When issuing insert statements you can’t provide a value for the ObjectId since that is managed by SDE just like it is in ArcMap. There will also be an SDE_STATE_ID column – leave that puppy alone as well. It’s exactly what you think it is based on our past articles but you need to let SDE do its versioning magic to keep everything in sync. And finally, if you are editing a feature record (with a spatial geometry on the map) the Multiversion View will have a SHAPE column that contains an integer value. You also shouldn’t mess with this field at all either. But any other SQL operation on the rest of the business fields works great. For example, if you need to make some mass updates to records you can easily do this via a simple update statement. This quick example would standardize all of your pole heights between 30 and 35 ft to be 35:
update Utility.Pole_MV set Height=35 where Height>30 and Height<35;
Looks just like regular SQL doesn’t it?!? Most typical SQL will work just fine with a few limitations. One major function that does not work is a SQL “update from” statement where you would be updating Table A with some values from Table B based on an inner join between the tables. This is a bit more advanced and is not supported by the Multiversion View. If you need to do this, there are some old-school workarounds I can share if you are interested.
Once you are editing, you can issue multiple SQL statements within the same edit session. Keep in mind that if you lose your connection to the database at any time, you would need to call the set_current_version stored procedure again to get your connection pointed to the right SDE version.
You do not need to call start editing on the version more than once unless you have stopped editing (see below). Don’t worry though, if you accidentally call it twice, Esri will kick back a pretty informative error message indicating the current state has not been closed (i.e. stop editing has not been called). No harm done. After you have issued your Multiversion View SQL statements within the edit session, you still need to call stop editing to close the state in the database which effectively stops the edit session:
call sde.version_user_ddl.edit_version (‘SDE.Working’, 2);
exec sde.sde.edit_version ‘SDE.Working’, 2;
The value of “2” causes the database to stop editing. Because we are editing directly in the database via SQL we don’t have the concept of Esri edit operations where you can rollback a set of edits. So if you have this requirement when making your SQL edits I’d recommend using a SQL transaction. This is the default when editing in Oracle. You have to either commit or rollback your edits within the SQL tool (i.e. use the buttons in the UI or SQL statement commit;). However, in SQL Server the default is to have your edits committed immediately so you may want to explicitly use the BEGIN TRANSACTION, COMMIT TRANSACTION and/or the ROLLBACK TRANSACTION SQL calls in your script. Alternatively, remember we are editing in an SDE version! You can always stop editing, delete your version, and start all over.
All right, you’ve now created your SDE version, started editing, made some cool and easy SQL updates, and stopped editing. What’s next? Unfortunately there are no reconcile or post operations available in SQL. But this is for good reason. Multiversion Views themselves don’t know about conflicts or how to manage the state tree during a reconcile or post. So once you’ve got your edits saved, you’ll need to open the version in good ole ArcMap and reconcile and post it from there. Resolve any conflicts along the way and you’ll be in good shape.
Hopefully I haven’t totally scared you off the topic of Multiversion Views and you can see the value they might add to your versioned geodatabase. As mentioned earlier, when used carefully and appropriately they are quite safe and powerful. I’ve covered a lot in this topic and over the last 4 months in this versioning series of articles. If you have any questions or any corrections (be nice) feel free to send them along. Just use our handy contact page on the website and let us know what you think.
And alas, this installment wraps up our series on Versioning For Dummies. It’s a bittersweet moment for me and though there are more versioning topics I could delve into, I’m about ready to write about something else! I’ve got some ideas in mind but if there are other GIS-related topics you’d like us to break down geek-style, shoot them our way and we’ll try to get them into a future installment of SSP Energy Advisor. Until then…