Learning > ecsqltutorial > Change Summary Queries Edit this page Querying Change Summaries Change Summaries are summaries of changes of ECInstances in an iModel Changeset. Please read Change Summaries first, before doing this section of the tutorial. Generate the Change Summaries In case the Change Summaries have not be generated yet for the tutorial's iModel, you have to do that first. In the iModelConsole run the following commands: Close the iModel first and re-open it read-write, so that you can generate the Change Summaries: .close .open -project:ECSQL -mode:readwrite .change extractsummaries Now close the iModel again and re-open it read-only. .close .open -project:ECSQL Attach the Change Summaries As explained in Change Summaries, you need to attach the file that contains the generated Change Summaries to this iModel by running this command in the iModelConsole: .change attachcache Sample Change History To keep this section as simple as possible, the iModel has three changesets, each with simple changes. They are described here in text to help understand the following queries. Changeset 1 Operations: Insert a new Device with code DEV-A-G-3 and User Label 'Fire detector' Result: ECInstanceId CodeValue UserLabel 0x20000000001 DEV-A-G-3 Fire detector Changeset 2 Operations: Update the User Label of Device DEV-A-G-3 to 'Fire extinguisher' Insert a new Device with code DEV-A-G-4 and User Label 'Fire detector' Result: ECInstanceId CodeValue UserLabel 0x20000000001 DEV-A-G-3 Fire extinguisher 0x20000000002 DEV-A-G-4 Fire detector Changeset 3 Operations: Delete Device DEV-A-G-3 again Result: ECInstanceId CodeValue UserLabel 0x20000000002 DEV-A-G-4 Fire detector Mining the Change Summaries There are two main ways to mine Change Summary information: Find out what classes, what instances, what property values have changed Find out how property values of certain instances have changed The following examples will run through both of them. Explore what has changed Generally there are three main classes in the ECDbChange ECSchema to explore what has changed: ChangeSummary InstanceChange PropertyValueChange Additionally the IModelChange ECSchema contains the ChangeSet ECClass that links a Change Summary to the changeset from which it was generated. The following examples will run through all of them. What Change Summaries are there Let's first find some information about the changesets for which Change Summaries have been generated. The query also serves to return the ECInstanceIds of the corresponding Change Summaries which we will need for all other queries. Try it yourself Goal: Return the ECInstanceId of the Change Summaries and information about the corresponding changesets, ordered from oldest to newest. ECSQL SELECT Summary.Id, WsgId, Description, PushDate, UserCreated, ParentWsgId FROM imodelchange.Changeset ORDER BY PushDate Result Summary.Id WsgId Description PushDate UserCreated ParentWsgId 0x6c f7c220138713044a89f4e5fa479564863516b53b Inserted new Device 'DEV-A-G-3'. 2018-12-18T16:03:08.373Z some user id NULL 0x35 9c4239a1fef7cc7136fcef1f6a7472a3b0ffbd7d Fixed user label of Device 'DEV-A-G-3'. Inserted new Device 'DEV-A-G-4'. 2018-12-18T16:03:19.763Z some user id f7c220138713044a89f4e5fa479564863516b53b 0x1 1264417d6364c79d3d1c8d6a45ee6e3ee79188c4 Removed Device 'DEV-A-G-3' again. 2018-12-18T16:03:27.140Z some user id 9c4239a1fef7cc7136fcef1f6a7472a3b0ffbd7d What instances have changed in a Change Summary Now that we know what changesets there are, let us look what instances were changed in one of them. Try it yourself Goal: Return the id and class id of all instances that have changed in Change Summary 0x35, including the op code for each change. ECSQL SELECT ECInstanceId, ChangedInstance.Id, ChangedInstance.ClassId, OpCode FROM ecchange.change.InstanceChange WHERE Summary.Id=0x35 Result ECInstanceId ChangedInstance.Id ChangedInstance.ClassId OpCode 0x36 0x20000000002 0x100 Insert 0x48 0x20000000001 0x100 Update 0x52 0x20000000002 0x9b Insert 0x57 0x20000000002 0xbd Insert 0x5c 0x20000000002 0x4f Insert 0x61 0x20000000002 0x56 Insert 0x66 0x20000000002 0x58 Insert For the sake of readability we modify the query by joining to the ECDbMeta ECSchema so that the class names of the changed instances are output. Try it yourself Goal: Return the id and class name of all instances that have changed in Change Summary 0x35, including the op code for each change. ECSQL SELECT ic.ECInstanceId, ic.ChangedInstance.Id, s.Name || '.' || c.Name ChangedClass, ic.OpCode FROM ecchange.change.InstanceChange ic JOIN main.meta.ECClassDef c ON ic.ChangedInstance.ClassId=c.ECInstanceId JOIN main.meta.ECSchemaDef s ON c.Schema.Id=s.ECInstanceId WHERE ic.Summary.Id=0x35 Result ECInstanceId ChangedInstance.Id ChangedClass OpCode 0x36 0x20000000002 MyDomain.Device Insert 0x48 0x20000000001 MyDomain.Device Update 0x52 0x20000000002 BisCore.GeometricElement3dIsInCategory Insert 0x57 0x20000000002 BisCore.PhysicalElementIsOfType Insert 0x5c 0x20000000002 BisCore.ModelContainsElements Insert 0x61 0x20000000002 BisCore.CodeSpecSpecifiesCode Insert 0x66 0x20000000002 BisCore.ElementScopesCode Insert How to read the result of this query Here is the gist of what you can read from this query: 7 instances were changed in Change Summary 0x35. 1 instance (a Device) was modified in that changeset. 6 instances were added to the iModel in that changeset. The change history's description states that one device was modified and one was added in this changeset. Why are there 5 more changes in this changeset? Inserting a device implicitly sets all its relationships, too. This is what the other 5 changes represent. What properties have changed for a changed instance Now that we know that Device 0x36 was modified in Change Summary 0x35 we might want to find out what properties were modified. Try it yourself Goal: Return the names of the properties that were modified in the InstanceChange 0x48. ECSQL SELECT AccessString FROM change.PropertyValueChange WHERE InstanceChange.Id=0x48 Result AccessString LastMod UserLabel As we know from the change history's description, the modification of UserLabel is expected. Why was LastMod modified as well? This again is an implicit change, caused by a trigger on the Element.LastMod property which sets it to the current time whenever the element is modified. Here we used the ECInstanceId 0x48 of the respective InstanceChange object that represents the update of Device 0x20000000001 from the previous query. As always we can use a join, if we only know the id of the changed device, but not the id of the actual change object: Try it yourself Goal: Return the names of the properties that were modified in Device 0x20000000001 in Change Summary 0x35. ECSQL SELECT AccessString FROM change.PropertyValueChange pc JOIN change.InstanceChange ic ON pc.InstanceChange.Id=ic.ECInstanceId WHERE ic.ChangedInstance.Id=0x20000000001 AND ic.ChangedInstance.ClassId=0x100 AND ic.Summary.Id=0x35 Result AccessString LastMod UserLabel To deepen the understanding of Change Summaries, let's look why we have to add the AND ic.Summary.Id=0x35 expression to the WHERE clause in the above query. If we forgot to add it, the query would return many more rows than just the expected two. The reason for that is simply that an instance can be changed in different change summaries. In fact, if you look at the change history's description again, we notice that Device 0x20000000001 is modified in all three changesets. First, it is inserted, then it is modified, and finally it is deleted. The query then returns all properties that have changed in all changesets. Now that we looked at this, let's modify the previous query and use the Change Summary of the last changeset: Try it yourself Goal: Return the names of the properties of Device 0x20000000001 that were affected in Change Summary 0x1. ECSQL SELECT AccessString FROM change.PropertyValueChange pc JOIN change.InstanceChange ic ON pc.InstanceChange.Id=ic.ECInstanceId WHERE ic.ChangedInstance.Id=0x20000000001 AND ic.ChangedInstance.ClassId=0x100 AND ic.Summary.Id=0x1 Result AccessString BBoxHigh.X BBoxHigh.Y BBoxHigh.Z BBoxLow.X BBoxLow.Y BBoxLow.Z Category.Id CodeScope.Id CodeSpec.Id CodeValue FederationGuid GeometryStream InSpatialIndex LastMod Model.Id Origin.X Origin.Y Origin.Z Pitch Roll TypeDefinition.Id TypeDefinition.RelECClassId UserLabel Yaw The query indicates that all properties of the Device were modified in that changeset. Why? Let's quickly run a query that tells us what kind of change this was, i.e. what the OpCode of that change was: Try it yourself Goal: Return the OpCode for the change of Device 0x20000000001 in Change Summary 0x1. ECSQL SELECT OpCode FROM change.InstanceChange WHERE Summary.Id=0x1 AND ChangedInstance.Id=0x20000000001 AND ChangedInstance.ClassId=0x100 Result OpCode Delete Now we can explain why the query before returned all properties: As Device 0x20000000001 was deleted in that changeset, all its property values were deleted as well. Consequently, the PropertyValueChange ECClass records all properties of the class as being deleted. The same is true for Inserts. You can try that out yourself if you replace the Change Summary id in the previous queries with 0x6c. Explore how data has changed In order to see how the property values of instances have changed in a given changeset, the ECSQL function Changes can be used. Changes Function Syntax SELECT ... FROM MySchema.MyClass.Changes(ChangeSummaryId, ChangedValueState) ... ChangeSummaryId: The ECInstanceId of the Change Summary. ChangedValueState: corresponds to the values of the enum ChangedValueState. Walking through the history Before looking at how the Devices have changed over the time, let's look at the current state. Try it yourself Goal: Return id, CodeValue and UserLabel of all Devices. ECSQL SELECT ECInstanceId,CodeValue,UserLabel FROM mydomain.Device Result ECInstanceId CodeValue UserLabel 0x10000000020 DEV-A-G-1 NULL 0x10000000022 DEV-A-G-2 NULL 0x10000000025 DEV-A-1-1 NULL 0x10000000028 DEV-A-2-1 NULL 0x1000000002a DEV-A-2-2 NULL 0x1000000002c DEV-A-2-3 NULL 0x1000000002e DEV-A-2-4 NULL 0x10000000030 DEV-A-2-5 NULL 0x10000000032 DEV-A-2-6 NULL 0x10000000034 DEV-A-2-7 NULL 0x10000000036 DEV-A-2-8 NULL 0x20000000002 DEV-A-G-4 Fire detector Changes in the first changeset Try it yourself Goal: Return id, CodeValue and UserLabel of the Devices that were inserted in Change Summary 0x6c. ECSQL SELECT ECInstanceId,CodeValue,UserLabel FROM mydomain.Device.Changes(0x6c,'AfterInsert') Result ECInstanceId CodeValue UserLabel 0x20000000001 DEV-A-G-3 Fire detector This example returns the Devices inserted in the first changeset. The returned Device does no longer exist (as it was deleted in the latest changeset), but you can find it with the help of Change Summaries. Now let's change the ChangedValueState argument in the query. Try it yourself Goal: Return id, CodeValue and UserLabel of the Devices that were updated in Change Summary 0x6c. ECSQL SELECT ECInstanceId,CodeValue,UserLabel FROM mydomain.Device.Changes(0x6c,'AfterUpdate') Result ECInstanceId CodeValue UserLabel no rows The query not returning anything just means that no Devices were updated in that changeset - which we already know from the change history's description and the previous queries. Consequently, the same result is obtained when using ChangedValueState.BeforeUpdate and ChangedValueState.BeforeDelete. Changes in the second changeset From the previous queries we know that in this changeset a new Device with code DEV-A-G-4 was inserted and an existing one's user label was modified. Try it yourself Goal: Return id, CodeValue and UserLabel of the Devices that were inserted in Change Summary 0x35. ECSQL SELECT ECInstanceId,CodeValue,UserLabel FROM mydomain.Device.Changes(0x35,'AfterInsert') Result ECInstanceId CodeValue UserLabel 0x20000000002 DEV-A-G-4 Fire detector When examining updated instances, we can look at the values before and after the update. Note: NULL is returned for CodeValue because it was not affected by this changeset. Try it yourself Goal: Return id, CodeValue and UserLabel of the Devices before they were updated in Change Summary 0x35. ECSQL SELECT ECInstanceId,CodeValue,UserLabel FROM mydomain.Device.Changes(0x35,'BeforeUpdate') Result ECInstanceId CodeValue UserLabel 0x20000000001 NULL Fire detector Try it yourself Goal: Return id, CodeValue and UserLabel of the Devices after they were updated in Change Summary 0x35. ECSQL SELECT ECInstanceId,CodeValue,UserLabel FROM mydomain.Device.Changes(0x35,'AfterUpdate') Result ECInstanceId CodeValue UserLabel 0x20000000001 NULL Fire extinguisher Changes in the third changeset In the third changeset the Device with code DEV-A-G-3 which was inserted in the first changeset was deleted again. Try it yourself Goal: Return id, CodeValue and UserLabel of the Devices before they were deleted in Change Summary 0x1. ECSQL SELECT ECInstanceId,CodeValue,UserLabel FROM mydomain.Device.Changes(0x1,'BeforeDelete') Result ECInstanceId CodeValue UserLabel 0x20000000001 DEV-A-G-3 Fire extinguisher < Previous | Next > Last Updated: 29 November, 2022