I got it working using a IUserType mapper, and if I wanted to let
NHibernate dictate my data model, could have done so without modifying
NHibernate, but I wanted a column that was shared across multiple
foreign keys and NHibernate needed each key column to be unique.
I defined a Key class:
public class Key
{
public Int64 TenantID { get; set; }
public Int64 ID { get; set; }
public override int GetHashCode()
{
return ID.GetHashCode();
}
public override bool Equals(object obj)
{
if (obj is Key)
{
return ID.Equals(((Key)obj).ID);
}
return false;
}
}
The key is in the IUserType: Here is a partial impl:
public class KeyUserType : IUserType
{
private static readonly NHibernate.SqlTypes.SqlType[]
_sqltypes = new SqlType[] { SqlTypeFactory.Int64,
SqlTypeFactory.Int64 };
public Type ReturnedType
{
get
{
return typeof(Key);
}
}
public NHibernate.SqlTypes.SqlType[] SqlTypes
{
get
{
return _sqltypes;
}
}
....
}
Here is the mapping I used.
<class name="Lookup" table="Lookup" schema="dbo">
<id name="Key" type="KeyUserType, TestTenantNHib" >
<column name="TenantID" sql-type="System.Int64" not-null="true" /
>
<column name="ID" sql-type="System.Int64" not-null="true" />
<generator class="TestTenantNHib.MultiTenantHiLoTableGenerator,
TestTenantNHib">
<param name="table">LookupHiLo</param>
<param name="schema">dbo</param>
<param name="column">lo</param>
<param name="max_lo">15</param>
</generator>
</id>
<property name="Name" column="Name" />
<property name="Description" column="Description" />
</class>
Here is a hilo generator subclass used to continue to use the hilo
paradigm.
namespace TestTenantNHib
{
class MultiTenantHiLoTableGenerator : TableHiLoGenerator
{
#region IConfigurable Members
/// <summary>
/// Configures the TableHiLoGenerator by reading the value of
<c>table</c>,
/// <c>column</c>, <c>max_lo</c>, and <c>schema</c> from the
<c>parms</c> parameter.
/// </summary>
/// <param name="type">The <see cref="IType"/> the identifier
should
be.</param>
/// <param name="parms">An <see cref="IDictionary"/> of Param
values
that are keyed by parameter name.</param>
/// <param name="dialect">The <see cref="Dialect.Dialect"/> to
help
with Configuration.</param>
public override void Configure(IType type, IDictionary<string,
string> parms, NHibernate.Dialect.Dialect dialect)
{
base.Configure(NHibernateUtil.Int64, parms, dialect);
}
#endregion //IConfigurable Members
#region IIdentifierGenerator Members
/// <summary>
/// Generate a <see cref="Int64"/> for the identifier by
selecting and updating a value in a table.
/// </summary>
/// <param name="session">The <see cref="ISessionImplementor"/
> this id is being generated in.</param>
/// <param name="obj">The entity for which the id is being
generated.</param>
/// <returns>The new identifier as a <see cref="Int64"/>.</
returns>
[MethodImpl(MethodImplOptions.Synchronized)]
public override object Generate(ISessionImplementor session,
object obj)
{
Key ret = new Key();
if (obj is Base)
{
ret.TenantID = ((Base)obj).Key.TenantID;
}
ret.ID = (long)base.Generate(session, obj);
return ret;
}
#endregion
}
}
Up to this point, the solution fits within NHibernate built-in
extensibility.
<class name="ObjectUsingLookup" table="ObjectUsingLookup"
schema="dbo">
<id name="Key" type="KeyUserType, TestTenantNHib" >
<column name="TenantID" sql-type="System.Int64" not-null="true" /
>
<column name="ID" sql-type="System.Int64" not-null="true" />
<generator class="TestTenantNHib.MultiTenantHiLoTableGenerator,
TestTenantNHib">
<param name="table">ObjectUsingLookupHiLo</param>
<param name="schema">dbo</param>
<param name="column">lo</param>
<param name="max_lo">15</param>
</generator>
</id>
<property name="Name" column="Name" />
<property name="Description" column="Description" />
<many-to-one name="Lookup" cascade="all"
class="TestTenantNHib.Lookup">
<column name="TenantID" sql-type="System.Int64" not-null="true" /
> // THIS IS WHAT BREAKS NHIBERNATE BUILT IN FUNCTIONALITY -- TO USE
BUILT IN FUNC, make this name unique, like LookupTenantID and modify
the table structure and FKs.
<column name="LookupID" sql-type="System.Int64" not-null="true" /
>
</many-to-one>
</class>
This allows for the generator to produce a true primary key, but
allows me to add the tenant identifier in for RI and data isolation.
By using this approach, I also allow poor SQL to be written that does
not fully utilize the FK and can use the hilo generated part of the
PK.
Happy coding,
Kyle
On Apr 16, 4:56 pm, 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.
