// Constructor
public CompaniesController(AdminRepository db, ILogger <CompaniesController> logger,
IShardResolver shardResolver, IExternalUserAccessor externalUserAccessor, IClientInfoAccessor clientInfoAccessor)
{
_repo = db;
_logger = logger;
_shardResolver = shardResolver;
_externalUserAccessor = externalUserAccessor;
_clientInfoAccessor = clientInfoAccessor;
}
/// <summary>
/// This trick makes it possible to injected the ApplicationContext into other components via DI as usual
/// but it automatically configures itself with the correct options. Inspired from this Microsoft sample: https://bit.ly/2TIEFMA
/// </summary>
/// <param name="shardResolver">The service that resolves the shard connection string</param>
/// <param name="tenantIdProvider">The service that retrieves tenants Ids from the headers</param>
/// <returns></returns>
private static DbContextOptions <ApplicationContext> CreateDbContextOptions(
IShardResolver shardResolver, ITenantIdProvider tenantIdProvider,
IUserProvider userService, ITenantUserInfoAccessor accessor)
{
// Prepare the options based on the connection created with the shard manager
var optionsBuilder = new DbContextOptionsBuilder <ApplicationContext>();
string connectionString = shardResolver.GetShardConnectionString();
if (tenantIdProvider is DesignTimeTenantIdProvider)
{
// Only for design time when running "ef migrations" command from the CLI
optionsBuilder = optionsBuilder.UseSqlServer(connectionString);
}
else
{
int tenantId = tenantIdProvider.GetTenantId() ?? throw new Controllers.Misc.BadRequestException("Tenant Id was not supplied");
// Unless this is a fake design time resolver, apply row level security and pass context info
SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand cmd = sqlConnection.CreateCommand();
cmd.CommandText = @"
-- Set the global values of the session context
EXEC sp_set_session_context @key=N'TenantId', @value=@TenantId;
EXEC sp_set_session_context @key=N'Culture', @value=@Culture;
EXEC sp_set_session_context @key=N'NeutralCulture', @value=@NeutralCulture;
-- Get the User Id
DECLARE
@UserId INT,
@ExternalId NVARCHAR(450),
@Email NVARCHAR(255),
@SettingsVersion UNIQUEIDENTIFIER,
@PermissionsVersion UNIQUEIDENTIFIER,
@ViewsAndSpecsVersion UNIQUEIDENTIFIER,
@UserSettingsVersion UNIQUEIDENTIFIER,
@PrimaryLanguageId NVARCHAR(255),
@PrimaryLanguageSymbol NVARCHAR(255),
@SecondaryLanguageId NVARCHAR(255),
@SecondaryLanguageSymbol NVARCHAR(255)
;
SELECT
@UserId = Id,
@ExternalId = ExternalId,
@Email = Email,
@PermissionsVersion = PermissionsVersion,
@UserSettingsVersion = UserSettingsVersion
FROM [dbo].[LocalUsers]
WHERE TenantId = @TenantId AND IsActive = 1 AND (ExternalId = @ExternalUserId OR Email = @UserEmail);
-- Set LastAccess (Works only if @UserId IS NOT NULL)
UPDATE [dbo].[LocalUsers] SET LastAccess = SYSDATETIMEOFFSET() WHERE Id = @UserId;
-- Get hashes
SELECT
@SettingsVersion = SettingsVersion,
@ViewsAndSpecsVersion = ViewsAndSpecsVersion,
@PrimaryLanguageId = PrimaryLanguageId,
@PrimaryLanguageSymbol = PrimaryLanguageSymbol,
@SecondaryLanguageId = SecondaryLanguageId,
@SecondaryLanguageSymbol = SecondaryLanguageSymbol
FROM [dbo].[Settings]
WHERE TenantId = @TenantId
-- Set the User Id
EXEC sp_set_session_context @key=N'UserId', @value=@UserId;
-- Return the user information
SELECT
@UserId AS userId,
@ExternalId AS ExternalId,
@Email AS Email,
@SettingsVersion AS SettingsVersion,
@PermissionsVersion AS PermissionsVersion,
@UserSettingsVersion AS UserSettingsVersion,
@ViewsAndSpecsVersion AS ViewsAndSpecsVersion,
@PrimaryLanguageId AS PrimaryLanguageId,
@PrimaryLanguageSymbol AS PrimaryLanguageSymbol,
@SecondaryLanguageId AS SecondaryLanguageId,
@SecondaryLanguageSymbol AS SecondaryLanguageSymbol;
";
cmd.Parameters.AddWithValue("@TenantId", tenantId);
cmd.Parameters.AddWithValue("@ExternalUserId", userService.GetUserId());
cmd.Parameters.AddWithValue("@UserEmail", userService.GetUserEmail());
cmd.Parameters.AddWithValue("@Culture", CultureInfo.CurrentUICulture.Name);
cmd.Parameters.AddWithValue("@NeutralCulture", CultureInfo.CurrentUICulture.IsNeutralCulture ? CultureInfo.CurrentUICulture.Name : CultureInfo.CurrentUICulture.Parent.Name);
sqlConnection.Open();
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
int i = 0;
var info = new TenantUserInfo
{
UserId = reader.IsDBNull(i) ? (int?)null : reader.GetInt32(i++),
ExternalId = reader.IsDBNull(i) ? null : reader.GetString(i++),
Email = reader.IsDBNull(i) ? null : reader.GetString(i++),
SettingsVersion = reader.IsDBNull(i) ? null : reader.GetGuid(i++).ToString(),
PermissionsVersion = reader.IsDBNull(i) ? null : reader.GetGuid(i++).ToString(),
UserSettingsVersion = reader.IsDBNull(i) ? null : reader.GetGuid(i++).ToString(),
ViewsAndSpecsVersion = reader.IsDBNull(i) ? null : reader.GetGuid(i++).ToString(),
PrimaryLanguageId = reader.IsDBNull(i) ? null : reader.GetString(i++),
PrimaryLanguageSymbol = reader.IsDBNull(i) ? null : reader.GetString(i++),
SecondaryLanguageId = reader.IsDBNull(i) ? null : reader.GetString(i++),
SecondaryLanguageSymbol = reader.IsDBNull(i) ? null : reader.GetString(i++),
};
// Provide the user throughout the current session
accessor.SetInfo(tenantId, info);
}
else
{
throw new Controllers.Misc.BadRequestException("Something went wrong while querying the user ID from the Database");
}
}
// Prepare the options based on the connection created with the shard manager
optionsBuilder = optionsBuilder.UseSqlServer(sqlConnection);
}
return(optionsBuilder
.ReplaceService <IMigrationsSqlGenerator, CustomSqlServerMigrationsSqlGenerator>()
.Options);
}
public ApplicationRepositoryLite(IShardResolver shardResolver, ILogger <ApplicationRepositoryLite> logger)
{
_shardResolver = shardResolver;
_logger = logger;
}
// Constructor
public ApplicationContext(IShardResolver shardProvider, ITenantIdProvider tenantIdProvider, IUserProvider userIdProvider, ITenantUserInfoAccessor accessor) :
base(CreateDbContextOptions(shardProvider, tenantIdProvider, userIdProvider, accessor))
{
_tenantIdProvider = tenantIdProvider;
}
private void ArrangeDatabasesForTests(string adminConnString, string adminEmail, IShardResolver shardResolver)
{
// Prepare the Admin database
var databaseId = 101; // from SqlDatabase.Id IDENTITY(101, 1)
var projectDir = Directory.GetCurrentDirectory();
var seedAdminPath = Path.Combine(projectDir, "SeedAdmin.sql");
var seedAdminSql = File.ReadAllText(seedAdminPath);
using (var conn = new SqlConnection(adminConnString))
{
using (var cmd = conn.CreateCommand())
{
cmd.Parameters.AddWithValue("@Email", adminEmail);
cmd.Parameters.AddWithValue("@DatabaseName", $"Tellma.IntegrationTests.{databaseId}");
cmd.CommandText = seedAdminSql;
conn.Open();
cmd.ExecuteNonQuery();
}
}
var appConnString = shardResolver.GetConnectionString(databaseId, cancellation: default).GetAwaiter().GetResult();
var seedAppPath = Path.Combine(projectDir, "SeedApplication.sql");
var seedAppSql = File.ReadAllText(seedAppPath);
using (var conn = new SqlConnection(appConnString))
{
using (var cmd = conn.CreateCommand())
{
cmd.Parameters.AddWithValue("@Email", adminEmail);
cmd.CommandText = seedAppSql;
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
