I am running queries like this to list data directives between the environment where our development has been happening and the environmants we are now testing in nad our basedata environment that will be used to spawn production at go live. Using power query then to “left anti join” these between databases (environments)
Can anyone point me to more info on the content of this table? I’m in the “knowing just enough to be dangerous, but not quite enough to be useful” zone currently…
I compare the directives between environments using
[Source] [BpMethodCode] [DirectiveType] [Name] as the key, I was using [DirectiveID] here to compare originally
Starts with Ice.[Company]/[BAQName].[DirectiveName] or Ice._/z[EpicorBAQs].[DirectiveName] or Erp.BO.[Method][DirectiveName] or Erp.Rpt.[Method][DirectiveName] etc, etc…
1 BO Pre-Processing Method Directive
2 BO Base-Processing Method Directive
3 BO Post-Processing Method Directive
Name → Friendly name someone gave it
Order → Order they execute in
IsEnabled → Duh
ReenterMax → I assume the max amount of recursion before it blows up ?
PreventDeadloops → Not quite sure, some safety measure we could or can use?
Company → Duh
Visibility Scope → ???hmm, who can see?
Directive Group → Friendly group name, can use with import / export
The only thing I’m stuck on now is identifying differences for directives that exist in both environments. I’m comparing Body, but just about all the directives seem different in their Body in the manner below relating to startNode at least, I assume something to do with bpms being created in one environment and solutionized in the other.
Hmm looking again I think if I filter out certain lines eg any lines containing StartNode or
<x:Reference>__ReferenceID0</x:Reference> or </DirectiveDefinition2.AdditionalReferences> then compare I might get some joy.
I worked on a similar project to compare items between our environments during our upgrade from E10 to Kinetic. I’m on prem, so I have access to the SQL servers. Not sure if your situation allows you that access. Anyway, here’s the SQL that I used to extract the C# code from our BPMs. It’s been months since I’ve looked at this, and I’m getting older every day, so I dont recall how I came up with it. I’m a trial and error kind of guy, so…
WITH XMLNAMESPACES('clr-namespace:Ice.Lib.Bpm.Model.Actions;assembly=Ice.Lib.Bpm.Shared' AS ilbma,
DEFAULT 'clr-namespace:Ice.Lib.Bpm.Model;assembly=Ice.Lib.Bpm.Shared')
SELECT 'BPM' AS [Category],
BD.[Name] AS [ItemName],
BD.[BpMethodCode] AS [ItemDescription],
BD.[Source] as [ItemType],
CAST(BD.Body AS nvarchar(MAX)) AS [Body],
BD.SysRevID as [SysRevID],
BD2.C.value('(./@Code)[1]','nvarchar(max)') AS [ItemContent],
CONCAT('{', BD.SysRowID, '}') as [SysRowID],
BD.[DirectiveID] as [DirectiveID],
BD.[Company] as [Company],
BD.[Order] AS [Order],
BD.[Description] as [Description],
BD.[DirectiveType] as [DirectiveType],
BD.IsEnabled as [IsEnabled],
BD.IsUpToDate as [IsUpToDate],
RANK() OVER (PARTITION BY BD.[DirectiveID] ORDER BY BD2.C.value('(./@Code)[1]','nvarchar(max)')) AS [CodeSequence],
0 As [Examined],
0 AS [Newest],
0 AS [Inspect]
FROM Ice.BpDirective BD
CROSS APPLY BD.Body.nodes('//ilbma:CustomCodeAction') AS BD2(C)
where BD.Source <> 'DQ' AND BD.IsEnabled = 1
Don’t know if that will help or not, but it worked for me. The ItemContent field contains the C# code. I guess I should also clarify that I was NOT pulling the information into any Epicor related. This was part of a WPF program that I did in Visual Studio, (the first time I had ever done such a thing, so it’s pretty ugly.
Wow, that’s SQL Jim, but not as I know it…
I ran it though, and yep out pops the C# code
I should be able to compare the two envirnoment’s code snippets with this.
Thanks again Kevin, read your post on Klient/Konsole plugin framework and it sounds exciting, but a little over my head at this point in my learning cycle. (I was hoping to hop off my learning cycle and park the damn bike months ago, but apparently that’s not to be …)
@mccoyjf If you could wrap your code in triple grave accents beginning and end and paste it back into your post then the single quotes won’t get converted to fancy quotes that have to be fixed before the sql can be run. It will help future copy pasters with your awesome code.
like so.
WITH XMLNAMESPACES('clr-namespace:Ice.Lib.Bpm.Model.Actions;assembly=Ice.Lib.Bpm.Shared' AS ilbma,
DEFAULT 'clr-namespace:Ice.Lib.Bpm.Model;assembly=Ice.Lib.Bpm.Shared')
SELECT 'BPM' AS [Category],
BD.[Name] AS [ItemName],
BD.[BpMethodCode] AS [ItemDescription],
BD.[Source] as [ItemType],
CAST(BD.Body AS nvarchar(MAX)) AS [Body],
BD.SysRevID as [SysRevID],
BD2.C.value('(./@Code)[1]','nvarchar(max)') AS [ItemContent],
CONCAT('{', BD.SysRowID, '}') as [SysRowID],
BD.[DirectiveID] as [DirectiveID],
BD.[Company] as [Company],
BD.[Order] AS [Order],
BD.[Description] as [Description],
BD.[DirectiveType] as [DirectiveType],
BD.IsEnabled as [IsEnabled],
BD.IsUpToDate as [IsUpToDate],
RANK() OVER (PARTITION BY BD.[DirectiveID] ORDER BY BD2.C.value('(./@Code)[1]','nvarchar(max)')) AS [CodeSequence],
0 As [Examined],
0 AS [Newest],
0 AS [Inspect]
FROM Ice.BpDirective BD
CROSS APPLY BD.Body.nodes('//ilbma:CustomCodeAction') AS BD2(C)
where BD.Source <> 'DQ' AND BD.IsEnabled = 1
63 BPMs identified where the code differs in our test and development environment to our pre-go-live basedata environment. A win for epiusers, a loss for our Epicor go-live program…
Doing development?
Live - Development Environment
Do the work, Close it out.
Rinse and repeat.
Multiple developers?.. Starts getting more complicated but if the development is not overlapping then they could be build/Tested and deployed in isolation (i.e. separate environment per dev)… Sorry SaaSies.
There is a lot to be said for being able to have a Docker image for development, that has a copy of your live (with obfuscated data) that could be downloaded from the control panel.
Absolutely. A development moritorium would absolutely have been the thing to do. However, in my situation, I don’t get to control those sorts of decisions, so building a utility to monitor what was getting changed in the production environment and compare it to other environments was the only way that I could figure to have any chance of a successful upgrade. It worked well and we’re on Kinetic now.
There is a school of thought that source code be stored in a repository by version to do compares and have commit IDs embedded in the environment to know which version is deployed.
