Chinese - Simplified   Dutch   English - United States   French   German   Greek   Portuguese - Iberian   Spanish  

Home




Usenet Posting #3 - Trigeminal Software, Inc. (English)


Subject: INFO: Inside the replication 'MSysConflicts' table in Jet 4.0
(Originally posted 5/23/99)
This new table in Jet 4.0 replication is the key to creating powerful custom resolvers. This "info" doc will explain the schema of this table.

First, note that in Jet 4.0 conflicts are reported at all replicas rather than just the losing replica. You may use the old scheme for finding conflicts: look through the TableDefs collection, and any table that has a ConflictTable property set to a table name has conflicts in it. But this is a lot more work than you need to do, really. A new table named MSysConflicts can be used instead; it contains metadata on *all* conflicts in the database.

An important change is that of "child conflicts" which happen when a divergent conflict (such as a dupe primary key in the employees table) causes some orders and order details associated with the losing employee record to be deleted).

THE SCHEMA OF MSYSCONFLICTS:

The first thing you will notice is that the Jet team has gone GUID-happy! But don't be too intimidated; most of them you do not care about and the ones you do care about are easy enough once you get the hang of them.

-BaseGuidSrc - A GUID that links up to the GuidSrc field in MSysGenHistory. Used internally by Jet to handle the case where there are multiple conflicts on the same row from multiple sources, in order to determine the most recent conflict. Usually not useful for users.

-BaseRowGuid - A GUID that links up to the s_GUID field of the conflict table. It represents the "parent" conflict, so all child conflicts contain the same BaseRowGuid as the parent. When it is the same as the ConflictIdGuid, then this is the parent conflict.

--BaseTableGuid - Links to the s_GUID field in MSysTableGuids and thus gives you the name of the source table for the conflict

-ConflictDescCode - Type of conflict - i.e. unique key, TLV, simultaneous update, foreign key etc.

-ConflictIdGuid - A GUID that links up to the s_GUID field of the conflict table. When it is the same as the BaseRowGuid, then this is a parent conflict, otherwise it is a child conflict.

-InvolvedObjects - A text field which theoretically tells you info about where the conflict occurred like table and field.... in practice I have never seen it populated.

-LosingReplicaId - Links to the s_GUID field in MSysReplicas and tells you which replica is the losing one.

-Reason - Text that gives some context sensitive info about the conflict, for display.

-WinningGuidSrc - Links to the GuidSrc field in MSysGenHistory. Like BaseGuidSrc, used internally by Jet to handle the case where there are multiple conflicts on the same row from multiple sources, in order to determine the most recent conflict. Usually not useful for users.

-WinningReplicaId - Links to the s_GUID field in MSysReplicas and tells you which replica is the winning one.

-WinningRowGuid - Links to the s_GUID field from the source table when there is still a source row that you might want to link to (such as in an update-update conflict or in a dupe primary key conflict).

-s_GUID - Replication system field... links up to the ConflictRowGuid in the conflict table if you need the link.

-s_Generation - Replication system field...

-s_Lineage - Replication system field...

OK..... so lets say you wanted to do the following:
1) List all parent conflicts but no child conflicts
2) Show the losing and winning replicas
3) Show the count of conflicts and the count of child conflicts
4) Show the name of the conflict table and of the src table

You could do it all with a query like the following:

SELECT 
    Max(tg.TableName) AS src_Table, 
    Max(st.SideTable) AS conflict_table, 
    Count(IIF(c_a.ConflictIdGuid <> c_a.s_Guid,Null, 1)) As CnfCount, 
    Count(IIF(c_a.ConflictIdGuid = c_a.s_Guid, Null, 1)) As 
CnfChildCount 
FROM 
    (((MSysTableGuids AS tg INNER JOIN 
    MSysSideTables AS st ON tg.s_GUID = st.TableGuid) INNER 
JOIN 
    MSysConflicts AS c ON st.TableGuid = c.BaseTableGuid) 
INNER JOIN 
    MSysReplicas AS r ON c.WinningReplicaId = r.ReplicaId) 
INNER JOIN 
    MSysConflicts AS c_a ON c_a.ConflictIdGuid = c.s_Guid 
WHERE 
    c.ConflictIDGuid = c.s_Guid 
GROUP BY 
    tg.TableName; 

And you will get a nice resultset back with all the source and conflict tables, as well as the count of conflicts and the associated child conflicts. And if you were scrolling through the conflicts and wanted to get the full src and cnf columns for the Customers table (for example) in a single recordset:

SELECT 
    Customers.*, 
    Customers_Conflict.* 
FROM 
    (Customers_Conflict INNER JOIN 
    MSysConflicts ON Customers_Conflict.ConflictRowGuid = MSysConflicts.s_GUID) LEFT JOIN 
    Customers ON MSysConflicts.WinningRowGuid = Customers.s_GUID 

And you can just kind of take it from there...

There will be lots more on this topic over time, obviously, but going through the schema seemed like a good first step.

Back to Usenet Musings


Problems with this site? Please contact the webmaster@trigeminal.com
with your comments, questions, or suggestions.