/// <summary>
/// Resolves the specified upgrade identifier.
/// </summary>
/// <param name="upgradeId">The upgrade identifier.</param>
/// <returns></returns>
public EntityAlias Resolve(Guid upgradeId)
{
EntityAlias alias;
if (!_cache.TryGetValue(upgradeId, out alias))
{
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
using (var command = ctx.CreateCommand("spResolveTenantAppAlias", CommandType.StoredProcedure))
{
ctx.AddParameter(command, "@upgradeId", DbType.Guid, upgradeId);
ctx.AddParameter(command, "@tenantId", DbType.Int64, _tenantId);
var result = command.ExecuteScalar( );
if (result != null && result != DBNull.Value)
{
alias = new EntityAlias(result.ToString( ));
_cache[upgradeId] = alias;
}
else
{
_cache[upgradeId] = null;
}
}
}
}
return(alias);
}
/// <summary>
/// Gets the alias by id from database.
/// </summary>
/// <param name="id">The id.</param>
/// <param name="direction">The direction.</param>
/// <returns>
/// The entity alias if found, null otherwise
/// </returns>
private static EntityAlias GetAliasByIdFromDatabase(long id, Direction direction)
{
EntityAlias alias;
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
using (IDbCommand command = ctx.CreateCommand())
{
/////
// TODO: Replace with a stored procedure call.
/////
command.CommandText = @"-- Entity: Resolve ID to alias
SELECT a.Namespace, a.Data
FROM dbo.Data_Alias a
WHERE a.EntityId = @id AND a.AliasMarkerId = @direction AND a.TenantId = @tenantId";
ctx.AddParameter(command, "@id", DbType.Int64, id);
ctx.AddParameter(command, "@direction", DbType.Int32, direction == Direction.Forward ? 0 : 1);
ctx.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
using (IDataReader reader = command.ExecuteReader())
{
if (reader != null && reader.Read())
{
alias = new EntityAlias(reader.GetString(0), reader.GetString(1));
}
else
{
return(null);
}
}
}
return(alias);
}
/// <summary>
/// Confirms the entity has been published.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="packageId">The package identifier.</param>
/// <param name="entityUid">The entity upgrade id.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">context</exception>
/// <exception cref="System.ArgumentException">
/// @Invalid PackageId;packageId
/// or
/// @Invalid EntityUid;entityUid
/// </exception>
public static bool ConfirmAppLibraryEntity(DatabaseContext context, Guid packageId, Guid entityUid)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (packageId == Guid.Empty)
{
throw new ArgumentException(@"Invalid PackageId", "packageId");
}
if (entityUid == Guid.Empty)
{
throw new ArgumentException(@"Invalid EntityUid", "entityUid");
}
const string query = @"SELECT Id FROM AppEntity WHERE AppVerUid = @packageId AND EntityUid = @entityUid";
using (IDbCommand command = context.CreateCommand(query))
{
context.AddParameter(command, "@packageId", DbType.Guid, packageId);
context.AddParameter(command, "@entityUid", DbType.Guid, entityUid);
object result = command.ExecuteScalar( );
if (result != null && result != DBNull.Value)
{
return(true);
}
}
return(false);
}
/// <summary>
/// Set a value into secured data.
/// </summary>
/// <param name="tenantId">The tenant Id</param>
/// <param name="context">A string representing the context of the storage.</param>
/// <param name="secureId">The secured Id as proved by the Set call.</param>
/// <param name="value">The value to be stored</param>
public void Update(Guid secureId, string value)
{
if (secureId == Guid.Empty)
{
throw new ArgumentException(nameof(secureId));
}
if (value == null)
{
throw new ArgumentNullException(nameof(value));
}
long userId;
RequestContext.TryGetUserId(out userId);
using (DatabaseContextInfo.SetContextInfo("Update secure data"))
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
using (IDbCommand command = ctx.CreateCommand("spSecuredDataUpdate", CommandType.StoredProcedure))
{
ctx.AddParameter(command, "@secureId", DbType.Guid, secureId);
ctx.AddParameter(command, "@data", DbType.String, value);
ctx.AddParameter(command, "@context", DbType.AnsiString, DatabaseContextInfo.GetMessageChain(userId));
var rows = command.ExecuteNonQuery( );
if (rows < 1)
{
throw new SecureIdNotFoundException($"Key not present. secureId: '{secureId}'");
}
}
}
}
/// <summary>
/// Confirms the entity has been deployed.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="tenantId">The tenant identifier.</param>
/// <param name="entityUid">The entity upgrade id.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">context</exception>
/// <exception cref="System.ArgumentException">
/// @Invalid TenantId;tenantId
/// or
/// @Invalid EntityUid;entityUid
/// </exception>
public static bool ConfirmTenantEntity(DatabaseContext context, long tenantId, Guid entityUid)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (tenantId < 0)
{
throw new ArgumentException(@"Invalid TenantId", "tenantId");
}
if (entityUid == Guid.Empty)
{
throw new ArgumentException(@"Invalid EntityUid", "entityUid");
}
const string query = @"SELECT Id FROM Entity WHERE TenantId = @tenantId AND UpgradeId = @entityUid";
using (IDbCommand command = context.CreateCommand(query))
{
context.AddParameter(command, "@tenantId", DbType.Int64, tenantId);
context.AddParameter(command, "@entityUid", DbType.Guid, entityUid);
object result = command.ExecuteScalar( );
if (result != null && result != DBNull.Value)
{
return(true);
}
}
return(false);
}
/// <summary>
/// Gets the tenant current package identifier by unique identifier.
/// </summary>
/// <param name="tenantId">The tenant identifier.</param>
/// <param name="applicationGuid">The application unique identifier.</param>
/// <returns></returns>
public static long GetTenantCurrentPackageIdByGuid(long tenantId, Guid applicationGuid)
{
long packageId = -1;
using (new GlobalAdministratorContext( ))
{
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
const string sql = @"
DECLARE @packageForApplication BIGINT = dbo.fnAliasNsId( 'packageForApplication', 'core', DEFAULT )
DECLARE @appVerId BIGINT = dbo.fnAliasNsId( 'appVerId', 'core', DEFAULT )
DECLARE @applicationId BIGINT = dbo.fnAliasNsId( 'applicationId', 'core', DEFAULT)
DECLARE @packageId BIGINT = dbo.fnAliasNsId( 'packageId', 'core', @tenantId )
SELECT
ag.EntityId
FROM
Entity e
INNER JOIN
Data_Guid g ON
e.UpgradeId = g.Data AND
g.FieldId = @applicationId AND
g.TenantId = 0
INNER JOIN
Relationship r ON
g.EntityId = r.ToId AND
r.TenantId = 0 AND
r.TypeId = @packageForApplication
INNER JOIN
Data_Guid ag ON
r.FromId = ag.EntityId AND
ag.FieldId = @appVerId AND
ag.TenantId = 0
INNER JOIN
Data_Guid tg ON
tg.Data = ag.Data AND
tg.FieldId = @packageId AND
tg.TenantId = @tenantId
WHERE
e.UpgradeId = @appGuid AND
e.TenantId = @tenantId" ;
using (IDbCommand command = ctx.CreateCommand(sql))
{
ctx.AddParameter(command, "@appGuid", DbType.Guid, applicationGuid);
ctx.AddParameter(command, "@tenantId", DbType.Int64, tenantId);
object packageIdObject = command.ExecuteScalar( );
if (packageIdObject != null && packageIdObject != DBNull.Value)
{
packageId = ( long )packageIdObject;
}
}
}
}
return(packageId);
}
/// <summary>
/// Confirms the tenant relationship.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="tenantId">The tenant identifier.</param>
/// <param name="direction">The direction.</param>
/// <param name="typeUid">The type upgrade id.</param>
/// <param name="sourceUid">The source upgrade id.</param>
/// <param name="targetUid">The target upgrade id.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">context</exception>
/// <exception cref="System.ArgumentException">
/// @Invalid TenantId;tenantId
/// or
/// @Invalid typeUid;typeUid
/// or
/// @Invalid sourceUid;sourceUid
/// or
/// @Invalid targetUid;targetUid
/// </exception>
public static bool ConfirmTenantRelationship(DatabaseContext context, long tenantId, Direction direction, Guid typeUid, Guid sourceUid, Guid targetUid)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (tenantId < 0)
{
throw new ArgumentException(@"Invalid TenantId", "tenantId");
}
if (typeUid == Guid.Empty)
{
throw new ArgumentException(@"Invalid typeUid", "typeUid");
}
if (sourceUid == Guid.Empty)
{
throw new ArgumentException(@"Invalid sourceUid", "sourceUid");
}
if (targetUid == Guid.Empty)
{
throw new ArgumentException(@"Invalid targetUid", "targetUid");
}
string query = direction == Direction.Forward ? "SELECT d.UpgradeId FROM Relationship r JOIN Entity t ON r.TypeId = t.Id AND r.TenantId = t.TenantId JOIN Entity s ON r.FromId = s.Id AND r.TenantId = s.TenantId JOIN Entity d ON r.ToId = d.Id AND r.TenantId = d.TenantId WHERE r.TenantId = @tenantId AND t.UpgradeId = @typeUid AND s.UpgradeId = @sourceUid" : "SELECT d.UpgradeId FROM Relationship r JOIN Entity t ON r.TypeId = t.Id AND r.TenantId = t.TenantId JOIN Entity s ON r.ToId = s.Id AND r.TenantId = s.TenantId JOIN Entity d ON r.FromId = d.Id AND r.TenantId = d.TenantId WHERE r.TenantId = @tenantId AND t.UpgradeId = @typeUid AND s.UpgradeId = @sourceUid";
using (IDbCommand command = context.CreateCommand(query))
{
context.AddParameter(command, "@tenantId", DbType.Int64, tenantId);
context.AddParameter(command, "@typeUid", DbType.Guid, typeUid);
context.AddParameter(command, "@sourceUid", DbType.Guid, sourceUid);
using (IDataReader reader = command.ExecuteReader( ))
{
while (reader.Read( ))
{
object result = reader.GetValue(0);
if (result == null || result == DBNull.Value)
{
continue;
}
if (result.Equals(targetUid))
{
return(true);
}
}
return(false);
}
}
}
/// <summary>
/// Confirms the application library relationship.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="packageId">The package identifier.</param>
/// <param name="direction">The direction.</param>
/// <param name="typeUid">The type upgrade id.</param>
/// <param name="sourceUid">The source upgrade id.</param>
/// <param name="targetUid">The target upgrade id.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">context</exception>
/// <exception cref="System.ArgumentException">
/// @Invalid PackageId;packageId
/// or
/// @Invalid typeUid;typeUid
/// or
/// @Invalid sourceUid;sourceUid
/// or
/// @Invalid targetUid;targetUid
/// </exception>
public static bool ConfirmAppLibraryRelationship(DatabaseContext context, Guid packageId, Direction direction, Guid typeUid, Guid sourceUid, Guid targetUid)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (packageId == Guid.Empty)
{
throw new ArgumentException(@"Invalid PackageId", "packageId");
}
if (typeUid == Guid.Empty)
{
throw new ArgumentException(@"Invalid typeUid", "typeUid");
}
if (sourceUid == Guid.Empty)
{
throw new ArgumentException(@"Invalid sourceUid", "sourceUid");
}
if (targetUid == Guid.Empty)
{
throw new ArgumentException(@"Invalid targetUid", "targetUid");
}
string query = direction == Direction.Forward ? "SELECT r.ToUid FROM AppRelationship r WHERE r.AppVerUid = @appVerUid AND r.TypeUid = @typeUid AND r.FromUid = @sourceUid" : "SELECT r.FromUid FROM AppRelationship r WHERE r.AppVerUid = @appVerUid AND r.TypeUid = @typeUid AND r.ToUid = @sourceUid";
using (IDbCommand command = context.CreateCommand(query))
{
context.AddParameter(command, "@appVerUid", DbType.Guid, packageId);
context.AddParameter(command, "@typeUid", DbType.Guid, typeUid);
context.AddParameter(command, "@sourceUid", DbType.Guid, sourceUid);
using (IDataReader reader = command.ExecuteReader( ))
{
while (reader.Read( ))
{
object result = reader.GetValue(0);
if (result == null || result == DBNull.Value)
{
continue;
}
if (result.Equals(targetUid))
{
return(true);
}
}
return(false);
}
}
}
/// <summary>
/// Gets the package identifier by name and version.
/// </summary>
/// <param name="applicationName">Name of the application.</param>
/// <param name="version">The version.</param>
/// <returns>
/// The package identifier.
/// </returns>
public static long GetPackageIdByNameAndVersion(string applicationName, string version)
{
long packageId = -1;
using (new GlobalAdministratorContext( ))
{
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
const string sql = @"
DECLARE @appVersionString BIGINT = dbo.fnAliasNsId( 'appVersionString', 'core', DEFAULT )
DECLARE @app BIGINT = dbo.fnAliasNsId( 'app', 'core', DEFAULT )
DECLARE @isOfType BIGINT = dbo.fnAliasNsId( 'isOfType', 'core', DEFAULT )
DECLARE @packageForApplication BIGINT = dbo.fnAliasNsId( 'packageForApplication', 'core', DEFAULT )
DECLARE @name BIGINT = dbo.fnAliasNsId( 'name', 'core', DEFAULT )
SELECT
TOP 1 ApplicationPackageId = ap.FromId
FROM
Relationship r
INNER JOIN
Data_NVarChar an ON an.TenantId = 0 AND r.FromId = an.EntityId AND an.FieldId = @name
INNER JOIN
Relationship ap ON ap.TenantId = 0 AND r.FromId = ap.ToId AND ap.TypeId = @packageForApplication
INNER JOIN
Data_NVarChar pv ON pv.TenantId = 0 AND ap.FromId = pv.EntityId AND pv.FieldId = @appVersionString
WHERE
r.TenantId = 0
AND
r.TypeId = @isOfType
AND
r.ToId = @app
AND
pv.Data LIKE @appVersion + '%'
AND
an.Data = @appName
ORDER BY
CAST( '/' + REPLACE( dbo.fnSanitiseVersion( pv.Data ), '.', '/' ) + '/' AS HIERARCHYID ) DESC" ;
using (IDbCommand command = ctx.CreateCommand(sql))
{
ctx.AddParameter(command, "@appName", DbType.String, applicationName);
ctx.AddParameter(command, "@appVersion", DbType.String, version);
object packageIdObject = command.ExecuteScalar( );
if (packageIdObject != null && packageIdObject != DBNull.Value)
{
packageId = ( long )packageIdObject;
}
}
}
}
return(packageId);
}
/// <summary>
/// Updates the user accounts last logon time.
/// </summary>
/// <param name="entityId">The entity identifier.</param>
/// <remarks>
/// This intentionally bypasses the entity model and directly updates the database asynchronously.
/// Any cached values are removed from the read-only cache upon completion.
/// Cache invalidators are not invoked due to this call.
/// </remarks>
private static void UpdateUserAccountLastLogon(long entityId)
{
long tenantId = RequestContext.TenantId;
long fieldId = WellKnownAliases.CurrentTenant.LastLogon;
DateTime now = DateTime.UtcNow;
ThreadPool.QueueUserWorkItem(state =>
{
using (DatabaseContextInfo.SetContextInfo($"Update last logon for user '{entityId}'"))
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
using (var command = ctx.CreateCommand("spData_DateTimeMerge", CommandType.StoredProcedure))
{
ctx.AddParameter(command, "@entityId", DbType.Int64, entityId);
ctx.AddParameter(command, "@tenantId", DbType.Int64, tenantId);
ctx.AddParameter(command, "@fieldId", DbType.Int64, fieldId);
ctx.AddParameter(command, "@data", DbType.DateTime, now);
ctx.AddParameter(command, "@context", DbType.AnsiString, DatabaseContextInfo.GetMessageChain(entityId));
command.ExecuteNonQuery( );
IEntityFieldValues values;
/////
// Remove the last logon time from the field cache (if it exists).
/////
if (EntityFieldCache.Instance.TryGetValue(entityId, out values))
{
object lastLogon;
if (values.TryGetValue(fieldId, out lastLogon))
{
values [fieldId] = now;
}
}
/////
// Invalidate any items referencing the LastLogon item (NOT the UserAccount item)
////
var cacheInvalidators = new CacheInvalidatorFactory( ).CacheInvalidators.ToList( );
List <IEntity> fieldTypes = new List <IEntity>
{
new IdEntity(fieldId)
};
foreach (ICacheInvalidator cacheInvalidator in cacheInvalidators)
{
cacheInvalidator.OnEntityChange(fieldTypes, InvalidationCause.Save, null);
}
}
}
});
}
public static long InsertEntity( )
{
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
using (var command = ctx.CreateCommand("DECLARE @output TABLE ( Id BIGINT ) INSERT INTO Entity ( TenantId, UpgradeId ) OUTPUT INSERTED.Id INTO @output VALUES ( @tenantId, @upgradeId ) SELECT Id FROM @output"))
{
ctx.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
ctx.AddParameter(command, "@upgradeId", DbType.Guid, Guid.NewGuid( ));
return(( long )command.ExecuteScalar( ));
}
}
}
/// <summary>
/// Get the event log entities to delete, usually the oldest ones.
/// </summary>
/// <param name="maxEventLogEntities">
/// The maximum number of event log rows to retain.
/// </param>
/// <returns>
/// The IDs of the event log entities to delete.
/// </returns>
public ICollection <long> GetEventLogEntitiesToDelete(int maxEventLogEntities)
{
if (maxEventLogEntities <= 0)
{
throw new ArgumentException("Must be positive", "maxEventLogEntities");
}
string sql = @"
DECLARE @isOfType BIGINT = dbo.fnAliasNsId( 'isOfType', 'core', @tenantId )
DECLARE @inherits BIGINT = dbo.fnAliasNsId( 'inherits', 'core', @tenantId )
DECLARE @createdDate BIGINT = dbo.fnAliasNsId( 'createdDate', 'core', @tenantId )
DECLARE @eventLogEntryType BIGINT = dbo.fnAliasNsId( 'workflowRunLogEntry', 'core', @tenantId )
CREATE TABLE #derived ( RowNum BIGINT IDENTITY(1,1), Id BIGINT PRIMARY KEY )
INSERT INTO #derived SELECT Id FROM dbo.fnDerivedTypes(@eventLogEntryType, @tenantId);
WITH auditLogRows AS
(
SELECT ROW_NUMBER() OVER(ORDER BY dt.Data DESC) RowNum, e.Id, dt.Data
FROM Entity e
JOIN Data_DateTime dt ON dt.EntityId = e.Id AND dt.TenantId = @tenantId AND dt.FieldId = @createdDate
JOIN Relationship et ON e.Id = et.FromId AND et.TypeId = @isOfType AND et.TenantId = @tenantId
JOIN #derived d on d.Id = et.ToId
WHERE e.TenantId = @tenantId
)
SELECT auditLogRows.Id FROM auditLogRows
WHERE auditLogRows.RowNum > @maximumEventLogRows
DROP TABLE #derived";
var entitiesToDelete = new List <long>();
using (DatabaseContext ctx = DatabaseContext.GetContext())
{
using (IDbCommand command = ctx.CreateCommand(sql))
{
ctx.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
ctx.AddParameter(command, "@maximumEventLogRows", DbType.Int32, maxEventLogEntities);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
entitiesToDelete.Add(reader.GetInt64(0));
}
}
}
}
return(entitiesToDelete);
}
/// <summary>
/// Called when an entity field is saved.
/// </summary>
/// <param name="entity">The entity.</param>
void IEntityFieldSave.OnSave(IEntity entity)
{
if (entity == null)
{
return;
}
long userId;
RequestContext.TryGetUserId(out userId);
/////
// This is already running in a transaction
/////
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
/////
// Command takes an Update Lock under SERIALIZABLE isolation to ensure concurrency.
/////
using (DatabaseContextInfo.SetContextInfo("Update autonumber instance"))
using (IDbCommand command = ctx.CreateCommand("spUpdateAutoNumberInstance", CommandType.StoredProcedure))
{
ctx.AddParameter(command, "@entityId", DbType.Int64, entity.Id);
ctx.AddParameter(command, "@fieldId", DbType.Int64, _field.Id);
ctx.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
ctx.AddParameter(command, "@context", DbType.AnsiString, DatabaseContextInfo.GetMessageChain(userId));
object newId = command.ExecuteScalar( );
if (newId != null && newId != DBNull.Value)
{
IEntityFieldValues readonlyFields;
/////
// Update the read-only cache with the new value.
/////
if (!EntityFieldCache.Instance.TryGetValue(entity.Id, out readonlyFields))
{
readonlyFields = new EntityFieldValues( );
EntityFieldCache.Instance[entity.Id] = readonlyFields;
}
/////
// Set the value.
/////
readonlyFields[_field.Id] = ( int )newId;
}
}
}
}
/// <summary>
/// Creates or removes a permission relationship between a tenant and an app in the global tenant.
/// </summary>
private static void UpdateTenantPermissionRelationship(long tenantId, long appId, string relationshipAlias, bool grant)
{
const string grantSql = @"-- Grant app access
IF ( @context IS NOT NULL )
BEGIN
DECLARE @contextInfo VARBINARY(128) = CONVERT( VARBINARY(128), @context )
SET CONTEXT_INFO @contextInfo
END
declare @relId bigint = dbo.fnAliasNsId( @relAlias, 'core', default )
if not exists ( select 1 from Relationship where TenantId = 0 and TypeId = @relId and FromId = @tenantId and ToId = @appId )
begin
insert
into Relationship (TenantId, TypeId, FromId, ToId)
values (0, @relId, @tenantId, @appId)
end
";
const string denySql = @"-- Deny app access
IF ( @context IS NOT NULL )
BEGIN
DECLARE @contextInfo VARBINARY(128) = CONVERT( VARBINARY(128), @context )
SET CONTEXT_INFO @contextInfo
END
declare @relId bigint = dbo.fnAliasNsId( @relAlias, 'core', default )
delete from Relationship
where TenantId = 0 and TypeId = @relId and FromId = @tenantId and ToId = @appId
";
long userId;
RequestContext.TryGetUserId(out userId);
using (DatabaseContextInfo.SetContextInfo("Update tenant permission relationship"))
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
string sql = grant ? grantSql : denySql;
using (IDbCommand command = ctx.CreateCommand(sql))
{
ctx.AddParameter(command, "@relAlias", DbType.String, relationshipAlias);
ctx.AddParameter(command, "@tenantId", DbType.Int64, tenantId);
ctx.AddParameter(command, "@appId", DbType.Int64, appId);
ctx.AddParameter(command, "@context", DbType.AnsiString, DatabaseContextInfo.GetMessageChain(userId));
command.ExecuteNonQuery( );
}
}
}
/// <summary>
/// Gets the package identifier by unique identifier and version.
/// </summary>
/// <param name="applicationGuid">The application unique identifier.</param>
/// <param name="version">The version.</param>
/// <returns>
/// The package identifier.
/// </returns>
public static long GetPackageIdByGuidAndVersion(Guid applicationGuid, string version)
{
long packageId = -1;
using (new GlobalAdministratorContext( ))
{
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
const string sql = @"
DECLARE @appVersionString BIGINT = dbo.fnAliasNsId( 'appVersionString', 'core', DEFAULT )
DECLARE @packageForApplication BIGINT = dbo.fnAliasNsId( 'packageForApplication', 'core', DEFAULT )
DECLARE @applicationId BIGINT = dbo.fnAliasNsId( 'applicationId', 'core', DEFAULT )
SELECT
TOP 1 ApplicationPackageId = r.FromId
FROM
Data_Guid g
INNER JOIN
Relationship r ON
g.EntityId = r.ToId AND
r.TenantId = 0 AND
r.TypeId = @packageForApplication
INNER JOIN
Data_NVarChar v ON v.TenantId = 0 AND r.FromId = v.EntityId AND v.FieldId = @appVersionString
WHERE
g.Data = @appGuid AND
g.TenantId = 0 AND
g.FieldId = @applicationId AND
v.Data LIKE @appVersion + '%'
ORDER BY
CAST( '/' + REPLACE( dbo.fnSanitiseVersion( v.Data ), '.', '/' ) + '/' AS HIERARCHYID ) DESC" ;
using (IDbCommand command = ctx.CreateCommand(sql))
{
ctx.AddParameter(command, "@appGuid", DbType.Guid, applicationGuid);
ctx.AddParameter(command, "@appVersion", DbType.String, version);
object packageIdObject = command.ExecuteScalar( );
if (packageIdObject != null && packageIdObject != DBNull.Value)
{
packageId = ( long )packageIdObject;
}
}
}
}
return(packageId);
}
/// <summary>
/// Find entities with a field of a particular value.
/// </summary>
/// <param name="fieldValues">The field values.</param>
/// <returns>
/// Dictionary matching field values to one or more entities that were found. N, or null if none were found.
/// </returns>
/// <exception cref="System.ArgumentNullException">
/// typeId
/// or
/// fieldId
/// or
/// fieldValues
/// </exception>
public ILookup <object, long> GetEntitiesByField(IReadOnlyCollection <object> fieldValues)
{
if (fieldValues == null)
{
throw new ArgumentNullException("fieldValues");
}
// Get user
long userId = RequestContext.GetContext( ).Identity.Id;
string sql = _queryResult.Sql;
// Run query
var entities = new List <Tuple <long, object> >( );
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
using (IDbCommand command = ctx.CreateCommand(sql))
{
ctx.AddParameter(command, "@user", DbType.Int64, userId);
ctx.AddParameter(command, "@tenant", DbType.Int64, RequestContext.TenantId);
command.AddListParameter("@valueList", _fieldType, fieldValues);
if (_queryResult.SharedParameters != null)
{
foreach (KeyValuePair <ParameterValue, string> parameter in _queryResult.SharedParameters)
{
ctx.AddParameter(command, parameter.Value, parameter.Key.Type, parameter.Key.Value);
}
}
using (IDataReader reader = command.ExecuteReader( ))
{
while (reader.Read( ))
{
long entityId = reader.GetInt64(0);
object fieldValue = reader.GetValue(1);
Tuple <long, object> entry = new Tuple <long, object>(entityId, fieldValue);
entities.Add(entry);
}
}
}
}
var results = entities.ToLookup(t => t.Item2, t => t.Item1);
return(results);
}
/// <summary>
/// Gets the name of the data table used by this type.
/// </summary>
/// <param name="typeId">The id.</param>
/// <returns>
/// The name of the data table used by this entity type.
/// </returns>
public static string GetDataTableName(long typeId)
{
EntityTypeContainer container;
/////
// Hit the cache.
/////
Instance.TryGetValue(typeId, out container);
if (container == null || (container.DataTableNameMember == null && !container.DataTableNameResolvedMember))
{
/////
// Initialize the dataTable to be a relationship.
/////
string dataTableName = "Relationship";
long dbFieldTableId = WellKnownAliases.CurrentTenant.DbFieldTable;
using (DatabaseContext ctx = DatabaseContext.GetContext())
{
using (IDbCommand command = ctx.CreateCommand())
{
command.CommandText = "dbo.spData_NVarCharRead";
command.CommandType = CommandType.StoredProcedure;
ctx.AddParameter(command, "@entityId", DbType.Int64, typeId);
ctx.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
ctx.AddParameter(command, "@fieldId", DbType.Int64, dbFieldTableId);
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read() && !reader.IsDBNull(3))
{
dataTableName = reader.GetString(3);
}
}
}
}
if (container == null)
{
container = new EntityTypeContainer(typeId);
Instance[typeId] = container;
}
container.DataTableName = dataTableName;
}
return(container.DataTableName);
}
/// <summary>
/// Inserts the data.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entityId">The entity identifier.</param>
/// <param name="fieldId">The field identifier.</param>
/// <param name="data">The data.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="databaseType">Type of the database.</param>
private static void InsertData <T>(long entityId, long fieldId, T data, string tableName, DbType databaseType)
{
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
using (var command = ctx.CreateCommand(string.Format("INSERT INTO Data_{0} ( EntityId, TenantId, FieldId, Data ) VALUES ( @entityId, @tenantId, @fieldId, @data )", tableName)))
{
ctx.AddParameter(command, "@entityId", DbType.Int64, entityId);
ctx.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
ctx.AddParameter(command, "@fieldId", DbType.Int64, fieldId);
ctx.AddParameter(command, "@data", databaseType, data);
command.ExecuteNonQuery( );
}
}
}
public static void InsertRelationship(long typeId, long fromId, long toId)
{
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
using (var command = ctx.CreateCommand("INSERT INTO Relationship ( TenantId, TypeId, FromId, ToId ) VALUES ( @tenantId, @typeId, @fromId, @toId )"))
{
ctx.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
ctx.AddParameter(command, "@typeId", DbType.Int64, typeId);
ctx.AddParameter(command, "@fromId", DbType.Int64, fromId);
ctx.AddParameter(command, "@toId", DbType.Int64, toId);
command.ExecuteNonQuery( );
}
}
}
/// <summary>
/// Gets the application identifier.
/// </summary>
/// <param name="applicationUpgradeId">The application upgrade identifier.</param>
/// <returns></returns>
private static long GetApplicationId(Guid applicationUpgradeId)
{
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
string commandText = @"
DECLARE @appId BIGINT = dbo.fnAliasNsId( 'applicationId', 'core', DEFAULT )
SELECT
EntityId
FROM
Data_Guid
WHERE
TenantId = 0
AND FieldId = @appId
AND Data = @applicationUpgradeId" ;
using (IDbCommand command = ctx.CreateCommand(commandText))
{
ctx.AddParameter(command, "@applicationUpgradeId", DbType.Guid, applicationUpgradeId);
object result = command.ExecuteScalar( );
if (result != null && result != DBNull.Value)
{
long id = ( long )result;
return(id);
}
return(-1);
}
}
}
/// <summary>
/// Creates the data.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="value">The value.</param>
/// <param name="data">The data.</param>
/// <param name="additionalColumns">The additional columns.</param>
/// <param name="additionalValues">The additional values.</param>
private void CreateData(DatabaseContext context, string tableName, string value, Dictionary <string, Queue <Tuple <long, long> > > data, string additionalColumns = null, string additionalValues = null)
{
using (IDbCommand command = context.CreateCommand($@"
INSERT INTO Entity (TenantId, UpgradeId) VALUES ( @tenantId, NEWID() )
DECLARE @entityId BIGINT = SCOPE_IDENTITY( )
INSERT INTO Entity (TenantId, UpgradeId) VALUES ( @tenantId, NEWID() )
DECLARE @fieldId BIGINT = SCOPE_IDENTITY( )
INSERT INTO Data_{tableName} (EntityId, TenantId, FieldId, Data{additionalColumns}) VALUES ( @entityId, @tenantId, @fieldId, {value}{additionalValues} )
SELECT @entityId, @fieldId"))
{
context.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
using (IDataReader reader = command.ExecuteReader( ))
{
if (reader.Read( ))
{
Queue <Tuple <long, long> > queue;
if (!data.TryGetValue(tableName, out queue))
{
queue = new Queue <Tuple <long, long> >( );
data[tableName] = queue;
}
queue.Enqueue(new Tuple <long, long>(reader.GetInt64(0), reader.GetInt64(1)));
}
}
}
}
/// <summary>
/// Preload all aliases in the current tenant.
/// </summary>
public static void PreloadAliases()
{
using (Profiler.Measure("Preload Aliases"))
using (DatabaseContext ctx = DatabaseContext.GetContext())
using (IDbCommand command = ctx.CreateCommand())
{
/////
// TODO: Replace with a stored procedure call.
/////
command.CommandText = @"-- Entity: Preload aliases
SELECT Namespace, Data Alias, EntityId, AliasMarkerId
FROM dbo.Data_Alias
WHERE TenantId = @tenantId AND Data IS NOT NULL";
ctx.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
using (IDataReader reader = command.ExecuteReader())
{
while (reader != null && reader.Read())
{
string ns = reader.GetString(0);
string alias = reader.GetString(1);
long id = reader.GetInt64(2);
Direction direction = reader.GetInt32(3) == 0 ? Direction.Forward : Direction.Reverse;
EntityAlias entityAlias = new EntityAlias(ns, alias);
CacheEntry cacheEntry = new CacheEntry {
Id = id, Direction = direction, Alias = entityAlias
};
_aliasCache[entityAlias] = cacheEntry;
}
}
}
}
/// <summary>
/// Get a value stored securely
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tenantId">The tenant Id</param>
/// <param name="context">A string representing the context of the storage.</param>
/// <param name="secureId">The secured Id as proved by the Set call.</param>
/// <returns>The secured value, null if no value </returns>
/// <exception cref="KeyNotFoundException">Thrown if the securedId is not found</exception>
public string Read(Guid secureId)
{
if (secureId == Guid.Empty)
{
throw new ArgumentException("Can not be an empty Id", nameof(secureId));
}
using (DatabaseContext ctx = DatabaseContext.GetContext())
{
using (IDbCommand command = ctx.CreateCommand("spSecuredDataRead", CommandType.StoredProcedure))
{
ctx.AddParameter(command, "@secureId", DbType.Guid, secureId);
var result = command.ExecuteScalar();
if (result == DBNull.Value)
{
return(null);
}
else if (result == null)
{
throw new SecureIdNotFoundException($"Key not present. secureId: '{secureId}'");
}
else
{
return((string)result);
}
}
}
}
public void TestEntity_Constructor_ActivationData_ValidType( )
{
ActivationData activationData = ActivationData.Empty;
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
using (IDbCommand command = ctx.CreateCommand("SELECT TOP 1 Id FROM Entity WHERE TenantId = @tenantId"))
{
ctx.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
using (IDataReader reader = command.ExecuteReader( ))
{
if (reader.Read( ))
{
activationData = new ActivationData(reader.GetInt64(0), RequestContext.TenantId);
}
}
}
}
var entity = new Entity(activationData);
Assert.IsNotNull(entity);
Assert.AreEqual(activationData.Id, entity.Id);
}
/// <summary>
/// Gets the count entity model audit log entries.
/// </summary>
/// <returns></returns>
private int GetCountEntityModelAuditLogEntries()
{
using (DatabaseContext context = DatabaseContext.GetContext())
{
string sql = @"DECLARE @isOfType BIGINT = dbo.fnAliasNsId( 'isOfType', 'core', @tenantId )
DECLARE @inherits BIGINT = dbo.fnAliasNsId( 'inherits', 'core', @tenantId )
DECLARE @auditLogCreatedDate BIGINT = dbo.fnAliasNsId( 'auditLogEntryCreatedDate', 'core', @tenantId )
DECLARE @auditLogEntryType BIGINT = dbo.fnAliasNsId( 'auditLogEntry', 'core', @tenantId )
CREATE TABLE #derived ( RowNum BIGINT IDENTITY(1,1), Id BIGINT PRIMARY KEY )
INSERT INTO #derived SELECT Id FROM dbo.fnDerivedTypes(@auditLogEntryType, @tenantId);
SELECT COUNT(*)
FROM Entity e
JOIN Relationship et ON e.Id = et.FromId AND et.TypeId = @isOfType AND et.TenantId = @tenantId
JOIN #derived d on d.Id = et.ToId
DROP TABLE #derived";
IDbCommand command = context.CreateCommand(sql);
context.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
var count = (int)command.ExecuteScalar();
return(count);
}
}
/// <summary>
/// Gets the tenant current package identifier by name.
/// </summary>
/// <param name="tenantId">The tenant identifier.</param>
/// <param name="applicationName">Name of the application.</param>
/// <returns>The tenants current package identifier.</returns>
public static long GetTenantCurrentPackageIdByName(long tenantId, string applicationName)
{
long packageId = -1;
using (new GlobalAdministratorContext( ))
{
using (DatabaseContext ctx = DatabaseContext.GetContext( ))
{
const string sql = @"
DECLARE @app BIGINT = dbo.fnAliasNsId( 'app', 'core', DEFAULT )
DECLARE @isOfType BIGINT = dbo.fnAliasNsId( 'isOfType', 'core', DEFAULT )
DECLARE @packageForApplication BIGINT = dbo.fnAliasNsId( 'packageForApplication', 'core', DEFAULT )
DECLARE @appVerId BIGINT = dbo.fnAliasNsId( 'appVerId', 'core', DEFAULT )
DECLARE @packageId BIGINT = dbo.fnAliasNsId( 'packageId', 'core', @tenantId )
SELECT
p.EntityId
FROM
Data_NVarChar an
INNER JOIN
Relationship ar ON an.EntityId = ar.FromId AND ar.TenantId = 0 AND TypeId = @isOfType AND ToId = @app AND an.Data = @appName
INNER JOIN
Relationship ap ON ar.FromId = ap.ToId AND ap.TypeId = @packageForApplication AND ap.TenantId = ar.TenantId
INNER JOIN
Data_Guid p ON ap.FromId = p.EntityId AND p.FieldId = @appVerId AND p.TenantId = ap.TenantId
INNER JOIN
Data_Guid sp ON p.Data = sp.Data AND sp.FieldId = @packageId AND sp.TenantId = @tenantId
WHERE
an.TenantId = 0" ;
using (IDbCommand command = ctx.CreateCommand(sql))
{
ctx.AddParameter(command, "@appName", DbType.String, applicationName);
ctx.AddParameter(command, "@tenantId", DbType.Int64, tenantId);
object packageIdObject = command.ExecuteScalar( );
if (packageIdObject != null && packageIdObject != DBNull.Value)
{
packageId = ( long )packageIdObject;
}
}
}
}
return(packageId);
}
/// <summary>
/// Create/Save long running task in the database.
/// </summary>
/// <param name="info">The info.</param>
public static void SaveLongRunningTaskInfo(LongRunningInfo info)
{
string infoXml = XmlHelper.SerializeUsingDataContract(info);
using (DatabaseContext databaseContext = DatabaseContext.GetContext( ))
{
// Create and initialize the command object
using (IDbCommand command = databaseContext.CreateCommand("spSaveLongRunningTask", CommandType.StoredProcedure))
{
databaseContext.AddParameter(command, "@taskId", DbType.Guid, info.TaskId);
databaseContext.AddParameter(command, "@status", DbType.String, info.Status);
databaseContext.AddParameter(command, "@additionalInfo", DbType.String, infoXml);
command.ExecuteNonQuery( );
}
}
}
/// <summary>
/// Adds the specified test string data.
/// </summary>
/// <param name="context"></param>
/// <param name="baseId"></param>
private void AddTestStringData(DatabaseContext context, Guid baseId)
{
using (IDbCommand command = context.CreateCommand(@"INSERT INTO Batch([BatchGuid])
VALUES(@base)" ))
{
context.AddParameter(command, "@base", DatabaseType.GuidType).Value = baseId;
command.ExecuteNonQuery( );
}
}
/// <summary>
/// Creates the entity.
/// </summary>
/// <param name="context">The context.</param>
private void CreateEntity(DatabaseContext context)
{
using (IDbCommand command = context.CreateCommand("INSERT INTO Entity ( TenantId, UpgradeId ) VALUES ( @tenantId, NEWID() )"))
{
context.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId);
command.ExecuteNonQuery( );
}
}
/// <summary>
/// Gets the data.
/// </summary>
/// <returns></returns>
public IList <Data> GetData( )
{
var data = new List <Data>( );
var sb = new StringBuilder( );
sb.AppendLine(string.Format(SqlQueries.GetTenantAppData, string.Empty));
using (IDbCommand command = Context.CreateCommand( ))
{
command.CommandText = sb.ToString( );
Context.AddParameter(command, "@tenant", DbType.Int64, Tenant.Id);
Context.AddParameter(command, "@solutionId", DbType.Int64, Id);
using (IDataReader reader = command.ExecuteReader( ))
{
while (reader.Read( ))
{
string type = reader.GetString(0, null);
Guid entityUpgradeId = reader.GetGuid(1);
Guid fieldUpgradeId = reader.GetGuid(2);
object value = reader.GetValue(3);
string entityName = reader.GetString(4, null);
string fieldName = reader.GetString(5, null);
data.Add(new Data
{
EntityUpgradeId = entityUpgradeId,
FieldUpgradeId = fieldUpgradeId,
Value = value,
EntityName = entityName,
FieldName = fieldName,
Type = type
});
}
}
}
Data = data;
return(Data);
}
