/// <summary> /// DO NOT USE DIRECTLY THIS METHOD FROM YOUR CODE. /// Updates the stream in the appropriate row of the Files table specified by the context. /// </summary> public void UpdateStream(BlobStorageContext context, Stream stream) { // We have to work with an integer since SQL does not support // binary values bigger than [Int32.MaxValue]. var bufferSize = Convert.ToInt32(stream.Length); var buffer = new byte[bufferSize]; if (bufferSize > 0) { // Read bytes from the source stream.Seek(0, SeekOrigin.Begin); stream.Read(buffer, 0, bufferSize); } //UNDONE: [DIREF] get connection string through constructor using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, CancellationToken.None)) { ctx.ExecuteNonQueryAsync(WriteStreamScript, cmd => { cmd.Parameters.AddRange(new[] { ctx.CreateParameter("@Id", SqlDbType.Int, context.FileId), ctx.CreateParameter("@Value", SqlDbType.VarBinary, bufferSize, buffer), }); }).GetAwaiter().GetResult(); } }
public override async Task <IEnumerable <int> > QueryNodesByTypeAndPathAndPropertyAsync(int[] nodeTypeIds, string pathStart, bool orderByPath, List <QueryPropertyData> properties, CancellationToken cancellationToken) { using (var ctx = new MsSqlDataContext(cancellationToken)) { var typeCount = nodeTypeIds?.Length ?? 0; var onlyNodes = true; (bool IsNodeTable, bool IsColumn, string Column, DbType DataType, object Value)[] propertyMapping = null;
public static Task AddStreamAsync(BlobStorageContext context, Stream stream, MsSqlDataContext dataContext) { if (stream == null || stream.Length == 0L) { return(Task.CompletedTask); } return(UpdateStreamAsync(context, stream, dataContext)); }
public byte[] ReadRandom(BlobStorageContext context, long offset, int count) { using (var ctx = new MsSqlDataContext(_connectionString, DataOptions, CancellationToken.None)) { return((byte[])ctx.ExecuteScalarAsync(LoadBinaryFragmentScript, cmd => { cmd.Parameters.AddRange(new[] { ctx.CreateParameter("@FileId", SqlDbType.Int, context.FileId), ctx.CreateParameter("@Position", SqlDbType.BigInt, offset + 1), ctx.CreateParameter("@Count", SqlDbType.Int, count), }); }).GetAwaiter().GetResult()); } }
/// <summary> /// Returns a context object that holds MsSql-specific data for blob storage operations. /// </summary> /// <param name="fileId">File identifier.</param> /// <param name="clearStream">Whether the blob provider should clear the stream during assembling the context.</param> /// <param name="versionId">Content version id.</param> /// <param name="propertyTypeId">Binary property type id.</param> /// <param name="cancellationToken">The token to monitor for cancellation requests.</param> public async Task <BlobStorageContext> GetBlobStorageContextAsync(int fileId, bool clearStream, int versionId, int propertyTypeId, CancellationToken cancellationToken) { var sql = GetBlobStorageContextScript; if (clearStream) { sql = ClearStreamByFileIdScript + sql; } cancellationToken.ThrowIfCancellationRequested(); using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken)) { return(await ctx.ExecuteReaderAsync(sql, cmd => { cmd.Parameters.Add(ctx.CreateParameter("@FileId", DbType.Int32, fileId)); if (clearStream) { cmd.Parameters.Add(ctx.CreateParameter("@VersionId", DbType.Int32, versionId)); cmd.Parameters.Add(ctx.CreateParameter("@PropertyTypeId", DbType.Int32, propertyTypeId)); } }, async (reader, cancel) => { cancel.ThrowIfCancellationRequested(); if (!await reader.ReadAsync(cancel).ConfigureAwait(false)) { return null; } var length = reader.GetSafeInt64(0); var providerName = reader.GetSafeString(1); var providerData = reader.GetSafeString(2); var provider = Providers.GetProvider(providerName); return new BlobStorageContext(provider, providerData) { VersionId = versionId, PropertyTypeId = propertyTypeId, FileId = fileId, Length = length, BlobProviderData = provider is IBuiltInBlobProvider ? new BuiltinBlobProviderData() : provider.ParseData(providerData) }; }).ConfigureAwait(false)); } }
/// <inheritdoc /> public async Task WriteAsync(BlobStorageContext context, long offset, byte[] buffer, CancellationToken cancellationToken) { using (var ctx = new MsSqlDataContext(_connectionString, DataOptions, cancellationToken)) { await ctx.ExecuteNonQueryAsync(UpdateStreamWriteChunkScript, cmd => { cmd.Parameters.AddRange(new[] { ctx.CreateParameter("@FileId", SqlDbType.Int, context.FileId), ctx.CreateParameter("@VersionId", SqlDbType.Int, context.VersionId), ctx.CreateParameter("@PropertyTypeId", SqlDbType.Int, context.PropertyTypeId), ctx.CreateParameter("@Data", SqlDbType.VarBinary, buffer), ctx.CreateParameter("@Offset", SqlDbType.BigInt, offset), }); }).ConfigureAwait(false); } }
private async Task CleanupFilesSetDeleteFlagAsync(string script, CancellationToken cancellationToken) { using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken)) { using (var transaction = ctx.BeginTransaction()) { try { await ctx.ExecuteNonQueryAsync(script).ConfigureAwait(false); transaction.Commit(); } catch (Exception e) { throw new DataException("Error during setting deleted flag on files.", e); } } } }
public async Task <bool> CleanupFilesAsync(CancellationToken cancellationToken) { using (var dctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken)) { return(await dctx.ExecuteReaderAsync(CleanupFileScript, async (reader, cancel) => { try { var deleted = false; var fileId = 0; var size = 0L; string providerName = null; string providerData = null; // We do not care about the number of deleted rows, // we only want to know if a row was deleted or not. if (await reader.ReadAsync(cancel).ConfigureAwait(false)) { deleted = true; fileId = reader.GetSafeInt32(reader.GetOrdinal("FileId")); size = reader.GetSafeInt64(reader.GetOrdinal("Size")); providerName = reader.GetSafeString(reader.GetOrdinal("BlobProvider")); providerData = reader.GetSafeString(reader.GetOrdinal("BlobProviderData")); } // delete bytes from the blob storage var provider = Providers.GetProvider(providerName); var ctx = new BlobStorageContext(provider, providerData) { VersionId = 0, PropertyTypeId = 0, FileId = fileId, Length = size }; await ctx.Provider.DeleteAsync(ctx, cancel).ConfigureAwait(false); return deleted; } catch (Exception ex) { throw new DataException("Error during binary cleanup.", ex); } }).ConfigureAwait(false)); } }
public async Task CommitChunkAsync(int versionId, int propertyTypeId, int fileId, long fullSize, BinaryDataValue source, CancellationToken cancellationToken) { try { using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken)) { using (var transaction = ctx.BeginTransaction()) { await ctx.ExecuteNonQueryAsync(CommitChunkScript, cmd => { cmd.Parameters.AddRange(new[] { ctx.CreateParameter("@FileId", DbType.Int32, fileId), ctx.CreateParameter("@VersionId", DbType.Int32, versionId), ctx.CreateParameter("@PropertyTypeId", DbType.Int32, propertyTypeId), ctx.CreateParameter("@Size", DbType.Int64, fullSize), ctx.CreateParameter("@Checksum", DbType.AnsiString, 200, DBNull.Value), ctx.CreateParameter("@ContentType", DbType.String, 50, source != null ? source.ContentType : string.Empty), ctx.CreateParameter("@FileNameWithoutExtension", DbType.String, 450, source != null ? source.FileName.FileNameWithoutExtension == null ? DBNull.Value : (object)source.FileName.FileNameWithoutExtension : DBNull.Value), ctx.CreateParameter("@Extension", DbType.String, 50, source != null ? ValidateExtension(source.FileName.Extension) : string.Empty), }); }).ConfigureAwait(false); transaction.Commit(); } } } catch (Exception ex) { throw new DataException("Error during committing binary chunk to file stream.", ex); } }
/* =============================================================================== Methods for Steps */ public Dictionary <string, string> GetContentPathsWhereTheyAreAllowedChildren(List <string> names) { var result = new Dictionary <string, string>(); var whereClausePart = string.Join(Environment.NewLine + " OR" + Environment.NewLine, names.Select(n => $" (t.Value like '{n}' OR t.Value like '% {n} %' OR t.Value like '{n} %' OR t.Value like '% {n}')")); // testability: the first line is recognizable for the tests. var sql = $"-- GetContentPathsWhereTheyAreAllowedChildren: [{string.Join(", ", names)}]" + Environment.NewLine; sql += @"SELECT n.Path, t.Value FROM LongTextProperties t JOIN PropertyTypes p ON p.PropertyTypeId = t.PropertyTypeId JOIN Versions v ON t.VersionId = v.VersionId JOIN Nodes n ON n.NodeId = v.NodeId WHERE p.Name = 'AllowedChildTypes' AND ( " + whereClausePart + @" ) "; //TODO: [DIREF] get options from DI through constructor using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), CancellationToken.None)) { var _ = ctx.ExecuteReaderAsync(sql, async(reader, cancel) => { cancel.ThrowIfCancellationRequested(); while (await reader.ReadAsync(cancel).ConfigureAwait(false)) { cancel.ThrowIfCancellationRequested(); result.Add(reader.GetString(0), reader.GetString(1)); } return(Task.FromResult(0)); }).GetAwaiter().GetResult(); } return(result); }
public static async Task UpdateStreamAsync(BlobStorageContext context, Stream stream, MsSqlDataContext dataContext) { // We have to work with an integer since SQL does not support // binary values bigger than [Int32.MaxValue]. var bufferSize = Convert.ToInt32(stream.Length); var buffer = new byte[bufferSize]; if (bufferSize > 0) { // Read bytes from the source stream.Seek(0, SeekOrigin.Begin); stream.Read(buffer, 0, bufferSize); } await dataContext.ExecuteNonQueryAsync(WriteStreamScript, cmd => { cmd.Parameters.AddRange(new[] { dataContext.CreateParameter("@Id", SqlDbType.Int, context.FileId), dataContext.CreateParameter("@Value", SqlDbType.VarBinary, bufferSize, buffer), }); }).ConfigureAwait(false); }
public async Task <string> StartChunkAsync(IBlobProvider blobProvider, int versionId, int propertyTypeId, long fullSize, CancellationToken cancellationToken) { var ctx = new BlobStorageContext(blobProvider) { VersionId = versionId, PropertyTypeId = propertyTypeId, FileId = 0, Length = fullSize }; string blobProviderName = null; string blobProviderData = null; if (!(blobProvider is IBuiltInBlobProvider)) { await blobProvider.AllocateAsync(ctx, cancellationToken).ConfigureAwait(false); blobProviderName = blobProvider.GetType().FullName; blobProviderData = BlobStorageContext.SerializeBlobProviderData(ctx.BlobProviderData); } try { using (var dctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken)) { using (var transaction = dctx.BeginTransaction()) { var result = await dctx.ExecuteReaderAsync(InsertStagingBinaryScript, cmd => { cmd.Parameters.AddRange(new[] { dctx.CreateParameter("@VersionId", DbType.Int32, versionId), dctx.CreateParameter("@PropertyTypeId", DbType.Int32, propertyTypeId), dctx.CreateParameter("@Size", DbType.Int64, fullSize), dctx.CreateParameter("@BlobProvider", DbType.String, 450, blobProviderName != null ? (object)blobProviderName : DBNull.Value), dctx.CreateParameter("@BlobProviderData", DbType.String, int.MaxValue, blobProviderData != null ? (object)blobProviderData : DBNull.Value), }); }, async (reader, cancel) => { int binaryPropertyId; int fileId; cancel.ThrowIfCancellationRequested(); if (await reader.ReadAsync(cancel).ConfigureAwait(false)) { binaryPropertyId = reader.GetSafeInt32(0); fileId = reader.GetSafeInt32(1); } else { throw new DataException("File row could not be inserted."); } ctx.FileId = fileId; return(new ChunkToken { VersionId = versionId, PropertyTypeId = propertyTypeId, BinaryPropertyId = binaryPropertyId, FileId = fileId }.GetToken()); }).ConfigureAwait(false); transaction.Commit(); return(result); } } } catch (Exception ex) { throw new DataException("Error during saving binary chunk to SQL Server.", ex); } }
public async Task <BinaryCacheEntity> LoadBinaryCacheEntityAsync(int versionId, int propertyTypeId, CancellationToken cancellationToken) { using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken)) return(await LoadBinaryCacheEntityAsync(versionId, propertyTypeId, ctx).ConfigureAwait(false)); }
/* =========================================================================================== Platform specific implementations */ /* =============================================================================================== Nodes */ /* =============================================================================================== NodeHead */ /* =============================================================================================== NodeQuery */ public override async Task <IEnumerable <int> > QueryNodesByTypeAndPathAndNameAsync(int[] nodeTypeIds, string[] pathStart, bool orderByPath, string name, CancellationToken cancellationToken) { var sql = new StringBuilder("SELECT NodeId FROM Nodes WHERE "); var first = true; if (pathStart != null && pathStart.Length > 0) { for (int i = 0; i < pathStart.Length; i++) { if (pathStart[i] != null) { pathStart[i] = pathStart[i].Replace("'", "''"); } } sql.AppendLine("("); for (int i = 0; i < pathStart.Length; i++) { if (i > 0) { sql.AppendLine().Append(" OR "); } sql.Append(" Path LIKE N'"); sql.Append(EscapeForLikeOperator(pathStart[i])); if (!pathStart[i].EndsWith(RepositoryPath.PathSeparator)) { sql.Append(RepositoryPath.PathSeparator); } sql.Append("%' COLLATE Latin1_General_CI_AS"); } sql.AppendLine(")"); first = false; } if (name != null) { name = name.Replace("'", "''"); if (!first) { sql.Append(" AND"); } sql.Append(" Name = '").Append(name).Append("'"); first = false; } if (nodeTypeIds != null) { if (!first) { sql.Append(" AND"); } sql.Append(" NodeTypeId"); if (nodeTypeIds.Length == 1) { sql.Append(" = ").Append(nodeTypeIds[0]); } else { sql.Append(" IN (").Append(string.Join(", ", nodeTypeIds)).Append(")"); } } if (orderByPath) { sql.AppendLine().Append("ORDER BY Path"); } cancellationToken.ThrowIfCancellationRequested(); using (var ctx = new MsSqlDataContext(cancellationToken)) { return(await ctx.ExecuteReaderAsync(sql.ToString(), async (reader, cancel) => { cancel.ThrowIfCancellationRequested(); var result = new List <int>(); while (await reader.ReadAsync(cancel).ConfigureAwait(false)) { cancel.ThrowIfCancellationRequested(); result.Add(reader.GetSafeInt32(0)); } return (IEnumerable <int>)result; }).ConfigureAwait(false)); } }