A quick tour of editing versioned data using SQL

To edit versioned data from a SQL client, you must edit a multiversioned view of the data, not the base (business) table itself. Versioned tables use two associated tables—the adds and deletes tables (collectively referred to as the delta tables)—to record changes. When you edit a multiversioned view of the table, edits are written to the adds and deletes tables. Editing the base table directly circumvents this and could lead to orphaned records and data loss.

When you execute SQL data manipulation statements against a multiversioned view, the following takes place in the database for each type of statement:*

• Insert: A row is added to the adds table of the underlying base table, and an ObjectID value for the new row is automatically generated.
• Update: Update deletes the original row and adds a new one that contains the new information. Therefore, a row is added to both the adds and deletes tables of the underlying base table when you execute an update statement.
• Delete: A row is added to the delete table of the underlying base table.

*If editing the DEFAULT version when it is pointing to state 0, all edits are immediately moved to the base table.

Be aware that no internal version reconciliation is done for edits performed through SQL; therefore, you must reconcile your edits with a parent version through ArcGIS Desktop or a Python script after you have finished editing.

Editing models

You can create a new, named geodatabase version and edit that version, or (beginning with ArcGIS 10 SP2 in all databases except PostgreSQL) you can edit the DEFAULT version directly. Which one you do depends on the requirements at your site. It is important to choose the appropriate model—either editing a named version or editing the DEFAULT version—to ensure optimal performance and scalability.

Editing a named version

You would create and use named versions to edit with SQL through multiversioned views if any of the following is true at your site:

• Multiple editors must change the same data.
• You require a well-defined quality control process.
• The changes do not have to be immediately available to other users; rather, they can be kept separate until you reconcile and post them.
• The versioned feature classes you want to edit use SDEBINARY or OGCWKB geometry storage in a geodatabase in Oracle or SQL Server.
• The versioned feature class or table you want to edit is registered as versioned with the option to move edits to base.

When you edit through a multiversioned view, edits are recorded in the adds and deletes tables. The edits are written to the current state that the named version references.

The steps you take to edit data in a named version are as follows and should be performed in the order shown:

1. Create a multiversioned view on a versioned table or feature class if one does not already exist.
2. Create a geodatabase version in which to do your edits.
3. Use the set_current_version procedure to specify that you want to access your new version. Doing so sets the edit session to the state the named version is pointing to and locks the version.
4. Start an edit session by executing the edit_version procedure or function appropriate to your database.
5. Perform your edits on the multiversioned view using SQL.
6. Commit your edits to the database or roll them back.
7. Stop the edit session by executing the edit_version procedure or function appropriate to your database.
8. Reconcile and post your edits through ArcGIS.
9. When all changes are posted to a parent version using ArcGIS, you can delete the version you created for your edits on the multiversioned view.

Editing the DEFAULT version

Beginning with ArcGIS 10 SP2, you could edit the DEFAULT version with SQL through multiversioned views if one or more of the following are true at your site:

• The edits to be made are short transactions.
• Your site requires that the edits made through a multiversioned view be available to other users immediately.
• If editing feature classes, the feature classes use SQL spatial types, not SDEBINARY or OGCWKB geometry storage.
• The table or feature class to be edited is not registered as versioned with the option to move edits to base.

When you edit the DEFAULT version, edits are recorded in the delta tables just as they are when you edit a named version. However, when you edit the DEFAULT version, the edits can be seen by anyone viewing the DEFAULT version.

If the DEFAULT version references state 0, each edit is applied directly to the base table of the versioned table or feature class. When the DEFAULT version is edited with an ArcGIS client, the version is updated to reference a new database state upon saving. When a multiversioned view edits DEFAULT directly, each insert, update, and delete operation is written to the current state the DEFAULT version references.

For example, if the DEFAULT version is updated with an ArcGIS client while multiple changes are being performed through the multiversioned view, it is possible that the changes made through the multiversioned view can be applied to multiple states.

Once your edits are committed, they are immediately accessible to the following:

• Applications that are working with the versioned table and the DEFAULT version
• Applications that are working with a child version that has a state lineage that contains the DEFAULT version's current state

Dive-in:

If the row being modified in the DEFAULT version by a multiversioned view has been modified by another version in a state that is dependent on the DEFAULT version's current state, the multiversioned view creates a new geodatabase state, updates the DEFAULT version to reference the new state, then performs the edit. This is required to ensure that the row being modified (which is also modified in a state dependent on the DEFAULT version's state) will not be overwritten by a compress operation or when the descendant state's version is reconciled with the DEFAULT version.

You do not set the version or start an edit session if you want to edit data in the DEFAULT geodatabase version through a multiversioned view. The steps you do perform are as follows:

1. Create a multiversioned view on a versioned table or feature class if one does not already exist.
   Note:

   If the multiversioned view was created prior to ArcGIS 10 SP2, you must re-create it; older multiversioned views cannot be edited in the DEFAULT version.

2. Perform your edits on the multiversioned view using SQL. You will automatically be editing the current state of the DEFAULT version.
3. Commit your edits to the database or roll them back. It is best to commit or roll back after each edit because, while your transaction is open, exclusive locks are held on the delta tables. The locks are not released until the transaction ends.