Multi-Tenant Hierachical Framework w/ SQL Server

originally posted 2013-4-3

1	originally posted 2013-4-3

Store hierachical organization data
All tables in a multi-tenant database would have a [OrganizationName] field.
The Organization table stores the hierarchical relationships for each organization.
SQL Server 2008+ supports the hierarchyID data type (not used here), which would make some of the hierarchical manipulations easier. I needed to support SQL Server 2005 as well, so I didn’t have the pleasure.

CREATE TABLE [dbo].[Organization](
 [Name] [nvarchar](100) NOT NULL,
 [DisplayName] [nvarchar](100) NULL,
 [ParentName] [nvarchar](100) NULL,
 [Enabled] [bit] NOT NULL,
 CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED 
(
 [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--get the hierarchical path for an organization
create function [dbo].[fGetOrganizationPath] (@organizationName nvarchar(100))
returns nvarchar(max)
as
begin
 declare @retVal nvarchar(max);
 set @retVal = N''

 select
 @retVal = 
 (
  select
   case when [OrganizationName] &lt;&gt; 'root' then N'/' + [OrganizationName] 
   else '' 
   end
  from [dbo].tfGetOrganizationNames_Ascend(@organizationName)
  for xml path(''), type, root).value('root[1]','nvarchar(max)'
 )
 return @retVal;
end
GO

--get parent organization names
create function [dbo].[tfGetOrganizationNames_Ascend] 
(
 @organizationName nvarchar(100)
)
returns @tab table ([OrganizationName] nvarchar(100), ParentNamespace nvarchar(100), Level int)
as
begin
 with c as (
 select
  n1.[OrganizationName]
  ,n1.[ParentOrganizationName]
  ,[Level] = 0
 from
  [dbo].[OrganizationName] n1
  left outer join [dbo].[OrganizationName] n2 on n1.[ParentOrganizationName] = n2.[OrganizationName] 
 where
  n1.[OrganizationName] = @organizationName
 union all
 select
  n1.[OrganizationName]
  ,n1.[ParentOrganizationName]
  ,[Level] = c.[Level] + 1
 from
  [dbo].[OrganizationName] n1
  inner join c on c.[ParentOrganizationName] = n1.[OrganizationName])
  
 insert @tab 
 select top 1000
  [OrganizationName]
  ,[ParentOrganizationName]
  ,[Level]
 from
  c
 order by
  [Level] desc
 option(maxrecursion 1000)
 
 return;
end
GO

--generic error handler
create procedure [dbo].[spHandleError] as
begin
 declare @errmsg nvarchar(2048)
 declare @severity tinyint
 declare @state tinyint
 declare @errno int
 declare @proc sysname
 declare @lineno int
            
 select @errmsg = error_message(), @severity = error_severity()
 ,@state  = error_state(), @errno = error_number()
 ,@proc   = error_procedure(), @lineno = error_line()
        
 IF @errmsg NOT LIKE '[[%]]'
 begin 
  select @errmsg = @errmsg + ' [' + isnull(quotename(@proc),'') + '] ' 
  raiserror(@errmsg, @severity, @state)
 end
 else raiserror(@errmsg, @severity, @state)
end
GO

--trigger to make sure organizations added to the Organization table meet the requirements
create trigger [dbo].[tCheckOrganizationName]
on [dbo].[Organization]
after insert,update
as
 begin try
  set nocount on
  set xact_abort on 
  declare @root nvarchar(10);
  set @root = 'root'; 
  
  --verifies new organization names against requirements prior to insert/update

  declare @tab table ([Name] nvarchar(100), [ParentName] nvarchar(100))
  declare @dummyTab table ([Name] nvarchar(100), [ParentName] nvarchar(100))
  
  --combine the existing and pending data. pending data for a organization name is used if the organization name is also found in existing data
  insert @tab
  select [Name],[ParentName] from inserted
  union 
  select [Name],[ParentName] from [dbo].[Name] where [Name] not in (select [Name] from inserted)
  
  --a non-root organization name must have a non-null parent organization name
  if exists (select [Name] from inserted where [Name] &lt;&gt; @root and [ParentName] is null)
  begin
   --error: non-root organization name requires a parent
   raiserror ('A non-root organization name must specify a parent organization name.',11,1)
  end 
  
  --the ROOT organization name must have a null parent organization name
  if exists (select [Name] from inserted where [Name] = @root and [ParentName] is not null)
  begin
   --error: root organization name cannot have a parent
   raiserror ('The root organization name must specify a null parent organization name.',11,1)
  end 
  
     --a organization name's parent organization name must exist
     if exists
     ( 
   select t2.[Name] 
   from @tab t 
   left outer join @tab t2 on t.[ParentName] = t2.[Name]
   where t.[ParentName] is not null and t2.[Name] is null
  )
  begin
   --error: non-existent parent specified
   raiserror ('A organization name specifies a parent organization name that does not exist.',11,1)
  end      
  
  --look for circular references in the pending hierarchy
  begin try
   declare @dummy bit; --trick the sql compiler. it wants a statement that ends with a ';' prior to the 'with' statement.

   with c as 
   (
    select distinct
     n1.[Name]
     ,n1.[ParentName]
    from
     @tab n1
     left outer join @tab n2 on n1.[ParentName] = n2.[Name] 
     
    union all
    
    select 
     n1.[Name]
     ,n1.[ParentName]
    from
     @tab n1
     inner join c on c.[ParentName] = n1.[Name]
   )
   
   insert @dummyTab
   select distinct *
   from c
   option(maxrecursion 1000) --this will throw an exception if circular references exist
   
  end try
  begin catch
   --error: cicular reference(s) found
   IF error_number() = 530 raiserror ('Operation would cause an illegal circular reference in organization name',11,1)
   else exec spHandleError
  end catch

 end try
 begin catch
  exec spHandleError
 end catch
GO
 
--called by the application when it creates a new SqlConnection
--the context travels with the connection for its lifetime.
--The context can be inspected by using T-SQL or C#.
create procedure [dbo].[spSetContext](@context nvarchar(100)) as
begin
 set nocount on
 declare @ctx varbinary(128)

 if([dbo].fIsNullOrWhitespace(@context) = 0) 
 begin
  
  declare @c varchar(127)
  set @c = @context
  declare @length tinyint 
  set @length = len(@c)
  select @ctx = convert(binary(1), @length) + convert(varbinary(127), @c)
 end
 else set @ctx = convert(binary(1), 0)
 set context_info @ctx
end
GO

--retrieve the context set on the current database connection
create function [dbo].[fGetContext]()
returns nvarchar(100) as 
begin
 declare @context nvarchar(100)
 set @context = null
 declare @ctx varbinary(128) 
 set @ctx = CONTEXT_INFO();
 declare @length tinyint 
 set @length = convert(tinyint, substring(@ctx, 1, 1))
 if(@length &gt; 0) select @context = convert(varchar(127), substring(@ctx, 2, 1 + @length))
 return @context
end
GO

--example:
--if the connection was created with the proper context (an organization name)
--a view can take advantage of the framework to select data for the current organization, 
--including data for its child organizations.
--This effectively creates a consolidated view of the data for a mult-tenant schema (one that stores a OrganizationName in each table)

create view [dbo].[v_SomeTable] as
select * 
from [npt_system32].[system].[tfGetOrganizationNamesForContext]() o
join [dbo].[SomeTable] t on o.[Organization] = t.[Organization]

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

CREATE TABLE [dbo].[Organization](

[Name] [nvarchar](100) NOT NULL,

[DisplayName] [nvarchar](100) NULL,

[ParentName] [nvarchar](100) NULL,

[Enabled] [bit] NOT NULL,

CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED

(

[Name] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

--get the hierarchical path for an organization

create function [dbo].[fGetOrganizationPath] (@organizationName nvarchar(100))

returns nvarchar(max)

begin

declare @retVal nvarchar(max);

set @retVal = N''

select

@retVal =

(

select

case when [OrganizationName] <> 'root' then N'/' + [OrganizationName]

else ''

end

from [dbo].tfGetOrganizationNames_Ascend(@organizationName)

for xml path(''), type, root).value('root[1]','nvarchar(max)'

)

return @retVal;

end

--get parent organization names

create function [dbo].[tfGetOrganizationNames_Ascend]

(

@organizationName nvarchar(100)

)

returns @tab table ([OrganizationName] nvarchar(100), ParentNamespace nvarchar(100), Level int)

begin

with c as (

select

n1.[OrganizationName]

,n1.[ParentOrganizationName]

,[Level] = 0

from

[dbo].[OrganizationName] n1

left outer join [dbo].[OrganizationName] n2 on n1.[ParentOrganizationName] = n2.[OrganizationName]

where

n1.[OrganizationName] = @organizationName

union all

select

n1.[OrganizationName]

,n1.[ParentOrganizationName]

,[Level] = c.[Level] + 1

from

[dbo].[OrganizationName] n1

inner join c on c.[ParentOrganizationName] = n1.[OrganizationName])

insert @tab

select top 1000

[OrganizationName]

,[ParentOrganizationName]

,[Level]

from

order by

[Level] desc

option(maxrecursion 1000)

return;

end

--generic error handler

create procedure [dbo].[spHandleError] as

begin

declare @errmsg nvarchar(2048)

declare @severity tinyint

declare @state tinyint

declare @errno int

declare @proc sysname

declare @lineno int

select @errmsg = error_message(), @severity = error_severity()

,@state = error_state(), @errno = error_number()

,@proc = error_procedure(), @lineno = error_line()

IF @errmsg NOT LIKE '[[%]]'

begin

select @errmsg = @errmsg + ' [' + isnull(quotename(@proc),'') + '] '

raiserror(@errmsg, @severity, @state)

end

else raiserror(@errmsg, @severity, @state)

end

--trigger to make sure organizations added to the Organization table meet the requirements

create trigger [dbo].[tCheckOrganizationName]

on [dbo].[Organization]

after insert,update

begin try

set nocount on

set xact_abort on

declare @root nvarchar(10);

set @root = 'root';

--verifies new organization names against requirements prior to insert/update

declare @tab table ([Name] nvarchar(100), [ParentName] nvarchar(100))

declare @dummyTab table ([Name] nvarchar(100), [ParentName] nvarchar(100))

--combine the existing and pending data. pending data for a organization name is used if the organization name is also found in existing data

insert @tab

select [Name],[ParentName] from inserted

union

select [Name],[ParentName] from [dbo].[Name] where [Name] not in (select [Name] from inserted)

--a non-root organization name must have a non-null parent organization name

if exists (select [Name] from inserted where [Name] <> @root and [ParentName] is null)

begin

--error: non-root organization name requires a parent

raiserror ('A non-root organization name must specify a parent organization name.',11,1)

end

--the ROOT organization name must have a null parent organization name

if exists (select [Name] from inserted where [Name] = @root and [ParentName] is not null)

begin

--error: root organization name cannot have a parent

raiserror ('The root organization name must specify a null parent organization name.',11,1)

end

--a organization name's parent organization name must exist

if exists

(

select t2.[Name]

from @tab t

left outer join @tab t2 on t.[ParentName] = t2.[Name]

where t.[ParentName] is not null and t2.[Name] is null

)

begin

--error: non-existent parent specified

raiserror ('A organization name specifies a parent organization name that does not exist.',11,1)

end

--look for circular references in the pending hierarchy

begin try

declare @dummy bit; --trick the sql compiler. it wants a statement that ends with a ';' prior to the 'with' statement.

with c as

(

select distinct

n1.[Name]

,n1.[ParentName]

from

@tab n1

left outer join @tab n2 on n1.[ParentName] = n2.[Name]

union all

select

n1.[Name]

,n1.[ParentName]

from

@tab n1

inner join c on c.[ParentName] = n1.[Name]

)

insert @dummyTab

select distinct *

from c

option(maxrecursion 1000) --this will throw an exception if circular references exist

end try

begin catch

--error: cicular reference(s) found

IF error_number() = 530 raiserror ('Operation would cause an illegal circular reference in organization name',11,1)

else exec spHandleError

end catch

end try

begin catch

exec spHandleError

end catch

--called by the application when it creates a new SqlConnection

--the context travels with the connection for its lifetime.

--The context can be inspected by using T-SQL or C#.

create procedure [dbo].[spSetContext](@context nvarchar(100)) as

begin

set nocount on

declare @ctx varbinary(128)

if([dbo].fIsNullOrWhitespace(@context) = 0)

begin

declare @c varchar(127)

set @c = @context

declare @length tinyint

set @length = len(@c)

select @ctx = convert(binary(1), @length) + convert(varbinary(127), @c)

end

else set @ctx = convert(binary(1), 0)

set context_info @ctx

end

--retrieve the context set on the current database connection

create function [dbo].[fGetContext]()

returns nvarchar(100) as

begin

declare @context nvarchar(100)

set @context = null

declare @ctx varbinary(128)

set @ctx = CONTEXT_INFO();

declare @length tinyint

set @length = convert(tinyint, substring(@ctx, 1, 1))

if(@length > 0) select @context = convert(varchar(127), substring(@ctx, 2, 1 + @length))

return @context

end

--example:

--if the connection was created with the proper context (an organization name)

--a view can take advantage of the framework to select data for the current organization,

--including data for its child organizations.

--This effectively creates a consolidated view of the data for a mult-tenant schema (one that stores a OrganizationName in each table)

create view [dbo].[v_SomeTable] as

select *

from [npt_system32].[system].[tfGetOrganizationNamesForContext]() o

join [dbo].[SomeTable] t on o.[Organization] = t.[Organization]

[C#] version of [dbo].[spSetContext]

protected void SetSqlContext(SqlConnection connection)
{
    const string sql = @"set context_info @ctx;"; //if not exists(select [Name] from (select [name] = dbo.fgetcontext()) as f1 where [Name] = '" + ns + "') raiserror('context not set!',11,1)";
    string ns = Namespace.Id;
    byte[] bytes = null;
    if (!string.IsNullOrWhiteSpace(ns))
    {
        //set the context
        //encode the namespace into a byte array
        var encoding = new System.Text.ASCIIEncoding();
        byte[] tempbytes = encoding.GetBytes(ns);
        bytes = new byte[tempbytes.Length + 1];
        tempbytes.CopyTo(bytes, 1);
        bytes[0] = Convert.ToByte(ns.Length);
    }
    else bytes = new byte[0]; //clear the context
            
    if (connection == null) return;
    SqlCommand command = GetNewSqlCommand(connection);
    command.CommandType = System.Data.CommandType.Text;
    command.CommandText = sql;
    var p1 = command.Parameters.Add("@ctx", System.Data.SqlDbType.VarBinary, 128);
    p1.Value = bytes;
    command.ExecuteNonQuery();
}

protected void SetSqlContext(SqlConnection connection)

{

const string sql = @"set context_info @ctx;"; //if not exists(select [Name] from (select [name] = dbo.fgetcontext()) as f1 where [Name] = '" + ns + "') raiserror('context not set!',11,1)";

string ns = Namespace.Id;

byte[] bytes = null;

if (!string.IsNullOrWhiteSpace(ns))

{

//set the context

//encode the namespace into a byte array

var encoding = new System.Text.ASCIIEncoding();

byte[] tempbytes = encoding.GetBytes(ns);

bytes = new byte[tempbytes.Length + 1];

tempbytes.CopyTo(bytes, 1);

bytes[0] = Convert.ToByte(ns.Length);

}

else bytes = new byte[0]; //clear the context

if (connection == null) return;

SqlCommand command = GetNewSqlCommand(connection);

command.CommandType = System.Data.CommandType.Text;

command.CommandText = sql;

var p1 = command.Parameters.Add("@ctx", System.Data.SqlDbType.VarBinary, 128);

p1.Value = bytes;

command.ExecuteNonQuery();

}