origam.com

Deployment Script Generator - Error when same tables in different schemas exist

In database I have table HangFire.Counter (it’s created by scheduler https://www.hangfire.io/, which I think is also considered to be used by Advantage Studios).
There is also Origam’s dbo.Counter.
This creates error when running Deployment Script Generator in Architect:
Item has already been added. Key in dictionary: 'Counter' Key being added: 'Counter'

Full error:

========================================
Error
Item has already been added. Key in dictionary: 'Counter'  Key being added: 'Counter'
========================================
Item has already been added. Key in dictionary: 'Counter'  Key being added: 'Counter'
------------------------------------------
========================================
 Stack trace
========================================
   at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
   at Origam.DA.Service.AbstractSqlDataService.getDbTableList()
   at Origam.DA.Service.AbstractSqlDataService.CompareSchema(IPersistenceProvider provider)
   at OrigamArchitect.SchemaCompareEditor.DisplayResults()
   at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
   at System.Windows.Forms.ComboBox.set_SelectedIndex(Int32 value)
   at OrigamArchitect.SchemaCompareEditor..ctor()
   at OrigamArchitect.Commands.ShowDbCompare.Run()
   at OrigamArchitect.frmMain.MenuItemClick(Object sender, EventArgs e)

Proposed solution:
I think the Deployment Script Generator should be checking only dbo schema, or be changed to work with multiple schemas (so it won’t crash when tables with same name in different schemas exist).
Plus I think it will be generally good if all Origam tables would be located in special Origam schema (so users can create their own tables in dbo and don’t be limited with table names excluding those from Origam, for example).

ORIGAM works with the default schema of the user it runs under. So it is not just dbo. If the service’s user name has a different default schema, e.g. origam, it will both create and access objects under that schema. If another app or service creates its objects under a different schema, it should work without problems.

Generally it would be interesting if ORIGAM would support multiple schemas within a model. So e.g. entities in different packages would map to different schemas, e.g. OrigamUser would be placed under root.OrigamUser. That is not supported.

Perfect, using db user with default schema origam solved it (I moved all Origam tables to this schema). Thank you, Tomáš.

FYI, one thing has to be done there, though. When I tried to run application in Docker, error appeared:

ERROR Origam.DA.Service.DataLoader - Invalid object name 'dbo.BusinessPartner'. Could not use view or function 'BusinessPartnerLookup' because of binding errors., SQL: SELECT (SELECT TOP 1 [Resource].[refOrganizationId] AS [refOrganizationId] FROM [Resource] AS [Resource] WHERE ( ([Resource].[refBusinessPartnerId] = [BusinessPartner].[Id]) AND ([Resource].[refResourceTypeId] = 'e0360aa5-fa37-4e3d-9827-26f6b4a8905e')) ) AS [Organization_Id], [BusinessPartner].[RecordCreated] AS [RecordCreated], [BusinessPartner].[Id] AS [Id], (SELECT TOP 1 [Resource].[Id] AS [Id] FROM [Resource] AS [Resource] WHERE ( ([Resource].[refBusinessPartnerId] = [BusinessPartner].[Id]) AND ([Resource].[refResourceTypeId] = 'e0360aa5-fa37-4e3d-9827-26f6b4a8905e')) ) AS [Resource_Id], [BusinessPartner].[LookupText] AS [FullName], (SELECT TOP 1 [Resource].[refBusinessUnitId] AS [refBusinessUnitId] FROM [Resource] AS [Resource] WHERE ( ([Resource].[refBusinessPartnerId] = [BusinessPartner].[Id]) AND ([Resource].[refResourceTypeId] = 'e0360aa5-fa37-4e3d-9827-26f6b4a8905e')) ) AS [BusinessUnit_Id], [BusinessPartner].[Name] AS [Name], [BusinessPartner].[UserName] AS [UserName], [BusinessPartner].[UserEmail] AS [UserEmail] FROM [BusinessPartnerLookup] AS [BusinessPartner] WHERE ( ([BusinessPartner].[UserName] = @BusinessPartner_parUserName))

Full error message: BusinessPartner error.txt (43.2 KB)

I solved this by creating synonym in db:
CREATE SYNONYM BusinessPartner FOR origam.BusinessPartner

Now application seems to be running OK (but I’m not sure whether there won’t be some other errors).

PS: In env file I have set OrigamSettings_DbUsername and OrigamSettings_DbPassword to origam user…original sa user (which uses dbo schema) didn’t work - error “Invalid object name 'OrigamModelVersion'” appeared in Docker, so I guess I have to use user tied to origam schema in env file.

So I guess the problem lies in a hardcoded dbo reference in the standard BusinessPartnerLookup view.

That should be easy to fix. Should be simply removed so it would use the default schema.

Anyway as I tried to search where dbo is possibly used, I found several cases. So if we really want to support other schemas, maybe someone should look into that (mainly deployment scripts of the root package) and remove dbo references.

Yes, correction of view BusinessPartnerLookup solved id, thanks. Now it works without synonym.