Thank you for your suggestion. Filters take you a long way. Setting the filter per tenant that binds in the tenant ID would allow you to enable the filter after the ISession impl is created. It cascades to collections and can be set for many-to-one relationships, so it will help. I will have to explore any join implications as I eagerly load some object graphs. Data retrieval is not quite as big of a deal if the insertion/updating/deleting keeps the data clean. Still does not address my referential integrity concerns, but it is something to explore. My factory is more centralized so I would just need to enable the filter in a couple of places throughout the system.
Regards, Kyle On Apr 17, 6:34 am, CSharper <[email protected]> wrote: > Could NHibernate Filters take the role of those extension methods so > in the end the feature is nonintrusive on the query code? > The only thing needed would be setting the correct filter after > creating each session. But I don't know if the feature is sufficient > to cover all the required cases. > > On 16 Apr., 23:30, Craig van Nieuwkerk <[email protected]> wrote: > > > > > > > > > For my multi-tenant app I added the extra Tenant ID column (or > > OrganisationId I called it) but I didn't add it to the primary key, it was > > just a foreign key to the Organisation table. Then I created some helper > > extension methods on top of QueryOver to automatically add it to any > > queries and security extension methods to make sure when they could only > > see their own data (if they changed the id in the url). > > > It wasn't that hard, but if you have a large app already it could take a > > while to retro fit. > > > Craig > > > On Tue, Apr 17, 2012 at 7:56 AM, Kyle <[email protected]> wrote: > > > I was trying to create a referentially safe multi-tenant db from an > > > existing db by adding a second primary key column to my existing HiLo > > > generated ID primary key column. > > > > I am sorry if this is answered elsewhere -- I have tried to search for > > > a solution/recommendation for this multiple place. I have seen good > > > discussions about adding the Tenant ID and understand concerns about > > > it. But that ship has sailed, and I must implement shared tables > > > isolated by TenantID. By adding it to the primary key, I resolve some > > > of the security concerns. Query length might become a problem with > > > the extra join criterion on EACH join (argh), but I can live with what > > > I have seen so far. > > > > Existing table: > > > > Create Table Lookup ( > > > ID long NOT NULL PRIMARY KEY, > > > Name nvarchar(100) NOT NULL, > > > Description nvarchar(1000) NOT NULL ) > > > > Create Table UsesLookup ( > > > ID long NOT NULL PRIMARY KEY, > > > LookupID long NOT NULL, -- foreign key back to Lookup table > > > Value long NOT NULL) > > > > New tables: > > > > Create Table Lookup ( > > > ID long NOT NULL, > > > TenantID long NOT NULL, > > > Name nvarchar(100) NOT NULL, > > > Description nvarchar(1000) NOT NULL ) > > > > ALTER TABLE [dbo].[Lookup] ADD CONSTRAINT PK_Lookup PRIMARY KEY > > > NONCLUSTERED ([TenantID], [ID]) > > > > Create Table UsesLookup ( > > > ID long NOT NULL PRIMARY KEY, > > > TenantID long NOT NULL PRIMARY KEY, > > > LookupID long NOT NULL, -- foreign key back to Lookup table > > > Value long NOT NULL) > > > > ALTER TABLE [dbo].[UsesLookup] ADD CONSTRAINT PK_UsesLookup PRIMARY > > > KEY NONCLUSTERED ([TenantID], [ID]) > > > > ALTER TABLE dbo.UsesLookup ADD CONSTRAINT FK_UsesLookup_Lookup_Lookup > > > FOREIGN KEY (TenantID, LookupID) REFERENCES dbo.Lookup(TenantID, ID) > > > > Using a composite-id, I lose the generator and some key optimizations > > > that I would hope to keep. > > > Does anyone have a solution for this, perhaps using a IUserType that > > > supports 2 columns? Or some NHibernate feature I am missing? > > > > Thanks, > > > Kyle > > > > -- > > > You received this message because you are subscribed to the Google Groups > > > "nhusers" group. > > > To post to this group, send email to [email protected]. > > > To unsubscribe from this group, send email to > > > [email protected]. > > > For more options, visit this group at > > >http://groups.google.com/group/nhusers?hl=en. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
