This question was originally posted on Stackoverflow, I'm moving it here as per request.

Initial post:

We have a DB with multiple schemas with the same tables in the different schemas. Using LLBLGenPro to generate NHibernate entities. However, LLBLGenPro falls over, complaining that there are duplicate table names.

What we want is for the entities to be generated into namespaces per schema (got this working by modifying the templates), and for each schema to be generated into its own folder.

Follow-up:

(please post questions about llblgen pro on our own forums at http://www.llblgen.com/tinyforum so our support team can pick them up, thanks)

A DB with multiple schemas, you mean 1 catalog with multiple schemas (SQLServer) or multiple schemas as with Oracle?

Anyway, this is supported. What is the exact error you got and when? Do you use the latest build? If not, please download the latest build.

If you want to have entities grouped per target schema, you could group them in the project, and use the group in a custom template as a namespace.

You can also use the grouping mechanism in the designer to generate a vs.net project per group which is done in its own namespace and folder. The downside of this is that relationships between entities in different groups aren't supported of course, so in that case you have to duplicate the entities.

It's up to you what fits your project best.

Some clarification on what we're trying to do. Our Postgres DB has one catalog with three schemas (it has more, but I'll keep it simple for now). We initially operated in AsVisualGroupingMechanism mode, but then we had to duplicate a schema, and of course the entity names were then not unique. We also modified the entity/HBM generator templates to include the schema name in entities, but of course this doesn't get detected by the tool, which still says that there are duplicate entities.

So, we switched to ProjectPerGroup mode. I created two groups, A and B, with the entities from schemas 1 and 2 respectively. That worked fine. Schema 3 has only a few tables, which are interrelated amongst themselves, but not with any other schema. I added these entites to groups A and B, since the entities in both groups need access to the set of entities in schema 3. When I add the entities to group A, it works fine and all the relationships are detected. When I add them to group B, it adds the entities but it doesn't detect the relationships between them. I could re-add them manually, but there are lots, and I'd have to do it for every group after the first one, which would be extremely tedious. I tested whether the problem was affected by which group I chose to add schema 3 to, first, but it was not. Whichever group I added the entities to first, worked fine; all sunsequent ones do not detect the relationships.

So that's about it. We really just want a way for the tool to recognise that entities in different schemas are not the same entity. The above problem is as close as we could get.

Which version (release date) of the Designer are you using? Could you post a repro project file?

When you use grouping in ProjectPerGroup mode, relationships which span multiple groups (e.g. you add an entity to B and it relates to an entity which is in A, no relationship is created). This is because it would otherwise only result in errors anyway. If I misread what you're doing, please let me know.

If you use the schemas as a grouping of entities which belong together, could you elaborate a bit about why you copied a schema so you created duplicate entities? Is this because you use a schema per 'customer' for example and you had to create a new one for a new customer?

It indeed would help if you could attach a llblgenproj file so we could reproduce it with the project (reverse engineer entities to groups, see what you ran into).

You can create a thread in the helpdesk forum which are only visible for the topicstarter and for us, to attach your project and refer to this thread from there.

I will set up a sample project.

Just one thing I wanted to clarify further: the entities in the 3rd schema are not related to any other groups. There are four tables in that schema which are only related to each other. But we add a copy of these entities to each of the other groups, and that's where we saw the problem. The designer doesn't have any errors, it just doesn't auto-create the relationships beyond the first group that we add the 3rd schema to.

It's hard to explain properly, I'll attach a sample project file soon.

the entities in the 3rd schema are not related to any other groups. There are four tables in that schema which are only related to each other. But we add a copy of these entities to each of the other groups, and that's where we saw the problem. The designer doesn't have any errors, it just doesn't auto-create the relationships beyond the first group that we add the 3rd schema to.

If these entities are not related to any other entity from the first 2 groups,... which relations are you speaking about, that are missing?

The relations in between the tables of the 3rd schema.

Pleochism wrote:

I will set up a sample project.

Just one thing I wanted to clarify further: the entities in the 3rd schema are not related to any other groups. There are four tables in that schema which are only related to each other. But we add a copy of these entities to each of the other groups, and that's where we saw the problem. The designer doesn't have any errors, it just doesn't auto-create the relationships beyond the first group that we add the 3rd schema to.

It's hard to explain properly, I'll attach a sample project file soon.

You mean it doesn't add the relationships between the copies? That's indeed true. The reason this is done is because when you create a copy of an entity with e.g. less fields, you get a new set of relationships with the entities the original also has relationships with, but the related entities do too, namely with the copy , and that's the problem. This leads to odd situations where the related entity now actually should get two FK fields (or two entities controlling the same FK field if you will, even if you don't actually use FK fields, they're technically there, also inside NHibernate. So the fk field is shared among relationships, which is supported btw, but not recommended) which are actually the same: one for the real entity and one for the copy. This could lead to weird situations where you set fkSideEntity.PkSide1 to an instance of PkSide, but after that you do fkSideEntity.PkSide2 to an instance and what should happen then? unclear.

Hence there are no relationships created for copies during reverse engineering (as you don't control the reverse engineering of relationships in that process). You could say: "but if the relationship is with a copy, it should be allowed!", true, but what if the copy is a copy of a copy? ... things get hairy pretty quickly in that respect.

Perhaps we were too rigid in cutting off the relationships reverse engineering for copies. E.g. within groups which are separated as projects, this could be OK. Thing is though... you can change that at any time to AsVisualGroupingMechanism, which suddenly makes it completely wrong.

I have attached a sample project that demonstrates the issue.

I have used "comesa" as group A and "eac" as group B. "data" is the 3rd schema, which we add to both groups A and B. So the entities "Attributes", "Spatial" and "Style" are added to both. You can remove and re-add them to see the problem: when they are not added to either group, and you add them, then the "Attributes" entity has its relationship to the "Spatial" table intact. Add these same entities to the second group, and Attributes suddenly doesn't have a link to Spatial.

The data schema has links to other schemas, like lookup, but since none of the objects in those schemas have been added to the two groups, I don't think that's the problem.

Yes, it exactly shows what I tried to describe above.

It also shows that our restriction is too strict: for the situation where a 'copy' is in a different group, it's not really a copy if groups are used for project grouping. We'll look into making this less strict: when the related entity is a copy but only in another group and grouping is ProjectPerGroup, it shouldn't restrict the relationship reverse engineering. This will require extra validation etc. so it's a bit bigger than a simple change we can do immediately, today. But there are other options for your situation, which IMHO are preferable. See below

For you there's another way to solve this btw (two actually). NHibernate's configuration object (obtainable through the sessionfactory I think or the session, have to look that up) allows you to dynamically overwrite the schema name: https://forums.hibernate.org/viewtopic.php?f=25&t=955250&start=0

Not per-call I think, but if you for example want to create 1 system and use it with multiple schemas, e.g. 1 schema per client, you can start the application multiple times, and simply overwrite the schema name in the configuration object at runtime. This allows you to use 1 set of entities and 1 set of mapping files: create your application and you can simply create multiple schemas for each customer another one.

I'd go for 1 catalog / database per customer however. Then you can simply switch by using a different connection string. Using the same schema and duplicating the entities in the project (and also duplicating mapping files) will already be odd with 1 copy (as you have now) but will be cumbersome when you get more and more...

Our own framework (using Adapter) allows schema name overwriting per call btw. You can then simply set the schema to use in code before executing a query and the schema name set is used for the queries executed on that adapter instance.

Though using different catalogs/databases for this is IMHO the best choice, switching between them using the connection string. It's the easiest to do with nhibernate, no configuration changing at runtime necessary.