/// <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); }